Execution plan processing method and apparatus, electronic device, computer-readable storage medium, and computer program product

Abstract

The application provides an execution plan processing method and device, electronic equipment, a computer readable storage medium and a computer program product. The method comprises: obtaining a first execution plan, wherein the first execution plan is an execution plan for executing a query statement of a database; sending the first execution plan to an artificial intelligence model to enable the artificial intelligence model to execute the first execution plan in a copy of the database; obtaining performance index data of the first execution plan from the artificial intelligence model when the first execution plan is completed; and updating a plan baseline of the query statement based on the first execution plan in response to the performance index data of the first execution plan being better than performance index data of a second execution plan, wherein the plan baseline comprises the second execution plan. Through the application, plan evolution can be carried out more efficiently, thereby meeting changing business conditions and performance requirements.

Description

Technical Field

[0001] This application relates to the field of Internet technology, and in particular to an execution plan processing method, apparatus, electronic device, computer-readable storage medium, and computer program product. Background Technology

[0002] Schedule management is a database technique used to control the stability of query schedules. However, while ensuring schedule stability, constantly changing data and load conditions may limit the selection of better-performing schedules. Currently, the common solution in the database field is to use schedule evolution (including online and offline evolution schemes) to explore and validate new schedules, and then update the schedule management baseline with the better schedules. However, online evolution schemes require offloading production traffic, which can easily cause performance fluctuations and significantly impact the overall instance load. Offline evolution schemes require pre-allocated maintenance windows and configuration, and the availability of the entire instance within the maintenance window is also greatly affected, thus compromising the ability of schedule evolution. Summary of the Invention

[0003] This application provides an execution plan processing method, apparatus, electronic device, computer-readable storage medium, and computer program product, which can perform plan evolution more efficiently to meet ever-changing business conditions and performance requirements.

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

[0005] This application provides an execution plan processing method, including:

[0006] Obtain the first execution plan, wherein the first execution plan is an execution plan for executing the database query statement;

[0007] Send the first execution plan to the artificial intelligence model so that the artificial intelligence model executes the first execution plan in a copy of the database;

[0008] If the first execution plan is completed, the performance index data of the first execution plan is obtained from the artificial intelligence model;

[0009] In response to the fact that the performance metrics data of the first execution plan are better than the performance metrics data of the second execution plan, the plan baseline of the query statement is updated based on the first execution plan, wherein the plan baseline includes the second execution plan.

[0010] This application provides an execution plan processing apparatus, including:

[0011] The acquisition module is used to acquire a first execution plan, wherein the first execution plan is an execution plan for executing a query statement in the database;

[0012] A sending module is configured to send the first execution plan to an artificial intelligence model, so that the artificial intelligence model executes the first execution plan in a copy of the database;

[0013] The acquisition module is further configured to acquire performance index data of the first execution plan from the artificial intelligence model when the first execution plan is completed;

[0014] An update module is configured to update the plan baseline of the query statement based on the first execution plan in response to the performance metric data of the first execution plan being better than the performance metric data of the second execution plan, wherein the plan baseline includes the second execution plan.

[0015] This application provides an electronic device, including:

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

[0017] The processor, when executing computer-executable instructions stored in the memory, implements the execution plan processing method provided in the embodiments of this application.

[0018] This application provides a computer-readable storage medium storing computer-executable instructions, which, when executed by a processor, implement the execution plan processing method provided in this application.

[0019] This application provides a computer program product, including a computer program or computer executable instructions, which, when executed by a processor, implements the execution plan processing method provided in this application.

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

[0021] After obtaining the first execution plan (i.e., the unverified execution plan) for executing the database query, the performance of the first execution plan is verified by calling the capabilities of the artificial intelligence model. Then, the performance index data of the first execution plan returned by the artificial intelligence model is compared with the performance index data of the second execution plan already existing in the plan baseline of the query statement. When the performance index data of the first execution plan is better than that of the second execution plan, the plan baseline of the query statement is updated based on the first execution plan. In this way, the plan evolution and plan management can be decoupled through the artificial intelligence model, reducing the burden on the database in the production environment, while also enhancing the availability of plan management, enabling better and more efficient plan evolution, thereby meeting the ever-changing business conditions and performance requirements. Attached Figure Description

[0022] Figure 1 This is a schematic diagram of the architecture of the execution plan processing system 100 provided in an embodiment of this application;

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

[0024] Figure 3 This is a first flowchart illustrating the execution plan processing method provided in this application embodiment;

[0025] Figure 4 This is a second flowchart illustrating the execution plan processing method provided in the embodiments of this application;

[0026] Figure 5 This is a schematic diagram of the first process of the execution plan processing method provided in the embodiments of this application;

[0027] Figure 6 This is a schematic diagram of the second process of the execution plan processing method provided in the embodiments of this application. Detailed Implementation

[0028] 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.

[0029] 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.

[0030] It is understood that in the embodiments of this application, data such as user information are involved. When the embodiments of this application are applied to specific products or technologies, user permission or consent is required, and the collection, use and processing of related data must comply with relevant laws, regulations and standards.

[0031] In the following description, the terms “first, second, ...” are used merely to distinguish similar objects and do not represent a specific ordering of objects. It is understood that “first, second, ...” 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.

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

[0033] 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.

[0034] 1) Responding to: used to indicate the conditions or states on which the operation is performed depends. When the conditions or states on which it depends are met, one or more operations can 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.

[0035] 2) Structured Query Language (SQL): A database query and programming language used to access, query, update, and manage relational database systems. SQL is a high-level, non-procedural programming language that allows users to work on high-level data structures. It does not require users to specify how data is stored, nor does it require users to understand the specific data storage methods. Therefore, different database systems with completely different underlying structures can use the same SQL as an interface for data input and management. SQL statements can be nested, giving it great flexibility and powerful functionality.

[0036] 3) Execution Plan: Also known as the query plan or interpretation plan, or simply the plan, it describes the internal process of how a database executes an SQL query statement. In other words, the execution plan is a set of specific operational procedures and strategies for the database to execute SQL queries. It details how the database accesses table data, including which indexes to use, which table join method to employ (e.g., nested loop joins, hash joins, etc.), how to perform data filtering and sorting, etc., directly affecting the query execution efficiency.

[0037] 4) Plan Baseline: This is a mechanism in a database used to capture and store the execution plan for a specific SQL query. When a database executes an SQL query, it generates an execution plan that determines how data is retrieved and processed. Over time, due to changes in data distribution, updates to hardware configurations, or updates to statistics, the original execution plan may no longer be optimal. By creating a plan baseline, the database can ensure that it still uses the optimal execution plan when faced with these changes. In other words, a plan baseline is a method of recording the execution plan of an SQL query. It saves the execution plan of a specific SQL query and automatically applies these plans during subsequent executions. Thus, when the execution plan of an SQL query changes, the database automatically rolls back to the previously saved optimal plan, thereby improving query efficiency.

[0038] 5) SQL Plan Management (SPM): This is a database query optimization term referring to the management of optimization plans generated after SQL query optimization. It is often used to prevent inefficient plans from impacting the load. The purpose of plan management is to prevent execution plans not in the plan baseline from being executed.

[0039] 6) Plan Evolution: As data and business continue to change, the planning baseline may become outdated, requiring the exploration and validation of new execution plans and their establishment as the new planning baseline.

[0040] 7) Artificial Intelligence (AI) model: refers to a neural network model for a specific task built using technologies such as machine learning and deep learning. Examples include models based on the Transformer architecture, models based on Convolutional Neural Networks (CNN), models based on Recurrent Neural Networks (RNN), models based on Residual Networks (ResNet), and models based on Deep Neural Networks (DNN). This application does not specifically limit the type of artificial intelligence model.

[0041] Currently, for the plan evolution part of plan management, the relevant technologies provide the following two solutions:

[0042] 1) Online Evolution Solution: In the actual production environment, a portion of traffic is proactively incorporated into the planning evolution process. By using the feedback data from the performance verification of this portion of traffic, the current baseline is updated by comparing it with the optimal plan in the current baseline, thereby completing the planning evolution.

[0043] 2) Offline evolution solution: Reserve an operation and maintenance window. During off-peak business periods, proactively execute plans in batches within the plan set that need to be evolved, collect specific performance data, compare them with the best plan in the current baseline, update the current baseline, and thus complete the plan evolution.

[0044] In addition to the two plan evolution schemes mentioned above, in many cases, database administrators (DBAs) will discover certain performance issues, proactively intervene to find out if there is a better execution plan, then manually verify it through experience, and then choose to add a new baseline plan, or fix the plan as the highest priority plan based on the specific performance of an existing baseline plan.

[0045] However, during the implementation of the embodiments of this application, the applicant discovered that the online evolution solution has the disadvantage of requiring the diversion of production traffic. For example, it may be necessary to divert 10% of the traffic to the verification of the new plan, which could potentially cause performance fluctuations and thus significantly impact the overall instance load. The overall plan management function is designed to prevent performance fluctuations caused by plan fluctuations. Businesses using this technology are often sensitive to performance fluctuations, therefore, this evolution solution carries a risk to performance stability. The offline evolution solution, on the other hand, requires pre-reserved maintenance windows and advance configuration. Furthermore, the availability of the entire instance within the maintenance window is significantly affected. Additionally, if the maintenance window is set too infrequently, it cannot respond to changes in data distribution in a timely manner, thus compromising the plan evolution capability.

[0046] In addition to the two planning evolution schemes of the planning management technology itself mentioned above, the applicant also found that the traditional scheme of having DBAs discover, locate, verify, and resolve problems also wastes a lot of human resources and is difficult to solve all problems in a refined manner for large-scale business and database instances.

[0047] In other words, traditional SPM systems rely on human experience or built-in rules of the optimizer for plan fixation and evolution, which has the following significant drawbacks:

[0048] 1. Limited search space: The optimizer can only generate candidate plans based on preset rules and statistical information, and cannot actively explore non-rule-driven plans defined by Hint (a comment on SQL query statements that can convey instructions to the database optimizer to generate a specified execution plan) combinations (such as forced use of specific indexes, parallelism adjustment, etc.).

[0049] 2. High verification cost: Online evolution solutions require offloading production traffic, which may cause performance fluctuations; while offline evolution solutions require reserving an operation and maintenance window and cannot respond to changes in data distribution in real time.

[0050] 3. Low evolution efficiency: Manually enumerating hint combinations is inefficient and lacks a system performance verification mechanism, making it difficult to discover potentially better execution plans.

[0051] In view of this, embodiments of this application provide an execution plan processing method, apparatus, electronic device, computer-readable storage medium, and computer program product, which can better and more efficiently perform plan evolution, thereby meeting constantly changing business conditions and performance requirements. The electronic device provided in the embodiments of this application will be described below. The electronic device provided in the embodiments of this application can be implemented as a server or a terminal device.

[0052] In some embodiments, see Figure 1 , Figure 1 This is a schematic diagram of the architecture of the execution plan processing system 100 provided in this application embodiment, as shown below. Figure 1 As shown, the execution plan processing system 100 provided in this application embodiment includes: a database 200, an artificial intelligence model 300, and a standby machine 400. The database 200 and the artificial intelligence model 300, as well as the artificial intelligence model 300 and the standby machine 400, can be connected via a network, which can be a local area network (LAN), a wide area network (WAN), or a combination of both. A copy of the database 200 is stored in the standby machine 400. Thus, after receiving the first execution plan (i.e., the plan to be evolved, for example, execution plan A) sent by the database 200, the artificial intelligence model 300 can verify the performance of the plan to be evolved in the copy of the database 200 stored in the standby machine 400 and return the verification result to the database 200. The plan management module in the database 200 (…) Figure 1(Not shown in the image) After receiving the verification result returned by the artificial intelligence model 300, the performance indicator data of execution plan A can be compared with the performance indicator data of the second execution plan (e.g., execution plan B) already in the plan baseline of the query statement. If the performance indicator data of execution plan A is better than that of execution plan B, the plan management module of database 200 can update the plan baseline of the query statement based on execution plan A. For example, execution plan A can be added to the plan baseline of the query statement, or execution plan A can replace the existing execution plan B in the plan baseline of the query statement. In this way, the query statement can be executed using the better-performing execution plan A, thereby realizing the closed loop of plan evolution.

[0053] The structure of the electronic device provided in the embodiments of this application will be described below. Taking the electronic device as a server as an example, see... Figure 2 , Figure 2 This is a schematic diagram of the structure of the electronic device 500 provided in the embodiments of this application. Figure 2 The illustrated electronic device 500 includes at least one processor 510, a memory 540, and at least one network interface 520. The various components in the electronic device 500 are coupled together via a bus system 530. It is understood that the bus system 530 is used to implement communication between these components. In addition to a data bus, the bus system 530 also includes a power bus, a control bus, and a status signal bus. However, for clarity, ... Figure 2 The general labeled all buses as Bus System 530.

[0054] 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.

[0055] The memory 540 may be removable, non-removable, or a combination thereof. Exemplary hardware devices include solid-state storage, hard disk drives, optical disk drives, etc. The memory 540 may optionally include one or more storage devices physically located away from the processor 510.

[0056] The memory 540 may include volatile memory or non-volatile memory, or both. The non-volatile memory may be read-only memory (ROM), and the volatile memory may be random access memory (RAM). The memory 540 described in this application embodiment is intended to include any suitable type of memory.

[0057] In some embodiments, memory 540 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.

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

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

[0060] In some embodiments, the apparatus provided in this application can be implemented in software. Figure 2 An execution plan processing device 543 stored in memory 540 is shown. This device can be software in the form of programs and plug-ins, and includes the following software modules: an acquisition module 5431, a sending module 5432, an update module 5433, a receiving module 5434, a generation module 5435, a deduplication module 5436, a parsing module 5437, and an addition module 5438. These modules are logically linked and can therefore be arbitrarily combined or further split according to the functions implemented. It should be noted that... Figure 2 For ease of explanation, all the above modules are shown at once, but this should not be interpreted as excluding the implementation of the execution plan processing device 543, which may only include the acquisition module 5431, the sending module 5432, and the update module 5433. The functions of each module will be described below.

[0061] For example, see Figure 3 , Figure 3 This is a first flowchart illustrating the execution plan processing method provided in this application embodiment, which will be combined with... Figure 3 The steps shown are explained.

[0062] It should be noted that, Figure 3The entity executing the steps shown can be the database's planning management system (i.e., SPM system). Specifically, it can be the planning management module in the database. This planning management module can be implemented in software and / or hardware and can be configured in an electronic device, which can be a server or a terminal device.

[0063] In step 101, the first execution plan is obtained.

[0064] Here, the first execution plan is the execution plan used to execute the database query statement. For example, it can be an unvalidated execution plan for the query statement, a new execution plan generated by an artificial intelligence model for the query statement, or a new execution plan generated by the database optimizer for the query statement.

[0065] In some embodiments, the first execution plan can be generated by an artificial intelligence model. Step 101 can be implemented as follows: Send a query statement for the database and database metadata (e.g., describing the structure and organization of the data, such as table definitions, field types and constraints, indexes, etc.) to the artificial intelligence model, so that the artificial intelligence model generates the first execution plan based on the metadata and the query statement; receive the first execution plan returned by the artificial intelligence model; or, receive a first parameter returned by the artificial intelligence model to identify the first execution plan, and generate the first execution plan based on the first parameter. The first parameter may include one of the following: a plan identifier for the first execution plan (e.g., Plan ID), or key information of the first execution plan (e.g., a set of prompts describing the first execution plan). In other words, after generating the first execution plan, the artificial intelligence model can directly return the first execution plan (e.g., return the plan text of the first execution plan), or it can return the first parameter used to identify the first execution plan, thereby reducing the amount of data that needs to be transmitted.

[0066] For example, taking an SQL query (e.g., "SELECT * FROM users WHERE age > 25") as an example, after receiving the SQL query from the user, the database's execution plan management system (e.g., the database's execution plan module) can send the SQL query and database metadata (e.g., table structure, field types, index information) to the AI ​​model. The AI ​​model then generates a new execution plan (i.e., the first execution plan) based on the database metadata and the user's SQL query. For instance, after receiving the SQL query and database metadata from the execution plan module, the AI ​​model can first perform syntax and semantic analysis on the received SQL query to ensure correct statement structure and thus correctly understand the user's query intent. Then, the AI ​​model can utilize the database metadata (e.g., table and field information, indexes, and statistics) to determine how to efficiently execute the user's SQL query. This includes selecting appropriate indexes, data partitioning, and join strategies. Finally, the AI ​​model can generate a detailed execution plan based on the query optimization results. This plan describes all the operational steps required to execute the user-input SQL query statement, such as table scanning, data filtering, and table joins. After generating a new execution plan (i.e., the plan to be evolved) for the SQL query statement based on the received SQL query statement and the database metadata, the AI ​​model can directly return the new execution plan (e.g., the plan text of the new execution plan) to the database's plan management module. Alternatively, the AI ​​model can also return the plan identifier of the new execution plan to the database's plan management module, or it can extract key information from the new execution plan (e.g., a set of hints describing the new execution plan) and return the extracted key information to the database's plan management module. This embodiment does not specifically limit this approach. In this way, by leveraging the capabilities of the artificial intelligence model to generate the plan to be evolved, the efficiency of generating the plan to be evolved is improved, and potential better execution plans can also be discovered.

[0067] In other embodiments, following the above examples, when the first parameter is the plan identifier of the first execution plan, the above-mentioned generation of the first execution plan based on the first parameter can be achieved in the following way: query a pre-created mapping table based on the plan identifier of the first execution plan to obtain the execution plan corresponding to the plan identifier, which is used as the first execution plan. The mapping table may include the mapping relationship (or correspondence relationship) between different execution plans and different plan identifiers.

[0068] For example, taking execution plan A generated by the AI ​​model as the first execution plan, after generating execution plan A (i.e., the plan to evolve) based on the received SQL query statement and database metadata, the AI ​​model can return the plan identifier (i.e., the Plan ID) of execution plan A to the database's plan management module. Upon receiving the plan identifier of execution plan A returned by the AI ​​model, the database's plan management module can find execution plan A from a pre-created mapping table based on the plan identifier. This mapping table can include the mapping relationship between different execution plans and different plan identifiers. In other words, there is a correspondence between plan identifiers and specific execution plans in the database, allowing the database's plan management module to find the corresponding execution plan based on the plan identifier.

[0069] It should be noted that the scenario where the AI ​​model returns the plan identifier of the first execution plan to the database's plan management module requires that the first execution plan already exists in the database. For example, if the AI ​​model has previously sent the first execution plan to the database's plan management module, then when the AI ​​model generates the first execution plan again, it only needs to send the plan identifier of the first execution plan to the database's plan management module, thereby reducing the amount of data that needs to be transmitted.

[0070] In some embodiments, following the above example, when the first parameter is key information of the first execution plan, the above-mentioned generation of the first execution plan based on the first parameter can be achieved in the following way: inputting the key information of the first execution plan into the optimizer of the database, so that the optimizer generates the first execution plan based on the key information.

[0071] For example, taking execution plan A generated by the AI ​​model as the first execution plan, after generating execution plan A (i.e., the plan to be evolved) based on the received SQL query statement and database metadata, the AI ​​model can extract key information from execution plan A. For example, it can extract a set of hints describing execution plan A. Then, the AI ​​model can send the extracted key information of execution plan A (such as scan method, table join order, indexes used, etc.) to the database's plan management module. After receiving the key information of execution plan A sent by the AI ​​model, the database's plan management module can input the key information of execution plan A into the database optimizer, so that the database optimizer can generate execution plan A based on the key information of execution plan A. For example, the database optimizer can reconstruct execution plan A based on a set of hints describing execution plan A. In this way, by extracting and sending the key information of execution plan A, the amount of data that needs to be transmitted can be effectively reduced.

[0072] In other embodiments, the first parameter mentioned above may be carried in the plan generation result, wherein the plan generation result may include the first parameter and a second parameter for identifying the query statement corresponding to the first execution plan. The second parameter may include one of the following: a statement identifier of the query statement, the statement text of the query statement, or the template text of the query statement. That is, after generating the first execution plan, the artificial intelligence model can return two parameters to the database's plan management module. One parameter is used to identify the generated first execution plan, and the other parameter is used to identify the query statement corresponding to the first execution plan. In this way, after receiving the plan generation result returned by the artificial intelligence model, the database's plan management module can clearly understand which query statement the first execution plan in the plan generation result corresponds to. For example, when the database's plan management module sends multiple query statements to the artificial intelligence model at one time, the artificial intelligence model can generate at least one corresponding first execution (i.e., a plan to be evolved) for each query statement. Then, the artificial intelligence model can return multiple plan generation results to the database's plan management module, wherein each plan generation result includes two parameters. One parameter is used to identify the first execution plan included in the plan generation result, and the other parameter is used to identify the query statement corresponding to the first execution plan.

[0073] For example, taking execution plan A generated by the AI ​​model as the first execution plan, after generating execution plan A based on the received SQL query statement and database metadata, the AI ​​model can return not only the first parameter identifying execution plan A (i.e., the plan to be evolved) to the database's plan management module, but also the second parameter identifying the SQL query statement corresponding to execution plan A. Thus, the database's plan management module can determine which SQL query statement the currently received execution plan (i.e., execution plan A) corresponds to based on the second parameter. In other words, for each new execution plan, the AI ​​model returns two parameters to the database's plan management module: the first parameter identifies the newly generated execution plan, and the second parameter identifies the corresponding SQL query statement. Therefore, when multiple SQL queries are sent to the AI ​​model at once, and multiple new execution plans corresponding to these SQL queries are received simultaneously, the database's plan management module can clearly identify which execution plan corresponds to which SQL query statement, avoiding confusion.

[0074] It's important to note that after generating the first execution plan, the AI ​​model does not immediately verify its performance (this is because the AI ​​model is unsure if this execution plan is the one required by the database's plan management module), nor does it permanently store the first execution plan locally (this is because execution plan data is quite large, and permanent local storage would waste valuable storage space). Instead, it returns the generated first execution plan to the database's plan management module. In other words, the database's plan management module can use the AI ​​model to generate multiple first execution plans (i.e., multiple plans to be evolved) for query statements in batches, and then send some of these first execution plans (e.g., the remaining plans after deduplication) to the AI ​​model at an appropriate time, allowing the AI ​​model to verify the performance of the received first execution plans.

[0075] In some embodiments, following the above, when the number of first execution plans returned by the artificial intelligence model is multiple, the following processing can also be performed: modeling the multiple first execution plans respectively to obtain multiple directed acyclic graphs (DAGs) corresponding to the multiple first execution plans respectively; performing hash processing on the multiple directed acyclic graphs respectively to obtain multiple hash values ​​corresponding to the multiple directed acyclic graphs respectively; and performing deduplication processing on the multiple first execution plans based on the multiple hash values.

[0076] For example, consider 10 execution plans (e.g., 10 evolution plans corresponding to a SQL statement), denoted as execution plan 1 to execution plan 10. However, these 10 execution plans may overlap, leading to data redundancy. To address this, after receiving these 10 execution plans from the AI ​​model, the database's plan management module can first model each of them as a corresponding 10 directed acyclic graphs (i.e., a directed graph without loops). In this modeling process, each operation step in the execution plan, such as table scanning, data filtering, and table joining, is abstracted as a node in the graph, and the dependencies between operation steps are represented as directed edges. After obtaining the 10 directed acyclic graphs corresponding to the 10 execution plans, a graph hashing algorithm can be used to calculate a unique hash value for each directed acyclic graph, thus obtaining the corresponding hash values ​​for the 10 directed acyclic graphs. Figure 1 Each hash corresponds to 10 hash values. These hash values ​​can then be used as the plan identifier for the corresponding execution plan. For example, the directed acyclic graph (DAG) corresponding to execution plan 1 can be used as an identifier. Figure 1The hash value obtained by hashing (e.g., hash value 1) can be used as the plan identifier (i.e., Plan ID) for execution plan 1. Similarly, the directed acyclic graph (e.g., assuming it is a directed acyclic graph) corresponding to execution plan 2 can be used as the plan identifier (i.e., Plan ID). Figure 2 The hash value obtained by hashing the 10 execution plans (e.g., hash value 2) is used as the plan identifier for execution plan 2. Similarly, the hash value obtained by hashing the directed acyclic graph (DAG) corresponding to execution plan 10 (e.g., assuming it's DAG 10) is used as the plan identifier for execution plan 10. After obtaining the 10 plan identifiers corresponding to these 10 execution plans, deduplication can be performed based on the plan identifiers. That is, only execution plans with different plan identifiers are retained. For example, if the plan identifier of execution plan 1 is the same as the plan identifier of execution plan 5, it means that these two execution plans are the same, and only one of them can be retained. For example, execution plan 1 can be retained, and execution plan 5 can be deleted. In this way, redundant execution plans can be eliminated, thereby reducing the time and cost required for subsequent plan verification.

[0077] It should be noted that the above-mentioned graph hashing algorithms are techniques for converting images into fixed-length hash values. Examples include the Average Hash algorithm (AHash), which converts the input image to grayscale, then shrinks it to 8×8 pixels, calculates the average of all grayscale values, and then checks each pixel's grayscale value; if it is greater than the average, it is marked as 1, otherwise as 0, ultimately forming a 64-bit hash value. The Difference Hash algorithm (DHash) converts the input image to grayscale, shrinks it to 9×8 pixels, calculates the difference between each pixel and its leftmost pixel to form a difference matrix, calculates the average of the difference matrix, and finally compares each element of the difference matrix with the average; elements greater than the average are marked as 1, otherwise as 0, thus forming a 64-bit hash value. The Perceptual Hash algorithm (PHash) is similar to the Average Hash algorithm, but uses Discrete Cosine Transform (DCT) to extract the main components of the image, then quantizes the DCT coefficients, and finally generates a hash value.

[0078] In some embodiments, following the above example, the above-mentioned modeling of multiple first execution plans can be achieved in the following way to obtain multiple directed acyclic graphs corresponding to the multiple first execution plans: For each of the multiple first execution plans, the following processing is performed: the multiple execution steps included in the first execution plan are mapped to the corresponding multiple nodes; a directed edge is added between the two nodes corresponding to any two execution steps that have a dependency relationship to obtain a directed acyclic graph corresponding to the first execution plan, wherein the direction of the directed edge is from the node corresponding to the dependent execution step to the node corresponding to the dependent execution step.

[0079] For example, taking execution plan A from multiple first execution plans as an example, assuming execution plan A includes three execution steps: scanning the table, filtering data, and joining the table, these three execution steps can be mapped to three corresponding nodes. For example, the execution step "scanning the table" can be mapped to node 1, the execution step "filtering data" can be mapped to node 2, and the execution step "joining the table" can be mapped to node 3. At the same time, assuming there is a dependency relationship between the execution steps "scanning the table" and "filtering data", for example, the input of the execution step "filtering data" depends on the output of the execution step "scanning the table", a directed edge can be added between node 1 corresponding to the execution step "scanning the table" and node 2 corresponding to the execution step "filtering data". For example, a directed edge from node 1 to node 2 can be added between node 1 and node 2, thus obtaining a directed acyclic graph corresponding to execution plan A.

[0080] It should be noted that the process of modeling other execution plans into corresponding directed acyclic graphs is similar to the process of modeling execution plan A into a corresponding directed acyclic graph. The implementation of execution plan A can be referred to, and will not be repeated here in the embodiments of this application.

[0081] In other embodiments, following the above examples, the above deduplication of multiple first execution plans based on multiple hash values ​​can be achieved in the following way: multiple hash values ​​are used as multiple plan identifiers corresponding to the multiple first execution plans respectively; for each of the multiple first execution plans, when there are other first execution plans with the same plan identifier as the first execution plan, the other first execution plan is deleted.

[0082] For example, taking 10 execution plans as examples, let's say they are execution plans 1 to 10. After receiving these 10 execution plans from the AI ​​model, the database's plan management module can model each of these 10 execution plans into 10 corresponding directed acyclic graphs (DAGs). Then, a graph hashing algorithm can be used to calculate the unique hash value corresponding to each DAG, thus obtaining the unique hash value of each of the 10 DAGs. Figure 1Each execution plan has 10 corresponding hash values. Each hash value can then be used as the plan identifier for the corresponding execution plan. For example, the hash value obtained by hashing the directed acyclic graph corresponding to execution plan 1 can be used as the plan identifier for execution plan 1. Thus, 10 plan identifiers can be obtained for each of the 10 execution plans. Finally, the 10 execution plans can be deduplicated based on the plan identifiers. That is, for these 10 execution plans, only execution plans with different plan identifiers are kept. For example, if the plan identifiers of execution plan 1 and execution plan 4 are the same, it means that these two execution plans are the same, and only one of them can be kept. For example, only execution plan 1 can be kept and execution plan 4 can be deleted. In this way, redundant execution plans can be eliminated by hash deduplication, which effectively saves the time and cost required for subsequent plan verification.

[0083] In some embodiments, the first execution plan may also be generated by the database optimizer, and the above step 101 may be implemented in the following way: input the query statement for the database into the database optimizer so that the optimizer parses the received query statement into an abstract syntax tree (AST) and generates the first execution plan of the query statement based on the abstract syntax tree.

[0084] For example, after receiving a user-inputted SQL query, the database's plan management module can also input the received SQL query into the database optimizer. Upon receiving the SQL query, the optimizer first parses it into an internal representation, such as an abstract syntax tree (Abstract Syntax Tree). It then checks the syntax of the SQL query against the Abstract Syntax Tree, ensuring that all referenced tables, columns, and functions exist and are valid. Next, the optimizer generates a new execution plan based on the database's current state (including table structure, indexes, and statistics) and the characteristics of the SQL query. Specifically, after receiving the SQL query, the optimizer first converts it into an internal representation (e.g., an Abstract Syntax Tree), then identifies keywords such as SELECT, FROM, and WHERE, along with their corresponding table names, column names, and conditional expressions. After parsing, the optimizer generates multiple possible execution plans, each describing how to access data in tables, apply filtering conditions, and join multiple tables. For example, regarding table access order: if a query involves multiple tables, the optimizer will try different table join orders to find the optimal one; regarding index selection: the optimizer will evaluate all available indexes on the table and decide whether to use a particular index to speed up the query; regarding join methods: for multi-table queries, the optimizer will choose an appropriate join method (such as nested loop joins, hash joins, merge joins, etc.); regarding aggregation and sorting: if the query contains GROUP BY or ORDER BY clauses, the optimizer will decide how to efficiently execute these operations. In this way, the traditional approach of discovering new execution plans from the native database domain is retained, resulting in better compatibility.

[0085] In other embodiments, the first execution plan described above may also be pre-generated by the database optimizer. In this case, step 101 can be implemented as follows: obtaining key information of the first execution plan for the database query statement and generating the first execution plan based on the key information, wherein the key information is extracted from the first execution plan pre-generated by the database optimizer; or, obtaining the serialization result of the first execution plan for the database query statement and deserializing the serialization result to obtain the first execution plan, wherein the serialization result may be obtained by copying the memory structure of the first execution plan pre-generated by the database optimizer and serializing the copied memory result.

[0086] It should be noted that the memory structure of the first execution plan can include: 1) Query tree: This is the internal representation of the parsed SQL query statement, usually a tree structure, where each node represents a part of the SQL query statement, such as keywords like SELECT, FROM, JOIN, and WHERE; 2) Operators: These are the various operators contained in the first execution plan, such as scan, join, filter, sort, and aggregate, each corresponding to a part of the SQL query statement; 3) Data access path: This defines how the database accesses data, such as through index scan, full table scan, or hash join. After copying the memory structure of the first execution plan, the copied memory structure can be serialized into a binary byte stream, thereby effectively reducing the data volume.

[0087] For example, taking execution plan A, pre-generated by the database optimizer, as the first execution plan, in practical applications, to save storage space and facilitate maintenance, execution plan A is often not saved directly. Instead, reproduction information is pre-generated for execution plan A. The amount of data in this reproduction information is smaller than the amount of data in execution plan A, so only the reproduction information needs to be saved. Later, when execution plan A is needed, this reproduction information can be used to reproduce execution plan A. Specifically, after the database optimizer generates execution plan A, key information of execution plan A can be captured through plan capture. For example, a set of hints describing execution plan A can be extracted. Then, when execution plan A is needed, the key information of execution plan A can be obtained, and execution plan A can be reproduced based on this key information. Alternatively, the memory structure of execution plan A can be copied through plan cloning, and the copied memory structure can be serialized, for example, the memory structure of execution plan A can be converted into a binary byte stream. Therefore, when execution plan A is needed later, the byte stream obtained after serialization can be deserialized to obtain the memory structure of execution plan A. Then, execution plan A can be reconstructed based on the memory structure of execution plan A. This can greatly save storage space and facilitate the maintenance of the generated execution plan.

[0088] It should be noted that serialization refers to the process of converting the state of an object into a storable or transmittable format. In this embodiment, the memory structure of the first execution plan can be converted into a continuous byte stream (or other standard format text), which can then be written to a disk file, sent to a remote location over a network, or correctly parsed by other processes or devices. Deserialization is the reverse process of serialization, which refers to the process of restoring the received byte stream or other standard format text back into the memory structure of the first execution plan. For example, the received byte stream or text can be parsed according to a protocol, and then field information can be extracted from the parsing results to restore the memory structure of the first execution plan.

[0089] In addition, it should be noted that in order to distinguish different execution plans, the execution plans can also be hashed to obtain the corresponding hash values. That is, when multiple execution plans are generated, different execution plans can be distinguished by hash encoding.

[0090] In step 102, a first execution plan is sent to the artificial intelligence model so that the artificial intelligence model executes the first execution plan in a copy of the database.

[0091] Here, the AI ​​model can be connected to a backup machine, where a copy of the current database can run. For example, all content of the current database can be synchronized to the backup machine, thus obtaining a copy of the current database. In this way, after receiving the first execution plan from the database's planning module, the AI ​​model can execute the first execution plan on the database copy, thereby obtaining the performance metrics data of the first execution plan.

[0092] In some embodiments, see Figure 4 , Figure 4 This is a second flowchart illustrating the execution plan processing method provided in the embodiments of this application, as shown below. Figure 4 As shown, Figure 3 Step 102 shown can be achieved through Figure 4 The implementation of steps 1021 and 1022 shown will be combined with Figure 4 The steps shown are explained.

[0093] In step 1021, a plan evolution task is generated based on the query statement, the first execution plan, and the performance metrics of the first execution plan that need to be verified by the artificial intelligence model.

[0094] Here, performance metrics may include central processing unit (CPU) time, disk input / output (I / O) time, memory usage, etc.

[0095] In some embodiments, taking the first execution plan as an example, which is the execution plan A generated by the AI ​​model (i.e. the plan to be evolved), the database's plan management module can generate a plan evolution task after receiving the execution plan A returned by the AI ​​model. The plan evolution task may include the execution plan A (i.e. the unverified execution plan), the SQL query statement corresponding to the execution plan A, and the performance indicators of the execution plan A that need to be verified by the AI ​​model, such as CPU time, running time, disk I / O time, etc.

[0096] In step 1022, a plan evolution task is sent to the artificial intelligence model through the calling interface of the artificial intelligence model, so that the artificial intelligence model executes the query statement in the copy of the database according to the first execution plan, and after the first execution plan is completed, the performance index data of the first execution plan that needs to be verified by the artificial intelligence model is obtained.

[0097] In some embodiments, following the above example, after the database's plan management module generates a corresponding plan evolution task based on the execution plan A returned by the AI ​​model, the corresponding SQL query statement of execution plan A, and the performance metrics of execution plan A that need to be verified by the AI ​​model, the module can send the generated plan evolution task to the AI ​​model through the AI ​​model's calling interface, such as a specific port or Internet Protocol (IP) address. Upon receiving the plan evolution task sent by the database's plan management module, the AI ​​model can parse the received plan evolution task to obtain the execution plan A, the corresponding SQL query statement of execution plan A, and the performance metrics of execution plan A that need to be verified by the AI ​​model. Then, the AI ​​model can execute the SQL query statement according to execution plan A in a copy of the current database running on a standby machine (for example, the current database can be synchronized to a standby machine connected to the AI ​​model, thus obtaining a copy of the current database on the standby machine). After completing execution plan A, the AI ​​model retrieves the performance metric data of execution plan A that needs to be verified by the AI ​​model from the database copy. For example, after completing execution plan A, the AI ​​model can obtain the performance metrics data of execution plan A through built-in tools in the database replica (e.g., built-in tools in the database replica can automatically record the performance metrics data of execution plan A after execution, so the AI ​​model can read the performance metrics data of execution plan A from the built-in tools). Alternatively, the AI ​​model can obtain the performance metrics data of execution plan A through third-party tools (e.g., third-party tools can be embedded in the database replica, so that after execution plan A is completed, the performance metrics data of execution plan A can be recorded through the inserted third-party tools). Or, it can also obtain the performance metrics data of execution plan A through system detection tools; this embodiment does not specifically limit this approach. In this way, by calling the capabilities of the artificial intelligence model to verify the performance of the evolving plan, the plan evolution and plan management are decoupled, reducing the burden on the database in the production environment, reducing the manpower input of database operation and maintenance personnel and database administrators, while also enhancing the availability of plan management and improving the efficiency and capability of plan evolution.

[0098] It should be noted that, in order to ensure the accuracy of the verification results, the artificial intelligence model can also verify the first execution plan multiple times and return the average result of the multiple verifications to the plan management module of the database. In other words, the performance index data of the above-mentioned execution plan A can be the average performance index data of multiple verifications (such as average runtime, average memory usage, and average disk I / O duration, etc.). This application embodiment does not specifically limit this.

[0099] In step 103, if the first execution plan is completed, the performance index data of the first execution plan is obtained from the artificial intelligence model.

[0100] In some embodiments, following the above example, step 103 can be implemented as follows: upon completion of the first execution plan, a task result set sent by the artificial intelligence model is received by detecting the call interface. The task result set may include the task identifier of the plan evolution task and the performance index data of the first execution plan that needs to be verified by the artificial intelligence model.

[0101] For example, continuing from the above example, taking the execution plan A generated by the AI ​​model based on the first execution plan as an example, after the AI ​​model completes the verification, the database's plan management module can detect the task result set returned by the AI ​​model through a specific port, or it can directly read the task result set (i.e., the evolution result) returned by the AI ​​model through a socket. The task result set can include the task identifier of the plan evolution task sent by the database's plan management module to the AI ​​model, as well as the performance index data corresponding to a series of performance indicators of the execution plan A that needs to be verified by the AI ​​model.

[0102] It should be noted that a socket is an abstraction of an endpoint for bidirectional communication between application processes on different hosts on a network. A socket represents one end of inter-process communication on the network, providing a mechanism for application-layer processes to exchange data using network protocols. In terms of its position, a socket connects to the application process above and the network protocol stack below, serving as the interface for applications to communicate via network protocols. In the technical solution provided in this application's embodiments, a socket can be used as the interface for communication between the database's plan management module and the artificial intelligence model.

[0103] In other embodiments, following the above, after receiving the task result set sent by the artificial intelligence model through the detection call interface, the following processing can also be performed: based on the task identifier included in the task result set, determine the planned evolution task corresponding to the task identifier; based on the multiple performance indicators of the first execution plan that needs to be verified by the artificial intelligence model included in the planned evolution task, parse the multiple performance indicator data included in the task result set one by one to obtain the performance indicator data corresponding to each performance indicator.

[0104] For example, continuing from the previous section, after receiving the task result set returned by the AI ​​model, the database's plan management module can first determine, based on the task identifiers included in the task result set, which execution plan within the current task evolution corresponds to the multiple performance indicator data. After identifying the corresponding task evolution based on the task identifiers in the task result set, the database's plan management module can obtain the multiple performance indicators that need to be verified by the AI ​​model within that task evolution. Then, it can parse and store each performance indicator data in the task result set based on these indicators, thereby obtaining the performance indicator data corresponding to each performance indicator. In other words, the database's plan management module can parse the performance indicator data in the task result set one by one based on the performance indicators included in the task evolution to obtain the performance indicator data corresponding to each performance indicator.

[0105] In step 104, in response to the fact that the performance metrics data of the first execution plan is better than the performance metrics data of the second execution plan, the plan baseline of the query statement is updated based on the first execution plan.

[0106] Here, the plan baseline of the query statement may include a second execution plan, that is, the second execution plan is the execution plan in the plan baseline of the query statement, that is, the second execution plan is the execution plan that has been verified.

[0107] In some embodiments, after obtaining the first execution plan for the query statement against the database, the first execution plan can be added to the plan baseline of the query statement, and the status of the first execution plan can be set to the unaccepted state (i.e., indicating that the first execution plan is an unverified execution plan). The above-mentioned update of the plan baseline of the query statement based on the first execution plan can be achieved in the following way: the status of the first execution plan in the plan baseline of the query statement is updated from the unaccepted state to the accepted state.

[0108] For example, taking execution plan A generated by the AI ​​model as the first execution plan, after receiving execution plan A returned by the AI ​​model, the database's plan management module can add execution plan A to the plan baseline of the corresponding SQL query statement (e.g., SQL A) and set the status of execution plan A to "unaccepted" to indicate that execution plan A is an unverified execution plan. Next, the database's plan management module can send execution plan A to the AI ​​model for verification and receive the verification results returned by the AI ​​model. The verification results include performance indicator data for the performance metrics of execution plan A that require AI model verification. Subsequently, the database's plan management module can compare the performance metrics data of execution plan A with the performance metrics data of the existing execution plan (i.e., the second execution plan, for example, execution plan B, and execution plan B is in the accepted state, meaning execution plan B is a validated execution plan) in the plan baseline of the SQL query statement corresponding to execution plan A. If the performance metrics data of execution plan A is better than those of execution plan B, for example, if the runtime of execution plan A is shorter than that of execution plan B, it means that the performance of execution plan A is better than that of the existing execution plan B. Then, the status of execution plan A in the plan baseline of the SQL query statement can be updated from unaccepted to accepted. In this way, the SQL query statement can be executed according to the better execution plan A, thereby realizing the closed loop of plan evolution.

[0109] In other embodiments, the above-mentioned update of the query statement's plan baseline based on the first execution plan can also be achieved by adding the first execution plan to the query statement's plan baseline, or by replacing the second execution plan in the query statement's plan baseline with the first execution plan.

[0110] For example, taking execution plan A as the first execution plan and execution plan B as the second execution plan, where execution plan A and execution plan B are execution plans for the same SQL query statement (e.g., SQL A), and execution plan B is a validated execution plan, then when the performance metrics of execution plan A are better than those of execution plan B—for example, the runtime of execution plan A is shorter than that of execution plan B, or the memory usage of execution plan A is less than that of execution plan B—execution plan A can be added to the plan baseline of the SQL query statement (in this case, the execution plans in the plan baseline are stateless, meaning that the execution plan added to the plan baseline is a validated execution plan). Thus, the SQL query statement can be executed according to the more performant execution plan A. Alternatively, execution plan B in the plan baseline of the SQL query statement can be replaced with execution plan A; this embodiment does not specifically limit this. For example, if the plan baseline of the SQL query allows multiple execution plans to exist simultaneously, then if the performance metrics of execution plan A are determined to be better than those of the existing execution plan B, execution plan A can also be added to the plan baseline of the SQL query. If the plan baseline of the SQL query only allows one execution plan, then if the performance metrics of execution plan A are determined to be better than those of the existing execution plan B in the plan baseline, execution plan A can replace the existing execution plan B in the plan baseline of the SQL query. In this way, the SQL query can be executed according to the better execution plan A, thereby further improving query efficiency.

[0111] The following will describe an exemplary application of the embodiments of this application in a real-world application scenario.

[0112] To address the problems of limited search space, high verification costs, and low evolution efficiency in related technologies, this application provides an execution plan processing method that fully utilizes the capabilities of artificial intelligence to solve several significant shortcomings of traditional SPM systems in plan evolution. Specifically, this application uses plan management technology as its foundation and replaces traditional plan evolution technology with AI capabilities, thereby further improving plan management functions and enhancing business stability.

[0113] In some embodiments, see Figure 5 , Figure 5 This is a schematic diagram of the first process of the execution plan processing method provided in the embodiments of this application, as shown below. Figure 5As shown, the technical solution provided in this application mainly includes the following three key technical points: generating a plan to be evolved (i.e., an unverified execution plan, corresponding to the first execution plan mentioned above, which could be a new execution plan generated by the native database optimizer or a new execution plan generated by an AI model), connecting to the AI ​​plan evolution interface, and obtaining and applying the evolution results. The technical solution provided in this application focuses on how to integrate general AI capabilities and combine them with plan management for plan evolution. Therefore, the following technical point descriptions also focus on how plan management itself connects to AI models for plan evolution, decoupling the dependence on different AI models. Any AI model that can complete the corresponding task can be connected to the plan management provided in this application for plan evolution. The three key technical points are explained in detail below.

[0114] (I) Generating the Evolution Plan

[0115] In some embodiments, the evolution plan can be generated in two ways. For example, it can rely on the new execution plan that the native database optimizer may generate, thus retaining the traditional plan management approach for discovering new execution plans in the native database domain, thereby achieving better compatibility. Furthermore, to save storage space, after obtaining a new execution plan, it can be processed through plan capture (i.e., by capturing key information of the new execution plan, such as extracting a set of hints describing the plan) or plan cloning (including copying, serializing, and deserializing the execution plan's memory structure), and then different execution plans can be distinguished by a unified hash encoding of all plan information. Additionally, some new execution plans can be recommended by an AI model. For example, the meta-information imported into the AI ​​model's database, along with the SQL query statement that requires the recommended execution plan, can be used to obtain the evolution plan recommended by the AI ​​model. In other words, this application embodiment provides an interface for importing evolution plans from AI models in plan management. Through the above two parts, the entry point for evolution plans in plan management is unified. The advantage of this is that it allows the use of general plan management capabilities to control the number of evolution plans, while also incorporating the traditional plan management framework.

[0116] The process of controlling the number of evolution plans is explained in detail below.

[0117] For example, execution plans (i.e., plans to be evolved) generated by AI models may contain a large number of duplicates, resulting in data redundancy. To address this issue, embodiments of this application can model multiple execution plans generated by the AI ​​model into corresponding directed acyclic graphs (DAGs). In this modeling process, each operation step in the execution plan, such as scanning tables, filtering data, and joining tables, is abstracted as a node in the graph, and the dependencies between operation steps are represented as directed edges in the graph. Then, a unique hash value can be calculated for each DAG using a graph hashing algorithm, and this hash value is used as the plan identifier (i.e., Plan ID) for the corresponding execution plan. Finally, deduplication can be performed based on the Plan ID, retaining only execution plans with different Plan IDs. This method typically eliminates more than 70% of redundant plans, significantly reducing the number of execution plans that need to be verified by the AI ​​model, thus lowering verification costs and time.

[0118] (II) AI Plan Evolution Interface Integration

[0119] In some embodiments, after obtaining the evolution plan, the plan can be sent to the AI ​​model for performance verification. An interface can also be provided to allow the AI ​​model to write the verification results back to the plan management module. Specifically, the following interfaces can be provided:

[0120] a)(AI model call) Adds an unvalidated execution plan (i.e., a plan to be evolved) to the specified SQL query statement;

[0121] b) (Planning Management Module Call) Send a plan evolution task to the AI ​​model. For example, the planning management module can generate a plan evolution task, which can include all information of the SQL query statement that needs to be evolved, the unverified execution plan, and the performance metrics that need to be verified and returned by the AI ​​model. Then the plan evolution task can be sent to the AI ​​model for verification.

[0122] c)(AI Model Invocation) sends a task result set, which may include a unique identifier for the corresponding planned evolution task, as well as all performance metrics data that need to be verified and returned by the AI ​​model.

[0123] In summary, this part mainly clarifies several defined protocols and then implements interaction through fixed interfaces, specifically including:

[0124] 1)INSERT_UNVERIFIED_PLAN([$SQL_ID, $SQL_TEXT, $SQL_DIGEST_TEXT], [$PLAN_ID, $PLAN_HINT, $PLAN_TEXT])

[0125] Here, $SQL_ID is a unique identifier for a SQL query statement, and databases typically have a mapping between $SQL_ID and specific SQL text; $SQL_TEXT is the specific SQL text, so it can be passed directly; $SQL_DIGEST_TEXT is the SQL template text, which uses "?" to replace constant parameters in the SQL text, and can generally be used to represent a type of SQL; $PLAN_ID is also a unique identifier for an execution plan, and databases typically have a mapping between $PLAN_ID and specific execution plans; $PLAN_HINT uses a set of optimization hints to constrain the generation of the execution plan, thereby controlling the optimizer to generate the desired target execution plan; $PLAN_TEXT is the specific plan information text (i.e., the execution plan's plan text), which may have different serialization representations in different databases, requiring support for the serialization and deserialization of the plan text.

[0126] In summary, the interface mainly consists of two parameters: the first parameter identifies the specified SQL query statement, and the second parameter identifies the specified SQL optimization plan (i.e., the evolution plan of the SQL query statement).

[0127] 2)DELIVER_EVOLVE_TASK_TO_AI($AI_connector, $EVOLVE_TASK_ID, $EVOLVE_TASK, $REQUEST_PERF_INDICATORS)

[0128] Here, `$AI_connector` is an abstract set of connectors that interact with the AI ​​model. For example, when the AI ​​model exposes services based on Transmission Control Protocol (TCP) / Internet Protocol (IP), it is represented as an IP:port tuple. For Hypertext Transfer Protocol (HTTP) / RESTful interfaces (an architectural style based on the HTTP protocol used to design interfaces for web services, which implements communication between clients and servers by defining a set of specifications and constraints), it is constructed as a complete service endpoint. This parameter can be instantiated using the factory pattern to create connector objects for the corresponding protocols, including Model Context Protocol (MCP). The extension of the Protocol; $EVOLVE_TASK_ID is a unique identifier for a planned evolution task (i.e., the third parameter $EVOLVE_TASK). After being sent to the AI ​​model, this parameter is used to uniquely identify the planned evolution task when the AI ​​model verifies and returns the result; $EVOLVE_TASK is a task that needs to be planned and evolved, and its abstract encapsulated members are all the information passed into interface a); $REQUEST_PERF_INDICATORS is a set of performance verification data required by the database. It abstracts a series of performance data that reflect SQL verification. Generally, it can include information such as the number of verification executions, average running time, total running time, average CPU time, total CPU time, average IO time, and total IO time.

[0129] 3)RECEIVE_EVOLVE_RESULT($PORT,$EVOLVE_TASK_ID,$PERF_INDICATORS)

[0130] Here, $PORT is the port used by the database to detect the received evolution task results, and can generally also be read directly through a socket; $EVOLVE_TASK_ID is a unique identifier of the corresponding planned evolution task sent by interface b) to the AI ​​model. Through this parameter, the database's planning management module can confirm which planned evolution task needs to be populated with information, corresponding to the performance indicator passed in the third parameter; $PERF_INDICATORS is a series of performance indicator data sent by interface b) to the AI ​​model for verification and feedback. It is parsed and stored one by one according to the previously sent $REQUEST_PERF_INDICATORS indicators.

[0131] (III) Obtaining and applying evolution results

[0132] In some embodiments, after obtaining the evolution results, the performance index data of the plan to be evolved can be compared with the performance index data of the existing execution plan. If the performance index data of the plan to be evolved is better than the performance index data of the existing execution plan, the plan to be evolved can be taken as the optimal plan, thus completing the closed loop of plan evolution and the plan management evolution is successful. Afterwards, the plan to be evolved will be used as the benchmark.

[0133] For example, see Figure 6 , Figure 6 This is a schematic diagram of the second process of the execution plan processing method provided in the embodiments of this application, as shown below. Figure 6 As shown, for a specified SQL query statement (e.g.) Figure 6 The SQL A shown in the figure is used to generate the evolution plan corresponding to the SQL query statement (e.g., Figure 6 After implementing Plan A as shown, Plan A can be added to the plan baseline of SQL A, and its status can be set to Unaccepted to indicate that Plan A is an unverified execution plan. Next, Plan A can be sent to the AI ​​model to validate its performance. After obtaining the evolution results returned by the AI ​​model, the performance metrics data of Plan A can be compared with the existing execution plans in the plan baseline of SQL A (corresponding to the second execution plan mentioned above, for example, which could be...). Figure 6 If the performance metrics of Plan A are compared with those of Plan 1, and Plan A is better than Plan 1, then the status of Plan A in the plan baseline of SQL A can be updated from unaccepted to accepted to indicate that Plan A has been verified. Then, SQL A can be executed according to Plan A.

[0134] It should be noted that, for Figure 6 The plan evolution process of SQLB shown is the same as that of SQLA, and can be implemented by referring to the plan evolution process of SQLA. This application embodiment will not repeat the details here. In other words, the technical solution provided by this application embodiment expands the inference architecture of traditional SPM, better integrates the capabilities of AI models, and replaces the original traditional database plan evolution method. Specifically, the database plan management module can perform plan evolution by calling the AI ​​model, and can achieve a complete plan evolution closed loop based on the evolution results returned by the AI ​​model. This breaks the traditional plan evolution scheme in database plan management, innovatively using the capabilities of artificial intelligence for plan evolution, enabling better and more efficient plan evolution to meet constantly changing business conditions and performance requirements.

[0135] In summary, the execution plan processing method provided in this application has the following beneficial effects:

[0136] The technical solution provided in this application overcomes the limitations of traditional database plan management in terms of plan evolution. It innovatively uses an AI model for plan evolution, decoupling plan evolution from plan management through the AI ​​model. This reduces the burden on the database in the production environment, lessens the manpower required for database operations and maintenance personnel and DBAs, and enhances the availability of plan management as well as the efficiency and capability of plan evolution. Furthermore, the technical solution provided in this application deeply integrates some functions of traditional databases with the AI ​​model, helping AI empower traditional business systems. In the future, it will also allow traditional business processes to rapidly iterate and update along with the rapid development of the AI ​​model.

[0137] The following description continues to illustrate the exemplary structure of the execution plan processing apparatus 543 provided in the embodiments of this application as a software module. In some embodiments, such as Figure 2 As shown, the software modules stored in the execution plan processing device 543 of the memory 540 may include: an acquisition module 5431, a sending module 5432, and an update module 5433.

[0138] The acquisition module 5431 is used to acquire a first execution plan, wherein the first execution plan is an execution plan for executing a query statement in the database; the sending module 5432 is used to send the first execution plan to the artificial intelligence model so that the artificial intelligence model executes the first execution plan in a copy of the database; the acquisition module 5431 is also used to acquire performance index data of the first execution plan from the artificial intelligence model when the first execution plan is completed; the update module 5433 is used to update the plan baseline of the query statement based on the first execution plan in response to the performance index data of the first execution plan being better than the performance index data of the second execution plan, wherein the plan baseline includes the second execution plan.

[0139] In some embodiments, the sending module 5432 is further configured to send a query statement for the database and metadata of the database to the artificial intelligence model, so that the artificial intelligence model generates a first execution plan based on the metadata and the query statement; the execution plan processing device 543 further includes a receiving module 5434 and a generating module 5435, wherein the receiving module 5434 is configured to receive the first execution plan returned by the artificial intelligence model; or, receive a first parameter returned by the artificial intelligence model to identify the first execution plan; the generating module 5435 is configured to generate the first execution plan based on the first parameter, wherein the first parameter includes one of the following: a plan identifier of the first execution plan, or key information of the first execution plan.

[0140] In some embodiments, when the first parameter is a plan identifier of the first execution plan, the generation module 5435 is further configured to query a pre-created mapping table based on the plan identifier to obtain the execution plan corresponding to the plan identifier as the first execution plan, wherein the mapping table includes the mapping relationship between different execution plans and different plan identifiers; when the first parameter is key information of the first execution plan, the generation module 5435 is further configured to input the key information of the first execution plan into the optimizer of the database so that the optimizer generates the first execution plan based on the key information.

[0141] In some embodiments, the first parameter is carried in the plan generation result and returned. The plan generation result includes the first parameter and a second parameter for identifying the query statement corresponding to the first execution plan. The second parameter includes one of the following: the statement identifier of the query statement, the statement text of the query statement, and the template text of the query statement.

[0142] In some embodiments, the execution plan processing device 543 further includes a deduplication module 5436, which is used to perform the following processing when the number of first execution plans returned by the artificial intelligence model is multiple: modeling the multiple first execution plans respectively to obtain multiple directed acyclic graphs corresponding to the multiple first execution plans respectively; performing hash processing on the multiple directed acyclic graphs respectively to obtain multiple hash values ​​corresponding to the multiple directed acyclic graphs respectively; and performing deduplication processing on the multiple first execution plans based on the multiple hash values.

[0143] In some embodiments, the deduplication module 5436 is further configured to perform the following processing for each of the plurality of first execution plans: mapping the plurality of execution steps included in the first execution plan to the plurality of corresponding nodes; adding a directed edge between the two nodes corresponding to any two execution steps that have a dependency relationship, to obtain a directed acyclic graph corresponding to the first execution plan, wherein the direction of the directed edge is from the node corresponding to the dependent execution step to the node corresponding to the dependent execution step.

[0144] In some embodiments, the deduplication module 5436 is further configured to use multiple hash values ​​as multiple plan identifiers corresponding to multiple first execution plans respectively; for each of the multiple first execution plans, when there are other first execution plans with the same plan identifier as the first execution plan, delete the other first execution plan.

[0145] In some embodiments, the acquisition module 5431 is further configured to input a query statement for the database into the database optimizer, so that the optimizer parses the query statement into an abstract syntax tree and generates a first execution plan for the query statement based on the abstract syntax tree.

[0146] In some embodiments, the acquisition module 5431 is further configured to acquire key information of the first execution plan of the query statement for the database, and generate the first execution plan based on the key information, wherein the key information is extracted from the first execution plan pre-generated by the database optimizer; or, acquire the serialization result of the first execution plan of the query statement for the database, and deserialize the serialization result to obtain the first execution plan, wherein the serialization result is obtained by copying the memory structure of the first execution plan pre-generated by the database optimizer, and serializing the copied memory structure.

[0147] In some embodiments, the generation module 5435 is further configured to generate a plan evolution task based on the query statement, the first execution plan, and the performance metrics of the first execution plan that need to be verified by the artificial intelligence model; the sending module 5432 is further configured to send the plan evolution task to the artificial intelligence model through the calling interface of the artificial intelligence model, so that the artificial intelligence model executes the query statement in the copy of the database according to the first execution plan, and after completing the first execution plan, obtains the performance metric data of the performance metrics of the first execution plan that need to be verified by the artificial intelligence model.

[0148] In some embodiments, the receiving module 5434 is further configured to receive a task result set sent by the artificial intelligence model by detecting the calling interface when the first execution plan is completed, wherein the task result set includes the task identifier of the planned evolution task and the performance index data of the first execution plan that needs to be verified by the artificial intelligence model.

[0149] In some embodiments, the execution plan processing device 543 further includes a parsing module 5437, which performs the following processing after the receiving module 5434 receives the task result set sent by the artificial intelligence model through a detection call interface: based on the task identifier included in the task result set, determines the plan evolution task corresponding to the task identifier; based on the multiple performance indicators of the first execution plan that need to be verified by the artificial intelligence model included in the plan evolution task, parses the multiple performance indicator data included in the task result set one by one to obtain the performance indicator data corresponding to each performance indicator.

[0150] In some embodiments, the execution plan processing apparatus 543 further includes an adding module 5438, which is used to add the first execution plan to the plan baseline of the query statement after the obtaining module 5431 obtains the first execution plan for the query statement to the database, and set the status of the first execution plan to an unaccepted state; the updating module 5433 is also used to update the status of the first execution plan in the plan baseline of the query statement from an unaccepted state to an accepted state.

[0151] In some embodiments, the update module 5433 is further configured to add a first execution plan to the plan baseline of the query statement, or to replace the second execution plan in the plan baseline of the query statement with the first execution plan.

[0152] It should be noted that the description of the apparatus in this application embodiment is similar to the description of the method embodiment above, and has similar beneficial effects as the method embodiment, therefore, it will not be repeated. For any technical details not covered in the execution plan processing apparatus provided in this application embodiment, please refer to... Figure 3 ,or Figure 4 The meaning is understood in accordance with the description of any of the accompanying drawings.

[0153] This application provides a computer program product, which includes a computer program or computer-executable instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer-executable instructions from the computer-readable storage medium and executes the computer-executable instructions, causing the computer device to perform the execution plan processing method described above in this application.

[0154] This application provides a computer-readable storage medium storing computer-executable instructions. When these computer-executable instructions are executed by a processor, they cause the processor to execute the execution plan processing method provided in this application. For example, ... Figure 3 ,or Figure 4 The execution plan processing method is shown.

[0155] In some embodiments, the computer-readable storage medium may be a memory such as FRAM, ROM, PROM, EPROM, EEPROM, 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.

[0156] In some embodiments, executable instructions may take the form of a program, software, software module, script, 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 a standalone program or as a module, component, subroutine, or other unit suitable for use in a computing environment.

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

[0158] 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 execution plans, characterized in that, The method includes: Obtain the first execution plan, wherein the first execution plan is an execution plan for executing the database query statement; Send the first execution plan to the artificial intelligence model so that the artificial intelligence model executes the first execution plan in a copy of the database; If the first execution plan is completed, the performance index data of the first execution plan is obtained from the artificial intelligence model; In response to the fact that the performance metrics data of the first execution plan are better than the performance metrics data of the second execution plan, the plan baseline of the query statement is updated based on the first execution plan, wherein the plan baseline includes the second execution plan.

2. The method according to claim 1, characterized in that, The process of obtaining the first execution plan includes: Send a query statement for the database and metadata of the database to the artificial intelligence model, so that the artificial intelligence model generates a first execution plan based on the metadata and the query statement; Receive the first execution plan returned by the artificial intelligence model; or, The system receives a first parameter returned by the artificial intelligence model to identify the first execution plan, and generates the first execution plan based on the first parameter, wherein the first parameter includes one of the following: the plan identifier of the first execution plan, or the key information of the first execution plan.

3. The method according to claim 2, characterized in that, When the first parameter is the plan identifier of the first execution plan, generating the first execution plan based on the first parameter includes: Based on the plan identifier, a pre-created mapping table is queried to obtain the execution plan corresponding to the plan identifier, which is used as the first execution plan. The mapping table includes the mapping relationship between different execution plans and different plan identifiers. When the first parameter is key information of the first execution plan, generating the first execution plan based on the first parameter includes: The key information of the first execution plan is input into the optimizer of the database, so that the optimizer generates the first execution plan based on the key information.

4. The method according to claim 2, characterized in that, The first parameter is carried in the plan generation result and returned. The plan generation result includes the first parameter and a second parameter for identifying the query statement corresponding to the first execution plan. The second parameter includes one of the following: the statement identifier of the query statement, the statement text of the query statement, and the template text of the query statement.

5. The method according to claim 2, characterized in that, When the number of the first execution plans returned by the artificial intelligence model is multiple, the method further includes: Model each of the first execution plans to obtain multiple directed acyclic graphs corresponding to each of the first execution plans; The multiple directed acyclic graphs are hashed respectively to obtain multiple hash values ​​corresponding to the multiple directed acyclic graphs; The multiple first execution plans are deduplicated based on the multiple hash values.

6. The method according to claim 5, characterized in that, The step of modeling multiple first execution plans to obtain multiple directed acyclic graphs corresponding to each of the multiple first execution plans includes: For each of the multiple first execution plans, perform the following processing: The first execution plan includes multiple execution steps that are mapped to corresponding multiple nodes; Add a directed edge between the two nodes corresponding to any two execution steps that have a dependency relationship to obtain a directed acyclic graph corresponding to the first execution plan, wherein the direction of the directed edge is from the node corresponding to the dependent execution step to the node corresponding to the dependent execution step.

7. The method according to claim 5, characterized in that, The process of deduplicating multiple first execution plans based on the multiple hash values ​​includes: The plurality of hash values ​​are used as multiple plan identifiers corresponding to the plurality of first execution plans respectively; For each of the multiple first execution plans, if there is another first execution plan with the same plan identifier as the first execution plan, the other first execution plan is deleted.

8. The method according to claim 1, characterized in that, The process of obtaining the first execution plan includes: A query statement for the database is input into the database optimizer, which parses the query statement into an abstract syntax tree and generates a first execution plan for the query statement based on the abstract syntax tree.

9. The method according to claim 1, characterized in that, The process of obtaining the first execution plan includes: Obtain key information of the first execution plan, and generate the first execution plan based on the key information, wherein the key information is extracted from the first execution plan pre-generated by the optimizer in the database; or, Obtain the serialization result of the first execution plan, and perform deserialization processing on the serialization result to obtain the first execution plan. The serialization result is obtained by copying the memory structure of the first execution plan pre-generated by the optimizer of the database, and then performing serialization processing on the copied memory structure.

10. The method according to claim 1, characterized in that, Sending the first execution plan to the artificial intelligence model so that the artificial intelligence model executes the first execution plan in a copy of the database includes: Based on the query statement, the first execution plan, and the performance metrics of the first execution plan that need to be verified by the artificial intelligence model, a plan evolution task is generated; The AI ​​model's API call interface sends the planned evolution task to the AI ​​model, so that the AI ​​model executes the query statement in the database copy according to the first execution plan, and after completing the first execution plan, obtains the performance index data of the first execution plan that needs to be verified by the AI ​​model.

11. The method according to claim 10, characterized in that, The step of obtaining performance metric data of the first execution plan from the artificial intelligence model upon completion of the first execution plan includes: Upon completion of the first execution plan, the task result set sent by the artificial intelligence model is received by detecting the calling interface. The task result set includes the task identifier of the plan evolution task and the performance index data of the first execution plan that needs to be verified by the artificial intelligence model.

12. The method according to claim 11, characterized in that, After receiving the task result set sent by the artificial intelligence model by detecting the calling interface, the method further includes: Based on the task identifiers included in the task result set, determine the planned evolution task corresponding to the task identifier; Based on the multiple performance metrics of the first execution plan that need to be verified by the artificial intelligence model, which are included in the plan evolution task, the multiple performance metric data included in the task result set are parsed one by one to obtain the performance metric data corresponding to each performance metric.

13. The method according to any one of claims 1 to 12, characterized in that, After obtaining the first execution plan, the method further includes: Add the first execution plan to the plan baseline of the query statement, and set the status of the first execution plan to an unaccepted state; The step of updating the plan baseline of the query statement based on the first execution plan includes: The status of the first execution plan in the plan baseline of the query statement is updated from the unaccepted status to the accepted status.

14. The method according to any one of claims 1 to 12, characterized in that, The step of updating the plan baseline of the query statement based on the first execution plan includes: Add the first execution plan to the plan baseline of the query statement, or replace the second execution plan in the plan baseline of the query statement with the first execution plan.

15. An execution plan processing apparatus, characterized in that, The device includes: The acquisition module is used to acquire a first execution plan, wherein the first execution plan is an execution plan for executing a query statement in the database; A sending module is configured to send the first execution plan to an artificial intelligence model, so that the artificial intelligence model executes the first execution plan in a copy of the database; The acquisition module is further configured to acquire performance index data of the first execution plan from the artificial intelligence model when the first execution plan is completed; An update module is configured to update the plan baseline of the query statement based on the first execution plan in response to the performance metric data of the first execution plan being better than the performance metric data of the second execution plan, wherein the plan baseline includes the second execution plan.

16. An electronic device, characterized in that, include: Memory is used to store executable instructions for a computer; A processor, when executing computer-executable instructions stored in the memory, implements the execution plan processing method according to any one of claims 1 to 14.

17. A computer-readable storage medium storing computer-executable instructions, characterized in that, When the computer-executable instructions are executed by the processor, they implement the execution plan processing method according to any one of claims 1 to 14.

18. A computer program product comprising a computer program or computer-executable instructions, characterized in that, When the computer program or computer-executable instructions are executed by the processor, the execution plan processing method according to any one of claims 1 to 14 is implemented.

Images