A query optimization method and device for structured query language

Abstract

The present application discloses a query optimization method and apparatus for structured query language (SQL), belonging to the field of big data technology. The method receives at least two SQL statements for executing data queries on a database, parses each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement, constructs a query relationship tree corresponding to each SQL statement, creates a directed connection graph between the data tables of the main query statement and the subquery statement contained in each SQL statement from the query relationship tree corresponding to each SQL statement, matches the directed connection graphs corresponding to each SQL statement to identify any set of directed connection graphs that are isomorphic, fuses and rewrites the associated target SQL statements to obtain a new SQL statement, and executes data queries on the database based on the new SQL statement. The present application can improve the query execution performance of batches of complex SQL statements received in real time.

Description

Technical Field

[0001] The present application relates to the technical field of big data processing, and in particular to a query optimization method and apparatus for structured query language. Background Art

[0002] With the deepening of enterprise digital transformation and the widespread adoption of data middle-office strategies, enterprise applications (such as ERP, CRM, and BI) increasingly rely on large-scale, multi-source, heterogeneous data warehouses or data lakes for decision support. In these scenarios, business logic is highly complex, often requiring in-depth analysis and value mining through complex SQL statements involving multi-table joins, nested subqueries, aggregate functions, and window functions. However, in big data business scenarios, the execution time of unoptimized complex SQL statements can increase exponentially, leading to server cluster overload and system response delays. Existing database optimizers often struggle to handle complex SQL statements, especially when optimizing batches of complex SQL statements.

[0003] The existing patent disclosure document CN118363980B constructs a hierarchical clustering tree by collecting SQL clauses and combined clauses in a historical database, and analyzes whether there is a merging probability between the SQL clauses to be merged and the merged feature vector by comparing the features between the SQL clauses to be merged and the merged feature vector, and merges the SQL clauses to be merged based on the merging probability. CN102945256B collects all SQL statements in the running system, parses each SQL statement, obtains the variable values ​​in each SQL statement, calculates the HASH value of the parsed SQL statement, and classifies and merges the SQL statements according to the HASH value. Although the above schemes propose solutions for the merging optimization of batch SQL statements, these schemes rely heavily on the statistical analysis of feature similarity between historical SQL statements, and are still insufficient for the combined optimization of multiple complex SQL statements received in real time. Therefore, for enterprise-level big data search and analysis scenarios, how to perform query optimization on batch complex SQL statements received in real time is a technical problem that needs to be solved urgently. Summary of the Invention

[0004] In view of this, the present application proposes a query optimization method and device for structured query language to improve the query execution performance of batch complex SQL statements received in real time in enterprise-level big data search and analysis scenarios, and reduce system response delay.

[0005] In a first aspect, the present application proposes a query optimization method for structured query language, the method comprising:

[0006] Receiving at least two SQL statements for executing data query on a database, and parsing each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement;

[0007] Based on the main query statement and subquery statements included in each SQL statement, a query relationship tree corresponding to each SQL statement is constructed, and a directed connection graph between data tables of the main query statement and subquery statements included in each SQL statement is created from the query relationship tree corresponding to each SQL statement, wherein nodes in the directed connection graph represent data tables of the main query statement and subquery statements, and edges in the directed connection graph represent connection relationships between the data tables of the main query statement and subquery statements;

[0008] The directed connection graph corresponding to each SQL statement is matched to identify any group of directed connection graphs that are isomorphic, the target SQL statements associated with the any group of directed connection graphs are fused and rewritten to obtain new SQL statements after fusion and rewriting, and data query is executed on the database based on the new SQL statements.

[0009] Optionally, creating a directed connection graph between data tables of a main query statement and subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement includes:

[0010] Obtaining the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement from the query relationship tree corresponding to each SQL statement;

[0011] The directed connection graph is constructed based on the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement.

[0012] Optionally, creating a directed connection graph between data tables of a main query statement and subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement includes:

[0013] Determining from the query relationship tree corresponding to each SQL statement whether the connection relationship between the data tables of the main query statement and the subquery statement is a dependent connection relationship, and if so, converting the connection relationship between the data tables of the main query statement and the subquery statement into a non-dependent connection relationship;

[0014] The query relationship tree is reconstructed according to the non-dependent connection relationship between the data tables of the main query statement and the sub-query statement to obtain a reconstructed query relationship tree.

[0015] Optionally, matching the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs that are isomorphic includes:

[0016] From any two directed connection graph node sets corresponding to any two SQL statements of the at least two SQL statements, sequentially traverse and select candidate nodes according to a forward search strategy for comparison, and determine whether the data tables represented by the candidate nodes are the same;

[0017] If the data tables represented by two adjacent candidate nodes traversed in sequence are the same, then further determine whether the connection types and connection conditions represented by the edges between the two adjacent candidate nodes in the edge sets of any two directed connection graphs are the same, and traverse all nodes and edges in the node set and edge set in sequence. If they are all the same, then identify that the any two directed connection graphs are isomorphic.

[0018] Optionally, matching the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs that are isomorphic includes:

[0019] Calculate the edit distance between any two directed connection graphs corresponding to any two SQL statements in the at least two SQL statements, and determine whether the edit distance between the any two directed connection graphs is zero. If so, identify that the any two directed connection graphs are isomorphic.

[0020] Optionally, the merging and rewriting of the target SQL statements associated with the arbitrary group of directed connection graphs to obtain new SQL statements after merging and rewriting includes:

[0021] Detecting whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results;

[0022] If fusion is possible, the query relationship trees corresponding to the target SQL statement are fused to generate a fused query relationship tree corresponding to the target SQL statement, and the target SQL statement is rewritten based on the fused query relationship tree to obtain the new SQL statement.

[0023] Optionally, detecting whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results includes:

[0024] If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is an aggregate function, the subquery conditions are the same, and the subquery result has no inclusion relationship, then it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be fused.

[0025] Optionally, detecting whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results includes:

[0026] If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is a column field query, and the subquery condition and the subquery result have an inclusion relationship, it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged.

[0027] Optionally, fusing the query relationship trees corresponding to the target SQL statement to generate a fused query relationship tree corresponding to the target SQL statement includes:

[0028] In the main query result of the fused query relationship tree, a conditional function is used to project the subquery result of another target SQL statement based on the subquery result of one target SQL statement in the target SQL statements.

[0029] In a second aspect, the present application further provides a query optimization device for structured query language, the device comprising:

[0030] A query receiving module, configured to receive at least two SQL statements for executing data queries on a database, and parse each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement;

[0031] a query parsing module, configured to construct a query relationship tree corresponding to each SQL statement based on the main query statement and subquery statements contained in each SQL statement, and create a directed connection graph between the data tables of the main query statement and the subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement, wherein the nodes in the directed connection graph represent the data tables of the main query statement and the subquery statement, and the edges in the directed connection graph represent the connection relationship between the data tables of the main query statement and the subquery statement;

[0032] A query optimization module is used to match the directed connection graph corresponding to each SQL statement to identify any group of directed connection graphs that are isomorphic, fuse and rewrite the target SQL statements associated with the any group of directed connection graphs to obtain new SQL statements after fusion and rewriting, and execute data queries on the database based on the new SQL statements.

[0033] This application can at least achieve the following beneficial effects:

[0034] The present application receives at least two SQL statements for executing data queries on a database, parses each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement, constructs a query relationship tree corresponding to each SQL statement based on the main query statement and the subquery statement contained in each SQL statement, creates a directed connection graph between data tables of the main query statement and the subquery statement contained in each SQL statement from the query relationship tree corresponding to each SQL statement, matches the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs belonging to the same structure, fuses and rewrites target SQL statements associated with any group of directed connection graphs to obtain a new SQL statement after fusion and rewriting, and executes data queries on the database based on the new SQL statement, thereby improving the query execution performance of batch complex SQL statements received in real time in enterprise-level big data search and analysis scenarios and reducing system response delay. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] In order to more clearly illustrate the technical solutions of the embodiments of the present application, the following is a brief introduction to the drawings required for use in the embodiments of the present application. It should be understood that the following drawings only illustrate certain embodiments of the present application and should not be regarded as limiting the scope of the present application.

[0036] Figure 1 This is a flow chart of a query optimization method for a structured query language according to the first embodiment of the present application;

[0037] Figure 2 is a schematic diagram of an exemplary first query relationship tree constructed;

[0038] Figure 3 is a schematic diagram of an exemplary second query relationship tree constructed;

[0039] Figure 4 is a schematic diagram of an exemplary reconstructed first query relationship tree;

[0040] Figure 5 This is a partial flow chart of a query optimization method for a structured query language according to the second embodiment of the present application;

[0041] Figure 6 This is a partial flow chart of a query optimization method for a structured query language according to the third embodiment of the present application;

[0042] Figure 7 is a schematic diagram of a first directed connection graph created based on the reconstructed first query relationship tree;

[0043] Figure 8 is a schematic diagram of a second directed connection graph created based on the second query relationship tree;

[0044] Figure 9 This is a partial flow chart of a query optimization method for a structured query language according to a fourth embodiment of the present application;

[0045] Figure 10 This is a partial flow chart of a query optimization method for a structured query language according to a fifth embodiment of the present application;

[0046] Figure 11 is a schematic diagram of an exemplary first fusion query relationship tree constructed;

[0047] Figure 12 is a schematic diagram of an exemplary third query relationship tree and a third directed connection graph constructed;

[0048] Figure 13 is a schematic diagram of an exemplary fourth query relationship tree and a fourth directed connection graph constructed;

[0049] Figure 14 is a schematic diagram of an exemplary second fusion query relationship tree constructed;

[0050] Figure 15 It is a structural diagram of a query optimization device for a structured query language according to the first embodiment of the present application. DETAILED DESCRIPTION

[0051] In order to make the purpose, technical solutions and advantages of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings of the embodiments of the present application. However, it should be understood that the described embodiments are only some exemplary embodiments of the present application, not all embodiments, and therefore the following detailed description of the embodiments of the present application is not intended to limit the scope of protection claimed in this application. Based on the embodiments of the present application, all other embodiments obtained by those skilled in the art without making creative work are within the scope of protection of this application.

[0052] It should be noted that the terms "first", "second", etc. in the description and claims of this application are only used to distinguish and describe similar objects, rather than to describe a specific order or sequence, and cannot be understood as indicating or implying relative importance.

[0053] As mentioned above, for enterprise-level big data search and analysis scenarios, there is still a challenge in optimizing queries for batches of complex SQL statements received in real time. To this end, this application proposes a query optimization method and device for structured query language to improve the query execution performance of batches of complex SQL statements received in real time in enterprise-level big data search and analysis scenarios, and reduce system response latency.

[0054] Figure 1 FIG. 1 is a flow chart of a query optimization method for a structured query language according to the first embodiment of the present application. Figure 1 As shown, the method includes the following steps:

[0055] Step 110: receiving at least two SQL statements for executing data query on a database, and parsing each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement;

[0056] Step 120: Based on the main query statement and subquery statements included in each SQL statement, a query relationship tree corresponding to each SQL statement is constructed, and a directed connection graph between the data tables of the main query statement and the subquery statements included in each SQL statement is created from the query relationship tree corresponding to each SQL statement, where the nodes in the directed connection graph represent the data tables of the main query statement and the subquery statement, and the edges in the directed connection graph represent the connection relationships between the data tables of the main query statement and the subquery statement;

[0057] Step 130: Match the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs that are isomorphic, fuse and rewrite the target SQL statements associated with the any group of directed connection graphs to obtain new SQL statements after fusion and rewriting, and execute data query on the database based on the new SQL statements.

[0058] In an embodiment of the present application, in an enterprise-level big data search and analysis scenario, it is common to encounter a situation where a batch of complex SQL statements are received in real time to execute a query plan on a database. The database may include a relational database in an enterprise information system for storing business big data. In this embodiment, at least two SQL statements for executing data queries on a database are first received, and each of the at least two SQL statements is parsed to obtain a main query statement and a subquery statement contained in each SQL statement. Each SQL statement may be a complex SQL statement containing any combination of multi-table joins, nested subqueries, aggregate functions, and window functions. It can usually be disassembled into a main query statement and at least one subquery statement. The connection relationship between the main query statement and the at least one subquery statement in the originally received SQL statement may constitute an implicit dependent connection relationship (for example, a nested subquery, that is, the execution of the subquery statement depends on the row-by-row execution result of the main query statement) or an explicit non-dependent connection relationship (for example, a left join LEFT JOIN and a right join RIGHT JOIN, that is, the execution of the subquery statement does not depend on the row-by-row execution result of the main query statement).

[0059] Subsequently, this embodiment can construct a query relationship tree corresponding to each SQL statement based on the main query statement and sub-query statements contained in each SQL statement. The query relationship tree is used to represent the tree structure of the logical relationship between the main query statement and at least one sub-query statement contained in each SQL statement. The following is an example of a set of complex SQL statements received in real time, which includes at least the following exemplary SQL statements SQL1 and SQL2.

[0060] SQL1:

[0061] SELECT a.id, (SELECT MIN(price) FROM Orders AS b WHERE type='1' anda.id=b.sid) "min_price " FROM Suppliers AS a;

[0062] SQL2:

[0063] SELECT a.id, b.max_price FROM Suppliers AS a LEFT JOIN (SELECT sid,MAX(price) AS max_price FROM Orders WHERE type = '1' GROUP BY sid) AS b ONa.id = b.sid.

[0064] For the above exemplary SQL statements SQL1 and SQL2, a corresponding query relationship tree can be constructed, such as Figure 2 and Figure 3 As shown. Among them, Figure 2 The query relationship tree shown shows the data tables and aliases contained in the main query statement and subquery statement respectively, and the connection relationship between the data tables in the main query statement and subquery statement, that is, the dependent connection relationship, and also parses the main query result of the main query statement and the subquery result and subquery conditions involved in the subquery statement. The main query result is the root node, the data tables contained in the main query statement and subquery statement are leaf nodes, and the others are intermediate nodes, forming the logical link of the query relationship tree. Similarly, Figure 3 The query relationship tree shown shows the data tables and aliases respectively contained in the main query statement and subquery statement decomposed from the SQL statement SQL2, the connection relationship between the data tables in the main query statement and the subquery statement, i.e. the left connection relationship, the main query result, the subquery result, and the subquery condition.

[0065] Subsequently, this embodiment can create a directed connection graph between the data tables of the main query statement and the subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement. The directed connection graph is used to represent the set of data tables of the main query statement and the subquery statements contained in each SQL statement and the connection relationships between the data tables, wherein the nodes of the directed connection graph represent the data tables of the main query statement and the subquery statements, and the edges in the directed connection graph represent the connection relationships between the data tables of the main query statement and the subquery statements.

[0066] In one embodiment, Figure 5 As shown, in step 120, creating a directed connection graph between the data tables of the main query statement and the subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement may include:

[0067] Step 210: Obtain the data tables of the main query statement and the sub-query statement, and the connection relationship between the data tables of the main query statement and the sub-query statement from the query relationship tree corresponding to each SQL statement;

[0068] Step 220: construct the directed connection graph based on the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement.

[0069] Specifically, this embodiment can be Figure 2 and 3The traversal starts from the leftmost leaf node of the query relationship tree shown, skipping the intermediate nodes of the sub-query results and sub-query conditions, and sequentially obtaining the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement.

[0070] In one embodiment, Figure 6 As shown, the step 120 of creating a directed connection graph between the data tables of the main query statement and the subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement may also include:

[0071] Step 310: Determine from the query relationship tree corresponding to each SQL statement whether the connection relationship between the data tables of the main query statement and the subquery statement is a dependent connection relationship. If so, convert the connection relationship between the data tables of the main query statement and the subquery statement into a non-dependent connection relationship.

[0072] Step 320 : reconstruct the query relationship tree according to the non-dependent connection relationship between the data tables of the main query statement and the sub-query statement to obtain a reconstructed query relationship tree.

[0073] In this embodiment, in order to facilitate comparison, the query relationship tree corresponding to each SQL statement can be grammatically aligned. Figure 2 The dependent connection in the query relationship tree of the SQL statement SQL1 shown is converted to something like Figure 3 The non-dependent connection relationship in, such as the left connection relationship, the reconstructed query relationship tree is as follows Figure 4 In this way, a directed connection graph between the data tables of the main query statement and the sub-query statement contained in each SQL statement can be created based on the aligned query relationship tree.

[0074] based on Figure 4 The reconstructed query relationship tree shown in the figure shows the directed connection diagram between the data tables of the main query statement and the subquery statement. Figure 7 As shown. The nodes in the directed connection graph represent the data tables in the main query statement and the sub-query statement, namely, Suppliers and Orders. The edges in the directed connection graph represent the connection relationship between the data tables in the main query statement and the sub-query statement. The connection relationship includes a connection type and a connection condition, namely, the connection type is a left connection, and the connection condition is Suppliers.id=Orders.sid. The direction of the edge in the directed connection graph represents the connection direction between the data tables in the main query statement and the sub-query statement. In order to subsequently match the directed connection graphs, Figure 4In the query relationship tree shown, in the connection conditions between the data tables in the main query statement and the subquery statement, the alias can be replaced with the actual data table. Figure 3 The query relationship tree shown in the figure constructs a directed connection diagram between the data tables of the main query statement and the subquery statement. Figure 8 shown.

[0075] Further, according to Figure 7 and Figure 8 The matching between the directed connection graphs shown can identify Figure 7 and Figure 8 The two separately created directed connection graphs are essentially isomorphic directed connection graphs. In this embodiment, the isomorphic directed connection graphs satisfy the requirement that the data tables for the main query statement and subquery statement represented by the nodes in the directed connection graphs are identical, and the connection relationships (including connection types and connection conditions) between the data tables for the main query statement and subquery statement represented by the edges in the directed connection graphs are also identical. In other words, based on the matching between the directed connection graphs between the data tables for the main query statement and subquery statement contained in each SQL statement, performance issues such as duplicate or redundant queries between the data tables for the main query statement and subquery statement between SQL statements SQL1 and SQL2 can be quickly identified. In this way, at least two target SQL statements associated with the isomorphic directed connection graphs can be further fused and rewritten to obtain a fused new SQL statement, and data queries can be executed against the database based on the new SQL statement. This can improve the query execution performance of batches of complex SQL statements received in real time in enterprise-level big data search and analysis scenarios and reduce system response latency.

[0076] It should be noted that, in order to simplify and facilitate the explanation of the conceptual principles of this application, the above exemplary SQL statement only contains one sub-query statement, but in fact it can also contain multiple sub-query statements. For SQL statements containing multiple sub-query statements, the method described in this embodiment is also applicable.

[0077] In one embodiment, Figure 9 As shown, in step 130, matching the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs belonging to the same structure may include:

[0078] Step 410: From any two directed connection graph node sets corresponding to any two SQL statements of the at least two SQL statements, candidate nodes are sequentially traversed and compared according to a forward search strategy to determine whether the data tables represented by the candidate nodes are the same.

[0079] Step 420: If the data tables represented by the two adjacent candidate nodes traversed in sequence are the same, further determine whether the connection types and connection conditions represented by the edges between the two adjacent candidate nodes in the edge sets of the arbitrary two directed connection graphs are the same, and traverse all the nodes and edges in the node set and edge set in sequence. If they are the same, identify that the arbitrary two directed connection graphs are isomorphic.

[0080] In this embodiment, the directed connection graph corresponding to each SQL statement can be expressed as , where V represents the node set of the directed connection graph, E represents the edge set of the directed connection graph, , JT and JC represent the connection type and connection condition of each edge respectively, and are stored in the form of two tuples. Figure 7 and Figure 8 The directed connection graph shown can be expressed as and First, candidate nodes are selected from the node sets V1 and V2 of G1 and G2 according to the forward search strategy for comparison, and it is determined whether the data tables represented by the candidate nodes are the same. If the data tables represented by the two adjacent candidate nodes traversed in sequence are the same, the edges between the two adjacent candidate nodes are further obtained from the edge sets E1 and E2 of G1 and G2, and it is determined whether the connection types and connection conditions represented by the edges between the two adjacent candidate nodes are the same. All nodes and edges in the node sets V1 and V2 and the edge sets E1 and E2 are traversed in sequence. If they are the same, it is determined that G1 and G2 are isomorphic.

[0081] In one embodiment, in step 130, matching the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs that are isomorphic may include:

[0082] Calculate the edit distance between any two directed connection graphs corresponding to any two SQL statements in the at least two SQL statements, and determine whether the edit distance between the any two directed connection graphs is zero. If so, identify that the any two directed connection graphs are isomorphic.

[0083] In this embodiment, the edit distance between any two directed graphs refers to the cost of the minimum sequence of edit operations required to convert one directed graph to the other. The edit distance between any two directed graphs can be calculated based on a conventional graph edit distance algorithm and will not be further described here.

[0084] In one embodiment, Figure 10As shown, in step 130, the target SQL statements associated with any group of directed connection graphs are fused and rewritten to obtain new SQL statements after fusion and rewriting, which may include:

[0085] Step 510: Detect whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results;

[0086] Step 520: If fusion is possible, the query relationship trees corresponding to the target SQL statement are fused to generate a fused query relationship tree corresponding to the target SQL statement, and the target SQL statement is rewritten based on the fused query relationship tree to obtain the new SQL statement.

[0087] In one embodiment, detecting whether the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery result in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery condition and the subquery result may include:

[0088] If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is an aggregate function, the subquery conditions are the same, and the subquery result has no inclusion relationship, then it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be fused.

[0089] In another embodiment, detecting whether the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery result in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery condition and the subquery result may include:

[0090] If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is a column field query, and the subquery condition and the subquery result have an inclusion relationship, it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged.

[0091] In this embodiment, for the above exemplary SQL statements SQL1 and SQL2, Figure 3 and Figure 4As can be seen, the subquery results of SQL1 and SQL2 involve the MIN(price) and MAX(price) aggregate functions, respectively. These functions group and aggregate the minimum and maximum values ​​of the price column in the Orders table by the key value sid. Furthermore, the subquery conditions for both SQL1 and SQL2 are type='1', meaning the minimum and maximum values ​​are calculated for the price column when the column type is 1. Therefore, there is no logical inclusion relationship between the subquery conditions and subquery results, meaning that result sets are not copied or deleted. Therefore, the two subqueries in SQL1 and SQL2 present performance issues due to duplication or redundancy. Therefore, they can be fused as a single subquery. In this case, the aggregate functions in the main and subquery results of SQL1 and SQL2 can be directly combined in the main and subquery results of the fused query relationship tree.

[0092] right Figure 3 and Figure 4 By fusing the query relationship tree shown, we can get Figure 11 The fusion query relationship tree shown in . Accordingly, from Figure 11 The fusion query relationship tree shown can rewrite the SQL1 and SQL2 to obtain a new SQL statement SQL3 after fusion and rewriting as shown below:

[0093] SQL3:

[0094] SELECT a.id, b.min_price,b.max_price FROM Suppliers AS a LEFT JOIN(SELECT sid, MIN(price) AS min_price, MAX(price) AS max_price FROM OrdersWHERE type = '1' GROUP BY sid) AS b ON a.id = b.sid.

[0095] Another fusion scenario can be explained with another set of exemplary SQL statements SQL4 and SQL5, as shown below:

[0096] SQL4:

[0097] SELECT a.id, b.price FROM Suppliers AS a LEFT JOIN (select sid, priceFROM Orders WHERE price<100) AS b on a.id = b.sid;

[0098] SQL5:

[0099] SELECT a.id, b.price FROM Suppliers AS a LEFT JOIN (select sid, priceFROM Orders WHERE price<100 and price>10) AS b on a.id = b.sid.

[0100] Then, according to the above exemplary SQL statements SQL4 and SQL5, a corresponding query relationship tree and a directed connection graph between the data tables of the main query statement and the sub-query statement constructed based on the query relationship tree can be constructed as follows: Figure 12 and Figure 13 As shown. Figure 12 and Figure 13 The directed connection graphs between the data tables of the main query and subquery statements shown are also isomorphic. Furthermore, we can determine that the query types of the subquery results in the query relationship trees corresponding to SQL4 and SQL5 both involve queries on the price column, and that the subquery conditions "price < 100" and "price < 100 and price > 10" in both cases have an inclusion relationship with the subquery results. This means that by further restricting the SQL4 subquery condition and subquery result to "price > 10," the subquery result in SQL5 can be projected. At this point, the two subqueries in SQL4 and SQL5 have duplication or redundancy performance issues and can therefore be merged as a single subquery.

[0101] In this case, the subquery result corresponding to SQL5 can be projected based on the subquery result of SQL4 by using a conditional function in the main query result of the fused query relationship tree.

[0102] right Figure 12 and Figure 13 By fusing the query relationship tree shown, we can get Figure 14 The fusion query relationship tree shown in FIG. Figure 14 In the fusion query relationship tree, the CASE condition function is added to the main query result. The function of this condition function is to realize the projection of the sub-query result corresponding to SQL5 based on the sub-query result of SQL4. Figure 14 The fusion query relationship tree shown can rewrite SQL4 and SQL5 to obtain a new SQL statement SQL6 after fusion and rewriting as shown below:

[0103] SQL6:

[0104] SELECT a.id, b.price,CASE WHEN b.price>10 THEN b.price ELSE NULL END

[0105] FROM Suppliers AS a LEFT JOIN (select sid, price FROM Orders WHEREprice<100) AS b on a.id = b.sid.

[0106] Figure 15 1 is a schematic diagram of a query optimization device for a structured query language according to the first embodiment of the present application. Figure 15 As shown, the device includes the following modules:

[0107] A query receiving module 610 is configured to receive at least two SQL statements for executing a data query on a database, and parse each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement;

[0108] A query parsing module 620 is configured to construct a query relationship tree corresponding to each SQL statement based on the main query statement and subquery statements contained in each SQL statement, and to create a directed connection graph between the data tables of the main query statement and the subquery statements contained in each SQL statement from the query relationship tree, wherein the nodes in the directed connection graph represent the data tables of the main query statement and the subquery statement, and the edges in the directed connection graph represent the connection relationships between the data tables of the main query statement and the subquery statement;

[0109] The query optimization module 630 is used to match the directed connection graph corresponding to each SQL statement to identify any group of directed connection graphs that are isomorphic, fuse and rewrite the target SQL statements associated with any group of directed connection graphs to obtain new SQL statements after fusion and rewriting, and execute data queries on the database based on the new SQL statements.

[0110] In one embodiment, the query parsing module 620 may also be used to:

[0111] Obtaining the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement from the query relationship tree corresponding to each SQL statement;

[0112] The directed connection graph is constructed based on the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement.

[0113] In one embodiment, the query parsing module 620 may also be used to:

[0114] Determining from the query relationship tree corresponding to each SQL statement whether the connection relationship between the data tables of the main query statement and the subquery statement is a dependent connection relationship, and if so, converting the connection relationship between the data tables of the main query statement and the subquery statement into a non-dependent connection relationship;

[0115] The query relationship tree is reconstructed according to the non-dependent connection relationship between the data tables of the main query statement and the sub-query statement to obtain a reconstructed query relationship tree.

[0116] In one embodiment, the query optimization module 630 may be used to:

[0117] From any two directed connection graph node sets corresponding to any two SQL statements of the at least two SQL statements, sequentially traverse and select candidate nodes according to a forward search strategy for comparison, and determine whether the data tables represented by the candidate nodes are the same;

[0118] If the data tables represented by two adjacent candidate nodes traversed in sequence are the same, then further determine whether the connection types and connection conditions represented by the edges between the two adjacent candidate nodes in the edge sets in any two directed connection graphs are the same, and traverse all nodes and edges in the node set and edge set in sequence. If they are all the same, then identify that the any two directed connection graphs are isomorphic.

[0119] In one embodiment, the query optimization module 630 may also be used to:

[0120] Calculate the edit distance between any two directed connection graphs corresponding to any two SQL statements in the at least two SQL statements, and determine whether the edit distance between any two directed connection graphs is zero. If so, identify that the any two directed connection graphs are isomorphic.

[0121] In one embodiment, the query optimization module 630 may also be used to:

[0122] Detecting whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results;

[0123] If fusion is possible, the query relationship trees corresponding to the target SQL statement are fused to generate a fused query relationship tree corresponding to the target SQL statement, and the target SQL statement is rewritten based on the fused query relationship tree to obtain a new SQL statement after fusion and rewriting.

[0124] In one embodiment, detecting whether the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery result in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery condition and the subquery result may include:

[0125] If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is an aggregate function, the subquery conditions are the same, and the subquery result has no inclusion relationship, then it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be fused.

[0126] In another embodiment, detecting whether the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery result in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery condition and the subquery result may include:

[0127] If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is a column field query, and the subquery condition and the subquery result have an inclusion relationship, it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged.

[0128] A query optimization method and device for a structured query language according to an embodiment of the present application receives at least two SQL statements for executing data queries on a database, parses each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement, constructs a query relationship tree corresponding to each SQL statement based on the main query statement and subquery statement contained in each SQL statement, creates a directed connection graph between data tables of the main query statement and the subquery statement contained in each SQL statement from the query relationship tree corresponding to each SQL statement, matches the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs belonging to the same structure, fuses and rewrites target SQL statements associated with any group of directed connection graphs to obtain a new SQL statement after fusion and rewriting, and executes data queries on the database based on the new SQL statement, thereby improving the query execution performance of batch complex SQL statements received in real time in enterprise-level big data search and analysis scenarios and reducing system response delay.

[0129] It should be noted that those skilled in the art will understand that the different implementation methods described in the method embodiments of the present application and their explanations and technical effects achieved are also applicable to the device embodiments of the present application and will not be repeated here.

[0130] Furthermore, an embodiment of the present application also provides an electronic device, which may include: a processor and a memory. The memory stores computer program instructions, and the processor can call the computer program instructions in the memory to execute all or part of the steps of the method described in any embodiment of the present application. The computer program instructions in the memory can be implemented in the form of a software functional unit and stored in a computer-readable storage medium when sold or used as a standalone product.

[0131] Furthermore, the present application also provides a computer program product, which includes a non-transitory computer-readable storage medium storing a computer program. When the computer-readable storage medium is connected to a computer device and the computer program is executed by one or more processors of the computer device, it can execute all or part of the steps of the method described in any embodiment of the present application.

[0132] Furthermore, the present application also provides a non-transitory computer-readable storage medium having a computer program stored thereon, which can be executed by one or more processors to perform all or part of the steps of the method described in any embodiment of the present application.

[0133] Through the description of the above embodiments, those skilled in the art can clearly understand that the various embodiments of the present application can be implemented by means of software or software combined with the necessary general hardware platform, and of course can also be implemented by hardware functions. Based on this understanding, the technical solution of the present application can be essentially or the part that contributes to the prior art can be embodied in the form of a software product, which is stored in a storage medium and includes a number of instructions for enabling a computer device, such as but not limited to a personal computer, a server, or a network device, to execute all or part of the steps of the method described in any embodiment of the present application. The aforementioned storage medium may include: various media that can store computer program code, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk or an optical disk.

[0134] While the exemplary embodiments of the present application have been described above, it should be understood that the exemplary embodiments are illustrative rather than restrictive, and the scope of protection of the present application is not limited thereto. It should be understood that those skilled in the art may modify and alter the embodiments of the present application without departing from the spirit and scope of the present application, and such modifications and alterations are intended to be within the scope of protection of the present application.

Claims

1. A query optimization method for structured query language, characterized in that: The method comprises: Receiving at least two SQL statements for executing data query on a database, and parsing each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement; Based on the main query statement and subquery statements included in each SQL statement, a query relationship tree corresponding to each SQL statement is constructed, and a directed connection graph between data tables of the main query statement and subquery statements included in each SQL statement is created from the query relationship tree corresponding to each SQL statement, wherein nodes in the directed connection graph represent data tables of the main query statement and subquery statements, and edges in the directed connection graph represent connection relationships between the data tables of the main query statement and subquery statements; The directed connection graph corresponding to each SQL statement is matched to identify any group of directed connection graphs that are isomorphic, the target SQL statements associated with the any group of directed connection graphs are fused and rewritten to obtain new SQL statements after fusion and rewriting, and data query is executed on the database based on the new SQL statements.

2. The query optimization method of structured query language according to claim 1, characterized in that: The step of creating a directed connection graph between data tables of a main query statement and subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement includes: Obtaining the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement from the query relationship tree corresponding to each SQL statement; The directed connection graph is constructed based on the data tables of the main query statement and the sub-query statement and the connection relationship between the data tables of the main query statement and the sub-query statement.

3. The query optimization method of structured query language according to claim 2, characterized in that: The step of creating a directed connection graph between data tables of a main query statement and subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement includes: Determining from the query relationship tree corresponding to each SQL statement whether the connection relationship between the data tables of the main query statement and the subquery statement is a dependent connection relationship, and if so, converting the connection relationship between the data tables of the main query statement and the subquery statement into a non-dependent connection relationship; The query relationship tree is reconstructed according to the non-dependent connection relationship between the data tables of the main query statement and the sub-query statement to obtain a reconstructed query relationship tree.

4. The query optimization method of structured query language according to claim 3, characterized in that: The matching of the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs belonging to the same structure includes: From any two directed connection graph node sets corresponding to any two SQL statements of the at least two SQL statements, sequentially traverse and select candidate nodes according to a forward search strategy for comparison, and determine whether the data tables represented by the candidate nodes are the same; If the data tables represented by two adjacent candidate nodes traversed in sequence are the same, then further determine whether the connection types and connection conditions represented by the edges between the two adjacent candidate nodes in the edge sets of any two directed connection graphs are the same, and traverse all nodes and edges in the node set and edge set in sequence. If they are all the same, then identify that the any two directed connection graphs are isomorphic.

5. The query optimization method of structured query language according to claim 3, characterized in that: The matching of the directed connection graphs corresponding to each SQL statement to identify any group of directed connection graphs belonging to the same structure includes: Calculate the edit distance between any two directed connection graphs corresponding to any two SQL statements in the at least two SQL statements, and determine whether the edit distance between the any two directed connection graphs is zero. If so, identify that the any two directed connection graphs are isomorphic.

6. The query optimization method of structured query language according to claim 4 or 5, characterized in that: The fusion rewriting of the target SQL statements associated with the arbitrary group of directed connection graphs to obtain a new SQL statement after fusion rewriting includes: Detecting whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged based on the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results; If fusion is possible, the query relationship trees corresponding to the target SQL statement are fused to generate a fused query relationship tree corresponding to the target SQL statement, and the target SQL statement is rewritten based on the fused query relationship tree to obtain the new SQL statement.

7. The query optimization method of structured query language according to claim 6, characterized in that: The detecting whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged according to the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results includes: If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is an aggregate function, the subquery conditions are the same, and the subquery result has no inclusion relationship, then it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be fused.

8. The query optimization method of structured query language according to claim 6, characterized in that: The detecting whether the subquery results and subquery conditions in the query relationship tree corresponding to the target SQL statement can be merged according to the query type of the subquery results in the query relationship tree corresponding to the target SQL statement and the logical relationship between the subquery conditions and the subquery results includes: If the query type of the subquery result in the query relationship tree corresponding to the target SQL statement is a column field query, and the subquery condition and the subquery result have an inclusion relationship, it is determined that the subquery result and the subquery condition in the query relationship tree corresponding to the target SQL statement can be merged.

9. The query optimization method of structured query language according to claim 8, characterized in that: The step of fusing the query relationship trees corresponding to the target SQL statements to generate a fused query relationship tree corresponding to the target SQL statements includes: In the main query result of the fused query relationship tree, a conditional function is used to project the subquery result of another target SQL statement based on the subquery result of one target SQL statement in the target SQL statements.

10. A query optimization device for structured query language, characterized in that: The device comprises: A query receiving module, configured to receive at least two SQL statements for executing data queries on a database, and parse each of the at least two SQL statements to obtain a main query statement and a subquery statement contained in each SQL statement; a query parsing module, configured to construct a query relationship tree corresponding to each SQL statement based on the main query statement and subquery statements contained in each SQL statement, and create a directed connection graph between the data tables of the main query statement and the subquery statements contained in each SQL statement from the query relationship tree corresponding to each SQL statement, wherein the nodes in the directed connection graph represent the data tables of the main query statement and the subquery statement, and the edges in the directed connection graph represent the connection relationship between the data tables of the main query statement and the subquery statement; A query optimization module is used to match the directed connection graph corresponding to each SQL statement to identify any group of directed connection graphs that are isomorphic, fuse and rewrite the target SQL statements associated with the any group of directed connection graphs to obtain new SQL statements after fusion and rewriting, and execute data queries on the database based on the new SQL statements.

Images