Method, device, electronic device and medium for optimizing execution of SQL statements

Abstract

This specification discloses a method, device, electronic device, and medium for optimizing the execution of SQL statements. The method includes: generating a first parameter view based on the parameters of multiple original SQL statements, wherein the multiple original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, and each record in the first parameter view corresponds to a parameter of an original SQL statement. Rewriting the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein the parameters of the target SQL statements are set to reference parameters corresponding to the records in the first parameter view. Generating and executing an execution plan for the target SQL statement, wherein the execution plan is used to call the parameters corresponding to the records in the first parameter view to perform the target change operation on the target table.

Description

Technical Field

[0001] This document belongs to the field of data processing technology, and in particular relates to a method, device, electronic device and medium for optimizing the execution of SQL statements. Background Art

[0002] Structured Query Language (SQL) is a commonly used relational database operation language. To reduce the cost of RPC interactions and execution context switches between clients and databases, traditional databases offer batch execution protocols to optimize this overhead, such as MySQL's Multiple Statement Execution (MSE).

[0003] In existing technologies, when relational databases execute multiple SQL statements, they typically process them one by one within the kernel. This process of processing each SQL statement individually incurs multiple link overheads, resulting in suboptimal execution performance. Therefore, this paper aims to address the technical problem of how to avoid the excessive overhead incurred when executing SQL statements in batches, thereby improving the performance of relational database batch SQL execution. Summary of the Invention

[0004] The embodiments of this specification provide a method, device, electronic device, and medium for optimizing the execution of SQL statements, which can avoid excessive overhead when executing SQL statements in batches, thereby improving the performance of executing SQL statements in batches in a relational database.

[0005] To solve the above technical problems, the embodiments of this specification are implemented as follows:

[0006] First, a method for optimizing the execution of SQL statements is proposed, including:

[0007] generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of one of the original SQL statements;

[0008] rewriting the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view;

[0009] An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

[0010] Secondly, an optimized execution device for SQL statements is proposed, comprising:

[0011] a view generation module, generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, wherein each record of the first parameter view corresponds to a parameter of an original SQL statement;

[0012] a statement rewriting module, rewriting the plurality of original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view;

[0013] A statement execution module generates and executes an execution plan for the target SQL statement, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

[0014] In a third aspect, an electronic device is provided, comprising: a processor; and a memory arranged to store computer-executable instructions, wherein when the instructions are executed, the processor performs the following operations:

[0015] generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of one of the original SQL statements;

[0016] rewriting the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view;

[0017] An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

[0018] In a fourth aspect, a computer-readable storage medium is provided, wherein the computer-readable storage medium stores one or more programs. When the one or more programs are executed by an electronic device including a plurality of application programs, the electronic device performs the following operations:

[0019] generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of one of the original SQL statements;

[0020] rewriting the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view;

[0021] An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

[0022] When it is necessary to batch execute multiple SQL statements of the same change operation type on a target table, the solution of the embodiment of this specification extracts the parameters of each SQL statement to generate a parameter view, wherein each record in the parameter view corresponds to the parameters of an original SQL statement. Afterwards, the multiple SQL statements are rewritten into a target SQL statement for the same type of change operation, and the parameters of the target SQL statement are set to reference the parameters in the parameter view, thereby generating an execution plan for calling the parameters of each record in the parameter view based on the target SQL statement to perform the change operation on the target table. In other words, the change operations to be performed by the original multiple SQL statements are equivalently executed with one execution plan, so there is no need to consider the execution order between the original multiple SQL statements, thereby eliminating the context overhead and significantly improving the performance of batch execution of SQL statements. BRIEF DESCRIPTION OF THE DRAWINGS

[0023] The drawings described herein are used to provide a further understanding of this specification and constitute a part of this specification. The exemplary embodiments and descriptions of this specification are used to explain this specification and do not constitute an improper limitation of this specification. In the drawings:

[0024] Figure 1 This is a schematic diagram of the first flow chart of the method for optimizing the execution of SQL statements provided in the embodiments of this specification.

[0025] Figure 2 Schematic diagram of the execution plan of the target SQL statement optimized by the optimization execution method according to the embodiment of this specification.

[0026] Figure 3 Schematic diagram of another execution plan of a target SQL statement optimized by the optimization execution method according to an embodiment of this specification.

[0027] Figure 4 This is a schematic diagram of the structure of the optimized execution device for SQL statements provided in the embodiments of this specification.

[0028] Figure 5 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this specification. DETAILED DESCRIPTION

[0029] To further clarify the objectives, technical solutions, and advantages of this document, the following will provide a clear and complete description of the technical solutions of this specification in conjunction with the specific embodiments of this specification and the corresponding drawings. Obviously, the described embodiments are only a portion of the embodiments of this document, and not all of them. All other embodiments derived by persons of ordinary skill in the art based on the embodiments in this specification without inventive effort are also within the scope of protection of this document.

[0030] As mentioned above, when executing multiple SQL statements, current relational databases usually process them one by one in the kernel. The process of processing each SQL statement separately in the kernel will incur multiple link overheads, resulting in unsatisfactory execution performance.

[0031] Here we take MySQL's Multiple Statement Execution as an example.

[0032] Currently, Multiple Statement Execution defines multiple SQL statements in a single string to request batch execution from a relational database. Each SQL statement in the string is separated by a semicolon (;).

[0033] For example, a Multiple Statement Execution request is as follows:

[0034] create table t1(a int primary key, b int);

[0035] update t1 set b=2where a=1; update t1 set b=3where a=2; update t1 set b=4where a=3; update t1 set b=5where a=4;

[0036] An update statement is an SQL statement that updates a table. Its syntax is: "update table name set column name = update field value where column name = match field value." For example, "update t1 set b = 2 where a = 1" means updating the column in table t1 where a = 1 to b = 2.

[0037] As can be seen, the above Multiple Statement Execution request contains multiple update statements. The normal execution process of Multiple Statement Execution is to execute each update statement serially in the order in which they are listed in the request. Therefore, in theory, later-executed update statements can see the incremental changes made by earlier statements and can continue to add incremental changes based on the results of previous executions. The execution process follows an alternating "read-write-read-write" pattern. Because the execution order of update statements must be strictly adhered to, a significant amount of context overhead is incurred. While Multiple Statement Execution can request multiple SQL statements to a relational database at once, thereby reducing the interaction cost between the client and the database, it does not provide any performance optimizations during the execution phase.

[0038] To address the above issues, this document aims to provide an optimized SQL statement execution solution that rewrites multiple SQL statements that perform the same change on a target table into a single SQL statement. This consolidates the original multiple SQL statements into a single execution plan, resulting in a "read-read-write-write" execution flow that does not strictly require a specific execution order. This model not only avoids the large number of transactions associated with single, row-by-row execution, but also eliminates the need to consider the execution order of different SQL statements, thus eliminating the significant context overhead. This significantly improves the performance of batch SQL statement execution in relational databases.

[0039] Figure 1 is a flowchart of a method for optimizing the execution of SQL statements according to an embodiment of the present specification. Figure 1 The method shown can be performed by the corresponding device below, and the specific steps include:

[0040] S102, generating a first parameter view based on parameters of multiple original SQL statements, where the multiple original SQL statements have the same grammatical structure and are all used to execute a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of an original SQL statement.

[0041] In this specification, multiple original SQL statements are SQL statements to be executed in batches. The target change operation can be any of the following: an update operation based on an update statement, a delete operation based on a delete statement, and an insert operation based on an insert statement.

[0042] It should be noted that although the grammatical structure of each original SQL statement is the same, the parameters used as variables in the grammatical structure are not necessarily the same.

[0043] For example, there are two update statements to be executed in batches:

[0044] Update statement 1, update t1 set b = 2 where a = 1;

[0045] Update statement 2, update t1 set b=3where a=2.

[0046] It can be seen that the set values and where values of the two update statements are different, so set and where are the parameters of the variable.

[0047] Similarly, the syntax for a delete statement is: delete from table name [where matching field value], indicating that the where parameter matches the field value in the target table as the parameter of the delete statement. The syntax for an insert statement is: insert [into] [column name] values <column value>, indicating that the values parameter to insert the field value in the target table as the parameter of the insert statement.

[0048] It can be seen that different SQL statements of the same type are equivalent to reflecting different data operations by setting different variable parameters. This step is to extract the parameters used as variables from multiple original SQL statements to generate a first parameter view.

[0049] Specifically, each record of the first parameter view in this specification corresponds to a unique ID stmt_id to represent the corresponding original SQL statement.

[0050] Here we use the Multiple Statement Execution request above as an example. Assume that the string in the Multiple Statement Execution request is as follows:

[0051] create table t1(a int primary key, b int);

[0052] update t1 set b=2where a=1; update t1 set b=3where a=2; update t1 set b=4where a=3; update t1 set b=5where a=4;

[0053] The structure of the corresponding first parameter view v1 is:

[0054]

[0055]

[0056] in:

[0057] stmt_id=0 is the record ID of the update statement "update t1 set b=2where a=1";

[0058] stmt_id=1 is the record ID of the update statement "update t1 set b=3where a=2";

[0059] stmt_id=2 is the record ID of the update statement "update t1 set b=4where a=3";

[0060] stmt_id=3 is the record ID of the update statement "update t1 set b=5 where a=4".

[0061] It can be seen that the first parameter view records the corresponding parameters for each update statement in the Multiple Statement Execution request.

[0062] It should be noted that the Multiple Statement Execution request cited above is for illustrative purposes only. In actual applications, multiple original SQL statements that need to be executed in batches can be requested using other methods, which are not specifically limited here.

[0063] S104: rewrite the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, and set the parameters of the target SQL statements to reference the parameters corresponding to the records in the first parameter view.

[0064] Let's take the Multiple Statement Execution request mentioned above as an example. Assume the string of the Multiple Statement Execution request is:

[0065] create table t1(a int primary key, b int);

[0066] update t1 set b=2where a=1; update t1 set b=3where a=2; update t1 set b=4where a=3; update t1 set b=5where a=4;

[0067] According to the structure of the first parameter view v1 above, multiple original update statements in the string can be rewritten into a target update statement, namely:

[0068] update t1,v1 set t1.b=v1.b where t1.a=v1.a;

[0069] In the target update statement, the update operation still uses the previous "update..., set... where... " syntax structure, but set sets the value of field b to the value of field b in the calling View v1, and where sets the value of field a to the value of field a in the calling View v1.

[0070] It should be noted that the method of rewriting multiple original delete statements into one target delete statement, or rewriting multiple original insert statements into one target insert statement, is the same as the method of rewriting the update statement, and no further examples are given here.

[0071] S106: Generate an execution plan for the target SQL statement. The execution plan calls the parameters of each group in the parameter view and performs the target change operation on the target table.

[0072] It should be understood that an execution plan is the specific execution process of a SQL statement, primarily reflecting information such as the order in which tables are read, the type of data read operation, which indexes are used, references between tables, and the operators at each stage. The target SQL statement in this specification must perform target changes on the target table according to the corresponding execution plan.

[0073] The embodiment of this specification executes the execution plan of the target SQL statement to perform the target change operation corresponding to each original SQL statement on the target table.

[0074] Let's take the target update statement as "update t1,v1 set t1.b=v1.b where t1.a=v1.a" as an example.

[0075] The rewritten target update statement has the following execution branches:

[0076] 1) Call the variable parameter of the group with stmt_id=0 to update the data of table t1 based on "update t1 set b=2where a=1".

[0077] 2) Call the variable parameter of the group with stmt_id=1 to identify the group based on "update t1 set b=3where a=2";

[0078] 3) Call the variable parameter of the group with stmt_id=2 based on the group identifier of "update t1 set b=4where a=3";

[0079] 4) Call the variable parameter of the group with stmt_id=3 to identify the group based on "update t1 set b=5where a=4".

[0080] In summary, when it is necessary to batch execute multiple SQL statements of the same change operation type on a target table, the method of the embodiment of this specification extracts the parameters of each SQL statement to generate a parameter view, wherein each record in the parameter view corresponds to the parameters of an original SQL statement. Afterwards, the multiple SQL statements are rewritten into a target SQL statement for the same type of change operation, and the parameters of the target SQL statement are set to reference the parameters in the parameter view, thereby generating an execution plan for calling the parameters of each record in the parameter view based on the target SQL statement to perform the change operation on the target table. In other words, the change operations to be performed by the original multiple SQL statements are equivalently executed with one execution plan, so there is no need to consider the execution order between the original multiple SQL statements, thereby eliminating the context overhead and significantly improving the performance of batch execution of SQL statements.

[0081] Specifically, in the embodiments of this specification, the Batch execution plan is equivalently rewritten into a general form of join+update / delete between a specially defined internal parameter view and the updated table. The left branch of the join is the parameter view, and the right branch of the join is the query of the DML statement itself. By traversing the parameters of the left branch parameter view, the query of the right branch of the join is driven, in order to achieve the effect of executing all sets of parameters.

[0082] Furthermore, because the embodiments of this specification integrate multiple original SQL statements into a single execution plan, the execution process follows a "read-read-write-write" pattern, which does not strictly require an execution order. If multiple original SQL statements involve incremental modifications to the target table, the "read-read-write-write" execution pattern cannot correctly implement incremental modifications to the target table.

[0083] For example, the string in the following Multiple Statement Execution request:

[0084] create table t_overlap(a int primary key, b int);

[0085] insert into t_overlap values(1,1);

[0086] update t_overlap set a=2where a=1; update t_overlap set a=3where a=2; update t_overlap set a=4where a=3; / /

[0087] Among them, "update t_overlap set a=3where a=2" updates the new value of a=3, and "update t_overlap set a=4where a=3" updates the position a=3. That is to say, "update t_overlap set a=4where a=3" must be executed after "update t_overlap set a=3where a=2" is executed.

[0088] To this end, when multiple original SQL statements need to be executed in batches, the embodiments of this specification can perform syntax analysis on the multiple original SQL statements to determine whether the multiple original SQL statements meet the SQL merge rewrite conditions.

[0089] The SQL merge rewrite condition includes at least one of the following:

[0090] There is no update operation on the predicate column in multiple original SQL statements;

[0091] There is no situation where multiple changes are performed on the same record in multiple original SQL statements;

[0092] There is no inconsistency in the corresponding parameter types among the multiple original SQL statements.

[0093] It should be understood that the predicate column in the embodiments of this specification refers to the query field in the where clause. Multiple original SQL statements do not update the predicate column; that is, the parameters of multiple original SQL statements do not modify the SQL predicate column conditions. For example, in update t1 set c1=10where c1=1, the SQL predicate column is c1, but c1 itself is also modified, which does not meet the SQL merge rewrite conditions.

[0094] It should be understood that multiple original SQL statements do not modify the same record multiple times, that is, multiple modification operations (updates or deletes) are not performed on the same record. For example, SQL1: update t1 set a1 = 10 where c1 = 1; SQL2: update t1 set a1 = 5 where c1 = 1. In these two statements, field a1 is modified twice for the record that meets the condition c1 = 1, which does not meet the SQL merge rewrite condition.

[0095] It should be understood that the existence of inconsistent parameter types in the multiple original SQL statements means that the syntax types of the corresponding parameters in any two original SQL statements must be consistent. For example, update t1 set a=1where b=1; update t1 set a=2where b='2'; these two SQL statements violate constant type consistency and cannot be optimized. If the SQL merge rewrite conditions are met, a decision is made to rewrite the multiple original SQL statements and generate a first parameter view based on the parameters of the multiple original SQL statements. Conversely, if the SQL merge rewrite conditions are not met, the multiple original SQL statements can be executed one by one.

[0096] In addition, the embodiment of this specification optimizes multiple original SQL statements to be executed by an execution plan of a target SQL statement. In the execution plan of the target SQL statement, if the target change operation fails for any parameter recorded in the first parameter view (for example, a foreign key constraint violation occurs), the target SQL statement needs to be rolled back.

[0097] Once the target SQL statement is rolled back, all original SQL statements before optimization are equivalent to not being executed. Obviously, the more original SQL statements are executed in a batch, the greater the probability that the target SQL statement will be rolled back, which can easily lead to the problem of the original SQL statement being stuck.

[0098] To address this issue, in an embodiment of the present specification, when a target SQL statement fails to execute, the execution operation of the target SQL statement can be rolled back first, and then other original SQL statements except the original SQL statement corresponding to the record where the execution failed in multiple original SQL statements can be executed one by one.

[0099] Alternatively, if the target SQL statement fails to execute, embodiments of this specification may also record the row identifier of the record in the first parameter view that failed to execute and roll back the execution of the target SQL statement. Subsequently, a second parameter view is generated based on the records before the row identifier of the record in the first parameter view that failed to execute, and a third parameter view is generated based on the records after the row identifier of the record in the first parameter view that failed to execute. The parameters of the target SQL statement are set to reference the parameters corresponding to the records in the second parameter view, and an execution plan for the target SQL statement is generated and executed. Furthermore, the parameters of the target SQL statement are set to reference the parameters corresponding to the records in the third parameter view, and an execution plan for the target SQL statement is generated and executed.

[0100] That is, the target record that failed to execute in the first parameter view is extracted, and the records before the target record in the first parameter view are merged into an execution plan for the target SQL statement and executed uniformly; the records after the target record in the first parameter view are merged into an execution plan for the target SQL statement and executed uniformly.

[0101] It should be understood that the row identifiers of records in the embodiments of this specification can be used to represent the order of records in the parameter view. For example, the order in which the parameters of multiple original SQL statements are passed in can be used as row identifiers. Of course, additional field values can also be added as row identifiers, and this embodiment of this specification does not limit this. In this embodiment of this specification, stmt_id in the parameter view is used to represent the row identifier.

[0102] As an example, assume that the structure of the first parameter view v1 is:

[0103] a b stmt_id 1 2 0 2 3 1 3 4 2 4 5 3 5 6 4 6 7 5

[0104] If, during the execution of the target SQL statement, the parameter with stmt_id=3 is called to perform a target change operation on the target table and fails, stmt_id=3 is marked.

[0105] After the target update statement is rolled back, on the one hand, a second parameter view V2 with the following structure is generated based on stmt_id=0, stmt_id=1, and stmt_id=2 before stmt_id=3:

[0106] a b stmt_id 1 2 0 2 3 1 3 4 2

[0107] The parameters of the target SQL statement are set to the parameters corresponding to the records in the third parameter view v2, and an execution plan for the target SQL statement is generated and executed.

[0108] On the other hand, a third parameter view V3 having the following structure is generated based on stmt_id=4 and stmt_id=5 following stmt_id=3:

[0109] a b stmt_id 5 6 4 6 7 5

[0110] For stmt_id=3, no further execution is performed.

[0111] In addition, the embodiments of this specification can record the execution information of the corresponding target change operation during the execution of the target SQL statement. After the execution plan of the target SQL statement is completed, the execution information corresponding to each record in the first parameter view is summarized and uniformly returned.

[0112] Optionally, as an embodiment, step S106 may be implemented as follows:

[0113] If the target change operation is an update operation based on an update statement, a join operator is generated between the first parameter view and the target table, where the left branch of the join operator is the first parameter view and the right branch of the join operator is a query of the target table;

[0114] Traverse the parameters of the left branch parameter view of the join operator to update the target table to drive the query of the right branch of the join.

[0115] Alternatively, optionally, as another embodiment, step S106 may be implemented as follows:

[0116] If the target change operation is a delete operation based on a delete statement, a join operator is generated between the first parameter view and the target table, where the left branch of the join operator is the first parameter view and the right branch of the join operator is a query of the target table;

[0117] The parameters of the left branch parameter view of the join operator are traversed to perform a delete operation on the target table to drive the query of the right branch of the join.

[0118] The following describes the execution plan using the target update statement as an example of the target SQL statement.

[0119] Specifically, assuming the target update statement is: update t1,v1 set t1.b=v1.b where t1.a=v1.a, the execution plan of the target update statement in this specification uses MySQL's Nested Loop Join algorithm to uniformly execute the execution plan of each original update statement.

[0120] Figure 2It is the execution plan structure diagram corresponding to the target update statement. In the embodiment of this specification, the execution plan of Batch can be equivalently rewritten into a general form of join+update / delete between a specially defined internal parameter view and the updated table. The left branch of the join is the parameter view, and the right branch of the join is the query of the DML statement itself. By traversing the parameters of the left branch parameter view, the query of the right branch of the join is driven, in order to achieve the effect of executing all groups of parameters. Specifically, if Figure 2 As shown, the Table Update operator, Nested Loop Join operator, and ObExprValues operator can be deployed.

[0121] The ObExprValues operator is responsible for outputting the parameters in parameter view v1 row by row (one row corresponds to one group) to the Nested Loop Join operator (each row corresponds to one group). It should be noted that each parameter group in parameter view v1 has a corresponding stmt_id. When the ObExprValues operator outputs the variable parameters in parameter view v1, it also needs to associate the output group identifier v1.stmt_id with the output group to maintain the group mapping.

[0122] The Nested Loop Join operator drives the Table Scan operator to scan data based on the parameters output by the ObExprValues operator. It then outputs the matching data to the Table Update operator to update t1. Similarly, when the Nested Loop Join operator outputs the scanned data, it also associates the output group identifier v1.stmt_id with the output to maintain the mapping relationship.

[0123] Furthermore, if the target original SQL statement among the multiple original statements includes a query based on a join statement, the right branch of the join operator is the query based on the join statement.

[0124] It should be understood that some original update statements themselves may contain queries based on join statements, such as:

[0125] update t1

[0126] [inner join|left join]t2 ON t1.id=t2.relation_id

[0127] set t1.column = t2.column

[0128] where condition;

[0129] The general meaning of this update statement is to associate table t1 with table t2, and then update the data of the target field in table t1 based on the data of the target field in table t2.

[0130] If the target original SQL statement among the multiple original update statements to be executed contains the above-mentioned query based on the join statement, then Figure 3 As shown, you also need to set a target query based on the join statement under the Nested LoopJoin operator in the execution plan of the target update statement. This target query is associated with the parameter view v1 and the output parameters TSC(t1) and TSC(t2) of the target original SQL statement.

[0131] When the Table Update operator updates table t1, it records the stmt_ids for which updates failed. When the target update is rolled back, the operator aggregates the previously successfully updated stmt_ids and re-calls the variable parameters in batches to update table t1. It then calls the variable parameters for each stmt_id that failed to update, performing additional updates to table t1. Furthermore, after each stmt_id is executed, the variable parameters are associated with the stmt_id and record the execution information required for the response. After all stmt_id variable parameters are executed, a unified response is generated based on the execution information for each stmt_id.

[0132] Similarly, the embodiments of this specification can refer to Figure 2 , rewrite the multiple original delete statements that need to be executed in batches into an execution plan for a target delete statement. Since the principle is the same, only Figure 2 、 Figure 3 The Update operation shown can be replaced with a delete operation, and no further examples are given here.

[0133] Figure 4 FIG. 4 is a schematic diagram of an optimized execution device 400 for SQL statements provided in one embodiment of this specification, comprising:

[0134] The view generation module 410 generates a first parameter view based on the parameters of multiple original SQL statements. The multiple original SQL statements have the same grammatical structure and are all used to perform the target change operation of the target table. Each record of the first parameter view corresponds to a parameter of an original SQL statement.

[0135] The statement rewriting module 420 rewrites the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view.

[0136] The statement execution module 430 generates and executes an execution plan for the target SQL statement, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

[0137] When the device of the embodiment of this specification needs to execute multiple SQL statements of the same change operation type on the target table in batches, the parameters of each SQL statement are extracted to generate a parameter view, wherein each record in the parameter view corresponds to the parameters of an original SQL statement. Afterwards, the multiple SQL statements are rewritten into a target SQL statement for the same type of change operation, and the parameters of the target SQL statement are set to reference the parameters in the parameter view, thereby generating an execution plan for calling the parameters of each record in the parameter view based on the target SQL statement to perform the change operation on the target table. In other words, the change operation to be performed by the original multiple SQL statements is equivalently executed with one execution plan, so there is no need to consider the execution order between the original multiple SQL statements, thereby eliminating the context overhead and significantly improving the performance of batch execution of SQL statements.

[0138] Optionally, the target change operation is an update operation based on an update statement, and the parameters corresponding to each group include: a set parameter representing the updated field value in the target table and a where parameter representing the matching field value in the target table; or, the target change operation is a delete operation based on a delete statement, and the parameters corresponding to each group include: a where parameter representing the matching field value in the target table.

[0139] Optionally, the view generation module 410 generates a first parameter view based on the parameters of multiple original SQL statements, including: performing grammatical analysis on the multiple original SQL statements to determine whether the multiple original SQL statements meet the SQL merge rewrite conditions; if the SQL merge rewrite conditions are met, generating a first parameter view based on the parameters of the multiple original SQL statements.

[0140] The SQL merge and rewrite conditions include at least one of the following:

[0141] There is no update operation on the predicate column in the multiple original SQL statements;

[0142] There is no situation where multiple change operations are performed on the same record in the multiple original SQL statements;

[0143] There is no inconsistency in the corresponding parameter types among the multiple original SQL statements.

[0144] Optionally, if the target SQL statement fails to execute, the statement execution module 430 rolls back the execution operation of the target SQL statement and executes the multiple original SQL statements one by one.

[0145] Optionally, if the target SQL statement fails to execute, the statement execution module 430 performs the following steps: recording the row identifier of the record in the first parameter view that failed to execute, and rolling back the execution operation of the target SQL statement; generating a second parameter view based on the records before the row identifier of the record in the first parameter view that failed to execute, and generating a third parameter view based on the records after the row identifier of the record in the first parameter view that failed to execute; setting the parameters of the target SQL statement to reference the parameters corresponding to the records in the second parameter view, generating and executing an execution plan for the target SQL statement; setting the parameters of the target SQL statement to reference the parameters corresponding to the records in the third parameter view, generating and executing an execution plan for the target SQL statement.

[0146] Optionally, as an embodiment, the statement execution module 430 is specifically configured to:

[0147] If the target change operation is an update operation based on an update statement, a join operator is generated between the first parameter view and the target table, where the left branch of the join operator is the first parameter view and the right branch of the join operator is a query of the target table;

[0148] Traverse the parameters of the left branch parameter view of the join operator to update the target table to drive the query of the right branch of the join.

[0149] Alternatively, optionally, as another embodiment, the statement execution module 430 is specifically configured to:

[0150] If the target change operation is a delete operation based on a delete statement, a join operator is generated between the first parameter view and the target table, where the left branch of the join operator is the first parameter view and the right branch of the join operator is a query of the target table;

[0151] The parameters of the left branch parameter view of the join operator are traversed to perform a delete operation on the target table to drive the query of the right branch of the join.

[0152] Optionally, if the target original SQL statement among the multiple original statements includes a query based on a join statement, the right branch of the join operator is the query based on the join statement.

[0153] Optionally, the device of the embodiment of this specification further includes:

[0154] The reporting execution module records the execution information corresponding to the target change operation during the execution of the target SQL statement; and after the execution plan of the target SQL statement is executed, summarizes the execution information corresponding to each record in the first parameter view and returns it.

[0155] Obviously, the optimization execution device of the embodiment of this specification can achieve Figure 1 The steps and functions in the illustrated embodiment will not be described in detail here.

[0156] Figure 5 This is a schematic diagram of the structure of an electronic device provided by an embodiment of this specification. Figure 5 At the hardware level, the electronic device includes a processor and, optionally, an internal bus, a network interface, and memory. The memory may include internal memory, such as high-speed random-access memory (RAM), or non-volatile memory, such as at least one disk drive. Of course, the electronic device may also include other hardware required for its services.

[0157] The processor, network interface, and memory can be interconnected via an internal bus, which can be an ISA (Industry Standard Architecture) bus, a PCI (Peripheral Component Interconnect) bus, or an EISA (Extended Industry Standard Architecture) bus. The bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 5 Only one bidirectional arrow is used in the diagram, but this does not mean that there is only one bus or one type of bus.

[0158] The memory is used to store programs. Specifically, the program may include program code, which includes computer operating instructions. The memory may include internal memory and non-volatile memory, and provides instructions and data to the processor.

[0159] Among them, the processor reads the corresponding computer program from the non-volatile memory into the memory and then runs it, forming the above-mentioned Figure 4 The optimized execution device shown. The processor executes the program stored in the memory and is specifically used to perform the following operations:

[0160] A first parameter view is generated based on parameters of multiple original SQL statements, where the multiple original SQL statements have the same grammatical structure and are all used to execute a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of an original SQL statement.

[0161] The multiple original SQL statements are rewritten into target SQL statements for performing a target change operation on the target table, and parameters of the target SQL statements are set to parameters corresponding to records referenced in the first parameter view.

[0162] An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

[0163] When the electronic device of the embodiment of this specification needs to batch execute multiple SQL statements of the same change operation type on a target table, it extracts the parameters of each SQL statement to generate a parameter view, where each record in the parameter view corresponds to the parameters of an original SQL statement. The multiple SQL statements are then rewritten into a target SQL statement for the same type of change operation, with the parameters of the target SQL statement set to reference the parameters in the parameter view. This generates an execution plan for the change operation on the target table, calling the parameters of each record in the parameter view based on the target SQL statement. In other words, the change operations originally intended to be performed by multiple SQL statements are equivalently executed using a single execution plan, eliminating the need to consider the execution order between the original multiple SQL statements, thereby eliminating context overhead and significantly improving the performance of batch SQL statement execution.

[0164] The above is as in this manual Figure 1The methods disclosed in the illustrated embodiments can be applied to or implemented by a processor. The processor may be an integrated circuit chip with signal processing capabilities. During implementation, each step of the above method can be completed by hardware integrated logic circuits in the processor or by software instructions. The above processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application-specific integrated circuit (ASIC), a field-programmable gate array (FPGA), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components. The methods, steps, and logic block diagrams disclosed in one or more embodiments of this specification can be implemented or executed. The general-purpose processor can be a microprocessor or any conventional processor. The steps of the methods disclosed in conjunction with one or more embodiments of this specification can be directly implemented and executed by a hardware decoding processor, or by a combination of hardware and software modules in the decoding processor. The software module can be located in a storage medium well-known in the art, such as random access memory, flash memory, read-only memory, programmable read-only memory, electrically erasable programmable memory, registers, etc. The storage medium is located in the memory, and the processor reads the information in the memory and, in conjunction with its hardware, completes the steps of the above method.

[0165] Of course, in addition to software implementation, the electronic device in this specification does not exclude other implementation methods, such as logic devices or a combination of software and hardware, etc. That is to say, the execution subject of the following processing flow is not limited to each logic unit, but can also be hardware or logic devices.

[0166] The embodiments of this specification also provide a computer-readable storage medium that stores one or more programs.

[0167] The one or more programs include instructions that, when executed by a portable electronic device including multiple application programs, enable the portable electronic device to execute Figure 1 The method of the embodiment shown is specifically used to perform the following operations:

[0168] A first parameter view is generated based on parameters of multiple original SQL statements, where the multiple original SQL statements have the same grammatical structure and are all used to execute a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of an original SQL statement.

[0169] The multiple original SQL statements are rewritten into target SQL statements for performing a target change operation on the target table, and parameters of the target SQL statements are set to parameters corresponding to records referenced in the first parameter view.

[0170] An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

[0171] When an electronic device in an embodiment of the present specification needs to execute multiple SQL statements in batches, it groups each SQL statement, generates a parameter view that records the variable parameters corresponding to all groups, and creates a target SQL statement based on the parameter view to replace the multiple SQL statements. The target SQL statement uses the grammatical structure of the original SQL statement, but the variable parameters are set to reference the variable parameters in the parameter view. In this way, the execution plan of multiple SQL statements can be equivalently converted to be executed by a single target SQL statement, eliminating the need to consider the execution order of the original multiple SQL statements, thereby eliminating context overhead and significantly improving the performance of batch SQL statement execution.

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

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

[0174] Computer-readable media includes permanent and non-permanent, removable and non-removable media that can be implemented by any method or technology to store information. The 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, magnetic disk storage 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 transitory computer-readable media (transitory media), such as modulated data signals and carrier waves.

[0175] It should also be noted that the terms "comprises," "includes," or any other variations thereof are intended to encompass non-exclusive inclusion, such that a process, method, commodity, or apparatus that includes a series of elements includes not only those elements but also other elements not explicitly listed, or includes elements inherent to such process, method, commodity, or apparatus. In the absence of further limitations, an element defined by the phrase "comprises a ..." does not exclude the presence of other identical elements in the process, method, commodity, or apparatus that includes the element.

[0176] The various embodiments in this specification are described in a progressive manner. Similar parts between the various embodiments can be referred to in conjunction with each other. Each embodiment focuses on the differences between the other embodiments. In particular, the system embodiments are generally similar to the method embodiments, so the description is relatively simple. For relevant parts, refer to the description of the method embodiments.

Claims

1. A method for optimizing the execution of SQL statements, comprising: generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of one of the original SQL statements; rewriting the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view; An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

2. The method according to claim 1, The target change operation is an update operation based on the update statement. The parameters corresponding to each group include: The set parameter indicates the updated field value in the target table and the where parameter indicates the matching field value in the target table; or, The target change operation is a delete operation based on a delete statement, and the parameters corresponding to each group include: a where parameter representing the matching field value in the target table.

3. The method according to claim 2, generating and executing an execution plan for the target SQL statement, comprising: If the target change operation is an update operation based on an update statement, a join operator is generated between the first parameter view and the target table, where the left branch of the join operator is the first parameter view and the right branch of the join operator is a query of the target table; Traverse the parameters of the left branch parameter view of the join operator to update the target table to drive the query of the right branch of the join.

4. The method according to claim 2, generating and executing an execution plan for the target SQL statement, comprising: If the target change operation is a delete operation based on a delete statement, a join operator is generated between the first parameter view and the target table, where the left branch of the join operator is the first parameter view and the right branch of the join operator is a query of the target table; The parameters of the left branch parameter view of the join operator are traversed to perform a delete operation on the target table to drive the query of the right branch of the join.

5. The method according to claim 3 or 4, further comprising: If the target original SQL statement among the multiple original statements includes a query based on a join statement, the right branch of the join operator is the query based on the join statement.

6. The method according to claim 1 or 2, Generate a first parameter view based on the parameters of multiple original SQL statements, including: Performing grammatical analysis on the multiple original SQL statements to determine whether the multiple original SQL statements meet the SQL merge rewriting condition; If the SQL merge rewriting condition is met, a first parameter view is generated based on the parameters of the multiple original SQL statements.

7. The method according to claim 6, The SQL merge rewrite condition includes at least one of the following: There is no update operation on the predicate column in the multiple original SQL statements; There is no situation where multiple change operations are performed on the same record in the multiple original SQL statements; There is no inconsistency in the corresponding parameter types among the multiple original SQL statements.

8. The method according to claim 1 or 2, further comprising: If the target SQL statement fails to execute, the execution operation of the target SQL statement is rolled back; Execute the multiple original SQL statements one by one.

9. The method according to claim 1 or 2, further comprising: If the target SQL statement fails to execute, the row identifier of the record where the execution fails in the first parameter view is recorded, and the execution operation of the target SQL statement is rolled back; generating a second parameter view based on records before the row identifier of the record in which the execution failed in the first parameter view, and generating a third parameter view based on records after the row identifier of the record in which the execution failed in the first parameter view; Setting the parameters of the target SQL statement to the parameters corresponding to the records in the second parameter view, and generating and executing an execution plan for the target SQL statement; Generate and execute an execution plan for the original SQL statement corresponding to the record where the execution failed; The parameters of the target SQL statement are set to the parameters corresponding to the records in the third parameter view, and an execution plan for the target SQL statement is generated and executed.

10. The method according to claim 1 or 2, further comprising: During the execution of the target SQL statement, recording execution information corresponding to the target change operation; as well as, After the execution plan of the target SQL statement is executed, the execution information corresponding to each record in the first parameter view is summarized and then returned.

11. An optimized execution device for SQL statements, comprising: a view generation module, generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, wherein each record of the first parameter view corresponds to a parameter of an original SQL statement; a statement rewriting module, rewriting the plurality of original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view; A statement execution module generates and executes an execution plan for the target SQL statement, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

12. An electronic device comprising: processor; and a memory arranged to store computer-executable instructions that, when executed, cause the processor to: generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of one of the original SQL statements; rewriting the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view; An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

13. A computer-readable storage medium storing one or more programs, which, when executed by an electronic device including a plurality of application programs, cause the electronic device to perform the following operations: generating a first parameter view based on parameters of a plurality of original SQL statements, wherein the plurality of original SQL statements have the same grammatical structure and are all used to perform a target change operation on a target table, and each record of the first parameter view corresponds to a parameter of one of the original SQL statements; rewriting the multiple original SQL statements into target SQL statements for performing a target change operation on the target table, wherein parameters of the target SQL statements are set to parameters corresponding to records in the first parameter view; An execution plan for the target SQL statement is generated and executed, wherein the execution plan is used to call parameters corresponding to the records of the first parameter view to perform the target change operation on the target table.

Images