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

Abstract

The application discloses a learning type query optimizer construction method and device based on two-stage search, comprising the following steps: through a suitable domain detector based on Mahalanobis distance measurement, whether a user query is in the suitable domain of an optimizer is verified by using training data; if not in the suitable domain, the query will be handed over to a bottom-layer database management system for processing and execution; if in the suitable domain, the method will generate the optimal k plans as a candidate set by using an existing beam search-based plan generator, and train a picker by using the execution plan after data enhancement; the plan picker will predict the expected delay of the candidate plan by using a regression model, so as to select the optimal query plan and hand over the optimal query plan to the database management system for execution. The application utilizes two-stage search and suitable domain detection, and proposes an accurate, efficient and robust query optimizer, which has the advantages of high plan output quality, high training efficiency and strong robustness, and has a wide application scenario.

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 a core component of a database management system. It determines the most efficient execution plan based on user queries and profoundly impacts the database's query processing capabilities. Its goal is to select the physical execution plan that minimizes system resource consumption (i.e., execution cost, generally referred to as execution time) for user Structured Query Language (SQL) queries.

[0003] Because the same SQL query statement can be executed in numerous ways to calculate the query result, and the execution time of these different execution methods can vary by several orders of magnitude, the selection of an execution plan plays a crucial role in database query processing efficiency. However, due to complex business operations, cumbersome SQL logic, and difficulty in obtaining information such as data distribution, database application developers struggle to write efficient SQL statements and execution plans, placing high demands on the performance of the query optimizer.

[0004] Today, 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 generated execution plans. However, 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, to provide stable and effective query optimization services at a low training cost, and to 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:

[0007] Collecting queries and their execution status information on the target dataset in the database management system;

[0008] constructing a training set based on the query and its execution information;

[0009] constructing an applicable domain detector based on the training set;

[0010] Based on 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 status are stored in an experience pool using data reuse and data enhancement techniques;

[0011] Construct and train a plan selector based on the data in the experience pool;

[0012] According to the applicable domain detector, plan generator and plan selector, a learning query optimizer is constructed.

[0013] 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:

[0014] A data collection module is used to collect query information and execution status information on the target data set in the database management system;

[0015] A training set construction module, configured to construct a training set based on the query and its execution status information;

[0016] A detector construction module, configured to construct an applicable domain detector based on the training set;

[0017] 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 status in an experience pool;

[0018] A selector construction module, used to construct and train a plan selector based on the data in the experience pool;

[0019] The optimizer construction module is used to construct a learning query optimizer based on the applicable domain detector, plan generator and plan selector.

[0020] According to a third aspect of an embodiment of the present invention, a method for generating an optimal plan for a database management system is provided, comprising:

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

[0022] 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:

[0023] The plan generation module is used to input the user query into the learning query optimizer to obtain the corresponding optimal plan. The learning query optimizer is constructed by the method described in the first aspect.

[0024] The technical solutions provided by the embodiments of the present application may have the following beneficial effects:

[0025] As can be seen from the above embodiments, the present application adopts a learning query optimizer based on two-stage search, thereby overcoming the problems of previous learning query optimizers being greatly affected by cost model accuracy errors and high training costs, thereby achieving an overall improvement in 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.

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

[0027] 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

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

[0029] Figure 1 This 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.

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

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

[0032] Figure 4 The present invention is a block diagram of a learning query optimizer device based on two-stage search.

[0033] Figure 5 The present invention is a flowchart of a method for generating an optimal plan for a database management system. DETAILED DESCRIPTION

[0034] Exemplary embodiments will be described in detail herein, with examples illustrated in the accompanying drawings. In the following description, when referring to the drawings, identical numerals in different figures represent identical or similar elements, unless otherwise indicated. The embodiments described in the following exemplary embodiments are not intended to represent all embodiments consistent with the present application. Rather, they are merely examples of apparatus and methods consistent with certain aspects of the present application, as detailed in the appended claims.

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

[0036] It should be understood that although the terms first, second, third, etc. may be used in this application to describe various information, such information should not be limited to these terms. These terms are only used to distinguish information of the same type from each other. For example, without departing from the scope of this application, first information may also be referred to as second information, and similarly, second information may also be referred to as 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".

[0037] Example 1

[0038] Figure 1 This 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 This is the overall framework diagram of the learning query optimizer according to an embodiment of the present invention. Figure 3 1 is a plan tree encoding and model structure framework diagram of an embodiment of the present invention, and the method includes the following steps:

[0039] Step S100: Collecting queries and execution status information on the target dataset in the database management system, specifically including the following steps:

[0040] Step S101: Filtering historical query log information of a database management system to obtain query information on a target data set, including selected table column information, connection information, and predicate information.

[0041] Specifically, we use the PostgreSQL database management system as an example. First, we determine that the target dataset D is the data of a specific database. Second, we collect the query statements and query information executed on this database from the SQL statement log in PostgreSQL. Taking query statement Q as an example, where the dimension of the target dataset D is d, we 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 filtering part of the Q sentence.

[0042] Step S102: Query execution information obtained from 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;

[0043] Specifically, taking the PostgreSQL database management system as an example, the SQL statement log in PostgreSQL captures 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.

[0044] Step S200: constructing a training set based on the query and its execution status information.

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

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

[0047] Step S300: Constructing an applicable domain detector based on the training set, specifically including the following steps:

[0048] Step S301: Select a training set The user query data Q in the database is input into the native query optimizer of the database to obtain the corresponding execution plan. .

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

[0050] Step S303: Based on the obtained one-dimensional vector data z and the Mahalanobis distance calculation part, an applicable domain detector is constructed to filter the queries input to 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 as an in-domain query, otherwise it will be classified as an out-of-domain query. The model performs best when the value of is 500. The introduction of the applicable domain detector helps the model solve different types of queries in a compartmentalized manner, focusing only on processing and executing applicable queries. This ensures that the model's worst-case performance is no worse than that of a typical database management system, thereby improving the model's efficiency, robustness, and practicality.

[0051] Step S400: Based on the training set, a beam search-based plan generator is constructed and trained. Different training plans generated in the training phase and their corresponding execution status are stored in an experience pool using data reuse and data enhancement techniques. Specifically, the following steps are included:

[0052] Step S401: Construct a learning value network model V, whose goal is to estimate the sub-plan of the input query Overall Cost The total cost of a subplan refers to the minimum execution plan that includes the current subplan among all complete plans for 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.

[0053] Step S402: Set the beam width b of the plan generator and the number of target candidate plans 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 plan collection Specific, initial plan Set to an empty plan with no operations performed, plan set is set to { }, complete plan set is set to the empty set.

[0054] Step S403: embedding the user queries in the training set into vectors of uniform size and inputting them into the plan generator for training;

[0055] 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 plan in the set and the complete plan are stored in the complete plan set middle.

[0056] Step S405: Collect the plans Sort all elements in from largest to smallest according to their total cost, retain only the first b elements, and discard the rest;

[0057] 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 augmented with data augmentation technology, the data is stored in the experience pool and used as training samples for the plan selector. This data reuse and augmentation process reduces the time it takes to collect training samples, increases the number of training samples, and improves the efficiency and effectiveness of model training.

[0058] Step S500: Constructing and training a plan selector based on the data in the experience pool, specifically including the following steps:

[0059] Step S501: Construct plan selector M (see Figure 3 ), which consists of a three-layer tree convolutional neural network, a pooling layer, and two fully connected layers. It predicts the query execution time and the 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; and 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. Perturbations to the prediction results. Models F and M share parameters except for the final fully connected layer. Learning from noise can significantly improve the model's ability to resist interference, making it more adaptable to real-world applications and enhancing its robustness and practicality.

[0060] Step S502: using the execution plans, execution sub-plans and corresponding cost information stored in the experience pool to train the plan selector;

[0061] The execution plan P and execution sub-plan in the training set and experience pool are combined Combined with the corresponding cost C, ) in the form of input into the plan selector for training.

[0062] Step S503: The query plan is represented by a feature vector tree, and the specific information of the sub-plan on each child node is represented by the one-hot encoded operator, the involved relationship table, the normalized cardinality estimate, and the cost estimate information;

[0063] plan The plan tree is converted into an encoding, where each operator and related relation table is encoded in a one-hot format, and the cardinality estimation results and cost estimation information corresponding to the plan are represented after normalization.

[0064] 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:

[0065]

[0066] Where N is the input ( ), = represents the perturbation of the data of sample i. Therefore, a sample with large perturbation indicates that the quality of the sample 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 influence 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.

[0067] Step S600: Based on the applicable domain detector, plan generator, and plan selector, the entire learning query optimizer is constructed, specifically including the following steps:

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

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

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

[0071] Correspondingly, Figure 4 As shown, an embodiment of the present invention further provides a learning query optimizer device based on two-stage search, comprising:

[0072] 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;

[0073] A training set construction module 102 is configured to construct a training set based on the query and its execution status information;

[0074] A detector construction module 103 is used to construct an applicable domain detector based on the training set;

[0075] A generator construction module 104 is configured to construct and train a plan generator based on a beam search according to the training set, and to store different training plans generated in the training phase and their corresponding execution statuses in an experience pool using data reuse and data enhancement techniques;

[0076] A selector construction module 105 is used to construct and train a plan selector based on the data in the experience pool;

[0077] The optimizer construction module 106 is used to construct a learning query optimizer based on the applicable domain detector, plan generator and plan selector.

[0078] Example 2

[0079] refer to Figure 5 This embodiment provides a method for generating an optimal plan for a database management system, including:

[0080] S1: Inputting a user query into a learning query optimizer to obtain a corresponding optimal plan, wherein the learning query optimizer is constructed according to the method described in the first aspect; this step includes the following sub-steps:

[0081] Step S11: Input the query. The applicable domain detector ADV will filter the query and directly input the query outside the applicable domain into the traditional database management system for execution. The query within the applicable domain will be input into the TPG. Specifically, ADV will encode each data in the input sample (Q, P) into a variable of the same length as the training set. Then, ADV will calculate the length of this sample to the set of 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 the Mahalanobis distance is obtained. The calculation formula is as follows:

[0082] .

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

[0084] Step S12: Based on the trained plan generator, a query Q in the applicable domain is input, and TPG first generates the k most promising candidate plans P using the first-stage beam search-based query generator.

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

[0086] 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:

[0087] The plan generation module is used to input the user query into the learning query optimizer to obtain the corresponding optimal plan. The learning query optimizer is constructed by the method described in the first aspect.

[0088] Regarding the apparatus 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.

[0089] For the device embodiments, since they basically correspond to the method embodiments, the relevant parts can be referred to the partial description of the method embodiments. The device embodiments described above are merely 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.

[0090] Accordingly, 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.

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

[0092] Those skilled in the art will readily appreciate other embodiments of the present application after considering the specification and practicing the contents disclosed herein. This application is intended to cover any variations, uses, or adaptations of the present application that follow the general principles of the present application and include common knowledge or customary techniques in the art not disclosed herein. The description and examples are to be considered merely as exemplary, and the true scope and spirit of the present application are indicated by the claims.

[0093] It should be understood that the present application is not limited to the exact structures 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 dataset in the database management system; constructing a training set based on the query and its execution information; constructing an applicable domain detector based on the training set; Based on 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 status are stored in an experience pool using data reuse and data enhancement techniques; Construct and train a plan selector based on the data in the experience pool; A learning query optimizer is constructed based on the applicable domain detector, plan generator and plan selector; The query and execution status information on the target dataset in the database management system are collected, including: Filtering query information in a target data set from historical query log information of a database management system, wherein the query information includes selected table column information, connection information, and filter predicate information; Query execution information obtained from historical query log information of the database management system, wherein the query execution information includes the execution plan corresponding to the query, the actual execution cost of the plan, and the execution time of the plan; Wherein, constructing an applicable domain detector based on the training set includes: Select user query data from the training set and input it into the database's native query optimizer to generate the corresponding execution plan; Concatenate 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 this batch of one-dimensional vector data and the Mahalanobis distance calculation part, we build an applicable domain detector to filter the queries input to the optimizer. The specific screening process is as follows: when the Mahalanobis distance between the query and the selected number of samples is less than the threshold, it is considered to be in the applicable domain. Otherwise, it is classified as out of the applicable domain. 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 status are stored in the experience pool using data reuse and data enhancement techniques, including: S401: Construct a learning value network model V, whose goal is to estimate the total cost of the sub-plan of the input query. The total cost of a sub-plan refers to the minimum execution plan that includes the current sub-plan among all complete plans of the current query. This indicator represents the potential of the sub-plan. That is, the smaller the total plan, the greater the potential of the 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 set B and complete plan set ; 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 largest to smallest according to their total cost, and only keep the top b elements; S406: Repeat steps S404-S405 until the number of complete plans is equal to k This process is used 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 being expanded using data enhancement technology, these data are stored in the experience pool as training samples for the plan selector. The process of constructing and training a plan selector based on the data in the experience pool includes: Build a plan selector, which consists of a three-layer tree convolutional neural network, a pooling layer, and two fully connected layers. It predicts 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, with the specific information of the sub-plan on each child node represented by one-hot encoded operators, related relation tables, normalized cardinality estimates, and cost estimates. 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. 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 query input into the query optimizer and inputs the output results into the database management system and plan generator respectively. The plan generator is the 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. The plan selector is the underlying module of the query optimizer. This module selects the input plan and inputs the output result into the database management system.

2. A device for constructing a learning query optimizer based on two-stage search, characterized in that: The device is used to perform the method according to claim 1, and the device comprises: A data collection module is used to collect query information and execution status information on the target data set in the database management system; A training set construction module, configured to construct a training set based on the query and its execution status information; A detector construction module, configured to construct an applicable domain detector based on 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 status 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.

3. 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 according to claim 1.

4. The method according to claim 3, characterized in that Input the user query into the learning query optimizer to obtain the corresponding optimal plan, including: Input user queries, the applicable domain detector filters the queries, directly inputs the queries outside the applicable domain into the traditional database management system for execution, and inputs the queries 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 an optimal plan from a plurality of candidate plans inputted, and the optimal plan is executed by the database management system.

5. A device for generating an optimal plan for a database management system, characterized in that: include: 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 claim 1.

Images