A database index optimization method and system based on deep reinforcement learning

Abstract

The present invention provides a database index optimization method and system based on deep reinforcement learning, comprising: obtaining query record data of a database to be optimized; performing feature extraction on the query record data using a pre-trained Transformer model to obtain corresponding distance feature information; performing cluster analysis on the distance feature information using a clustering algorithm based on the distance feature information to obtain query pattern information of the database to be optimized; adjusting the index structure of the database to be optimized using a pre-built index optimization model based on the query pattern information to obtain the optimal index structure of the database to be optimized; the present application can analyze historical query logs by using the Transformer model, and can identify the main components of the workload through clustering processing; and can perceive and analyze the workload in real time by using an index optimization model that introduces a priority experience replay mechanism, and dynamically adjust the optimal index structure.

Description

Technical Field

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

[0002] Traditional index selection and optimization techniques in current database management systems are facing severe challenges. With the continuous growth of data volumes and the increasing diversification of business needs, workloads are also changing rapidly. However, traditional index selection and optimization techniques are unable to effectively adapt to these changes, which directly leads to a decline in database query performance.

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

[0004] (1) Rule-based method: This method relies on pre-set rules for index optimization. For example, it constructs indexes based on data access frequency, data type, and other rules. The disadvantages are as follows:

[0005] Lack of flexibility: Preset rules are often based on past experience or general database principles, making them difficult to adapt to complex and changing real-world workloads. For example, during an e-commerce promotion, data for products that are rarely accessed may suddenly become popular, and rule-based methods cannot detect such temporary changes in a timely manner.

[0006] Difficulty coping with complex scenarios: In databases containing multiple related tables, simple rule-based index optimization cannot effectively handle complex queries involving multi-table joins. Different tables may have one-to-many or many-to-many relationships, and queries may involve multiple filtering conditions and sorting requirements. Relying solely on preset rules makes it difficult to find the optimal indexing strategy.

[0007] (2) Machine learning-based methods: This method uses machine learning algorithms to learn and optimize based on data characteristics and has certain adaptive capabilities. However, its disadvantages are as follows:

[0008] High computing resource consumption: Machine learning methods require learning and training large amounts of data. Deep learning algorithms, in particular, require building complex neural network models, consuming significant computing resources such as CPUs, memory, and GPUs. For resource-limited database systems, this resource consumption can degrade system performance and even impact the normal operation of other critical services.

[0009] Long response time: Machine learning methods require multiple complex steps, including data collection, feature extraction, and model training. This results in long response times when faced with new query requests or workload changes. For example, when a new query type appears for the first time, the machine learning model may need to be re-evaluated and adjusted, a process that can take minutes or even longer, making it unsuitable for applications that require fast responses.

[0010] Lack of flexibility in dynamic environments: Although machine learning methods have certain adaptability, they are still insufficient when dealing with rapidly changing workloads. For example, in real-time financial transaction databases, the flow and pattern of transaction data can change dramatically in a short period of time. However, the learning and adjustment process of machine learning models is relatively slow, making it impossible to quickly provide optimized indexing solutions for new workload patterns.

[0011] To summarize, the core problem faced by traditional index selection and optimization technologies in current database management systems can be summarized as follows: they are unable to efficiently adapt to rapidly changing workloads, resulting in a significant decline in database query performance. Summary of the Invention

[0012] To address the problem that traditional index selection methods cannot adapt to rapid changes in workload, resulting in decreased database query performance, this paper proposes a database index optimization method based on deep reinforcement learning, including:

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

[0014] 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;

[0015] performing cluster analysis on the distance feature information using a clustering algorithm according to the distance feature information to obtain query pattern information of the database to be optimized;

[0016] 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;

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

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

[0019] Perform feature extraction based on the query pattern information to obtain index feature data;

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

[0021] According to the index adjustment strategy, an optimal index structure of the database to be optimized is output through an adaptive index adjustment mechanism.

[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] Using the index strategy corresponding to the historical index feature data as output training data;

[0026] Storing the input training data and the output training data as experience values in the experience playback buffer;

[0027] Selecting a preset number of experience values from the experience replay buffer as index data and inputting them into the deep Q network, and outputting corresponding index prediction data;

[0028] 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 replay buffer;

[0029] According to the loss function value, the model parameters are updated using the gradient descent method to obtain an index optimization model.

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

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

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

[0033] The index adjustment amount is evaluated by a performance evaluation function, and an optimal index structure of the database to be optimized is output.

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

[0035]

[0036] Where f(I) represents the overall performance score of the index structure I; α represents the first weight coefficient; β represents the second weight coefficient; C krepresents 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.

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

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

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

[0040] The Transformer model is trained based on the input and output of the training data.

[0041] Optionally, performing cluster analysis on the distance feature information using a clustering algorithm to obtain query pattern information of the database to be optimized includes:

[0042] Constructing 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, a K-means clustering algorithm is used to perform cluster analysis on the distance matrix to generate a corresponding cluster map;

[0044] Performing feature statistics based on the clustering graph to obtain query pattern information of the database to be optimized;

[0045] The query mode information includes: low-frequency query mode information, high-frequency query mode information and abnormal query mode information.

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

[0047] A data acquisition module is used to obtain query record data of the database to be optimized;

[0048] A feature extraction module is 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;

[0049] A clustering analysis module, configured to perform cluster analysis on the distance feature information using a clustering algorithm based on the distance feature information to obtain query pattern information of the database to be optimized;

[0050] A structure adjustment module is used to adjust the index structure of the database to be optimized based on the query pattern information using a pre-built index optimization model to obtain an optimal index structure of the database to be optimized;

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

[0052] Optionally, the structure adjustment module includes:

[0053] An index feature analysis submodule, configured to extract features based on the query pattern information to obtain index feature data;

[0054] An index optimization submodule, configured to generate an index adjustment strategy based on the index feature data and using a pre-built index optimization model;

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

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

[0057] Initialization submodule, used to initialize the network structure, model parameters and experience replay buffer;

[0058] Input data configuration submodule, used to use historical index feature data as input training data;

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

[0060] An experience value storage submodule, configured to store the input training data and the output training data as experience values in the experience playback buffer;

[0061] A data training submodule is used to select a preset number of experience values from the experience playback buffer as index data to input into the deep Q network, and output corresponding index prediction data;

[0062] a loss function calculation submodule, configured to calculate a corresponding loss function value based on the index prediction data and the target structure data of the index data stored in the experience replay buffer;

[0063] The model parameter updating submodule is used to update the model parameters according to the loss function value using the gradient descent method to obtain an index optimization model.

[0064] Optionally, the index adjustment submodule includes:

[0065] A policy parsing unit, configured to parse the index adjustment policy 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 is used 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 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.

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

[0072] An input feature configuration submodule is used to use the historical query record data of the database to be optimized as input for training data;

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

[0074] The model training submodule is used to train the Transformer model based on the input and output of the training data.

[0075] Optionally, the cluster analysis module includes:

[0076] A distance matrix construction submodule is used to construct a distance matrix between the distance feature information according to the distance feature information;

[0077] 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;

[0078] A feature statistics submodule, configured to perform feature statistics based on the clustering graph to obtain query pattern information of the database to be optimized;

[0079] The query mode information includes: low-frequency query mode information, high-frequency query mode information and abnormal query mode information.

[0080] In another aspect, the present invention further provides an electronic device, comprising: at least one processor and a memory; the memory and the processor are connected via 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, the 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 having an execution program stored thereon. When the execution program is executed, the database index optimization method based on deep reinforcement learning as described above is implemented.

[0084] Compared with the prior art, the present invention has the following beneficial effects:

[0085] The present invention provides a database index optimization method and system based on deep reinforcement learning, comprising: obtaining query record data of a database to be optimized; performing feature extraction on the query record data using a pre-trained Transformer model to obtain distance feature information corresponding to the query record data; performing cluster analysis on the distance feature information using a clustering algorithm based on the distance feature information to obtain query pattern information of the database to be optimized; adjusting the index structure of the database to be optimized using a pre-constructed index optimization model based on the query pattern information 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 can identify the main components of the workload in the database through clustering processing; by using an index optimization model that introduces a priority experience replay mechanism, it can perceive and analyze the workload in real time and dynamically adjust the optimal index structure; therefore, the method of the present invention can effectively improve the query performance of the database and provide users with a more efficient and fast database service experience. BRIEF DESCRIPTION OF THE DRAWINGS

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

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

[0088] Figure 3 A schematic diagram of the structure of a database index optimization system based on deep reinforcement learning provided by the present invention;

[0089] Figure 4 This is a structural diagram of an electronic device provided by the present invention. DETAILED DESCRIPTION

[0090] The present invention proposes a database index optimization method, system, device and medium based on deep reinforcement learning. The specific implementation methods of the present invention are further described in detail below with reference to the accompanying drawings.

[0091] Example 1:

[0092] The present invention provides a database index optimization method based on deep reinforcement learning, the flow chart is as follows Figure 1 Shown, including:

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

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

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

[0096] Step 4: Based on the query pattern information, use the pre-built 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 built based on deep reinforcement learning and priority experience replay mechanism.

[0098] Generally, query record data reflects the actual usage of the database. This information provides a real and dynamic basis for index optimization, ensuring that the optimization strategy can meet actual needs. In addition, the timestamp and frequency information of the query record data can help the system perceive changes in workload in real time, thereby dynamically adjusting the index structure. This real-time perception capability is impossible to achieve with traditional static methods. Therefore, by comprehensively analyzing the query record data, we can more accurately identify queries and data structures that need to be optimized, avoid unnecessary index adjustments, and thus improve optimization efficiency and system performance.

[0099] For example, the query record data mentioned above may include detailed information such as query type, frequency, execution time, query statement, query timestamp, and objects involved. Automated optimization methods based on query record data can reduce reliance on manual intervention, lower labor costs, and improve the timeliness and accuracy of optimization. Therefore, obtaining database query record data is the basis and key to index optimization. By comprehensively collecting and analyzing this data, workload changes can be perceived in real time, query patterns can be accurately identified, and a scientific basis can be provided for index optimization, thereby significantly improving database query performance and overall system efficiency.

[0100] Obtaining query record data from the database provides a solid foundation for index optimization, but how to extract valuable information from this data and use it for actual optimization remains a key issue. To achieve in-depth analysis and feature extraction of query record data, this paper introduces the Transformer model. With its powerful self-attention mechanism and parallel computing capabilities, the Transformer model can efficiently capture the semantic associations and time series characteristics between queries, providing 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] The historical query record data of the database to be optimized is used as the input of training data;

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

[0104] Train the Transformer model based on 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, with its powerful self-attention mechanism and parallel computing capabilities, 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 capability 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 actual workloads, thereby generating optimization strategies that are more in line with actual needs. This data-driven training method can not only improve the model's adaptability, but also reduce the cost and time overhead of manual intervention. In addition, the training process of the Transformer model is highly scalable and generalizable, and can adapt to 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 in-depth analysis of query record data. However, the high dimensionality and complexity of this feature information still require further processing to convert it into actionable query pattern information. In order to extract meaningful query patterns from distance feature information, this paper introduces a clustering analysis method. The clustering algorithm can classify query records with similar characteristics, thereby identifying different query patterns and providing accurate classification basis for subsequent index optimization. Specifically:

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

[0108] According to the distance feature information, a distance matrix between the distance feature information is constructed;

[0109] According to the distance matrix between the distance feature information, the K-means clustering algorithm is used to perform cluster analysis on the distance matrix to generate the corresponding cluster map;

[0110] Perform feature statistics based on the clustering graph to obtain query pattern information of the database to be optimized;

[0111] The query mode information may include: low-frequency query mode information, high-frequency query mode information, and abnormal query mode information;

[0112] In this implementation, by constructing a distance matrix and applying the K-means clustering algorithm, the generated cluster map intuitively shows the result of query records being divided into different clusters. Each cluster represents a group of similar query patterns or types, and 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 those low-frequency or abnormal query patterns that are easily overlooked in traditional methods. The ability to identify unconventional query patterns allows all possible query scenarios to be considered more comprehensively during the optimization process, thereby avoiding performance bottlenecks caused by ignoring certain query patterns. Secondly, the cluster map generated by cluster analysis provides an intuitive and operational pattern classification basis for database index optimization. This data-based The 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, it can promptly discover potential performance issues or data anomalies, so that corresponding optimization measures can be taken before the problem escalates. This anomaly detection capability is particularly important for maintaining stable and efficient operation in complex and changing database environments. In addition, the clustering analysis process is highly flexible and scalable, and can adapt to changes in different database environments and workloads. Whether facing high-concurrency real-time trading systems or processing complex data analysis queries, clustering algorithms 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 cluster analysis provides a clear classification basis for index optimization, but 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, this paper introduces an index optimization model based on deep reinforcement learning. This model can generate accurate index adjustment strategies based on 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 using a pre-built index optimization model based on the query pattern information in step 4 to obtain the optimal index structure of the database to be optimized may include:

[0115] Perform feature extraction based on query pattern information to obtain index feature data;

[0116] Generate index adjustment strategies based on index feature data using pre-built index optimization models;

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

[0118] In this implementation, by extracting index feature data from query pattern information, the impact of different query patterns on database performance can be accurately captured, thereby providing a scientific basis for index optimization. This data-driven feature extraction method not only improves the accuracy of optimization, but also significantly reduces dependence on manual experience, making the optimization process more intelligent and automated; secondly, the index adjustment strategy is generated using a pre-built index optimization model, which can dynamically adjust the index structure according to the real-time query pattern. This dynamic adjustment capability enables the system to respond quickly to rapidly changing workloads, ensuring that the database is always in the best performance state. More importantly, the index optimization model continuously optimizes its own strategy generation capability through deep reinforcement learning, and By interacting with the environment, the system dynamically adjusts the index structure and finds the globally optimal index configuration in complex database environments to maximize query efficiency, significantly improving query performance. This global optimization capability enables the system to maintain efficient operation when processing complex queries and large-scale data. In addition, the adaptive index adjustment mechanism outputs the optimal index structure, enabling real-time evaluation and optimization of index adjustment strategies. This adaptive mechanism not only adjusts the index structure according to the current query pattern, but also provides feedback and optimization on the adjustment effect through performance evaluation functions, ensuring that each adjustment can bring significant performance improvement. This real-time feedback and optimization capability enables the system to maintain stable and efficient operation in dynamically changing database environments. In addition, this implementation method can identify and optimize low-frequency queries and abnormal query patterns that are easily overlooked in traditional methods. By optimizing these unconventional query patterns, the system can significantly improve overall query performance and avoid performance bottlenecks caused by ignoring certain query patterns. The adaptive index adjustment mechanism can also dynamically adjust the priority and configuration of indexes according to changes in query patterns, thereby achieving optimal performance allocation under limited resources.

[0119] Index structure adjustment based on query pattern information relies on a pre-built index optimization model. The model construction process is the core link to ensure the effectiveness of index optimization. To train an efficient and accurate index optimization model, this paper adopts a deep reinforcement learning method, combined with a priority experience playback mechanism, based on historical data. Through continuous learning and optimization, the model's predictive ability and generalization performance are improved. Specifically:

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

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

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

[0123] The index strategy corresponding to the historical index feature data is used as the output training data;

[0124] The input training data and the output training data are stored as experience values in the experience playback 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, and output the corresponding index prediction data;

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

[0127] According to the loss function value, the gradient descent method is used to update the model parameters to obtain the index optimization model;

[0128] In this implementation, the adaptive learning ability of the deep Q network can dynamically adjust the indexing strategy, significantly improving the system's adaptability and robustness in scenarios with changing data distribution and fluctuating query loads, breaking through the limitations of traditional static optimization methods. Secondly, by leveraging the powerful fitting ability of deep neural networks, the model can efficiently capture the nonlinear relationship between complex query patterns and data features, thereby generating better indexing strategies when processing high-dimensional, unstructured data, significantly improving query performance. In addition, the introduction of the experience replay mechanism enables the model to efficiently learn from historical data, avoiding the traditional method's excessive reliance on real-time data. At the same time, through batch learning and gradient descent optimization, the training efficiency and model stability are significantly improved. This index optimization method that combines deep learning and reinforcement learning not only provides new ideas for database performance optimization, but also demonstrates advantages in adaptive learning, complex pattern capture, and efficient training.

[0129] In the above implementation, 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 database performance remains a key issue. To achieve this goal, the present invention can also introduce an adaptive index adjustment mechanism. This mechanism analyzes and prioritizes index adjustment strategies, combines them with performance evaluation functions, and dynamically outputs the optimal index structure, thereby ensuring that the database is always in the best operating state. Specifically:

[0130] In one implementation, 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 may include:

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

[0132] According to the index operation priority, the index adjustment amount is obtained;

[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, by analyzing the index adjustment strategy, the priority of different index operations can be accurately identified, thereby ensuring that when resources are limited, the adjustments that will most significantly improve performance are executed first. This priority sorting capability enables efficient resource allocation in complex database environments and avoids resource waste caused by blind adjustments. Secondly, the index adjustment amount is calculated according to the index operation priority, and an optimization strategy that is highly matched with the current workload can be dynamically generated. This dynamic adjustment capability not only improves the accuracy of the optimization, but also significantly reduces the need for manual intervention. The index adjustment amount is evaluated through a performance evaluation function, and the adjustment effect can be fed back in real time. The index structure is continuously optimized based on the feedback results. This real-time feedback and optimization mechanism enables rapid response when facing rapidly changing workloads, ensuring that the database is always in the best performance state. In addition, the adaptive index adjustment mechanism can identify and optimize low-frequency queries and abnormal query patterns that are easily overlooked in traditional methods. By optimizing these unconventional query patterns, it can significantly improve overall query performance and avoid performance bottlenecks caused by ignoring certain query patterns. This mechanism can dynamically adjust the index priority and configuration according to changes in query patterns, thereby achieving optimal performance allocation when resources are limited.

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

[0136]

[0137] 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 the index structure I. In this example, the performance evaluation function can comprehensively evaluate the performance of the index structure by comprehensively considering the query response time and resource utilization. This comprehensive evaluation method not only improves the accuracy of the optimization, but also ensures that the optimized index structure can effectively control resource consumption while improving query performance, thereby achieving the best balance between performance and resource utilization. Secondly, the performance evaluation function introduces the query frequency C k As a weighting factor, it can focus more on performance optimization of high-frequency queries, significantly improving overall query performance. This frequency-based optimization strategy maintains efficient operation even in the face of highly concurrent queries. Furthermore, the performance evaluation function divides query patterns into different categories through cluster analysis and evaluates the performance of each cluster under the index structure. This evaluation method not only improves optimization accuracy but also identifies and optimizes low-frequency queries and abnormal query patterns that are easily overlooked in traditional methods. Based on changes in query patterns, it dynamically adjusts index priority and configuration, achieving optimal performance allocation within limited resources.

[0138] In summary, in the current database management system, traditional index selection and optimization technologies can no longer meet the challenges of rapid growth in data volume, diversification of business needs and dynamic changes in workload. In order to solve 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 sensing workload changes in real time 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. The trend detection stage first uses a pre-trained Transformer model to extract features from historical query data, and then clusters the feature matrix to analyze historical query patterns (high-frequency query patterns, low-frequency query patterns and abnormal query patterns); the online index selection stage adopts a deep reinforcement learning algorithm to achieve dynamic adjustment of the optimal index configuration during database operation. Through clustering analysis in the trend detection stage, different query patterns can be effectively identified, so that the system can accurately capture changes in user query needs. This process ensures that the database can adapt to new query patterns in a timely manner and avoid performance bottlenecks caused by unreasonable index structures. During the online index selection phase, the deep reinforcement learning algorithm can automatically adjust the index configuration by sensing changes in workload in real time. This dynamic optimization capability not only improves query response speed but also significantly reduces database resource consumption. Compared with traditional static indexing strategies, this method can achieve higher scalability and flexibility in big data environments.

[0139] Example 2:

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

[0141] Step 1: Collect financial-related query records from the database, including:

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

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

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

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

[0146] Step 3: Calculate the distance matrix based on the results of Step 2, use K-means to build a clustering map based on 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 mode: Focuses on daily flow queries (e.g., accounting for 70%), and requires prioritizing index optimization to reduce full table scans.

[0149] Low-frequency mode: Most of these queries are cross-year audit queries (e.g., accounting for 25%), and a balance must be struck between index storage overhead and query performance.

[0150] Abnormal mode: Sudden audit requests (e.g., accounting for 5%) require dynamic creation of temporary indexes and rapid release;

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

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

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

[0154] Clustering model update: adjust cluster centers based on the latest query distribution to avoid pattern drift;

[0155] This specific embodiment can illustrate the database index optimization method based on deep reinforcement learning provided by the present invention, which shows significant technical advantages and effects in the financial query scenario of the smart operation center of the smart shared financial platform. Figure 2 As shown, the system collects relevant query record data, divides it into several subsets, and uses the Transformer model to construct a structured representation of the data. The information is then verified and quantified, and the K-means clustering algorithm is used to classify the data into different categories for further processing. During this process, the optimal subset is selected for detailed analysis to ensure accurate understanding of the target data. Simultaneously, the system obtains the current query status in real time. Ultimately, a robust learning model is built and algorithm optimization is used to improve performance, ensuring that the constructed model efficiently and accurately reflects user needs. Throughout this process, relevant Transformer and K-means parameters are updated to continuously improve model performance. By accurately identifying and optimizing high-frequency, low-frequency, and abnormal queries, database query performance is significantly improved. The Transformer model and K-means clustering algorithm detect workload changes in real time and dynamically adjust the index structure to ensure efficient operation despite new business scenarios or shifting query patterns. Furthermore, the deep reinforcement learning model uses a prioritized experience replay mechanism to rapidly generate optimal indexing strategies, and combines this with performance evaluation functions to achieve precise optimization. This automated management of the entire lifecycle not only reduces manual intervention costs but also significantly improves the system's flexibility and adaptability. Overall, this intelligent index optimization method achieves efficient, stable, and cost-effective performance improvements in complex and changing database environments.

[0156] Example 3:

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

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

[0159] The feature extraction module is 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;

[0160] The clustering analysis module is used to perform cluster analysis on the distance feature information using a clustering algorithm to obtain query pattern information of the database to be optimized;

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

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

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

[0164] The input feature configuration submodule is used to use the historical query record data of the database to be optimized as the input of training data;

[0165] Output feature configuration submodule, used to output the distance feature information of historical query record data as training data;

[0166] The model training submodule is used to train the Transformer model based on the input and output of the training data.

[0167] In one implementation, the cluster analysis module may include:

[0168] The distance matrix construction submodule is used to construct a distance matrix between distance feature information based on the distance feature information;

[0169] The graph generation submodule is used to perform cluster analysis on the distance matrix between the distance feature information using the K-means clustering algorithm to generate the corresponding cluster graph;

[0170] The feature statistics submodule is used to perform feature statistics based on the clustering graph to obtain query pattern information of the database to be optimized;

[0171] The query mode information includes: low-frequency query mode information, high-frequency query mode information and abnormal query mode information.

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

[0173] Initialization submodule, used to initialize the network structure, model parameters and experience replay buffer;

[0174] Input data configuration submodule, used to use historical index feature data as input training data;

[0175] The output data configuration submodule is used to use the index strategy corresponding to the historical index feature data as the output training data;

[0176] The experience value storage submodule is used to store the input training data and the output training data as experience values in the experience playback buffer;

[0177] The data training submodule is used to select a preset number of experience values from the experience playback buffer as index data to input into the deep Q network and output the corresponding index prediction data;

[0178] A loss function calculation submodule is used to calculate the corresponding loss function value based on the target structure data of the index prediction data and the index data stored in the experience playback buffer;

[0179] The model parameter update submodule is used to update the model parameters using the gradient descent method according to the loss function value to obtain the index optimization model.

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

[0181] The index feature analysis submodule is used to extract features based on query pattern information to obtain index feature data;

[0182] The index optimization submodule is used to generate index adjustment strategies based on index feature data and using a pre-built index optimization model;

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

[0184] In this implementation, the index adjustment submodule may include:

[0185] A policy parsing unit, used to parse the index adjustment policy and obtain the index operation priority;

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

[0187] The performance evaluation unit 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] For example, the expression corresponding to the above performance evaluation function can be as follows:

[0189]

[0190] Where f(I) represents the overall performance score of the index structure I; α represents the first weight coefficient; β represents the second weight coefficient; C krepresents 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.

[0191] Example 4:

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

[0193] The processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSP), application-specific integrated circuits (ASIC), field-programmable gate arrays (FPGA) 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, which is suitable for implementing one or more instructions, specifically 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] Example 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), which is a memory device in an electronic device for storing programs and data. It can be understood that the storage medium here can include both built-in storage media in the electronic device and, of course, extended storage media supported by the electronic device. The storage medium provides a storage space that stores the operating system of the terminal. In addition, one or more instructions suitable for being loaded and executed by the processor are also stored in the storage space. 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. The processor loads and executes one or more instructions stored in the storage medium, which can implement the steps of a database index optimization method based on deep reinforcement learning in the above embodiment.

[0196] It will be understood by those skilled in the art that embodiments of the present invention may be provided as methods, systems, or computer program products. Thus, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware. Furthermore, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

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

[0198] These computer program instructions may 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, so that the instructions stored in the computer readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 a process or multiple processes and / or boxes Figure 1 The function specified in one or more boxes.

[0199] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operational steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing the instructions executed on the computer or other programmable device for implementing the process. Figure 1 a process or multiple processes and / or boxes Figure 1 A step that specifies a function in one or more boxes.

[0200] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and are not intended to limit its scope of protection. Although the present invention has been described in detail with reference to the above embodiments, ordinary technicians in the field should understand that after reading the present invention, those skilled in the art may still make various changes, modifications or equivalent substitutions to the specific implementation methods of the application, but these changes, modifications or equivalent substitutions are all within the scope of protection of the pending claims.

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; 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 cluster map; Performing feature statistics based on the clustering graph 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; 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 replay 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 replay buffer; According to the loss function value, the model parameters are updated using the gradient descent method to obtain an index optimization model; The query mode information includes: low-frequency query mode information, high-frequency query mode information and abnormal query mode information.

2. The method according to claim 1, wherein The adjusting the index structure of the database to be optimized by using a pre-built index optimization model based on the query pattern information to obtain the optimal index structure of the database to be optimized includes: Perform feature extraction based on 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, wherein Outputting the optimal index structure of the database to be optimized through an adaptive index adjustment mechanism according to the index adjustment strategy includes: Analyze 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.

4. The method according to claim 3, wherein The expression corresponding to the performance evaluation function is as follows: ； in, Represents the index structure Overall performance rating; represents the first weight coefficient; represents the second weight coefficient; Indicates the The query frequency of each cluster; ; Indicates the total number of clusters; Indicates the Clusters in the index structure Query response time under Indicates the Clusters in the index structure The resource utilization rate is as follows.

5. The method according to claim 1, wherein The Transformer model training process includes the following: Using historical query record data of the database to be optimized as input for training data; Using the distance feature information of the historical query record data as output of training data; The Transformer model is trained based on the input and output of the training data.

6. A database index optimization system based on deep reinforcement learning, characterized in that: include: The data acquisition module is used to obtain the query record data of the database to be optimized; A feature extraction module is 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, configured to perform cluster analysis on the distance feature information using a clustering algorithm based on the distance feature information to obtain query pattern information of the database to be optimized; A structure adjustment module is used to adjust the index structure of the database to be optimized based on the query pattern information using a pre-built index optimization model to obtain an optimal index structure of the database to be optimized; The cluster analysis module includes: A distance matrix construction submodule is 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, configured to perform feature statistics based on the clustering graph to obtain query pattern information of the database to be optimized; The system further comprises: an optimization model building module, comprising: Initialization submodule, used to initialize the network structure, model parameters and experience replay buffer; Input data configuration submodule, used to use historical index feature data as input training data; An output data configuration submodule, configured to use the index strategy corresponding to the historical index feature data as output training data; An experience value storage submodule, configured to store the input training data and the output training data as experience values in the experience playback buffer; A data training submodule is used to select a preset number of experience values from the experience playback buffer as index data to input into the deep Q network, and output corresponding index prediction data; a loss function calculation submodule, configured to calculate a corresponding loss function value based on the index prediction data and the target structure data of the index data stored in the experience replay buffer; A model parameter updating submodule is used to update the model parameters using a gradient descent method according to the loss function value to obtain an index optimization model; The query mode information includes: low-frequency query mode information, high-frequency query mode information and abnormal query mode information.

7. The system according to claim 6, wherein: The structure adjustment module includes: An index feature analysis submodule, configured to extract features based on the query pattern information to obtain index feature data; An index optimization submodule, configured to generate an index adjustment strategy based on the index feature data and 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