CN-115617842-B - Query optimization method and device for relational data

CN115617842BCN 115617842 BCN115617842 BCN 115617842BCN-115617842-B

Abstract

The invention provides a query optimization method and device for relational data. The method comprises the steps of determining a dynamic analysis feature grammar tree of relational data, determining 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 grammar tree, 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, the execution plan comprises at least one execution action, judging whether a logic execution plan of the structured query statement is optimal based on the optimal cost of the execution plan, and if not, optimizing a target structured query statement corresponding to a non-optimal execution action in the logic execution plan according to the dynamic analysis feature grammar tree. By adopting the method, the structured query statement to be optimized is determined based on the cost difference, so that the targeted optimization is realized, and the accuracy and the efficiency of query data optimization are improved.

Inventors

LIU FANG
ZHAO HONGSONG
LI FANGYAN
YAN WEN

Assignees

中国移动通信集团黑龙江有限公司
中国移动通信集团有限公司

Dates

Publication Date: 20260512
Application Date: 20210714

Claims (9)

1. A method for query optimization of relational data, comprising: determining a dynamic analysis characteristic grammar tree of the relational data; Determining an optimal cost corresponding to an execution action in an execution plan based on the dynamic performance data in the dynamic analysis feature grammar tree and the 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 the logic execution plan of the structured query statement is optimal or not 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 logic execution plan according to the dynamic analysis characteristic grammar tree; The dynamic analysis characteristic grammar tree for determining the relational data specifically comprises the following steps: obtaining a structured query statement for querying a target database; analyzing the structured query statement to determine an initial dynamic analysis feature grammar tree, wherein the structured query statement is relational data; Extracting dynamic performance data corresponding to the target database, wherein the dynamic performance data comprises dynamic statistical data and a dynamic data dictionary, and the dynamic statistical data comprises distinguishing degree, average line length and data quantity corresponding to the target database; And constructing a dynamic analysis characteristic grammar tree of the relational data based on the dynamic performance data and the initial dynamic analysis characteristic grammar tree.
2. The method for optimizing query of relational data according to claim 1, wherein constructing the dynamic analysis feature syntax tree of the relational data based on the dynamic performance data and the initial dynamic analysis feature syntax tree specifically comprises: And mounting the dynamic performance data and the corresponding weight scores on leaf nodes of the initial dynamic analysis feature grammar tree to serve as attributes of the leaf nodes to obtain a dynamic analysis feature grammar tree of the relational data, wherein the weight scores are weight scores corresponding to each dynamic data in the dynamic performance data respectively.
3. The method for query optimization of relational data as in claim 1, further comprising: Acquiring a logic execution plan of an initial structured query statement, and determining the cost of the logic execution plan; Judging whether the cost of the logic execution plan is higher than the optimal cost of the execution plan, if so, determining an execution action corresponding to the logic execution plan with the cost higher than the optimal cost of the execution plan, taking an execution statement corresponding to the execution action as a structural query statement to be optimized in the initial structural query statement, and optimizing the execution statement to be optimized based on dynamic performance data and corresponding weight scores contained in the dynamic analysis feature grammar tree, wherein the execution statement to be optimized is the structural query statement to be optimized.
4. The method for optimizing query of relational data according to claim 3, further comprising determining and storing an audit rule 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 rule is used for auditing and optimizing a subsequent target structured query statement.
5. The method for optimizing query of relational data according to claim 1, wherein determining an optimal cost corresponding to an execution action in an execution plan based on dynamic performance data and a corresponding weight score in the dynamic analysis feature syntax tree, and obtaining the optimal cost of the execution plan, specifically comprises: And weighting the dynamic data included in each execution action in the execution plan according to the weight score based on the weight score of each dynamic data corresponding to the leaf node in the dynamic analysis feature grammar tree, determining the optimal cost corresponding to each execution action, and adding the optimal cost of each execution action to be used as the optimal cost of the execution plan.
6. The method for optimizing query of relational data according to claim 1, wherein optimizing the target structured query statement corresponding to the non-optimal execution action in the logic execution plan according to the dynamic analysis feature syntax tree specifically comprises: Optimizing the target structured query sentence according to the dynamic data in the dynamic analysis characteristic grammar tree, and determining the actual cost corresponding to the optimized sentence until the difference between the actual cost corresponding to the optimized sentence and the optimal cost of the execution plan is smaller than a preset threshold value, wherein the optimized sentence is an optimized execution sentence.
7. A query optimization device for relational data, comprising: a syntax tree determining unit for determining a dynamic analysis feature syntax tree of the relational data; An execution plan cost estimation unit, configured to determine an optimal cost corresponding to an execution action in an execution plan based on dynamic performance data in the dynamic analysis feature syntax tree and a corresponding weight score, and obtain the optimal cost of the execution plan, where the execution plan is an execution process or an access path of a structured query statement in a database, and the execution plan includes at least one execution action; The query data optimizing unit is used for judging whether the logic execution plan of the structured query statement is optimal or not based on the optimal cost of the execution plan, and if not, optimizing the target structured query statement corresponding to the non-optimal execution action in the logic execution plan according to the dynamic analysis characteristic grammar tree; The dynamic analysis characteristic grammar tree for determining the relational data specifically comprises the following steps: obtaining a structured query statement for querying a target database; analyzing the structured query statement to determine an initial dynamic analysis feature grammar tree, wherein the structured query statement is relational data; Extracting dynamic performance data corresponding to the target database, wherein the dynamic performance data comprises dynamic statistical data and a dynamic data dictionary, and the dynamic statistical data comprises distinguishing degree, average line length and data quantity corresponding to the target database; And constructing a dynamic analysis characteristic grammar tree of the relational data based on the dynamic performance data and the initial dynamic analysis characteristic grammar tree.
8. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, characterized in that the processor implements the steps of the method for query optimization of relational data according to any one of claims 1-6 when the program is executed by the processor.
9. A processor readable storage medium having stored thereon a computer program, which when executed by a processor, implements the steps of a method of query optimization of relational data according to any one of claims 1-6.

Description

Query optimization method and device for relational data Technical Field The invention relates to the technical field of computer application, in particular to a query optimization method and device for relational data. In addition, the invention also relates to an electronic device and a processor readable storage medium. Background In recent years, with the rapid development of internet technology, database-based implementation is increasingly applied. In the database working process, SQL (Structured Query Language ) query sentences executed by the database are usually required to be optimized, so that the structured query sentences with poor performance are prevented from entering the system, and the performance and stability of the database are reduced. The structured query language is a database query and programming language for accessing data and querying, updating and managing data stored in a relational database system. The structured query language can allow a user to work on a high-level data structure. The method does not require a user to specify a data storage method or require the user to know a specific data storage mode, so that different database systems with completely different underlying structures can use the same structured query language as an interface for data input and management. Meanwhile, the structured query language sentences can be nested, so that the structured query language sentences have great flexibility and powerful functions. However, in the prior art, the optimization processing mode flow aiming at the structured query statement is single, and the pertinence is lacking, so that the stability and the efficiency of the query data optimization processing are lower. Therefore, how to design a stable and efficient query data optimization processing scheme becomes a difficult problem to be solved. Disclosure of Invention Therefore, the invention provides a query optimization method and device for relational data, which are used for solving the problems of low optimization efficiency and accuracy of query data caused by poor pertinence of a data query optimization processing scheme in the prior art. In a first aspect, the present invention provides a method for optimizing query of relational data, including: determining a dynamic analysis characteristic grammar tree of the relational data; Determining an optimal cost corresponding to an execution action in an execution plan based on the dynamic performance data in the dynamic analysis feature grammar tree and the 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; And judging whether the logic execution plan of the structured query statement is optimal based on the optimal cost of the execution plan, and if not, optimizing the target structured query statement corresponding to the non-optimal execution action in the logic execution plan according to the dynamic analysis characteristic grammar tree. In one embodiment, the determining the dynamic analysis feature syntax tree of the relational data specifically includes: obtaining a structured query statement for querying a target database; analyzing the structured query statement to determine an initial dynamic analysis feature grammar tree, wherein the structured query statement is relational data; Extracting dynamic performance data corresponding to the target database, wherein the dynamic performance data comprises dynamic statistical data and a dynamic data dictionary, and the dynamic statistical data comprises distinguishing degree, average line length and data quantity corresponding to the target database; And constructing a dynamic analysis characteristic grammar tree of the relational data based on the dynamic performance data and the initial dynamic analysis characteristic grammar tree. In one embodiment, based on the dynamic performance data and the initial dynamic analysis feature syntax tree, constructing a dynamic analysis feature syntax tree of the relational data specifically includes: And mounting the dynamic performance data and the corresponding weight scores on leaf nodes of the initial dynamic analysis feature grammar tree to serve as attributes of the leaf nodes to obtain a dynamic analysis feature grammar tree of the relational data, wherein the weight scores are weight scores corresponding to each dynamic data in the dynamic performance data respectively. In one embodiment, the method for optimizing the query of the relational data further comprises: Acquiring a logic execution plan of an initial structured query statement, and determining the cost of the logic execution plan; Judging whether the cost of the logic execution plan is higher than the optimal cost of the execution plan, if so, determining an execution action corresponding to the logic