Database query statement processing method and device, equipment, storage medium and program product

Abstract

The application provides a database query statement processing method and device, equipment, storage medium and program product; the method comprises the following steps: grouping a plurality of query templates of a database to obtain a plurality of query template groups, each query template group is associated with a machine learning model; from the query statement to be executed, the first query template of the query statement is extracted, and the first query template group is determined from the plurality of query template groups; a plurality of execution plans associated with the first query template are acquired; based on the query statement and any two execution plans in the plurality of execution plans, the machine learning model associated with the first query template group is called for prediction to obtain a prediction result, and the prediction result represents the comparison result of the performance of any two execution plans; based on the prediction result of any two execution plans in the plurality of execution plans, the target execution plan is determined from the plurality of execution plans. Through the application, the selection accuracy of the execution plan and the performance of the database query can be improved.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, apparatus, device, storage medium, and program product for processing database query statements. Background Technology

[0002] In database query optimization, machine learning solutions are often used to improve the accuracy of query execution plan selection. In related technologies, each query template is modeled individually to adapt to different query characteristics. However, as business grows, the number of query templates surges, and the corresponding number of models explodes. This not only wastes equipment resources but also leads to fragmented training data due to the excessive number of models, resulting in decreased model accuracy. Consequently, the accuracy of execution plan selection decreases, impacting database query performance. Summary of the Invention

[0003] This application provides a method, apparatus, electronic device, computer-readable storage medium, and computer program product for processing database query statements, which can improve the accuracy of execution plan selection and the performance of database queries.

[0004] The technical solution of this application embodiment is implemented as follows:

[0005] This application provides a method for processing database query statements, including:

[0006] Multiple query templates in the database are grouped to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models;

[0007] From the query statement to be executed, extract the first query template of the query statement, and determine the first query template group from the plurality of query template groups, wherein the first query template group includes the first query template;

[0008] Obtain multiple execution plans associated with the first query template;

[0009] Based on the query statement and any two execution plans from the plurality of execution plans, the machine learning model associated with the first query template group is invoked to make a prediction and obtain a prediction result, wherein the prediction result represents the performance comparison result of the arbitrary two execution plans;

[0010] Based on the prediction results of any two execution plans among the plurality of execution plans, a target execution plan for the query statement is determined from the plurality of execution plans.

[0011] This application embodiment also provides a method for processing database query statements, including:

[0012] Multiple query templates in the database are grouped to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models;

[0013] From the query statement to be executed, extract the first query template of the query statement, and determine the first query template group from the plurality of query template groups, wherein the first query template group includes the first query template;

[0014] Obtain multiple execution plans associated with the first query template;

[0015] The machine learning model associated with the first query template group is invoked to determine the distance between each execution plan and the query statement;

[0016] Based on each of the distances, a target execution plan for the query statement is determined from the plurality of execution plans.

[0017] This application embodiment also provides a database query statement processing apparatus, including:

[0018] The first grouping module is used to group multiple query templates in the database to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models;

[0019] The first extraction module is used to extract a first query template from the query statement to be executed, and to determine a first query template group from the plurality of query template groups, wherein the first query template group includes the first query template;

[0020] The first acquisition module is used to acquire multiple execution plans associated with the first query template;

[0021] The first prediction module is used to call the machine learning model associated with the first query template group to make a prediction based on the query statement and any two execution plans among the plurality of execution plans, and obtain a prediction result, wherein the prediction result represents the performance comparison result of the arbitrary two execution plans;

[0022] The first determining module is used to determine the target execution plan for the query statement from the plurality of execution plans based on the prediction results of any two execution plans among the plurality of execution plans.

[0023] This application embodiment also provides a database query statement processing apparatus, including:

[0024] The second grouping module is used to group multiple query templates in the database to obtain multiple query template groups. Each query template group includes at least two query templates, and each query template group is associated with a machine learning model. Different query template groups are associated with different machine learning models.

[0025] The second extraction module is used to extract the first query template of the query statement from the query statement to be executed, and to determine the first query template group from the plurality of query template groups, wherein the first query template group includes the first query template;

[0026] The second acquisition module is used to acquire multiple execution plans associated with the first query template;

[0027] The second prediction module is used to call the machine learning model associated with the first query template group to determine the distance between each execution plan and the query statement.

[0028] The second determining module is used to determine, based on each of the distances, a target execution plan for the query statement from the plurality of execution plans.

[0029] This application also provides an electronic device, including:

[0030] Memory is used to store executable instructions for a computer;

[0031] The processor, when executing computer-executable instructions stored in the memory, implements the database query statement processing method provided in the embodiments of this application.

[0032] This application also provides a computer-readable storage medium storing computer-executable instructions or computer programs, which, when executed by a processor, implement the database query statement processing method provided in this application.

[0033] This application also provides a computer program product, including computer-executable instructions or a computer program, which, when executed by a processor, implements the database query statement processing method provided in this application.

[0034] The embodiments of this application have the following beneficial effects:

[0035] Applying the above embodiments of this application, firstly, multiple query templates in the database are grouped to obtain multiple query template groups; for a query statement to be executed, a first query template of the query statement is extracted from the query statement, and a first query template group including the first query template is determined from the multiple query template groups, and multiple execution plans associated with the first query template are obtained. Then, based on the query statement and any two execution plans from the multiple execution plans, the machine learning model associated with the first query template group is invoked to make a prediction, obtaining a prediction result. Based on the prediction results of any two execution plans from the multiple execution plans, a target execution plan for the query statement is determined from the multiple execution plans. Here, each query template group includes at least two query templates, and each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models. Thus, 1) the number of machine learning models is reduced, avoiding waste of equipment resources; 2) due to the reduction in the number of machine learning models, the impact of training data dispersion is also reduced, improving the accuracy of model prediction, thereby improving the selection accuracy of the execution plan and the performance of database queries. Attached Figure Description

[0036] Figure 1 This is a schematic diagram of the architecture of the database query statement processing system provided in the embodiments of this application;

[0037] Figure 2 This is a schematic diagram of the structure of the electronic device provided in the embodiments of this application;

[0038] Figure 3 This is a flowchart illustrating the database query statement processing method provided in the embodiments of this application;

[0039] Figure 4 This is a grouping diagram of the query template provided in the embodiments of this application;

[0040] Figure 5 This is a flowchart illustrating the database query statement processing method provided in the embodiments of this application;

[0041] Figure 6 This is a flowchart illustrating the database query statement processing method provided in the embodiments of this application;

[0042] Figure 7 This is a schematic diagram illustrating the training of the machine learning model provided in an embodiment of this application;

[0043] Figure 8 This is a schematic diagram of the structure of the machine learning model provided in the embodiments of this application;

[0044] Figure 9This is a flowchart illustrating the database query statement processing method provided in the embodiments of this application;

[0045] Figure 10 This is a schematic diagram of the structure of the machine learning model provided in the embodiments of this application. Detailed Implementation

[0046] To make the objectives, technical solutions, and advantages of this application clearer, the application will be further described in detail below with reference to the accompanying drawings. The described embodiments should not be regarded as limitations on this application. All other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0047] In the following description, references are made to “some embodiments,” which describe a subset of all possible embodiments. However, it is understood that “some embodiments” may be the same subset or different subsets of all possible embodiments and may be combined with each other without conflict.

[0048] In the following description, the terms "first, second, third" are used merely to distinguish similar objects and do not represent a specific ordering of objects. It is understood that "first, second, third" may be interchanged in a specific order or sequence where permitted, so that the embodiments of this application described herein can be implemented in an order other than that illustrated or described herein.

[0049] 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 a larger module or unit that includes the functionality of the module or unit.

[0050] Unless otherwise defined, all technical and scientific terms used in the embodiments of this application have the same meaning as commonly understood by one of ordinary skill in the art. The terminology used in the embodiments of this application is for the purpose of describing the embodiments of this application only and is not intended to limit this application.

[0051] In the implementation of this application, the collection and processing of relevant data should strictly comply with the requirements of relevant laws and regulations, obtain the informed consent or separate consent of the personal information subject, and carry out subsequent data use and processing within the scope of laws and regulations and the authorization of the personal information subject.

[0052] Before providing a further detailed description of the embodiments of this application, the nouns and terms involved in the embodiments of this application will be explained, and the nouns and terms involved in the embodiments of this application shall be interpreted as follows.

[0053] 1) Client: An application running on an electronic device that provides various services, such as a client that supports querying a database based on query statements.

[0054] 2) In response to, used to indicate the conditions or states on which the operation performed depends. When the conditions or states on which it depends are met, one or more operations performed may be performed in real time or with a set delay. Unless otherwise specified, there is no restriction on the order in which the multiple operations are performed.

[0055] 3) Execution plan: A specific operational flow adopted by the database when executing a query statement. It is a set of specific operation instructions and strategies for the database to execute the query statement. The execution plan instructs the database to access data tables in a specific way and according to the data table join order. The specific way includes using specific indexes, adopting specific data table join methods (such as nested loop joins, hash joins), specific data filtering and sorting steps, etc. The execution plan directly affects the query execution efficiency. The data table join order is a key factor affecting the performance of the execution plan. In the embodiments of this application, each candidate plan of the query template can be represented by the data table join order.

[0056] 4) Online Analytical Processing (OLAP) systems are database systems that handle analytical queries. For example, they support multidimensional aggregation and ad-hoc queries on large-scale historical data. Typical scenarios include data warehousing and business intelligence. Analytical queries present two main challenges: the complexity of the analytical logic and the scale of data to be processed. Typically, these queries have relatively long response times.

[0057] This application provides a method, apparatus, electronic device, computer-readable storage medium, and computer program product for processing database query statements, which can improve the accuracy of execution plan selection and the performance of database queries. The embodiments of this application will now be described in detail based on the above description of the terms and concepts used in the embodiments.

[0058] The following describes the database query statement processing system provided in the embodiments of this application. See also... Figure 1 , Figure 1This is a schematic diagram of the architecture of a database query statement processing system provided in this application embodiment. To support an exemplary application, the database query statement processing system 100 includes: a server 200, a network 300, a terminal 400, and a database 600. The terminal 400 is connected to the server 200 via the network 300, which can be a wide area network (WAN), a local area network (LAN), or a combination of both, using wireless or wired links for data transmission.

[0059] Here, server 200 first groups the multiple query templates of database 600 to obtain multiple query template groups. Each query template group includes at least two query templates, and each query template group is associated with a machine learning model. Different query template groups are associated with different machine learning models.

[0060] When a query is required against database 600, terminal 400 (e.g., a client running a query statement against database 600) responds to the query instruction against database 600 by sending a query statement to be executed against database 600 to server 200; server 200 receives the query statement sent by terminal 400; extracts a first query template from the query statement, and determines a first query template group from multiple query template groups, wherein the first query template group includes the first query template; obtains multiple execution plans associated with the first query template; based on the query statement and any two execution plans from the multiple execution plans, calls the machine learning model associated with the first query template group to make a prediction, and obtains a prediction result, wherein the prediction result represents the performance comparison result of any two execution plans; based on the prediction results of any two execution plans from the multiple execution plans, determines the target execution plan for the query statement from the multiple execution plans; executes the query statement against database 600 based on the target execution plan, and obtains the query result; returns the query result to terminal 400; terminal 400 receives the query result returned by server 200; and displays the query result.

[0061] The database query processing method provided in this application embodiment is implemented by an electronic device. For example, it can be implemented by a terminal alone, by a server alone, or by a terminal and a server working together. The electronic device implementing the database query processing method provided in this application embodiment can be various types of terminals or servers. The server (e.g., server 200) can be an independent physical server, a server cluster or distributed system composed of multiple physical servers, or a cloud server providing 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 (CDNs), and big data and artificial intelligence platforms. The terminal (e.g., terminal 400) can be a laptop, tablet, desktop computer, smartphone, smart voice interaction device (e.g., smart speaker), smart home appliance (e.g., smart TV), smartwatch, vehicle terminal, wearable device, virtual reality (VR) device, aircraft, etc., but is not limited to these. The terminal and server can be connected directly or indirectly through wired or wireless communication, and this application embodiment does not impose any restrictions on this.

[0062] In some embodiments, the terminal or server can implement the database query statement processing method provided in this application embodiment by running various computer-executable instructions or computer programs. For example, computer-executable instructions can be microprogram-level commands, machine instructions, or software instructions. Computer programs can be native programs or software modules in an operating system; they can be native applications (APPs), i.e., programs that need to be installed in the operating system to run; or they can be applets that can be embedded in any APP, i.e., programs that only need to be downloaded to a browser environment to run. In summary, the aforementioned computer-executable instructions can be any form of instruction, and the aforementioned computer programs can be any form of application, module, or plugin.

[0063] The following describes an electronic device that implements a database query statement processing method according to embodiments of this application. See also... Figure 2 , Figure 2 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application. The electronic device 500 provided in this embodiment can be a terminal or a server. Figure 2As shown, electronic device 500 includes at least one processor 510, memory 550, at least one network interface 520, and user interface 530. The various components in electronic device 500 are coupled together via a bus system 540. It is understood that the bus system 540 is used to implement communication between these components. In addition to a data bus, the bus system 540 also includes a power bus, a control bus, and a status signal bus. However, for clarity, in… Figure 2 The general labeled all buses as Bus System 540.

[0064] The processor 510 can be an integrated circuit chip with signal processing capabilities, such as a general-purpose processor, a digital signal processor (DSP), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. The general-purpose processor can be a microprocessor or any conventional processor, etc.

[0065] User interface 530 includes one or more output devices 531 that enable the presentation of media content, including one or more speakers and / or one or more visual displays. User interface 530 also includes one or more input devices 532, including user interface components that facilitate user input, such as a keyboard, mouse, microphone, touch screen display, camera, other input buttons and controls.

[0066] Memory 550 may be removable, non-removable, or a combination thereof. Memory 550 may include one or more storage devices physically located away from processor 510. Memory 550 may include volatile memory or non-volatile memory, or both. Non-volatile memory may be read-only memory (ROM), and volatile memory may be random access memory (RAM). The memory 550 described in this application embodiment is intended to include any suitable type of memory.

[0067] In some embodiments, memory 550 is capable of storing data to support various operations, examples of which include programs, modules, and data structures or subsets or supersets thereof, as illustrated below.

[0068] Operating system 551 includes system programs for handling various basic system services and performing hardware-related tasks, such as the framework layer, core library layer, driver layer, etc., for implementing various basic business functions and handling hardware-based tasks;

[0069] The network communication module 552 is used to reach other electronic devices via one or more (wired or wireless) network interfaces 520, exemplary network interfaces 520 including: Bluetooth, WiFi, and Universal Serial Bus (USB), etc.

[0070] Presentation module 553 is configured to enable the presentation of information (e.g., a user interface for operating peripheral devices and displaying content and information) via one or more output devices 531 (e.g., a display screen, a speaker, etc.) associated with user interface 530;

[0071] The input processing module 554 is used to detect and translate one or more user inputs or interactions from one or more input devices 532.

[0072] In some embodiments, the database query statement processing apparatus provided in this application can be implemented in software. Figure 2 A processing device 555 for query statements of a database stored in memory 550 is shown. It can be software in the form of programs and plug-ins, including the following software modules: a first grouping module 5551, a first extraction module 5552, a first acquisition module 5553, a first prediction module 5554, and a first determination module 5555. These modules are logically related and can therefore be arbitrarily combined or further divided according to the functions they implement. The functions of each module will be described below.

[0073] The following describes the database query statement processing method provided in the embodiments of this application. As mentioned above, the database query statement processing method provided in the embodiments of this application is implemented by an electronic device, for example, it can be implemented by a server or terminal alone, or by a server and terminal working together. Therefore, the executing entity of each step will not be described again below. See Figure 3 , Figure 3 This is a flowchart illustrating a database query statement processing method provided in this application embodiment. The database query statement processing method provided in this application embodiment includes:

[0074] Step 101: Group the multiple query templates in the database to obtain multiple query template groups.

[0075] Each query template group includes at least two query templates, and each query template group is associated with a machine learning model. Different query template groups are associated with different machine learning models.

[0076] For step 101, given the multiple query templates involved in the database, these templates are grouped to obtain multiple query template groups. Each query template group includes at least two query templates. A separate machine learning model is trained for each query template group; different query template groups are associated with different machine learning models. See [link to relevant documentation] Figure 4 , Figure 4 This is a schematic diagram illustrating the grouping of query templates provided in this application embodiment. Here, the query template group includes query template group 1 to query template group n1, and each query template group includes at least two query templates (for example, query template group 1 includes query template 1 to query template n2; query template group 2 includes query template 1 to query template n3); each query template group is associated with a machine learning model (for example, query template group 1 is associated with machine learning model 1, and query template group n1 is associated with machine learning model n1). Thus, multiple query statements using query templates within a query template group share a single machine learning model. Wherein, n1 is an integer greater than 1; n2 and n3 are integers greater than or equal to 2.

[0077] In some embodiments, see Figure 5 Step 101, "Grouping multiple query templates in the database to obtain multiple query template groups," can be achieved by executing the following steps 1011-1013: Step 1011, obtaining multiple database query statements for the database, wherein the multiple database query statements are associated with multiple query templates; Step 1012, performing clustering processing on the multiple database query statements to obtain multiple clusters, wherein each cluster includes at least two database query statements; Step 1013, for each cluster, constructing a query template group based on the query templates associated with at least two database query statements in the cluster.

[0078] In step 1011, multiple database query statements are obtained for the database. These multiple database query statements are used to query the database. The set of these multiple database query statements can be considered as the workload for the database; that is, the workload is a set of multiple database query statements. It should be noted that the query templates used by these multiple database query statements are the multiple database query templates in step 101. This is because the multiple database query templates are extracted from the multiple database query statements. Each database query statement includes a query template (i.e., structure) and query parameters (i.e., binding values). By binding different query parameters to the same query template, different multiple database query statements under the same query template can be obtained. Therefore, the multiple database query statements in step 1011 are obtained by binding different query parameters to multiple query statements respectively.

[0079] In step 1012, multiple database query statements are clustered to obtain multiple clusters, where each cluster includes at least two database query statements. Clustering algorithms (such as k-means) can be used for clustering. In practical applications, clustering keywords (i.e., clustering keys) can be set for clustering, thereby clustering multiple database query statements based on the clustering keywords. For example, the clustering keyword can be the query join structure of the database query statements, which includes: multiple data tables queried by the database query statements, and the join relationships (also called association relationships) between the multiple data tables. The data tables are the data tables accessed by the database query statements. A database query statement involves at least one data table, and different data tables have join relationships. These join relationships can be represented by join fields, which describe which fields are used to establish the connection (i.e., association) between multiple data tables. Therefore, the query join structure can specifically include: multiple data tables and join fields between the multiple tables. The query join structure does not depend on the filtering conditions (i.e., binding values) or the final statistical target (such as summation or counting) in the database query statement. It only focuses on the join relationship (association method) between data tables, which mainly includes: the set of multiple data tables involved in the join, and the join rules between the data tables (such as which fields and what operators are used for the join).

[0080] For step 1013, for each cluster, a query template group is constructed based on the query templates associated with at least two database query statements in the cluster. Specifically, the query templates used by at least two database query statements in the cluster can be added to the same query template group, that is: at least two query templates involved in each cluster constitute a query template group.

[0081] Applying the above embodiments, 1) the model bloat problem is solved: query template groups are constructed according to clusters, and the same query template group shares the model, eliminating the need to build a separate model for each query template, reducing the total number of models and lowering storage and maintenance costs; 2) the model generalization ability is improved: query statements associated with query templates within clusters have similar query connection structures and optimization rules. After merging training data, the model learns general rules and has stronger adaptability to new query statements within the query template group, avoiding overfitting or underfitting of a single template model; 3) the load processing efficiency is optimized: scattered database query statements are classified according to clusters to form structured query template groups, which can then be used to generate candidate plans and train models in a targeted manner, avoiding indiscriminate processing of all queries and improving the overall efficiency of query optimization.

[0082] In some embodiments, step 1012, "clustering multiple database query statements to obtain multiple clusters," can be achieved by performing the following steps: obtaining the query connection structure of each database query statement, wherein the query connection structure includes: multiple data tables queried by the database query statement, and the connection relationships between the multiple data tables; based on the query connection structure of each database query statement, clustering multiple database query statements to obtain multiple clusters, wherein each cluster includes at least two database query statements with the same query connection structure.

[0083] Here, we can first parse each database query statement to extract four key types of information: query template, query parameters (i.e., binding values), the accessed data tables (also called join tables, such as sales and orders), and the join relationships between the data tables (also called join conditions, such as sales.order_id = orders.id). Then, from the extracted key information, we can obtain the query join structure for each database query statement. This query join structure includes multiple data tables and the join relationships between them (specifically, join fields). For example, join fields can include: left table fields + right table fields + join operators. For example, the database query statement: QueryA: salesJOIN orders ON sales.order_id = orders.id; Data tables: {sales, orders}; Join fields: (Equivalent association). After obtaining the query join structure, multiple database query statements are clustered based on their query join structure to obtain multiple clusters. Specifically, at least two database query statements with the same query join structure are grouped into one cluster, so that each cluster includes at least two database query statements with the same query join structure.

[0084] It should be noted that for database query statements with the same query join structure, optimization rules can be reused (e.g., joining tables with fewer rows first to reduce intermediate results, and prioritizing indexes on join fields to speed up the join). Therefore, the query templates used by database query statements with the same query join structure can be treated as a group of query templates. Each group of query templates can share a single machine learning model for coverage, eliminating the need to build a separate machine learning model for each query template, thus solving the model bloat problem during query optimization. However, database query statements with different query join structures have significantly different optimization rules (e.g., the execution plan selection logic for two-table joins and five-table joins is completely different), and different machine learning models must be used. Otherwise, the generalization ability of the machine learning model will be greatly reduced.

[0085] Applying the above embodiments, 1) precise division of optimization units: clustering according to "same query connection structure" ensures that the data table set and inter-table connection relationship of the query statements in the same cluster are consistent, and their query optimization rules can be reused, laying a reasonable grouping foundation for subsequent shared models and avoiding model generalization failure due to structural differences; 2) enhanced model training effectiveness: the same query connection structure in the same cluster results in a more concentrated distribution of features in the merged training data, enabling the model to focus on learning the optimization rules under this structure, reducing interference from irrelevant features, improving the accuracy of selecting the optimal execution plan for queries within the group, and alleviating the overfitting or underfitting problem of a single template model.

[0086] In some embodiments, when the number of query templates associated with at least two database query statements in a cluster is greater than a number threshold, step 1013, "constructing a query template group based on the query templates associated with at least two database query statements in a cluster", can be achieved by performing the following steps: obtaining preset grouping parameters; grouping the query templates associated with multiple database query statements in a cluster according to the preset grouping parameters to obtain at least two query template groups.

[0087] Here, if the number of query templates associated with at least two database query statements in a cluster exceeds a threshold (i.e., the number of query templates associated with a cluster is greater than the threshold when grouping them into a query template group), then further subdivision is possible. Specifically, preset grouping parameters can be obtained. For example, these parameters could be: a) the size of the data tables involved in the at least two database query statements in the cluster (e.g., the number of data tables); b) the join complexity of the join relationships between the data tables; c) the type of query operator (e.g., aggregation, sorting); d) the business tags (e.g., sales analysis, customer analysis); etc. Then, according to the preset grouping parameters, the query templates associated with multiple database query statements in the cluster are grouped to obtain at least two query template groups.

[0088] By applying the above embodiments, 1) the excessive number of query templates in a single cluster can lead to a decrease in the model's generalization ability. By splitting the query templates using preset grouping parameters, the number of query templates within each group is balanced. This preserves the reusability of query optimization rules for the same query connection structure while preventing excessive differences between query templates within the same group from interfering with model learning. 2) The size of the query template groups is controlled to avoid overloading of data in a single group, reducing the computational cost of subsequent candidate plan generation and model training. At the same time, each query template group focuses on query templates for specific substructures, improving the model's adaptation and selection accuracy for query templates within the group. 3) The splitting process better meets query optimization needs. The query templates in each group are more focused on specific connection structure scenarios, allowing for targeted adaptation of candidate plan generation. This reduces the generation of redundant candidate plans, ensures the compatibility of query templates within the group with database query statements, and supports efficient execution of query statements.

[0089] Step 102: Extract the first query template from the query statement to be executed, and determine the first query template group from multiple query template groups.

[0090] The first query template group includes the first query template.

[0091] For step 102, for the query statement to be executed, firstly, a first query template is extracted from the query statement to be executed. The first query template is the structure of the query statement. Then, a first query template group is determined from the multiple query template groups mentioned above. This first query template group includes the first query template of the query statement. Specifically, the first query template is compared with each query template in the multiple query template groups to determine a target query template group that includes query templates identical to the first query template. This target query template group can then be used as the first query template group.

[0092] In some embodiments, step 102, "extracting the first query template of the query statement from the query statement to be executed", can be achieved through the following steps: parsing the query statement to be executed to obtain the constant elements and structure elements included in the query statement; replacing the constant elements in the query statement with placeholders to obtain the initial query template; and standardizing the initial query template to obtain the first query template.

[0093] Here, the query statement to be executed is first parsed to obtain constant elements and structure elements. Constant elements are specific numerical values, strings, dates, etc., that change according to the query requirements, determining the filtering range or target value (i.e., bound values ​​or query parameters), such as string numbers, dates, etc. Structure elements are used to define the fixed parts of the query logic framework, the data table connections, and calculation rules, and do not change with the query conditions; these include keywords, data table names, field names, operators, functions, wildcards, etc. In practical applications, query statements can be parsed to obtain an Abstract Syntax Tree (AST). This AST consists of multiple nodes, each corresponding to a query component in the query statement (such as SELECT clause nodes, JOIN clause nodes, and WHERE condition nodes), facilitating accurate identification of structural elements and constant elements. Then, each node in the AST is traversed to determine whether it is a structural element or a constant element. For example: a) Keyword / Function Nodes: If the node content is a standard database keyword (such as SELECT / JOIN) or a built-in function (such as SUM / BETWEEN), then the node is a structural element; b) Table Name / Field Name Nodes: If the node content corresponds to an actual table / field in the database (metadata verification is required), then the node is a structural element; c) Numeric / String Nodes: If the node content is a semantically meaningless literal (such as a quoted string or a pure number), then the node is a constant element; d) Conditional Expression Nodes: Operators in the node content (such as = / IN) are structural elements; literals on both sides of the operator in the node content are constant elements.

[0094] Next, replace constant elements in the query statement with placeholders (such as standard placeholders). Placeholders only need to ensure their position is unique; their format can be customized. The core principle is that constants of the same type use placeholders with the same format, regardless of their specific values. This yields the initial query template. Then, the initial query template is standardized to obtain the first query template. Specifically, the same query statement written in different styles may generate different initial query templates. Standardization is necessary to ensure that queries with the same logic correspond to a unique query template. Standardization methods can include: unifying keyword case, cleaning up redundant spaces and line breaks, standardizing JOIN order, standardizing condition order, and unifying aliases, etc.

[0095] Applying the above embodiments, the composition of the query statement is accurately broken down: constants and structural elements are separated through syntax parsing, and the variable data and fixed logic of the query statement are clarified. This facilitates the determination of the first query template group and avoids constants interfering with the determination of the query template group.

[0096] Step 103: Obtain multiple execution plans associated with the first query template.

[0097] In the embodiments of this application, see also Figure 4 For each query template in the multiple query template groups, multiple candidate plans associated with that query template are generated; that is, one query template is associated with multiple candidate plans. Therefore, for step 103, multiple execution plans associated with the first query template can be obtained from the multiple candidate plans associated with each of the multiple query templates.

[0098] In some embodiments, before "obtaining multiple execution plans associated with the first query template", the following steps may be performed: For each query template in each query template group, the following processing is performed to generate multiple candidate plans associated with the query template: From multiple database query statements targeting the database, M target query statements associated with the query template are obtained, where M is an integer greater than 1; N statement candidate plans are generated for each target query statement, and the N statement candidate plans for each of the M target query statements are used as M*N candidate plans associated with the query template, where N is an integer greater than 1. Based on this, "obtaining multiple execution plans associated with the first query template" can be achieved by performing the following steps: Using the M*N candidate plans associated with the first query template from the multiple query templates as multiple execution plans associated with the first query template.

[0099] Here, the following processing is performed for each query template: First, from multiple database query statements targeting the database, obtain M target query statements associated with the query template. All M target query statements use the same query template, and these M target query statements can be randomly selected from the multiple database query statements. Then, for each target query statement, generate N candidate statement plans associated with it. Thus, each target query statement is associated with N candidate statement plans. Therefore, the N candidate statement plans for each of the M target query statements are used as the M*N candidate plans associated with the query template, meaning each query template is associated with M*N candidate plans. Therefore, obtaining the multiple execution plans associated with the first query template in step 103 can be achieved by using the M*N candidate plans associated with the first query template from among the multiple query templates as the multiple execution plans associated with the first query template.

[0100] Applying the above embodiments, 1) a sufficient number of candidate plans are constructed: For each query template, N candidate plans are generated from each of the M target query statements, forming M*N candidate plans, covering more potential execution plans and avoiding the omission of the optimal execution plan due to a narrow candidate plan set, thus providing a rich sample basis for subsequent model optimization. 2) The commonalities and differences of the query template are aligned: The M target query statements are associated with the same query template, ensuring that the candidate plans are generated around the core structure of the query template (i.e., the query join structure). At the same time, the N plans for each target query statement reflect the strategy adaptability brought about by the differences in binding values, taking into account both common optimization rules and individual differences. 3) Efficient model training and generalization are supported: The M*N candidate plans cover a variety of combinations of join orders, access paths, etc. After execution, rich runtime data (such as execution time) can be collected, providing sufficient training samples for the machine learning model, helping the model learn the general optimization rules under the query template, and improving the generalization ability of new query statements with the same query template.

[0101] In some embodiments, see Figure 6 The process of "generating N candidate plans for each target query statement" can be achieved by executing the following steps 201-202: Step 201: Obtain the K data tables associated with the query template and the connection relationships between the K data tables, where K is an integer greater than 1; Step 202: For each target query statement, perform the following processing: Step 2021: Based on the target query statement, determine the priority ranking probability of each data table, which is positively correlated with the order of the data table in the K data tables; Step 2022: Based on the connection relationships and priority ranking probabilities, perform N random permutation operations on the K data tables to obtain the ranking order of the K data tables output by each random permutation operation, where the ranking order output by each random permutation operation is different; Step 2023: For each random permutation operation, connect the K data tables according to the ranking order output by the random permutation operation to obtain the candidate plan for the statement.

[0102] For step 201, firstly, obtain the K data tables associated with the query template. These K data tables are actually the data tables involved in the M target query statements using this query template. Then, obtain the connection relationships between the K data tables, which can be represented by the join field. In practical applications, a connection graph of the query template can also be obtained directly. This connection graph is an undirected graph constructed with each of the K data tables as a node and the connection relationships between the data tables as edges, so as to better represent the connection relationships between the K data tables.

[0103] For step 202, steps 2021-2023 are executed for each target query statement to obtain N candidate statement plans for each target query statement. For step 2021, based on the target query statement, the priority ranking probability of each data table is determined. This priority ranking probability is positively correlated with the order in which the data table appears in the K data tables. It should be noted that generating candidate statement plans actually involves determining the order in which the K data tables are connected, and this order can be obtained by randomly arranging the K data tables. The priority ranking probability of a data table is positively correlated with its order in the K data tables; that is, the higher the priority ranking probability of a data table, the higher the probability that the data table appears earlier in the K data tables' order.

[0104] In some embodiments, step 2021, "determining the priority ranking probability of each data table based on the target query statement," can be achieved by performing the following steps: extracting data volume filtering conditions from the target query statement; filtering each data table based on the data volume filtering conditions to obtain filtered data tables; and for each data table, determining the priority ranking probability of the data table based on the data volume of the filtered data table and the total data volume of the K filtered data tables.

[0105] Here, the target query statement includes query conditions (which can be specific to a particular database). These query conditions are extracted and used as data volume filtering conditions for the data tables. These data volume filtering conditions are used to filter the number of rows in the data tables (i.e., the cardinality obtained through cardinality estimation). Thus, based on the data volume filtering conditions, each data table is filtered to obtain filtered data tables. For example, for each data table, rows that do not meet the data volume filtering conditions are removed. Then, for each data table, based on the data volume of the filtered data table and the total data volume of the K filtered data tables, the priority ranking probability of the data table is determined. For example, the ratio of the data volume of the filtered data table to the total data volume of the K filtered data tables is the priority ranking probability.

[0106] Applying the above embodiments, 1) accurately obtain the scale of filtered data: extract the data volume filtering conditions and filter the data tables to obtain the filtered data volume that fits the filtering logic of the target query statement, providing a real data basis for subsequent probability calculations and avoiding probability deviations caused by using the original table data volume; 2) scientifically determine the basis for table priority: calculate the extraction probability based on the data volume of the filtered tables and the total data volume, so that tables with larger data volumes have a higher priority ranking probability, which is in line with the OLAP data query scenario: prioritizing large tables (i.e., data tables with data volumes greater than the data volume threshold) can reduce the optimization rules of intermediate results and improve the rationality of subsequent join order; 3) support the generation of diverse candidate plans: reasonable extraction probabilities can guide the generation of more high-quality join orders (such as the order of prioritizing large tables), avoid the random generation of inefficient orders without basis, provide high-quality samples for the candidate plan set, help the model to select the best option, and improve query execution efficiency.

[0107] For step 2022, based on the connection relationships and priority order probabilities, N random permutation operations are performed on the K data tables to obtain the permutation order of the K data tables output by each random permutation operation. The permutation order output by each random permutation operation is different. It should be noted that each random permutation operation is performed on K data tables; one random permutation operation yields one permutation order of the K data tables, and N random permutation operations yield a total of N permutation orders of the K data tables. Specifically, each random permutation operation includes: performing a first random selection from the K data tables and determining the permutation order of the first randomly selected data table as 1; removing the selected data, performing a second random selection from the remaining data tables and determining the permutation order of the second randomly selected data table as 2; ...; determining the permutation order of the Kth randomly selected data table as K. Thus, one permutation order of the K data tables is obtained. Each random selection is based on join relationships and priority order probabilities. Therefore, the priority order probability can also be called the priority selection probability. The higher the priority selection probability of a data table, the higher the likelihood (i.e., the higher its ranking) in the order of the data tables. Selection based on join relationships ensures that the order of the K data tables matches the join relationships between them. In practical applications, the priority order probability can be calculated based on the remaining data tables after each random selection. The calculation method is the same each time: the number of data in the first data table is divided by the total number of data in the remaining data tables.

[0108] For step 2023, after obtaining N possible permutations of the K data tables from N random permutation operations, for each permutation, the K data tables are joined according to that permutation order to obtain the statement candidate plan. Here, the subsequent statement plan is obtained by joining the K data tables according to the permutation order, which is also the joining order of the K data tables.

[0109] It should be noted that the data tables used in steps 2022 and 2023 are both filtered data tables. That is: based on the join relationship and priority ranking probability, N random permutation operations are performed on the K filtered data tables to obtain the order of the K filtered data tables output by each random permutation operation; and, for each random permutation operation, the K filtered data tables are joined according to the order of the output of the random permutation operation to obtain the statement candidate plan.

[0110] Applying the above embodiments: 1) Define the connection basis: Obtain K data tables and their connection relationships to provide a structural basis for subsequent arrangement and connection, ensuring that the connection operation conforms to the query logic and avoiding invalid arrangement due to missing data tables or connection relationships. 2) Guide high-quality arrangement: The probability of priority arrangement is positively correlated with the degree of priority in the arrangement order, making data tables with large data volumes and those suitable for the target query statement more likely to be placed at the top, which conforms to the rule of "prioritizing the connection of better tables and reducing intermediate results" in OLAP query scenarios, improving the rationality of the arrangement order. 3) Generate diverse candidate plans: N random permutations output different orders, and combined with the connection relationships, N statement candidate plans are obtained, covering more potential high-quality connection strategies and avoiding a single candidate set; diverse plans provide sufficient samples for subsequent model optimization, helping the model's ability to select the best execution plan.

[0111] In some embodiments, the candidate plans can be expanded by performing the following steps: obtaining table information of the data table associated with the query template and obtaining description information of M target query statements; based on the table information and description information, expanding the M*N candidate plans associated with the query template to obtain H candidate plans associated with the query template, where H is an integer greater than M*N.

[0112] Here, for each query template associated with M*N candidate plans, the candidate plans can be expanded. Specifically, first, the table information of the data tables associated with the query template is obtained, along with the descriptive information of the M target query statements. The table information includes, but is not limited to, table structure (fields, types, indexes, etc.) and statistical information (such as the number of rows in each table, the number of unique values, etc.); the descriptive information includes, but is not limited to, the query template information of the target query statements, specific query parameters (i.e., binding values), and association logic. Then, based on the table information and descriptive information, the M*N candidate plans associated with the query template are expanded to obtain H candidate plans associated with the query template. Specifically, prompt words can be constructed based on the table information and descriptive information, and then a large language model is called to generate new candidate plans for expansion based on the prompt words. These new candidate plans are added to the M*N candidate plans, resulting in H candidate plans, thus expanding the candidate plans.

[0113] In practical applications, large language model expansion is not the only supplementary method. Candidate plans can also be expanded based on manual or other experience. For example, 1) Manual supplementation: DBAs supplement specific query templates with verified efficient join orders based on business experience (such as a certain type of "sales-inventory" join query, where "the inventory table joins the sales table first" can reduce intermediate results) to obtain new candidate plans; 2) Other experience supplementation: reuse the optimal execution plan of historical queries (such as extracting the join order with the highest execution efficiency in the past from the query log), or based on business rules (such as "the dimension table joins the fact table first" - in OLAP, the dimension table usually has a small amount of data, while the fact table has a large amount of data).

[0114] Applying the above embodiments, 1) Improve candidate plan coverage: make up for the blind spots of "probabilistic selection" of M*N candidate plans, and generate non-intuitive but efficient connection order (i.e., candidate plan); 2) Transfer expert experience: the large language model is trained with a large number of DBA tuning cases, which is equivalent to reusing "expert experience" to all queries, especially for business scenarios lacking manual tuning resources; 3) Adapt to complex query statements: for complex scenarios such as multi-table joins and nested subqueries, the large language model can generate connection order that is more in line with the data characteristics of the query statement, avoiding intermediate result explosion; 4) Reduce model training cost: a high-quality candidate plan set is the foundation of machine learning model training. The high-quality plans supplemented by the large language model can reduce the model's learning of "low-quality candidate plans" and improve training efficiency and selection accuracy.

[0115] In some embodiments, the machine learning model associated with each query template group can be trained by performing the following steps: For each query template group, perform the following processing: For each query template in the query template group, construct training data based on the M*N candidate plans associated with the query template, wherein the training data includes the target query statement and any two candidate plans from the M*N candidate plans, each training data is labeled, and the label represents the performance comparison result of any two candidate plans; obtain the initial machine learning model associated with the query template group, and call the initial machine learning model to predict the training data to obtain the target prediction result, wherein the target prediction result represents the prediction comparison result of the performance of any two candidate plans; based on the difference between the target prediction result and the label, train the initial machine learning model to obtain the machine learning model associated with the query template group.

[0116] Here, a machine learning model is trained for each query template group. It should be noted that before training multiple machine learning models, step 101 above and the step of "generating M*N candidate plans associated with each query template" need to be performed. In this way, multiple query template groups and M*N candidate plans associated with each query template are obtained. At the same time, a corresponding initial machine learning model can be pre-built for each query template group. The initial machine learning models of multiple query template groups can be completely identical, completely different, or partially identical and partially different. The different initial machine learning models can be different in model parameters, different in model structure, or different in both model parameters and structure.

[0117] Based on this, for each of the M*N candidate plans associated with each query template, the target query statement associated with that candidate plan is first executed to obtain runtime data for each candidate plan. This runtime data includes, but is not limited to, execution time, resource consumption (such as CPU resources, memory resources, I / O resources, etc.). However, the performance comparison results in the prediction results are based on predictions from a machine learning model. Training data can be constructed based on this runtime data.

[0118] Specifically, for each query template group, the following processing is performed to obtain the machine learning model associated with each query template group: For each query template in the query template group, M training data are constructed based on the M*N candidate plans associated with the query template. These training data correspond one-to-one with the target query statement associated with the query template. Each training data includes: the target query statement and any two candidate plans from the M*N candidate plans. Each training data is labeled, and the label represents the performance comparison result of any two candidate plans. This performance is represented by the runtime data of the aforementioned two candidate plans, and the comparison result is obtained by comparing the performance represented by the runtime data of the two candidate plans. Based on this, for each training data, the initial machine learning model associated with the query template group is invoked to predict the training data, obtaining the target prediction result. This target prediction result represents the prediction comparison result of the performance of the two candidate plans in the training data. Thus, the target prediction result for each training data is obtained. Then, for each training data set, the difference between the target prediction result and the label of the training data is determined, and an initial machine learning model is trained based on this difference (i.e., the model parameters of the initial machine learning model are updated based on the difference), thus obtaining the machine learning model associated with the query template group.

[0119] See Figure 7 , Figure 7This is a schematic diagram illustrating the training of the machine learning model provided in this application embodiment. Here, the training data includes a target query statement and any two candidate plans (such as candidate plan 1 and candidate plan 2), which are labeled; the training data is input into an initial machine learning model, and the initial machine learning model is called to predict the training data to obtain the target prediction result; based on the target prediction result and the labels, the value of the loss function is determined; based on the value of the loss function, the model parameters of the initial machine learning model are updated to obtain the machine learning model.

[0120] Applying the above embodiments: 1) Constructing high-quality training data: Training data with performance comparison labels is generated based on M*N candidate plans, covering diverse plan combinations, providing the model with sufficient and realistic learning samples, avoiding insufficient generalization caused by single training data. 2) Precisely optimizing the model: The model is adjusted by the difference between the target prediction result and the label, allowing the model to learn the performance comparison rules of candidate plans, improving the accuracy of judging the merits of plans, and providing reliable model support for subsequent optimization. 3) Forming a machine learning model specific to the query template group: A dedicated model is trained for the query template group. The model adapts to the query structure features within the group, and can more accurately capture the optimization rules of plans within the group. Compared with the general model, the optimization accuracy of plans for queries within the group is higher, helping to improve query execution efficiency; and it solves the problem of model bloat.

[0121] Step 104: Based on the query statement and any two execution plans from multiple execution plans, call the machine learning model associated with the first query template group to make a prediction and obtain the prediction result.

[0122] The prediction results represent the performance comparison between any two execution plans.

[0123] For step 104, after determining the first query template group to which the first query template of the query statement to be executed belongs, the machine learning model associated with the first query template group can be used to determine the optimal execution plan for the query statement. Specifically, based on the query statement and any two execution plans from multiple execution plans, the machine learning model associated with the first query template group can be invoked to make a prediction and obtain the prediction result. That is, at least one plan pair is generated for multiple execution plans, and each plan pair includes any two execution plans from multiple execution plans; then, for each plan pair, the query statement and the plan pair (i.e., any two execution plans from multiple execution plans) are input into the machine learning model associated with the first query template group, thereby invoking the machine learning model associated with the first query template group to make a prediction and obtain the prediction result for each plan pair. The prediction result is a comparison of the performance of two execution plans in the standard plan. This comparison result indicates the relative superiority or inferiority of the two execution plans, which can be represented by a winner bitmap. For example, if the performance of execution plan 1 is better than that of execution plan 2, then the first position of the winner bitmap (representing execution plan 1) is 1 and the second position (representing execution plan 2) is 0, i.e., 10; if the performance of execution plan 3 is worse than that of execution plan 4, then the first position of the winner bitmap (representing execution plan 3) is 0 and the second position (representing execution plan 4) is 1, i.e., 01.

[0124] It's important to note that execution plan performance refers to the performance of executing a query statement based on that plan. This performance can be measured using runtime data of the query statement, which includes, but is not limited to, execution time, resource consumption (such as CPU, memory, and I / O resources), etc. However, the performance comparisons in the prediction results are based on predictions from machine learning models.

[0125] In some embodiments, step 104, "based on the query statement and any two execution plans from multiple execution plans, call the machine learning model associated with the first query template group to make a prediction and obtain a prediction result," can be achieved by performing the following steps: call the machine learning model associated with the first query template group and perform the following processing: extract features from the query statement to obtain query statement features, and extract features from any two execution plans from multiple execution plans to obtain two execution plan features; make a prediction based on the query statement features and the two execution plan features to obtain a prediction result.

[0126] Here, the machine learning model associated with the first query template group performs the following processing: First, feature extraction is performed on the query statement to obtain query statement features; specifically, the query parameters (i.e., binding values) of the query statement are first encoded to obtain query encoded features, and then the query encoded features are embedded (for example, using a feedforward neural network to embed the query encoded features) to obtain query statement features. Simultaneously, feature extraction is performed on any two execution plans from multiple execution plans to obtain two execution plan features; specifically, for each execution plan, the execution plan is first encoded to obtain plan encoded features, and then the plan encoded features are embedded (for example, using tree convolution to embed the plan encoded features) to obtain execution plan features. Then, prediction is made based on query statement features and two execution plan features to obtain the prediction result. Specifically, the distance between each execution plan feature and the query statement feature can be calculated, and the prediction result is obtained based on these two distances. For example, if the two execution plan features include execution plan feature 1 of execution plan 1 and execution plan feature 2 of execution plan 2, corresponding to distance 1 and distance 2 respectively, then: 1) if distance 1 is greater than distance 2, the prediction result indicates that the performance of execution plan 1 is better than that of execution plan 2; 2) if distance 2 is greater than distance 1, the prediction result indicates that the performance of execution plan 2 is better than that of execution plan 1; 3) if distance 1 is equal to distance 2, the prediction result indicates that the performance of execution plan 1 and execution plan 2 are the same. The two distances can also be mapped (e.g., based on nonlinear transformations such as activation functions) to obtain the prediction result.

[0127] See Figure 8 , Figure 8This is a schematic diagram of the machine learning model provided in this application embodiment. Here, the machine learning model includes a query encoding layer, a plan encoding layer, a query embedding layer, a plan embedding layer, and a result prediction layer. Based on this, firstly, the query parameters (i.e., binding values) of the query statement are encoded through the query encoding layer to obtain query encoding features. Then, the query embedding layer performs embedding processing on the query encoding features (e.g., using a feedforward neural network to embed the query encoding features) to obtain query statement features. Features are extracted from any two execution plans (i.e., execution plans 1 and 2) from multiple execution plans to obtain two execution plan features. Specifically, for each execution plan, the execution plan is first encoded through the plan encoding layer to obtain plan encoding features (e.g., plan encoding feature 1 and plan encoding feature 2). Then, the plan embedding layer performs embedding processing on the plan encoding features (e.g., using tree convolution to embed the plan encoding features) to obtain execution plan features (e.g., execution plan feature 1 and execution plan feature 2). Finally, the result prediction layer performs prediction based on the query statement features and the two execution plan features to obtain the prediction result.

[0128] Applying the above embodiments, 1) Key features are accurately extracted: Query statement and execution plan features are extracted specifically to ensure that the features align with the query connection structure of the first query template group, providing accurate input for model prediction and avoiding interference from irrelevant features. 2) Prediction results are efficiently output: Leveraging the dedicated model of the first query template group, the performance of two execution plans is quickly predicted by combining two types of features. Compared to general models, this is more adaptable to the query patterns within the group, resulting in higher prediction accuracy and facilitating the rapid selection of the optimal execution plan, thus improving query optimization efficiency.

[0129] In some embodiments, “extracting features from any two execution plans among multiple execution plans to obtain two execution plan features” can be achieved by performing the following steps: For each of the two execution plans, perform the following processing: obtain multiple data tables included in the execution plan and obtain the connection fields between the multiple data tables; determine the data table number of each data table and determine the field number of each connection field; extract features from each data table number and each field number of the execution plan to obtain the number feature, and use the number feature as the execution plan feature of the execution plan.

[0130] Here, after obtaining the query template groups in step 101, a locally unique number is assigned to the data tables and join fields involved in each query template group. The data tables and join fields involved in the query template group include: multiple data tables involved in the database query statement using the query template of the query template group, and the join fields between the data tables. For example, the data table `sales` in query template group 1 is numbered 1, the data table `orders` in query template group 1 is numbered 2, the join field `sales.order_id` in query template group 1 is numbered A1, and the join field `orders.id` in query template group 2 is numbered A2. Based on this, as can be seen from the above embodiment, the execution plan includes multiple joined data tables and join fields between the data tables. Therefore, feature extraction of the execution plan is implemented as follows: first, the data table number of each data table is determined, and the field number of each join field is determined; then, feature extraction is performed on each data table number and each field number of the execution plan to obtain the number feature; finally, the number feature is used as the execution plan feature of the execution plan.

[0131] Applying the above embodiments: 1) Unified encoding dimension: Assigning unique numbers to data tables and join fields eliminates interference from differences in table / field names, ensures consistent encoding of execution plans including those with the same query join structure, reduces feature vector sparsity, and adapts to model learning patterns. 2) Accurate extraction of core features: Focusing on number feature extraction, directly associating with the query join structure of the execution plan avoids redundant irrelevant information, making the execution plan features more aligned with optimization needs, and helping the model efficiently capture key optimization dimensions such as join order. 3) Supporting efficient model prediction: The number features are concise and information-focused, enabling rapid input into the model for computation, improving the accuracy of comparing the merits of different execution plans, and providing a reliable feature foundation for subsequent optimization.

[0132] Step 105: Based on the prediction results of any two execution plans among multiple execution plans, determine the target execution plan for the query statement from among the multiple execution plans.

[0133] For step 105, based on the prediction results of any two execution plans among multiple execution plans, a target execution plan for the query statement is determined from the multiple execution plans. This target execution plan is the optimal execution plan for the query statement, and the query statement is executed according to the target execution plan.

[0134] In some embodiments, step 105, "Based on the prediction results of any two execution plans among multiple execution plans, determine the target execution plan for the query statement from among multiple execution plans", can be achieved by performing the following steps: based on the prediction results of any two execution plans among multiple execution plans, perform global sorting on the multiple execution plans to obtain a global sorting result; and take the execution plan with the best performance indicated by the global sorting result as the target execution plan.

[0135] Here, firstly, based on the prediction results of any two execution plans among multiple execution plans, a global ranking is performed to obtain a global ranking result, which includes all the execution plans. Then, the execution plan with the best performance indicated by the global ranking result is taken as the target execution plan. If the global ranking is in descending order of performance, then the execution plan at the top of the global ranking result is the target execution plan; if the global ranking is in ascending order of performance, then the execution plan at the bottom of the global ranking result is the target execution plan.

[0136] Applying the above embodiments, 1) the optimal plan is accurately selected: by integrating the prediction results of pairwise execution plans through global sorting, the bias of local comparisons is avoided, ensuring that the target execution plan with the best performance is selected, thus improving the accuracy of query optimization. 2) Execution scheme is efficiently determined: global sorting directly outputs the order of plan superiority, without the need for additional complex calculations, quickly locating the optimal solution, reducing query optimization time, and adapting to the efficiency requirements of OLAP scenarios. 3) Execution effect is guaranteed: sorting based on prediction results ensures that the selected target execution plan fits the query characteristics and data patterns, effectively reducing resource consumption and improving query execution efficiency and stability.

[0137] In some embodiments, after "determining the target execution plan for the query statement", the query statement can also be executed based on the target execution plan. The specific steps may be as follows: execute the query statement using the target execution plan; or, convert the target execution plan into query optimizer instructions for the query statement and execute the query statement based on the query optimizer instructions.

[0138] In one example, the target execution plan exists as a query optimizer hint. Therefore, after obtaining the target execution plan for the query, the query can be executed directly based on it. For example, the target execution plan can be embedded in the query to obtain a query containing the target execution plan, which can then be executed to obtain the query results. In another example, the target execution plan may only provide the join order of multiple tables, and is not a query optimizer hint that the query optimizer can directly recognize. Therefore, the target execution plan is first converted into a query optimizer hint, and then the query is executed based on the hint.

[0139] Applying the above embodiments, 1) ensuring efficient query execution: Both methods are centered on the target execution plan (optimal performance). Direct execution or hint execution avoids inefficient plans, adapts to query characteristics and data patterns, reduces resource consumption, and improves query response efficiency in OLAP scenarios. 2) adapting to different optimization needs: Direct execution adapts to fixed plan scenarios, while hint execution allows the optimizer to supplement details, flexibly responding to dynamic data changes, balancing plan stability and execution flexibility, and ensuring efficient query execution in diverse scenarios.

[0140] In some embodiments, the following steps may also be performed: when the new query template of the new query statement to be executed does not belong to multiple query template groups, a second query template group matching the new query statement is determined from the multiple query template groups, wherein the second query template group includes a second query template, and the similarity between the second query template and the new query template is greater than a similarity threshold; multiple candidate plans associated with the second query template are obtained; based on the new query statement and any two candidate plans from the multiple candidate plans, a machine learning model associated with the second query template group is invoked to make a prediction, and an updated prediction result is obtained, wherein the updated prediction result represents the performance comparison result of any two candidate plans; based on the updated prediction results of any two candidate plans from the multiple candidate plans, a target candidate plan for the new query statement is determined from the multiple candidate plans.

[0141] Here, for the new query statement to be executed, a new query statement template is first extracted from the new query statement. It should be noted that the implementation process of "extracting the new query statement template from the new query statement" can be the same as the implementation process of "extracting the first query template from the query statement to be executed" in step 102 above, and will not be elaborated here. If the new query template is not included in the existing multiple query template groups, the similarity between the new query template and each query template in the existing multiple query template groups can be determined. Therefore, a second query template with a similarity greater than a similarity threshold is selected from the multiple query templates included in the multiple query template groups. Then, a third query template group including the second query template is determined from the multiple query template groups. If there is only one third query template group, then that third query template group is used as the second query template group; if there are multiple third query template groups, then a target third query template group is selected from the multiple third query template groups as the second query template group. Specifically, the selection can be based on the machine learning models associated with the third query template group. For example, data from multiple dimensions such as performance metrics (accuracy, precision, recall, etc.), generalization ability metrics, convergence metrics, number of iterations, and training data volume of the machine learning models associated with each third query template group can be obtained. Based on this multi-dimensional data, the target machine learning model with the best performance (e.g., best performance, most iterations, largest training data volume, etc.) can be determined from the machine learning models associated with multiple query template groups. The third query template group associated with the target machine learning model can then be used as the second query template group.

[0142] Continuing, obtain multiple candidate plans associated with the second query template. It should be noted that the implementation process of "obtaining multiple candidate plans associated with the second query template" can be the same as the implementation process of "obtaining multiple execution plans associated with the first query template" in step 103 above, and will not be repeated here. Here, if there is only one second query template, then directly obtain multiple candidate plans associated with that one second query template; if there are multiple second query templates, only obtain multiple candidate plans associated with the second query template with the highest similarity; of course, multiple candidate plans associated with each second query template can also be obtained, which is not limited here.

[0143] Continuing, based on the new query statement and any two candidate plans from the multiple candidate plans, the machine learning model associated with the second query template group is invoked to make a prediction, obtaining an updated prediction result. This updated prediction result represents the performance comparison result of any two candidate plans. It should be noted that the implementation process of "invoking the machine learning model associated with the second query template group to make a prediction based on the new query statement and any two candidate plans from the multiple candidate plans to obtain an updated prediction result" can be the same as the implementation process of step 104 above, and will not be elaborated here. Finally, based on the updated prediction results of any two candidate plans from the multiple candidate plans, a target candidate plan for the new query statement is determined from the multiple candidate plans. It should be noted that the implementation process of "determining the target candidate plan for the new query statement from the multiple candidate plans based on the updated prediction results of any two candidate plans from the multiple candidate plans" can be the same as the implementation process of "execute the query statement based on the target execution plan" above, and will not be elaborated here. Thus, the new query statement can be executed based on the target candidate plan. The implementation process of "executing the new query statement based on the target candidate plan" can be the same as the implementation process of "executing the query statement based on the target execution plan" above, and will not be elaborated here.

[0144] By applying the above embodiments, when the new query template of the new query statement to be executed does not belong to multiple query template groups, by selecting the second query template group that matches the new query statement from the existing multiple query template groups, the execution plan of the new query statement under the new query template can be accurately selected, avoiding the cost of training a machine learning model separately for the new query template. At the same time, the adaptability to the new query statement is guaranteed by the transfer of template similarity, improving the transferability of this application and the adaptability to new query scenarios.

[0145] Applying the above embodiments of this application, firstly, multiple query templates in the database are grouped to obtain multiple query template groups; for a query statement to be executed, a first query template of the query statement is extracted from the query statement, and a first query template group including the first query template is determined from the multiple query template groups, and multiple execution plans associated with the first query template are obtained. Then, based on the query statement and any two execution plans from the multiple execution plans, the machine learning model associated with the first query template group is invoked to make a prediction, obtaining a prediction result. Based on the prediction results of any two execution plans from the multiple execution plans, a target execution plan for the query statement is determined from the multiple execution plans. Here, each query template group includes at least two query templates, and each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models. Thus, 1) the number of machine learning models is reduced, avoiding waste of equipment resources; 2) due to the reduction in the number of machine learning models, the impact of training data dispersion is also reduced, improving the accuracy of model prediction, thereby improving the selection accuracy of the execution plan and the performance of database queries.

[0146] The following describes the database query statement processing method provided in the embodiments of this application. As mentioned above, the database query statement processing method provided in the embodiments of this application is implemented by an electronic device, for example, it can be implemented by a server or terminal alone, or by a server and terminal working together. Therefore, the executing entity of each step will not be described again below. See Figure 9 , Figure 9 This is a flowchart illustrating a database query statement processing method provided in this application embodiment. The database query statement processing method provided in this application embodiment includes:

[0147] Step 901: Group the multiple query templates in the database to obtain multiple query template groups.

[0148] Each query template group includes at least two query templates, and each query template group is associated with a machine learning model. Different query template groups are associated with different machine learning models. The training process for each machine learning model is the same as that for the machine learning model in the previous embodiment.

[0149] Here, the implementation process of step 901 can be the same as the implementation process of step 101 above, and will not be repeated here.

[0150] Step 902: Extract the first query template from the query statement to be executed, and determine the first query template group from multiple query template groups.

[0151] The first query template group includes the first query template.

[0152] Here, the implementation process of step 902 can be the same as the implementation process of step 102 above, and will not be described in detail here.

[0153] Step 903: Obtain multiple execution plans associated with the first query template.

[0154] Here, the implementation process of step 903 can be the same as the implementation process of step 103 above, and will not be repeated here.

[0155] Step 904: Call the machine learning model associated with the first query template group to determine the distance between each execution plan and the query statement.

[0156] Here, the machine learning model associated with the first query template group is invoked to calculate the distance between each execution plan and the query statement. For details, see [link to details]. Figure 10 Here, the machine learning model includes a query encoding layer, a plan encoding layer, a query embedding layer, a plan embedding layer, and a result prediction layer. Based on this, firstly, the query parameters (i.e., binding values) of the query statement are encoded through the query encoding layer to obtain query encoded features. Then, the query embedding layer performs embedding processing on the query encoded features (e.g., using a feedforward neural network to embed the query encoded features) to obtain query statement features. For each execution plan (including execution plan 1 to execution plan n4), the execution plan is first encoded through the plan encoding layer to obtain plan encoded features (including plan encoded feature 1 to plan encoded feature n4). Then, the plan embedding layer performs embedding processing on the plan encoded features (e.g., using tree convolution to achieve embedding) to obtain execution plan features (including execution plan feature 1 to execution plan feature n4). Finally, the result prediction layer determines the distance (including distance 1 to distance n4) between each execution plan feature and the query statement features. Here, n4 is an integer greater than 1.

[0157] It should be noted that the implementation process of "extracting features from the query statement" here can be the same as the implementation process of "extracting features from the query statement" in the above embodiment, and will not be elaborated here; similarly, the implementation process of "extracting features from the execution plan" here can be the same as the implementation process of "extracting features from each of any two execution plans" in the above embodiment, and will not be elaborated here. The distance between the execution plan features and the query statement features can be cosine distance, Euclidean distance, Manhattan distance, Chebyshev distance, etc., and is not limited here.

[0158] Step 905: Based on each distance, determine the target execution plan for the query statement from multiple execution plans.

[0159] Here, the target distance with the smallest value is selected from multiple distances. Then, the execution plan corresponding to the target distance is determined from multiple execution plans. This execution plan corresponding to the target distance is used as the target execution plan for the query statement. In other words, the distance between the target execution plan and the query statement is the target distance, which is the smallest value among multiple distances. This target execution plan can be considered the optimal execution plan for the query statement, thus achieving the selection of the optimal execution plan for the query statement. After obtaining the target execution plan for the query statement, the query statement is executed based on the target execution plan. It should be noted that the implementation process of "executing the query statement based on the target execution plan" can be the same as the implementation process of "executing the query statement based on the target execution plan" in the above embodiment, and will not be elaborated here.

[0160] In some embodiments, the following steps may also be performed: when the new query template of the new query statement to be executed does not belong to multiple query template groups, a second query template group matching the new query statement is determined from the multiple query template groups, wherein the second query template group includes a second query template, and the similarity between the second query template and the new query template is greater than a similarity threshold; multiple candidate plans associated with the second query template are obtained; a machine learning model associated with the second query template group is invoked to determine the update distance between each candidate plan and the query statement; based on each update distance, a target candidate plan for the query statement is determined from the multiple candidate plans.

[0161] Here, for the new query statement to be executed, a new query statement template is first extracted from the new query statement. It should be noted that the implementation process of "extracting the new query statement template from the new query statement" can be the same as the implementation process of "extracting the first query template from the query statement to be executed" in step 102 above, and will not be elaborated here. If the new query template is not included in the existing multiple query template groups, the similarity between the new query template and each query template in the existing multiple query template groups can be determined. Therefore, a second query template with a similarity greater than a similarity threshold is selected from the multiple query templates included in the multiple query template groups. Then, a third query template group including the second query template is determined from the multiple query template groups. If there is only one third query template group, then that third query template group is used as the second query template group; if there are multiple third query template groups, then a target third query template group is selected from the multiple third query template groups as the second query template group. Specifically, the selection can be based on the machine learning models associated with the third query template group. For example, data from multiple dimensions such as performance metrics (accuracy, precision, recall, etc.), generalization ability metrics, convergence metrics, number of iterations, and training data volume of the machine learning models associated with each third query template group can be obtained. Based on this multi-dimensional data, the target machine learning model with the best performance (e.g., best performance, most iterations, largest training data volume, etc.) can be determined from the machine learning models associated with multiple query template groups. The third query template group associated with the target machine learning model can then be used as the second query template group.

[0162] Continuing, obtain multiple candidate plans associated with the second query template. It should be noted that the implementation process of "obtaining multiple candidate plans associated with the second query template" can be the same as the implementation process of "obtaining multiple execution plans associated with the first query template" in step 103 above, and will not be repeated here. Here, if there is only one second query template, then directly obtain multiple candidate plans associated with that one second query template; if there are multiple second query templates, only obtain multiple candidate plans associated with the second query template with the highest similarity; of course, multiple candidate plans associated with each second query template can also be obtained, which is not limited here.

[0163] Continuing, the machine learning model associated with the second query template group is invoked to determine the update distance between each candidate plan and the query statement. This update prediction result represents a performance comparison between any two candidate plans. It should be noted that the implementation process of "invoking the machine learning model associated with the second query template group to determine the update distance between each candidate plan and the query statement" can be the same as the implementation process of step 904 above, and will not be elaborated here. Finally, based on each update distance, a target candidate plan for the query statement is determined from multiple candidate plans. It should be noted that the implementation process of "determining the target candidate plan for the query statement from multiple candidate plans based on each update distance" can be the same as the implementation process of step 905 above, and will not be elaborated here. Thus, a new query statement can be executed based on the target candidate plan. The implementation process of "executing a new query statement based on the target candidate plan" can be the same as the implementation process of "executing a query statement based on the target execution plan" above, and will not be elaborated here.

[0164] By applying the above embodiments, when the new query template of the new query statement to be executed does not belong to multiple query template groups, by selecting the second query template group that matches the new query statement from the existing multiple query template groups, the execution plan of the new query statement under the new query template can be accurately selected, avoiding the cost of training a machine learning model separately for the new query template. At the same time, the adaptability to the new query statement is guaranteed by the transfer of template similarity, improving the transferability of this application and the adaptability to new query scenarios.

[0165] Applying the above embodiments of this application, firstly, multiple query templates in the database are grouped to obtain multiple query template groups; for a query statement to be executed, a first query template of the query statement is extracted from the query statement, and a first query template group including the first query template is determined from the multiple query template groups, and multiple execution plans associated with the first query template are obtained, thereby calling the machine learning model associated with the first query template group, determining the distance between each execution plan and the query statement, so as to determine the target execution plan for the query statement from the multiple execution plans based on each distance. Here, each query template group includes at least two query templates, and each query template group is associated with a machine learning model, and the machine learning models associated with different query template groups are different; thus, 1) the number of machine learning models is reduced, avoiding waste of equipment resources; 2) due to the reduction in the number of machine learning models, the impact of training data dispersion will also be reduced, improving the accuracy of model prediction, thereby improving the selection accuracy of execution plans and the performance of database queries (such as query efficiency, accuracy, etc.).

[0166] The following describes an exemplary application of the embodiments of this application in a real-world application scenario. First, the technical terms involved in this application will be explained, including:

[0167] 1) Workload: Consists of several query sequences that are executed concurrently. Each sequence contains several query statements that are executed sequentially. The same query statement can appear repeatedly in different sequences or in the same sequence.

[0168] 2) Query Template: Multiple query statements may have the same structure, differing only in some constants. These query statements are said to share the same query template. Query templates are also called parameterized queries.

[0169] 3) Join Graph: A query typically accesses several tables and performs a series of operations. When a query involves multiple tables, there may be join conditions between the tables. These tables form nodes in the join graph, and the join conditions between two tables form edges between the corresponding nodes. When optimizing the join order of tables in the execution plan, only adjacent nodes in the join graph are considered for connection, thus avoiding the generation of excessively large intermediate datasets.

[0170] 4) Statistics: Descriptions of data characteristics used to support query optimization decisions and performance evaluation. Statistics include the number of rows, the number of unique values, and data histograms, which can describe the data in storage and intermediate data generated during computation. Descriptions of the business operation process, such as time, CPU, and memory consumption, are also known as runtime statistics.

[0171] 5) Cardinality estimation: An estimate of the size of the dataset to be processed for a specific database operation (such as join or filter). For relational databases, the logical unit in a dataset is a row, so cardinality is the number of rows. Cardinality is a core criterion for the optimizer to select an efficient execution plan.

[0172] 6) Cost Model: An assessment of the resource consumption (CPU, I / O, memory) for each database operation. This can be understood as representing the cost of the operation as a polynomial, where each term represents a resource, coefficients represent resource weights, and variables represent resource consumption. A value can be derived by substituting into the cardinality or by using the cardinality. Summarizing all operations in the execution plan yields the cost of the execution plan. This cost is used to compare the relative merits of different plans.

[0173] 7) Execution Plan: A specific operational flow used by the database when executing a query statement. In practical applications, the execution plan is usually represented by a tree structure, with each tree node representing an operation. During execution, the parent node obtains input data from its child nodes and executes the processing logic set by the parent node's operation. Leaf nodes represent reading data from storage in a certain way, and the root node returns data to the database client. Node operations include reading raw data in a specific way (called the table access path), joining left and right input data according to a specific algorithm, and sorting inputs. The order of join processing is implicitly determined by multiple adjacent join nodes. The join order and table access path of the data tables are key factors affecting the performance of the execution plan. The execution plan after separating the binding values ​​is called a parameterized execution plan.

[0174] 8) Plan Space: The set of all possible execution plans for a query. The optimizer evaluates the set of execution plans to produce the optimal solution. Because there are many combinations of operations, the execution plan space can be very large. The optimizer may employ strategies to achieve a balance between resources and quality, producing an acceptable suboptimal solution rather than the optimal one.

[0175] 9) Candidate Plan Set: This is the set of all execution plans (candidate plans) actually reached during the optimization process. It is usually a subset of the plan space, but it can also be the complete plan space, depending on the specific algorithm of the optimizer. In practice, the optimizer evaluates the candidate plan set, not the theoretical complete plan space. Whether the candidate plan set contains the optimal plan determines whether the optimizer can generate the optimal solution.

[0176] 10) Hints: When developers realize that certain real-world scenarios may not match estimates, they embed optimizer instructions (such as forcing indexes or adjusting join order) into the query statement. The optimizer can then use the information provided by these instructions to calibrate estimates or adjust the algorithm process to produce a better solution. These optimizer instructions are called optimizer hints.

[0177] 11) Online Analytical Processing (OLAP): Database systems that handle analytical queries. Examples include supporting multidimensional aggregation and ad-hoc queries on large-scale historical data. Typical scenarios include data warehousing and business intelligence. Analytical queries present two main challenges: the complexity of the analytical logic and the scale of data to be processed. Typically, these queries have relatively long response times.

[0178] In database query optimization, especially in OLAP multi-table join scenarios, machine learning solutions are often used to improve the accuracy of execution plan selection. However, model bloat has become a key bottleneck. To adapt to different query characteristics, either a separate model is built for each query template, leading to a surge in the number of templates and an explosive growth in the number of models as business grows, consuming excessive storage and computing resources; or a simple shared model is used for all query templates, but poor adaptability occurs due to differences in query characteristics. Simultaneously, model bloat is accompanied by problems such as scattered training data and high vector sparsity, further weakening the model's generalization ability and making it difficult to cope with dynamically changing query loads in the database. Therefore, this application provides a solution that can effectively control model size while maintaining optimization performance, thereby overcoming the dilemma of model bloat in database query optimization. The following is a description of this solution.

[0179] This application provides a solution to the model bloat problem and achieves good generalization performance. The main idea is to divide the workload (i.e., the database query statements involved in the workload) and group related query templates into the same group. All query templates within the same group share a single model, while different groups use different models. Specifically:

[0180] (1) Load partitioning and parameter extraction.

[0181] The workload consists of multiple query statements. Each query statement logically comprises a parameterized query (i.e., a query template) and a list of bound values. Specifically, it may take the form of a parameterized query and bound values, or it may be a complete, concrete query statement. For a concrete query statement, the query template and bound value list need to be separated. For the same query template, different bound values ​​can distinguish the concrete query statement. Load partitioning involves grouping the query statements in the workload. A recommended approach is to analyze each query statement, extracting the query template and bound values, as well as the join table (i.e., the data table mentioned above) and join fields. The query statements are then clustered by table and join fields, with the query templates involved in each cluster forming a query template group. If the number of query templates in a query template group exceeds a threshold, the query template group can be further split into smaller query template groups based on preset grouping parameters.

[0182] Here, tables and join fields involved in the query statements within the same query template group are also assigned numbers, which are used as reference numbers during subsequent coding. The table and join field numbers must be saved for future reference; when using the model, the tables and join fields must first be converted into their corresponding numbers before being used in the coding.

[0183] (2) Generation of candidate plans.

[0184] (a) Generation of candidate plans: Given a query template, first generate k1 (i.e., M) query statements for the template (e.g., these can be extracted from the workload), and then generate k2 (i.e., N) execution plans for each query statement, forming a candidate plan set including k1*k2 candidate plans. The specific processing is as follows:

[0185] Given a query template, extract the join graph of that template. Randomly select k1 specific query statements from multiple queries within the same template. For each specific query statement, call the optimizer to calculate an execution plan, simultaneously obtaining the number of rows remaining after filtering each table using specific conditions (i.e., the data volume filtering conditions mentioned above) (i.e., the cardinality of the intermediate results after filtering). This allows you to obtain the cardinality of all nodes in the join graph. Given the cardinality of a table group and one table within that group, mapping the cardinality to the same space allows you to calculate the probability of that table being selected from the group (a larger cardinality results in a higher probability of selection). Therefore, first randomly select one table from the table group consisting of all nodes in the join graph, then randomly select a second table from the remaining nodes, and so on, until a complete join order is formed. Repeating this process yields k2 join orders, or candidate plans, for a single query.

[0186] Execute k1*k2 candidate plans to obtain the corresponding runtime data (used to annotate the victory bitmaps of two candidate plans based on the runtime data when building the training data for the model later), as well as the correspondence between hints and candidate plans (candidate plans are represented in the form of hints). It should be noted that for a candidate plan set to be expanded, each time a new candidate plan is added, the sum of the distances between all bound value embedding vectors and the candidate plan embedding vectors in the new candidate plan set must be minimized.

[0187] (b) Expansion of candidate plans based on a large language model: A large language model can be invoked, allowing query statements, table structures (fields, types, indexes, etc.), and statistical information (such as the number of rows in each table, the number of unique values, etc.) to be described in the hints. The large language model then generates candidate plans for expansion. Candidate plans are represented using hints; for OLAP analytical workloads, only the join order needs to be indicated. Candidate plans can also be expanded manually or through other experience.

[0188] (3) Model usage.

[0189] For a query statement to be executed, the query statement is analyzed to extract the query template, bind values, table names, and join fields. The query template group to which the query statement belongs is identified, and its number is obtained (for encoding the subsequent parameterized execution plan). The model is called with the bind values ​​and a set of candidate plans as input. The embedding vectors of the bind values ​​and each candidate plan are calculated, and the distances between the bind value vectors and all candidate plan vectors are calculated. Based on these distances, the candidate plans are ranked in descending order, and the highest-ranking candidate plan is selected as the optimal execution plan for the query statement. In practical applications, the optimal execution plan can be used directly to execute the query statement, or the hint corresponding to the optimal execution plan can be applied to the query optimization process.

[0190] (4) Model training.

[0191] The query templates are divided into several query template groups, and a model is trained for each query template group. For each query template in a query template group, a candidate plan set for each query template is generated in the manner described in (2) above, and the corresponding training data is constructed. The training data corresponding to a query template group are integrated into a training dataset, so that the model of the query template group is trained through the training dataset.

[0192] In (2) above, the query statement, candidate plan, and actual execution time are collected for each execution of the candidate plan. The execution time is used to determine the relative merits of each pair of queries (i.e., the relative merits of the candidate plans). The relative merits of each pair of queries can be represented by a two-bit map, called the winner bitmap, where a set bit indicates that the plan corresponding to that position is better. A training dataset consists of <query statement, two candidate plans, winner bitmap>.

[0193] The query statement and candidate plans are encoded into feature vectors: feature encoding is performed separately for the parameterized execution plan (specifically, the table and join field numbers in the execution plan) and the bind values. The list of bind values ​​uses a conventional encoding method. This generates feature vectors for the parameterized execution plan and bind value feature vectors, respectively.

[0194] Generate embedding vectors: Perform tree convolution on the execution plan vector, followed by dynamic pooling to obtain the embedding vectors. Then, use a feedforward neural network to embed the bound value vectors. This generates both the parameterized execution plan embedding vector and the bound value embedding vector.

[0195] Loss function: The loss function is represented by the average of the winner bitmap (true results and predicted results), and the model is trained based on the value of the loss function.

[0196] For example, the formula for the loss function is as follows:

[0197]

[0198] Where L is the value of the loss function, m represents the number of training data, and u t,i Let y be the true label of the first execution plan in the winner bitmap from the i-th training data. p,i The second one in the i-th training data

[0199] The actual label of the execution plan in the winner's bitmap The first execution plan for the i-th training data.

[0200] Predictions in the winner's bitmap For the second execution plan in the i-th training data, in the winner position

[0201] The prediction results are shown in the figure. and The distance d between the parameterized execution plan embedding vector and the bound value embedding vector is calculated, i.e. The same applies.

[0202] Model update: Retrain the model when a new template is discovered or when there are significant changes in business data.

[0203] By applying the above embodiments of this application, the model bloat problem in query optimization is solved. At the same time, by numbering the table and join fields, vector sparsity is reduced, training is accelerated and generalization ability is improved. Furthermore, in terms of expanding the candidate plan set, it is also considered to supplement it by combining a large language model, which can explore more DBA experience and improve the coverage of the candidate plan set.

[0204] The following description continues to illustrate the exemplary structure of the database query statement processing device 555 provided in the embodiments of this application as a software module. In some embodiments, such as... Figure 2As shown, the software module in the query statement processing device 555 of the database stored in the memory 550 may include: a first grouping module 5551, used to group multiple query templates of the database to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models; a first extraction module 5552, used to extract a first query template of the query statement from the query statement to be executed, and determine a first query template group from the multiple query template groups, wherein the first query template group is... The query template group includes the first query template; a first acquisition module 5553, used to acquire multiple execution plans associated with the first query template; a first prediction module 5554, used to, based on the query statement and any two execution plans among the multiple execution plans, call the machine learning model associated with the first query template group to make a prediction and obtain a prediction result, wherein the prediction result represents the performance comparison result of the arbitrary two execution plans; and a first determination module 5555, used to, based on the prediction result of any two execution plans among the multiple execution plans, determine the target execution plan for the query statement from the multiple execution plans.

[0205] In some embodiments, the first grouping module 5551 is further configured to obtain a plurality of database query statements for the database, wherein the plurality of database query statements are associated with a plurality of query templates; perform clustering processing on the plurality of database query statements to obtain a plurality of clusters, wherein each cluster includes at least two of the database query statements; and for each cluster, construct a query template group based on the query templates associated with at least two of the database query statements in the cluster.

[0206] In some embodiments, the first grouping module 5551 is further configured to obtain the query connection structure of each database query statement, wherein the query connection structure includes: multiple data tables queried by the database query statement and the connection relationship between the multiple data tables; based on the query connection structure of each database query statement, the multiple database query statements are clustered to obtain multiple clusters, wherein at least two database query statements included in each cluster have the same query connection structure.

[0207] In some embodiments, the first grouping module 5551 is further configured to obtain a preset grouping parameter when the number of query templates associated with at least two database query statements in the cluster is greater than a number threshold; and to group the query templates associated with multiple database query statements in the cluster according to the preset grouping parameter to obtain at least two query template groups.

[0208] In some embodiments, the first extraction module 5552 is further configured to perform syntax parsing on the query statement to be executed to obtain the constant elements and structure elements included in the query statement; replace the constant elements in the query statement with placeholders to obtain an initial query template; and perform standardization processing on the initial query template to obtain the first query template.

[0209] In some embodiments, the first acquisition module 5553 is further configured to perform the following processing for each query template in each query template group before acquiring the multiple execution plans associated with the first query template: acquiring M target query statements associated with the query template from multiple database query statements for the database, where M is an integer greater than 1; generating N statement candidate plans for each target query statement, and using the N statement candidate plans of each of the M target query statements as the M*N candidate plans associated with the query template, where N is an integer greater than 1; the first acquisition module 5553 is further configured to use the M*N candidate plans associated with the first query template in the multiple query templates as the multiple execution plans associated with the first query template.

[0210] In some embodiments, the first acquisition module 5553 is further configured to acquire the K data tables associated with the query template and the connection relationships between the K data tables, wherein K is an integer greater than 1; for each target query statement, the following processing is performed: based on the target query statement, determining the priority ranking probability of each data table, wherein the priority ranking probability is positively correlated with the degree to which the data table ranks first among the K data tables; based on the connection relationships and the priority ranking probabilities, performing N random permutation operations on the K data tables to obtain the ranking order of the K data tables output by each random permutation operation, wherein the ranking order output by each random permutation operation is different; for each random permutation operation, connecting the K data tables according to the ranking order output by the random permutation operation to obtain the statement candidate plan.

[0211] In some embodiments, the first acquisition module 5553 is further configured to extract data volume filtering conditions from the target query statement; filter each data table based on the data volume filtering conditions to obtain filtered data tables; for each data table, determine the priority arrangement probability of the data table based on the data volume of the filtered data table and the total data volume of the K filtered data tables; the first acquisition module 5553 is further configured to perform N random arrangement operations on the K filtered data tables based on the connection relationship and the priority arrangement probability to obtain the arrangement order of the K filtered data tables output by each random arrangement operation.

[0212] In some embodiments, the first acquisition module 5553 is further configured to acquire table information of the data table associated with the query template and acquire description information of the M target query statements; based on the table information and the description information, expand the M*N candidate plans associated with the query template to obtain H candidate plans associated with the query template, where H is an integer greater than M*N.

[0213] In some embodiments, the first acquisition module 5553 is further configured to perform the following processing for each of the query template groups: for each query template in the query template group, construct training data based on the M*N candidate plans associated with the query template, wherein the training data includes the target query statement and any two candidate plans from the M*N candidate plans, each training data is labeled with a label, the label representing the performance comparison result of the arbitrary two candidate plans; acquire the initial machine learning model associated with the query template group, and call the initial machine learning model to predict the training data to obtain a target prediction result, wherein the target prediction result represents the prediction comparison result of the performance of the arbitrary two candidate plans; train the initial machine learning model based on the difference between the target prediction result and the label to obtain the machine learning model associated with the query template group.

[0214] In some embodiments, the first prediction module 5554 is further configured to call the machine learning model associated with the first query template group and perform the following processing: extracting features from the query statement to obtain query statement features, and extracting features from any two execution plans among the plurality of execution plans to obtain two execution plan features; and making a prediction based on the query statement features and the two execution plan features to obtain the prediction result.

[0215] In some embodiments, the first prediction module 5554 is further configured to perform the following processing for each of the two execution plans: obtaining a plurality of data tables included in the execution plan and obtaining the connection fields between the plurality of data tables; determining the data table number of each data table and determining the field number of each connection field; performing feature extraction on each data table number and each field number of the execution plan to obtain number features, and using the number features as the execution plan features of the execution plan.

[0216] In some embodiments, the first extraction module 5552 is further configured to, when the new query template of the new query statement to be executed does not belong to the plurality of query template groups, determine a second query template group matching the new query statement from the plurality of query template groups, wherein the second query template group includes a second query template, and the similarity between the second query template and the new query template is greater than a similarity threshold; the first acquisition module 5553 is further configured to acquire a plurality of candidate plans associated with the second query template; the first prediction module 5554 is further configured to, based on the new query statement and any two candidate plans among the plurality of candidate plans, call the machine learning model associated with the second query template group to perform prediction and obtain an updated prediction result, wherein the updated prediction result characterizes the performance comparison result of the arbitrary two candidate plans; the first determination module 5555 is further configured to, based on the updated prediction result of any two candidate plans among the plurality of candidate plans, determine a target candidate plan for the new query statement from the plurality of candidate plans.

[0217] In some embodiments, the first determining module 5555 is further configured to perform a global sorting of the plurality of execution plans based on the prediction results of any two execution plans among the plurality of execution plans, to obtain a global sorting result; and to take the execution plan with the best performance indicated by the global sorting result as the target execution plan.

[0218] In some embodiments, the first determining module 5555 is further configured to, after determining the target execution plan for the query statement, execute the query statement using the target execution plan; or, convert the target execution plan into query optimizer instructions for the query statement and execute the query statement based on the query optimizer instructions.

[0219] This application embodiment also provides a database query statement processing apparatus, comprising: a second grouping module, configured to group multiple query templates of the database to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models; a second extraction module, configured to extract a first query template of the query statement from the query statement to be executed, and determine a first query template group from the multiple query template groups, wherein the first query template group includes the first query template; a second acquisition module, configured to acquire multiple execution plans associated with the first query template; a second prediction module, configured to call the machine learning model associated with the first query template group to determine the distance between each execution plan and the query statement; and a second determination module, configured to determine a target execution plan for the query statement from the multiple execution plans based on each distance.

[0220] It should be noted that the description of the apparatus embodiments in this application is similar to the description of the method embodiments described above, and has similar beneficial effects as the method embodiments, so it will not be repeated here. Technical details not covered in the database query statement processing apparatus provided in the embodiments of this application can be understood based on the description of the technical details in the above method embodiments.

[0221] This application also provides a computer program product, which includes computer-executable instructions or a computer program stored in a computer-readable storage medium. The processor of an electronic device reads the computer-executable instructions or computer program from the computer-readable storage medium and executes the computer-executable instructions or computer program, causing the electronic device to perform the database query statement processing method provided in this application embodiment.

[0222] This application also provides a computer-readable storage medium storing computer-executable instructions or computer programs. When the computer-executable instructions or computer programs are executed by a processor, the processor will execute the database query statement processing method provided in this application.

[0223] In some embodiments, the computer-readable storage medium may be a memory such as RAM, ROM, flash memory, magnetic surface memory, optical disk, or CD-ROM; or it may be a variety of devices including one or any combination of the above-mentioned memories.

[0224] In some embodiments, computer-executable instructions may take the form of programs, software, software modules, scripts, or code, written in any form of programming language (including compiled or interpreted languages, or declarative or procedural languages), and may be deployed in any form, including as stand-alone programs or as modules, components, subroutines, or other units suitable for use in a computing environment.

[0225] As an example, computer-executable instructions may, but do not necessarily, correspond to files in a file system. They may be stored as part of a file that holds other programs or data, for example, in one or more scripts in a Hyper Text Markup Language (HTML) document, in a single file dedicated to the program in question, or in multiple co-located files (e.g., files that store one or more modules, subroutines, or code sections).

[0226] As an example, computer-executable instructions can be deployed to execute on a single electronic device, or on multiple electronic devices located at one location, or on multiple electronic devices distributed across multiple locations and interconnected via a communication network.

[0227] The above description is merely an embodiment of this application and is not intended to limit the scope of protection of this application. Any modifications, equivalent substitutions, and improvements made within the spirit and scope of this application are included within the scope of protection of this application.

Claims

1. A method for processing database query statements, characterized in that, The method includes: Multiple query templates in the database are grouped to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models; From the query statement to be executed, extract the first query template of the query statement, and determine the first query template group from the plurality of query template groups, wherein the first query template group includes the first query template; Obtain multiple execution plans associated with the first query template; Based on the query statement and any two execution plans from the plurality of execution plans, the machine learning model associated with the first query template group is invoked to make a prediction and obtain a prediction result, wherein the prediction result represents the performance comparison result of the arbitrary two execution plans; Based on the prediction results of any two execution plans among the plurality of execution plans, a target execution plan for the query statement is determined from the plurality of execution plans.

2. The method as described in claim 1, characterized in that, The process of grouping multiple query templates in the database to obtain multiple query template groups includes: Obtain multiple database query statements for the database, wherein the multiple database query statements are associated with multiple query templates; The multiple database query statements are clustered to obtain multiple clusters, wherein each cluster includes at least two of the database query statements; For each cluster, a query template group is constructed based on the query template associated with at least two database query statements in the cluster.

3. The method as described in claim 2, characterized in that, The clustering process performed on the multiple database query statements yields multiple clusters, including: Obtain the query join structure for each of the database query statements, wherein the query join structure includes: multiple data tables queried by the database query statement, and the join relationships between the multiple data tables; Based on the query join structure of each database query statement, the multiple database query statements are clustered to obtain multiple clusters, wherein each cluster includes at least two database query statements that have the same query join structure.

4. The method as described in claim 2, characterized in that, The process of constructing the query template group based on the query templates associated with at least two database query statements in the cluster includes: When the number of query templates associated with at least two database query statements in the cluster is greater than the number threshold, a preset grouping parameter is obtained. According to the preset grouping parameters, the query templates associated with multiple database query statements in the cluster are grouped to obtain at least two query template groups.

5. The method as described in claim 1, characterized in that, Extracting the first query template of the query statement from the query statement to be executed includes: The query statement to be executed is parsed to obtain the constant elements and structure elements included in the query statement; Replace the constant elements in the query statement with placeholders to obtain the initial query template; The initial query template is standardized to obtain the first query template.

6. The method as described in claim 1, characterized in that, Before obtaining the multiple execution plans associated with the first query template, the method further includes: For each query template in each of the query template groups, the following processing is performed: From multiple database query statements targeting the database, obtain M target query statements associated with the query template, where M is an integer greater than 1; Generate N candidate statement plans for each of the M target query statements, and use the N candidate statement plans for each of the M target query statements as the M*N candidate plans associated with the query template, where N is an integer greater than 1; The step of obtaining multiple execution plans associated with the first query template includes: The M*N candidate plans associated with the first query template among the multiple query templates are used as multiple execution plans associated with the first query template.

7. The method as described in claim 6, characterized in that, The generation of N candidate query plans for each of the target query statements includes: Obtain the K data tables associated with the query template, and the connection relationships between the K data tables, where K is an integer greater than 1; For each of the target query statements, the following processing is performed: Based on the target query statement, the priority ranking probability of each data table is determined, and the priority ranking probability is positively correlated with the degree to which the data table ranks first among the K data tables; Based on the connection relationship and the priority order probability, N random permutation operations are performed on the K data tables to obtain the permutation order of the K data tables output by each random permutation operation, wherein the permutation order output by each random permutation operation is different; For each random permutation operation, the K data tables are joined according to the permutation order output by the random permutation operation to obtain the statement candidate plan.

8. The method as described in claim 7, characterized in that, Determining the priority ranking probability of each data table based on the target query statement includes: Extract data volume filtering conditions from the target query statement; Based on the data volume filtering conditions, each of the data tables is filtered to obtain the filtered data tables. For each of the data tables, the priority ranking probability of the data table is determined based on the amount of data in the filtered data table and the total amount of data in the K filtered data tables; The step of performing N random permutation operations on the K data tables based on the connection relationship and the priority permutation probability, to obtain the permutation order of the K data tables output by each random permutation operation, includes: Based on the connection relationship and the priority order probability, N random permutation operations are performed on the K filtered data tables to obtain the permutation order of the K filtered data tables output by each random permutation operation.

9. The method as described in claim 6, characterized in that, The method further includes: Obtain the table information of the data table associated with the query template, and obtain the description information of the M target query statements; Based on the table information and the description information, the M*N candidate plans associated with the query template are expanded to obtain H candidate plans associated with the query template, where H is an integer greater than M*N.

10. The method as described in claim 6, characterized in that, The method further includes: For each of the aforementioned query template groups, the following processing is performed: For each query template in the query template group, training data is constructed based on the M*N candidate plans associated with the query template. The training data includes the target query statement and any two candidate plans from the M*N candidate plans. Each training data is labeled with a label, which represents the performance comparison result of the arbitrary two candidate plans. Obtain the initial machine learning model associated with the query template group, and call the initial machine learning model to predict the training data to obtain the target prediction result, wherein the target prediction result represents the prediction comparison result of the performance of any two candidate plans; Based on the difference between the target prediction result and the label, the initial machine learning model is trained to obtain the machine learning model associated with the query template group.

11. The method as described in claim 1, characterized in that, The step involves calling the machine learning model associated with the first query template group to perform prediction based on the query statement and any two execution plans from the plurality of execution plans, and obtaining the prediction result, including: Invoke the machine learning model associated with the first query template group and perform the following processing: Feature extraction is performed on the query statement to obtain query statement features, and feature extraction is performed on any two execution plans among the multiple execution plans to obtain two execution plan features; The prediction result is obtained by making a prediction based on the query statement features and the two execution plan features.

12. The method as described in claim 11, characterized in that, The step of extracting features from any two execution plans among the plurality of execution plans to obtain two execution plan features includes: For each of the two execution plans, perform the following processing: Obtain the multiple data tables included in the execution plan, and obtain the connection fields between the multiple data tables; Determine the data table number for each of the data tables, and determine the field number for each of the connection fields; Feature extraction is performed on each of the data table numbers and each of the field numbers in the execution plan to obtain number features, and the number features are used as the execution plan features of the execution plan.

13. The method according to any one of claims 1-12, characterized in that, The method further includes: When the new query template of the new query statement to be executed does not belong to the plurality of query template groups, a second query template group matching the new query statement is determined from the plurality of query template groups, wherein the second query template group includes a second query template, and the similarity between the second query template and the new query template is greater than a similarity threshold; Retrieve multiple candidate plans associated with the second query template; Based on the new query statement and any two candidate plans from the plurality of candidate plans, the machine learning model associated with the second query template group is invoked to make a prediction and obtain an updated prediction result, wherein the updated prediction result represents the performance comparison result of the arbitrary two candidate plans; Based on the updated prediction results of any two candidate plans among the plurality of candidate plans, a target candidate plan for the new query statement is determined from the plurality of candidate plans.

14. The method according to any one of claims 1-12, characterized in that, The step of determining the target execution plan for the query statement from the plurality of execution plans based on the prediction results of any two execution plans includes: Based on the prediction results of any two execution plans among the plurality of execution plans, the plurality of execution plans are globally sorted to obtain a global sorting result; The execution plan with the best performance indicated by the global sorting result shall be taken as the target execution plan.

15. The method according to any one of claims 1-12, characterized in that, After determining the target execution plan for the query statement, the method further includes: The query statement is executed using the target execution plan. Alternatively, the target execution plan can be converted into query optimizer instructions for the query statement, and the query statement can be executed based on the query optimizer instructions.

16. A method for processing database query statements, characterized in that, The method includes: Multiple query templates in the database are grouped to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models; From the query statement to be executed, extract the first query template of the query statement, and determine the first query template group from the plurality of query template groups, wherein the first query template group includes the first query template; Obtain multiple execution plans associated with the first query template; The machine learning model associated with the first query template group is invoked to determine the distance between each execution plan and the query statement; Based on each of the distances, a target execution plan for the query statement is determined from the plurality of execution plans.

17. A database query statement processing apparatus, characterized in that, The device includes: The first grouping module is used to group multiple query templates in the database to obtain multiple query template groups, wherein each query template group includes at least two query templates, each query template group is associated with a machine learning model, and different query template groups are associated with different machine learning models; The first extraction module is used to extract a first query template from the query statement to be executed, and to determine a first query template group from the plurality of query template groups, wherein the first query template group includes the first query template; The first acquisition module is used to acquire multiple execution plans associated with the first query template; The first prediction module is used to call the machine learning model associated with the first query template group to make a prediction based on the query statement and any two execution plans among the plurality of execution plans, and obtain a prediction result, wherein the prediction result represents the performance comparison result of the arbitrary two execution plans; The first determining module is used to determine the target execution plan for the query statement from the plurality of execution plans based on the prediction results of any two execution plans among the plurality of execution plans.

18. An electronic device, characterized in that, The electronic device includes: Memory is used to store executable instructions for a computer; A processor, configured to execute computer-executable instructions stored in the memory, implements the method for processing a database query statement as described in any one of claims 1 to 16.

19. A computer-readable storage medium storing computer-executable instructions or a computer program, characterized in that, When the computer-executable instructions or computer program are executed by a processor, they implement the database query statement processing method according to any one of claims 1 to 16.

20. A computer program product comprising computer-executable instructions or a computer program, characterized in that, When the computer-executable instructions or computer program are executed by a processor, they implement the database query statement processing method according to any one of claims 1 to 16.

Images