Optimization method and device for database query

Abstract

The invention provides an optimization method and device for database query, and the method comprises the steps: obtaining a query statement, wherein the query statement indicates connection query and connection conditions for a plurality of data tables, and a target operation item for a target data table in the plurality of data tables; generating a connection sequence of the plurality of data tables; determining an optimization item for the target operation item, wherein the optimization item indicates a cutting level of a scanning path corresponding to the target operation item; in the execution process of the target operation item, when data meeting the connection condition is queried in the current query iteration round, the optimization item is executed, so that the scanning path is cut based on the cutting level, the scanning path for the inner-layer data table is cut, and the inner-layer data table is the data table with the connection sequence behind the target data table in the multiple data tables. According to the method, the scanning paths are cut and optimized, so that the scanning turns of the connection table are effectively reduced, and the query performance is improved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to an optimization method and device for database query. Background Art

[0002] With the development of Internet technology, many applications are accompanied by the generation of a large amount of data, which is stored in the database. At the same time, specific query languages, such as structured query language (SQL), can be used to query and obtain the required data from the database. In database queries, multi-table joins are a common operation used to perform associated queries on data in multiple data tables. In the multi-table join scenario, since it is necessary to perform associated queries on data in multiple data tables, the outer data table needs to be scanned once and the inner data table needs to be scanned multiple times during the query process. However, for certain specific query operations (such as distinct / group by operations), in some cases, there will be some invalid scan paths, which not only affects the query performance, but also increases the resource overhead of the computing device. Summary of the invention

[0003] The embodiments of the present application provide a method and device for optimizing database queries, which effectively reduce the number of scan rounds of connection tables and improve query performance by optimizing the scan path.

[0004] In a first aspect, the present application provides an optimization method for database queries, including obtaining a query statement indicating a join query for multiple data tables, a join condition, and a target operation item for a target data table in the multiple data tables; generating a join order for the multiple data tables; determining an optimization item for the target operation item, the optimization item including a clipping hierarchy of a scan path corresponding to the target operation item; during the execution of the target operation item, when the current query iteration round queries data that meets the join condition, executing the optimization item to clip the scan path based on the clipping hierarchy, so that the scan path for the inner data table is clipped, and the inner data table is a data table in the multiple data tables whose join order is after the target data table.

[0005] The optimization method for database query provided by the present application, during the query plan execution process, when the current query iteration round queries data that meets the connection conditions, executes the optimization item to optimize the scan path clipping, so as to clip the scan path of the inner data table, effectively reduce the scan rounds of the connection table, and improve the query performance.

[0006] The inner data table means the data table whose connection order is after the target data table, for example, data table t1, data table t2 and data table t3. The connection order is data table t1 connected to data table t2, data table t2 connected to data table t3. If the target data table is data table t1, data table t2 and data table t3 are the inner data tables of data table t1.

[0007] In a possible implementation, a specific implementation of determining optimization items for target operation items is: based on the target data table and the connection order, the optimization items are determined, and the clipping level of the optimization items is the scanning path level of the outermost inner data table corresponding to the target data table.

[0008] For example, the multiple data tables are data table t1, data table t2, and data table t3, data table t1 is the target data table, and the connection order of the multiple data tables is data table t1 connected to data table t2, and data table t2 connected to data table t3. At this time, data table t3 is the inner data table of data table t2, and data table t2 is the inner data table of data table t1. During the query process, when data table t1 scans a row of data, it is necessary to perform a full table scan on data table t2. When data table t2 scans a row of data, it is necessary to perform a full table scan on data table t3. The scanning path for data table t1 can be called the first scanning path level, the scanning path for data table t2 can be called the second scanning path level, and the scanning path for data table t3 can be called the third scanning path level. After knowing that the target data table is data table t1, and the connection order of multiple data tables is data table t1 connected to data table t2, and data table t2 connected to data table t3, the optimization item corresponding to the target operation item can be determined as the second scanning path level of the clipping level, that is, the scanning path level of the outermost inner data table corresponding to the target data table. The clipping level determination logic of the optimization item is simple, saving calculation overhead.

[0009] In another possible implementation, the target operation item includes a first target operation item and a second target operation item, such as a distinct operation and a group by operation; the optimization method for database query provided in the present application includes saving optimization items for distinct and optimization items for group by operations to obtain an optimization set; that is, the optimization set includes optimization items for distinct and optimization items for group by operations; a target optimization item is determined from the optimization set, and the target optimization item is the innermost optimization item of a clipping hierarchy in the optimization set; during the execution of the target operation item, when the current query iteration round queries data that meets the connection condition, the optimization item is executed, and a specific implementation of clipping the scan path based on the clipping hierarchy is: during the execution of the distinct operation or the group by operation, when the current query iteration round queries data that meets the connection condition, the target optimization item is executed, and the scan path is clipped based on the clipping hierarchy of the target optimization item.

[0010] This application selects the optimization items of the innermost clipping level from the optimization set as the target optimization items, and uses the clipping level of the target optimization items to clip the scan path as a whole, thereby achieving joint clipping optimization of the distinct operation scan path and the group by operation scan path.

[0011] In another possible implementation, the optimization method for database query provided by the present application further includes, before determining the optimization item step for the target operation item: determining the scanning path of the target operation item during query execution, and whether there is room for cutting optimization. That is, before calculating the optimization item of the target operation item, first determine whether there is room for cutting optimization for the target operation item. If there is no room for cutting optimization, there is no need to calculate the optimization item of the target operation item, and the query plan can be executed according to normal conditions, saving computing overhead. If there is room for cutting optimization, then calculate the optimization item of the target operation item, and save the optimization item to the optimization set of this query.

[0012] Optionally, whether the target data table is at the end of the connection sequence can be used to determine whether there is room for optimization in the scan path of the target operation item during query execution. If the target data table is at the end of the connection sequence, it is determined that there is no room for optimization in the scan path of the target operation item during query execution, and there is no need to calculate optimization items for it. The target operation item can be scanned and searched according to the normal scan path during query execution. Otherwise, it is determined that there is room for optimization in the scan path of the target operation item during query execution, and it is necessary to calculate optimization items for it and store the optimization items in the optimization set.

[0013] In another possible implementation, the optimization method for database query provided by the present application further includes, before the step of obtaining the query statement, determining that the query statement includes a target field, where the target field indicates a target operation item. That is, the optimization method for database query provided by the present application only performs query optimization on query statements for specific operations. For example, query statements including "distinct" or "group by" fields.

[0014] In a second aspect, the present application provides an optimization device for database query, including an acquisition module, a generation module, a first determination module and a clipping optimization module, wherein the acquisition module is used to acquire a query statement, the query statement indicates a connection query for multiple data tables, a connection condition and a target operation item for a target data table in multiple data tables; the generation module is used to generate a connection order for multiple data tables; the first determination module is used to determine an optimization item for the target operation item, the optimization item including a clipping hierarchy of a scan path corresponding to the target operation item; the clipping optimization module is used to execute the optimization item during the execution of the target operation item when the current query iteration round queries data that meets the connection condition, so as to clip the scan path based on the clipping hierarchy, so that the scan path for the inner data table is clipped, and the inner data table is a data table whose connection order is after the target data table in the multiple data tables.

[0015] In a possible implementation, the first determination module is specifically used to: determine optimization items based on the target data table and the connection sequence, and the clipping level of the optimization items is the scan path level where the outermost inner data table corresponding to the target data table is located.

[0016] In another possible implementation, the target operation item includes a first target operation item and a second target operation item; the optimization device for database query provided by the present application also includes a saving module, which is used to save the first optimization item for the first target operation item and the second optimization item for the second target operation item to obtain an optimization set; the first determination module is also used to determine the target optimization item from the optimization set, and the target optimization item is the innermost optimization item of the clipping hierarchy in the optimization set; the clipping optimization module is specifically used to execute the target optimization item during the execution of the first target operation item or the second target operation item, when the current query iteration round queries the data that meets the connection condition, and clip the scan path based on the clipping hierarchy of the target optimization item.

[0017] In another possible implementation, the target operation item includes a deduplication operation and / or a grouping operation.

[0018] In another possible implementation, the optimization device for database query provided by the present application further includes a second determination module, which is used to determine the scanning path of the target operation item during query execution, and there is room for trimming optimization.

[0019] In another possible implementation, the second determination module is specifically used to determine the scan path of the target operation item during query execution if the target data table is not at the end in the connection sequence, and there is room for trimming optimization.

[0020] In another possible implementation, the optimization device for database query provided by the present application further includes a third determination module, and the third determination module is used to determine that the query statement includes a target field, and the target field indicates a target operation item.

[0021] In a third aspect, an embodiment of the present application provides a computing device, including a memory and a processor, wherein the memory stores instructions, and when the instructions are executed by the processor, the method described in the first aspect is implemented.

[0022] In a fourth aspect, an embodiment of the present application provides a computer-readable storage medium having a computer program stored thereon, and when the computer program is executed by a processor, the method described in the first aspect is implemented.

[0023] In a fifth aspect, an embodiment of the present application further provides a computer program or a computer program product, wherein the computer program or the computer program product comprises instructions, and when the instructions are executed, the computer is caused to execute the method described in the first aspect.

[0024] In a sixth aspect, an embodiment of the present application further provides a chip, comprising at least one processor and a communication interface, wherein the processor is used to execute the method described in the first aspect. BRIEF DESCRIPTION OF THE DRAWINGS

[0025] Figure 1 A schematic diagram of a general process of multi-table connection query is shown;

[0026] Figure 2 A schematic diagram of a query scan path that does not use the optimization solution provided in the embodiment of the present application to perform scan path clipping optimization;

[0027] Figure 3 A schematic diagram of the structure of a database system is shown;

[0028] Figure 4 A flowchart of an optimization method for database query provided in an embodiment of the present application;

[0029] Figure 5 A schematic diagram of a scan path corresponding to a distinct operation;

[0030] Figure 6 A schematic diagram of a clipped optimized scan path is shown;

[0031] Figure 7The following is a diagram showing an implementation architecture of an optimization method for database query provided by an embodiment of the present application;

[0032] Figure 8 A schematic structural diagram of an optimization device for database query is shown;

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

[0034] Fig.10 A schematic diagram of a computing device cluster provided in an embodiment of the present application;

[0035] Fig.11 for Fig.10 A schematic diagram of an application scenario of a computing device cluster is provided. DETAILED DESCRIPTION

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

[0037] 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 objects. For example, a first optimization item and a second optimization item are used to distinguish different optimization items rather than to describe a specific order of optimization items.

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

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

[0040] The nested relationship of iterator access is usually determined by a query plan tree composed of multiple operators (for example, in the database volcano model). In this query plan tree, each operator can be regarded as an iterator with its own input and output. When an operator depends on the output of another operator, they can be nested to form an iterator chain.

[0041] In multi-table join scenarios, complex queries can be implemented by nesting operators. For example, after three data tables are joined, the optimizer will calculate the possible join order and generate the corresponding access method to drive each query iterator to obtain data. The outer iterator depends on the return data of the inner iterator. When it is determined that the data meets the join conditions, the innermost loop is exited, and the outer loop continues the iteration process. Figure 1 A schematic diagram of a general process of multi-table connection query is shown, where "x" is an optional reference to the connection method.

[0042] In the current multi-table connection scenario, iterator access optimization is not performed for operations such as distinct / group by. Assume that there are three data tables and the data in them is as follows:

[0043] CREATE TABLE t1(a INT);

[0044] INSERT INTO t1 VALUES(1),(2),(5),(6);

[0045] CREATE TABLE t2(a INT);

[0046] INSERT INTO t2 VALUES(1),(2),(5),(6);

[0047] CREATE TABLE t3(a INT);

[0048] INSERT INTO t3 VALUES(1),(2),(5),(6);

[0049] The query statement is as follows:

[0050] select distinct t1.a from t1 join t2 on t1.a+6>t2.a join t3 on t2.a+3=t3.a;

[0051] The connection conditions are t1.a+6>t2.a and t2.a+3==t3.a respectively; the optimizer determines that the connection order of data table t1, data table t2 and data table t3 is data table t1 connected to data table t2, and data table t2 connected to data table t3; and the connection method is nested loop connection (NestLoopJoin), at this time, table t1 is the outermost query table, and table t3 is the innermost query table.

[0052] The judgment result of t1.a+6>t2.a is always true. Each time the outer table is iterated, the inner table will be iterated. When a series of data is scanned according to this process and the data set t1.a=1, t2.a=2, t3.a=5 is located, it is judged that all the connection conditions are met. At this time, the iterator accessing the t3 table will end the iteration process, and the iterator accessing the t2 table will continue to query the next row, that is, the data corresponding to t2.a=5, and then continue the nested query process, such as Figure 2 shown.

[0053] From the query statement, we can know that the distinct operation is applied to the t1 table. Therefore, for the final query result, the result of t1.a is unique. After the t1.a value that meets the connection condition, that is, 1, is found, the t1.a value corresponding to the t2 and t3 tables is still 1. This is an unnecessary access path for obtaining the query statement execution result, which is a waste of system resources.

[0054] To this end, an embodiment of the present application provides an optimization method for database queries, which optimizes and trims the scan path (also referred to as the access path) during the execution of the query statement to trim unnecessary scan paths, improve query performance, and reduce the resource overhead of the database management system.

[0055] The technical solution provided by the embodiments of the present application is further described in detail below through the drawings and examples.

[0056] Figure 3 A schematic diagram of the structure of a database system is shown. Figure 3 As shown, the database system includes a client 31 , a database management system (DBMS) 32 and a database 33 .

[0057] The database 33 refers to an organized data set stored in a data storage device, that is, a collection of associated data organized, stored and used according to a certain data model. For example, the database may include one or more tables (Table) of data. A table is an object used to store data in a database. It is a collection of structured data and is the basis of the entire database system. A table is a database object that contains all the data in the database, and a table can be defined as a collection of columns. The database 33 can be a relational database (such as PostgreSQL, MySQL, and openGauss databases, etc.) and a non-relational database (such as Cassandra database). The embodiment of the present application does not specifically limit the specific type of the database 33.

[0058] The database management system 32 is used to uniformly manage and control the database to ensure the security and integrity of the database. In order to facilitate the processing of matters related to the database 33, the database management system 32 establishes a communication connection with the data block, which can be a wired communication connection or a wireless communication connection. In terms of deployment and implementation, the database management system 32 and the database 33 can be deployed on the same physical device or on different physical devices.

[0059] The client 31 may include any type of device or application configured to interact with the database management system 32. When a user needs to query a database, the user may write a query statement through the client 31, and then the client 31 sends the query statement to the database management system 32. The database management system 32 accesses data related to the query statement from the database 33 according to the query statement to generate a query result corresponding to the query statement, and then returns the query result to the client 31.

[0060] It is understandable that the query statement may be different depending on the database query language supported by the database 33. For example, the query statement may be a SQL statement or a NoSQL statement. The following is an example in which the query statement is a SQL statement.

[0061] The database management system 32 generally includes a parser, an optimizer, an executor and a memory. Among them, the parser is used to perform grammatical and semantic analysis of SQL query statements; the optimizer is used to generate an optimal (which can be understood as the one with the lowest execution cost) query plan for the SQL query statement. For example, when there are multiple table connections in the query statement, the optimizer can determine the connection order of each table. For a given query statement, different connection orders can be used to connect multiple data tables indicated by the query statement, and the more data tables to be connected, the more connection orders the query statement corresponds to, that is, the more candidate plans the query statement corresponds to. The logical results corresponding to different connection orders are the same, but the execution costs are generally different. The role of the optimizer is to decide to select a connection order with the lowest cost and generate a query plan for the query statement according to the connection order. The executor is used to operate according to the query plan generated by the optimizer to generate query results. The memory is responsible for managing the data of the table and the actual content of the index on the file system, and also manages the cache, buffer, things, log and other data during runtime. For example, the memory can write the execution results of the execution engine into the memory through physical I / O.

[0062] The client 31 can be deployed on a terminal device, and the database management system 32 and the database 33 can be deployed on a server. The terminal device and the server are connected in communication. The user can input a query statement through the client 31 on the terminal device. The database management system 32 receives the query statement sent by the client through a communication interface, such as an application programming interface (API) or an Ethernet interface or other network interface, generates a query result corresponding to the query statement from the database query data, and returns the query result to the client 31 through the above-mentioned communication interface.

[0063] Those skilled in the art will understand that a server may be an independent physical server, a server cluster or a distributed system composed of multiple physical servers, or a cloud server or server cluster that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDNs), and big data and artificial intelligence platforms.

[0064] The terminal can be a smart phone, a tablet computer, a laptop computer, a desktop computer, a wearable electronic device (such as a smart watch), a car terminal, a smart home appliance (such as a smart TV), an AR / VR device, etc., but is not limited to these.

[0065] The terminal and the server may be connected to each other via wired or wireless communication, which is not limited in the embodiments of the present application.

[0066] It should be understood by those skilled in the art that a database system may include Figure 3 More or fewer parts may be shown. Figure 3 Only components related to the implementation disclosed in the embodiment of the present application are shown. Figure 3 The structure of the database shown does not constitute a limitation on the embodiments of the present application.

[0067] Figure 4 A flowchart of an optimization method for database query provided in an embodiment of the present application. The method can be deployed with Figure 3 The server implementation of the database management system 32 shown in the figure may also be implemented by any computing device that is in communication with the server. Figure 4 As shown, an optimization method for database query provided in an embodiment of the present application includes steps S401 to S404.

[0068] In step S401, a multi-table connection query statement is obtained.

[0069] The specific method of obtaining the query statement is not specifically limited in the embodiments of the present application. Optionally, the query statement can be directly obtained from the client, or generated by the client according to a received business processing request involving a database query requirement. After obtaining the business processing request, the client can generate a corresponding query statement according to the request, and then transmit it to the database server according to a communication protocol, such as the MySQL communication protocol. Of course, after obtaining the business processing request, the client can directly send the business request to the database server, and the database server generates a query statement corresponding to the business processing request.

[0070] For different application scenarios and database languages, the query statement has a different structure. For example, when the database is a relational database, the query statement is an SQL query statement, and when the database is a non-relational database, the query statement is a NoSQL query statement. The embodiment of the present application takes the database as a relational database and the query statement as an SQL query statement as an example to introduce the detailed scheme of the embodiment of the present application.

[0071] Exemplarily, after receiving a business processing request involving a database query requirement, the client generates an SQL query statement according to the request, and sends the generated SQL query statement to the database server, and the data management system deployed on the server receives the SQL query statement sent by the client and obtains the SQL query statement. The SQL query statement is a multi-table connection query statement for connection query of multiple data tables.

[0072] After obtaining the query statement, the data management system analyzes the syntax and semantics of the query statement through a parser to obtain multiple data tables for multi-table connection query, operations on the data tables and connection conditions for connecting multiple data tables.

[0073] For example, the SQL query statement obtained by the data management system is: "select distinct t1.a from t1join t2 on t1.a+6>t2.a join t3 on t2.a+3=t3.a".

[0074] The SQL query statement is parsed by the parser, and the query semantics are obtained as follows: the multiple data tables are data table t1, data table t2, and data table t3, and the connection conditions are "t1.a+6>t2.a" and "t2.a+3=t3.a", respectively, and the data table t1 is deduplicated. At this time, the deduplication operation is the target operation item, and the operation object of the deduplication operation is the target data table, that is, in the SQL query statement, the distinct operation is the target operation item, and the data table t1 is the target data table.

[0075] It should be pointed out that the above SQL query statement is only an example and does not constitute a limitation on the embodiments of the present application. The SQL query statement may also include multi-table join queries for more or fewer data tables. The join conditions between multiple data tables may be other join conditions and other target operation items for the data tables, such as group by operations.

[0076] In another example, the optimization method for database query provided by the embodiment of the present application only optimizes queries for specific operation items. For example, the specific operation items are distinct operations and group by operations. Before the optimization scheme of the present application is executed, it is first determined whether the query statement includes a distinct field indicating a distinct operation, and / or a group by field indicating a group by operation. If the distinct field and / or the group by field are included, the optimization scheme provided by the embodiment of the present application is executed. Otherwise, the optimization scheme implemented by the present application is not executed, and the subsequent execution is carried out according to the normal query process.

[0077] The query mentioned in the embodiment of the present application means a request to view, access, and operate data stored in a database.

[0078] In step S402, a connection sequence of multiple data tables is generated.

[0079] The parser parses the SQL query statement to obtain the query semantics, that is, what kind of data the user wants to query from which data tables, and does not limit the connection order of multiple data tables. Different connection orders have a great impact on the execution efficiency of the query. At this time, the optimizer needs to generate the optimal connection order for the SQL query statement according to the optimization algorithm. The optimal connection order means that the time to obtain the query result by executing the query according to this connection order is the shortest.

[0080] That is to say, for a given SQL query statement, different connection orders can be used to connect multiple data tables indicated by the SQL query statement, and the more data tables to be connected, the more connection orders the SQL query statement corresponds to, that is, the more candidate plans the SQL query statement corresponds to. The logical results corresponding to different connection orders are the same, but the execution costs are generally different. The role of the optimizer is to decide to select a connection order with the lowest cost and generate a query plan for the SQL query statement based on the connection order.

[0081] For example, the parser parses the SQL query statement to obtain multiple data tables as data table t1, data table t2, and data table t3. The connection sequence generated by the optimizer is that data table t1 is first connected to data table t2, and then data table t2 is connected to data table t3.

[0082] After generating the connection sequence, the optimizer will also generate a query plan for the SQL query statement based on the connection sequence. The query plan includes the specific access path for executing the query, which includes the scanning method, connection method, and connection order. The scanning method indicates the scanning method (also called the scanning path) of the multiple data tables involved in the SQL query statement. The connection method indicates the connection method between the multiple data tables involved in the SQL query statement, such as nested loop join, hash join, and merge join. The connection order indicates the connection order between the multiple data tables involved in the SQL query statement, that is, when connecting multiple data tables, which data table is in front and which data table is in the back.

[0083] It should be pointed out that the embodiments of the present application do not specifically limit the optimization algorithm used by the optimizer, and any suitable optimization algorithm can be set in the optimizer to generate the connection order and query plan. And the optimizer mentioned in the embodiments of the present application generates the optimal connection order, which only refers to the optimal connection order in the current optimization algorithm, and does not guarantee the objectively optimal connection order. For example, the connection order generated by the optimization algorithm used by the optimizer may be that data table t1 is first connected to data table t2, and data table t2 is then connected to data table t3, but the connection order generated by the more advanced optimization algorithm may be that data table t3 is first connected to data table t2, and data table t2 is then connected to data table t1.

[0084] In step S403, an optimization item for the target operation item is determined, where the optimization item indicates a clipping level of a scan path corresponding to the target operation item.

[0085] Through the above steps, the target operation item's action object (i.e., the target data table) and the connection order of multiple connection tables are obtained, and then based on the target data table and the connection order, the optimization item is determined. The trimming level of the optimization item is the scanning path level of the outermost inner data table corresponding to the target data table.

[0086] For example, the multiple data tables are data table t1, data table t2, and data table t3, data table t1 is the target data table, and the connection order of the multiple data tables generated by the optimizer is data table t1 connected to data table t2, and data table t2 connected to data table t3. At this time, data table t3 is the inner data table of data table t2, and data table t2 is the inner data table of data table t1. During the query process, the scanning paths of the multiple data tables are: when data table t1 scans one row of data, a full table scan of data table t2 is required, and when data table t2 scans one row of data, a full table scan of data table t3 is required. The scanning path of data table t1 can be called the first scanning path level, the scanning path of data table t2 can be called the second scanning path level, and the scanning path of data table t3 can be called the third scanning path level. When it is known that the target data table is data table t1, and the connection order of multiple data tables is data table t1 connected to data table t2, and data table t2 connected to data table t3, the optimization item corresponding to the target operation item can be determined as the clipping level is the second scanning path level, that is, the scanning path level of the outermost inner data table corresponding to the target data table.

[0087] An SQL query statement may include multiple target operation items. In this case, it is necessary to calculate the optimization items corresponding to the items under the multiple target operations. For example, if the multiple target operation items include distinct operations and group by operations, the optimization items corresponding to the distinct operations and the optimization items corresponding to the group by operations are calculated separately. The optimization items corresponding to the distinct operations and the optimization items corresponding to the group by operations constitute the optimization set.

[0088] After determining the optimization items of each target operation item, each optimization item is stored in a memory to form an optimization set for subsequent steps to call.

[0089] In step S404, during the execution of the target operation item, when the current query iteration round finds data that meets the connection condition, the optimization item is executed to clip the scan path based on the clipping level.

[0090] The query plan is usually expressed in the form of a query plan tree. The query plan tree includes multiple operators, such as scan operators, deduplication operators, grouping operators, etc. Each operator can be regarded as an iterator. The query plan tree defines the relationship between each iterator. For example, when an iterator depends on the output of another iterator, the two iterators form a nested relationship, and the two are nested to form an iterator chain.

[0091] The executor performs query operations according to the query plan tree generated by the optimizer. When the query plan tree executes the target operation item, when the current query iteration round queries data that meets the connection conditions, the optimization item is executed and the scan path is clipped based on the clipping level, so that the scan path for the inner data table is clipped.

[0092] For example, the SQL query statement is: "select distinct t1.a from t1 join t2 on t1.a+6>t2.a join t3 on t2.a+3=t3.a", and the data tables t1, t2, and t3 are generated according to the following instructions:

[0093] CREATE TABLE t1(a INT);

[0094] INSERT INTO t1 VALUES(1),(2),(5),(6);

[0095] CREATE TABLE t2(a INT);

[0096] INSERT INTO t2 VALUES(1),(2),(5),(6);

[0097] CREATE TABLE t3(a INT);

[0098] INSERT INTO t3 VALUES(1),(2),(5),(6);

[0099] That is, data table t1, data table t2 and data table t3 have column a, and the row data are 1, 2, 5 and 6 respectively.

[0100] The query plan tree is generated by the optimizer, and the scan path corresponding to the distinct operation is obtained as follows Figure 5 As shown, a row of data in data table t1 is scanned first, and then the entire table of data table t2 is scanned. After scanning a row of data in data table t2, the entire table of data table t3 is scanned.

[0101] The pruning level indicated by the optimization item for the distinct operation is the second level, that is, the scan path level where the data table t2 is located.

[0102] The executor performs query operations according to the query plan tree generated by the optimizer. When the query plan tree executes the target operation item, when the current query iteration round queries the data that meets the connection condition, the optimization item corresponding to the distinct operation acts on the corresponding iterator (that is, the iterator used to scan the data table t2), and the scan path is trimmed at the second layer, so that the scan path for the data table t2 and the data table t3 is trimmed.

[0103] Figure 6 A schematic diagram of a scan path that has been optimized by clipping is shown. Figure 6 As shown in the figure, during the query plan tree execution process, the data is scanned in sequence according to the scanning path to determine that the connection condition is not established, and then the data set t1.a=1, t2.a=2, t3.a=5 is located and determined to meet the connection condition. Then, the subsequent access paths of tables t2 and t3 can be cut off according to the optimization items, and a new round of scanning and judgment corresponding to the next row of data in table t1 (t1.a=2) is directly started. Figure 6 It can be seen that after executing the cutting optimization strategy provided in the embodiment of the present application, the<t2.a,t3.a> The data in the form of <5,1>, <5,2>, <5,5>, <5,6>, <6,1>, <6,2>, <6,5>, and <6,6> can be effectively reduced in scanning and in reducing the storage and computing resource overhead when judging data rows.

[0104] In another example, when a SQL query statement includes multiple target operation items (such as distinct operations and group by operations), that is, the optimization set includes multiple optimization items, such as a first optimization item for a distinct operation and a second optimization item for a group by operation. It is necessary to determine the target optimization item from the optimization set, and the target optimization item is the innermost optimization item in the trimming hierarchy of the optimization set. During the execution of a distinct operation or a group by operation, when the current query iteration round queries data that meets the connection conditions, the target optimization item is executed, and the scan path is trimmed based on the trimming hierarchy of the target optimization item, so that the scan path for the inner data table is trimmed, thereby achieving overall trimming of the scan path.

[0105] For example, multiple data tables include data table t1, data table t2, data table t3, and data table t4, and the connection order is data table t1, data table t3, data table t4, and data table t2. The scan path level where data table t1 is located is the first level, the scan path level where data table t3 is located is the second level, the scan path level where data table t4 is located is the third level, and the scan path level where data table t2 is located is the fourth level. The clipping level of the first optimization item is the second level, and the clipping level of the second optimization item is the third level. The third level is inner than the second level, so the target optimization item is the third level. The executor performs query operations according to the query plan tree generated by the optimizer. When the query plan tree performs a distinct operation or a group by operation, when the current query iteration round finds data that meets the connection conditions, the scan path is clipped at the third level, so that the scan paths for data table t4 and data table t2 are clipped, thereby achieving overall clipping of the scan path.

[0106] Figure 7 FIG. 1 shows an implementation architecture diagram of an optimization method for database query provided by an embodiment of the present application. Figure 7 As shown, the optimizer generates a query plan tree through an optimization algorithm. The query plan tree is composed of multiple iterators, indicating the relationship between each iterator. During the optimization process, the optimizer determines to perform tailoring optimization on the scan path of the scan iterator under the target operation (such as distinct operation and / or group by operation), and groups the optimization items of each target operation into an optimization set to obtain global optimization information. Figure 7 In the above, "X" represents the connection mode such as loop nested connection, hash connection and merge connection, "Y" represents the optimization aspects such as distinct, group by, etc., and "Z" represents the pruning level, that is, the outermost level where unnecessary access paths of inner tables can be pruned and optimized. The "Y...Level Z..." in the optimization set means that in a certain connection mode belonging to the X set, an optimization item belonging to the Y set is optimized and analyzed, and the information that the inner table access path can be pruned at a certain outermost pruning point level is obtained. In an SQL query statement, there may be multiple target operation items, such as distinct operation and groupby operation, and there are multiple Y. Multiple optimizations of the Y set are analyzed, and the generated optimization information is stored as the global optimization set to which this query belongs. The innermost pruning level is selected from each optimization information as the overall pruning level, so that multiple optimization items act on the access iterator in combination, in order to reduce the number of iterations of all unnecessary inner tables and increase query efficiency.

[0107] For example, the SQL query statement is: "select distinct t1.afrom t1 join t2 on t1.a+6>t2.ajoin t3 on t2.a+3=t3.a", and the row data in column a of data table t1, data table t2 and data table t3 are 1, 2, 5 and 6 respectively. When the optimization strategy switch state is on, the nested loop join deduplication operation is optimized (CheckNestLoopJoin do distinct Optimize) and the distinct optimization item is analyzed. It is found that data t1 is affected by the distinct operation while data table t2 and data table t3 are not affected. Then it is concluded that the pruning level corresponding to the optimization item is the scanning level of data table t2 and is stored in the optimization set. Figure 6 As shown in the figure, when the join conditions in the NestLoopJoin mode are t1.a+6>t2.a and t2.a+3==t3.a, the data is scanned in turn to determine that the join conditions are not met, and then the data set of t1.a=1, t2.a=2, and t3.a=5 is located and determined to meet the join conditions. Then, the subsequent access paths of the t2 and t3 tables can be cut off according to the optimization information, and a new round of judgment corresponding to the next row of data in the t1 table (t1.a=2) is directly started. Then, after using this optimization strategy, it will skip<t2.a,t3.a> The data in the form of <5,1>, <5,2>, <5,5>, <5,6>, <6,1>, <6,2>, <6,5>, and <6,6> can be effectively reduced by the storage and computing resource overhead when scanning and judging the data rows.

[0108] The following introduces a specific implementation of the optimization method for database query provided by an embodiment of the present application in practical application.

[0109] S1. Create table t1 that contains only columns of type int and named a. Create SQL statements for multi-table join + distinct scenarios and send them to the database computing layer, such as select distinct t1.afrom t1 join t2 on t1.a+6>t2.a join t3on t2.a+3=t3.a.

[0110] S2. The table connection sequence generated by the computing layer for this query is t1, t2, and t3, and the connection method is a loop nested connection;

[0111] S3, the computing layer determines that distinct is applied to data table t1 but not to data table t2 and data table t3, and data table t2 and data table t3 are inner tables of data table t1 on the connection path. When the optimization strategy switch state corresponding to the embodiment of the present application is on, the distinct operation optimization in the loop nested connection mode can be performed;

[0112] S4, the computing layer generates distinct optimization item t1 table to clip the hierarchical information and saves it in the optimization set;

[0113] S5. The loop nested connection related access iterator generated by the computing layer initiates a scan request for the data table corresponding to the current connection sequence to the storage layer;

[0114] S6. The storage layer receives the IO request and returns the data scanned by the data table to the computing layer;

[0115] S7, continue to execute steps S5-S6 to drive scanning of the next sequential connection table, obtain data in each data table corresponding to the connection condition, and the computing layer determines that when all connection conditions are met, based on the information in the optimization set, no longer drive scanning of the table within the clipping level, advance processing of the row data corresponding to t1.a=2 in t1, and return to step S5 to continue the next round of nested query process until all row data of table t1 are processed;

[0116] S8. Finally, the computing layer returns the query results to the client.

[0117] The present application also provides an optimization device for database query, such as Figure 8 As shown, including:

[0118] An acquisition module 801 is used to acquire a query statement, wherein the query statement indicates a connection query for multiple data tables, a connection condition, and a target operation item for a target data table in the multiple data tables;

[0119] A generating module 802, configured to generate a connection sequence of the plurality of data tables;

[0120] A first determination module 803 is used to determine an optimization item for the target operation item, where the optimization item indicates a clipping level of a scan path corresponding to the target operation item;

[0121] The trimming optimization module 804 is used to execute the optimization item when the current query iteration round queries the data that meets the connection condition during the execution of the target operation item, so as to trim the scanning path based on the trimming level so that the scanning path for the inner data table is trimmed. The inner data table is a data table in the multiple data tables whose connection order is after the target data table.

[0122] Among them, the acquisition module 801, the generation module 802, the first determination module 803 and the cutting optimization module 804 can all be implemented by software, or can be implemented by hardware. Exemplarily, the following takes the acquisition module 801 as an example to introduce the implementation of the acquisition module 801. Similarly, the implementation of the generation module 802, the first determination module 803 and the cutting optimization module 804 can refer to the implementation of the acquisition module 801.

[0123] As an example of a software functional unit, the acquisition module 801 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 501 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 similar geographical locations. Among them, usually a region may include multiple AZs.

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

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

[0126] The multiple computing devices included in the acquisition module 801 can be distributed in the same region or in different regions. The multiple computing devices included in the acquisition module 801 can be distributed in the same AZ or in different AZs. Similarly, the multiple computing devices included in the acquisition module 801 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.

[0127] It should be noted that, in other embodiments, the acquisition module 801 can be used to execute any step in the optimization method for database query, the generation module 802 can be used to execute any step in the optimization method for database query, the first determination module 803 can be used to execute any step in the optimization method for database query, and the tailoring optimization module 804 can be used to execute any step in the optimization method for database query; the steps that the acquisition module 801, the generation module 802, the first determination module 803 and the tailoring optimization module 804 are responsible for implementing can be specified as needed, and the acquisition module 801, the generation module 802, the first determination module 803 and the tailoring optimization module 804 respectively implement different steps in the optimization method for database query to realize all the functions of the optimization device for database query.

[0128] The present application embodiment also provides a computing device 900. Fig. 9 As shown, the computing device 900 includes: a bus 902, a processor 904, a memory 906, and a communication interface 908. The processor 904, the memory 906, and the communication interface 908 communicate through the bus 902. The computing device 900 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 900.

[0129] The bus 902 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, Fig. 9 The bus 902 may include a path for transmitting information between various components of the computing device 900 (eg, the memory 906, the processor 904, and the communication interface 908).

[0130] The processor 904 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).

[0131] The memory 906 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).

[0132] The memory 906 stores executable program codes, and the processor 904 executes the executable program codes to respectively implement the functions of the aforementioned acquisition module 801, generation module 802, first determination module 803, and trimming optimization module 804, thereby implementing the optimization method for database query. That is, the memory 906 stores instructions for the optimization method for database query.

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

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

[0135] like Fig.10 As shown, the computing device cluster includes at least one computing device 900. The memory 906 in one or more computing devices 900 in the computing device cluster may store the same instructions for executing the optimization method for database query.

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

[0137] It should be noted that the memory 906 in different computing devices 900 in the computing device cluster can store different instructions, which are respectively used to execute part of the functions of the optimization device for database query. That is, the instructions stored in the memory 906 in different computing devices 900 can implement the functions of one or more modules among the acquisition module 801, the generation module 802, the first determination module 803 and the clipping optimization module 804.

[0138] 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.11 A possible implementation is shown. Fig.11 As shown, two computing devices 900A and 900B 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 906 in the computing device 900A stores instructions for executing the functions of the acquisition module 801 and the generation module 802. At the same time, the memory 906 in the computing device 900B stores instructions for executing the functions of the first determination module 803 and the clipping optimization module 804.

[0139] It should be understood that Fig.11 The functions of the computing device 900A shown in FIG. 9A may also be completed by multiple computing devices 900. Similarly, the functions of the computing device 900B may also be completed by multiple computing devices 900.

[0140] The embodiment of the present application also provides a computer program product comprising instructions. The computer program product may be software or a program product comprising instructions that can be run on a computing device or stored in any available medium. When the computer program product is run on at least one computing device, the at least one computing device is caused to perform an optimization method for database query.

[0141] The embodiment of the present application also provides a computer-readable storage medium. The computer-readable storage medium can be any available medium that can be stored by a computing device or a data storage device such as a data center containing one or more available media. The available medium can be a magnetic medium (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 hard disk). The computer-readable storage medium includes instructions that instruct the computing device to execute an optimization method for a database query.

[0142] An embodiment of the present application provides a chip, which includes at least one processor and an interface, wherein the at least one processor determines program instructions or data through the interface; the at least one processor is used to execute the program instructions to implement the above-mentioned optimization method for database query.

[0143] In the above-mentioned embodiments, the description of each embodiment has its own emphasis. For the part that is not described or recorded in detail in a certain embodiment, reference can be made to the relevant description of other embodiments.

[0144] The basic principles of the present application are described above in conjunction with specific embodiments. However, it should be noted that the advantages, strengths, effects, etc. mentioned in the embodiments of the present application are only examples and not limitations, and it cannot be considered that these advantages, strengths, effects, etc. are required by each embodiment of the present disclosure. In addition, the specific details disclosed above are only for the purpose of illustration and ease of understanding, rather than limitation, and the above details do not limit the embodiments of the present application to the necessity of adopting the above specific details to be implemented.

[0145] The block diagrams of the devices, equipment, and systems involved in the embodiments of the present application are only illustrative examples and are not intended to require or imply that they must be connected, arranged, and configured in the manner shown in the block diagram. As will be appreciated by those skilled in the art, these devices, devices, equipment, and systems can be connected, arranged, and configured in any manner. Words such as "including", "comprising", "having", etc. are open words, referring to "including but not limited to", and can be used interchangeably with them. The words "or" and "and" used here refer to the words "and / or", and can be used interchangeably with them, unless the context clearly indicates otherwise. The words "such as" used here refer to the phrase "such as but not limited to", and can be used interchangeably with them.

[0146] It should also be noted that in the apparatus, device and method of the embodiments of the present application, each component or each step can be decomposed and / or recombined. Such decomposition and / or recombination should be regarded as equivalent solutions of the embodiments of the present application.

[0147] The above description has been given for the purpose of illustration and description. In addition, this description is not intended to limit the embodiments of the present disclosure to the forms disclosed herein. Although multiple example aspects and embodiments have been discussed above, those skilled in the art will recognize certain variations, modifications, changes, additions and sub-combinations thereof.

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

Claims

1. A method for optimizing database query, characterized in that: include: Obtaining a query statement, wherein the query statement indicates a connection query for multiple data tables, a connection condition, and a target operation item for a target data table in the multiple data tables; Generate a connection sequence of the multiple data tables; Determine an optimization item for the target operation item, where the optimization item indicates a clipping level of a scan path corresponding to the target operation item; During the execution of the target operation item, when the current query iteration round queries data that meets the connection condition, the optimization item is executed to clip the scan path based on the clipping level, so that the scan path for the inner data table is clipped. The inner data table is a data table in the multiple data tables whose connection order is after the target data table.

2. The method according to claim 1, characterized in that: The determining of the optimization item for the target operation item includes: The optimization item is determined based on the target data table and the connection sequence, and the clipping level of the optimization item is the scanning path level where the outermost inner data table corresponding to the target data table is located.

3. The method according to claim 1 or 2, characterized in that: The target operation item includes a first target operation item and a second target operation item; The method further comprises: saving a first optimization item for the first target operation item and a second optimization item for the second target operation item to obtain an optimization set; Determine a target optimization item from the optimization set, where the target optimization item is the optimization item at the innermost layer of the clipping hierarchy in the optimization set; During the execution of the target operation item, when the current query iteration round queries data that meets the connection condition, the optimization item is executed, and the scan path is clipped based on the clipping level, including: During the execution of the first target operation item or the second target operation item, when the current query iteration round queries data that meets the connection condition, the target optimization item is executed, and the scan path is clipped based on the clipping level of the target optimization item.

4. The method according to any one of claims 1 to 3, characterized in that: The target operation items include a deduplication operation and / or a grouping operation.

5. The method according to any one of claims 1 to 4, characterized in that: The determining of the optimization item for the target operation item also includes: The scanning path of the target operation item during query execution is determined, and there is room for trimming and optimization.

6. The method according to claim 5, characterized in that The determining of the scanning path of the target operation item during query execution, where there is room for trimming and optimization, includes: If the target data table is not at the end in the connection sequence, the scanning path of the target operation item during query execution is determined, and there is room for trimming optimization.

7. The method according to any one of claims 1 to 6, characterized in that: The obtaining query statement also includes: It is determined that the query statement includes a target field, where the target field indicates the target operation item.

8. An optimization device for database query, characterized in that: include: An acquisition module, configured to acquire a query statement, wherein the query statement indicates a connection query for multiple data tables, a connection condition, and a target operation item for a target data table in the multiple data tables; A generating module, used for generating a connection sequence of the plurality of data tables; A first determination module, configured to determine an optimization item for the target operation item, wherein the optimization item indicates a clipping level of a scan path corresponding to the target operation item; A trimming optimization module is used to execute the optimization item when the current query iteration round queries data that meets the connection condition during the execution of the target operation item, so as to trim the scan path based on the trimming level so that the scan path for the inner data table is trimmed. The inner data table is a data table in the multiple data tables whose connection order is after the target data table.

9. The device according to claim 8, characterized in that The first determining module is specifically used for: The optimization item is determined based on the target data table and the connection sequence, and the clipping level of the optimization item is the scanning path level where the outermost inner data table corresponding to the target data table is located.

10. The device according to claim 8 or 9, characterized in that The target operation item includes a first target operation item and a second target operation item; The device also includes: A saving module, used for saving a first optimization item for the first target operation item and a second optimization item for the second target operation item to obtain an optimization set; The first determination module is further used to determine a target optimization item from the optimization set, where the target optimization item is the optimization item at the innermost layer of the clipping hierarchy in the optimization set; The cutting optimization module is specifically used for: During the execution of the first target operation item or the second target operation item, when the current query iteration round queries data that meets the connection condition, the target optimization item is executed, and the scan path is clipped based on the clipping level of the target optimization item.

11. The device according to any one of claims 8 to 10, characterized in that: The target operation items include a deduplication operation and / or a grouping operation.

12. The device according to claim 11, characterized in that Also includes: The second determination module is used to determine the scanning path of the target operation item during query execution, and there is room for trimming and optimization.

13. The device according to claim 12, characterized in that The second determining module is specifically used for: If the target data table is not at the end in the connection sequence, the scanning path of the target operation item during query execution is determined, and there is room for trimming optimization.

14. The device according to any one of claims 11 to 13, characterized in that: Also includes: The third determination module is used to determine that the query statement includes a target field, and the target field indicates the target operation item.

15. A computing device comprising a memory and a processor, characterized in that: Instructions are stored in the memory, and when the instructions are executed by the processor, the method according to any one of claims 1 to 7 is implemented.

16. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images