Interpretable database query optimization method and system

Abstract

The invention discloses an interpretable database query optimization method and system, and relates to the field of database query optimizing.The method comprises the steps that a random forest model is constructed based on a genetic programming algorithm; obtaining a to-be-queried SQL statement; generating a prompt set based on a scanning method and a connection method, and generating a query plan set for each to-be-queried SQL statement according to the prompt set; encoding each query plan in the query plan set to obtain a feature vector set; inputting the feature vector set into a random forest model, and outputting a judgment rule set, feature importance ranking and a prediction execution time set; determining the query plan corresponding to the minimum prediction execution time in the prediction execution time set as an optimal query plan; based on the optimal query plan, a database query engine executes a query. According to the method, the database is queried through the determined optimal query plan, so that the execution efficiency and credibility of query can be improved.

Description

Technical Field

[0001] The present application relates to the field of database query optimization, and in particular to an explainable database query optimization method. Background Art

[0002] In database management systems, query optimization has always been the core point of its technical field. The core goal of database query optimization is to select the best execution plan for a given SQL query to minimize the query execution time and resource consumption. Traditional query optimizers mainly rely on heuristic rules and cost models. Heuristic rules are based on the experience and knowledge of database experts and are used to guide query optimizers to make decisions when searching for the optimal query plan. The cost model is used to estimate the execution cost of different query plans, including CPU time, memory usage, disk I / O, etc. The optimizer will select the query plan with the lowest cost to execute according to the cost predicted by the cost model. However, heuristic rules will fail in some scenarios, and the accuracy of the cost model depends on the accuracy and completeness of statistical information. Although traditional query optimizers can provide good performance in many cases, their optimization effect will be limited when facing complex queries and large data volumes.

[0003] In recent years, artificial intelligence has developed rapidly, and query optimization has been carried out by introducing technologies such as machine learning and deep learning. However, there are still key issues to be solved at this stage, which are specifically manifested in the following two aspects:

[0004] 1. The training cost of machine learning models is high, resulting in reduced database execution efficiency. The machine learning training process requires a large number of training and test sets and a lot of training time. In actual database application scenarios, it is difficult to provide a large amount of known data for the model, and database queries have strict speed requirements. A large amount of model-specific training time will seriously reduce query performance.

[0005] 2. The prediction results of machine learning models lack explainability. Although machine learning performs well in rule summarization and pattern recognition, its "black box" characteristics make it impossible to explain the decision basis of the prediction results, and it is difficult for professionals to judge the credibility of the prediction results. When optimization accidents occur, machine learning models cannot provide execution plans or other explanatory information for a single statement like traditional query optimizers, which makes it more difficult to fundamentally improve optimization technology. Summary of the invention

[0006] The purpose of this application is to provide an explainable database query optimization method, which can improve the execution efficiency and credibility of the query by querying the database with a determined optimal query plan.

[0007] To achieve the above objectives, this application provides the following solutions:

[0008] In a first aspect, the present application provides an explainable database query optimization method, the explainable database query optimization method comprising:

[0009] Based on the genetic programming algorithm, a random forest model is constructed;

[0010] Get the SQL statement to be queried;

[0011] Generate a prompt set based on the scanning method and the connection method, and generate a query plan set for each SQL statement to be queried according to the prompt set;

[0012] Encoding each query plan in the query plan set to obtain a feature vector set;

[0013] Inputting the feature vector set into the random forest model, outputting a judgment rule set, a feature importance ranking, and a predicted execution time set; the judgment rule set and the feature importance ranking are used to explain the predicted execution time set;

[0014] Determine the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan;

[0015] Based on the optimal query plan, the database query engine executes the query.

[0016] Optionally, a random forest model is constructed based on a genetic programming algorithm, specifically including:

[0017] Constructing a sample data set; the sample data set includes a sample query SQL statement set, a sample query plan set corresponding to the sample query SQL statement set, and a sample execution time set;

[0018] Encoding the sample query plans in the sample query plan set to obtain a sample feature vector set;

[0019] Initializing the sample feature vector set, generating multiple GP individuals, and obtaining a sample population;

[0020] Randomly select GP individuals from the sample population for mutation and crossover to obtain a first population;

[0021] Using a cross-validation method, the sample data set is divided into a training set and a test set;

[0022] Based on the training set, each GP individual in the first population is constructed in a random manner to generate multiple decision trees; one GP individual corresponds to one decision tree;

[0023] Estimate the generalization loss of all decision trees based on the test set to obtain a loss vector;

[0024] According to the loss vector, the decision tree is selected using a Lexicase selection operator to obtain an optimal decision tree;

[0025] A random forest model is constructed based on the optimal decision tree.

[0026] Optionally, construct a sample data set, specifically including:

[0027] Get a set of sample query SQL statements;

[0028] Processing the sample query SQL statement set through a database query optimizer to generate a sample query plan set;

[0029] The sample query plan set is executed by a database query engine to generate a sample execution time set.

[0030] Optionally, encoding the sample query plans in the sample query plan set to obtain a sample feature vector set specifically includes:

[0031] Traversing the nodes in each sample query plan in the sample query plan set to determine the characteristic index of each sample query plan; the nodes include: single nodes and double-layer parent-child nodes; the characteristic indexes include: the number of nodes, buffer metric value, execution cost, and the number of rows retrieved or returned;

[0032] Accumulating the characteristic index of each sample query plan to obtain a sample characteristic vector;

[0033] The sample feature vectors of all sample query plans are aggregated to obtain a sample feature vector set.

[0034] Optionally, the sample feature vector set is initialized to generate multiple GP individuals to obtain a sample population, specifically including:

[0035] Randomly selecting sample feature vectors in the sample feature vector set to obtain leaf nodes;

[0036] Randomly select the basic operators to obtain non-leaf nodes;

[0037] Based on the leaf nodes and the non-leaf nodes, a plurality of GP individuals are generated to obtain a sample population.

[0038] Optionally, estimating the generalization loss of all decision trees based on the test set to obtain a loss vector specifically includes:

[0039] Based on the test set, the decision tree is verified by cross-validation to obtain the predicted execution time corresponding to the test set;

[0040] According to the predicted execution time corresponding to the test set, a combination of absolute deviation and time ratio is used to obtain multiple loss binary groups; the multiple loss binary groups include a loss with an index of 0 and a loss with an index of 1;

[0041] Concatenate multiple loss binary tuples to obtain a loss vector.

[0042] Optionally, according to the loss vector, a Lexicase selection operator is used to select the decision tree to obtain an optimal decision tree, specifically including:

[0043] According to the first critical value, the decision tree is screened to obtain a first screening result; the first screening result is the decision tree whose loss of the loss binary group indexed as 0 in the loss vector is less than the first critical value; determining whether the decision tree is unique; if so, obtaining the best decision tree;

[0044] If not, the decision trees in the first screening result are screened according to the second critical value to obtain the best decision tree; the best decision tree is the decision tree in the first screening result whose loss of the loss binary group indexed as 1 in the loss vector is less than the second critical value.

[0045] Optionally, after determining the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan, the method further includes:

[0046] Executing the optimal query plan through a database query engine to generate an optimal execution time;

[0047] The training data set is updated based on the SQL statement set to be queried, the optimal query plan set, and the optimal execution time set.

[0048] In a second aspect, the present application provides an explainable database query optimization system, which is used to implement any of the above-mentioned explainable database query optimization methods, and the explainable database query optimization system includes:

[0049] A building block for building a random forest model based on a genetic programming algorithm;

[0050] The acquisition module is used to obtain the SQL statement to be queried;

[0051] A query plan generation module, used to generate a prompt set based on a scanning method and a connection method, and generate a query plan set for each SQL statement to be queried according to the prompt set;

[0052] A feature vector generation module, used for encoding each query plan in the query plan set to obtain a feature vector set;

[0053] An explanation module, used for inputting the feature vector set into the random forest model, and outputting a judgment rule set, a feature importance ranking, and a predicted execution time set; the judgment rule set and the feature importance ranking are used to explain the predicted execution time set;

[0054] An output module determines the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan;

[0055] The query module executes the query based on the optimal query plan.

[0056] Optionally, the explainable database query optimization system further includes:

[0057] The training data set updating module is used to execute the optimal query plan through the database query engine to generate the optimal execution time; update the training data set based on the SQL statement to be queried, the optimal query plan and the optimal execution time.

[0058] According to the specific embodiments provided in this application, this application has the following technical effects:

[0059] (1) This application uses a genetic programming algorithm to construct a complex feature generation random forest model, replaces the feature vector of a complex structure with a one-dimensional feature vector, and replaces the complex model with a smaller model, effectively reducing the performance burden caused by training time, thereby improving the efficiency of model query execution. At the same time, the genetic programming algorithm combines complex features to significantly enhance the model prediction accuracy.

[0060] (2) The present application inputs a feature vector set into a random forest model, and outputs a judgment rule set, a feature importance ranking, and a predicted execution time set. When the random forest model outputs the predicted execution time set, the application also outputs the judgment rule set used by the random forest model when making predictions, as well as the feature importance ranking of the feature vector set. This allows the application to output the decision of the predicted execution time set result while outputting the predicted execution time set result, thereby enhancing the interpretability of the prediction result and improving the credibility of the decision. BRIEF DESCRIPTION OF THE DRAWINGS

[0061] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings required for use in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present application. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0062] Figure 1 A flowchart of an explainable database query optimization method provided in one embodiment of the present application.

[0063] Figure 2 for Figure 1 Detailed process diagram.

[0064] Figure 3 for Figure 1 A detailed flowchart of the steps for building a random forest model in .

[0065] Figure 4 A schematic diagram of a query plan tree structure and node structure types according to an embodiment of the present application.

[0066] Figure 5 A schematic diagram of a technical route for constructing or iterating a random forest model in one embodiment of the present application.

[0067] Figure 6 A schematic diagram of the feature importance ranking output by the random forest according to an embodiment of the present application.

[0068] Figure 7 A schematic diagram of the judgment rules output by the random forest according to an embodiment of the present application.

[0069] Figure 8 A schematic diagram of a technical route of an explainable database query optimization method provided in another embodiment of the present application. DETAILED DESCRIPTION

[0070] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0071] In order to make the above-mentioned objects, features and advantages of the present application more obvious and easy to understand, the present application is further described in detail below with reference to the accompanying drawings and specific implementation methods.

[0072] In an exemplary embodiment, Figure 1-Figure 2 As shown, an explainable database query optimization method is provided, which includes the following steps:

[0073] Step 1: Construct a random forest model based on the genetic programming algorithm.

[0074] Step 2: Get the SQL statement to be queried.

[0075] Step 3: Generate a hint set based on the scanning method and the connection method, and generate a query plan set for each SQL statement to be queried according to the hint set.

[0076] Step 4: Encode each query plan in the query plan set to obtain a feature vector set.

[0077] Step 5: Input the feature vector set into the random forest model and output the judgment rule set, feature importance ranking and predicted execution time set; the judgment rule set and feature importance ranking are used to explain the predicted execution time set.

[0078] Step 6: Determine the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan.

[0079] Step 7: Based on the optimal query plan, the database query engine executes the query.

[0080] In a specific embodiment, Figure 3 As shown, step 1 specifically includes:

[0081] Step 11: Construct a sample data set; the sample data set includes a sample query SQL statement set, a sample query plan set corresponding to the sample query SQL statement set, and a sample execution time set.

[0082] Step 12: Encode the sample query plans in the sample query plan set to obtain a sample feature vector set.

[0083] Step 13: Initialize the sample feature vector set, generate multiple GP individuals, and obtain the sample population.

[0084] Step 14: Randomly select GP individuals from the sample population for mutation and crossover to obtain the first population.

[0085] Step 15: Using a cross-validation method, the sample data set is divided into a training set and a test set.

[0086] Step 16: Based on the training set, each GP individual in the first population is constructed by random construction to generate multiple decision trees; one GP individual corresponds to one decision tree.

[0087] Step 17: Estimate the generalization loss of all decision trees based on the test set and obtain the loss vector.

[0088] Step 18: According to the loss vector, the Lexicase selection operator is used to select the decision tree to obtain the best decision tree.

[0089] Step 19: Build a random forest model based on the best decision tree.

[0090] In a specific embodiment, step 11 specifically includes:

[0091] Step 111: Obtain a sample query SQL statement set.

[0092] Step 112: Process the sample query SQL statement set through a database query optimizer to generate a sample query plan set.

[0093] Step 113: The sample query plan set is processed by the database executor to generate a sample execution time set. After the execution is completed, the query plan P and the actual execution time time are collected as an experience P e ,time] is stored in the experience pool, where P e It is the query plan P, and time is the actual execution time.

[0094] In a specific embodiment, step 12 specifically includes:

[0095] Step 121: Traverse the nodes in each sample query plan in the sample query plan set to determine the characteristic indicators of each sample query plan; the nodes include: single nodes and double-layer parent-child nodes; the characteristic indicators include: the number of nodes, buffer metric value, execution cost, number of rows retrieved or returned; accumulate the characteristic indicators of each sample query plan to obtain a sample feature vector.

[0096] Specifically, set the node type to be encoded, traverse the tree query plan, and when a specific node type is found, according to the bit encoding method, the number of nodes Count, buffer metric value Buff, execution cost Cost, and number of rows retrieved and returned Row are accumulated in the corresponding bit of the one-dimensional vector as the feature vector Vec e For all P e Do this and get a Vecset e The nodes include: single node NodeType and double-layer parent-child node FCNodeType. By combining the two types of nodes, the node dependency relationship of the parent-child structure of the tree plan is obtained as much as possible. Figure 4 As shown, the gray pattern in the node type represents a single node, and the gray pattern in the parent-child node type represents a double-layer parent-child node.

[0097] Step 122: As shown in Table 1, the characteristic index of each sample query plan is accumulated to obtain a sample characteristic vector. The sample characteristic vector is a one-dimensional vector.

[0098] Table 1 Feature vector table based on bit coding

[0099]

[0100]

[0101]

[0102] Step 123: Summarize the sample feature vectors of all sample query plans to obtain a sample feature vector set.

[0103]

[0104] Among them, Pset 样 is the sample query plan set; Query plan for the first sample; Query plan for the second sample; Query plan for the nth sample; Vecset 样 is the sample feature vector set; is the first sample feature vector; is the second sample feature vector; is the nth sample feature vector.

[0105] In a specific embodiment, Figure 5 As shown, step 13 specifically includes: randomly selecting sample feature vectors in the sample feature vector set to obtain leaf nodes; randomly selecting basic operators to obtain non-leaf nodes; based on the leaf nodes and the non-leaf nodes, generating multiple GP individuals to obtain a sample population.

[0106] Specifically, the Ramped Half-and-Half method is used to perform the sampling feature vector set Vecset 样 Initialize and generate multiple GP individuals to obtain a sample population.

[0107] A GP individual I m Contains multiple GP trees, a GP tree gp t Contains leaf nodes and non-leaf nodes. Leaf nodes are sample feature vectors. A feature randomly selected from the non-leaf nodes contains basic operators such as addition, subtraction, multiplication, division, and square root.

[0108] T gp represents a set of complex features that can be used to construct a decision tree. They are combined into an I, which will be used to generate a decision tree in the future. After initialization, m I are generated, which means that the random forest model will have m decision trees in the future. Among them, t and m are the values ​​of the initial settings.

[0109] Iset: 1 ,I 2 …I m}.

[0110] I m ={gp 1 ,gp 2 …gp t}.

[0111] Among them, Iset is the sample population; I 1 is the first GP individual; I 2 is the second GP individual; I m is the mth GP individual; gp 1 For the first complex feature; gp 2 For the second complex feature; gp t is the tth complex feature.

[0112] In a specific embodiment, step 14 specifically includes:

[0113] The mutation operator randomly selects one of the I in Iset and randomly selects one of the gp and replaces it with a random new gp. The crossover operator randomly replaces the operator or feature of the gp in different I. In order to maintain the diversity of the population, the mutation operator is forced to generate a new I, thus generating the first population.

[0114] In a specific embodiment, step 15 specifically includes: using a five-fold cross-validation method to divide the data set into five subsets, taking four subsets as training sets each time, and the remaining one subset as a test set, and repeating five times.

[0115] In a specific embodiment, step 17 specifically includes:

[0116] Step 171: Based on the test set, the decision tree is evaluated by cross-validation to obtain the predicted execution time of the test set.

[0117] Step 172: Based on the predicted execution time of the test set, a combination of absolute deviation and time ratio is used to obtain multiple loss pairs. The calculation formula is:

[0118]

[0119] Among them, l i is the loss binary group of the i-th group in the five groups of data; f(X i ) is the total predicted execution time of the test set; Y i is the sum of the actual execution time of the test set; Pred i The total time spent on making predictions using a decision tree; |f(X i )-Y i | is the loss with index 0; is the loss with index 1.

[0120] Step 173: Concatenate multiple loss binary groups to obtain a loss vector, and the calculation formula is:

[0121]

[0122] Among them, Fitness x is the loss vector of the x-th decision tree; is the first loss tuple of the x-th decision tree; is the second loss tuple of the x-th decision tree; is the third loss binary group of the x-th decision tree; is the 4th loss binary group of the xth decision tree; is the fifth loss binary of the xth decision tree.

[0123] In a specific embodiment, step 18 specifically includes:

[0124] Step 181: According to the first critical value, the decision tree is screened to obtain the first screening result; the first screening result is the decision tree whose loss of the loss binary in the loss vector is indexed as 0 and is less than the first critical value. Randomly select a sequence number k∈[1,5] and screen The kth loss binary in A decision tree whose value is less than the first critical value. Determine whether the decision tree is unique; if so, obtain the best decision tree. The calculation formula of the first critical value is:

[0125]

[0126] Among them, θ k is the first critical value; mad k [0] is the median of the absolute values ​​of the deviations with index 0; is the loss with index 0 of the k-th loss binary of the loss vector of the i-th decision tree; The loss vector of the j-th decision tree has the loss at index 0 of the k-th loss binary.

[0127] Step 182: If not, then according to the second critical value, the decision trees in the first screening result are screened to obtain the best decision tree; the best decision tree is the decision tree whose loss of the loss binary index 1 in the loss vector is less than the second critical value. Less than the first critical value, continue screening The kth loss binary at index 1 Less than γ k GP individuals.

[0128]

[0129] Among them, γ k is the second critical value; mad k [1] is the median of the absolute values ​​of the deviations with index 1; is the loss with index 1 of the k-th loss binary of the loss vector of the i-th decision tree; The loss with index 1 of the k-th loss binary of the loss vector of the j-th decision tree.

[0130] If there are still multiple decision trees at this time If the value is less than the second critical value, the screening of step 181 and step 182 is repeated until the best decision tree is screened out.

[0131] Repeat steps 14 to 18, and predetermine m as the number of decision trees in the random forest. If the number of decision trees in the current random forest is less than m, continue to construct until the number of decision trees is m, and the construction or iteration is completed.

[0132] In a specific embodiment, step 3 specifically includes:

[0133] Generate a hint set based on the scan method and the join method, specifically including: using the pg_hint_plan plug-in to limit the scan methods and join methods that the query optimizer can use. Among them, the scan method includes: ScanMethod = {SeqScan, IndexScan, IndexOnlyScan, BitmapScan, TidScan}, and the join method includes: JoinMethod = {NestLoop, HashJoin, MergeJoin}. Next, construct the hint method Hint used by the database query optimizer. Each hint method Hint contains at least one ScanMethod and one JoinMethod. The expression is:

[0134]

[0135] Among them, Hint n For the nth prompt method; Scan 1 The first scanning method: Scan 2 This is the second scanning method; For nth 1 Scan method; Join 1 The first connection method; Join 2 For the second connection method; For nth 2 connection method;

[0136] According to experience, representative hint methods Hint are selected and combined into a hint set Hset:

[0137] Hset = {Hint 1 ,Hint2 …Hint n},n∈Z + ;

[0138] Among them, Hset is the hint set; Hint 1 Hint is the first hint method. 2 Hint is the second hint method. n This is the nth prompt method.

[0139] Generate a query plan set based on the hint set, specifically including: for each hint method Hint in the hint set Hset, the traditional optimizer generates a query plan P for it. The traditional optimizer limits the generation of query plans that only contain the scan method and connection method types listed in the hint method Hint, so each query plan contains different operation operators. Then, merge all the generated query plans P n The query plan set Pset is composed of:

[0140]

[0141] Among them, Pset is the query plan set; P n Plan for the nth query.

[0142] In a specific embodiment, step 4 specifically includes:

[0143] The sample query plans in the query plan set are encoded to obtain a feature vector set.

[0144]

[0145] Among them, Vecset is the feature vector set; Vec n is the nth sample feature vector.

[0146] In a specific embodiment, step 5 specifically includes:

[0147] The feature vector set Vecset is input into the random forest model, and the judgment rule set, feature importance ranking and predicted execution time set are output; the judgment rule set and feature importance ranking are used to explain the predicted execution time set.

[0148] In the prediction stage of the random forest model, the random forest model can collect the features of all decision trees, quantify the importance of the features according to the frequency of feature use and the position in the decision tree, and present them in the form of graphs. For specific graph examples, see Figure 6 , where the vertical axis represents the complexity of the feature and the horizontal axis represents the frequency of use. For example, the first one in the chart is X 15 +X 93 Represented by feature X 15 and feature X93 The most frequently used complex features are Represented by feature X 17 and feature X 58 The complex features composed of are used least frequently; at the same time, the random forest model judgment rule set of the output prediction results is used. The judgment rule of each decision tree is R n , as the judgment rule set RSet = {R 1 ,R 2 …R n}, explain the prediction results, and see some examples of judgment rules for details. Figure 7 , where the final predicted execution time is 0.43553945422172546, and the previous content in the figure represents the judgment rule of the decision tree output for the predicted time.

[0149] In a specific embodiment, after step 7, the method further includes:

[0150] like Figure 8 As shown, step 8: execute the optimal query plan through the database query engine to generate the optimal execution time.

[0151] Specifically, based on the actual execution results (optimal execution time) of the database query engine, experience is collected to update the sample data set in the experience pool. After the execution is completed, the query plan P is collected. min , and the actual execution time time, as a rule of thumb [P e ,time] is stored in the experience pool, where P e It's P min In order to prevent the dataset from becoming too large and increasing the training cost, only the latest K pieces of experience data are maintained in the experience pool, which are used as sample datasets for building or iterating the random forest model. K is the value of the initial setting.

[0152] Step 9: Update the training data set based on the SQL statement set to be queried, the optimal query plan set, and the optimal execution time set.

[0153] Specifically, based on the number of SQL statements executed in the database, the random forest model is constructed or iterated. After N SQL statements are queried, the random forest model construction or model iteration is triggered. Where N is the value of the initial setting. Take out K data in the experience pool [P e ,time] as the sample data set. Here P e is the query plan in the experience pool, and time is the actual execution time of the query plan. e Recode it into a one-dimensional feature vector Vec eThe iterative random forest model can adapt the model to the current data load and improve the accuracy of the prediction results. Figure 8 shown.

[0154] This application has the following technical effects:

[0155] (1) This application proposes to use a genetic programming algorithm to construct a feature vector to generate a random forest model, replace the feature vector of a complex structure with a one-dimensional feature vector, and replace the complex model with a smaller model, thereby effectively reducing the performance burden caused by training time and improving the execution efficiency of queries.

[0156] (2) This application can balance the training cost and model prediction ability, efficiently control the training cost, and use genetic programming algorithms to combine complex features to significantly enhance the model prediction accuracy.

[0157] (3) In view of the deficiency of existing research in being unable to make interpretable judgments on the prediction results of learning models for database query optimization, this application can output the decision rules and feature importance ranking charts of all decision trees, enhance the interpretability of the prediction values, and improve the credibility of the query.

[0158] Based on the same inventive concept, the embodiment of the present application also provides an interpretable database query optimization system. The implementation scheme for solving the problem provided by the system is similar to the implementation scheme recorded in the above method, so the specific limitations in one or more interpretable database query optimization system embodiments provided below can refer to the limitations of the interpretable database query optimization method above, and will not be repeated here.

[0159] In an exemplary embodiment, an explainable database query optimization system is provided, comprising:

[0160] Building module for building random forest models based on genetic programming algorithm.

[0161] The acquisition module is used to obtain the SQL statement to be queried.

[0162] The query plan generation module is used to generate a prompt set based on the scanning method and the connection method, and generate a query plan set for each SQL statement to be queried according to the prompt set.

[0163] The feature vector generation module is used to encode each query plan in the query plan set to obtain a feature vector set.

[0164] The explanation module is used to input the feature vector set into the random forest model, and output the judgment rule set, the feature importance ranking and the predicted execution time set; the judgment rule set and the feature importance ranking are used to explain the predicted execution time set.

[0165] The output module determines the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan.

[0166] The query module executes the query plan based on the optimal query plan.

[0167] Specifically, the explainable database query optimization system further includes:

[0168] The training data set update module is used to process the optimal query plan through the database executor query engine to generate the optimal execution time; and update the training data set based on the SQL statement to be queried, the optimal query plan and the optimal execution time.

[0169] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, stored data, displayed data, etc.) involved in this application are all information and data authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data must comply with relevant regulations.

[0170] Those of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to the memory, database or other medium used in the embodiments provided in the present application can include at least one of non-volatile and volatile memory. Non-volatile memory can include read-only memory (ROM), magnetic tape, floppy disk, flash memory, optical memory, high-density embedded non-volatile memory, resistive random access memory (ReRAM), magnetic random access memory (MRAM), ferroelectric random access memory (FRAM), phase change memory (PCM), graphene memory, etc. Volatile memory can include random access memory (RAM) or external cache memory, etc. By way of illustration and not limitation, RAM may be in various forms, such as static random access memory (SRAM) or dynamic random access memory (DRAM).

[0171] The database involved in each embodiment provided in this application only includes a relational database. The processor involved in each embodiment provided in this application can be a general-purpose processor, a central processing unit, a graphics processor, a digital signal processor, a programmable logic unit, a data processing logic unit based on quantum computing, etc., but is not limited thereto.

[0172] The technical features of the above embodiments may be combined arbitrarily. To make the description concise, not all possible combinations of the technical features in the above embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0173] This article uses specific examples to illustrate the principles and implementation methods of this application. The description of the above embodiments is only used to help understand the method and core ideas of this application; at the same time, for those skilled in the art, according to the ideas of this application, there will be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as limiting this application.

Claims

1. An explainable database query optimization method, characterized in that: The explainable database query optimization method comprises: Based on the genetic programming algorithm, a random forest model is constructed; Get the SQL statement to be queried; Generate a prompt set based on the scanning method and the connection method, and generate a query plan set for each SQL statement to be queried according to the prompt set; Encoding each query plan in the query plan set to obtain a feature vector set; Inputting the feature vector set into the random forest model, outputting a judgment rule set, a feature importance ranking, and a predicted execution time set; the judgment rule set and the feature importance ranking are used to explain the predicted execution time set; Determine the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan; Based on the optimal query plan, the database query engine executes the query.

2. The interpretable database query optimization method according to claim 1, characterized in that: Based on the genetic programming algorithm, a random forest model is constructed, which includes: Constructing a sample data set; the sample data set includes a sample query SQL statement set, a sample query plan set corresponding to the sample query SQL statement set, and a sample execution time set; Encoding the sample query plans in the sample query plan set to obtain a sample feature vector set; Initializing the sample feature vector set, generating multiple GP individuals, and obtaining a sample population; Randomly select GP individuals from the sample population for mutation and crossover to obtain a first population; Using a cross-validation method, the sample data set is divided into a training set and a test set; Based on the training set, each GP individual in the first population is constructed in a random manner to generate multiple decision trees; one GP individual corresponds to one decision tree; Estimate the generalization loss of all decision trees based on the test set to obtain a loss vector; According to the loss vector, the decision tree is selected using a Lexicase selection operator to obtain an optimal decision tree; A random forest model is constructed based on the optimal decision tree.

3. The interpretable database query optimization method according to claim 2, characterized in that: Construct a sample data set, including: Get a set of sample query SQL statements; Processing the sample query SQL statement set through a database query optimizer to generate a sample query plan set; The sample query plan set is executed by a database query engine to generate a sample execution time set.

4. The interpretable database query optimization method according to claim 2, characterized in that: The sample query plans in the sample query plan set are encoded to obtain a sample feature vector set, specifically including: Traversing the nodes in each sample query plan in the sample query plan set to determine the characteristic index of each sample query plan; the nodes include: single nodes and double-layer parent-child nodes; the characteristic indexes include: the number of nodes, buffer metric value, execution cost, and the number of rows retrieved or returned; Accumulating the characteristic index of each sample query plan to obtain a sample characteristic vector; The sample feature vectors of all sample query plans are aggregated to obtain a sample feature vector set.

5. The interpretable database query optimization method according to claim 2, characterized in that: Initializing the sample feature vector set to generate multiple GP individuals to obtain a sample population, specifically including: Randomly selecting sample feature vectors in the sample feature vector set to obtain leaf nodes; Randomly select the basic operators to obtain non-leaf nodes; Based on the leaf nodes and the non-leaf nodes, a plurality of GP individuals are generated to obtain a sample population.

6. The interpretable database query optimization method according to claim 2, characterized in that: Based on the test set, the generalization loss of all decision trees is estimated to obtain a loss vector, which specifically includes: Based on the test set, the decision tree is verified by cross-validation to obtain the predicted execution time corresponding to the test set; According to the predicted execution time corresponding to the test set, a combination of absolute deviation and time ratio is used to obtain multiple loss binary groups; the multiple loss binary groups include a loss with an index of 0 and a loss with an index of 1; Concatenate multiple loss binary tuples to obtain a loss vector.

7. The explainable database query optimization method according to claim 6, characterized in that: According to the loss vector, the decision tree is selected using the Lexicase selection operator to obtain the best decision tree, which specifically includes: According to the first critical value, the decision tree is screened to obtain a first screening result; the first screening result is the decision tree whose loss of the loss binary group indexed as 0 in the loss vector is less than the first critical value; determining whether the decision tree is unique; if so, obtaining the best decision tree; If not, the decision trees in the first screening result are screened according to the second critical value to obtain the best decision tree; the best decision tree is the decision tree in the first screening result whose loss of the loss binary group indexed as 1 in the loss vector is less than the second critical value.

8. The explainable database query optimization method according to claim 2, characterized in that: After determining the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan, the method further includes: Executing the optimal query plan through a database query engine to generate an optimal execution time; The training data set is updated based on the SQL statement set to be queried, the optimal query plan set, and the optimal execution time set.

9. An explainable database query optimization system, characterized in that: The interpretable database query optimization method applied to any one of claims 1 to 8, wherein the interpretable database query optimization system comprises: A building block for building a random forest model based on a genetic programming algorithm; The acquisition module is used to obtain the SQL statement to be queried; A query plan generation module, used to generate a prompt set based on a scanning method and a connection method, and generate a query plan set for each SQL statement to be queried according to the prompt set; A feature vector generation module, used for encoding each query plan in the query plan set to obtain a feature vector set; An explanation module, used for inputting the feature vector set into the random forest model, and outputting a judgment rule set, a feature importance ranking, and a predicted execution time set; the judgment rule set and the feature importance ranking are used to explain the predicted execution time set; An output module determines the query plan corresponding to the minimum predicted execution time in the predicted execution time set as the optimal query plan; The query module executes the query based on the optimal query plan.

10. The explainable database query optimization system according to claim 9, characterized in that: The explainable database query optimization system further includes: The training data set updating module is used to execute the optimal query plan through the database query engine to generate the optimal execution time; update the training data set based on the SQL statement to be queried, the optimal query plan and the optimal execution time.

Images