Database index optimization method and system based on deep reinforcement learning

Abstract

The invention provides a database index optimization method and system based on deep reinforcement learning. The method comprises the steps of obtaining query record data of a to-be-optimized database; performing feature extraction on the query record data by using a pre-trained Transform model to obtain corresponding distance feature information; according to the distance feature information, utilizing a clustering algorithm to perform clustering analysis on the distance feature information to obtain query mode information of the to-be-optimized database; based on the query mode information, performing index structure adjustment on the to-be-optimized database by utilizing a pre-constructed index optimization model to obtain an optimal index structure of the to-be-optimized database; according to the method, the historical query logs can be analyzed by using the Transform model, and main components of the workload can be identified through clustering processing; by using an index optimization model which introduces a priority experience playback mechanism, real-time perception and analysis of a workload can be realized, and an optimal index structure is dynamically adjusted.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to a database index optimization method and system based on deep reinforcement learning. Background Art

[0002] Currently, in the field of current database management systems, traditional index selection and optimization techniques are facing severe challenges. With the continuous growth of data volume and the increasing diversification of business requirements, the workload is changing rapidly. However, traditional index selection and optimization techniques are unable to cope effectively in this situation. They are difficult to adapt to these changes effectively, which directly leads to a decline in database query performance.

[0003] Existing index optimization techniques are mainly divided into two categories: rule-based methods and machine learning-based methods. However, both of these methods have obvious defects in practical applications.

[0004] (1) Rule-based methods: Depend on pre-set rules for index optimization. For example, build indexes according to rules such as data access frequency and data type. The disadvantages are as follows:

[0005] Lack of flexibility: Pre-set rules are usually based on past experience or general database principles and are difficult to adapt to complex and changing actual workloads. For example, during an e-commerce promotion period, some product data that is rarely accessed usually may suddenly become hot data, and rule-based methods cannot perceive this temporary change in time;

[0006] Difficult to handle complex scenarios: In a database containing multiple related tables, index optimization based on simple rules cannot handle complex queries involving multi-table joins well. There may be various relationships such as one-to-many and many-to-many between different tables, and the query may also involve various filtering conditions and sorting requirements. It is difficult to find the optimal index strategy simply relying on pre-set rules;

[0007] (2) Machine learning-based methods: Learn and optimize according to data characteristics through machine learning algorithms and have a certain degree of adaptability. However, its disadvantages are as follows:

[0008] High computational resource consumption: Machine learning methods need to learn and train a large amount of data. Especially when using deep learning algorithms, complex neural network models need to be built, consuming a large amount of computational resources such as CPU, memory, and GPU. For database systems with limited resources, this resource consumption may lead to a decline in system performance and even affect the normal operation of other critical services;

[0009] Long response time: Machine learning methods require multiple complex steps such as data collection, feature extraction, and model training. When facing new query requests or workload changes, the response time is relatively long. For example, when a new query type appears for the first time, the machine learning model may need to be re-evaluated and adjusted, which may take several minutes or even longer, and cannot meet the application scenarios that require quick responses.

[0010] Lack of flexibility in dynamic environments: Although machine learning methods have a certain degree of adaptability, they are still insufficient in dealing with rapidly changing workloads. For example, in a real-time financial transaction database, the traffic and patterns of transaction data may change significantly in a short period of time, while the learning and adjustment process of the machine learning model is relatively slow, and it cannot quickly provide an optimized indexing scheme for the new workload pattern.

[0011] In summary, the core problems faced by traditional index selection and optimization techniques in current database management systems can be summarized as: being unable to efficiently adapt to rapidly changing workloads, resulting in a significant decline in database query performance. Summary of the Invention

[0012] To solve the problem that traditional index selection methods cannot adapt to the rapid changes in workloads, resulting in a decline in database query performance, the present invention proposes a database index optimization method based on deep reinforcement learning, including:

[0013] Obtain the query record data of the database to be optimized;

[0014] Use a pre-trained Transformer model to extract features from the query record data to obtain the distance feature information corresponding to the query record data;

[0015] According to the distance feature information, use a clustering algorithm to perform clustering analysis on the distance feature information to obtain the query pattern information of the database to be optimized;

[0016] Based on the query pattern information, use a pre-constructed index optimization model to adjust the index structure of the database to be optimized to obtain the optimal index structure of the database to be optimized;

[0017] Among them, the index optimization model is constructed based on deep reinforcement learning and a priority experience replay mechanism.

[0018] Optionally, the step of using a pre-constructed index optimization model to adjust the index structure of the database to be optimized based on the query pattern information to obtain the optimal index structure of the database to be optimized includes:

[0019] Extract features according to the query pattern information to obtain index feature data;

[0020] Generate an index adjustment strategy based on the index feature data using a pre-constructed index optimization model;

[0021] Output the optimal index structure of the database to be optimized through an adaptive index adjustment mechanism according to the index adjustment strategy.

[0022] Optionally, the index optimization model includes the following construction process:

[0023] Initialize the network structure, model parameters, and experience replay buffer;

[0024] Use historical index feature data as input training data;

[0025] Use the index strategy corresponding to the historical index feature data as output training data;

[0026] Store the input training data and the output training data as experience values in the experience replay buffer;

[0027] Select a preset number of experience values from the experience replay buffer as index data and input them into the deep Q network to output corresponding index prediction data;

[0028] Calculate the corresponding loss function value according to the index prediction data and the target structure data of the index data stored in the experience replay buffer;

[0029] Update the model parameters using the gradient descent method according to the loss function value to obtain an index optimization model.

[0030] Optionally, the output of the optimal index structure of the database to be optimized through an adaptive index adjustment mechanism according to the index adjustment strategy includes:

[0031] Parse the index adjustment strategy to obtain the index operation priority;

[0032] Obtain the index adjustment amount according to the index operation priority;

[0033] Evaluate the index adjustment amount through a performance evaluation function and output the optimal index structure of the database to be optimized.

[0034] Optionally, the expression corresponding to the performance evaluation function is as follows:

[0035]

[0036] Among them, f(I) represents the overall performance score of the index structure I; α represents the first weight coefficient; β represents the second weight coefficient; C kDenote the query frequency of the k-th cluster; k = 1…K; K represents the total number of clusters; D k (I) Denote the query response time of the k-th cluster under the index structure I; R k (I) Denote the resource utilization rate of the k-th cluster under the index structure I.

[0037] Optionally, the Transformer model includes the following training process:

[0038] Use the historical query record data of the database to be optimized as the input of the training data;

[0039] Use the distance feature information of the historical query record data as the output of the training data;

[0040] Train the Transformer model according to the input and output of the training data.

[0041] Optionally, according to the distance feature information, use a clustering algorithm to perform clustering analysis on the distance feature information to obtain the query pattern information of the database to be optimized, including:

[0042] Construct a distance matrix between the distance feature information according to the distance feature information;

[0043] According to the distance matrix between the distance feature information, use the K-means clustering algorithm to perform clustering analysis on the distance matrix to generate a corresponding clustering map;

[0044] Perform feature statistics according to the clustering map to obtain the query pattern information of the database to be optimized;

[0045] Among them, the query pattern information includes: low-frequency query pattern information, high-frequency query pattern information, and abnormal query pattern information.

[0046] Based on the same inventive concept, the present invention also provides a database index optimization system based on deep reinforcement learning, including:

[0047] A data acquisition module for acquiring query record data of the database to be optimized;

[0048] A feature extraction module for using a pre-trained Transformer model to extract features from the query record data to obtain the distance feature information corresponding to the query record data;

[0049] A clustering analysis module for performing clustering analysis on the distance feature information according to the distance feature information by using a clustering algorithm to obtain the query pattern information of the database to be optimized;

[0050] A structure adjustment module, configured to perform index structure adjustment on the database to be optimized based on the query pattern information by using a pre-constructed index optimization model, so as to obtain the optimal index structure of the database to be optimized;

[0051] Wherein, the index optimization model is constructed based on deep reinforcement learning and a priority experience replay mechanism.

[0052] Optionally, the structure adjustment module includes:

[0053] An index feature analysis sub-module, configured to perform feature extraction according to the query pattern information to obtain index feature data;

[0054] An index optimization sub-module, configured to generate an index adjustment strategy according to the index feature data by using a pre-constructed index optimization model;

[0055] An index adjustment sub-module, configured to output the optimal index structure of the database to be optimized through an adaptive index adjustment mechanism according to the index adjustment strategy.

[0056] Optionally, the system further includes: an optimization model construction module, including:

[0057] An initialization sub-module, configured to initialize a network structure, model parameters, and an experience replay buffer;

[0058] An input data configuration sub-module, configured to use historical index feature data as input training data;

[0059] An output data configuration sub-module, configured to use the index strategy corresponding to the historical index feature data as output training data;

[0060] An experience value storage sub-module, configured to store the input training data and the output training data as experience values into the experience replay buffer;

[0061] A data training sub-module, configured to select a preset number of experience values from the experience replay buffer as index data and input them into a deep Q network, and output corresponding index prediction data;

[0062] A loss function calculation sub-module, configured to calculate a corresponding loss function value according to the index prediction data and the target structure data of the index data stored in the experience replay buffer;

[0063] A model parameter update sub-module, configured to update the model parameters by using the gradient descent method according to the loss function value to obtain an index optimization model.

[0064] Optionally, the index adjustment sub-module includes:

[0065] A strategy analysis unit, configured to analyze the index adjustment strategy to obtain an index operation priority;

[0066] An index adjustment unit, configured to obtain an index adjustment amount according to the index operation priority;

[0067] A performance evaluation unit, configured to evaluate the index adjustment amount through a performance evaluation function and output an optimal index structure of the database to be optimized.

[0068] Optionally, the expression corresponding to the performance evaluation function is as follows:

[0069]

[0070] where f(I) represents the overall performance score of the index structure I; α represents a first weight coefficient; β represents a second weight coefficient; C k represents the query frequency of the k-th cluster; k = 1…K; K represents the total number of clusters; D k (I) represents the query response time of the k-th cluster under the index structure I; R k (I) represents the resource utilization rate of the k-th cluster under the index structure I.

[0071] Optionally, the system further includes: a model training module, including:

[0072] An input feature configuration sub-module, configured to use the historical query record data of the database to be optimized as the input of the training data;

[0073] An output feature configuration sub-module, configured to use the distance feature information of the historical query record data as the output of the training data;

[0074] A model training sub-module, configured to train a Transformer model according to the input and output of the training data.

[0075] Optionally, the clustering analysis module includes:

[0076] A distance matrix construction sub-module, configured to construct a distance matrix between the distance feature information according to the distance feature information;

[0077] A graph generation sub-module, configured to perform clustering analysis on the distance matrix using the K-means clustering algorithm according to the distance matrix between the distance feature information and generate a corresponding clustering graph;

[0078] A feature statistics sub-module, configured to perform feature statistics according to the clustering graph to obtain query pattern information of the database to be optimized;

[0079] Among them, the query pattern information includes: low-frequency query pattern information, high-frequency query pattern information, and abnormal query pattern information.

[0080] On the other hand, the present invention also provides an electronic device, including: at least one processor and a memory; the memory and the processor are connected through a bus;

[0081] The memory is used to store one or more programs;

[0082] When the one or more programs are executed by the at least one processor, a database index optimization method based on deep reinforcement learning as described above is implemented.

[0083] On the other hand, the present invention also provides a computer-readable storage medium with an execution program stored thereon. When the execution program is executed, a database index optimization method based on deep reinforcement learning as described above is implemented.

[0084] Compared with the prior art, the beneficial effects of the present invention are:

[0085] The present invention provides a database index optimization method and system based on deep reinforcement learning, including: obtaining query record data of a database to be optimized; using a pre-trained Transformer model to extract features from the query record data to obtain distance feature information corresponding to the query record data; according to the distance feature information, using a clustering algorithm to perform clustering analysis on the distance feature information to obtain query pattern information of the database to be optimized; based on the query pattern information, using a pre-constructed index optimization model to adjust the index structure of the database to be optimized to obtain the optimal index structure of the database to be optimized; wherein, the index optimization model is constructed based on deep reinforcement learning and a priority experience replay mechanism; the present application can analyze the historical query logs of the database by using a pre-trained Transformer model, and through clustering processing, can identify the main components of the workload in the database; by using an index optimization model introducing a priority experience replay mechanism, it can perform real-time perception and analysis of the workload and dynamically adjust the optimal index structure; therefore, through the method of the present invention, the query performance of the database can be effectively improved, providing users with a more efficient and fast database service experience. BRIEF DESCRIPTION OF THE DRAWINGS

[0086] Figure 1 It is a schematic flowchart of a database index optimization method based on deep reinforcement learning provided by the present invention;

[0087] Figure 2 It is a schematic diagram of a query log service application framework of a database index optimization method based on deep reinforcement learning provided in a specific embodiment of the present invention;

[0088] Figure 3 Schematic diagram of the structural composition of a database index optimization system based on deep reinforcement learning provided by the present invention;

[0089] Figure 4 Schematic diagram of the structure of an electronic device provided by the present invention. Specific embodiments

[0090] The present invention proposes a database index optimization method, system, device and medium based on deep reinforcement learning. The following further elaborates on the specific embodiments of the present invention with reference to the accompanying drawings.

[0091] Embodiment 1:

[0092] The present invention provides a database index optimization method based on deep reinforcement learning. The process schematic diagram is as Figure 1 shown and includes:

[0093] Step 1: Obtain query record data of the database to be optimized;

[0094] Step 2: Use a pre-trained Transformer model to extract features from the query record data to obtain distance feature information corresponding to the query record data;

[0095] Step 3: According to the distance feature information, use a clustering algorithm to perform clustering analysis on the distance feature information to obtain query pattern information of the database to be optimized;

[0096] Step 4: Based on the query pattern information, use a pre-constructed index optimization model to adjust the index structure of the database to be optimized to obtain the optimal index structure of the database to be optimized;

[0097] Among them, the index optimization model is constructed based on deep reinforcement learning and a priority experience replay mechanism.

[0098] Generally, the query record data reflects the actual usage situation of the database. This information provides a real and dynamic basis for index optimization, ensuring that the optimization strategy can fit the actual needs. Moreover, the timestamp and frequency information of the query record data can help the system perceive the changes in the workload in real time, so as to dynamically adjust the index structure. This real-time perception ability cannot be achieved by traditional static methods. Therefore, by comprehensively analyzing the query record data, queries and data structures that need to be optimized can be more accurately identified, unnecessary index adjustments can be avoided, and thus the optimization efficiency and system performance can be improved.

[0099] Exemplarily, the above query record data may include detailed information such as query type, frequency, execution time, query statement, query timestamp, involved objects, etc.; The automated optimization method based on query record data can reduce the dependence on manual intervention, lower the labor cost, and at the same time improve the timeliness and accuracy of optimization; Therefore, obtaining the query record data of the database is the basis and key of index optimization. By comprehensively collecting and analyzing these data, the change of workload can be perceived in real time, the query pattern can be accurately identified, and a scientific basis can be provided for index optimization, thereby significantly improving the database query performance and the overall system efficiency.

[0100] Obtaining the query record data of the database provides a solid foundation for index optimization, but how to extract valuable information from these data and use it for actual optimization is still a key issue. In order to achieve in-depth analysis and feature extraction of query record data, the present invention introduces a Transformer model. With its powerful self-attention mechanism and parallel computing ability, the Transformer can efficiently capture the semantic associations and time series characteristics between queries, and provide accurate feature information for subsequent clustering analysis and index optimization. Specifically:

[0101] In one implementation, the Transformer model in step 2 above may include the following training process:

[0102] Use the historical query record data of the database to be optimized as the input of the training data;

[0103] Use the distance feature information of the historical query record data as the output of the training data;

[0104] Train the Transformer model according to the input and output of the training data;

[0105] In this implementation, by using the Transformer model to train historical query record data, the accuracy and efficiency of database index optimization can be significantly improved. First of all, with its powerful self-attention mechanism and parallel computing ability, the Transformer model can efficiently capture the semantic associations and time series characteristics between query statements, thereby extracting more accurate distance feature information. This feature extraction ability can deeply understand the changing trends of query patterns and provide a scientific basis for subsequent clustering analysis and index optimization. Secondly, by using historical query record data as input, the Transformer model can learn the complexity and diversity of the actual workload, thereby generating optimization strategies that better meet the actual needs. This data-driven training method can not only improve the adaptive ability of the model but also reduce the cost and time overhead of manual intervention. In addition, the training process of the Transformer model has high scalability and generalization ability, which can adapt to the changes in different database environments and workloads, ensuring that the optimization effect remains stable in various scenarios.

[0106] The distance feature information extracted by the Transformer model provides key input for the in-depth analysis of query record data. However, the high-dimensionality and complexity of these feature information still need to be further processed to be transformed into actionable query pattern information. In order to extract meaningful query patterns from the distance feature information, the present invention introduces a clustering analysis method. The clustering algorithm can classify query records with similar features, thereby identifying different query patterns and providing an accurate classification basis for subsequent index optimization. Specifically:

[0107] In one implementation, the process of performing clustering analysis on the distance feature information using a clustering algorithm according to the distance feature information in step 3 above to obtain the query pattern information of the database to be optimized may include:

[0108] Construct a distance matrix between the distance feature information according to the distance feature information;

[0109] Perform clustering analysis on the distance matrix using the K-means clustering algorithm according to the distance matrix between the distance feature information to generate a corresponding clustering map;

[0110] Perform feature statistics according to the clustering map to obtain the query pattern information of the database to be optimized;

[0111] Among them, the query pattern information may include: low-frequency query pattern information, high-frequency query pattern information, and abnormal query pattern information;

[0112] In this implementation, by constructing a distance matrix and applying the K-means clustering algorithm, the generated clustering map intuitively shows the results of dividing query records into different clusters. Each cluster represents a group of similar query patterns or types, which can divide complex query record data into query patterns with similar characteristics, such as low-frequency queries, high-frequency queries, and abnormal queries. This classification can not only identify common query patterns but also capture low-frequency or abnormal query patterns that are easily overlooked in traditional methods. This ability to identify unconventional query patterns enables more comprehensive consideration of all possible query scenarios during the optimization process, thereby avoiding performance bottlenecks caused by ignoring certain query patterns. Secondly, the clustering map generated by clustering analysis provides an intuitive and operable basis for pattern classification in database index optimization. This data-driven pattern recognition method not only improves the accuracy of optimization but also significantly reduces the need for manual intervention. More importantly, by identifying abnormal query patterns, potential performance problems or data anomalies can be discovered in a timely manner, and corresponding optimization measures can be taken before the problems expand. This anomaly detection ability is particularly important for maintaining stable and efficient operation in a complex and changing database environment. In addition, the process of clustering analysis is highly flexible and scalable, capable of adapting to changes in different database environments and workloads. Whether facing a high-concurrency real-time transaction system or processing complex data analysis queries, the clustering algorithm can effectively capture changes in query patterns and provide a basis for dynamic adjustment of index optimization. This adaptability enables the system to maintain a high optimization effect in various scenarios, thereby significantly improving the overall performance of the database.

[0113] The query pattern information obtained through clustering analysis provides a clear classification basis for index optimization. However, how to convert this pattern information into specific index adjustment strategies remains a key step in achieving efficient optimization. To closely integrate query pattern information with index structure adjustment, the present invention introduces an index optimization model based on deep reinforcement learning. This model can generate accurate index adjustment strategies according to query pattern information and dynamically optimize the index structure through an adaptive mechanism, thereby ensuring that the database is always in the best performance state. Specifically:

[0114] In one implementation, the process of adjusting the index structure of the database to be optimized based on the query pattern information in step 4 above by using a pre-constructed index optimization model to obtain the optimal index structure of the database to be optimized may include:

[0115] Extract feature data according to the query pattern information to obtain index feature data;

[0116] Generate an index adjustment strategy by using the pre-constructed index optimization model according to the index feature data;

[0117] According to the index adjustment strategy, through the adaptive index adjustment mechanism, output the optimal index structure of the database to be optimized;

[0118] In this implementation method, by extracting index feature data from the query pattern information, the impact of different query patterns on the database performance can be accurately captured, thus providing a scientific basis for index optimization. This data-driven feature extraction method not only improves the accuracy of optimization but also significantly reduces the dependence on manual experience, making the optimization process more intelligent and automated. Secondly, using the pre-constructed index optimization model to generate the index adjustment strategy can dynamically adjust the index structure according to the real-time query pattern. This dynamic adjustment ability enables the system to quickly respond when facing rapidly changing workloads, ensuring that the database is always in the best performance state. More importantly, the index optimization model continuously optimizes its strategy generation ability through deep reinforcement learning and dynamically adjusts the index structure through interaction with the environment to find the global optimal index configuration in a complex database environment to maximize the query efficiency, thereby significantly improving the query performance. This global optimization ability enables the system to still operate efficiently when processing complex queries and large-scale data. In addition, by outputting the optimal index structure through the adaptive index adjustment mechanism, the real-time evaluation and optimization of the index adjustment strategy can be realized. This adaptive mechanism can not only adjust the index structure according to the current query pattern but also feedback and optimize the adjustment effect through the performance evaluation function, thus ensuring that each adjustment can bring a significant performance improvement. This real-time feedback and optimization ability enables the system to maintain stable and efficient operation in a dynamically changing database environment. In addition, this implementation method can identify and optimize those low-frequency queries and abnormal query patterns that are easily ignored in traditional methods. By optimizing these unconventional query patterns, the system can significantly improve the overall query performance, avoid performance bottlenecks caused by ignoring certain query patterns, and the adaptive index adjustment mechanism can also dynamically adjust the priority and configuration of the index according to the change of the query pattern, so as to achieve the optimal performance allocation under limited resources.

[0119] The adjustment of the index structure based on the query pattern information depends on the pre-constructed index optimization model, and the construction process of this model is the core link to ensure the index optimization effect. In order to train an efficient and accurate index optimization model, the present invention adopts the method of deep reinforcement learning and combines the priority experience replay mechanism. Based on historical data, through continuous learning and optimization, the prediction ability and generalization performance of the model are improved. Specifically:

[0120] In one implementation method, the above-mentioned index optimization model may include the following construction process:

[0121] Initialize the network structure, model parameters, and experience replay buffer;

[0122] Use the historical index feature data as the input training data;

[0123] Use the index strategy corresponding to the historical index feature data as the output training data;

[0124] Store the input training data and the output training data as experience values in the experience replay buffer;

[0125] Select a preset number of experience values from the experience replay buffer as index data and input them into the deep Q-network to output the corresponding index prediction data;

[0126] Calculate the corresponding loss function value according to the index prediction data and the target structure data of the index data stored in the experience replay buffer;

[0127] Update the model parameters using the gradient descent method according to the loss function value to obtain the index optimization model;

[0128] In this implementation method, through the adaptive learning ability of the deep Q-network, the index strategy can be dynamically adjusted, significantly improving the adaptability and robustness of the system in scenarios of data distribution changes and query load fluctuations, breaking through the limitations of traditional static optimization methods; secondly, leveraging the powerful fitting ability of the deep neural network, the model can efficiently capture the non-linear relationship between complex query patterns and data features, thereby generating a better index strategy when processing high-dimensional and unstructured data, greatly improving the query performance; in addition, the introduction of the experience replay mechanism enables the model to efficiently learn from historical data, avoiding the over-reliance of traditional methods on real-time data, and at the same time significantly improving the training efficiency and model stability through batch learning and gradient descent optimization. This index optimization method combining deep learning and reinforcement learning not only provides a new idea for database performance optimization but also demonstrates advantages in aspects such as adaptive learning, complex pattern capture, and efficient training.

[0129] In the above implementation method, the construction process of the index optimization model provides a basis for generating accurate index adjustment strategies. However, how to convert these strategies into specific index adjustment actions and ensure that the adjusted index structure can effectively improve the database performance is still a key issue. To achieve this goal, the present invention can also introduce an adaptive index adjustment mechanism. This mechanism dynamically outputs the optimal index structure by parsing and prioritizing the index adjustment strategies and combining with a performance evaluation function, thereby ensuring that the database is always in the best operating state. Specifically:

[0130] In one implementation method, the process of outputting the optimal index structure of the database to be optimized through the adaptive index adjustment mechanism according to the index adjustment strategy can include:

[0131] Parse the index adjustment strategy to obtain the index operation priority;

[0132] Obtain the index adjustment amount according to the index operation priority;

[0133] Evaluate the index adjustment amount through the performance evaluation function and output the optimal index structure of the database to be optimized;

[0134] In this implementation method, by parsing the index adjustment strategy, the priorities of different index operations can be accurately identified, so as to ensure that under limited resources, the adjustments that most significantly improve performance are preferentially executed. This priority sorting ability enables efficient resource allocation in a complex database environment and avoids resource waste caused by blind adjustments. Secondly, calculating the index adjustment amount according to the index operation priority can dynamically generate an optimization strategy that highly matches the current workload. This dynamic adjustment ability not only improves the accuracy of optimization but also significantly reduces the need for manual intervention. Moreover, by evaluating the index adjustment amount through the performance evaluation function, the adjustment effect can be feedback in real time, and the index structure can be continuously optimized according to the feedback result. This real-time feedback and optimization mechanism enables a rapid response when facing a rapidly changing workload, ensuring that the database is always in the best performance state. In addition, the adaptive index adjustment mechanism can identify and optimize those low-frequency queries and abnormal query patterns that are easily overlooked in traditional methods. By optimizing these unconventional query patterns, the overall query performance can be significantly improved, avoiding performance bottlenecks caused by ignoring certain query patterns. And this mechanism can dynamically adjust the priority and configuration of the index according to the changes in the query pattern, so as to achieve optimal performance allocation under limited resources.

[0135] Exemplarily, the expression corresponding to the above performance evaluation function can be as follows:

[0136]

[0137] Among them, f(I) represents the overall performance score of the index structure I; α represents the first weight coefficient; β represents the second weight coefficient; C k represents the query frequency of the k-th cluster; k = 1…K; K represents the total number of clusters; D k (I) represents the query response time of the k-th cluster under the index structure I; R k(I) represents the resource utilization rate of the k-th cluster under the index structure I; in this example, the performance evaluation function can comprehensively evaluate the performance of the index structure by considering both the query response time and the resource utilization rate. This comprehensive evaluation method not only improves the accuracy of optimization but also ensures that the optimized index structure can effectively control resource consumption while improving query performance, thus achieving the best balance between performance and resource utilization. Secondly, by introducing the query frequency C k as a weight factor, it can pay more attention to the performance optimization of high-frequency queries, thereby significantly improving the overall query performance. This frequency-based optimization strategy enables efficient operation even in the face of high-concurrency queries. In addition, the performance evaluation function divides query patterns into different categories through clustering analysis and evaluates the performance of each cluster under the index structure separately. This evaluation method can not only improve the accuracy of optimization but also identify and optimize low-frequency queries and abnormal query patterns that are easily overlooked in traditional methods. According to the changes in query patterns, it dynamically adjusts the priority and configuration of indexes, thereby achieving optimal performance allocation under limited resources.

[0138] In summary, in the current database management system, traditional index selection and optimization techniques can no longer meet the challenges of rapid data volume growth, diverse business requirements, and dynamic changes in the workload. To address these pain points, the present invention proposes a database index optimization method based on deep reinforcement learning. Its core idea is to achieve efficient database performance optimization by real-time perceiving workload changes and dynamically adjusting the index structure. The index optimization process of the present invention mainly consists of two key stages, namely the trend detection stage and the online index selection stage. In the trend detection stage, a pre-trained Transformer model is first used to extract features from historical query data, and then the feature matrix is clustered to analyze historical query patterns (high-frequency query patterns, low-frequency query patterns, and abnormal query patterns). In the online index selection stage, a deep reinforcement learning algorithm is adopted to dynamically adjust the optimal index configuration during the operation of the database. Through the clustering analysis in the trend detection stage, different query patterns can be effectively identified, enabling the system to accurately capture changes in users' query requirements. This process ensures that the database can promptly adapt to new query patterns and avoid performance bottlenecks caused by unreasonable index structures. In the online index selection stage, the deep reinforcement learning algorithm can automatically adjust the index configuration by real-time perceiving changes in the workload. This dynamic optimization ability not only improves the query response speed but also significantly reduces the resource consumption of the database. Compared with traditional static index strategies, this method can achieve higher scalability and flexibility in a big data environment.

[0139] Example 2:

[0140] Taking the intelligent operation center of the intelligent sharing financial platform for daily financial-related query scenarios as an example, a database index optimization method based on deep reinforcement learning provided by the present invention is described. The specific steps include:

[0141] Step1: Centralize the collection of financial-related query records in the database, including:

[0142] High-frequency operations: such as SELECT * FROM the transaction table WHERE date BETWEEN '2023-01-01' AND '2023-12-31' (monthly statement generation).

[0143] Low-frequency operations: such as complex join queries for cross-year historical data auditing.

[0144] Abnormal operations: such as a sudden surge in temporary tax inspection requests;

[0145] Step2: Use a pre-trained Transformer model to extract features from each batch of data and vectorize the data;

[0146] Step3: Calculate the distance matrix based on the results of Step2, construct a clustering map using K-means according to the calculation results, and analyze the characteristics of each type of query;

[0147] For example, the key analysis results are as follows:

[0148] High-frequency patterns: Concentrated in daily transaction queries (for example, accounting for 70%), and the index needs to be optimized first to reduce full-table scans;

[0149] Low-frequency patterns: Mostly cross-year audit queries (for example, accounting for 25%), and the index storage overhead and query performance need to be balanced;

[0150] Abnormal patterns: Sudden inspection requests (for example, accounting for 5%), and temporary indexes need to be dynamically created and released quickly;

[0151] Step4: Determine which category in the results of Step3 the current query data belongs to, use the index set of this category of historical data as the candidate index for the current query, design a deep reinforcement learning-related model and initialize it, and train and generate the optimal index structure;

[0152] Step5: After executing the selected index optimization action, update the Transformer model and K-means parameters, including:

[0153] Transformer fine-tuning: New query data triggers incremental training of the model, enhancing its adaptability to new business scenarios (such as new tax rules).

[0154] Clustering model update: Adjust the clustering center according to the latest query distribution to avoid mode drift;

[0155] Through this specific embodiment, a database index optimization method based on deep reinforcement learning provided by the present invention can be illustrated. In the financial query scenario of the intelligent operation center of the intelligent sharing financial platform, significant technical advantages and effects are demonstrated. The schematic diagram is as Figure 2 shown. By collecting relevant query record data, it is divided into several subsets, and the Transformer model is used to construct a structured representation of the data, and the information is inspected and quantified. Then, the data is classified by the K-means clustering algorithm to form different categories for subsequent further processing. In this process, the optimal subset will be selected for refined analysis to ensure the accurate grasp of the target data. At the same time, the system will obtain the current state of the query statement in real time. Finally, a powerful learning model is established, and the performance is improved through an optimization algorithm to ensure that the constructed model can efficiently and accurately reflect user needs. During the whole process, the relevant parameters of Transformer and K-means will also be updated to continuously improve the model effect. Through the accurate identification and optimization of high-frequency queries, low-frequency queries, and abnormal queries, the query performance of the database is significantly improved; by the Transformer model and the K-means clustering algorithm, the workload changes are sensed in real time, and the index structure is dynamically adjusted to ensure efficient operation even when new business scenarios or query mode drift occur. In addition, the deep reinforcement learning model generates the optimal index strategy quickly through the priority experience replay mechanism, and realizes precise optimization in combination with the performance evaluation function. This full-life-cycle automated management not only reduces the manual intervention cost, but also significantly improves the flexibility and adaptability of the system. Generally speaking, through the intelligent index optimization method, the present invention realizes high-efficient, stable and low-cost performance improvement in a complex and changeable database environment.

[0156] Embodiment 3:

[0157] Based on the same inventive concept, the present invention also provides a database index optimization system based on deep reinforcement learning. The schematic diagram of the structural composition is as Figure 3 shown, including:

[0158] A data acquisition module, used to acquire the query record data of the database to be optimized;

[0159] A feature extraction module, used to extract the feature of the query record data by using a pre-trained Transformer model to obtain the distance feature information corresponding to the query record data;

[0160] A clustering analysis module, which is used to perform clustering analysis on distance feature information by using a clustering algorithm according to the distance feature information, so as to obtain query pattern information of the database to be optimized;

[0161] A structure adjustment module, which is used to adjust the index structure of the database to be optimized by using a pre-constructed index optimization model based on the query pattern information, so as to obtain the optimal index structure of the database to be optimized;

[0162] Among them, the index optimization model is constructed based on deep reinforcement learning and a priority experience replay mechanism.

[0163] In one implementation, the system may further include: a model training module, which may specifically include:

[0164] An input feature configuration sub-module, which is used to use the historical query record data of the database to be optimized as the input of the training data;

[0165] An output feature configuration sub-module, which is used to use the distance feature information of the historical query record data as the output of the training data;

[0166] A model training sub-module, which is used to train the Transformer model according to the input and output of the training data.

[0167] In one implementation, the above-mentioned clustering analysis module may include:

[0168] A distance matrix construction sub-module, which is used to construct a distance matrix between distance feature information according to the distance feature information;

[0169] A spectrum generation sub-module, which is used to perform clustering analysis on the distance matrix by using the K-means clustering algorithm according to the distance matrix between distance feature information, and generate a corresponding clustering spectrum;

[0170] A feature statistics sub-module, which is used to perform feature statistics according to the clustering spectrum to obtain query pattern information of the database to be optimized;

[0171] Among them, the query pattern information includes: low-frequency query pattern information, high-frequency query pattern information, and abnormal query pattern information.

[0172] In one implementation, the system may further include: an optimization model construction module, which may specifically include:

[0173] An initialization sub-module, which is used to initialize the network structure, model parameters, and experience replay buffer;

[0174] An input data configuration sub-module, which is used to use the historical index feature data as the input training data;

[0175] An output data configuration sub-module, which is used to use the index strategy corresponding to the historical index feature data as the output training data;

[0176] An experience value storage sub-module, which is used to store the input training data and the output training data as experience values into the experience replay buffer;

[0177] A data training sub-module, which is used to select a preset number of experience values from the experience replay buffer as index data and input them into the deep Q network, and output the corresponding index prediction data;

[0178] A loss function calculation sub-module, which is used to calculate the corresponding loss function value according to the index prediction data and the target structure data of the index data stored in the experience replay buffer;

[0179] A model parameter update sub-module, which is used to update the model parameters by using the gradient descent method according to the loss function value to obtain an index optimization model.

[0180] In one implementation, the above-mentioned structure adjustment module may include:

[0181] An index feature analysis sub-module, which is used to perform feature extraction according to the query pattern information to obtain index feature data;

[0182] An index optimization sub-module, which is used to generate an index adjustment strategy according to the index feature data by using a pre-constructed index optimization model;

[0183] An index adjustment sub-module, which is used to output the optimal index structure of the database to be optimized through an adaptive index adjustment mechanism according to the index adjustment strategy.

[0184] In this implementation, the above-mentioned index adjustment sub-module may include:

[0185] A strategy parsing unit, which is used to parse the index adjustment strategy to obtain the index operation priority;

[0186] An index adjustment unit, which is used to obtain the index adjustment amount according to the index operation priority;

[0187] A performance evaluation unit, which is used to evaluate the index adjustment amount through a performance evaluation function and output the optimal index structure of the database to be optimized.

[0188] Exemplarily, the expression corresponding to the above-mentioned performance evaluation function may be as follows:

[0189]

[0190] Among them, f(I) represents the overall performance score of the index structure I; α represents the first weight coefficient; β represents the second weight coefficient; C kDenote the query frequency of the k-th cluster; k = 1…K; K represents the total number of clusters; D k (I) Denote the query response time of the k-th cluster under the index structure I; R k (I) Denote the resource utilization rate of the k-th cluster under the index structure I.

[0191] Embodiment 4:

[0192] As Figure 4 shown, the present invention also provides an electronic device, which may be a computer device, a single-chip microcomputer device, a smart mobile device, etc. The electronic device in this embodiment may include a processor, a memory, a transceiver component, etc. The memory, the processor, and the transceiver component are connected by a bus; the memory can be used to store an execution program, and an exemplary execution program may include instructions; the processor is used to execute the instructions stored in the memory. The memory can also be used to store data, and this data can be called and / or modified when the instructions are executed.

[0193] The processor may be a Central Processing Unit (CPU), or may also be other general-purpose processors, Digital Signal Processors (DSPs), Application Specific Integrated Circuits (ASICs), Field-Programmable Gate Arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc. It is the computing core and control core of the terminal, and is suitable for implementing one or more instructions. Specifically, it is suitable for loading and executing one or more instructions in the storage medium to implement the corresponding method flow or corresponding function, so as to implement the steps of a database index optimization method based on deep reinforcement learning in the above embodiment.

[0194] Embodiment 5:

[0195] Based on the same inventive concept, the present invention also provides a readable storage medium, specifically an electronic device-readable storage medium (Memory). The electronic device-readable storage medium is a memory device in the electronic device, used to store programs and data. It can be understood that the storage medium here can include both the built-in storage medium in the electronic device and, of course, the extended storage medium supported by the electronic device. The storage medium provides a storage space, and this storage space stores the operating system of the terminal. Moreover, in this storage space, one or more instructions suitable for being loaded and executed by the processor are also stored. These instructions can be one or more execution programs (including program codes). It should be noted that the storage medium here can be a high-speed RAM memory or a non-volatile memory, such as at least one disk memory. By the processor loading and executing one or more instructions stored in the storage medium, the steps of a method for optimizing a database index based on deep reinforcement learning in the above embodiments can be implemented.

[0196] Those skilled in the art should understand that the embodiments of the present invention can be provided as a method, a system, or a computer program product. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk memories, CD-ROMs, optical memories, etc.) containing computer-usable program codes.

[0197] The present invention is described with reference to the flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to the embodiments of the present invention. It should be understood that each flow and / or block in the flowchart and / or block diagram, and the combination of flows and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, such that the instructions executed by the processor of the computer or other programmable data processing devices generate a device for implementing the specified functions in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0198] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory generate a manufactured article including an instruction device, and the instruction device implements the specified functions in Figure 1 one flow or multiple flows and / or blocks Figure 1 one block or multiple blocks.

[0199] These computer program instructions can also be loaded onto a computer or other programmable data processing apparatus, so that a series of operation steps are executed on the computer or other programmable apparatus to produce a computer-implemented process, thereby providing instructions for implementing the functions specified in one process or multiple processes and / or one block or multiple blocks in the flow Figure 1 one process or multiple processes and / or blocks Figure 1 steps of the function specified in one block or multiple blocks.

[0200] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention rather than to limit the scope of its protection. Although the present invention has been described in detail with reference to the above embodiments, those of ordinary skill in the art should understand that after reading the present invention, various changes, modifications or equivalent replacements can still be made to the specific implementation manners of the application. However, these changes, modifications or equivalent replacements are all within the scope of the protection of the claims pending for the application.

Claims

1. A database index optimization method based on deep reinforcement learning, characterized in that: include: Obtain query record data of the database to be optimized; Using a pre-trained Transformer model to perform feature extraction on the query record data to obtain distance feature information corresponding to the query record data; According to the distance feature information, cluster analysis is performed on the distance feature information using a clustering algorithm to obtain query pattern information of the database to be optimized; Based on the query pattern information, the index structure of the database to be optimized is adjusted using a pre-built index optimization model to obtain an optimal index structure of the database to be optimized; Among them, the index optimization model is built based on deep reinforcement learning and priority experience replay mechanism.

2. The method according to claim 1, characterized in that The step of adjusting the index structure of the database to be optimized by using a pre-built index optimization model based on the query mode information to obtain an optimal index structure of the database to be optimized includes: Perform feature extraction according to the query pattern information to obtain index feature data; Generate an index adjustment strategy based on the index feature data using a pre-built index optimization model; According to the index adjustment strategy, an optimal index structure of the database to be optimized is output through an adaptive index adjustment mechanism.

3. The method according to claim 2, characterized in that The index optimization model includes the following construction process: Initialize the network structure, model parameters and experience replay buffer; Use historical index feature data as input training data; Using the index strategy corresponding to the historical index feature data as output training data; storing the input training data and the output training data as experience values ​​in the experience playback buffer; Selecting a preset number of experience values ​​from the experience playback buffer as index data and inputting them into the deep Q network, and outputting corresponding index prediction data; Calculating a corresponding loss function value according to the index prediction data and the target structure data of the index data stored in the experience playback buffer; According to the loss function value, the model parameters are updated using the gradient descent method to obtain an index optimization model.

4. The method according to claim 2, characterized in that Outputting the optimal index structure of the database to be optimized according to the index adjustment strategy through an adaptive index adjustment mechanism includes: Parsing the index adjustment strategy to obtain the index operation priority; Obtaining an index adjustment amount according to the index operation priority; The index adjustment amount is evaluated by a performance evaluation function, and an optimal index structure of the database to be optimized is output.

5. The method according to claim 4, characterized in that The expression corresponding to the performance evaluation function is as follows: Where f(I) represents the overall performance score of the index structure I; α represents the first weight coefficient; β represents the second weight coefficient; C k represents the query frequency of the kth cluster; k = 1…K; K represents the total number of clusters; D k (I) represents the query response time of the kth cluster under index structure I; R k (I) represents the resource utilization of the kth cluster under index structure I.

6. The method according to claim 1, characterized in that The Transformer model includes the following training process: Using historical query record data of the database to be optimized as input of training data; Using the distance feature information of the historical query record data as output of training data; The Transformer model is trained according to the input and output of the training data.

7. The method according to claim 1, characterized in that The step of performing cluster analysis on the distance feature information by using a clustering algorithm according to the distance feature information to obtain the query pattern information of the database to be optimized includes: Constructing a distance matrix between the distance feature information according to the distance feature information; According to the distance matrix between the distance feature information, a K-means clustering algorithm is used to perform cluster analysis on the distance matrix to generate a corresponding clustering map; Perform feature statistics according to the clustering graph to obtain query pattern information of the database to be optimized; The query mode information includes: low-frequency query mode information, high-frequency query mode information and abnormal query mode information.

8. A database index optimization system based on deep reinforcement learning, characterized in that: include: A data acquisition module is used to obtain query record data of the database to be optimized; A feature extraction module, used to extract features from the query record data using a pre-trained Transformer model to obtain distance feature information corresponding to the query record data; A clustering analysis module, used to perform clustering analysis on the distance feature information using a clustering algorithm according to the distance feature information, so as to obtain query pattern information of the database to be optimized; A structure adjustment module, used to adjust the index structure of the database to be optimized based on the query mode information and using a pre-built index optimization model to obtain an optimal index structure of the database to be optimized; Among them, the index optimization model is built based on deep reinforcement learning and priority experience replay mechanism.

9. The system according to claim 8, characterized in that The cluster analysis module comprises: A distance matrix construction submodule, used to construct a distance matrix between the distance feature information according to the distance feature information; A graph generation submodule is used to perform cluster analysis on the distance matrix between the distance feature information using a K-means clustering algorithm to generate a corresponding cluster graph; A feature statistics submodule, used to perform feature statistics according to the clustering graph to obtain query mode information of the database to be optimized; The query mode information includes: low-frequency query mode information, high-frequency query mode information and abnormal query mode information.

10. The system according to claim 8, characterized in that The structural adjustment module comprises: An index feature analysis submodule, used to extract features according to the query pattern information to obtain index feature data; An index optimization submodule, used to generate an index adjustment strategy based on the index feature data using a pre-built index optimization model; The index adjustment submodule is used to output the optimal index structure of the database to be optimized through an adaptive index adjustment mechanism according to the index adjustment strategy.

Images