Adaptive database index optimization and abnormal query detection method and system based on deep reinforcement learning

Abstract

The present invention relates to the technical field of database management, and discloses a method and system for adaptive database index optimization and abnormal query detection based on deep reinforcement learning. The method collects historical query records of a database, extracts query features and constructs an N-dimensional query feature vector of the query, identifies abnormal queries according to the query feature vector and generates an abnormal query set, then extracts abnormal query features from the abnormal query set and generates an abnormal query feature set; further, the system generates a current index feature set, compares it with the abnormal query feature set, identifies abnormal query features not covered by the current index, and generates an index gap feature set; generates an optimal index optimization scheme according to the index gap feature set and a pre-constructed deep reinforcement learning model; the present invention can effectively improve the efficiency and accuracy of database index optimization, and timely detect and process abnormal queries to maintain the performance and stability of the database.

Description

Technical Field

[0001] The present invention relates to the field of database management technology, and more specifically, to a method and system for adaptive database index optimization and abnormal query detection based on deep reinforcement learning. Background Art

[0002] With the rapid development of information technology, the scale and complexity of data continue to grow. Efficient management and utilization of massive data has become a difficult problem that needs to be solved urgently in various industries. In the field of database management, predecessors have made many useful explorations.

[0003] A Chinese patent with publication number CN117876016A discloses a distributed market data collection and management system. The system uses a variety of data processing technologies to optimize the processing flow of market data. The system improves the efficiency and accuracy of data retrieval and market dynamic monitoring through dynamic index adjustment algorithms and real-time data monitoring frameworks. The anomaly detection and risk assessment mechanisms enhance the ability of enterprises to cope with market risks. The optimized query strategies and deep learning models improve the scientific nature of risk prevention decisions.

[0004] In addition, a Chinese patent with publication number CN117992766A discloses an artificial intelligence-based model recognition evaluation management system and method. During the data recognition process, the system not only considers the current input data, but also integrates the recognition results of the remaining data to achieve a comprehensive evaluation of the recognition scheme. The invention provides intelligent assistance and reference for administrators to screen recognition data.

[0005] Although the existing technology has made some progress, there is still room for improvement in adaptive optimization of database indexes and real-time detection of abnormal queries. Traditional index optimization methods are difficult to dynamically adapt to changes in query load and are easily limited by human experience. Summary of the invention

[0006] In order to overcome the above-mentioned defects of the prior art, the present invention provides an adaptive database index optimization and abnormal query detection method and system based on deep reinforcement learning.

[0007] To achieve the above object, the present invention provides the following technical solutions:

[0008] Adaptive database index optimization and abnormal query detection method based on deep reinforcement learning, including:

[0009] Collect historical query records of the database, extract query features of the queries in the historical query records, and construct an N-dimensional query feature vector of the query, where N is the number of extracted query features; identify abnormal queries based on the query feature vector of the query, and generate an abnormal query set; extract abnormal query features from the abnormal query set, and generate an abnormal query feature set;

[0010] Generate the current index feature set, compare and analyze the abnormal query feature set with the current index feature set, identify the abnormal query features not covered by the current index, and generate the index gap feature set; generate the optimal index optimization plan based on the index gap feature set and the pre-built deep reinforcement learning model.

[0011] Furthermore, the method for generating an abnormal query set includes:

[0012] Let the query feature vector of the i-th query be x i , the query feature vector of the jth query is x j , calculate x i The local density ρ(x i ) and x i With x j The distance d(x i ,x j ), according to ρ(x i ) and d(x i ,x j ) Calculate the abnormality score;

[0013] Sort the anomaly scores from large to small, and take the first k queries as abnormal query candidates, where k is the anomaly threshold, which is determined by the average of the maximum anomaly score and the anomaly score;

[0014] Aggregate the abnormal query candidates into an abnormal query set.

[0015] Further, the abnormal query features include data tables, columns and predicate conditions;

[0016] The extracting abnormal query features from the abnormal query set to generate the abnormal query feature set includes:

[0017] Count the frequency distribution of abnormal query features and identify the tables, columns, and predicate conditions that appear frequently in abnormal queries and are greater than the abnormal frequency threshold k1;

[0018] The tables, columns and predicate conditions that appear frequently and are greater than the abnormal frequency threshold k1 are summarized into an abnormal query feature set.

[0019] Furthermore, the current index feature set is obtained by loading the current index configuration of the database, parsing the index configuration information, and extracting key features of the index.

[0020] Furthermore, the method for generating the index gap feature set is to count the frequency of uncovered abnormal query features, mark the uncovered abnormal query features whose frequency exceeds the gap frequency threshold k2 as index gap features, and generate the index gap feature set.

[0021] Furthermore, the method for generating an index optimization solution includes:

[0022] Evaluate the cost-benefit of introducing a new index for index gap features, take the index gap feature set as the state space, define the index operation as the action space, take the cost-benefit of the index as the reward function, and build a deep reinforcement learning model;

[0023] Input the index gap feature set into the deep reinforcement learning model to obtain the optimal index optimization strategy;

[0024] Decode the optimal index optimization strategy into an executable optimal index optimization plan.

[0025] An adaptive database index optimization and abnormal query detection system based on deep reinforcement learning, which is used to implement the above-mentioned adaptive database index optimization and abnormal query detection method based on deep reinforcement learning, and the system includes:

[0026] Data collection module: used to collect historical query records of the database;

[0027] Feature extraction module: used to extract query features of queries in historical query records and construct query feature vectors of queries;

[0028] Abnormal query identification module: used to identify abnormal queries according to the query feature vector of the query and generate an abnormal query set; extract abnormal query features from the abnormal query set and generate an abnormal query feature set;

[0029] Index gap identification module: used to generate the current index feature set, compare and analyze the abnormal query feature set with the current index feature set, identify the abnormal query features not covered by the current index, and generate the index gap feature set;

[0030] Index optimization module: used to generate the optimal index optimization solution based on the index gap feature set and the pre-built deep reinforcement learning model.

[0031] An electronic device comprises a memory, a processor and a computer program stored in the memory and executable on the processor. When the central processor executes the computer program, the steps of the above-mentioned method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning are implemented.

[0032] A computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed, implements the steps in the above-mentioned method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning.

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

[0034] By using deep reinforcement learning methods, the present invention can intelligently analyze and optimize the index structure of the database; this method can not only significantly reduce the query response time, but also maintain efficient query performance under different query loads; compared with traditional index optimization methods, deep reinforcement learning can better adapt to dynamically changing query patterns, thereby improving overall query efficiency.

[0035] The method of the present invention can automatically adjust the optimization strategy according to the historical query records of the database to adapt to the changing query environment; through continuous learning and optimization, the system can dynamically adjust the index structure to cope with new query patterns and data distribution changes; this adaptability ensures that the database can maintain optimal performance under any circumstances.

[0036] The present invention not only optimizes indexes, but also can detect abnormal query behaviors in the database; by analyzing query characteristics, the system can identify abnormal query patterns, such as frequent full table scans, time-consuming queries, etc.; this detection function can help database administrators discover and handle potential problems in a timely manner, thereby improving the stability and security of the database.

[0037] Traditional index optimization usually requires database administrators to make manual adjustments, which consumes a lot of time and effort. The present invention greatly reduces the need for manual intervention through an automated deep reinforcement learning method. Database administrators only need to perform a small amount of monitoring and adjustment, which greatly reduces maintenance costs.

[0038] The method of the present invention can be applied to different types of database systems, whether relational databases or non-relational databases, and can achieve index optimization and abnormal query detection through deep reinforcement learning technology; this wide applicability makes the present invention have high practical value and market prospects.

[0039] By collecting database query records in real time, the present invention can respond quickly when data changes, perform index optimization and abnormal query detection; real-time ensures the efficiency and security of the database system, can respond to emergencies in a timely manner, and reduce performance problems caused by abnormal queries.

[0040] Through continuous optimization and anomaly detection, the present invention can effectively prevent database performance degradation and failures; the system can automatically identify and resolve potential performance bottlenecks, ensuring that the database can still run stably under high load conditions.

[0041] In summary, the present invention uses deep reinforcement learning technology to realize the automation and intelligence of database index optimization and abnormal query detection, which not only improves query efficiency and system stability, but also greatly reduces operation and maintenance costs. It has significant technical advantages and practical application value. BRIEF DESCRIPTION OF THE DRAWINGS

[0042] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0043] Figure 1 It is a principle flow chart of the adaptive database index optimization and abnormal query detection method based on deep reinforcement learning in the present invention;

[0044] Figure 2 A flow chart of a method for generating an abnormal query set in the method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning of the present invention;

[0045] Figure 3 A flow chart of a method for generating an abnormal query feature set in the adaptive database index optimization and abnormal query detection method based on deep reinforcement learning of the present invention;

[0046] Figure 4 It is a functional module diagram of the adaptive database index optimization and abnormal query detection system based on deep reinforcement learning in the present invention;

[0047] Figure 5 A schematic diagram of the structure of an electronic device provided in one embodiment of the present application;

[0048] Figure 6 A schematic diagram of the computer-readable storage medium structure provided for one embodiment of the present application. DETAILED DESCRIPTION

[0049] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0050] Example 1

[0051] See also Figure 1 As shown, this embodiment provides an adaptive database index optimization and abnormal query detection method based on deep reinforcement learning, including:

[0052] Step S1000, collecting historical query records of a database, extracting query features of queries in the historical query records, and constructing a query feature vector of the query; identifying abnormal queries according to the query feature vector of the query, and generating an abnormal query set; extracting abnormal query features from the abnormal query set, and generating an abnormal query feature set;

[0053] Furthermore, step S1000 includes:

[0054] Step S1100, collecting historical query records of the database, extracting query features of the query in the historical query records, vectorizing the extracted query features, and constructing an N-dimensional query feature vector of the query, where N is the number of extracted query features;

[0055] Specifically, by monitoring the SQL execution log or query cache of the database, we can obtain SQL query statements within a certain time range (such as the past day, the past week, the past month, etc.) to form historical query records. The historical query records reflect the query mode and load characteristics of the database in the real business environment; parse the historical query records one by one, and use regular expression matching, syntax tree analysis and other technologies to extract the key features of the query statements, such as the query type (SELECT, INSERT, UPDATE, etc.), the query frequency (the number of times it appears in a period of time), the average query execution time, the data tables and column names involved in the query, etc. These features characterize the query pattern from different angles. The extracted query features are vectorized and converted into fixed-length real vectors. For example, one-hot encoding can be used to encode categorical features (such as query type), and numerical features (such as query frequency and execution time) can be normalized. Finally, a query record is represented as an N-dimensional real vector, where N is the number of extracted features. Vectorized representation facilitates subsequent machine learning algorithm processing.

[0056] Step S1100 obtains a structured representation of database queries, namely, a query feature vector, through data collection and feature engineering. The query feature vector summarizes the key features of the query in a compact digital form and is the basis for abnormal query detection and index optimization. On the one hand, the comprehensive historical query records cover the various query modes of the database in actual operation, providing raw materials for mining query rules. On the other hand, the query feature vector eliminates redundant information in the query statement, highlights the key factors that affect the query performance, and facilitates the identification and clustering of abnormal query patterns. At the same time, the digitization of the feature vector also makes it possible to apply anomaly detection algorithms based on statistics and machine learning.

[0057] Step S1200, using an adaptive clustering algorithm, identifying potential abnormal queries according to the query feature vector of the query, and generating an abnormal query set;

[0058] Furthermore, if Figure 2 As shown, step S1200 includes:

[0059] Step S1210, let the query feature vector of the i-th query be x i , the query feature vector of the jth query is x j , calculate x i The local density ρ(x i ) and x i With x j The distance d(x i ,x j ), according to ρ(x i ) and d(x i ,x j ) Calculate the abnormality score;

[0060] Step S1220, sort the abnormality scores from large to small, and take the first k queries as abnormal query candidates, where k is the abnormality threshold, and k is determined by the average of the maximum abnormality score and the abnormality score;

[0061] Step S1230, aggregate the abnormal query candidates into an abnormal query set, and calculate the abnormality index of each query:

[0062] The calculation formula of the abnormality score is:

[0063]

[0064] Among them, A(x i ) is the abnormality score, x i represents the query feature vector of the i-th query, x j represents the query feature vector of the jth query, ρ(x i ) represents x i The local density, d(x i ,x j ) represents x i With x j The distance between i ) represents x i The distance between the nearest neighbor with a greater density than it, d(x i ,x j ) can use Euclidean distance, Manhattan distance, etc., n represents the total number of queries;

[0065] The x i The local density ρ(x i ) is calculated as:

[0066]

[0067] Where exp(·) is the exponential function, dc is the cutoff distance, which is used to control the decay rate of local density; the anomaly score A(x i ) takes into account the local density of the query and the distance to the high-density area. The larger the score, the higher the possibility of anomaly.

[0068] The calculation formula of the abnormal threshold k is:

[0069]

[0070] Among them, A max is the maximum abnormality score, is the average value of the anomaly score; the anomaly threshold can automatically adapt to the overall distribution of the anomaly score. When the anomaly of a few queries is much higher than the average level, the threshold will become smaller and the identified abnormal queries will be more concentrated.

[0071] The calculation formula of the abnormality index is:

[0072]

[0073] Among them, S(x i ) is the abnormality index, C is the abnormal query set, and the abnormality index S(x i ) reflects x i The relative abnormality degree among all abnormal query candidates provides a reference for subsequent index optimization. The higher the abnormality index of the query, the more urgent the optimization need.

[0074] The adaptive clustering algorithm can adjust itself according to the characteristics of query distribution, and can detect isolated anomalies as well as loose anomaly areas. The anomaly score and anomaly threshold can objectively quantify the degree of anomaly of the query, providing a basis for the formulation of optimization schemes. The introduction of local density factors makes the algorithm more robust to boundary points and noise points. As the query scale grows, the threshold of the algorithm will change dynamically. When the overall anomaly shows a significant increase, the anomaly threshold is reduced, and the detection becomes more stringent, which helps to find prominent abnormal queries as early as possible. When new abnormal patterns become frequent, their anomaly will gradually decrease until they no longer enter the abnormal query set, which is consistent with our conventional understanding of anomalies. In addition, this algorithm reduces the reliance on feature selection, does not need to spend manpower to design abnormal features, and can better adapt to changing query patterns. The abnormal query set points the direction for index optimization, and the optimization will focus on these queries that deviate from the normal pattern.

[0075] In summary, step S1200 uses adaptive clustering to achieve automatic identification and quantitative evaluation of abnormal queries, laying a data foundation for index optimization. The focus of optimization is determined autonomously by the system, which not only reduces labor costs but also improves the targetedness of optimization. Subsequent optimization can be carried out in a targeted manner, continuously improving the system's ability to handle abnormal queries.

[0076] Step S1300, extracting abnormal query features from the abnormal query set to generate an abnormal query feature set;

[0077] Furthermore, if Figure 3 As shown, step S1300 includes:

[0078] Step S1310, extracting abnormal query features from the abnormal query set, wherein the abnormal query features include data tables, columns, and predicate conditions;

[0079] Step S1320, counting the frequency distribution of abnormal query features, identifying tables, columns, and predicate conditions that appear frequently in abnormal queries and are greater than an abnormal frequency threshold k1;

[0080] Step S1330, summarizing the tables, columns and predicate conditions that appear frequently and are greater than the abnormal frequency threshold k1 into an abnormal query feature set, each abnormal query feature including occurrence frequency information;

[0081] Specifically, step S1310 performs syntax analysis on each SQL query statement in the abnormal query set. Compared with the feature extraction in step S1100, the syntax analysis here is more fine-grained, and what is extracted is the specific components of the query statement, such as the name of the queried data table, the name of the filtered column, the predicate conditions used (such as equality conditions, range conditions), etc. These elements directly determine the performance of the query; the abnormal query features can reveal the nature and causes of abnormal queries, such as the skewness of data distribution, the poor selectivity of query conditions, etc. By extracting these key features, more accurate guidance can be provided for index optimization; step S1320 performs frequency statistics and sorting on the extracted elements to identify the data tables, columns and predicate conditions that appear repeatedly in abnormal queries. These high-frequency elements are common features of abnormal queries. Optimizing indexes for them can benefit more abnormal queries. The abnormal frequency threshold k1 is a numerical value used to determine whether the frequency of a table, column or predicate condition in a query exceeds the normal range, so that it is marked as abnormal. It can be obtained through statistical analysis, expert experience or machine learning methods. For example, through historical query logs, the frequency of occurrence of each table, column and predicate condition is counted, and a reasonable threshold is set according to the statistical results, such as two standard deviations exceeding the normal frequency. Combined with the experience of the database administrator, an initial value is set, and the threshold is continuously adjusted and optimized according to the actual operation and feedback. Or through a deep reinforcement learning model, k1 is automatically identified and adjusted. When training the model, a large amount of query log data is input to allow the model to adaptively learn which frequencies are abnormal. By identifying and optimizing abnormal queries, the occupancy of database resources by queries is reduced and the overall performance is improved. Step S1330 summarizes the high-frequency elements into an abnormal query feature set, and each feature also contains its frequency information. The higher the frequency, the greater the optimization value of the feature.

[0082] For example, assume that there are the following five database query logs:

[0083] Query 1: SELECT name,age FROM users WHERE age>30;

[0084] Query 2: SELECT id,email FROM users WHERE email LIKE'%@gmail.com';

[0085] Query 3: SELECT name,age FROM users WHERE age>40;

[0086] Query 4: SELECT id,name FROM users WHERE name='Alice';

[0087] Query 5: SELECT name,age FROM users WHERE age>50;

[0088] Extract abnormal query features:

[0089] Parse the above query statements to extract the key components of each query statement, including the data table name, column name, and predicate condition involved in the query:

[0090] Query 1: The table used is users, the columns are name and age, and the predicate condition is age>30;

[0091] Query 2: The table used is users, the columns are id and email, and the predicate condition is email LIKE '%@gmail.com';

[0092] Query 3: The table used is users, the columns are name and age, and the predicate condition is age>40;

[0093] Query 4: The table used is users, the columns are id and name, and the predicate condition is name = 'Alice';

[0094] Query 5: The table used is users, the columns are name and age, and the predicate condition is age>50;

[0095] Statistical frequency distribution of abnormal query features:

[0096] Perform frequency statistics on the extracted features to identify tables, columns, and predicate conditions that frequently appear in abnormal queries:

[0097] The table users appears 5 times;

[0098] The column name appears 4 times;

[0099] The column age appears 3 times;

[0100] The column id appears twice;

[0101] The column email appears 1 time;

[0102] The predicate condition age>30 appeared once;

[0103] The predicate condition email LIKE '%@gmail.com' occurred 1 times;

[0104] The predicate condition age>40 appeared once;

[0105] The predicate condition name = 'Alice' appears once;

[0106] The predicate condition age>50 appeared once;

[0107] Assuming that the abnormal frequency threshold k1 is set to 2, the features above the threshold are:

[0108] Table users (occurs 5 times);

[0109] Column name (appears 4 times);

[0110] Column age (occurs 3 times);

[0111] Aggregate into abnormal query feature set: Aggregate abnormal query features above the threshold into an abnormal query feature set, and attach the frequency of occurrence information of each feature:

[0112] Table users: appears 5 times;

[0113] Column name: appears 4 times;

[0114] Column age: appears 3 times;

[0115] These features will become the focus of index optimization because they frequently appear in abnormal queries. Optimizing these features will help improve the query performance of the database.

[0116] Step S1300 further explores the inherent characteristics of abnormal queries based on step S1200; if step S1200 discovers "which queries are abnormal", then step S1300 analyzes "why the abnormal queries are abnormal"; through grammatical analysis, the internal structure of abnormal queries is dissected, and the "lesions" of slow queries are exposed, which may be that some data tables lack indexes, some columns frequently appear in predicate conditions, resulting in a large amount of data scanning, etc.; frequency statistics of these "lesions" can quantify their impact on query performance; "lesions" with high frequency are the primary target of index optimization, and by creating appropriate indexes on these data tables and columns, the performance of abnormal queries can be improved to the greatest extent; the abnormal query feature set concretizes the optimization direction and lays the foundation for targeted indexing.

[0117] Step S1000 mines the query patterns and performance bottlenecks of the database from the hidden historical query records through a series of processes such as data collection, feature extraction, adaptive clustering, and syntax parsing, forming a structured abnormal query feature set; this set is like a "portrait" of abnormal queries, depicting what typical abnormal queries look like and where they are slow, providing clear optimization objects and optimization directions for subsequent index optimization; this data-driven abnormal query analysis method can adaptively identify performance bottlenecks in real business environments, and continue to evolve as business query patterns change, always focusing on queries that need to be optimized most. By optimizing the index according to this "portrait", the index input-output ratio can be maximized, and the most significant performance improvement can be achieved with the least index resources; it can be seen that high-quality abnormal query identification is a prerequisite for efficient index optimization.

[0118] Step S2000, generating a current index feature set, comparing and analyzing the abnormal query feature set with the current index feature set, and generating an index gap feature set; generating an index optimization solution based on the index gap feature set and a pre-built deep reinforcement learning model;

[0119] Furthermore, step S2000 includes:

[0120] Step S2100, loading the current index configuration of the database, parsing the index configuration information, extracting the key features of the index, and generating the current index feature set;

[0121] Specifically, the created index information in the database is read through the system view or metadata table, such as MySQL's information architecture statistics table and Oracle's user index view. These metadata record the definition of each index in detail, such as the table to which the index belongs, the index type, the index column, the index field length, whether it is unique, etc. Further semantic analysis is performed on the metadata to extract index features that have a greater impact on query performance. Taking MySQL as an example, common index types include B+ tree indexes, hash indexes, full-text indexes, etc. Different index types have different applicable scenarios for query optimization. The selection of index columns is highly related to the query conditions. Ideally, the predicate conditions of the query can be accurately matched with the index columns. The index order affects the efficiency of range queries. The index order that matches the sorting column can avoid additional sorting operations. The index granularity involves targeted optimization. Table-level indexes are suitable for queries on the entire table, while partitioned indexes can use partition pruning to quickly locate data subsets. These heterogeneous index features are abstracted into a structured set of index features to facilitate subsequent feature matching and optimization decisions.

[0122] The current index feature set is like a "physical examination report" of the database index, reflecting the existing indexing capabilities and optimization level of the database. By analyzing the current index configuration, we can find possible index defects, such as incomplete index coverage, poor index selectivity, index fragmentation, etc., which provides direction and goals for index optimization. At the same time, this set is also the starting point of the optimization process, and new index schemes will be superimposed and evolved on this basis. It can be seen that a comprehensive understanding of the current index configuration of the database is a prerequisite for optimization, which requires systematic extraction and characterization of the key features of existing indexes.

[0123] For example, assume that there are two indexes in the order table of an e-commerce database: the first index is a B+ tree index on the order date column, which is arranged in ascending order by date; the second index is a joint index of the customer ID and order status columns, which are arranged in ascending order by customer ID and order status. After the processing of step S2100, these two indexes can be represented in a structured form in the current index feature set, recording the core elements such as the name of the index, the table where it is located, the index type, the index column, the index order, and the index granularity. The order date index and the customer ID index can speed up the query of the date dimension and the customer dimension of the order, but lack index support for other commonly used query fields of the order such as the order amount and the payment method; in addition, the selectivity of the order status column in the joint index is poor (such as most orders are in the completed state), which will affect the efficiency of the index. These problems will be further improved in subsequent steps.

[0124] Step S2200, comparing and analyzing the abnormal query feature set with the current index feature set to identify abnormal query features that are not covered by the current index;

[0125] Step S2300, counting the frequencies of uncovered abnormal query features, marking uncovered abnormal query features whose frequencies exceed the gap frequency threshold k2 as index gap features, and generating an index gap feature set;

[0126] Specifically, through feature matching analysis, find out the features in the abnormal query feature set that are not covered by the current index feature set. These features represent query patterns that are difficult for the current index to support efficiently. Feature matching can be based on similarity calculations, such as the Jaccard coefficient of the set, the cosine similarity of the vector, etc.; data structures such as inverted indexes can also be used to accelerate the matching process; the frequency of uncovered features is counted and summarized, and the frequency reflects the degree of influence of the feature on the query performance. The higher the frequency, the greater the optimization benefit; the uncovered abnormal query features whose frequency exceeds the gap frequency threshold k2 are marked as index gap features; if the frequency of the uncovered abnormal query features does not exceed the gap frequency threshold k2, continue to count and analyze other uncovered abnormal query features to ensure that all possible abnormal features are fully covered.

[0127] The gap frequency threshold k2 is a predefined value used to determine which uncovered abnormal query features should be marked as index gap features. In this embodiment, abnormal query features with a frequency exceeding k2 will be considered significant. The gap frequency threshold k2 can be set to a reasonable value based on historical data and experience, or a threshold that can significantly distinguish between normal and abnormal features can be determined by statistical methods to analyze the abnormal query feature frequency data collected over a period of time. According to the actual situation of the system operation, the value of k2 is dynamically adjusted to ensure the flexibility and adaptability of the system. By setting a reasonable gap frequency threshold k2, by screening out abnormal query features with higher frequencies, potential index gaps and system vulnerabilities can be more accurately identified and processed; resources are concentrated on processing abnormal features with higher frequencies, improving the overall efficiency and response speed of the system. The summarized index gap feature set reflects the shortcomings and deficiencies of the current index configuration in dealing with abnormal queries, and is the main focus of subsequent index optimization.

[0128] Step S2400, generating an optimal index optimization solution according to the index gap feature set and the pre-built deep reinforcement learning model;

[0129] Further, step S2400 includes:

[0130] Step S2410, evaluating the cost-benefit of introducing a new index for the index gap feature, taking the index gap feature set as the state space, defining the index operation as the action space, taking the cost-benefit of the index as the reward function, and constructing a deep reinforcement learning model;

[0131] The cost of the index includes the creation, maintenance and storage costs of the index, and the benefit of the index includes the improvement of query performance;

[0132] Step S2420, inputting the index gap feature set into the deep reinforcement learning model, trial and error different index operations, learning the impact of index operations on cost-benefit, and obtaining the optimal index optimization strategy;

[0133] Step S2430, decoding the optimal index optimization strategy into an executable optimal index optimization plan, and evaluating the expected effect of the optimization plan;

[0134] Specifically, the state space design abstracts the index optimization problem into a multi-stage decision problem. The state faced in each stage is the current combination of index gaps, which represents the potential and space for query optimization. In each state, you can choose to create an index on a certain index gap or delete an inefficient existing index. These index operations constitute the action space. Different actions will lead to different state transitions and optimization effects, and their cost-benefit is reflected in the reward function. Creating an index can improve query performance, but it also introduces additional creation and maintenance costs. Deleting an index is the opposite, saving space and maintenance overhead, but may reduce the efficiency of some queries. The reward function needs to weigh the positive and negative effects of the index and find the best balance. This reward function design based on the cost model can quantify the pros and cons of different optimization schemes and objectively judge the quality of the index scheme. Modeling index optimization as a sequential decision process, each decision is based on the current database state, and obtains immediate feedback through the reward function, and then adjusts the subsequent decision, which is highly consistent with the reinforcement learning paradigm.

[0135] The Deep Q-Network (DQN) algorithm in deep reinforcement learning is used to learn the index optimization strategy. DQN introduces a deep neural network to approximate the state-action value function Q(s,a) and estimate the long-term reward of taking action a under state s. The optimization process can be regarded as learning the control strategy of index decision through continuous trial and error. The training of the policy network uses the idea of ​​Q-Learning, decomposes the reward function using the Bellman equation, and accelerates convergence by combining technologies such as experience replay. DQN can handle high-dimensional state space and mine the association between state features. Compared with traditional heuristic rules or simple cost models, it can better adapt to complex and changing query scenarios. During the learning process, it constantly interacts with the database, evaluates query performance under a given index configuration, and adjusts index decisions based on feedback from the reward function. This exploration-utilization mechanism enables the optimizer to continuously try and improve on the basis of existing indexes, weigh various factors, and finally converge to the optimal index strategy. Reinforcement learning plays a role similar to "trial and error" here. By simulating and evaluating a large number of possible index combinations, high-quality solutions are selected from them, avoiding the subjectivity and limitations of manually designed index rules.

[0136] The learned optimal strategy is then decoded into an executable index optimization plan, which includes a series of DDL statements, such as CREATE INDEX to create a new index, ALTER INDEX to modify index properties, and DROP INDEX to delete inefficient indexes. To facilitate database administrators' understanding and review, the optimization plan also includes the expected effect evaluation of each index operation, such as query acceleration ratio, disk space usage, etc. The automatic generation of optimization plans simplifies the index optimization process. Administrators can directly review the plan and adjust the execution, eliminating tedious manual design. The cost-based effect evaluation greatly improves the interpretability and credibility of the plan.

[0137] In summary, step S2400 uses deep reinforcement learning technology to model index optimization as a sequential decision problem, and continuously optimizes long-term query efficiency by trial and error of different index configurations. Compared with traditional rule-based or cost-based optimization methods, reinforcement learning can more comprehensively consider different factors, adapt to complex query patterns, and converge to the optimal index strategy. This "machine automatic tuning" idea breaks through the limitations of manual tuning and enhances usability through explainable optimization solutions.

[0138] Exemplarily, taking the e-commerce order table of step S2100 as an example, steps S2200 and S2300 identify that abnormal queries frequently use conditions such as order amount and payment method, but the current index cannot effectively support them, which becomes an index gap feature. The reinforcement learning optimization process of step S2400 experiments to add new indexes to these gaps, such as adding a B+ tree index to the order amount, or adding a bitmap index to the payment method. The expected cost-benefit of different combinations is estimated and fed back to the optimizer through the query simulator; at the same time, the effect of changing the existing index is evaluated, such as adjusting the order of the order status column in the joint index; and finally a set of index optimization solutions that balance space cost and query benefit is obtained, including specific DDL statements and effect estimates, such as "modify the order table, add an index order amount, and it is expected that the performance of order statistics queries will be improved by 20% and the space usage will increase by 1%". This solution takes into account the optimization of multiple index gaps, and can explain the basis and effect of the optimization, greatly improving the comprehensive performance of abnormal queries.

[0139] Step S2500, evaluating the index optimization scheme;

[0140] Specifically, in the test environment, the index optimization scheme is tested, historical queries are replayed, and the query performance improvement before and after optimization is evaluated; the negative impact of the index optimization scheme is evaluated, including the impact of index maintenance on data update performance and the occupancy of index storage space; an evaluation report of the index optimization scheme is generated, which includes key indicators such as query performance improvement ratio, index maintenance cost, and storage space occupancy; through a comprehensive evaluation mechanism, the effectiveness and feasibility of the index optimization scheme in the test environment are verified, and the optimization effect is quantitatively analyzed, with data speaking. The evaluation process covers the positive and negative impacts of optimization, and explores potential problems and optimization space. The evaluation report presents the optimization effect in an intuitive and quantitative way, helping decision makers to objectively judge and weigh the benefits and costs of index optimization. A strict evaluation process is a necessary guarantee for database optimization, and the optimization scheme is continuously polished through continuous trial and error and feedback. Step S2500 demonstrates the concept of evaluation-driven optimization and verifies the value of optimization with data. This scientific and rigorous attitude ensures that index optimization always takes into account the controllable cost of optimization while improving database performance.

[0141] Exemplarily, for the e-commerce order table index optimization solution generated in step S2400, step S2500 replays one month of historical order queries in the test environment and compares the average response time before and after optimization. The results show that order statistics queries are accelerated by an average of 20%, which is in line with expectations; but the improvement in order details queries is not obvious, indicating that the current solution is insufficient for optimizing such queries. Further testing found that the space occupied by the newly created index is 10% higher than expected. The reason for the analysis is that the order amount field width is too large, and the prefix length of the index needs to be trimmed. In terms of data update, the account deduction time of the order delivery process increases by 5%, and there is not much room for optimization. The evaluation report recommends trimming the length of the amount index and supplementing the optimization of order details queries, such as adding an order number index. After evaluation, discovery and improvement, the optimization solution is more comprehensive and accurate, taking into account both space and time efficiency. The final version of the optimization solution can meet the performance requirements of the business and control the optimization cost, and can be applied in the production environment.

[0142] Step S3000, dynamically updating the abnormal query set and the abnormal query feature set during database operation;

[0143] Specifically, the query pattern of the database will continue to evolve with the development of the business and changes in user behavior. For example, during the e-commerce promotion period, order queries will surge; after the iteration of product functions, TOP N queries may be replaced. In this regard, the abnormal query detection mechanism also needs to keep pace with the times and timely perceive the changes in query patterns. Through real-time monitoring and incremental analysis of query logs, the newly emerging abnormal query patterns are dynamically included in the abnormal query set, and their key features are extracted to expand the abnormal query feature set; this dynamic detection ensures that the optimization always focuses on the most important performance issues at the moment; in addition, the impact of pattern changes on existing indexes is evaluated to trigger the incremental index optimization process. Incremental optimization supplements the necessary indexes for new query features on the basis of reusing the current index configuration as much as possible, so as to achieve continuous matching between queries and indexes. The dynamic update mechanism gives the system adaptive capabilities, making it more flexible and efficient in dealing with dynamic changes in data and queries.

[0144] Compared with static abnormal query analysis, dynamic monitoring can better reflect the real-time status of the database and capture subtle changes in the business environment. It breaks the time limit of optimization and no longer relies on offline historical data. Instead, it gives optimization guidance at the moment the query occurs. This is particularly critical for scenarios where data and query patterns fluctuate greatly, such as social and financial fields; dynamic mechanisms ensure that optimization decisions are always based on the latest and most accurate information, thereby minimizing performance lags caused by business changes. In addition, incremental index evolution avoids disruptive changes in optimization, and protects existing investments to the maximum extent while meeting new needs. However, dynamic monitoring also places higher demands on the system, and it is necessary to find a balance between timeliness and cost; through incremental extraction of abnormal query features, rapid evaluation of index benefits, and other means, the overhead of dynamic optimization can be reduced as much as possible; in general, the dynamic matching of queries and indexes greatly improves the overall adaptability of the database, making it an agile force in business innovation and technological change.

[0145] Example 2

[0146] This embodiment provides an adaptive database index optimization and abnormal query detection system based on deep reinforcement learning on the basis of embodiment 1. Figure 4 As shown, including:

[0147] Data collection module: used to collect historical query records of the database;

[0148] Feature extraction module: used to extract query features of queries in historical query records and construct query feature vectors of queries;

[0149] Abnormal query identification module: used to identify abnormal queries according to the query feature vector of the query and generate an abnormal query set; extract abnormal query features from the abnormal query set and generate an abnormal query feature set;

[0150] Index gap identification module: used to generate the current index feature set, compare and analyze the abnormal query feature set with the current index feature set, identify the abnormal query features not covered by the current index, and generate the index gap feature set;

[0151] Index optimization module: used to generate the optimal index optimization solution based on the index gap feature set and the pre-built deep reinforcement learning model.

[0152] Example 3

[0153] This embodiment discloses an electronic device 500, such as Figure 5As shown, the electronic device 500 may include one or more processors and one or more memories. The memories store computer-readable codes, which, when executed by one or more processors, may execute the adaptive database index optimization and abnormal query detection method based on deep reinforcement learning as described above.

[0154] The method or system according to the embodiment of the present application can also be used by Figure 5 The electronic device architecture shown in FIG. Figure 5 As shown, the electronic device 500 may include a bus 501, one or more CPUs 502, a read-only memory (ROM) 503, a random access memory (RAM) 504, a communication port 505 connected to a network, an input / output component 506, a hard disk 507, etc. The storage device in the electronic device 500, such as ROM 503 or hard disk 507, can store the adaptive database index optimization and abnormal query detection method based on deep reinforcement learning provided in the present application. The adaptive database index optimization and abnormal query detection method based on deep reinforcement learning may, for example, include: collecting historical query records of the database, extracting query features of the query in the historical query records, and constructing an N-dimensional query feature vector of the query, where N is the number of extracted query features; according to the query feature vector of the query, identifying abnormal queries and generating an abnormal query set; extracting abnormal query features from the abnormal query set and generating an abnormal query feature set; generating a current index feature set, comparing and analyzing the abnormal query feature set with the current index feature set, identifying abnormal query features not covered by the current index, and generating an index gap feature set; generating an optimal index optimization scheme according to the index gap feature set and a pre-built deep reinforcement learning model. Furthermore, the electronic device 500 may also include a user interface 508. Figure 5 The architecture shown is only exemplary and can be omitted according to actual needs when implementing different devices. Figure 5 One or more components of an electronic device are shown.

[0155] Example 4

[0156] This embodiment discloses a computer-readable storage medium 600, such as Figure 6 As shown, computer-readable instructions are stored on the computer-readable storage medium 600. When the computer-readable instructions are executed by the processor, the adaptive database index optimization and abnormal query detection method based on deep reinforcement learning according to the embodiment of the present application described with reference to the above figures can be executed. The storage medium 600 includes, but is not limited to, for example, volatile memory and / or non-volatile memory. Volatile memory may include, for example, random access memory (RAM) and cache memory (cache), etc. Non-volatile memory may include, for example, read-only memory (ROM), hard disk, flash memory, etc.

[0157] In addition, according to the implementation of the present application, the process described in the above reference flow chart can be implemented as a computer software program. For example, the present application provides a non-transitory machine-readable storage medium, the non-transitory machine-readable storage medium stores machine-readable instructions, and the machine-readable instructions can be executed by a processor to execute instructions corresponding to the method steps provided in the present application, for example: collecting historical query records of a database, extracting query features of queries in historical query records, and constructing an N-dimensional query feature vector of the query, where N is the number of extracted query features; according to the query feature vector of the query, identifying abnormal queries and generating an abnormal query set; extracting abnormal query features from the abnormal query set and generating an abnormal query feature set; generating a current index feature set, comparing and analyzing the abnormal query feature set with the current index feature set, identifying abnormal query features that are not covered by the current index, and generating an index gap feature set; generating an optimal index optimization solution based on the index gap feature set and a pre-built deep reinforcement learning model. When the computer program is executed by a central processing unit (CPU), the above functions defined in the method of the present application are executed.

[0158] The methods, apparatuses, and devices of the present application may be implemented in many ways. For example, the methods, apparatuses, and devices of the present application may be implemented by software, hardware, firmware, or any combination of software, hardware, and firmware. The above order of steps for the method is for illustration only, and the steps of the method of the present application are not limited to the order specifically described above unless otherwise specified. In addition, in some embodiments, the present application may also be implemented as programs recorded in a recording medium, which include machine-readable instructions for implementing the method according to the present application. Therefore, the present application also covers recording media storing programs for executing the method according to the present application.

[0159] In addition, the parts of the above-mentioned technical solutions provided in the embodiments of the present application that are consistent with the implementation principles of the corresponding technical solutions in the prior art are not described in detail to avoid excessive redundancy.

[0160] The specific implementation modes as described above further describe the purpose, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific implementation mode of the present invention and is not intended to limit the present invention. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. Adaptive database index optimization and abnormal query detection method based on deep reinforcement learning, characterized in that: The method comprises: Collect historical query records of the database, extract query features of the queries in the historical query records, and construct an N-dimensional query feature vector of the query, where N is the number of extracted query features; identify abnormal queries based on the query feature vector of the query, and generate an abnormal query set; extract abnormal query features from the abnormal query set, and generate an abnormal query feature set; Generate the current index feature set, compare and analyze the abnormal query feature set with the current index feature set, identify the abnormal query features not covered by the current index, and generate the index gap feature set; generate the optimal index optimization solution based on the index gap feature set and the pre-built deep reinforcement learning model; The method for generating an abnormal query set comprises: Set up The query feature vector of a query is , No. The query feature vector of a query is ,calculate The local density as well as and The distance between ,according to and Calculate the abnormality score; Sort the anomaly scores from large to small, and take the first k queries as abnormal query candidates, where k is the anomaly threshold, which is determined by the average of the maximum anomaly score and the anomaly score; Aggregate abnormal query candidates into an abnormal query set; The calculation formula of the abnormality score is: in, is the abnormality score, express The distance to the nearest neighbor with a higher density than it, and n represents the total number of queries.

2. The method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning according to claim 1 is characterized in that: The abnormal query features include data tables, columns and predicate conditions; The extracting abnormal query features from the abnormal query set to generate the abnormal query feature set includes: Count the frequency distribution of abnormal query features and identify the tables, columns, and predicate conditions that appear frequently in abnormal queries and are greater than the abnormal frequency threshold k1; The tables, columns and predicate conditions that appear frequently and are greater than the abnormal frequency threshold k1 are summarized into an abnormal query feature set.

3. The method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning according to claim 1 is characterized in that: The current index feature set is obtained by loading the current index configuration of the database, parsing the index configuration information, and extracting key features of the index.

4. The method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning according to claim 1 is characterized in that: The method for generating the index gap feature set is to count the frequency of uncovered abnormal query features, mark the uncovered abnormal query features whose frequency exceeds the gap frequency threshold k2 as index gap features, and generate the index gap feature set.

5. The method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning according to claim 1 is characterized in that: Methods for generating index optimization plans include: Evaluate the cost-benefit of introducing a new index for index gap features, take the index gap feature set as the state space, define the index operation as the action space, take the cost-benefit of the index as the reward function, and build a deep reinforcement learning model; Input the index gap feature set into the deep reinforcement learning model to obtain the optimal index optimization strategy; Decode the optimal index optimization strategy into an executable optimal index optimization plan.

6. An adaptive database index optimization and abnormal query detection system based on deep reinforcement learning, which is used to implement the adaptive database index optimization and abnormal query detection method based on deep reinforcement learning as described in any one of claims 1 to 5, characterized in that: The system comprises: Data collection module: used to collect historical query records of the database; Feature extraction module: used to extract query features of queries in historical query records and construct query feature vectors of queries; Abnormal query identification module: used to identify abnormal queries according to the query feature vector of the query and generate an abnormal query set; extract abnormal query features from the abnormal query set and generate an abnormal query feature set; Index gap identification module: used to generate the current index feature set, compare and analyze the abnormal query feature set with the current index feature set, identify the abnormal query features not covered by the current index, and generate the index gap feature set; Index optimization module: used to generate the optimal index optimization solution based on the index gap feature set and the pre-built deep reinforcement learning model.

7. An electronic device, characterized in that: It includes a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the program, the steps in the method for adaptive database index optimization and abnormal query detection based on deep reinforcement learning as described in any one of claims 1 to 5 are implemented.

8. A readable storage medium, characterized in that: The readable storage medium stores a computer program, which is suitable for being loaded by a processor to execute the steps in the adaptive database index optimization and abnormal query detection method based on deep reinforcement learning as described in any one of claims 1 to 5.

Images