Query optimization method, device and storage medium

Abstract

Embodiments of the present application provide a query optimization method and device and a storage medium. After an optimizer obtains a logical execution plan of a query statement, the optimizer obtains statistical information of a logical plan node in the logical execution plan from collected statistical information of a data source, and can perform a cost-based optimization operation on the logical execution plan according to the statistical information of the logical plan node. The statistical information of the data source includes statistical information collected from the data source when an executor executes a physical execution plan of the specified query statement, reducing the dependence on statistical information collected by the data source itself. Furthermore, when querying a plurality of different types of data sources, the statistical information of the logical plan node can be flexibly obtained, thereby facilitating adaptation to the scenario of querying a plurality of different data sources, and improving the universality of the query optimization method.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a query optimization method, device and storage medium. Background Technology

[0002] Typically, to improve query efficiency, cost-based optimizers are used in databases to optimize the logical execution plan corresponding to the query statement. In one query optimization process, it is necessary to obtain statistical information from the data source and determine the query cost of the logical execution plan based on this information. However, existing methods for obtaining statistical information from the data source are highly dependent on the data source itself. If the data source lacks statistical capabilities, cost-based optimization cannot be performed, resulting in poor flexibility and versatility. Therefore, a new solution is needed. Summary of the Invention

[0003] This application provides a query optimization method, device, and storage medium to flexibly obtain statistical information of logical plan nodes, reduce dependence on data sources, and thus facilitate adaptation to various query scenarios.

[0004] This application provides a query optimization method, including: obtaining a logical execution plan of a first query statement; the logical execution plan includes at least one logical plan node; obtaining statistical information of the at least one logical plan node from collected statistical information of a data source; the statistical information of the data source includes: first statistical information collected from the data source when the physical execution plan of the specified query statement is executed by the executor; and performing a cost-based optimization operation on the logical execution plan based on the statistical information of the at least one logical plan node.

[0005] Optionally, before obtaining the statistical information of the at least one logical plan node from the collected statistical information of the data source, the method further includes: determining whether the type of the data source is a target type with statistical capabilities; if not, determining the first statistical information as the statistical information of the data source; if yes, determining the statistical information of the data source based on the first statistical information and the second statistical information collected from the data source.

[0006] Optionally, before obtaining the statistical information of the at least one logical plan node from the statistical information in the data table, the method further includes: for any second query statement in the specified query statement, determining the target data source to be scanned for executing the query operation of the second query statement based on the logical execution plan of the second query statement; and collecting the statistical information of the target data source using the executor when the executor executes the physical execution plan corresponding to the second query statement.

[0007] Optionally, when the executor executes the physical execution plan corresponding to the second query statement, the executor collects statistical information of the target data source, including: determining whether the logical execution plan of the second query statement contains a connection node; if it contains the connection node, the executor collects statistical information of the target data source when the executor executes the physical execution plan corresponding to the second query statement.

[0008] Optionally, obtaining statistical information of the at least one logical plan node from the collected statistical information of the data source includes: traversing the syntax tree corresponding to the first query statement to obtain the logical expression of each of the at least one logical plan node; and calculating the statistical information of the data source based on the logical expression of each of the at least one logical plan node to obtain the statistical information of each of the at least one logical plan node.

[0009] Optionally, based on the statistical information of the at least one logical plan node, a cost-based optimization operation is performed on the logical execution plan, including at least one of the following: determining the computational cost of the filtering node based on the statistical information of the filtered data source corresponding to the filtering node; determining the computational cost and memory cost of the aggregation node based on the statistical information of the aggregated data source corresponding to the aggregation node; determining the computational cost and memory cost of the connection node based on the statistical information of the connected data source corresponding to the connection node and the number of task executions of the connection operation; and determining the network cost of the interaction node based on the statistical information of the interacted data source corresponding to the interaction node and the number of task executions of the interaction operation.

[0010] This application embodiment also provides a query optimization method, including: collecting statistical information of the scanned data source when executing the physical execution plan corresponding to the query statement; providing the collected statistical information of the data source to the optimizer, so that the optimizer performs cost-based optimization operations on the logical execution plan of the received query statement according to the statistical information of the data source.

[0011] Optionally, when executing the physical execution plan corresponding to the query statement, statistical information of the scanned data source is collected, including: determining whether the physical execution plan contains join operations; if it contains join operations, then when executing the physical execution plan, statistical information of the data source is collected.

[0012] This application also provides a server, including: a memory and a processor; the memory is used to store one or more computer instructions; the processor is used to execute the one or more computer instructions to perform the steps in the method provided in this application.

[0013] This application also provides a computer-readable storage medium storing a computer program, which, when executed by a processor, can implement the steps in the method provided in this application.

[0014] In this embodiment, after the optimizer obtains the logical execution plan of the query statement, it extracts the statistical information of the logical plan nodes from the collected statistical information of the data source. Based on this statistical information, the optimizer performs cost-based optimization operations on the logical execution plan. The statistical information of the data source includes the statistical information collected from the data source when the executor executes the physical execution plan of the specified query statement, reducing reliance on statistical information collected by the data source itself. Furthermore, it can flexibly obtain the statistical information of the logical plan nodes when querying various types of data sources, thus facilitating adaptation to scenarios involving queries from multiple different data sources and improving the universality of the query optimization method. Attached Figure Description

[0015] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0016] Figure 1 A flowchart illustrating a query optimization method provided in an exemplary embodiment of this application;

[0017] Figure 2 A flowchart illustrating a query optimization method provided as another exemplary embodiment of this application;

[0018] Figure 3 This is a flowchart illustrating a query optimization method provided in an exemplary embodiment of this application;

[0019] Figure 4 This is a schematic diagram of the structure of a server provided for an exemplary embodiment of this application. Detailed Implementation

[0020] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0021] The terminology used in the embodiments of this invention is for the purpose of describing particular embodiments only and is not intended to limit the invention. The singular forms “a,” “the,” and “the” used in the embodiments of this invention and the appended claims are also intended to include the plural forms, unless the context clearly indicates otherwise. “Multiple” generally includes at least two, but does not exclude the inclusion of at least one.

[0022] It should be understood that the term "and / or" used in this article is merely a description of the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A existing alone, A and B existing simultaneously, and B existing alone. Additionally, the character " / " in this article generally indicates that the preceding and following related objects have an "or" relationship.

[0023] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a product or system comprising a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a product or system. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the product or system that includes said element.

[0024] In database query operations, multi-table joins are among the most important and time-consuming database operators. In distributed databases, multi-table joins are often implemented in stages: the right table node broadcasts the right table to all left table nodes, and then performs a hash join operation on the left tables. When the right table is large, this consumes significant network and memory resources, easily leading to memory shortages in the right table nodes. Considering the frequent multi-data source join operations in databases, cost-based optimization (CBO) is typically used to optimize the logical execution plan of the query statement to reduce query costs. During query optimization, it is necessary to obtain statistical information from the logical plan nodes in the logical execution plan and combine this information to determine the query cost of the logical execution plan. This approach places high demands on the data source, requiring the data source itself to have the ability to provide statistical information. However, not all data sources can provide statistical information, thus limiting its flexibility in various scenarios.

[0025] To address the aforementioned technical problems, a solution is provided in some embodiments of this application. The technical solutions provided by each embodiment of this application are described in detail below with reference to the accompanying drawings.

[0026] Figure 1This is a flowchart illustrating a query optimization method provided in an exemplary embodiment of this application. When executed by the optimizer, this method may include, for example... Figure 1 The steps shown are as follows:

[0027] Step 101: Obtain the logical execution plan of the first query statement; the logical execution plan contains at least one logical plan node.

[0028] Step 102: Obtain statistical information of the at least one logical plan node from the collected statistical information of the data source; the statistical information of the data source includes: first statistical information collected from the data source when the physical execution plan of the specified query statement is executed by the executor.

[0029] Step 103: Perform cost-based optimization operations on the logical execution plan based on the statistical information of the at least one logical plan node.

[0030] The execution entity in this embodiment can be an optimizer. This optimizer runs on the database server and is used to optimize the logical execution plan of the query statement based on cost or rules. After obtaining the optimized logical execution plan, a corresponding physical execution plan can be generated, and the executor on the database server executes the physical execution plan to obtain the query results.

[0031] The first query statement can be any query statement received by the database client. The database client can send the first query statement to the database server. The database server's parser can perform syntax analysis on the first query statement to obtain an Abstract Syntax Tree (AST). The AST describes the query logic of the first query statement, and the nodes in the AST are called logical plan nodes. For example, logical nodes may include: JoinNodes and SemiJoinNodes. JoinNodes include CrossJoin, InnerJoin, LeftJoin, RightJoin, and FullJoin functions, while SemiJoinNodes include SemiJoin and AntiJoin functions.

[0032] In this context, a data source refers to an object used to store data. This data source includes, but is not limited to, at least one of the following: database, data table, text, file body, and URL (Uniform Resource Locator). The first query statement typically describes the information of the data source to be queried and the information of the data to be queried, with the expectation of retrieving specific information from the specified data source.

[0033] Data source statistics are a collection of metadata information describing the tables, rows, and columns within a data source. They reflect the costs incurred in performing data query operations from the data source, such as network costs, memory costs, and computational costs. Statistics for any data source can include one of the following: the total amount of data collected from the data source, row-level statistics, and column-level statistics. Row-level statistics may include the total number of rows in the data source, the average row length, and the total number of pages of data on disk. Column-level statistics may include at least one of the following: the largest value in the column, the smallest value, the percentage of null values, the average row size, and the total number of distinct values.

[0034] In this embodiment, the statistical information of the data source includes: first statistical information collected from the data source when the executor executes the physical execution plan of the specified query statement. The specified query statement may include at least one of the following: a user-specified query statement, a query statement containing a specific query operation (e.g., a join operation), and historical query statements; wherein, historical query statements refer to query statements executed within a historical time period prior to receiving the first query statement. During the execution of the physical execution plan corresponding to the historical query statement, the executor needs to access the data source corresponding to the execution plan and may collect statistical information of the data source during the access process. For example, the executor may collect statistical information of a data table while scanning it.

[0035] Based on this, in this embodiment, after obtaining the logical execution plan corresponding to the first query statement, the statistical information of at least one logical plan node in the logical execution plan can be obtained from the statistical information of the collected data source.

[0036] The statistics for any logical plan node refer to the statistics of the data sources accessed by that logical plan node. For example, when a logical plan node is a connection node, the statistics for that connection node are the statistics of the multiple data tables being connected. When a node in the logical execution plan is a filter node, the statistics for that filter node are the statistics of the data tables being filtered, which will not be elaborated further.

[0037] After determining the statistical information of at least one logical plan node, cost-based optimization operations can be performed on the logical execution plan based on this information. During this process, multiple combinations of the at least one logical plan node can be determined based on their statistical information and preset optimization rules, and the query cost of each combination can be calculated. After determining the query costs of the various combinations, the combination with the lowest execution cost can be selected as the final logical execution plan. The optimizer can optimize the logical execution plan based on different optimization rules according to different node types. The following will use join nodes and semi-join nodes as examples for illustrative purposes.

[0038] If the join node includes inner join functionality, the optimizer can identify multiple tables corresponding to the inner join and combine these tables in different orders to obtain multiple inner join combinations. The optimizer can determine the query cost of each of these inner join combinations based on the statistical information of each table. After determining the query cost of each inner join combination, the optimizer can select the inner join combination with the lower query cost as the optimized inner join combination. In some embodiments, to shorten execution time, a threshold of 3 tables for the number of inner joins can be set. If the number of tables in the inner join is greater than 3, the inner join cost calculation operation is not performed, and a calculation failure result is returned.

[0039] In this implementation, for join nodes, the optimizer can determine whether the left and right tables can be swapped during the execution of the join node based on the join node's left and right table swapping attribute. If the semantics of the join node do not include operators such as left join or right join, then it can be determined that the join node can swap the left and right tables during execution. In this implementation, the optimizer can identify multiple tables to be joined and determine the distribution type based on the statistical information of these tables; the distribution type includes broadcast distribution or hash distribution. Broadcast distribution means that the sending node copies all rows of the data to be distributed and sends the copy result to the receiving node. Hash distribution means that the sending node uses a hash function on one or more columns of the rows of the data to be distributed and sends the row to the receiving node based on the calculation result of the hash function. Considering network and memory costs, the distribution type of the table can be determined as broadcast distribution when the number of data rows in the table being distributed is less than a set threshold (e.g., 100,000 rows), and otherwise as hash distribution. In other words, for join nodes where the left and right tables can be interchanged, the distributed tables can be combined based on their statistical information to obtain several join combinations, such as right table broadcast distribution, left table broadcast distribution, right table hash distribution, and left table hash distribution. After determining these join combinations, the query cost of each combination is determined based on the statistical information of the joined tables. Then, the optimizer can select the join combination with the lower query cost as the optimized join combination for that join node.

[0040] For half-join nodes where left and right tables cannot be interchanged, the optimizer identifies multiple tables to be joined and determines the distribution type based on the statistics of the assigned right table. For example, if the number of rows in the right table is less than a set threshold (e.g., 100,000 rows), the distribution type is determined to be broadcast; otherwise, it is hash distribution. That is, for half-join nodes where left and right tables cannot be interchanged, the following half-join combinations can be obtained: right table broadcast distribution and right table hash distribution. After determining multiple half-join combinations, the query cost of each combination is determined based on the statistics of the left and right tables. Then, the optimizer selects the half-join combination with the lower query cost as the optimized join combination for that half-join node.

[0041] In this embodiment, after obtaining the logical execution plan of the query statement, the optimizer extracts the statistical information of each logical plan node in the logical execution plan from the collected statistical information of the data source. Based on this statistical information, the optimizer performs cost-based optimization operations on the logical execution plan. The data source statistics include those collected from the data source when the executor executes the physical execution plan of the specified query statement, reducing reliance on statistics collected by the data source itself. Furthermore, the optimizer can flexibly obtain the statistical information of logical plan nodes when querying various data sources, thus adapting to scenarios involving queries from multiple different data sources and improving the universality of the query optimization method. In addition, when performing cost-based optimization operations on the logical execution plan, the optimizer can dynamically determine the broadcast type of joins and semi-joins based on the statistical information of the joined tables, and can determine the inner join order of multiple tables based on the statistical information of the joined tables. This effectively reduces the risk of memory overflow in the joined distributed nodes and improves query execution efficiency.

[0042] In some exemplary embodiments, before obtaining the statistical information of the at least one logical plan node from the collected statistics of the data source, the optimizer may further determine whether the data source type is a target type with statistical capabilities. If not, the data source does not have statistical capabilities, and the optimizer may determine the aforementioned first statistical information as the statistical information of the data source. The first statistical information is collected by the executor from the data source when executing the physical execution plan of the specified query statement.

[0043] This statistical capability, independent of data sources, is suitable for scenarios involving queries from multiple different data sources. Optionally, if the data source is a target type with statistical capabilities, then the data source possesses statistical capabilities, and the optimizer can determine the data source's statistical information based on the first statistical information and the second statistical information collected from the data source. In this implementation, the data source's statistical information is partly collected by the executor and partly obtained from the data source's own statistics, making the statistical data more accurate and thus improving the execution efficiency of the final logical execution plan obtained through optimization.

[0044] In some exemplary embodiments, when the executor executes any query statement in the specified query statement, the optimizer can determine the target data source involved in the query statement, and when the executor executes the physical execution plan corresponding to the query statement, it can use the executor to collect statistical information of the target data source. For example, the optimizer can send an instruction to the executor to collect statistical information of the target data source, so that the executor can complete the collection operation of statistical information of the target data source.

[0045] In some alternative embodiments, since multi-table joins are one of the more important and time-consuming database operators, statistical information collection operations on the joined data sources can be initiated when the logical execution plan includes multi-table join related operators, in order to avoid unnecessary communication interference.

[0046] In some embodiments A1, when the executor executes the physical execution plan corresponding to the query statement, the optimizer can determine whether the logical execution plan of the query statement contains a connection node; if it contains a connection node, the optimizer can use the executor to collect statistical information of the target data source when the executor executes the physical execution plan corresponding to the query statement.

[0047] In some embodiments A2, after obtaining the physical execution plan corresponding to the query statement, the executor can determine whether the physical execution plan contains a join operation; if it contains a join operation, the executor can collect statistical information of the target data source when executing the physical execution plan.

[0048] The collected statistical information from the target data sources can be stored locally on the database server or on a cloud server for later use. As the number of query operations increases, the statistical information from different data sources can be continuously improved and dynamically updated according to the actual situation of the data sources, thereby providing more reliable data support to the optimizer.

[0049] After obtaining the statistical information of the data source based on the above implementation method, a logical execution plan corresponding to the query request can be generated when a query request is received subsequently, and the query cost of the logical execution plan can be calculated based on the statistical information of the data source. The following will continue to use the first query statement as an example for illustrative purposes. After generating the logical execution plan of the first query statement, the optimizer can obtain the statistical information of at least one logical plan node in the logical execution plan from the collected statistical information of the data source. Optionally, the optimizer can traverse the syntax tree corresponding to the first query statement to obtain the logical expression of each of the at least one logical plan node. The optimizer can calculate the statistical information of the data source based on the logical expression of each of the at least one logical plan node to obtain the statistical information of each of the at least one logical plan node. For example, the statistical information of the filter node can be predicted based on the filter expression of the filter node and the statistical information of the data source.

[0050] After determining the statistical information of each logical node, the optimizer can calculate the query cost of the logical execution plan.

[0051] Optionally, when the optimizer determines the query cost of the logical execution plan based on the statistical information of each of the at least one logical plan node, it may calculate the cost of each of the at least one logical plan node separately, and obtain the query cost of the physical logical execution plan by combining the costs of each of the at least one logical plan node.

[0052] The computational cost required for different logical plan nodes varies, as illustrated below.

[0053] Optionally, the filtering node is a scalar computation node, and its computational cost needs to be determined. The optimizer can determine the computational cost of the filtering node based on the statistics of the data source being filtered (e.g., the total amount of data in the data source).

[0054] Optionally, the aggregation node needs to cache the aggregated data and perform aggregation calculations. The optimizer can determine the computational and memory costs of the aggregation node based on the statistics of the corresponding aggregated data source (such as the total amount of data in the data source, row-level statistics, and column-level statistics).

[0055] Optionally, the connection node involves data computation, right table data distribution, and right table data caching, and the memory cost required for right table hash distribution in broadcast mode is related to the number of task executions. Therefore, the optimizer can determine the computational and memory costs of the connection node based on the statistics of the connected data source corresponding to the connection node and the number of task executions for the connection operation. The number of task executions corresponds to the number of rows in the right table. If the right table contains N rows of data, then the N rows of data from the right table need to be broadcast to the left table node containing the left table, and the required number of task executions is N, where N is a positive integer. In some embodiments, for ease of calculation, the number of task executions can be set to a fixed value, for example, 20.

[0056] Optionally, the interaction node is used to implement inter-process communication. The optimizer can determine the network cost of the interaction node based on the statistics of the data source being interacted with and the number of tasks executed in the interaction operation.

[0057] After determining the cost of at least one logical plan node in the logical execution plan based on the above implementation method, the query cost of the physical logical execution plan can be obtained by weighted summation based on the cost of each of the at least one logical plan node.

[0058] Figure 2 The query optimization method provided in the embodiments of this application is further illustrated, such as... Figure 2After receiving a query statement, the database server parses it to obtain an Abstract Syntax Tree (AST) and generates a logical execution plan based on the AST. The logical execution plan includes join nodes and semi-join nodes. The optimizer performs cost-based optimization operations on the logical execution plan. The optimizer determines whether the data source provides statistics. If the data source provides statistics, these statistics are used as the statistics for the scanned tables. Otherwise, statistics from previous join queries are used as the statistics for the scanned tables.

[0059] like Figure 2 As shown, after determining the statistical information, the optimizer can perform cost calculation. If the join node includes inner join functionality, it checks whether the number of tables in the inner join is greater than a specified number (e.g., 3). If it is greater than the specified number, it skips the process and does not perform the inner join cost calculation. If it is less than or equal to the specified number, it runs the inner join order rules, generates multiple sorting combinations of the joined tables, and calculates the cost of each sorting combination based on the statistical information of the joined tables.

[0060] For connected nodes, a connection distribution type rule can be executed to determine whether left and right table swapping is possible based on the semantics of the connected node. If left and right table swapping is determined to be possible, the distribution type (broadcast distribution or hash distribution) can be determined based on the row-level statistics of the left and right tables. For example, when distributing the right table to the left table, the distribution type (broadcast distribution or hash distribution) can be determined based on the row-level statistics of the right table. Similarly, when distributing the left table to the right table, the distribution type (broadcast distribution or hash distribution) can be determined based on the row-level statistics of the left table. After determining the distribution type, combinations such as left table broadcast distribution, right table broadcast distribution, left table hash distribution, and right table hash distribution can be generated, and the cost of each combination can be calculated based on the statistical information of the tables in each combination.

[0061] For semi-join nodes, the distribution type of the right table can be determined as broadcast distribution or hash distribution based on the row-level statistics of the right table. After determining the distribution type, combinations such as right table broadcast distribution and right table hash distribution can be generated, and the cost of each combination can be calculated based on the statistics of the tables in each combination.

[0062] In the cost calculation process described above, if the statistical information of each scanned table can be obtained, the query cost of each join node can be successfully calculated, and the combination with the lower cost can be selected as the optimal execution plan. Conversely, if the cost calculation fails (e.g., the left table lacks statistical information), the final execution plan can be determined based on the number of rows in the right table. Figure 2As shown, if the number of rows in the right table is less than the specified number of rows, the logical execution plan of the broadcast distribution type is determined as the better execution plan. If the number of rows in the right table is greater than or equal to the specified number of rows, the logical execution plan of the hash distribution type is adopted as the better execution plan, thereby reducing the memory and network costs incurred in the connection process and improving connection efficiency.

[0063] Figure 3 This is a flowchart illustrating a query optimization method provided in another exemplary embodiment of this application. When executed on the executor side, this method may include, for example... Figure 3 The steps shown are as follows:

[0064] Step 301: When executing the physical execution plan corresponding to the query statement, collect statistical information of the scanned data source.

[0065] Step 302: Provide the collected statistical information of the data source to the optimizer so that the optimizer can perform cost-based optimization operations on the logical execution plan of the received query statement based on the statistical information of the data source.

[0066] The execution entity in this application embodiment can be an executor. This executor runs on the database server, and is used to obtain an optimized logical execution plan from the optimizer, convert the logical execution plan into a physical execution plan, and based on the physical execution plan, execute query operations in the data source (e.g., a physical database such as a disk), and return the query results to the database client.

[0067] In this implementation, the executor can scan the data source while performing a query operation on the data source, and collect statistical information from the data source during the scanning process. For example, it can scan all the data in the data source to obtain the total amount of data in the data source. For example, it can scan the row records of the data source row by row to obtain row-level statistical information of the data source. Or, for example, it can perform column-by-column statistics on large values, small values, and null values ​​in the data source to obtain column-level statistical information of the data source, which will not be elaborated further. The collected statistical information can be stored in a specified location (e.g., a database server or a cloud server) for use by the optimizer.

[0068] In some optional embodiments, the executor can determine whether the physical execution plan contains join operations during the physical execution planning process. If join operations are included, the executor can collect statistical information from the data sources while executing the physical execution plan. That is, it collects statistical information from the data sources involved in frequent and time-consuming multi-table join operations. Based on this implementation, the executor initiates information statistics operations when it determines that the query statement contains dispatch type operations. This is beneficial for subsequent optimization of time-consuming join operations based on statistical information, and it can also reduce unnecessary communication interference during the current query process, thereby improving query efficiency.

[0069] In this implementation, when the executor executes the physical execution plan of a query statement, it collects statistical information from the data source to perform cost-based optimization operations on subsequent query plans, reducing reliance on statistical information collected by the data source itself. Furthermore, it can flexibly obtain statistical information from logical plan nodes when querying various types of data sources, thus facilitating adaptation to scenarios involving queries from multiple different data sources and improving the universality of the query optimization method.

[0070] It should be noted that the execution subject of each step of the method provided in the above embodiments can be the same device, or the method can be executed by different devices. For example, the execution subject of steps 101 to 104 can be device A; or the execution subject of steps 101 and 102 can be device A, and the execution subject of step 103 can be device B; and so on.

[0071] Furthermore, some processes described in the above embodiments and accompanying drawings include multiple operations appearing in a specific order. However, it should be clearly understood that these operations may not be executed in the order they appear herein, or they may be executed in parallel. The operation numbers, such as 101, 102, etc., are merely used to distinguish different operations and do not represent any execution order. Additionally, these processes may include more or fewer operations, and these operations may be executed sequentially or in parallel. It should be noted that the descriptions such as "first" and "second" in this document are used to distinguish different messages, devices, modules, etc., and do not represent a sequential order, nor do they limit "first" and "second" to different types.

[0072] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0073] Figure 4 This illustration shows a schematic diagram of the structure of a server provided in an exemplary embodiment of this application, which is suitable for the query optimization method provided in the foregoing embodiments. For example... Figure 4 As shown, the server includes: a memory 401 and a processor 402.

[0074] Memory 401 is used to store computer programs and can be configured to store various other data to support operations on the server. Examples of this data include instructions for any application or method used to operate on the server.

[0075] In some exemplary embodiments, processor 402, coupled to memory 401, is configured to execute a computer program in memory 401 for: obtaining a logical execution plan for a first query statement; the logical execution plan comprising at least one logical plan node; obtaining statistical information of the at least one logical plan node from collected statistical information from a data source; the statistical information from the data source including: first statistical information collected from the data source when the physical execution plan of the specified query statement is executed by the executor; and performing cost-based optimization operations on the logical execution plan based on the statistical information of the at least one logical plan node.

[0076] Optionally, before obtaining the statistical information of the at least one logical plan node from the collected statistical information of the data source, the processor 402 is further configured to: determine whether the type of the data source is a target type with statistical capabilities; if not, determine the first statistical information as the statistical information of the data source; if yes, determine the statistical information of the data source based on the first statistical information and the second statistical information collected from the data source.

[0077] Optionally, before obtaining the statistical information of the at least one logical plan section from the statistical information of the data table, the processor 402 is further configured to: for any second query statement in the specified query statement, determine the target data source to be scanned for executing the query operation of the second query statement according to the logical execution plan of the second query statement; and collect the statistical information of the target data source using the executor when the executor executes the physical execution plan corresponding to the second query statement.

[0078] Optionally, when the processor 402 collects statistical information of the target data source using the executor while the executor executes the physical execution plan corresponding to the second query statement, it specifically performs the following: determines whether the logical execution plan of the second query statement contains a connection node; if it contains the connection node, it collects statistical information of the target data source using the executor while the executor executes the physical execution plan corresponding to the second query statement.

[0079] Optionally, when the processor 402 obtains the statistical information of the at least one logical plan node from the collected statistical information of the data source, it specifically performs the following steps: traversing the syntax tree corresponding to the first query statement to obtain the logical expression of each of the at least one logical plan node; and calculating the statistical information of the data source based on the logical expression of each of the at least one logical plan node to obtain the statistical information of each of the at least one logical plan node.

[0080] Optionally, when the processor 402 performs cost-based optimization operations on the logical execution plan based on the statistical information of the at least one logical plan node, it specifically performs at least one of the following operations: determining the computational cost of the filtering node based on the statistical information of the filtered data source corresponding to the filtering node; determining the computational cost and memory cost of the aggregation node based on the statistical information of the aggregated data source corresponding to the aggregation node; determining the computational cost and memory cost of the connection node based on the statistical information of the connected data source corresponding to the connection node and the number of task executions of the connection operation; and determining the network cost of the interaction node based on the statistical information of the interacted data source corresponding to the interaction node and the number of task executions of the interaction operation.

[0081] Accordingly, embodiments of this application also provide a computer-readable storage medium storing a computer program, which, when executed, can perform the steps that can be executed by the server in the above-described method embodiments. This computer program can be implemented as an optimizer application running on a database server.

[0082] In some other exemplary embodiments, processor 402, coupled to memory 401, is configured to execute a computer program in memory 401 for: collecting statistics of scanned data sources while executing a physical execution plan corresponding to a query statement; and providing the collected statistics of the data sources to an optimizer so that the optimizer performs cost-based optimization operations on the logical execution plan of the received query statement based on the statistics of the data sources.

[0083] Optionally, when the processor 402 collects statistical information of the scanned data source while executing the physical execution plan corresponding to the query statement, it specifically determines whether the physical execution plan contains a join operation; if it contains a join operation, it collects statistical information of the data source while executing the physical execution plan.

[0084] Accordingly, embodiments of this application also provide a computer-readable storage medium storing a computer program, which, when executed, can perform the steps that can be executed by the server in the above-described method embodiments. This computer program can be implemented as an executor application running on a database server.

[0085] Furthermore, such as Figure 4 As shown, the server also includes other components such as communication component 403 and power supply component 404. Figure 4 The diagram only shows some components and does not mean that the server only includes... Figure 4 The components shown.

[0086] The memory 401 can be implemented by any type of volatile or non-volatile storage device or a combination thereof, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic storage, flash memory, magnetic disk or optical disk.

[0087] The communication component 403 is configured to facilitate wired or wireless communication between the device containing the communication component and other devices. The device containing the communication component can access wireless networks based on communication standards, such as Wi-Fi, 2G (e.g., Global System for Mobile Communications (GSM)), 3G (e.g., Wideband Code Division Multiple Access (WCDMA), 4G (e.g., Long Term Evolution (LTE)), 4G+ (e.g., LTE-Advanced (LTE-A)), or 5G (5th Generation Mobile Communication Technology), or combinations thereof. In one exemplary embodiment, the communication component receives broadcast signals or broadcast-related information from an external broadcast management system via a broadcast channel. In one exemplary embodiment, the communication component may be implemented based on Near Field Communication (NFC), Radio Frequency Identification (RFID), Infrared Data Association (IrDA), Ultra Wideband (UWB), Bluetooth (BT), and other technologies.

[0088] The power supply component 404 is used to provide power to various components of the device in which the power supply component is located. The power supply component may include a power management system, one or more power supplies, and other components associated with generating, managing, and distributing power to the device in which the power supply component is located.

[0089] In this embodiment, after the optimizer obtains the logical execution plan of the query statement, it extracts the statistical information of each logical plan node in the logical execution plan from the collected statistical information of the data source. Based on the statistical information of each logical plan node, it performs cost-based optimization operations on the logical execution plan. The statistical information of the data source includes the statistical information collected from the data source when the executor executes the physical execution plan of the specified query statement, reducing the reliance on statistical information collected by the data source itself. Furthermore, it can flexibly obtain the statistical information of logical plan nodes when querying various types of data sources, thus facilitating adaptation to scenarios involving queries from multiple different data sources and improving the universality of the query optimization method.

[0090] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0091] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0092] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0093] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0094] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0095] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0096] Computer-readable media includes both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0097] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0098] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A query optimization method, comprising: Obtain the logical execution plan for the first query statement; The logical execution plan contains at least one logical plan node; Determine whether the data source type is a target type with statistical capabilities; If so, then based on the second statistical information, obtain the statistical information corresponding to the at least one logical plan node; The second statistical information is a collection of metadata information provided by the data source to describe the tables, rows, and columns in the data source; If not, then based on the first statistical information, obtain the statistical information corresponding to the at least one logical plan node; The first statistical information is a collection of metadata information describing the tables, rows, and columns in the data source, collected from the data source when the executor executes the physical execution plan of the specified query statement; The specified query statement includes: query statements executed within the historical time period prior to receiving the first query statement; Based on the statistical information corresponding to the at least one logical plan node, a cost-based optimization operation is performed on the logical execution plan.

2. The method according to claim 1, wherein if so, obtaining statistical information corresponding to the at least one logical plan node based on the second statistical information includes: If so, then based on the first statistical information and the second statistical information, obtain the statistical information corresponding to the at least one logical plan node.

3. The method according to claim 1, further comprising, before obtaining the statistical information corresponding to the at least one logical plan node based on the first statistical information if no, the method includes: For any second query statement in the specified query statement, the target data source to be scanned for executing the query operation of the second query statement is determined according to the logical execution plan of the second query statement; When the executor executes the physical execution plan corresponding to the second query statement, the executor collects the first statistical information of the target data source.

4. The method according to claim 3, wherein when the executor executes the physical execution plan corresponding to the second query statement, the executor collects first statistical information of the target data source, including: Determine whether the logical execution plan of the second query statement contains join nodes; If the connection node is included, then when the executor executes the physical execution plan corresponding to the second query statement, the executor collects the first statistical information of the target data source.

5. The method according to claim 1, wherein obtaining statistical information corresponding to the at least one logical plan node from the first statistical information includes: Traverse the syntax tree corresponding to the first query statement to obtain the logical expression of each of the at least one logical plan node; Based on the logical expressions of each of the at least one logical plan node, the first statistical information of the data source is calculated to obtain the statistical information corresponding to each of the at least one logical plan node.

6. The method according to any one of claims 1-5, wherein a cost-based optimization operation is performed on the logical execution plan based on statistical information corresponding to the at least one logical plan node, comprising at least one of the following: The computational cost of the filtering node is determined based on the statistical information of the data source being filtered corresponding to the filtering node. Based on the statistical information of the corresponding aggregated data source of the aggregation node, determine the computational cost and memory cost of the aggregation node. The computational cost and memory cost of the connection node are determined based on the statistical information of the connected data source corresponding to the connection node and the number of task executions of the connection operation. The network cost of the interactive node is determined based on the statistical information of the data source being interacted with and the number of tasks executed in the interactive operation.

7. A query optimization method applied to an executor, comprising: When executing the physical execution plan corresponding to the specified query statement, the first statistical information of the scanned data source is collected. The first statistical information is a set of metadata information describing the tables, rows and columns in the data source collected from the data source when the executor executes the physical execution plan of the specified query statement. The first statistical information collected from the data source is provided to the optimizer so that the optimizer can determine whether the type of the data source is a target type with statistical capabilities; If yes, then according to the second statistical information, obtain the statistical information corresponding to at least one logical plan node contained in the logical execution plan of the first query statement. The second statistical information is a set of metadata information provided by the data source to describe the tables, rows and columns in the data source. If no, then according to the first statistical information, obtain the statistical information corresponding to the at least one logical plan node, and according to the statistical information corresponding to the at least one logical plan node, perform cost-based optimization operations on the logical execution plan. The first query statement is a query statement that will be executed within a future time period following the specified query statement.

8. The method according to claim 7, wherein when executing the physical execution plan corresponding to the specified query statement, collecting first statistical information of the scanned data source includes: Determine whether the physical execution plan includes a connection operation; If a connection operation is involved, first statistics of the data source are collected when the physical execution plan is executed.

9. A server, comprising: Memory and processor; The memory is used to store one or more computer instructions; The processor is configured to execute one or more computer instructions for performing the steps of the method according to any one of claims 1-8.

10. A computer-readable storage medium storing a computer program, which, when executed by a processor, enables the query optimization method according to any one of claims 1-8.

Images