Query instruction processing method, device, equipment, medium and program product

Abstract

The embodiments of the present application provide a query instruction processing method, device, equipment, medium and program product. The method is applied to an electronic device including an optimizer, and the method includes: obtaining the original query instruction; determining through the optimizer whether the main query in the original query instruction has an operation to implement deduplication, and determining whether the original query instruction meets the preset conditions; when it is determined that the main query has an operation to implement deduplication, and the original query instruction meets the preset conditions, adding a subquery to the original query instruction to implement the deduplication operation, or removing the subquery in the original query instruction to implement the deduplication operation, to obtain at least one new query instruction equivalent to the original query instruction; determining the execution cost corresponding to each of the original query instruction and at least one new query instruction, and determining the query instruction corresponding to the minimum execution cost as the target query instruction for execution. This method can reduce the execution cost when executing the target query instruction and save computing resources.

Description

Technical Field

[0001] The present application relates to the field of data processing technology, and in particular to a query instruction processing method, device, equipment, medium and program product. Background Art

[0002] The query instructions compiled using structured query language (SQL) can be used to perform database management operations such as data query, data modification or data addition on the database.

[0003] In some scenarios, the query instructions input into the database may be modified by the optimizer to obtain new query instructions that are optimized from the original query instructions. For example, when the original query instructions input are relatively complex, the original query instructions will be simplified and new query instructions will be obtained. However, when the instructions are executed based on the obtained new query instructions, the execution cost may be high, resulting in a large overhead on computing resources when executing the new query instructions. Summary of the invention

[0004] Embodiments of the present application provide a query instruction processing method, apparatus, device, medium, and program product to achieve the purpose of reducing the overhead of computing resources when executing new query instructions.

[0005] In a first aspect, an embodiment of the present application provides a query instruction processing method, which is applied to an electronic device, wherein the electronic device includes an optimizer, and the method includes:

[0006] Obtaining an original query instruction, wherein the original query instruction is an initial query instruction compiled using a structured query language;

[0007] Determine, by the optimizer, whether the main query in the original query instruction has an operation for implementing deduplication, and determine whether the original query instruction meets preset conditions, wherein the preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculation;

[0008] When it is determined that the main query of the original query instruction has an operation for implementing deduplication and the original query instruction meets a preset condition, a subquery for implementing the operation for implementing deduplication is added to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, the subquery for implementing the operation for implementing deduplication is removed to obtain at least one new query instruction equivalent to the original query instruction;

[0009] The optimizer determines the execution costs corresponding to the original query instruction and the at least one new query instruction respectively, and based on the execution costs, determines the query instruction corresponding to the minimum execution cost as the target query instruction, and the target query instruction is used to execute and obtain an execution result.

[0010] In a possible implementation, the deduplication operation includes implementing the deduplication operation through at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by.

[0011] In a possible implementation, adding a subquery for implementing a deduplication operation to the original query instruction includes:

[0012] Detecting, by the optimizer, the deduplication-implementing operation of the main query in the original query instruction, and determining the query table item corresponding to the deduplication-implementing operation of the main query;

[0013] A subquery for implementing a deduplication operation is added to the original query instruction according to the query table item.

[0014] In a possible implementation, the query table items are at least two query table items, and adding a subquery for implementing a deduplication operation to the original query instruction according to the query table items includes:

[0015] Determine any one of the at least two query table items as a query table item of a sub-query, and add a sub-query for implementing a deduplication operation to the original query instruction according to the query table item of the sub-query;

[0016] Alternatively, two or more query table items among the at least two query table items are determined as query table items of a sub-query, and a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table items of the sub-query.

[0017] In a possible implementation manner, the electronic device further includes a grammar, and after obtaining the original query instruction, the method further includes:

[0018] In the case where the main query in the original query instruction includes an operation of implementing deduplication through an operator group by, detecting target column information of the operator group by through the grammarizer to determine whether the target column information complies with the grammar rules of group by;

[0019] When it is determined that the target column information complies with the grammatical rule of the group by, the original query instruction is input into the optimizer.

[0020] In a possible implementation, determining, by the optimizer, the execution costs corresponding to the original query instruction and the at least one new query instruction, respectively, includes:

[0021] For any query instruction among the original query instruction and the at least one new query instruction, the optimizer calculates the amount of data corresponding to executing the any query instruction;

[0022] An execution cost corresponding to executing any query instruction is calculated according to the data volume.

[0023] In a second aspect, an embodiment of the present application provides a query instruction processing device, which is applied to an electronic device, wherein the electronic device includes an optimizer, and the device includes:

[0024] An acquisition module, used for acquiring an original query instruction, wherein the original query instruction is an initial query instruction compiled using a structured query language;

[0025] an optimization module, configured to determine, through the optimizer, whether the main query in the original query instruction has an operation for implementing deduplication, and determine whether the original query instruction meets preset conditions, wherein the preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculation;

[0026] The optimization module is further configured to, when it is determined that the main query of the original query instruction has an operation for implementing deduplication and the original query instruction meets a preset condition, add a subquery for implementing the operation for implementing deduplication to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, remove the subquery for implementing the operation for implementing deduplication, to obtain at least one new query instruction equivalent to the original query instruction;

[0027] The optimization module is also used to determine the execution costs corresponding to the original query instruction and the at least one new query instruction through the optimizer, and based on each of the execution costs, determine the query instruction corresponding to the minimum execution cost as the target query instruction, and the target query instruction is used to execute and obtain an execution result.

[0028] In a possible implementation, the deduplication operation includes implementing the deduplication operation through at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by.

[0029] In a possible implementation manner, the optimization module is specifically used to:

[0030] Detecting, by the optimizer, the deduplication-implementing operation of the main query in the original query instruction, and determining the query table item corresponding to the deduplication-implementing operation of the main query;

[0031] A subquery for implementing a deduplication operation is added to the original query instruction according to the query table item.

[0032] In a possible implementation manner, the query table items are at least two query table items, and the optimization module is specifically configured to:

[0033] Determine any one of the at least two query table items as a query table item of a sub-query, and add a sub-query for implementing a deduplication operation to the original query instruction according to the query table item of the sub-query;

[0034] Alternatively, two or more query table items among the at least two query table items are determined as query table items of a sub-query, and a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table items of the sub-query.

[0035] In a possible implementation, the electronic device further includes a grammar, and the device further includes a grammar module, wherein the grammar module is used to detect target column information of the group by operator through the grammar to determine whether the target column information complies with the grammar rule of group by when the main query in the original query instruction includes an operation of implementing deduplication through the group by operator;

[0036] When it is determined that the target column information complies with the grammatical rule of the group by, the original query instruction is input into the optimizer.

[0037] In a possible implementation manner, the optimization module is specifically used to:

[0038] For any query instruction among the original query instruction and the at least one new query instruction, the optimizer calculates the amount of data corresponding to executing the any query instruction;

[0039] An execution cost corresponding to executing any query instruction is calculated according to the data volume.

[0040] In a third aspect, an embodiment of the present application provides an electronic device, comprising: a memory and a processor; the memory stores computer-executable instructions; the processor executes the computer-executable instructions stored in the memory, so that the processor executes the first aspect above and / or various possible implementations of the first aspect.

[0041] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium, in which computer-executable instructions are stored. When the computer-executable instructions are executed by a processor, they are used to implement the first aspect above and / or various possible implementations of the first aspect.

[0042] In a fifth aspect, an embodiment of the present application provides a computer program product, including a computer program, which, when executed by a processor, implements the above first aspect and / or various possible implementation methods of the first aspect.

[0043] The query instruction processing method, device, equipment, medium and program product provided in the embodiments of the present application are applied to an electronic device, and the electronic device includes an optimizer. After obtaining the original query instruction, the method determines through the optimizer whether the main query in the original query instruction has an operation to implement deduplication, and determines whether the original query instruction meets the preset conditions. The preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculations. The preset condition is a sufficient condition that can be equivalent to adding or deleting a subquery that implements a deduplication operation for the original query instruction. Therefore, when adding or deleting a subquery that implements a deduplication operation for the original query instruction based on the preset condition to generate a new query instruction, a new query instruction equivalent to the original query instruction can be generated to avoid obtaining an erroneous new query instruction that is inconsistent with the execution result of the original query instruction. Furthermore, when it is determined that the main query of the original query instruction has an operation for implementing deduplication, and the original query instruction meets the preset conditions, a subquery for implementing the operation for implementing deduplication is added to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, the subquery for implementing the operation for implementing deduplication is removed, to obtain at least one new query instruction equivalent to the original query instruction. In this way, at least one new query instruction equivalent to the original query instruction can be obtained, and the at least one new query instruction may contain a query instruction with a lower execution cost than the original query instruction. Thereafter, the execution cost corresponding to each of the original query instruction and the at least one new query instruction is determined by the optimizer, and based on each execution cost, the query instruction corresponding to the minimum execution cost is determined as the target query instruction, and the target query instruction is used to execute and obtain the execution result. Based on this, the target query instruction with the minimum execution cost can be obtained, and when the execution is performed based on the target query instruction and the execution result is obtained, the execution cost can be reduced, thereby achieving the purpose of reducing the computing resource overhead. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0045] Figure 1 A flowchart for optimizing and executing query instructions in a related method;

[0046] Figure 2 Schematic diagram of the query instruction processing method provided in the embodiment of the present application Figure 1 ;

[0047] Figure 3 Schematic diagram of data deduplication in a data table provided in an embodiment of the present application Figure 1 ;

[0048] Figure 4 Schematic diagram of data deduplication in a data table provided in an embodiment of the present application Figure 2 ;

[0049] Figure 5 Schematic diagram of the query instruction processing method provided in the embodiment of the present application Figure 2 ;

[0050] Figure 6 A schematic diagram of the structure of a query instruction processing device provided in an embodiment of the present application;

[0051] Figure 7 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application.

[0052] The above drawings have shown clear embodiments of the present application, which will be described in more detail later. These drawings and text descriptions are not intended to limit the scope of the present application in any way, but to illustrate the concept of the present application to those skilled in the art by referring to specific embodiments. DETAILED DESCRIPTION

[0053] 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 the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0054] In the embodiments of the present application, words such as "first" and "second" are used to distinguish the same or similar items with substantially the same functions and effects. For example, the first electronic device and the second electronic device are only used to distinguish different electronic devices, and their order is not limited. Those skilled in the art can understand that words such as "first" and "second" do not limit the quantity and execution order, and words such as "first" and "second" do not necessarily limit them to be different.

[0055] In the embodiments of the present application, the words "exemplary" or "for example" are used to indicate examples, illustrations or descriptions. Any embodiment or design described as "exemplary" or "for example" in the present application should not be interpreted as being more preferred or more advantageous than other embodiments or designs. Specifically, the use of words such as "exemplary" or "for example" is intended to present related concepts in a specific way.

[0056] In the embodiments of the present application, "at least one" means one or more, and "more" means two or more. "And / or" describes the association relationship of the associated objects, indicating that there can be three relationships. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone, where A and B can be singular or plural. The character " / " generally indicates that the associated objects before and after are in an "or" relationship.

[0057] In the technical solutions of the embodiments of the present application, the collection, storage, use, processing, transmission, provision and disclosure of user personal information involved are in compliance with the provisions of relevant laws and regulations and do not violate public order and good morals.

[0058] It should be noted that 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 application 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 the relevant countries and regions, and provide corresponding operation entrances for users to choose to authorize or refuse.

[0059] Figure 1 The flowchart of optimizing and executing query instructions in the related method is shown in FIG. In a database system or a database management system, etc., software for managing a database may include functional components such as an optimizer and an executor. Figure 1 As shown, the SQL query instruction input by the user can be input into the optimizer as the original query instruction, so that the original query instruction can be optimized through the optimization strategy in the optimizer.

[0060] The optimization strategy for deduplication operations provided by the optimizer in the related method includes detecting and eliminating all unnecessary deduplication operations. Therefore, when an original query instruction including one or more deduplication operations is input into the optimizer, the optimizer will decide whether one, multiple or all deduplication operations can be eliminated to obtain an optimized new query instruction.

[0061] After obtaining the optimized new query instruction, the optimizer can generate a corresponding execution plan based on the new query instruction and instruct the executor to execute the instruction according to the execution plan. After obtaining the execution plan, the executor will perform corresponding operations on the data table in the database according to the execution plan to obtain the execution result.

[0062] For example, the original query command input is "Select distinct t1.a,t2.b from(selectdistinct a from t1),t2;". Select means selection; distinct means different, which is an operator used to indicate the deduplication operation; t1 means data table t1, t1.a means column a in data table t1; similarly, t2.b means column b in data table t2; t1.a and t2.b are the objects of the deduplication operation performed by distinct; from means from, and the value following from is the query table item, which can be understood as the object to be queried.

[0063] For example, a subquery can be understood as a query nested in other queries, and can be understood as a query nested in an inner layer; the nested outer layer query can be understood as the main query corresponding to the subquery. The subquery can also be called a sublink, a subconnection, or an inner query, etc., and the main query can also be called the parent query of the subquery or an outer query, etc.

[0064] In the original query instruction "Select distinct t1.a,t2.b from (select distinct a from t1), t2;", (select distinct a from t1) is a subquery nested under the main query of the original query instruction, indicating that the distinct deduplication operation is performed on the a column in the data table t1 to obtain the deduplication result set. The part before the subquery "Select distinct t1.a,t2.b from" is the main query in the original query instruction. The result set of the subquery and the data table t2 following from are the query table items of the main query, which can be understood as the execution object of the main query.

[0065] Since the original query instruction has deduplication operations in the main query and the subquery, the optimizer will analyze each deduplication operation to eliminate as many deduplication operations as possible and obtain a new query instruction equivalent to the original query instruction. For example, if the optimizer analyzes and determines that the deduplication operation in the subquery is unnecessary and can be eliminated, the optimized new query instruction can be "Select distinct t1.a,t2.b from t1,t2;".

[0066] For example, the deduplication operation mainly removes duplicate data in a data table. Since all data in the data table needs to be traversed, the execution cost of the deduplication operation is usually high. The optimization strategy designed for the deduplication operation by the optimizer in the related method is to eliminate the deduplication operation as much as possible. The premise of the optimization strategy is that the deduplication operation will definitely increase the execution cost. Obviously, the premise of the optimization strategy has the defects of blindness and one-size-fits-all.

[0067] In some scenarios, such as scenarios where there is a large amount of duplicate data in the queried data table, if the deduplication operation on the data table is blindly eliminated, it is inevitable to perform operations on many duplicate data when executing query instructions on the data table, and the computing overhead consumed by performing operations on all data may be greater than the computing overhead consumed by first deduplicating the data table and then performing data operations on the deduplicated data table. Therefore, the relevant methods, without detailed consideration of the execution cost dimension of the original query instructions before deduplication optimization and the new query instructions after deduplication optimization, eliminate deduplication operations as much as possible in a one-size-fits-all manner, which may lead to the problem of high computational overhead in executing new query instructions.

[0068] For example, in the original query instruction and the corresponding new query instruction in the above example, from the operator operation point of view, the new query instruction has one less distinct deduplication operation than the original query instruction, and executing the new query instruction may use less computing resource overhead, and the speed of executing the new query instruction may be faster. However, if there are many duplicate data in column a of t1, the subquery in the original query instruction can first deduplicate the duplicate data in column a of t1, which can eliminate more duplicate data. When the main query is executed, the amount of data in the result set of the subquery after deduplication is greatly reduced, and the computing overhead required for executing the main query will be greatly reduced, which will result in the overall computing resource overhead of executing the original query instruction being less than the overall computing resource overhead of executing the new query instruction. For example, if there are 100 data items in column a of t1 and 10,000 data items in column b of t2, then when executing the new query instruction, 100×10,000 (i.e., 1 million) data items need to be queried; while when executing the original query instruction, the deduplication operation of the subquery is first performed to deduplicate the data in column a of t1. Assuming that all 100 data items are duplicate data, 1 data item is obtained after deduplication. When executing based on the main query, only 1×10,000 (i.e., 10,000) data items need to be queried. Therefore, the overall computing resource overhead of executing the original query instruction is less than the overall computing resource overhead of executing the new query instruction.

[0069] Furthermore, since the related technology adheres to the optimization strategy of eliminating deduplication operations as much as possible, the existing optimizer will not set an optimization strategy to actively add deduplication operations to the original query instructions to generate new query instructions with an execution cost lower than the original query instructions.

[0070] In summary, when a new query instruction is obtained by optimizing the original query instruction based on relevant technologies, and the instruction is executed based on the new query instruction to obtain the execution result, it will bring about a high execution cost, resulting in a large computing resource overhead.

[0071] In view of this, an embodiment of the present application provides a query instruction processing method, which is applied to an electronic device, and the electronic device includes an optimizer. After obtaining the original query instruction, the method determines through the optimizer whether the main query in the original query instruction has an operation to implement deduplication, and determines whether the original query instruction meets the preset conditions. The preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculations. The preset condition is a sufficient condition that can be equivalent to adding or deleting a subquery that implements a deduplication operation for the original query instruction. Therefore, when adding or deleting a subquery that implements a deduplication operation for the original query instruction based on the preset condition to generate a new query instruction, a new query instruction equivalent to the original query instruction can be generated to avoid obtaining an erroneous new query instruction that is inconsistent with the execution result of the original query instruction. Furthermore, when it is determined that the main query of the original query instruction has an operation for implementing deduplication, and the original query instruction meets the preset conditions, a subquery for implementing the operation for implementing deduplication is added to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, the subquery for implementing the operation for implementing deduplication is removed, to obtain at least one new query instruction equivalent to the original query instruction. In this way, at least one new query instruction equivalent to the original query instruction can be obtained, and the at least one new query instruction may contain a query instruction with a lower execution cost than the original query instruction. Thereafter, the execution cost corresponding to each of the original query instruction and the at least one new query instruction is determined by the optimizer, and based on each execution cost, the query instruction corresponding to the minimum execution cost is determined as the target query instruction, and the target query instruction is used to execute and obtain the execution result. Based on this, the target query instruction with the minimum execution cost can be obtained, and when the execution is performed based on the target query instruction and the execution result is obtained, the execution cost can be reduced, thereby achieving the purpose of reducing the computing resource overhead.

[0072] The technical solution of the present application is described in detail with specific embodiments below. The following specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of the present application will be described below in conjunction with the accompanying drawings.

[0073] Figure 2 Schematic diagram of the query instruction processing method provided in the embodiment of the present application Figure 1The execution subject of the method may be an electronic device with corresponding data storage and computing capabilities, such as a computer, a server or a server cluster. The electronic device includes an optimizer, which can be understood as a component or module in a database system or a database management system for processing input query instructions, optimizing query instructions and formulating corresponding execution plans. The optimizer may be a software module composed of a computer program. Figure 2 As shown, the method includes:

[0074] S201, obtaining an original query instruction, where the original query instruction is an initial query instruction compiled using a structured query language.

[0075] Exemplarily, the structured query language is SQL, and the query instruction may also be referred to as an SQL instruction or an SQL query instruction. The original query instruction may be an SQL instruction input by a user, or an SQL instruction automatically generated by an algorithm, and the original query instruction is a query instruction that has not been optimized by an optimizer. The original query instruction may be obtained by receiving an input from a user, or by receiving an automatically generated SQL instruction, or by other methods.

[0076] S202, determining through the optimizer whether the main query in the original query instruction has an operation to implement deduplication, and determining whether the original query instruction meets preset conditions, the preset conditions including that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculations.

[0077] Exemplarily, after obtaining the original query instruction, the original query instruction can be input into the optimizer so that the optimizer can optimize the original query instruction and / or formulate an execution plan, etc. For example, the original query instruction can be detected by a preset program in the optimizer to determine whether the main query in the original query instruction has an operation to implement deduplication, and to determine whether the original query instruction meets the preset conditions. It should be understood that determining whether the main query in the original query instruction has an operation to implement deduplication and determining whether the original query instruction meets the preset conditions can be performed simultaneously or sequentially; if the determination is performed sequentially, the order of the two can be unlimited.

[0078] In a possible implementation, the deduplication operation includes implementing the deduplication operation through at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by.

[0079] Exemplarily, the operator distinct is used in SQL instructions to perform deduplication operations on duplicate data in query table items to obtain a result set without duplicate data. Union means union, and is used in SQL instructions to merge query objects to obtain a union. When executing union, the deduplication operation can be preset. Therefore, the deduplication effect can also be achieved through the deduplication operation in the union operator. The grouping operator group by, where group means grouping and by means according to, group by can be used to group rows with the same value. Therefore, the deduplication effect can also be achieved through the operator group by. The operation to achieve deduplication can be understood as retaining only one copy of duplicate data in a data table or other data object, and removing other data that is the same as the data.

[0080] In this embodiment, the deduplication operation can be achieved by at least one of the deduplication operator distinct, the deduplication operation in the union operator union, or the grouping operator group by, and the duplicate data in objects such as data tables can be simplified and retained as a copy to obtain a result set without duplicate data.

[0081] For example, in SQL, ROWNUM represents an operator for row numbers, which is a pseudo-column in the database and is used to assign a unique row number to each row in the query result set. For the deduplication operation on the data in the original query instruction, if the target column of the outer query has a deduplication operation, when the original query instruction does not involve operations such as grouping aggregation, ROWNUM and / or window functions, full data calculations, or data labeling, whether the deduplication operation is performed in the inner query has no substantial effect on the execution result. Therefore, by forward-decomposing this situation, the sufficient conditions for adding or deleting the deduplication operation by equivalent transformation of the original query instruction can be described as the following preset conditions.

[0082] Exemplarily, the preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or join, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculations.

[0083] Among them, only including at least one relational algebra operation among selection, projection or join can be understood as using only one or more relational algebra operations among selection, projection and join in SQL, that is, it can be understood as using only SPJ (selection projection join, SPJ). For example, the optimizer detects whether the original query instruction only includes at least one of the predicates Select, from and where, where where represents a condition, which is used to indicate the filtering condition during execution. If it is detected that the predicates in the original query instruction are only one or more of Select, from and where, and there are no other predicates, then the original query instruction meets the condition in the preset condition that the original query instruction only includes at least one relational algebra operation among selection, projection or join.

[0084] In the preset conditions, if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, wherein the grouping operation can be understood as an operation of grouping data objects through group by. A grouping operation of a non-aggregate function can be understood as there is no aggregate function in the execution object of group by. An aggregate function can be a function used to perform calculations on a set of values ​​and return a single summary value. An aggregate function can be used for statistical analysis and data aggregation operations. Aggregate functions are, for example, a SUM function that calculates the sum of a numerical column, an AVG function that calculates the average value of a data column, a MIN function that returns the minimum value of a data column, or a MAX function that returns the maximum value of a data column.

[0085] In the preset condition, if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculation, and the sorting operation can be understood as an operation of sorting data objects through the operator order by.

[0086] The sorting operation may be an operation performed by the operator order by, where order means sorting and by means according to. order by can be used to sort the query objects, for example, the sorting order can be specified according to one or more columns in the data table. The expression of the sorting operation may be an expression of order by, which can be understood as an SQL expression statement that instructs order by to perform a sorting operation. The expression only includes table element calculations, which can be understood as an SQL statement that only has simple table element calculations in the expression, and the table element can be understood as the column data of the data table specified by the expression of order by.

[0087] When the main query of the original query instruction has an operation to implement deduplication and the original query instruction meets the preset conditions, it can be understood that the target column of the outer query of the original query instruction has a deduplication operation, and the deduplication operation of the inner query has no effect on the final execution result. Therefore, when the dual conditions are met, the deduplication operation can be added or deleted to the original query instruction, and an execution result consistent with the execution of the original query instruction can be obtained.

[0088] S203, when it is determined that the main query of the original query instruction has an operation to implement deduplication and the original query instruction meets the preset conditions, a sub-query for implementing the operation to implement deduplication is added to the original query instruction, or, when the original query instruction includes a sub-query for implementing the operation to implement deduplication, the sub-query for implementing the operation to implement deduplication is removed to obtain at least one new query instruction equivalent to the original query instruction.

[0089] Exemplarily, the new query instruction can be understood as a rewritten query instruction obtained by equivalently rewriting the original query instruction. When it is determined that the main query of the original query instruction has an operation to implement deduplication, and the original query instruction meets the preset conditions, a subquery to implement the deduplication operation can be added to the original query instruction to obtain at least one new query instruction equivalent to the original query instruction; or, when the original query instruction includes a subquery to implement the deduplication operation, the subquery to implement the deduplication operation can be removed to obtain at least one new query instruction equivalent to the original query instruction.

[0090] When adding a subquery for implementing a deduplication operation, at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by may be added.

[0091] In a possible implementation, when adding a subquery for implementing a deduplication operation to the original query instruction, it can be specifically implemented in the following manner: the optimizer detects the deduplication operation of the main query in the original query instruction, and determines the query table item corresponding to the deduplication operation of the main query; and according to the query table item, adds a subquery for implementing a deduplication operation to the original query instruction.

[0092] Exemplarily, the original query instruction can be parsed by a preset program in the optimizer to determine the operation for implementing deduplication in the main query, and the object of the operation for implementing deduplication, that is, the query table item corresponding to the operation for implementing deduplication in the main query can be determined. The query table item can be understood as a data object such as a queried data table or a result set of other operations.

[0093] Adding a subquery to the original query instruction to implement deduplication operations based on the query table items can be understood as adding one or more subqueries based on the data object to pre-filter and deduplicate the data object for deduplication in the main query, thereby reducing duplicate data in advance and reducing the amount of data when executing the main query.

[0094] In a possible implementation, if the query table item is a query table item, the query table item may be determined as a query table item of a sub-query, and a sub-query for implementing a deduplication operation is added to the original query instruction.

[0095] For example, the query table item corresponding to the deduplication operation of the main query is to deduplicate column a in data table t1. Then column a in data table t1 can be used as the query table item of the subquery that implements the deduplication operation, so as to add a subquery in the main query to pre-duplicate and filter column a in data table t1, thereby reducing the amount of data when executing the main query.

[0096] In one possible implementation, the query table items are at least two query table items. When a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table items, it can be specifically implemented in the following manner: any one of the at least two query table items is determined as a query table item of the sub-query, and a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table item of the sub-query.

[0097] For example, if the original query instruction is "Select distinct t1.a, t2.b from t1, t2 where t2.a<10;", where t1.a represents column a of data table t1, t2.b represents column b of data table t2, and t2.a represents column a of data table t2, t1.a, t2.b and t2.a are all in the main query, and the main query is the main query that performs deduplication through the operator distinct, so t1.a, t2.b and t2.a are all query table items corresponding to the deduplication operation of the main query, and the query table items at this time are at least two query table items. Therefore, any one of t1.a, t2.b and t2.a can be determined as the query table item of the subquery. For example, after determining t1.a as the query table item of the subquery, and adding a subquery for implementing the deduplication operation to the original query instruction according to t1.a, a new query instruction "Select distinct t1.a,t2.b from (select distincta from t1) as t1,t2 where t2.a<10;" can be obtained. Compared with the original query instruction, the new query instruction adds a subquery "(select distinct a from t1)" for implementing the deduplication operation. The following as can represent as, that is, the execution result of the subquery "(select distinct a from t1)" is used as t1 in the main query.

[0098] In one possible implementation, the query table items are at least two query table items. When a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table items, it can be specifically implemented in the following manner: two or more query table items out of the at least two query table items are determined as query table items of the sub-query, and a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table items of the sub-query.

[0099] Continuing with the example of the original query instruction "Select distinct t1.a,t2.b from t1,t2 where t2.a<10;", in the original query instruction, t1.a, t2.b and t2.a are all query table items corresponding to the deduplication operation of the main query. Therefore, two or more query table items among t1.a, t2.b and t2.a can be determined as the query table items of the subquery, and a subquery for implementing the deduplication operation is added to the original query instruction according to the query table items of the subquery.

[0100] For example, after determining t2.b and t2.a as query table items of the subquery, and adding a subquery for implementing deduplication to the original query instruction according to t2.b and t2.a, a new query instruction "Select distinct t1.a, t2.b from t1, (select distinct a, b from t2) as t2 where t2.a<10;" can be obtained. Compared with the original query instruction, the new query instruction adds a subquery for implementing deduplication "(select distinct a, b from t2)". In the subquery, a and b after distinct represent columns a and b in data table t2. Columns a and b are query table items of the subquery. The data of a row in column a and the data of the row in column b are compared with the data of column a and column b in other rows to see if they are duplicate data, thereby implementing deduplication of data table t2.

[0101] Figure 3 Schematic diagram of data deduplication in a data table provided in an embodiment of the present application Figure 1 ,like Figure 3 As shown, in this data table, there are columns a and b, and each row has its own data under columns a and b. For example, the data of row 1 can be represented as (4, 2), the data of row 2 can be represented as (1, 2), and so on. When performing deduplication for the subquery "(select distinct a,b fromt2)", the query table items are columns a and b, so it is necessary to compare the data of columns a and b in any two rows of data at the same time. For example, Figure 3 In the figure, it is data table t2. When comparing the first row and the second row, it is necessary to compare whether 4 is equal to 1 in the first row (4, 2) and the second row (1, 2), and whether 2 is equal to 2. Since 4 is not equal to 1, although 2 is equal to 2, the data in the first row and the second row are not exactly the same. Therefore, they are not duplicate data and cannot be deduplicated. For another example, when comparing the first row and the third row, it is necessary to compare whether 4 is equal to 4 in the first row (4, 2) and the third row (4, 2), and whether 2 is equal to 2. Since they are equal, the data in the first row and the third row are exactly the same. Therefore, they are duplicate data and can be deduplicated.

[0102] Figure 4 Schematic diagram of data deduplication in a data table provided in an embodiment of the present application Figure 2 ,like Figure 4As shown in the figure, in this data table, there are columns a and b. The data in row 1 can be expressed as (4, 2), the data in row 2 can be expressed as (1, 2), and so on. If you perform deduplication on the subquery "(select distinct a from t1)", the query table item of the subquery is only column a. Therefore, when performing deduplication, you only need to compare whether the data in column a of the two rows are the same. For example, Figure 4 In the data table t1, when comparing the 1st and 2nd rows, it is necessary to compare whether 4 in the 1st row (4, 2) and the 2nd row (1, 2) is equal to 1. Since 4 is not equal to 1, the data in the 1st and 2nd rows are different, so they are not duplicate data and cannot be deduplicated; for another example, when comparing the 1st and 3rd rows, it is necessary to compare whether 4 in the 1st row (4, 2) and the 3rd row (4, 2) is equal to 4. Since the two are the same, the data in the 1st and 3rd rows can be deduplicated. For example, when comparing the 5th, 6th and 7th rows, the data in column a of these three rows are all 6, then only one of the data in these three rows can be retained, and the others can be removed.

[0103] Through the above Figure 3 and Figure 4 It can be seen from the corresponding embodiment that when the number of query table items determined as subqueries increases, the number of data columns involved in the comparison also increases, which will lead to a lower probability that any two rows are duplicate data, so the deduplication strength is also low. On the contrary, when the number of query table items determined as subqueries decreases, the number of data columns involved in the comparison also decreases, so the probability that any two rows are duplicate data increases, so the deduplication strength is also high.

[0104] Based on this, in the present embodiment, any one of the at least two query table items is determined as a query table item of a sub-query, so that a data deduplication effect with a greater degree of deduplication can be achieved, and more duplicate data can be removed, so that the new query instruction obtained by adding the sub-query can reduce the amount of data queried, and thus the execution cost of the new query instruction may be lower.

[0105] In addition, determining two or more query table items of at least two query table items as query table items of a subquery can be applicable to scenarios where multiple columns of data need to be comprehensively compared and deduplicated. Since the data distribution of a data table is usually uncontrollable, when adding a subquery for deduplication based on one, two or more query table items, either method has a certain probability of obtaining a new query instruction with a lower execution cost.

[0106] Exemplarily, when a subquery for implementing a deduplication operation is added to the original query instruction according to the query table item, one subquery, two subqueries, or multiple subqueries may be added.

[0107] For example, for the original query instruction "Select distinct t1.a,t2.b from t1,t2 where t2.a<10;", two subqueries for implementing deduplication operations can be added to it at the same time. The two subqueries can be "(select distinct a from t1)" and "(select distinct a,b from t2)" respectively, and a new query instruction "Select distinct t1.a,t2.b from (select distinct a from t1) as t1, (select distinct a,b from t2) as t2 where t2.a<10;" corresponding to the original query instruction can be obtained. Based on the new query instruction, the query table items of the main query can be pre-filtered in multiple dimensions to obtain a new query instruction with a lower execution cost.

[0108] Exemplarily, when the original query instruction includes a subquery for implementing a deduplication operation, at least one new query instruction equivalent to the original query instruction can be obtained by removing the subquery for implementing a deduplication operation.

[0109] For example, when the main query of the original query instruction contains an operation that implements deduplication through at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by, and the original query instruction includes a subquery that implements a deduplication operation through at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by, the subquery can be eliminated to obtain at least one new query instruction equivalent to the original query instruction.

[0110] The new query instruction obtained based on this removes the deduplication operation of the subquery. Therefore, the execution cost of the new query instruction may be lower due to the reduction of unnecessary deduplication operations.

[0111] S204, determining the execution costs corresponding to the original query instruction and at least one new query instruction respectively through the optimizer, and determining the query instruction corresponding to the minimum execution cost as the target query instruction based on the execution costs, and the target query instruction is used to execute and obtain an execution result.

[0112] Exemplarily, for the original query instruction and at least one new query instruction, the execution cost corresponding to each query instruction can be determined respectively. The execution cost can be a representation of the amount of calculation and / or calculation time required by the electronic device when executing the corresponding query instruction.

[0113] The execution time of a query instruction is calculated based on the specific data volume and the execution plan. In order to improve the execution efficiency of the query instruction, it is necessary to accurately estimate the data volume to more accurately determine the execution cost of the original query instruction or the new query instruction, and then formulate a better execution plan.

[0114] For the optimizer, cardinality estimation and multi-selection cost estimation are of utmost importance. Through the above embodiments, the original query instruction can be filtered out of duplicate data in advance, or unnecessary deduplication operations can be removed, thereby obtaining at least one new query instruction. The execution cost of the new query instruction may be higher or lower than the execution cost of the original query instruction. Therefore, it is necessary to obtain a more accurate execution cost for each query instruction based on cardinality estimation, so as to more accurately compare the pros and cons of each query instruction during execution, and then determine the target query instruction that can be executed at the minimum execution cost.

[0115] In one possible implementation, when the optimizer determines the execution cost corresponding to the original query instruction and at least one new query instruction, it can be specifically implemented in the following way: for any query instruction among the original query instruction and the at least one new query instruction, the optimizer calculates the amount of data corresponding to the execution of any query instruction; and calculates the execution cost corresponding to the execution of any query instruction based on the data amount.

[0116] Exemplarily, for any query instruction in the original query instruction or the new query instruction, the optimizer can determine the execution cost of the query instruction by estimating the cardinality and cost of the query instruction, wherein the cardinality estimation can be understood as an estimation of the amount of data involved in executing the query instruction, and the cost estimation can be an estimation of the computing overhead of the electronic device involved in executing the query instruction, and the obtained execution cost can be a value representing the execution time required to execute the query instruction.

[0117] For example, a data volume estimation program for estimating the data volume of the data object corresponding to the query table entry is preset in the optimizer, and the data volume corresponding to the query instruction can be estimated through the data volume estimation program. After obtaining the data volume, the optimizer can estimate the execution cost of the query instruction based on the data volume and the hardware computing capability of the electronic device, wherein the hardware computing capability of the electronic device can be determined by obtaining the hardware parameters of the storage hardware and computing hardware of the electronic device.

[0118] In this embodiment, for any query instruction in the original query instruction and at least one new query instruction, the optimizer calculates the amount of data corresponding to executing any query instruction, and further calculates the execution cost corresponding to executing any query instruction based on the data amount, thereby improving the accuracy of the calculated execution cost.

[0119] The query instruction processing method provided in the embodiment of the present application is applied to an electronic device, and the electronic device includes an optimizer. After obtaining the original query instruction, the method determines through the optimizer whether the main query in the original query instruction has an operation to implement deduplication, and determines whether the original query instruction meets the preset conditions. The preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculations. The preset condition is a sufficient condition that can equivalently add or delete a subquery that implements a deduplication operation for the original query instruction. Therefore, when adding or deleting a subquery that implements a deduplication operation for the original query instruction based on the preset condition to generate a new query instruction, a new query instruction equivalent to the original query instruction can be generated to avoid obtaining an erroneous new query instruction that is inconsistent with the execution result of the original query instruction. Furthermore, when it is determined that the main query of the original query instruction has an operation for implementing deduplication, and the original query instruction meets the preset conditions, a subquery for implementing the operation for implementing deduplication is added to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, the subquery for implementing the operation for implementing deduplication is removed, to obtain at least one new query instruction equivalent to the original query instruction. In this way, at least one new query instruction equivalent to the original query instruction can be obtained, and the at least one new query instruction may contain a query instruction with a lower execution cost than the original query instruction. Thereafter, the execution cost corresponding to each of the original query instruction and the at least one new query instruction is determined by the optimizer, and based on each execution cost, the query instruction corresponding to the minimum execution cost is determined as the target query instruction, and the target query instruction is used to execute and obtain the execution result. Based on this, the target query instruction with the minimum execution cost can be obtained, and when the execution is performed based on the target query instruction and the execution result is obtained, the execution cost can be reduced, thereby achieving the purpose of reducing the computing resource overhead.

[0120] For example, in actual applications, group by is rather special. In the SQL syntax writing, there is an implicit rule for group by. The implicit rule is: if group by exists in the SQL instruction, its target column information must be a group by element or an aggregate function.

[0121] Since the implicit rule can be set in the database management system to follow the implicit rule or not follow the implicit rule, therefore, if the original query instruction obtained is not checked for compliance with the implicit rule of group by before the original query instruction is input into the optimizer, then for the original query instruction that includes group by and does not follow the implicit rule, if a subquery that implements a deduplication operation is added or deleted, it may not be possible to obtain a new query instruction equivalent to the original query instruction, resulting in an error in the new query instruction or the inability to generate a new query instruction.

[0122] In a possible implementation, the electronic device also includes a grammar. After obtaining the original query instruction, the method also includes: when the main query in the original query instruction includes an operation of deduplication through an operator group by, detecting the target column information of the operator group by through the grammar to determine whether the target column information complies with the grammatical rules of group by; when it is determined that the target column information complies with the grammatical rules of group by, inputting the original query instruction into the optimizer.

[0123] Exemplarily, a grammarizer can be understood as a component or module in a database system or database management system for performing grammar detection on an input query instruction, and a grammarizer can be a software module composed of a computer program. The grammarizer can also identify operators in the input query instruction. The grammar rule of group by can be an implicit rule of group by, including that if group by exists in an SQL instruction, its target column information must be a group by element or an aggregation function.

[0124] For example, if the grammarizer determines that the original query instruction includes the operator group by through operator recognition, it can perform syntax detection on the target column information of the original query instruction to determine whether the target column information of group by is a group by element or an aggregation function. The target column information of the operator group by can be the grouping object corresponding to group by, which can be understood as a data table or other data set grouped by group by. The group by element can be understood as the column data corresponding to the grouping.

[0125] If it is determined that the target column information conforms to the grammatical rules of group by, it indicates that the original query instruction conforms to the implicit rules of group by, and a subquery that implements the deduplication operation can be added or deleted to the original query instruction. If it is determined that the target column information conforms to the grammatical rules of group by, the original query instruction is input into the optimizer, and the optimizer can further determine whether the main query in the original query instruction has an operation that implements deduplication, and determine whether the original query instruction meets the preset conditions.

[0126] For example, the original query instruction is "Select t1.a,t2.b from t1,t2 where t2.a<10group byt1.a,t2.b;" In the original query instruction, the target column information of group by is t1.a and t2.b, that is, column a of data table t1 and column b of data table t2. Both are column data corresponding to the grouping and meet the group by element. Therefore, the original query instruction meets the grammatical rules of group by.

[0127] When the main query in the original query instruction implements a deduplication operation through the group by operator, there is no aggregation function in the inner query and the outer query, and the group by operation is a simple deduplication operation, the subqueries in the inner query that implement deduplication operations through at least one of the deduplication operator distinct, the deduplication operation in the union operator, or the group by operator can be eliminated.

[0128] Similar to the conditions for eliminating subqueries by group by, when the main query in the original query instruction implements deduplication through the group by operator, when group by complies with the implicit rule, when the inner query and the outer query have no aggregation function and the group by operation is a simple deduplication operation, a subquery that implements deduplication through at least one of the deduplication operator distinct, the deduplication operation in the union operator, or the group by operator can be added to the inner query item where the outer grouping key exists. Among them, the outer grouping key can be understood as the object of grouping. When adding a subquery to the original query instruction, the query table item of the added subquery can be determined based on at least one query table item of the outer query.

[0129] For example, for the original query instruction "Select t1.a, t2.b from t1, t2 where t2.a<10 group by t1.a, t2.b;", the query table items of the outer query include t1.a, t2.a and t2.b, so the query table items of the added subquery can be determined based on one, two or more of t1.a, t2.a and t2.b.

[0130] For example, the query table item t1.a is determined as the query table item of the subquery, and a subquery for implementing the deduplication operation is added to the original query instruction. Then, a new query instruction can be obtained as "Select t1.a,t2.b from (select a from t1 group by a) as t1,t2 where t2.a<10 group by t1.a,t2.b;", where the added subquery is "(select a from t1 group by a)".

[0131] For example, query table items t2.a and t2.b are determined as query table items of a subquery, and a subquery for implementing a deduplication operation is added to the original query instruction. Then, a new query instruction can be obtained as "Select distinct t1.a,t2.bfrom t1,(select a,b from t2 group by a,b)as t2 where t2.a<10group by t1.a,t2.b;", where the added subquery is "(select a,b from t2 group by a,b)".

[0132] For example, based on the query table items t1.a, t2.a and t2.b of the main query, two subqueries for implementing deduplication operations can be added to the original query instruction, and a new query instruction can be obtained: "Select distinct t1.a,t2.b from(select a from t1 group by a))as t1,(select a,b from t2 group by a,b)as t2where t2.a<10group by t1.a,t2.b;", where the added subqueries are "(select a from t1 groupby a)" and "(select a,b from t2group by a,b)".

[0133] In this embodiment, the target column information of the operator group by is detected by a grammarizer to determine whether the target column information conforms to the grammatical rules of group by, so that it can be determined whether the original query instruction conforms to the grammatical rules of group by. Then, when it is determined that the target column information conforms to the grammatical rules of group by, the original query instruction is input into the optimizer to generate a new query instruction, thereby avoiding obtaining an erroneous new query instruction or a new query instruction that is not equivalent to the original query instruction due to the original query instruction not conforming to the grammatical rules of group by, thereby improving the accuracy of the new query instruction.

[0134] The following is an example of a specific implementation method to illustrate the query instruction processing method provided in the embodiment of the present application.

[0135] Figure 5 Schematic diagram of the query instruction processing method provided in the embodiment of the present application Figure 2 ,like Figure 5As shown, after obtaining the original query instruction, the original query instruction can be processed. The original query instruction can be first tested for preset conditions to determine whether the original query instruction only uses SPJ in SQL, group by has no aggregate function, and the expression of the order statement used for sorting only has simple table element calculations. If not, it means that the original query instruction does not meet the preset conditions, and the original query instruction can be processed according to other query instruction processing methods. If so, it can be further determined whether the outer query of the original query instruction includes group by, or whether it includes deduplication operations in distinct or union.

[0136] When it is determined that the outer query of the original query instruction includes group by, the grammar can be used to determine whether the original query instruction complies with the implicit rule of group by. If it is determined to be no, the original query instruction can be processed according to other query instruction processing methods; if it is determined to be yes, it is further determined whether the inner query of the original query instruction includes a subquery that implements a deduplication operation through at least one of the deduplication operator distinct, the deduplication operation in the union operator union, or the grouping operator group by. If so, a new query instruction can be obtained by eliminating the subquery that implements the deduplication operation; if not, a new query instruction can be obtained by adding a subquery that implements the deduplication operation to the original query instruction.

[0137] In the case where it is determined that the outer query of the original query instruction includes a deduplication operation in distinct or union, it can be further determined whether the inner query of the original query instruction includes a subquery that implements a deduplication operation through at least one of the deduplication operator distinct, the deduplication operation in the union operator union, or the grouping operator group by. If so, a new query instruction can be obtained by eliminating the subquery that implements the deduplication operation; if not, a new query instruction can be obtained by adding a subquery that implements the deduplication operation to the original query instruction. In the case where it is determined that the original query instruction does not include a deduplication operation in distinct or union, the original query instruction can be processed according to other query instruction processing methods.

[0138] In an embodiment of the present application, a method is proposed to obtain a new query instruction based on the execution cost by adding or deleting a subquery that implements a deduplication operation to the original query instruction to obtain an execution cost that may be less than the execution cost of the original query instruction. This method aims to solve the problem of blindly eliminating deduplication operations without considering the execution cost in the related art, which leads to an increase in execution cost. By adding or eliminating subqueries when the original query instruction meets the preset conditions and there is a deduplication operation in the outer query, the logical conversion principle of the executor can be effectively expanded, so that the optimizer has a greater possibility of obtaining a better new query instruction. After determining the target query instruction, the execution cost paid during execution can be reduced based on the target query instruction, reducing the computational overhead of the electronic device computing resources and saving computing resources.

[0139] Figure 6 A schematic diagram of the structure of the query instruction processing device provided in the embodiment of the present application is shown in FIG. Figure 6 As shown, an embodiment of the present application provides a query instruction processing device, which is applied to an electronic device, and the electronic device includes an optimizer, and the device includes:

[0140] The acquisition module 601 is used to acquire an original query instruction, where the original query instruction is an initial query instruction compiled using a structured query language;

[0141] The optimization module 602 is used to determine, through the optimizer, whether the main query in the original query instruction has an operation for implementing deduplication, and whether the original query instruction meets a preset condition, wherein the preset condition includes that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculation;

[0142] The optimization module 602 is further configured to, when it is determined that the main query of the original query instruction has an operation for implementing deduplication and the original query instruction meets a preset condition, add a subquery for implementing the operation for implementing deduplication to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, remove the subquery for implementing the operation for implementing deduplication, to obtain at least one new query instruction equivalent to the original query instruction;

[0143] The optimization module 602 is also used to determine the execution costs corresponding to the original query instruction and at least one new query instruction through the optimizer, and determine the query instruction corresponding to the minimum execution cost as the target query instruction based on the execution costs. The target query instruction is used to execute and obtain an execution result.

[0144] In a possible implementation, the deduplication operation includes implementing the deduplication operation through at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by.

[0145] In a possible implementation, the optimization module 602 is specifically configured to:

[0146] The optimizer detects the deduplication operation of the main query in the original query instruction, and determines the query table item corresponding to the deduplication operation of the main query;

[0147] Add a subquery to the original query instruction to implement the deduplication operation according to the query table items.

[0148] In a possible implementation manner, the query table items are at least two query table items, and the optimization module 602 is specifically configured to:

[0149] Determine any one of the at least two query table items as a query table item of a sub-query, and add a sub-query for implementing a deduplication operation to the original query instruction according to the query table item of the sub-query;

[0150] Alternatively, two or more query table items among the at least two query table items are determined as query table items of a sub-query, and a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table items of the sub-query.

[0151] In a possible implementation, the electronic device further includes a grammar, and the device further includes a grammar module, wherein the grammar module is used to detect target column information of the group by operator through the grammar to determine whether the target column information complies with the grammar rule of the group by when the main query in the original query instruction includes an operation of implementing deduplication through the group by operator;

[0152] After confirming that the target column information complies with the grammatical rules of group by, the original query instruction is input into the optimizer.

[0153] In a possible implementation, the optimization module 602 is specifically configured to:

[0154] For any query instruction among the original query instruction and the at least one new query instruction, the optimizer calculates the amount of data corresponding to executing any query instruction;

[0155] The execution cost corresponding to executing any query instruction is calculated based on the amount of data.

[0156] The query instruction processing device provided in the embodiment of the present application can be used to execute the technical solution of the query instruction processing method in any of the above embodiments of the present application. Its implementation principle and technical effect are similar, and this embodiment will not be repeated here.

[0157] Figure 7 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application is shown in FIG. Figure 7 As shown, the electronic device of this embodiment may include: at least one processor 701; and a memory 702 communicatively connected to the at least one processor; wherein the memory 702 stores instructions executable by the at least one processor 701, and the instructions are executed by the at least one processor 701 so that the electronic device executes a method as in any of the above embodiments.

[0158] Optionally, the memory 702 may be independent or integrated with the processor 701 .

[0159] The implementation principle and technical effects of the electronic device provided in this embodiment can be found in the aforementioned embodiments and will not be described in detail here.

[0160] An embodiment of the present application further provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer-executable instructions. When a processor executes the computer-executable instructions, the method described in any of the above embodiments is implemented.

[0161] An embodiment of the present application further provides a computer program product, including a computer program, which implements the method described in any of the above embodiments when executed by a processor.

[0162] In the several embodiments provided in this application, it should be understood that the disclosed devices and methods can be implemented in other ways. For example, the device embodiments described above are only schematic, for example, the division of the modules is only a logical function division, and there may be other division methods in actual implementation, such as multiple modules can be combined or integrated into another system, or some features can be ignored or not executed.

[0163] The above-mentioned integrated module implemented in the form of a software function module can be stored in a computer-readable storage medium. The above-mentioned software function module is stored in a storage medium, including a number of instructions for causing a computer device (which can be a personal computer, a server, or a network device, etc.) or a processor to perform some steps of the method described in each embodiment of the present application.

[0164] It should be understood that the above-mentioned processor can be a processing unit (Central Processing Unit, CPU), or other general-purpose processors, digital signal processors (Digital Signal Processor, DSP), application-specific integrated circuits (Application Specific Integrated Circuit, ASIC), etc. The general-purpose processor can be a microprocessor or the processor can also be any conventional processor, etc. The steps of the method disclosed in the application can be directly embodied as being executed by a hardware processor, or can be executed by a combination of hardware and software modules in the processor. The memory may include random access memory (Random Access Memory, RAM), and may also include non-volatile memory (Non-Volatile Memory, NVM), such as at least one disk storage, and can also be a USB flash drive, a mobile hard disk, a read-only memory, a disk or an optical disk, etc.

[0165] The above storage medium can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read only memory (EEPROM), erasable programmable read only memory (EPROM), programmable read only memory (PROM), read only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk. The storage medium can be any available medium that can be accessed by a general or special-purpose computer.

[0166] An exemplary storage medium is coupled to a processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in a dedicated integrated circuit. Of course, the processor and the storage medium can also exist as discrete components in an electronic device or a main control device.

[0167] It should be noted that, in this article, the terms "include", "comprises" or any other variations thereof are intended to cover non-exclusive inclusion, so that a process, method, article 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, article or device. In the absence of further restrictions, an element defined by the sentence "comprises a ..." does not exclude the existence of other identical elements in the process, method, article or device including the element.

[0168] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0169] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, magnetic disk, optical disk), and includes a number of instructions for a terminal device (which can be a mobile phone, computer, server, air conditioner, or network device, etc.) to execute the methods described in each embodiment of the present application.

[0170] The above are only preferred embodiments of the present application, and are not intended to limit the patent scope of the present application. Any equivalent structure or equivalent process transformation made using the contents of the present application specification and drawings, or directly or indirectly applied in other related technical fields, are also included in the patent protection scope of the present application.

[0171] Those skilled in the art will readily come up with other implementations of the embodiments of the present application after considering the specification and practicing the application disclosed herein. The present application is intended to cover any modifications, uses or adaptations of the embodiments of the present application, which follow the general principles of the embodiments of the present application and include common knowledge or customary technical means in the art that are not disclosed in the embodiments of the present application.

[0172] It should be understood that the embodiments of the present application are not limited to the precise structures described above and shown in the drawings, and various modifications and changes can be made without departing from the scope thereof. The scope of the embodiments of the present application is limited only by the appended claims.

Claims

1. A query instruction processing method, characterized in that: Applied to an electronic device, the electronic device includes an optimizer, and the method includes: Obtaining an original query instruction, wherein the original query instruction is an initial query instruction compiled using a structured query language; Determine, by the optimizer, whether the main query in the original query instruction has an operation for implementing deduplication, and determine whether the original query instruction meets preset conditions, wherein the preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculation; When it is determined that the main query of the original query instruction has an operation for implementing deduplication and the original query instruction meets a preset condition, a subquery for implementing the operation for implementing deduplication is added to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, the subquery for implementing the operation for implementing deduplication is removed to obtain at least one new query instruction equivalent to the original query instruction; The optimizer determines the execution costs corresponding to the original query instruction and the at least one new query instruction respectively, and based on the execution costs, determines the query instruction corresponding to the minimum execution cost as the target query instruction, and the target query instruction is used to execute and obtain an execution result.

2. The method according to claim 1, characterized in that The deduplication operation includes implementing the deduplication operation through at least one of a deduplication operator distinct, a deduplication operation in a union operator union, or a grouping operator group by.

3. The method according to claim 1, characterized in that The adding a subquery for implementing a deduplication operation to the original query instruction includes: Detecting, by the optimizer, the deduplication-implementing operation of the main query in the original query instruction, and determining the query table item corresponding to the deduplication-implementing operation of the main query; A subquery for implementing a deduplication operation is added to the original query instruction according to the query table item.

4. The method according to claim 3, characterized in that The query table items are at least two query table items, and the adding a subquery for implementing a deduplication operation to the original query instruction according to the query table items includes: Determine any one of the at least two query table items as a query table item of a sub-query, and add a sub-query for implementing a deduplication operation to the original query instruction according to the query table item of the sub-query; Alternatively, two or more query table items among the at least two query table items are determined as query table items of a sub-query, and a sub-query for implementing a deduplication operation is added to the original query instruction according to the query table items of the sub-query.

5. The method according to any one of claims 1 to 4, characterized in that: The electronic device further includes a grammarizer. After obtaining the original query instruction, the method further includes: In the case where the main query in the original query instruction includes an operation of implementing deduplication through an operator group by, detecting target column information of the operator group by through the grammarizer to determine whether the target column information complies with the grammar rules of group by; When it is determined that the target column information complies with the grammatical rule of the group by, the original query instruction is input into the optimizer.

6. The method according to any one of claims 1 to 4, characterized in that: The determining, by the optimizer, the execution costs corresponding to the original query instruction and the at least one new query instruction respectively includes: For any query instruction among the original query instruction and the at least one new query instruction, the optimizer calculates the amount of data corresponding to executing the any query instruction; An execution cost corresponding to executing any query instruction is calculated according to the data volume.

7. A query instruction processing device, characterized in that: Applied to an electronic device, the electronic device includes an optimizer, and the device includes: An acquisition module, used for acquiring an original query instruction, wherein the original query instruction is an initial query instruction compiled using a structured query language; an optimization module, configured to determine, through the optimizer, whether the main query in the original query instruction has an operation for implementing deduplication, and determine whether the original query instruction meets preset conditions, wherein the preset conditions include that the original query instruction only includes at least one relational algebra operation of selection, projection or connection, and if the original query instruction includes a grouping operation, the grouping operation is a grouping operation of a non-aggregate function, and if the original query instruction includes a sorting operation, the expression of the sorting operation only includes table element calculation; The optimization module is further configured to, when it is determined that the main query of the original query instruction has an operation for implementing deduplication and the original query instruction meets a preset condition, add a subquery for implementing the operation for implementing deduplication to the original query instruction, or, when the original query instruction includes a subquery for implementing the operation for implementing deduplication, remove the subquery for implementing the operation for implementing deduplication, to obtain at least one new query instruction equivalent to the original query instruction; The optimization module is also used to determine the execution costs corresponding to the original query instruction and the at least one new query instruction through the optimizer, and based on each of the execution costs, determine the query instruction corresponding to the minimum execution cost as the target query instruction, and the target query instruction is used to execute and obtain an execution result.

8. An electronic device, characterized in that: include: Memory and processor; The memory stores computer-executable instructions; The processor executes the computer-executable instructions stored in the memory, so that the processor performs the method according to any one of claims 1 to 6.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer-executable instructions, which are used to implement the method according to any one of claims 1 to 6 when executed by a processor.

10. A computer program product, characterized in that The method comprises a computer program, which implements the method according to any one of claims 1 to 6 when being executed by a processor.

Images