Database query plan optimization method and system

Abstract

The invention discloses a database query plan optimization method and system, and belongs to the technical field of databases and artificial intelligence. AI model training: training the model by using the preprocessed historical query data according to a specific model architecture, a training mode and resource and time arrangement, and dynamically adjusting the model according to the change of the database; query analysis: evaluating the complexity of the query and the possibly involved data volume, and allocating a complexity level and a data access volume range for the query; query plan generation: generating and evaluating a plurality of candidate query plans based on prediction and query analysis results of the AI model, and selecting an optimal query plan as a final execution scheme; and performing query execution and monitoring. According to the method, the query execution time can be greatly shortened, and the database response speed is increased; system resources are utilized more effectively; dynamic changes of the database can be adapted, and the query plan is automatically updated according to structure and data distribution adjustment.

Description

Technical Field

[0001] The present invention relates to the field of database technology and artificial intelligence technology, and in particular to a database query plan optimization method and system. Background Art

[0002] In traditional database queries, query plan generation mainly relies on the rules and algorithms built into the database management system. However, these methods often have difficulty in quickly generating optimal query plans when faced with complex query scenarios and large-scale data, resulting in low query efficiency and consumption of a large amount of system resources. With the continuous growth of data volume and the increasing complexity of query requirements, there is an urgent need for a more intelligent and efficient method to optimize database query plans. Summary of the invention

[0003] The technical task of the present invention is to address the above deficiencies and provide a database query plan optimization method and system, which can significantly shorten the query execution time and improve the database response speed; reduce system resource consumption, more effectively utilize system resources, and improve overall performance; and adapt to the dynamic changes of the database and automatically update the query plan according to the structure and data distribution.

[0004] The technical solution adopted by the present invention to solve the technical problem is:

[0005] A method for optimizing a database query plan, the implementation of which includes the following steps:

[0006] Data collection: Continuously use a combination of scheduled tasks and event triggers to collect various types of information from the database, and organize and classify the collected data for storage;

[0007] AI model training: Use pre-processed historical query data to train the model according to the specific model architecture, training method, resources and time schedule, and dynamically adjust the model according to changes in the database;

[0008] Query analysis: After receiving a new query request, the query statement is fully analyzed according to a specific process to extract key information, evaluate the complexity of the query and the amount of data that may be involved, and assign a complexity level and data access range to the query;

[0009] Query plan generation: Based on the prediction of the AI ​​model and the results of query analysis, multiple candidate query plans are generated and evaluated, and the optimal query plan is selected as the final execution plan;

[0010] Query execution and monitoring: Execute query operations strictly according to the generated query plan, and continuously use the execution monitoring module to monitor the query performance indicators in real time during the query execution process, and make timely adjustments when abnormalities are found.

[0011] The present invention uses artificial intelligence technology, especially advanced methods such as deep reinforcement learning and natural language processing, to intelligently optimize database query plans. By training intelligent models, it is possible to quickly analyze query requirements and database characteristics, predict and generate optimal query execution paths and strategies, and significantly shorten query execution time, allowing users to obtain required data in a shorter time, meeting the high requirements for data real-time in modern business. Through intelligent query plan optimization, the system can use resources more efficiently. For example, by reasonably selecting indexes, optimizing data reading methods, and reducing unnecessary computing operations, the utilization rate of resources such as CPU, memory, and disk can be reduced, the resource utilization efficiency of the system can be improved, and sufficient resource space can be reserved for other tasks.

[0012] Furthermore, the model architecture in the AI ​​model training includes adopting a deep reinforcement learning model, combining a neural network and a Monte Carlo tree search algorithm. During the training process, a reward function is defined with the weighted sum of the proportion of shortened query execution time, the proportion of reduced resource consumption, and the proportion of improved query result accuracy as the reward value.

[0013] Furthermore, the candidate query plan generation in the query plan generation is based on different execution path strategies and AI model predictions. The evaluation adopts simulated execution and pre-established evaluation models to obtain key evaluation data such as estimated execution time, resource consumption, and possible query result accuracy. The optimal query plan is selected using an intelligent selection algorithm and comprehensively considers factors such as the shortest execution time and the lowest resource consumption.

[0014] Furthermore, the query execution and monitoring execute each query step strictly according to the query plan to ensure the accuracy and completeness of the data. The monitoring process records the execution status and resource usage of the query in real time. If an abnormality is found, it will automatically trigger the mechanism of regenerating the query plan or adjusting the system resource allocation measures.

[0015] The present invention also claims protection for a database query plan optimization system, comprising:

[0016] The data collection module is used to regularly scan the metadata of the database and record the user's query statements and the corresponding execution time and resource consumption information in real time, and pre-process the collected data;

[0017] The AI ​​model training module uses a deep reinforcement learning model architecture combined with a neural network and a Monte Carlo tree search algorithm to train the model using preprocessed historical query data to generate a model that can predict the optimal query plan;

[0018] The query analysis module uses natural language processing technology to perform word segmentation, syntax analysis, table and field matching, and complexity and data volume estimation on newly received query requests;

[0019] The query plan generation module generates multiple candidate query plans based on the prediction results of the AI ​​model and query analysis information, and evaluates and selects the optimal query plan as the final execution plan;

[0020] The execution monitoring module monitors the query execution status in real time during the query execution process and adjusts the query plan or system resource allocation in a timely manner if an abnormality is found;

[0021] The system can implement the above-mentioned database query plan optimization method.

[0022] Furthermore, the data acquisition module includes:

[0023] Metadata collection unit, which regularly scans the metadata of the database, including table structure, index information, etc.;

[0024] The query statement collection unit records the user's query statements and corresponding execution time, resource consumption and other information in real time;

[0025] Data preprocessing unit, which cleans, standardizes and extracts features from the collected data;

[0026] The AI ​​model training module includes:

[0027] The data preprocessing submodule cleans, standardizes, and converts historical query data into vector representations that can be understood by the model;

[0028] The model building submodule builds a multi-layer neural network structure including input layer, hidden layer and output layer, and combines it with the Monte Carlo tree search algorithm;

[0029] The training submodule uses a large number of simulated query execution scenarios and defined reward functions to train the model, and uses optimization algorithms to continuously adjust model parameters. It also performs incremental training regularly and uses early stopping to prevent overfitting.

[0030] Furthermore, the query analysis module includes:

[0031] A word segmentation processing unit that uses a word segmentation algorithm in natural language processing technology to split the query statement into independent words or phrases;

[0032] Syntax parsing unit, which identifies the grammatical components and logical relations of the query statement through a syntax analyzer;

[0033] A table and field matching unit matches and verifies the tables and fields involved in the query statement according to the database metadata information;

[0034] The complexity and data volume estimation unit comprehensively considers multiple factors to evaluate the complexity of the query and the amount of data that may be involved;

[0035] The query plan generation module includes:

[0036] A candidate plan generation unit generates multiple candidate query plans based on different execution path strategies and AI model predictions;

[0037] The evaluation unit uses simulated execution and pre-established evaluation models to quickly evaluate each candidate query plan and obtain evaluation data, including estimated execution time, resource consumption, and query result accuracy;

[0038] The selection unit selects the optimal query plan using an intelligent selection algorithm.

[0039] Furthermore, the execution monitoring module includes:

[0040] The real-time monitoring unit records the query execution status, resource usage, and accuracy of intermediate results every second by deploying a monitoring agent on the database server;

[0041] The exception handling unit takes corresponding adjustment measures when it finds an exception in query execution, including regenerating the query plan or adjusting system resource allocation;

[0042] The feedback unit feeds back the performance data of query execution to the AI ​​model training module and the query analysis module, and receives user feedback on the query results.

[0043] The present invention also claims protection for a database query plan optimization implementation device, comprising: at least one memory and at least one processor;

[0044] The at least one memory is used to store a machine-readable program;

[0045] The at least one processor is used to call the machine-readable program to implement the above method.

[0046] The present invention also claims protection for a computer-readable medium having computer instructions stored thereon, which can implement the above method when executed by a processor.

[0047] Compared with the prior art, the database query plan optimization method and system of the present invention have the following advantages:

[0048] Beneficial effects:

[0049] Significantly improve query efficiency. Through intelligent optimization of AI models, the optimal query plan can be quickly generated, greatly shortening the query execution time. For example, in complex enterprise-level database application scenarios, for queries involving multiple table associations and complex conditions, the average execution time is shortened from several minutes to tens of seconds or even shorter, which improves the response speed of the database and meets the user's demand for real-time data query.

[0050] Reduce system resource consumption. The optimized query plan can more effectively utilize system resources and reduce unnecessary resource waste. During query execution, the utilization of resources such as CPU, memory, and disk I / O is reduced through intelligent resource allocation and query plan adjustment. For example, when querying large-scale data, the CPU utilization rate is reduced by an average of 30%-50%, and the memory usage is reduced by 20%-40%, which improves the overall performance and stability of the system, while also reserving sufficient resources for the execution of other tasks.

[0051] Adapt to dynamic changes. It can automatically update the query plan according to the changes in the database structure and the dynamic adjustment of data distribution to maintain the efficiency of the query. When the database structure changes (such as adding a new table, modifying the table structure, etc.) or the data distribution changes significantly (such as a sharp increase in data volume or changes in data characteristics), the system can retrain the model and generate a query plan that adapts to the new environment in a short period of time (for example, within 24 hours) without manual intervention, ensuring the continuous optimization and adaptability of the system. BRIEF DESCRIPTION OF THE DRAWINGS

[0052] Figure 1 It is a flowchart of a database query plan optimization method provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0053] The present invention will be further described below in conjunction with specific embodiments.

[0054] An embodiment of the present invention provides a method for optimizing a database query plan, and the implementation of the method includes the following steps:

[0055] Data collection: Continuously use a combination of scheduled tasks and event triggers to collect various types of information from the database, and organize and classify the collected data for storage;

[0056] AI model training: Use pre-processed historical query data to train the model according to the specific model architecture, training method, resources and time schedule, and dynamically adjust the model according to changes in the database;

[0057] Query analysis: After receiving a new query request, the query statement is fully analyzed according to a specific process to extract key information, evaluate the complexity of the query and the amount of data that may be involved, and assign a complexity level and data access range to the query;

[0058] Query plan generation: Based on the prediction of the AI ​​model and the results of query analysis, multiple candidate query plans are generated and evaluated, and the optimal query plan is selected as the final execution plan;

[0059] Query execution and monitoring: Execute query operations strictly according to the generated query plan, and continuously use the execution monitoring module to monitor the query performance indicators in real time during the query execution process, and make timely adjustments when abnormalities are found.

[0060] Among them, the model architecture in the AI ​​model training includes the use of a deep reinforcement learning model combined with a neural network and a Monte Carlo tree search algorithm. During the training process, a reward function is defined with the weighted sum of the proportion of query execution time reduction, the proportion of resource consumption reduction, and the proportion of query result accuracy improvement as the reward value.

[0061] The candidate query plan generation in the query plan generation is based on different execution path strategies and the prediction of the AI ​​model. The evaluation adopts simulated execution and a pre-established evaluation model to obtain key evaluation data such as the estimated execution time, resource consumption, and possible query result accuracy. The optimal query plan is selected using an intelligent selection algorithm and comprehensively considers factors such as the shortest execution time and the lowest resource consumption.

[0062] The query execution and monitoring are carried out in strict accordance with the query plan for each query step to ensure the accuracy and completeness of the data. The monitoring process records the query execution status and resource usage in real time. If an abnormality is found, it will automatically trigger the mechanism of regenerating the query plan or adjusting the system resource allocation measures.

[0063] The specific implementation process of this method is as follows:

[0064] S1. Data collection:

[0065] We continuously use a combination of scheduled tasks and event triggers to collect various types of database information. Scheduled tasks perform comprehensive data collection once a day, including database structure information, data distribution characteristics, etc. When the database structure changes (such as adding a new table, modifying the table structure, etc.) or a new query is executed, the corresponding data collection operation is immediately triggered to obtain the latest query statements and execution information.

[0066] The collected data is sorted and stored in categories. It is stored in specially designed system tables according to data type (such as metadata, query statement data, resource consumption data, etc.) and time sequence, so that it can be quickly retrieved and analyzed later.

[0067] S2. AI model training:

[0068] According to the above model architecture, training method, resources and time arrangement, the model is trained using preprocessed historical query data. The model is fully trained regularly (such as weekly) to learn the overall characteristics and query patterns of the database. At the same time, after the daily incremental data collection is completed, a small-scale incremental training is performed to enable the model to adapt to new data characteristics and changes in query requirements in a timely manner.

[0069] Dynamically adjust the structure and parameters of the model according to changes in the database and the evolution of query patterns. For example, if a special data type is added to the database or a new grammatical structure or business logic appears in the query, the input layer and hidden layer of the model need to be adjusted and optimized accordingly, and the corresponding feature processing units and neuron connections need to be added.

[0070] S3, query analysis:

[0071] According to the detailed process of the query analysis module, a comprehensive analysis of the query statement is completed in a very short time (e.g. within 80 milliseconds) after receiving a new query request, including word segmentation, syntax parsing, table and field matching, and complexity and data volume estimation, so as to quickly provide accurate information basis for subsequent query plan generation.

[0072] Extract key elements of the query, such as the target table, conditional fields, and associated relationships, and store these key elements in a temporary cache in the form of structured data to facilitate rapid access and processing during subsequent query plan generation and optimization.

[0073] Based on the analysis results, the query is assigned a complexity level (such as simple, medium, complex) and a rough range of data access volume (such as small amount of data, medium amount of data, large amount of data) so that subsequent query plan generation and resource allocation modules can perform targeted optimization based on this information.

[0074] S4. Query plan generation:

[0075] Based on the query analysis results and AI model predictions, multiple feasible candidate query plans are generated by comprehensively considering various factors. At least 5 candidate query plans are generated based on different execution path strategies (such as index selection, table connection order, data screening method, etc.), and each candidate plan contains detailed query steps and resource usage prediction information.

[0076] Quickly and accurately evaluate each candidate query plan. Using a pre-established evaluation model (a model that predicts execution time and resource consumption based on historical data training), key evaluation data such as the estimated execution time, resource consumption, and possible query result accuracy of each candidate query plan can be obtained in a very short time (e.g., within 100 seconds).

[0077] According to the evaluation results, an intelligent selection algorithm (such as an algorithm based on multi-objective optimization) is used to select the optimal query plan as the final execution plan. On the basis of considering the shortest execution time and the minimum resource consumption, the accuracy and stability of the query results are also taken into account to ensure that high-quality query services are provided to users.

[0078] S5. Query execution and monitoring:

[0079] Execute query operations strictly according to the generated query plan. Ensure that each query step is executed correctly according to the predetermined order and logic, and verify the accuracy and completeness of the data in real time during the execution process. For example, when executing table connection operations, check in real time whether the connection conditions are applied correctly and whether the data types match to prevent incorrect query results.

[0080] During the query execution process, the execution monitoring module is continuously used to monitor the query performance indicators in real time. Through close integration with the database server, the usage of resources such as CPU, memory, and disk I / O is obtained in real time, and these data are correlated with the progress and status information of query execution for analysis. For example, when the CPU usage rate is found to have suddenly increased, it is analyzed whether it is due to the high computational complexity of a query step, and whether it is necessary to adjust the query plan or optimize the execution method of the step.

[0081] If the query execution is abnormal or the performance is not ideal, take corresponding adjustment measures immediately. When the query execution time exceeds the preset threshold, the mechanism of regenerating the query plan is automatically triggered, so that the query plan generation module can regenerate a more optimized query plan according to the current system status and database status. If the resource utilization rate is too high, adjust the system resource allocation through the dynamic resource allocation algorithm, such as reclaiming resources from other low-priority tasks to allocate to the current query, or adopting more efficient memory management strategies (such as data compression, temporary result cache cleaning, etc.) to reduce resource consumption.

[0082] An embodiment of the present invention further provides a database query plan optimization system, which can implement the database query plan optimization method described in the above embodiment.

[0083] The system includes:

[0084] 1. Data collection module, which is used to regularly scan the metadata of the database and record the user's query statements and the corresponding execution time and resource consumption information in real time, and pre-process the collected data.

[0085] The data acquisition module includes:

[0086] Metadata collection unit, which regularly scans the metadata of the database, including table structure, index information, etc.;

[0087] The query statement collection unit records the user's query statements and corresponding execution time, resource consumption and other information in real time;

[0088] The data preprocessing unit cleans, standardizes and extracts features from the collected data.

[0089] 2. The AI ​​model training module uses a deep reinforcement learning model architecture combined with a neural network and a Monte Carlo tree search algorithm, and uses preprocessed historical query data for model training to generate a model that can predict the optimal query plan.

[0090] AI model training modules include:

[0091] The data preprocessing submodule cleans, standardizes, and converts historical query data into vector representations that can be understood by the model;

[0092] The model building submodule builds a multi-layer neural network structure including input layer, hidden layer and output layer, and combines it with the Monte Carlo tree search algorithm;

[0093] The training submodule uses a large number of simulated query execution scenarios and defined reward functions to train the model, and uses optimization algorithms to continuously adjust model parameters. It also performs incremental training regularly and uses early stopping to prevent overfitting.

[0094] 3. The query analysis module uses natural language processing technology to perform word segmentation, syntax analysis, table and field matching, and complexity and data volume estimation on newly received query requests.

[0095] The query analysis module includes:

[0096] A word segmentation processing unit that uses a word segmentation algorithm in natural language processing technology to split the query statement into independent words or phrases;

[0097] Syntax parsing unit, which identifies the grammatical components and logical relations of the query statement through a syntax analyzer;

[0098] A table and field matching unit matches and verifies the tables and fields involved in the query statement according to the database metadata information;

[0099] The complexity and data volume estimation unit comprehensively considers multiple factors to evaluate the complexity of the query and the amount of data that may be involved.

[0100] 4. The query plan generation module generates multiple candidate query plans based on the prediction results of the AI ​​model and query analysis information, and evaluates and selects the optimal query plan as the final execution plan;

[0101] The query plan generation module includes:

[0102] A candidate plan generation unit generates multiple candidate query plans based on different execution path strategies and AI model predictions;

[0103] The evaluation unit uses simulated execution and pre-established evaluation models to quickly evaluate each candidate query plan and obtain evaluation data, including estimated execution time, resource consumption, and query result accuracy;

[0104] The selection unit selects the optimal query plan using an intelligent selection algorithm.

[0105] 5. Execution monitoring module: monitors the execution of queries in real time during the query execution process, and adjusts the query plan or system resource allocation in time if an abnormality is found;

[0106] The execution monitoring module includes:

[0107] The real-time monitoring unit records the query execution status, resource usage, and accuracy of intermediate results every second by deploying a monitoring agent on the database server;

[0108] The exception handling unit takes corresponding adjustment measures when it finds an exception in query execution, including regenerating the query plan or adjusting system resource allocation;

[0109] The feedback unit feeds back the performance data of query execution to the AI ​​model training module and the query analysis module, and receives user feedback on the query results.

[0110] The data collection module is used to collect database structure information, data distribution characteristics, historical query records and other data; the AI ​​model training module uses deep learning algorithms to build a query plan prediction model and continuously train and optimize it; when a new query request is received, the query analysis module parses the query statement to extract key information; the query plan generation module generates and selects the query plan with the best execution path and index based on the AI ​​model prediction results and query analysis information; during the query execution process, the execution monitoring module monitors in real time.

[0111] The specific implementation method of the system to achieve database query plan optimization is as follows:

[0112] S1. The data acquisition module is responsible for collecting database structure information, data distribution characteristics, historical query records and other related data.

[0113] Regularly scan the database metadata to obtain table structure, index information, etc. For example, perform a comprehensive metadata scan every day during a period of time when the database load is low (such as 2 am to 4 am).

[0114] Record the user's query statements and the corresponding execution time, resource consumption, etc. For each query, record in detail its start time, end time, database tables involved, indexes used, and CPU, memory and other resource usage.

[0115] S2, AI model training module, adopts deep reinforcement learning model architecture, combines neural network and Monte Carlo tree search algorithm training to generate the optimal query plan strategy.

[0116] First, the collected historical query data is preprocessed, including data cleaning (removing noise data, outliers and duplicate data), standardization (processing data according to a unified format and range), and converting query statements and database features into vector representations that can be understood by the model.

[0117] Construct a multi-layer neural network structure, including input layer, hidden layer and output layer. The input layer receives the query statement vector and database feature vector, the hidden layer performs deep encoding and feature extraction on the data through multiple neurons, and the output layer predicts the optimal query plan action.

[0118] During the training process, the Monte Carlo tree search algorithm is used for strategy optimization. By simulating a large number of query execution scenarios, starting from the current state, different query plan paths are continuously explored, and the value of each action is evaluated based on the simulation results. A reward function is defined, with the weighted sum of the query execution time reduction ratio, resource consumption reduction ratio, and query result accuracy improvement ratio as the reward value, to guide the model to learn the optimal query plan strategy.

[0119] Use optimization algorithms (such as Adam optimizer) to continuously adjust the parameters of the model, set the initial learning rate to 0.001, and adopt a learning rate decay strategy. After a certain number of training steps (for example, 1000 steps), the learning rate is reduced to 0.9 times the original.

[0120] To adapt to the dynamic changes of the database, new historical query data is introduced regularly (such as every week) for incremental training, and the early stopping method is used (training is stopped when the performance of the model on the validation set has not improved significantly for multiple consecutive training cycles) to prevent overfitting.

[0121] In terms of training resource requirements, during the initial model training phase, a high-performance server is required, equipped with at least 64GB of memory, a 32-core processor, and a 2TB solid-state drive to store training data and model files. As the model is iterated and the amount of data increases, the server resources can be expanded as needed.

[0122] In terms of training time, when training the model for the first time, if the historical query data of the past year (assuming the data volume is about 1TB) is used for training, it will take about 72 hours on the server with the above configuration to complete the initial training and make it basically usable. After that, as new data is continuously added and the model is regularly updated, each update training takes about 6-10 hours.

[0123] S3, query analysis module, analyzes the user's query statement, obtains the key features in the query statement and the metadata features of the related tables, and estimates the amount of data that may be involved.

[0124] When a new query request is received, the query statement is first analyzed grammatically and semantically using the YACC parser and LEX lexical analyzer. The grammatical components such as the subject, predicate, and object in the query statement, as well as various conditional expressions and logical operators, are identified. For example, the target tables of the query are determined, what the conditional fields are, and the logical relationships between the various conditions (such as "and", "or", "not", etc.).

[0125] Then, the tables and fields involved in the query statement are matched and verified based on the database metadata information, including table structure, field type, index information, etc. Ensure that the tables and fields specified in the query statement actually exist in the database and have the correct data type.

[0126] Further analyze the complexity of the query. Comprehensively evaluate the complexity of the query based on factors such as the number of tables involved in the query statement, the number and complexity of conditions, and whether it contains subqueries. For example, if the query involves the association of multiple tables and the conditions are relatively complex, it is considered a complex query; if it is just a simple single-table query with relatively simple conditions, it is considered a simple query.

[0127] Finally, estimate the amount of data that may be involved. Based on the query conditions and database statistics (such as the number of rows in each table, data distribution, etc.), roughly estimate the amount of data that the query may need to access. For example, for a query with the condition "query sales records within a certain time period in a sales table containing 1 million records", based on the data distribution and the range of the time period, it can be roughly estimated that the amount of data that may be involved is about 100,000.

[0128] S4: Query plan generation module generates an optimized query plan based on the query analysis information and the prediction results of the AI ​​model.

[0129] Based on the query analysis information and the prediction results of the AI ​​model, multiple candidate query plans are generated. For example, if the model predicts that for a certain query, the possible best execution path includes different strategies such as first creating a temporary index for the condition field and then querying, or first filtering an intermediate table and then associating it with other tables, then multiple candidate query plans are generated based on these strategies.

[0130] Each candidate query plan is evaluated, including key indicators such as estimated execution time and resource consumption. Based on the evaluation results, the optimal query plan is selected as the final execution plan. The query plan with the shortest estimated execution time and the lowest resource consumption is preferred. If the execution time and resource consumption of multiple candidate plans are slightly different, further factors such as query stability and impact on subsequent queries are considered for comprehensive evaluation and selection.

[0131] S5. Execution monitoring module: During the query execution process, the query execution status is monitored in real time.

[0132] Record the actual query execution time, resource consumption and other performance indicators. Record the query execution status and resource usage every second so that you can understand the query execution process and resource changes in detail.

[0133] If the query execution is abnormal, timely measures are taken to adjust it, such as regenerating the query plan or adjusting the system resource allocation. For example, if it is found that the execution time of a query is too long and exceeds the preset threshold (for example, a relatively reasonable execution time limit is set based on the complexity of the query and historical data statistics), the execution monitoring module will notify the query plan generation module to regenerate the query plan, or adjust the system resources, such as increasing the number of CPU cores or memory capacity allocated to the query.

[0134] The system uses deep learning algorithms such as neural networks to train models, and allows the model to learn the relationship between database features and query patterns based on historical query data; parses query statements to determine key elements and evaluates query complexity and data access volume; generates multiple candidate query plans and evaluates their estimated execution time and resource consumption to select the best solution. It can significantly shorten query execution time and improve database response speed. For example, in an enterprise-level database application scenario, it can shorten the execution time of complex queries involving multi-table associations from several minutes to a few seconds; it can reduce system resource consumption, use system resources more effectively, and improve overall performance; it can adapt to dynamic changes in the database and automatically update query plans based on structure and data distribution.

[0135] The embodiment of the present invention also provides a device for implementing database query plan optimization, comprising: at least one memory and at least one processor;

[0136] The at least one memory is used to store a machine-readable program;

[0137] The at least one processor is used to call the machine-readable program to implement the database query plan optimization method described in the above embodiment.

[0138] The embodiment of the present invention further provides a computer-readable medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the database query plan optimization method described in the above embodiment is implemented. Specifically, a system or device equipped with a storage medium can be provided, on which a software program code for implementing the functions of any of the above embodiments is stored, and a computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0139] In this case, the program code itself read from the storage medium can realize the function of any one of the above-mentioned embodiments, and thus the program code and the storage medium storing the program code constitute a part of the present invention.

[0140] The storage medium embodiments for providing the program code include a floppy disk, a hard disk, a magneto-optical disk, an optical disk (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), a magnetic tape, a non-volatile memory card, and a ROM. Alternatively, the program code can be downloaded from a server computer by a communication network.

[0141] In addition, it should be clear that the functions of any of the above embodiments can be implemented not only by executing the program code read by the computer, but also by enabling an operating system operating on the computer to complete part or all of the actual operations based on instructions from the program code.

[0142] In addition, it can be understood that the program code read from the storage medium is written to a memory provided in an expansion board inserted into the computer or written to a memory provided in an expansion unit connected to the computer, and then based on the instructions of the program code, a CPU installed on the expansion board or the expansion unit is enabled to perform part or all of the actual operations, thereby realizing the functions of any of the above-mentioned embodiments.

[0143] The present invention is shown and described in detail above through the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above multiple embodiments, those skilled in the art can know that the code review methods in the above different embodiments can be combined to obtain more embodiments of the present invention, and these embodiments are also within the protection scope of the present invention.

Claims

1. A method for optimizing a database query plan, characterized in that: The implementation of this method includes the following steps: Data collection: Continuously use a combination of scheduled tasks and event triggers to collect various types of information from the database, and organize and classify the collected data for storage; AI model training: Use pre-processed historical query data to train the model according to the specific model architecture, training method, resources and time schedule, and dynamically adjust the model according to changes in the database; Query analysis: After receiving a new query request, the query statement is fully analyzed according to a specific process to extract key information, evaluate the complexity of the query and the amount of data that may be involved, and assign a complexity level and data access range to the query; Query plan generation: Based on the prediction of the AI ​​model and the results of query analysis, multiple candidate query plans are generated and evaluated, and the optimal query plan is selected as the final execution plan; Query execution and monitoring: Execute query operations strictly according to the generated query plan, and continuously use the execution monitoring module to monitor the query performance indicators in real time during the query execution process, and make timely adjustments when abnormalities are found.

2. A database query plan optimization method according to claim 1, characterized in that: The model architecture in the AI ​​model training includes the use of a deep reinforcement learning model combined with a neural network and a Monte Carlo tree search algorithm. During the training process, a reward function is defined with the weighted sum of the proportion of shortened query execution time, the proportion of reduced resource consumption, and the proportion of improved query result accuracy as the reward value.

3. A database query plan optimization method according to claim 1, characterized in that: The candidate query plan generation in the query plan generation is based on different execution path strategies and the prediction of the AI ​​model. The evaluation adopts simulated execution and a pre-established evaluation model to obtain key evaluation data such as the estimated execution time, resource consumption, and possible query result accuracy. The optimal query plan is selected using an intelligent selection algorithm and comprehensively considers factors such as the shortest execution time and the lowest resource consumption.

4. A database query plan optimization method according to claim 1, characterized in that: The query execution and monitoring are carried out in strict accordance with the query plan for each query step to ensure the accuracy and completeness of the data. The monitoring process records the query execution status and resource usage in real time. If an abnormality is found, it will automatically trigger the mechanism of regenerating the query plan or adjusting the system resource allocation measures.

5. A database query plan optimization system, characterized in that: include: The data collection module is used to regularly scan the metadata of the database and record the user's query statements and the corresponding execution time and resource consumption information in real time, and pre-process the collected data; The AI ​​model training module uses a deep reinforcement learning model architecture combined with a neural network and a Monte Carlo tree search algorithm to train the model using preprocessed historical query data to generate a model that can predict the optimal query plan; The query analysis module uses natural language processing technology to perform word segmentation, syntax analysis, table and field matching, and complexity and data volume estimation on newly received query requests; The query plan generation module generates multiple candidate query plans based on the prediction results of the AI ​​model and query analysis information, and evaluates and selects the optimal query plan as the final execution plan; The execution monitoring module monitors the query execution status in real time during the query execution process and adjusts the query plan or system resource allocation in a timely manner if an abnormality is found; The system can implement the database query plan optimization method described in any one of claims 1 to 4.

6. A database query plan optimization system according to claim 5, characterized in that: The data acquisition module comprises: The metadata collection unit periodically scans the metadata of the database, including table structure and index information; The query statement collection unit records the user's query statements and the corresponding execution time and resource consumption information in real time; Data preprocessing unit, which cleans, standardizes and extracts features from the collected data; The AI ​​model training module includes: The data preprocessing submodule cleans, standardizes, and converts historical query data into vector representations that can be understood by the model; The model building submodule builds a multi-layer neural network structure including input layer, hidden layer and output layer, and combines it with the Monte Carlo tree search algorithm; The training submodule uses a large number of simulated query execution scenarios and defined reward functions to train the model, and uses optimization algorithms to continuously adjust model parameters. It also performs incremental training regularly and uses early stopping to prevent overfitting.

7. A database query plan optimization system according to claim 5, characterized in that: The query analysis module includes: A word segmentation processing unit that uses a word segmentation algorithm in natural language processing technology to split the query statement into independent words or phrases; Syntax parsing unit, which identifies the grammatical components and logical relations of the query statement through a syntax analyzer; A table and field matching unit matches and verifies the tables and fields involved in the query statement according to the database metadata information; The complexity and data volume estimation unit comprehensively considers multiple factors to evaluate the complexity of the query and the amount of data that may be involved; The query plan generation module includes: A candidate plan generation unit generates multiple candidate query plans based on different execution path strategies and AI model predictions; The evaluation unit uses simulated execution and pre-established evaluation models to quickly evaluate each candidate query plan and obtain evaluation data, including estimated execution time, resource consumption, and query result accuracy; The selection unit selects the optimal query plan using an intelligent selection algorithm.

8. A database query plan optimization system according to claim 5, characterized in that: The execution monitoring module comprises: The real-time monitoring unit records the query execution status, resource usage, and accuracy of intermediate results every second by deploying monitoring agents on the database server; The exception handling unit takes corresponding adjustment measures when it finds an exception in query execution, including regenerating the query plan or adjusting system resource allocation; The feedback unit feeds back the performance data of query execution to the AI ​​model training module and the query analysis module, and receives user feedback on the query results.

9. A database query plan optimization implementation device, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is used to call the machine-readable program to implement the method described in any one of claims 1 to 4.

10. A computer-readable medium, characterized in that The computer readable medium stores computer instructions, which, when executed by a processor, can implement the method according to any one of claims 1 to 4.

Images