Learning type query optimizer construction method and device based on two-stage search

Abstract

The invention discloses a learning type query optimizer construction method and device based on two-stage search, and the method comprises the steps: verifying whether a user query is in an application domain of an optimizer or not through an application domain detector based on Mahalanobis distance measurement and through training data; if the query is not in the application domain, the query is processed and executed by a database management system at the bottom layer; if in the applicable domain, the method uses an existing plan generator based on beam search to generate k optimal plans as a candidate set, and uses an execution plan after data enhancement to train a selector; and the plan selector predicts the predicted time delay of the candidate plan by using a regression model so as to select an optimal query plan and submit the optimal query plan to the database management system for execution. The invention provides an accurate, efficient and robust query optimizer by utilizing a two-stage search and application domain detection mode, has the advantages of high output plan quality, high training efficiency, strong robustness and the like, and has a wide application scene.

Description

Technical Field

[0001] The present application relates to the field of information retrieval technology, and in particular to a method and device for constructing a learning query optimizer based on two-stage search. Background Art

[0002] The query optimizer is one of the core components of the database management system. It can determine a more efficient execution plan based on the input user query and is a component that profoundly affects the query processing capabilities of the database. Its goal is to select the physical execution plan with the lowest system resource consumption (i.e., execution cost, generally refers to execution time) for the input user Structured Query Language (SQL) query.

[0003] Since there are many ways to calculate the query results for the same SQL query statement, and different execution methods may differ by several orders of magnitude in terms of execution time, the selection of execution plans plays a vital role in the efficiency of database query processing. However, due to complex business, cumbersome SQL logic, and difficulty in obtaining information such as data distribution, it is difficult for database application developers to write efficient SQL statements and execution plans, which places high demands on the performance of query optimizers.

[0004] Nowadays, major database management systems all use traditional query optimizers based on statistical information such as histograms and sampling. However, the design of traditional query optimizers is mainly based on the assumption of ideal data distribution, while the data distribution in real scenarios is difficult to meet this assumption, resulting in poor performance of traditional optimizers in practical applications. Inspired by the excellent representation and learning capabilities of machine learning, a large number of learning query optimizers using machine learning have emerged in recent years, which can significantly improve the quality of the output execution plans, but their robustness needs to be improved, mainly reflected in the following aspects: (1) In the cost model that the learning query optimizer relies on, errors are inevitable and propagable, which will affect the effectiveness of the query optimizer. (2) Existing learning query optimizers lack a defined scope of application, which will affect the stability of the system. (3) The high training cost also affects the scalability of such optimizers. Therefore, how to design a robust query optimization algorithm that meets the requirements of system stability, effectiveness, and scalability is the primary challenge. Summary of the invention

[0005] The purpose of the embodiments of the present application is to provide a method and device for constructing a learning query optimizer based on two-stage search, so as to provide stable and effective query optimization services at a low training cost and improve the robustness of the entire system.

[0006] According to a first aspect of an embodiment of the present invention, a method for constructing a learning query optimizer based on two-stage search is provided, comprising: Collecting queries and their execution status information on the target data set in the database management system; constructing a training set based on the query and its execution information; Constructing an applicable domain detector according to the training set; According to the training set, a plan generator based on beam search is constructed and trained, and different training plans generated in the training phase and their corresponding execution conditions are stored in an experience pool using data reuse and data enhancement techniques; Constructing and training a plan selector based on the data in the experience pool; According to the applicable domain detector, plan generator and plan selector, a learning query optimizer is constructed.

[0007] According to a second aspect of an embodiment of the present invention, there is provided a device for constructing a learning query optimizer based on two-stage search, comprising: A data collection module, used to collect query information and execution status information on a target data set in a database management system; A training set building module, used to build a training set according to the query and its execution status information; A detector construction module, used to construct an applicable domain detector according to the training set; A generator construction module is used to construct and train a plan generator based on beam search according to the training set, and use data reuse and data enhancement techniques to store different training plans generated in the training phase and their corresponding execution conditions in an experience pool; A selector construction module, used to construct and train a plan selector based on the data in the experience pool; The optimizer construction module is used to construct a learning query optimizer based on the applicable domain detector, plan generator and plan selector.

[0008] According to a third aspect of an embodiment of the present invention, there is provided a method for generating an optimal plan for a database management system, comprising: The user query is input into a learning query optimizer to obtain a corresponding optimal plan, wherein the learning query optimizer is constructed by the method described in the first aspect.

[0009] According to a fourth aspect of an embodiment of the present invention, there is provided a device for generating an optimal plan for a database management system, comprising: The plan generation module is used to input the user query into the learning query optimizer to obtain the corresponding optimal plan, and the learning query optimizer is constructed by the method described in the first aspect.

[0010] The technical solution provided by the embodiments of the present application may have the following beneficial effects: It can be seen from the above embodiments that the present application adopts a learning query optimizer based on two-stage search, so it overcomes the problems of the previous learning query optimizer being greatly affected by the cost model accuracy error and the high training cost, thereby achieving the effect of comprehensively improving efficiency, accuracy, robustness and scalability; by reusing and enhancing the query data generated by the first-stage plan generator, the long training data collection time required by the original learning query optimizer is overcome, thereby improving the training efficiency of the model and making the method more cheaply deployable; by introducing an applicable domain detector to screen user queries, the problem of the original learning query optimizer lacking a defined scope of application is overcome, thereby improving the stability and applicability of the system.

[0011] The model designed in this application provides a more robust, stable, efficient and practical solution to query optimization problems in database management systems.

[0012] It should be understood that the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the present application. BRIEF DESCRIPTION OF THE DRAWINGS

[0013] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0014] Figure 1 It is a flow chart of a method for constructing a learning query optimizer based on two-stage search according to an embodiment of the present invention.

[0015] Figure 2 It is an overall framework diagram of the learning query optimizer according to an embodiment of the present invention.

[0016] Figure 3 It is a plan tree coding and model structure framework diagram of an embodiment of the present invention.

[0017] Figure 4 It is a block diagram of a learning query optimizer device based on two-stage search according to an embodiment of the present invention.

[0018] Figure 5 The present invention is a flowchart of a method for generating an optimal plan for a database management system according to an embodiment of the present invention. DETAILED DESCRIPTION

[0019] Exemplary embodiments will be described in detail herein, examples of which are shown in the accompanying drawings. When the following description refers to the drawings, unless otherwise indicated, the same numbers in different drawings represent the same or similar elements. The implementations described in the following exemplary embodiments do not represent all implementations consistent with the present application. Instead, they are merely examples of devices and methods consistent with some aspects of the present application as detailed in the appended claims.

[0020] The terms used in this application are for the purpose of describing specific embodiments only and are not intended to limit this application. The singular forms of "a", "said" and "the" used in this application and the appended claims are also intended to include plural forms unless the context clearly indicates other meanings. It should also be understood that the term "and / or" used herein refers to and includes any or all possible combinations of one or more associated listed items.

[0021] It should be understood that although the terms first, second, third, etc. may be used in the present application to describe various information, these information should not be limited to these terms. These terms are only used to distinguish the same type of information from each other. For example, without departing from the scope of the present application, the first information may also be referred to as the second information, and similarly, the second information may also be referred to as the first information. Depending on the context, the word "if" as used herein may be interpreted as "at the time of" or "when" or "in response to determining".

[0022] Embodiment 1 Figure 1 is a flow chart of a method for constructing a learning query optimizer based on two-stage search according to an embodiment of the present invention. Figure 2 is an overall framework diagram of the learning query optimizer of an embodiment of the present invention, Figure 3 : is a plan tree coding and model structure framework diagram of an embodiment of the present invention, the method comprises the following steps: Step S100: collecting queries and execution status information on the target data set in the database management system, specifically including the following steps: Step S101: Filter the query information on the target data set from the historical query log information of the database management system, including selection table column information, connection information, and predicate information.

[0023] Specifically, take the PostgreSQL database management system as an example. First, determine that the target data set D is the data of a specific database. Second, collect the query statements and query information executed on this database from the SQL statement log in PostgreSQL. Take the query statement Q as an example, where the dimension of the target data set D is d; parse the database historical query log information, and extract the selected n tables (relations) T (including arrive ); Extract from the attribute selection part of the Q statement m columns (attributes) in the table arrive ; Extract the predicate information p from the predicate screening part of the Q sentence.

[0024] Step S102: the query execution information obtained from the historical query log information of the database management system includes the execution plan corresponding to the query and the actual execution cost of the plan, that is, the execution time of the plan; Specifically, taking the PostgreSQL database management system as an example, the SQL statement log in PostgreSQL obtains the execution status of the current query, including the execution plan P corresponding to the query and the actual execution cost C of the plan, that is, the execution time of the plan.

[0025] Step S200: constructing a training set according to the query and its execution status information.

[0026] Step S201: The relevant information of the query statement Q extracted in step S101 (including the selection table column information, connection information, and filter predicate information) is combined with the execution status information corresponding to the query extracted in step S102, that is, the execution plan P corresponding to the query and the actual execution cost C of the plan, to form a corresponding method.<Q, P, C > The triples are stored in the experience pool and recorded as training sets spare.

[0027] Step S202: Repeat step S201 until the training set part in the experience pool Stored in<Q, P, C > When the number of triplets is sufficient for model training, the loop ends.

[0028] Step S300: constructing an applicable domain detector according to the training set, specifically comprising the following steps: Step S301: Select training set The user query data Q in is input into the database native query optimizer to obtain the corresponding execution plan .

[0029] Step S302: Connect each sample in the training set with the corresponding plan and encode them into one-dimensional vector data z of equal length.

[0030] Step S303: Based on the obtained batch of one-dimensional vector data z and the Mahalanobis distance calculation part, an applicable domain detector is constructed to screen the queries input into the optimizer. The specific process is: select the threshold and the number of samples , when the Mahalanobis distance between the query and the selected number of samples is less than the threshold, it will be considered to be classified as an in-domain query, otherwise it will be classified as an out-of-domain query. The value of is 500, at which the model has the best performance. The introduction of the applicable domain detector can help the model solve different types of queries in a divided manner, so that the model is only responsible for processing and executing applicable queries, ensuring that the worst performance of the model will not be worse than that of ordinary database management systems, thereby improving the efficiency, robustness and practicality of the model.

[0031] Step S400: construct and train a plan generator based on beam search according to the training set, and use data reuse and data enhancement techniques to store different training plans generated in the training phase and their corresponding execution conditions into an experience pool, specifically including the following steps: Step S401: Construct a learning value network model V, whose goal is to estimate the sub-plan of the input query Overall Cost The overall cost of a subplan refers to the minimum execution plan that includes the current subplan among all complete plans of the current query. This indicator represents the potential of the subplan, that is, the smaller the overall plan, the greater the potential of this subplan to develop into the optimal plan.

[0032] Step S402: Set the beam width b of the plan generator and the number of target candidate plans parameter k. We recommend setting b to 20 and k to 10. Under this setting, the model performs well; Initialize the initial plan , Plan Collection and the complete set of plans . Specific, initial plan Set to an empty plan with no operations performed, plan collection is set to { }, complete plan set is set to the empty set.

[0033] Step S403: embedding the user queries in the training set into vectors of uniform size and inputting them into the plan generator for training; Step S404: When the number of complete plans is less than the target number k, the plan set is continuously expanded in a bottom-up manner. The incomplete plans in the set are stored in the complete plan set. middle.

[0034] Step S405: Assemble the plans All elements in are sorted from large to small according to the overall cost, and only the first b elements are retained, and other elements can be discarded; Step S406: Repeat steps S405 and S406 until the number of complete plans = k, and use this process to continuously train the plan generator, and save various training data generated in this process, including user query Q, execution plan , total execution time C, sub-plan execution time After being expanded by data enhancement technology, it is stored in the experience pool as a training sample for the plan selector. This data reuse and enhancement process reduces the time for collecting training samples, expands the number of training samples, and improves the efficiency and effect of model training.

[0035] Step S500: constructing and training a plan selector based on the data in the experience pool, specifically including the following steps: Step S501: Construct a plan selector M (see Figure 3 ), which consists of a three-layer tree convolutional neural network, a pooling layer, and two fully connected layers, predicts the query execution time and predicted data noise based on the input query and plan The three-layer tree convolutional neural network has 1×256, 1×128, and 1×64 layers respectively; the pooling layer has 1×64 layers; the fully connected layer has 1×32 and 1×1 layers respectively. In addition, the model introduces an additional model F to learn data noise. The perturbation of the prediction results, where model F and model M share parameters except for the last fully connected layer. Learning about noise can significantly improve the model's anti-interference ability, making the model more adaptable to actual application scenarios, and improving the model's robustness and practicality.

[0036] Step S502: using the execution plan, execution sub-plan and corresponding cost information stored in the experience pool to train the plan selector; The execution plan P and execution sub-plan in the training set and experience pool are and the corresponding cost C to obtain ( ) into the plan selector for training.

[0037] Step S503: using a feature vector tree to represent the query plan, using one-hot encoded operators and related relationship tables, normalized cardinality estimates, and cost estimates to represent specific information of the sub-plan on each sub-node; plan The plan tree is converted into an encoding, where each operator and related relationship table is encoded in the form of One Hot, and the cardinality estimation results and cost estimation information corresponding to the plan are represented after normalization.

[0038] Step S504: The predicted execution cost corresponding to the execution plan and the actual execution cost Input to the heteroskedastic regression loss function , by minimizing the heteroscedasticity loss function value on the training set, the plan selector is trained to obtain the optimal parameters. The specific loss function is as follows:

[0039] Where N is the input ( ), represents the perturbation of the data of sample i. Therefore, a sample with large perturbation indicates that the sample quality is relatively low, thus Reduce the weight coefficient of the sample. This allows the loss function to guide the model to pay more attention to samples with small disturbances and reduce the impact of samples with large disturbances. For the second term in the loss function, add The penalty term can prevent the model from continuously improving To reduce losses. It should be noted that There is no so-called label, but it can be automatically learned through the loss function.

[0040] Step S600: Based on the applicable domain detector, plan generator and plan selector, the entire learning query optimizer is constructed, which specifically includes the following steps: Step S601: The applicable domain detector is used as the top-level module of the query optimizer. This module processes the user query input into the query optimizer and inputs the output results into the database management system and the plan generator respectively.

[0041] Step S602: The plan generator is a middle-layer module of the query optimizer. This module generates a plan for the input query within the applicable domain and inputs the output result into the plan selector.

[0042] Step S603: The plan selector is the bottom module of the query optimizer. This module selects the input plan and inputs the output result into the database management system.

[0043] Correspondingly, Figure 4 As shown, the embodiment of the present invention also provides a learning query optimizer device based on two-stage search, comprising: The data collection module 101 is used to collect the query and execution status information on the target data set in the database management system; A training set building module 102, used to build a training set according to the query and its execution status information; A detector construction module 103, used to construct an applicable domain detector according to the training set; A generator construction module 104 is used to construct and train a plan generator based on beam search according to the training set, and use data reuse and data enhancement technology to store different training plans generated in the training phase and their corresponding execution conditions into an experience pool; A selector construction module 105, used to construct and train a plan selector based on the data in the experience pool; The optimizer construction module 106 is used to construct a learning query optimizer according to the applicable domain detector, plan generator and plan selector.

[0044] Embodiment 2 refer to Figure 5 This embodiment provides a method for generating an optimal plan for a database management system, including: S1: Inputting the user query into the learning query optimizer to obtain the corresponding optimal plan, wherein the learning query optimizer is constructed by the method described in the first aspect; this step includes the following sub-steps: Step S11: Input the query, and the applicable domain detector ADV will screen the query, directly input the query outside the applicable domain into the traditional database management system for execution, and input the query within the applicable domain into the TPG. Specifically, ADV will encode each data in the input sample (Q, P) into a variable with the same length as the training set. Then, ADV will calculate the set of this sample to N training samples. The dimension of the encoding vector is represented as d; the encoding vector of the test sample is represented as z, with a shape of 1 × d, and the encoding matrix of the training set of N training samples is represented as Z, with a shape of N × d. The mean µ and covariance matrix Σ of Z are calculated, and then the Mahalanobis distance is obtained. The calculation formula is as follows:

[0045] .

[0046] Then, if If the value is in γ, it is considered to be within the applicable domain. Otherwise, it is classified as being outside the applicable domain.

[0047] Step S12: According to the trained plan generator, a query Q in the applicable domain is input, and TPG first uses the first-stage beam search-based query generator to generate k most promising candidate plans P.

[0048] Step S13: The plan selector selects the best plan from the candidate plans P , the optimal plan is executed by the underlying database management system.

[0049] Correspondingly, according to an embodiment of the present invention, there is also provided a device for generating an optimal plan for a database management system, comprising: The plan generation module is used to input the user query into the learning query optimizer to obtain the corresponding optimal plan, and the learning query optimizer is constructed by the method described in the first aspect.

[0050] Regarding the device in the above embodiment, the specific manner in which each module performs operations has been described in detail in the embodiment of the method, and will not be elaborated here.

[0051] For the device embodiment, since it basically corresponds to the method embodiment, the relevant parts can refer to the partial description of the method embodiment. The device embodiment described above is only schematic, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the present application scheme. A person of ordinary skill in the art can understand and implement it without paying any creative work.

[0052] Correspondingly, the present application also provides an electronic device, comprising: one or more processors; a memory for storing one or more programs; when the one or more programs are executed by the one or more processors, the one or more processors implement the above-mentioned two-stage search-based learning query optimizer construction method or database management system optimal plan generation method.

[0053] Accordingly, the present application also provides a computer-readable storage medium having computer instructions stored thereon, which, when executed by a processor, implement the above-mentioned two-stage search-based learning query optimizer construction method or database management system optimal plan generation method.

[0054] Those skilled in the art will readily appreciate other embodiments of the present application after considering the description and practicing the contents disclosed herein. The present application is intended to cover any modification, use or adaptation of the present application, which follows the general principles of the present application and includes common knowledge or customary techniques in the art that are not disclosed in the present application. The description and examples are intended to be exemplary only, and the true scope and spirit of the present application are indicated by the claims.

[0055] It should be understood that the present application is not limited to the precise structures that have been described above and shown in the drawings, and that various modifications and changes may be made without departing from the scope thereof. The scope of the present application is limited only by the appended claims.

Claims

1. A method for constructing a learning query optimizer based on two-stage search, characterized in that: include: Collecting queries and their execution status information on the target data set in the database management system; constructing a training set based on the query and its execution information; Constructing an applicable domain detector according to the training set; According to the training set, a plan generator based on beam search is constructed and trained, and different training plans generated in the training phase and their corresponding execution conditions are stored in an experience pool using data reuse and data enhancement techniques; Constructing and training a plan selector based on the data in the experience pool; According to the applicable domain detector, plan generator and plan selector, a learning query optimizer is constructed.

2. The method according to claim 1, characterized in that Collects queries and their execution status information on the target dataset in the database management system, including: Filtering query information in the target data set from historical query log information of the database management system, the query information includes selection table column information, connection information, and filter predicate information; The query execution status information is obtained from the historical query log information of the database management system, and the query execution status information includes the execution plan corresponding to the query, the actual execution cost of the plan, and the execution time of the plan.

3. The method according to claim 1, characterized in that Based on the training set, an applicable domain detector is constructed, including: Select the user query data in the training set and input it into the database's native query optimizer to generate the corresponding execution plan; Connect each sample query in the training set with the corresponding database native query optimizer execution plan and encode them into a one-dimensional vector of equal length; Relying on obtaining this batch of one-dimensional vector data and the Mahalanobis distance calculation part, an applicable domain detector is constructed to screen the queries input into the optimizer. The specific screening process is: selecting a threshold and the number of sampling samples. When the Mahalanobis distance between the query and the selected number of samples is less than the threshold, it will be considered to be classified as a query within the applicable domain, otherwise it will be classified as a query outside the applicable domain.

4. The method according to claim 1, characterized in that According to the training set, a plan generator based on beam search is constructed and trained, and different training plans generated in the training phase and their corresponding execution conditions are stored in the experience pool using data reuse and data enhancement techniques, including: S401: construct a learning value network model V, the goal of which is to estimate the overall cost of the sub-plan of the input query. The overall cost of the sub-plan refers to the minimum execution plan that contains the current sub-plan among all the complete plans of the current query. This indicator represents the potential of the sub-plan, that is, the smaller the overall plan, the greater the potential of this sub-plan to develop into the optimal plan; S402: Set the beam width b and target number parameter k of the plan generator and initialize the initial plan , Plan Collection and the complete set of plans ; S403: embedding the user queries in the training set into vectors of uniform size and inputting the vectors into the plan generator for training; S404: When the number of complete plans is less than the target number k, the incomplete plans in the plan set are continuously expanded in a bottom-up manner, and the complete plans are stored in the complete plan set. middle; S405: sort all elements in plan set B from large to small according to the total cost, and only retain the first b elements; S406: Repeat steps S404-S405 until the number of complete plans = k, and use this process to continuously train the plan generator, and save various types of training data generated in this process, including user queries, execution plans, total execution time, and sub-plan execution time. After expansion using data enhancement technology, they are stored in the experience pool as training samples for the plan selector.

5. The method according to claim 1, characterized in that Based on the data in the experience pool, a plan selector is constructed and trained, including: Build a plan picker, which includes a three-layer tree convolutional neural network, a pooling layer, and two fully connected layers to predict the query execution time and data noise based on the input query and plan information; Use the execution plans, execution sub-plans and corresponding cost information stored in the experience pool to train the plan selector; The query plan is represented by a feature vector tree, and the specific information of the sub-plan on each sub-node is represented by the one-hot encoded operator and the related relation table, normalized cardinality estimation and cost estimation information; The predicted execution cost and the actual execution cost corresponding to the execution plan are input into the heteroscedastic regression loss function. By minimizing the heteroscedastic loss function value on the training set, the plan selector is trained to obtain the optimal parameters.

6. The method according to claim 1, characterized in that According to the applicable domain detector, plan generator and plan selector, the entire learning query optimizer is constructed, including: Combine the constructed applicable domain detector, plan generator and plan selector to build the entire learning query optimizer; The applicable domain detector is the top-level module of the query optimizer. This module processes the user queries input into the query optimizer and inputs the output results into the database management system and the plan generator respectively. The plan generator is the middle layer module of the query optimizer. This module generates a plan for the input query in the applicable domain and inputs the output result into the plan selector. The plan selector is the bottom-level module of the query optimizer. This module selects the input plan and inputs the output result into the database management system.

7. A device for constructing a learning query optimizer based on two-stage search, characterized in that: include: A data collection module, used to collect query information and execution status information on a target data set in a database management system; A training set building module, used to build a training set according to the query and its execution status information; A detector construction module, used to construct an applicable domain detector according to the training set; A generator construction module is used to construct and train a plan generator based on beam search according to the training set, and use data reuse and data enhancement techniques to store different training plans generated in the training phase and their corresponding execution conditions in an experience pool; A selector construction module, used to construct and train a plan selector based on the data in the experience pool; The optimizer construction module is used to construct a learning query optimizer based on the applicable domain detector, plan generator and plan selector.

8. A method for generating an optimal plan for a database management system, characterized in that: include: The user query is input into a learning query optimizer to obtain a corresponding optimal plan, wherein the learning query optimizer is constructed by the method described in any one of claims 1 to 6.

9. The method according to claim 8, characterized in that Input the user query into the learning query optimizer to obtain the corresponding optimal plan, including: Input user query, the applicable domain detector screens the query, directly inputs the query outside the applicable domain into the traditional database management system for execution, and inputs the query within the applicable domain into the plan generator; The trained plan generator generates several candidate plans based on the query input in the applicable domain using a plan generation method based on beam search; The plan selector selects the best plan from a plurality of candidate plans inputted, and the best plan is executed by the database management system.

10. A device for generating an optimal plan for a database management system, characterized in that: include: A plan generation module is used to input user queries into a learning query optimizer to obtain a corresponding optimal plan, wherein the learning query optimizer is constructed by the method described in any one of claims 1 to 6.

Images