Data query method and apparatus, device, medium, and program product
By generating the first execution plan and obtaining plan hints through a pre-trained plan generation model, the problem of a surge in the number of execution plan enumerations caused by the increase in database complexity is solved, thereby improving database query efficiency and response speed.
Patent Information
- Application Number
- PCT/CN2026/090519
- Authority / Receiving Office
- WO · WO
- Patent Type
- Applications
- Current Assignee / Owner
- Priority Date
- 2025-05-13
- Filing Date
- 2026-04-14
- Publication Date
- 2026-11-19
Smart Images

Figure CN2026090519_19112026_PF_FP_ABST
Abstract
Description
Data query methods, devices, equipment, media and program products
[0001] This application claims priority to Chinese Patent Application No. 202510615421.3, filed on May 13, 2025, entitled “Data Query Method, Apparatus, Device, Medium and Program Product”, the entire contents of which are incorporated herein by reference. Technical Field
[0002] This application relates to the field of computer technology, and in particular to a data query method, apparatus, device, medium, and program product. Background Technology
[0003] An execution plan is an operational scheme generated by the database optimizer for a given query statement, which typically includes the specific query operations used when executing the given query statement in the database.
[0004] In related technologies, during database runtime, a cost-based optimizer can be used to generate an execution plan. After the query statement is input into the optimizer, the optimizer uses the database statistics to enumerate multiple candidate execution plans, then selects the candidate execution plan with the lowest execution time from the enumerated candidate execution plans, and finally uses the candidate execution plan as the final execution plan.
[0005] However, as the complexity of the database increases, the number of candidate execution plans that the cost-based optimizer needs to enumerate increases significantly, thereby increasing the time spent analyzing the execution plans and resulting in lower execution efficiency of the query statements. Summary of the Invention
[0006] This application provides a data query method, apparatus, device, medium, and program product. The technical solution provided by this application includes the following aspects.
[0007] According to one aspect of the embodiments of this application, a data query method is provided, executed by a computer device, the method comprising:
[0008] A first execution plan for a first query statement is generated using a plan generation model. The first execution plan includes a first query operation corresponding to the first query statement. The plan generation model is a machine learning model used to generate execution plans.
[0009] If the first execution plan meets the preset execution requirements, obtain the plan prompt information corresponding to the first execution plan. The plan prompt information is used to prompt the operation method of the first query operation.
[0010] During the operation of the database, based on the first query statement and the plan prompt information, a second execution plan for the first query statement is determined, and the second execution plan includes at least one second query operation corresponding to the first query statement;
[0011] Execute the at least one second query operation in the database to obtain the query result of the first query statement.
[0012] According to one aspect of the embodiments of this application, a data query apparatus is provided, the apparatus comprising:
[0013] The first processing module is used to generate a first execution plan for a first query statement through a plan generation model. The first execution plan includes a first query operation corresponding to the first query statement. The plan generation model is a machine learning model used to generate the execution plan.
[0014] The first processing module is used to obtain plan prompt information corresponding to the first execution plan when the first execution plan meets the preset execution requirements. The plan prompt information is used to prompt the operation method of the first query operation.
[0015] The second processing module is used to determine a second execution plan for the first query statement based on the first query statement and the plan prompt information during the operation of the database. The second execution plan includes at least one second query operation corresponding to the first query statement.
[0016] The second processing module is used to execute the at least one second query operation in the database to obtain the query result of the first query statement.
[0017] According to one aspect of the embodiments of this application, a computer device is provided, the computer device including a processor and a memory, the memory storing a computer program, the computer program being loaded and executed by the processor to implement the above-described data query method.
[0018] According to one aspect of the embodiments of this application, a computer-readable storage medium is provided, wherein a computer program is stored in the computer-readable storage medium, and the computer program is loaded and executed by a processor to implement the above-described data query method.
[0019] According to one aspect of the embodiments of this application, a computer program product is provided, the computer program product including a computer program stored in a computer-readable storage medium, and a processor reading from the computer-readable storage medium and executing the computer program to implement the above-described data query method.
[0020] The beneficial effects of the technical solutions provided in this application include at least the following:
[0021] A first execution plan for a first query statement is generated using a pre-trained plan generation model, and then the corresponding plan hint information is obtained. During database runtime, a second execution plan is quickly generated and executed by combining the first query statement and the existing plan hint information to obtain the query results. Compared to related technologies that use a cost-based optimizer to generate execution plans during database runtime, this application, on the one hand, uses an AI model (i.e., a plan generation model) to generate the first execution plan, and its corresponding plan hint information ensures that the second execution plan generated during database runtime can refer to the optimization strategy of the first execution plan, thereby guaranteeing the quality of the execution plan. On the other hand, this application generates the first execution plan using a plan generation model before database runtime and represents the first execution plan through plan hint information. During database runtime, the system can directly obtain the analysis results of the AI model (i.e., the first execution plan) based on the plan hint information, avoiding the AI inference process during database runtime. This improves the generation efficiency of the execution plan while ensuring the quality of the execution plan generation, thereby improving data query efficiency.
[0022] The plan prompts clearly define the operation method of the first query operation in the first execution plan. This allows the database optimizer to quickly determine the appropriate execution scheme for the first query statement without having to enumerate a massive number of candidate execution plans at runtime. This avoids the problems of excessively long execution plan analysis time and excessive runtime computing power overhead caused by the surge in the number of candidate execution plans when the database complexity increases. As a result, the overall response time of the query statement is significantly shortened, and the query execution efficiency and running performance of the database are significantly improved. This is especially suitable for scenarios with high requirements for query response speed, such as large-scale complex database systems, high-concurrency data query business, and terminal devices with limited computing power. Attached Figure Description
[0023] Figure 1 is a schematic diagram of a computer system provided in an exemplary embodiment of this application;
[0024] Figure 2 is a flowchart of a data query method provided in an exemplary embodiment of this application;
[0025] Figure 3 is a schematic diagram of a data query method provided in an exemplary embodiment of this application;
[0026] Figure 4 is a flowchart of a data query method provided by another exemplary embodiment of this application;
[0027] Figure 5 is a flowchart of a data query method provided in yet another exemplary embodiment of this application;
[0028] Figure 6 is a flowchart of a data query method provided in another exemplary embodiment of this application;
[0029] Figure 7 is a schematic diagram of a data query method provided by another exemplary embodiment of this application;
[0030] Figure 8 is a structural block diagram of a data query apparatus provided in an exemplary embodiment of this application;
[0031] Figure 9 is a structural block diagram of a computer device provided in an exemplary embodiment of this application. Detailed Implementation
[0032] It should be noted that the data collection and processing in this application should strictly comply with the requirements of relevant national laws and regulations, obtain the informed consent or separate consent of the personal information subject, and carry out subsequent data use and processing within the scope of laws and regulations and the authorization of the personal information subject.
[0033] An execution plan is the operational scheme generated by a database optimizer for a given query statement, typically including the specific query operations performed when executing the given query in the database. In related technologies, database optimizers include rule-based optimizers and cost-based optimizers. Rule-based optimizers generate execution plans based on a set of predefined, fixed rules. Cost-based optimizers select the optimal plan by estimating the cost of different execution plans, mainly consisting of cardinality estimation, cost modeling, and plan enumeration. Cost-based optimizers utilize database statistics to enumerate multiple candidate execution plans, and then select the candidate plan with the lowest execution time as the final execution plan. However, rule-based optimizers rely on predefined, fixed rules to generate execution plans, making them unable to adapt to dynamic changes in the database, thus leading to suboptimal or even incorrect execution plans. Cost-based optimizers have extremely high requirements for the accuracy of statistical information. If the statistical information is outdated or incomplete, it will lead to incorrect subsequent cost estimation and result in the selection of a suboptimal plan. In addition, as the complexity of the database increases, the number of candidate execution plans that the cost-based optimizer needs to enumerate will increase significantly, which will greatly increase the time spent analyzing the execution plans and result in low execution efficiency of the query statements.
[0034] Based on this, this application provides a data query method. A first execution plan for a first query statement is generated using a pre-trained plan generation model, and then the corresponding plan hint information is obtained. During database runtime, a second execution plan is quickly generated and executed by combining the first query statement and the existing plan hint information to obtain the query results. Compared to traditional optimizers in related technologies, on the one hand, in this application, the first execution plan is generated by an AI model, and its corresponding plan hint information ensures that the second execution plan generated during database runtime can refer to the optimization strategy of the first execution plan, thereby guaranteeing the quality of the execution plan. On the other hand, this application generates the first execution plan using a plan generation model before database runtime and represents the first execution plan through plan hint information. During database runtime, the system can directly obtain the analysis results of the AI model based on the plan hint information, avoiding the AI inference process during database runtime. This improves the generation efficiency of the execution plan while ensuring its quality, thereby improving data query efficiency.
[0035] The computer system provided in this application is described below.
[0036] Figure 1 is a schematic diagram of a computer system provided in an exemplary embodiment of this application. This computer system can implement a system architecture for a data query method. The computer system includes a terminal 110 and a server 120. The terminal 110 is connected to the server 120 via a wireless network or a wired network.
[0037] Terminal 110 can be an electronic device such as a mobile phone, tablet computer, vehicle terminal (vehicle system), wearable device, PC (Personal Computer), virtual reality (VR) device, augmented reality (AR) device, etc. A client for the first application can be installed and run on terminal 110. This first application is an application that provides data query functions; this application is not limited in its form. Furthermore, this application is not limited in its form, including but not limited to Apps (Applications), mini-programs, etc., installed on terminal 110, and can also be in web page form. Server 120 can be a standalone physical server, a server cluster or distributed system composed of multiple physical servers, a cloud server providing basic cloud computing services, or a node in a blockchain system. Server 120 can be the backend server of the aforementioned first application, used to provide backend services to the client of the first application.
[0038] Optionally, server 120 runs a database management system, such as MySQL (My Structured Query Language). This database management system manages the database, and the client of the first application can connect to it. The database management system can receive database query requests from the client of the first application and return the query results. Alternatively, the database management system can also run on terminal 110; this application does not limit its use in this regard.
[0039] The data query method provided in this application can be executed by a computer device, which refers to an electronic device with data computing, processing, and storage capabilities. As shown in Figure 1, the data query method can be executed by a terminal 110 (e.g., by a database management system installed and running on the terminal 110), by a server 120, or by the terminal 110 and the server 120 interacting and cooperating to execute the data query method. This application does not limit the scope of the method.
[0040] The following example illustrates how terminal 110 and server 120 interact and cooperate to execute a data query method.
[0041] (1) Terminal 110 sends a database query request to server 120.
[0042] (2) The database management system 121 in server 120 parses the database query request and obtains the first query statement for the database.
[0043] (3) Server 120 generates the first execution plan for the first query statement through the plan generation model.
[0044] The first execution plan includes the first query operation corresponding to the first query statement, wherein the plan generation model is a pre-trained machine learning model used to generate the execution plan.
[0045] (4) If the first execution plan meets the preset execution requirements, the server 120 obtains the plan prompt information corresponding to the first execution plan.
[0046] The plan prompt message is used to guide you through the operation method for the first query.
[0047] For example, before the database runs, server 120 analyzes the SQL statement (i.e., the first query statement) through AI optimizer (i.e., plan generation model) to obtain the first execution plan. If the first execution plan meets the preset execution requirements (e.g., the execution time is less than the preset time), the first query operation in it is represented by the first HINT information (i.e., plan hint information); then the first HINT information is added to the OUTLINE table of the database management system.
[0048] (5) During the operation of the database, the server 120 determines the second execution plan of the first query statement based on the first query statement and the plan prompt information.
[0049] The second execution plan includes at least one second query operation corresponding to the first query statement. Optionally, the at least one second query operation includes the first query operation.
[0050] In some embodiments, a first query statement and plan hints are input into the optimizer to obtain a second execution plan. Optionally, the optimizer is a conventional optimizer as described above.
[0051] (6) Server 120 executes the second execution plan in the database (that is, executes at least one second query operation) and obtains the query result of the first query statement.
[0052] For example, during database runtime, the OUTLINE table is first queried based on the SQL statement. Since the OUTLINE table stores the first hint information of the SQL statement, this first hint information can be added to the SQL statement. Then, the SQL statement with the added first hint information is input into the traditional optimizer, which outputs a second execution plan. The first hint information serves as a constraint for the traditional optimizer, influencing the generation and selection process of the second execution plan. For instance, if the first hint information specifies that the traditional optimizer should choose an index scan for a given query operation, then the traditional optimizer will need to choose an index scan for the given query operation when generating the second execution plan. After generating the second execution plan, the database executes it to obtain the query results.
[0053] In some embodiments, after obtaining the query results, the server 120 can send the query results back to the terminal 110.
[0054] Those skilled in the art will understand that the number of terminals 110 described above can be more or less. For example, there may be only one terminal 110, or there may be dozens or hundreds of terminals 110, or even more. This application does not limit the number or type of terminals 110.
[0055] Next, the flow of the data query method provided in the embodiments of this application will be described.
[0056] Figure 2 is a flowchart of a data query method provided in an embodiment of this application. The method is executed by a computer device, which may be the terminal 110 and / or server 120 shown in Figure 1. The method includes the following steps 210 to 240.
[0057] Step 210: Generate the first execution plan for the first query statement using the plan generation model.
[0058] The first query statement refers to a query statement targeting a database. A query statement is an instruction used to retrieve, manipulate, or manage data from a database. Depending on the type of database, the syntax and functionality of query statements differ.
[0059] Optionally, the first query statement can be implemented as an SQL (Structured Query Language) statement (or a relational database query statement). SQL is a standard programming language used to manage and manipulate relational databases. For example, SQL statements can be used to manage and manipulate relational databases through relational database management systems (such as MySQL, Oracle, SQL Server, etc.).
[0060] Optionally, the first query statement can also be implemented as a query statement of a non-relational database, such as a CQL (Cassandra Query Language) statement in Cassandra, or a command in Redis (such as GET, HGET). This application does not limit this, and mainly uses the SQL statement as an example for illustration.
[0061] Optionally, the database includes, but is not limited to, at least one of relational databases, non-relational databases, and distributed databases.
[0062] An execution plan is a detailed sequence of operation steps generated to execute a query statement. Optionally, a single execution plan includes at least one query operation. The execution plan determines how the database retrieves data and returns the retrieval results. Optionally, the execution plan consists of a series of operators in a specific order. The operators include, but are not limited to: (1) Table scan: a full table scan or index scan used to obtain raw data. (2) Index lookup: determining data rows through an index. (3) Filtering: applying WHERE conditions to filter data. (4) Join: merging data from multiple tables; joins include nested loop joins, hash joins, merge joins, etc. (5) Grouping and aggregation: applying aggregate functions after grouping data. (6) Sorting: sorting data by a specified column. (7) Deduplication: removing duplicate rows. It should be noted that the above examples of operators are merely illustrative and are not intended to limit the scope of this application. Optionally, in the execution plan, a single query operation is defined by at least one operator.
[0063] The first execution plan includes at least one first query operation corresponding to the first query statement, and the first query operation is defined by at least one operator. Optionally, the first execution plan contains at least two first query operations.
[0064] Among them, the plan generation model is a pre-trained machine learning model used to generate execution plans.
[0065] Optionally, the model type for plan generation includes, but is not limited to, at least one of contrastive models, reinforcement learning models, and diffusion models. For example, a contrastive model distinguishes between effective and ineffective execution plans (such as execution plans meeting abnormal execution conditions as described below) through contrastive learning, thereby generating an execution plan that meets the execution objective (e.g., execution time less than a preset time). A reinforcement learning model can train an agent through a reward mechanism to generate execution plans that meet the execution objective (e.g., execution time less than a preset time). A diffusion model generates execution plans that meet the execution objective (e.g., execution time less than a preset time) through a progressive denoising process. It should be noted that the above examples of plan generation models are merely illustrative and are not intended to limit the scope of this application.
[0066] In some embodiments, the plan generation model can be implemented as an end-to-end model. The database and a first query statement are input into the plan generation model to obtain a first execution plan. Exemplarily, the database information includes metadata such as table structure, index information, and statistical information (e.g., histograms, data distribution); the plan generation model is an end-to-end neural network model, including an encoder and a decoder. The encoder extracts the syntax tree features of the first query statement and the metadata features of the database, and the decoder directly generates the operation sequence of the first execution plan.
[0067] In other embodiments, at least two candidate execution plans corresponding to the first query statement are obtained; a plan feature representation corresponding to each of the at least two candidate execution plans is generated by a plan generation model; the plan feature representation is analyzed by the plan generation model to obtain the quality scores corresponding to each of the at least two candidate execution plans; among the at least two candidate execution plans, the candidate execution plan whose quality score meets the preset quality requirements is determined as the first execution plan.
[0068] Optionally, at least two candidate execution plans may be determined by the plan generation model through analysis of the first query statement; or, at least two candidate execution plans may be obtained by a traditional optimizer (e.g., a rule-based optimizer, a cost-based optimizer, etc.) after analyzing the first query statement. For example, the traditional optimizer may be guided to generate multiple different candidate execution plans by at least one of the following methods: inserting HINT information into the first query statement to change the execution plan generated by the traditional optimizer; or adjusting the internal parameters of the traditional optimizer (e.g., optimizer weights, etc.) to indirectly affect the traditional optimizer to output different execution plans.
[0069] Optionally, the plan feature representation can be implemented as a feature vector tree. For example, a feature vector is generated for each query operation in the candidate execution plan, and the feature vectors corresponding to multiple query operations in the candidate execution plan are combined to obtain a feature vector tree.
[0070] Optionally, the quality score characterizes the quality of the candidate execution plan. Optionally, the execution time of the candidate execution plan is negatively correlated with the quality score, that is, the shorter the execution time, the higher the quality score.
[0071] Optionally, after obtaining the quality scores corresponding to at least two candidate execution plans, the candidate execution plan with the highest quality score is selected as the first execution plan.
[0072] For example, firstly, by adjusting the internal parameters of a traditional optimizer, multiple candidate execution plans for the first query statement are generated. Then, each candidate execution plan is input into a plan generation model, which encodes its features, sequentially extracting structural features of the query operations such as table scan type, join method, join order, filtering timing, and aggregation node position. A high-dimensional feature vector is generated for each query operation, and these features are then combined into a feature vector tree according to the operation hierarchy and execution order of the execution plans, forming a complete plan feature representation. The plan generation model analyzes each plan feature representation, comprehensively estimating execution time, CPU (Central Processing Unit) usage, I / O (Input / Output) overhead, and memory consumption, and outputs a quality score for each candidate execution plan. Candidate execution plans with better execution paths, smaller intermediate result sets, and lower resource overhead receive higher quality scores. Finally, the candidate execution plan with the highest quality score is selected as the first execution plan for the first query statement.
[0073] In the above embodiments, on the one hand, by using a plan generation model to score the quality of multiple candidate execution plans, a refined evaluation and quantitative comparison of candidate execution plans is achieved, improving the accuracy and reliability of the first execution plan; on the other hand, by screening candidate execution plans through preset quality requirements, low-quality plans can be eliminated, ensuring that the selected first execution plan has superior execution performance, providing a high-quality foundation for subsequent plan prompt information generation, thereby ensuring the quality of the second execution plan generated during database runtime, and ultimately improving the efficiency and stability of data query.
[0074] In other embodiments, the plan generation model primarily uses artificial intelligence techniques to optimize the cardinality estimation, cost calculation, and plan enumeration of traditional optimizers, thereby accurately generating the optimal execution plan. Optionally, the plan generation model includes at least one of a cardinality prediction sub-model, a cost calculation sub-model, and a plan enumeration sub-model.
[0075] Optionally, the cardinality prediction sub-model is a pre-trained machine learning model used for cardinality estimation. The query feature representation of the first query statement is extracted; the query feature representation and database metadata are input into the cardinality prediction sub-model, and the cardinality estimate is output. The query feature representation includes, but is not limited to, features such as the query statement's syntax tree, predicate conditions, table join relationships, and aggregate functions. Database metadata includes table structure (such as column types and index information), statistical information (such as histograms, cardinality, and data distribution), etc. The cardinality estimate represents the number of rows of intermediate results expected to be generated when the first query statement is executed in the database. For example, the cardinality estimate represents the number of rows of intermediate results expected to be generated when a certain query operation (such as a join) corresponding to the first query statement is executed.
[0076] Optionally, the cost calculation sub-model is a pre-trained machine learning model used to generate candidate execution plans. The query feature representation of the first query statement is extracted; the query feature representation and optimization objective are input into the plan enumeration sub-model, outputting a set of candidate execution plans. The optimization objective represents a user-specified or system-default objective (such as minimizing the execution time of the execution plan). The candidate execution plan set includes at least two candidate execution plans.
[0077] Optionally, the cardinality estimate and the set of candidate execution plans are input into the cost calculation sub-model, and the cost estimate of each candidate execution plan in the set of candidate execution plans is output, wherein the cost estimate can be implemented as the expected execution time of the candidate execution plan.
[0078] Optionally, after obtaining the cost estimate of each candidate execution plan in the candidate execution plan set, the candidate execution plan with the lowest cost estimate is selected as the first execution plan of the first query statement.
[0079] In some embodiments, at least one query statement is obtained during the database preprocessing stage, and the at least one query statement includes a first query statement. Optionally, the at least one query statement is a pre-obtained query statement to be processed.
[0080] Step 220: If the first execution plan meets the preset execution requirements, obtain the plan prompt information corresponding to the first query operation.
[0081] Preset execution requirements are pre-defined constraints; in other words, they are used to filter execution plans generated by the plan generation model.
[0082] In some embodiments, if the first simulated execution data of the first execution plan meets preset execution requirements, plan prompt information corresponding to the first query operation is obtained. The first simulated execution data represents the execution data of simulating the execution of the first execution plan in the database, and includes execution duration, resources consumed during execution (such as memory resources, processor resources, etc.). For example, if the execution duration in the first simulated execution data is less than a first preset duration, and / or the resources consumed during execution in the first simulated execution data are less than a first preset resource amount, it is determined that the first simulated execution data meets the preset execution requirements.
[0083] In other embodiments, if the second simulated execution data of the first execution plan meets preset execution requirements, plan prompt information corresponding to the first query operation is obtained. The second simulated execution data represents the execution data of the first query operation when the first execution plan is simulated in the database, and includes execution duration, resources consumed, etc. For example, if the execution duration in the second simulated execution data is less than a second preset duration, and / or the resources consumed in the second simulated execution data are less than a second preset resource amount, it is determined that the second simulated execution data meets the preset execution requirements.
[0084] The following explanation uses the first and second simulated execution data as examples to illustrate the execution duration. The first execution plan meets the preset execution requirements under at least one of the following conditions:
[0085] (1) The first simulation duration corresponding to the first execution plan is less than the preset first execution duration.
[0086] The first simulation duration represents the time required to simulate the execution of the first execution plan in the database.
[0087] Optionally, if the first simulation duration of the first execution plan is less than the preset first execution duration, the plan prompt information corresponding to the first execution plan is obtained. For example, a first execution duration corresponding to the first query statement is preset, and the database is simulated based on the model's running conditions. The first execution plan is executed in the simulated database, and the simulated execution duration corresponding to the first execution plan is obtained. The simulated execution duration is the total execution duration of the first execution plan. If the simulated execution duration is less than the preset first execution duration, the first execution plan is determined to meet the preset execution requirements.
[0088] In the above scheme, setting a first execution duration to select execution plans with higher execution efficiency as subsequent inheritance execution plans improves the quality of execution plans generated by the database at runtime.
[0089] By using a preset first execution duration as a screening threshold, the first execution plan generated by the plan generation model is simulated and verified. This ensures that only plans with satisfactory execution efficiency (i.e., shorter simulated execution duration) can proceed to subsequent processes, guaranteeing the quality of plan prompts. Furthermore, by comparing simulated execution durations, inefficient or abnormal execution plans that the plan generation model may output can be effectively filtered out, preventing inferior plans from being embedded in the plan prompts and further improving the execution speed and system stability of data queries.
[0090] In some embodiments, at least two simulation running conditions are obtained; after simulating the database operation based on the at least two simulation running conditions, first candidate durations corresponding to the at least two simulation running conditions are obtained; the first candidate duration represents the duration required to execute the first execution plan in the database; and the first simulation duration is determined based on the average duration or weighted average duration of the at least two first candidate durations.
[0091] Optionally, the simulated operating conditions of the database include, but are not limited to, at least one of the following: data scale (the amount of data in the tables in the database, including the number of rows, columns, and data storage size), hardware resource configuration (the hardware environment required for database operation, including the number of CPUs, memory capacity, disk I / O speed, network bandwidth, etc.), load conditions (the concurrent query pressure, lock contention, buffer status, and other load characteristics of the database), and statistical information version (such as using statistical information from historical time points in the database).
[0092] Optionally, the at least two simulated running conditions can be customized by the database administrator, or the at least two simulated running conditions can be obtained from the database's historical running logs, such as extracting at least two sets of actual running parameters within a preset historical time period from the database's historical running logs, and using each set of actual running parameters as a simulated running condition.
[0093] Optionally, the average or weighted average duration of at least two first candidate durations can be obtained as the first simulation duration. For the weighted average duration, the weight of different first candidate durations is determined by the simulation running conditions corresponding to the first candidate duration. For example, the database administrator can configure different weights for different simulation running conditions, such as assigning higher weights to simulation running conditions during peak business hours and lower weights to simulation running conditions during resource idle hours.
[0094] In the above scheme, at least two simulated running conditions are considered to simulate the operation of the database and obtain the corresponding first candidate duration, and then the first simulation duration is determined. This fully simulates the actual running state of the database, improves the simulation accuracy of the first simulation duration, and uses the first simulation duration to filter the execution plan, making the filtered execution plan more accurate.
[0095] In addition, by comprehensively considering the execution time under various simulated operating conditions, the first simulation duration can be made closer to the database's performance in a complex and ever-changing real business environment, thereby improving the accuracy of judging whether the first execution plan meets the preset execution requirements and ensuring that the selected plan prompt information has stronger robustness and adaptability.
[0096] (2) The simulated execution time corresponding to the first query operation is less than the preset second execution time.
[0097] The second simulation duration represents the execution time required for the first query operation when simulating the execution of the first execution plan in the database.
[0098] Optionally, if the second simulation duration of the first query operation is less than the preset second execution duration, the plan prompt information corresponding to the first execution plan is obtained.
[0099] For example, a second execution duration corresponding to the first query operation is preset, and the database is simulated based on the model's running conditions. The first execution plan is executed in the simulated database, and the simulated execution duration corresponding to the first query operation in the first execution plan is obtained. If the simulated execution duration is less than the second execution duration, the first execution plan is determined to meet the preset execution requirements.
[0100] Optionally, if the execution time of some first query operations in the first execution plan is less than the corresponding second execution time, the first execution plan is determined to meet the preset execution requirements. Alternatively, if the execution time of each first query operation in the first execution plan is less than the corresponding second execution time, the first execution plan is determined to meet the preset execution requirements.
[0101] Optionally, the same second execution duration can be set for all different first query operations in the first execution plan. Alternatively, different second execution durations can be set for different first query operations in the first execution plan. For example, if the first execution plan includes three different first query operations—primary key index lookup, nested loop join, and conditional filtering—the second execution duration can be uniformly preset for these three different types of query operations; or, based on the operational complexity, data processing volume, and business priority of the first query operations, differentiated second execution durations can be set for different first query operations.
[0102] In the above scheme, setting a second execution duration to evaluate the simulated execution duration of the query operations in the first execution plan allows for more granular filtering of execution plans, achieving precise selection and further improving the quality of execution plans generated during database runtime. Furthermore, by setting a separate second execution duration threshold for the first query operation, it is possible to accurately identify and retain first execution plans whose overall execution duration may be affected by other operations, but whose key operations themselves are highly efficient.
[0103] In some embodiments, at least two simulated running conditions are obtained; the database is simulated and run according to the at least two simulated running conditions, and second candidate durations corresponding to the at least two simulated running conditions are obtained respectively; the second candidate duration represents the execution time required for the first query operation when the first execution plan is executed in the database; and the second simulation duration is determined based on the at least two second candidate durations.
[0104] Optionally, the average or weighted average duration of at least two second candidate durations can be obtained as the second simulation duration. For the weighted average duration, the weight of different second candidate durations is determined by the simulation running conditions corresponding to the second candidate duration. For example, the database administrator can configure different weights for different simulation running conditions.
[0105] If the first execution plan meets the preset execution requirements, the first query operation is analyzed to obtain plan prompt information. This plan prompt information is used to guide the operation method for the first query operation.
[0106] Optionally, the plan hint information is used to indicate at least one of the following for the first query operation: the lookup method (e.g., full table scan or index scan), the scan method (if index lookup), the table join order, and the table join method (e.g., nested loop join, hash join, merge join). For example, if the first query operation uses a hash join to connect tables A and B, then the plan hint information indicates the specific operation method of the first query operation, namely: using a hash join to connect tables A and B.
[0107] Optionally, the plan hint information can be implemented as HINT information. In the database field, HINT information is plan hint information provided by the database to force the optimizer to select specific query operations, such as specifying index lookup, specifying index scan, specifying table join order, specifying table join method, etc.
[0108] Optionally, if the first execution plan includes at least two first query operations, the plan hint information is used to indicate some of the first query operations in the first execution plan, or the plan hint information is used to indicate all the first query operations in the first execution plan.
[0109] It should be noted that the above-mentioned plan prompts, in addition to indicating the operation method of the first query operation, can also indicate the resource strategy during the generation of the second execution plan. Optionally, the resource strategy is used to restrict the resource usage corresponding to the query operation. For example, when generating the second execution plan, the first query operation can only be selected as at least one of the final generated second query operations if the current memory usage of the database is greater than the preset usage rate; if the current memory usage of the database is less than or equal to the preset usage rate, the first query operation cannot be selected as at least one of the final generated second query operations. As another example, if the current memory usage of the database is less than or equal to the preset usage rate, the optimizer can reselect the query operation to be added to the final generated second execution plan based on the current running state of the database.
[0110] In some embodiments, at least two execution plans for a first query statement are generated using a plan generation model, and the at least two execution plans include the first execution plan. If the first execution plan among the at least two execution plans meets preset execution requirements, plan suggestion information corresponding to the first query operation is obtained. For example, multiple candidate execution plans corresponding to the first query statement are obtained, and after obtaining the quality scores corresponding to the multiple candidate execution plans, the at least two candidate execution plans with the highest quality scores are selected as the at least two execution plans for the first query statement.
[0111] In some embodiments, if the first execution plan meets the preset execution requirements, the first execution plan is parsed to obtain the first operation data corresponding to the first query operation; the prompt information template corresponding to the operation mode of the first query operation is obtained; and a plan prompt information is generated based on the first operation data and the prompt information template.
[0112] The first operation data includes the specific operation parameters of the first query operation, including but not limited to: table name, operation type, execution parameters, etc. The operation type may include, for example, a full table scan or index scan, nested loop join or hash join; the execution parameters may include, for example, table join order, filtering conditions, etc.
[0113] Optionally, after obtaining the first operation data and the prompt information template, the prompt information template is filled in according to the first operation data to generate the planned prompt information.
[0114] Optionally, the hint template is a predefined, standardized format used to convert the first query operation in the first execution plan into a plan hint message recognizable by the database. For example, the hint template can be implemented as a database-supported HINT template; that is, the first execution plan is parsed to obtain the first operation data corresponding to the first query operation; if the HINT template corresponding to the first query operation is a database-supported HINT template, the HINT template is populated based on the first operation data to obtain the first HINT information as the plan hint message. The database-supported HINT templates include, but are not limited to, at least one of: performing a full table scan, specifying an index scan, specifying the table join order, specifying the table join method, and specifying an index lookup.
[0115] In the above solution, the operation data of the query operation is obtained by parsing the execution plan. Then, the operation data is combined with the prompt information template to generate the plan prompt information. The plan prompt information is automatically generated by the prompt information template, which avoids the tedious process of manually designing the plan prompt information based on the execution plan and improves the efficiency of plan prompt information generation.
[0116] Compared to related technologies that require database administrators or developers to manually write hint messages (such as HINT statements) based on the execution plan, automatically generating plan hint messages ensures that the plan hint messages are consistent with the actual operation methods in the first execution plan. This avoids the problem of mismatch between hint messages and the plan due to human misunderstanding, thereby ensuring that the second execution plan generated at database runtime can accurately inherit the optimization strategies of the first execution plan, further improving the reliability and efficiency of query execution.
[0117] Step 230: During the operation of the database, based on the first query statement and the plan hint information, determine the second execution plan for the first query statement.
[0118] The second execution plan includes at least one second query operation corresponding to the first query statement. Optionally, the second query operation is defined by at least one operator.
[0119] Among them, the plan prompt information serves as a constraint in the second execution plan generation process.
[0120] Optionally, at least one second query operation includes the first query operation in the first execution plan. For example, during the generation of the second execution plan, based on prompts in the plan hint information, the system selects the first query operation to add to the second execution plan. That is, the second execution plan generated at database runtime can directly inherit the efficient query operation obtained from the plan generation model analysis, effectively ensuring the quality and stability of the final query execution.
[0121] In some embodiments, the first execution plan and the second execution plan are identical. For example, assuming the first execution plan only contains query operation a and query operation b, and the execution order of the operations in the first execution plan is to execute query operation a first and then query operation b, then the first execution plan and the second execution plan being identical means that the second execution plan also contains query operation a and query operation b, and the execution order of the operations is to execute query operation a first and then query operation b.
[0122] In the above embodiments, the second execution plan generated by the database runtime completely replicates all query operations in the first execution plan generated by the plan generation model. This means that the computational cost incurred by the AI model in the preprocessing stage is reused to the maximum extent, and the database runtime does not need to perform any additional tentative optimizations or partial reconstructions. Thus, while ensuring optimal execution quality, the runtime computational overhead is reduced, and the query response speed is significantly improved.
[0123] In other embodiments, the first execution plan and the second execution plan are different. Optionally, the second execution plan includes at least two second query operations, which include the first query operation but are not all of the first query operation. For example, the first execution plan includes query operation a and query operation b, while the second execution plan includes query operation a, query operation b, and query operation c. That is, the first query operation indicated in the plan hint information only affects some optimization strategies (such as table join order, index selection, etc.).
[0124] Optionally, if the first query operation suggested in the plan hint information refers to a portion of the query operations in the first execution plan, then the generated second execution plan will differ from the first execution plan, but the second execution plan may include the first query operation. Alternatively, if the first query operation suggested in the plan hint information refers to all query operations in the first execution plan, but because the system (or the traditional optimizer described below) generates additional query operations based on the real-time running status of the database (such as temporary table strategies), the generated second execution plan will not be exactly the same as the first execution plan.
[0125] In the above embodiments, the first execution plan and the second execution plan are different, and the second execution plan includes the first query operation in the first execution plan but does not completely reproduce all operations (for example, an additional query operation c is added). This allows the traditional optimizer to flexibly add additional query operations (such as temporary table materialization, parallelism adjustment, dynamic partition pruning, etc.) according to the actual state of the database runtime (such as cache hit rate, real-time memory pressure, data distribution changes, etc.), so that the second execution plan has runtime adaptive capabilities while inheriting the core optimization strategy, thus preventing the plan rigidity problem caused by excessive inheritance.
[0126] In some embodiments, a plan hint is inserted into the first query statement to obtain a second query statement; the second query statement is then analyzed by the optimizer to obtain a second execution plan. For example, if the plan hint is implemented as the first HINT information of the first execution plan, and the first query statement is implemented as a first SQL statement, the first HINT information is inserted into the first SQL statement using a specified syntax format to obtain a second SQL statement as the second query statement. For example, / *+...* / is the specified syntax format (the format of a comment), and the "..." is filled with the HINT statement, i.e., the first HINT information.
[0127] Optionally, the optimizer is implemented as a conventional optimizer, wherein the optimizer includes at least one of a rule-based optimizer and a cost-based optimizer.
[0128] In the above scheme, the plan hint information is inserted into the first query statement to obtain the second query statement. Then, the traditional optimizer based on rules and / or cost is used to analyze and obtain the second execution plan. The plan hint information is used as a constraint condition for the traditional optimizer to generate the execution plan. During the analysis process, the traditional optimizer will refer to the efficient execution plan obtained by the AI optimizer (i.e., the plan generation model) based on the plan hint information. This improves the efficiency of execution plan generation while ensuring the quality of the final effective execution plan.
[0129] By injecting plan hints into the query statement through syntax embedding, the optimization results of the AI optimizer (i.e., the plan generation model) are delivered to any type of traditional optimizer in a low-cost, non-intrusive manner, achieving intelligent constraints without modifying the internal logic of the traditional optimizer. Furthermore, when analyzing the second query statement, the traditional optimizer retains its original rule-based or cost-based optimization capabilities. Within the constraints defined by the plan hints, it can further refine its adjustments using runtime database statistics. This avoids the problem of traditional optimizers failing to find a high-quality plan due to enumeration space explosion, and also prevents the risk of being unable to adapt to changes in the runtime environment due to hard-coding the first execution plan. Thus, efficient collaboration between AI optimization and traditional optimization is achieved, significantly improving the efficiency of execution plan generation and the stability of the final query performance.
[0130] In some embodiments, the insertion position of the planning hint information in the first query statement is determined; the planning hint information is inserted at the insertion position of the first query statement to obtain the second query statement.
[0131] Optionally, the insertion position can be determined in the first query statement based on the type of planning prompt information.
[0132] For example, if the plan hint information is implemented as the first HINT information of the first execution plan, and the first query statement is implemented as the first SQL statement, the insertion position of the first HINT information in the first SQL statement is determined, and the first HINT information is inserted into the insertion position of the first SQL statement in a specified syntax format to obtain the second SQL statement as the second query statement. For example, if the insertion position is after the SELECT keyword, that is, the first HINT information is added after the SELECT keyword in the first SQL statement in the form of / *+...* / (comment form) to obtain the second query statement.
[0133] Optionally, determine the scope of the plan hint information within the first query statement, and determine the insertion position within the scope of the first query statement.
[0134] For example, the scope of the first HINT information within the first SQL statement is determined, and the first HINT information is inserted as a comment after the SELECT keyword within the scope of the first SQL statement. For instance, if the first HINT information specifies the scan method for a single table, then the first HINT information is inserted after the SELECT keyword of the query block corresponding to the specified single table.
[0135] In the above scheme, the insertion position of the plan prompt information in the first query statement is determined, and then the insertion is performed to obtain the second query statement. This ensures the accuracy of the second query statement generation process and avoids generating an incorrect second query statement due to an incorrect insertion position.
[0136] Based on the type and scope of the plan hints (e.g., hints specifying index scans must immediately follow the SELECT keyword, and hints specifying table join order must be placed within the query block corresponding to the join operation), the legal insertion position of the plan hints in the first query statement is accurately determined. This ensures that the generated first query statement conforms to the database syntax rules, avoids the hints being ignored by the parser or causing syntax exceptions due to incorrect insertion positions, and thus guarantees that the plan hints can be correctly recognized and effective by the optimizer, improving the accuracy and effectiveness of plan hint constraints.
[0137] In some embodiments, a first query request is received during database operation; the first query request is parsed to obtain a first query statement; and a second execution plan for the first query statement is determined based on the first query statement and plan hint information. Optionally, the client of the first application sends the first query request to the database management system, which parses the first query request to obtain the first query statement and determines a second execution plan for the first query statement based on the first query statement and plan hint information.
[0138] In some embodiments, the plan hint information of the first execution plan is stored in a plan hint table. During database operation, the first query statement and the plan hint table need to be matched to obtain the plan hint information. Optionally, during database operation, the first query statement and the plan hint table are matched; if the first query statement and the plan hint information of the first execution plan stored in the plan hint table match, the plan hint information of the first execution plan is obtained from the plan hint table, and a second execution plan for the first query statement is determined based on the first query statement and the plan hint information.
[0139] Optionally, the unique identifier information of the first query statement and the plan suggestion information in the plan suggestion table are stored correspondingly (associated storage). For example, during database operation, matching the first query statement and the plan suggestion table means: obtaining the unique identifier information of the first query statement, and traversing the plan suggestion table based on the unique identifier information. If the plan suggestion table contains the unique identifier information of the first query statement, the plan suggestion information corresponding to the unique identifier information in the plan suggestion table is obtained. Optionally, the unique identifier information can be implemented as the unique identifier number of the first query statement, a hash value, etc.
[0140] Optionally, the execution plan hint information can be implemented as a first HINT, and the execution plan hint table can be implemented as an OUTLINE table. The OUTLINE table is a data structure in the database used to store execution plan hint (HINT) information. For example, the unique identifier information of the first query statement and the first HINT information are stored correspondingly in the first OUTLINE of the OUTLINE table.
[0141] Optionally, after obtaining the second query statement, the second query statement is converted into a syntax tree, and the syntax tree is input into the optimizer to output a second execution plan. The second execution plan represents the query process executed by the database based on the first query statement.
[0142] The syntax tree includes abstract syntax trees (ASTs), etc. For example, taking the implementation of the first hint information as an example, to decompose the second query statement into tokens, an abstract syntax tree (AST) is constructed based on the tokens; the tokens corresponding to the first hint information are identified from the AST; and the corresponding first query operation is determined and extracted based on the tokens corresponding to the first hint information. The first query operation can be regarded as a constraint condition for generating the second execution plan, that is, at least one second query operation in the generated second execution plan needs to contain the first query operation.
[0143] Taking the optimizer implementation as a rule-based optimizer as an example, the rule-based optimizer includes a rule engine, which includes predefined fixed rules. The syntax tree is input into the rule-based optimizer, and the first hint information is extracted from the syntax tree. The first hint information is added to the rule engine as a hint rule. The first hint information has a higher priority than the fixed rules in the rule engine. When the rule engine is used to generate an execution plan (i.e., the second execution plan) for the syntax tree, the rule engine matches the syntax tree with all available rules (including hint rules and predefined rules) to obtain all rules (including hint rules) that match the syntax tree (i.e., the second query statement). The second execution plan is obtained by combining all rules that match the syntax tree.
[0144] Taking the optimizer implementation as a cost-based optimizer as an example, the syntax tree and the current statistics of the database are input into the cost-based optimizer. The first hint information is extracted from the syntax tree and used as a constraint. The cost-based optimizer analyzes the syntax tree and statistics to generate multiple candidate execution plans. The candidate execution plans must include the first query operation indicated by the first hint information. The cost of the candidate execution plans is estimated (such as calculating I / O utilization, CPU utilization, memory utilization, etc.), and the candidate execution plan with the lowest cost is selected as the second execution plan.
[0145] In some embodiments, during the operation of the database, while the plan prompt information is in effect, if the plan prompt information meets preset requirements, the first execution plan is updated based on the current operating state of the database through the plan generation model; the updated plan prompt information is determined based on the updated first execution plan; and during the operation of the database, the second execution plan for the first query statement is determined based on the first query statement and the updated plan prompt information.
[0146] The "plan prompt information taking effect" means that the plan prompt information is valid, and a corresponding second execution plan can be generated based on the plan prompt information taking effect and the first query statement.
[0147] Among them, updating the first execution plan based on the current running state of the database through the plan generation model means running the database with the current running state of the database as the model running condition, analyzing the first query statement through the plan generation model, and generating the updated first execution plan.
[0148] In the above scheme, before the first query statement is executed, the first execution plan will be updated based on preset requirements, and the plan prompt information will be updated to ensure the synchronization between the plan prompt information and the current running status of the database, thereby improving the quality of the final generated second execution plan.
[0149] By introducing a dynamic update mechanism for plan prompts, the first execution plan and its corresponding plan prompts are updated in a timely manner, avoiding the problem of old plan prompts becoming invalid due to updates to database statistics or changes in load patterns. This ensures that the plan prompts are always synchronized with the current operating status of the database, enabling AI optimization results to continuously iterate as the database evolves, thereby significantly improving the long-term query performance and system robustness of the database in dynamic production environments.
[0150] Optionally, the plan notification information meets the preset requirements under at least one of the following conditions:
[0151] (1) The duration of the planned prompt message generation is longer than the preset duration.
[0152] Optionally, if the generation time of the plan prompt information exceeds the preset time, the first execution plan is updated based on the current running status of the database through the plan generation model.
[0153] The generation duration refers to the time from the generation time of the plan hint information to the current time. For example, during database operation, before the execution of the first query statement, the first execution plan can be updated once every preset time interval. The updated plan hint information is determined based on the updated first execution plan. Then, based on the first query statement and the updated plan hint information, the second execution plan for the first query statement is determined. The specific determination process can be referred to the above process of determining the second execution plan based on the first query statement and plan hint information, and will not be elaborated here.
[0154] In the above scheme, the first execution plan is updated based on a preset duration requirement, ensuring that the plan prompt information is generated based on the latest data block running status, and avoiding the generation of an incorrect second execution plan due to an old plan (i.e., an outdated plan prompt information). When the plan prompt information has been present for too long (i.e., the generation time exceeds a preset threshold), the update process is proactively triggered, avoiding the problem of old plan prompt information becoming invalid or execution efficiency decreasing due to factors that accumulate over time, such as changes in business data distribution, updates to statistical information, or adjustments to the hardware environment. This ensures the timeliness and accuracy of the plan prompt information.
[0155] (2) The current operating status of the database meets the requirements for status change.
[0156] Optionally, if the current running state of the database meets the requirements for a state change, the first execution plan is updated based on the current running state of the database through the plan generation model.
[0157] Optionally, if the resource utilization in the current operating state meets the state change requirements, the first execution plan is updated based on the current operating state of the database through the plan generation model. If the database resource utilization is greater than a preset utilization rate for M consecutive collection cycles, it is determined that the resource utilization meets the state change requirements. Here, M is a pre-defined positive integer. Resource utilization includes at least one of I / O utilization, CPU utilization, and memory utilization.
[0158] In the above scheme, the first execution plan is updated based on state change requirements. This ensures that the plan prompts are generated based on the latest data block running status, thus adapting to changes in the database's running status and generating a better execution plan. For example, when CPU, memory, or I / O utilization consistently exceeds limits, updating the plan prompts can guide the optimizer to choose a more efficient execution path, reducing resource consumption. When system resources (such as CPU utilization, memory usage, I / O throughput, etc.) show continuous abnormalities or significant fluctuations, the execution plan update process is immediately initiated, enabling the first execution plan to respond promptly to changes in the operating environment's load. This avoids using high-consumption query operations (such as full table scans or large-scale hash joins) when resources are scarce, effectively preventing system performance degradation or resource exhaustion due to plan mismatch.
[0159] Step 240: Execute at least one second query operation in the database to obtain the query results of the first query statement.
[0160] The query result is the result of the first query statement in the database.
[0161] For example, queries are executed in the database according to the execution order of at least one second query operation and the query operation corresponding to at least one second query operation in the second execution plan, and query results are obtained.
[0162] For example, please refer to Figure 3, which illustrates a schematic diagram of a data query method. As shown in Figure 3, multiple SQL statements are input into the plan generation model 302, which outputs execution plans corresponding to the multiple SQL statements, including a first SQL statement 301. Hint information 303 corresponding to each execution plan is determined and added to the OUTLINE table 304. When the database 308 runs, the first SQL statement 301 is matched against the OUTLINE table 304. If the first hint information corresponding to the first SQL statement 301 exists in the OUTLINE table 304, the first hint information is inserted into the first SQL statement 301, resulting in a second SQL statement 305. The second SQL statement 305 is input into the optimizer 306, which determines an execution plan 307 under the constraints of the first hint information. The database 308 is then invoked to execute the query operation in the execution plan 307, resulting in a query result 309.
[0163] In summary, the data query method provided in this application generates a first execution plan for a first query statement using a pre-trained plan generation model, and then obtains the corresponding plan hint information. During database runtime, the method combines the first query statement and the existing plan hint information to quickly generate a second execution plan and execute it to obtain the query results. Compared to related technologies that use a cost-based optimizer to generate execution plans during database runtime, this application, on the one hand, uses an AI model to generate the first execution plan, and its corresponding plan hint information ensures that the second execution plan generated during database runtime can inherit the optimization strategy of the first execution plan, thus guaranteeing the quality of the execution plan; on the other hand, this application generates the first execution plan using a plan generation model before database runtime and represents the first execution plan through plan hint information. During database runtime, the system can directly obtain the analysis results of the AI model based on the plan hint information, avoiding the AI inference process during database runtime. This improves the generation efficiency of the execution plan while ensuring its quality, thereby improving data query efficiency.
[0164] In some embodiments, for an execution plan that fails to execute, if a corresponding plan hint message exists for that execution plan, the plan hint message can be made invalid. For example, please refer to Figure 4, which shows a flowchart of a data query method, including steps 250 and 260. Optionally, steps 250 and 260 are executed after step 240.
[0165] Step 250: If the second execution plan meets the abnormal execution conditions, the control plan prompt message becomes invalid.
[0166] The second execution plan may meet at least one of the following abnormal execution conditions: the execution duration of the second execution plan exceeds the preset duration; the resources consumed during the execution of the second execution plan exceed the preset resource amount; or the query results corresponding to the second execution plan are incorrect. By identifying the above abnormal execution conditions, the system can promptly determine whether there are quality issues with the second execution plan and trigger subsequent plan prompt information failure and model update processes accordingly.
[0167] For example, the second execution plan may meet the abnormal execution conditions in at least one of the following situations:
[0168] First, abnormal execution time. The actual execution time of the second execution plan in the database exceeds the preset time threshold (for example, the preset time is 100 milliseconds, but the actual execution time exceeds this value). This situation usually indicates that although the second execution plan inherits the optimization strategy of the first execution plan, the actual execution efficiency is significantly lower than expected due to changes in data distribution, outdated statistical information, or increased system load.
[0169] Second, abnormal resource consumption. If the system resources consumed during the execution of the second execution plan (such as CPU time, memory usage, disk I / O operations, or network bandwidth) exceed the preset resource amount (for example, memory usage exceeds 1GB or disk reads exceed 100,000), this situation may cause resource contention and affect the normal execution of other concurrent queries in the database.
[0170] Third, the query results are incorrect. The query results returned after the second execution plan are not consistent with the expected results (e.g., the number of records does not match, the data values are incorrect, or the result set is empty but should contain data). This situation usually indicates that the plan prompt message forcibly specified an operation mode that is not applicable to the current data semantics (e.g., incorrectly forcing the use of an index, resulting in some data not being scanned).
[0171] In some embodiments, the execution duration of the second execution plan in the database is obtained; if the execution duration exceeds a preset duration, the control plan prompt information becomes invalid. Optionally, once the plan prompt information becomes invalid, it cannot be used anymore, that is, the corresponding execution plan cannot be generated based on the plan prompt information and the first query statement.
[0172] In the above scheme, the execution time is used as the criterion for judging abnormal execution conditions. This ensures that the prompts for plans with low actual execution efficiency can be removed in a timely manner, avoiding the problem of low query efficiency caused by the subsequent use of inefficient execution plans, thereby improving the overall efficiency of the query system.
[0173] Step 260: Use the execution record data corresponding to the second execution plan as training sample data to update the model parameters of the plan generation model.
[0174] The execution record data includes at least one of the following: the query statement corresponding to the second execution plan (such as the first query statement and the second query statement), the plan prompt information, the execution duration, the resources consumed during execution, the query results obtained after execution, the database information (such as the database name, the database running status during the execution of the second execution plan, etc.), each query operation in the second execution plan, and the execution duration of each query operation in the second execution plan. No limitation is imposed here.
[0175] In some embodiments, the plan generation model is a comparison model. Optionally, if the second execution plan meets the abnormal execution conditions, the execution record data corresponding to the second execution plan is obtained; the execution record data corresponding to the second execution plan is used as negative sample data to update the model parameters of the plan generation model.
[0176] For example, the anomaly type corresponding to the execution record data is determined. For instance, if the abnormal execution condition is that the execution duration of the second execution plan exceeds a preset duration, the anomaly type is "Execution Duration Anomaly"; if the abnormal execution condition is that the resources consumed during the execution of the second execution plan exceed a preset resource amount, the anomaly type is "Resource Consumption Anomaly"; if the abnormal execution condition is that the query result corresponding to the second execution plan is incorrect, the anomaly type is "Query Result Anomaly," etc. Based on the anomaly type corresponding to the execution record data, at least two normal types of execution record data are obtained. For example, if the anomaly type is "Execution Duration Anomaly," then the normal type of execution record data is the execution record data of the execution plan whose execution duration is less than or equal to the preset duration; if the anomaly type is "Resource Consumption Anomaly," then the normal type of execution record data is the execution record data of the execution plan whose execution duration is less than or equal to the preset resource amount; if the anomaly type is "Query Result," then the normal type of execution record data is the execution record data of the execution plan whose query result is correct. The execution record data of the anomaly type is encoded to obtain negative sample feature representations, and the execution record data of at least two normal types is encoded to obtain at least two positive sample feature representations. The contrastive loss function is calculated based on the similarity between at least two positive sample feature representations (positive sample pairs) and the similarity between a negative sample feature representation and any positive sample feature representation (negative sample pair). The optimization objective of the contrastive loss function is to maximize the similarity of positive sample pairs and minimize the similarity of negative sample pairs. The update gradient is calculated using the loss value of the contrastive loss function, and the model parameters of the planned generation model are adjusted based on the update gradient.
[0177] The updated plan generation model can be used in subsequent data query processes.
[0178] In the above scheme, the execution record data corresponding to the second execution plan meeting the abnormal execution conditions is used as negative sample data to update the model parameters of the plan generation model. This realizes the introduction of data under actual abnormal execution conditions as negative feedback. The plan generation model can optimize the model parameters according to the actual negative feedback, thereby generating a more accurate execution plan. This also generates more accurate plan prompt information, further improving the quality of the execution plan generated by the subsequent database runtime.
[0179] This application enables the plan generation model to more accurately capture complex scenarios in actual database operation, continuously improve the model's ability to identify low-quality execution plans, and thus generate higher-quality first execution plans. This reduces the probability of subsequent plan prompts becoming invalid or execution anomalies, and significantly enhances the adaptive optimization capability and continuous stability of query performance of the database management system in long-term operation.
[0180] In some embodiments, an abnormal execution database is constructed to store execution record data of execution plans that meet abnormal execution conditions, such as the execution record data corresponding to the second execution plan. Optionally, if the second execution plan meets the abnormal execution conditions, the execution record data corresponding to the second execution plan is stored in the abnormal execution database. Target execution record data is obtained from the abnormal execution database, where the similarity between the target execution record data and the execution record data corresponding to the second execution plan is greater than a preset similarity. Based on the similarity between the target execution record data and the execution record data corresponding to the second execution plan, the update gradient is determined when the execution record data is used as training sample data.
[0181] For example, when new abnormal execution record data is generated, the system searches the abnormal execution database for historical abnormal data (i.e., target execution record data) with a similarity greater than a preset threshold. Higher similarity indicates a higher frequency of abnormal occurrences in that type of execution plan, thus increasing the impact of the abnormal execution record data on the model parameters and consequently increasing the gradient update (i.e., increasing gradient weights). Lower similarity indicates a lower frequency of abnormal occurrences in that type of execution plan, thus decreasing the gradient update (i.e., reducing gradient weights). This filters out execution plan anomalies caused by accidental reasons (such as network anomalies, device hardware anomalies, etc.) and avoids over-adjustment of the model.
[0182] In summary, the data query method provided in this application controls the corresponding plan prompt information to become invalid when the second execution plan meets the abnormal execution conditions, thereby avoiding the problem of low query efficiency or query failure caused by the subsequent use of abnormal execution plans and improving the reliability of the database management system.
[0183] In some embodiments, for query statements that meet the short query requirements, an execution plan can be directly generated and executed according to preset short query rules. For example, please refer to Figure 5, which shows a flowchart of a data query method, including steps 270 and 280.
[0184] Step 270: If the first query statement meets the short query requirement, generate a third execution plan for the first query statement according to the preset short query rules.
[0185] The third execution plan includes at least one third query operation.
[0186] Optionally, if the query type corresponding to the first query statement is a short query, then the first query statement is determined to meet the short query requirement. A short query refers to a query type with short execution time (typically in the millisecond range), simple logic, and low data access volume. Optionally, the query type of a short query includes at least one of the following:
[0187] (1) Simple single-table query: A simple SELECT operation involving only a single table.
[0188] (2) Queries based on primary key or unique index: Equality queries are performed using primary key or unique index fields to directly locate the target record.
[0189] (3) Constant table lookup: The lookup conditions can uniquely identify a single record.
[0190] (4) Simple aggregation query: Perform simple aggregation statistics on small datasets (such as COUNT, SUM).
[0191] (5) Covering index query: All fields required for the query are contained in the index, and there is no need to go back to the table.
[0192] It should be noted that the above examples of short queries are merely illustrative and are not intended to limit the scope of this application.
[0193] Optionally, different short queries for different query types correspond to different preset short query rules; generating a third execution plan for the first query statement based on the preset short query rules includes: determining a preset first short query rule based on the query type corresponding to the first query statement; and generating a third execution plan for the first query statement based on the first short query rule.
[0194] For example, the syntax tree of the first query statement is obtained; the query type corresponding to the first query statement is determined based on the syntax tree; if the query type is a short query, the first short query rule corresponding to the query type of the first query statement is obtained. A third execution plan for the first query statement is generated based on the first short query rule.
[0195] Step 280: Execute at least one third query operation in the database to obtain the query results of the first query statement.
[0196] That is, the third execution plan is executed in the database to obtain the query results.
[0197] For example, queries are executed in the database according to the execution order of at least one third query operation in the third execution plan and the query operations corresponding to at least one third query operation, and query results are obtained.
[0198] In summary, the data query method provided in this application directly generates and executes a third execution plan based on preset short query rules for a first query statement that meets the short query requirements. This avoids complex and redundant processing of short queries, simplifies the short query processing flow, and improves the overall query efficiency of the query system to a certain extent.
[0199] By identifying short query characteristics and matching them with preset short query rules (such as using primary keys or unique indexes for quick location in simple single-table queries, and directly returning index data in covering index queries), the time-consuming steps of syntax tree parsing, candidate plan enumeration, and cost estimation in the execution plan generation process are bypassed, simplifying the processing path for short queries and significantly reducing query response latency. In addition, by offloading short queries to the fast path for execution, the computational load on the traditional optimizer and plan generation model is reduced, allowing the system to devote more computational resources to the optimization of complex queries. This improves the overall throughput and resource utilization efficiency of the database management system and enables differentiated and refined processing of different types of queries.
[0200] For example, please refer to Figure 6, which shows a flowchart of a data query method, which includes steps 601 to 613.
[0201] Step 601: Input at least one query statement into the plan generation model and output the execution plan of at least one query statement.
[0202] At least one of the query statements can be implemented as an SQL statement.
[0203] Optionally, during the database preprocessing stage, at least one query statement to be executed is obtained; then, the at least one query statement is input into the plan generation model, and the execution plan of the at least one query statement is output.
[0204] Optionally, at least one query statement is a predefined query statement to be executed later.
[0205] Among them, the plan generation model is a pre-trained machine learning model used to generate execution plans. For example, in the database field, the plan generation model can be called an AI optimizer.
[0206] In some embodiments, at least two candidate execution plans corresponding to the query statement are obtained; the plan feature representations corresponding to the at least two candidate execution plans are extracted by the plan generation model; the quality scores of the candidate execution plans are obtained by analyzing the plan feature representations by the plan generation model; and the candidate execution plan with the highest quality score among the at least two candidate execution plans is used as the execution plan for the query statement.
[0207] In other embodiments, the plan generation model primarily uses artificial intelligence technology to optimize the cardinality estimation, cost calculation, and plan enumeration of traditional optimizers, thereby accurately generating the optimal execution plan. Optionally, the plan generation model includes at least one of a cardinality prediction sub-model, a cost calculation sub-model, and a plan enumeration sub-model. Optionally, a query feature representation of the query statement is extracted; the query feature representation and database metadata are input into the cardinality prediction sub-model, and a cardinality estimate is output. Optionally, the query feature representation and optimization objective are input into the plan enumeration sub-model, and a set of candidate execution plans is output, wherein the optimization objective represents a user-specified or default objective (such as minimizing execution time). Optionally, the cardinality estimate and the set of candidate execution plans are input into the cost calculation sub-model, and a cost estimate for each candidate execution plan in the candidate execution plan set is output, wherein the cost estimate can be implemented as the expected execution time of the candidate execution plan. Optionally, after obtaining the cost estimate for each candidate execution plan in the candidate execution plan set, the candidate execution plan with the lowest cost estimate is selected as the execution plan for the query statement.
[0208] Step 602: Obtain the plan hint information of the execution plan that meets the preset execution requirements from the execution plan of at least one query statement, and add the plan hint information of the execution plan that meets the preset execution requirements to the plan hint table.
[0209] In some embodiments, in the execution plan of at least one query statement, the HINT information of the execution plan that meets the preset execution requirements is obtained, and the HINT information of the execution plan that meets the preset execution requirements is added to the OUTLINE table.
[0210] Optionally, the execution plan of at least one query statement is simulated, the simulated execution duration of the execution plan of at least one query statement is obtained, and if the simulated execution duration meets the preset duration condition, the execution plan is determined to meet the preset execution requirements.
[0211] Optionally, after determining that the execution plan meets preset execution requirements, the HINT information of the execution plan is obtained. For example, the execution plan is parsed to obtain at least one operator, and this operator is mapped to HINT information supported by the database.
[0212] Optionally, after obtaining the hint information of at least one query statement, the hint information of at least one query statement is added to the OUTLINE table. For example, a hash value of the query statement is generated, and the hash value and the hint information of the query statement are stored correspondingly in the OUTLINE table.
[0213] For example, as shown in Figure 7, each OUTLINE in the OUTLINE table (i.e., the schedule reminder table 705) (Q1 to Q in Figure 7) nEach OUTLINE corresponds to a query statement, and each OUTLINE stores the following information: the identifier of the query statement (such as the hash value of the query statement), the hint information of the query statement (P1 to P in Figure 7). n ), whether the OUTLINE is valid, the OUTLINE update time, the conditions for the OUTLINE to take effect, the HINT version number, etc.
[0214] Step 603: During the operation of the database, obtain the first query statement for the database.
[0215] Alternatively, the database can be implemented as a relational database.
[0216] Optionally, the first query statement is a query statement in at least one query statement.
[0217] In some embodiments, a first query request is received during database operation; the first query request is parsed to obtain a first query statement; and a second execution plan for the first query statement is determined based on the first query statement and plan hint information. Optionally, the client of the first application sends the first query request to the database management system, which parses the first query request to obtain the first query statement and determines a second execution plan for the first query statement based on the first query statement and plan hint information.
[0218] Step 604: If the first query statement meets the short query requirement, generate the third execution plan for the first query statement according to the preset short query rules.
[0219] In some embodiments, if the query type corresponding to the first query statement is a short query, it is determined that the first query statement meets the short query requirement.
[0220] Optionally, the first query statement is identified, and an identification result is obtained. If the identification result indicates that the query operation of the first query statement is a single-table query and only contains predicate conditions that can be hit by a single index, then the query type corresponding to the first query statement is determined to be a short query, that is, the first query statement meets the requirements of a short query. For example, a single-table query operation means that the SQL statement only involves operations on a single table (such as SELECT, UPDATE, DELETE) and does not contain multi-table joins (JOIN), subqueries, or set operations (UNION). Only containing predicate conditions that can be hit by a single index means that the conditions in the WHERE or ORDER BY clauses of the query can be completely covered by an existing index.
[0221] Optionally, after determining that the first query statement meets the short query requirement, a third execution plan for the first query statement is generated according to the preset short query rules.
[0222] Please refer to Figure 7, which illustrates a data query method. As shown in Figure 7, before SQL statement 701 enters the optimizer, it is identified. If the query operation corresponding to SQL statement 701 is a single-table query and only contains predicate conditions that can be hit by a single index, it will be determined as a short query. When SQL statement 701 is determined to be a short query, it is input into the fast path mechanism 703 to obtain the execution plan a of SQL statement 701. The fast path mechanism 703 predefines short query rules. For example, for the SQL statement "SELECT username, email FROM users WHERE user_id=123", this SQL statement belongs to a single-table query and user_id is the primary key, so it is determined to be a short query. The execution plan for this short query is directly generated as "quickly locate the record of user_id=123 through the primary key index and return username and email".
[0223] Step 605: Execute the third execution plan in the database to obtain the query results.
[0224] That is, at least one third query operation in the third execution plan is executed in the database to obtain the query result of the first query statement.
[0225] In the above scheme, if the first query statement meets the short query requirement, there is no need to input the first query statement into the optimizer; the third execution plan is executed directly to obtain the query result. For example, as shown in Figure 7, if SQL statement 701 is determined to be a short query, the execution plan a generated by the fast path mechanism 703 is input into the executor 702, and the database is called to execute the execution plan a to obtain the query result 704.
[0226] Step 606: If the first query statement does not meet the short query requirement, match the first query statement with the plan hint table.
[0227] Optionally, if the query type corresponding to the first query statement is not a short query, the first query statement and the plan hint table are matched. For example, matching the first query statement and the plan hint table means determining whether the plan hint table records the plan hint information corresponding to the first query statement, i.e., the hint information; optionally, the hash value of the first query statement is obtained, and the plan hint table is traversed based on the hash value of the first query statement to query whether the plan hint table contains the hash value of the first query statement.
[0228] Step 607: If the first query statement and the plan hint table do not match, input the first query statement into the optimizer to obtain the fourth execution plan.
[0229] The fourth execution plan includes at least one fourth query operation.
[0230] If the hash value of the first query statement is not found in the query plan hint table, it means that the plan hint information corresponding to the first query statement is not stored in the plan hint table. In this case, the first query statement is directly input into the optimizer to obtain the fourth execution plan. For example, taking a cost-based optimizer, the first query statement is transformed into a first syntax tree (such as an abstract syntax tree AST), and the first syntax tree and the current statistics of the database are input into the optimizer to obtain the fourth execution plan.
[0231] Step 608: Execute the fourth execution plan in the database to obtain the query results.
[0232] That is, at least one fourth query operation in the fourth execution plan is executed in the database to obtain the query result of the first query statement.
[0233] For example, as shown in Figure 7, if the SQL statement 701 and the plan hint table 705 do not match, the SQL statement 701 is input into the executor 702. The executor 702 calls the optimizer to generate execution plan b (that is, the fourth execution plan mentioned above), and then calls the database to execute the execution plan b to obtain the query result 704.
[0234] Step 609: If the first query statement matches the plan hint information of the first execution plan in the plan hint table, bind the plan hint information of the first execution plan and the first query statement to obtain the second query statement.
[0235] The first execution plan includes at least one first query operation.
[0236] If the hash value of the first query statement is present in the query plan hint table, it means that the plan hint table stores the plan hint information corresponding to the first query statement. This plan hint information corresponds to the first execution plan. Therefore, it is necessary to bind the plan hint information of the first execution plan and the first query statement to obtain the second query statement. For example, if the hash value in one of the OUTLINE tables is the same as the hash value of the first query statement, it means that the OUTLINE stores the hint information of the first query statement. Retrieve this hint information and insert it into the first query statement to obtain the second query statement.
[0237] Step 610: Input the second query statement into the optimizer to obtain the second execution plan.
[0238] The second execution plan includes at least one second query operation.
[0239] For example, using a cost-based optimizer, the second query statement is transformed into a second syntax tree (such as an abstract syntax tree AST), and the second syntax tree and the current statistics of the database are input into the optimizer to obtain a second execution plan.
[0240] Step 611: Execute the second execution plan in the database to obtain the query results.
[0241] That is, at least one second query operation in the second execution plan is executed in the database to obtain the query result of the first query statement.
[0242] For example, as shown in Figure 7, when SQL statement 701 matches plan hint table 705, the HINT information of the SQL statement (P1' to P1' in Figure 7) is obtained from plan hint table 705. n ' indicates the matching HINT information), insert the HINT information into the SQL statement, input the SQL statement with the inserted HINT information into the executor 702, the executor 702 calls the optimizer to generate execution plan c (that is, the second execution plan mentioned above), and then calls the database to execute the execution plan c to obtain the query result 704.
[0243] Step 612: If the second execution plan meets the abnormal execution conditions, the plan prompt information of the first execution plan in the control plan prompt table becomes invalid.
[0244] Considering that business data and load conditions are constantly changing during database operation, execution plans generated by the plan generation model based on data and load conditions at a specific point in time may not be suitable for new business scenarios. To address this issue, a load monitoring system is introduced to monitor database load conditions in real time and accurately.
[0245] For example, as shown in Figure 7, the load monitoring system 706 monitors the execution time of each query statement, that is, the complete time from the query statement being input into the database to obtaining the returned result (including optimizer processing time and execution plan execution time). If the execution time of a query statement is found to be longer than the preset time, the load monitoring system 706 determines whether the execution plan of the query statement uses the execution plan generated by the plan generation model 707, that is, whether the execution plan of the query statement is generated based on the plan hint information. If so, it is determined that the execution plan of the query statement meets the abnormal execution conditions, and the load monitoring system 706 controls the plan hint information corresponding to the query statement in the plan hint table 705 to become invalid, that is, controls the OUTLINE corresponding to the query statement in the plan hint table 705 to become invalid.
[0246] It should be noted that, in addition to the load monitoring system 706, DBAs (Database Administrators) can also manually intervene in the OUTLINE table, such as invalidating the OUTLINE corresponding to expired query statements, thereby avoiding the negative impact of expired OUTLINE on performance.
[0247] Step 613: Use the execution record data corresponding to the second execution plan as training sample data to update the model parameters of the plan generation model.
[0248] In other words, if the second execution plan meets the abnormal execution conditions, the execution record data corresponding to the second execution plan is used as training sample data to update the model parameters of the plan generation model. The updated plan generation model is then used in the subsequent data query process.
[0249] For example, as shown in Figure 7, the plan generation model 707 can be implemented as a comparison model. For execution plans that meet the abnormal execution conditions, the load monitoring system 706 will obtain the execution record data corresponding to the execution plan, such as the query statement corresponding to the execution plan, execution plan details, and number of data scan rows. Then, the execution record data is added to the training data of the comparison model. This execution record data will serve as negative sample data in the training data of the comparison model. The model parameters of the comparison model can be updated through this negative sample data. The updated comparison model will be used in subsequent data query processes. For example, a new batch of query statements can be obtained, and execution plans can be generated for the new batch of query statements. Then, plan prompt information for the execution plans of the new batch of query statements can be generated, and the plan prompt information for the new batch of query statements can be updated in the plan prompt table 705.
[0250] In summary, the data query method provided in this application, through the organic integration of an AI optimizer and a load monitoring system, constructs an intelligent optimization system capable of effectively responding to changes in business scenarios. Leveraging the real-time feedback mechanism of load monitoring, the system can promptly eliminate the adverse effects of expired plans; while newly added training data helps the AI optimizer quickly adapt to new business loads and data characteristics, continuously ensuring the high-performance operation of the database management system.
[0251] The data query method provided in this application can be applied to e-commerce, gaming applications, payment platforms, social platforms, logistics, finance and other scenarios. This application does not limit it. The following explanation uses logistics and finance scenarios as examples.
[0252] (I) Logistics scenario.
[0253] Step 1: In the preprocessing stage, obtain at least one query statement for the logistics database.
[0254] The logistics database is used to store logistics data, such as order information tables, inventory record tables, transportation status tables, delivery network tables, vehicle resource tables, and personnel resource tables.
[0255] Optionally, during the preprocessing stage, at least one query statement is pre-constructed for each logistics order in the logistics database, such as a query statement for querying logistics delivery details.
[0256] Step 2: Generate at least one execution plan corresponding to at least one query statement through the plan generation model, and obtain the plan prompt information corresponding to the execution plan that meets the preset execution requirements.
[0257] A plan generation model is a pre-trained machine learning model used to generate execution plans.
[0258] Step 3: During the operation of the logistics database, receive the first query request sent by the logistics platform's client.
[0259] For example, when a user triggers a query operation on the client side of the logistics platform, the client will send a first query request to the database management system of the logistics database. For example, the first query request is used to request the logistics delivery details of a specified logistics order.
[0260] Step 4: Obtain the first query statement based on the first query request.
[0261] The first query statement is a query statement among at least one query statement involved in the aforementioned preprocessing stage. For example, parsing the first query request yields the first query statement, which belongs to at least one pre-constructed query statement. For instance, the first query statement is used to query the logistics delivery details of a specified logistics order.
[0262] Step 5: If the first query statement and the plan hint information of the first execution plan in the plan hint table match, determine the second execution plan based on the first query statement and the plan hint information.
[0263] Optionally, if the first query statement matches the plan hint information of the first execution plan in the plan hint table, the plan hint information of the first execution plan is inserted into the first query statement to obtain the second query statement. The second query statement is then input into the optimizer to obtain the second execution plan.
[0264] The second execution plan includes at least one second query operation.
[0265] Step 6: Execute the second execution plan in the logistics database to obtain the logistics query results.
[0266] That is, at least one second query operation is performed in the logistics database to obtain the logistics query result. For example, if the first query request is used to query the logistics delivery details of a specified logistics order, then the logistics query result refers to the logistics delivery details of the specified logistics order.
[0267] Step 7: Send the logistics query results back to the logistics platform's client.
[0268] For example, the database management system of the logistics database obtains the logistics query results and sends the logistics query results to the client of the logistics platform.
[0269] (ii) Financial scenarios.
[0270] Step 1: In the preprocessing stage, obtain at least one query statement for the financial database.
[0271] Among them, financial databases are used to store financial transaction information, such as user account tables, transaction record tables, risk assessment tables, etc.
[0272] Optionally, during the preprocessing stage, at least one query statement is pre-constructed for each account, such as a query statement for querying the account balance.
[0273] Step 2: Generate at least one execution plan corresponding to at least one query statement through the plan generation model, and obtain the plan prompt information corresponding to at least one execution plan that meets the preset execution requirements.
[0274] A plan generation model is a pre-trained machine learning model used to generate execution plans.
[0275] Step 3: During the operation of the financial database, receive the first query request sent by the client of the financial trading platform.
[0276] For example, when a query operation is triggered on the client side of a financial trading platform, a first query request is sent to the database management system of the financial database. For example, the first query request is used to request a query of the account balance of a specified account.
[0277] Step 4: Obtain the first query statement based on the first query request.
[0278] The first query statement is a query statement among at least one query statement involved in the aforementioned preprocessing stage. For example, parsing the first query request yields the first query statement, which belongs to at least one pre-constructed query statement. For instance, the first query statement is used to query the account balance of a specified account.
[0279] Step 5: If the first query statement and the plan hint information of the first execution plan in the plan hint table match, determine the second execution plan of the first query statement based on the first query statement and the plan hint information.
[0280] Optionally, if the first query statement matches the plan hint information of the first execution plan in the plan hint table, the plan hint information of the first execution plan is inserted into the first query statement to obtain the second query statement. The second query statement is then input into the optimizer to obtain the second execution plan.
[0281] The second execution plan includes at least one second query operation.
[0282] Step 6: Execute the second execution plan in the financial database to obtain the financial query results.
[0283] That is, at least one second query operation is performed in the logistics database to obtain the logistics query result. For example, if the first query request is used to request the account balance of a specified account, then the financial query result refers to the account balance of the specified account.
[0284] Step 7: Feedback the financial query results to the client of the financial trading platform.
[0285] For example, after the database management system of the financial database obtains the logistics query results, it sends the financial query results to the client of the financial transaction platform.
[0286] Figure 8 is a structural block diagram of a data query apparatus provided in an exemplary embodiment of this application. As shown in Figure 8, the apparatus includes:
[0287] The first processing module 810 is used to generate a first execution plan for a first query statement through a plan generation model. The first execution plan includes a first query operation corresponding to the first query statement. The plan generation model is a machine learning model used to generate the execution plan.
[0288] The first processing module 810 is used to obtain plan prompt information corresponding to the first execution plan when the first execution plan meets the preset execution requirements. The plan prompt information is used to prompt the operation mode of the first query operation. The second processing module 820 is used to determine a second execution plan for the first query statement based on the first query statement and the plan prompt information during the operation of the database. The second execution plan includes the database performing at least one second query operation corresponding to the first query statement.
[0289] The second processing module 820 is used to execute the at least one second query operation in the database to obtain the query result of the first query statement.
[0290] In some embodiments, the first processing module 810 is configured to obtain the plan prompt information corresponding to the first execution plan when the first simulation duration of the first execution plan is less than a preset first execution duration, wherein the first simulation duration represents the duration required to simulate the execution of the first execution plan in the database.
[0291] In some embodiments, the first processing module 810 is configured to obtain at least two simulated running conditions; after simulating the database according to the at least two simulated running conditions, obtain first candidate durations corresponding to the at least two simulated running conditions respectively; the first candidate duration represents the duration required to execute the first execution plan in the database; and determine the first simulation duration based on the average duration or weighted average duration of the at least two first candidate durations.
[0292] In some embodiments, the first processing module 810 is configured to obtain the plan prompt information corresponding to the first execution plan when the second simulation duration of the first query operation is less than the preset second execution duration, wherein the second simulation duration represents the execution duration required for the first query operation when the first execution plan is simulated in the database.
[0293] In some embodiments, the first processing module 810 is configured to, when the first execution plan meets the preset execution requirements, parse the first execution plan to obtain first operation data corresponding to the first query operation; obtain a prompt information template corresponding to the operation mode of the first query operation; and generate the plan prompt information based on the first operation data and the prompt information template.
[0294] In some embodiments, the second processing module 820 is configured to insert the plan suggestion information into the first query statement to obtain a second query statement; and to analyze the second query statement through an optimizer to obtain a second execution plan, wherein the optimizer includes at least one of a rule-based optimizer and a cost-based optimizer.
[0295] In some embodiments, the second processing module 820 is configured to determine the insertion position of the plan prompt information in the first query statement; insert the plan prompt information at the insertion position of the first query statement to obtain the second query statement.
[0296] In some embodiments, the second processing module 820 is configured to disable the plan prompt information when the second execution plan meets the abnormal execution conditions; wherein the conditions under which the second execution plan meets the abnormal execution conditions include at least one of the following:
[0297] The execution duration of the second execution plan is longer than the preset duration;
[0298] The second execution plan consumes more resources than the preset resource amount during execution;
[0299] The query result corresponding to the second execution plan is incorrect.
[0300] In some embodiments, the second processing module 820 is configured to, when the second execution plan meets the abnormal execution conditions, obtain the execution record data corresponding to the second execution plan; and use the execution record data corresponding to the second execution plan as negative sample data to update the model parameters of the plan generation model.
[0301] In some embodiments, the first processing module 810 is configured to generate a third execution plan for the first query statement according to a preset short query rule when the first query statement meets the short query requirement, the third execution plan including at least one third query operation corresponding to the first query statement; and execute the at least one third query operation in the database to obtain the query result of the first query statement.
[0302] In some embodiments, the second processing module 820 is configured to, during the process of the plan prompt information taking effect, update the first execution plan based on the current running status of the database through the plan generation model if the plan prompt information meets preset requirements; determine updated plan prompt information based on the updated first execution plan; and, during the operation of the database, determine the second execution plan of the first query statement based on the first query statement and the updated plan prompt information.
[0303] In some embodiments, the plan prompt information meeting preset requirements includes at least one of the following:
[0304] The time taken to generate the plan notification information is longer than the preset time.
[0305] The current operating status of the database meets the requirements for status change.
[0306] In some embodiments, the at least one second query operation includes the first query operation; the first execution plan and the second execution plan are the same, or the first execution plan and the second execution plan are different.
[0307] In some embodiments, the first processing module 810 is configured to obtain at least two candidate execution plans corresponding to the first query statement; generate plan feature representations corresponding to the at least two candidate execution plans respectively through the plan generation model; analyze the plan feature representations through the plan generation model to obtain quality scores corresponding to the at least two candidate execution plans respectively; and determine the candidate execution plan whose quality score meets the preset quality requirements as the first execution plan among the at least two candidate execution plans.
[0308] It should be noted that the apparatus provided in the above embodiments is only illustrated by the division of the above functional modules when implementing its functions. In actual applications, the above functions can be assigned to different functional modules as needed, that is, the content structure of the device can be divided into different functional modules to complete all or part of the functions described above. In addition, the apparatus and method embodiments provided in the above embodiments belong to the same concept, and the specific implementation process can be found in the method embodiments, which will not be repeated here.
[0309] Please refer to Figure 9, which shows a structural block diagram of a computer device provided in one embodiment of this application. This computer device 900 may be a terminal or server in the computer system shown in Figure 1, used to implement the data query method provided in the above embodiments. Specifically:
[0310] Typically, computer device 900 includes a processor 901 and a memory 902.
[0311] Processor 901 may include one or more processing cores, such as a quad-core processor, an octa-core processor, etc. Processor 901 may be implemented using at least one hardware form selected from DSP (Digital Signal Processing), FPGA (Field Programmable Gate Array), and PLA (Programmable Logic Array). Processor 901 may also include a main processor and a coprocessor. The main processor, also known as a CPU (Central Processing Unit), is used to process data in the wake-up state; the coprocessor is a low-power processor used to process data in the standby state. In some embodiments, processor 901 may integrate a GPU (Graphics Processing Unit), which is responsible for rendering and drawing the content required to be displayed on the screen. In some embodiments, processor 901 may also include an AI (Artificial Intelligence) processor, which is used to handle computational operations related to machine learning.
[0312] The memory 902 may include one or more computer-readable storage media, which may be non-transitory. The memory 902 may also include high-speed random access memory and non-volatile memory, such as one or more disk storage devices or flash memory devices. In some embodiments, the non-transitory computer-readable storage media in the memory 902 are used to store a computer program configured to be executed by one or more processors to implement the data query method described above.
[0313] In some embodiments, the computer device 900 may also optionally include other components 903, such as a peripheral device interface and at least one peripheral device. The processor 901, memory 902, and peripheral device interface can be connected via a bus or signal lines. Each peripheral device can be connected to the peripheral device interface via a bus, signal lines, or a circuit board. Specifically, the peripheral device includes at least one of the following: radio frequency circuitry, a display screen, audio circuitry, and a power supply.
[0314] Those skilled in the art will understand that the structure shown in FIG9 does not constitute a limitation on the computer device 900, and may include more or fewer components than shown, or combine certain components, or employ different component arrangements.
[0315] In an exemplary embodiment, a computer-readable storage medium is also provided, wherein a computer program is stored in the storage medium, and the computer program, when executed by a processor, implements the above-described data query method. Optionally, the computer-readable storage medium may include: ROM (Read-Only Memory), RAM (Random Access Memory), SSD (Solid State Drives), or optical disc, etc. The random access memory may include ReRAM (Resistance Random Access Memory) and DRAM (Dynamic Random Access Memory).
[0316] In an exemplary embodiment, a computer program product is also provided, the computer program product including a computer program stored in a computer-readable storage medium. A processor of a computer device reads the computer program from the computer-readable storage medium, and the processor executes the computer program, causing the computer device to perform the data query method described above.
[0317] It should be understood that "multiple" as used herein refers to two or more. "And / or" describes the relationship between related objects, indicating that three relationships can exist. For example, A and / or B can represent: A alone, A and B simultaneously, or B alone. The character " / " generally indicates that the preceding and following related objects are in an "or" relationship. Furthermore, the step numbers described herein are merely illustrative of one possible execution order. In some other embodiments, the steps may not be executed in numerical order, such as two steps with different numbers being executed simultaneously, or two steps with different numbers being executed in the reverse order of the illustration. This application does not limit this.
[0318] The above description is merely an exemplary embodiment of this application and is not intended to limit this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.
Claims
1. A data query method, executed by a computer device, the method comprising: A first execution plan for the first query statement is generated using a plan generation model. The first execution plan includes the first query operation corresponding to the first query statement. The plan generation model is a machine learning model used to generate execution plans; If the first execution plan meets the preset execution requirements, obtain the plan prompt information corresponding to the first execution plan. The plan prompt information is used to prompt the operation method of the first query operation. During the operation of the database, based on the first query statement and the plan prompt information, a second execution plan for the first query statement is determined, and the second execution plan includes at least one second query operation corresponding to the first query statement; Execute the at least one second query operation in the database to obtain the query result of the first query statement.
2. The method according to claim 1, wherein, The step of obtaining the plan prompt information corresponding to the first execution plan when the first execution plan meets the preset execution requirements includes: If the first simulation duration of the first execution plan is less than the preset first execution duration, the plan prompt information corresponding to the first execution plan is obtained, where the first simulation duration represents the duration required to simulate the execution of the first execution plan in the database.
3. The method according to claim 1 or 2, wherein, The method further includes: Obtain at least two simulation run conditions; After simulating the database according to the at least two simulated running conditions, a first candidate duration corresponding to each of the at least two simulated running conditions is obtained; the first candidate duration represents the duration required to execute the first execution plan in the database; The first simulation duration is determined based on the average duration or weighted average duration of at least two first candidate durations.
4. The method according to any one of claims 1 to 3, wherein, The step of obtaining the plan prompt information corresponding to the first execution plan when the first execution plan meets the preset execution requirements includes: If the second simulation duration of the first query operation is less than the preset second execution duration, the plan prompt information corresponding to the first execution plan is obtained. The second simulation duration represents the execution duration required for the first query operation when the first execution plan is simulated in the database.
5. The method according to any one of claims 1 to 4, wherein, The step of obtaining the plan prompt information corresponding to the first execution plan when the first execution plan meets the preset execution requirements includes: If the first execution plan meets the preset execution requirements, the first execution plan is parsed to obtain the first operation data corresponding to the first query operation; Obtain the prompt information template corresponding to the operation method of the first query operation; The plan prompt information is generated based on the first operation data and the prompt information template.
6. The method according to any one of claims 1 to 5, wherein, The step of determining the second execution plan for the first query statement based on the first query statement and the plan suggestion information includes: Insert the plan prompt information into the first query statement to obtain the second query statement; The second query statement is analyzed by an optimizer to obtain the second execution plan. The optimizer includes at least one of a rule-based optimizer and a cost-based optimizer.
7. The method according to any one of claims 1 to 6, wherein, The step of inserting the plan prompt information into the first query statement to obtain the second query statement includes: Determine the insertion position of the plan prompt information in the first query statement; The plan hint information is inserted at the insertion position of the first query statement to obtain the second query statement.
8. The method according to any one of claims 1 to 7, wherein, The method further includes: If the second execution plan meets the abnormal execution conditions, the plan prompt information is disabled; wherein, the second execution plan meets the abnormal execution conditions including at least one of the following: The execution duration of the second execution plan is longer than the preset duration; The second execution plan consumes more resources than the preset resource amount. The query result corresponding to the second execution plan is incorrect.
9. The method according to any one of claims 1 to 8, wherein, The plan generation model is a comparative model; the method further includes: If the second execution plan meets the abnormal execution conditions, obtain the execution record data corresponding to the second execution plan; The execution record data corresponding to the second execution plan is used as negative sample data to update the model parameters of the plan generation model.
10. The method according to any one of claims 1 to 9, wherein, The method further includes: If the first query statement meets the short query requirement, a third execution plan for the first query statement is generated according to the preset short query rules. The third execution plan includes at least one third query operation corresponding to the first query statement. The at least one third query operation is executed in the database to obtain the query result of the first query statement.
11. The method according to any one of claims 1 to 10, wherein, The method further includes: During the process of the plan prompt information taking effect, if the plan prompt information meets the preset requirements, the first execution plan is updated based on the current running status of the database through the plan generation model; The updated plan prompt information is determined based on the updated first execution plan; During the operation of the database, the second execution plan for the first query statement is determined based on the first query statement and the updated plan hint information.
12. The method according to any one of claims 1 to 11, wherein, The plan prompt information meets the preset requirements in at least one of the following situations: The time taken to generate the plan notification information is longer than the preset time. The current operating status of the database meets the requirements for status change.
13. The method according to any one of claims 1 to 12, wherein, The at least one second query operation includes the first query operation; the first execution plan and the second execution plan are the same, or the first execution plan and the second execution plan are different.
14. The method according to any one of claims 1 to 13, wherein, The process of generating the first execution plan for the first query statement using the plan generation model includes: Obtain at least two candidate execution plans corresponding to the first query statement; The plan generation model generates plan feature representations corresponding to the at least two candidate execution plans, respectively. By analyzing the plan feature representation using the plan generation model, quality scores are obtained for each of the at least two candidate execution plans. Among the at least two candidate execution plans, the candidate execution plan whose quality score meets the preset quality requirements is determined as the first execution plan.
15. A data query device, the device comprising: The first processing module is used to generate a first execution plan for a first query statement through a plan generation model. The first execution plan includes a first query operation corresponding to the first query statement. The plan generation model is a machine learning model used to generate execution plans; The first processing module is used to obtain plan prompt information corresponding to the first execution plan when the first execution plan meets the preset execution requirements. The plan prompt information is used to prompt the operation method of the first query operation. The second processing module is used to determine a second execution plan for the first query statement based on the first query statement and the plan prompt information during the operation of the database. The second execution plan includes at least one second query operation corresponding to the first query statement. The second processing module is used to execute the at least one second query operation in the database to obtain the query result of the first query statement.
16. The apparatus according to claim 15, wherein, The first processing module is further configured to obtain the plan prompt information corresponding to the first execution plan when the first simulation duration of the first execution plan is less than the preset first execution duration, wherein the first simulation duration represents the duration required to simulate the execution of the first execution plan in the database.
17. The apparatus according to claim 15 or 16, wherein, The first processing module is further configured to parse the first execution plan and obtain the first operation data corresponding to the first query operation when the first execution plan meets the preset execution requirements; Obtain the prompt information template corresponding to the operation method of the first query operation; The plan prompt information is generated based on the first operation data and the prompt information template.
18. A computer device comprising a processor and a memory, the memory storing a computer program, the computer program being loaded and executed by the processor to implement the data query method as claimed in any one of claims 1 to 14.
19. A computer-readable storage medium storing a computer program, the computer program being loaded and executed by a processor to implement the data query method as described in any one of claims 1 to 14.
20. A computer program product comprising a computer program stored in a computer-readable storage medium, wherein a processor reads from and executes the computer program to implement the data query method as described in any one of claims 1 to 14.
