SQL (Structured Query Language) statement processing method and device, electronic equipment and storage medium

Abstract

The invention provides an SQL (Structured Query Language) statement processing method and device, electronic equipment and a storage medium. The method comprises the following steps: acquiring a to-be-processed SQL statement; obtaining an index of a to-be-queried table, determining a sequence elimination index in the index of the to-be-queried table, and determining at least one to-be-rewritten in condition expression related to the sequence elimination index in the in condition expression of the to-be-processed SQL statement; constructing a temporary table with multiple rows and one column based on a plurality of constants of the in condition expressions, and replacing the in condition expressions to be rewritten in the SQL statements to be rewritten with corresponding equivalent expressions to form clauses, establishing a transverse connection between the temporary table and the clause, and replacing a query table and a query condition of the SQL statement to be rewritten with a transverse connection result; and pushing down an order by function and a limit function for the to-be-queried table to a clause constructed when the last to-be-rewritten SQL statement is rewritten, so as to obtain a processing result of the to-be-processed SQL statement.

Description

Technical Field

[0001] One or more embodiments of the present specification relate to the field of database technology, and in particular, to a method and device for processing SQL statements, an electronic device, and a storage medium. Background Art

[0002] In recent years, with the development of computers and databases, SQL (Structure Query Languge) has been widely used as a database-specific computer programming language and has become the most important and most commonly used relational database query language. When querying data based on SQL statements, a certain amount of computing power of the computer application system will be consumed, that is, the execution engine is called to execute the data query.

[0003] In the related art, when data query is performed based on relatively complex SQL statements, the efficiency is low, which affects the processing efficiency of related tasks and user experience. Summary of the invention

[0004] In view of this, one or more embodiments of the present specification provide a method and device for processing an SQL statement, an electronic device, and a storage medium.

[0005] To achieve the above objectives, one or more embodiments of this specification provide the following technical solutions:

[0006] According to a first aspect of one or more embodiments of this specification, a method for processing an SQL statement is proposed, the method comprising:

[0007] Obtaining a SQL statement to be processed, wherein the SQL statement to be processed includes a table to be queried, and an order by function, a limit function, and an in conditional expression for the table to be queried;

[0008] Obtaining the index of the table to be queried, determining a de-sequencing index in the index of the table to be queried, and determining at least one in conditional expression to be rewritten related to the de-sequencing index in the in conditional expression of the SQL statement to be processed;

[0009] Determine in sequence the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, and rewrite the SQL statement to be rewritten in the following manner: construct a temporary table with multiple rows and one column based on multiple constants of the in conditional expression, replace the in conditional expression to be rewritten in the SQL statement to be rewritten with a corresponding equivalent expression to form a clause, establish a horizontal connection between the temporary table and the clause, and replace the query table and query condition of the SQL statement to be rewritten with the result of the horizontal connection;

[0010] The order by function and the limit function for the table to be queried are pushed down to the clause constructed when rewriting the last SQL statement to be rewritten, so as to obtain the processing result of the SQL statement to be processed.

[0011] In one or more possible embodiments of the present specification, the rewriting process of the SQL statement to be rewritten further includes the following steps:

[0012] The projection column is updated to the query result of the clause.

[0013] In one or more possible embodiments of the present specification, the rewriting process of the SQL statement to be rewritten further includes the following steps:

[0014] Create a distinct function for the temporary table;

[0015] The establishing a lateral connection between the temporary table and the clause includes:

[0016] A lateral connection is established between the execution result of the distinct function and the clause.

[0017] In one or more possible embodiments of the present specification, the equivalent expression corresponding to the in conditional expression to be rewritten is used to represent the equivalent relationship between the columns in the in conditional expression to be rewritten and the columns of the temporary table.

[0018] In one or more possible embodiments of the present specification, sequentially determining the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten includes:

[0019] For the first in conditional expression to be rewritten, the SQL statement to be processed is used as the corresponding SQL statement to be rewritten;

[0020] For in conditional expressions other than the first one to be rewritten, the clause constructed when rewriting the previous SQL statement to be rewritten is used as the corresponding SQL statement to be rewritten.

[0021] In one or more possible embodiments of the present specification, obtaining the index of the table to be queried and determining the de-sequencing index in the index of the table to be queried includes:

[0022] Select at least one index of the table to be queried in sequence until the selected index meets a preset condition and determines the selected index as a deordered index, wherein the preset condition includes: the result of removing the set of equivalent columns in the SQL statement to be processed from the sorting column set in the SQL statement to be processed, and the prefix of the result of removing the set of equivalent columns in the SQL statement to be processed from the set of columns included in the index, the sorting column set is the column targeted by the order by function, and the set of equivalent columns is the columns in all equivalent expressions including the in conditional expression.

[0023] In one or more possible embodiments of the present specification, determining at least one in conditional expression to be rewritten related to the deordering index in the in conditional expression of the SQL statement to be processed includes:

[0024] If the column in the in conditional expression is a column in the column set included in the desequencing index, it is determined that the in conditional expression is an in conditional expression to be rewritten that is related to the desequencing index.

[0025] In one or more possible embodiments of the present specification, the SQL statement to be processed does not include an aggregate function, a window function, or a distinct function.

[0026] In one or more possible embodiments of this specification, the method further includes:

[0027] If the execution cost of the SQL statement to be processed is less than the processing result of the SQL statement to be processed, saving the processing result of the SQL statement to be processed;

[0028] If the execution cost of the SQL statement to be processed is not less than the processing result of the SQL statement to be processed, the processing result of the SQL statement to be processed is deleted.

[0029] According to a second aspect of one or more embodiments of this specification, a device for processing SQL statements is provided, the device comprising:

[0030] An acquisition module, used for acquiring a SQL statement to be processed, wherein the SQL statement to be processed includes a table to be queried, and an order by function, a limit function and an in conditional expression for the table to be queried;

[0031] An index module, used to obtain the index of the table to be queried, determine the de-sequencing index in the index of the table to be queried, and determine at least one in conditional expression to be rewritten related to the de-sequencing index in the in conditional expression of the SQL statement to be processed;

[0032] A rewriting module, used for determining in turn the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, and rewriting the SQL statement to be rewritten in the following manner: constructing a temporary table with multiple rows and one column based on multiple constants of the in conditional expression, replacing the in conditional expression to be rewritten in the SQL statement to be rewritten with a corresponding equivalent expression to form a clause, establishing a horizontal connection between the temporary table and the clause, and replacing the query table and query condition of the SQL statement to be rewritten with the result of the horizontal connection;

[0033] The push-down module is used to push down the order by function and the limit function for the table to be queried to the clause constructed when rewriting the last SQL statement to be rewritten, so as to obtain the processing result of the SQL statement to be processed.

[0034] In one or more possible embodiments of the present specification, the rewriting process of the SQL statement to be rewritten further includes the following steps:

[0035] The projection column is updated to the query result of the clause.

[0036] In one or more possible embodiments of the present specification, the rewriting process of the SQL statement to be rewritten further includes the following steps:

[0037] Create a distinct function for the temporary table;

[0038] The establishing a lateral connection between the temporary table and the clause includes:

[0039] A lateral connection is established between the execution result of the distinct function and the clause.

[0040] In one or more possible embodiments of the present specification, the equivalent expression corresponding to the in conditional expression to be rewritten is used to represent the equivalent relationship between the columns in the in conditional expression to be rewritten and the columns of the temporary table.

[0041] In one or more possible embodiments of the present specification, when the rewriting module is used to sequentially determine the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, it is used to:

[0042] For the first in conditional expression to be rewritten, the SQL statement to be processed is used as the corresponding SQL statement to be rewritten;

[0043] For in conditional expressions other than the first one to be rewritten, the clause constructed when rewriting the previous SQL statement to be rewritten is used as the corresponding SQL statement to be rewritten.

[0044] In one or more possible embodiments of the present specification, the index module is used to obtain the index of the table to be queried, and when determining the de-sequencing index in the index of the table to be queried, is used to:

[0045] Select at least one index of the table to be queried in sequence until the selected index meets a preset condition and determines the selected index as a deordered index, wherein the preset condition includes: the result of removing the set of equivalent columns in the SQL statement to be processed from the sorting column set in the SQL statement to be processed, and the prefix of the result of removing the set of equivalent columns in the SQL statement to be processed from the set of columns included in the index, the sorting column set is the column targeted by the order by function, and the set of equivalent columns is the columns in all equivalent expressions including the in conditional expression.

[0046] In one or more possible embodiments of the present specification, when the index module is used to determine at least one in conditional expression to be rewritten related to the deordering index in the in conditional expression of the SQL statement to be processed, it is used to:

[0047] If the column in the in conditional expression is a column in the column set included in the desequencing index, it is determined that the in conditional expression is an in conditional expression to be rewritten that is related to the desequencing index.

[0048] In one or more possible embodiments of the present specification, the SQL statement to be processed does not include an aggregate function, a window function, or a distinct function.

[0049] In one or more possible embodiments of the present specification, the device further includes a cost evaluation module, which is used to:

[0050] If the execution cost of the SQL statement to be processed is less than the processing result of the SQL statement to be processed, saving the processing result of the SQL statement to be processed;

[0051] If the execution cost of the SQL statement to be processed is not less than the processing result of the SQL statement to be processed, the processing result of the SQL statement to be processed is deleted.

[0052] According to a third aspect of one or more embodiments of this specification, a computer program product is provided, comprising a computer program / instruction, which implements the steps of the method described in the first aspect when executed by a processor.

[0053] According to a fourth aspect of one or more embodiments of this specification, an electronic device is provided, including:

[0054] processor;

[0055] a memory for storing processor-executable instructions;

[0056] The processor implements the method as described in the first aspect by running the executable instructions.

[0057] According to a fifth aspect of one or more embodiments of the present specification, a computer-readable storage medium is provided, on which computer instructions are stored, and when the instructions are executed by a processor, the steps of the method described in the first aspect are implemented.

[0058] The technical solutions provided by the embodiments of this specification may have the following beneficial effects:

[0059] The SQL statement processing method provided in the embodiment of the present specification obtains a SQL statement to be processed, wherein the SQL statement to be processed includes a table to be queried, and an order by function, a limit function and an in conditional expression for the table to be queried; obtains an index of the table to be queried, and determines a deordering index in the index of the table to be queried, and determines at least one in conditional expression to be rewritten related to the deordering index in the in conditional expression of the SQL statement to be processed; sequentially determines the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, and rewrites the SQL statement to be rewritten in the following manner: constructs a temporary table with multiple rows and one column based on multiple constants of the in conditional expression, replaces the in conditional expression to be rewritten in the SQL statement to be rewritten with a corresponding equivalent expression to form a clause, and establishes a horizontal connection between the temporary table and the clause; pushes down the order by function and the limit function for the table to be queried to the clause constructed when rewriting the last SQL statement to be rewritten, so as to obtain the processing result of the SQL statement to be processed. Since the in condition expression related to the deordered index is rewritten as an equivalent expression, the order by function and limit function can be pushed down to the index scanning stage after the rewrite, thereby eliminating the sorting operation involved in the limit, thereby reducing the execution overhead of the SQL statement and improving the efficiency of data query. BRIEF DESCRIPTION OF THE DRAWINGS

[0060] Figure 1 It is a structural diagram of a distributed database provided by an exemplary embodiment.

[0061] Figure 2 It is a flowchart of a method for processing SQL statements provided by an exemplary embodiment.

[0062] Figure 3 The figure is a flowchart of a method for rewriting a SQL statement provided by an exemplary embodiment.

[0063] Figure 4 It is a structural schematic diagram of a device provided by an exemplary embodiment.

[0064] Figure 5 It is a block diagram of a device for processing SQL statements provided by an exemplary embodiment. DETAILED DESCRIPTION

[0065] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, the same numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with one or more embodiments of this specification. Instead, they are merely examples of devices and methods consistent with some aspects of one or more embodiments of this specification as detailed in the appended claims.

[0066] It should be noted that: in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the steps included in the method may be more or less than those described in this specification. In addition, a single step described in this specification may be decomposed into multiple steps for description in other embodiments; and multiple steps described in this specification may be combined into a single step for description in other embodiments.

[0067] First, some concepts involved in this specification are explained.

[0068] Query rewriting: rewrite a SQL request into another SQL request and ensure that the semantics of the two are completely equivalent.

[0069] in expression statement: a filter predicate that can filter the result set.

[0070] Index scan: A data scanning method in which the database system quickly matches the target record by searching the index tree.

[0071] Deordered index: When querying based on an index in a table, the order by clause in the query statement can be eliminated. This index is called a deordered index.

[0072] Horizontal join: A join method that is implemented through Nested Loop Join. The right table in a lateral join can reference columns in the left table.

[0073] Limit function: This syntax is used to return a specified number of rows from a result set.

[0074] Values ​​table: A temporary table built at runtime.

[0075] In business scenarios, there are the following queries: query the table based on the query conditions and sort the query results, and take the first few rows after sorting. For example: select * from t1 where t1.c1 = 1 order by t1.c2 limit 2, the conventional algorithm is to first query all rows that satisfy t1.c1 = 1 from the t1 table, then sort based on the t1.c2 column, and finally take the first two rows in the sorted result. If the t1 table has an index (c1, c2), then when using the index to query, since the rows in the index are sorted by (c1, c2), and t1.c1 = 1, the query results are naturally ordered by the c2 column. At this time, the order by operation can be eliminated, and the limit statement can be pushed down to the index scan stage. The query can be stopped after two rows are queried. It can be seen that using the index (c1, c2) for querying reduces the index scan overhead and avoids the sorting overhead. We call this index a de-ordering index.

[0076] However, the query statement may also contain an in conditional expression, for example: select * from t1 where t1.c1in(1,2,3,4) order by t1.c2 limit 2. In this case, since t1.c1 is not a fixed value, the order of the query results cannot be guaranteed when using the index (c1,c2) ​​for query. In this case, the sorting cannot be eliminated, and therefore the limit statement cannot be pushed down to the index query stage, which may result in time-consuming query and sorting operations.

[0077] Based on the above technical problems, at least one embodiment of the present specification provides a method for processing SQL statements, which can rewrite SQL statements containing order functions, limit functions, and in conditional expressions, so that the rewritten SQL statements push the order by function and limit function down to the index scanning stage, thereby eliminating the sorting operation involved in limit, and further reducing the execution overhead of the SQL statement.

[0078] This method can be applied to Figure 1 The distributed database based on the LSM-Tree storage engine, or other types of databases are shown.

[0079] Please refer to the attached Figure 2 , which exemplarily shows the process of the partition management method with short write-stop time, including steps S201 to S204.

[0080] In step S201, a SQL statement to be processed is obtained, wherein the SQL statement to be processed includes a table to be queried, and an order by function, a limit function, and an in conditional expression for the table to be queried.

[0081] Example 1

[0082] The database schema is defined as follows:

[0083] create table t1(c1 int not null,c2 int not null,c3 int not null);

[0084] create index i12 on t1(c1,c2);

[0085] The SQL statements to be processed are as follows:

[0086] select*from t1 where t1.c1 in(1,2,3,4)order by t1.c2 limit 2.

[0087] Example 2

[0088] The database schema is defined as follows:

[0089] create table t1(c1 int not null,c2 int not null,c3 int not null);

[0090] create index i123 on t1(c1,c2,c3);

[0091] The SQL statements to be processed are as follows:

[0092] select*from t1 where t1.c1 in(1,2)and t1.c2 in(1,2)order by t1.c3limit 2.

[0093] It should be understood that the SQL statement to be processed does not contain aggregate functions, window functions and distinct functions, and the above functions need to be executed before the limit function. However, after the rewrite, this method pushes limit down to the index retrieval stage, so that limit is executed before the above functions, resulting in inconsistent execution results before and after the rewrite.

[0094] Therefore, when obtaining the SQL statement to be processed in this step, a rewrite check can be performed, that is, checking whether the SQL statement contains the table to be queried, and the order by function, limit function and in conditional expression for the table to be queried, and does not contain aggregate functions, window functions and distinct functions. If so, it is regarded as the SQL statement to be processed and rewritten by this method, otherwise, it is not rewritten.

[0095] The SQL statement in Example 1 above contains order by t1.c2 limit 2 and t1.c1 in(1,2,3,4), and does not contain aggregate functions, window functions, or distinct functions. Therefore, the SQL statement can be used as a pending SQL statement.

[0096] The SQL statement in Example 2 above contains order by t1.c3 limit 2, t1.c1 in(1,2)andt1.c2 in(1,2), and does not contain aggregate functions, window functions, or distinct functions. Therefore, the SQL statement can be used as a pending SQL statement.

[0097] In step S202, the index of the table to be queried is obtained, and a de-sequencing index is determined in the index of the table to be queried, and at least one in conditional expression to be rewritten related to the de-sequencing index is determined in the in conditional expression of the SQL statement to be processed.

[0098] Exemplarily, a de-sequencing index may be determined in the index of the table to be queried in the following manner: at least one index of the table to be queried is selected in sequence until the selected index satisfies a preset condition and the selected index is determined as the de-sequencing index, wherein the preset condition includes: a result of removing a set of equivalent columns in the SQL statement to be processed from a set of sorting columns in the SQL statement to be processed, a prefix of a result of removing a set of equivalent columns in the SQL statement to be processed from a set of columns included in the index, the sorting column set being the columns targeted by the order by function, and the equivalent column set being the columns in all equivalent expressions including an in conditional expression, such as an = expression, an is expression, an in expression, etc.

[0099] Taking Example 1 above as an example, the table t1 to be queried has an index, the column set contained in the index is a set consisting of c1 and c2, the sorting column set is c2, and the equal value column set is c1. Since the index meets the above preset conditions, it is determined that the index is a deordered index.

[0100] Taking Example 2 above as an example, the table t1 to be queried has an index, the column set contained in the index is a set consisting of c1, c2, and c3, the sorting column set is c3, and the equal value columns are combined into a set consisting of c1 and c2. Since the index meets the above preset conditions, the index is determined to be a deordered index.

[0101] As another example, at least one in conditional expression to be rewritten related to the desequencing index can be determined in the in conditional expression of the SQL statement to be processed in the following manner: if the column in the in conditional expression is a column in the column set included in the desequencing index, then the in conditional expression is determined to be the in conditional expression to be rewritten related to the desequencing index.

[0102] Taking Example 1 above as an example, c1 in the in conditional expression t1.c1 in (1,2,3,4) is a column in the column sets c1 and c2 included in the de-sequencing index, so the in conditional expression is an in conditional expression to be rewritten related to the de-sequencing index.

[0103] Taking Example 2 above as an example, c1 in the in conditional expression t1.c1 in(1,2) is a column in the column set c1, c2, and c3 included in the de-sequencing index, so the in conditional expression is an in conditional expression to be rewritten that is related to the de-sequencing index; c2 in the in conditional expression t1.c2 in(1,2) is a column in the column set c1, c2, and c3 included in the de-sequencing index, so the in conditional expression is an in conditional expression to be rewritten that is related to the de-sequencing index.

[0104] In step S203, the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten is determined in turn, and the SQL statement to be rewritten is rewritten in the following manner: a temporary table with multiple rows and one column is constructed based on multiple constants of the in conditional expression, the in conditional expression to be rewritten in the SQL statement to be rewritten is replaced with a corresponding equivalent expression to form a clause, a horizontal connection is established between the temporary table and the clause, and the query table and query condition of the SQL statement to be rewritten are replaced with the result of the horizontal connection.

[0105] Preferably, if there is one in conditional expression to be rewritten in the SQL statement to be processed, the SQL statement to be rewritten corresponding to the in conditional expression to be rewritten is rewritten in this step; if there are multiple in conditional expressions to be rewritten in the SQL statement to be processed, each SQL statement to be rewritten corresponding to the in conditional expression to be rewritten is rewritten after it is determined. For example, for the first in conditional expression to be rewritten, the SQL statement to be processed is used as the corresponding SQL statement to be rewritten; for non-first in conditional expressions to be rewritten, the clause constructed when rewriting the previous SQL statement to be rewritten is used as the corresponding SQL statement to be rewritten.

[0106] It should be understood that after each rewriting of the SQL statement to be rewritten is completed, the SQL statement to be rewritten may be replaced with the rewriting result.

[0107] Exemplarily, the equivalent expression corresponding to the in conditional expression to be rewritten is used to represent the equivalent relationship between the column in the in conditional expression to be rewritten and the column of the temporary table.

[0108] As another example, the rewriting process of the SQL statement to be rewritten further includes the following step: updating the projection column to the query result of the clause.

[0109] In another exemplary embodiment, the rewriting process of the SQL statement to be rewritten further includes the following steps: creating a distinct function for the temporary table. Based on this, establishing a horizontal connection between the temporary table and the clause includes: establishing a horizontal connection between the execution result of the distinct function and the clause.

[0110] Please refer to the attached Figure 3 , taking Example 1 above as an example, the above rewriting process is described in detail:

[0111] In step S301, a temporary table with multiple rows and one column is constructed based on multiple constants of the in conditional expression, and a distinct function is created for the temporary table: (select distinct*from(values ​​row(1),row(2),row(3),row(4)))vt.

[0112] In step S302, the in conditional expression to be rewritten in the SQL statement to be rewritten is replaced with a corresponding equivalent expression to form a clause: select * from t1 where t1.c1 = vt.column_0.

[0113] In step S303, a lateral connection is established between the execution result of the distinct function for the temporary table and the clause, and the query table and query condition of the SQL statement to be rewritten are replaced with the result of the lateral connection: select * from (select distinct * from (values ​​row(1), row(2), row(3), row(4))) vt, lateral (select * from t1 where t1.c1 = vt.column_0) view order by view.c2 limit 2.

[0114] In step S304, the projection column is updated to the query result of the clause: select view.*from(select distinct*from(values ​​row(1),row(2),row(3),row(4)))vt,lateral(select*from t1 where t1.c1=vt.column_0)view order by view.c2 limit 2.

[0115] Take Example 2 above as an example. In this example, there are two in conditional expressions to be rewritten: t1.c1 in(1,2) and t1.c2 in(1,2). The following describes the rewriting process in detail through three steps:

[0116] Step 1: Determine that the SQL statement to be rewritten corresponding to t1.c1 in(1,2) is the SQL statement to be processed, that is, select*from t1 where t1.c1 in(1,2)and t1.c2 in(1,2)order by t1.c3 limit 2; and rewrite the SQL statement to be rewritten in the following manner:

[0117] First, a temporary table with multiple rows and one column is constructed based on multiple constants of the in conditional expression, and a distinct function is created for the temporary table: (select distinct*from(values ​​row(1),row(2)))vt1.

[0118] Next, replace the in condition expression to be rewritten in the SQL statement to be rewritten with the corresponding equivalent expression to form a clause: select * from t1 where t1.c2 in (1, 2) and t1.c1 = vt1.column_0.

[0119] Next, a lateral connection is established between the execution result of the distinct function for the temporary table and the clause, and the query table and query condition of the SQL statement to be rewritten are replaced with the result of the lateral connection: select*from(select distinct*from(values ​​row(1),row(2)))vt1,lateral(select*from t1 where t1.c2 in(1,2)and t1.c1=vt1.column_0)view1 order by view1.c3limit 2.

[0120] Finally, update the projection column to the query result of the clause: select view1.*from(selectdistinct*from(values ​​row(1),row(2)))vt1,lateral(select*from t1 where t1.c2 in(1,2)and t1.c1=vt1.column_0)view1 order by view1.c3 limit 2.

[0121] Step 2: Determine that the SQL statement to be rewritten corresponding to t1.c2 in(1,2) is the clause constructed when rewriting the previous SQL statement to be rewritten, that is, select * from t1 where t1.c2 in(1,2) and t1.c1 = vt1.column_0; and rewrite the SQL statement to be rewritten in the following manner:

[0122] First, a temporary table with multiple rows and one column is constructed based on multiple constants of the in conditional expression, and a distinct function is created for the temporary table: (select distinct*from(values ​​row(1),row(2)))vt 2.

[0123] Next, replace the in condition expression to be rewritten in the SQL statement to be rewritten with the corresponding equivalent expression to form a clause: select * from t1 where t1.c1 = vt1.column_0 and t1.c2 = vt2.column_0.

[0124] Next, a lateral connection is established between the execution result of the distinct function for the temporary table and the clause, and the query table and query condition of the SQL statement to be rewritten are replaced with the result of the lateral connection: select*from(select distinct*from(values ​​row(1),row(2)))vt2,lateral(select*from t1 where t1.c1=vt1.column_0and t1.c2=vt 2.column_0)view2.

[0125] Finally, update the projection column to the query result of the clause: select view2.*from(selectdistinct*from(values ​​row(1),row(2)))vt 2,lateral(select*from t1 where t1.c1=vt1.column_0and t1.c2=vt2.column_0)view2.

[0126] Step 3:

[0127] Replace the clause in the rewritten result in step 1 with the rewritten result in step 2: select view1.*from(select distinct*from(values ​​row(1),row(2)))vt 1,lateral(select view2.*from(select distinct*from(values ​​row(1),row(2)))vt2,lateral(select*from t1 where t1.c1=vt1.column_0and t1.c2=vt2.column_0)view2)view1 order by view1.c3limit 2.

[0128] In step S204, the order by function and the limit function for the table to be queried are pushed down to the clause constructed when rewriting the last SQL statement to be rewritten, so as to obtain the processing result of the SQL statement to be processed.

[0129] Taking Example 1 above as an example, the processing result of the SQL statement to be processed is: select view.*from(selectdistinct*from(values ​​row(1),row(2),row(3),row(4)))vt,lateral(select*from t1where t1.c1=vt.column_0order by t1.c2 limit 2)view order by view.c2 limit 2.

[0130] Taking Example 2 above as an example, the processing result of the SQL statement to be processed is: select view1.*from(selectdistinct*from(values ​​row(1),row(2)))vt1,lateral(select view2.*from(selectdistinct*from(values ​​row(1),row(2)))vt2,lateral(select*from t1 where t1.c1=vt1.column_0and t1.c2=vt2.column_0order by t1.c3 limit 2)view2)view1 orderby view1.c3 limit 2.

[0131] Exemplarily, the method may further include: if the execution cost of the SQL statement to be processed is less than the processing result of the SQL statement to be processed, saving the processing result of the SQL statement to be processed, i.e. accepting the rewrite; if the execution cost of the SQL statement to be processed is not less than the processing result of the SQL statement to be processed, deleting the processing result of the SQL statement to be processed, i.e. discarding the rewrite.

[0132] In the SQL statement processing method provided in the embodiments of this specification, since the in condition expression related to the deordered index is rewritten as an equivalent expression, the order by function and the limit function can be pushed down to the index scanning stage after the rewrite, so that the sorting operation involved in the limit can be eliminated, thereby reducing the execution overhead of the SQL statement and improving the efficiency of data query.

[0133] Figure 4 is a schematic structural diagram of a device provided by an exemplary embodiment. Figure 4 At the hardware level, the device includes a processor 402, an internal bus 404, a network interface 406, a memory 408, and a non-volatile memory 410, and may also include hardware required for other tasks. One or more embodiments of this specification may be implemented based on software, such as the processor 402 reading the corresponding computer program from the non-volatile memory 410 into the memory 408 and then running it. Of course, in addition to the software implementation, one or more embodiments of this specification do not exclude other implementations, such as logic devices or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0134] Please refer to Figure 5 , the SQL statement processing device can be applied to Figure 4The device shown in the figure can realize the technical solution of this specification. The processing device of the SQL statement can include:

[0135] The acquisition module 501 is used to acquire a SQL statement to be processed, wherein the SQL statement to be processed includes a table to be queried, and an order by function, a limit function and an in conditional expression for the table to be queried;

[0136] An index module 502, configured to obtain an index of the table to be queried, determine a de-ordering index in the index of the table to be queried, and determine at least one in conditional expression to be rewritten related to the de-ordering index in the in conditional expression of the SQL statement to be processed;

[0137] The rewriting module 503 is used to determine the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten in turn, and rewrite the SQL statement to be rewritten in the following manner: construct a temporary table with multiple rows and one column based on multiple constants of the in conditional expression, replace the in conditional expression to be rewritten in the SQL statement to be rewritten with a corresponding equivalent expression to form a clause, establish a horizontal connection between the temporary table and the clause, and replace the query table and query condition of the SQL statement to be rewritten with the result of the horizontal connection;

[0138] The push-down module 504 is used to push down the order by function and the limit function for the table to be queried to the clause constructed when rewriting the last SQL statement to be rewritten, so as to obtain the processing result of the SQL statement to be processed.

[0139] In one or more possible embodiments of the present specification, the rewriting process of the SQL statement to be rewritten further includes the following steps:

[0140] The projection column is updated to the query result of the clause.

[0141] In one or more possible embodiments of the present specification, the rewriting process of the SQL statement to be rewritten further includes the following steps:

[0142] Create a distinct function for the temporary table;

[0143] The establishing a lateral connection between the temporary table and the clause includes:

[0144] A lateral connection is established between the execution result of the distinct function and the clause.

[0145] In one or more possible embodiments of the present specification, the equivalent expression corresponding to the in conditional expression to be rewritten is used to represent the equivalent relationship between the columns in the in conditional expression to be rewritten and the columns of the temporary table.

[0146] In one or more possible embodiments of the present specification, when the rewriting module is used to sequentially determine the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, it is used to:

[0147] For the first in conditional expression to be rewritten, the SQL statement to be processed is used as the corresponding SQL statement to be rewritten;

[0148] For in conditional expressions other than the first one to be rewritten, the clause constructed when rewriting the previous SQL statement to be rewritten is used as the corresponding SQL statement to be rewritten.

[0149] In one or more possible embodiments of the present specification, the index module is used to obtain the index of the table to be queried, and when determining the de-sequencing index in the index of the table to be queried, is used to:

[0150] Select at least one index of the table to be queried in sequence until the selected index meets a preset condition and determines the selected index as a deordered index, wherein the preset condition includes: the result of removing the set of equivalent columns in the SQL statement to be processed from the sorting column set in the SQL statement to be processed, and the prefix of the result of removing the set of equivalent columns in the SQL statement to be processed from the set of columns included in the index, the sorting column set is the column targeted by the order by function, and the set of equivalent columns is the columns in all equivalent expressions including the in conditional expression.

[0151] In one or more possible embodiments of the present specification, when the index module is used to determine at least one in conditional expression to be rewritten related to the deordering index in the in conditional expression of the SQL statement to be processed, it is used to:

[0152] If the column in the in conditional expression is a column in the column set included in the desequencing index, it is determined that the in conditional expression is an in conditional expression to be rewritten that is related to the desequencing index.

[0153] In one or more possible embodiments of the present specification, the SQL statement to be processed does not include an aggregate function, a window function, or a distinct function.

[0154] In one or more possible embodiments of the present specification, the device further includes a cost evaluation module, which is used to:

[0155] If the execution cost of the SQL statement to be processed is less than the processing result of the SQL statement to be processed, saving the processing result of the SQL statement to be processed;

[0156] If the execution cost of the SQL statement to be processed is not less than the processing result of the SQL statement to be processed, the processing result of the SQL statement to be processed is deleted.

[0157] The systems, devices, modules or units described in the above embodiments may be implemented by computer chips or entities, or by products with certain functions. A typical implementation device is a computer, which may be in the form of a personal computer, a laptop computer, a cellular phone, a camera phone, a smart phone, a personal digital assistant, a media player, a navigation device, an email transceiver, a game console, a tablet computer, a wearable device or a combination of any of these devices.

[0158] In a typical configuration, a computer includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0159] The memory may include non-permanent storage in a computer-readable medium, random access memory (RAM) and / or non-volatile memory in the form of read-only memory (ROM) or flash RAM. The memory is an example of a computer-readable medium.

[0160] Computer-readable media include permanent and non-permanent, removable and non-removable media that can be used to store information by any method or technology. Information can be computer-readable instructions, data structures, program modules or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technology, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic cassettes, disk storage, quantum memory, graphene-based storage media or other magnetic storage devices or any other non-transmission media that can be used to store information that can be accessed by a computing device. As defined herein, computer-readable media does not include temporary computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0161] It should also be noted that the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, commodity or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, commodity or device. In the absence of more restrictions, the elements defined by the sentence "comprises a ..." do not exclude the existence of other identical elements in the process, method, commodity or device including the elements.

[0162] The above is a description of a specific embodiment of the specification. Other embodiments are within the scope of the appended claims. In some cases, the actions or steps recorded in the claims can be performed in an order different from that in the embodiments and still achieve the desired results. In addition, the processes depicted in the drawings do not necessarily require the specific order or continuous order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0163] The terms used in one or more embodiments of this specification are only for the purpose of describing specific embodiments, and are not intended to limit one or more embodiments of this specification. The singular forms of "a", "said" and "the" used in one or more embodiments of this specification and the appended claims are also intended to include plural forms, unless the context clearly indicates other meanings. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0164] The user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this manual are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with the relevant laws, regulations and standards of relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0165] It should be understood that although the terms first, second, third, etc. may be used to describe various information in one or more embodiments of this specification, these information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of one or more embodiments of this specification, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining".

[0166] The above description is merely a preferred embodiment of one or more embodiments of the present specification and is not intended to limit one or more embodiments of the present specification. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of one or more embodiments of the present specification shall be included in the scope of protection of one or more embodiments of the present specification.

Claims

1. A method for processing an SQL statement, the method comprising: Obtaining a SQL statement to be processed, wherein the SQL statement to be processed includes a table to be queried, and an order by function, a limit function, and an in conditional expression for the table to be queried; Obtaining the index of the table to be queried, determining a de-sequencing index in the index of the table to be queried, and determining at least one in conditional expression to be rewritten related to the de-sequencing index in the in conditional expression of the SQL statement to be processed; Determine in sequence the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, and rewrite the SQL statement to be rewritten in the following manner: construct a temporary table with multiple rows and one column based on multiple constants of the in conditional expression, replace the in conditional expression to be rewritten in the SQL statement to be rewritten with a corresponding equivalent expression to form a clause, establish a horizontal connection between the temporary table and the clause, and replace the query table and query condition of the SQL statement to be rewritten with the result of the horizontal connection; The order by function and the limit function for the table to be queried are pushed down to the clause constructed when rewriting the last SQL statement to be rewritten, so as to obtain the processing result of the SQL statement to be processed.

2. According to the method for processing SQL statements in claim 1, the rewriting process of the SQL statement to be rewritten further comprises the following steps: Updates the projected columns to the query results of the clause.

3. The method for processing SQL statements according to claim 1, wherein the rewriting process of the SQL statement to be rewritten further comprises the following steps: Create a distinct function for the temporary table; The establishing a lateral connection between the temporary table and the clause includes: A lateral connection is established between the execution result of the distinct function and the clause.

4. According to the SQL statement processing method described in claim 1, the equivalent expression corresponding to the in conditional expression to be rewritten is used to characterize the equivalent relationship between the column in the in conditional expression to be rewritten and the column of the temporary table.

5. The method for processing SQL statements according to claim 1, wherein the step of sequentially determining the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten comprises: For the first in conditional expression to be rewritten, the SQL statement to be processed is used as the corresponding SQL statement to be rewritten; For in conditional expressions other than the first one to be rewritten, the clause constructed when rewriting the previous SQL statement to be rewritten is used as the corresponding SQL statement to be rewritten.

6. The method for processing SQL statements according to claim 1, wherein obtaining the index of the table to be queried and determining the de-ordering index in the index of the table to be queried comprises: Select at least one index of the table to be queried in sequence until the selected index meets a preset condition and determines the selected index as a deordered index, wherein the preset condition includes: the result of removing the set of equivalent columns in the SQL statement to be processed from the sorting column set in the SQL statement to be processed, and the prefix of the result of removing the set of equivalent columns in the SQL statement to be processed from the set of columns included in the index, the sorting column set is the column targeted by the order by function, and the set of equivalent columns is the columns in all equivalent expressions including the in conditional expression.

7. The method for processing SQL statements according to claim 1, wherein determining at least one in conditional expression to be rewritten related to the deordering index in the in conditional expression of the SQL statement to be processed comprises: If the column in the in conditional expression is a column in the column set included in the desequencing index, it is determined that the in conditional expression is an in conditional expression to be rewritten that is related to the desequencing index.

8. According to the method for processing SQL statements of claim 1, the SQL statement to be processed does not contain an aggregate function, a window function, or a distinct function.

9. The method for processing SQL statements according to claim 1, further comprising: If the execution cost of the SQL statement to be processed is less than the processing result of the SQL statement to be processed, saving the processing result of the SQL statement to be processed; If the execution cost of the SQL statement to be processed is not less than the processing result of the SQL statement to be processed, the processing result of the SQL statement to be processed is deleted.

10. A device for processing SQL statements, the device comprising: An acquisition module, used for acquiring a SQL statement to be processed, wherein the SQL statement to be processed includes a table to be queried, and an order by function, a limit function and an in conditional expression for the table to be queried; An index module, used to obtain the index of the table to be queried, determine the de-sequencing index in the index of the table to be queried, and determine at least one in conditional expression to be rewritten related to the de-sequencing index in the in conditional expression of the SQL statement to be processed; A rewriting module, used for determining in turn the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, and rewriting the SQL statement to be rewritten in the following manner: constructing a temporary table with multiple rows and one column based on multiple constants of the in conditional expression, replacing the in conditional expression to be rewritten in the SQL statement to be rewritten with a corresponding equivalent expression to form a clause, establishing a horizontal connection between the temporary table and the clause, and replacing the query table and query condition of the SQL statement to be rewritten with the result of the horizontal connection; The push-down module is used to push down the order by function and the limit function for the table to be queried to the clause constructed when rewriting the last SQL statement to be rewritten, so as to obtain the processing result of the SQL statement to be processed.

11. The SQL statement processing device according to claim 10, wherein the rewriting process of the SQL statement to be rewritten further comprises the following steps: The projection column is updated to the query result of the clause.

12. The SQL statement processing device according to claim 10, wherein the rewriting process of the SQL statement to be rewritten further comprises the following steps: Create a distinct function for the temporary table; The establishing a lateral connection between the temporary table and the clause includes: A lateral connection is established between the execution result of the distinct function and the clause.

13. The SQL statement processing device according to claim 10, wherein the equivalent expression corresponding to the in conditional expression to be rewritten is used to characterize the equivalent relationship between the columns in the in conditional expression to be rewritten and the columns of the temporary table.

14. The SQL statement processing device according to claim 10, wherein the rewriting module is used to sequentially determine the SQL statement to be rewritten corresponding to each in conditional expression to be rewritten, and is used to: For the first in conditional expression to be rewritten, the SQL statement to be processed is used as the corresponding SQL statement to be rewritten; For in conditional expressions other than the first one to be rewritten, the clause constructed when rewriting the previous SQL statement to be rewritten is used as the corresponding SQL statement to be rewritten.

15. The SQL statement processing device according to claim 10, wherein the index module is used to obtain the index of the table to be queried, and when determining the de-ordering index in the index of the table to be queried, is used to: At least one index of the table to be queried is selected in sequence until the selected index meets a preset condition and the selected index is determined as a de-ordered index, wherein: The preset conditions include: the result of removing the set of equivalent columns in the SQL statement to be processed from the sorting column set in the SQL statement to be processed, and the prefix of the result of removing the set of equivalent columns in the SQL statement to be processed from the set of columns included in the index, the sorting column set is the columns targeted by the order by function, and the set of equivalent columns is the columns in all equivalent expressions including the in conditional expression.

16. The SQL statement processing device according to claim 10, wherein the index module is used to, when determining at least one in conditional expression to be rewritten related to the deordering index in the in conditional expression of the SQL statement to be processed, If the column in the in conditional expression is a column in the column set included in the desequencing index, it is determined that the in conditional expression is an in conditional expression to be rewritten that is related to the desequencing index.

17. A computer program product, comprising a computer program / instruction, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 9.

18. An electronic device, comprising: processor; a memory for storing processor-executable instructions; The processor implements the method according to any one of claims 1 to 9 by running the executable instructions.

19. A computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the steps of the method according to any one of claims 1 to 9.

Images