Database optimization method and device based on large model, equipment and medium

Abstract

The invention discloses a database optimization method and device based on a large model, equipment and a medium, and relates to the technical field of database performance optimizing.The method comprises the steps that a target SQL query is analyzed to obtain a target abstract syntax tree corresponding to the target SQL query, and candidate execution plans corresponding to the target SQL query are determined, determining a target feature vector of each candidate execution plan based on the target abstract syntax tree; training a preset deep learning model based on historical SQL query to obtain a target cost evaluation model, and determining a plan evaluation result corresponding to each candidate execution plan based on the target feature vector by using the target cost evaluation model; and determining a target execution plan from the candidate execution plans according to the plan evaluation result and a preset optimization plan selection mechanism, and executing the target SQL query based on the target execution plan by using the target database. In this way, the prediction precision of the SQL query execution cost can be remarkably improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database performance optimization, and in particular to a database optimization method, device, equipment and medium based on a large model. Background Art

[0002] With the rapid growth of data volumes and the increasing complexity of SQL queries, traditional database query optimizers face challenges in processing complex queries and large-scale datasets. Traditional optimizers typically rely on rule-based or cost model-based approaches, which rely on database statistics such as table size, index selectivity, and data distribution. However, these statistics often fail to fully reflect the dynamic nature of the data, making it difficult for traditional optimizers to accurately predict the execution cost of complex queries, which in turn affects the overall performance of the database. In recent years, machine learning and deep learning technologies have gradually been introduced into the field of database optimization. By learning from large amounts of historical query data and execution plans, large models can capture complex patterns in query execution and provide more accurate cost predictions. However, how to effectively design feature extraction methods, build and train models to improve prediction accuracy, and integrate these technologies into existing database systems remain major challenges. Summary of the Invention

[0003] In view of this, the present invention aims to provide a database optimization method, apparatus, device, and medium based on a large model, which can improve the prediction accuracy of SQL query execution costs. The specific solution is as follows:

[0004] In a first aspect, the present application provides a database optimization method based on a large model, comprising:

[0005] Parsing a target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query, and determining candidate execution plans corresponding to the target SQL query, so as to determine a target feature vector for each candidate execution plan based on the target abstract syntax tree;

[0006] A target cost evaluation model is obtained by training a preset deep learning model based on historical SQL queries, and a plan evaluation result corresponding to each candidate execution plan is determined based on the target feature vector using the target cost evaluation model;

[0007] A target execution plan is determined from the candidate execution plans according to the plan evaluation result and a preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan using the target database, so as to optimize the query process of the target database for the target SQL query based on the target execution plan.

[0008] Optionally, parsing the target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query includes:

[0009] Parsing the target SQL query to determine a target keyword, a target identifier, a target operator, and a target constant corresponding to the target SQL query, and determining a target grammar rule corresponding to the target SQL query;

[0010] Constructing an initial abstract syntax tree corresponding to the target SQL query based on a preset data structure, the target grammar rule, the target keyword, the target identifier, the target operator, and the target constant;

[0011] The initial abstract syntax tree is checked according to the target SQL query to obtain a corresponding check result, and the initial abstract syntax tree is updated based on the check result to obtain the target abstract syntax tree.

[0012] Optionally, determining a target feature vector for each candidate execution plan based on the target abstract syntax tree includes:

[0013] Determining an operator corresponding to the candidate execution plan based on the target abstract syntax tree, and determining a target type, a target action object, and a target complexity of the operator, so as to determine a target operator feature of the candidate execution plan based on the target type, the target action object, and the target complexity;

[0014] Obtaining metadata and statistical information of the target database based on a preset database interface, and determining target data features of the candidate execution plan based on the target abstract syntax tree, the metadata, and the statistical information;

[0015] The target nesting level, target number of subqueries, target subquery type, and target join complexity of the candidate execution plan are determined based on the target abstract syntax tree, and the target structural features of the candidate execution plan are determined based on the target nesting level, target number of subqueries, target subquery type, and target join complexity.

[0016] Optionally, before obtaining a target cost evaluation model by training a preset deep learning model based on historical SQL queries, the method further includes:

[0017] Obtaining an initial historical SQL query from the historical query log of the target database, and determining a historical query plan and a historical execution cost corresponding to the initial historical SQL query;

[0018] Performing data cleaning on the initial historical SQL query based on a preset data cleaning method to obtain a cleaned historical SQL query, and determining a target historical query plan and a target historical execution cost corresponding to the cleaned historical SQL query based on the historical query plan and the historical execution cost;

[0019] Determining a feature vector of the target historical query plan based on a preset feature engineering method and the cleaned historical SQL query, and constructing a target dataset based on the cleaned historical SQL query, the target historical query plan, the feature vector of the target historical query plan, and the target historical execution cost;

[0020] The target data set is divided into corresponding training sets, validation sets and test sets, so as to train the preset deep learning model based on the training set, the validation set and the test set to obtain the target cost evaluation model.

[0021] Optionally, the training of the preset deep learning model based on the training set, the validation set, and the test set to obtain the target cost evaluation model includes:

[0022] Inputting the feature vector of the target historical query plan in the training set into the preset deep learning model to obtain a corresponding output result, and determining a target loss value between the output result and the target historical execution cost corresponding to the target historical query plan based on a preset mean square error loss function;

[0023] Based on a preset gradient descent algorithm and the target loss value, the parameters of the preset deep learning model are updated to obtain a corresponding cost evaluation model to be optimized, and the hyperparameters of the cost evaluation model to be optimized are optimized based on a preset hyperparameter tuning method, the training set, and the validation set;

[0024] Based on the test set, a model evaluation is performed on the optimized cost evaluation model to be optimized to obtain a corresponding model evaluation result, and the target cost evaluation model is determined based on the model evaluation result and the cost evaluation model to be optimized.

[0025] Optionally, the plan evaluation result is an evaluation value of the execution cost of the candidate execution plan;

[0026] Accordingly, determining a target execution plan from the candidate execution plans according to the plan evaluation result and a preset optimization plan selection mechanism includes:

[0027] Determine the candidate execution plan corresponding to the minimum plan evaluation result as the target execution plan;

[0028] Alternatively, a confidence interval of a plan evaluation result corresponding to the candidate execution plan is determined, and a target width and a target weight corresponding to the confidence interval are determined, so as to determine a target score corresponding to the candidate execution plan based on the plan evaluation result, the target width, and the target weight, and determine the candidate execution plan corresponding to the smallest target score as the target execution plan;

[0029] Alternatively, a target execution cost threshold is set for each candidate execution plan, and the candidate execution plans whose plan evaluation results exceed the target execution cost threshold are filtered to obtain the execution plans to be selected, and the execution plan to be selected corresponding to the smallest plan evaluation result is determined as the target execution plan.

[0030] Optionally, executing the target SQL query based on the target execution plan using the target database includes:

[0031] Sending the target execution plan to a database execution engine corresponding to the target database based on a preset database interface, and using the database execution engine to execute the target SQL query based on the target execution plan to obtain a corresponding execution result;

[0032] caching the execution result corresponding to the target SQL query in a preset query result cache pool, so that the target database directly determines the execution result corresponding to the target SQL query based on the preset query result cache pool;

[0033] During the execution of the target SQL query, the execution status of the target execution plan is monitored in real time, and corresponding feedback information is generated based on the execution status, so as to update the target cost evaluation model in real time based on a preset online learning algorithm and the feedback information.

[0034] In a second aspect, the present application provides a database optimization device based on a large model, comprising:

[0035] a target feature vector determination module, configured to parse a target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query, and determine candidate execution plans corresponding to the target SQL query, so as to determine a target feature vector for each candidate execution plan based on the target abstract syntax tree;

[0036] A plan evaluation result determination module is used to obtain a target cost evaluation model by training a preset deep learning model based on historical SQL queries, and to determine a plan evaluation result corresponding to each candidate execution plan based on the target feature vector using the target cost evaluation model;

[0037] A target execution plan determination module is used to determine a target execution plan from the candidate execution plans based on the plan evaluation results and a preset optimization plan selection mechanism, and use the target database to execute the target SQL query based on the target execution plan, so as to optimize the query process of the target database for the target SQL query based on the target execution plan.

[0038] In a third aspect, the present application provides an electronic device, comprising:

[0039] Memory, used to store computer programs;

[0040] A processor is used to execute the computer program to implement the aforementioned database optimization method based on a large model.

[0041] In a fourth aspect, the present application provides a computer-readable storage medium for storing a computer program; wherein, when the computer program is executed by a processor, the aforementioned large model-based database optimization method is implemented.

[0042] In this application, the target SQL query is first parsed to obtain the target abstract syntax tree corresponding to the target SQL query, and the candidate execution plan corresponding to the target SQL query is determined, so as to determine the target feature vector of each candidate execution plan based on the target abstract syntax tree; then, a preset deep learning model is trained based on historical SQL queries to obtain a target cost evaluation model, and the target cost evaluation model is used to determine the plan evaluation result corresponding to each candidate execution plan based on the target feature vector; finally, a target execution plan is determined from the candidate execution plans based on the plan evaluation result and a preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan using the target database, so as to optimize the query process of the target database for the target SQL query based on the target execution plan. As can be seen from the above, in this application, the target SQL query is first parsed to obtain the target abstract syntax tree, and the target feature vector of each candidate execution plan of the target SQL query is determined based on the target abstract syntax tree, and then the target cost evaluation model and the target feature vector are used to determine the plan evaluation result of each candidate execution plan, and finally, the target execution plan is determined based on the plan evaluation result and the preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan. In this way, this application uses historical SQL queries to train a preset deep learning model to obtain a target cost evaluation model. Based on the target cost evaluation model, the plan evaluation results of each candidate execution plan are determined, thereby obtaining the target execution plan for the target SQL query. This significantly improves the prediction accuracy of the SQL query execution cost, thereby optimizing the SQL query execution plan. In addition, this application reduces manual intervention and can continuously adjust and optimize the execution plan based on actual execution conditions. This application can adapt to different database environments and meet the database system's needs for efficient query optimization. BRIEF DESCRIPTION OF THE DRAWINGS

[0043] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are merely embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on the provided drawings without paying any creative work.

[0044] Figure 1 A flowchart of a database optimization method based on a large model provided in this application;

[0045] Figure 2 A flowchart of a specific database optimization method based on a large model provided in this application;

[0046] Figure 3 A flowchart of a specific database optimization method based on a large model provided in this application;

[0047] Figure 4 A schematic diagram of the structure of a database optimization device based on a large model provided in this application;

[0048] Figure 5 This is a structural diagram of an electronic device provided in this application. DETAILED DESCRIPTION

[0049] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0050] With the rapid growth of data volume and the increase in the complexity of SQL queries, traditional database query optimizers face challenges in processing complex queries and large-scale data sets. Traditional optimizers usually rely on rule-based or cost model-based methods, which rely on database statistics, such as table size, index selectivity, data distribution, etc. However, these statistical information often cannot fully reflect the dynamic characteristics of the data, making it difficult for traditional optimizers to accurately predict the execution cost of complex queries, which in turn affects the overall performance of the database. In recent years, machine learning and deep learning technologies have gradually been introduced into the field of database optimization. By learning a large amount of historical query data and execution plans, large models can capture complex patterns in query execution and provide more accurate cost predictions. However, how to effectively design feature extraction methods, build and train models to improve prediction accuracy, and integrate these technologies into existing database systems remains a major challenge. To this end, the present application provides a database optimization solution based on a large model that can improve the prediction accuracy of SQL query execution costs.

[0051] See also Figure 1 As shown, the embodiment of the present invention discloses a database optimization method based on a large model, which may include:

[0052] Step S11: Parse the target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query, and determine candidate execution plans corresponding to the target SQL query, so as to determine a target feature vector of each candidate execution plan based on the target abstract syntax tree.

[0053] In this embodiment, in order to convert a SQL query statement submitted by a user into a structured representation, the above-mentioned parsing of the target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query may include: first parsing the target SQL query to determine the target keywords, target identifiers, target operators, and target constants corresponding to the target SQL query, and determining the target grammar rules corresponding to the target SQL query; then constructing an initial abstract syntax tree corresponding to the target SQL query based on a preset data structure, the target grammar rules, the target keywords, the target identifiers, the target operators, and the target constants; finally, checking the initial abstract syntax tree according to the target SQL query to obtain a corresponding check result, and updating the initial abstract syntax tree based on the check result to obtain the target abstract syntax tree. Specifically, the target SQL query may be parsed based on lexical analysis to identify keywords of the target SQL query, including but not limited to SELECT, FROM, WHERE, LIMIT, GROUP BY, HAVING, ORDER BY, etc.; identify identifiers of the target SQL query, including but not limited to table names and column names; identify operators of the target SQL query, including but not limited to +, -, *, / , =, <, >, etc.; and identify constants of the target SQL query, including but not limited to strings and numbers. An abstract syntax tree can fully express the logical structure of an SQL query and is easy for program analysis and processing. Therefore, an initial abstract syntax tree can be constructed based on the lexical units obtained from lexical analysis and the target grammar rules corresponding to the target SQL query. In one specific embodiment, starting from the beginning of the target SQL query statement, when the SELECT keyword is encountered, a SELECT node is created as the root node or one of the main branch nodes of the tree. Then, child nodes are gradually added based on the subsequent content. For example, when a column name list is encountered after SELECT, a child node is created for the SELECT node to represent the column name list, and each column name is added as a child of the child node. When the FROM keyword is encountered, a FROM node is created, and the table name is added as a child node of the FROM node. If there are multiple tables, such as those connected using JOIN, further processing of the connection relationship is required. If the query contains a JOIN operation, the JOIN type, such as INNER JOIN or LEFT JOIN, can be identified, and a JOIN node is created. The two tables to be connected are then made child nodes of the JOIN node, and a child node is created under the JOIN node to represent the join condition. After obtaining the initial abstract syntax tree corresponding to the target SQL query, it is necessary to check the initial abstract syntax tree, traverse the initial abstract syntax tree, and first check whether all table names and column names in the initial abstract syntax tree exist in the database.If a non-existent identifier is found, it is marked as a semantic error. After that, the data types of the operator and operand are checked for compatibility. For example, strings and numbers cannot be directly added. If incompatibility is found, it is marked as a semantic error. Finally, the initial abstract syntax tree is updated based on the check results to obtain the target abstract syntax tree corresponding to the target SQL query. The target abstract syntax tree includes not only basic regular statements such as SELECT and WHERE, but also detailed descriptions of each subquery, connection conditions, sorting rules, etc. During the parsing process of the target SQL query, complex structures such as subqueries, joins, and aggregation operations in the SQL query are also decomposed to facilitate subsequent feature extraction and cost estimation.

[0054] It's important to note that the database optimizer generates multiple candidate execution plans for the target SQL query. An execution plan defines how to convert a logical query, such as SELECT * FROM orders JOIN customers , into specific physical operations, such as a full table scan, index scan, or hash join. The core function of an execution plan is to determine the optimal physical operation path, not to control the execution order of SQL statements. The above-mentioned determination of the target feature vector of each candidate execution plan based on the target abstract syntax tree may include: first, determining the operator corresponding to the candidate execution plan based on the target abstract syntax tree, and determining the target type, target object, and target complexity of the operator, so as to determine the target operator feature of the candidate execution plan based on the target type, the target object, and the target complexity; then, obtaining the metadata and statistical information of the target database based on the preset database interface, and determining the target data feature of the candidate execution plan based on the target abstract syntax tree, the metadata, and the statistical information; finally, determining the target nesting level, target number of subqueries, target subquery type, and target join complexity of the candidate execution plan based on the target abstract syntax tree, and determining the target structural feature of the candidate execution plan based on the target nesting level, the target number of subqueries, the target subquery type, and the target join complexity. Specifically, this embodiment can extract key information of the query from the target abstract syntax tree and convert this information into a feature vector input to the deep learning model. The target operator feature can reflect the usage of different operators in the SQL query, and operators include but are not limited to selection, projection, connection, etc. In a specific embodiment, the target operator characteristics of the candidate execution plan can be determined based on the target type of the operator, the target object, such as table, column, and the target complexity of the operator, such as the join type, i.e., nested loop or hash join. The target data characteristics involve the metadata and statistical information of the target database, that is, the characteristics of the data acted on by the query, such as the size of the table, the selectivity of the column, the existence of indexes, the distribution of data, etc. In a specific embodiment, the target data characteristics of the candidate execution plan can be determined based on the metadata, statistical information and target abstract syntax tree of the target database. The target structural characteristics describe the structural complexity of the query. In a specific embodiment, the target structural characteristics of the candidate execution plan can be determined based on the target nesting level, the target number of subqueries, the target subquery type, and the complexity of the join conditions in the candidate execution plan.

[0055] Step S12: A target cost evaluation model is obtained by training a preset deep learning model based on historical SQL queries, and the target cost evaluation model is used to determine a plan evaluation result corresponding to each candidate execution plan based on the target feature vector.

[0056] It should be noted that the basis of large model training is high-quality training data. The data preparation stage mainly includes steps such as data collection, cleaning, feature engineering, and data set division. Before the above-mentioned training of the preset deep learning model based on historical SQL queries to obtain the target cost evaluation model, it can also include: first, obtaining the initial historical SQL query from the historical query log of the target database, and determining the historical query plan and historical execution cost corresponding to the initial historical SQL query; then, performing data cleaning on the initial historical SQL query based on the preset data cleaning method to obtain the cleaned historical SQL query, and determining the target historical query plan and target historical execution cost corresponding to the cleaned historical SQL query based on the historical query plan and the historical execution cost; then, determining the feature vector of the target historical query plan based on the preset feature engineering method and the cleaned historical SQL query, and constructing the target data set based on the cleaned historical SQL query, the target historical query plan, the feature vector of the target historical query plan, and the target historical execution cost; finally, dividing the target data set to obtain the corresponding training set, validation set, and test set, so as to train the preset deep learning model based on the training set, the validation set, and the test set to obtain the target cost evaluation model. Specifically, a large number of executed SQL queries can be collected from the target database's historical query logs. These include the initial historical SQL queries, along with the corresponding historical query plans and historical execution costs. Specifically, the historical SQL query text and detailed execution plan descriptions, such as the operators used, table sizes, and join types, are collected, along with actual execution costs, such as query execution time, CPU usage, and memory usage. Because historical query data may contain noise, missing values, or outliers, these data can affect model training. Therefore, the initial historical SQL queries can be cleansed to produce cleaned historical SQL queries. This data cleaning process involves removing invalid or incomplete query records, addressing outliers such as those with extreme execution times, and filling or deleting missing data. After data cleaning, feature engineering is performed on the cleaned historical SQL queries to convert various SQL query information in the target historical query plans into feature vectors that can be processed by the model. This feature engineering process includes normalizing the data scale, encoding categorical variables, such as operator types and join methods, and generating high-order features, such as interaction features between different operator combinations. The target dataset can then be constructed based on the cleaned historical SQL queries, the target historical query plan, the target historical query plan's feature vectors, and the target historical execution cost. Finally, before model training, the target dataset needs to be divided into a training set, a validation set, and a test set. The training set is used for learning model parameters, the validation set is used for parameter tuning and model selection, and the test set is used to evaluate the model's generalization capabilities.Typically, the training set accounts for 70% to 80%, and the validation set and test set each account for 10% to 15%.

[0057] In this embodiment, the choice of large model depends on the complexity of the SQL query features and the prediction accuracy requirements of the query execution cost. Currently, the model suitable for processing sequence data and high-dimensional features in the field of deep learning is Transformer. Transformer is a model based on the self-attention mechanism that can process long sequence data in parallel computing. Transformer can handle the complex dependency structure of SQL queries very well, especially for large queries or complex nested queries. Transformer has significant advantages in modeling global dependencies. At the same time, the architecture of the model can be adjusted according to the dimension of the feature input, the amount of training data, and the complexity of the prediction target, such as the number of layers, the number of neurons in each layer, etc. Training the preset deep learning model based on the training set, the validation set, and the test set to obtain the target cost evaluation model may include: first, inputting the feature vector of the target historical query plan in the training set into the preset deep learning model to obtain a corresponding output result, and determining a target loss value between the output result and the target historical execution cost corresponding to the target historical query plan based on a preset mean squared error loss function; then, updating the parameters of the preset deep learning model based on a preset gradient descent algorithm and the target loss value to obtain a corresponding cost evaluation model to be optimized; and optimizing the hyperparameters of the cost evaluation model to be optimized based on a preset hyperparameter tuning method, the training set, and the validation set; finally, performing a model evaluation on the optimized cost evaluation model to be optimized based on the test set to obtain a corresponding model evaluation result, and determining the target cost evaluation model based on the model evaluation result and the cost evaluation model to be optimized. Specifically, to optimize model performance, it is necessary to select an appropriate loss function. For the SQL query cost prediction task, MSE (Mean Squared Error) is selected as the loss function. MSE can directly measure the difference between the model prediction value and the actual execution cost. Deep learning models are trained using gradient descent optimization algorithms, such as SGD (Stochastic Gradient Descent) or Adam (Adaptive Moment Estimation). These optimization algorithms effectively update model parameters, gradually reducing the loss function and improving the model's predictive performance. Model training performance is significantly influenced by hyperparameters, such as the learning rate, batch size, and the number of model layers. Therefore, during model training, hyperparameters can be tuned using methods such as cross-validation and grid search. Hyperparameter tuning ensures an optimal balance between model performance on the training and validation sets, avoiding overfitting or underfitting.

[0058] It should be noted that this embodiment uses a target cost evaluation model to determine the plan evaluation results for each candidate execution plan based on the target feature vector. Specifically, the target cost evaluation model is used to predict the execution costs of multiple candidate execution plans and assign a score to each candidate execution plan. This process requires inputting the feature vectors of the candidate execution plans into the target cost evaluation model to obtain the predicted cost for each candidate execution plan.

[0059] Step S13: determine a target execution plan from the candidate execution plans based on the plan evaluation result and a preset optimization plan selection mechanism, and use the target database to execute the target SQL query based on the target execution plan, so as to optimize the query process of the target database for the target SQL query based on the target execution plan.

[0060] In this embodiment, executing the target SQL query using the target database based on the target execution plan may include: first, sending the target execution plan to the database execution engine corresponding to the target database based on a preset database interface, and executing the target SQL query using the database execution engine based on the target execution plan to obtain a corresponding execution result; then, caching the execution result corresponding to the target SQL query in a preset query result cache pool, so that the target database can directly determine the execution result corresponding to the target SQL query based on the preset query result cache pool; and finally, during the execution of the target SQL query, monitoring the execution status of the target execution plan in real time and generating corresponding feedback information based on the execution status, so as to update the target cost evaluation model in real time based on a preset online learning algorithm and the feedback information. Specifically, the database interface serves as a bridge for interaction between the external cost optimizer and the DBMS (Database Management System). The database interface is primarily responsible for obtaining database metadata and statistical information and delivering the optimized execution plan to the database execution engine. The database interface needs to periodically or in real time obtain database metadata, including table structure, column information, index status, etc., as well as database statistical information, such as column selectivity, data distribution, and index usage. The database interface transmits the target execution plan corresponding to the target SQL query to the database execution engine. The database execution engine then executes the target SQL query based on the target execution plan, obtains the corresponding execution results, monitors the execution status of the plan, and generates corresponding feedback information during the execution process, such as actual execution time and resource consumption. This feedback information is then returned to further optimize and adjust the target cost evaluation model based on the online learning algorithm and feedback information. Furthermore, the execution results corresponding to the target SQL query can be cached in a preset query result cache pool, allowing subsequent execution results corresponding to the same target SQL query to be directly determined from the preset query result cache pool.

[0061] As can be seen from the above, in this embodiment, the target SQL query is first parsed to obtain the target abstract syntax tree corresponding to the target SQL query, and the candidate execution plan corresponding to the target SQL query is determined, so as to determine the target feature vector of each candidate execution plan based on the target abstract syntax tree; then, a preset deep learning model is trained based on historical SQL queries to obtain a target cost evaluation model, and the target cost evaluation model is used to determine the plan evaluation results corresponding to each candidate execution plan based on the target feature vector; finally, a target execution plan is determined from the candidate execution plans based on the plan evaluation results and a preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan using the target database, so as to optimize the query process of the target database for the target SQL query based on the target execution plan. As can be seen from the above, in this embodiment, the target SQL query is first parsed to obtain the target abstract syntax tree, and the target feature vector of each candidate execution plan of the target SQL query is determined based on the target abstract syntax tree; then, the target cost evaluation model and the target feature vector are used to determine the plan evaluation results of each candidate execution plan; finally, the target execution plan is determined based on the plan evaluation results and the preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan. In this way, this embodiment uses historical SQL queries to train a preset deep learning model to obtain a target cost evaluation model. Based on the target cost evaluation model, the plan evaluation results of each candidate execution plan are determined, thereby obtaining a target execution plan for the target SQL query. This significantly improves the prediction accuracy of the SQL query execution cost, thereby optimizing the SQL query execution plan. In addition, this embodiment reduces manual intervention and can continuously adjust and optimize the execution plan based on actual execution conditions. This application can adapt to different database environments and meet the database system's needs for efficient query optimization.

[0062] See also Figure 2 As shown, the embodiment of the present invention further discloses a database optimization method based on a large model, which may include:

[0063] Step S21 : Parse the target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query, and determine candidate execution plans corresponding to the target SQL query, so as to determine a target feature vector for each candidate execution plan based on the target abstract syntax tree.

[0064] Step S22: A target cost evaluation model is obtained by training a preset deep learning model based on historical SQL queries, and the target cost evaluation model is used to determine a plan evaluation result corresponding to each candidate execution plan based on the target feature vector.

[0065] Step S23: determine a target execution plan from the candidate execution plans based on the plan evaluation result and a preset optimization plan selection mechanism, and use the target database to execute the target SQL query based on the target execution plan, so as to optimize the query process of the target database for the target SQL query based on the target execution plan.

[0066] It should be noted that the plan evaluation result generated by the target cost evaluation model is the execution cost evaluation value of the candidate execution plan. In this embodiment, the target execution plan can be determined from the candidate execution plans based on the plan evaluation result and a preset optimization plan selection mechanism.

[0067] In a first specific implementation, the candidate execution plan corresponding to the minimum plan evaluation result is determined as the target execution plan. Specifically, the plan evaluation result generated by the target cost evaluation model is the cost score of the candidate execution plan, so the execution plan with the lowest cost can be directly selected as the target execution plan.

[0068] Due to the prediction error of the model and the complexity of the database system, sometimes the lowest cost predicted by the target cost evaluation model may not be the optimal plan during actual execution. Therefore, a certain fault tolerance mechanism can be introduced. In the second specific implementation, the confidence interval of the plan evaluation result corresponding to the candidate execution plan is determined, and the target width and target weight corresponding to the confidence interval are determined to determine the target score corresponding to the candidate execution plan based on the plan evaluation result, the target width and the target weight, and the candidate execution plan corresponding to the minimum target score is determined as the target execution plan. Specifically, the target cost evaluation model outputs the plan evaluation result and the confidence interval corresponding to the plan evaluation result. If the lower limit of the predicted cost of a plan is still higher than the upper limit of other plans, it is necessary to make a comprehensive judgment based on the overlap of the confidence intervals and the confidence width, that is, give priority to plans with low predicted costs and narrow confidence intervals, for example: Plan A has a predicted cost of 120ms and a confidence interval of [100ms, 140ms]; Plan B has a predicted cost of 130ms and a confidence interval of [125ms, 135ms]. If PlanA's lower limit of 100ms is greater than PlanB's upper limit of 135ms, PlanB is selected as the target execution plan.

[0069] In a third specific implementation, a target execution cost threshold is set for each candidate execution plan, and the candidate execution plans whose plan evaluation results exceed the target execution cost threshold are filtered to obtain each execution plan to be selected, and the execution plan to be selected corresponding to the smallest plan evaluation result is determined as the target execution plan. Specifically, an acceptable maximum execution cost, i.e., a target execution cost threshold, is defined for each candidate execution plan, and then the execution plan to be selected whose predicted cost is lower than the target execution cost threshold and has the smallest predicted cost is determined as the target execution plan.

[0070] In one embodiment, see Figure 3 As shown, the process of the large-model-based database optimization method can be specifically as follows: parsing the input SQL query and extracting its features; training a large-scale cost model based on historical query data; using the trained large-model to predict the execution cost of each SQL query plan based on different query plans; selecting the execution plan with the lowest cost, i.e., the optimized query plan, based on the cost evaluation results, and returning it to the database execution engine; interacting with the database system to obtain metadata and statistical information from the database and executing the optimized query plan. In this way, this embodiment achieves intelligent query optimization through precise cost evaluation, significantly improving the overall performance of the database system.

[0071] For more specific processing procedures of the above steps S21 and S22, reference may be made to the corresponding contents disclosed in the aforementioned embodiments, which will not be elaborated here.

[0072] As can be seen from the above, in this embodiment, the candidate execution plan corresponding to the minimum plan evaluation result can be determined as the target execution plan; alternatively, the confidence interval of the plan evaluation result corresponding to the candidate execution plan can be determined, and the target execution plan can be determined based on the confidence interval and the plan evaluation result; alternatively, a target execution cost threshold can be set for each candidate execution plan, and the target execution plan can be determined based on the target execution cost threshold and the plan evaluation result. In this way, in addition to considering the plan evaluation results generated by the target cost evaluation model, this embodiment also introduces certain fault-tolerance mechanisms, such as considering the confidence interval of the plan evaluation result and setting a target execution cost threshold, to prevent performance degradation caused by model prediction errors.

[0073] Accordingly, see Figure 4 As shown, the embodiment of the present application also provides a database optimization device based on a large model, which may include:

[0074] a target feature vector determination module 11, configured to parse a target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query, and determine candidate execution plans corresponding to the target SQL query, so as to determine a target feature vector for each candidate execution plan based on the target abstract syntax tree;

[0075] A plan evaluation result determination module 12 is configured to obtain a target cost evaluation model by training a preset deep learning model based on historical SQL queries, and to determine a plan evaluation result corresponding to each candidate execution plan based on the target feature vector using the target cost evaluation model;

[0076] The target execution plan determination module 13 is used to determine a target execution plan from the candidate execution plans based on the plan evaluation results and a preset optimization plan selection mechanism, and use the target database to execute the target SQL query based on the target execution plan, so as to optimize the query process of the target database for the target SQL query based on the target execution plan.

[0077] As can be seen from the above, in this application, the target SQL query is first parsed to obtain the target abstract syntax tree corresponding to the target SQL query, and the candidate execution plan corresponding to the target SQL query is determined, so as to determine the target feature vector of each candidate execution plan based on the target abstract syntax tree; then, a preset deep learning model is trained based on historical SQL queries to obtain a target cost evaluation model, and the target cost evaluation model is used to determine the plan evaluation result corresponding to each candidate execution plan based on the target feature vector; finally, a target execution plan is determined from the candidate execution plans based on the plan evaluation result and a preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan using the target database, so as to optimize the query process of the target database for the target SQL query based on the target execution plan. As can be seen from the above, in this application, the target SQL query is first parsed to obtain the target abstract syntax tree, and the target feature vector of each candidate execution plan of the target SQL query is determined based on the target abstract syntax tree, and then the target cost evaluation model and the target feature vector are used to determine the plan evaluation result of each candidate execution plan, and finally, the target execution plan is determined based on the plan evaluation result and the preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan. In this way, this application uses historical SQL queries to train a preset deep learning model to obtain a target cost evaluation model. Based on the target cost evaluation model, the plan evaluation results of each candidate execution plan are determined, thereby obtaining the target execution plan for the target SQL query. This significantly improves the prediction accuracy of the SQL query execution cost, thereby optimizing the SQL query execution plan. In addition, this application reduces manual intervention and can continuously adjust and optimize the execution plan based on actual execution conditions. This application can adapt to different database environments and meet the database system's needs for efficient query optimization.

[0078] In some specific implementations, the target feature vector determination module 11 may include:

[0079] a target grammar rule determination unit, configured to parse the target SQL query to determine a target keyword, a target identifier, a target operator, and a target constant corresponding to the target SQL query, and to determine a target grammar rule corresponding to the target SQL query;

[0080] an initial abstract syntax tree determining unit, configured to construct an initial abstract syntax tree corresponding to the target SQL query based on a preset data structure, the target syntax rule, the target keyword, the target identifier, the target operator, and the target constant;

[0081] A target abstract syntax tree determining unit is configured to check the initial abstract syntax tree according to the target SQL query to obtain a corresponding check result, and update the initial abstract syntax tree based on the check result to obtain the target abstract syntax tree.

[0082] In some specific implementations, the target feature vector determination module 11 may include:

[0083] a target operator feature determination unit, configured to determine an operator corresponding to the candidate execution plan based on the target abstract syntax tree, and determine a target type, a target action object, and a target complexity of the operator, so as to determine a target operator feature of the candidate execution plan based on the target type, the target action object, and the target complexity;

[0084] a target data feature determination unit, configured to obtain metadata and statistical information of the target database based on a preset database interface, and determine target data features of the candidate execution plan based on the target abstract syntax tree, the metadata, and the statistical information;

[0085] a target structural feature determination unit, configured to determine a target nesting level, a target number of subqueries, a target subquery type, and a target join complexity of the candidate execution plan based on the target abstract syntax tree, and to determine a target structural feature of the candidate execution plan based on the target nesting level, the target number of subqueries, the target subquery type, and the target join complexity.

[0086] In some specific implementations, the large model-based database optimization device may further include:

[0087] An initial historical SQL query acquisition module is used to acquire an initial historical SQL query from the historical query log of the target database and determine a historical query plan and a historical execution cost corresponding to the initial historical SQL query;

[0088] an initial historical SQL query cleaning module, configured to clean the initial historical SQL query based on a preset data cleaning method to obtain a cleaned historical SQL query, and determine a target historical query plan and a target historical execution cost corresponding to the cleaned historical SQL query based on the historical query plan and the historical execution cost;

[0089] a target data set construction module, configured to determine a feature vector of the target historical query plan based on a preset feature engineering method and the cleaned historical SQL query, and to construct a target data set based on the cleaned historical SQL query, the target historical query plan, the feature vector of the target historical query plan, and the target historical execution cost;

[0090] The target cost evaluation model module is used to divide the target data set into corresponding training sets, validation sets and test sets, so as to train the preset deep learning model based on the training set, the validation set and the test set to obtain the target cost evaluation model.

[0091] In some specific implementations, the plan evaluation result determination module 12 may include:

[0092] a target loss value determining unit, configured to input the feature vector of the target historical query plan in the training set into the preset deep learning model, obtain a corresponding output result, and determine a target loss value between the output result and the target historical execution cost corresponding to the target historical query plan based on a preset mean square error loss function;

[0093] A hyperparameter optimization unit, configured to update the parameters of the preset deep learning model based on a preset gradient descent algorithm and the target loss value to obtain a corresponding cost evaluation model to be optimized, and optimize the hyperparameters of the cost evaluation model to be optimized based on a preset hyperparameter tuning method, the training set, and the validation set;

[0094] The target cost evaluation model determination unit is used to perform model evaluation on the optimized cost evaluation model to be optimized based on the test set to obtain a corresponding model evaluation result, and determine the target cost evaluation model based on the model evaluation result and the cost evaluation model to be optimized.

[0095] In some specific implementations, the plan evaluation result is an evaluation value of the execution cost of the candidate execution plan;

[0096] Accordingly, the target execution plan determination module 13 may include:

[0097] a first target execution plan determining unit, configured to determine the candidate execution plan corresponding to the minimum plan evaluation result as the target execution plan;

[0098] a second target execution plan determination unit, configured to determine a confidence interval of a plan evaluation result corresponding to the candidate execution plan, and determine a target width and a target weight corresponding to the confidence interval, so as to determine a target score corresponding to the candidate execution plan based on the plan evaluation result, the target width, and the target weight, and determine the candidate execution plan corresponding to the minimum target score as the target execution plan;

[0099] The third target execution plan determination unit is used to set a target execution cost threshold corresponding to each candidate execution plan, and filter the candidate execution plans whose plan evaluation results exceed the target execution cost threshold to obtain each execution plan to be selected, and determine the execution plan to be selected corresponding to the smallest plan evaluation result as the target execution plan.

[0100] In some specific implementations, the target execution plan determination module 13 may include:

[0101] An execution result determining unit, configured to send the target execution plan to a database execution engine corresponding to the target database based on a preset database interface, and utilize the database execution engine to execute the target SQL query based on the target execution plan to obtain a corresponding execution result;

[0102] an execution result caching unit, configured to cache the execution result corresponding to the target SQL query in a preset query result cache pool, so that the target database directly determines the execution result corresponding to the target SQL query based on the preset query result cache pool;

[0103] The target cost evaluation model updating unit is used to monitor the execution status of the target execution plan in real time during the execution of the target SQL query, and generate corresponding feedback information based on the execution status, so as to update the target cost evaluation model in real time based on the preset online learning algorithm and the feedback information.

[0104] Furthermore, the embodiment of the present application also discloses an electronic device, Figure 5 This is a structural diagram of an electronic device 20 according to an exemplary embodiment. The content in the diagram should not be considered as any limitation on the scope of use of this application. The electronic device 20 may specifically include: at least one processor 21, at least one memory 22, a power supply 23, a communication interface 24, an input / output interface 25, and a communication bus 26. The memory 22 is used to store a computer program, which is loaded and executed by the processor 21 to implement the relevant steps in the large model-based database optimization method disclosed in any of the aforementioned embodiments. In addition, the electronic device 20 in this embodiment may specifically be an electronic computer.

[0105] In this embodiment, the power supply 23 is used to provide operating voltage for each hardware device on the electronic device 20; the communication interface 24 can create a data transmission channel between the electronic device 20 and the external device. The communication protocol it follows is any communication protocol that can be applied to the technical solution of this application and is not specifically limited here; the input and output interface 25 is used to obtain external input data or output data to the outside world. Its specific interface type can be selected according to specific application needs and is not specifically limited here.

[0106] In addition, the memory 22, as a carrier for resource storage, can be a read-only memory, random access memory, disk or CD, etc. The resources stored thereon can include an operating system 221, a computer program 222, etc., and the storage method can be temporary storage or permanent storage.

[0107] The operating system 221 is used to manage and control the hardware devices on the electronic device 20 and the computer program 222, and can be Windows Server, NetWare, Unix, Linux, etc. In addition to including computer programs capable of implementing the large model-based database optimization method disclosed in any of the aforementioned embodiments and executed by the electronic device 20, the computer program 222 can further include computer programs capable of implementing other specific tasks.

[0108] Furthermore, this application discloses a computer-readable storage medium for storing a computer program; wherein, when executed by a processor, the computer program implements the aforementioned large model-based database optimization method. The specific steps of this method can be found in the corresponding content disclosed in the aforementioned embodiments and will not be repeated here.

[0109] The various embodiments in this specification are described in a progressive manner, with each embodiment focusing on its differences from the other embodiments. Reference can be made to the same or similar parts between the various embodiments. For the devices disclosed in the embodiments, since they correspond to the methods disclosed in the embodiments, the description is relatively simple, and the relevant parts can be referred to the method description.

[0110] Professionals may further appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the above description has generally described the components and steps of each example according to their functions. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians may use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.

[0111] The steps of the methods or algorithms described in conjunction with the embodiments disclosed herein may be implemented directly using hardware, a software module executed by a processor, or a combination of the two. The software module may be placed in random access memory (RAM), internal memory, read-only memory (ROM), electrically programmable ROM, electrically erasable programmable ROM, registers, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0112] Finally, it should be noted that, in this document, relational terms such as first and second, etc., are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any actual relationship or order between these entities or operations. Moreover, the terms "comprises," "comprising," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or device comprising a series of elements includes not only those elements, but also other elements not explicitly listed, or elements inherent to such process, method, article, or device. In the absence of further limitations, an element defined by the phrase "comprising a ..." does not exclude the presence of additional identical elements in the process, method, article, or device comprising the element.

[0113] The above is a detailed introduction to the technical solution provided by the present application. Specific examples are used herein to illustrate the principles and implementation methods of the present application. The description of the above embodiments is only used to help understand the method of the present application and its core idea. At the same time, for those skilled in the art, according to the ideas of the present application, there may be changes in the specific implementation methods and application scope. In summary, the content of this specification should not be understood as a limitation on the present application.

Claims

1. A database optimization method based on a large model, characterized in that: include: Parsing a target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query, and determining candidate execution plans corresponding to the target SQL query, so as to determine a target feature vector for each candidate execution plan based on the target abstract syntax tree; A target cost evaluation model is obtained by training a preset deep learning model based on historical SQL queries, and a plan evaluation result corresponding to each candidate execution plan is determined based on the target feature vector using the target cost evaluation model; A target execution plan is determined from the candidate execution plans according to the plan evaluation result and a preset optimization plan selection mechanism, and the target SQL query is executed based on the target execution plan using the target database, so as to optimize the query process of the target database for the target SQL query based on the target execution plan.

2. The database optimization method based on a large model according to claim 1, characterized in that: The step of parsing the target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query includes: Parsing the target SQL query to determine a target keyword, a target identifier, a target operator, and a target constant corresponding to the target SQL query, and determining a target grammar rule corresponding to the target SQL query; Constructing an initial abstract syntax tree corresponding to the target SQL query based on a preset data structure, the target grammar rule, the target keyword, the target identifier, the target operator, and the target constant; The initial abstract syntax tree is checked according to the target SQL query to obtain a corresponding check result, and the initial abstract syntax tree is updated based on the check result to obtain the target abstract syntax tree.

3. The database optimization method based on a large model according to claim 1, characterized in that: The determining of the target feature vector of each candidate execution plan based on the target abstract syntax tree includes: Determining an operator corresponding to the candidate execution plan based on the target abstract syntax tree, and determining a target type, a target action object, and a target complexity of the operator, so as to determine a target operator feature of the candidate execution plan based on the target type, the target action object, and the target complexity; Obtaining metadata and statistical information of the target database based on a preset database interface, and determining target data features of the candidate execution plan based on the target abstract syntax tree, the metadata, and the statistical information; The target nesting level, target number of subqueries, target subquery type, and target join complexity of the candidate execution plan are determined based on the target abstract syntax tree, and the target structural features of the candidate execution plan are determined based on the target nesting level, target number of subqueries, target subquery type, and target join complexity.

4. The database optimization method based on a large model according to claim 1, characterized in that: Before the target cost evaluation model is obtained by training the preset deep learning model based on historical SQL queries, the method further includes: Obtaining an initial historical SQL query from the historical query log of the target database, and determining a historical query plan and a historical execution cost corresponding to the initial historical SQL query; Performing data cleaning on the initial historical SQL query based on a preset data cleaning method to obtain a cleaned historical SQL query, and determining a target historical query plan and a target historical execution cost corresponding to the cleaned historical SQL query based on the historical query plan and the historical execution cost; Determining a feature vector of the target historical query plan based on a preset feature engineering method and the cleaned historical SQL query, and constructing a target dataset based on the cleaned historical SQL query, the target historical query plan, the feature vector of the target historical query plan, and the target historical execution cost; The target data set is divided into corresponding training sets, validation sets and test sets, so as to train the preset deep learning model based on the training set, the validation set and the test set to obtain the target cost evaluation model.

5. The database optimization method based on a large model according to claim 4, characterized in that: The step of training the preset deep learning model based on the training set, the validation set, and the test set to obtain the target cost evaluation model includes: Inputting the feature vector of the target historical query plan in the training set into the preset deep learning model to obtain a corresponding output result, and determining a target loss value between the output result and the target historical execution cost corresponding to the target historical query plan based on a preset mean square error loss function; Based on a preset gradient descent algorithm and the target loss value, the parameters of the preset deep learning model are updated to obtain a corresponding cost evaluation model to be optimized, and the hyperparameters of the cost evaluation model to be optimized are optimized based on a preset hyperparameter tuning method, the training set, and the validation set; Based on the test set, a model evaluation is performed on the optimized cost evaluation model to be optimized to obtain a corresponding model evaluation result, and the target cost evaluation model is determined based on the model evaluation result and the cost evaluation model to be optimized.

6. The database optimization method based on a large model according to claim 1, characterized in that: The plan evaluation result is the execution cost evaluation value of the candidate execution plan; Accordingly, determining a target execution plan from the candidate execution plans according to the plan evaluation result and a preset optimization plan selection mechanism includes: Determine the candidate execution plan corresponding to the minimum plan evaluation result as the target execution plan; Alternatively, a confidence interval of a plan evaluation result corresponding to the candidate execution plan is determined, and a target width and a target weight corresponding to the confidence interval are determined, so as to determine a target score corresponding to the candidate execution plan based on the plan evaluation result, the target width, and the target weight, and determine the candidate execution plan corresponding to the smallest target score as the target execution plan; Alternatively, a target execution cost threshold is set for each candidate execution plan, and the candidate execution plans whose plan evaluation results exceed the target execution cost threshold are filtered to obtain the execution plans to be selected, and the execution plan to be selected corresponding to the smallest plan evaluation result is determined as the target execution plan.

7. The database optimization method based on a large model according to any one of claims 1 to 6, characterized in that: The executing the target SQL query based on the target execution plan using the target database includes: Sending the target execution plan to a database execution engine corresponding to the target database based on a preset database interface, and using the database execution engine to execute the target SQL query based on the target execution plan to obtain a corresponding execution result; caching the execution result corresponding to the target SQL query in a preset query result cache pool, so that the target database directly determines the execution result corresponding to the target SQL query based on the preset query result cache pool; During the execution of the target SQL query, the execution status of the target execution plan is monitored in real time, and corresponding feedback information is generated based on the execution status, so as to update the target cost evaluation model in real time based on a preset online learning algorithm and the feedback information.

8. A database optimization device based on a large model, characterized in that: include: a target feature vector determination module, configured to parse a target SQL query to obtain a target abstract syntax tree corresponding to the target SQL query, and determine candidate execution plans corresponding to the target SQL query, so as to determine a target feature vector for each candidate execution plan based on the target abstract syntax tree; A plan evaluation result determination module is used to obtain a target cost evaluation model by training a preset deep learning model based on historical SQL queries, and to determine a plan evaluation result corresponding to each candidate execution plan based on the target feature vector using the target cost evaluation model; A target execution plan determination module is used to determine a target execution plan from the candidate execution plans based on the plan evaluation results and a preset optimization plan selection mechanism, and use the target database to execute the target SQL query based on the target execution plan, so as to optimize the query process of the target database for the target SQL query based on the target execution plan.

9. An electronic device, characterized in that: The electronic device includes a processor and a memory; wherein the memory is used to store a computer program, and the computer program is loaded and executed by the processor to implement the large model-based database optimization method according to any one of claims 1 to 7.

10. A computer-readable storage medium, characterized in that Used to store a computer program, which, when executed by a processor, implements the large model-based database optimization method according to any one of claims 1 to 7.

Images