Database query optimization method, apparatus, device, and medium

Abstract

Embodiments of the present application provide a database query optimization method and device, equipment and medium, the method comprises: obtaining a sample query statement, generating at least two first optimization hint sets for the sample query statement;According to the execution evaluation parameter of each first optimization hint set, at least two first optimization hint sets are constructed sample optimization label and contrast optimization label pair;Obtain the first database auxiliary information of the sample query statement, according to the sample query statement and the first database auxiliary information, first task prompt information is constructed;According to the first task prompt information and the sample optimization label, the network parameter of the pre-training language model is modified, and the first query optimization model is obtained;According to the first task prompt information and the contrast optimization label pair, the parameter optimization of the first query optimization model is carried out, and the second query optimization model is obtained. Through the embodiments of the present application, the accuracy of database query optimization can be improved.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a database query optimization method, apparatus, device, and medium. Background Technology

[0002] Query optimization, a key task in database management systems, involves finding an effective execution strategy for a given query statement to improve its performance in the database.

[0003] Current database management systems employ cost-based optimizers to find the least-cost execution strategy for queries. For example, cardinality estimation, cost models, strategy enumeration, and selection can be used to choose the least-cost execution strategy while maintaining semantic equivalence. However, as data volume increases and query complexity rises, cost-based optimizers face challenges such as inaccurate cardinality estimation and difficulty in accurately modeling cost, leading to low accuracy in execution strategies. Summary of the Invention

[0004] This application provides a database query optimization method, apparatus, device, and medium that can improve the accuracy of database query optimization.

[0005] This application provides a database query optimization method, including:

[0006] Obtain the sample query statement and generate at least two sets of first optimization hints for the sample query statement;

[0007] Based on the execution evaluation parameters of each first optimization suggestion set, construct sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets;

[0008] Obtain the first database auxiliary information of the sample query statement, and construct the first task prompt information based on the sample query statement and the first database auxiliary information;

[0009] Based on the prompts from the first task and the optimized labels for the samples, the network parameters of the pre-trained language model are corrected to obtain the first query optimization model.

[0010] Based on the first task prompt information and the comparison optimization label pair, the parameters of the first query optimization model are optimized to obtain the second query optimization model. The second query optimization model is used to generate a set of query optimization prompts for structured query statements.

[0011] This application provides a database query optimization method, including:

[0012] Obtain the structured query statement and the second database auxiliary information of the structured query statement; construct the second task prompt information based on the structured query statement and the second database auxiliary information.

[0013] Based on the second task prompt information, the second query optimization model outputs a set of query optimization prompts for structured query statements.

[0014] Based on the query optimization knowledge base and query optimization toolkit, the query optimization suggestion set is updated to obtain at least one candidate suggestion set;

[0015] From at least one set of candidate suggestions, determine a set of recommended suggestions for the structured query.

[0016] One embodiment of this application provides a database query optimization device, including:

[0017] The suggestion generation module is used to obtain the sample query statement and generate at least two first optimization suggestion sets for the sample query statement;

[0018] A label building module is used to construct sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets based on the execution evaluation parameters of each first optimization suggestion set;

[0019] The first input construction module is used to obtain the first database auxiliary information of the sample query statement, and construct the first task prompt information based on the sample query statement and the first database auxiliary information;

[0020] The first training module is used to correct the network parameters of the pre-trained language model based on the first task prompt information and sample optimization labels to obtain the first query optimization model;

[0021] The second training module is used to optimize the parameters of the first query optimization model based on the first task prompt information and the comparison optimization label pair to obtain the second query optimization model. The second query optimization model is used to generate a set of query optimization prompts for structured query statements.

[0022] When the suggestion generation module obtains the sample query statement and generates at least two first optimized suggestion sets for the sample query statement, it performs the following steps:

[0023] Get the sample query statement and get M initial optimization hints for the sample query statement, where M is an integer greater than 1;

[0024] Based on the suggestion types of the M initial optimization suggestions, the M initial optimization suggestions are combined to obtain a set of N second optimization suggestions, where N is an integer greater than 1;

[0025] Based on the execution evaluation parameters of each second optimization suggestion set, at least two first optimization suggestion sets are determined from the N second optimization suggestion sets.

[0026] When the suggestion generation module determines at least two first optimization suggestion sets from N second optimization suggestion sets based on the execution evaluation parameters of each second optimization suggestion set, it performs the following steps:

[0027] Based on each second optimization suggestion set, the database optimization component is invoked to generate sample query statements and sample execution strategies for each second optimization suggestion set;

[0028] According to the sample execution strategy corresponding to each second optimization suggestion set, execute sample query statements in the database to obtain the execution evaluation parameters for each second optimization suggestion set;

[0029] In the N sets of second optimization suggestions, delete the sets of second optimization suggestions whose execution evaluation parameters are greater than the threshold of the first parameter, so as to obtain at least two sets of first optimization suggestions.

[0030] The label construction module, when constructing sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets based on the execution evaluation parameters of each first optimization suggestion set, performs the following steps:

[0031] Based on the sample execution strategy corresponding to each first optimization suggestion set, at least two first optimization suggestion sets are clustered to obtain at least one cluster. Based on at least one cluster, the sample optimization label of the sample query statement is determined.

[0032] Among at least two first optimization suggestion sets, first optimization suggestion set a and first optimization suggestion set b are determined, wherein the execution evaluation parameter of first optimization suggestion set a is greater than the execution evaluation parameter of first optimization suggestion set b;

[0033] If the difference between the execution evaluation parameters of the first optimization suggestion set a and the execution evaluation parameters of the first optimization suggestion set b is greater than the second parameter threshold, then the first optimization suggestion set a and its inference description are determined as positive optimization labels.

[0034] The first optimization hint set b and its inference description are identified as negative optimization labels, and the positive and negative optimization labels are identified as the comparison optimization label pair of the sample query statement.

[0035] When the label building module determines the sample optimization label for a sample query statement based on at least one cluster, it performs the following steps:

[0036] Using a pre-trained language model, semantic analysis is performed on the sample query statements and the first set of optimization hints within each cluster to generate a third set of optimization hints for each cluster, as well as an inference description of the third set of optimization hints.

[0037] Obtain the execution evaluation parameters of the third optimization suggestion set. In the third optimization suggestion set corresponding to at least one cluster, determine the third optimization suggestion set corresponding to the smallest execution evaluation parameter as the label suggestion set.

[0038] The set of tag hints and the inference description of the set of tag hints are used to determine the sample optimized tags for the sample query statement.

[0039] The first input construction module obtains the first database auxiliary information of the sample query statement. When constructing the first task prompt information based on the sample query statement and the first database auxiliary information, it performs the following steps:

[0040] Construct an abstract syntax tree of the sample query statement, obtain the nested representation text of the abstract syntax tree, and determine the sample query statement, the nested representation text, and the default execution strategy of the sample query statement in the database as query semantic information.

[0041] Obtain database statistics and database metadata, obtain database load detection information, and determine the statistics, database metadata, and load detection information as the first database auxiliary information;

[0042] Obtain the task instruction text and task example text, and determine the query semantic information, first database auxiliary information, task instruction text, and task example text as the first task prompt information.

[0043] Specifically, when the first training module corrects the network parameters of the pre-trained language model based on the first task prompt information and sample optimization labels to obtain the first query optimization model, it performs the following steps:

[0044] The pre-trained language model outputs the first predicted conditional probability of the optimized label for the sample based on the prompt information of the first task.

[0045] The negative log-likelihood loss for the first prediction conditional probability is determined. Based on the negative log-likelihood loss, the network parameters of the pre-trained language model are corrected to obtain the first query optimization model.

[0046] Among them, the comparison optimization label pairs include positive optimization labels and negative optimization labels;

[0047] The second training module optimizes the parameters of the first query optimization model based on the prompts from the first task and the comparison and optimization label pairs. When obtaining the second query optimization model, it performs the following steps:

[0048] The first query optimization model is backed up to obtain a reference model, and the network parameters of the reference model are fixed.

[0049] Based on the reference model and the prompt information from the first task, the second predicted conditional probability for the positive optimization label and the third predicted conditional probability for the negative optimization label are output.

[0050] Based on the first task prompt information, the first query optimization model outputs the fourth predicted conditional probability for the positive optimization label and the fifth predicted conditional probability for the negative optimization label.

[0051] The contrast preference difference between the positive and negative optimization labels is determined based on the first logarithmic ratio between the fourth and second prediction condition probabilities, and the second logarithmic ratio between the fifth and third prediction condition probabilities.

[0052] By performing a probabilistic mapping on the contrast preference difference, the preference optimization loss of the first query optimization model for positive and negative optimization labels is obtained;

[0053] Based on the preference optimization loss, the network parameters of the first query optimization model are modified to obtain the second query optimization model.

[0054] One embodiment of this application provides a database query optimization device, including:

[0055] The second input construction module is used to obtain the structured query statement and the second database auxiliary information of the structured query statement, and to construct the second task prompt information based on the structured query statement and the second database auxiliary information.

[0056] The model output module is used to output a set of query optimization suggestions for structured query statements based on the second task prompt information through the second query optimization model.

[0057] The suggestion optimization module is used to update the query optimization suggestion set based on the query optimization knowledge base and the query optimization tool library to obtain at least one candidate suggestion set;

[0058] The suggestion recommendation module is used to determine a set of recommended suggestions for a structured query statement from at least one set of candidate suggestions.

[0059] The suggestion optimization module updates the query optimization suggestion set based on the query optimization knowledge base and query optimization tool library. When at least one candidate suggestion set is obtained, it performs the following steps:

[0060] The root node of the tree structure is constructed based on the query optimization suggestion set. The query optimization suggestion set is analyzed and verified by coordinating the intelligent object to call the query optimization tool library and query optimization knowledge base, and feedback evaluation information of the query optimization suggestion set is obtained.

[0061] Based on the feedback evaluation information, determine the first suggestion to be modified in the query optimization suggestion set, and determine the first proprietary intelligent object that matches the first suggestion to be modified in the proprietary intelligent object set;

[0062] The first proprietary smart object is invoked to modify the query optimization suggestion set in the root node, resulting in the first candidate suggestion set. The first candidate suggestion set is then constructed as the first child node of the root node.

[0063] Based on the root node and the first child node, the tree structure is expanded to obtain at least one candidate hint set, and the at least one candidate hint set includes the first candidate hint set.

[0064] When the suggestion optimization module expands the tree structure based on the root node and the first child node to obtain at least one set of candidate suggestions, it performs the following steps:

[0065] Get the first exploration contribution parameter of the root node, and get the second exploration contribution parameter of the first child node;

[0066] If the second exploration contribution parameter is greater than the first exploration contribution parameter, then the coordination smart object is called to modify the first candidate hint set in the first child node to obtain the second candidate hint set, and the second candidate hint set is constructed as the second child node;

[0067] If the number of nodes in the tree structure meets the exploration stopping condition, then the node expansion of the tree structure is stopped, and at least one candidate hint set is obtained, which includes a second candidate hint set.

[0068] Specifically, when the optimization module obtains the second exploration contribution parameter of the first child node, it performs the following steps:

[0069] Based on the first candidate suggestion set in the first child node, execute the structured query statement to obtain the query execution time of the structured query statement, normalize the query execution time to obtain the performance parameters of the first child node;

[0070] Based on the exploration path of the first child node in the tree structure, the summary information of the first child node is obtained, and semantic analysis is performed on the summary information to obtain the summary confidence of the first child node.

[0071] The second exploration contribution parameter of the first child node is determined based on performance parameters, summary confidence, and the number of visits to the first child node.

[0072] The device also includes:

[0073] The knowledge base construction module is used to acquire database corpus data, perform semantic segmentation on the database corpus data, and obtain a set of corpus data blocks.

[0074] The knowledge base construction module is also used to perform vectorized encoding on each corpus data block in the corpus data block set to obtain the data encoding vector of each corpus data block, and to construct a query optimization knowledge base from the data encoding vector of each corpus data block.

[0075] The tool library construction module is used to build at least one auxiliary tool based on the database query optimization task, obtain the interface description information of each auxiliary tool, and construct the at least one auxiliary tool and the interface description information of each auxiliary tool into a query optimization tool library.

[0076] The device also includes:

[0077] The execution strategy generation module is used to generate a query execution strategy for structured query statements by calling the database optimization component based on the set of recommended suggestions.

[0078] The query result retrieval module is used to execute structured query statements in the database according to the query execution strategy and obtain the data query results of the structured query statements.

[0079] One aspect of this application provides a computer device, including a memory and a processor. The memory is connected to the processor, the memory is used to store computer programs, and the processor is used to call the computer programs so that the computer device executes the method provided in one aspect of this application.

[0080] One aspect of this application provides a computer-readable storage medium storing a computer program adapted to be loaded and executed by a processor, so that a computer device having a processor performs the method provided in one aspect of this application.

[0081] According to one aspect of this application, a computer program product is provided, which may include a computer program stored in a computer-readable storage medium. A processor of a computer device reads the computer program from the computer-readable storage medium, and executes the computer program, causing the computer device to perform the method provided in the above aspect.

[0082] In this embodiment, a sample query statement can be obtained, and at least two first optimization suggestion sets can be generated for the sample query statement. Based on the execution evaluation parameters of each first optimization suggestion set, sample optimization labels and comparison optimization label pairs are constructed in the at least two first optimization suggestion sets. First task suggestion information is constructed based on the sample query statement and first database auxiliary information. This first task suggestion information can be used as input data during model training to guide the model to more accurately understand the sample query statement. Based on the first task suggestion information and sample optimization labels, a pre-trained language model is trained (supervised fine-tuning) to obtain a first query optimization model. Based on the first query optimization model, preference optimization is performed on the first query optimization model according to the first task suggestion information and comparison optimization label pairs to obtain a second query optimization model, which can improve the performance of the second query optimization model. Applying the second query optimization model to a database query optimization task can improve the accuracy of database query optimization. Attached Figure Description

[0083] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0084] Figure 1 This is a schematic diagram of a network architecture provided in an embodiment of this application;

[0085] Figure 2 This is a flowchart illustrating a database query optimization method provided in an embodiment of this application. Figure 1 ;

[0086] Figure 3 This is a schematic diagram illustrating a sample query statement provided in an embodiment of this application;

[0087] Figure 4 This is a flowchart illustrating a database query optimization method provided in an embodiment of this application. Figure 2 ;

[0088] Figure 5 This is a training diagram of a pre-trained language model provided in an embodiment of this application;

[0089] Figure 6 This is a flowchart illustrating a database query optimization method provided in an embodiment of this application. Figure 3 ;

[0090] Figure 7This is a reasoning diagram illustrating a query optimization suggestion set for a structured query statement provided in an embodiment of this application;

[0091] Figure 8 This is a schematic diagram of the structure of a database query optimization device provided in an embodiment of this application. Figure 1 ;

[0092] Figure 9 This is a schematic diagram of the structure of a database query optimization device provided in an embodiment of this application. Figure 2 ;

[0093] Figure 10 This is a schematic diagram of the structure of a computer device provided in an embodiment of this application. Detailed Implementation

[0094] The technical solutions of the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this application, all other embodiments obtained by those of ordinary skill in the art without creative effort are within the scope of protection of this application.

[0095] Please see Figure 1 , Figure 1 This is a schematic diagram of a network architecture provided in an embodiment of this application. The network architecture may include a server 10d and a terminal cluster. The terminal cluster may include one or more terminal devices. No limitations are placed on the number of terminal devices included in the terminal cluster or the number of servers included in the network architecture. Figure 1 As shown, the terminal cluster may specifically include terminal device 10a, terminal device 10b, and terminal device 10c, etc. Each terminal device in the terminal cluster can connect to server 10d via the network so that each terminal device can interact with server 10d through the network connection.

[0096] in, Figure 1 The terminal devices in the terminal cluster shown may include: smartphones, tablets, laptops, PDAs, desktop computers, wearable devices (such as smartwatches, smart bracelets, etc.), smart voice interaction devices, smart home appliances (such as smart TVs, etc.), in-vehicle devices, aircraft, and other electronic devices with database query functions. This application does not limit the type of terminal device.

[0097] Figure 1The server 10d shown can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, content delivery networks (CDN), and big data and artificial intelligence platforms. This application does not limit the type of server.

[0098] In the embodiments of this application, Figure 1 The terminal cluster shown can run a database management system (DBMS) on each terminal device. This DBMS allows for the storage, management, and retrieval of data in a structured manner. Optionally, the DBMS can also run on... Figure 1 The application describes server 10d as shown. This application does not impose any limitations on the operating environment of the database management system.

[0099] A database management system (DBMS) is a software system used to create, manage, and operate databases. It has functions such as data storage and management, data definition, data querying and manipulation, data security and access control, concurrency control, backup, and recovery. DBMSs can include relational database management systems, non-relational database management systems, object database management systems, distributed database management systems, etc., and this application does not limit this. Different DBMSs can be applied in different application scenarios to manage data in different types of databases.

[0100] For ease of understanding, this application describes a relational database management system as an example. A relational database management system can use tables (relational model) to organize data and supports Structured Query Language (SQL) queries; data storage is represented in rows and columns, with explicit relational constraints. Relational database management systems can include, but are not limited to, MySQL (MySQL Structured Query Language), Oracle (an enterprise-level relational database management system), and SQL Server (a structured query language service). Structured Query Language (SQL) is a standard language used for managing and manipulating relational databases.

[0101] Database management systems may include, but are not limited to, components such as database optimizers and query executors. The database optimizer (also known as the database optimization component) optimizes the execution strategy of database queries to improve query performance and efficiency. The goal of the database optimization component is to select an optimal execution strategy that allows the query to complete in the shortest possible time, reducing system resource consumption. The query executor (also known as the query execution component) is responsible for executing the execution strategy generated by the database optimization component, completing the SQL query by performing database operations (such as table scans, joins, sorting, etc.).

[0102] The execution strategy can refer to the detailed operations or steps generated by the database optimization component for a specific structured query statement, such as the table access order, indexes used, join methods, and sorting methods. A structured query statement can refer to instructions written in a structured query language, also known as an SQL statement.

[0103] It is understood that this application proposes a database optimization component based on a large model, where the large model can be referred to as a query optimization model. This query optimization model can understand a given structured query statement and generate a set of effective query optimization suggestions and an inference description of the set of suggestions. The query optimization model can refer to a model obtained by supervised fine-tuning (SFT) and / or direct preference optimization (DPO) of a large language model (LLM). This query optimization model can be applied to database query optimization scenarios, such as intelligent query optimization in cloud databases and query optimization in relational databases, etc., and this application does not limit its application to these applications.

[0104] For example, a query optimization model can refer to a model obtained by supervised fine-tuning a pre-trained language model; or it can refer to a model obtained by direct preference optimization of a pre-trained language model; or it can refer to a model obtained by performing supervised fine-tuning on a pre-trained language model followed by direct preference optimization. In this embodiment, for ease of understanding, when the query optimization model refers to a model obtained by supervised fine-tuning and direct preference optimization of a pre-trained language model, the model after supervised fine-tuning can be called the first query optimization model, and the model after direct preference optimization based on the first query optimization model can be called the second query optimization model.

[0105] In this context, a query optimization hint set can refer to a collection of hints, which can be represented as a hint set. A hint is a commenting syntax in databases that guides and prompts the database to perform corresponding operations; in other words, a hint is a suggestion from a database optimization component, used to guide the component on how to select specific operations or steps when generating execution strategies. The reasoning description of the query optimization hint set can also be called the inference process of that set. This inference process can be understood as the process by which the database optimization component analyzes, rewrites, and optimizes structured query statements based on a series of rules, logic, and experience.

[0106] For example, when the structured query statement is "SELECT * FROM emp", it means querying the table named "emp" in the database. The hint can be used as follows: "SELECT / *+INDEX(emp emp_idx)* / *FROM emp;". Here, / *+INDEX(emp emp_idx)* / is a hint used to instruct the database to force the use of the index named "emp_idx" when querying the "emp" table.

[0107] A large language model is an artificial intelligence model built using deep learning technology. It can contain artificial neural networks with hundreds of millions of parameters and is trained on large-scale unlabeled text data through self-supervised or semi-supervised learning, aiming to generate high-quality natural language output. Supervised fine-tuning refers to further training the large language model based on pre-trained data, using instruction fine-tuning data (which may include prompt words and corresponding answers). In this embodiment, the instruction fine-tuning data may include prompt words and corresponding answers for query optimization tasks. Supervised fine-tuning aims to optimize the performance of the large language model on query optimization tasks, making it more accurate and efficient. Direct preference optimization is an optimization method in reinforcement learning that aims to optimize the model's output by learning directly from object preference data without relying on complex reward functions.

[0108] Please see Figure 2 , Figure 2 This is a flowchart illustrating a database query optimization method provided in an embodiment of this application. Figure 1 It is understandable that this database query optimization method can be executed by a computer device, which can be a terminal device, such as... Figure 1 Any terminal device in the terminal cluster shown; or it can be a server, such as Figure 1The server 10d shown is not limited in this application. Figure 2 As shown, the database query optimization method may include the following steps S101 to S105:

[0109] Step S101: Obtain the sample query statement and generate at least two first optimization hint sets for the sample query statement.

[0110] In this embodiment, the computer device can construct a supervised fine-tuning dataset for database query optimization tasks. This supervised fine-tuning dataset refers to a dataset used to adjust a pre-trained language model, where the pre-trained language model can be a large language model trained using large-scale unlabeled text data. The database query optimization task can refer to the task of improving the execution efficiency of database queries and reducing the response time and resource consumption of query operations through various techniques and strategies. The goal of the database query optimization task is to enable SQL queries to execute in the shortest possible time and minimize the database load, thereby improving the performance and responsiveness of the database management system.

[0111] This supervised fine-tuning dataset can include multiple sample data sets. Each sample data set can include a sample query statement and its label information. The label information can refer to the correct answer or target output associated with the sample query statement, such as a set of effective optimization hints for the sample query. The set of effective optimization hints can refer to a collection of hints used to help the database optimization component make more appropriate decisions in queries.

[0112] In one or more embodiments, database schemas from multiple benchmarks and all query SQLs can be used as the query tasks to be optimized, and label information is constructed for each query SQL to obtain a supervised fine-tuning database. Here, benchmarks can refer to the process of standardizing tests on a model, which can be used to understand the model's performance in a specific task (e.g., a database query task). A database schema can refer to a framework describing the structure and organization of a database, which can be used to define the structure of tables, fields, data types, relationships, constraints, etc., within the database. Query SQLs can refer to query instructions written in SQL, and each query SQL can serve as a sample query statement. For ease of understanding, the following description uses a sample query statement as an example to illustrate the construction process of the supervised fine-tuning database.

[0113] Specifically, the computer device can acquire a sample query statement. For this sample query statement, multiple hints can be enumerated to obtain M initial optimization hints for the sample query statement, where M is an integer greater than 1. Based on the hint types of the M initial optimization hints, the M initial optimization hints can be combined to obtain N sets of second optimization hints, where N is an integer greater than 1. Typically, N can be an integer greater than or equal to M. Each set of second optimization hints can include at least one initial optimization hint. Based on the execution evaluation parameters of each set of second optimization hints, at least two sets of first optimization hints are determined from the N sets of second optimization hints.

[0114] Each initial optimization hint can be a suggestion provided by the database optimization component to help it select the most suitable query execution strategy and provide fine-grained control over query execution. Different database management systems can support different types of initial optimization hints, which may include: join type hints, index hints, scan type hints, optimization strategy hints, query plan hints, locking hints, parallel execution hints, etc. This application does not limit the type of initial optimization hint. The type of initial optimization hint can be referred to as the hint type.

[0115] The second set of optimization hints can refer to a set of hints obtained by randomly combining at least one of the M initial optimization hints. For example, each set of second optimization hints may include one initial optimization hint, indicating that each initial optimization hint can be applied independently to the same sample query statement. Each set of second optimization hints may include two or more initial optimization hints, indicating that multiple different initial optimization hints can be applied to the same sample query statement. This application does not limit the number of initial optimization hints in the second set of optimization hints.

[0116] Execution evaluation parameters can refer to key metrics used to measure and optimize the execution efficiency of SQL queries. These parameters may include at least one of the following: execution time, CPU (Central Processing Unit) time, number of I / O (input / output) operations, etc., which are not limited in this application. These execution evaluation parameters can serve as a reference for selecting the first set of optimization suggestions. For example, based on the execution evaluation parameters of each second set of optimization suggestions, at least two first sets of optimization suggestions can be selected from N second sets of optimization suggestions, where the number of first sets of optimization suggestions is less than or equal to the number of second sets of optimization suggestions.

[0117] The first optimization hint set can refer to the effective hint set selected from N second optimization hint sets. Each first optimization hint set can generate its corresponding inference description. The inference description here can refer to the process of analyzing, rewriting, and optimizing the sample query statement based on each first optimization hint set. This inference description can be considered a "trial optimization" process, such as adding the first optimization hint set to the sample query statement.

[0118] In one or more embodiments, the process of determining at least two first optimization suggestion sets from N second optimization suggestion sets may include, but is not limited to: based on each second optimization suggestion set, invoking a database optimization component (database optimizer) to generate a sample execution strategy for a sample query statement under each second optimization suggestion set; executing the sample query statement in the database according to the sample execution strategy corresponding to each second optimization suggestion set to obtain the execution evaluation parameters for each second optimization suggestion set; and deleting second optimization suggestion sets from the N second optimization suggestion sets whose execution evaluation parameters are greater than a first parameter threshold to obtain at least two first optimization suggestion sets.

[0119] Here, the sample execution strategy can refer to the detailed operations or steps generated by the database optimization component for the current sample query statement based on each second optimization suggestion set. Each second optimization suggestion set can correspond to a sample execution strategy. By executing the sample query statement according to each sample execution strategy in sequence, the execution evaluation parameters of each second optimization suggestion set can be obtained. The first parameter threshold can refer to a pre-set threshold, which can be flexibly set according to the specific needs of the actual application scenario.

[0120] For example, when the evaluation parameter is execution time, the first parameter threshold can be twice the default execution time. Here, the default execution time can refer to the execution time of the sample query statement in the query without any hints. Optionally, the first parameter threshold can also be the default execution time; this application does not limit the value of the first parameter threshold.

[0121] Sample queries can be executed according to the sample execution strategy corresponding to each second optimization suggestion set. If the execution time exceeds the first parameter threshold, the execution of the sample query can be directly interrupted, and the current second optimization suggestion set can be deleted. This avoids unnecessary execution time and improves execution efficiency. For example, when executing a sample query according to the sample execution strategy of the second optimization suggestion set s1, if the execution time exceeds the first parameter threshold, the current query execution can be immediately interrupted and the second optimization suggestion set s1 can be deleted, regardless of whether the current query execution process is complete. For each second optimization suggestion set, it can be determined whether it can be retained using the above method. Ultimately, the retained second optimization suggestion sets can be used as the first optimization suggestion set.

[0122] Step S102: Based on the execution evaluation parameters of each first optimization suggestion set, construct sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets.

[0123] Specifically, the computer device can sort at least two first optimization suggestion sets according to the order of the execution evaluation parameters from smallest to largest, thus obtaining a suggestion set list. The first first optimization suggestion set in this suggestion set list may refer to the first optimization suggestion set with the smallest execution evaluation parameters; the last first optimization suggestion set in this suggestion set list may refer to the first optimization suggestion set with the largest execution evaluation parameters.

[0124] The first optimized hint set in the hint set list, along with the reasoning process of the first optimized hint set, can be identified as the sample optimization label of the sample query statement. At this point, (sample query statement, sample optimization label) can be used as a sample data in the supervised fine-tuning dataset.

[0125] The process of constructing sample optimization labels for sample query statements may include, but is not limited to: clustering at least two first optimization suggestion sets according to the sample execution strategy corresponding to each first optimization suggestion set to obtain at least one cluster; performing semantic analysis on the sample query statement and the first optimization suggestion sets within each cluster using a pre-trained language model to generate a third optimization suggestion set corresponding to each cluster, as well as an inference description of the third optimization suggestion set; obtaining the execution evaluation parameters of the third optimization suggestion set; and determining the third optimization suggestion set corresponding to the smallest execution evaluation parameter among the third optimization suggestion sets corresponding to at least one cluster as the label suggestion set; and then determining the label suggestion set and its inference description as the sample optimization labels for the sample query statement.

[0126] Clustering refers to automatically dividing at least two sets of initial optimization suggestions into multiple categories (clusters), such that the sets of initial optimization suggestions within each cluster are highly similar, while the sets of initial optimization suggestions between different clusters are significantly different. In other words, the purpose of clustering is to group at least two sets of initial optimization suggestions according to the similarity between their sample execution strategies, making the sets of initial optimization suggestions within the same cluster as similar as possible, while the sets of initial optimization suggestions between different clusters are as different as possible. A cluster can be considered a category, all sets of initial optimization suggestions within the same cluster can be considered to have the same or similar sample execution strategies, and sets of initial optimization suggestions within different clusters can have different sample execution strategies.

[0127] The clustering algorithm used in the embodiments of this application can be any one of K-means clustering, hierarchical clustering, or DBSCAN (Density-Based Spatial Clustering of Applications with Noise), and this application does not limit it. The number of at least one cluster can be pre-specified, such as by the K-means clustering algorithm, or it can be calculated by a clustering algorithm, such as DBSCAN.

[0128] Since all first-optimized hint sets within the same cluster have the same or similar sample execution strategies, a pre-trained language model can be used to analyze sample query statements and the first-optimized hint sets within each cluster to obtain a third-optimized hint set and its corresponding inference description. Each cluster can correspond to a third-optimized hint set, which can be a more effective set of hints generated for the sample query statement based on the first-optimized hint sets within the same cluster.

[0129] The execution evaluation parameters of the third optimization suggestion set corresponding to each cluster can be obtained. The process of obtaining the execution evaluation parameters of the third optimization suggestion set can be found in the process of obtaining the execution evaluation parameters of the second optimization suggestion set, and will not be repeated here. Among the third optimization suggestion sets corresponding to at least one cluster, the third optimization suggestion set with the smallest execution evaluation parameters can be determined as the label suggestion set. Then, the label suggestion set and the inference description of the label suggestion set can be determined as the sample optimization label of the sample query statement.

[0130] In one or more embodiments, two first optimization suggestion sets whose evaluation parameters are greater than the second parameter threshold, along with their respective inference descriptions, can be included in the suggestion set list as comparison optimization label pairs for the sample query statement. In this case, (sample query statement, comparison optimization label pair) can be used as sample data for direct preference optimization. Optionally, in at least one cluster corresponding to a third optimization suggestion set, two third optimization suggestion sets whose evaluation parameters are greater than the second parameter threshold, along with their respective inference descriptions, can be included as comparison optimization label pairs for the sample query statement.

[0131] The second parameter threshold can be a pre-set value, which represents the preference relationship between the two first optimization hint sets. The specific value can be flexibly set according to the specific requirements of the actual application scenario. The comparison optimization label pair can include two first optimization hint sets. The first optimization hint set with the smaller execution evaluation parameter can be used as the better hint set applied to the sample query statement, and the first optimization set with the larger execution parameter can be used as the worse hint set applied to the sample query statement.

[0132] For example, among at least two first optimization suggestion sets, first optimization suggestion set a and first optimization suggestion set b are determined. If the execution evaluation parameter of first optimization suggestion set a is greater than the execution evaluation parameter of first optimization suggestion set b, and the difference between the execution evaluation parameter of first optimization suggestion set a and the execution evaluation parameter of first optimization suggestion set b is greater than a second parameter threshold, then first optimization suggestion set a, and its inference description, can be determined as a positive optimization label for the sample query statement; first optimization suggestion set b, and its inference description, can be determined as a negative optimization label for the sample query statement. The positive optimization label and the negative optimization label are then determined as a comparative optimization label pair for the sample query statement.

[0133] In this context, positive optimization labels represent the set of correct hints for a sample query, while negative optimization labels represent the set of incorrect hints. By comparing the difference in execution evaluation parameters between the positive and negative optimization labels in a pair of optimization labels, ensuring the difference exceeds a second parameter threshold, the pre-trained language model can learn the preference relationship between the positive and negative optimization labels.

[0134] Optionally, one or more comparison optimization label pairs can be constructed for the same sample query statement. For example, among at least two first optimization suggestion sets, when the difference between the execution evaluation parameters of first optimization suggestion set a and first optimization suggestion set b is greater than a second parameter threshold, first optimization suggestion set a and first optimization suggestion set b can be used as comparison optimization label pair 1. When the difference between the execution evaluation parameters of first optimization suggestion set a and first optimization suggestion set c is greater than the second parameter threshold, first optimization suggestion set a and first optimization suggestion set c can be used as comparison optimization label pair 2. In this case, comparison optimization label pair 1 and comparison optimization label pair 2 are for the same sample structured statement, which can enrich the diversity of sample data.

[0135] Please see Figure 3 , Figure 3 This is a schematic diagram illustrating a sample query statement provided in an embodiment of this application. This embodiment uses execution time as an example for description, such as... Figure 3 As shown, for each sample query statement, multiple hints can be enumerated. For example, M initial optimization hints can be enumerated, namely initial optimization hint 11, initial optimization hint 12, initial optimization hint 13, ..., initial optimization hint 1M. At least one of the M initial optimization hints can be combined into N sets of second optimization hints, represented as second optimization hint set 21, second optimization hint set 22, second optimization hint set 23, ..., second optimization hint set 2N. For example, initial optimization hint 11 can be used as second optimization hint set 21, initial optimization hint 11 and initial optimization hint 13 can be combined into second optimization hint set 22, initial optimization hint 12 can be used as second optimization hint set 23, and initial optimization hint 12, initial optimization hint 13, and initial optimization hint 1M can form second optimization hint set 2N.

[0136] For each second optimization suggestion set, a sample query statement can be executed to obtain the effective optimization suggestion set, its corresponding execution strategy, and execution time. To avoid unnecessary execution time consumption, the query operation of the sample query statement can be interrupted when the execution time of a second optimization suggestion set exceeds the first parameter threshold, thus improving execution efficiency. For example, when executing a sample query statement according to the second optimization suggestion set 2N, if the execution time exceeds the first parameter threshold, the current query operation can be directly interrupted, and the second optimization suggestion set 2N can be deleted. When the execution time does not exceed the first parameter threshold, the corresponding second optimization suggestion sets can be retained, and their execution times can be obtained. For example, the execution time of the second optimization suggestion set 21 can be denoted as t1, the execution time of the second optimization suggestion set 22 as t2, the execution time of the second optimization suggestion set 23 as t3, etc., where the execution times t1, t2, and t3 are all less than or equal to the first parameter threshold. The second optimization suggestion sets whose execution times are less than or equal to the first parameter threshold can be determined as the first optimization suggestion sets.

[0137] like Figure 3 As shown, if the execution time t2 of the first optimization suggestion set 32 ​​is the smallest among all the first optimization suggestion sets, then the first optimization suggestion set 32 ​​and its corresponding inference description can be determined as the sample optimization label of the sample query statement. If the difference between the execution time t1 of the first optimization suggestion set 31 and the execution time t3 of the first optimization suggestion set 33 is greater than the second parameter threshold, then the first optimization suggestion set 31 and the first optimization suggestion set 33 can be constructed as a comparison optimization label pair. If the execution time t1 is greater than the execution time t3, then the first optimization suggestion set 31 and its corresponding inference description can be used as a positive optimization label, and the first optimization suggestion set 33 and its corresponding inference description can be used as a negative optimization label.

[0138] Step S103: Obtain the first database auxiliary information of the sample query statement, and construct the first task prompt information based on the sample query statement and the first database auxiliary information.

[0139] Specifically, the computer device can obtain the first database auxiliary information of the sample query based on the database pattern corresponding to the sample query statement. Then, the sample query statement and the first database auxiliary information can be used to construct the first task prompt information. This first task prompt information can be used as input data for the pre-trained language model in the supervised fine-tuning process.

[0140] The first database auxiliary information can refer to information used to assist the database optimization component in generating a more accurate set of hints. The first task prompt text can be text or instructions directed to the input of the pre-trained language model, guiding the pre-trained language model to generate specific outputs. The first task prompt information can be considered as prompt words designed for the pre-trained language model, and can serve as input data for the pre-trained language model.

[0141] In one or more embodiments, the process of constructing the first task prompt information may include, but is not limited to: constructing an abstract syntax tree of the sample query statement, obtaining the nested representation text of the abstract syntax tree, and determining the sample query statement, the nested representation text, and the default execution strategy of the sample query statement in the database as query semantic information; obtaining database statistics and database metadata, obtaining database load detection information, and determining the statistics, database metadata, and load detection information as first database auxiliary information; obtaining task instruction text and task example text, and determining the query semantic information, the first database auxiliary information, the task instruction text, and the task example text as the first task prompt information.

[0142] The first database auxiliary information may include query semantic information of the sample query statement, database metadata, statistical information, load detection information, etc., which are not limited in this application. The query semantic information may refer to the logical meaning of the sample query statement, and may include, but is not limited to, the sample query statement, the nested representation text of the abstract syntax tree, and the default execution strategy selected by the database for the sample query statement.

[0143] An Abstract Syntax Tree (ABST) is a tree-like structure that represents the syntactic structure of a sample query statement. Each node in the ABST can represent a part of the sample query statement, such as operators, keywords, tables, columns, etc. Nested representation text can be used to represent the hierarchical relationship between different operations and expressions in the sample query statement. The default execution strategy refers to the query execution strategy found by the database optimization component in the current database management system for the sample query statement. The default execution strategy can be a tree structure, represented in JSON (JavaScript Object Notation, JavaScript is a lightweight, interpreted programming language) format, and can include the join order, the corresponding algorithm used for each operator, and related tables, etc.

[0144] For example, suppose the sample query statement is as follows:

[0145] SELECT name, age

[0146] FROM employees

[0147] WHERE age>30

[0148] ORDER BY age DESC;

[0149] The nested representation of the abstract syntax tree of this sample query statement can then be represented as follows:

[0150]

[0151] Here, SELECT represents a selection statement, which can be a node in the abstract syntax tree listing the columns the sample query needs to return. Columns represents the columns to be queried: name and age. FROM represents the source table of the queried data, where employees is the table name. WHERE represents the query's filtering conditions, one of which is age>30 (years greater than 30), a comparison expression. Operand represents the operands, and Operator represents the operator. ORDER BY represents the sorting operation, DESC indicates sorting the age column in descending order, and Direction indicates the direction.

[0152] Database metadata can include table metadata and configuration metadata. Table metadata refers to the structural metadata of a database table, which can be provided by the database schema creation statement, and multiple values ​​can be pre-selected for each column in the table to enrich its semantics. Configuration metadata refers to a series of key parameters, schemas, etc., that affect database performance.

[0153] Statistical information includes table-level, index-level, and column-level statistics, such as the tables, indexes, and columns involved in the sample query statement. Table-level statistics may include the estimated number of rows, average row length, proportion of data stored in memory, and compression ratio. Index-level statistics may include index vector information for different indexes (the number of rows corresponding to different index values), the proportion of NULL (unknown or missing) values, and the proportion of index structure data stored in memory. Column-level statistics may include the number of different literals in different columns, value range information, proportion of NULL values, or histogram information.

[0154] Load monitoring information refers to performance metrics related to the current data load, such as CPU speed, memory usage, I / O utilization, cache hit rate, and QPS (Queries Per Second). Task instruction text describes the role of the pre-trained language model in the database query optimization task, along with relevant text for that task. Task example text provides a concrete example of the database query optimization task, including how to understand the input data, the thought process for optimizing a given sample query, and the final set of suggested hints. By providing a problem-solving paradigm (task example text), the pre-trained language model's thinking can be guided.

[0155] Step S104: Based on the first task prompt information and sample optimization labels, the network parameters of the pre-trained language model are corrected to obtain the first query optimization model.

[0156] Specifically, the model training phase can include a supervised fine-tuning phase and a direct preference optimization phase. After constructing the sample optimization labels for the sample query statements, the computer device can use the first task prompt information as the input data of the pre-trained language model and the sample optimization labels as the output data to perform supervised fine-tuning on the pre-trained language model, thereby obtaining the first query optimization model.

[0157] In the supervised fine-tuning phase, the pre-trained language model can learn how to modify the query for a given instance (e.g., setting a reasonable hint set) to obtain an effective query execution strategy and shorter execution time. In other words, the first query optimization model can refer to the larger model obtained by supervised fine-tuning the pre-trained language model. Compared to the pre-trained language model, it can generate a more accurate hint set for sample queries, resulting in a more effective query execution strategy and shorter execution time.

[0158] Step S105: Based on the first task prompt information and the comparison optimization label pair, optimize the parameters of the first query optimization model to obtain the second query optimization model.

[0159] Specifically, after supervising the pre-trained language model to obtain the first query optimization model, the computer device can continue to perform direct preference optimization on the first query optimization model. In the direct preference optimization stage, preference feedback data can be added to each sample query statement. This preference feedback data can be the relative merits of different hint sets, such as comparing the positive and negative optimization labels in an optimization label pair. These positive and negative optimization labels can be determined by the execution evaluation parameters.

[0160] Direct preference optimization can be used to align preference feedback data, further optimizing hint selection. This allows the second query optimization model, obtained during the direct preference optimization stage, to more accurately predict the hint set of sample query statements. The second query optimization model can refer to the larger model obtained by performing direct preference optimization on the first query optimization model.

[0161] In this embodiment, a sample query statement can be obtained, and at least two first optimization suggestion sets can be generated for that sample query statement. Based on the execution evaluation parameters of each first optimization suggestion set, sample optimization labels and comparison optimization label pairs are constructed in the at least two first optimization suggestion sets, which can improve the annotation quality of the sample query samples. Based on the sample query statement and first database auxiliary information, first task suggestion information is constructed. Using the first task suggestion information as input data and the sample optimization labels as output data, supervised fine-tuning of the pre-trained language model is performed to obtain a first query optimization model. The first query optimization model can be further optimized using direct preference to obtain a second query optimization model, which can improve the performance of the second query optimization model. Applying the second query optimization model to database query optimization tasks can improve the accuracy of database query optimization.

[0162] Please see Figure 4 , Figure 4 This is a flowchart illustrating a database query optimization method provided in an embodiment of this application. Figure 2 It is understandable that this database query optimization method can be executed by a computer device, which can be a terminal device, such as... Figure 1 Any terminal device in the terminal cluster shown; or it can be a server, such as Figure 1 The server 10d shown is not limited in this application. Figure 4 As shown, this may include the following steps S201 to S211:

[0163] Step S201: Obtain the sample query statement and generate at least two first optimization hint sets for the sample query statement.

[0164] Step S202: Based on the execution evaluation parameters of each first optimization suggestion set, construct sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets.

[0165] Step S203: Obtain the first database auxiliary information of the sample query statement, and construct the first task prompt information based on the sample query statement and the first database auxiliary information.

[0166] The specific implementation process of steps S201 to S203 can be found in [reference needed]. Figure 2The relevant descriptions of steps S101 to S103 in the corresponding embodiments will not be repeated here.

[0167] Step S204: Using a pre-trained language model, output the first predicted conditional probability of the optimized label for the sample based on the first task prompt information.

[0168] Specifically, the computer device can input the first task prompt information into a pre-trained language model. This pre-trained language model then performs forward computation on the first task prompt information, outputting a first predicted conditional probability for the optimized label of the sample. In other words, the first task prompt information can serve as input data to the pre-trained language model, and the optimized label of the sample can be used as output data for supervised fine-tuning of the pre-trained language model.

[0169] The sample optimization label can be a text sequence, which can consist of a series of tokens. A token can be considered a basic unit in natural language processing; it can be a single character, word, phrase, etc., and this application does not impose any limitations on this. For example, the text sequence corresponding to the sample optimization label can be represented as y = (y 1 ,y 2 ,y 3 ,…,y K ), where y represents the sample optimized label, and K represents the number of lexical units in the sample optimized label. The first prediction conditional probability can be expressed as shown in formula (1):

[0170]

[0171] In formula (1), x represents the first task prompt information, θ represents the network parameters of the pre-trained language model, and y 1 y represents the first term in the sample optimized label. 2 y represents the second term in the sample optimized label. K y represents the Kth word in the sample optimized label. t Let p represent the t-th word in the sample optimized label, where t is a positive integer less than or equal to K. θ (y|x) represents the first predicted conditional probability output by the pre-trained language model. This first predicted conditional probability can refer to the conditional probability value of the optimized label for the sample predicted by the pre-trained language model based on the first task prompt information.

[0172] p θ (y t |x,y 1:t-1 ) represents the conditional probability value of the t-th word, calculated based on the conditional probability values ​​of the first t-1 words output by the pre-trained language model; y 1:t-1This represents the first to the (t-1)th word in the sample optimized label. The first predicted conditional probability can be obtained by multiplying the conditional probability values ​​of each word in the sample optimized label. For example, the first predicted conditional probability can be obtained by multiplying the conditional probability values ​​of the first word, the second word, ..., the Kth word.

[0173] Step S205: Determine the negative log-likelihood loss of the first prediction conditional probability, and adjust the network parameters of the pre-trained language model based on the negative log-likelihood loss to obtain the first query optimization model.

[0174] Specifically, in the supervised fine-tuning stage, the negative log-likelihood of the first predicted conditional probability can be used as the loss of the pre-trained language model during supervised fine-tuning. By minimizing the negative log-likelihood, the network parameters of the pre-trained language model are adjusted to obtain the fine-tuned first query optimization model.

[0175] After the pre-trained language model outputs the first predicted conditional probability, the negative log-likelihood loss of the first predicted conditional probability can be calculated, and its partial derivative can be taken. Representing the partial derivative as a vector yields the gradient vector of the pre-trained language model. Using stochastic gradient descent or other algorithms, the gradient vector is backpropagated to update the network parameters of the pre-trained language model. When the pre-trained language model reaches the training stopping condition, training can be stopped, and the pre-trained language model at the point of training stoppage is defined as the first query optimization model. The training stopping condition can be a pre-set maximum number of training iterations, or it can be reaching a convergence condition (such as the negative log-likelihood loss reaching a pre-set error range); this application does not limit this.

[0176] During the supervised fine-tuning phase, the negative log-likelihood loss of the pre-trained language model can be calculated as shown in Equation (2):

[0177]

[0178] in, This represents the negative log-likelihood loss of the pre-trained language model during the supervised fine-tuning phase. Let represent the negative log-likelihood of the first predictive conditional probability, and log represent the logarithmic function.

[0179] Supervised fine-tuning is essentially supervised imitation learning, a widely used guidance strategy. In this phase, pre-trained language models can be used to interpret and solve database query optimization tasks, mimicking general database optimization components. Combined with an effective hint set (sample optimization labels), a more accurate hint set can be generated for a specific database query optimization task. After supervised fine-tuning, the first query optimization model is obtained.

[0180] Please see Figure 5 , Figure 5 This is a training diagram of a pre-trained language model provided in an embodiment of this application. For example... Figure 5 As shown, sample query statements and sample optimization labels can be used as sample data for the pre-trained language model during the supervised fine-tuning stage. The sample query statements can serve as input data to the pre-trained language model, and the sample optimization labels can serve as output data. By performing semantic analysis on the first task prompt information through the pre-trained language model, the first predicted conditional probability for the sample query statement can be obtained. The negative log-likelihood loss of the first predicted conditional probability is calculated. By minimizing the negative log-likelihood loss, the network parameters of the pre-trained language model are trained. After supervised fine-tuning, the first query optimization model can be obtained.

[0181] Step S206: Back up the first query optimization model to obtain a reference model, and fix the network parameters of the reference model.

[0182] In this embodiment, after supervised fine-tuning, the first query optimization model can be further trained to distinguish between good and ordinary hint set suggestions, thereby enhancing the first query optimization model's ability to generate more effective hint set suggestions. A good hint set can be labeled as a positive optimization, while an ordinary hint set can be labeled as a negative optimization. For example, direct preference optimization techniques can be used to further adjust the first query optimization model. Direct preference optimization techniques can increase the likelihood of the first query optimization model generating a good hint set and decrease the likelihood of it generating an ordinary hint set.

[0183] Specifically, the computer equipment can back up the first query optimization model after supervised fine-tuning, and use the backed-up first query optimization model as a reference model for the direct preference optimization stage. This reference model can be denoted as π. sft In the direct preference optimization phase, the reference model π can be fixed. sft The network parameters, i.e., no need to modify the reference model π. sft Training is then performed. It is understandable that a reference model π is used during the direct preference optimization phase. sft This can prevent model rollback.

[0184] Step S207: Using the reference model, output the second predicted conditional probability for the positive optimization label and the third predicted conditional probability for the negative optimization label based on the first task prompt information.

[0185] Specifically, computer devices can use the initial task prompt information as a reference model π. sft The input data will use the positive optimization label as the reference model π. sft The output data is obtained through the reference model π. sft Output the second predicted conditional probability for the positive optimization label. This second predicted conditional probability can refer to the reference model π. sft Based on the initial task prompts, the conditional probability value of the positive optimized label is predicted. Reference model π sft The conditional probability values ​​of each word in the positive optimization label can be output sequentially. By multiplying the conditional probability values ​​of each word in the positive optimization label, the second prediction conditional probability can be calculated. The second prediction conditional probability can be expressed as π. sft (y w |x), y w This indicates the positive optimization label for the sample query statement, x represents the first task prompt information, and π sft This represents the reference model.

[0186] The first task prompt information is used as the reference model π sft The input data uses the negative optimization label as the reference model π. sft The output data is obtained through the reference model π. sft It can output a third predictive conditional probability for the negative optimization label. This third predictive conditional probability can refer to the reference model π. sft Based on the initial task prompts, the conditional probability value of the negative optimized label is predicted. Reference model π sft The conditional probability values ​​of each word in the negative optimization label can be output sequentially. By multiplying the conditional probability values ​​of each word in the negative optimization label, the third prediction conditional probability can be calculated. The third prediction conditional probability can be represented as π. sft (y l |x), y l This indicates a positive optimization label for the sample query statement.

[0187] like Figure 5 As shown, the sample query statement, positive optimization label, and negative optimization label can be input into the reference model. This reference model can then predict the second prediction conditional probability (π) of the positive optimization label. sft (y w |x)), and the third prediction conditional probability (π) of the negative optimization label. sft (yl It is understood that the calculation methods for the second and third prediction condition probabilities can be found in formula (1), which is the same as the calculation method for the first prediction condition probability mentioned above. This will not be elaborated further here.

[0188] Step S208: Using the first query optimization model, output the fourth predicted conditional probability for the positive optimization label and the fifth predicted conditional probability for the negative optimization label based on the first task prompt information.

[0189] Specifically, the computer device can use the first task prompt information as input data for the first query optimization model, and the positive optimization label as output data for the first query optimization model. The first query optimization model then outputs a fourth predicted conditional probability for the positive optimization label. This fourth predicted conditional probability can refer to the conditional probability value of the positive optimization label predicted by the first query optimization model based on the first task prompt information. The first query optimization model can sequentially output the conditional probability values ​​of each word element in the positive optimization label. By multiplying the conditional probability values ​​of each word element in the positive optimization label, the fourth predicted conditional probability can be calculated. The fourth predicted conditional probability can be represented as π. dpo (y w |x), π dpo This represents the first query optimization model.

[0190] The first task prompt information is used as input data for the first query optimization model, and the negative optimization label is used as output data. This first query optimization model can output a fifth prediction conditional probability for the negative optimization label. This fifth prediction conditional probability refers to the conditional probability value of the negative optimization label predicted by the first query optimization model based on the first task prompt information. The first query optimization model can sequentially output the conditional probability values ​​of each word element in the negative optimization label. By multiplying the conditional probability values ​​of each word element in the negative optimization label, the fifth prediction conditional probability can be calculated. The fifth prediction conditional probability can be represented as π. dpo (y l |x).

[0191] like Figure 5 As shown, the sample query statement, positive optimization label, and negative optimization label can be input into the first query optimization model. This first query optimization model can then predict the fourth prediction conditional probability (π) of the positive optimization label. dpo (y w |x)), and the fifth prediction conditional probability (π) of the negative optimization label. dpo (y l|x)). It is understandable that the calculation methods for the fourth and fifth prediction condition probabilities can be found in formula (1), which is the same as the calculation method for the first prediction condition probability mentioned above, and will not be repeated here.

[0192] Step S209: Determine the contrast preference difference between the positive and negative optimization labels based on the first logarithmic ratio between the fourth and second prediction conditional probabilities and the second logarithmic ratio between the fifth and third prediction conditional probabilities.

[0193] Specifically, after calculating the second, third, fourth, and fifth prediction condition probabilities, the payoff difference between the positive and negative optimization labels can be calculated based on the first logarithmic ratio between the fourth and second prediction condition probabilities and the second logarithmic ratio between the fifth and third prediction condition probabilities. This payoff difference can be called the contrast preference difference.

[0194] The calculation method for this contrast preference difference can be shown in formula (3):

[0195]

[0196] In formula (4), u(x,y) w ,y l ) can represent the difference in contrasting preferences between the positive and negative optimization labels learned by the first query optimization model. This represents the first logarithm ratio between the fourth and second predicted conditional probabilities. This represents the second logarithmic ratio between the fifth and third predicted conditional probabilities. `log` represents the logarithmic function, and `β` represents the balancing coefficient, used to control the weights of the first and second logarithmic ratios and adjust the contrast intensity.

[0197] Step S210: Perform probabilistic mapping on the contrast preference difference to obtain the preference optimization loss of the first query optimization model for positive and negative optimization labels.

[0198] Specifically, the computer device can use the sigmoid function (an activation function) to map the aforementioned contrasting preference differences to (0, 1), obtaining the preference optimization loss of the first query optimization model for positive and negative optimization labels. The sigmoid function can transform the preference relationship between positive and negative optimization labels into a probabilistic form of loss. The preference optimization loss can be expressed as shown in formula (4):

[0199]

[0200] in, This represents the preference optimization loss of the first query optimization model in the direct preference optimization stage, where θ represents the network parameters of the first query optimization model. σ represents the sigmoid function. -logσ(·) represents the negative log-likelihood form; the closer the sigmoid output (i.e., the output of σ(·)) is to 1, the more the first query optimization model favors the positive optimization label y. w The smaller the preference optimization loss, the closer the Sigmoid output is to 0 (the first query optimization model confuses the positive optimization label y). w and negative optimization label y l The greater the preference optimization loss, the better.

[0201] Step S211: Based on the preference optimization loss, the network parameters of the first query optimization model are corrected to obtain the second query optimization model.

[0202] Specifically, the computer device can calculate the partial derivative of the preference optimization loss, and represent the partial derivative as a vector to obtain the gradient vector of the first query optimization model. During the direct preference optimization stage, a stochastic gradient descent algorithm, or other algorithms, can be used to backpropagate the gradient vector and update the network parameters of the first query optimization model. When the first query optimization model reaches the training stopping condition, training of the first query optimization model can be stopped, and the first query optimization model at the time of training stoppage is determined as the second query optimization model. The training stopping condition can be a pre-set maximum number of training iterations, or it can be reaching a convergence condition (such as the preference optimization loss reaching a pre-set error range); this application does not limit this.

[0203] In this embodiment, a domain-specific large model (second query optimization model) for database optimization components can be constructed. In the database query optimization task, the pre-trained language model (large language model) is subjected to supervised fine-tuning and direct preference optimization, so that the trained second query optimization model can capture deep contextual information in complex SQL queries and improve the accuracy of the query optimization suggestion set.

[0204] Understandably, after supervised fine-tuning and direct preference optimization, a pre-trained language model can be trained to obtain a second query optimization model, which can then be applied to database query optimization tasks. Please see [link / reference]. Figure 6 , Figure 6 This is a flowchart illustrating a database query optimization method provided in an embodiment of this application. Figure 3 It is understandable that this database query optimization method can be executed by a computer device, which can be a terminal device, such as... Figure 1 Any terminal device in the terminal cluster shown; or it can be a server, such as Figure 1The server 10d shown is not limited in this application. Figure 8 As shown, this may include the following steps S301 to S304:

[0205] Step S301: Obtain the structured query statement and the second database auxiliary information of the structured query statement, and construct the second task prompt information based on the structured query statement and the second database auxiliary information.

[0206] In this embodiment, the application of the second query optimization model in the database query optimization task can be referred to as the inference phase of the database query optimization task. To supplement relevant database knowledge during the inference phase and reduce the illusion problem in the output results, a query optimization knowledge base and a query optimization toolkit can be pre-built. The illusion problem refers to the situation in the database query optimization task where duplicate or erroneous data is read, leading to incorrect output results.

[0207] Among them, a query optimization knowledge base is a systematic way of storing, organizing, and managing information in database query optimization scenarios. A query optimization toolkit can refer to a set of auxiliary tools that support database query optimization tasks. The auxiliary tools in the query optimization toolkit can integrate external resources and functional modules through standardized interfaces, making up for the limitations of the second query optimization model in areas such as real-time data acquisition and specialized calculations.

[0208] In one or more embodiments, the process of constructing a query optimization knowledge base may include, but is not limited to: a computer device acquiring database corpus data, performing semantic segmentation on the database corpus data to obtain a set of corpus data blocks, performing vectorization encoding on each corpus data block in the set of corpus data blocks to obtain a data encoding vector for each corpus data block, and constructing a query optimization knowledge base using the data encoding vectors of each corpus data block.

[0209] The database corpus data may include database documents, community discussion Q&A and articles, query optimization strategy case documents, execution plan explanations, and database optimization component hint usage instructions, etc., which are not limited in this application. Documents can be segmented according to the semantics of each type of corpus data, such as by document chapters, question-and-answer pairs, or explanations of individual keywords, dividing each type of corpus data into at least two corpus data blocks, and combining these corpus data blocks into a corpus data block set. In other words, a corpus data block set may include multiple corpus data blocks.

[0210] Furthermore, each corpus data block in the corpus data block set can be vectorized to obtain a data encoding vector for each corpus data block. The vectorization encoding of each corpus data block can refer to the process of mapping the corpus data block to a vector space. For example, it can be performed using an embedding layer network, or other methods can be used to vectorize the corpus data blocks; this application does not limit this approach. The data encoding vectors of each corpus data block are then used to construct a query optimization knowledge base.

[0211] In this embodiment, by introducing a query optimization knowledge base, the domain knowledge of the second query optimization model can be supplemented. The second query optimization model can connect the query optimization toolkit and the query optimization knowledge base through MCP (Model Context Protocol), enabling the second query optimization model to access and use relevant domain knowledge and external functions (e.g., database interaction), thereby improving the accuracy of the query optimization suggestion set.

[0212] The MCP protocol is a standardized protocol that defines a standardized communication method between a model (e.g., a second query optimization model) and the context. This MCP protocol can define specific communication protocols, data formats, rules, etc., allowing the second query optimization model to access various types of services when accessing the context.

[0213] In one or more embodiments, the process of creating a query optimization toolkit may include, but is not limited to, building at least one auxiliary tool based on the database query optimization task, such as... Figure 7 As shown, the at least one auxiliary tool here may include, but is not limited to: optimizing switch checks, calculating selectivity, obtaining indexes, checking statistical information, retrieving database knowledge, and performing analysis, etc., and this application does not limit this. For each auxiliary tool, the interface description information of each of the at least one auxiliary tool can be obtained, and the at least one auxiliary tool and the interface description information of each auxiliary tool can be constructed into the query optimization tool library. Each auxiliary tool in this query optimization tool library can interact with the database and the query optimization knowledge base.

[0214] Specifically, the optimization switch check refers to checking whether the database optimization options and configuration functions are enabled. These options and configuration functions control how the database optimization component selects the query execution strategy (query execution plan). Calculating the selectivity refers to the filtering ability of query conditions, typically expressed as the ratio of the number of rows that meet the conditions to the total number of rows in the table. Retrieving indexes refers to the indexes that can be used when executing structured queries. Checking statistics verifies whether the statistics stored in the database are up-to-date. Retrieving database knowledge refers to using the database's internal knowledge base to obtain detailed information about tables, columns, indexes, etc. Execution analysis refers to a detailed review of the database query execution process, such as analyzing the query execution strategy generated by the database optimization component.

[0215] Interface descriptions can be used to describe the usage methods, specific implementation steps, and returned data of each auxiliary tool's API (Application Programming Interface). Each auxiliary tool's API can be registered with the database management system where the second query optimization model resides. To improve the ease of calling auxiliary tools in the query optimization tool library, a hint template is constructed for each hint type (e.g., index, join order) to represent the role of each intelligent object (AI Agent). An intelligent object is an intelligent system architecture integrating multi-module capabilities. Its core is driven by the second query optimization model, achieving fully automated processing of database query optimization tasks through integrated planning, memory, and tool invocation mechanisms. The hint template can be used to describe the intelligent object's role in the database query optimization task, task description, basic steps, and available auxiliary tools.

[0216] Specifically, when an object wants to query data stored in a database, it can enter a structured query statement through the database management system. This structured query statement can be code instructions written using the Structured Query Language (SQL).

[0217] Please see Figure 7 , Figure 7 This is a reasoning diagram illustrating a query optimization suggestion set for a structured query statement provided in an embodiment of this application. For example... Figure 7 As shown, when an object inputs a structured query statement, the computer device can obtain the structured query statement and second database auxiliary information about the database indicated by the structured query statement. The second database auxiliary information may refer to relevant information used to assist database optimization components in generating a more accurate set of hints.

[0218] Secondary database auxiliary information may include, but is not limited to, the abstract syntax tree of structured query statements, database metadata, statistical information, load monitoring information, and execution information. Execution information may include the default execution strategy of structured query statements, query performance statistics, the execution order of each operation, and how the database accesses data. Query performance statistics may include the number of rows returned by the query, execution time, I / O operations, and memory usage.

[0219] In database query optimization tasks, structured query statements and second database auxiliary information can be used to construct the second task prompt information for the second query optimization model. For details on how to construct the second task prompt information, please refer to [link to documentation / reference]. Figure 2 The method for constructing the first task prompt information in the corresponding embodiment will not be described again here.

[0220] Step S302: Using the second query optimization model, output a set of query optimization suggestions for structured query statements based on the second task prompt information.

[0221] Understandably, in database query optimization tasks, a multi-intelligent object architecture can be introduced to perform in-depth analysis and verification of the structured query statements input by the objects, and to explore multiple times using a tree search strategy to obtain multiple sets of query optimization suggestions. These multiple sets of query optimization suggestions can be recorded in the form of a tree structure, where each node can contain a set of query optimization suggestions.

[0222] The multi-intelligent object architecture can include multiple intelligent objects, each playing a different role in the database query optimization task. These intelligent objects can collaborate to complete the query process of structured queries. Each intelligent object can autonomously decide which auxiliary tools in the query optimization toolkit to call, and based on the feedback from the called auxiliary tools, it considers which auxiliary tools to use next, iterating continuously until the optimal set of hints is selected.

[0223] The multi-intelligent object architecture includes multiple intelligent objects, which can include a coordinating intelligent object and a set of dedicated intelligent objects. The set of dedicated intelligent objects can include at least one dedicated intelligent object. The coordinating intelligent object can be used to interact with the database by invoking multiple auxiliary tools in the query optimization toolkit. Based on the current query execution strategy and execution information, the coordinating intelligent object can also analyze the optimizable methods and optimizable local regions within the current query execution strategy, and then invoke the corresponding dedicated intelligent objects to respond. The communication method between the coordinating intelligent object and at least one dedicated intelligent object can be called a "ring topology." In this communication method, the coordinating intelligent object is the center, and all dedicated intelligent objects communicate only with the coordinating intelligent object, ensuring process controllability.

[0224] Each proprietary smart object can receive specific hint recommendations, such as Figure 7 As shown, the proprietary smart object set can include index smart objects, join order smart objects, parameter configuration smart objects, operator type smart objects, etc. Among them, index smart objects can be used for index optimization, join order smart objects can be used for join order optimization, operator type smart objects can be used for operator type optimization, and parameter configuration smart objects can be used for parameter configuration optimization, etc.

[0225] Each proprietary intelligent object can invoke relevant auxiliary tools according to the hint template to optimize the specified query execution strategy and provide hint recommendations. The hint recommendations provided by the proprietary intelligent object are then transmitted to the coordinating intelligent object, which can replace the original hints with the hints provided by the proprietary intelligent object, resulting in a new set of query optimization hints. For example, if the query optimization hint set hints1 contains hints A, B, and C, and the proprietary intelligent object optimizes hint A to obtain hint A1, it can transmit hint A1 to the coordinating intelligent object. The coordinating intelligent object can then replace hint A in the query optimization hint set hints1 with hint A1 output by the proprietary intelligent object, resulting in a new set of query optimization hints that can contain hints A1, B, and C.

[0226] Specifically, after constructing the second task hint information, the computer device can invoke the trained second query optimization model through the coordinating intelligent object in the multi-intelligent object architecture, inputting the second task hint information into the second query optimization model. Based on the second query optimization model, forward reasoning is performed on the second task hint information, outputting a set of query optimization hints for structured query statements, which can be denoted as hints1.

[0227] The second query optimization model can simulate the data optimization component in a database management system. Based on the second task's hints, it analyzes and understands the structured query statement, generating a set of query optimization hints, `hints1`, for that structured query statement. In other words, the set of query optimization hints, `hints1`, refers to the set of hints generated by the second query optimization model for the structured query statement.

[0228] It is understandable that the basic network of the second query optimization model can be a currently common large language model, and its forward inference process is determined by the network structure of the second query optimization model. This application does not limit the network structure of the second query optimization model.

[0229] Step S303: Update the query optimization suggestion set based on the query optimization knowledge base and query optimization toolkit to obtain at least one candidate suggestion set.

[0230] Step S304: Determine a set of recommended suggestions for the structured query statement from at least one set of candidate suggestions.

[0231] In this embodiment, after the second query optimization model outputs a set of query optimization hints1, the structured query statement can be simulated and executed in the database management system based on this set of hints1 to generate a query execution strategy for the set of hints1. The system can coordinate intelligent objects to call auxiliary tools in the query optimization toolkit, interact with the database and query optimization knowledge base, and deeply analyze the problems existing in the query execution strategy of the set of hints1. Based on the problems, the system calls the corresponding dedicated intelligent objects to perform targeted optimizations and generate a new set of query optimization hints.

[0232] Understandably, each proprietary intelligent object can invoke auxiliary tools in the query optimization toolkit, and the output of each proprietary intelligent object needs to be returned to the coordinating intelligent object, which then outputs a new set of query optimization hints. By iterating through this process, at least one candidate hint set can be obtained. This candidate hint set can include all query optimization hint sets generated during the query optimization process of the database query optimization task; that is, this at least one candidate hint set can include the aforementioned query optimization hint set hints1, as well as the new query optimization hint set obtained by invoking the proprietary intelligent object. The best candidate hint set can be selected from this at least one candidate hint set as the optimal hint set chosen by the database optimization component for the structured query statement, i.e., the recommended hint set. For example, the candidate hint set with the shortest execution time among the at least one candidate hint set can be determined as the recommended hint set.

[0233] It should be noted that during the query optimization process, structured query statements can be simulated in the database for each set of candidate suggestions to obtain information such as the query execution strategy (query execution plan) and execution time for each set of candidate suggestions.

[0234] In one or more embodiments, such as Figure 7As shown, the computer device can construct the root node 20a of a tree structure based on the query optimization suggestion set. By coordinating intelligent objects to call the query optimization toolkit and query optimization knowledge base, it analyzes and verifies the query optimization suggestion set, obtaining feedback evaluation information. Here, the tree structure is a data structure used to represent elements with hierarchical relationships; it can consist of nodes and edges. Each node in the tree structure can record the related intelligent object's thinking, actions, and observations regarding the structured query statement. Thinking can refer to the process by which the coordinating intelligent object processes, reasons, and judges environmental information during the decision-making process; actions can refer to the operations or steps performed by the coordinating intelligent object in the environment; and observations can refer to the information about the environment acquired by the coordinating intelligent object.

[0235] Root node 20a can refer to the top-level node of the tree structure. The "thinking" in root node 20a can be understood as the process of coordinating the intelligent object to use the second query optimization model to semantically understand the second task hint information and generate a set of query optimization hints, hints1. The "action" in root node 20a can be understood as the operation of coordinating the intelligent object to obtain the structured query statement and construct the second task hint information. The "observation" in root node 20a can be understood as the coordinating intelligent object obtaining the set of query optimization hints, hints1, output by the second query optimization model.

[0236] Feedback evaluation information can refer to the coordinated intelligent object calling one or more auxiliary tools in the query optimization toolkit to interact with the database and query optimization knowledge base, and obtaining the problems existing in the query execution strategy of the query optimization hint set hints1.

[0237] The computer device can determine the first suggestion to be modified in the query optimization suggestion set hints1 based on the feedback evaluation information. The first suggestion to be modified can refer to the optimizable direction or optimizable local region determined by the coordinating intelligent object for the query optimization suggestion set hints1. A first proprietary intelligent object matching the first suggestion to be modified can be determined from the proprietary intelligent object set. For example, if the first suggestion to be modified is an index, the first proprietary intelligent object determined from the proprietary intelligent object set can be the index intelligent object.

[0238] The first dedicated smart object, such as the index smart object, can be invoked to modify the query optimization hint set `hints1` in the root node, resulting in a first candidate hint set, which can be denoted as `hints2`. This first candidate hint set can then be constructed as the first child node `20b` of the root node. Based on the root node `20a` and the first child node `20b`, the tree structure can be expanded to obtain at least one candidate hint set, which includes the first candidate hint set. For example... Figure 7 As shown, the first child node 20b can be a child node of the root node 20a. By exploring the root node 20a, the tree structure can be expanded to generate the first child node 20b. At this point, the number of visits to the root node 20a can be recorded as 1. The thinking process in the first child node 20b can be understood as the process of the index intelligent object performing in-depth analysis and verification of the query optimization hint set hints1. The action process in the first child node 20b can be understood as the index intelligent object performing index optimization on the query optimization hint set hints1. The observation process in the first child node 20b can be understood as the index intelligent object obtaining the first candidate hint set hints2. Based on the existing root node 20a and first child node 20b in the tree structure, the tree structure can be further expanded to obtain at least one candidate hint set.

[0239] In one or more embodiments, the process of expanding the tree structure based on the root node 20a and the first child node 20b may include, but is not limited to: the computer device can obtain a first exploration contribution parameter of the root node 20a and a second exploration contribution parameter of the first child node 20b; if the second exploration contribution parameter is greater than the first exploration contribution parameter, the coordinating intelligent object is invoked to modify the first candidate hint set hints2 in the first child node to obtain a second candidate hint set, which can be denoted as hints3, and the second candidate hint set hints3 is constructed as the second child node 20d. If the number of nodes in the tree structure meets the exploration stopping condition, the node expansion of the tree structure is stopped, and at least one candidate hint set is obtained, wherein the at least one candidate hint set includes the second candidate hint set hints3.

[0240] The first exploration contribution parameter represents the potential for optimization of the query optimization hint set hints1 in the root node 20a, and can be understood as the weight value of the root node 20a. The second exploration contribution parameter represents the potential for optimization of the first candidate hint set hints2 in the first child node 20b. It is understood that each node in the tree structure can calculate its corresponding exploration contribution parameter, and the exploration contribution parameter of each node can change as the number of nodes in the tree structure changes. A larger exploration contribution parameter indicates a greater potential for optimization of the query optimization hint set in that node; a smaller exploration contribution parameter indicates a lower potential for optimization of the query optimization hint set in that node.

[0241] The computer device can select the node with the largest exploration contribution parameter from all nodes in the tree structure to continue node expansion until the number of nodes in the tree structure meets the exploration stopping condition. The set of query optimization hints within all nodes in the tree structure at this point is taken as at least one candidate hint set. The exploration stopping condition may refer to the number of nodes in the tree structure reaching a pre-set maximum number of nodes, or it can be understood as a pre-set maximum number of explorations (the maximum number of times node diffusion is performed), etc. This application does not limit it in this way.

[0242] For example, the Upper Confidence Bounds Applied to Trees (UCT) algorithm can be used to select the node with the largest exploration contribution parameter from the tree structure for node diffusion, creating new nodes. In this case, the exploration contribution parameter can be the upper confidence interval of each node. The node selection process will not be described in detail here.

[0243] The second child node 20d can be a child node of the first child node 20b. The "thinking" in the second child node 20d can be understood as the process by which the connection order intelligent object performs in-depth analysis and verification of the first candidate hint set, hints2. The "action" in the second child node 20d can be understood as the operation of optimizing the connection order of the first candidate hint set, hints2, such as optimizing connection order 1. The "observation" in the second child node 20d can be understood as the connection order intelligent object obtaining the second candidate hint set, hints3.

[0244] The following describes the calculation process of the exploration contribution parameter of a single node in a tree structure, using the second exploration contribution parameter as an example. For instance, a computer device can execute a structured query statement based on the first candidate hint set hints2 in the first child node 20b, obtain the query execution time of the structured query statement, normalize the query execution time, and obtain the performance parameter of the first child node 20b. Based on the exploration path of the first child node 20b in the tree structure, the summary information of the first child node 20b is obtained, and semantic analysis is performed on the summary information to obtain the summary confidence of the first child node 20b. Based on the performance parameter, the summary confidence, and the number of visits to the first child node 20b, the second exploration contribution parameter of the first child node 20b is determined.

[0245] The query execution time can refer to the execution time obtained by simulating the execution of a structured query statement in the database according to the first candidate hint set hints2. Normalizing the query execution time to between 0 and 1 yields the performance parameters. The summary information can refer to the analysis results obtained by reflecting on the set of query optimization hints from the bottom up, based on an exploration path (e.g., root node 20a -> first child node 20b) of the first child node 20b in the tree structure. The summary confidence score can be a value between 0 and 1, obtained by scoring the semantics of the summary information. A higher summary confidence score indicates a greater likelihood of optimization of the query optimization hint set in that node; a lower score indicates a lower likelihood of optimization. The second exploration contribution parameter of the first child node 20b can be calculated based on the performance parameters, summary confidence score, and number of visits. A higher number of visits results in a lower second exploration contribution parameter, and vice versa. Similarly, a higher performance parameter results in a lower second exploration contribution parameter, and vice versa. The higher the abstract confidence, the larger the contribution parameter of the second exploration; the lower the abstract confidence, the smaller the contribution parameter of the second exploration.

[0246] like Figure 7 As shown, the above process can be iterated continuously. For example, the first child node 20b can be further expanded to obtain a third child node 20e. This third child node 20e can contain a third candidate hint set, hints4. The third candidate hint set, hints4, is a new query-optimized hint set obtained by optimizing the join order of the first candidate hint set, hints2, such as optimizing join order 2. Similarly, the first child node 20b can be further expanded to obtain a fourth child node 20f. This fourth child node 20f can contain a fourth candidate hint set, hints5. The fourth candidate hint set, hints5, is a new query-optimized hint set obtained by optimizing the index of the first candidate hint set, hints2.

[0247] like Figure 7 As shown, the second child node 20d can be further expanded to obtain the fifth child node 20g. This fifth child node 20g can contain the fifth candidate hint set hints6. The fifth candidate hint set hints6 is a new query optimization hint set obtained by optimizing the operator type of the second candidate hint set hints3. The root node 20a can be further expanded to obtain the sixth child node 20c. The action of the sixth child node 20c is stop, and the observation is empty. At this point, the node expansion of the tree structure can be stopped, resulting in the final tree structure 20h.

[0248] From all nodes in the tree structure 20h, the node with the shortest query execution time can be selected as the set of query optimization hints (which can be called the recommended hint set). After obtaining the recommended hint set, the database optimization component can be invoked to generate a query execution strategy for the structured query statement. Following the query execution strategy, the structured query statement is executed in the database to obtain the data query results.

[0249] Understandably, a tree search strategy can be used to store the changes in the set of query optimization hints during each query optimization process. That is, a node in the tree structure can contain a set of hints. Each tree search process can include selection, expansion, simulated execution, and feedback. A tree search process can be called a node expansion process or an exploration process. Understandably, node selection in the tree structure can be based on the exploration contribution parameters of each node, as well as strategies (such as UCT) to select the nodes most worthy of in-depth exploration. Node expansion in the tree structure can refer to the coordinating intelligent object selecting multiple proprietary intelligent objects to recommend hint sets based on the analysis results of the current environment. Node evaluation in the tree structure can obtain the actual query execution time and execution information; feedback in the tree structure can reflect on the effect of each node based on a single path to form summary information and decide whether the current node (such as a leaf node) needs to continue exploration. Summary confidence and performance improvement can serve as references for the exploration contribution parameters of each node.

[0250] In this embodiment, a pre-trained language model is trained using supervised fine-tuning and direct preference optimization to obtain a second query optimization model. This model then leverages rich domain knowledge (query optimization knowledge base), a multi-intelligent object architecture, and a tree search strategy to perform in-depth analysis and verification of structured query statements. This intervention optimizes the query execution strategy, resulting in shorter query execution time. This improves the versatility and effectiveness of the database optimization components, enhances their applicability under different load conditions and data characteristics, and ultimately improves the efficiency and accuracy of database query optimization.

[0251] It is understood that in the specific implementation of this application, the basic user information stored in the database is involved. When the above embodiments of this application are applied to specific products or technologies, permission or consent from relevant institutions or departments, or the user himself or himself or himself or her or his or her ...

[0252] Please see Figure 8 , Figure 8 This is a schematic diagram of the structure of a database query optimization device provided in an embodiment of this application. Figure 1 .like Figure 8 As shown, the database query optimization device 1 may include: a prompt generation module 101, a tag construction module 102, a first input construction module 103, a first training module 104, and a second training module 105;

[0253] The suggestion generation module 101 is used to obtain the sample query statement and generate at least two first optimization suggestion sets for the sample query statement;

[0254] The label building module 102 is used to build sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets based on the execution evaluation parameters of each first optimization suggestion set;

[0255] The first input construction module 103 is used to obtain the first database auxiliary information of the sample query statement and construct the first task prompt information based on the sample query statement and the first database auxiliary information.

[0256] The first training module 104 is used to correct the network parameters of the pre-trained language model based on the first task prompt information and sample optimization labels to obtain the first query optimization model.

[0257] The second training module 105 is used to optimize the parameters of the first query optimization model based on the first task prompt information and the comparison optimization label pair to obtain the second query optimization model. The second query optimization model is used to generate a set of query optimization prompts for structured query statements.

[0258] In one or more embodiments, when the suggestion generation module 101 obtains a sample query statement and generates at least two first optimized suggestion sets for the sample query statement, it performs the following steps:

[0259] Get the sample query statement and get M initial optimization hints for the sample query statement, where M is an integer greater than 1;

[0260] Based on the suggestion types of the M initial optimization suggestions, the M initial optimization suggestions are combined to obtain a set of N second optimization suggestions, where N is an integer greater than 1;

[0261] Based on the execution evaluation parameters of each second optimization suggestion set, at least two first optimization suggestion sets are determined from the N second optimization suggestion sets.

[0262] In one or more embodiments, when the suggestion generation module 101 determines at least two first optimization suggestion sets from N second optimization suggestion sets based on the execution evaluation parameters of each second optimization suggestion set, it performs the following steps:

[0263] Based on each second optimization suggestion set, the database optimization component is invoked to generate sample query statements and sample execution strategies for each second optimization suggestion set;

[0264] According to the sample execution strategy corresponding to each second optimization suggestion set, execute sample query statements in the database to obtain the execution evaluation parameters for each second optimization suggestion set;

[0265] In the N sets of second optimization suggestions, delete the sets of second optimization suggestions whose execution evaluation parameters are greater than the threshold of the first parameter, so as to obtain at least two sets of first optimization suggestions.

[0266] In one or more embodiments, when the label construction module 102 constructs sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets based on the execution evaluation parameters of each first optimization suggestion set, it performs the following steps:

[0267] Based on the sample execution strategy corresponding to each first optimization suggestion set, at least two first optimization suggestion sets are clustered to obtain at least one cluster. Based on at least one cluster, the sample optimization label of the sample query statement is determined.

[0268] Among at least two first optimization suggestion sets, first optimization suggestion set a and first optimization suggestion set b are determined, wherein the execution evaluation parameter of first optimization suggestion set a is greater than the execution evaluation parameter of first optimization suggestion set b;

[0269] If the difference between the execution evaluation parameters of the first optimization suggestion set a and the execution evaluation parameters of the first optimization suggestion set b is greater than the second parameter threshold, then the first optimization suggestion set a and its inference description are determined as positive optimization labels.

[0270] The first optimization hint set b and its inference description are identified as negative optimization labels, and the positive and negative optimization labels are identified as the comparison optimization label pair of the sample query statement.

[0271] In one or more embodiments, when the tag building module 102 determines the sample optimization tags for a sample query statement based on at least one cluster, it performs the following steps:

[0272] Using a pre-trained language model, semantic analysis is performed on the sample query statements and the first set of optimization hints within each cluster to generate a third set of optimization hints for each cluster, as well as an inference description of the third set of optimization hints.

[0273] Obtain the execution evaluation parameters of the third optimization suggestion set. In the third optimization suggestion set corresponding to at least one cluster, determine the third optimization suggestion set corresponding to the smallest execution evaluation parameter as the label suggestion set.

[0274] The set of tag hints and the inference description of the set of tag hints are used to determine the sample optimized tags for the sample query statement.

[0275] In one or more embodiments, when the first input construction module 103 obtains first database auxiliary information of the sample query statement and constructs first task prompt information based on the sample query statement and the first database auxiliary information, it performs the following steps:

[0276] Construct an abstract syntax tree of the sample query statement, obtain the nested representation text of the abstract syntax tree, and determine the sample query statement, the nested representation text, and the default execution strategy of the sample query statement in the database as query semantic information.

[0277] Obtain database statistics and database metadata, obtain database load detection information, and determine the statistics, database metadata, and load detection information as the first database auxiliary information;

[0278] Obtain the task instruction text and task example text, and determine the query semantic information, first database auxiliary information, task instruction text, and task example text as the first task prompt information.

[0279] In one or more embodiments, when the first training module 104 corrects the network parameters of the pre-trained language model based on the first task prompt information and sample optimization labels to obtain the first query optimization model, it performs the following steps:

[0280] The pre-trained language model outputs the first predicted conditional probability of the optimized label for the sample based on the prompt information of the first task.

[0281] The negative log-likelihood loss for the first prediction conditional probability is determined. Based on the negative log-likelihood loss, the network parameters of the pre-trained language model are corrected to obtain the first query optimization model.

[0282] In one or more embodiments, the comparison optimization label pair includes a positive optimization label and a negative optimization label;

[0283] When the second training module 105 optimizes the parameters of the first query optimization model based on the first task prompt information and the comparison and optimization label pair, and obtains the second query optimization model, it performs the following steps:

[0284] The first query optimization model is backed up to obtain a reference model, and the network parameters of the reference model are fixed.

[0285] Based on the reference model and the prompt information from the first task, the second predicted conditional probability for the positive optimization label and the third predicted conditional probability for the negative optimization label are output.

[0286] Based on the first task prompt information, the first query optimization model outputs the fourth predicted conditional probability for the positive optimization label and the fifth predicted conditional probability for the negative optimization label.

[0287] The contrast preference difference between the positive and negative optimization labels is determined based on the first logarithmic ratio between the fourth and second prediction condition probabilities, and the second logarithmic ratio between the fifth and third prediction condition probabilities.

[0288] By performing a probabilistic mapping on the contrast preference difference, the preference optimization loss of the first query optimization model for positive and negative optimization labels is obtained;

[0289] Based on the preference optimization loss, the network parameters of the first query optimization model are modified to obtain the second query optimization model.

[0290] According to one embodiment of this application, the foregoing Figure 2 The steps involved in the database query optimization method shown can be derived from... Figure 8 The database query optimization device 1 shown is executed by each module. For example, Figure 2 The step S101 shown can be performed by Figure 8The prompt generation module 101 shown is used to execute this. Figure 2 The step S102 shown can be performed by Figure 8 The label building module 102 shown is used to execute this. Figure 2 The step S103 shown can be performed by Figure 8 The first input building module 103 shown is used for execution. Figure 2 Step S104 shown can be derived from Figure 8 The first training module 104 shown is used to execute this. Figure 2 The step S105 shown can be performed by Figure 8 The second training module 105 shown is used to perform the above.

[0291] According to one embodiment of this application, Figure 8 The modules in the database query optimization device 1 shown can be individually or entirely combined into one or more modules, or some of the modules can be further divided into at least two functionally smaller units to achieve the same operation without affecting the technical effects of the embodiments of this application. The above modules are based on logical function division. In practical applications, the function of one module can also be implemented by at least two units, or the function of at least two modules can be implemented by one module. In other embodiments of this application, the database query optimization device 1 may also include other modules or units. In practical applications, these functions can also be implemented with the assistance of other modules, and can be implemented collaboratively by at least two modules.

[0292] In this embodiment, a domain-specific large model (second query optimization model) for database optimization components can be constructed. In the database query optimization task, the pre-trained language model (large language model) is subjected to supervised fine-tuning and direct preference optimization, so that the trained second query optimization model can capture deep contextual information in complex SQL queries and improve the accuracy of the query optimization suggestion set.

[0293] Please see Figure 9 , Figure 9 This is a schematic diagram of the structure of a database query optimization device provided in an embodiment of this application. Figure 2 .like Figure 9 As shown, the database query optimization device 2 may include: a second input construction module 201, a model output module 202, a prompt optimization module 203, and a prompt recommendation module 204;

[0294] The second input construction module 201 is used to obtain a structured query statement and the second database auxiliary information of the structured query statement, and to construct a second task prompt information based on the structured query statement and the second database auxiliary information.

[0295] The model output module 202 is used to output a set of query optimization suggestions for structured query statements based on the second task prompt information through the second query optimization model.

[0296] The suggestion optimization module 203 is used to update the query optimization suggestion set based on the query optimization knowledge base and the query optimization tool library to obtain at least one candidate suggestion set;

[0297] The suggestion recommendation module 204 is used to determine a set of recommended suggestions for a structured query statement from at least one set of candidate suggestions.

[0298] In one or more embodiments, when the suggestion optimization module 203 updates the query optimization suggestion set based on the query optimization knowledge base and the query optimization tool library to obtain at least one candidate suggestion set, it performs the following steps:

[0299] The root node of the tree structure is constructed based on the query optimization suggestion set. The query optimization suggestion set is analyzed and verified by coordinating the intelligent object to call the query optimization tool library and query optimization knowledge base, and feedback evaluation information of the query optimization suggestion set is obtained.

[0300] Based on the feedback evaluation information, determine the first suggestion to be modified in the query optimization suggestion set, and determine the first proprietary intelligent object that matches the first suggestion to be modified in the proprietary intelligent object set;

[0301] The first proprietary smart object is invoked to modify the query optimization suggestion set in the root node, resulting in the first candidate suggestion set. The first candidate suggestion set is then constructed as the first child node of the root node.

[0302] Based on the root node and the first child node, the tree structure is expanded to obtain at least one candidate hint set, and the at least one candidate hint set includes the first candidate hint set.

[0303] In one or more embodiments, when the suggestion optimization module 203 expands the tree structure based on the root node and the first child node to obtain at least one candidate suggestion set, it performs the following steps:

[0304] Get the first exploration contribution parameter of the root node, and get the second exploration contribution parameter of the first child node;

[0305] If the second exploration contribution parameter is greater than the first exploration contribution parameter, then the coordination smart object is called to modify the first candidate hint set in the first child node to obtain the second candidate hint set, and the second candidate hint set is constructed as the second child node;

[0306] If the number of nodes in the tree structure meets the exploration stopping condition, then the node expansion of the tree structure is stopped, and at least one candidate hint set is obtained, which includes a second candidate hint set.

[0307] In one or more embodiments, when the optimization module 203 prompts the first child node to obtain the second exploration contribution parameter, it performs the following steps:

[0308] Based on the first candidate suggestion set in the first child node, execute the structured query statement to obtain the query execution time of the structured query statement, normalize the query execution time to obtain the performance parameters of the first child node;

[0309] Based on the exploration path of the first child node in the tree structure, the summary information of the first child node is obtained, and semantic analysis is performed on the summary information to obtain the summary confidence of the first child node.

[0310] The second exploration contribution parameter of the first child node is determined based on performance parameters, summary confidence, and the number of visits to the first child node.

[0311] In one or more embodiments, the database query optimization device 2 further includes: a knowledge base construction module 205 and a tool library construction module 206;

[0312] The knowledge base construction module 205 is used to acquire database corpus data, perform semantic segmentation on the database corpus data, and obtain a set of corpus data blocks;

[0313] The knowledge base construction module 205 is also used to perform vectorized encoding on each corpus data block in the corpus data block set to obtain the data encoding vector of each corpus data block, and to construct a query optimization knowledge base from the data encoding vector of each corpus data block.

[0314] The tool library construction module 206 is used to build at least one auxiliary tool based on the database query optimization task, obtain the interface description information of each auxiliary tool, and construct the at least one auxiliary tool and the interface description information of each auxiliary tool into a query optimization tool library.

[0315] In one or more embodiments, the database query optimization device 2 further includes: an execution strategy generation module 207 and a query result acquisition module 208;

[0316] The execution strategy generation module 207 is used to generate a query execution strategy for a structured query statement by calling the database optimization component based on the set of recommended suggestions.

[0317] The query result retrieval module 208 is used to execute structured query statements in the database according to the query execution strategy and obtain the data query results of the structured query statements.

[0318] According to one embodiment of this application, the foregoing Figure 6 The steps involved in the database query optimization method shown can be derived from... Figure 9 The database query optimization device 2 shown is executed by various modules. For example, Figure 6 The step S301 shown can be performed by Figure 9 The second input building module 201 shown is used for execution. Figure 6 The step S302 shown can be performed by Figure 9 The model output module 202 shown is used for execution. Figure 6 The step S303 shown can be performed by Figure 9 The prompt optimization module 203 is executed as shown. Figure 6 The step S304 shown can be performed by Figure 9 The prompts shown recommend module 204 to be executed, etc.

[0319] According to one embodiment of this application, Figure 9 The modules in the database query optimization device 2 shown can be individually or all merged into one or more modules, or some of the modules can be further divided into at least two functionally smaller units to achieve the same operation without affecting the technical effects of the embodiments of this application. The above modules are based on logical function division. In practical applications, the function of one module can also be implemented by at least two units, or the function of at least two modules can be implemented by one module. In other embodiments of this application, the database query optimization device 2 may also include other modules or units. In practical applications, these functions can also be implemented with the assistance of other modules, and can be implemented collaboratively by at least two modules.

[0320] In this embodiment, a pre-trained language model is trained using supervised fine-tuning and direct preference optimization to obtain a second query optimization model. This model then leverages rich domain knowledge (query optimization knowledge base), a multi-intelligent object architecture, and a tree search strategy to perform in-depth analysis and verification of structured query statements. This intervention optimizes the query execution strategy, resulting in shorter query execution time. This improves the versatility and effectiveness of the database optimization components, enhances their applicability under different load conditions and data characteristics, and ultimately improves the efficiency and accuracy of database query optimization.

[0321] Please see Figure 10 , Figure 10 This is a schematic diagram of the structure of a computer device provided in an embodiment of this application. Figure 10 As shown, the computer device 1000 can be a terminal device, for example, the one described above. Figure 1The terminal device 10a in the corresponding embodiment; or it can be a server, for example, Figure 1 The server 10d shown is illustrated below. For ease of understanding, the computer device is described below as a terminal device; the computer device 1000 may include: a processor 1001, a network interface 1004, and a memory 1005. Furthermore, the computer device 1000 may also include: a user interface 1003, and at least one communication bus 1002. The communication bus 1002 is used to implement communication between these components. The user interface 1003 may also include a standard wired interface and a wireless interface. The network interface 1004 may optionally include a standard wired interface and a wireless interface (such as a Wi-Fi interface). The memory 1005 may be high-speed RAM or non-volatile memory, such as at least one disk storage device. The memory 1005 may also optionally be at least one storage device located remotely from the aforementioned processor 1001. Figure 10 As shown, the memory 1005, which is a computer-readable storage medium, may include an operating system, a network communication module, a user interface module, and a device control application.

[0322] The network interface 1004 in the computer device 1000 can also provide network communication functions, and the optional user interface 1003 can also include a display screen and a keyboard. Figure 10 In the computer device 1000 shown, the network interface 1004 provides network communication functionality; the user interface 1003 is mainly used to provide an input interface for the user; and the processor 1001 can be used to call the device control application stored in the memory 1005 to achieve:

[0323] Obtain the sample query statement and generate at least two sets of first optimization hints for the sample query statement;

[0324] Based on the execution evaluation parameters of each first optimization suggestion set, construct sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets;

[0325] Obtain the first database auxiliary information of the sample query statement, and construct the first task prompt information based on the sample query statement and the first database auxiliary information;

[0326] Based on the prompts from the first task and the optimized labels for the samples, the network parameters of the pre-trained language model are corrected to obtain the first query optimization model.

[0327] Based on the first task prompt information and the comparison optimization label pair, the parameters of the first query optimization model are optimized to obtain the second query optimization model. The second query optimization model is used to generate a set of query optimization prompts for structured query statements.

[0328] Alternatively, processor 1001 can be used to call the device control application stored in memory 1005 to achieve:

[0329] Obtain the structured query statement and the second database auxiliary information of the structured query statement; construct the second task prompt information based on the structured query statement and the second database auxiliary information.

[0330] Based on the second task prompt information, the second query optimization model outputs a set of query optimization prompts for structured query statements.

[0331] Based on the query optimization knowledge base and query optimization toolkit, the query optimization suggestion set is updated to obtain at least one candidate suggestion set;

[0332] From at least one set of candidate suggestions, determine a set of recommended suggestions for the structured query.

[0333] It should be understood that the computer device 1000 described in the embodiments of this application can execute the foregoing text. Figure 2 , Figure 4 , Figure 6 The description of the database query optimization method in any of the embodiments can also be executed as described above. Figure 8 In the corresponding embodiment, the database query optimization device 1, or the execution of the preceding text Figure 9 The description of the database query optimization device 2 in the corresponding embodiments will not be repeated here. Furthermore, the beneficial effects of using the same method will also not be repeated.

[0334] Furthermore, it should be noted that this application embodiment also provides a computer-readable storage medium, which stores a computer program executed by the aforementioned database query optimization device 1 or database query optimization device 2. The computer program includes computer instructions, and when the processor executes the computer instructions, it can execute the aforementioned... Figure 2 , Figure 4 , Figure 6The description of the database query optimization method in any of the embodiments is already provided, and therefore will not be repeated here. Furthermore, the beneficial effects of using the same method will also not be repeated. The storage medium can be a magnetic disk, optical disk, read-only memory (ROM), or random access memory (RAM), etc. For technical details not disclosed in the embodiments of the computer-readable storage medium involved in this application, please refer to the description of the method embodiments of this application. As an example, program instructions can be deployed and executed on a single computer device, or on multiple computer devices located in one location, or on multiple computer devices distributed across multiple locations and interconnected via a communication network. Multiple computer devices distributed across multiple locations and interconnected via a communication network can constitute a blockchain system.

[0335] Furthermore, it should be noted that this application also provides a computer program product, which may include a computer program that can be stored in a computer-readable storage medium. The processor of a computer device reads the computer program from the computer-readable storage medium, and the processor can execute the computer program, causing the computer device to perform the aforementioned... Figure 2 , Figure 4 , Figure 6 The description of the database query optimization method in any of the embodiments is already provided, and therefore will not be repeated here. Furthermore, the beneficial effects of using the same method will also not be repeated. For technical details not disclosed in the computer program products or computer program embodiments related to this application, please refer to the description of the method embodiments of this application.

[0336] It is understood that in the description of the embodiments of this application, "when," "if," and "if" all refer to the device making corresponding processing under certain objective circumstances, and are not limited to a time, nor do they require the device to have a judgment action when it is implemented, nor do they mean that there are other limitations.

[0337] The terms "first," "second," etc., in the specification, claims, and drawings of this application are used to distinguish different media content, not to describe a specific order. Furthermore, the term "comprising," and any variations thereof, are intended to cover non-exclusive inclusion. For example, a process, method, apparatus, product, or device that includes a series of steps or units is not limited to the listed steps or modules, but may optionally include steps or modules not listed, or may optionally include other step units inherent to these processes, methods, apparatuses, products, or devices.

[0338] Those skilled in the art will recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementations should not be considered beyond the scope of this application.

[0339] The methods and related apparatus provided in this application are described with reference to the method flowcharts and / or structural diagrams provided in this application. Specifically, each block of the method flowchart and / or structural diagram, as well as combinations of blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data display device to create a machine, such that the instructions executable by the processor of the computer or other programmable data display device generate instructions for implementing the process. Figure 1 A schematic diagram of one or more processes and / or structures. Figure 1 The computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data display device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 A schematic diagram of one or more processes and / or structures. Figure 1 The functions specified in one or more boxes. These computer program instructions may also be loaded onto a computer or other programmable display device to cause a series of operational steps to be performed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable device for implementing the process. Figure 1 A process or multiple processes and / or structures illustrate the steps of the functions specified in one or more boxes.

[0340] In this application embodiment, the terms "module" or "unit" refer to a computer program or part of a computer program that has a predetermined function and works with other related parts to achieve a predetermined goal, and can be implemented wholly or partially using software, hardware (such as processing circuitry or memory), or a combination thereof. Similarly, a processor (or multiple processors or memory) can be used to implement one or more modules or units. Furthermore, each module or unit can be part of an overall module or unit that includes the functionality of that module or unit.

[0341] The above-disclosed embodiments are merely preferred embodiments of this application and should not be construed as limiting the scope of this application. Therefore, any equivalent variations made in accordance with the claims of this application shall still fall within the scope of this application.

Claims

1. A database query optimization method, characterized in that, include: Obtain a sample query statement and generate at least two first optimization suggestion sets for the sample query statement; Based on the execution evaluation parameters of each of the first optimization suggestion sets, construct sample optimization labels and comparison optimization label pairs in at least two of the first optimization suggestion sets; Obtain the first database auxiliary information of the sample query statement, and construct the first task prompt information based on the sample query statement and the first database auxiliary information; Based on the first task prompt information and the sample optimization label, the network parameters of the pre-trained language model are corrected to obtain the first query optimization model; Based on the first task prompt information and the comparison optimization label pair, the parameters of the first query optimization model are optimized to obtain a second query optimization model. The second query optimization model is used to generate a set of query optimization prompts for structured query statements.

2. The method according to claim 1, characterized in that, The process of obtaining the sample query statement and generating at least two first optimization suggestion sets for the sample query statement includes: Obtain the sample query statement and obtain M initial optimization suggestions for the sample query statement, where M is an integer greater than 1; Based on the suggestion types of the M initial optimization suggestions, the M initial optimization suggestions are combined to obtain a set of N second optimization suggestions, where N is an integer greater than 1; Based on the execution evaluation parameters of each of the second optimization suggestion sets, at least two of the first optimization suggestion sets are determined from the N second optimization suggestion sets.

3. The method according to claim 2, characterized in that, The step of determining at least two first optimization suggestion sets from N second optimization suggestion sets based on the execution evaluation parameters of each second optimization suggestion set includes: Based on each of the second optimization suggestion sets, the database optimization component is invoked to generate a sample execution strategy for the sample query statement under each of the second optimization suggestion sets; According to the sample execution strategy corresponding to each of the second optimization suggestion sets, the sample query statement is executed in the database to obtain the execution evaluation parameters of each of the second optimization suggestion sets; In the N sets of second optimization suggestions, delete the set of second optimization suggestions where the execution evaluation parameter is greater than the first parameter threshold, to obtain at least two sets of first optimization suggestions.

4. The method according to any one of claims 1 to 3, characterized in that, The step of constructing sample optimization label and comparison optimization label pairs in at least two first optimization suggestion sets based on the execution evaluation parameters of each first optimization suggestion set includes: Based on the sample execution strategy corresponding to each of the first optimization suggestion sets, at least two of the first optimization suggestion sets are clustered to obtain at least one cluster, and the sample optimization label of the sample query statement is determined based on at least one cluster. Among at least two first optimization suggestion sets, a first optimization suggestion set a and a first optimization suggestion set b are determined, wherein the execution evaluation parameter of the first optimization suggestion set a is greater than the execution evaluation parameter of the first optimization suggestion set b; If the difference between the execution evaluation parameters of the first optimization suggestion set a and the execution evaluation parameters of the first optimization suggestion set b is greater than the second parameter threshold, then the first optimization suggestion set a and the reasoning description of the first optimization suggestion set a are determined as positive optimization labels. The first optimization hint set b and its inference description are determined as negative optimization labels, and the positive optimization label and the negative optimization label are determined as the comparison optimization label pair of the sample query statement.

5. The method according to claim 4, characterized in that, The step of determining the sample optimization label of the sample query statement based on at least one of the clusters includes: Using the pre-trained language model, semantic analysis is performed on the sample query statement and the first optimization hint set within each cluster to generate a third optimization hint set corresponding to each cluster, as well as an inference description of the third optimization hint set; Obtain the execution evaluation parameters of the third optimization suggestion set, and in the third optimization suggestion set corresponding to at least one cluster, determine the third optimization suggestion set corresponding to the smallest execution evaluation parameter as the label suggestion set; The set of tag hints and the inference description of the set of tag hints are determined as the sample optimization tags for the sample query statement.

6. The method according to claim 1, characterized in that, The step of obtaining the first database auxiliary information of the sample query statement and constructing the first task prompt information based on the sample query statement and the first database auxiliary information includes: Construct an abstract syntax tree of the sample query statement, obtain the nested representation text of the abstract syntax tree, and determine the sample query statement, the nested representation text, and the default execution strategy of the sample query statement in the database as query semantic information; Obtain statistical information and database metadata of the database, obtain load detection information of the database, and determine the statistical information, the database metadata, and the load detection information as the first database auxiliary information; Obtain the task instruction text and task example text, and determine the query semantic information, the first database auxiliary information, the task instruction text, and the task example text as the first task prompt information.

7. The method according to claim 1, characterized in that, The step of correcting the network parameters of the pre-trained language model based on the first task prompt information and the sample optimization label to obtain the first query optimization model includes: The pre-trained language model outputs a first predicted conditional probability for the optimized label of the sample based on the first task prompt information. The negative log-likelihood loss of the first predicted conditional probability is determined, and the network parameters of the pre-trained language model are corrected based on the negative log-likelihood loss to obtain the first query optimization model.

8. The method according to claim 1, characterized in that, The comparison optimization label pair includes positive optimization labels and negative optimization labels; The step of optimizing the parameters of the first query optimization model based on the first task prompt information and the comparison optimization tag pair to obtain a second query optimization model includes: The first query optimization model is backed up to obtain a reference model, and the network parameters of the reference model are fixed. Based on the reference model, a second predicted conditional probability for the positive optimization label and a third predicted conditional probability for the negative optimization label are output according to the first task prompt information. Based on the first query optimization model, the fourth predicted conditional probability for the positive optimization label and the fifth predicted conditional probability for the negative optimization label are output according to the first task prompt information. The contrast preference difference between the positive optimization label and the negative optimization label is determined based on the first logarithmic ratio between the fourth and second predicted conditional probabilities, and the second logarithmic ratio between the fifth and third predicted conditional probabilities. By performing a probabilistic mapping on the contrast preference difference, the preference optimization loss of the first query optimization model for the positive optimization label and the negative optimization label is obtained; Based on the preference optimization loss, the network parameters of the first query optimization model are modified to obtain the second query optimization model.

9. A database query optimization method, characterized in that, include: Obtain a structured query statement and the second database auxiliary information of the structured query statement; construct a second task prompt message based on the structured query statement and the second database auxiliary information. Using the second query optimization model, based on the second task prompt information, a set of query optimization prompts for the structured query statement is output; The query optimization suggestion set is updated based on the query optimization knowledge base and query optimization tool base to obtain at least one candidate suggestion set; From at least one of the candidate suggestion sets, a recommended suggestion set for the structured query statement is determined.

10. The method according to claim 9, characterized in that, The query optimization suggestion set is updated based on the query optimization knowledge base and query optimization tool library to obtain at least one candidate suggestion set, including: The root node of the tree structure is constructed based on the query optimization suggestion set. The query optimization tool library and the query optimization knowledge base are called by the coordinated intelligent object to analyze and verify the query optimization suggestion set, and obtain the feedback evaluation information of the query optimization suggestion set. Based on the feedback evaluation information, a first suggestion to be modified is determined in the query optimization suggestion set, and a first proprietary intelligent object matching the first suggestion to be modified is determined in the proprietary intelligent object set; The first proprietary intelligent object is invoked to modify the query optimization suggestion set in the root node to obtain a first candidate suggestion set, and the first candidate suggestion set is constructed as the first child node of the root node; Based on the root node and the first child node, the tree structure is expanded to obtain at least one candidate hint set, and at least one candidate hint set includes the first candidate hint set.

11. The method according to claim 10, characterized in that, The step of expanding the tree structure based on the root node and the first child node to obtain at least one candidate hint set includes: Obtain the first exploration contribution parameter of the root node, and obtain the second exploration contribution parameter of the first child node; If the second exploration contribution parameter is greater than the first exploration contribution parameter, then the coordination intelligent object is invoked to modify the first candidate suggestion set in the first child node to obtain the second candidate suggestion set, and the second candidate suggestion set is constructed as the second child node; If the number of nodes in the tree structure meets the exploration stop condition, then the node expansion of the tree structure is stopped, and at least one candidate hint set is obtained, wherein at least one candidate hint set includes the second candidate hint set.

12. The method according to claim 11, characterized in that, The step of obtaining the second exploration contribution parameter of the first child node includes: Based on the first candidate suggestion set in the first child node, the structured query statement is executed to obtain the query execution time of the structured query statement. The query execution time is then normalized to obtain the performance parameters of the first child node. Based on the exploration path of the first child node in the tree structure, the summary information of the first child node is obtained, and semantic analysis is performed on the summary information to obtain the summary confidence of the first child node. The second exploration contribution parameter of the first child node is determined based on the performance parameters, the summary confidence, and the number of visits to the first child node.

13. The method according to claim 9, characterized in that, The method further includes: Obtain database corpus data, perform semantic segmentation on the database corpus data, and obtain a set of corpus data blocks; Each corpus data block in the corpus data block set is vectorized and encoded to obtain a data encoding vector for each corpus data block. The data encoding vectors of each corpus data block are then used to construct the query optimization knowledge base. Based on the database query optimization task, at least one auxiliary tool is constructed, and the interface description information of each auxiliary tool is obtained. The at least one auxiliary tool and the interface description information of each auxiliary tool are constructed into the query optimization tool library.

14. The method according to any one of claims 9 to 13, characterized in that, The method further includes: Based on the set of recommended suggestions, the database optimization component is invoked to generate the query execution strategy for the structured query statement; According to the query execution strategy, the structured query statement is executed in the database to obtain the data query results of the structured query statement.

15. A database query optimization device, characterized in that, include: The suggestion generation module is used to obtain the sample query statement and generate at least two first optimized suggestion sets for the sample query statement; A label building module is used to build sample optimization labels and comparison optimization label pairs in at least two first optimization suggestion sets based on the execution evaluation parameters of each first optimization suggestion set; The first input construction module is used to obtain the first database auxiliary information of the sample query statement and construct the first task prompt information based on the sample query statement and the first database auxiliary information. The first training module is used to correct the network parameters of the pre-trained language model based on the first task prompt information and the sample optimization label to obtain the first query optimization model. The second training module is used to optimize the parameters of the first query optimization model based on the first task prompt information and the comparison optimization label pair to obtain a second query optimization model. The second query optimization model is used to generate a set of query optimization prompts for structured query statements.

16. A database query optimization device, characterized in that, include: The second input construction module is used to obtain a structured query statement and the second database auxiliary information of the structured query statement, and to construct a second task prompt information based on the structured query statement and the second database auxiliary information. The model output module is used to output a set of query optimization suggestions for the structured query statement based on the second task prompt information through the second query optimization model. The suggestion optimization module is used to update the query optimization suggestion set based on the query optimization knowledge base and the query optimization tool library to obtain at least one candidate suggestion set; The suggestion recommendation module is used to determine a set of recommended suggestions for the structured query statement from at least one set of candidate suggestions.

17. A computer device, characterized in that, Including memory and processor; The memory is connected to the processor, the memory is used to store computer programs, and the processor is used to invoke the computer programs so that the computer device performs the method according to any one of claims 1 to 8, or performs the method according to any one of claims 9 to 14.

18. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program adapted to be loaded and executed by a processor to cause a computer device having the processor to perform the method of any one of claims 1 to 8, or the method of any one of claims 9 to 14.

19. A computer program product, characterized in that, The computer program product includes a computer program that, when executed by a processor, implements the method of any one of claims 1 to 8, or implements the method of any one of claims 9 to 14.

Images