Query statement conversion method and device, equipment and storage medium

Abstract

The invention provides a query statement conversion method and device, equipment and a storage medium, and the method comprises the steps: obtaining a first query statement of a database, and when the first query statement comprises a target operation of a first table and a second table, determining whether to rewrite the first query statement according to the number of filtering conditions and the data size of the first query statement. If it is determined that the first query statement needs to be rewritten, the first query statement is rewritten, a second query statement with the query efficiency higher than that of the first query statement is obtained, and the second query statement is executed. Therefore, the query efficiency of the query statement is improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a query statement conversion method, device, equipment and storage medium. Background Art

[0002] The query statement of the database includes various query conditions. When the query device executes the query statement, it can judge whether the data in the query table meets the query conditions, and finally query to obtain the data that meets the conditions.

[0003] In the prior art, query conditions in some query statements include combining data in a table to be queried with data in other tables. This combination of data in a table to be queried with data in other tables in the query statement is called a set operation.

[0004] With the prior art, when a query device executes a query statement including a set operation, it needs to perform operations with a large amount of data and high complexity between different tables, resulting in a problem of low processing efficiency in executing the query statement including a set operation. Summary of the invention

[0005] The present application provides a query statement conversion method, apparatus, device and storage medium to solve the technical problem of low processing efficiency of query statements including set operations in the prior art.

[0006] The first aspect of the present application provides a query statement conversion method, comprising: obtaining a first query statement of a database; when the first query statement includes target operations of a first table and a second table, determining whether to rewrite the first query statement according to the number of filter conditions and the amount of data of the first query statement, the target operation includes a set operation, a semi-join operation, or an anti-join operation; if it is determined to rewrite the first query statement, rewriting the first query statement to obtain a second query statement, the query efficiency of the second query statement is higher than the query efficiency of the first query statement; executing the second query statement.

[0007] Optionally, the target operation is a set operation, and determining whether to rewrite the first query statement based on the number of filter conditions and the amount of data in the first query statement includes: when the number of filter conditions is less than or equal to a preset number threshold, and the amount of data is greater than or equal to a preset data amount threshold, determining to rewrite the first query statement. Rewriting the first query statement to obtain the second query statement includes: according to the type of the set operation, converting the set operation in the first query statement into an equivalent semi-join operation or anti-join operation to obtain the second query statement, and the type of the set operation includes a deduplication type and a non-deduplication type.

[0008] Optionally, according to the type of the set operation, the set operation in the first query statement is converted into an equivalent semi-join operation or anti-join operation to obtain the second query statement, including: when the type of the set operation is a deduplication type, the set operation is rewritten into a semi-join operation or anti-join operation including a deduplication operation to obtain the second query statement.

[0009] Optionally, the rewriting of the set operation into a semi-join operation or anti-join operation including a deduplication operation to obtain the second query statement includes: when the set operation is a difference operation, rewriting the difference operation into an anti-join operation including a deduplication operation to obtain the second query statement; or, when the set operation is an intersection operation, rewriting the intersection operation into a semi-join operation including a deduplication operation to obtain the second query statement.

[0010] Optionally, rewriting the difference operation into an anti-join operation including a deduplication operation to obtain the second query statement includes: when at least one target column in the first query statement is a column that does not filter null values, converting the difference operation into an anti-join operation including a deduplication operation; using a logical operation function for processing null values ​​to encapsulate the equality condition of the target column to obtain the second query statement.

[0011] Optionally, according to the type of the set operation, the set operation in the first query statement is converted into an equivalent semi-join operation or anti-join operation to obtain the second query statement, including: when the type of the set operation is a non-deduplication type and the set operation is an intersection operation, the intersection operation is rewritten into a semi-join operation that does not include a deduplication operation to obtain the second query statement.

[0012] Optionally, the set operation is rewritten into a semi-join operation or anti-join operation including a deduplication operation to obtain the second query statement, including: taking the target column of the first branch of the set operation as a condition, and constructing a first equi-join condition with the target columns of the remaining branches of the set operation; according to the first equi-join condition, obtaining a sub-query of the semi-join operation or the anti-join operation, and the target column of the sub-query is a default value; mounting the sub-query after the conditional clause of the first branch to obtain a candidate second query statement; adding a deduplication operation to the target column of the candidate second query statement to obtain the second query statement.

[0013] Optionally, the intersection operation is rewritten into a semi-join operation that does not include a deduplication operation to obtain the second query statement, including: taking the target column of the first branch of the intersection operation as a condition, and constructing a second equi-join condition with the target columns of the remaining branches of the intersection operation; according to the second equi-join condition, obtaining a sub-query of the semi-join operation, the target column of the sub-query being a default value; and mounting the sub-query after the conditional clause of the first branch to obtain the second query statement.

[0014] Optionally, the target operation is a semi-join operation or an anti-join operation, and determining whether to rewrite the first query statement according to the number of filter conditions and the amount of data of the first query statement includes: when the number of filter conditions is greater than a preset number threshold and the amount of data is less than a preset data amount threshold, determining to rewrite the first query statement. Rewriting the first query statement to obtain the second query statement includes: according to the type of the first query statement, rewriting the semi-join operation or the anti-join operation into an equivalent set operation to obtain the second query statement, and the type of the first query statement includes a deduplication type and a non-deduplication type.

[0015] Optionally, according to the type of the first query statement, the semi-join operation or the anti-join operation is rewritten into an equivalent set operation to obtain the second query statement, including: generating a target branch according to an equivalent condition related to a parent query in the semi-join operation or the anti-join operation, wherein a target column of the target branch is the equivalent condition; constructing a set operation equivalent to the semi-join operation or the anti-join operation according to a subquery corresponding to the semi-join operation or the anti-join operation and the target branch; and adjusting the type of the set operation according to the type of the first query statement to obtain the second query statement.

[0016] The second aspect of the present application provides a query statement conversion device, including: an acquisition module, used to acquire a first query statement of a database; a determination module, used to determine whether to rewrite the first query statement according to the number of filtering conditions and the amount of data of the first query statement when the first query statement includes target operations of a first table and a second table, and the target operation includes a set operation, a semi-join operation, or an anti-join operation; a processing module, used to rewrite the first query statement if it is determined to rewrite the first query statement to obtain a second query statement, and the query efficiency of the second query statement is higher than the query efficiency of the first query statement; an execution module, used to execute the second query statement.

[0017] The third aspect of the present application provides a query statement conversion device, comprising: a memory and a processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory, so that the data query device executes the query statement conversion method as described in any one of the first aspects of the present application.

[0018] A fourth aspect of the present application provides a computer-readable storage medium storing computer-executable instructions, which, when executed, implement the query statement conversion method as described in any one of the first aspects of the present application.

[0019] A fifth aspect of the present application provides a computer program product, including a computer program, which, when executed, implements the query statement conversion method as described in any one of the first aspects of the present application.

[0020] In summary, the query statement conversion method, device, equipment and storage medium provided by the present application obtain a first query statement of a database, and when the first query statement includes target operations of a first table and a second table, determine whether to rewrite the first query statement according to the number of filter conditions and the amount of data of the first query statement. If it is determined that the first query statement needs to be rewritten, the first query statement is rewritten to obtain a second query statement with a higher query efficiency than the first query statement, and the second query statement is executed. Thereby, the query efficiency of the query statement is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0021] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative labor.

[0022] Figure 1 A schematic diagram of the application scenario of this application;

[0023] Figure 2 A flowchart of a query statement conversion method provided in an embodiment of the present application;

[0024] Figure 3 A flowchart of another query statement conversion method provided in an embodiment of the present application;

[0025] Figure 4 A flowchart of another query statement conversion method provided in an embodiment of the present application;

[0026] Figure 5 A flowchart of another query statement conversion method provided in an embodiment of the present application;

[0027] Figure 6 A flowchart of another query statement conversion method provided in an embodiment of the present application;

[0028] Figure 7 A flowchart of another query statement conversion method provided in an embodiment of the present application;

[0029] Figure 8 A schematic diagram of a query statement conversion device provided by the present application;

[0030] Fig. 9 A structural diagram of a query statement conversion device provided in this application. DETAILED DESCRIPTION

[0031] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0032] The terms "first", "second", "third", "fourth", etc. (if any) in the specification and claims of the present application and the above-mentioned drawings are used to distinguish similar objects, and are not necessarily used to describe a specific order or sequence. It should be understood that the data used in this way can be interchangeable where appropriate, so that the embodiments of the present application described herein can be implemented in an order other than those illustrated or described herein, for example. In addition, the terms "including" and "having" and any of their variations are intended to cover non-exclusive inclusions, for example, a process, method, system, product or device comprising a series of steps or units is not necessarily limited to those steps or units clearly listed, but may include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0033] Figure 1 This is a schematic diagram of the application scenario of this application, such as Figure 1 As shown, the present application is applied in the technical field of databases. The database 20 may be a device that can be used to store data, such as an SQL database, an Oracle database, a Kingbase database, etc., and the present application embodiment does not limit the type of the database 20. The electronic device 10 may be a computer, a server, a workstation, etc., and the electronic device 10 may communicate with the database 20.

[0034] In one application scenario, a user may send a data query statement to the electronic device 10, and the data query statement is used to query the database 20 for data that meets the conditions. The electronic device 10 then obtains data from the database 20 according to the data query statement and returns the data to the user who needs to query the data.

[0035] In recent years, with the development of new database application scenarios such as mobile Internet, big data, artificial intelligence, and new database hardware technologies, the scale and complexity of databases have increased significantly, the amount of data that needs to be processed has become larger and larger, and the efficiency of devices in database management systems when processing query statements has become more and more serious.

[0036] At present, when the electronic device 10 executes some query statements including set operations (such as intersect, except, etc.), for the branches of the set operation, each branch can only be used as a subquery, and finally the result set of each subquery is processed to obtain the result set of the set operation. For example, taking the query statement including the set operation as Select*from t1except select*from t2 as an example, the execution plan of the query statement can be as follows:

[0037] HashSetOp Except

[0038] ->Append

[0039] ->Subquery Scan on "*SELECT*1"

[0040] ->Seq Scan on t1

[0041] ->Subquery Scan on "*SELECT*2"

[0042] ->Seq Scan on t2

[0043] That is, each branch of the set operation of the query statement (each branch is a subquery) is queried respectively, and after obtaining the result set of each subquery, the set operation is processed on the result set of each subquery to obtain the result set of the set operation.

[0044] However, currently, when executing a query statement including a set operation, operations with a large amount of data and high complexity need to be performed between different tables, resulting in a problem of low processing efficiency in executing the query statement including a set operation.

[0045] In addition, when the electronic device 10 executes some query statements including an exists subquery and a not exists subquery, the exists subquery can be promoted to a semi join and the not exists subquery can be promoted to an antijoin, so that the database can more effectively reduce the amount of data to be processed. For example, taking semi join as an example, its main focus is whether there are matching rows in a table in another table, without having to process a complete connection result set like a normal join. For example, in a query statement, if there is an exists subquery used to check whether a record in a table has a corresponding record in another table, after converting it to a semi join, the database can quickly determine the existence directly based on optimization means such as indexes, without having to traverse a large amount of unnecessary data, thereby improving processing efficiency.

[0046] For example, taking the query statement including the exists subquery as Select*from t1 where exists(select 1from t2 where t1.id=t2.id) as an example, the exists subquery can be promoted to semi join to improve processing efficiency. The promoted execution plan can be as follows:

[0047] Hash Semi Join

[0048] Hash Cond:(t1.id=t2.id)

[0049] ->Seq Scan on t1

[0050] ->Hash

[0051] ->Seq Scan on t2

[0052] However, when using an exists subquery, when the exists subquery uses nodes in the parent query (such as tables, columns, and other elements), the nodes where the exists subquery is located will establish a binding relationship with the nodes in the parent query. That is, the execution result of the subquery depends on the values ​​of these nodes in the parent query. Because of the binding relationship at this time, when executing the query, you must first determine the values ​​of the relevant nodes in the parent query, and then execute the subquery based on these values, resulting in the problem that query efficiency cannot be improved through parallel execution.

[0053] The inventor of the present application has found that set operations and semi-joins or anti-joins can be equivalently converted to improve data processing efficiency by rewriting the set operations in the query statement into semi-joins or anti-joins when executing set operations with large data volumes and high complexity. When using exists subqueries to implement semi-joins and not exists subqueries to implement anti-joins, and when parallel execution is needed to improve query efficiency, semi-joins or anti-joins are rewritten into set operations to improve query efficiency through parallel execution.

[0054] In view of this, the present application provides a query statement conversion method, which can determine whether the first query statement needs to be rewritten according to the number of filtering conditions and data volume of the first query statement when processing the first query statement of the database, so as to realize the conversion between set operation, sum, semi-join operation or anti-join operation, and rewrite the first query statement into a second query statement with higher query efficiency than the first query statement based on the conversion, thereby improving the execution efficiency of the query statement.

[0055] The technical solution of the present application is described in detail with specific embodiments below. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be described in detail in some embodiments. Figure 2 A flowchart of a query statement conversion method provided in an embodiment of the present application. Figure 2 The method shown can be applied to Figure 1 In the scenario shown, it is executed by an electronic device. Specifically, Figure 2 The query statement conversion methods shown include:

[0056] S201: Obtain a first query statement from a database.

[0057] The electronic device may receive a query statement indicated by a user, or the electronic device may receive a query statement sent by another device. In this embodiment, the query statement received by the electronic device is recorded as the first query statement. In one embodiment, the query statement may be a Structured Query Language (SQL) statement.

[0058] S202: When the first query statement includes target operations on the first table and the second table, determine whether to rewrite the first query statement according to the number of filtering conditions and the amount of data in the first query statement.

[0059] The target operation includes a set operation, a semi-join operation, or an anti-join operation. The set operation includes an intersection operation (intersect) and a difference operation (except or minus). The intersection operation finds data that exists in both tables; the difference operation finds data that exists in one table but not in the other.

[0060] In a database, a table is the basic structure for data storage, which includes rows and columns. The first table and the second table are the two tables involved in the first query statement. For example, in the query statement "Select * from table1 join table2 on table1.id = table2.id", table1 is the first table and table2 is the second table.

[0061] Semi join is mainly used to obtain records from one table (such as the first table) that have matching rows in another table (the second table), but only returns the relevant columns in the first table. It focuses on existence, that is, judging whether some rows in the first table have corresponding rows in the second table. In SQL, semi join can usually be implemented using the exists subquery. For example, "Select * from table1 where exists (select 1 from table2 where table1.id = table2.id)" is a semi join operation implemented by the exists subquery, which is used to return rows in table1 that have matching ids in table2.

[0062] The anti-join operation is used to find records that exist in one table (the first table) but not in another table (the second table). This can be achieved using a not exists subquery. For example, "Select * from table1 where not exists (select 1 from table2 where table1.id = table2.id)" is an anti-join operation implemented by a not exists subquery, which is used to return rows in table1 that do not have a matching id in table2.

[0063] The filter condition of the first query statement is the condition used to limit the returned results in the query statement. For example, in the query statement "Select*from table1 where column1>10and column2='abc'", "column1>10" and "column2='abc'" are the filter conditions. In the query statement, the number of filter conditions will affect the execution efficiency of the query.

[0064] The data volume of the first query statement refers to the number of data records in the first table and the second table. The data volume has a great impact on query efficiency. A large amount of data may lead to a long query execution time, especially when complex operations (such as join operations, set operations, etc.) are involved.

[0065] The inventor of the present application has found that when the target operation is a set operation, if there are fewer filtering conditions and a larger amount of data, rewriting the set operation into a semi-join operation or an anti-join operation can reduce unnecessary data comparison and processing, thereby improving query efficiency. When the target operation is a semi-join operation or an anti-join operation, if there are more filtering conditions in the subquery that implements the semi-join operation or the anti-join operation and the amount of data is smaller, rewriting the subquery that implements the semi-join operation or the anti-join operation into a set operation can achieve parallel execution of the database, thereby improving query efficiency.

[0066] Therefore, when the first query statement includes target operations of the first table and the second table, it can be determined whether the first query statement needs to be rewritten based on the number of filter conditions and the amount of data of the first query statement, so as to achieve the purpose of improving query efficiency. For example, a threshold value of the number of filter conditions and a data amount threshold value can be set, and it can be determined whether the first query statement needs to be rewritten based on the comparison result of the number of filter conditions and the threshold value of the number of filter conditions, and the comparison result of the amount of data and the threshold value of the data amount. Alternatively, when the query execution time of the first query statement exceeds the preset time threshold, the query execution can be stopped, and the query can be executed again after the first query statement is rewritten.

[0067] If it is determined that the first query statement needs to be rewritten, step S203 is executed to rewrite the first query statement into a second query statement with higher query efficiency than the first query statement. If it is determined that the first query statement does not need to be rewritten, step S205 is executed to directly execute the first query statement for querying.

[0068] S203: Rewrite the first query statement to obtain a second query statement.

[0069] The query efficiency of the second query statement is higher than the query efficiency of the first query statement.

[0070] In one embodiment, after obtaining the first query statement, the processing of the first query statement may include at least the following stages: first, the first query statement is parsed, then the first query statement is logically and physically optimized by the optimizer to obtain the second query statement, and finally the optimized second query statement is executed by the executor. Then the above step S203 of the present application corresponds to the stage in which the optimizer optimizes the first query statement to obtain the second query statement after obtaining the first query statement and before the executor executes the second query statement.

[0071] Specifically, in S203, the target operation included in the first query statement may be rewritten specifically according to the content in the first query statement. That is, the set operation between the first table and the second table may be rewritten as a semi-join operation or an anti-join operation specifically according to the content in the first query statement, or the semi-join operation or anti-join operation between the first table and the second table may be rewritten as a set operation according to the content in the first query statement, so as to improve the query efficiency.

[0072] In one embodiment, the semi-join operation or anti-join operation in the first query statement is implemented by an exists subquery or a not exists subquery. Alternatively, the semi-join operation or anti-join operation can also be implemented by any, in, some and other subqueries.

[0073] S204: Execute the second query statement.

[0074] Specifically, after the optimizer completes processing of the first query statement to obtain the second query statement in S203, the executor may execute the second query statement optimized by the optimizer.

[0075] In summary, the query statement connection conversion method provided in this embodiment obtains a first query statement of a database, and when the first query statement includes target operations of a first table and a second table, determines whether to rewrite the first query statement according to the number of filter conditions and the amount of data of the first query statement. If it is determined that the first query statement needs to be rewritten, the first query statement is rewritten to obtain a second query statement with a higher query efficiency than the first query statement, and the second query statement is executed. Thereby, the query efficiency of the query statement is improved.

[0076] Below, first taking the target operation in the first query statement as a set operation as an example, how to determine whether to rewrite the first query statement based on the number of filtering conditions and data volume of the first query statement in the aforementioned step S202, and how to rewrite the first query statement in the aforementioned step S203 to obtain the second query statement are explained in detail.

[0077] Figure 3 A flowchart of another query statement conversion method provided in an embodiment of the present application. Figure 3 As shown, the method may specifically include:

[0078] S301: When the number of filtering conditions is less than or equal to a preset number threshold and the amount of data is greater than or equal to a preset data amount threshold, determine to rewrite the first query statement.

[0079] The preset number threshold and the preset data volume threshold may be determined according to historical query records, which may include a large number of historical query statements, query execution time of historical query statements, number of filter conditions corresponding to historical query statements, and data volume.

[0080] For example, based on the query execution time, number of filter conditions, and data volume corresponding to the historical query records, it is possible to determine that when the query execution time of a historical query statement using a set operation is greater than or equal to the query execution time of a historical query statement using a semi-join or anti-join operation, the corresponding number of filter conditions and data volume, and the number of filter conditions is used as a preset number threshold, and the data volume is used as a preset data volume threshold.

[0081] Based on the comparison between the number of filtering conditions included in the first query statement and the preset number threshold, determine whether the number of filtering conditions is less than or equal to the preset number threshold; based on the comparison between the data volume of the first query statement and the preset data volume threshold, determine whether the data volume is greater than or equal to the preset data volume threshold.

[0082] If the number of filter conditions is less than or equal to the preset number threshold and the data volume is greater than or equal to the preset data volume threshold, it can be determined that the first query statement including the set operation needs to be rewritten into a query statement implemented by a semi-join or anti-join operation to reduce the query execution time and improve query efficiency, and step S302 is executed.

[0083] If the number of filter conditions is greater than the preset number threshold and the data volume is less than the preset data volume threshold, it can be determined that the first query statement including the set operation does not need to be rewritten into a query statement implemented by a semi-join or anti-join operation, so as to avoid the problem of reduced query efficiency due to the inability to execute in parallel after rewriting. Step S303 is executed to execute the first query statement.

[0084] S302: According to the type of the set operation, convert the set operation in the first query statement into an equivalent semi-join operation or anti-join operation to obtain a second query statement.

[0085] The types of set operations include deduplication type and non-deduplication type. Specifically, when the set operation is intersect or except, its type is deduplication type; when the set operation includes the keyword all, such as intersect all or except all, its type is non-deduplication type.

[0086] Different types of set operations have different ways of converting them into semi-join operations or anti-join operations. Therefore, it is necessary to determine a rewriting method according to the type of set operation to convert the set operation in the first query statement into an equivalent semi-join operation or anti-join operation.

[0087] Among them, when the set operation is except all, it represents subtracting the records in the second table from the first table. If there are duplicate records in the second table, except all only performs a subtraction operation once and retains the remaining values. For example, if the records in the first table are {null,1,1} and the records in the second table are {1}, the result set of executing except all is {null,1}. If except all is rewritten as an anti-join operation, the anti-join operation will select records that are not in the subquery. For the example in which the records in the first table are {null,1,1} and the records in the second table are {1}, the anti-join operation is {null,1,1}not exists{1}={null}. Therefore, when the set operation is except all, there is no equivalent semi-join operation or anti-join operation, so except all is not rewritten.

[0088] The following describes in detail how to convert the set operation in the first query statement into an equivalent semi-join operation or anti-join operation to obtain the second query statement for different types of set operations.

[0089] Case 1: When the type of the set operation is a deduplication type, the set operation is rewritten as a semi-join operation or an anti-join operation including a deduplication operation to obtain a second query statement.

[0090] When the type of the set operation is a deduplication type, the set operation may include an intersection operation, an except operation, or a minus operation.

[0091] When the set operation is a difference operation except or minus:

[0092] Since the semantics of the difference operation (except or minus) is to find the elements (or rows) that exist in one set (or table) but not in another set (or table), the semantics of the anti-join operation is also to find the records that exist in one table but not in the other. Therefore, the semantics of the two are the same, so the difference operation can be rewritten as the anti-join operation.

[0093] Secondly, in database operations, since there may be duplicate rows in the data in the table, when performing the difference operation except or minus, the difference operation will deduplicate the data. Therefore, when converting the difference operation to an anti-join operation, it is necessary to add a deduplication operation to the rewritten query statement so that after the difference operation except or minus is converted to an anti-join operation, the result set output by the query statement is the same.

[0094] Therefore, the difference operation can be rewritten as an anti-join operation including a deduplication operation to obtain a second query statement.

[0095] For example, taking the first query statement Select distinct id from table1 except selectid from table2 as an example, the second query statement obtained by rewriting the difference operation into an anti-join operation including a deduplication operation can be Select distinct t1.id from table1 t1 where not exists(select 1fromtable2 t2 where t1.id=t2.id). Among them, not exists(select 1fromtable2 t2 where t1.id=t2.id) in the second query statement is an anti-join operation, and distinct is a deduplication operation in the second query statement, so that after the difference operation except is converted into an anti-join operation, the result set output by the second query statement is the same as that of the first query statement.

[0096] When the set operation is the intersection operation intersect:

[0097] Since the semantics of the intersection operation is to find the elements (or rows) that exist in both sets (or tables), the semi-join operation can be used to determine whether the rows in one table have corresponding rows in the other table. Therefore, the semantics of the two operations are similar, so the difference operation can be rewritten as a semi-join operation through appropriate construction.

[0098] In addition, similar to the difference operation, since there may be duplicate data in the table, the intersection operation itself returns the common elements in the two sets without duplication. When it is rewritten as a semi-join operation, in order to ensure that the result is consistent with the semantics of the intersection operation, it is necessary to add a deduplication operation to the rewritten query statement.

[0099] Therefore, the intersection operation can be rewritten as a semi-join operation including a deduplication operation to obtain the second query statement.

[0100] For example, taking the first query statement as Select distinct id from table1 intersectselect id from table2 as an example, the second query statement obtained by rewriting the intersection operation into a semi-join operation including a deduplication operation can be Select distinct t1.id from table1 t1 where exists(select1from table2 t2 where t1.id=t2.id). Among them, exists(select 1fromtable2t2 where t1.id=t2.id) in the second query statement is a semi-join operation, and distinct is a deduplication operation in the second query statement, so that after the intersection operation intersect is converted into a semi-join operation, the result set output by the second query statement is the same as that of the first query statement.

[0101] Case 2: When the type of the set operation is a non-deduplication type and the set operation is an intersection operation, the intersection operation is rewritten as a semi-join operation that does not include a deduplication operation, and the second query statement is obtained.

[0102] When the type of the set operation is a non-deduplication type and the set operation is an intersection operation, the first query statement is a query statement including intersect all. In this case, the intersection operation is rewritten as a semi-join operation in a similar manner to the above-mentioned method of rewriting the intersection operation as a semi-join operation when the set operation is the intersection operation intersect. The only difference is that in this case, there is no need to add the deduplication operation distinct to the rewritten query statement, which will not be repeated here.

[0103] In one possible implementation, when the set operation is a difference operation except or minus, if there is a null value (null) in the first table or the second table, the difference operation handles the null value in the same way as the non-null value, that is, the null value will be treated as a non-null value for comparison.

[0104] However, when the exists subquery encounters a null value, since exists mainly focuses on whether the subquery returns rows, and null values ​​are considered to not meet the condition in the condition judgment, the exists subquery will directly return false. Correspondingly, since not exists is the negation of exists, when exists returns false due to a null value, not exists will return true.

[0105] Therefore, when the difference operation is rewritten as an anti-join operation implemented by a not exists subquery, if the values ​​of both tables in the result set of the difference operation are null, the not exists subquery should return false to match the result set logic of the difference operation. If no adaptive adjustment is performed, the not exists subquery will return true, which is inconsistent with the result set logic of the difference operation.

[0106] Therefore, the method provided in the embodiment of the present application may also include content of adaptively adjusting the not exists subquery when the first query statement does not have a null value filtering operation. Figure 4 A flowchart of another query statement conversion method provided in an embodiment of the present application is shown below. Figure 4 As shown, the method may also include:

[0107] S401: When at least one target column in the first query statement is a column that does not filter null values, convert a difference operation into an anti-join operation including a deduplication operation.

[0108] Optionally, the query conditions of the target column (i.e., the column selected by Select) can be analyzed to determine whether the first query statement includes a target column that does not filter null values. Specifically, the conditions related to the target column in the where clause in the first query statement can be directly checked. If there is no judgment on the null value in the target column (such as not null constraints or other logics that indirectly exclude null values, such as implicit non-null conditions such as id>0), the target column can be considered as a target column that does not filter null values.

[0109] Alternatively, it is possible to determine whether the target column has a not null constraint based on metadata of the table (such as the first table and the second table) involved in the first query statement. If not, the target column can be considered as a target column that does not filter null values.

[0110] When at least one target column in the first query statement is a column that does not filter null values, in order to prevent the difference operation caused by null values ​​from being rewritten as an anti-join operation, which would cause the result set logics before and after the rewrite to be inconsistent, it is necessary to first convert the difference operation into an anti-join operation including a deduplication operation, and then execute step S402 to adaptively adjust the converted anti-join operation so that the result set logics before and after the rewrite are consistent.

[0111] S402: Use a logical operation function for processing null values ​​to encapsulate the equal value condition of the target column to obtain a second query statement.

[0112] The logical operation function used to handle null values ​​is the not lnnvl function. The not lnnvl function has special logic, and it can return results according to specific rules even if there is a null value in the conditional expression. For example, in the expression notlnnvl(t1.id=t2.id), when t1.id or t2.id is null, the not lnnvl function will handle the null value according to its internally defined logic.

[0113] For example, the first query statement is Select pk,id from t1 except select pk,idfrom t2, where it is assumed that the pk column has a not null constraint and the id column does not have a not null constraint. At this time, the target columns include the pk column and the id column. The query statement obtained by converting the difference operation except in the first query statement into an anti-join operation including a deduplication operation may be Select distinct pk,id from t1 where not exists(select 1fromt2 where t1.pk=t2.pk and t1.id=t2.id). Among them, the equivalent condition of the target column is t1.id=t2.id, and the equivalent condition of the target column is encapsulated using a logical operation function for processing null values, which may be not lnnvl(t1.id=t2.id). Based on this, the second query statement finally rewritten is Select distinct pk,id from t1 wherenot exists(select 1from t2 where t1.pk=t2.pk and not lnnvl(t1.id=t2.id)).

[0114] Through this method, when at least one target column in the first query statement is a column that does not filter null values, it can be prevented that the difference set operation caused by null values ​​is rewritten as an anti-join operation, and the problem of logical inconsistency of the result sets before and after the rewriting occurs, thereby achieving the purpose of converting the difference set operation into an anti-join operation, thereby improving query efficiency.

[0115] Below, taking the deduplication scenario as an example, how to rewrite the first query statement to obtain the second query statement is described in detail. Figure 5 A flowchart of another query statement conversion method provided in an embodiment of the present application is shown below. Figure 5 As shown, the method may specifically include:

[0116] S501. Use the target column of the first branch of the set operation as a condition to construct a first equal-value join condition with the target columns of the remaining branches of the set operation.

[0117] In set operations (such as union, intersect, except), when rewriting query statements, in order to convert the set operation into a semi-join or anti-join operation, it is necessary to establish appropriate join conditions.

[0118] Specifically, the semantics of set operations can be simulated by establishing an equijoin. That is, the target column of the first branch of the set operation is used as the basis to construct an equijoin condition with the target columns of the remaining branches to simulate the semantics of the set operation. For example, taking the difference operation (except) as an example, the difference operation is to find elements that exist in the first set but not in the second set. By constructing an equijoin condition for the corresponding target columns in the two sets, the foundation can be laid for the subsequent conversion to an anti-join operation. Because the anti-join operation (such as using not exists) is also based on a certain existence judgment, and this equijoin condition can determine the corresponding relationship between elements in different sets during the judgment process.

[0119] For example, assume that the original query statement is Select id,name from table1 except selectid,name from table2, where table1 is the first table, table2 is the second table, and id and name are target columns. The target columns id and name of the first branch are used as conditions, and the first equal join condition table1.id=table2.id and table1.name=table2.name is constructed with the target columns id and name of the remaining branches of the set operation (i.e. id and name in except selectid,name).

[0120] S502: Obtain a subquery for a semi-join operation or an anti-join operation according to the first equi-join condition.

[0121] The target column of the subquery is the default value.

[0122] Based on the equijoin conditions constructed previously, you can construct a subquery for semijoin or antijoin operations. For semijoin operations (such as using exists) or antijoin operations (such as using not exists), the subquery only needs to determine whether there are rows that meet the equijoin conditions, so the value of the target column is not important. Therefore, you can set the target column of the subquery to the default value (for example, it can be set to 1 in SQL) to simplify the structure of the query statement.

[0123] Continuing with the example of S501, the difference operation is converted into an anti-join operation. According to the equi-join condition table1.id=table2.id and table1.name=table2.name, the anti-join subquery constructed is notexes(select 1from table2 where table1.id=table2.id and table1.name=table2.name).

[0124] S503: Mount the subquery on the conditional clause of the first branch to obtain a candidate second query statement.

[0125] In one embodiment, the conditional clause may be, for example, a where clause.

[0126] The purpose of mounting the constructed subquery after the conditional clause of the first branch is to integrate the logic of the semi-join or anti-join operation into the original first branch query. This mounting method enables the first branch in the original query to be associated with other branches through the newly constructed subquery, thereby simulating the semantics of set operations.

[0127] Continuing with the previous example, after the anti-join subquery is mounted on the condition clause of the first branch, the candidate second query statement can be Select id,name from table1 where not exists(select1fromtable2 where table1.id=table2.id and table1.name=table2.name).

[0128] S504: Add a deduplication operation to the target column of the candidate second query statement to obtain the second query statement.

[0129] because Figure 5 In the provided embodiments, all set operations are deduplication type set operations, i.e., intersect and except. After the set operation is converted to a semi-join or anti-join operation, in order to maintain semantic consistency with the original set operation, a deduplication operation needs to be added to the target column. Because duplicate data may be introduced during the conversion process, the deduplication operation can ensure that the result is semantically equivalent to the original set operation.

[0130] Continuing with the previous example, based on the candidate second query statement Select id,name from table1 where not exists(select 1from table2 where table1.id=table2.id and table1.name=table2.name), after adding the deduplication operation, the second query statement can be Select distinct id,name fromtable1 where not exists(select 1from table2 where table1.id=table2.id andtable1.name=table2.name).

[0131] In the following, taking the scenario without deduplication as an example, how to rewrite the first query statement to obtain the second query statement is described in detail. Figure 6 A flowchart of another query statement conversion method provided in an embodiment of the present application is shown below. Figure 6 As shown, the method may specifically include:

[0132] S601. Use the target column of the first branch of the intersection operation as a condition to construct a second equal-value join condition with the target columns of the remaining branches of the intersection operation.

[0133] In this step, except that the set operation is an intersection operation and the names of the second equivalue connection condition and the first equivalue connection condition are different, the actual implementation method is the same as the aforementioned step S501, which will not be repeated here.

[0134] S602: Obtain a subquery for a semi-join operation according to the second equi-join condition.

[0135] This step is implemented in the same manner as the method of obtaining the subquery of the semi-join operation according to the first equal join condition in the aforementioned step S602, and will not be described in detail here.

[0136] S603: Mount the subquery after the conditional clause of the first branch to obtain a second query statement.

[0137] The constructed semi-join subquery is mounted after the conditional clause of the first branch in order to integrate the logic of the semi-join operation into the original first branch query. In this way, a new query statement is constructed, which simulates the semantics of the original intersection operation through the semi-join operation.

[0138] In this scenario, the intersection operation is a set operation without duplicate removal, that is, intersect all. Therefore, Figure 6 The method shown does not require Figure 5 In the method shown, it is necessary to further add a deduplication operation to generate the second query statement. Therefore, after the subquery is mounted on the conditional clause of the first branch, the second query statement can be obtained.

[0139] Optionally, when the first query statement includes multiple set operation branches, all branches included in the first query statement may be traversed, and each branch may be rewritten in turn through the above steps S501-S504 or steps S601-S603.

[0140] Below, first taking the target operation in the first query statement as a semi-join or anti-join operation as an example, how to determine whether to rewrite the first query statement based on the number of filtering conditions and data volume of the first query statement in the aforementioned step S202, and how to rewrite the first query statement in the aforementioned step S203 to obtain the second query statement are explained in detail.

[0141] Figure 7 A flowchart of another query statement conversion method provided in an embodiment of the present application is shown below. Figure 7 As shown, the method may specifically include:

[0142] S701: When the number of filtering conditions is greater than a preset number threshold and the amount of data is less than a preset data amount threshold, determine to rewrite the first query statement.

[0143] The preset quantity threshold and the preset data volume threshold may be obtained by the method for determining the preset quantity threshold and the preset data volume threshold in the aforementioned step S301, which will not be described in detail here.

[0144] By comparing the number of filtering conditions included in the first query statement with the preset number threshold, determine whether the number of filtering conditions is greater than the preset number threshold; by comparing the data volume of the first query statement with the preset data volume threshold, determine whether the data volume is less than the preset data volume threshold.

[0145] If the number of filter conditions is greater than the preset number threshold and the data volume is less than the preset data volume threshold, it can be determined that the first query statement including the semi-join or anti-join operation needs to be rewritten into a query statement implemented by set operations to achieve parallel execution of the database, reduce query execution time, and improve query efficiency, and execute step S702.

[0146] If the number of filter conditions is less than or equal to the preset number threshold and the data volume is greater than or equal to the preset data volume threshold, it can be determined that the first query statement including the semi-join or anti-join operation does not need to be rewritten as a query statement implemented by a set operation, so as to avoid the problem of reduced query efficiency due to the large amount of query data generated by the set operation after the rewrite, and execute step S703 to execute the first query statement.

[0147] S702: According to the type of the first query statement, rewrite the semi-join operation or the anti-join operation into an equivalent set operation to obtain a second query statement.

[0148] The type of the first query statement includes a deduplication type and a non-deduplication type.

[0149] The method of rewriting the semi-join operation or anti-join operation into an equivalent set operation to obtain the second query statement can be achieved by rewriting the set operation into an equivalent semi-join operation or anti-join operation in the aforementioned embodiment in reverse, and various situations are not described here.

[0150] Specifically, the implementation method of rewriting the semi-join operation or anti-join operation into an equivalent set operation may refer to the following sub-steps:

[0151] S7021. Generate a target branch according to an equivalent condition related to the parent query in a semi-join operation or an anti-join operation, wherein a target column of the target branch is the equivalent condition.

[0152] The purpose of this step is to extract key information from the semi-join operation or anti-join operation, that is, the equivalent condition related to the parent query, and create a target branch based on it. This target branch uses the equivalent condition as its target column to prepare for the subsequent construction of an equivalent set operation.

[0153] For example, assume that the first query statement is Select id from t1 where exists(select1from t2 where t1.id=t2.id and t2.id<10), and t1.id=t2.id is an equivalence condition related to the parent query (here, the query about the t1 table). By using this equivalence condition as the target column of the target branch (i.e., the set operation branch generated in order to rewrite it as a set operation), for example, Select id from t1 where exists(select t2.idfrom t2 where t2.id<10). In this way, the equivalence condition originally in the subquery condition clause whrer is extracted as the target column to reconstruct the query logic to implement a set operation equivalent to the original semi-join or anti-join operation.

[0154] S7022. According to the subquery corresponding to the semi-join operation or the anti-join operation and the target branch, construct a set operation equivalent to the semi-join operation or the anti-join operation.

[0155] The purpose of this step is to construct a set operation equivalent to the original semi-join or anti-join operation based on the subquery corresponding to the previously obtained semi-join operation or anti-join operation and the newly generated target branch.

[0156] Continuing with the example in step S7021, for the rewritten query statement Select idfrom t1 where exists(select t2.id from t2 where t2.id<10) in step S7021, an equivalent set operation can be constructed based on the subquery corresponding to this semi-join operation (select t2.id from t2 where t2.id<10) and the target branch created previously (the target column is t2.id).

[0157] If the original query is based on the existence judgment (exists or not exists) of a semi-join or anti-join, a set operation similar to intersect or except can be constructed. For example, Select id from t1 intersectall select t2.id from t2 where t2.id<10 is constructed. Here, intersect all is used to implement the set operation logic equivalent to the previous semi-join operation, and the two query result sets are merged through the intersection operation.

[0158] S7023. Adjust the type of the set operation according to the type of the first query statement to obtain a second query statement.

[0159] When the first query statement is a semi-join operation and the parent query of the semi-join operation does not include a deduplication operation (for example, the parent query does not include the keyword distinct), the conditional clause in the parent query is rewritten as an intersection operation without deduplication, that is, intersect all.

[0160] When the parent query of the semi-join operation or anti-join operation in the first query statement includes a deduplication operation (for example, the parent query includes the keyword distinct), the conditional clause in the parent query is rewritten as a deduplication type intersection operation, ie, intersect.

[0161] The type of the set operation is adjusted according to the type of the first query statement, so that the second query statement obtained after rewriting is consistent with the first query statement in terms of whether to perform a deduplication operation.

[0162] Optionally, when the first query statement includes multiple subqueries for implementing semi-join or anti-join operations, all subqueries included in the first query statement can be traversed, and each subquery for implementing the semi-join or anti-join operation can be rewritten in turn using the method of steps S701-S702 above.

[0163] In the aforementioned embodiments of the present application, the query statement processing method provided by the embodiments of the present application is introduced, and in order to realize the functions in the methods provided by the embodiments of the present application, the electronic device as the execution subject may include a hardware structure and / or a software module, and the above functions are realized in the form of a hardware structure, a software module, or a hardware structure plus a software module. Whether one of the above functions is executed in the form of a hardware structure, a software module, or a hardware structure plus a software module depends on the specific application and design constraints of the technical solution.

[0164] Figure 8 A schematic diagram of a query statement conversion device provided by the present application, such as Figure 8 The device shown can be used to execute the query statement conversion method provided by any of the above embodiments. In one embodiment, Figure 8 The device 1000 shown includes: an acquisition module 1001 , a determination module 1002 , a processing module 1003 , and an execution module 1004 .

[0165] An acquisition module 1001 is used to acquire a first query statement from a database;

[0166] A determination module 1002 is used to determine whether to rewrite the first query statement according to the number of filter conditions and the amount of data in the first query statement when the first query statement includes a target operation of the first table and the second table, and the target operation includes a set operation, a semi-join operation, or an anti-join operation;

[0167] The processing module 1003 is used for rewriting the first query statement to obtain a second query statement if it is determined to rewrite the first query statement, and the query efficiency of the second query statement is higher than the query efficiency of the first query statement;

[0168] The execution module 1004 is used to execute the second query statement.

[0169] Optionally, when the target operation is a set operation, the determination module 1002 is specifically used to determine to rewrite the first query statement when the number of filtering conditions is less than or equal to a preset number threshold and the data volume is greater than or equal to a preset data volume threshold; the processing module 1003 is specifically used to convert the set operation in the first query statement into an equivalent semi-join operation or anti-join operation according to the type of the set operation to obtain the second query statement, and the type of the set operation includes a deduplication type and a non-deduplication type.

[0170] Optionally, the processing module 1003 is specifically configured to, when the type of the set operation is a deduplication type, rewrite the set operation into a semi-join operation or an anti-join operation including a deduplication operation, to obtain a second query statement;

[0171] Optionally, processing module 1003 is specifically used to rewrite the difference operation into an anti-join operation including a deduplication operation to obtain a second query statement when the set operation is a difference operation; or, when the set operation is an intersection operation, rewrite the intersection operation into a semi-join operation including a deduplication operation to obtain a second query statement.

[0172] Optionally, processing module 1003 is specifically used to convert the difference operation into an anti-join operation including a deduplication operation when at least one target column in the first query statement is a column that does not filter null values; and use a logical operation function for processing null values ​​to encapsulate the equality condition of the target column to obtain a second query statement.

[0173] Optionally, the processing module 1003 is specifically configured to, when the type of the set operation is a non-deduplication type and the set operation is an intersection operation, rewrite the intersection operation into a semi-join operation that does not include a deduplication operation, to obtain a second query statement.

[0174] Optionally, processing module 1003 is specifically used to use the target column of the first branch of the set operation as a condition to construct a first equi-join condition with the target columns of the remaining branches of the set operation; according to the first equi-join condition, obtain a subquery of a semi-join operation or an anti-join operation, and the target column of the subquery is a default value; after mounting the subquery on the conditional clause of the first branch, obtain a candidate second query statement; add a deduplication operation to the target column of the candidate second query statement to obtain a second query statement.

[0175] Optionally, processing module 1003 is specifically used to use the target column of the first branch of the intersection operation as a condition to construct a second equal-join condition with the target columns of the remaining branches of the intersection operation; according to the second equal-join condition, a subquery of the semi-join operation is obtained, and the target column of the subquery is a default value; the subquery is mounted on the conditional clause of the first branch to obtain a second query statement.

[0176] Optionally, when the target operation is a semi-join operation or an anti-join operation, the determination module 1002 is specifically used to determine to rewrite the first query statement when the number of filtering conditions is greater than a preset number threshold and the data volume is less than a preset data volume threshold; the processing module 1003 is specifically used to rewrite the semi-join operation or the anti-join operation into an equivalent set operation according to the type of the first query statement to obtain the second query statement, and the type of the first query statement includes a deduplication type and a non-deduplication type.

[0177] Optionally, processing module 1003 is specifically used to generate a target branch according to an equivalent condition related to a parent query in a semi-join operation or an anti-join operation, wherein a target column of the target branch is an equivalent condition; construct a set operation equivalent to the semi-join operation or the anti-join operation according to a subquery corresponding to the semi-join operation or the anti-join operation, and the target branch; and adjust the type of the set operation according to the type of the first query statement to obtain a second query statement.

[0178] The specific implementation method and principle of the query statement conversion device for the above query statement refer to the description of the query statement conversion method for the above query statement, which will not be repeated here.

[0179] It should be noted that it should be understood that the division of the various modules of the above device is only a division of logical functions. In actual implementation, they can be fully or partially integrated into one physical entity, or they can be physically separated. And these modules can all be implemented in the form of software called by processing elements; they can also be all implemented in the form of hardware; some modules can also be implemented in the form of software called by processing elements, and some modules can be implemented in the form of hardware. For example, the processing module can be a separately established processing element, or it can be integrated in a chip of the above device. In addition, it can also be stored in the memory of the above device in the form of program code, and called and executed by a processing element of the above device. The function of the above-mentioned module is determined. The implementation of other modules is similar. In addition, these modules can be fully or partially integrated together, or they can be implemented independently. The processing element described here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each module above can be completed by an integrated logic circuit of hardware in the processor element or instructions in the form of software.

[0180] For example, the above modules may be one or more integrated circuits configured to implement the above methods, such as one or more application specific integrated circuits (ASIC), or one or more microprocessors (digital signal processors, DSP), or one or more field programmable gate arrays (FPGA), etc. For another example, when a module above is implemented in the form of a processing element scheduling program code, the processing element may be a general-purpose processor, such as a central processing unit (CPU) or other processor that can call program code. For another example, these modules may be integrated together and implemented in the form of a system-on-a-chip (SOC).

[0181] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present application is generated in whole or in part. The computer may be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions may be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium, for example, the computer instructions may be transmitted from a website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.) mode to another website site, computer, server or data center. The computer-readable storage medium may be any available medium that a computer can access or a data storage device such as a server or data center that includes one or more available media integrated. The available medium may be a magnetic medium, (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid state disk (SSD)), etc.

[0182] For example, Fig. 9 A structural diagram of a query statement conversion device provided by this application, such as Fig. 9 The device shown can be used to execute the query statement conversion method shown in any of the above items. In one embodiment, Fig. 9 The query statement processing device 2000 shown includes one or more processors 2001 and a memory 2002; wherein the memory 2002 is used to store computer executable instructions, and the processor 2001 can execute the computer executable instructions stored in the memory 2002. When the computer executable instructions are executed by the processor 2001, the processor 2001 implements any query statement conversion method in the aforementioned embodiments of the present application.

[0183] In one embodiment, if Fig. 9 The query statement conversion device 2000 shown further includes a communication interface 2003 , wherein the processor 2001 can communicate with other devices through the communication interface 2003 , for example, the processor 2001 obtains the first query statement through the communication interface 2003 .

[0184] The present application also provides a computer-readable storage medium, which stores computer-executable instructions. When the computer-executable instructions are executed, they can be used to implement any query statement conversion method in the aforementioned embodiments of the present application.

[0185] An embodiment of the present application also provides a chip for executing instructions, wherein the chip is used to execute any query statement conversion method as described above in the present application.

[0186] An embodiment of the present application also provides a computer program product, including a computer program, which, when executed, implements any query statement conversion method as described above in the present application.

[0187] Those skilled in the art can understand that all or part of the steps of implementing the above-mentioned method embodiments can be completed by hardware related to program instructions. The aforementioned program can be stored in a computer-readable storage medium. When the program is executed, the steps of the above-mentioned method embodiments are executed; and the aforementioned storage medium includes: ROM, RAM, disk or optical disk and other media that can store program codes.

[0188] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or replace some or all of the technical features therein with equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A query statement conversion method, characterized in that: include: Get the first query statement of the database; When the first query statement includes a target operation of the first table and the second table, determining whether to rewrite the first query statement according to the number of filtering conditions and the amount of data of the first query statement, and the target operation includes a set operation, a semi-join operation, or an anti-join operation; If it is determined to rewrite the first query statement, rewrite the first query statement to obtain a second query statement, where the query efficiency of the second query statement is higher than that of the first query statement; Execute the second query statement.

2. The method according to claim 1, characterized in that The target operation is a set operation, and determining whether to rewrite the first query statement according to the number of filtering conditions and the amount of data of the first query statement includes: When the number of the filtering conditions is less than or equal to a preset number threshold, and the data volume is greater than or equal to a preset data volume threshold, determining to rewrite the first query statement; The rewriting of the first query statement to obtain a second query statement includes: According to the type of the set operation, the set operation in the first query statement is converted into an equivalent semi-join operation or anti-join operation to obtain the second query statement, and the type of the set operation includes a deduplication type and a non-deduplication type.

3. The method according to claim 2, characterized in that The step of converting the set operation in the first query statement into an equivalent semi-join operation or anti-join operation according to the type of the set operation to obtain a second query statement includes: When the type of the set operation is a deduplication type, the set operation is rewritten into a semi-join operation or an anti-join operation including a deduplication operation to obtain the second query statement.

4. The method according to claim 3, characterized in that The step of rewriting the set operation into a semi-join operation or an anti-join operation including a deduplication operation to obtain the second query statement includes: When the set operation is a difference operation, rewriting the difference operation into an anti-join operation including a deduplication operation to obtain the second query statement; or, When the set operation is an intersection operation, the intersection operation is rewritten as a semi-join operation including a deduplication operation to obtain the second query statement.

5. The method according to claim 4, characterized in that The step of rewriting the difference operation into an anti-join operation including a deduplication operation to obtain the second query statement includes: When at least one target column in the first query statement is a column that does not filter null values, converting the difference operation into an anti-join operation including a deduplication operation; The equal value condition of the target column is encapsulated using a logical operation function for processing null values ​​to obtain the second query statement.

6. The method according to claim 2, characterized in that The step of converting the set operation in the first query statement into an equivalent semi-join operation or anti-join operation according to the type of the set operation to obtain a second query statement includes: When the type of the set operation is a non-deduplication type and the set operation is an intersection operation, the intersection operation is rewritten into a semi-join operation that does not include a deduplication operation, to obtain the second query statement.

7. The method according to any one of claims 3 to 5, characterized in that: The step of rewriting the set operation into a semi-join operation or an anti-join operation including a deduplication operation to obtain the second query statement includes: Taking the target column of the first branch of the set operation as a condition, and constructing a first equivalue join condition with the target columns of the remaining branches of the set operation; According to the first equi-join condition, a subquery of the semi-join operation or the anti-join operation is obtained, wherein the target column of the subquery is a default value; Mounting the subquery after the conditional clause of the first branch to obtain a candidate second query statement; A deduplication operation is added to the target column of the candidate second query statement to obtain the second query statement.

8. The method according to claim 6, characterized in that The step of rewriting the intersection operation into a semi-join operation that does not include a deduplication operation to obtain the second query statement includes: Taking the target column of the first branch of the intersection operation as a condition, and constructing a second equivalue join condition with the target columns of the remaining branches of the intersection operation; According to the second equi-join condition, a subquery of the semi-join operation is obtained, wherein the target column of the subquery is a default value; The subquery is mounted after the conditional clause of the first branch to obtain the second query statement.

9. The method according to claim 1, characterized in that: The target operation is a semi-join operation or an anti-join operation, and determining whether to rewrite the first query statement according to the number of filtering conditions and the amount of data of the first query statement includes: When the number of the filtering conditions is greater than a preset number threshold and the amount of data is less than a preset data amount threshold, determining to rewrite the first query statement; The rewriting of the first query statement to obtain a second query statement includes: According to the type of the first query statement, the semi-join operation or the anti-join operation is rewritten into an equivalent set operation to obtain the second query statement, and the type of the first query statement includes a deduplication type and a non-deduplication type.

10. The method according to claim 9, characterized in that The step of rewriting the semi-join operation or the anti-join operation into an equivalent set operation according to the type of the first query statement to obtain the second query statement includes: Generate a target branch according to an equivalence condition related to the parent query in the semi-join operation or the anti-join operation, wherein a target column of the target branch is the equivalence condition; According to the subquery corresponding to the semi-join operation or the anti-join operation and the target branch, construct a set operation equivalent to the semi-join operation or the anti-join operation; According to the type of the first query statement, the type of the set operation is adjusted to obtain the second query statement.

Images