Query optimization method and system based on algorithm engine

Abstract

The invention relates to the technical field of data processing, in particular to a query optimization method and system based on an algorithm engine, and the method comprises the steps: firstly receiving an initial logic analysis instruction aiming at a target data storage cluster, then traversing all query columns of the initial logic analysis instruction to extract a trend query column containing a trend analysis function, and then, carrying out query optimization on the trend query column containing the trend analysis function; and performing logic rewriting on the trend query column by adopting a preset rewriter to obtain a query rewriting instruction containing multi-table association, and finally submitting the query rewriting instruction to a pre-configured database engine to return a query result related to trend analysis. Compared with the prior art, the method has the advantages that the content of the multi-associated data does not need to be inquired manually and independently, the possibility of memory overflow of a system end caused by multi-manual operation is reduced, the possibility of field repetition is also reduced, and therefore the method has higher stability and lower resource consumption.

Description

Technical Field

[0001] This invention relates to the field of data processing technology. More specifically, this invention relates to a query optimization method and system based on an algorithm engine. Background Technology

[0002] In big data analytics scenarios, cross-period trend analysis (such as daily and weekly comparisons) is a core tool for users to deeply understand business growth. It directly impacts business strategies and is crucial for the smooth operation of user business processes. However, implementing cross-period trend analysis primarily relies on operations personnel directly pulling and calculating the full, interconnected data at the application layer. In a distributed cluster environment handling massive amounts of data, the following drawbacks exist:

[0003] When the application layer directly initiates query requests, massive amounts of data need to be frequently transmitted across the network. This not only consumes a large amount of network bandwidth, causing network congestion, but may also lead to memory overflow due to excessive data volume, affecting system stability. Furthermore, in multi-table join queries, different tables often have many fields with the same name, making manual field differentiation and filtering highly prone to errors. This naming conflict makes it difficult for the database parser to accurately identify the source of the fields, preventing the generation of an effective physical execution plan, and consequently causing query commands to fail to be issued and executed correctly. These problems not only reduce query efficiency but may also lead to serious consequences such as data inconsistency or system crashes.

[0004] Therefore, how to solve the problems of poor execution stability and excessive system resource consumption faced by complex analysis and query tasks in a distributed environment is a technical challenge that urgently needs to be addressed. Summary of the Invention

[0005] To address the aforementioned technical problems, this invention discloses a query optimization method and system based on an algorithm engine.

[0006] In a first aspect, this invention discloses a query optimization method based on an algorithm engine, comprising: Receive initial logical analysis instructions for the target data storage cluster; Iterate through all query columns of the initial logical analysis instruction to extract the trend query columns that contain the trend analysis function; The trend query column is logically rewritten using a preset rewriter to obtain a query rewrite instruction that includes multi-table joins. Submit the query rewrite command to the pre-configured database engine to return query results related to trend analysis.

[0007] Preferably, a preset rewriter is used to logically rewrite the trend query column, resulting in a query rewrite instruction that includes multi-table joins, including: Based on the time query labels in the trend query column, construct a first data subquery to retrieve data for the first date, and a previous data subquery to retrieve data for comparison dates; Construct a left join structure between the first data subquery and the previous data subquery; Based on the definition of the trend analysis function, an arithmetic calculation expression is generated; wherein, the arithmetic calculation expression uses the output columns of the first data subquery and the output columns of the previous period data subquery for calculation. By combining the left join structure and the arithmetic calculation expression, a query rewrite instruction is obtained.

[0008] Preferably, the steps for constructing a subquery of past data are as follows: Parse the first-time filtering condition in the initial logic analysis instruction; Based on the time granularity specified by the trend analysis function, the time value of the first time filtering condition is pre-offset calculated to obtain the pre-time filtering condition. Replace the filter conditions in the subquery of past data with the filter conditions of previous time; In the join condition of the left join structure, the time column of the first data subquery is aligned with the time column of the previous data subquery using a time offset function.

[0009] Preferably, when the trend analysis function is a ratio calculation type, the generated arithmetic expression includes conditional judgment logic: Determine if the output column of the subquery containing the historical data used as a divisor is zero; If so, return the default value. If not, then perform a division operation.

[0010] Preferably, the logical rewriting process also includes a field conflict disambiguation step: Check if the first data subquery and the previous data subquery have fields with the same name; If so, assign separate table aliases to the first data subquery and the previous data subquery; Modify the access path of the same-name referenced field in the query rewrite command to a fully qualified format that includes the table alias.

[0011] Preferably, before generating the query rewrite instruction, the method of the present invention further includes: Retrieve candidate physics tables; Extract column coverage conditions and timing conditions from the initial logic analysis instructions; Filter out candidate physical tables that meet the column coverage and time conditions; From the selected candidate physical tables, choose the one with the smallest number of rows as the data source for the query.

[0012] Preferably, the rewriter includes a trend function rewriter and a basic rewriter; The trend function rewriter is used to identify trend query columns and convert them into query rewrite instructions; The basic rewriter is used to perform division by zero protection and field disambiguation on query rewrite commands.

[0013] Preferably, if the initial logic analysis instruction also includes an aggregation grouping function, the summary rewriter is started; The summary rewriter is configured as follows: The query containing grouping and aggregation logic is encapsulated as a subquery, and the output of the subquery is subjected to secondary aggregation calculation.

[0014] Preferably, the trend analysis functions include the day-on-day growth rate function and the day-on-day growth value function.

[0015] Secondly, the present invention discloses a query optimization system based on an algorithm engine, including a processor and a memory, wherein the memory stores computer program instructions, and when the computer program instructions are executed by the processor, the query optimization method based on the algorithm engine described in the first aspect is implemented.

[0016] The beneficial effects of this invention are as follows: When users need to perform cross-period trend analysis, there is no need to separately query related data and then integrate it. Instead, after issuing the initial logical analysis command, the initial logical analysis command is automatically rewritten by the rewriter, and a query rewrite command is used to instruct the database engine to execute the query task, automatically returning the query results related to the trend analysis. Compared with existing technologies, the method of this invention does not require manual separate querying of multiple related data contents, reducing the possibility of system memory overflow caused by multiple manual operations, and also reducing the possibility of field duplication. Therefore, the method of this invention has stronger stability and consumes less resources. Attached Figure Description

[0017] The above and other objects, features, and advantages of exemplary embodiments of the present invention will become readily apparent upon reading the following detailed description with reference to the accompanying drawings. In the drawings, several embodiments of the invention are illustrated by way of example and not limitation, and like or corresponding reference numerals denote like or corresponding parts, wherein: Figure 1 This is a flowchart of the query optimization method based on an algorithm engine in Embodiment 1 of the present invention; Figure 2 This is a schematic diagram of the structure of the query optimization system based on the algorithm engine in Embodiment 2 of the present invention. Detailed Implementation

[0018] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some, not all, of the embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0019] The specific embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0020] Example 1 like Figure 1 As shown, this embodiment discloses a query optimization method based on an algorithm engine, including: S10: Receive the initial logical analysis command for the target data storage cluster.

[0021] In this embodiment, the method is applicable to high-concurrency query scenarios. The aforementioned target data storage cluster refers to the underlying system that actually stores the data and is responsible for executing the final SQL query. The initial logical analysis instruction refers to a user query instruction that includes trend analysis and aggregation analysis requirements. It can be natural language, for example: querying the month-on-month growth rate, month-on-month growth value, and summary data of product A from November 10th to November 15th, 2024. Alternatively, it can be a query operation instruction automatically generated after configuring the rule options in the system interface.

[0022] It should be explained that the initial logical analysis instructions generated from natural language need to be reconstructed using a large language model and a pre-defined query rule structure to achieve compatibility with the query data.

[0023] For the same query task, the initial logic analysis instructions generated in the two ways will be output as multiple query columns after structured processing. For example, the following provides the initial logic analysis instructions obtained based on a preset structure template: SELECT create_date, dod_rate(SUM(order_count)) AS order_growth_rate, dod_val(SUM(revenue)) AS revenue_growth_value, SUM(order_count) AS current_order_count, SUM(revenue) AS current_revenue FROM sales_subject WHERE create_date>= '2024-11-10_00' AND create_date<= '2024-11-15_23' GROUP BY create_date ORDER BY create_date DESC LIMIT 10 The format of the initial logic analysis instruction described above is only an illustrative example. In actual processing, the structure template can be expanded with additional columns according to actual needs.

[0024] S20: Traverse all query columns of the initial logic analysis instruction to extract the trend query column containing the trend analysis function.

[0025] In step S20, the aforementioned trend analysis functions mainly include the day-on-day growth rate function and the day-on-day growth value function. Specifically, in the instruction definition, dod_rate represents the day-on-day growth rate function, while dod_val represents the day-on-day growth value function.

[0026] Then, in order to provide a reliable data source for the subsequent step S30, the method of this embodiment further includes the following after step S20 is executed: S200: Obtain candidate physics tables.

[0027] It should be explained that the above candidate physical tables are obtained from the cache. This refers to the data table containing the user's query content, which can be pre-stored according to the actual situation.

[0028] S201: Extract column coverage conditions and timing conditions from the initial logic analysis instructions.

[0029] S202: Filter out candidate physical tables that meet the column coverage and time conditions.

[0030] Specifically, the column coverage condition requires that the number of columns in the candidate physical table be greater than the column threshold, while the time condition requires that the time interval of the candidate material table covers the time query label.

[0031] S203: From the selected candidate physical tables, select the candidate physical table with the smallest number of data rows as the data source for the query.

[0032] Steps S200-S203 above are essentially a multi-dimensional table selection algorithm. The data source provided by the above scheme can ensure a strong correlation between the data source and the content to be queried, while also controlling the number of data rows to be called to a minimum, thereby reducing the total amount of data processing, reducing the data processing load, and improving data query efficiency.

[0033] S30: Use a preset rewriter to logically rewrite the trend query column to obtain a query rewrite instruction that includes multi-table joins.

[0034] In this embodiment, the rewriter includes a trend function rewriter, a basic rewriter, and a summary rewriter. The trend function rewriter identifies the trend query column and converts it into a query rewrite instruction. The basic rewriter performs division-by-zero protection and field disambiguation on the query rewrite instruction. The summary rewriter encapsulates queries containing grouping and aggregation logic as subqueries and performs secondary aggregation calculations on the subquery's output.

[0035] Specifically, for the trend function rewriter, step S30 above includes: S31: Based on the time query label of the trend query column, construct a first data subquery to retrieve the first date data, and a previous data subquery to retrieve the comparison date data.

[0036] It should be explained that the above time query labels include the query start time and query end time. The query start time corresponds to the first date, while the query end time corresponds to the comparison date. Step S31 above is used to generate subquery instructions concurrently.

[0037] S32: Construct a left join structure between the first data subquery and the previous data subquery.

[0038] It's important to explain that the data in the database is stored vertically by time. For example, number 1 is in row 1 and number 2 is in row 2. To calculate the "month-on-month comparison," comparing number 1 and number 2, without a left join operation, it's difficult for the database to directly subtract row 2 from row 1. This is because the database would have to maintain a huge cache window in memory or sort the entire table by time. In high-concurrency, big data processing scenarios, this can lead to Out of Memory (OOM) errors or extremely slow disk swapping. However, through step S32, the "first data subquery" and the "past data subquery" can be concatenated into the same row, eliminating the need for sorting. The database operation problem changes from "inter-row calculation" to a simple "intra-row calculation." The CPU only needs to read two values ​​from the same memory address range within a single register cycle to perform the operation, achieving nanosecond-level efficiency.

[0039] Furthermore, the steps for constructing the subquery of past data described above are as follows: S321: Parse the first-time filtering condition in the initial logic analysis instruction.

[0040] S322: Based on the time granularity specified by the trend analysis function, perform a forward offset calculation on the time value of the first time filtering condition to obtain the forward time filtering condition.

[0041] S323: Replace the filter conditions in the past data subquery with the previous time filter conditions.

[0042] S324: In the join condition of the left join structure, configure the time column of the current data subquery and the time column of the previous data subquery to be aligned using a time offset function.

[0043] Steps S321-S333 described above exponentially reduce I / O overhead, transforming the traditional "full table scan" method into a "single partition scan" method. It's important to explain that in traditional techniques, database engines, for security reasons, initiate a full table scan, i.e., traversing and scanning the entire database before searching for data for the corresponding date. This undoubtedly increases the system's data query load and reduces query efficiency. The method described above, however, calculates a "previous time filtering condition" and hard-codes it directly into the WHERE clause of the past data subquery, thereby triggering a branch pruning mechanism in the underlying database storage engine. When the database engine receives the aforementioned past data subquery, it skips irrelevant data and only opens the partition file for the corresponding date. Step S334 essentially instructs the database to move the past data to the same row as the first data, supporting the execution of step S32.

[0044] S33: Generate an arithmetic calculation expression based on the definition of the trend analysis function.

[0045] The arithmetic calculation expression uses the output columns of the first data subquery and the output columns of the previous period data subquery for calculation. More specifically, the above arithmetic calculation expression includes:

[0046] In the formula, Indicates the month-on-month growth rate. Indicates the first date Data, Data indicating comparison dates, express and Find the absolute value of the difference.

[0047] For the month-on-month growth value, by calculation You can obtain it by doing so.

[0048] It should be noted that when the trend analysis function is a ratio calculation type, the generated arithmetic expression includes conditional judgment logic: S331: Determine if the output column of the subquery containing the historical data used as a divisor is zero; S332: If yes, return the default value. S333: If not, then perform the division operation.

[0049] It needs further explanation that when calculating the growth rate, if the denominator is missing (NULL) or zero, the underlying calculation unit will directly trigger an arithmetic exception interruption, causing the entire distributed computing task to fail cascaded, severely impacting the cluster's availability. Through the solutions introduced in steps S331-S333 above, the method in this embodiment possesses a "division-by-zero error" elimination function, exhibiting strong robustness and reducing the likelihood of cascading exceptions triggered in high-concurrency systems. Regarding the precision control of the calculation results, the results are retained to 5 decimal places; values ​​exceeding 5 decimal places are rounded.

[0050] S34: Combine the left join structure and the arithmetic calculation expression to obtain the query rewrite instruction.

[0051] It should be explained that after the left join structure of the script layer is formed, the above arithmetic calculation expression needs to be configured in the query rewrite instruction in the form of a script.

[0052] Furthermore, during the logic rewriting process, the method in this embodiment also includes a field conflict disambiguation step: S300: Check if there are fields with the same name in the first data subquery and the previous data subquery.

[0053] S301: If so, assign separate table aliases to the first data subquery and the previous data subquery.

[0054] S302: Modify the access path of the same-name referenced field in the query rewrite instruction to a fully qualified format that includes the table alias.

[0055] Through steps S300-S302 above, the method in this embodiment eliminates field ambiguity in multi-table join scenarios, ensuring that the automatically rewritten instructions can be accurately recognized and executed by the database engine. In self-join queries, the two tables involved in the calculation have completely identical structures and will inevitably have the same field names (e.g., both have a "sales amount" field). If the rewritten instruction directly references this field, the database parser will be unable to determine whether it comes from the "first data table" or the "past data table," thus directly throwing a "field ambiguity" exception and terminating the calculation task. The above solution, by detecting potential naming conflicts, forces the allocation of independent table aliases for the two parts of data (e.g., renaming the two tables to "Table A" and "Table B" respectively), and modifies the access path of all fields to the precise format of "table alias plus field name" (e.g., explicitly specifying "sales amount of Table A" or "sales amount of Table B"). This mechanism not only fundamentally avoids system parsing crashes caused by field conflicts, but also provides a clear data addressing path for subsequent calculations, enabling the calculation engine to accurately distinguish and load data blocks from different time periods. It is a key fault-tolerant design to ensure the stable operation of the automated query rewrite system.

[0056] It is important to note that the trend function rewriter has the highest execution level. Subsequent rewrites, including the basic rewriter and the summary rewriter, must be executed after the DOD trend rewrite.

[0057] S40: Submit the query rewrite instruction to the pre-configured database engine to return query results related to trend analysis.

[0058] Through steps S10-S40 above, this invention introduces a rewriter mechanism during cross-cycle trend analysis. This mechanism automatically optimizes initial logical analysis instructions, transforming them into efficient query rewriting instructions that directly drive the database engine to execute. This method completely eliminates the cumbersome process of traditional manual querying and data integration, significantly improving analysis efficiency. The rewriter can intelligently identify cross-cycle data association needs and automatically generate the optimal query path, avoiding common errors in multi-table join queries encountered in manual operations. Simultaneously, this mechanism effectively reduces redundant data transmission, lowers the risk of system memory overflow, and ensures the stability of the query process. Furthermore, through automated field filtering and deduplication, naming conflicts caused by manual intervention are eliminated, further optimizing resource utilization efficiency.

[0059] Example 2 like Figure 2 As shown, this embodiment also discloses a query optimization system based on an algorithm engine, including a processor and a memory. The memory stores computer program instructions, which, when executed by the processor, implement the query optimization method based on an algorithm engine described in the first aspect.

[0060] The system in this embodiment also includes other components well known to those skilled in the art, such as communication interfaces. Their settings and functions are known in the art, and therefore will not be described in detail here.

[0061] In this invention, the aforementioned memory can be any tangible medium containing or storing a program that can be used or combined with an instruction execution system, apparatus, or device. For example, a computer-readable storage medium can be any suitable magnetic or magneto-optical storage medium, such as Resistive Random Access Memory (RRAM), Dynamic Random Access Memory (DRAM), Static Random Access Memory (SRAM), Enhanced Dynamic Random Access Memory (EDRAM), High-Bandwidth Memory (HBM), Hybrid Memory Cube (HMC), etc., or any other medium that can be used to store desired information and can be accessed by an application, module, or both. Any such computer storage medium can be part of a device or accessible to or connected to a device. Any application or module described in this invention can be implemented using computer-readable / executable instructions that can be stored or otherwise maintained by such a computer-readable medium.

[0062] In the description of this specification, "multiple" means at least two, such as two, three or more, etc., unless otherwise expressly and specifically defined.

[0063] While this specification has shown and described numerous embodiments of the invention, it will be apparent to those skilled in the art that such embodiments are provided by way of example only. Many modifications, alterations, and alternatives will occur to those skilled in the art without departing from the spirit and essence of the invention. It should be understood that various alternatives to the embodiments of the invention described herein may be employed in the practice of this invention.

Claims

1. A query optimization method based on an algorithm engine, characterized in that, include: Receive initial logical analysis instructions for the target data storage cluster; Iterate through all the query columns of the initial logic analysis instruction to extract the trend query columns containing the trend analysis function; The trend query column is logically rewritten using a preset rewriter to obtain a query rewrite instruction that includes multi-table joins. The query rewrite instruction is submitted to the pre-configured database engine to return query results related to trend analysis.

2. The query optimization method based on an algorithm engine according to claim 1, characterized in that, The trend query column is logically rewritten using a preset rewriter to obtain a query rewrite instruction that includes multi-table joins, including: Based on the time query labels of the trend query column, construct a first data subquery for obtaining data for the first date, and a past data subquery for obtaining data for the comparison date; Construct a left join structure between the first data subquery and the previous data subquery; Based on the definition of the trend analysis function, an arithmetic calculation expression is generated; wherein, the arithmetic calculation expression refers to the output column of the first data subquery and the output column of the past data subquery for calculation; The left join structure and the arithmetic expression are combined to obtain the query rewrite instruction.

3. The query optimization method based on an algorithm engine according to claim 2, characterized in that, The steps for constructing the aforementioned subquery of past data are as follows: Parse the first-time filtering condition in the initial logic analysis instruction; Based on the time granularity specified by the trend analysis function, the time value of the first time filtering condition is pre-offset calculated to obtain the pre-time filtering condition. Replace the filtering conditions in the previous data subquery with the previous time filtering conditions; In the connection condition of the left join structure, the time column of the first data subquery is configured to be aligned with the time column of the previous data subquery using a time offset function.

4. The query optimization method based on an algorithm engine according to claim 2, characterized in that, When the trend analysis function is a ratio calculation type, the generated arithmetic expression includes conditional judgment logic: Determine whether the output column of the past data subquery, which is used as the divisor, is zero; If so, return the default value. If not, then perform a division operation.

5. The query optimization method based on an algorithm engine according to claim 2, characterized in that, The logical rewriting process also includes a field conflict disambiguation step: Detect whether the first data subquery and the previous data subquery have the same referenced field; If so, assign independent table aliases to the first data subquery and the previous data subquery respectively; Modify the access path of the referenced field with the same name in the query rewrite instruction to a fully qualified format that includes the table alias.

6. The query optimization method based on an algorithm engine according to claim 1, characterized in that, Before generating the query rewrite instruction, the method further includes: Retrieve candidate physics tables; Extract column coverage conditions and time conditions from the initial logic analysis instructions; Filter out candidate physical tables that meet the column coverage condition and the time condition; From the selected candidate physical tables, choose the one with the smallest number of rows as the data source for the query.

7. The query optimization method based on an algorithm engine according to claim 1, characterized in that, The rewriter includes a trend function rewriter and a basic rewriter; The trend function rewriter is used to identify the trend query column and convert it into a query rewrite instruction; The basic rewriter is used to perform division by zero protection and field disambiguation on the query rewrite instruction.

8. The query optimization method based on an algorithm engine according to claim 1, characterized in that, If the initial logic analysis instruction also includes an aggregation grouping function, start the summary rewriter; The aggregation rewriter is configured as follows: The query containing grouping and aggregation logic is encapsulated as a subquery, and the output of the subquery is subjected to a second aggregation calculation.

9. The query optimization method based on an algorithm engine according to claim 1, characterized in that, The trend analysis functions include the day-on-day growth rate function and the day-on-day growth value function.

10. A query optimization system based on an algorithm engine, characterized in that, It includes a processor and a memory, wherein the memory stores computer program instructions, and when the computer program instructions are executed by the processor, the query optimization method based on the algorithm engine as described in any one of claims 1-9 is implemented.

Images