Database query optimization method and device and computing device cluster

Abstract

The database query optimization method comprises the steps that a query statement is obtained, and the query statement comprises a connection operation and a grouping aggregation operation; determining that a connection key and a grouping key in the query statement are the same, an aggregation function in the query statement is detachable, and input parameters of the aggregation function are from the same side of a connection condition in the query statement; creating a fusion operator, wherein the fusion operator is used for completing a connection operation and a grouping aggregation operation; under the condition that the cost of executing the fusion operator is smaller than or equal to a target cost, an optimal execution plan related to the fusion operator is generated according to the fusion operator, and the target cost is the cost of executing the connection operator and the grouping aggregation operator; and executing operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set. Therefore, when the query statement meets the corresponding characteristics, the connection and grouping aggregation operations can be fused into one operator, so that the processing flow of an intermediate result can be reduced, the execution of the query statement is accelerated, and the execution performance of the query is improved.

Description

Technical Field

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

[0002] In a database management system (DBMS), for a given structured query language (SQL) query statement, the query optimizer in the DBMS can rewrite the SQL statement (also known as query conversion) according to predefined rules to generate query statements equivalent to the initial SQL statement. Then, the query optimizer can evaluate the cost of these equivalent statements and generate the optimal execution plan based on the equivalent query statement with the minimum cost. However, in the optimal execution plan, some redundant calculations often occur, wasting performance and reducing execution efficiency. Summary of the invention

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

[0004] In a first aspect, the present application provides a database query optimization method, comprising: obtaining a query statement, the query statement comprising: 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 is 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 according to the fusion operator, 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; executing operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set.

[0005] In this way, when the query statement meets the corresponding characteristics, the connection 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; and compared with a single operator, the fused operator can use less memory and CPU resources, saving memory overhead and CPU resources.

[0006] In a possible implementation, operations are performed 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 connection operation as the construction side table, constructing an ordered memory index, the ordered memory index including: a search key value, a key value count, a matching mark, and an aggregate function value, 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, the matching mark 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, 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; 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 and the matching mark in the ordered memory index; based on the connection type in the query statement, filtering the query result set from the ordered memory index. In this way, the connection matching and group aggregation operations can be implemented simultaneously through the ordered memory index, thereby improving the execution performance. In addition, during the execution of the operation sequence, only the construction side table can be sorted, reducing the number of sorting times. In addition, these operation sequences all use the same physical data structure (i.e., ordered memory index), so the time and space locality during the execution of the fusion operator is better, resulting in higher execution efficiency.

[0007] In a possible implementation, the table on the first side of the connection operation is used as the construction side table to construct an ordered memory index, including: constructing a data record for each connection key value in the construction side table, the data record including: 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; 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 construction side table, updating the aggregate function value related to the first data record or the second data record. In this way, an ordered memory index can be constructed.

[0008] In a possible implementation, inserting the constructed data records into the ordered memory index includes: sorting the data records according to the search key value in the data records, and inserting the data records into the ordered memory index in sequence based on the sorting result; 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 value in the data records. In this way, the data in the ordered memory index can be sorted.

[0009] In a 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.

[0010] In a possible implementation, before using any connection key value in the detection side table to detect the ordered memory index, it also includes: generating 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, the target range condition is used to indicate the range of the connection key value in the detection side table; determining that any connection key value satisfies the target range condition, wherein, if any connection key value does not satisfy the target range condition, no longer using any connection key value to detect the ordered memory index. In this way, data can be filtered before detecting the ordered memory index, unnecessary detection can be avoided, and execution performance can be improved.

[0011] 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, etc.).

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

[0013] In a possible implementation, the connection condition is used for equal-value connection, or for unequal-value connection, or for equal-value connection and unequal-value connection.

[0014] In a second aspect, the present application provides a database query optimization device, including: an acquisition module and a processing module. Among them, the acquisition module is used to obtain a query statement, and the query statement 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 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 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.

[0015] In a 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 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, 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 update 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.

[0016] In one possible implementation, when the processing module constructs an ordered memory index with the table on the first side in a join operation as the build side table, the processing module is specifically used to: construct a data record for each connection 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; 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.

[0017] 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 values ​​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 values ​​in the data records.

[0018] 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.

[0019] 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, the target range condition being 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.

[0020] 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, etc.).

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

[0022] In a possible implementation, the connection condition is used for equal-value connection, or for unequal-value connection, or for equal-value connection and unequal-value connection.

[0023] 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.

[0024] In a fourth aspect, the present application provides a computer-readable storage medium, including computer program instructions, when the computer program instructions are executed by a computing device cluster, the computing device cluster executes the method described in the first aspect or any possible implementation of the first aspect, or executes 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.

[0025] In a fifth aspect, the present application provides a computer program product including instructions, which, when executed by a computing device cluster, enables 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.

[0026] 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

[0027] Figure 1 It is a schematic diagram of the logical architecture of a database management system provided in an embodiment of the present application;

[0028] Figure 2 It is a flowchart of a database query optimization method provided in an embodiment of the present application;

[0029] Figure 3 It is a schematic diagram of the steps of constructing an ordered memory index provided by an embodiment of the present application;

[0030] Figure 4 It is a schematic diagram of a query execution process of an SQL query statement provided in an embodiment of the present application;

[0031] Figure 5 This is a schematic diagram of the query execution process of another SQL query statement provided in an embodiment of the present application;

[0032] Figure 6 It is a schematic diagram of a process of constructing an ordered memory index and generating a range condition provided by an embodiment of the present application;

[0033] Figure 7 It is a structural schematic diagram of a database query optimization device provided in an embodiment of the present application;

[0034] Figure 8 is a schematic diagram of the structure of a computing device provided in an embodiment of the present application;

[0035] Fig. 9 is a schematic diagram of the structure of a computing device cluster provided in an embodiment of the present application;

[0036] Fig.10 It is a structural diagram of another computing device cluster provided in an embodiment of the present application. DETAILED DESCRIPTION

[0037] The term "and / or" in this article is a description of the association relationship of associated objects, indicating that there can be three relationships. For example, A and / or B can represent: A exists alone, A and B exist at the same time, and B exists alone. The symbol " / " in this article indicates that the associated objects are in an or relationship, for example, A / B means A or B.

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

[0039] In the embodiments of the present 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 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.

[0040] In the description of the embodiments of the present application, unless otherwise specified, "multiple" means two or more than two. For example, multiple processing units refer to two or more processing units, etc.; multiple elements refer to two or more elements, etc.

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

[0042] (1) Query

[0043] Query refers to the operation of retrieving data from a database. It can obtain data from a single table or from multiple tables through a join operation. Queries can be written using SQL statements. Through queries, users can easily obtain the required data for data analysis, report generation, decision support, and other tasks.

[0044] (2) Operator

[0045] Operators are also called operators, which are used to process data. Operators are divided into logical operators and physical operators. Logical operators describe the semantics of operations but do not involve specific implementations. Physical operators describe the specific execution methods. For example, the join operation is a logical operator, and the corresponding hash join, nested loop join, and sort-merge join are physical operators.

[0046] (3) Query Optimizer

[0047] The query optimizer is an important component in the database management system. It is used to parse, analyze, optimize, and generate execution plans for SQL statements. The goal of the query optimizer is to find the best execution plan to meet the user's query requirements with the least time and resource cost. The query optimizer is mainly responsible for converting logical operators into physical operators and generating an efficient execution plan. Among them, the physical operators are displayed in the execution plan.

[0048] (4) Execution plan

[0049] The execution plan describes the specific steps and execution order of the database management system to execute the query statement. The basic operation unit in the execution plan is called a (physical) operator, which represents a specific operation, such as table scan, hash join, etc. The operators in the execution plan form a tree structure in the order of execution. The root node of the tree is the outermost operator, and the leaf node is the innermost operator.

[0050] (5) Connection operation

[0051] A join operation is to match the data in two or more tables according to the join conditions to generate a new table. Through the join operation, you can query related information from multiple tables. The join operation is usually implemented using SQL statements, including keywords such as INNER JOIN, LEFT JOIN, and RIGHT JOIN.

[0052] (6) Connection conditions

[0053] The join condition can specify the conditions for joining two tables, usually based on the comparison of certain columns in the two tables, so as to join the related data rows in the two tables together. For example, the query statement SELECT * FROM t1 INNER JOIN t2 ON t1.c1=t2.c2, the join condition is t1.c1=t2.c2, when the value of the c1 column of the t1 table is equal to the value of the c2 column of the t2 table, the related data rows of t1 and t2 are joined together. The join conditions are usually divided into equijoin and non-equijoin. Equijoin only uses the equal sign, and non-equijoin usually uses comparison operators, such as greater than, greater than or equal to, less than, less than or equal to, and not equal to.

[0054] (7) Connection key

[0055] A join key is a column or a combination of columns used to connect two tables. It can associate rows with the same or related values ​​in the two tables to generate a result set after the join. In SQL queries, the JOIN clause is usually used to specify the join key. For example, in the join condition t1.c1=t2.c2, the join key of the t1 table is c1, and the join key of the t2 table is c2; in the join condition t1.c1>t2.c2 AND t1.c3>t2.c4, the join key of the t1 table is (c1,c3), and the join key of the t2 table is (c2,c4).

[0056] (8) Grouping Key

[0057] A grouping key is a column or combination of columns used to group data. In SQL queries, a grouping key is usually specified using the GROUP BY clause. The grouping key determines how the data is divided into different groups, each with the same grouping key value. Grouping aggregation operations group data based on the value of the grouping key and calculate aggregate values ​​for each group (such as sum, average, count, etc.).

[0058] (9) Group aggregation

[0059] Group aggregation is to classify data according to the specified grouping conditions, and perform aggregation calculations on the data in each group to obtain the summary results of each group. Aggregation calculations can include: sum, average, maximum, minimum, etc. Group aggregation can be applied in data analysis, report generation and other fields. It can help users quickly understand the overall situation of the data and the distribution of each dimension, so as to make more accurate decisions. Group aggregation is usually implemented in SQL statements using GROUP BY and aggregate functions.

[0060] (10) Ordered Memory Index

[0061] Ordered memory index means that all index data is stored in memory and the index data is sorted according to the search key value. Ordered memory index can process point query and range query. The query condition of point query is equality condition, and the query condition of range query is inequality condition.

[0062] (11) Search key

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

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

[0065] Generally, when a query statement involves a join operation and a grouping and aggregation operation, a single physical operator is usually used in the DBMS to implement these two operations respectively. For example, a hash join is used to process a join operation, and a hash grouping and aggregation is used to process a grouping and aggregation operation. However, in some query statements that contain join operations and grouping and aggregation operations, when the join and grouping and aggregation have certain characteristics (for example, the join key and the grouping key are the same, etc.), using a single operator to implement them separately will appear redundant and waste performance.

[0066] 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.

[0067] For example, Figure 1 FIG. 1 shows a schematic diagram of the logical architecture of a database management system provided by an embodiment of the present application. Figure 1 As shown, the database management system 100 may include: a client 110, a SQL engine 120 and a storage engine 130. The client 110 refers to various forms of connecting to a database, such as activeX dataobjects (ADO) connection used in .Net, Java database connect (JDBC) connection used in Java, etc.

[0068] The SQL engine 120 is mainly responsible for generating an efficient execution plan for the SQL statement input by the client 110 under the current load scenario, and running the 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 mainly responsible for communicating with the client 110, and is responsible for business logic processing such as connection authentication, connection number judgment, and connection pool processing. The main function of the query cache 122 is to improve the efficiency of the query. The cache is stored in the form of a hash table of key and value. The key is a specific SQL statement, and the value is a collection of results. When a SQL statement arrives, if the query cache function is turned on, the SQL engine 120 can first check whether there is a data match in the query cache 122. If it matches, the matching data is directly returned to the client 110 without parsing the corresponding SQL statement. However, if there are user-defined functions, stored functions, user variables or temporary tables in the SQL statement, it will not pass through the query cache 122. If there is no match in the query cache 122, the parser 123 will be used to parse the corresponding SQL statement. The parser 123 is mainly responsible for parsing the SQL statement according to the grammatical rules, etc., 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. The executor 125 is mainly responsible for calling the interface of the storage engine 130 to execute the query or other operations according to the optimal execution plan after the optimizer 124 finds the optimal execution plan, and finally returns the query result set to the client 110.

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

[0070] Next, combine Figure 1 The database management system 100 shown introduces the database query optimization method provided in the embodiment of the present application.

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

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

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

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

[0075] In this embodiment, after obtaining the SQL query statement, it can be determined whether the connection 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 connection 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 connection 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 connection condition can be used for equal connection, can also be used for non-equal connection, and can also be used for both equal connection and non-equal connection.

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

[0077] 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 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 are 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 are added together to obtain the final result. Common separable aggregate functions include: SUM, AVG, COUNT, MAX and MIN.

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

[0079] 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 connection condition in the SQL query statement. When they come from the same side of the connection condition, S205 can be executed; otherwise, S207 is executed. For example, assuming the connection 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, so it does not meet the condition.

[0080] 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 in parallel or in sequence, and so on.

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

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

[0083] 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. The fusion operator can be used to complete the connection and group aggregation operations. In this way, the connection operation and the group aggregation operation can be completed by one operator, without using one operator to complete the connection operation and another operator to complete the group aggregation operation, thereby reducing the processing flow of the 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.

[0084] 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.

[0085] In this embodiment, after the fusion operator is generated, it can be determined whether the cost of executing the fusion operator is greater than the cost of executing the grouping aggregation operator and the connection operator. If the cost of executing the fusion operator is greater than the cost of executing the grouping aggregation operator and the connection operator, it indicates that the use of the fusion operator for connection and grouping aggregation operations cannot 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 grouping aggregation operator and the connection operator, it indicates that the use of the fusion operator for connection and grouping aggregation operations can bring performance improvement, so S209 can be executed.

[0086] 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.

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

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

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

[0090] S209: Generate an optimal execution plan related to the fusion operator according to the fusion operator.

[0091] In this embodiment, when the cost of executing the fusion operator is less than or equal to the cost of executing the grouping aggregation operator and the connection operator, an optimal execution plan related to the fusion operator can be generated according to the fusion operator. The optimal execution plan describes the operation sequence when executing the acquired SQL query statement.

[0092] 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.

[0093] 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.

[0094] In this way, when the SQL query statement meets the corresponding characteristics, the connection 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.

[0095] In some embodiments, the operation sequence in the optimal execution plan related to the fusion operator described in S209 may include: 1) constructing an ordered memory index, 2) detecting the ordered memory index and calculating the aggregate function and updating the matching mark, and 3) determining the final result of the aggregate function based on the ordered memory index to obtain a query result set. The following describes these three operation sequences respectively. For the process of executing these three operation sequences, please refer to the following description, so they will not be repeated one by one.

[0096] 1) Build an ordered memory index

[0097] When constructing an ordered memory index, a table on one side of a join operation in an SQL query statement can be used as a 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 match tag. The search key value is a connection 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 match 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 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 an 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 an aggregate function value related to the detection side table.

[0098] As a possible implementation, Figure 3As shown, constructing an ordered memory index may include the following steps: S301, scanning the search key value from the construction side table. In order to ensure that all index data can be stored in the memory, a side table that occupies less memory can be selected as the construction side table. S302, constructing a data record for the scanned search key value. A data record is related to a search key value. In the data record, the search key value, the initial value of the aggregate function value, the number of key values, and the initial value of the matching mark may be included. S303, inserting the constructed data record as a leaf node into the ordered memory index to construct an ordered memory index. In the leaf node of the ordered memory index, only one data record is saved for the data record with repeated search key values, but the number of key values ​​in these data records needs to be accumulated. In addition, if the input parameter of the aggregate function in the query statement comes from the construction side table, it is necessary to calculate the aggregate function while inserting the data record in the ordered memory index, and update the aggregate function value related to the construction side table. If the input parameter of the aggregate function comes from the detection side table, it is not necessary to calculate the aggregate function at this time, and only the initial value is retained. In addition, the data records can be sorted in the ordered memory index. Before inserting the data records into the ordered memory index, the data records may be sorted according to the search key values ​​in the data records; then, based on the sorting results, the data records may be sequentially inserted into the ordered memory index. In addition, the data records may be inserted into the ordered memory index first, and then the data records may be sorted in the ordered memory index according to the search key values ​​in the data records. This allows the construction side table to be sorted.

[0099] For example, see Figure 4 , Figure 4 (A) shows the SQL query statement. Figure 4 (B) shows Table 1 (i.e. Figure 4 t1) described in (A) and Table 2 (i.e. Figure 4 t2 described in (A)). Figure 4 Table 1 shown in (B) is an ordered memory index for constructing the side table, and the following can be obtained: Figure 4The ordered memory index shown in (C). Among them, the search key used in the process of building 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 number of key values ​​(count column) is 2, and the records with the connection key values ​​t1.x of 2 and 3 appear once each, so the number of key values ​​is 1. The input parameter of the aggregate function SUM(t1.y) comes from the construction side table (that is, Table 1), so the result can be directly calculated. The input parameter of SUM(t2.c) comes from the detection side table (that is, Table 2). Since the record value of Table 2 has not been obtained at this time, the aggregate 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 match. Since it has not yet matched Table 2, it is initialized to 0 at this time.

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

[0101] When detecting the ordered memory index and calculating the aggregate function and updating the matching mark, the table on the other side of the connection operation can be used as the detection side table, and the connection key value in each record in the detection side table can be used to detect the ordered memory index, and 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 mark is updated. When the connection key value in a certain record matches the search key value in the ordered memory index, the aggregate function in the detection side table can be calculated based on the key value number of the search key value, and the aggregate function value related to the search key value and the connection key value can be updated. In addition, the matching mark can also be updated (that is, marked as matched). When the connection key value in a certain record does not match the search key value in the ordered memory index, the next record is obtained for detection. In this way, through an ordered memory index, the connection matching and group aggregation operations are simultaneously realized.

[0102] For example, see Figure 4 , in constructing Figure 4 After the ordered memory index shown in (C) is obtained, the ordered memory index can be detected using Table 2 as the detection side table, and the aggregate function can be calculated and the matching mark can be updated. 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 remains unchanged at this time, so Figure 4 (D) shows the ordered memory index and Figure 4 The ordered memory index is the same as shown in (C).

[0103] Then, the second record (5,2,4) 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 and the match flag can be updated, that is, the match flag is updated from 0 to 1. At the same time, the aggregate function SUM(t2.c) can be calculated. The number of key values ​​in the ordered memory index needs to be used in the calculation, and the aggregate function value related to the search key value 5 in the ordered memory index needs to be updated. Among them, the aggregate function value SUM(t2.c) = 0 + 4*2 = 8. In this way, we can get the following Figure 4 The ordered memory index shown in (E).

[0104] Next, use the third record (2,6,3) in Table 2 to detect in the ordered memory index. The connection key value 2 in the 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. Among them, the aggregate function value SUM(t2.c) = 0 + 3*1 = 3. In this way, we can get the following: Figure 4 The ordered memory index shown in (F).

[0105] 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 indicates a match at this time, it does not need to be updated. At the same time, the aggregate function SUM(t2.c) can be calculated. The calculation requires the use of the number of key values ​​in the ordered memory index and the value of the aggregate function calculated previously, as well as the update of 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, we can get Figure 4 The ordered memory index shown in (G).

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

[0107] After completing the detection of the ordered memory index, the execution result of the created fusion operator has been saved in the ordered memory index. However, for some aggregate functions, a calculation may need to be performed to determine the final result. For example, when the aggregate function is an AVG function, a division calculation needs to be performed again to obtain the mean result. Finally, the query result set can be obtained by filtering from the ordered memory index according to the connection type in the SQL query statement. Among them, 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, so in some queries with sorting operations (such as: ORDER BY, etc.), the sorting operation can be omitted, reducing the overhead of the sorting operation and improving the query efficiency. In some embodiments, in addition to the join operation and the grouping aggregation operation, the query statement can also include: sorting operation (such as: ORDER BY, etc.). At this time, the fusion operator is also used to complete the sorting operation. Among them, the sorting operation is automatically completed during the construction of the ordered content index.

[0108] For example, see Figure 4 ,exist Figure 4 The SQL query statement shown in (A) and Figure 4 Under the ordered memory index shown in (G), the query result set can be Figure 4 In addition, please refer to Figure 5 ,exist Figure 5 The SQL query statement shown in (A) and Figure 5 Under the ordered memory index shown in (G), the query result set can be Figure 5 As shown in (H). Among them, Figure 5 and Figure 4 The main difference is that the SQL query statement and query result set are different. Figure 5 The process shown in can be found in Figure 4 The description in , will not be repeated here. Figure 5 and Figure 4 It can be seen 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.

[0109] Through the above description of each operation sequence in the optimal execution plan related to the fusion operator, it can be seen that these operation sequences all use the same physical data structure (i.e., ordered memory index), so the time and space locality during the execution of the fusion operator is better, resulting in higher execution efficiency. In addition, during the execution of the fusion operator, only the construction side table needs to be sorted, and there is no need to sort the detection side table, which can avoid a high-cost sorting operation, thereby further improving the query execution performance.

[0110] In addition, to further improve query execution performance, after building an ordered memory index and before probing 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 build side table. This range condition can be used to indicate the range of the join key value in the probing side table. For example, see Figure 6 ,exist Figure 6 The SQL query statement shown in (A) and Figure 6 Table 1 (i.e. Figure 6 t1 in (A) and Table 2 (i.e. Figure 6 In (A) under t2), using Table 1 as the construction side table, we can construct the following Figure 6 The ordered memory index shown in (C). Figure 6 From the ordered memory index shown in (C), we can know that the minimum value of the connection key value t1.x in Table 1 is 0 and the maximum value is 5, that is, t1.x>=0AND t1.x<=5. Figure 6 As shown in (D), after obtaining the range of connection key values ​​in Table 1, combined with Figure 6 The connection condition t1.x>t2.a shown in (A) can obtain the range condition of the connection key value in Table 2: t2.a<5.

[0111] Furthermore, when detecting an ordered memory index, when using any record in the detection side table for detection, you can 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, data can be filtered in advance before detecting the ordered memory index, thereby avoiding unnecessary subsequent calculations and improving the execution performance of the query. For example, continue to refer to Figure 6 , Figure 6 (B) shows that the connection key values ​​of Table 2 are 10 and 20, both of which are greater than 5, that is, they do not satisfy Figure 6 (D) shows the range condition, so execution can end early without probing the ordered memory index.

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

[0113] It is understandable that the order of execution of the steps in the above embodiments does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present application. In addition, the various embodiments described above can be combined according to actual conditions, and the combined solutions are still within the scope of protection of the present application.

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

[0115] For example, Figure 7 FIG. 1 is a schematic diagram showing the structure of a database query optimization device provided in an embodiment of the present application. Figure 7 As shown, 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 connection operation and a grouping aggregation operation. The processing module 702 is used to determine that the connection key and the grouping 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 connection condition in the query statement. The processing module 702 is also used to create a fusion operator, which is used to complete the connection operation and the grouping aggregation operation; and, when the cost of executing the fusion operator is less than or equal to the target cost, according to the fusion operator, an optimal execution plan related to the fusion operator is generated, 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. 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.

[0116] 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 in 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 a connection 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 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 build 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 in the ordered memory index, and update the matching tag; based on the connection type in the query statement, filter out the query result set from the ordered memory index.

[0117] In some embodiments, when the processing module 702 constructs an ordered memory index with the table on the first side in the connection operation as the construction side table, 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.

[0118] 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 values ​​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 values ​​in the data records.

[0119] 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.

[0120] 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.

[0121] In some embodiments, 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, etc.).

[0122] In some embodiments, the query statement also includes a sorting operation. In this case, the fusion operator is also used to complete the sorting operation.

[0123] In some embodiments, the connection condition is used for equal-value connection, or for unequal-value connection, or for both equal-value connection and unequal-value connection.

[0124] In some embodiments, Figure 7 The acquisition module 701 and the processing module 702 shown in the figure can be implemented by software or by hardware. For example, the implementation of the acquisition module 701 is described below by taking 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.

[0125] As an example of a software functional unit, the acquisition module 701 may include code running on a computing instance. Among them, the computing instance may include at least one of a physical host (computing device), a virtual machine, and a container. Further, the above-mentioned 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 (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 a data center or multiple data centers with close geographical locations. Among them, usually a region may include multiple AZs.

[0126] Similarly, multiple hosts / virtual machines / containers used to run the code can be distributed in the same virtual private cloud (VPC) or in multiple VPCs. Usually, a VPC is set up in a region. For cross-region communication between two VPCs in the same region and between VPCs in different regions, a communication gateway needs to be set up in each VPC to achieve interconnection between VPCs through the communication gateway.

[0127] As an example of a hardware functional unit, the acquisition module 701 may include at least one computing device, such as a server, etc. Alternatively, the acquisition module 701 may also be a device 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.

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

[0129] 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, and the acquisition module 701 and the processing module 702 respectively implement different steps in the database query optimization method described in the above embodiment to achieve Figure 7 The entire functions of the database query optimization device 700 are shown.

[0130] The present application also provides a computing device 800. Figure 8 As shown, the computing device 800 includes: a bus 802, a processor 804, a memory 806, and a communication interface 808. The processor 804, the memory 806, and the communication interface 808 communicate through the bus 802. The computing device 800 can be a server or a terminal device. It should be understood that the present application does not limit the number of processors and memories in the computing device 800.

[0131] The bus 802 may be a peripheral component interconnect (PCI) bus or an extended industry standard architecture (EISA) bus. The bus may be divided into an address bus, a data bus, a control bus, etc. For ease of representation, Figure 8 The bus 804 is represented by only one line, but does not mean that there is only one bus or one type of bus. The bus 804 may include a path for transmitting information between various components of the computing device 800 (eg, the memory 806, the processor 804, and the communication interface 808).

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

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

[0134] The memory 806 stores executable program codes, and the processor 804 executes the executable program codes to respectively implement the aforementioned Figure 7 The functions of the acquisition module 701 and the processing module 702 shown in the above embodiment are implemented to realize the database query optimization method described in the above embodiment. That is, the memory 806 stores instructions for executing the database query optimization method described in the above embodiment.

[0135] Alternatively, the memory 806 stores executable codes, and the processor 804 executes the executable codes to respectively implement the aforementioned Figure 7 The functions of the database query optimization device 700 shown in the figure are implemented to realize the database query optimization method described in the above embodiment. That is, the memory 806 stores instructions for executing the database query optimization method described in the above embodiment.

[0136] 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.

[0137] The embodiment of the present application also provides 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 smart phone.

[0138] like Fig. 9As shown, 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.

[0139] 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.

[0140] It should be noted that the memory 806 in different computing devices 800 in the computing device cluster may store different instructions, which are respectively used to execute the aforementioned Figure 7 The illustrated database query optimization apparatus 700 shows partial functions. That is, 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.

[0141] In some possible implementations, one or more computing devices in the computing device cluster may be connected via a network, which may be a wide area network or a local area network. Fig.10 A possible implementation is shown. Fig.10 As shown, 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. At the same time, the memory 806 in the computing device 800B stores instructions for executing the functions of the processing module 702.

[0142] It should be understood that Fig.10 The functions of the computing device 800A shown in FIG. 8 may also be completed by multiple computing devices 800. Similarly, the functions of the computing device 800B may also be completed by multiple computing devices 800.

[0143] The present application embodiment also provides another computing device cluster. The connection relationship between the computing devices in the computing device cluster can be similar to that of Fig. 9 and Fig.10 The connection mode of the computing device cluster is different in that the memory 806 in one or more computing devices 800 in the computing device cluster may store the same instructions for executing the method in the above embodiment.

[0144] 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 may jointly execute instructions for executing the aforementioned database query optimization method.

[0145] Based on the method in the above embodiment, the embodiment of the present application provides a computer-readable storage medium, which stores a computer program. When the computer program runs 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 the 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 (e.g., a floppy disk, a hard disk, a magnetic tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state hard disk), etc.

[0146] Based on the method in the above embodiment, an embodiment of the present application provides a computer program product including 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.

[0147] It is understood that the processor in the embodiments of the present application may be a central processing unit (CPU), or 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.

[0148] 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, and the software modules 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.

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

[0150] It should be understood that the various numerical numbers involved in the embodiments of the present application are only used for the convenience of description and are not used to limit the scope of the embodiments of the present application.

[0151] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit it. Although the present application has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate 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