Database table optimization method and device, equipment and storage medium
By extracting performance-impacting query statements from query logs and parsing the target query fields and time ranges, an optimized database table is generated. This solves the problem that fixed indexes in OLAP databases cannot adapt to business query patterns, thereby improving query efficiency.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2025-12-09
- Publication Date
- 2026-04-28
AI Technical Summary
The fixed indexes predefined during table creation in existing OLAP databases are difficult to dynamically adapt to the diversity of business query patterns, resulting in redundant data loading, increased query latency, and impact on query efficiency.
Extract performance-impacting query statements from the query logs of the original database tables, parse the target query fields and time ranges, generate the target database table, and optimize the query process using ROLLUP indexes and time partitioning.
By optimizing the target database tables, redundant data calculations are avoided, query latency is reduced, and query execution efficiency is improved.
Smart Images
Figure CN121935240A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of database technology, and in particular to methods, apparatus, devices and storage media for optimizing database tables. Background Technology
[0002] OLAP (Online Analytical Processing) databases are widely used to handle complex queries on massive amounts of data. When creating tables, data distribution and index structure are usually pre-defined based on historical experience, and the most frequently used fields are selected as indexes. However, predefined fixed indexes are difficult to dynamically adapt to the diversity of business query patterns, which can easily lead to redundant data being loaded into memory for computation, thereby increasing query latency and affecting query efficiency. Summary of the Invention
[0003] The technical problem solved by this invention is to provide a method, apparatus, device and storage medium for optimizing database tables, which effectively solves the problem of low data query efficiency.
[0004] The above-mentioned technical problems are solved by the following technical solutions: A database table optimization method includes: Extract performance-impacting query statements from the query logs of the original database tables; The performance impact query statement is parsed to determine the target query field and the target time range, wherein the frequency of occurrence of the target query field is greater than a preset frequency threshold; Based on the target query fields, target time range, and original database table, a target database table is generated. Compared with the prior art, the database table optimization method of this invention has the following beneficial effects: The database table optimization method provided by this embodiment includes extracting performance-impacting query statements from the query logs of the original database table; parsing the performance-impacting query statements to determine the target query fields and target time ranges; and generating a target database table based on the target query fields, target time ranges, and the original database table. This method extracts performance-impacting query statements from the query logs and parses their target query fields and target time ranges to generate an optimized target database table. This allows users to perform queries through the target database table, avoiding redundant data in calculations, reducing query latency, and improving the efficiency of query statement execution.
[0005] In one embodiment, extracting performance-impacting query statements from the query logs of the original database table includes: Query statements in the query log whose execution time is greater than or equal to the preset execution time are identified as performance-impacting query statements.
[0006] In one embodiment, it further includes: Query statements in the query logs of the original database tables that failed due to exceeding memory limits were identified as performance-impacting queries.
[0007] In one embodiment, it includes: The performance impact query statement is parsed to determine the query execution plan; Extract the filter criteria fields and target time range from the query execution plan; Select the filter fields that appear more frequently than a preset frequency threshold as the target query fields.
[0008] In one embodiment, it includes: Based on the target query field and the target time range, a first database table is generated. The first database table includes one or more time partitions, which are determined based on the target time range. Import the data from the original database table into the first database table to generate the target database table.
[0009] In one embodiment, the method further includes: The target database table and the original database table are compared; If the comparison results are consistent, then the target database table is confirmed to be correct. In one embodiment, the method further includes: Test the query performance of the target database tables; If the performance metrics of the target database table meet the preset conditions, then the target database table is determined as the database table to be queried, and the performance metrics include at least response time.
[0010] An optimization apparatus for database tables, the apparatus comprising: The statement extraction module is used to extract performance-impacting query statements from the query logs of the original database tables; The statement parsing module is used to parse the performance impact query statement, determine the target query field and the target time range, wherein the frequency of occurrence of the target query field is greater than a preset frequency threshold; The database table generation module is used to generate a target database table based on the target query field, the target time range, and the original database table.
[0011] A computer device, comprising: A memory and a processor are interconnected and communicate with each other. The memory stores computer instructions, and the processor executes the computer instructions to perform a method for optimizing database tables.
[0012] A computer-readable storage medium storing computer instructions for causing a computer to perform a method for optimizing database tables. Attached Figure Description
[0013] To more clearly illustrate the specific embodiments of the present invention or the technical solutions in the prior art, the drawings used in the description of the specific embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are some embodiments of the present invention. For those skilled in the art, other drawings can be obtained from these drawings without creative effort.
[0014] Figure 1 This is a flowchart illustrating the database table optimization method according to an embodiment of the present invention. Figure 2 This is a schematic diagram of a database table optimization device according to an embodiment of the present invention; Figure 3 This is a schematic diagram of the hardware structure of a computer device according to an embodiment of the present invention. Detailed Implementation
[0015] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.
[0016] In the description of this application, it should be understood that the terms "center", "upper", "lower", "front", "rear", "left", "right", "vertical", "horizontal", "top", "bottom", "inner", "outer", etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are only for the convenience of describing this application and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limitations on this application.
[0017] The terms "first" and "second" are used for descriptive purposes only and should not be construed as indicating or implying relative importance or implicitly specifying the number of technical features indicated. Therefore, a feature defined as "first" or "second" may explicitly or implicitly include one or more of that feature. In the description of this application, unless otherwise stated, "a plurality of" means two or more.
[0018] In the description of this application, it should be noted that, unless otherwise expressly specified and limited, the terms "installation," "connection," and "linking" should be interpreted broadly. For example, they can refer to a fixed connection, a detachable connection, or an integral connection; they can refer to a mechanical connection or an electrical connection; they can refer to a direct connection or an indirect connection through an intermediate medium; and they can refer to the internal connection between two components. Those skilled in the art can understand the specific meaning of the above terms in this application based on the specific circumstances.
[0019] According to embodiments of the present invention, a method for optimizing database tables is provided. Figure 1 This is a flowchart of a database table optimization method according to an embodiment of the present invention, such as... Figure 1 As shown, the process includes the following steps: Step S101: Extract performance-impacting query statements from the query logs of the original database tables.
[0020] The query log is a log generated by the database system. It records detailed information about queries performed on the original database tables. The query log can be periodically scanned (e.g., every hour) using predefined monitoring scripts. Performance-impacting queries refer to SQL statements that may cause system slowdowns.
[0021] In some optional implementations, step S101 includes: identifying query statements in the query log with an execution duration greater than or equal to a preset execution duration as performance-impacting query statements.
[0022] The execution duration field recorded in the query log can be used as a basis. Execution duration refers to the total time consumed from the start to the end of the query. The preset execution duration is a configurable threshold parameter that can be dynamically set according to the service level agreement of the business system and user needs. By parsing the query log, records related to execution duration are extracted from the log. The actual execution duration is compared with the preset execution duration. If the extracted execution duration is greater than or equal to the preset execution duration, the query statement corresponding to that log entry is marked as a performance-impacting query statement.
[0023] In some optional implementations, step S101 includes: identifying query statements in the query log of the original database table that failed to execute due to memory exceeding the limit as performance-impacting query statements.
[0024] By analyzing the error fields in the query logs, check if they contain keywords related to memory exceeding limits. If they do, it indicates that the query failed because memory exceeded the limit, and the corresponding query is marked as a performance-impacting query.
[0025] Step S102: Parse the performance impact query statement to determine the target query field and target time range.
[0026] The target query field appears more frequently than a preset frequency threshold. Performance-impacting queries are analyzed to extract filtering conditions, which include query fields (e.g., region, product type) and time ranges. The target query field is the query field whose frequency exceeds the preset frequency threshold, and the target time range includes time-related filtering conditions appearing in all queries.
[0027] Specifically, the EXPLAIN command in the database can be used to obtain a detailed query execution plan. The target query field and target time range can then be extracted from the execution plan, which contains detailed filtering conditions. Regular expressions can be used to extract time-range related filtering conditions and define them as the target time range. The frequency of each query field in the filtering conditions is then calculated, and query fields with a frequency greater than a preset frequency are identified as target query fields.
[0028] Step S103: Generate the target database table based on the target query field, the target time range, and the original database table.
[0029] Use the target query field as the key of the ROLLUP index (a materialized index that speeds up queries in Doris database tables) of the target database table, and generate multiple time partitions based on the target time range.
[0030] For example, if the original database table contains data from January to December, and the target time range to be extracted is only January to March and October to December, then time partitions for January to March and October to December are set in the target database table. The data from the original database table is migrated into a new table to form the target database table, which can be queried directly as needed. The target database table can be used as a temporary table for temporary queries and can be deleted later as needed.
[0031] The database table optimization method provided in this invention includes extracting performance-impact query statements from the query logs of the original database table; parsing the performance-impact query statements to determine the target query fields and target time ranges; and generating a target database table based on the target query fields, target time ranges, and the original database table. This method extracts performance-impact query statements from the query logs and parses their target query fields and target time ranges to generate an optimized target database table. This allows users to perform queries using the target database table, avoiding redundant data in calculations, reducing query latency, and improving the efficiency of query statement execution.
[0032] According to an embodiment of the present invention, a method for optimizing a database table is provided, the method comprising the following steps: Step S201: Extract performance-impacting query statements from the query logs of the original database tables.
[0033] Please see details Figure 1 Step S101 of the illustrated embodiment will not be described again here.
[0034] Step S202: Parse the performance impact query statement to determine the target query field and target time range.
[0035] Specifically, step S202 includes: Step S2021: Parse the performance impact query statement and determine the query execution plan.
[0036] The EXPLAIN command provided by the database engine can be used to parse each performance-impacting query statement to obtain the query execution plan. The query execution plan can be a structured text or tree description, which includes detailed filtering conditions, data access paths, etc.
[0037] Step S2022: Extract the filter criteria fields and target time range from the query execution plan.
[0038] The query execution plan is analyzed, and the nodes representing data read operations are scanned in particular. The filter condition fields and target time ranges are extracted from the detailed information of the nodes representing data read operations. The filter condition fields are fields used as filter conditions, such as: region, device type, etc.
[0039] Given the prevalence of time-series data queries, the time range can be used as a separate filtering condition. Regular expressions can be used to extract time-range related filtering conditions from the filtering criteria and determine them as the target time range.
[0040] Step S2023: The filter condition fields that appear more frequently than the preset frequency threshold are determined as the target query fields.
[0041] Count the frequency of occurrence of all extracted filter condition fields, and use the filter conditions with a frequency greater than a preset frequency threshold as the target query fields.
[0042] Step S203: Generate the target database table based on the target query field, the target time range, and the original database table.
[0043] Specifically, step S203 includes: Step S2031: Generate the first database table based on the target query field and the target time range.
[0044] The first database table includes one or more time partitions, determined based on the target time range. When generating the first database table, a table structure and partitioning strategy adapted to high-frequency query scenarios are constructed by combining the query frequency of the target query fields with the target time range. First, for the target query fields, fields with the highest frequency are prioritized as core index fields. For example, the top two high-frequency target query fields are combined to construct a ROLLUP index, which serves as the core for query optimization in the first database table, ensuring that high-frequency filtering conditions directly hit the index and reducing invalid data scanning. The field order in the ROLLUP index can be adjusted according to the frequency of combined use of fields in the query conditions, prioritizing fields with higher individual filtering frequencies to improve index matching efficiency.
[0045] If the target time range is a continuous date interval, such as from October 1, 2023 to December 31, 2023, it can be split into multiple continuous partitions based on time granularity (day, week, or month, which can be dynamically configured according to the query scenario). If the target time range contains multiple discrete intervals, such as from January 1, 2023 to March 31, 2023, or from October 1, 2023 to December 31, 2023, a separate partition is created for each discrete interval. Through the partitioning strategy, query requests can load only the partitioned data within the target time range, avoiding excessive query latency caused by loading redundant data into memory under static partitioning mode.
[0046] Step S2032: Import the data from the original database table into the first database table to generate the target database table.
[0047] Data import can be performed using INSERT INTO SELECT, filtering all data within the target time range from the original database table and importing them in batches into the corresponding partition of the first database table. The import operation can be performed using a database connection tool, specifying the connection information of the original database (including host IP, port number, username, etc.) and executing structured query import statements to ensure the stability and efficiency of data transmission.
[0048] Once all the target data has been imported, the first database table will become the target database table containing complete and valid data.
[0049] The target database table generated in this embodiment of the invention achieves accurate adaptation to high-frequency query scenarios through index optimization and partition optimization, which can significantly reduce the data scanning range and resource consumption during the query process and improve query efficiency.
[0050] In some optional implementations, after step S2032 above, the method further includes: comparing the target database table and the original database table; if the comparison results are consistent, then the target database table is confirmed to be correct.
[0051] To determine if there is data loss or duplicate import during the data migration process, the data in the same range of the original and target database tables must be compared. Specifically, this can be done by filtering data within the same target time range. Additionally, further sampling comparison can be performed. Specifically, a predetermined proportion of samples can be randomly drawn from the target database table and compared with the corresponding areas of the original database table. If all samples match the data in the original database table, then the target database table is confirmed to be correct.
[0052] If the comparison determines that there are data errors in the target database table, i.e. the comparison results are inconsistent, the specific details of the inconsistency are recorded, and a rollback statement is automatically executed to delete the inconsistent data. The target database table can also be further deleted, and the above steps S101 to S103 are re-executed to regenerate the target database table.
[0053] In some optional implementations, after step S103, the method further includes: detecting the query performance of the target database table; if the performance indicators of the target database table meet preset conditions, then the target database table is determined as the database table to be queried. The performance indicators include at least response time.
[0054] Query performance metrics can include response time, CPU utilization, peak memory usage, etc., with thresholds set for each metric. Each metric is tested individually, and the results are compared to the corresponding thresholds. If all performance metrics meet the preset conditions, the table is selected as the target database table for querying. After the target database table is selected, users can directly query it according to their needs.
[0055] This embodiment also provides a database table optimization device for implementing the above embodiments and preferred embodiments; details already described will not be repeated. As used below, the term "module" can refer to a combination of software and / or hardware that performs a predetermined function. Although the device described in the following embodiments is preferably implemented in software, hardware implementation, or a combination of software and hardware, is also possible and contemplated.
[0056] This embodiment provides an optimized configuration for a database table, such as... Figure 2 As shown, it includes: The statement extraction module is used to extract performance-impacting query statements from the query logs of the original database tables; The statement parsing module is used to parse the performance impact query statement, determine the target query field and the target time range, wherein the frequency of occurrence of the target query field is greater than a preset frequency threshold; The database table generation module is used to generate a target database table based on the target query field, the target time range, and the original database table.
[0057] In some alternative implementations, the statement extraction module includes: The first extraction unit is used to identify query statements in the query log whose execution time is greater than or equal to a preset execution time as performance-impacting query statements.
[0058] In some alternative implementations, the statement extraction module includes: The second extraction module is used to identify query statements in the query logs of the original database tables that failed due to memory exceeding the limit as performance-impacting query statements.
[0059] In some alternative implementations, the statement parsing module includes: The statement parsing unit is used to parse the performance impact query statement and determine the query execution plan; The field extraction unit is used to extract the filter condition fields and target time range from the query execution plan; The field determination unit is used to determine the filter condition fields that appear more frequently than a preset frequency threshold as the target query fields.
[0060] In some optional implementations, the database table generation module includes: The first generation unit is configured to generate a first database table based on the target query field and the target time range. The first database table includes one or more time partitions, which are determined based on the target time range. The data import unit is used to import data from the original database table into the first database table to generate the target database table.
[0061] In some optional implementations, the database table generation module further includes: The comparison unit is used to compare the target database table with the original database table; The result determination unit is used to confirm that the target database table is correct if the comparison results are consistent. In some alternative embodiments, the apparatus further includes: The performance testing module is used to test the query performance of the target database table; The database table determination module is used to determine the target database table as the database table to be queried if the performance indicators of the target database table meet preset conditions, wherein the performance indicators include at least response time.
[0062] Further functional descriptions of the above modules and units are the same as those in the corresponding embodiments described above, and will not be repeated here.
[0063] In this embodiment, the database table optimization device is presented in the form of a functional unit. Here, a unit refers to an ASIC (Application Specific Integrated Circuit) circuit, a processor and memory that execute one or more software or fixed programs, and / or other devices that can provide the above functions.
[0064] This invention also provides a computer device having the above-described features. Figure 2 The database table optimization device shown.
[0065] Please see Figure 3 , Figure 3 This is a schematic diagram of the structure of a computer device provided in an optional embodiment of the present invention, such as... Figure 3 As shown, the computer device includes one or more processors 10, memory 20, and interfaces for connecting the components, including high-speed interfaces and low-speed interfaces. The components communicate with each other via different buses and can be mounted on a common motherboard or otherwise installed as needed. The processors can process instructions executed within the computer device, including instructions stored in or on memory to display graphical information of a GUI on external input / output devices (such as display devices coupled to the interfaces). In some alternative implementations, multiple processors and / or multiple buses can be used with multiple memories and multiple memory modules, if desired. Similarly, multiple computer devices can be connected, each providing some of the necessary operations (e.g., as a server array, a group of blade servers, or a multiprocessor system). Figure 3 Take a processor 10 as an example.
[0066] Processor 10 may be a central processing unit, a network processor, or a combination thereof. Processor 10 may further include a hardware chip. The hardware chip may be an application-specific integrated circuit (ASIC), a programmable logic device (PLD), or a combination thereof. The programmable logic device may be a complex programmable logic device (CAMP), a field-programmable gate array (FPGA), a general-purpose array logic (GDA), or any combination thereof.
[0067] The memory 20 stores instructions executable by at least one processor 10 to cause the at least one processor 10 to perform the method shown in the above embodiments.
[0068] The memory 20 may include a program storage area and a data storage area. The program storage area may store the operating system and applications required for at least one function; the data storage area may store data created based on the use of the computer device. Furthermore, the memory 20 may include high-speed random access memory and may also include non-transitory memory, such as at least one disk storage device, flash memory device, or other non-transitory solid-state storage device. In some alternative embodiments, the memory 20 may optionally include memory remotely located relative to the processor 10, and these remote memories may be connected to the computer device via a network. Examples of such networks include, but are not limited to, the Internet, intranets, local area networks, mobile communication networks, and combinations thereof.
[0069] The memory 20 may include volatile memory, such as random access memory; the memory may also include non-volatile memory, such as flash memory, hard disk or solid-state drive; the memory 20 may also include a combination of the above types of memory.
[0070] The computer device also includes a communication interface 30 for communicating with other devices or communication networks.
[0071] This invention also provides a computer-readable storage medium. The methods described above according to embodiments of the invention can be implemented in hardware or firmware, or implemented as computer code that can be recorded on a storage medium, or implemented as computer code downloaded via a network and originally stored on a remote storage medium or a non-transitory machine-readable storage medium and then stored on a local storage medium. Thus, the methods described herein can be processed by software stored on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. The storage medium can be a magnetic disk, optical disk, read-only memory, random access memory, flash memory, hard disk, or solid-state drive, etc.; further, the storage medium can also include combinations of the above types of memory. It is understood that computers, processors, microprocessor controllers, or programmable hardware include storage components capable of storing or receiving software or computer code, which, when accessed and executed by the computer, processor, or hardware, implements the methods shown in the above embodiments.
[0072] In the specific implementation of the above embodiments, the technical features can be combined in any non-contradictory way. For the sake of brevity, not all possible combinations of the above technical features are described. However, as long as the combination of these technical features is not contradictory, it should be considered to be within the scope of this specification.
[0073] The specific embodiments described above are merely illustrative of several implementations of the present invention, and while the descriptions are detailed, they should not be construed as limiting the scope of the present invention. It should be noted that those skilled in the art can make various modifications and improvements without departing from the concept of the present invention, and these modifications and improvements all fall within the scope of protection of the present invention. Therefore, the scope of protection of this patent should be determined by the appended claims.
Claims
1. A method for optimizing database tables, characterized in that, include: Extract performance-impacting query statements from the query logs of the original database tables; The performance impact query statement is parsed to determine the target query field and the target time range, wherein the frequency of occurrence of the target query field is greater than a preset frequency threshold; Based on the target query fields, target time range, and original database table, a target database table is generated.
2. The database table optimization method according to claim 1, characterized in that, The extraction of performance-impacting query statements from the query logs of the original database tables includes: Query statements in the query log whose execution time is greater than or equal to the preset execution time are identified as performance-impacting query statements.
3. The database table optimization method according to claim 2, characterized in that, The extraction of performance-impacting query statements from the query logs of the original database tables also includes: Query statements in the query logs of the original database tables that failed due to exceeding memory limits were identified as performance-impacting queries.
4. The database table optimization method according to claim 1, characterized in that, The process of parsing the performance impact query statement to determine the target query field and target time range includes: The performance impact query statement is parsed to determine the query execution plan; Extract the filter criteria fields and target time range from the query execution plan; Select the filter fields that appear more frequently than a preset frequency threshold as the target query fields.
5. The database table optimization method according to claim 1, characterized in that, The step of generating the target database table based on the target query field, the target time range, and the original database table includes: Based on the target query field and the target time range, a first database table is generated. The first database table includes one or more time partitions, which are determined based on the target time range. Import the data from the original database table into the first database table to generate the target database table.
6. The database table optimization method according to claim 5, characterized in that, After importing data from the original database table into the first database table to generate the target database table, the method further includes: The target database table and the original database table are compared; If the comparison results are consistent, then the target database table is confirmed to be correct.
7. The database table optimization method according to claim 1, characterized in that, After generating the target database table based on the target query field, the target time range, and the original database table, the method further includes: Test the query performance of the target database tables; If the performance metrics of the target database table meet the preset conditions, then the target database table is determined as the database table to be queried, and the performance metrics include at least response time.
8. A database table optimization device, characterized in that, The device includes: The statement extraction module is used to extract performance-impacting query statements from the query logs of the original database tables; The statement parsing module is used to parse the performance impact query statement, determine the target query field and the target time range, wherein the frequency of occurrence of the target query field is greater than a preset frequency threshold; The database table generation module is used to generate a target database table based on the target query field, the target time range, and the original database table.
9. A computer device, characterized in that, include: A memory and a processor are communicatively connected, the memory storing computer instructions, and the processor executing the computer instructions to perform the database table optimization method according to any one of claims 1 to 7.
10. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions for causing a computer to perform the method for optimizing a database table as described in any one of claims 1 to 7.