Database query optimization method and system based on cue word guidance and application

Abstract

The invention discloses a database query optimization method based on cue word guidance, which comprises the following steps of: 1, identifying a single potential effective cue word in an SQL (Structured Query Language), and filtering the single cue word by utilizing a pre-construction rule; step 2, performing binary combination on the filtered single cue words to generate a plurality of candidate cue word pairs, and filtering the current candidate cue word pair according to an evaluation index of a traditional optimizer; 3, binding the filtered candidate prompt word pairs with the SQL statement to generate one or more query plans, and encoding the query plans; 4, risk information in the query plan is extracted and coded, and after the coded risk information is spliced to the coded query plan operator, a spliced coding vector is obtained; and 5, putting the coding vector in the step 4 into a performance predictor, predicting delay distribution of the query plan, and obtaining an optimal query plan. The invention further discloses a query optimization system which has a wide application scene.

Description

Technical Field

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

[0002] In the data age, database is undoubtedly an important foundation for storing and managing data. Database is a system that stores and organizes data in an orderly and controllable way, providing data support for various applications. It can not only store a large amount of structured data efficiently, but also provide fast, secure and reliable data access and management functions. In the database system, the query optimizer occupies a vital 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] Traditional cost-based query optimizers aim to select candidate plans with the lowest estimated cost, where the cost represents execution latency or other user-defined metrics. A variety of techniques have been proposed in cost-based optimizers, such as data profiling, histograms, sampling, etc. These techniques have different assumptions, such as histograms rely on attribute independence and consistency of data distribution. These assumptions are not easy to achieve in real production, and if the actual conditions differ too much from the assumptions, it will cause serious performance regression.

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

[0005] Existing learning optimizers can include the following:

[0006] Bao [1]The paper is the first study to implement a learning query optimizer based on hint words. The core idea is to avoid learning the optimizer from scratch, rather than completely replacing the traditional query optimizer. Specifically, when SQL arrives, Bao uses a preset hint word set to act on the query, generates several query plans (there may be identical query plans), and then selects an estimated optimal query plan from the candidate query plans through Thompson sampling and executes it to get the actual latency. Finally, the query plan and latency are stored in the training pool to continuously update the model. The method proposed by Bao greatly reduces the training time and the amount of training data, and improves query efficiency.

[0007] COOOL [2] The paper inherits Bao's approach and makes some improvements. It believes that Bao's 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 estimation of exact cost to the estimation of the cost order of the query plan. 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] This paper inherits Bao's paper, which also believes that Bao can be optimized using LTR technology, and proposes a new query plan enumeration strategy to augment candidate query plans. Specifically, the cardinality estimator is used as a tuning knob for query plan exploration. The cardinality estimate of the subquery is intentionally enlarged or reduced by a fixed ratio before being input into the cost model. In this way, it may generate some better plans in the adjacent plan space.

[0009] However, existing learning optimizers have the following disadvantages:

[0010] (1) Query representation is not comprehensive enough. Previous query plan representation methods mostly focus on partial 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 fail to extract all key information.

[0011] (2) The current method of using prompt words has certain limitations. For example, Bao's paper manually combined 6 important Boolean prompt words into 48 prompt word sets, which were applied to each SQL statement to limit the search space. However, this method has certain limitations: on the one hand, the manually preprocessed prompt word set is workload-independent, that is, no matter what SQL statement is used, the same set of planned exploration strategies is used, which is not targeted. Second, this method can only deal with a small number of prompt word sets. If there are more prompt words (in fact, there are far more prompt words that can have a significant effect in modern commercial databases than these 6 types), or if the granularity of the prompt word effect is finer, there will be a problem of too many combinations.

[0012] (3) In actual production environments, query optimization involves many uncertain factors, such as uncertainty in statistical information (missing or outdated statistical information leads to inaccurate statistical information), uncertainty in cardinality estimation (columns and tables rely on the independence assumption, and single-point queries rely on the mean assumption), and uncertainty in cost models (inaccurate cost estimation of querying back to the table leads to inaccurate cost model estimation). The current learning optimizer strategy cannot perceive these uncertainties. Even if the data in the table changes dramatically, 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] In order to solve the deficiencies in the prior art, the purpose of the present invention is to provide a database query optimization method, system and application based on prompt word guidance.

[0014] The present invention provides a database query optimization method based on prompt word guidance, and the query optimization method comprises the following steps:

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

[0016] Step 2: Binary combination of the filtered single prompt words to generate multiple candidate prompt word pairs, and filter the current candidate prompt word pairs based on the evaluation index of the traditional optimizer;

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

[0018] Step 4: extract and encode the risk information in the query plan, splice the encoded risk information to the encoded query plan operator, and use the encoded vector formed by splicing as the data in the data set;

[0019] Step 5: Put the encoding vector in step 4 as a whole into the performance predictor, predict the execution distribution of the query plan, and obtain the optimal query plan.

[0020] In step 1, the prompt words include the connection operation between two tables, the table access method, the prefix of the table connection sequence, and the prompt 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; or,

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

[0025] Leading hints are only used for two tables with a join key in SQL.

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

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

[0028] In step 5, 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 smallest estimated execution delay after filtering the execution plans that exceed the preset threshold;

[0030] In a specific implementation, 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 invention, the construction and training of the performance predictor specifically includes the following steps:

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

[0033] Step II: Obtain statistical information of all tables involved in the query SQL in the database; the statistical information includes histograms, high-frequency values, the number of different elements, etc.;

[0034] Step III: Encode the query plan in step I to form an automatically labeled training data set, and update it in real time;

[0035] Step IV: extract and encode the risk information in the query plan, splice the encoded risk information to the query plan operator encoded in step III, and use the encoded vector formed by splicing as the data in the data set;

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

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

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

[0039] Encoding the operator information of each node into a non-repeating integer using a coding dictionary; and / or,

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

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

[0042] Encoding the predicate using histograms, high frequency values, number of different elements, etc.; and / or,

[0043] Encode the sort key as an integer using an encoding dictionary; and / or,

[0044] The join order obtained by traversing the query plan is encoded into integers using an encoding dictionary.

[0045] In step IV, the risk information includes: cardinality estimation risks such as the cardinality estimation between columns relying on the independence assumption, the independence assumption of the connection between tables, and the cardinality estimation risk of single-point query relying on the uniform assumption, statistical information risks such as missing statistical information, statistical information out of bounds, and statistical information expiration, and cost model risks such as inaccurate estimation of the table return plan, inaccurate cost estimation of new operators and special operators, etc.

[0046] and / or,

[0047] Evaluate the risk of each operator when encoding. Set the corresponding position to 1 when risk exists, otherwise set it to 0. Finally, an 8-bit 0 / 1 vector is obtained by encoding and concatenated to the tail of the operator encoded in step III.

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

[0049] The present invention also provides a query optimization system for implementing the query optimization method, the query optimization system comprising: an SQL parsing module, a prompt word filtering module, a query plan generating module, a characterization module, a performance prediction module, and an execution module;

[0050] The SQL parsing module is used to receive and parse SQL query statements, identify and extract potential prompt words;

[0051] The prompt word filtering module is used to filter the extracted prompt words based on pre-built rules;

[0052] The query plan generation module is used to generate a query plan bound to the prompt word and encode it;

[0053] The representation module uses a self-attention mechanism to deeply represent the query plan;

[0054] The performance prediction module predicts the performance distribution of the query plan and selects the optimal query plan through the Gaussian process regression model;

[0055] The execution module is used to execute the optimal query plan and output the query result.

[0056] The present invention also provides the above-mentioned query optimization method, or the application of the above-mentioned query optimization system in reducing database query delay, improving database performance in an integrated manner, etc.

[0057] The beneficial effects of the present invention include:

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

[0059] The present invention implements an end-to-end learning optimizer. In terms of generating a prompt word set, a method is designed to support dynamic binding of prompt words from the SQL level, which extracts prompt words from SQL and filters them based on some heuristic rules and greedy algorithms to finally form a prompt word set with multiple combinations.

[0060] The present invention proposes a method for identifying the possible risk of misestimation in query plans. It includes comprehensive consideration of the timeliness of statistical information, cardinality estimation, and inaccurate cost model estimation. Applying this method in a learning optimizer 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 comprehensive considerations based on the distribution.

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

[0062] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying any creative work.

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

[0064] Figure 2 It is a flow chart of the present invention, which is divided into two parts, a training part and an inference part.

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

[0066] The present invention is further described in detail with reference to the following specific examples and drawings. The process, conditions, experimental methods, etc. for implementing the present invention, except for the contents specifically mentioned below, are all common knowledge and common common sense in the art and are not particularly limited by the present invention.

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

[0068] 1. Deep query plan characterization: In a learning optimizer, the characterization of query plans is crucial and will directly affect the ability to predict performance. A good characterization method should fully capture the key features of the query plan, while also maintaining the compactness and interpretability of the characterization so that the prediction model can quickly understand and process it. Most of the existing query plan characterization methods focus on partial information of the query plan, such as operator type, connection 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 the present invention is to propose a comprehensive deep query plan characterization method to better cover the core information of the query plan.

[0069] 2. Dynamic hint word binding: The learning optimizer needs to ensure the generalization of the model by continuously learning different query plans. Selecting some excellent query plans from the massive query plan space has become one of the important research directions. [1,2] A method of using prompt words is proposed to limit the query plan enumeration space. The method is to manually combine 6 important Boolean prompt words into 48 prompt word sets, which are applied to each SQL respectively to achieve the purpose of limiting its search space. However, this method has certain limitations: on the one hand, the manually pre-processed prompt word set is load-independent, that is, no matter what SQL is used, the same set of plan exploration strategies are used, which is not targeted. Second, such a method can only deal with a small number of prompt word sets. If there are more prompt words (in fact, there are far more prompt words that can play a significant role in modern commercial databases than these 6 types), or the granularity of the prompt word effect is finer, there will be a problem of too many combinations. Therefore, the second goal of the present invention is to propose a dynamic prompt word binding method and apply it to an end-to-end learning 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 and outdated statistical information will lead to inaccurate statistical information.

[0072] (b) Uncertainty in cardinality estimation: Columns and tables rely on independence assumptions, and single-point queries rely on mean assumptions, which can lead to inaccurate cardinality estimation.

[0073] (c) Uncertainty of the cost model: Inaccurate cost estimation of querying the table will lead to inaccurate estimation of the cost model.

[0074] The current learning optimizer strategy cannot perceive these uncertainties. Even if the data in the table changes dramatically, the optimizer will still refer to the previous model estimation results, which will significantly affect the final query performance and query stability. Therefore, the third goal of the present invention is to add a mechanism to capture uncertainty to the above end-to-end query optimizer to enhance query performance and stability.

[0075] The database query optimization method based on prompt word guidance proposed in the present invention 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 obtain the actual execution time, and store the SQL query plan and actual execution time in a 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, you can skip step I and directly execute step II.

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

[0078] Step III, read the information stored in the database in step I (including one or more SQL query plans and the corresponding actual execution time), encode the SQL query plan, form an automatically annotated training data set, and the training data set formed by the 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, sort 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., which is encoded into a non-repeating integer using the encoding dictionary.

[0081] Cardinality estimation information: Each node outputs the subquery cardinality information for the upper node, indicating the estimated number of rows of the subquery, and normalizes it to the interval [0, 1] using normalization methods such as Min-Max and Z-Score. In a specific implementation, the Min-Max method is used to normalize the subquery cardinality information to the interval [0, 1] to simplify processing and speed up calculations.

[0082] Cost estimation information: The cost estimate is expressed as the estimated cost of the current subquery, and is normalized to the interval [0, 1] using normalization methods including Min-Max and Z-Score. In a specific implementation, the Min-Max method is used to normalize the estimated cost of the subquery to the interval [0, 1] to simplify processing and speed up calculations.

[0083] Predicate information: The predicate is encoded using the histogram, high frequency value (MCV) and number of different elements (NDV) in the database statistical information. The predicates considered in the present invention can be divided into two types, one is an equal value predicate and the other is a range predicate. An equal value predicate is used to compare whether two values ​​are equal. In SQL queries, equal value predicates are usually represented by the = operator (the predicate of the In operation can also be regarded as an equal value predicate, that is, as a set of multiple equal value predicates). It checks whether the value of a column completely matches the specified value. Range predicates are used to specify a range of values, usually involving operators such as < (less than), > (greater than), <= (less than or equal to), >= (greater than or equal to). In SQL queries, range predicates are used to select records within a specified range. Common equal value predicate encoding only uses NDV for estimated encoding, without combining more statistical information, such as knowledge of MCV statistics, which will cause inaccurate encoding and affect the selection of query plans. Encoding of range predicates is a common encoding method. In a specific implementation process, a predicate of the form col=value is called an equivalue predicate (the predicate of the In operation can also be regarded as an equivalue predicate, that is, as a set of multiple equivalue predicates), where col and "=" are encoded as integers using a coding dictionary, and value is encoded as the selectivity of the predicate. The specific process is: first check whether 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 it represents, and then divide its range interval by the total range interval to get its selectivity.

[0084] Sort key (order by): The sort key is encoded into an integer using the encoding dictionary.

[0085] Join order: The join order refers to the order in which tables are joined in a multi-table join. The join order can be obtained by traversing the query plan and then encoded into an integer using a coding 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 use the encoded vector formed by splicing as the data in the data set;

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

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

[0089] 1. Risks of cardinality estimation: Cardinality estimation between columns relies on the independence assumption, the independence assumption of table connections, and single-point queries rely on the uniformity assumption;

[0090] 2. Risks of statistical information: including missing statistical information, out-of-bounds statistical information, and expired statistical information;

[0091] 3. Risks of cost model: including inaccurate estimation of return plan, inaccurate estimation of cost of new operators and special operators;

[0092] The above risks will be evaluated when each operator is encoded. If the operator has the risk, the corresponding position will be set to 1, otherwise it will be set to 0. It will eventually be encoded into an 8-bit 0 / 1 vector, which will be concatenated to the end of the operator encoded in step III.

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

[0094] like Figure 3 The model architecture diagram of the present invention is shown. During the model training process, the present invention uses a self-attention mechanism to generate high-dimensional vector embeddings of query features. Self-attention enables the model to take into account the dependencies between the various 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, and this vector 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 can not only provide prediction results, but also give prediction uncertainty estimates, and can identify queries with high model prediction uncertainty, so that corresponding measures can be taken to improve the accuracy of optimization.

[0095] In the present invention, the performance predictor obtained by the above training can be used for database optimization query guided by prompt words, comprising the following steps:

[0096] Step 1: Identify potentially effective SQL prompt words. This step only considers a single prompt word and uses some preset strong rules to filter the generated single prompt word set.

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

[0098] The prompt word filtering rules in the present invention are as follows, and prompt words that meet any of the rules will be filtered:

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

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

[0101] 3. Leading hints are only used for two tables with a join key in SQL.

[0102] Step 2: Combine the filtered single prompt word set. In this step, only two prompt words are combined to obtain prompt word pairs, and then filter them according to the evaluation indicators of the traditional optimizer.

[0103] In step 2, first, all candidate prompt words are put into the candidate list, and then one prompt word is taken out each time (without removing the prompt word from the candidate list), and the prompt word is combined with all the prompt words in the candidate list and put into the optimizer to estimate the delay, and the prompt word pairs with delay less than max_lantency are put at the end of the candidate list. The above process is repeated until the end of the candidate list is traversed, and the prompt words in the final candidate list are the candidate prompt word set.

[0104] Step 3: Bind the filtered prompt word set to SQL, obtain the query plan corresponding to the SQL statement, and complete the encoding of the query plan.

[0105] Step 4: Extract the risk information in the query plan and encode it. After splicing the encoded risk information to the encoded query plan operator obtained in step 3, the encoded vector formed after splicing is used as the data in the data set.

[0106] Step 5: Put the code in step 4 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, it can predict the performance distribution of query plans. Therefore, this step will filter out query plans with higher risk factors based on the set threshold (the default is 0.5, and this parameter will be adjusted based on the validation set later) and select an execution plan with the smallest estimated execution delay from the remaining query plans.

[0108] After step five, the method further includes:

[0109] Step 6: Put the query plan obtained in step 5 into the executor for execution to obtain the final result.

[0110] Step 7: Persist the query plan obtained in step 5 and the execution delay obtained in step 6 in the database for subsequent model update.

[0111] In the specific implementation process, the query optimization method of the present invention can also be integrated into databases including PostgreSQL, TiDB, etc., which can bring significant performance improvements.

[0112] Compared with the prior art, the three objectives of the present invention are achieved through the following technical solutions:

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

[0114] A query plan is a series of steps, operations and descriptions generated by a database engine to execute a specific query statement, usually represented by a query plan operator tree. It represents different database operations, such as table scans, index lookups, join operations, sorting, aggregation, etc. The edges of the tree represent the data flow relationship between the operations. The present invention extracts key information and encodes it. Figure 1 This shows the query plan representation strategy:

[0115] First, the present invention encodes the information of all tables into a continuously increasing series and stores it as a coding dictionary, such as Figure 1 Then traverse the entire query plan tree and encode each node in the query plan operator tree (extract the following information):

[0116] 1. Operator information: Each node contains the operator information of the node, such as HashJoin, MergeJoin, IndexScan, etc., which is encoded into a non-repeating integer using the encoding dictionary. Figure 1 The TableScan(c) operator is encoded as [3].

[0117] 2. Cardinality estimation: Each node will output the cardinality information of the subquery for the upper node, indicating the estimated number of rows of the subquery. The present invention uses the Min-Max method to normalize it to the interval [0,1], such as Figure 1The cardinality estimate is encoded as [0.1].

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

[0119] 4. Predicate filtering: The present invention uses the histogram (Histogram), high frequency value (MCV) and number of different elements (NDV) in the database statistical information to encode the predicate. The predicates considered in the present invention can be divided into two types, one is the equivalent predicate and the other is the range predicate. The predicate of the form [col=value] is called an equivalent predicate (the predicate of the In operation can also be regarded as an equivalent predicate, that is, as a set of multiple equivalent predicates), where col and "=" are encoded into integers using a coding dictionary, and value is encoded into the selectivity of the predicate. The specific process is: first check whether 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 predicate of the form [col op value] (op in ["<", ">", "<=", ">="]) is called a range predicate. The range predicate can use the histogram information to count the range interval it represents, and then divide its range interval by the total range interval to obtain its selectivity, such as Figure 1 In the example, a.col2 in ('a', 'b') is encoded as [3, 2, 0.2], and c.col1>100 is encoded as [8, 1, 0.3].

[0120] 5. Sort key (order by): The sort key is encoded into an integer using the encoding dictionary, such as Figure 1 In the example, order by a.col1 is encoded as [1].

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

[0122] 2) Dynamic prompt word binding:

[0123] The specific steps for binding dynamic prompt words are as follows:

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

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

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

[0127] 3. Join order class: Leading

[0128] 4.other category: Different databases have different operators. For example, in TiDB, the USE_TOJA operator, when the value is True, will enable the optimizer to try to convert the in (subquery) condition into join and aggregation.

[0129] Since each type of prompt word set contains a large number of prompt words, the present invention performs filtering according to the following strong rules:

[0130] 1. Only columns with indexes will use the NestedLoopJoin hint.

[0131] 2. Only columns with indexes will use the IndexScan hint.

[0132] 3. Leading hints are only used for two tables with a join key in SQL.

[0133] Example Figure 1 The following prompt words can be identified in the SQL: 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, several single hint words are obtained. If the size of the remaining hint word set after filtering according to the strong rule still exceeds the predetermined threshold m (in a specific implementation, the filtering parameter of the hint word set size defaults to 20), further filtering is required, otherwise the hint word set is directly combined. Specifically, each hint word obtained after the above filtering is combined with SQL and put into the optimizer, and the optimizer estimates its latency, and finally selects the hint word corresponding to the first m query plans with the smallest latency, which is recorded as single_hint_set, and the largest estimated latency among the m query plans is recorded as max_lantency.

[0135] Step 2: Implement multiple combinations of hint word sets. First, put all candidate hint words into the candidate list, take out one hint word at a time (do not remove the hint word from the candidate list), and combine the hint word with all hint words in single_hint_set and put them into the optimizer to estimate the delay, and put the hint word combination with a delay less than max_lantency at the end of the candidate list. Repeat the above process until you reach the end of the candidate list, and the hint words in the final candidate list are the candidate hint word set.

[0136] 3) Design of an end-to-end learning optimizer with enhanced risk plan representation:

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

[0138] like Figure 1 In the query plan tree, for example, when calculating the risk information of the HashJoin(a,b) operator, it is necessary to detect these eight risks separately.

[0139] (1) The cardinality estimation of multiple column filter conditions depends on the independence assumption: Since there is only one filter condition, a.col2 in('a','b'), this assumption does not exist and the position is set to 0.

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

[0141] (3) Single-point query relies on the uniform assumption: Since there is no single-point query for this operator, this position is set to 0.

[0142] (4) Statistical information missing, statistical information out of bounds, and statistical information expired: Regarding statistical information, it is necessary to determine whether the two tables a and b have statistical information based on the actual situation. Assuming that the statistical information is missing but not out of bounds or expired, the code here is [1,0,0].

[0143] (5) Inaccurate estimation of the return cost: There is no return to the table for this operator, so this position is set to 0.

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

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

[0146] 2. Predict performance distribution. The concatenated vector encoding is input into the Gaussian process regression model. The model predictor changes from predicting the performance of the original 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 best estimated performance and hand it over to the executor for execution. However, due to the generalization problem of the prediction model, the performance prediction is inaccurate, and the predicted optimal plan may not be the actual optimal plan. Therefore, in the plan selection process, the present invention will strike a balance between exploring new plans and using known plans based on 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 factor is set to 0.5. Then, the query plan with the predicted result of (200, 0.8) is eliminated, and the query plan with the lowest execution delay estimate, that is, the query plan corresponding to (500, 0.3), is selected from the remaining query plans for execution.

[0149] Example

[0150] Use JOB (7.5G) and TPC-H (1G) to test 31 and 22 SQLs respectively, and compare the latency difference between the present invention (the method of the present invention is integrated into TiDB) and the native TiDB and Bao methods.

[0151] Test the total delay of executing all SQL statements using various methods. The final results are shown in the following table:

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

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

[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

[0157] [3] Zhu R, Chen W, Ding B, et al. Lero: Alearning-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 present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program codes. The solutions in the embodiments of the present invention may be implemented in various computer languages, such as the object-oriented programming language Java and the interpreted scripting language JavaScript.

[0161] The computer program instructions for implementing the method of the present invention may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to work in a specific manner, so that the instructions stored in the computer-readable memory produce a manufactured product including an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0162] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0163] Although the preferred embodiments of the present application have been described, those skilled in the art may make other changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications falling within the scope of the present application.

[0164] Obviously, those skilled in the art can make various changes and modifications to the present application without departing from the spirit and scope of the present application. Thus, if these modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is also intended to include these modifications and variations.

[0165] The protection content of the present invention is not limited to the above embodiments. Without departing from the spirit and scope of the present invention, changes and advantages that can be thought of by those skilled in the art are included in the present invention and are protected by the attached claims.

Claims

1. A database query optimization method based on prompt word guidance, characterized in that: The query optimization method comprises the following steps: Step 1: Identify potentially valid single prompt words in SQL, and filter the single prompt words using pre-built rules; Step 2: Binary combination of the filtered single prompt words to generate multiple candidate prompt word pairs, and filter the current candidate prompt word pairs based on the evaluation index of the traditional optimizer; Step 3: Bind the filtered candidate prompt word pairs to SQL statements, generate one or more query plans, and encode the query plans; Step 4: extract and encode the risk information in the query plan, splice the encoded risk information to the encoded query plan operator, and use the encoded vector formed by splicing as the data in the data set; Step 5: Put the encoding vector in step 4 into the performance predictor, predict the execution delay distribution of the query plan, and obtain the optimal query plan.

2. The query optimization method according to claim 1, characterized in that: The prompt words include the connection operation between two tables, the table access mode, the prefix of the table connection sequence, and the prompt words specific to different databases; and / or, The filtering rules include the following: Only columns with indexes will use the NestedLoopJoin hint; or, Only columns with indexes will use the IndexScan hint; or, Leading hints are only used for two tables with a join key in SQL.

3. The query optimization method according to claim 1, characterized in that: In step 2, all candidate prompt words are put into the candidate list, and one prompt word is taken out each time. The taken prompt word is combined with all the prompt words in the candidate list to form a candidate prompt word pair, and then put into the traditional optimizer to estimate the delay. The prompt word pair with a delay less than the expected maximum delay max_lantency is put at the end of the candidate list. The above process is repeated until the end of the candidate list is traversed. The prompt words in the final candidate list are the candidate prompt word set.

4. The query optimization method according to claim 1, characterized in that: In step 5, the performance predictor uses a Gaussian process regression model to predict the performance distribution of the query plan; The optimal query plan refers to an execution plan with the smallest estimated execution delay after filtering execution plans that exceed a preset threshold.

5. The query optimization method according to claim 1, characterized in that: In step 5, the construction and training of the performance predictor specifically includes the following steps: Step I: read the SQL data set, execute the SQL and obtain the actual execution time, and store the query plan and the actual execution time in the database; Step II: Obtain 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 and encode the risk information in the query plan, splice the encoded risk information to the query plan operator encoded in step III, and use the encoded vector formed by splicing as the data in the data set; Step V: Use the data set obtained in step IV to train the performance predictor of the query plan and persist the optimized model parameters.

6. The query optimization method according to claim 5, characterized in that: In step III, the information in the query plan is encoded, including the following: Encoding the operator information of each node into a non-repeating integer using a coding dictionary; and / or, Normalizing the cardinality information of the subqueries output by each node for the upper node; and / or, Normalize the estimated cost of the current subquery; and / or, Encoding the predicate using histograms, high frequency values, and the number of distinct elements; and / or, Encode the sort key as an integer using an encoding dictionary; and / or, The join order obtained by traversing the query plan is encoded into integers using an encoding dictionary.

7. The query optimization method according to claim 5, characterized in that: In step IV, the risk information includes: risks of cardinality estimation between columns that rely on independence assumptions, independence assumptions between table connections, and cardinality estimation risks of single-point queries that rely on uniform assumptions; statistical information risks including missing statistical information, statistical information out of bounds, and statistical information expiration; and cost model risks including inaccurate estimates of table return plans, new operators, and inaccurate cost estimates of special operators; and / or, Evaluate the risk of each operator when encoding. Set the corresponding position to 1 when risk exists, otherwise set it to 0. Finally, an 8-bit 0 / 1 vector is obtained by encoding and concatenated to the tail of the operator encoded in step III.

8. The query optimization method according to claim 5, characterized in that: In step V, during the training process of the performance predictor, a self-attention mechanism is used to generate a high-dimensional vector embedding of query features to capture the dependencies between the steps in the query, and then the high-dimensional vector embedding is input into a Gaussian process regression model to predict the execution performance of the query and estimate the prediction uncertainty.

9. A query optimization system for implementing the query optimization method according to any one of claims 1 to 8, 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, 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 bound to the prompt word and encode it; The representation module uses a self-attention mechanism to deeply represent the query plan; The performance prediction module predicts the performance distribution of the query plan through the 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 result.

10. Application of the query optimization method according to any one of claims 1 to 8, or the query optimization system according to claim 9, in reducing database query latency and improving database performance in an integrated manner.

Images