Query execution plan generation method and device, equipment and storage medium

Abstract

The invention discloses a query execution plan generation method and device, equipment and a storage medium. The method comprises the following steps: generating a plurality of query execution plans corresponding to query statements based on a plurality of optimizers; estimating the cost of each query execution plan, and determining the query cost of each query execution plan; inputting query features corresponding to the query execution plans into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to the query execution plans according to the query features corresponding to the query execution plans, the execution data comprises execution time and execution resources; and determining a target query execution plan according to the query cost and the execution data corresponding to each query execution plan. According to the technical scheme, the target query execution plan with the optimal performance is determined in the query execution plans corresponding to the query statement.

Description

Technical Field

[0001] Embodiments of the present invention relate to the field of big data technology, and in particular to a method, apparatus, device, and storage medium for generating a query execution plan. Background Art

[0002] Structured Query Language (SQL) is a database query and programming language. SQL statements are executed on a relational database management system (RDBMS) through an executor. The executor is generally capable of performing a wide range of queries, including but not limited to join operations, group operations, trigger operations, and function execution in query conditions.

[0003] In the prior art, an optimizer is usually used to generate a query execution plan corresponding to an SQL statement and execute the plan accordingly.

[0004] However, complex queries usually involve operations such as joining multiple tables, nested subqueries, grouping and aggregation. The number of execution plans that the optimizer needs to consider increases exponentially, making it difficult to determine the optimal execution plan corresponding to the SQL statement. Summary of the Invention

[0005] The present invention provides a method, apparatus, device and storage medium for generating a query execution plan, so as to determine a target query execution plan with the best performance among multiple query execution plans corresponding to a query statement.

[0006] In a first aspect, an embodiment of the present invention provides a method for generating a query execution plan, comprising:

[0007] Generate multiple query execution plans corresponding to query statements based on multiple optimizers;

[0008] Determining the query cost of each query execution plan by performing cost estimation on each query execution plan;

[0009] Inputting query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources;

[0010] A target query execution plan is determined according to the query cost and the execution data corresponding to each query execution plan.

[0011] The technical solution of an embodiment of the present invention provides a method for generating a query execution plan, including: generating multiple query execution plans corresponding to query statements based on multiple optimizers; determining the query cost of each query execution plan by performing cost estimation on each query execution plan; inputting query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan based on the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources; and determining a target query execution plan based on the query cost and the execution data corresponding to each query execution plan. The above technical solution can first use multiple different optimizers to generate multiple query execution plans corresponding to the query statement. Secondly, the query cost of each query execution plan can be determined by performing cost estimation on each query execution plan. The query features corresponding to each query execution plan can also be input into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plan, and a more accurate prediction of the execution time and execution resources can be achieved based on the execution prediction model. The query performance of each query execution plan can be determined according to the query cost and execution data corresponding to each query execution plan, and the target query execution plan can be determined according to the query performance, so as to achieve the target query execution plan with the best performance among the multiple query execution plans corresponding to the query statement.

[0012] Furthermore, multiple query execution plans corresponding to the query statements are generated based on multiple optimizers, including:

[0013] A rule-based optimizer, a cost-based optimizer, a heuristic-based optimizer and a distributed optimizer are respectively used to generate a first query execution plan, a second query execution plan, a third query execution plan and a fourth query execution plan corresponding to the query statement.

[0014] Furthermore, by performing cost estimation on each query execution plan, the query cost of each query execution plan is determined, including:

[0015] For each query execution plan, determining a reading cost, a processing cost, and a sorting cost of the query execution plan according to statistical characteristics of the query execution plan;

[0016] The query cost of the query execution plan is determined according to the read cost, the processing cost, and the sorting cost of the query execution plan.

[0017] Furthermore, the step of executing the training of the prediction model includes:

[0018] Obtain historical query statements, and determine historical query features and historical execution data corresponding to the historical query statements;

[0019] The historical query features of the historical query statements are used as training inputs, and the historical execution data is used to guide training outputs to perform network training to obtain the execution prediction model.

[0020] Furthermore, determining a target query execution plan according to the query cost and the execution data corresponding to each query execution plan includes:

[0021] For each query execution plan, normalizing the query cost, the execution time, and the execution resources corresponding to the query execution plan;

[0022] performing weighted summation of the normalized query cost, the normalized execution time, and the normalized execution resources based on the weights corresponding to the query cost, the execution time, and the execution resources to obtain the query performance of the query execution plan;

[0023] The query execution plan corresponding to the optimal query performance is determined as the target query execution plan based on the query performance of each query execution plan.

[0024] Furthermore, after determining a target query execution plan according to the query cost and the execution data corresponding to each query execution plan, the method further includes:

[0025] The target query execution plan is executed, and execution resources and / or execution time of the target query execution plan are adjusted according to real-time resources and execution progress.

[0026] In a second aspect, an embodiment of the present invention further provides a device for generating a query execution plan, comprising:

[0027] A generation module is used to generate multiple query execution plans corresponding to query statements based on multiple optimizers;

[0028] An estimation module, configured to determine the query cost of each query execution plan by performing cost estimation on each query execution plan;

[0029] a determination module, configured to input query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources;

[0030] An execution module is configured to determine a target query execution plan according to the query cost and the execution data corresponding to each query execution plan.

[0031] In a third aspect, an embodiment of the present invention further provides an electronic device, characterized in that the electronic device includes:

[0032] at least one processor; and a memory communicatively coupled to the at least one processor;

[0033] The memory stores a computer program that can be executed by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the query execution plan generation method as described in any one of the first aspects.

[0034] In a fourth aspect, an embodiment of the present invention further provides a storage medium comprising computer-executable instructions, wherein the computer-executable instructions, when executed by a computer processor, are used to execute the query execution plan generation method as described in any one of the first aspects.

[0035] In a fifth aspect, the present application provides a computer program product, which includes computer instructions. When the computer instructions are executed on a computer, the computer executes the query execution plan generation method provided in the first aspect.

[0036] It should be noted that the aforementioned computer instructions may be stored in whole or in part on a computer-readable storage medium. The computer-readable storage medium may be packaged together with the processor of the query execution plan generation device, or may be packaged separately from the processor of the query execution plan generation device, and this application does not limit this.

[0037] The descriptions of the second, third, fourth and fifth aspects of this application can refer to the detailed description of the first aspect; and the beneficial effects of the descriptions of the second, third, fourth and fifth aspects can refer to the analysis of the beneficial effects of the first aspect, which will not be repeated here.

[0038] In this application, the name of the query execution plan generation device does not limit the device or functional module itself. In actual implementation, these devices or functional modules may appear with other names. As long as the functions of each device or functional module are similar to those of this application, they are within the scope of the claims of this application and their equivalents.

[0039] These and other aspects of the present application will become more readily apparent from the following description. BRIEF DESCRIPTION OF THE DRAWINGS

[0040] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0041] Figure 1 A flowchart of a method for generating a query execution plan provided by an embodiment of the present invention;

[0042] Figure 2 A flowchart of another method for generating a query execution plan provided by an embodiment of the present invention;

[0043] Figure 3 A schematic diagram of the structure of a query execution plan generation device provided by an embodiment of the present invention;

[0044] Figure 4 A schematic structural diagram of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0045] The present invention will be further described in detail below with reference to the accompanying drawings and examples. It will be understood that the specific embodiments described herein are intended only to illustrate the present invention and are not intended to limit the present invention. It should also be noted that, for ease of description, the accompanying drawings only illustrate portions relevant to the present invention, not all structures.

[0046] The term "and / or" in this article is merely a description of the association relationship between associated objects, indicating that three relationships may exist. For example, A and / or B can mean: A exists alone, A and B exist at the same time, and B exists alone.

[0047] The terms "first" and "second" and the like in the specification and drawings of this application are used to distinguish different objects, or to distinguish different processing of the same object, rather than to describe a specific order of objects.

[0048] Furthermore, the terms "including," "having," and any variations thereof, as used in the description of this application are intended to cover non-exclusive inclusions. For example, a process, method, system, product, or apparatus comprising a series of steps or units is not limited to the listed steps or units but may optionally include other steps or units not listed, or may optionally include other steps or units inherent to the process, method, product, or apparatus.

[0049] Before any example embodiments are described in further detail, it should be noted that some example embodiments are described as processes or methods depicted as flowcharts. Although the processes or methods are described in a particular sequential order, many of the processes or methods can be performed in parallel, concurrently or in any order. In addition, the order of the processes or methods can be re-arranged. The processes or methods can be terminated when their operations are completed, but the processes or methods can also end in response to events external to the processes or methods. The processes or methods can correspond to methods, functions, procedures, subroutines, subprograms, etc. When the processes or methods correspond to functions, the functions can be module, segment, component, memory, etc. Furthermore, the embodiments or features of the embodiments can be combined or integrated with each other when not mutually exclusive.

[0050] It should be noted that in the present application, the words "exemplary" and "for example" are used to mean "an example of" or "an example, only. Any implementation or embodiment described as "exemplary" or "for example" in the present application should not be construed as being more important or superior to other embodiments or implementations. In fact, the use of the words "exemplary" or "for example" is intended to present concepts in a particular manner. In the present application, the word "include" is used to mean "comprise" or "consist" of.

[0051] In the description of the present application, "a plurality of" means two or more, unless otherwise specified.

[0052] In the prior art, a rule-based optimizer, a cost-based optimizer, a heuristic-based optimizer or a distributed optimizer can be used to generate a query execution plan corresponding to a query statement.

[0053] The rule-based optimizer is a database query optimization technique that determines the best execution plan for a query through predefined rules. These rules are usually based on algebraic equivalence relations and other heuristic methods to transform and reorganize the execution path of the query. The rule-based optimizer is widely used in early database systems, and its goal is to reduce query execution time and improve query efficiency. The performance of the rule-based optimizer is highly dependent on the integrity and accuracy of the predefined rules, and for complex queries or large-scale data sets, the execution plan generated by the rule-based optimizer may not be optimal. In addition, the increasing number of rules can make the optimizer bloated and less efficient.

[0054] The cost-based optimizer is an advanced database query optimization technique that selects the most efficient execution strategy by evaluating the expected cost of different query execution plans. The cost-based optimizer does not simply rely on predefined rules, but dynamically calculates and selects the optimal query execution plan based on statistical information and cost models. Due to the difficulty of updating statistical information in real time and the limited descriptive ability of statistical information, the cost-based optimizer still faces challenges in terms of statistical information accuracy, cost model applicability and dynamic environment adaptability.

[0055] A heuristic-based optimizer is a technique that uses experience and simple rules to guide the query optimization process. It combines features of rule-based and cost-based optimizers, aiming to improve query execution efficiency through intuitive, empirical methods. This optimizer is simple to implement, has low computational overhead, and can be very efficient in specific scenarios. However, it may not perform as well as cost-based optimization techniques for complex queries and large datasets.

[0056] A distributed optimizer is a tool specifically designed to optimize query execution plans in distributed database systems. Unlike traditional standalone database optimizers, a distributed optimizer considers factors such as the distributed storage characteristics of data, network communication overhead, and the computing power of different nodes to determine the optimal data processing and transmission strategy.

[0057] Although existing optimizers can achieve good results in some aspects, there are still some shortcomings in general.

[0058] Therefore, this application proposes a method for generating a query execution plan to solve the above technical problems.

[0059] Figure 1 A flowchart of a method for generating a query execution plan provided by an embodiment of the present invention is provided. This embodiment is applicable to situations where a query execution plan corresponding to a query statement needs to be generated. The method can be executed by a query execution plan generation device, such as Figure 1 As shown, the specific steps include:

[0060] Step 110: Generate multiple query execution plans corresponding to the query statements based on multiple optimizers.

[0061] Among them, the optimizer may include a rule-based optimizer, a cost-based optimizer, a heuristic-based optimizer, and a distributed optimizer.

[0062] Specifically, multiple optimizers can be used to generate multiple query execution plans corresponding to query statements. For example, a rule-based optimizer, a cost-based optimizer, a heuristic-based optimizer, and a distributed optimizer can be used to generate multiple query execution plans corresponding to query statements.

[0063] In an embodiment of the present invention, multiple different query execution plans corresponding to query statements are generated according to multiple different optimizers, providing a basis for determining an optimal query execution plan.

[0064] Step 120: Determine the query cost of each query execution plan by performing cost estimation on each query execution plan.

[0065] Among them, the query cost is different when the statistical characteristics of the query execution plan are different.

[0066] Specifically, query costs are typically composed of read costs, processing costs, and sorting costs. First, the read costs, processing costs, and sorting costs of each query execution plan can be determined. The sum of these costs is then used to determine the query cost. Specifically, a cost estimate can be performed based on the statistical characteristics of the query execution plan to determine the read costs, processing costs, and sorting costs of the query execution plan.

[0067] In the embodiment of the present invention, the query cost of each query execution plan is determined by performing cost estimation on each query execution plan.

[0068] Step 130: Input the query features corresponding to the query execution plans into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plans according to the query features corresponding to the query execution plans.

[0069] Execution data includes execution time and execution resources. The execution prediction model is trained based on the query characteristics of the query execution plan, as well as the execution time and resources. It is used to determine the execution time and resources based on the query characteristics of the query execution plan. Query characteristics of the query execution plan can include different table join orders, different index selections, and scan methods.

[0070] Specifically, the query features corresponding to the query execution plan can be input into a pre-trained execution prediction model, that is, the table connection order, index selection, and scanning method corresponding to the query execution plan can be input into the pre-trained execution prediction model, so that the execution prediction model can determine the execution time and execution resources corresponding to the query execution plan based on the table connection order, index selection, and scanning method of the query execution plan, thereby realizing the prediction of the execution time and execution resources of the query execution plan.

[0071] In an embodiment of the present invention, query features corresponding to each query execution plan are input into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plan, and a more accurate prediction of execution time and execution resources is achieved based on the execution prediction model.

[0072] Step 140: Determine a target query execution plan according to the query cost and the execution data corresponding to each query execution plan.

[0073] Specifically, after determining the query cost and execution data corresponding to each query execution plan, the query performance of the query execution plan can be determined based on the query cost and execution data, and the query execution plans can be sorted according to the query performance, and the query execution plan corresponding to the optimal query performance in the sorting results can be determined as the target query execution plan.

[0074] In an embodiment of the present invention, it is achieved to determine a target query execution plan with the best performance among multiple query execution plans corresponding to a query statement.

[0075] The query execution plan generation method provided by an embodiment of the present invention includes: generating multiple query execution plans corresponding to query statements based on multiple optimizers; determining the query cost of each query execution plan by performing cost estimation on each query execution plan; inputting query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan based on the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources; and determining a target query execution plan based on the query cost and the execution data corresponding to each query execution plan. The above technical solution can first use multiple different optimizers to generate multiple query execution plans corresponding to the query statement. Secondly, the query cost of each query execution plan can be determined by performing cost estimation on each query execution plan. The query features corresponding to each query execution plan can also be input into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plan, and a more accurate prediction of the execution time and execution resources can be achieved based on the execution prediction model. The query performance of each query execution plan can be determined according to the query cost and execution data corresponding to each query execution plan, and the target query execution plan can be determined according to the query performance, so as to achieve the target query execution plan with the best performance among the multiple query execution plans corresponding to the query statement.

[0076] Figure 2 This is a flowchart of another method for generating a query execution plan provided by an embodiment of the present invention. This embodiment is specific based on the above embodiment. Figure 2 As shown, in this embodiment, the method may further include:

[0077] Step 210: Generate multiple query execution plans corresponding to the query statements based on multiple optimizers.

[0078] In one implementation, step 210 may specifically include:

[0079] A rule-based optimizer, a cost-based optimizer, a heuristic-based optimizer and a distributed optimizer are respectively used to generate a first query execution plan, a second query execution plan, a third query execution plan and a fourth query execution plan corresponding to the query statement.

[0080] Specifically, a rule-based optimizer is used to generate a first query execution plan corresponding to the query statement, a cost-based optimizer is used to generate a second query execution plan corresponding to the query statement, a heuristic-based optimizer is used to generate a third query execution plan corresponding to the query statement, and a distributed optimizer is used to generate a fourth query execution plan corresponding to the query statement.

[0081] In an embodiment of the present invention, multiple different query execution plans corresponding to query statements are generated according to multiple different optimizers, providing a basis for determining an optimal query execution plan.

[0082] Step 220: For each query execution plan, determine the read cost, processing cost, and sorting cost of the query execution plan according to the statistical characteristics of the query execution plan.

[0083] Specifically, first, the statistical characteristics of each query execution plan can be determined, that is, the statistical characteristics of the number of rows and indexes of the table can be determined. Secondly, the statistical characteristics of the number of rows and indexes of the table corresponding to the query execution plan can be input into a pre-trained read cost estimation model. The cost estimation model is obtained based on the statistical characteristics and read costs of the number of rows and indexes of the table corresponding to the historical query execution plan. The read cost of the query execution plan can be determined based on the statistical characteristics of the number of rows and indexes of the table corresponding to the query execution plan. The statistical characteristics of the number of rows and indexes of the table corresponding to the query execution plan can be input into a pre-trained processing cost estimation model. The processing cost model is obtained based on the statistical characteristics and processing costs of the number of rows and indexes of the table corresponding to the query execution plan. The statistical characteristics of the number of rows and indexes of the table corresponding to the query execution plan can be input into a pre-trained sorting cost estimation model. The sorting cost model is obtained based on the statistical characteristics and sorting costs of the table corresponding to the query execution plan. The sorting cost of the query execution plan can be determined based on the statistical characteristics of the number of rows and indexes of the table corresponding to the query execution plan.

[0084] In the embodiment of the present invention, the reading cost, processing cost and sorting cost of each query execution plan are determined by performing cost estimation on each query execution plan.

[0085] Step 230: Determine the query cost of the query execution plan according to the read cost, the processing cost, and the sorting cost of the query execution plan.

[0086] Specifically, after determining the read cost, processing cost, and sorting cost of each query execution plan, the query cost can be determined by summing the read cost, processing cost, and sorting cost. That is, the sum of the read cost, processing cost, and sorting cost of each query execution plan can be determined as the query cost of each query execution plan.

[0087] In the embodiment of the present invention, the query cost of each query execution plan is determined according to the reading cost, processing cost and sorting cost of each query execution plan.

[0088] Step 240: Input the query features corresponding to the query execution plans into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plans according to the query features corresponding to the query execution plans.

[0089] The execution data includes execution time and execution resources.

[0090] As described in the previous embodiment 1, the query features corresponding to the query execution plan can be input into a pre-trained execution prediction model, that is, the table connection order, index selection, and scanning method corresponding to the query execution plan can be input into the pre-trained execution prediction model. The execution prediction model can determine the execution time and execution resources corresponding to the query execution plan based on the table connection order, index selection, and scanning method of the query execution plan, thereby realizing the prediction of the execution time and execution resources of the query execution plan.

[0091] In one embodiment, the step of executing the prediction model training includes:

[0092] Obtain historical query statements, determine historical query features and historical execution data corresponding to the historical query statements; use the historical query features of the historical query statements as training inputs and the historical execution data to guide training outputs for network training to obtain the execution prediction model.

[0093] In an embodiment of the present invention, query features corresponding to each query execution plan are input into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plan, and a more accurate prediction of execution time and execution resources is achieved based on the execution prediction model.

[0094] Step 250: Determine a target query execution plan according to the query cost and the execution data corresponding to each query execution plan.

[0095] In one implementation, step 250 may specifically include:

[0096] For each query execution plan, the query cost, execution time, and execution resources corresponding to the query execution plan are normalized; based on the weights corresponding to the query cost, the execution time, and the execution resources, the normalized query cost, normalized execution time, and normalized execution resources are weightedly summed to obtain the query performance of the query execution plan; based on the query performance of each query execution plan, the query execution plan corresponding to the optimal query performance is determined as the target query execution plan.

[0097] Specifically, after determining the query cost, execution time and execution resources corresponding to each query execution plan, the query performance of the query execution plan can be determined based on the query cost, execution time and execution resources. Specifically, the query cost, execution time and execution resources can be normalized first to obtain the normalized query cost, normalized execution time and normalized execution resources. Secondly, the first weight corresponding to the query cost, the second weight corresponding to the execution time and the third weight corresponding to the execution resources can be determined according to user needs, and the normalized query cost, normalized execution time and normalized execution resources are weighted and summed based on the first weight corresponding to the query cost, the second weight corresponding to the execution time and the third weight corresponding to the execution resources. The summation result is determined as the query performance of the query execution plan. Then, the query execution plans can be sorted from large to small based on the query performance, the maximum query performance is determined as the optimal query performance, and the query execution plan corresponding to the optimal query performance is determined as the target query execution plan, so as to determine the target query execution plan corresponding to the query statement.

[0098] In actual applications, the execution resources of the target query execution plans corresponding to multiple query statements can be used to predict the demand for server resources within a preset time period, such as CPU usage, memory usage, disk I / O, etc., to facilitate server resource scheduling and capacity planning.

[0099] In an embodiment of the present invention, it is achieved to determine a target query execution plan with the best performance among multiple query execution plans corresponding to a query statement.

[0100] Step 260: Execute the target query execution plan, and adjust the execution resources and / or execution time of the target query execution plan according to real-time resources and execution progress.

[0101] Specifically, during the execution of the target query execution plan, the real-time resources of the server and the query progress of the target query execution plan are monitored in real time, and the execution resources and / or execution time of the target query execution plan are adjusted according to the real-time resources and query progress, so as to improve the execution efficiency without affecting the execution of the target query execution plan and realize the optimization of the execution plan.

[0102] In actual applications, during the execution of the target query plan, the query duration and resource usage can also be recorded, and the relevant data can be stored in the log for model optimization of the read cost estimation model, processing cost estimation model, sorting cost estimation model, and execution prediction model.

[0103] In an embodiment of the present invention, during the execution of a target query execution plan, the execution resources and / or execution time of the target query execution plan are adjusted according to real-time resources and execution progress, thereby achieving execution optimization of the query execution plan.

[0104] A method for generating a query execution plan provided by an embodiment of the present invention includes: generating multiple query execution plans corresponding to query statements based on multiple optimizers; for each query execution plan, determining the read cost, processing cost and sorting cost of the query execution plan according to the statistical characteristics of the query execution plan; determining the query cost of the query execution plan according to the read cost, the processing cost and the sorting cost of the query execution plan; inputting query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan; determining a target query execution plan according to the query cost and the execution data corresponding to each query execution plan; executing the target query execution plan, and adjusting the execution resources and / or execution time of the target query execution plan according to real-time resources and execution progress. The above technical solution can first use multiple different optimizers to generate multiple query execution plans corresponding to the query statement. Secondly, it can determine the read cost, processing cost, and sorting cost of each query execution plan by cost estimation for each query execution plan. The query cost of each query execution plan is determined based on the read cost, processing cost, and sorting cost of each query execution plan. The query features corresponding to each query execution plan can also be input into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plan. Based on the execution prediction model, a more accurate prediction of execution time and execution resources can be achieved. The query performance of each query execution plan can then be determined based on the query cost and execution data corresponding to each query execution plan. A target query execution plan can be determined based on the query performance, and the target query execution plan with the best performance can be determined from the multiple query execution plans corresponding to the query statement. By combining the query execution plan generated by the optimizer with cost estimation and execution data prediction, a query execution plan with the best query performance corresponding to the query statement can be determined, balancing performance and flexibility.

[0105] Furthermore, during the execution of the target query execution plan, the execution resources and / or execution time of the target query execution plan are adjusted according to real-time resources and execution progress, thereby achieving execution optimization of the query execution plan.

[0106] Figure 3 This is a schematic diagram of the structure of a query execution plan generation device provided in an embodiment of the present invention. This device is applicable to situations where a query execution plan corresponding to a query statement needs to be generated, thereby improving the efficiency of query execution plan generation. This device can be implemented using software and / or hardware and is generally integrated into an electronic device, such as a computer.

[0107] like Figure 3 As shown, the device includes:

[0108] A generation module 310 is configured to generate multiple query execution plans corresponding to query statements based on multiple optimizers;

[0109] An estimation module 320, configured to determine a query cost of each query execution plan by performing cost estimation on each query execution plan;

[0110] Determination module 330, configured to input query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan based on the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources;

[0111] The execution module 340 is configured to determine a target query execution plan according to the query cost and the execution data corresponding to each query execution plan.

[0112] The query execution plan generation device provided in this embodiment generates multiple query execution plans corresponding to query statements based on multiple optimizers; determines the query cost of each query execution plan by performing cost estimation on each query execution plan; inputs the query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources; and determines a target query execution plan according to the query cost and the execution data corresponding to each query execution plan. The above technical solution can first use multiple different optimizers to generate multiple query execution plans corresponding to the query statement. Secondly, the query cost of each query execution plan can be determined by performing cost estimation on each query execution plan. The query features corresponding to each query execution plan can also be input into a pre-trained execution prediction model, so that the execution prediction model determines the execution data corresponding to the query execution plan, and a more accurate prediction of the execution time and execution resources can be achieved based on the execution prediction model. The query performance of each query execution plan can be determined according to the query cost and execution data corresponding to each query execution plan, and the target query execution plan can be determined according to the query performance, so as to achieve the target query execution plan with the best performance among the multiple query execution plans corresponding to the query statement.

[0113] Based on the above embodiment, the generation module 310 is specifically configured to:

[0114] A rule-based optimizer, a cost-based optimizer, a heuristic-based optimizer and a distributed optimizer are respectively used to generate a first query execution plan, a second query execution plan, a third query execution plan and a fourth query execution plan corresponding to the query statement.

[0115] Based on the above embodiment, the estimation module 320 is specifically configured to:

[0116] For each query execution plan, the read cost, processing cost and sorting cost of the query execution plan are determined according to the statistical characteristics of the query execution plan; and the query cost of the query execution plan is determined according to the read cost, the processing cost and the sorting cost of the query execution plan.

[0117] Based on the above embodiment, the device further includes:

[0118] A training module is used to obtain historical query statements, determine the historical query features and historical execution data corresponding to the historical query statements; use the historical query features of the historical query statements as training input and the historical execution data to guide the training output for network training to obtain the execution prediction model.

[0119] Based on the above embodiment, the execution module 340 is specifically configured to:

[0120] For each query execution plan, the query cost, execution time, and execution resources corresponding to the query execution plan are normalized; based on the weights corresponding to the query cost, the execution time, and the execution resources, the normalized query cost, normalized execution time, and normalized execution resources are weightedly summed to obtain the query performance of the query execution plan; based on the query performance of each query execution plan, the query execution plan corresponding to the optimal query performance is determined as the target query execution plan.

[0121] Based on the above embodiment, the device further includes:

[0122] An adjustment module is used to, after determining a target query execution plan based on the query cost and the execution data corresponding to each query execution plan, execute the target query execution plan, and adjust the execution resources and / or execution time of the target query execution plan based on real-time resources and execution progress.

[0123] The query execution plan generation device provided in the embodiment of the present invention can execute the query execution plan generation method provided in any embodiment of the present invention, and has the corresponding functional modules and beneficial effects of executing the query execution plan generation method.

[0124] It is worth noting that in the embodiment of the above-mentioned query execution plan generation device, the various units and modules included are only divided according to functional logic, but are not limited to the above-mentioned division, as long as the corresponding functions can be achieved; in addition, the specific names of the various functional units are only for the convenience of distinguishing each other, and are not used to limit the scope of protection of the present invention.

[0125] Figure 4 A schematic structural diagram of an electronic device provided by an embodiment of the present invention. Figure 4 A block diagram of an exemplary electronic device 4 suitable for implementing embodiments of the present invention is shown. Figure 4 The electronic device 4 shown is only an example and should not bring any limitation to the functions and scope of use of the embodiments of the present invention.

[0126] like Figure 4 As shown, electronic device 4 is in the form of a general-purpose computing electronic device. Components of electronic device 4 may include, but are not limited to, one or more processors or processing units 16, system memory 28, and a bus 18 connecting various system components (including system memory 28 and processing unit 16).

[0127] Bus 18 represents one or more of several types of bus structures, including a memory bus or memory controller, a peripheral bus, an accelerated graphics port, a processor, or a local bus using any of a variety of bus architectures. Examples of these architectures include, but are not limited to, an Industry Standard Architecture (ISA) bus, a Micro Channel Architecture (MAC) bus, an Enhanced ISA bus, a Video Electronics Standards Association (VESA) local bus, and a Peripheral Component Interconnect (PCI) bus.

[0128] The electronic device 4 typically includes a variety of computer system readable media. These media can be any available media that can be accessed by the electronic device 4, including volatile and non-volatile media, removable and non-removable media.

[0129] The system memory 28 may include computer system readable media in the form of volatile memory, such as random access memory (RAM) 30 and / or cache memory 32. The electronic device 4 may further include other removable / non-removable, volatile / non-volatile computer system storage media. By way of example only, the storage system 34 may be configured to read and write non-removable, non-volatile magnetic media ( Figure 4 Not shown, often called a "hard drive"). Although Figure 4 Not shown, a magnetic disk drive for reading and writing to a removable non-volatile magnetic disk (e.g., a "floppy disk"), and an optical disk drive for reading and writing to a removable non-volatile optical disk (e.g., a CD-ROM, DVD-ROM, or other optical media) may be provided. In these cases, each drive may be connected to bus 18 via one or more data media interfaces. System memory 28 may include at least one program product having a set (e.g., at least one) of program modules configured to perform the functions of various embodiments of the present invention.

[0130] A program / utility 40 having a set (at least one) of program modules 42 may be stored, for example, in system memory 28. Such program modules 42 include, but are not limited to, an operating system, one or more application programs, other program modules, and program data, each of which, or some combination thereof, may include an implementation of a network environment. Program modules 42 generally perform the functions and / or methods of the embodiments described herein.

[0131] The electronic device 4 may also communicate with one or more external devices 14 (e.g., a keyboard, a pointing device, a display 24, etc.), one or more devices that enable a user to interact with the electronic device 4, and / or any device that enables the electronic device 4 to communicate with one or more other computing devices (e.g., a network card, a modem, etc.). Such communication may be performed via an input / output (I / O) interface 22. Furthermore, the electronic device 4 may also communicate with one or more networks (e.g., a local area network (LAN), a wide area network (WAN), and / or a public network, such as the Internet) via a network adapter 20. Figure 4 As shown, the network adapter 20 communicates with other modules of the electronic device 4 via the bus 18. Figure 4 Not shown, other hardware and / or software modules may be used in conjunction with the electronic device 4, including but not limited to microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0132] The processing unit 16 executes various functional applications and page displays by running programs stored in the system memory 28, for example, implementing the method for generating a query execution plan provided in an embodiment of the present invention, which includes:

[0133] Generate multiple query execution plans corresponding to query statements based on multiple optimizers;

[0134] Determining the query cost of each query execution plan by performing cost estimation on each query execution plan;

[0135] Inputting query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources;

[0136] A target query execution plan is determined according to the query cost and the execution data corresponding to each query execution plan.

[0137] Of course, those skilled in the art will appreciate that the processor may also implement the technical solution of the query execution plan generation method provided by any embodiment of the present invention.

[0138] An embodiment of the present invention provides a computer-readable storage medium having a computer program stored thereon. When the program is executed by a processor, the method for generating a query execution plan provided in an embodiment of the present invention is implemented, for example, and the method includes:

[0139] Generate multiple query execution plans corresponding to query statements based on multiple optimizers;

[0140] Determining the query cost of each query execution plan by performing cost estimation on each query execution plan;

[0141] Inputting query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources;

[0142] A target query execution plan is determined according to the query cost and the execution data corresponding to each query execution plan.

[0143] The computer storage medium of the embodiment of the present invention can adopt any combination of one or more computer-readable media. The computer-readable medium can be a computer-readable signal medium or a computer-readable storage medium. The computer-readable storage medium can be, for example, but not limited to: an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, device or component, or any combination of the above. More specific examples (non-exhaustive list) of computer-readable storage media include: an electrical connection with one or more wires, a portable computer disk, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or flash memory), an optical fiber, a portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the above. In this document, a computer-readable storage medium can be any tangible medium containing or storing a program that can be used by or in combination with an instruction execution system, device or device.

[0144] A computer-readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, which carries computer-readable program code. Such propagated data signals may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A computer-readable signal medium may also be any computer-readable medium other than a computer-readable storage medium that can transmit, propagate, or transport a program for use by or in conjunction with an instruction execution system, apparatus, or device.

[0145] Program code embodied on a computer-readable medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

[0146] Computer program code for performing the operations of the present invention may be written in one or more programming languages, or a combination thereof, including object-oriented programming languages ​​such as Java, Smalltalk, C++, and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may be executed entirely on the user's computer, partially on the user's computer, as a stand-alone software package, partially on the user's computer and partially on a remote computer, or entirely on a remote computer or server. In cases involving a remote computer, the remote computer may be connected to the user's computer through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computer (e.g., through the Internet using an Internet service provider).

[0147] Those skilled in the art will appreciate that the modules or steps of the present invention described above can be implemented using a general-purpose computing device. They can be centralized on a single computing device or distributed across a network of multiple computing devices. Alternatively, they can be implemented using program code executable by a computer device, which can then be stored in a storage device and executed by the computing device. Alternatively, they can be fabricated into separate integrated circuit modules, or multiple modules or steps can be fabricated into a single integrated circuit module. Thus, the present invention is not limited to any specific combination of hardware and software.

[0148] In addition, the acquisition, storage, use, and processing of data in the technical solution of the present invention comply with relevant provisions of laws and regulations.

[0149] Note that the above are only preferred embodiments of the present invention and the technical principles employed. Those skilled in the art will appreciate that the present invention is not limited to the specific embodiments herein, and that various obvious changes, readjustments, and substitutions are possible for those skilled in the art without departing from the scope of protection of the present invention. Therefore, although the present invention has been described in detail through the above embodiments, the present invention is not limited to the above embodiments and may include many other equivalent embodiments without departing from the scope of the present invention. The scope of the present invention is determined by the scope of the appended claims.

Claims

1. A method for generating a query execution plan, characterized in that: include: Generate multiple query execution plans corresponding to query statements based on multiple optimizers; Determining the query cost of each query execution plan by performing cost estimation on each query execution plan; Inputting query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources; A target query execution plan is determined according to the query cost and the execution data corresponding to each query execution plan.

2. The method for generating a query execution plan according to claim 1, wherein: Generates multiple query execution plans corresponding to query statements based on various optimizers, including: A rule-based optimizer, a cost-based optimizer, a heuristic-based optimizer and a distributed optimizer are respectively used to generate a first query execution plan, a second query execution plan, a third query execution plan and a fourth query execution plan corresponding to the query statement.

3. The method for generating a query execution plan according to claim 1, wherein: Determining the query cost of each query execution plan by estimating the cost of each query execution plan includes: For each query execution plan, determining a reading cost, a processing cost, and a sorting cost of the query execution plan according to statistical characteristics of the query execution plan; The query cost of the query execution plan is determined according to the read cost, the processing cost, and the sorting cost of the query execution plan.

4. The method for generating a query execution plan according to claim 1, wherein: The step of executing the prediction model training comprises: Obtain historical query statements, and determine historical query features and historical execution data corresponding to the historical query statements; The historical query features of the historical query statements are used as training inputs, and the historical execution data is used to guide training outputs to perform network training to obtain the execution prediction model.

5. The method for generating a query execution plan according to claim 1, wherein: Determining a target query execution plan according to the query cost and the execution data corresponding to each query execution plan includes: For each query execution plan, normalizing the query cost, the execution time, and the execution resources corresponding to the query execution plan; performing weighted summation of the normalized query cost, the normalized execution time, and the normalized execution resources based on the weights corresponding to the query cost, the execution time, and the execution resources to obtain the query performance of the query execution plan; The query execution plan corresponding to the optimal query performance is determined as the target query execution plan based on the query performance of each query execution plan.

6. The method for generating a query execution plan according to claim 1, wherein: After determining a target query execution plan according to the query cost and the execution data corresponding to each query execution plan, the method further includes: The target query execution plan is executed, and execution resources and / or execution time of the target query execution plan are adjusted according to real-time resources and execution progress.

7. A device for generating a query execution plan, characterized in that: include: A generation module is used to generate multiple query execution plans corresponding to query statements based on multiple optimizers; An estimation module, configured to determine the query cost of each query execution plan by performing cost estimation on each query execution plan; a determination module, configured to input query features corresponding to each query execution plan into a pre-trained execution prediction model, so that the execution prediction model determines execution data corresponding to each query execution plan according to the query features corresponding to each query execution plan, wherein the execution data includes execution time and execution resources; An execution module is configured to determine a target query execution plan according to the query cost and the execution data corresponding to each query execution plan.

8. An electronic device, characterized in that: The electronic device comprises: at least one processor; and a memory communicatively coupled to the at least one processor; The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor so that the at least one processor can execute the query execution plan generation method according to any one of claims 1 to 6.

9. A storage medium containing computer-executable instructions, characterized in that: When the computer executable instructions are executed by a computer processor, they are used to execute the query execution plan generation method according to any one of claims 1 to 6.

10. A computer program product, characterized in that The computer program product comprises a computer program, which, when executed by a processor, implements the method for generating a query execution plan according to any one of claims 1 to 6.

Images