Database query acceleration optimization method and system based on artificial intelligence

Through the accelerated optimization method of database query based on artificial intelligence, automatic analysis and recommendation indexes, the problem of slow query on large-scale e-commerce platforms has been solved, efficient query performance improvement and operation and maintenance cost reduction, and adapted to modern times Real-time and dynamic requirements of database environments.

CN118939654APending Publication Date: 2024-11-12CHINA UNITECHS
View PDF 0 Cites 4 Cited by

Patent Information

Application Number
CN202410970712.X
Authority / Receiving Office
CN · China
Patent Type
Applications(China)
Current Assignee / Owner
Filing Date
2024-07-19
Publication Date
2024-11-12

AI Technical Summary

Technical Problem

The slow query problem caused by the surge in business volume on traditional database management systems on large-scale e-commerce platforms is difficult to effectively solve through manual tuning, and is inefficient and cannot meet the real-time and dynamic requirements of modern database environments.

Method used

Adopt database query acceleration optimization method based on artificial intelligence, combine artificial intelligence models with traditional cost-based optimization strategies, automatically analyze historical query modes and index benefits, intelligently recommend missing efficient indexes, build binary classification models through XGBoost algorithm, and slow real-time detection Query and verify index potential to reduce manual intervention.

Benefits of technology

Significantly improve database query efficiency, reduce operation and maintenance costs, improve user experience and business processing capabilities, and in some cases, query performance is improved by up to 80%, reducing dependence on professional DBAs, avoiding useless index creation, and ensuring query performance Continuous optimization.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118939654A_ABST
    Figure CN118939654A_ABST
Patent Text Reader

Abstract

The invention discloses a database query acceleration optimization method and system based on artificial intelligence, and the method comprises the steps: capturing and recording a slow query log in real time, collecting comprehensive database performance indexes, and carrying out the data cleaning and standardization; columns related to keywords in the slow query statement are automatically identified, single-column to multi-column candidate index combinations are generated, and the leftmost prefix principle is followed when the multi-column candidate index combinations are generated; constructing a feature vector containing statement features and statistical features; an XGBoost algorithm is utilized to construct a dichotomy model, and model training is carried out; automatically detecting a new slow query, instantly generating a feature vector of the candidate index, and predicting the potential of each candidate index for improving the query performance by utilizing a pre-trained model; verifying a prediction result of the model in a safe sampling library environment; and when the sampling library passes verification, applying the recommended index to the production environment. According to the method and the system, the efficiency problem of traditional database index optimization is solved.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the field of database management systems, and in particular to an artificial intelligence-based database query acceleration optimization method and system. Background Art

[0002] In the current database management system field, especially on large-scale e-commerce platforms with hundreds of millions of query requests every day, slow query problems have become a major challenge restricting business efficiency. Traditional optimization methods rely on manual identification and tuning by database administrators (DBAs), which can no longer adapt to such high-intensity and high-frequency query requirements. With the continuous growth of business volume, this manual index management and performance tuning method is not only time-consuming and labor-intensive, but also difficult to scale and inefficient, and cannot meet the real-time and dynamic requirements of modern database environments. Summary of the invention

[0003] In order to solve the slow query problem caused by the surge in business volume in a large-scale database environment, the present invention provides an artificial intelligence-based database query acceleration optimization method and system, which combines the artificial intelligence model with the traditional cost-based optimization strategy, and automatically analyzes historical query patterns and index benefits to intelligently recommend missing efficient indexes for specific slow queries without manual intervention, thereby significantly improving database query efficiency and reducing operation and maintenance costs.

[0004] To achieve the above object, the present invention adopts the following technical solutions:

[0005] In one embodiment of the present invention, a database query acceleration optimization method based on artificial intelligence is proposed, and the method includes:

[0006] Capture and record slow query logs in real time, collect comprehensive database performance indicators, and perform data cleaning and standardization; automatically identify the columns involved in the keywords in slow query statements, generate candidate index combinations from single to multiple columns, and follow the leftmost prefix principle when generating multiple-column candidate index combinations;

[0007] Construct a feature vector of candidate indexes containing sentence features and statistical features; use the XGBoost algorithm to build a binary classification model and perform model training;

[0008] Automatically detect new slow queries, instantly generate feature vectors for candidate indexes, and use pre-trained binary classification models to predict the potential of each candidate index to improve query performance. Verify the model's prediction results in a safe sampling library environment. When the sampling library verification passes, apply the recommended index to the production environment, otherwise abandon the recommendation.

[0009] Furthermore, a feature vector of candidate indexes including sentence features and statistical features is constructed, including:

[0010] Use one-hot encoding to describe the position of the columns involved in the candidate index in the slow query statement. If the candidate index involves multiple columns, generate an independent one-hot encoding for the position of each column and integrate these encodings to form the statement position feature part.

[0011] Integrate the characteristics of database tables and columns to form the statistical feature part;

[0012] The above two types of features are integrated into a single feature vector and normalized or standardized.

[0013] Furthermore, manually annotated indexes or indexes actually applied in a test environment are used to assign positive and negative labels to feature vectors corresponding to the indexes. The feature vectors and corresponding positive and negative labels are then used to train the model, so that the model learns the relationship between feature combinations and query performance improvements.

[0014] Furthermore, the prediction results of the model are verified in a safe sampling library environment, including:

[0015] Validate model predictions in a secure sampling library environment;

[0016] Based on the high-potential indexes predicted by the model, these indexes are quickly created in the sampling library, and the corresponding slow queries are executed, comparing the query execution time before and after index creation.

[0017] In one embodiment of the present invention, a database query acceleration optimization system based on artificial intelligence is also proposed, and the system includes:

[0018] The data collection and preprocessing module is used to capture and record slow query logs in real time, collect comprehensive database performance indicators, and perform data cleaning and standardization. It automatically identifies the columns involved in the keywords in the slow query statements, generates single-column to multi-column candidate index combinations, and follows the leftmost prefix principle when generating multi-column candidate index combinations.

[0019] Feature engineering and model training module, used to construct feature vectors of candidate indexes containing sentence features and statistical features; use XGBoost algorithm to build a binary classification model and perform model training;

[0020] The model deployment and index evaluation module is used to automatically detect new slow queries, instantly generate feature vectors of candidate indexes, and use pre-trained binary classification models to predict the potential of each candidate index to improve query performance; verify the prediction results of the model in a safe sampling library environment; when the sampling library verification passes, the recommended index is applied to the production environment, otherwise the recommendation is abandoned.

[0021] Furthermore, a feature vector of candidate indexes including sentence features and statistical features is constructed, including:

[0022] Use one-hot encoding to describe the position of the columns involved in the candidate index in the slow query statement. If the candidate index involves multiple columns, generate an independent one-hot encoding for the position of each column and integrate these encodings to form the statement position feature part.

[0023] Integrate the characteristics of database tables and columns to form the statistical feature part;

[0024] The above two types of features are integrated into a single feature vector and normalized or standardized.

[0025] Furthermore, manually annotated indexes or indexes actually applied in a test environment are used to assign positive and negative labels to feature vectors corresponding to the indexes. The feature vectors and corresponding positive and negative labels are then used to train the model, so that the model learns the relationship between feature combinations and query performance improvements.

[0026] Furthermore, the prediction results of the model are verified in a safe sampling library environment, including:

[0027] Validate model predictions in a secure sampling library environment;

[0028] Based on the high-potential indexes predicted by the model, these indexes are quickly created in the sampling library, and the corresponding slow queries are executed, comparing the query execution time before and after index creation.

[0029] In one embodiment of the present invention, a computer device is also proposed, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the aforementioned artificial intelligence-based database query acceleration and optimization method is implemented.

[0030] In one embodiment of the present invention, a computer-readable storage medium is also proposed, which stores a computer program for executing an artificial intelligence-based database query acceleration optimization method.

[0031] Beneficial effects:

[0032] 1. The present invention can significantly reduce the database query response time by intelligently identifying and recommending missing or optimal indexes. In some cases, the query performance is improved by up to 80%, which greatly improves the user experience and business processing capabilities, especially in high-concurrency scenarios, and effectively supports the stable operation of large platforms.

[0033] 2. The automatic index optimization of the present invention reduces the manpower dependence on professional database administrators (DBAs) and alleviates the operation and maintenance burden. It is estimated that at least 30% of database maintenance hours can be saved, allowing DBAs to focus on higher-level system design and optimization work.

[0034] 3. The present invention avoids the creation of useless or redundant indexes by accurately recommending indexes, effectively utilizes storage resources, reduces system overhead caused by index maintenance, and improves the overall operating efficiency of the database.

[0035] 4. As business data and query patterns change, the present invention can continuously learn and automatically adjust index strategies to ensure that query performance always remains optimal, thereby enhancing the database system's ability to quickly respond to changes in business needs.

[0036] 5. Through the real-time monitoring and feedback mechanism of the model, the present invention can continuously learn from actual operation, automatically iterate and optimize the model, make the index recommendation more accurate, form a set of self-improving optimization closed loop, and ensure long-term performance optimization effect.

[0037] 6. The present invention provides data support and quantitative analysis for database optimization decisions, making the decision-making process more scientific and transparent, helping management to better understand database performance bottlenecks, and guiding future architecture adjustments and technology selection.

[0038] In summary, the present invention not only solves the efficiency problem of traditional database index optimization, but also brings revolutionary changes to database operation and maintenance through intelligent and automated methods. It is an indispensable and efficient tool for modern large-scale database management. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 It is a schematic diagram of a flow chart of a database query acceleration optimization method based on artificial intelligence according to an embodiment of the present invention;

[0040] Figure 2 is a process diagram of constructing feature vectors and assigning labels for queries in a training set according to an embodiment of the present invention;

[0041] Figure 3 This is an architecture diagram of the database query acceleration optimization system based on artificial intelligence of the present invention;

[0042] Figure 4 It is a schematic diagram of the computer device structure of the present invention. DETAILED DESCRIPTION

[0043] The principles and spirit of the present invention will be described below with reference to several exemplary embodiments. It should be understood that these embodiments are provided only to enable those skilled in the art to better understand and implement the present invention, and are not intended to limit the scope of the present invention in any way. On the contrary, these embodiments are provided to make the present disclosure more thorough and complete, and to fully convey the scope of the present disclosure to those skilled in the art.

[0044] Those skilled in the art will appreciate that the embodiments of the present invention may be implemented as a system, system, device, method or computer program product. Therefore, the present disclosure may be implemented in the following forms, namely: complete hardware, complete software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0045] According to the implementation mode of the present invention, a database query acceleration optimization method based on artificial intelligence is proposed, which uses the automated processing capability and data-driven insight of the AI ​​algorithm to automatically detect and solve the slow query problem in the database, and achieves a substantial improvement in query performance by intelligently recommending missing or better index configurations. This not only greatly improves the response speed of database queries, but also significantly reduces the manpower and time costs of database operation and maintenance, bringing revolutionary improvements to business continuity and user experience in large-scale database environments. The introduction of the present invention marks an important transformation from the traditional experience-based DBA manual tuning mode to intelligent and automated database management, which is of great significance for improving the competitiveness of modern data-intensive applications.

[0046] The principle and spirit of the present invention are explained in detail below with reference to several representative embodiments of the present invention.

[0047] The database query acceleration optimization method based on artificial intelligence of the present invention mainly includes the following core components:

[0048] 1. Data collection and preprocessing mechanism: The system integrates real-time monitoring functions, captures and records slow query logs in real time, and uses performance monitoring tools to collect comprehensive database performance indicators. Through business workflow analysis, key query paths and performance bottlenecks are identified. The initial manual labeling provides basic data for model training, and then through data cleaning, standardization and feature extraction, high-quality training samples are prepared for subsequent model training.

[0049] 2. Feature engineering and model training stage: The present invention proposes a novel feature construction method, including automatically identifying the columns involved in the keywords in the slow query statement, generating candidate index combinations from a single column to multiple columns, and following the leftmost prefix principle when generating multiple column candidate index combinations. Construct a feature vector of the candidate index containing statement features and statistical features, and use one-hot encoding, statistical analysis and other means to ensure that the model can understand the impact of the index on query performance. Use the XGBoost algorithm to build a binary classification model to predict whether the candidate index can significantly improve the performance of database queries. Specifically, the output of the binary classification model is a binary classification of the index optimization effect-positive labels indicate performance improvement, and negative labels indicate no significant improvement. In the model training stage, the feature vector and the performance improvement labels obtained from actual tests or manual annotations (positive and negative labels assigned to the feature vectors) are used to train the model so that the model can learn the relationship between which feature combinations and query performance improvements. Through iterative training and verification, the model gradually improves the prediction accuracy and reduces dependence on manual annotation.

[0050] 3. Model deployment and real-time evaluation system: The present invention also includes a model deployment and real-time index recommendation mechanism to ensure the effective operation of the model in a production environment. The system can automatically detect new slow queries, instantly generate feature vectors for candidate indexes, and use a pre-trained binary classification model to predict the potential of each candidate index to improve query performance. By verifying the prediction results of the model in a safe sampling library environment, it is ensured that the recommended index is truly effective; only when the sampling library is verified will the recommended index be applied to the production environment, otherwise the recommendation will be abandoned, ensuring the accuracy and security of the recommendation.

[0051] In summary, the present invention realizes the automation and intelligence of database query performance optimization through deep integration of artificial intelligence technology, which not only significantly improves the efficiency of processing large-scale slow queries, but also has the ability to automatically adjust the optimization strategy as the data and query mode change, providing strong support for database operation and maintenance, effectively reducing labor costs and resource waste, and promoting the advancement of database management technology.

[0052] It should be noted that, although the operations of the method of the present invention are described in a specific order in the above embodiments and the accompanying drawings, this does not require or imply that the operations must be performed in the specific order, or that all the operations shown must be performed to achieve the desired results. Additionally or alternatively, some steps may be omitted, multiple steps may be combined into one step, and / or one step may be decomposed into multiple steps.

[0053] In order to explain the above-mentioned artificial intelligence-based database query acceleration optimization method more clearly, a specific embodiment is used for illustration below. However, it should be noted that this embodiment is only for better illustrating the present invention and does not constitute an improper limitation to the present invention.

[0054] Example:

[0055] Figure 1 FIG. 1 is a flow chart of a method for accelerating and optimizing database query based on artificial intelligence according to an embodiment of the present invention. Figure 1 As shown, the method includes:

[0056] 1. Data collection and preprocessing phase: This phase is the first step in the recommendation system. Its core goal is to obtain and prepare the data required for training the model. This phase mainly includes the following key activities:

[0057] (1) Real-time monitoring of slow queries:

[0058] Collect logs: First, you need to configure the logging function on the database server, especially the slow query log. The slow query log records all SQL queries that exceed the preset threshold (such as execution time exceeding 1 second), which is a valuable data source for analysis and optimization. For example: the system turns on the slow query log and sets the threshold to 200 milliseconds.

[0059] Performance monitoring: In addition to slow query logs, you also need to deploy database performance monitoring tools to regularly collect indicators such as query execution time and resource consumption (CPU, I / O). These data help evaluate the actual effect of index optimization.

[0060] Cleaning and standardization: Clean the collected data to remove outliers, duplicate records, etc. At the same time, standardize the SQL statements, such as removing extra blank characters, unifying upper and lower case, and standardizing table and column names.

[0061] (2) Candidate index generation:

[0062] Automatically identify columns involved in keywords: The system first automatically scans each slow query statement and identifies the columns involved in the WHERE clause, JOIN operation, ORDER BY, and GROUP BY clauses. These columns directly affect the execution path and efficiency of the query and are considered the key points for optimizing performance, thus becoming the basis for single-column candidate indexes.

[0063] Generate single-column and multi-column index combinations: Based on the identified single-column candidate indexes, the system further generates all possible two-column and three-column combination candidate indexes. Such combinations take into account the optimization potential of multiple field joint indexes, especially for those columns that often appear together in query conditions.

[0064] Follow the leftmost prefix principle: When generating multi-column index combinations, the system follows the leftmost prefix principle in database index design. This means that when considering multi-column indexes, only those index combinations that contain all columns from left to right will be recommended, avoiding recommending longer indexes that have been covered by shorter prefix indexes, in order to reduce unnecessary index redundancy and maintenance costs. For example, for frequently occurring query patterns:

[0065] SELECT*FROM products WHERE category_id=? AND price BETWEEN? AND?

[0066] ORDER BY sales_rank DESC LIMIT 20

[0067] The system generates the following candidate indexes:

[0068] 1. (category_id)

[0069] 2. (category_id, price)

[0070] 3. (category_id,price,sales_rank)

[0071] The work at this stage is the foundation of the entire system. It ensures that subsequent model training and index recommendations can be based on high-quality, carefully prepared data, thereby improving the accuracy and efficiency of the recommendation index.

[0072] 2. Feature engineering and model training phase: mainly focuses on how to convert raw data into features that can be understood by the model, and use these features to train machine learning models to predict the impact of indexes on query performance. It can be divided into the following key steps:

[0073] (1) Feature construction:

[0074] Statement feature integration: Use one-hot encoding to describe the position of the columns involved in the candidate index in the SQL query (such as WHERE clause, JOIN condition, ORDER BY or GROUP BY clause). If the candidate index involves multiple columns, generate independent one-hot encoding for each column position, and these encodings may be integrated through concatenation or matrix operations to form the statement position feature part.

[0075] Statistical feature integration: Statistical features reflect the characteristics of database tables and columns, which are crucial for evaluating the potential benefits of indexes. These features include: number of rows in the table, number of unique values ​​in the column, selectivity, etc.

[0076] Integrated feature vector: Combine the above two types of features into a single feature vector. For example, if a candidate index has three statistical features (number of table rows, number of unique values, selectivity) and five positional features (assuming that each column appears in at most five key positions), the feature vector may consist of these eight feature values. Each feature value is arranged in a predefined order to form a vector of fixed length.

[0077] Normalization or standardization: In order to ensure the scale consistency between different features and avoid some features dominating the model learning due to excessive numerical range, it may be necessary to normalize or standardize the feature vector. This can be done through maximum and minimum scaling, Z-score standardization, etc. Ultimately, each candidate index corresponds to an integrated and processed feature vector, which contains rich information about how the index affects query performance, and the model can predict the performance improvement potential of the index based on these features.

[0078] (2) Model construction and training:

[0079] Model selection: This system uses the XGBoost algorithm to build the model, which is an efficient gradient boosting decision tree algorithm suitable for processing classification and regression tasks.

[0080] Binary classification model: For single-column, two-column, and three-column index recommendations, a binary classification model is built respectively. The output of the model is whether the query performance can be significantly improved after the index is established. A positive label indicates performance improvement, and a negative label indicates no significant improvement.

[0081] Feature vector as input: The feature vector containing sentence features and statistical features constructed previously is used as the input data of the model.

[0082] Label assignment: Labels are assigned to feature vectors using manually labeled data or by measuring performance improvements through the actual application of indexes in a test environment. The basis for the division of positive and negative labels is the improvement in query performance after the addition of the index. Among them, manually labeled data are mainly those examples that have been manually analyzed and confirmed to have improved query performance after the addition of the index. This data is usually selected by database administrators (DBAs) or system engineers based on their professional knowledge and experience. They observe and record the specific impact of indexes on query performance by actually applying different index configurations in a test environment. If the execution time of a query is significantly shortened or resource consumption is reduced after adding a certain index, the feature vector related to the index will be marked with a positive label, indicating that the index effectively improves query performance. Conversely, if the addition of an index does not significantly improve or even decreases query performance, the feature vector will be marked with a negative label.

[0083] Model training: Use these feature vectors and the corresponding performance improvement labels as training data sets for model training. During the training process, the model learns which feature combinations are associated with improved query performance, so that when receiving new slow queries, it can predict the index optimization effect of unknown queries. Among them, performance improvement labels refer to positive and negative labels assigned to feature vectors. These labels are set based on whether the query performance is improved after the index is added. If the addition of a candidate index leads to a significant improvement in query performance, the feature vector corresponding to the index will be labeled with a positive label; on the contrary, if the addition of the index does not significantly improve the query performance, or the performance deteriorates, the feature vector is labeled with a negative label.

[0084] This phase uses a combination of feature engineering techniques, machine learning model selection and training to ensure that the system can accurately predict the impact of indexes on specific query performance, laying a solid foundation for subsequent real-time index recommendations and continuous optimization. The following process uses a query example to illustrate how to construct feature vectors and assign labels for queries in the training set, such as Figure 2 As shown in the figure. This query involves the members and suppliers tables, where the four columns m_g_id, m_id, m_s_id, and m_name of the members table are involved in the query operation, so a single-column feature vector is generated for each of these columns; and the s_id column of the suppliers table is also used in the query, and a single-column feature vector is generated accordingly. Since the query uses the single-column index Idx(s_id), the feature vector of the corresponding single-column candidate index (s_id) is marked as a positive sample, and the remaining feature vectors are marked as negative samples.

[0085] Subsequently, these single-column candidate indexes are combined to generate multi-column candidate indexes and corresponding feature vectors. Since this query only uses a three-column index Idx(m_s_id,m_id,m_name), the step of building a two-column combination is ignored, and the three-column combination generation stage is directly entered. This decision is based on the index used by the actual query to label the feature vector: if the query does not use any two-column index, all generated two-column feature vectors will be defined as negative samples, which may lead to an imbalance in the ratio of positive and negative samples in the training data.

[0086] Finally, based on the three columns of indexes involved in the query, the feature vectors corresponding to the three candidate indexes (m_s_id, m_id, m_name) are identified as positive samples. This process summarizes all the details of generating feature vectors for a specific query and completing label assignment. This query not only provides five samples (one positive sample and four negative samples) for the training set of the single-column index recommendation model, but also contributes six samples (one positive sample and five negative samples) to the training set of the three-column index recommendation model.

[0087] 3. Model deployment and index evaluation phase: Its main purpose is to ensure that the trained model can run correctly and effectively in the actual production environment and accurately evaluate the effectiveness of the recommended index. Specifically, this phase includes the following steps:

[0088] (1) Real-time index recommendation:

[0089] When the system detects a new slow query, it automatically performs a feature extraction process to generate a feature vector of candidate indexes for this query.

[0090] The pre-trained models (binary classification models for single-column, two-column, and three-column indexes) are used to predict the potential of each candidate index to improve query performance. This means that the model receives a feature vector as input and outputs a probability or score indicating how likely it is that query performance will improve if the index is applied.

[0091] (2) Sampling library verification:

[0092] To ensure the accuracy of index recommendations and avoid risks directly in the production environment, the system verifies the model's predictions in a safe sampling library environment (containing 10% of the production data).

[0093] Based on the high-potential indexes predicted by the model, these indexes are quickly created in the sample library, and the corresponding slow queries are executed, and the query execution time before and after the index creation is compared. This verification process is an important step to ensure that the index can indeed bring performance improvements.

[0094] (3) Result feedback and application:

[0095] Only when the sampling library verification results show that the index significantly improves query performance will the system submit the recommended index to the database administrator or automatically apply it to the production environment.

[0096] If the index fails to achieve the expected performance improvement, it will not be recommended, thus avoiding the waste of resources caused by deploying invalid indexes in the production environment.

[0097] This stage is the process of converting theoretical predictions into practical actions and ensuring the effectiveness of the actions. It ensures the system's intelligent index optimization capabilities through real-time recommendations, security verification, and feedback loop mechanisms, while also maintaining a high degree of accuracy and security. This stage embodies the closed-loop control concept of AI systems in practical applications, that is, from data collection to model training, to model application and continuous optimization, a complete iterative evolution system has been formed.

[0098] Through the above embodiments, the index recommendation system of the present invention can not only solve the slow query problem efficiently and accurately, but also automatically adjust the optimization strategy as the data and query mode change, providing strong support for database management.

[0099] Based on the same inventive concept, the present invention also proposes a database query acceleration optimization system based on artificial intelligence. The implementation of the system can refer to the implementation of the above method, and the repetitions will not be repeated. The term "module" used below can be a combination of software and / or hardware that implements a predetermined function. Although the system described in the following embodiments is preferably implemented in software, the implementation of hardware, or a combination of software and hardware is also possible and conceived.

[0100] Figure 3 This is the architecture diagram of the database query acceleration optimization system based on artificial intelligence of the present invention. Figure 3 As shown, the system includes:

[0101] The data collection and preprocessing module 101 is used to capture and record slow query logs in real time, collect comprehensive database performance indicators, and perform data cleaning and standardization; automatically identify the columns involved in the keywords in the slow query statement, generate single-column to multi-column candidate index combinations, and follow the leftmost prefix principle when generating multi-column candidate index combinations.

[0102] The feature engineering and model training module 102 is used to construct a feature vector of a candidate index containing sentence features and statistical features; use the XGBoost algorithm to build a binary classification model, use manually labeled indexes or indexes actually applied in a test environment to assign positive and negative labels to the feature vectors corresponding to the indexes, and then use the feature vectors and corresponding positive and negative labels to perform model training, so that the model learns the relationship between feature combinations and query performance improvements.

[0103] Construct a feature vector of candidate indexes containing sentence features and statistical features, including:

[0104] Use one-hot encoding to describe the position of the columns involved in the candidate index in the slow query statement. If the candidate index involves multiple columns, generate an independent one-hot encoding for the position of each column and integrate these encodings to form the statement position feature part.

[0105] Integrate the characteristics of database tables and columns to form the statistical feature part;

[0106] The above two types of features are integrated into a single feature vector and normalized or standardized.

[0107] The model deployment and index evaluation module 103 is used to automatically detect new slow queries, instantly generate feature vectors of candidate indexes, and use a pre-trained binary classification model to predict the potential of each candidate index to improve query performance; verify the prediction results of the model in a safe sampling library environment, quickly create these indexes in the sampling library based on the high-potential indexes predicted by the model, execute corresponding slow queries, and compare the execution time of queries before and after index creation; when the sampling library verification passes, the recommended index is applied to the production environment, otherwise the recommendation is abandoned.

[0108] It should be noted that although several modules of the database query acceleration optimization system based on artificial intelligence are mentioned in the above detailed description, this division is only exemplary and not mandatory. In fact, according to an embodiment of the present invention, the features and functions of two or more modules described above can be concretized in one module. Conversely, the features and functions of one module described above can be further divided into multiple modules to be concretized.

[0109] Based on the above invention concept, Figure 4 As shown, the present invention also proposes a computer device 200, including a memory 210, a processor 220, and a computer program 230 stored in the memory 210 and executable on the processor 220. When the processor 220 executes the computer program 230, the aforementioned artificial intelligence-based database query acceleration optimization method is implemented.

[0110] Based on the aforementioned inventive concept, the present invention also proposes a computer-readable storage medium, which stores a computer program for executing the aforementioned artificial intelligence-based database query acceleration optimization method.

[0111] The database query acceleration optimization method and system based on artificial intelligence proposed in the present invention have the following advantages:

[0112] 1. Significantly improve query performance: By intelligently identifying and recommending missing or optimal indexes, the system can significantly reduce database query response time. In some cases, query performance is improved by up to 80%, greatly improving user experience and business processing capabilities, especially in high-concurrency scenarios, effectively supporting the stable operation of large platforms.

[0113] 2. Reduce operation and maintenance costs: Automated index optimization reduces the manpower dependence on professional database administrators (DBAs) and alleviates the operation and maintenance burden. It is estimated that it can save at least 30% of database maintenance hours, allowing DBAs to focus on higher-level system design and optimization work.

[0114] 3. Improve resource utilization: The system avoids the creation of useless or redundant indexes by accurately recommending indexes, effectively utilizes storage resources, reduces system overhead caused by index maintenance, and improves the overall operating efficiency of the database.

[0115] 4. Enhance system adaptability: As business data and query patterns change, the system can continuously learn and automatically adjust index strategies to ensure that query performance always remains optimal, enhancing the database system's ability to respond quickly to changes in business needs.

[0116] 5. Achieve a continuous optimization cycle: Through the real-time monitoring and feedback mechanism of the model, the system can continuously learn from actual operation, automatically iterate and optimize the model, make the index recommendation more accurate, form a self-improving optimization closed loop, and ensure long-term performance optimization effect.

[0117] 6. Enhanced decision support capabilities: Provides data support and quantitative analysis for database optimization decisions, making the decision-making process more scientific and transparent, helping management to better understand database performance bottlenecks, and guiding future architecture adjustments and technology selection.

[0118] Although the spirit and principle of the present invention have been described with reference to several specific embodiments, it should be understood that the present invention is not limited to the disclosed specific embodiments, and the division of various aspects does not mean that the features in these aspects cannot be combined to benefit, and such division is only for the convenience of expression. The present invention is intended to cover various modifications and equivalent arrangements contained in the spirit and scope of the attached claims.

[0119] Regarding the limitation of the protection scope of the present invention, those skilled in the art should understand that, based on the technical solution of the present invention, various modifications or variations that can be made by those skilled in the art without creative work are still within the protection scope of the present invention.

Claims

1. A database query acceleration optimization method based on artificial intelligence, characterized in that: The method includes: Capture and record slow query logs in real time, collect comprehensive database performance indicators, and perform data cleaning and standardization; automatically identify the columns involved in the keywords in slow query statements, generate candidate index combinations from single to multiple columns, and follow the leftmost prefix principle when generating multiple-column candidate index combinations; Construct a feature vector of candidate indexes containing sentence features and statistical features; use the XGBoost algorithm to build a binary classification model and perform model training; Automatically detect new slow queries, instantly generate feature vectors for candidate indexes, and use pre-trained binary classification models to predict the potential of each candidate index to improve query performance. Verify the model's prediction results in a safe sampling library environment. When the sampling library verification passes, apply the recommended index to the production environment, otherwise abandon the recommendation.

2. The method for accelerating and optimizing database query based on artificial intelligence according to claim 1, characterized in that: Construct a feature vector of candidate indexes containing sentence features and statistical features, including: Use one-hot encoding to describe the position of the columns involved in the candidate index in the slow query statement. If the candidate index involves multiple columns, generate an independent one-hot encoding for the position of each column and integrate these encodings to form the statement position feature part. Integrate the characteristics of database tables and columns to form the statistical feature part; The above two types of features are integrated into a single feature vector and normalized or standardized.

3. The method for accelerating and optimizing database query based on artificial intelligence according to claim 1, characterized in that: Use manually labeled indexes or indexes actually applied in a test environment to assign positive and negative labels to the feature vectors corresponding to the indexes. Then use the feature vectors and the corresponding positive and negative labels to train the model so that the model learns the relationship between feature combinations and query performance improvements.

4. The method for accelerating and optimizing database query based on artificial intelligence according to claim 1, characterized in that: Validate your model’s predictions in a secure sampling library environment, including: Validate model predictions in a secure sampling library environment; Based on the high-potential indexes predicted by the model, these indexes are quickly created in the sampling library, and the corresponding slow queries are executed, comparing the query execution time before and after index creation.

5. A database query acceleration optimization system based on artificial intelligence, characterized in that: The system includes: The data collection and preprocessing module is used to capture and record slow query logs in real time, collect comprehensive database performance indicators, and perform data cleaning and standardization. It automatically identifies the columns involved in the keywords in the slow query statements, generates single-column to multi-column candidate index combinations, and follows the leftmost prefix principle when generating multi-column candidate index combinations. Feature engineering and model training module, used to construct feature vectors of candidate indexes containing sentence features and statistical features; use XGBoost algorithm to build a binary classification model and perform model training; The model deployment and index evaluation module is used to automatically detect new slow queries, instantly generate feature vectors of candidate indexes, and use pre-trained binary classification models to predict the potential of each candidate index to improve query performance; verify the prediction results of the model in a safe sampling library environment; when the sampling library verification passes, the recommended index is applied to the production environment, otherwise the recommendation is abandoned.

6. The database query acceleration optimization system based on artificial intelligence according to claim 5 is characterized in that: Construct a feature vector of candidate indexes containing sentence features and statistical features, including: Use one-hot encoding to describe the position of the columns involved in the candidate index in the slow query statement. If the candidate index involves multiple columns, generate an independent one-hot encoding for the position of each column and integrate these encodings to form the statement position feature part. Integrate the characteristics of database tables and columns to form the statistical feature part; The above two types of features are integrated into a single feature vector and normalized or standardized.

7. The database query acceleration optimization system based on artificial intelligence according to claim 5 is characterized in that: Use manually labeled indexes or indexes actually applied in a test environment to assign positive and negative labels to the feature vectors corresponding to the indexes. Then use the feature vectors and the corresponding positive and negative labels to train the model so that the model learns the relationship between feature combinations and query performance improvements.

8. The database query acceleration optimization system based on artificial intelligence according to claim 5 is characterized in that: Validate your model’s predictions in a secure sampling library environment, including: Validate model predictions in a secure sampling library environment; Based on the high-potential indexes predicted by the model, these indexes are quickly created in the sampling library, and the corresponding slow queries are executed, comparing the query execution time before and after index creation.

9. A computer device comprising a memory, a processor and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the method according to any one of claims 1 to 4 is implemented.

10. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores a computer program for executing the method according to any one of claims 1 to 4.

Citation Information

Cited By

  • Data query optimization method and system based on machine learning, database and electronic equipment

    CN119782359A

  • Data retrieval method and device based on data quality management platform

    CN120104644A

  • SQL and index combined closed-loop optimization method and system based on verification result

    CN120804103A

  • A method and system for SQL and index combined closed-loop optimization based on verification results

    CN120804103B