A relational data query optimization method and device

Abstract

The application provides a relational data query optimization method and device. The method comprises the following steps: determining a dynamic analysis feature syntax tree of relational data; determining an optimal cost of an execution action corresponding to an execution plan based on dynamic performance data in the dynamic analysis feature syntax tree and a corresponding weight score, and obtaining the optimal cost of the execution plan; wherein the execution plan is an execution process or an access path of a structured query statement in a database, and the execution plan comprises at least one execution action; judging whether a logical execution plan of the structured query statement is optimal based on the optimal cost of the execution plan; if not, optimizing a target structured query statement corresponding to a non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree. The method of the application determines the structured query statement that needs to be optimized based on the cost difference, thereby realizing targeted optimization and improving the accuracy and efficiency of query data optimization.

Description

Technical Field

[0001] This invention relates to the field of computer application technology, specifically to a method and apparatus for optimizing queries of relational data. It also relates to an electronic device and a processor-readable storage medium. Background Technology

[0002] In recent years, with the rapid development of internet technology, more and more applications are based on databases. During database operation, it is often necessary to optimize the SQL (Structured Query Language) statements executed by the database to avoid inefficient structured queries from entering the system, which could reduce database performance and stability.

[0003] Structured Query Language (SCL) is a database query and programming language used to access, query, update, and manage data stored in relational database systems. SCL allows users to work on high-level data structures. It does not require users to specify data storage methods or understand the specific data storage format, so different database systems with completely different underlying structures can use the same SCL as an interface for data input and management. Furthermore, SCL statements can be nested, giving it great flexibility and powerful functionality. However, existing optimization methods for structured query statements are relatively simple and lack specificity, resulting in low stability and efficiency in query data optimization. Therefore, designing a stable and efficient query data optimization scheme has become an urgent problem to be solved. Summary of the Invention

[0004] To address this issue, the present invention provides a method and apparatus for optimizing relational data queries, thereby solving the problem that existing data query optimization solutions are poorly targeted, leading to reduced optimization efficiency and accuracy of query data.

[0005] In a first aspect, the present invention provides a query optimization method for relational data, comprising:

[0006] Determine the dynamic analytical feature syntax tree for relational data;

[0007] Based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, the optimal cost corresponding to the execution action in the execution plan is determined, and the optimal cost of the execution plan is obtained; wherein, the execution plan is the execution process or access path of the structured query statement in the database, and the execution plan contains at least one of the execution actions;

[0008] Based on the optimal cost of the execution plan, determine whether the logical execution plan of the structured query statement is optimal; if not, optimize the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree.

[0009] In one embodiment, determining the dynamic analysis feature syntax tree of relational data specifically includes:

[0010] Obtain the structured query statement used to query the target database;

[0011] The structured query statement is analyzed to determine the initial dynamic analysis feature syntax tree; wherein the structured query statement is relational data;

[0012] Extract dynamic performance data corresponding to the target database; the dynamic performance data includes dynamic statistical data and a dynamic data dictionary; wherein, the dynamic statistical data includes the discrimination index, average row length, and data volume corresponding to the target database;

[0013] Based on the dynamic performance data and the initial dynamic analysis feature syntax tree, a dynamic analysis feature syntax tree for the relational data is constructed.

[0014] In one embodiment, constructing a dynamic analysis feature syntax tree for the relational data based on the dynamic performance data and the initial dynamic analysis feature syntax tree specifically includes:

[0015] The dynamic performance data and the corresponding weight scores are attached to the leaf nodes of the initial dynamic analysis feature syntax tree as attributes of the leaf nodes, thus obtaining the dynamic analysis feature syntax tree of relational data; wherein, the weight scores are the weight scores corresponding to each dynamic data in the dynamic performance data.

[0016] In one embodiment, the query optimization method for relational data further includes:

[0017] Obtain the logical execution plan for the initial structured query statement and determine the cost of the logical execution plan;

[0018] Determine whether the cost of the logical execution plan is higher than the optimal cost of the execution plan. If so, determine the execution action in the logical execution plan whose cost is higher than the optimal cost, and take the execution statement corresponding to the execution action as the structured query statement to be optimized in the initial structured query statement. Optimize the execution statement to be optimized based on the dynamic performance data contained in the dynamic analysis feature syntax tree and the corresponding weight score; wherein, the execution statement is the structured query statement to be executed.

[0019] In one embodiment, the relational data query optimization method further includes: after optimizing the execution statement to be optimized, determining and storing the audit rules corresponding to the execution action based on the optimized execution statement corresponding to the execution action; wherein the audit rules are used to audit and optimize subsequent target structured query statements.

[0020] In one embodiment, based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, the optimal cost corresponding to the execution action in the execution plan is determined, and the optimal cost of the execution plan is obtained, specifically including:

[0021] Based on the weight scores of each dynamic data corresponding to the leaf nodes in the dynamic analysis feature syntax tree, the dynamic data included in each execution action in the execution plan are weighted according to the weight scores to determine the optimal cost corresponding to each execution action, and the optimal costs of each execution action are added together to obtain the optimal cost of the execution plan.

[0022] In one embodiment, based on the dynamic analysis feature syntax tree, the target structured query statement corresponding to the non-optimal execution action in the logical execution plan is optimized, specifically including:

[0023] Based on the dynamic data in the dynamic analysis feature syntax tree, the target structured query statement is optimized, and the actual cost corresponding to the optimized statement is determined until the difference between the actual cost corresponding to the optimized statement and the optimal cost of the execution plan is less than a preset threshold; the optimized statement is the optimized execution statement.

[0024] Secondly, the present invention also provides a query optimization device for relational data, comprising:

[0025] Syntax tree determination unit, used to determine the dynamic analysis feature syntax tree of relational data;

[0026] An execution plan cost estimation unit is used to determine the optimal cost corresponding to the execution action in the execution plan based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, and to obtain the optimal cost of the execution plan; wherein, the execution plan is the execution process or access path of a structured query statement in the database, and the execution plan contains at least one of the execution actions;

[0027] The query data optimization unit is used to determine whether the logical execution plan of the structured query statement is optimal based on the optimal cost of the execution plan; if not, it optimizes the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree.

[0028] In one embodiment, the syntax tree determination unit is specifically used for:

[0029] Obtain the structured query statement used to query the target database;

[0030] The structured query statement is analyzed to determine the initial dynamic analysis feature syntax tree; wherein the structured query statement is relational data;

[0031] Extract dynamic performance data corresponding to the target database; the dynamic performance data includes dynamic statistical data and a dynamic data dictionary; wherein, the dynamic statistical data includes the discrimination index, average row length, and data volume corresponding to the target database;

[0032] Based on the dynamic performance data and the initial dynamic analysis feature syntax tree, a dynamic analysis feature syntax tree for the relational data is constructed.

[0033] In one embodiment, constructing a dynamic analysis feature syntax tree for the relational data based on the dynamic performance data and the initial dynamic analysis feature syntax tree specifically includes:

[0034] The dynamic performance data and the corresponding weight scores are attached to the leaf nodes of the initial dynamic analysis feature syntax tree as attributes of the leaf nodes, thus obtaining the dynamic analysis feature syntax tree of relational data; wherein, the weight scores are the weight scores corresponding to each dynamic data in the dynamic performance data.

[0035] In one embodiment, the relational data query optimization device further includes:

[0036] A logical execution plan cost determination unit is used to obtain the logical execution plan of the initial structured query statement and determine the cost of the logical execution plan;

[0037] The query data optimization unit is further configured to determine whether the cost of the logical execution plan is higher than the optimal cost of the execution plan. If so, it determines the execution action in the logical execution plan whose cost is higher than the optimal cost, takes the execution statement corresponding to the execution action as the structured query statement to be optimized in the initial structured query statement, and optimizes the execution statement to be optimized based on the dynamic performance data contained in the dynamic analysis feature syntax tree and the corresponding weight score; the execution statement is the structured query statement to be executed.

[0038] In one embodiment, the relational data query optimization device further includes: an audit rule determination unit, configured to determine and store audit rules corresponding to the execution action based on the optimized execution statement corresponding to the execution action after optimizing the execution statement to be optimized; wherein the audit rules are used to audit and optimize subsequent target structured query statements.

[0039] In one embodiment, the execution plan cost estimation unit is specifically used for:

[0040] Based on the weight scores of each dynamic data corresponding to the leaf nodes in the dynamic analysis feature syntax tree, the dynamic data included in each execution action in the execution plan are weighted according to the weight scores to determine the optimal cost corresponding to each execution action, and the optimal costs of each execution action are added together to obtain the optimal cost of the execution plan.

[0041] In one embodiment, the query data optimization unit is specifically used for:

[0042] Based on the dynamic data in the dynamic analysis feature syntax tree, the target structured query statement is optimized, and the actual cost corresponding to the optimized statement is determined until the difference between the actual cost corresponding to the optimized statement and the optimal cost of the execution plan is less than a preset threshold; the optimized statement is the optimized execution statement.

[0043] Thirdly, the present invention also provides an electronic device, comprising: a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor executes the program to implement the steps of the relational data query optimization method as described in any of the preceding claims.

[0044] Fourthly, the present invention also provides a processor-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the relational data query optimization method as described in any of the preceding claims.

[0045] The query optimization method for relational data described in this invention constructs a dynamic analysis feature syntax tree by combining dynamic performance data. Based on this dynamic analysis feature syntax tree, the corresponding execution plan and the cost of the logical execution plan of the target structured query statement are determined. Based on the cost difference, it is determined whether to optimize the structured query statement. That is, based on the optimal cost, it is determined whether the logical execution plan is optimal. For the structured query statements corresponding to the non-optimal execution actions, targeted optimization is performed based on the dynamic analysis feature syntax tree. This provides an effective basis, improves the quality of application development, reduces the cost of manual review, achieves targeted optimization, and improves the accuracy and efficiency of query data optimization. Attached Figure Description

[0046] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the 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 based on these drawings without creative effort.

[0047] Figure 1 A flowchart illustrating the query optimization method for relational data provided in this embodiment of the invention;

[0048] Figure 2 A schematic diagram illustrating the process of feature expansion in dynamic analysis syntax tree provided in an embodiment of the present invention;

[0049] Figure 3 This is a flowchart illustrating the reverse recommendation rules based on dynamic analysis syntax tree provided in an embodiment of the present invention.

[0050] Figure 4 This is a schematic diagram of the structure of the relational data query optimization device provided in an embodiment of the present invention;

[0051] Figure 5 This is a schematic diagram of the physical structure of an electronic device provided in an embodiment of the present invention. Detailed Implementation

[0052] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, 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 embodiments of the present invention, not all embodiments. 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.

[0053] The following describes in detail an embodiment of the query optimization method for relational data based on the present invention. For example... Figure 1 The diagram shown is a flowchart of a query optimization method for relational data provided in an embodiment of the present invention. The specific implementation process includes the following steps:

[0054] Step 101: Determine the dynamic analysis feature syntax tree for relational data.

[0055] In this embodiment of the invention, the process of determining the dynamic analysis feature syntax tree for relational data includes: first, obtaining a structured query statement, i.e., an SQL (Structured Query Language) query statement, to query the target database; then, analyzing the structured query statement to determine an initial dynamic analysis feature syntax tree; extracting corresponding dynamic performance data from the target database, and constructing the dynamic analysis feature syntax tree for the relational data based on the dynamic performance data and the initial dynamic analysis feature syntax tree. The dynamic analysis feature syntax tree is an abstract syntax tree (AST). The dynamic performance data specifically includes dynamic statistical data and a dynamic data dictionary, hence the term "dynamic data." The dynamic statistical data includes statistical information such as the discrimination index, average row length, and data volume corresponding to the target database, and the structured query statement is relational data.

[0056] Specifically, the process begins by analyzing the SQL queries used to access the target database to construct an initial dynamic analysis feature syntax tree. Then, dynamic statistical data such as discrimination, average row length, and data volume, as well as dynamic performance data like a dynamic data dictionary, are extracted from the target database. The extracted dynamic performance data and their corresponding weight scores are then attached to the leaf nodes of the initial dynamic analysis feature syntax tree to obtain the dynamic analysis feature syntax tree for relational data.

[0057] Therefore, the specific implementation process of constructing the dynamic analysis feature syntax tree for the relational data based on the dynamic performance data and the initial dynamic analysis feature syntax tree includes: firstly, attaching the dynamic performance data and the corresponding weight scores to the leaf nodes of the initial dynamic analysis feature syntax tree as attributes of the leaf nodes, thereby constructing the dynamic analysis feature syntax tree for the relational data. The weight scores are the weight scores corresponding to each dynamic data point in the dynamic performance data.

[0058] Step 102: Based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, determine the optimal cost corresponding to the execution action in the execution plan, and obtain the optimal cost of the execution plan.

[0059] In this step, firstly, based on the weight scores of each dynamic data corresponding to the leaf nodes in the dynamic analysis feature syntax tree, the dynamic data included in each execution action in the execution plan are weighted according to the weight scores to determine the optimal cost corresponding to each execution action. The optimal costs of each execution action are then summed to obtain the optimal cost of the execution plan. Here, the execution plan is the execution process or access path of a structured query statement in the database, and the execution plan contains at least one of the execution actions.

[0060] That is, based on the weight scores of each dynamic data corresponding to each leaf node in the dynamic analysis feature syntax tree and the dynamic data included in the action of the execution plan, the cost is weighted according to the weight scores, the optimal cost corresponding to each execution action is calculated, and the optimal costs of each execution action are added together as the optimal cost of the execution plan.

[0061] Step 103: Based on the optimal cost of the execution plan, determine whether the logical execution plan of the structured query statement is optimal; if not, optimize the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree.

[0062] Specifically, the target structured query statement is first optimized based on the dynamic data in the dynamic analysis feature syntax tree, and the actual cost corresponding to the optimized statement is determined until the difference between the actual cost corresponding to the optimized statement and the optimal cost of the execution plan is less than a preset threshold. The optimized statement is the optimized execution statement.

[0063] Furthermore, in the specific implementation process, the logical execution plan of the initial structured query statement can be obtained, and the cost of the logical execution plan can be determined. By comparing the cost of the logical execution plan with the optimal cost of the execution plan, it can be determined whether the cost of the logical execution plan is higher than the optimal cost. If so, the execution action corresponding to the higher cost in the logical execution plan is determined, and the execution statement corresponding to the execution action is taken as the structured query statement to be optimized in the initial structured query statement. The execution statement to be optimized is optimized based on the dynamic performance data and corresponding weight scores contained in the dynamic analysis feature syntax tree. After optimizing the execution statement to be optimized, the review rules corresponding to the execution action are determined and stored based on the optimized execution statement corresponding to the execution action. The review rules are used to review and optimize subsequent target structured query statements. The execution statement is the structured query statement to be executed.

[0064] The process of determining the cost of the logical execution plan includes: determining the cost of the logical execution plan according to a preset cost estimation model based on the weights of the dynamic data contained in the execution actions in the logical execution plan.

[0065] Specifically, the logical execution plan of the SQL query statement is obtained, and the cost of the logical execution plan is calculated based on the weight scores of the dynamic data included in each execution action in the logical execution plan. If the cost of the logical execution plan is higher than the optimal cost of the execution plan, the execution action corresponding to the higher cost in the logical execution plan is determined, and the execution statement corresponding to the execution action is taken as the structured query statement to be optimized in the initial structured query statement, i.e., the statement to be optimized or the execution statement to be optimized. The execution statement to be optimized is optimized based on the dynamic data in the syntax tree, and the cost corresponding to the optimized execution statement is calculated until the difference between the cost corresponding to the optimized statement and the optimal cost is less than a preset threshold. Furthermore, in order to facilitate the review and optimization of subsequent target structured query statements, in this embodiment of the invention, the review rules corresponding to the execution action can also be determined and stored based on the execution statement after the execution action is optimized.

[0066] In this embodiment of the invention, the dynamic analysis syntax tree for relational data will be systematically described below:

[0067] The Dynamic Analyzed Syntax Tree (DAST) proposed in this embodiment of the invention refers to a dynamic analysis syntax tree for relational data after feature expansion. It can dynamically expand the SQL syntax of relational data based on dynamic performance data to generate execution logic. Specifically, it includes two parts: lexical analysis and syntax analysis. The lexical analysis involves keywords in SQL. The syntax, or SQL syntax, is the SQL query statement for relational data. The dynamic performance data, or dynamic data, includes dynamic statistical data, dynamic data dictionaries, etc., such as attributes like degree, number of rows, and available line size. The dynamic statistical data refers to the dynamic statistical data or information generated during the execution of the structured query statement.

[0068] The implementation process of dynamic analysis feature syntax tree for relational data includes:

[0069] Lexical analysis reads the SQL syntax; then, according to preset rules, it merges the data into individual tokens. Simultaneously, whitespace, comments, etc., are removed; finally, the entire SQL syntax is divided into a list of tokens (or a one-dimensional array).

[0070] Syntax analysis can transform the array or list obtained from lexical analysis into a tree-like structure, constructing the corresponding syntax tree. The next state is obtained through the event and the current state, i.e., event + state = next state, ultimately achieving greedy matching between the syntax and regular expressions. Therefore, the syntax tree construction process can be viewed as a regular expression matching process.

[0071] After the syntax tree is constructed, feature extensions are performed on target databases such as Oracle, MySQL, DB2, SQL Server, GaussDB, OceanBase, and PostgreSQL. In addition to user input, dynamic statistical data from the execution process of structured query statements are also incorporated into the initial dynamic analysis syntax tree.

[0072] The feature extension is implemented by extracting user-defined information such as tables and columns from the SQL objects used in the target database. This invention effectively correlates dynamic statistical data and data dictionary information from operation monitoring, calculates field distinguishability (uniqueness), execution actions, number of rows, wait events, transaction information, etc., and uses this information to carry different target database design models. All dynamic data is mounted as attributes on the leaf nodes of the initial dynamic analysis syntax tree, the associated data of each leaf node is counted, and the weights of the final path nodes selected for each node are increased or decreased, ultimately forming a dynamic analysis feature syntax tree and optimal path weights for relational data.

[0073] Specifically, the dynamic analysis feature syntax tree (i.e., parsing engine) for relational data is used to parse, optimize, and audit SQL queries. However, due to the complex nesting of SQL queries and the presence of various functions and operators, traditional syntax tree (AST) structures are quite complex, typically requiring complex traversals and relying on dynamic data dictionaries and statistical data. This makes directly applying traditional syntax trees (ASTs) costly. This invention provides its API, such as... Figure 2 As shown, this specifically includes: obtaining SQL dependency objects; obtaining the table's related columns, filter columns, grouping columns, and related statistical data and dynamic data dictionary information; obtaining the SQL's structural characteristics: nesting level and dependent statistical data level information.

[0074] In this embodiment of the invention, the auditing rules for relational data will be systematically described below:

[0075] The obtained review rules are multi-source review rules, i.e., rules from the expert review engine. In addition to fixed rules and custom rules, recommended rules are also added. The recommended rules are derived by reverse parsing based on the dynamic analysis syntax tree (DAST) and combining the obtained dynamic statistical data and dynamic data dictionary. Users can either fix the recommended rules or activate them temporarily; no specific limitations are made here.

[0076] The recommended rules cover GaussDB, MySQL (open-source database), Oracle, and SQL Server databases. Furthermore, based on existing fixed rules, they can be combined and integrated. The rules that can be integrated include, but are not limited to, the following:

[0077]

[0078]

[0079] Table 1 GaussDB Data Source Audit

[0080] After expert review of the engine, the GaussDB data source review method has at least five main core review rules, which are not specifically limited here.

[0081]

[0082]

[0083] Table 2 MySQL Data Source Audit

[0084] The expert review engine and GaussDB data source review method of this invention have at least 20 main core review rules.

[0085] Serial Number Library type Types of objects to be reviewed Rule Name 1 OceanBase sqltext DML statement missing WHERE clause 2 OceanBase sqltext The table join operation lacked join conditions. 3 OceanBase sqltext Use select* 4 OceanBase sqltext Use left fuzzy search 5 OceanBase sqltext The execution plan contains Cartesian operations.

[0086] Table 3 OceanBase Data Source Audit

[0087] The expert review engine of this invention and the OceanBase data source review method have at least the above 5 main core review rules.

[0088]

[0089]

[0090] Table 4 Oracle Data Source Audit

[0091] The expert review engine of this invention and the Oracle data source review method have at least 20 main core review rules.

[0092]

[0093] Table 5 SQL Server Data Source Audit

[0094] The following describes typical use cases where the parsing results of the dynamic analysis syntax tree (DAST) can be used to recommend audit rules in reverse. However, in the embodiments of this invention, the following scenarios are included but not limited to: when dealing with the full table scan step in the execution plan, the parsing is mainly carried out from two cases: active selection and passive selection.

[0095] By combining dynamic analysis of syntax trees with dynamic data dictionaries and dynamic statistical data, the system proactively recommends optimal, non-fixed audit rules. Without affecting the logic of the corresponding application, it adaptively adjusts the data types based on user configuration.

[0096] If the filter condition column has an index, but the optimizer determines that a full table scan is more efficient (a full table scan involves multiple reads, while an index is generally a single read and often requires a table lookup), the optimizer can proactively recommend rewriting the full table scan rule and proactively adjust the execution action based on the user configuration. Based on the analysis results, the optimizer can proactively recommend and suggest similar scenarios where the actual performance is low and the query method needs to be adjusted.

[0097] Passive selection occurs when the filter condition lacks an index, the index on the filter condition is invalid (INVALID), or the index cannot be used due to reasons such as function calculations on the indexed column, implicit conversions caused by different data types between the indexed column and the assigned value.

[0098] In the specific implementation process, the proactive selection of embedded audit rules includes: 1) Ignoreable cases: When the business table has a small amount of data, a full table scan only requires one IO operation to obtain all rows, while using an index + table lookup requires at least two IO operations to obtain the required results. In this case, a full table scan is definitely more efficient, and it can be used directly to avoid designing too many indexes. However, future data changes in the table need to be considered. For example, if the business data table may experience a significant increase in data in the future, indexes should be designed in advance to avoid a sharp drop in query performance due to large changes in data at a certain stage. If it is a configuration table, its data is basically stable within a certain range, and the full table scan can be ignored. 2) Query design rationality rules: When the query conditions are interleaved, that is, when the query results account for a high proportion of the total data volume, the optimizer is also very likely to choose a full table scan. In this case, the query business design rationality rules need to be considered to see if it violates the principle of minimizing OLTP business query data.

[0099] In addition, passive selection scenarios for embedding audit rules include: 1) Rules where the condition column has no index. Create appropriate indexes on query columns with high selection filtering and high execution frequency to ensure that SQL queries can use the index during execution, thereby improving execution efficiency. 2) Index invalidation rules. When there are function calculations, expressions, implicit conversions, or left fuzzy matching on the indexed column, the index may become invalid, causing the query to fail to use the index and instead perform a full table scan.

[0100] Automatic recommendation rules, such as the audit rules for candidate index generation, are as follows: Attributes are divided into four groups: J: Attributes appearing in JOIN conditions; EQ: Attributes appearing in EQUAL conditions; RANGE: Attributes appearing in range conditions; O: Attributes appearing in GROUP BY and ORDER BY clauses. The generation of candidate rule suggestions is guided by dynamically parsed data, column selectivity, data volume, average row length, etc.: 1. Construct all single-attribute indexes using the optimal column selectivity, data volume, and average row length from J, EQ, and RANGE. 2. When attributes in a table come from the same table, use the attribute with the highest distinguishability in O to generate the index. 3. If table a joins table b with multiple attributes, construct the index using all attributes with the highest join distinguishability. 4. Represent the attribute from J or the index generated by rule 3 as J, and the attribute from RANGE as r, constructing indexes J+EQ+r, index EQ+r, index J+r, and index J+EQ. The above rules can be initially selected using selectivity, which will not be elaborated further here.

[0101] like Figure 3 As shown, other examples of proactive recommendation rule applications are: 1. Proactive selection of full table scan. After dynamic statistical data analysis, a full table scan is performed when the query condition has low filtering power (object_id>100), while an index range scan is performed when the filtering power is high (object_id<100). 2. Passive selection of full table scan. 1) Index range scan (after dynamic data dictionary analysis, object_id in the test2 table is a character type). 2) Full table scan is performed due to implicit conversion (after dynamic data dictionary analysis, object_id in the test2 table is a character type). 3) Through dynamic statistical data analysis, a full table scan is performed when the attribute includes function operations, expressions, and fuzzy queries. Without modifying the initial logic, the matching data type can be adaptively modified or the logic adjusted.

[0102] In the specific implementation process, dynamic statistical data such as leaf node discrimination, average row length, and data volume obtained from dynamic analysis of the syntax tree, as well as dynamic database data such as the dynamic data dictionary, are used as data inputs for the multi-source rule review method. This allows for the output of reverse-suggested rules based on the dynamic analysis of the syntax tree, enabling the review of inefficient SQL issues and risks. The specific process for proactively recommending rules is as follows: 1. Obtain the objects operated on by the SQL query statement through dynamic analysis of the syntax tree parsing method; 2. Capture statistical data and dynamic data dictionaries of the involved objects in real time; 3. Obtain the execution plan of the SQL query statement and determine if the execution plan matches the review criteria; 4. In cases of inefficient SQL execution, proceed to rule review and matching, and simultaneously recommend the optimal review rule suggestion; 5. If the performance judgment result indicates an automatically recommended rule, this rule can be either permanently applied or temporarily enabled.

[0103] This invention integrates features from various mainstream databases, providing a method for efficiently parsing the SQL language of databases such as Oracle, MySQL, DB2, SQL Server, GaussDB, OceanBase, PostgreSQL, and OpenGauss. It generates a Dynamic Analysis Feature Syntax Tree (DAST) for relational data and fuses it with dynamic statistical analysis data to create audit rules. Furthermore, addressing pain points in SQL programs, it develops an expert audit engine for audit rules. Besides managing static data such as SQL, tables, indexes, and sequences, it can also fuse dynamic performance data, including dynamic data dictionaries and dynamic statistical data, with the SQL parsing DAST to obtain the most accurate audit results and optimal SQL program optimization suggestions. Additionally, it can merge query plans with the DAST to form new result sets and, based on the DAST, recommend rules to help improve the audit optimization system and develop best practices. This result set can be used in various SQL quality analysis scenarios, such as expert rule-based SQL analysis, semantically heuristic SQL analysis, and machine learning-based SQL analysis. It can quickly perform pre-deployment analysis, online analysis, and user-defined analysis of SQL, minimizing the risks and problems in SQL code development. At the same time, the review process is automated, reducing the workload of reviewing massive amounts of SQL and freeing up human resources.

[0104] The query optimization method for relational data described in this invention constructs a dynamic analysis feature syntax tree by combining dynamic performance data. Based on this dynamic analysis feature syntax tree, the corresponding execution plan and the cost of the logical execution plan of the target structured query statement are determined. Based on the cost difference, it is determined whether to optimize the structured query statement. That is, based on the optimal cost, it is determined whether the logical execution plan is optimal. The structured query statements corresponding to the non-optimal execution actions are optimized in a targeted manner according to the dynamic analysis feature syntax tree. This provides an effective basis, improves the quality of application development, reduces the cost of manual review, achieves targeted optimization, and improves the accuracy and efficiency of query data optimization.

[0105] Corresponding to the above-described method for optimizing relational data queries on the base station side, this invention also provides a device for optimizing relational data queries on the base station side. Since the embodiments of this device are similar to the above-described method embodiments, the description is relatively simple. For relevant details, please refer to the description in the above-described method embodiment section. The embodiments of the relational data query optimization device described below are merely illustrative. Please refer to... Figure 4 As shown, it is a schematic diagram of the structure of a query optimization device for relational data provided in an embodiment of the present invention.

[0106] The query optimization device for relational data described in this invention specifically includes the following parts:

[0107] Syntax tree determination unit 401 is used to determine the dynamic analysis feature syntax tree of relational data;

[0108] The execution plan cost estimation unit 402 is used to determine the optimal cost corresponding to the execution action in the execution plan based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, and to obtain the optimal cost of the execution plan; wherein, the execution plan is the execution process or access path of a structured query statement in the database, and the execution plan contains at least one of the execution actions;

[0109] The query data optimization unit 403 is used to determine whether the logical execution plan of the structured query statement is optimal based on the optimal cost of the execution plan; if not, it optimizes the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree.

[0110] The query optimization device for relational data described in this embodiment of the invention constructs a dynamic analysis feature syntax tree by combining dynamic performance data, and determines the corresponding execution plan and the cost of the logical execution plan of the target structured query statement based on the dynamic analysis feature syntax tree. Based on the cost difference, it determines whether to optimize the structured query statement, that is, it determines whether the logical execution plan is optimal based on the optimal cost. For the structured query statements corresponding to the non-optimal execution actions, targeted optimization is performed based on the dynamic analysis feature syntax tree, thereby providing effective basis, improving the quality of application development, reducing the cost of manual review, achieving targeted optimization, and improving the accuracy and efficiency of query data optimization.

[0111] Corresponding to the query optimization method for relational data provided above, this invention also provides an electronic device. Since the embodiment of this electronic device is similar to the method embodiment described above, it is described simply. For relevant details, please refer to the description in the method embodiment section above. The electronic device described below is merely illustrative. Figure 5 The diagram illustrates the physical structure of an electronic device according to an embodiment of the present invention. The electronic device may include a processor 501, a memory 502, and a communication bus 503. The processor 501 and the memory 502 communicate with each other via the communication bus 503 and communicate with external systems via a communication interface 504. The processor 501 can call logical instructions in the memory 502 to execute a query optimization method for relational data. This method includes: determining a dynamic analysis feature syntax tree for relational data; determining the optimal cost corresponding to an execution action in an execution plan based on dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, and obtaining the optimal cost of the execution plan; wherein the execution plan is the execution process or access path of a structured query statement in a database, and the execution plan includes at least one execution action; determining whether the logical execution plan of the structured query statement is optimal based on the optimal cost of the execution plan; if not, optimizing the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree.

[0112] Furthermore, the logical instructions in the aforementioned memory 502 can be implemented as software functional units and, when sold or used as independent products, can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, essentially, or the part that contributes to the prior art, or a part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of the present invention. The aforementioned storage medium includes various media capable of storing program code, such as memory chips, USB flash drives, portable hard drives, read-only memory (ROM), random access memory (RAM), magnetic disks, or optical disks.

[0113] On the other hand, embodiments of the present invention also provide a computer program product, the computer program product including a computer program stored on a processor-readable storage medium, the computer program including program instructions, and when the program instructions are executed by a computer, the computer is able to execute the relational data query optimization method provided in the above-described method embodiments. The method includes: determining a dynamic analysis feature syntax tree of the relational data; determining the optimal cost corresponding to the execution action in the execution plan based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, and obtaining the optimal cost of the execution plan; wherein the execution plan is the execution process or access path of a structured query statement in the database, and the execution plan includes at least one of the execution actions; determining whether the logical execution plan of the structured query statement is optimal based on the optimal cost of the execution plan; if not, optimizing the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree.

[0114] In another aspect, embodiments of the present invention also provide a processor-readable storage medium storing a computer program, which, when executed by a processor, implements the query optimization method for relational data provided in the above embodiments. The method includes: determining a dynamic analysis feature syntax tree (MAS) of the relational data; determining the optimal cost corresponding to an execution action in an execution plan based on dynamic performance data and corresponding weight scores in the MAS, and obtaining the optimal cost of the execution plan; wherein the execution plan is the execution process or access path of a structured query statement in a database, and the execution plan includes at least one of the execution actions; determining whether the logical execution plan of the structured query statement is optimal based on the optimal cost of the execution plan; if not, optimizing the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the MAS.

[0115] The processor-readable storage medium can be any available medium or data storage device that the processor can access, including but not limited to magnetic memory (e.g., floppy disk, hard disk, magnetic tape, magneto-optical disk (MO)), optical memory (e.g., CD, DVD, BD, HVD), and semiconductor memory (e.g., ROM, EPROM, EEPROM, non-volatile memory (NAND FLASH), solid-state drive (SSD)).

[0116] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules can be selected to achieve the purpose of this embodiment according to actual needs. Those skilled in the art can understand and implement this without any creative effort.

[0117] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods described in the various embodiments or some parts of the embodiments.

[0118] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features; and these modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A query optimization method for relational data, characterized in that, include: Determine the dynamic analytical feature syntax tree for relational data; Based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, the optimal cost corresponding to the execution action in the execution plan is determined, and the optimal cost of the execution plan is obtained; wherein, the execution plan is the execution process or access path of the structured query statement in the database, and the execution plan contains at least one of the execution actions; Based on the optimal cost of the execution plan, determine whether the logical execution plan of the structured query statement is optimal; if not, optimize the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree. The dynamic analysis feature syntax tree for determining relational data specifically includes: Obtain the structured query statement used to query the target database; The structured query statement is analyzed to determine the initial dynamic analysis feature syntax tree; wherein the structured query statement is relational data; Extract dynamic performance data corresponding to the target database; the dynamic performance data includes dynamic statistical data and a dynamic data dictionary; wherein, the dynamic statistical data includes the discrimination index, average row length, and data volume corresponding to the target database; Based on the dynamic performance data and the initial dynamic analysis feature syntax tree, a dynamic analysis feature syntax tree for the relational data is constructed.

2. The query optimization method for relational data according to claim 1, characterized in that, Based on the dynamic performance data and the initial dynamic analysis feature syntax tree, a dynamic analysis feature syntax tree for the relational data is constructed, specifically including: The dynamic performance data and the corresponding weight scores are attached to the leaf nodes of the initial dynamic analysis feature syntax tree as attributes of the leaf nodes, thus obtaining the dynamic analysis feature syntax tree of relational data; wherein, the weight scores are the weight scores corresponding to each dynamic data in the dynamic performance data.

3. The query optimization method for relational data according to claim 1, characterized in that, Also includes: Obtain the logical execution plan for the initial structured query statement and determine the cost of the logical execution plan; Determine whether the cost of the logical execution plan is higher than the optimal cost of the execution plan. If so, determine the execution action in the logical execution plan whose cost is higher than the optimal cost of the execution plan, and use the execution statement corresponding to the execution action as the structured query statement to be optimized in the initial structured query statement. Optimize the execution statement to be optimized based on the dynamic performance data contained in the dynamic analysis feature syntax tree and the corresponding weight score; wherein, the execution statement to be optimized is the structured query statement to be optimized.

4. The query optimization method for relational data according to claim 3, characterized in that, Also includes: After optimizing the execution statement to be optimized, the audit rules corresponding to the execution action are determined and stored based on the optimized execution statement corresponding to the execution action; wherein, the audit rules are used to audit and optimize subsequent target structured query statements.

5. The query optimization method for relational data according to claim 1, characterized in that, Based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, the optimal cost corresponding to the execution action in the execution plan is determined, and the optimal cost of the execution plan is obtained, specifically including: Based on the weight scores of each dynamic data corresponding to the leaf nodes in the dynamic analysis feature syntax tree, the dynamic data included in each execution action in the execution plan are weighted according to the weight scores to determine the optimal cost corresponding to each execution action, and the optimal costs of each execution action are added together to obtain the optimal cost of the execution plan.

6. The query optimization method for relational data according to claim 1, characterized in that, Based on the dynamic analysis feature syntax tree, the target structured query statements corresponding to non-optimal execution actions in the logical execution plan are optimized, specifically including: Based on the dynamic data in the dynamic analysis feature syntax tree, the target structured query statement is optimized, and the actual cost corresponding to the optimized statement is determined until the difference between the actual cost corresponding to the optimized statement and the optimal cost of the execution plan is less than a preset threshold; the optimized statement is the optimized execution statement.

7. A query optimization device for relational data, characterized in that, include: Syntax tree determination unit, used to determine the dynamic analysis feature syntax tree of relational data; An execution plan cost estimation unit is used to determine the optimal cost corresponding to the execution action in the execution plan based on the dynamic performance data and corresponding weight scores in the dynamic analysis feature syntax tree, and to obtain the optimal cost of the execution plan; wherein, the execution plan is the execution process or access path of a structured query statement in the database, and the execution plan contains at least one of the execution actions; The query data optimization unit is used to determine whether the logical execution plan of the structured query statement is optimal based on the optimal cost of the execution plan; if not, it optimizes the target structured query statement corresponding to the non-optimal execution action in the logical execution plan according to the dynamic analysis feature syntax tree. The dynamic analysis feature syntax tree for determining relational data specifically includes: Obtain the structured query statement to query the target database; The structured query statement is analyzed to determine the initial dynamic analysis feature syntax tree; wherein the structured query statement is relational data; Extract dynamic performance data corresponding to the target database; the dynamic performance data includes dynamic statistical data and a dynamic data dictionary; wherein, the dynamic statistical data includes the discrimination index, average row length, and data volume corresponding to the target database; Based on the dynamic performance data and the initial dynamic analysis feature syntax tree, a dynamic analysis feature syntax tree for the relational data is constructed.

8. An electronic device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps of the query optimization method for relational data as described in any one of claims 1-6.

9. A processor-readable storage medium having a computer program stored thereon, characterized in that, When executed by a processor, the computer program implements the steps of the query optimization method for relational data as described in any one of claims 1-6.

Images