Database query optimization method and apparatus, and computing device cluster

Abstract

A database query optimization method, comprising: acquiring a query statement, the query statement comprising a connection operation and a group aggregation operation; determining that a connection key and a group key in the query statement are the same, wherein an aggregation function in the query statement can be split, and an input parameter of the aggregation function comes from the same side as a connection condition in the query statement; creating a fusion operator, the fusion operator being used for completing the connection operation and the group aggregation operation; when a cost for executing the fusion operator is less than or equal to a target cost, on the basis of the fusion operator, generating an optimal execution plan related to the fusion operator, wherein the target cost is a cost for executing a connection operator and a group aggregation operator; and performing the operations step by step on the basis of an operation sequence in the optimal execution plan, so as to obtain a query result set. Therefore, when the query statement meets corresponding features, the connection operation and the group aggregation operation can be fused into one operator, thereby reducing the processing flow of an intermediate result, accelerating the execution of the query statement, and improving the query execution performance.

Description

Database query optimization method, device and computing device cluster

[0001] This application claims priority to the Chinese patent application filed with the State Intellectual Property Office of China on November 10, 2023, with application number 202311503166.0 and application name “A database query optimization method, device and computing device cluster”, the entire contents of which are incorporated by reference into this application. Technical Field

[0002] The present application relates to the field of information technology (IT), and in particular to a database query optimization method, apparatus, and computing device cluster. Background Art

[0003] In a database management system (DBMS), for a given structured query language (SQL) query statement, the DBMS's query optimizer can rewrite the SQL statement (also known as query transformation) according to predefined rules, generating equivalent queries to the original SQL statement. The query optimizer then evaluates the cost of these equivalent queries and generates an optimal execution plan based on the query with the lowest cost. However, this optimal execution plan often includes redundant computations, which wastes performance and reduces execution efficiency.

[0004] Summary of the Invention

[0005] The present application provides a database query optimization method, apparatus, computing device cluster, computer storage medium and computer product, which can improve query execution performance.

[0006] In a first aspect, the present application provides a database query optimization method, comprising: obtaining a query statement, the query statement including: a join operation and a group aggregation operation; determining that the join key and the group key in the query statement are the same, the aggregation function in the query statement can be split for calculation, and the input parameters of the aggregation function come from the same side of the join condition in the query statement; creating a fusion operator, the fusion operator being used to complete the join operation and the group aggregation operation; when the cost of executing the fusion operator is less than or equal to the target cost, generating an optimal execution plan related to the fusion operator based on the fusion operator, the target cost being the cost of executing the join operator and the group aggregation operator, the join operator being used to complete the join operation, and the group aggregation operator being used to complete the group aggregation operation; executing operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set.

[0007] In this way, when the query statement meets the corresponding characteristics, the join and group aggregation operations can be fused into one operator, thereby reducing the processing flow of intermediate results and accelerating the execution of the query statement. In addition, compared with a single operator, the fused operator can use less memory and CPU resources, saving memory overhead and CPU resources.

[0008] In one possible implementation, operations are executed step by step according to the operation sequence in the optimal execution plan to obtain a query result set, including: using the table on the first side of the join operation as the build-side table, constructing an ordered memory index, the ordered memory index including: a search key value, a key value count, a match tag, and an aggregate function value, the search key value being the join key value in the build-side table, the key value count being the number of times the search key value appears in the build-side table, the match tag being used to identify whether the search key value successfully matches the join key value in the detection-side table, the detection-side table being the second side of the join operation, the aggregate function value being the value obtained by calculating the aggregate function in the query statement, the aggregate function value including the aggregate function value related to the build-side table and / or the aggregate function value related to the detection-side table; using the join key value in the detection-side table to probe the ordered memory index, and, based on the probe result, updating the aggregate function value related to the detection-side table and the match tag in the ordered memory index; and filtering the query result set from the ordered memory index based on the join type in the query statement. In this way, the ordered memory index can simultaneously implement join matching and group aggregation operations, thereby improving execution performance. Furthermore, during the execution of the operation sequence, only the build-side table can be sorted, reducing the number of sorts. Furthermore, these operation sequences all use the same physical data structure (i.e., ordered memory indexes), resulting in better temporal and spatial locality during the execution of the fusion operator, leading to higher execution efficiency.

[0009] In one possible implementation, an ordered memory index is constructed using the table on the first side of a join operation as a build-side table, including: constructing a data record for each join key value in the build-side table, the data record including: a search key value, an initial value of an aggregate function value, the number of key values, and an initial value of a matching tag; inserting the constructed data record into the ordered memory index to construct an ordered memory index; wherein, when the first data record and the second data record inserted into the ordered memory index are the same, retaining the first data record or the second data record, accumulating the number of key values ​​in the first data record and the second data record, and, when the input parameter of the aggregate function in the query statement comes from the build-side table, updating the aggregate function value associated with the first data record or the second data record. In this way, an ordered memory index can be constructed.

[0010] In one possible implementation, inserting the constructed data records into the ordered memory index includes: sorting the data records according to the search key values ​​in the data records, and sequentially inserting the data records into the ordered memory index based on the sorting results; or inserting the data records into the ordered memory index and sorting the data records in the ordered memory index according to the search key values ​​in the data records. In this way, the data in the ordered memory index can be sorted.

[0011] In one possible implementation, the ordered memory index is detected using the connection key value in the detection side table, including: using the connection key value in the first record in the detection side table to match the search key value in the ordered memory index, the first record being any record in the detection side table; based on the detection result, updating the aggregate function value related to the detection side table in the ordered memory index, and updating the matching identifier, including: when the connection key value in the first record matches the first search key value in the ordered memory index, updating the matching identifier related to the first search key value in the ordered memory index, and, based on the number of key values ​​related to the first search key value, updating the aggregate function value related to the first search key value and the connection key value in the first record; when the connection key value in the first record does not match the first search key value in the ordered memory index, using the connection key value in any remaining record in the detection side table to match the search key value in the ordered memory index. In this way, the connection matching and group aggregation operations are simultaneously implemented through an ordered memory index, thereby improving the execution performance.

[0012] In one possible implementation, before using any join key value in the detection side table to detect the ordered memory index, the method further includes: generating a target range condition based on the range of the search key value in the ordered memory index and the join condition in the query statement, the target range condition being used to indicate the range of the join key value in the detection side table; and determining whether any join key value satisfies the target range condition, wherein if any join key value does not satisfy the target range condition, no longer using any join key value to detect the ordered memory index. In this way, data can be filtered before detecting the ordered memory index, avoiding unnecessary detection and improving execution performance.

[0013] In a possible implementation, both the construction side table and the detection side table may be base tables or derived tables (such as views or intermediate results in a query process).

[0014] In a possible implementation, the query statement further includes a sorting operation, and the fusion operator is also used to complete the sorting operation.

[0015] In a possible implementation, the join condition is used for an equi-join, or for an unequal-join, or for both an equi-join and an unequal-join.

[0016] In the second aspect, the present application provides a database query optimization device, including: an acquisition module and a processing module. The acquisition module is used to obtain a query statement, which includes: a join operation and a group aggregation operation. The processing module is used to determine that the join key and the group key in the query statement are the same, the aggregation function in the query statement can be split for calculation, and the input parameters of the aggregation function come from the same side of the join condition in the query statement. The processing module is also used to create a fusion operator, which is used to complete the join operation and the group aggregation operation; and, when the cost of executing the fusion operator is less than or equal to the target cost, based on the fusion operator, an optimal execution plan related to the fusion operator is generated, the target cost is the cost of executing the join operator and the group aggregation operator, the join operator is used to complete the join operation, and the group aggregation operator is used to complete the group aggregation operation. In addition, the processing module is also used to step by step execute operations according to the operation sequence in the optimal execution plan to obtain a query result set.

[0017] In one possible implementation, when the processing module executes operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set, it is specifically used to: use the table on the first side of the connection operation as the build side table to build an ordered memory index, and the ordered memory index includes: a search key value, a key value count, a matching tag, and an aggregate function value. The search key value is the connection key value in the construction side table, the key value count is the number of times the search key value appears in the construction side table, and the matching tag is used to identify whether the search key value successfully matches the connection key value in the detection side table. The detection side table is the table on the second side of the connection operation, and the aggregate function value is the value obtained by calculating the aggregate function in the query statement. The aggregate function value includes the aggregate function value related to the construction side table and / or the aggregate function value related to the detection side table; use the connection key value in the detection side table to detect the ordered memory index, and, based on the detection result, update the aggregate function value related to the detection side table and the matching tag in the ordered memory index; based on the connection type in the query statement, filter out the query result set from the ordered memory index.

[0018] In one possible implementation, when the processing module constructs an ordered memory index with the table on the first side in the join operation as the build side table, it is specifically used to: construct a data record for each connection key value in the build side table, and the data record includes: the search key value, the initial value of the aggregate function value, the number of key values, and the initial value of the matching tag; insert the constructed data record into the ordered memory index to construct an ordered memory index; wherein, when the first data record and the second data record inserted into the ordered memory index are the same, retain the first data record or the second data record, and accumulate the number of key values ​​in the first data record and the second data record; and, when the input parameter of the aggregate function in the query statement comes from the build side table, update the aggregate function value related to the first data record or the second data record.

[0019] In one possible implementation, when the processing module inserts the constructed data records into the ordered memory index, it is specifically used to: sort the data records according to the search key value in the data records, and insert the data records into the ordered memory index in sequence based on the sorting results; or, insert the data records into the ordered memory index, and sort the data records in the ordered memory index according to the search key value in the data records.

[0020] In a possible implementation, when the processing module uses the connection key value in the detection side table to detect the ordered memory index, it is specifically used to: use the connection key value in the first record in the detection side table to match the search key value in the ordered memory index, and the first record is any record in the detection side table. At this time, based on the detection result, the aggregate function value related to the detection side table is updated in the ordered memory index, and the matching identifier is updated, including: when the connection key value in the first record matches the first search key value in the ordered memory index, the matching identifier related to the first search key value is updated in the ordered memory index, and, based on the number of key values ​​related to the first search key value, the aggregate function value related to the first search key value and the connection key value in the first record is updated; when the connection key value in the first record does not match the first search key value in the ordered memory index, the connection key value in any remaining record in the detection side table is used to match the search key value in the ordered memory index.

[0021] In one possible implementation, before using any connection key value in the detection side table to detect the ordered memory index, the processing module is also used to: generate a target range condition based on the range of the search key value in the ordered memory index and the connection condition in the query statement, and the target range condition is used to indicate the range of the connection key value in the detection side table; determine whether any connection key value satisfies the target range condition, wherein, if any connection key value does not satisfy the target range condition, no longer use any connection key value to detect the ordered memory index.

[0022] In a possible implementation, both the construction side table and the detection side table may be base tables or derived tables (such as views or intermediate results in a query process).

[0023] In a possible implementation, the query statement further includes a sorting operation, and the fusion operator is also used to complete the sorting operation.

[0024] In a possible implementation, the join condition is used for an equi-join, or for an unequal-join, or for both an equi-join and an unequal-join.

[0025] In a third aspect, the present application provides a computing device cluster comprising at least one computing device, each computing device comprising a processor and a memory; the processor of at least one computing device is used to execute instructions stored in the memory of at least one computing device, so that the computing device cluster performs the method described in the first aspect or any possible implementation of the first aspect.

[0026] In a fourth aspect, the present application provides a computer-readable storage medium comprising computer program instructions. When the computer program instructions are executed by a computing device cluster, the computing device cluster performs the method described in the first aspect or any possible implementation of the first aspect, or performs the method described in the second aspect or any possible implementation of the second aspect. The computing device cluster may include one or more computing devices.

[0027] In a fifth aspect, the present application provides a computer program product comprising instructions, which, when executed by a computing device cluster, causes the computing device cluster to perform the method described in the first aspect or any possible implementation of the first aspect. The computing device cluster may include one or more computing devices.

[0028] It can be understood that the beneficial effects of the second to fifth aspects mentioned above can be found in the relevant description of the first aspect mentioned above, and will not be repeated here. BRIEF DESCRIPTION OF THE DRAWINGS

[0029] FIG1 is a schematic diagram of the logical architecture of a database management system provided in an embodiment of the present application;

[0030] FIG2 is a flow chart of a database query optimization method provided in an embodiment of the present application;

[0031] FIG3 is a schematic diagram of steps for constructing an ordered memory index according to an embodiment of the present application;

[0032] FIG4 is a schematic diagram of a query execution process of an SQL query statement provided in an embodiment of the present application;

[0033] FIG5 is a schematic diagram of a query execution process of another SQL query statement provided in an embodiment of the present application;

[0034] FIG6 is a schematic diagram of a process for constructing an ordered memory index and generating a range condition provided by an embodiment of the present application;

[0035] FIG7 is a schematic diagram of the structure of a database query optimization device provided in an embodiment of the present application;

[0036] FIG8 is a schematic diagram of the structure of a computing device provided in an embodiment of the present application;

[0037] FIG9 is a schematic diagram of the structure of a computing device cluster provided in an embodiment of the present application;

[0038] FIG10 is a schematic diagram of the structure of another computing device cluster provided in an embodiment of the present application. DETAILED DESCRIPTION

[0039] The term "and / or" as used herein describes an association between related objects, indicating that three possible relationships exist. For example, "A and / or B" can represent: A exists alone, A and B exist simultaneously, or B exists alone. The symbol " / " as used herein indicates that the related objects are in an "or" relationship, for example, A / B means either A or B.

[0040] The terms "first" and "second" in this specification and claims are used to distinguish different objects rather than to describe a specific order of objects. For example, "first response message" and "second response message" are used to distinguish different response messages rather than to describe a specific order of response messages.

[0041] In the embodiments of this application, words such as "exemplary" or "for example" are used to indicate examples, illustrations, or descriptions. Any embodiment or design described as "exemplary" or "for example" in the embodiments of this application should not be interpreted as being preferred or advantageous over other embodiments or designs. Rather, the use of words such as "exemplary" or "for example" is intended to present the relevant concepts in a concrete manner.

[0042] In the description of the embodiments of the present application, unless otherwise specified, "multiple" means two or more, for example, multiple processing units means two or more processing units, etc.; multiple elements means two or more elements, etc.

[0043] First, some technical terms involved in this application are introduced.

[0044] (1) Query

[0045] A query is the process of retrieving data from a database. This can involve retrieving data from a single table or joining data from multiple tables. Queries can be written using SQL statements. Through queries, users can easily retrieve the data they need for data analysis, report generation, and decision support.

[0046] (2) Operator

[0047] Operators, also known as operators, are used to process data. Operators are categorized as logical operators and physical operators. Logical operators describe the semantics of an operation but do not cover its specific implementation. Physical operators describe its execution. For example, a join is a logical operator, while the corresponding hash join, nested loop join, and sort-merge join are physical operators.

[0048] (3) Query Optimizer

[0049] The query optimizer is a key component of a database management system, responsible for parsing, analyzing, optimizing, and generating execution plans for SQL statements. Its goal is to find the optimal execution plan to meet user query requirements with minimal time and resource costs. The query optimizer is primarily responsible for converting logical operators into physical operators to generate an efficient execution plan. The execution plan displays the physical operators.

[0050] (4) Execution plan

[0051] An execution plan describes the specific steps and execution order for a database management system to execute a query statement. The basic unit of operation in an execution plan is called a (physical) operator, representing a specific operation, such as a table scan or hash join. Operators in an execution plan form a tree structure in the order in which they are executed. The root node of the tree is the outermost operator, and the leaf nodes are the innermost operators.

[0052] (5) Connection operation

[0053] A join operation generates a new table by matching data from two or more tables according to the join conditions. Joins allow you to retrieve related information from multiple tables. Joins are typically implemented using SQL statements, including keywords such as INNER JOIN, LEFT JOIN, and RIGHT JOIN.

[0054] (6) Connection conditions

[0055] A join condition specifies the conditions for connecting two tables, typically based on comparing certain columns in the two tables to join related rows. For example, the query statement SELECT * FROM t1 INNER JOIN t2 ON t1.c1 = t2.c2 has a join condition of t1.c1 = t2.c2. When the value of column c1 in table t1 equals the value of column c2 in table t2, the related rows of t1 and t2 are joined. Join conditions are typically categorized as equijoins and non-equijoins. Equijoins use only the equal sign, while non-equijoins typically use comparison operators such as greater than, greater than or equal to, less than, less than or equal to, and not equal to.

[0056] (7) Connection key

[0057] A join key is a column or combination of columns used to connect two tables. It associates rows with identical or related values ​​in the two tables to generate the joined result set. In SQL queries, the join key is typically specified using the JOIN clause. For example, in the join condition t1.c1 = t2.c2, the join key for table t1 is c1, and the join key for table t2 is c2; in the join condition t1.c1 > t2.c2 AND t1.c3 > t2.c4, the join key for table t1 is (c1, c3), and the join key for table t2 is (c2, c4).

[0058] (8) Grouping key

[0059] A grouping key is a column or combination of columns used to group data. In SQL queries, you typically use the GROUP BY clause to specify the grouping key. The grouping key determines how data is divided into distinct groups, each with the same grouping key value. Group aggregation operations group data based on the grouping key value and calculate aggregate values ​​(such as sums, averages, counts, etc.) for each group.

[0060] (9) Group aggregation

[0061] Group aggregation categorizes data according to specified grouping criteria and performs aggregate calculations on the data within each group to produce a summary result for each group. Aggregate calculations can include sums, averages, maximums, and minimums. Group aggregation can be applied in fields such as data analysis and report generation, helping users quickly understand the overall data situation and the distribution of each dimension, enabling more accurate decision-making. Group aggregation is typically implemented in SQL statements using GROUP BY and aggregate functions.

[0062] (10) Ordered memory index

[0063] An ordered memory index stores all indexed data in memory and is sorted by the search key. Ordered memory indexes can handle both point and range queries. Point queries use equality conditions, while range queries use inequality conditions.

[0064] (11) Search key

[0065] A search key is one or more columns used to search for and locate data in an ordered in-memory index.

[0066] Next, the technical solution provided by this application is introduced.

[0067] Generally, when a query involves a join and a group-by-group aggregation, the DBMS typically implements each operation using a single physical operator. For example, a hash join might be used to handle the join, while a hash group-by-group aggregation might be used to handle the group-by-group aggregation. However, in some queries involving both joins and group-by-group aggregations, when the join and group-by-group aggregation share certain characteristics (e.g., the join key and group-by-group key are identical), implementing each operation using a single operator can be redundant and wasteful.

[0068] In view of this, an embodiment of the present application provides a database query optimization method, which can fully utilize the characteristics of SQL query statements containing connection and group aggregation operations, avoid redundant calculations, reduce memory overhead, and improve the execution efficiency of query statements.

[0069] For example, Figure 1 illustrates a logical architecture diagram of a database management system provided by an embodiment of the present application. As shown in Figure 1 , database management system 100 may include a client 110, an SQL engine 120, and a storage engine 130. Client 110 refers to various forms of database connection, such as ActiveX data objects (ADO) connections used in .Net and Java database connections (JDBC) used in Java.

[0070] The SQL engine 120 is primarily responsible for generating an efficient execution plan for SQL statements entered by the client 110 under the current load scenario and executing that execution plan. The SQL engine 120 may include a connector 121, a query cache 122, a parser 123, an optimizer 124, and an executor 125. The connector 121 is primarily responsible for communicating with the client 110 and handling business logic such as connection authentication, connection count determination, and connection pool management. The query cache 122 primarily serves to improve query efficiency. The cache stores data in the form of a key and value hash table, where the key is the specific SQL statement and the value is the result set. When a SQL statement arrives, if the query cache function is enabled, the SQL engine 120 will first check the query cache 122 for matching data. If a match is found, the matching data is directly returned to the client 110 without further parsing the corresponding SQL statement. However, if the SQL statement contains user-defined functions, stored functions, user variables, or temporary tables, the query cache 122 will not be used. If no match is found in the query cache 122, the parser 123 is used to parse the corresponding SQL statement. The parser 123 is primarily responsible for parsing the SQL statement according to grammatical rules and generating an internally recognizable parse tree. The optimizer 124 is responsible for optimizing the parse tree generated by the parser 123 to find an optimal execution plan. After the optimizer 124 finds the optimal execution plan, the executor 125 is primarily responsible for calling the storage engine 130 interface to execute the query or other operations based on the optimal execution plan, ultimately returning the query result set to the client 110.

[0071] The storage engine 130 is primarily responsible for data storage, retrieval, and management. It defines important characteristics of the database system, such as how to organize data, how to execute queries and transactions, and data security and reliability.

[0072] Next, the database query optimization method provided in the embodiment of the present application is introduced in conjunction with the database management system 100 shown in FIG1 .

[0073] For example, FIG2 shows a flow chart of a database query optimization method provided by an embodiment of the present application. It is understood that the method can be executed by any device, equipment, platform, or device cluster with computing and processing capabilities. As shown in FIG2 , the database query optimization method may include the following steps:

[0074] S201: Obtain an SQL query statement, which includes a join operation and a grouping and aggregation operation.

[0075] In this embodiment, the SQL query statement may be input by the user or predefined by the application or system, and the specific method may be determined according to actual conditions and is not limited here.

[0076] S202: Determine whether the join key and the group key in the SQL query statement are the same.

[0077] In this embodiment, after obtaining the SQL query statement, it can be determined whether the join key and the grouping key in the query statement are the same. If they are the same, S203 can be executed; otherwise, S207 is executed. For example, assuming that the join condition is t1.x=t2.a, if the grouping key is t1.x or t2.a (i.e. GROUP BY t1.x or GROUP BY t2.a), the condition is met; if the grouping key is t1.y or t2.b, the condition is not met. In addition, assuming that the join condition is t1.x=t2.a AND t1.y>t2.b, if the grouping key is (t1.x, t1.y) or (t2.a, t2.b), the condition is met. Exemplarily, the join condition can be used for equijoin, unequaljoin, or both equijoin and unequaljoin.

[0078] S203: Determine whether the aggregate function in the SQL query statement can be split for calculation.

[0079] In this embodiment, after obtaining the SQL query statement, it can be determined whether the aggregate function in the query statement can be split for calculation. When the calculation can be split, S204 can be executed; otherwise, S207 is executed. Among them, the aggregate function can be split for calculation, which means that the data to be calculated can be split into multiple parts, and the calculation can be performed on each part of the data separately, and then the calculation results can be summarized to obtain the final result. For example: when using the aggregate function SUM to sum 10 numbers, the 10 numbers can be split into two parts and summed separately, and then the summed results of the two parts can be added together to obtain the final result. Common splittable aggregate functions include: SUM, AVG, COUNT, MAX and MIN.

[0080] S204: Determine whether the input parameters of the aggregate function in the SQL query statement are from the same side of the join condition in the SQL query statement.

[0081] In this embodiment, after obtaining the SQL query statement, it can be determined whether the input parameters of the aggregate function in the query statement come from the same side of the join condition in the SQL query statement. When they come from the same side of the join condition, S205 can be executed; otherwise, S207 is executed. For example, assuming the join condition is t1.x=t2.a, if the input parameters of the aggregate function SUM(t2.c) come from the t2 side (t2 can be a base table or the result of a subquery), the condition is met; similarly, the aggregate function SUM(t1.z) also meets the condition; but the aggregate function SUM(t1.z+t2.c), its input parameters involve both the t1 side and the t2 side, and therefore does not meet the condition.

[0082] It should be understood that the execution order of S202, S203 and S204 can be selected according to actual conditions and is not limited here. For example, they can be executed simultaneously in parallel or sequentially, and so on.

[0083] When the SQL query statement satisfies the conditions in S202, S203 and S204 at the same time, S205 can be executed.

[0084] S205: Create a fusion operator, which is used to complete the connection and group aggregation operations.

[0085] In this embodiment, when the obtained SQL query statement satisfies the conditions in S202, S203, and S204 at the same time, a fusion operator can be created. This fusion operator can be used to complete the join and group aggregation operations. In this way, the join operation and the group aggregation operation can be completed by a single operator, without having to use one operator to complete the join operation and another operator to complete the group aggregation operation, thereby reducing the processing flow of intermediate results. In addition, compared with a single operator, the fusion operator uses less memory and central processing unit (CPU) resources, thereby saving memory overhead and CPU resources.

[0086] S206: Determine whether the cost of executing the fusion operator is greater than the cost of executing the grouping aggregation operator and the connection operator.

[0087] In this embodiment, after generating the fusion operator, it can be determined whether the cost of executing the fusion operator is greater than the cost of executing the group aggregation operator and the join operator. If the cost of executing the fusion operator is greater than the cost of executing the group aggregation operator and the join operator, it indicates that using the fusion operator for join and group aggregation operations will not bring performance improvement, so S207 can be executed. If the cost of executing the fusion operator is not greater than (i.e., less than or equal to) the cost of executing the group aggregation operator and the join operator, it indicates that using the fusion operator for join and group aggregation operations can bring performance improvement, so S209 can be executed.

[0088] S207 . Generate an optimal execution plan related to the grouping aggregation operator and the connection operator according to the grouping aggregation operator and the connection operator.

[0089] In this embodiment, when the cost of executing the fusion operator is greater than the cost of executing the grouping aggregation operator and the join operator, an optimal execution plan related to the grouping aggregation operator and the join operator can be generated based on the grouping aggregation operator and the join operator. The optimal execution plan describes the operation sequence when executing the obtained SQL query statement.

[0090] S208 . Execute operations step by step according to the operation sequence in the optimal execution plan related to the grouping aggregation operator and the join operator to obtain a query result set.

[0091] In this embodiment, after obtaining the optimal execution plan related to the group aggregation operator and the join operator, each operation can be executed step by step according to the operation sequence in the optimal execution plan to obtain a query result set.

[0092] S209: Generate an optimal execution plan related to the fusion operator based on the fusion operator.

[0093] In this embodiment, when the cost of executing the fusion operator is less than or equal to the cost of executing the group aggregation operator and the join operator, an optimal execution plan related to the fusion operator can be generated based on the fusion operator. The optimal execution plan describes the operation sequence when executing the obtained SQL query statement.

[0094] S210 , executing operations step by step according to the operation sequence in the optimal execution plan related to the fusion operator to obtain a query result set.

[0095] In this embodiment, after obtaining the optimal execution plan related to the fusion operator, each operation can be gradually executed according to the operation sequence in the optimal execution plan to obtain a query result set.

[0096] In this way, when the SQL query statement meets the corresponding characteristics, the join and group aggregation operations can be fused into one operator, thereby reducing the processing flow of intermediate results and accelerating the execution of SQL query statements; and compared with a single operator, the fused operator can use less memory and CPU resources, saving memory overhead and CPU resources.

[0097] In some embodiments, the sequence of operations in the optimal execution plan associated with the fusion operator described in S209 may include, in order: 1) constructing an ordered memory index, 2) detecting the ordered memory index and calculating an aggregate function and updating a matching tag, and 3) determining the final result of the aggregate function based on the ordered memory index to obtain a query result set. These three operation sequences are described below. The process of executing these three operation sequences can also be found in the following description, and will not be detailed here.

[0098] 1) Build an ordered memory index

[0099] When constructing an ordered memory index, the table on one side of the join operation in the SQL query statement can be used as the build side table, and the ordered memory index can be constructed through the build side table. Exemplarily, the ordered memory index can be a B+ tree (B+-tree) or other data structures such as a skip list. Among them, the ordered memory index may include: a search key value, an aggregate function value, a key value count, and a matching tag. The search key value is the join key value in the build side table. The key value count is the number of times the search key value appears in the build side table. The matching tag is used to identify whether the search key value successfully matches the join key value in the detection side table. The detection side table is the table on the other side of the join operation. The aggregate function value is the value obtained by calculating the aggregate function in the query statement. When the input parameter of the aggregate function comes from the build side table, the aggregate function value may include the aggregate function value related to the build side table. When the input parameter of the aggregate function comes from the detection side table, the aggregate function value may include the aggregate function value related to the detection side table.

[0100] As a possible implementation, as shown in Figure 3, constructing an ordered in-memory index may include the following steps: S301: Scanning search key values ​​from the build-side table. To ensure that all index data can be stored in memory, a side table that occupies less memory can be selected as the build-side table. S302: Constructing data records for the scanned search key values. A data record is associated with a search key value. The data record may include: the search key value, the initial value of the aggregate function value, the number of key value occurrences, and the initial value of the match flag. S303: Inserting the constructed data record as a leaf node into the ordered in-memory index to construct the ordered in-memory index. For data records with duplicate search key values, the leaf nodes of the ordered in-memory index only store one data record, but the number of key value occurrences in these data records must be accumulated. In addition, if the input parameters of the aggregate function in the query statement come from the build-side table, the aggregate function must be calculated and the aggregate function value associated with the build-side table must be updated when the data record is inserted into the ordered in-memory index. If the input parameters of the aggregate function come from the detection-side table, the aggregate function does not need to be calculated; only the initial value is retained. Additionally, data records can be sorted within the ordered memory index. Before inserting data records into the ordered memory index, each data record can be sorted based on the search key value within each data record. Then, based on the sorting results, each data record can be sequentially inserted into the ordered memory index. Furthermore, data records can be inserted into the ordered memory index first, and then sorted within the ordered memory index based on the search key value within each data record. This allows for sorting of the build-side table.

[0101] For example, please refer to Figure 4. Figure 4 (A) shows an SQL query statement, and Figure 4 (B) shows Table 1 (i.e., t1 described in Figure 4 (A)) and Table 2 (i.e., t2 described in Figure 4 (A)). By constructing an ordered memory index with Table 1 shown in Figure 4 (B) as the construction side table, an ordered memory index as shown in Figure 4 (C) can be obtained. Among them, the search key used in the process of constructing the ordered memory index is t1.x, and the ordered memory index is sorted according to the search key value. In Table 1, the record with the connection key value t1.x of 5 appears twice, so the key value count (count column) is 2, and the records with the connection key values ​​t1.x of 2 and 3 appear once each, so the key value count is 1. The input parameter of the aggregation function SUM(t1.y) comes from the construction side table (i.e., Table 1), so the result can be directly calculated. The input parameter for SUM(t2.c) comes from the detection-side table (i.e., Table 2). Since the record values ​​for Table 2 are not yet available, the aggregation function is set to the initial value and will be calculated during subsequent matching. The match flag (match column) marks the result of the connection matching. Since there is no match with Table 2, it is initialized to 0 at this time.

[0102] 2) Detect the ordered memory index and calculate the aggregate function and update the matching mark

[0103] When probing the ordered memory index and calculating the aggregate function and updating the matching mark, the table on the other side of the join operation can be used as the probe side table, and the join key value in each record in the probe side table can be used to probe the ordered memory index, and based on the probe result, the aggregate function value related to the probe side table can be updated in the ordered memory index, and the matching mark can be updated. When the join key value in a certain record matches the search key value in the ordered memory index, the aggregate function in the probe side table can be calculated based on the number of key values ​​of the search key value, and the aggregate function value related to the search key value and the join key value can be updated. In addition, the matching mark can also be updated (that is, marked as matched). When the join key value in a certain record does not match the search key value in the ordered memory index, the next record is obtained for probing. In this way, through an ordered memory index, the join matching and group aggregation operations are simultaneously realized.

[0104] For example, referring to FIG4 , after constructing the ordered memory index shown in FIG4 (C), the ordered memory index can be detected using Table 2 as the detection side table, and the aggregate function and the updated matching mark can be calculated. The first record (1,3,7) in Table 2 can be used to detect in the ordered memory index. Since the connection key value 1 in the record does not exist in the ordered memory index, the match is unsuccessful. The ordered memory index at this time remains unchanged. Therefore, the ordered memory index shown in FIG4 (D) is the same as the ordered memory index shown in FIG4 (C).

[0105] Then, using the second record (5,2,4) in Table 2, we search the ordered memory index. The connection key value 5 in this record exists in the ordered memory index, so the match is successful. The match flag can be updated from 0 to 1. At the same time, we can calculate the aggregate function SUM(t2.c). This calculation requires using the number of key values ​​in the ordered memory index and updating the aggregate function value associated with the search key value 5 in the ordered memory index. Here, the aggregate function value SUM(t2.c) = 0 + 4*2 = 8. In this way, we can obtain the ordered memory index shown in (E) of Figure 4.

[0106] Next, the third record (2,6,3) in Table 2 is used to search the ordered memory index. The connection key value 2 in this record exists in the ordered memory index, so the match is successful and the match flag can be updated from 0 to 1. At the same time, the aggregate function SUM(t2.c) can be calculated. The calculation requires the number of key values ​​in the ordered memory index and the update of the aggregate function value related to the search key value 2 in the ordered memory index. The aggregate function value SUM(t2.c) = 0 + 3 * 1 = 3. In this way, the ordered memory index shown in (F) of Figure 4 can be obtained.

[0107] Finally, the fourth record (5,1,5) in Table 2 is used to detect in the ordered memory index. The connection key value 5 in the record exists in the ordered memory index, so the match is successful. Since the matching identifier corresponding to the connection key value 5 at this time indicates that it has been matched, it does not need to be updated. At the same time, the aggregate function SUM (t2.c) can be calculated. When calculating, it is necessary to use the number of key values ​​in the ordered memory index and the value of the aggregate function calculated previously, as well as update the aggregate function value related to the search key value 5 in the ordered memory index. Among them, the aggregate function value SUM (t2.c) = 8 + 5 * 2 = 18. In this way, the ordered memory index shown in (G) of Figure 4 can be obtained.

[0108] 3) Based on the ordered memory index, determine the final result of the aggregate function to obtain the query result set.

[0109] After completing the detection of the ordered memory index, the execution results of the created fusion operator are stored in the ordered memory index. However, for certain aggregation functions, another calculation may be required to determine the final result. For example, when the aggregation function is the AVG function, another division calculation is required to obtain the average result. Finally, the query result set can be obtained by filtering from the ordered memory index based on the join type in the SQL query statement. The query result set can be obtained by scanning the leaf nodes in the ordered memory index. Since the leaf nodes of the ordered memory index are ordered, the results in the query result set are also ordered. Therefore, in certain queries with sorting operations (such as ORDER BY, etc.), the sorting operation can be omitted, reducing the overhead of the sorting operation and improving query efficiency. In some embodiments, in addition to the join operation and the group aggregation operation, the query statement may also include a sorting operation (such as ORDER BY, etc.). In this case, the fusion operator is also used to complete the sorting operation. The sorting operation is automatically completed during the construction of the ordered content index.

[0110] For example, continuing to refer to Figure 4, under the SQL query statement shown in (A) of Figure 4 and the ordered memory index shown in (G) of Figure 4, the query result set can be as shown in (H) of Figure 4. In addition, please refer to Figure 5. Under the SQL query statement shown in (A) of Figure 5 and the ordered memory index shown in (G) of Figure 5, the query result set can be as shown in (H) of Figure 5. Among them, the main difference between Figure 5 and Figure 4 is that the SQL query statement and the query result set are different. The process shown in Figure 5 can be found in the description in Figure 4, and will not be repeated here. It can be seen from Figure 5 and Figure 4 that for SQL query statements with different connection types but the same other data, combined with matching tags, the required query result set can be obtained through the same fusion operator.

[0111] The above description of the various operation sequences in the optimal execution plan associated with the fusion operator shows that they all use the same physical data structure (i.e., ordered memory indexes). This results in better temporal and spatial locality during the execution of the fusion operator, leading to higher execution efficiency. Furthermore, during the fusion operator execution, only the build-side table needs to be sorted, not the probe-side table. This avoids a costly sort operation, further improving query execution performance.

[0112] In addition, in order to further improve the execution performance of the query, after building the ordered memory index and before detecting the ordered memory index, a range condition can be generated based on the join condition in the SQL query statement and the range of the search key value in the construction side table. The range condition can be used to indicate the range of the join key value in the detection side table. For example, please refer to Figure 6. Under the SQL query statement shown in Figure 6 (A) and Table 1 (i.e., t1 in Figure 6 (A)) and Table 2 (i.e., t2 in Figure 6 (A)) shown in Figure 6 (B), an ordered memory index as shown in Figure 6 (C) can be constructed with Table 1 as the construction side table. From the ordered memory index shown in Figure 6 (C), it can be known that the minimum value of the join key value t1.x in Table 1 is 0 and the maximum value is 5, that is, t1.x>=0AND t1.x<=5. As shown in FIG6 (D), after obtaining the range of the connection key values ​​in Table 1, combined with the connection condition t1.x>t2.a shown in FIG6 (A), the range condition of the connection key values ​​in Table 2 can be obtained: t2.a<5.

[0113] Furthermore, when detecting an ordered memory index, when using any record in the detection side table for detection, it is possible to first determine whether the connection key value in the record meets the range condition of the connection key value in the generated detection side table. When the range condition is met, the connection key value in the record can be used to detect the ordered memory index. When the range condition is not met, the next record in the detection side table can be judged. In this way, the data can be filtered in advance before detecting the ordered memory index, thereby avoiding subsequent unnecessary calculations and improving the execution performance of the query. For example, referring to Figure 6, the connection key values ​​of Table 2 shown in (B) of Figure 6 are 10 and 20, both of which are greater than 5, that is, they do not meet the range condition shown in (D) of Figure 6. Therefore, there is no need to detect the ordered memory index, and the execution can be terminated early.

[0114] It should be understood that the construction side table and detection side table described above can be physical tables stored in the database (i.e., base tables), or derived tables (such as views or intermediate results in the query process, etc.). The specific details can be determined according to actual conditions and are not limited here.

[0115] It should be understood that the order of execution of the steps in the above embodiments does not necessarily imply a specific order of execution. The order of execution of each process should be determined by its function and inherent logic, and should not constitute any limitation on the implementation process of the embodiments of this application. In addition, the various embodiments described above can be combined according to actual circumstances, and the combined solutions are still within the scope of protection of this application.

[0116] Based on the method in the above embodiment, an embodiment of the present application provides a database query optimization device.

[0117] Exemplarily, FIG7 shows a schematic structural diagram of a database query optimization device provided by an embodiment of the present application. As shown in FIG7 , the database query optimization device 700 includes: an acquisition module 701 and a processing module 702. Among them, the acquisition module 701 is used to obtain a query statement, and the query statement includes: a join operation and a group aggregation operation. The processing module 702 is used to determine that the join key and the group key in the query statement are the same, the aggregation function in the query statement can be split and calculated, and the input parameters of the aggregation function come from the same side of the join condition in the query statement. The processing module 702 is also used to create a fusion operator, which is used to complete the join operation and the group aggregation operation; and, when the cost of executing the fusion operator is less than or equal to the target cost, based on the fusion operator, an optimal execution plan related to the fusion operator is generated, the target cost is the cost of executing the join operator and the group aggregation operator, the join operator is used to complete the join operation, and the group aggregation operator is used to complete the group aggregation operation. In addition, the processing module 702 is also used to step by step execute operations according to the operation sequence in the optimal execution plan to obtain a query result set.

[0118] In some embodiments, when the processing module 702 executes operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set, it is specifically used to: use the table on the first side of the connection operation as the build side table to build an ordered memory index, and the ordered memory index includes: a search key value, a key value count, a matching tag and an aggregate function value, the search key value is the connection key value in the construction side table, the key value count is the number of times the search key value appears in the construction side table, the matching tag is used to identify whether the search key value successfully matches the connection key value in the detection side table, the detection side table is the table on the second side in the connection operation, the aggregate function value is the value obtained by calculating the aggregate function in the query statement, and the aggregate function value includes the aggregate function value related to the construction side table and / or the aggregate function value related to the detection side table; use the connection key value in the detection side table to detect the ordered memory index, and, based on the detection result, update the aggregate function value related to the detection side table and the matching tag in the ordered memory index; based on the connection type in the query statement, filter out the query result set from the ordered memory index.

[0119] In some embodiments, when the processing module 702 uses the table on the first side of the connection operation as the construction side table to construct an ordered memory index, it is specifically used to: construct a data record for each connection key value in the construction side table, and the data record includes: the search key value, the initial value of the aggregate function value, the number of key values ​​and the initial value of the matching tag; insert the constructed data record into the ordered memory index to construct an ordered memory index; wherein, when the first data record and the second data record inserted into the ordered memory index are the same, retain the first data record or the second data record, and accumulate the number of key values ​​in the first data record and the second data record; and, when the input parameter of the aggregate function in the query statement comes from the construction side table, update the aggregate function value related to the first data record or the second data record.

[0120] In some embodiments, when inserting the constructed data records into the ordered memory index, the processing module 702 is specifically used to: sort the data records according to the search key value in the data records, and insert the data records into the ordered memory index in sequence based on the sorting result; or, insert the data records into the ordered memory index, and sort the data records in the ordered memory index according to the search key value in the data records.

[0121] In some embodiments, when the processing module 702 uses the connection key value in the detection side table to detect the ordered memory index, it is specifically used to: use the connection key value in the first record in the detection side table to match the search key value in the ordered memory index, and the first record is any record in the detection side table. At this time, based on the detection result, the aggregate function value related to the detection side table is updated in the ordered memory index, and the matching identifier is updated, including: when the connection key value in the first record matches the first search key value in the ordered memory index, the matching identifier related to the first search key value is updated in the ordered memory index, and, based on the number of key values ​​related to the first search key value, the aggregate function value related to the first search key value and the connection key value in the first record is updated; when the connection key value in the first record does not match the first search key value in the ordered memory index, the connection key value in any remaining record in the detection side table is used to match the search key value in the ordered memory index.

[0122] In some embodiments, before using any connection key value in the detection side table to detect the ordered memory index, the processing module 702 is also used to: generate a target range condition based on the range of the search key value in the ordered memory index and the connection condition in the query statement, and the target range condition is used to indicate the range of the connection key value in the detection side table; determine whether any connection key value satisfies the target range condition, wherein, if any connection key value does not satisfy the target range condition, no longer use any connection key value to detect the ordered memory index.

[0123] In some embodiments, both the build-side table and the detection-side table may be base tables or derived tables (such as views or intermediate results in a query process).

[0124] In some embodiments, the query statement further includes a sorting operation, and the fusion operator is also used to complete the sorting operation.

[0125] In some embodiments, the join condition is used for equal-value joins, or for unequal-value joins, or for both equal-value joins and unequal-value joins.

[0126] In some embodiments, both the acquisition module 701 and the processing module 702 shown in FIG7 can be implemented via software or hardware. For example, the implementation of the acquisition module 701 will be described below using the acquisition module 701 as an example. Similarly, the implementation of the processing module 702 can also refer to the implementation of the acquisition module 701.

[0127] As an example of a software functional unit, the acquisition module 701 may include code running on a computing instance. The computing instance may include at least one of a physical host (computing device), a virtual machine, and a container. Furthermore, the computing instance may be one or more. For example, the acquisition module 701 may include code running on multiple hosts / virtual machines / containers. It should be noted that the multiple hosts / virtual machines / containers used to run the code may be distributed in the same region or in different regions. Furthermore, the multiple hosts / virtual machines / containers used to run the code may be distributed in the same availability zone (AZ) or in different AZs, each AZ including one data center or multiple geographically close data centers. Typically, a region may include multiple AZs.

[0128] Similarly, multiple hosts / virtual machines / containers running the code can be distributed within the same virtual private cloud (VPC) or across multiple VPCs. Typically, a VPC is set up within a region. Cross-region communication between two VPCs within the same region, or between VPCs in different regions, requires a communication gateway within each VPC to interconnect the VPCs.

[0129] As an example of a hardware functional unit, the acquisition module 701 may include at least one computing device, such as a server. Alternatively, the acquisition module 701 may be implemented using an application-specific integrated circuit (ASIC) or a programmable logic device (PLD). The PLD may be a complex programmable logical device (CPLD), a field-programmable gate array (FPGA), a generic array logic (GAL), or any combination thereof.

[0130] The multiple computing devices included in acquisition module 701 can be distributed in the same region or in different regions. The multiple computing devices included in acquisition module 701 can be distributed in the same AZ or in different AZs. Similarly, the multiple computing devices included in acquisition module 701 can be distributed in the same VPC or in multiple VPCs. The multiple computing devices can be any combination of servers, ASICs, PLDs, CPLDs, FPGAs, GALs, and other computing devices.

[0131] It should be noted that, in other embodiments, the acquisition module 701 can be used to execute any step in the database query optimization method described in the above embodiment, and the processing module 702 can also be used to execute any step in the database query optimization method described in the above embodiment. The steps that the acquisition module 701 and the processing module 702 are responsible for implementing can be specified as needed. By having the acquisition module 701 and the processing module 702 respectively implement different steps in the database query optimization method described in the above embodiment, the full functionality of the database query optimization device 700 shown in Figure 7 is achieved.

[0132] This application also provides a computing device 800. As shown in Figure 8, computing device 800 includes a bus 802, a processor 804, a memory 806, and a communication interface 808. Processor 804, memory 806, and communication interface 808 communicate with each other via bus 802. Computing device 800 can be a server or a terminal device. It should be understood that this application does not limit the number of processors and memories in computing device 800.

[0133] Bus 802 may be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, among others. Buses may be classified as address buses, data buses, control buses, and the like. For ease of illustration, FIG8 illustrates a single bus line, but this does not imply a single bus or type of bus. Bus 804 may include a path for transmitting information between various components of computing device 800 (e.g., memory 806, processor 804, and communication interface 808).

[0134] The processor 804 may include any one or more processors such as a central processing unit (CPU), a graphics processing unit (GPU), a microprocessor (MP), or a digital signal processor (DSP).

[0135] The memory 806 may include volatile memory, such as random access memory (RAM). The processor 804 may also include non-volatile memory, such as read-only memory (ROM), flash memory, hard disk drive (HDD), or solid state drive (SSD).

[0136] Memory 806 stores executable program code, which processor 804 executes to implement the functions of acquisition module 701 and processing module 702 shown in FIG. 7 , thereby implementing the database query optimization method described in the above embodiment. In other words, memory 806 stores instructions for executing the database query optimization method described in the above embodiment.

[0137] Alternatively, the memory 806 stores executable code, and the processor 804 executes the executable code to respectively implement the functions of the database query optimization device 700 shown in Figure 7, thereby implementing the database query optimization method described in the above embodiment. In other words, the memory 806 stores instructions for executing the database query optimization method described in the above embodiment.

[0138] The communication interface 803 uses a transceiver module such as, but not limited to, a network interface card or a transceiver to implement communication between the computing device 800 and other devices or a communication network.

[0139] Embodiments of the present application also provide a computing device cluster. The computing device cluster includes at least one computing device. The computing device can be a server, such as a central server, an edge server, or a local server in a local data center. In some embodiments, the computing device can also be a terminal device such as a desktop computer, a laptop computer, or a smartphone.

[0140] As shown in Figure 9, the computing device cluster includes at least one computing device 800. The memory 806 in one or more computing devices 800 in the computing device cluster may store the same instructions for executing the database query optimization method described in the above embodiment.

[0141] In some possible implementations, the memory 806 of one or more computing devices 800 in the computing device cluster may also store some instructions for executing the database query optimization method described in the above embodiment. In other words, the combination of one or more computing devices 800 can jointly execute the instructions for executing the database query optimization method described in the above embodiment.

[0142] It should be noted that the memory 806 in different computing devices 800 in the computing device cluster can store different instructions, each for executing part of the functions of the database query optimization apparatus 700 shown in FIG7 . In other words, the instructions stored in the memory 806 in different computing devices 800 can implement the functions of one or more modules in the acquisition module 701 and the processing module 702.

[0143] In some possible implementations, one or more computing devices in a computing device cluster may be connected via a network. The network may be a wide area network (WAN) or a local area network (LAN), among others. FIG. 10 illustrates a possible implementation. As shown in FIG. 10 , two computing devices 800A and 800B are connected via a network. Specifically, the network is connected via a communication interface in each computing device. In this type of possible implementation, the memory 806 in the computing device 800A stores instructions for executing the functions of the acquisition module 701. Simultaneously, the memory 806 in the computing device 800B stores instructions for executing the functions of the processing module 702.

[0144] It should be understood that the functionality of the computing device 800A shown in FIG10 may also be implemented by multiple computing devices 800. Similarly, the functionality of the computing device 800B may also be implemented by multiple computing devices 800.

[0145] The present application also provides another computing device cluster. The connection relationship between the computing devices in this computing device cluster can be similar to the connection method of the computing device cluster described in Figures 9 and 10. However, the memory 806 in one or more computing devices 800 in this computing device cluster can store the same instructions for executing the method described in the above embodiment.

[0146] In some possible implementations, the memory 806 of one or more computing devices 800 in the computing device cluster may also store partial instructions for executing the aforementioned database query optimization method. In other words, the combination of one or more computing devices 800 can jointly execute instructions for executing the aforementioned database query optimization method.

[0147] Based on the method in the above embodiment, an embodiment of the present application provides a computer-readable storage medium, which stores a computer program. When the computer program is run on a computing device cluster including at least one computing device, the computing device cluster executes the method described in the above embodiment. Exemplarily, the computer-readable storage medium can be any available medium that can be stored by a computing device or a data storage device such as a data center containing one or more available media. The available medium can be a magnetic medium (for example, a floppy disk, a hard disk, a tape), an optical medium (for example, a DVD), or a semiconductor medium (for example, a solid-state drive), etc.

[0148] Based on the method in the above embodiment, an embodiment of the present application provides a computer program product containing instructions. When the computer program product is run on a computing device cluster including at least one computing device, the computing device cluster executes the method in the above embodiment.

[0149] It is understood that the processor in the embodiments of the present application may be a central processing unit (CPU), or may be other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field programmable gate arrays (FPGA), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. The general-purpose processor may be a microprocessor or any conventional processor.

[0150] The method steps in the embodiments of the present application can be implemented by hardware or by a processor executing software instructions. The software instructions can be composed of corresponding software modules, which can be stored in random access memory (RAM), flash memory, read-only memory (ROM), programmable read-only memory (PROM), erasable programmable read-only memory (EPROM), electrically erasable programmable read-only memory (EEPROM), registers, hard disks, mobile hard disks, CD-ROMs or any other form of storage medium known in the art. An exemplary storage medium is coupled to the 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 an ASIC.

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

[0152] It will be understood that the various numerical numbers involved in the embodiments of the present application are merely distinctions for the convenience of description and are not intended to limit the scope of the embodiments of the present application.

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

Claims

1. A database query optimization method, characterized in that: include: Obtaining a query statement, wherein the query statement includes: a join operation and a grouping and aggregation operation; Determine that the join key and the group key in the query statement are the same, the aggregate function in the query statement can be split for calculation, and the input parameters of the aggregate function come from the same side of the join condition in the query statement; Creating a fusion operator, where the fusion operator is used to complete the connection operation and the grouping aggregation operation; In the case where the cost of executing the fusion operator is less than or equal to the target cost, generating an optimal execution plan related to the fusion operator according to the fusion operator, wherein the target cost is the cost of executing a join operator and a group aggregation operator, wherein the join operator is used to complete the join operation, and the group aggregation operator is used to complete the group aggregation operation; The operations are executed step by step according to the operation sequence in the optimal execution plan to obtain a query result set.

2. The method according to claim 1, characterized in that The step of gradually executing operations according to the operation sequence in the optimal execution plan to obtain a query result set includes: Taking the table on the first side in the connection operation as the construction side table, an ordered memory index is constructed, wherein the ordered memory index includes: a search key value, a key value count, a matching tag, and an aggregate function value, wherein the search key value is a connection key value in the construction side table, the key value count is the number of times the search key value appears in the construction side table, and the matching tag is used to identify whether the search key value successfully matches the connection key value in the detection side table, the detection side table is a table on the second side in the connection operation, and the aggregate function value is a value obtained by calculating the aggregate function in the query statement, and the aggregate function value includes an aggregate function value related to the construction side table and / or an aggregate function value related to the detection side table; Using the connection key value in the detection side table to detect the ordered memory index, and, based on the detection result, updating the aggregate function value related to the detection side table in the ordered memory index and updating the matching identifier; Based on the connection type in the query statement, the query result set is filtered out from the ordered memory index.

3. The method according to claim 2, characterized in that The step of using the table on the first side in the join operation as a construction side table to construct an ordered memory index includes: A data record is constructed for each connection key value in the construction side table, wherein the data record includes: the search key value, the initial value of the aggregate function value, the key value count, and the initial value of the matching mark; Inserting the constructed data record into the ordered memory index to construct the ordered memory index; Among them, when the first data record and the second data record inserted into the ordered memory index are the same, the first data record or the second data record is retained, and the number of key values ​​in the first data record and the second data record is accumulated, and when the input parameter of the aggregate function in the query statement comes from the construction side table, the aggregate function value related to the first data record or the second data record is updated.

4. The method according to claim 3, characterized in that The inserting the constructed data record into the ordered memory index comprises: Sorting the data records according to the search key values ​​in the data records, and inserting the data records into the ordered memory index in sequence based on the sorting results; Alternatively, the data records are inserted into the ordered memory index, and the data records are sorted in the ordered memory index according to the key values ​​in the data records.

5. The method according to any one of claims 2 to 4, characterized in that: The using the connection key value in the detection side table to detect the ordered memory index includes: Use the connection key value in the first record in the detection side table to match the search key value in the ordered memory index, where the first record is any record in the detection side table; The updating of the aggregate function value related to the detection side table in the ordered memory index based on the detection result and the updating of the matching identifier include: In the case where the join key value in the first record matches the first search key value in the ordered memory index, updating a match identifier associated with the first search key value in the ordered memory index, and, based on the key value count associated with the first search key value, updating an aggregate function value associated with the first search key value and the join key value in the first record; When the connection key value in the first record does not match the first search key value in the ordered memory index, the connection key value in any remaining record in the detection side table is used to match the search key value in the ordered memory index.

6. The method according to any one of claims 2 to 5, characterized in that: Before using any one of the connection key values ​​in the detection side table to detect the ordered memory index, the method further includes: Generate a target range condition based on the range of the search key value in the ordered memory index and the join condition in the query statement, wherein the target range condition is used to indicate the range of the join key value in the detection side table; It is determined that any one of the connection key values ​​satisfies the target range condition, wherein, if any one of the connection key values ​​does not satisfy the target range condition, the any one of the connection key values ​​is no longer used to detect the ordered memory index.

7. The method according to any one of claims 2 to 6, characterized in that: Both the construction-side table and the detection-side table may be base tables or derived tables.

8. The method according to any one of claims 1 to 7, characterized in that: The query statement also includes: a sorting operation, and the fusion operator is also used to complete the sorting operation.

9. The method according to any one of claims 1 to 8, characterized in that: The connection conditions are used for equal value connection and non-equal value connection.

10. A database query optimization device, characterized in that: include: An acquisition module, used for acquiring a query statement, wherein the query statement includes: a connection operation and a grouping and aggregation operation; A processing module, used for determining that the join key and the group key in the query statement are the same, the aggregate function in the query statement can be split for calculation, and the input parameters of the aggregate function are from the same side of the join condition in the query statement; The processing module is further used to create a fusion operator, and the fusion operator is used to complete the connection operation and the group aggregation operation; The processing module is further used to generate an optimal execution plan related to the fusion operator according to the fusion operator when the cost of executing the fusion operator is less than or equal to the target cost, the target cost is the cost of executing the connection operator and the grouping aggregation operator, the connection operator is used to complete the connection operation, and the grouping aggregation operator is used to complete the grouping aggregation operation; The processing module is further used to execute operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set.

11. The device according to claim 10, characterized in that When the processing module gradually executes operations according to the operation sequence in the optimal execution plan to obtain a query result set, it is specifically used to: Taking the table on the first side in the connection operation as the construction side table, an ordered memory index is constructed, wherein the ordered memory index includes: a search key value, a key value count, a matching tag, and an aggregate function value, wherein the search key value is a connection key value in the construction side table, the key value count is the number of times the search key value appears in the construction side table, and the matching tag is used to identify whether the search key value successfully matches the connection key value in the detection side table, the detection side table is a table on the second side in the connection operation, and the aggregate function value is a value obtained by calculating the aggregate function in the query statement, and the aggregate function value includes an aggregate function value related to the construction side table and / or an aggregate function value related to the detection side table; Using the connection key value in the detection side table to detect the ordered memory index, and, based on the detection result, updating the aggregate function value related to the detection side table in the ordered memory index and updating the matching identifier; Based on the connection type in the query statement, the query result set is filtered out from the ordered memory index.

12. The device according to claim 11, characterized in that When the processing module uses the table on the first side in the connection operation as the construction side table to construct an ordered memory index, it is specifically used to: A data record is constructed for each connection key value in the construction side table, wherein the data record includes: the search key value, the initial value of the aggregate function value, the key value count, and the initial value of the matching mark; Inserting the constructed data record into the ordered memory index to construct the ordered memory index; Among them, when the first data record and the second data record inserted into the ordered memory index are the same, the first data record or the second data record is retained, and the number of key values ​​in the first data record and the second data record is accumulated, and when the input parameter of the aggregate function in the query statement comes from the build side table, the aggregate function value related to the first data record or the second data record is updated.

13. The device according to claim 12, characterized in that When inserting the constructed data record into the ordered memory index, the processing module is specifically used to: Sorting the data records according to the search key values ​​in the data records, and inserting the data records into the ordered memory index in sequence based on the sorting results; Alternatively, the data records are inserted into the ordered memory index, and the data records are sorted in the ordered memory index according to the key values ​​in the data records.

14. The device according to any one of claims 11 to 13, characterized in that: When the processing module detects the ordered memory index using the connection key value in the detection side table, it is specifically used to: Use the connection key value in the first record in the detection side table to match the search key value in the ordered memory index, where the first record is any record in the detection side table; The updating of the aggregate function value related to the detection side table in the ordered memory index based on the detection result and the updating of the matching identifier include: In the case where the join key value in the first record matches the first search key value in the ordered memory index, updating a match identifier associated with the first search key value in the ordered memory index, and, based on the key value count associated with the first search key value, updating an aggregate function value associated with the first search key value and the join key value in the first record; When the connection key value in the first record does not match the first search key value in the ordered memory index, the connection key value in any remaining record in the detection side table is used to match the search key value in the ordered memory index.

15. The device according to any one of claims 11 to 14, characterized in that: Before using any one of the connection key values ​​in the detection side table to detect the ordered memory index, the processing module is further used to: Generate a target range condition based on the range of the search key value in the ordered memory index and the join condition in the query statement, wherein the target range condition is used to indicate the range of the join key value in the detection side table; It is determined that any one of the connection key values ​​satisfies the target range condition, wherein, if any one of the connection key values ​​does not satisfy the target range condition, the any one of the connection key values ​​is no longer used to detect the ordered memory index.

16. The device according to any one of claims 11 to 15, characterized in that: Both the construction-side table and the detection-side table may be base tables or derived tables.

17. The device according to any one of claims 10 to 16, characterized in that: The query statement also includes: a sorting operation, and the fusion operator is also used to complete the sorting operation.

18. The device according to any one of claims 10 to 17, characterized in that: The connection conditions are used for equal value connection and non-equal value connection.

19. A computing device cluster, characterized in that: comprising at least one computing device, each computing device comprising a processor and a memory; The processor of the at least one computing device is used to execute instructions stored in the memory of the at least one computing device, so that the computing device cluster executes the method according to any one of claims 1-9.

20. A computer-readable storage medium storing a computer program, which, when executed on a computing device cluster comprising at least one computing device, enables the computing device cluster to execute the method according to any one of claims 1 to 9.

21. A computer program product, characterized in that When the computer program product is run on a computing device cluster including at least one computing device, the computing device cluster is enabled to execute the method according to any one of claims 1 to 9.

Images