A database query optimization method and system based on prompt word guidance and application thereof

Abstract

The application discloses a database query optimization method based on prompt word guidance, comprising the following steps: step one, identifying a single potential effective prompt word in SQL, and filtering the single prompt word by using a pre-constructed rule; step two, performing binary combination on the filtered single prompt word to generate a plurality of candidate prompt word pairs, and filtering the current candidate prompt word pair according to an evaluation index of a traditional optimizer; step three, binding the filtered candidate prompt word pair with the SQL statement to generate one or more query plans, and encoding the query plans; step four, extracting risk information in the query plan and encoding, splicing the encoded risk information to the encoded query plan operator to obtain a spliced encoding vector; step five, putting the encoding vector in step four into a performance predictor to predict the delay distribution of the query plan, and obtaining an optimal query plan. The application also discloses a query optimization system, which has a wide application scenario.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of data query, and relates to a database query optimization method and system based on prompt word guidance and application thereof. BACKGROUND

[0002] In the data era, a database is undoubtedly an important base for storing and managing data. The database is a system for storing and organizing data in an orderly and controllable manner, and provides data support for various application programs. It can not only efficiently store a large amount of structured data, but also provide fast, safe and reliable data access and management functions. In the database system, the query optimizer occupies a crucial position. It is one of the core components of the database management system, responsible for automatically optimizing the query statements submitted by users, thereby improving the query performance of the entire system.

[0003] The traditional cost-based query optimizer aims to select the candidate plan with the minimum estimated cost, where the cost represents the execution delay or other user-defined indicators. Various techniques such as data sketching, histogram, sampling, etc. are proposed in the cost-based optimizer. These techniques all have different assumptions, such as dependence on attribute independence and consistency of data distribution, etc. These assumptions are not easy to achieve in real production, and if the real conditions differ greatly from the assumed conditions, it will cause a serious performance rollback.

[0004] The learning-based optimizer refers to using machine learning techniques to optimize the execution plan of the database query. These optimizers are different from the traditional rule-based or cost-based optimizers, and they improve the accuracy and efficiency of optimization by learning a large number of query samples. The core idea of the learning-based optimizer is to use historical query data and the corresponding execution plan as a training set, and to learn the mapping relationship between query features and execution plans through a machine learning model. In this way, when a new query comes, the optimizer can predict the best execution plan without performing expensive query plan enumeration and cost estimation.

[0005] Existing learning-based optimizers can include the following:

[0006] Bao [1]The paper is the first research on learning-based query optimizer based on hints. The core idea is to avoid learning the optimizer from scratch, rather than completely replacing the traditional query optimizer. Specifically, when the SQL arrives, Bao uses a pre-set hint set to act on the query to generate several query plans (there may be the same query plan), then selects an estimated optimal query plan through Thompson sampling in the candidate query plan and executes to get the real delay. Finally, the query plan and delay are stored in the training pool to update the model continuously. The method proposed by Bao greatly reduces the training time and training data volume, and improves the query efficiency.

[0007] COOOL [2] The paper inherits the method of Bao and makes improvements. It believes that the Bao model needs to predict the exact cost of each plan, which is very challenging for existing models. To solve these problems, COOOL uses Learning-To-Rank (LTR) technology to change the estimated exact cost to the estimated cost order of query plans. Based on this, it only needs to predict the pros and cons of two input query plans, without estimating the exact delay of the query plan.

[0008] Lero [3] The paper inherits the Bao paper and also believes that LTR technology can be used to optimize Bao, while proposing a new query plan enumeration strategy to expand the candidate query plan. Specifically, the cardinality estimator is used as the adjustment knob for query plan exploration. Before inputting the cardinality estimate of the subquery into the cost model, it will be intentionally enlarged or reduced by a fixed proportion. Through this method, it may generate some better plans in the adjacent plan space.

[0009] The existing learning-based optimizer has the following shortcomings:

[0010] (1) The query representation is not comprehensive enough. Previous query plan representation methods mostly focus on part of the information of the query plan, such as operator type, join type, cardinality estimation, etc., but do not fully utilize the query plan and statistical information, and do not extract all the key information.

[0011] (2) Current methods for using search suggestions have certain limitations. For example, Bao's paper uses a manual approach to combine six important Boolean type suggestions into 48 suggestion sets, applying them to each SQL statement to limit its search space. However, this method has certain limitations: First, the manually preprocessed suggestion set is workload-independent, meaning that the same search strategy is used for all SQL statements, lacking specificity. Second, this method can only handle a small number of suggestion sets. If there are more suggestions (in fact, modern commercial databases have far more suggestions that can have a significant effect than these six), or if the granularity of the suggestion's effect is finer, the problem of too many combinations will arise.

[0012] (3) Query optimization in real-world production environments involves many uncertainties, such as the uncertainty of statistical information (missing or outdated statistical information leads to inaccurate statistics), the uncertainty of cardinality estimation (column and table relationships rely on the independence assumption, while single-point queries rely on the mean assumption), and the uncertainty of the cost model (inaccurate cost estimation of query return tables leads to inaccurate cost model estimation). Current learning-based optimizer strategies cannot perceive these uncertainties. Even if the data in the table changes significantly, the optimizer will still refer to the previous model estimation results, which will significantly affect the final query performance and query stability. Summary of the Invention

[0013] To address the shortcomings of existing technologies, the purpose of this invention is to provide a database query optimization method, system, and application based on prompt words.

[0014] This invention provides a database query optimization method based on prompt words, the query optimization method comprising the following steps:

[0015] Step 1: Identify potentially valid individual prompt words in the SQL and filter these individual prompt words using pre-built rules;

[0016] Step 2: Combine the filtered individual prompt words into binary pairs to generate multiple candidate prompt word pairs, and filter the current candidate prompt word pairs based on the evaluation metrics of the traditional optimizer;

[0017] Step 3: Bind the filtered candidate suggestion pairs to the SQL statement to generate one or more query plans, and encode the query plans;

[0018] Step 4: Extract and encode the risk information from the query plan, concatenate the encoded risk information to the encoded query plan operator, and use the resulting encoded vector as the data in the dataset.

[0019] Step five, put the encoding vector in step four as a whole into the performance predictor, predict the execution distribution of the query plan, and obtain the optimal query plan.

[0020] In step one, the hint words include connection operations between two tables, access modes of tables, prefixes of table connection sequences, and hint words specific to different databases.

[0021] and / or,

[0022] The filtering rules include the following:

[0023] Only columns with indexes will use the NestedLoopJoin hint word; or,

[0024] Only columns with indexes will use the IndexScan hint word; or,

[0025] Only two tables with connection keys in SQL will use the Leading hint word.

[0026] As long as any of the above filtering rules are met, it is filtered.

[0027] In step two, all candidate hint words are put into a candidate list, one hint word is taken out each time, and the taken-out hint word is combined with all hint words in the candidate list to form a candidate hint word pair, which is put into a traditional optimizer to estimate the delay, and the hint word pair with a delay less than the expected maximum delay max_lantency is put at the tail end of the candidate list. The above process is repeatedly repeated until the end of the candidate list is traversed, and the hint words in the final candidate list are the candidate hint word set.

[0028] In step five, the performance predictor uses a Gaussian process regression model to predict the performance distribution of the query plan.

[0029] The optimal query plan refers to the execution plan with the minimum estimated execution delay after filtering execution plans exceeding a preset threshold.

[0030] In one specific embodiment, the threshold is set to 0.5, and the threshold can be adjusted according to the verification result of the verification set.

[0031] In the present application, the construction and training of the performance predictor specifically include the following steps:

[0032] Step I, read the SQL data set, execute the SQL and obtain the real execution time, store the query plan and the real execution time in the database;

[0033] Step II, obtain the statistical information of all tables involved in the query SQL in the database; the statistical information includes histograms, high-frequency values, and different element numbers.

[0034] Step III, encoding the query plan in step I, forming the automatically labeled training data set, and updating in real time;

[0035] Step IV, extracting risk information in the query plan and encoding, the encoded risk information is spliced to the encoded query plan operator in step III, and the spliced encoded vector is used as data in the data set;

[0036] Step V, using the data set obtained in step IV to train the performance predictor of the query plan, and persisting the optimized model parameters.

[0037] In the present application, step I is only performed in the first training, and subsequent training directly starts from step II.

[0038] In step III, the information in the query plan is encoded, including but not limited to the following:

[0039] Using an encoding dictionary to encode the operator information of each node into a non-repeating integer; and / or,

[0040] Normalizing the cardinality information of the subquery output by the upper node for each node; and / or,

[0041] Normalizing the estimated cost of the current subquery; and / or,

[0042] Encoding the predicate using histogram, high value, number of different elements, etc.; and / or,

[0043] Using an encoding dictionary to encode the sort key into an integer; and / or,

[0044] Using an encoding dictionary to encode the connection order obtained by traversing the query plan into an integer.

[0045] In step IV, the risk information includes: the cardinality estimation risk including the independence assumption between columns and columns, the independence assumption between tables and tables, and the uniform assumption on which single-point queries depend, the statistical information risk including statistical information missing, statistical information out of bounds, and statistical information expired, and the cost model risk including inaccurate table-back plan estimation, new operator, and inaccurate special operator cost estimation;

[0046] And / or,

[0047] Assess the risk when each operator is encoded, set 1 to the corresponding position if the risk exists, otherwise set 0, finally encode an 8-bit 0 / 1 vector, and splice it at the tail of the encoded operator in step III.

[0048] In step V, in the training process of the performance predictor, a self-attention mechanism is used to generate a high-dimensional vector embedding of query features, capture the dependency relationship of each step in the query, and then input the high-dimensional vector embedding into a Gaussian process regression model to predict the execution performance of the query and estimate the prediction uncertainty.

[0049] The application further provides a query optimization system for implementing the query optimization method, and the query optimization system comprises an SQL parsing module, a hint word filtering module, a query plan generation module, a representation module, a performance prediction module and an execution module.

[0050] The SQL parsing module is used for receiving and parsing an SQL query statement, and identifying and extracting potential hint words.

[0051] The hint word filtering module is used for filtering the extracted hint words based on pre-constructed rules.

[0052] The query plan generation module is used for generating a query plan after binding the hint words and encoding the query plan.

[0053] The representation module is used for deep representation of the query plan by using a self-attention mechanism.

[0054] The performance prediction module is used for predicting the performance distribution of the query plan by using a Gaussian process regression model and screening an optimal query plan.

[0055] The execution module is used for executing the optimal query plan and outputting a query result.

[0056] The application further provides application of the query optimization method or the query optimization system in reducing database query delay and improving database performance.

[0057] The application has the following beneficial effects:

[0058] The application provides a query plan representation method based on a self-attention mechanism, which uses various statistical information (such as a histogram (Histogram), a high-frequency value (MCV) and a number of different elements (NDV)) in a database to encode a query plan, and also considers more query plan information. Based on this representation method, a learning optimizer can better capture the key information of a query plan to distinguish different query plans, and provide strong support for a subsequent prediction module.

[0059] The application implements an end-to-end learning optimizer. In the aspect of generating a hint word set, a method of dynamically binding hint words from an SQL level is designed, the method extracts hint words from an SQL, filters the hint words based on some heuristic rules and a greedy algorithm, and finally forms a hint word set with multiple combinations.

[0060] The present application proposes a set of methods for identifying possible estimation error risks in query plans. It includes a comprehensive consideration of the timeliness of statistical information, inaccurate cardinality estimation, and inaccurate cost model estimation. When applied in a learning optimizer, this method can help the performance predictor more accurately predict the delay distribution of the query plan. Based on the delay distribution, the optimizer does not necessarily choose the query plan with the smallest estimated delay, but can make a comprehensive consideration based on the distribution.

[0061] The present application is integrated into the commercial database TiDB and tested using JOB [4] , TPC-H [5] . The overall delay of TPC-H is reduced by 33%, and the overall delay of JOB is reduced by 20%. From the perspective of each query, Q5 in TPC-H reduces the query delay by 75.0% after using the hint of leading(tpch.customer, tpch.orders) selected by the model, and Q12 in TPC-H reduces the query delay by 71.8% after using the hash_join(tpch.orders) selected by the model. BRIEF DESCRIPTION OF DRAWINGS

[0062] In order to more clearly illustrate the technical solutions in the embodiments of the present application or the prior art, the following will briefly introduce the drawings needed to be used in the embodiment or prior art description. Obviously, the drawings in the following description are only some embodiments of the present application, and other drawings can also be obtained by those skilled in the art without creative labor.

[0063] Figure 1 is a query plan representation based on self-attention mechanism; it is divided into three parts, the left side is the original SQL statement, the right side is the encoded query plan, and the bottom is the encoder used in the encoding process.

[0064] Figure 2 is a flowchart of the present application, divided into two parts, the training part and the inference part.

[0065] Figure 3 is a model architecture diagram of the present application. DETAILED DESCRIPTION

[0066] The present application will be further described in detail in combination with the following specific embodiments and drawings. The process, conditions, experimental methods, etc. for implementing the present application are the general knowledge and common sense in the art, and the present application does not have special restrictions.

[0067] The present application provides a database query optimization method based on prompt word guidance, and the main implementation goals of the method include the following:

[0068] 1. Deep query plan representation: In the learning-based optimizer, the representation of the query plan is crucial, which will directly affect the ability of performance prediction. A good representation should fully capture the key features of the query plan, while also maintaining the compactness and interpretability of the representation, so that the prediction model can be quickly understood and processed. Existing query plan representation methods mainly focus on partial information of the query plan, such as operator type, join type, cardinality estimation, etc., but do not fully utilize the risk factors and local statistical information contained in the query plan, and do not extract all the key information. Therefore, the first goal of this invention aims to propose a comprehensive deep query plan representation method to better cover the core information of the query plan.

[0069] 2. Dynamic hint binding: The learning-based optimizer needs to continuously learn different query plans to ensure the generalization of the model, and selecting some excellent query plans from the vast query plan space has become one of the important research directions. Currently, there are papers [1,2] that propose using hints to limit the query plan enumeration space. The method is to use a manual way to combine 6 important Boolean type hints into 48 hint sets, and apply them to each SQL to limit the search space. However, this approach has certain limitations: on the one hand, the manually preprocessed hint set is workload-independent, i.e. the same set of plan exploration strategies is used for any SQL, which is not targeted. Two is that such a method can only deal with a small number of hint sets, if there are more hints (in fact, the number of hints that can have a significant effect in modern commercial databases is much more than 6), or the granularity of the hints is finer, the number of combinations will be too large. Therefore, the second goal of this invention is to propose a dynamic hint binding method and apply it to the end-to-end learning-based optimizer.

[0070] 3. Risk elimination mechanism in query optimization (important): In the actual production environment, there are many uncertain factors. For example:

[0071] (a) Uncertainty of statistical information: missing statistical information, outdated statistical information, which will lead to inaccurate statistical information.

[0072] (b) Uncertainty of cardinality estimation: column-to-column, table-to-table, single-point query, all rely on independence assumptions, which will lead to inaccurate cardinality estimation.

[0073] (c) Uncertainty of cost model: inaccurate cost estimation of query table lookup, etc. which will lead to inaccurate cost model estimation.

[0074] Current learning optimizer strategy cannot perceive these uncertainties, even if the data in the table has changed greatly, the optimizer will refer to the previous model estimation results, which will significantly affect the final query performance and query stability. Therefore, the third objective of the present application is to add a mechanism to capture uncertainty on the above end-to-end query optimizer to enhance query performance and stability.

[0075] The prompt word guided database query optimization method proposed in the present application first needs to train the query effect prediction model (performance predictor), and the training part includes the following steps:

[0076] Step I, read the SQL data set, execute SQL and get the real execution time, store the query plan of SQL and the real execution time in the database (such as SQLite). The purpose of storing in the database is to prevent repeated training. When executing the training part for the second time, step I can be skipped and step II can be directly executed.

[0077] Step II, obtain the statistical information of the corresponding table (all tables involved in the query SQL) in the database, including histogram (Histogram), high frequency value (MCV) and different element number (NDV) and other information.

[0078] Step III, read the information stored in the database in step I (including the query plan of one or more SQLs and the corresponding real execution time), encode the query plan of SQL to form an automatically labeled training data set, and the training data set formed by query encoding is updated in real time.

[0079] In the specific implementation process, the information that needs to be encoded in step III includes operator information, cardinality estimation information, cost estimation information, predicate information, sorting key, connection order, etc. The encoding method of the above information is as follows:

[0080] Operator information: each node contains the operator information of the node, such as HashJoin, MergeJoin, IndexScan, etc. Use the encoding dictionary to encode it into a non-repeating integer.

[0081] Cardinality estimation information: each node will output the cardinality information of the upper layer node for the subquery, indicating the estimated number of rows of the subquery, and is normalized to the [0, 1] interval through normalization methods including Min-Max, Z-Score, etc. In a specific embodiment, the cardinality information of the subquery is normalized to the [0, 1] interval using the Min-Max method, which simplifies the processing and speeds up the calculation.

[0082] Cost estimation information: the cost estimation is represented as the estimated cost of the current subquery, and is normalized to the [0, 1] interval by including normalization methods such as Min-Max, Z-Score, etc. In one specific embodiment, the method of Min-Max is used to normalize the estimated cost of the subquery to the [0, 1] interval, simplifying the processing and speeding up the calculation.

[0083] Predicate information: the predicates are encoded using the histogram (Histogram), high frequency value (MCV), and different element number (NDV) in the database statistics information. The predicates considered by the present application can be divided into two types, one is an equality predicate, and one is a range predicate. The equality predicate is used to compare whether two values are equal. In the SQL query, the equality predicate is usually represented by the = operator (the predicate of the In operation can also be regarded as an equality predicate, that is, regarded as a set of multiple equality predicates). It checks whether the value of a certain column matches the specified value exactly. The range predicate is used to specify the range of a value, usually involving < (less than), > (greater than), <= (less than or equal to), >= (greater than or equal to), etc. In the SQL query, the range predicate is used to select records within a certain specified range. The common equality predicate encoding only uses NDV to estimate the encoding, without combining more statistical information such as the knowledge of MCV statistics, which can cause inaccurate encoding and affect the selection of the query plan. The encoding of the range predicate is the common encoding method. In one specific implementation process, the predicate in the form of col = value is called an equality predicate (the predicate of the In operation can also be regarded as an equality predicate, that is, regarded as a set of multiple equality predicates), wherein col and "=" are encoded into integers using the encoding dictionary, and value is encoded into the selectivity of the predicate. The specific process is as follows: first, check whether the value exists in the MCV, if it exists, its selectivity is its frequency, if it does not exist, its selectivity is recorded as 1 / NDV. The range predicate can use histogram information to count the range interval represented by it, and then divide the range interval by the total range interval to obtain its selectivity.

[0084] Order by: the order by is encoded into an integer using the encoding dictionary.

[0085] Join order: the join order refers to the connection order between tables in a multi-table connection, which can be obtained by traversing the query plan, and then encoded into an integer using the encoding dictionary.

[0086] Step IV, extract the risk information in the query plan and encode it, splice the encoded risk information to the encoded query plan operator obtained in step III, and take the spliced encoded vector as the data in the data set;

[0087] The data in the data set is data formed by splicing the code vector formed by splicing the code obtained in step IV to step III;

[0088] The risk information in step IV includes but is not limited to the following three types, each type includes several risks:

[0089] 1. Risk of cardinality estimation: including the dependence of cardinality estimation between columns on the independence assumption, the independence assumption of table and table connection, and the dependence of single-point query on the uniform assumption;

[0090] 2. Risk of statistical information: including missing statistical information, out-of-bound statistical information, and out-of-date statistical information;

[0091] 3. Risk of cost model: including inaccurate table lookup plan estimation, new operator, and inaccurate special operator cost estimation;

[0092] The above risks will be evaluated when each operator is coded. If the operator has the risk, the corresponding position is set to 1, otherwise it is set to 0. Finally, it will be coded into an 8-bit 0 / 1 vector, which will be spliced at the tail of the operator coded in step III.

[0093] Step V, training model. The data set of step IV is used to train the performance predictor of the query plan. The model parameters obtained by the current training are persisted to disk storage.

[0094] As Figure 3 The model architecture diagram of the present application is shown. In the process of model training, the present application uses a self-attention mechanism to generate a high-dimensional vector embedding of query features. Self-attention allows the model to consider the dependencies between different parts of the sequence when processing sequence data. In this way, a vector representation in a high-dimensional space can be generated for each query, which captures the key features of the query. The generated high-dimensional vector embedding is then input into a Gaussian Process Regression model. Gaussian Process Regression is a non-parametric Bayesian method that models functions by defining a prior probability distribution. It not only provides prediction results, but also gives an uncertainty estimate of the prediction, which can identify queries with high model prediction uncertainty and take appropriate measures to improve the accuracy of optimization.

[0095] In the present application, the performance predictor obtained by the above training can be used for prompt word guided database optimization queries, including the following steps:

[0096] Step one, identify potential effective prompt words of SQL. This step only considers a single prompt word, and uses some pre-set strong rules to filter the generated single prompt word set.

[0097] Step one will identify the hint words that may appear in SQL, including the connection operation between two tables (HashJoin, NestedLoopJoin, MergeJoin, etc.), the access method of the table (IndexScan, SeqScan, etc.), the prefix of the table connection order (Leading, etc.), and special hint words (unique to different databases, such as USE_TOJA in TiDB).

[0098] The hint word filtering rules in the application are as follows: the hint words that meet any of the rules will be filtered:

[0099] 1. Only columns with indexes will use the NestedLoopJoin hint word;

[0100] 2. Only columns with indexes will use the IndexScan hint word;

[0101] 3. Only two tables with connection keys in SQL will use the Leading hint word.

[0102] Step two, combine the filtered single hint word set, this step only does the combination of two hint words, obtains the hint word pair, and filters according to the evaluation index of the traditional optimizer.

[0103] In step two, first put all the candidate hint words into the candidate list, take out one hint word each time (do not remove the hint word from the candidate list), and combine the hint word with all the hint words in the candidate list and put it into the optimizer to estimate the delay, put the hint word pair with a delay less than max_lantency at the tail of the candidate list. Repeat the above process until the end of the candidate list is traversed, and finally the hint words in the candidate list are the candidate hint word set.

[0104] Step three, bind the filtered hint word set to SQL, obtain the query plan corresponding to the SQL statement, and complete the encoding of the query plan.

[0105] Step four, extract the risk information in the query plan and encode it, splice the encoded risk information to the encoded query plan operator obtained in step three, and form the encoding vector after splicing as the data in the data set.

[0106] Step five, put the encoding of step four as a whole into the performance predictor, predict the execution delay of the query plan, and obtain the "optimal" query plan.

[0107] Since the model uses Gaussian process regression, the performance distribution of the query plan can be predicted, so this step filters out the query plan with high risk coefficient according to the set threshold (the default is 0.5, which will be adjusted according to the validation set), and selects an execution plan with the minimum estimated execution delay from the remaining query plans.

[0108] After the fifth step, it further comprises:

[0109] Step six, the query plan obtained in step five is put into the executor for execution to obtain the final result.

[0110] Step seven, the query plan obtained in step five and the execution delay obtained in step six are persisted to the database for subsequent model updating.

[0111] In the specific implementation process, the query optimization method in the application can also be integrated into databases including PostgreSQL, TiDB and the like, and significant performance improvement can be brought.

[0112] The three objectives of the prior art in the application are realized through the following technical solutions:

[0113] 1) Query plan representation based on self-attention mechanism:

[0114] The query plan is a series of steps and operations and descriptions generated by the database engine for executing a specific query statement, and is usually represented by a query plan operator tree. Different database operations are represented, such as table scanning, index lookup, connection operation, sorting, aggregation, etc. The edges of the tree represent the data flow relationship between operations. The application extracts key information and encodes it. Figure 1 The representation strategy of the query plan is shown:

[0115] Firstly, the application encodes all table information into a continuous increasing sequence and stores it as an encoding dictionary, such as Figure 1 Encoder Dict in the application. Then the entire query plan tree is traversed, and each node in the query plan operator tree is encoded (the following information is extracted):

[0116] 1. Operator information: each node contains the operator information of the node, such as HashJoin, MergeJoin, IndexScan, etc. The encoding dictionary is used to encode it into a non-repeating integer. For example, Figure 1 The TableScan(c) operator in the application is encoded as [3].

[0117] 2. Base estimate: each node outputs the base information of the upper layer node for the subquery, indicating the estimated number of rows of the subquery. The application normalizes it to the [0, 1] interval using the Min-Max method, such as Figure 1The middle cardinality estimate is encoded into [0.1].

[0118] 3. Cost estimate: The cost estimate is the estimated cost of the current subquery, which is also normalized to the interval [0, 1] by the Min-Max method, such as Figure 1 The middle cost estimate is encoded into [0.2].

[0119] 4. Predicate filtering: The present application encodes the predicates by using the histogram, MCV and NDV in the database statistics. The predicates considered by the present application can be divided into two types, one is the equality predicate, and the other is the range predicate. The predicate in the form of [col = value] is called the equality predicate (the predicate of the In operation can also be regarded as the equality predicate, that is, regarded as a set of multiple equality predicates), wherein the col and “=” are encoded into integers by using the encoding dictionary, and the value is encoded into the selectivity of the predicate. The specific process is as follows: first, check whether the value exists in the MCV, if it exists, the selectivity thereof is the frequency thereof, and if it does not exist, the selectivity thereof is recorded as 1 / NDV. The predicate in the form of [col op value] (op in [“<”, “>”, “<=”, “>=”]) is called the range predicate, and the histogram information can be used to count the range interval represented by the range predicate, and then the selectivity thereof is obtained by dividing the range interval thereof by the total range interval, such as Figure 1 In the middle, a.col2 in (‘a’, ‘b’) is encoded into [3, 2, 0.2], and c.col1>100 is encoded into [8, 1, 0.3].

[0120] 5. Order by: The order by is encoded into an integer by using the encoding dictionary, such as Figure 1 In the middle, order by a.col1 is encoded into [1].

[0121] 6. Join order: The join order refers to the connection order between the tables in the multi-table connection, which can be obtained by traversing the query plan, and then encoded into an integer by using the encoding dictionary, such as Figure 1 The query plan connection order of [1, 2, 3] is represented.

[0122] 2) Dynamic hint binding:

[0123] The specific steps of the dynamic hint binding are as follows:

[0124] Step 1: Identify SQL, extract the possible set of hint words, and then filter according to strong rules. The recommended set of hint words is fine-grained, and each hint word acts on one or several tables, so the initial set of hint words will be too large and needs to be filtered in advance. First, according to the different types of hint words, the hint words are divided into four groups, which are:

[0125] 1. join method class: HashJoin, MergeJoin, NestedLoopJoin

[0126] 2. access method class: IndexScan, TableScan

[0127] 3. join order class: Leading

[0128] 4. other class: Different databases will have different operators, such as the USE_TOJA operator in TiDB. When the value is True, the optimizer will try to convert the in(subquery) condition into join and aggregation.

[0129] Since each class of hint word set contains a large number of hint words, the present application filters according to the following strong rules:

[0130] 1. Only columns with indexes can use NestedLoopJoin hint words.

[0131] 2. Only columns with indexes can use IndexScan hint words.

[0132] 3. Only two tables with join keys in SQL can use Leading hint words.

[0133] Example Figure 1 The SQL in the example can identify the following hint words: HashJoin(a), HashJoin(b), HashJoin(c), MergeJoin(a), MergeJoin(b), MergeJoin(c), NestedLoopJoin(a), NestedLoopJoin(b), IndexScan(a), IndexScan(b), TableScan(a), TableScan(b), TableScan(c), Leading(a,b), Leading(a,c), Leading(b,a), Leading(b,c), Leading(c,a), Leading(c,b).

[0134] After filtering using the above rules, a number of single hints are obtained. If the size of the hint set after filtering according to the strong rules still exceeds a predetermined threshold m (in one specific embodiment, the filtering parameter of the size of the hint set is 20 by default), further filtering is required, otherwise the combination of the hint set is directly performed. Specifically, each hint obtained after the above filtering is combined with SQL and put into the optimizer, and the optimizer estimates its delay, and finally selects the top m query plans corresponding to the hint with small delay, denoted as single_hint_set, and the maximum estimated delay in the m query plans is recorded as max_lantency.

[0135] Step 2: Implementing multi-combination of hint set. First, all candidate hints are put into a candidate list, one hint is taken out each time (without removing the hint from the candidate list), and the hint is combined with all hints in single_hint_set and put into the optimizer to estimate the delay, and the hint combination with delay less than max_lantency is put into the tail end of the candidate list. Repeat the above process until the end of the candidate list is reached, and finally the hints in the candidate list are the candidate hint set.

[0136] 3) Risk plan representation enhanced end-to-end learning optimizer design:

[0137] 1. Increase the representation of uncertainty factors. When encoding the query plan tree, the invention integrates three levels of risk factors into the vector representation, including statistical information level, cardinality estimation level and cost model level. The above risks are evaluated when each operator is encoded, and if the operator has the risk, the corresponding position is set to 1, otherwise it is set to 0, and finally it is encoded into an 8-bit 0 / 1 vector, which is concatenated behind the corresponding encoded query plan operator.

[0138] For example, as shown in the query plan tree in Figure 1 , such as the HashJoin(a, b) operator, when calculating its risk information, the above eight risks need to be detected respectively.

[0139] (1) The cardinality estimation of multi-column filter condition depends on the independence assumption: since there is only one filter condition a.col2 in ('a', 'b'), there is no such assumption, and the position is set to 0.

[0140] (2) The cardinality estimation of multi-table join condition depends on the independence assumption: there is a connection between the a and b tables, and there is a connection key, so there is a connection assumption, and the position is set to 1.

[0141] (3) Single-point query depends on the uniform assumption: since there is no single-point query in the operator, the position is set to 0.

[0142] (4) Missing, out-of-bound, and expired statistics: whether the statistics of the two tables are indeed correct needs to be determined according to the actual situation. Assuming that the statistics are missing, but not out-of-bound and not expired, the code at this position is [1, 0, 0].

[0143] (5) Inaccurate estimation of the cost of returning the table: this operator does not have a return table condition, so the position is set to 0.

[0144] (6) Inaccurate cost model of new operators and special operators: the HashJoin operator is not a special operator, so the position is set to 0.

[0145] The final risk information is encoded as [0, 1, 0, 1, 0, 0, 0, 0], which is concatenated to the corresponding encoded query plan operator.

[0146] 2. Predict the performance distribution. The vector code formed after concatenation is input into the Gaussian process regression model, and the model predictor is changed from predicting the performance of the query plan to predicting the performance distribution of the query plan, which will provide key information for subsequent plan selection.

[0147] 3. Plan selection. The original plan selection is to select a query plan with the optimal estimated performance and hand it over to the executor for execution. However, due to the generalization problem of the prediction model, the predicted optimal plan may not be the actual optimal plan. Therefore, in the plan selection process, the present application will balance between exploring new plans and using known plans according to the predicted performance distribution.

[0148] For example, there are three query plans, and the predicted results are (200, 0.8), (600, 0.2), and (500, 0.3). The risk coefficient is set to 0.5, so the query plan with the predicted result (200, 0.8) is removed, and the query plan with the lowest estimated execution delay is selected from the remaining query plans, i.e., the query plan corresponding to (500, 0.3) is executed.

[0149] Embodiment

[0150] The JOB (7.5G) and TPC-H (1G) are used to test 31 and 22 SQLs respectively, and the delay gap between the present application (the present application method is integrated into TiDB) and the original TiDB and Bao method is compared.

[0151] The total delay of executing all SQLs by various methods is tested. The final results are as follows:

[0152] The present invention Native TiDB Bao TPC-H 23.8s 36.0s 28.6s JOB 150.3s 188.3s 166.2s

[0153] From each query perspective, Q5 in TPC-H reduces the query latency by 75.0% compared to native TiDB and 71.6% compared to Bao method after using the hint of leading (tpch.customer, tpch.orders) selected by the application, and Q12 in TPC-H reduces the query latency by 71.8% compared to native TiDB and 33.1% compared to Bao method after using the hash_join (tpch.orders) selected by the model.

[0154] References

[0155] [1] Marcus R, Negi P, Mao H, et al. Bao: Making learned query optimization practical [C]. SIGMOD. 2021: 1275-1288.

[0156] [2] Xu X, Zhao Z, Zhang T, et al. COOOL: A Learning-To-Rank Approach for SQL Hint Recommendations [J]. arXiv preprint arXiv:2304.04407, 2023.

[0157] [3] Zhu R, Chen W, Ding B, et al. Lero: A learning-to-rank query optimizer [J]. PVLDB, 2023, 16(6): 1466-1479.

[0158] [4] V. Leis, A. Gubichev, A. Mirchev, P. Boncz, A. Kemper, and T. Neumann. How Good Are Query Optimizers, Really? PVLDB, 9(3): 204-215, 2015.

[0159] [5] TPC-H benchmark. [Online]. Available: https: / / www.tpc.org / tpch /

[0160] The application can take the form of a computer program product accessible from one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) containing computer usable program code. The program code can be implemented in various computer languages, such as, for example, Java, JavaScript, and the like.

[0161] Computer program instructions implementing the methods of the present application can also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instructions which implement the Figure 1 one or more flowcharts and / or blocks Figure 1 one or more flowcharts and / or blocks

[0162] The computer program instructions can also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the Figure 1 one or more flowcharts and / or blocks Figure 1 one or more flowcharts and / or blocks

[0163] Although preferred embodiments of the application have been described, those skilled in the art will be able to make additional modifications and variations to the described embodiments without departing from the spirit and scope of the application. Accordingly, the appended claims are intended to encompass all such modifications and variations as falling within the scope of the application.

[0164] Obviously, numerous modifications and variations of the present application are possible in light of the above teachings. It is therefore to be understood that within the scope of the appended claims and their equivalents, the application can be practiced otherwise than as specifically described.

[0165] The present application is not limited to the embodiments described above. It is contemplated that changes and modifications can be made by one of ordinary skill in the art, which changes and modifications are both mode and within the scope of the application as claimed.

Claims

1. A method for query optimization based on prompt word guidance, characterized in that, The query optimization method comprises the following steps: Step one, identify the potential effective single hint word in SQL, and filter the single hint word by using pre-constructed rules; the hint word includes the connection operation between two tables, the access mode of the table, the prefix of the table connection order, and the hint word specific to different databases; The filtering rules include the following: Only columns with indexes will use the NestedLoopJoin hint word; or, Only columns with indexes will use the IndexScan hint word; or, Only two tables with connection keys in SQL will use the Leading hint word; Step two, combine the filtered single hint word into a binary combination to generate multiple candidate hint word pairs, and filter the current candidate hint word pair based on the evaluation index of the traditional optimizer; In step two, all candidate hint words are put into a candidate list, one hint word is taken out each time, and the taken-out hint word is combined with all hint words in the candidate list into a candidate hint word pair, and put into the traditional optimizer for pre-estimation delay. The hint word pair with a delay less than the expected maximum delay max_lantency is put at the tail end of the candidate list, and the above process is repeatedly repeated until the end of the candidate list is traversed. The hint word in the final candidate list is the candidate hint word set; Step three, bind the filtered candidate hint word pair to the SQL statement to generate one or more query plans, and encode the query plan; Step four, extract the risk information in the query plan and encode it, splice the encoded risk information to the encoded query plan operator, and form the encoded vector after splicing as the data in the data set; Step five, put the encoded vector in step four into the performance predictor to predict the execution delay distribution of the query plan, and obtain the optimal query plan; In step five, the construction and training of the performance predictor specifically comprises the following steps: Step I, read the SQL data set, execute the SQL and obtain the real execution time, store the query plan and the real execution time in the database; Step II, obtain the statistical information of all tables involved in the query SQL in the database; the statistical information includes histogram, high frequency value, and number of different elements; Step III, encode the query plan in step I to form an automatically labeled training data set, and update it in real time; Step IV, extract the risk information in the query plan and encode it, splice the encoded risk information to the encoded query plan operator in step III, and form the encoded vector after splicing as the data in the data set; In step IV, the risk information includes: the base number estimation between columns depends on the independence assumption, the independence assumption between table connections, and the base number estimation risk of single-point query depending on the uniform assumption, including statistical information missing, statistical information out of bounds, and statistical information out of date statistical information risk, including inaccurate table back plan estimation, new operator, and inaccurate cost model risk of special operator cost estimation; And / or, The risk of encoding each operator is evaluated. If a risk exists, the corresponding position is set to 1; otherwise, it is set to 0. Finally, an 8-bit 0 / 1 vector is obtained and concatenated to the end of the operator encoded in step III. Step V: Use the dataset obtained in Step IV to train a performance predictor for the query plan and persist the optimized model parameters. In step V, during the training of the performance predictor, a self-attention mechanism is used to generate high-dimensional vector embeddings of query features to capture the dependencies between each step in the query. Then, the high-dimensional vector embeddings are input into a Gaussian process regression model to predict the execution performance of the query and estimate the prediction uncertainty.

2. The query optimization method of claim 1, wherein, In step five, the performance predictor uses a Gaussian process regression model to predict the performance distribution of the query plan; The optimal query plan refers to the execution plan with the lowest estimated execution delay after filtering out execution plans that exceed a preset threshold.

3. The query optimization method of claim 1, wherein, In step III, the information in the query plan is encoded, including the following: The operator information of each node is encoded into unique integers using an encoding dictionary; and / or, Normalize the cardinality information of the subqueries output by each node to the parent node; and / or, Normalize the estimated cost of the current subquery; and / or, Encoding predicates using histograms, high-frequency values, and the number of distinct elements; and / or, Encode the sort key into an integer using an encoding dictionary; and / or, The join order obtained from the traversal query plan is encoded into integers using an encoding dictionary.

4. A query optimization system implementing the query optimization method according to any one of claims 1 to 3, characterized in that, The query optimization system includes: an SQL parsing module, a prompt word filtering module, a query plan generation module, a representation module, a performance prediction module, and an execution module; The SQL parsing module is used to receive and parse SQL query statements, and identify and extract potential prompt words; The prompt word filtering module is used to filter the extracted prompt words based on pre-built rules; The query plan generation module is used to generate a query plan after binding prompt words and encode it; The representation module utilizes a self-attention mechanism to deeply represent the query plan; The performance prediction module predicts the performance distribution of query plans using a Gaussian process regression model and selects the optimal query plan. The execution module is used to execute the optimal query plan and output the query results.

5. The query optimization method as described in any one of claims 1-3, or the query optimization system as described in claim 4, in the application of reducing database query latency and improving database performance.

Images