Database query optimization method and device and computing device cluster

Abstract

The database query optimization method comprises the following steps: obtaining a query statement containing a connection operation and a deduplication operation; determining that a column used by the connection operation contains a column used by a target operation in the query statement, wherein the target operation is an operation needing to be executed after the connection operation; creating a fusion operator, wherein the fusion operator is used for completing a connection operation and a deduplication operation; when the cost for 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, the target cost is the cost for executing a connection operator and a deduplication operator, the connection operator is used for completing connection operation, and the deduplication operator is used for completing deduplication operation; 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 deduplication 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, including: obtaining a query statement, the query statement including a join operation and a deduplication operation; determining that the columns used for the join operation include the columns used for the target operation in the query statement, the target operation being the operation to be performed after the join operation; creating a fusion operator, the fusion operator being used to complete the join operation and the deduplication operation; when the cost of executing the fusion operator is less than or equal to the target cost, generating an optimal execution plan related to the fusion operator based on the fusion operator, the target cost being the cost of executing the join operator and the deduplication operator, the join operator being used to complete the join operation, and the deduplication operator being used to complete the deduplication operation; and 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 deduplication operations can be fused into one operator, thereby reducing the processing flow of intermediate results and accelerating the execution of query statements. 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, and performing a deduplication operation during the construction process, wherein the ordered memory index includes: a search key value and a matching tag, wherein the search key value is a connection key value 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, and the detection side table is the table on the second side in the connection operation; using the connection key value in the detection side table to detect the ordered memory index, and, based on the detection result, updating the matching identifier in the ordered memory index; based on the connection type in the query statement, filtering out the query result set from the ordered memory index. In this way, the connection matching and deduplication operations can be realized at the same time 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, thereby reducing the number of sorting times. In addition, these operation sequences all use the same physical data structure (i.e., the ordered memory index), so the time and space locality during the execution of the fusion operator is better, thereby having a higher execution efficiency.

[0007] In a possible implementation, the table on the first side in the connection operation is used as the construction side table, an ordered memory index is constructed, and a deduplication operation is performed during the construction process, including: constructing a data record for each connection key value in the construction side table, the data record including: the search key value 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. In this way, an ordered memory index can be constructed and the deduplication operation can be completed.

[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 matching identifier in the ordered memory index, 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; 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, connection matching and deduplication operations are simultaneously implemented through an ordered memory index, thereby improving 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.

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

[0013] In a possible implementation, the join condition used in the join operation is used for equal-value join and unequal-value join.

[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, which includes a join operation and a deduplication operation. The processing module is used to determine that the columns used for the join operation include the columns used for the target operation in the query statement, and the target operation is the operation to be performed after the join operation. The processing module is also used to create a fusion operator, and the fusion operator is used to complete the join operation and the deduplication operation. The processing module is also 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 join operator and the deduplication operator. The join operator is used to complete the join operation, and the deduplication operator is used to complete the deduplication operation. The processing module is also used to execute operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set.

[0015] In one possible implementation, when the processing module executes operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set, it is specifically used to: use the table on the first side of the connection operation as the construction side table, build an ordered memory index, and perform a deduplication operation during the construction process, the ordered memory index includes: a search key value and a matching tag, the search key value is the connection key value 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, and the detection side table is the table on the second side in the connection operation; use the connection key value in the detection side table to detect the ordered memory index, and based on the detection result, 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 a possible implementation, when the processing module uses the table on the first side in the connection operation as the construction side table, constructs an ordered memory index, and performs a deduplication operation during the construction process, 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 initial value of the search key value and 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.

[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 one 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, the first record is any record in the detection side table; based on the detection result, update the matching identifier in the ordered memory index, 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; when the connection key value in the first record does not match the first search key value in the ordered memory index, use the connection key value in any remaining record in the detection side table 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.

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

[0022] In a possible implementation, the join condition used in the join operation is used for equal-value join and unequal-value join.

[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 by an embodiment of the present application;

[0029] Figure 3 This 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) Deduplication

[0051] Deduplication refers to removing duplicate records or rows from query results to ensure that the data in the query result set is unique. Deduplication can include explicit deduplication and implicit deduplication. Explicit deduplication refers to using clear SQL query statements to explicitly remove duplicate records from the query result set. Such SQL query statements often have explicit deduplication semantics, which are usually implemented using the DISTINCT keyword in SQL query statements. Implicit deduplication refers to the automatic removal of duplicate records by the DBMS when performing specific types of operations. SQL query statements containing these specific types of operations usually contain implicit deduplication semantics, such as: GROUP BY clauses without aggregate functions, set operations with deduplication semantics (such as UNION, INTERSECT, MINUS), etc. There are also some SQL query statements whose results are not affected by duplicate records, such as aggregate functions MIN or MAX. Such query statements can also be regarded as satisfying implicit deduplication semantics.

[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) Ordered Memory Index

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

[0058] (9) Search key

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

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

[0061] Generally, when a query statement involves a join operation and a deduplication 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 the join operation, and a combined operator, the Sort Dist inct operator, is used to process the deduplication operation. However, in some query statements that include join operations and deduplication operations, when the join and deduplication have certain characteristics, using a single operator to implement them separately will appear redundant and waste performance.

[0062] 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 operations and deduplication operations, avoid redundant calculations, reduce memory overhead, and improve the execution efficiency of query statements.

[0063] 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 1As 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 active data objects (ADO) connection used in .Net, Java database connection (JDBC) connection used in Java, etc.

[0064] 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, they 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.

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

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

[0067] For example, Figure 2The 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:

[0068] S201. Obtain an SQL query statement, which includes a connection operation and a deduplication operation.

[0069] In this embodiment, the SQL query statement can be input by the user or predefined by the application or system. The specific situation can be determined according to the actual situation and is not limited here. Among them, when the query statement contains a connection condition, it can be considered that the query statement contains a connection operation. When the query statement contains explicit or implicit deduplication semantics, it can be considered that the query statement contains a deduplication operation. Exemplarily, the situation in which the query statement contains a deduplication operation may include one or more of the following: 1) The query statement explicitly specifies the DISTINCT keyword; 2) The aggregate function is MIN or MAX, and the query statement may or may not include the GROUP BY clause; 3) The input parameter of the aggregate function specifies the DISTINCT keyword, such as SUM (DISTINCTt1.x), and the query statement may or may not include the GROUP BY clause; 4) The query statement specifies the GROUP BY clause, but does not specify the aggregate function; 5) The query statement uses a set operation with deduplication semantics, including: UNION, INTERSECT or MINUS, etc.; 6) Semi-join (SemiJoin) or anti-join (AntiJoin) generated by subquery de-nesting.

[0070] S202: Determine whether the columns used by the join operation include the columns used by the target operation in the query statement, where the target operation is an operation to be performed after the join operation.

[0071] In this embodiment, after obtaining the SQL query statement, it can be determined whether the columns used in the operation to be performed after the connection operation in the query statement are in the columns specified by the connection key, that is, it can be determined whether the columns used in the connection operation are included in the columns used in the operation to be performed after the connection operation. When they are in the columns specified by the connection key, S203 can be executed; otherwise, S205 is executed. For example, assuming that the connection condition of the query statement is t1.x=t2.aANDt1.y=t2.b, it is required that after the connection operation, the columns using t1 should be t1.x, t1.y (including t1.x and t1.y at the same time or only t1.x or only t1.y), and the columns using t2 are similar. At this time, when a deduplication operation is performed after the connection operation, if the deduplication semantics is: SELECT DISTINCT t1.x, t1.y, then the condition is met; if the deduplication semantics is: SELECT DISTINCT t1.x, t1.z, then the condition is not met.

[0072] S203: Create a fusion operator, which is used to complete the connection operation and the deduplication operation.

[0073] In this embodiment, when the obtained SQL query statement satisfies the conditions in S202 at the same time, a fusion operator can be created. The fusion operator can be used to complete the connection and deduplication operations. In this way, the connection operation and the deduplication operation can be completed by one operator, without using one operator to complete the connection operation and another operator to complete the deduplication 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.

[0074] S204: Determine whether the cost of executing the fusion operator is greater than the cost of executing the deduplication operator and the connection operator.

[0075] 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 deduplication operator and the connection operator. If the cost of executing the fusion operator is greater than the cost of executing the deduplication operator and the connection operator, it indicates that the use of the fusion operator for connection and deduplication operations cannot bring performance improvement, so S205 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 deduplication operator and the connection operator, it indicates that the use of the fusion operator for connection and deduplication operations can bring performance improvement, so S207 can be executed.

[0076] S205 . Generate an optimal execution plan related to the deduplication operator and the connection operator according to the deduplication operator and the connection operator.

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

[0078] S206 . Execute operations step by step according to the operation sequence in the optimal execution plan related to the deduplication operator and the join operator to obtain a query result set.

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

[0080] S207. Generate an optimal execution plan related to the fusion operator according to the fusion operator.

[0081] In this embodiment, when the cost of executing the fusion operator is less than or equal to the cost of executing the deduplication operator and the join 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.

[0082] S208. Execute 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.

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

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

[0085] In some embodiments, the operation sequence in the optimal execution plan related to the fusion operator described in S207 may include: 1) constructing an ordered memory index and removing duplicates, 2) detecting the ordered memory index and updating the matching identifier, and 3) obtaining a query result set based on the ordered memory index. The following describes these three operation sequences respectively. For the process of executing these three operation sequences, please refer to the following description, which will not be repeated one by one.

[0086] 1) Build an ordered memory index and remove duplicates

[0087] When constructing an ordered memory index, the table on one side of the join operation in the SQL query statement can be used as the construction side table, and the ordered memory index can be constructed through the construction side table, and deduplication can be performed during the construction process. 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 can include: a search key value and a match tag. The search key value is the connection key value in the construction 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.

[0088] As a possible implementation, Figure 3 As shown, constructing an ordered memory index may include the following steps: S301, scanning the search key value from the construction side table. Among them, 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. Among them, the data record may include: the initial value of the search key value and the matching tag. S303, judging whether the data record exists in the ordered memory index. When there is such a data record, if the data record is inserted into the ordered content index, it will cause duplicate data records in the ordered memory index. Therefore, in order to avoid this situation, the data record can be discarded and return to execute S301; otherwise, S304 can be executed. S304, insert the constructed data record as a leaf node into the ordered memory index to construct an ordered memory index. 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 can be sorted according to the search key values ​​in the data records; then, based on the sorting results, the data records can be inserted into the ordered memory index in sequence. In addition, the data records can be inserted into the ordered memory index first, and then the data records can be sorted in the ordered memory index according to the search key values ​​in the data records. In this way, the construction side table can be sorted, thereby realizing the two operations of deduplication and sorting.

[0089] 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). The search key used in the process of constructing the ordered memory index is t1.x, and the ordered memory index is sorted according to the search key value. In the process of constructing the ordered memory index, the search key value 2 is first scanned from Table 1. At this time, a data record can be constructed for the search key value 2. The data record can be [2,0], where 2 represents the search key value and 0 represents no match. The search key value 2 is not currently included in the ordered memory index, so the data record can be inserted into the ordered content index as a leaf node. Next, the search key value 1 is scanned from Table 1. At this time, a data record can be constructed for the search key value 1. The data record can be [1,0], where 1 represents the search key value and 0 represents no match. The search key value 1 is not currently included in the ordered memory index, so the data record can be inserted into the ordered content index as a leaf node. Then, the search key value 3 is scanned from Table 1. At this time, a data record can be constructed for the search key value 3. The data record can be [3,0], where 3 represents the search key value and 0 represents no match. Currently, the ordered memory index does not contain the search key value 3, so the data record can be inserted into the ordered content index as a leaf node. Finally, the search key value 2 is scanned from Table 1. At this time, a data record can be constructed for the search key value 2. The data record can be [2,0], where 2 represents the search key value and 0 represents no match. Currently, the search key value 2 already exists in the ordered memory index, so this data record can be discarded. In this way, the following is constructed: Figure 4 The ordered memory index shown in (C) is shown in FIG. During the construction process, the search key values ​​may be sorted.

[0090] 2) Detect ordered memory indexes and update matching flags

[0091] When probing the ordered memory index and updating the matching mark, the table on the other side of the join operation can be used as the probing side table, and the ordered memory index can be probed using the connection key value in each record of the probing side table, and the matching mark can be updated in the ordered memory index based on the probing result. When the connection key value in a certain record matches the search key value in the ordered memory index, the matching mark can be updated (i.e., 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 probing.

[0092] 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 matching mark can be updated. The first record (4,3,2) in Table 2 can be used to detect in the ordered memory index. Since the connection key value 4 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).

[0093] Then, the second record (2,5,4) in Table 2 is used 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. In this way, the following can be obtained: Figure 4 The ordered memory index shown in (E).

[0094] Next, we use the third record (3,7,6) in Table 2 to detect in the ordered memory index. The connection key value 3 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. In this way, we can get Figure 4 The ordered memory index shown in (F).

[0095] Finally, the fourth record (2,9,8) in Table 2 is used 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. Since the matching identifier corresponding to the connection key value 2 indicates a match at this time, there is no need to update it. In this way, we can get Figure 4 The ordered memory index shown in (G).

[0096] 3) Based on the ordered memory index, obtain the query result set.

[0097] 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. Finally, according to the connection type in the SQL query statement and the matching identifier in the ordered memory index, the query result set can be obtained by filtering from the ordered memory index. 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 connection operation and the deduplication 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 in the process of building the ordered content index.

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

[0099] It should be understood that Figure 4 and Figure 5 It is described by the query statement containing explicit deduplication semantics. When the query statement contains implicit deduplication semantics, the above method can also be used. For example, when the implicit deduplication semantics in the query statement is the aggregate function MAX, since the leaf nodes of the ordered memory index are ordered, the maximum marked result is directly output based on the matching mark, which is the calculation result of the aggregate function MAX. According to the implementation process of the fusion operator, the data has been deduplicated before the connection matching, thereby reducing the amount of data for the connection and subsequent operations, thereby improving the execution efficiency of the query.

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

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

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

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

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

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

[0106] 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 7As 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, which includes a connection operation and a deduplication operation. The processing module 702 is used to determine that the columns used by the connection operation include the columns used by the target operation in the query statement, and the target operation is the operation to be performed after the connection operation. The processing module 702 is also used to create a fusion operator, and the fusion operator is used to complete the connection operation and the deduplication operation. The processing module 702 is also 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 deduplication operator. The connection operator is used to complete the connection operation, and the deduplication operator is used to complete the deduplication operation. The processing module 702 is also used to execute operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set.

[0107] In some embodiments, when the processing module 702 executes operations step by step according to the operation sequence in the optimal execution plan to obtain a query result set, it is specifically used to: use the table on the first side of the connection operation as the construction side table, build an ordered memory index, and perform deduplication operations during the construction process, the ordered memory index includes: a search key value and a matching tag, the search key value is the connection key value 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, and the detection side table is the table on the second side in the connection operation; use the connection key value in the detection side table to detect the ordered memory index, and based on the detection result, 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.

[0108] In some embodiments, when the processing module 702 uses the table on the first side of the connection operation as the construction side table, constructs an ordered memory index, and performs a deduplication operation during the construction process, 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 initial value of the search key value and 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.

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

[0110] 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, the first record is any record in the detection side table; based on the detection result, update the matching identifier in the ordered memory index, 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; when the connection key value in the first record does not match the first search key value in the ordered memory index, use the connection key value in any remaining record in the detection side table to match the search key value in the ordered memory index.

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

[0112] In some embodiments, both the build-side table and the probe-side table may be base tables or derived tables.

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

[0114] In some embodiments, the join condition used by the join operation is used for equal-value joins and non-equal-value joins.

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

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

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

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

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

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

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

[0122] 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).

[0123] 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).

[0124] 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).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

[0139] 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 a processor so that the processor can read information from the storage medium and write information to the storage medium. Of course, the storage medium can also be a component of the processor. The processor and the storage medium can be located in an ASIC.

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

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

[0142] 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 deduplication operation; Determine that the columns used by the join operation include the columns used by the target operation in the query statement, where the target operation is an operation to be performed after the join operation; Creating a fusion operator, where the fusion operator is used to complete the connection operation and the deduplication 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 deduplication operator, the join operator is used to complete the join operation, and the deduplication operator is used to complete the deduplication 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 join operation as the construction side table, construct an ordered memory index, and perform a deduplication operation during the construction process, the ordered memory index includes: a search key value and a match tag, the search key value is the connection key value in the construction side table, and the match tag is used to identify whether the search key value successfully matches the connection key value in the detection side table, and the detection side table is the table on the second side in the join operation; Use the connection key value in the detection side table to detect the ordered memory index, and based on the detection result, update the matching identifier in the ordered memory index; 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 of the join operation as a construction side table, constructing an ordered memory index, and performing a deduplication operation during the construction process 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 and the initial value of the matching tag; Inserting the constructed data record into the ordered memory index to construct the ordered memory index; 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.

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 matching identifier in the ordered memory index based on the detection result includes: In a case where the connection key value in the first record matches the first search key value in the ordered memory index, updating a matching identifier associated with the first search key value in the ordered memory index; 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 used in the connection operation 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 deduplication operation; A processing module, configured to determine that the columns used by the join operation include the columns used by the target operation in the query statement, where the target operation is an operation to be performed after the join operation; The processing module is further used to create a fusion operator, and the fusion operator is used to complete the connection operation and the deduplication 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 deduplication operator, the connection operator is used to complete the connection operation, and the deduplication operator is used to complete the deduplication 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 join operation as the construction side table, construct an ordered memory index, and perform a deduplication operation during the construction process, the ordered memory index includes: a search key value and a match tag, the search key value is the connection key value in the construction side table, and the match tag is used to identify whether the search key value successfully matches the connection key value in the detection side table, and the detection side table is the table on the second side in the join operation; Use the connection key value in the detection side table to detect the ordered memory index, and based on the detection result, update the matching identifier in the ordered memory index; 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, constructs an ordered memory index, and performs a deduplication operation during the construction process, the processing module 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 and the initial value of the matching tag; Inserting the constructed data record into the ordered memory index to construct the ordered memory index; 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.

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 matching identifier in the ordered memory index based on the detection result includes: In a case where the connection key value in the first record matches the first search key value in the ordered memory index, updating a matching identifier associated with the first search key value in the ordered memory index; 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 configured 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 used in the connection operation 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