A database index optimization method and system based on virtual index
Through virtual indexing and machine learning models, database indexing is analyzed and optimized, and the problem of index creation in the existing technology is solved. The problem of index creation relying on experience and inability to adapt to data changes is achieved, and efficient index query and performance improvement is achieved.
Patent Information
- Application Number
- CN202011146017.X
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2020-10-23
- Publication Date
- 2024-12-03
- Estimated Expiration
- 2040-10-23
AI Technical Summary
The existing database index creation method relies on the experience of developers and cannot effectively match SQL statements, resulting in low query efficiency and inability to adapt to changes in database table data volume and data distribution, resulting in unreasonable indexes and affecting system performance.
Using a database index optimization method and system based on virtual indexes, the index collection is generated by analyzing and generating an index set from SQL query statements, the running time of each index is calculated, the best index is selected, and the evaluation value is established using machine learning models such as XGBOOT, LIGHTGBM, and CATBOOST. Models, monitor and update indexes in real time, adapt to data changes.
It improves database index query efficiency, reduces hardware resource overhead, realizes automatic optimization and adjustment of indexes, adapts to different business scenarios, and improves query performance.
Smart Images

Figure CN113704246B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of databases, and more specifically, to a database index optimization method and system based on virtual indexes. Background Art
[0002] Databases are ubiquitous in software systems, whether web applications or mobile apps. MySQL is one of the most popular relational database management systems, which is favored by developers and has gradually become a mainstream database solution, with wide applications in various business fields.
[0003] An index is a structure that sorts the values of one or more columns in a database table, providing pointers to the data values stored in specified columns of the table. A good index can greatly reduce the execution time of SQL statements and improve the data query efficiency of the application.
[0004] Currently, the creation of indexes mainly depends on the experience of developers. When implementing business system functions, developers create indexes at the same time as they create tables based on their previous experience. Since developers do not have a systematic grasp of database index knowledge and are highly subjective, indexes cannot match SQL statements, resulting in low query efficiency. In addition, when the amount of database table data changes or the distribution of table data changes, historical indexes are no longer suitable and query efficiency cannot be guaranteed. These lead to a large number of unreasonable indexes in actual applications, which ultimately affect the query performance of the system.
[0005] The Chinese patent application "Database Index Creation Method and Device" (201610181731.X) proposes to perform statistical analysis on each query field combination of data query operations, and to create an index based on the query field combination when the number of data query operations meets the preset automatic index creation conditions. The method introduced in the patent only creates the corresponding data table index based on the query field combination with a large number of queries, but does not consider the running time of each index in the actual SQL statement, and cannot determine whether the created index improves the actual query speed.
[0006] The Chinese patent application "Database Index Optimization Method, Device and System Based on Python" (CN201811284394.2) designs a device that uses the Django framework of Python as the front end to accept database operation statements input by users, and uses the open source tool SQLAdvisor to process and analyze to obtain index optimization results. The method of this patent analyzes the SQL statements input by developers to obtain index optimization suggestions, which can help create or optimize indexes before SQL goes online; however, when the amount of database table data changes or the distribution of table data changes in the actual environment, the index created before going online cannot match the existing environment, and the query efficiency cannot be guaranteed.
[0007] Therefore, in order to further improve the efficiency of database index query and adapt to automatic optimization and adjustment of database index in different business scenarios, it is hoped to provide an improved database index query efficiency optimization method and system. Summary of the invention
[0008] This Summary is provided to introduce some concepts in a simplified form that are further described below in the Detailed Description. This Summary is not intended to identify key features or essential features of the claimed subject matter, nor is it intended to be used as an aid in determining the scope of the claimed subject matter.
[0009] The present invention aims to provide a database index optimization method and system based on virtual index, which selects the best index based on the actual operation efficiency of SQL query statements, can adapt to the automatic optimization and adjustment of database indexes in different business scenarios, no longer rely on manual experience, improve the efficiency of database system index query, and improve the database query performance, wherein the database includes but is not limited to MySQL database. The present invention establishes three modules: production module, replication module, and self-analysis module. By synchronizing the database data and logs in the production module to the replication module, the SQL statements in the slow query log are selected for lexical analysis and syntax analysis to generate a set of index sets, and the effective index combination, i.e., the virtual index, is evaluated and obtained, and then the running time of each virtual index is calculated, and the best index is compared and selected and automatically updated to the production database to realize the first update of the virtual index. Subsequently, the self-analysis module uses the method of fusion of three models, XGBOOT, LIGHTGBM, and CATBOOST, based on the data actually generated in the first update to train and establish a virtual index evaluation value model, and the best index prediction is performed for the production environment where the data changes in real time, and it is automatically updated to the production database to complete the real-time monitoring and maintenance of the index efficiency.
[0010] According to one aspect of the present invention, a database index optimization method based on virtual index is provided, the method comprising:
[0011] Respond to index update requests and synchronize production database and log information;
[0012] Creating a virtual index set based on analysis of corresponding query statements in the log;
[0013] Select one or more optimal indexes based on the actual query execution time of each virtual index in the virtual index set and automatically update them to the production database; and
[0014] One or more optimal indexes are selected again based on the virtual index evaluation values reflecting the improvement in query execution time before and after each index is created and automatically updated to the production database.
[0015] According to an embodiment of the present invention, the index update request is triggered periodically or in response to the production database query efficiency being lower than a threshold preset in the log.
[0016] According to a further embodiment of the present invention, creating a virtual index set further comprises:
[0017] Selecting corresponding query statements in the log to perform lexical analysis and grammatical analysis to generate a candidate index set; and
[0018] The evaluation obtains a virtual index set with index evaluation parameters, wherein the index evaluation parameters include: a column combination associated with an equal value predicate following WHERE in a query; fields in ORDER or GROUP must be included in the combined index and in the same order; and a covering index.
[0019] According to a further embodiment of the present invention, selecting one or more optimal indexes again based on the virtual index evaluation value reflecting the improvement of query execution time before and after each index is created and automatically updating the indexes to the production database further comprises:
[0020] Record query and index features F = (f1, f2, ..., f k );as well as
[0021] Generate training data including the features for virtual index evaluation value model training, wherein the amount of data = number of virtual index combinations * number of SQL statements * number of tables * number of queries.
[0022] According to a further embodiment of the present invention, the virtual index evaluation value model is established by training using a method of fusing three models: XGBOOT, LIGHTGBM, and CATBOOST.
[0023] According to a further embodiment of the present invention, selecting one or more optimal indexes again based on the virtual index evaluation value reflecting the improvement of query execution time before and after each index is created and automatically updating the indexes to the production database further includes:
[0024] Get the actual running time of each virtual index in the virtual index set and the query and index features F′(f′1,f′2,…,f′ k );
[0025] pass To train and obtain model M, models M1, M2, and M3 are obtained by training with XGBOOT, LIGHTGBM, and CATBOOST, respectively, and the optimal parameters Para1, Para2, and Para3 are obtained by five-fold cross validation, respectively;
[0026] pass Fit the best model M best =a*M1+b*M2+c*M3, where Opt(I,q) is the optimization index of index I for query SQL statement q;
[0027] For different SQL statements involved in the same table in the log, the optimal model is used to simulate and predict the optimal index.
[0028] The optimal index is calculated using the index evaluation function Z, I best =min(Z|I i )i∈[0,k], where Z=∑ q∈S Opt(I,q); and
[0029] The index with the smallest virtual index evaluation value is compared with the current application index, and one or more optimal indexes are retained.
[0030] According to a further embodiment of the present invention, Opt(I,q) is the reciprocal of the difference between the execution time before and after the index is created.
[0031] According to another aspect of the present invention, a database index optimization system based on virtual index is provided, the system comprising:
[0032] A production module, wherein the production module is configured to: record log information, monitor query efficiency, and send an index update request at a fixed time or when the query efficiency is lower than a preset threshold;
[0033] A replication module, the replication module being configured to:
[0034] Synchronize production database, log information, and
[0035] Creating a virtual index set based on an analysis of a corresponding query statement in the log; and a self-analysis module, the self-analysis module being configured to:
[0036] Select one or more optimal indexes based on the actual query execution time of each virtual index in the virtual index set and automatically update them to the production database; and
[0037] One or more optimal indexes are selected again based on the virtual index evaluation values reflecting the improvement in query execution time before and after each index is created and automatically updated to the production database.
[0038] According to one embodiment of the present invention, the log information includes user query status within a preset time period, including the original file of the slow query log, and the statistically generated slow query SQL statement (sql_text), start execution time (start_time), statement execution time (query_time), table lock time (lock_time), the number of records returned by the query (rows_sent), the number of scanned rows (rows_examined), and the number of statement executions (rows_count).
[0039] According to a further embodiment of the present invention, the self-analysis module is further configured to:
[0040] Record query and index features F = (f1, f2, ..., f k );
[0041] Generate training data including the features for training the virtual index evaluation value model, wherein the amount of data = number of virtual index combinations * number of SQL statements * number of tables * number of queries; and
[0042] Based on the training data, the virtual index evaluation value model is trained and established by adopting the fusion method of three models: XGBOOT, LIGHTGBM and CATBOOST, and the optimal index prediction is performed based on the virtual index evaluation value model.
[0043] Compared with the solutions in the prior art, the database index optimization system and method provided by the present invention have at least the following advantages:
[0044] (1) Currently, there are some technologies that implement index creation based on field word frequency analysis in query logs, SQL statement execution analysis, etc., but the query efficiency of the index is not strongly related to the query field and SQL statement format, etc., and the characteristics of the database table, the amount of data, and the data distribution must also be considered. Therefore, these technologies cannot produce an index with the best query efficiency. The present invention proposes a simulation operation method through virtual indexing, which ultimately obtains the most accurate index result, can greatly improve the actual query speed of the index, and can also reduce the cost of hardware resources.
[0045] (2) The present invention proposes a method for selecting the best index by analyzing a virtual index combination from a slow query log and simulating the SQL statement to run calculations, thereby improving the traditional index optimization based on the field combination in the query log without considering the operation effect of the index in the actual production environment.
[0046] (3) The present invention proposes a new method for improving the efficiency of MySQL database index query. Based on the virtual index, the method simulates the operation from the SQL query, automatically creates training samples, collects data features, and adopts the method of fusing three machine learning models, namely XGBOOT, LIGHTGBM and CATBOOST, to effectively solve the problem of insufficient prediction accuracy of traditional index creation methods.
[0047] (4) Possess online learning capabilities and strong adaptability. Traditional index creation methods are mainly based on data statistical analysis. The method of this patent uses machine learning to continuously train and automatically tune the index evaluation value model according to the changes in database data characteristics, continuously adapt to the database's requirements for indexes, and ultimately improve query performance.
[0048] (5) It is universal. Although different businesses have different database query statements and data tables, and different index requirements, this method selects the best index based on the simulation operation of the virtual index to the replication environment. At the same time, a virtual index evaluation value model is established based on real data based on machine learning training, which can be used to predict the best index. Therefore, this method can be used to optimize database indexes in different business scenarios.
[0049] These and other features and advantages will become apparent by reading the following detailed description and by reference to the associated drawings.It is to be understood that the foregoing general description and the following detailed description are illustrative only and are not restrictive of the aspects of what is claimed. BRIEF DESCRIPTION OF THE DRAWINGS
[0050] In order to understand the manner in which the above features of the present invention are used in detail, the above briefly summarized contents can be described in more detail with reference to various embodiments, some of which are shown in the accompanying drawings. However, it should be noted that the accompanying drawings only show some typical aspects of the present invention and should not be considered to limit its scope, because the description may allow for other equally effective aspects.
[0051] Figure 1 The diagram is an example architecture diagram of a database index query efficiency optimization system based on virtual indexes according to an embodiment of the present invention.
[0052] Figure 2 The present invention is a flowchart of a method for optimizing database index query efficiency based on virtual indexes according to an embodiment of the present invention.
[0053] Figure 3 The flowchart of a method for performing a first update of a virtual index based on an actual calculation optimal index update according to an embodiment of the present invention.
[0054] Figure 4The flowchart of the method for performing the second update of the virtual index based on the virtual index evaluation value model according to one embodiment of the present invention. DETAILED DESCRIPTION
[0055] The present invention will be described in detail below in conjunction with the accompanying drawings, and the features of the present invention will be further revealed in the following specific description.
[0056] Figure 1 FIG. 1 is an exemplary architecture diagram of a database index optimization system 100 based on a virtual index according to an embodiment of the present invention. Figure 1 As shown in FIG. 1 , the system 100 of the present invention includes a production module 101 , a replication module 102 and a self-analysis module 103 .
[0057] The production module 101 includes a timing unit 104, an anomaly detection unit 105, a user log unit 106 and a production database 107. The production database 107 is a database for real business operations. The user log unit 106 is responsible for recording and counting user queries within a preset time period, including the original file of the slow query log, and the statistically generated slow query SQL statement (sql_text), start execution time (start_time), statement execution time (query_time), lock table time (lock_time), the number of records returned by the query (rows_sent), the number of rows scanned (rows_examined), the number of statement executions (rows_count) and other information. The anomaly detection unit 105 is used to record and detect whether the execution time of the current query statement is significantly aggravated compared to the history and to generate abnormal query data, and trigger an index update request. The timing unit 104 is used to synchronize all data and log information of the database at a regular time and trigger an index update request.
[0058] The replication module 102 includes a replication unit 108 and a virtual index unit 109. The replication unit 108 is used to replicate and synchronize the production database and the user log. The virtual index unit 109 decomposes the query condition information and field information of each query statement in the replicated user log, parses and generates an index combination, and finally combines with the self-analysis module 103 to generate a set of virtual index combinations with index evaluation parameters.
[0059] The self-analysis module 103 includes a calculation unit 110, a prediction unit 111 and an optimization unit 112. The calculation unit 110 is used to re-execute the corresponding query statements in the copied database using various virtual indexes to calculate and generate an effect evaluation value that measures the actual effect of the virtual index. The prediction unit 111 is used to train the best virtual index based on data features to realize the automatic index creation process based on data change trends. The optimization unit 112 evaluates and adjusts the prediction results.
[0060] Those skilled in the art will appreciate that the database index optimization system and its modules of the present invention may be implemented in the form of hardware or software, and the modules may be merged or combined in any appropriate manner.
[0061] Figure 2 2 is a flowchart of a database index optimization method 200 based on a virtual index according to an embodiment of the present invention. The method 200 starts at step 201, where the production module 101 records log information, monitors query efficiency, and sends index update requests regularly or when an exception is detected. The log information includes the original file of the slow query log, as well as the statistically generated slow query SQL statement (sql_text), start execution time (start_time), statement execution time (query_time), table lock time (lock_time), number of records returned by the query (rows_sent), number of rows scanned (rows_examined), number of statement executions (rows_count), and other information. The query efficiency standard is based on a threshold in the slow query log, which is set based on expert experience. For example, the threshold is set to 1 by default in this system. When the query efficiency is lower than the threshold, an exception alarm will be triggered and recorded in the exception log. The exception detection unit 105 in the production module 101 triggers an index update request in response to the query efficiency of the production database being lower than the preset threshold in the slow query log. The timing unit 104 in the production module 101 synchronizes all the data and log information of the database to the replication module at a regular time, triggering an index update request. For example, the timing unit synchronizes data and triggers an index update request every three months.
[0062] In step 202, the replication module 102 synchronizes the production database and log information based on the binlog log and creates a virtual index set. After the replication and synchronization of the database and log information are completed by the replication unit 108, the virtual index unit 109 in the replication module 102 selects the corresponding query statement according to the index update trigger situation and parses it one by one. The parsing process is completed by the lexical analysis tool Flex and the syntax analysis tool Bison. As an example, the SQL statement is: SELECT A, B FROMT WHERE C=x and A>y ORDER BY B, which can be parsed by the virtual index unit 109 as follows:
[0063] {SELECT:{A,B},FROM:{T},WHERE:{AND:{C:{C=x},A:{A>y}}},ORDER:{B}}
[0064] After parsing is completed, the virtual index unit 109 generates a set of candidate index sets (for example, {[A], [C], [C, A], [C, A, B], [C, B, A]}) based on the parsing results. The virtual index unit 109 then obtains a set of valid index combinations (i.e., virtual indexes) based on the index evaluation criteria. The virtual index evaluation criteria are as follows: (1) The column combination associated with the equal value predicate following WHERE in the query; (2) The fields in ORDER or GROUP must be included in the combined index and in the same order; (3) All columns in the query statement are included (covering index). An example of a virtual index with index evaluation criteria parameters is as follows:
[0065] Index combination Evaluation criteria parameters Index_1 [A] Index_2 [C] Index_3 [C,A] [1] Index_4 [C,A,B] [1,3] Index_5 [C,B,A] [2,3]
[0066] In step 203, the self-analysis module 103 performs the first update of the virtual index based on the actual operation optimal index update. Figure 3 Further shown in .
[0067] In step 204, the self-analysis module 103 performs a second update of the virtual index based on the virtual index evaluation value model to complete the real-time detection and maintenance of the index. Figure 4 Further shown in .
[0068] Figure 3 3 is a flow chart of a method 300 for performing a first update of a virtual index based on an actual calculation of the best index update according to an embodiment of the present invention. The method 300 starts at step 301, where the calculation unit 110 in the self-analysis module 103 creates each virtual index (including no index) obtained by the virtual index unit 109, and executes and records the actual running time of each SQL query.
[0069] In step 302, the computing unit 110 records the characteristic attributes of the query and the index as follows:
[0070] name Object describe S_TYPE Query q Query type (select, insert, update, delete) S_TNUM Query q Query the number of tables involved S_RNUM Query q Query the number of records in the table involved (average of multiple tables) S_ROWS_EXAMINED Query q The number of rows scanned by the query S_COST Query q The cost of query S_ROWS_SENT Query q The number of records returned by the query S_ROWS_COUNT Query q Number of query statement executions S_OPERATION Query q The number of operators involved in the query S_CODE Query q Encoding of query condition fields S_ZNUM Query q The number of query field types I_RNUM Index I The number of records in the data table where the index is located I_ZNUM Index I The number of fields included in the index I_CODE Index I The encoding of the fields included in the index
[0071] In step 303, training data is generated for the prediction unit 111 to perform model training and construction. The training data includes the actual running time and feature summary F of each virtual index (f1, f2, ..., f k ), where data volume = number of virtual index combinations * number of SQL statements * number of tables * number of queries.
[0072] In step 304, the actual running time of each virtual index is compared, and the top K best indexes are selected as outputs to be fed back to the production module and the index is automatically created. The first update process of the virtual index is completed.
[0073] Figure 44 is a flow chart of a method 400 for performing a second update of a virtual index based on a virtual index evaluation value model according to an embodiment of the present invention. The method 400 begins at step 401, obtaining the actual running time and feature summary F of the index in the first update (f1, f2, ..., f k ), where the categorical features (e.g., S_TYPE) are quantized into categorical values, the data features (e.g., S_RNUM) are binned, and finally the features are summarized as F′=(f′1,f′2,…,f′ k ).
[0074] In step 402, training is performed using xgboost, lightgbm, and catboost to obtain models M1, M2, and M3, respectively.
[0075] In step 403, the optimal parameters Para1, Para2, and Para3 of the three algorithms are respectively obtained through five-fold cross validation.
[0076] In step 404, the model M is obtained by training.
[0077]
[0078] Where M reflects the relationship between the index and the query, that is, M(F)→Opt(I,q), where Opt(I,q) is the optimization index of index I for query SQL statement q. Opt(I,q) is measured by the inverse of the difference in execution time before and after index creation, that is, In real time, Opt(I,q) cannot be obtained directly, but the value can be predicted, for example, through machine learning.
[0079] In step 405, by Fit the best model M best =a*M1+b*M2+c*M3.
[0080] In step 406, for different SQL statements related to the same table in the log, the best index is predicted by simulation, that is,
[0081] In step 407, the optimal index is calculated using the index evaluation function Z of the same table, that is,
[0082] I best =min(Z|I i )i∈[0,k]
[0083] Where Z = ∑ q∈s Opt(I,q).
[0084] In step 408, the index with the smallest virtual index evaluation value is compared with the current application index, and the top K best indexes are retained, that is, the indexes that are not in the top K best indexes are automatically deleted and the indexes that have not been created in the top K best indexes are automatically created. In addition, in the case where the production database does not actually create an index, the virtual index can be directly based on the best model M fitted in step 405. best Achieve evaluation value Z I =∑ q∈s Opt(I,q) to generate.
[0085] It should be understood that in an actual database environment, after an index update request is issued, the self-analysis module 103 no longer needs to spend a lot of time to create and execute each SQL query statement under each virtual index, compare the actual running time to select the best index, but instead completes the index real-time detection and maintenance tasks based on the virtual index evaluation value model.
[0086] What has been described above includes examples of various aspects of the claimed subject matter. Of course, it is not possible to describe every conceivable combination of components or methods for the purpose of describing the claimed subject matter, but one of ordinary skill in the art will recognize that many further combinations and permutations of the claimed subject matter are possible. Thus, the disclosed subject matter is intended to encompass all such alterations, modifications, and variations that fall within the spirit and scope of the appended claims.
Claims
1. A database index optimization method based on virtual index, It is characterized in that The method comprises: Respond to index update requests and synchronize production database and log information; Creating a virtual index set based on analysis of corresponding query statements in the log information; Select one or more optimal indexes based on the actual query execution time of each virtual index in the virtual index set and automatically update them to the production database; and Reselecting one or more optimal indexes based on the virtual index evaluation value and automatically updating the indexes to the production database, wherein the virtual index evaluation value reflects the improvement of the query execution time before and after each index is created, wherein reselecting the one or more optimal indexes further includes: Obtain the actual query execution time of each virtual index in the virtual index set and the quantitative feature F′(f′) of the query index 1 ,f′ 2 ,…,f′ k ); pass To train and obtain model M, where model M 1 、M 2 、M 3 The optimal parameters para are obtained through training with XGBOOT, LIGHTGBM, and CATBOOST. 1 、Para 2 、para 3 are obtained through five-fold cross validation; pass Fit the best model M best =a*M 1 +b*M 2 +c*M 3 , where Opt real (I,q) is the inverse of the difference in execution time before and after index creation. For different SQL statements related to the same table in the log information, the optimal model is used to simulate and predict the optimal index. Where F(f1 1 ,f 2 ,…,f k ) is the feature of the query index; The optimal index is calculated using the index evaluation function Z, I best =min(Z|I i )i∈[0,k], where Z=∑ q∈S Opt real (I,q); and The index with the smallest virtual index evaluation value is compared with the current application index, and one or more optimal indexes are retained.
2. The method according to claim 1, It is characterized in that The index update request is triggered periodically or in response to a production database query efficiency being lower than a threshold preset in the log.
3. The method according to claim 1, It is characterized in that The creating of the virtual index set further comprises: Selecting a corresponding query statement in the log information to perform lexical analysis and grammatical analysis to generate a candidate index set; and A virtual index set is obtained based on index evaluation criteria, wherein the index evaluation criteria include: a combination of columns associated with an equal value predicate following WHERE in a query; fields in ORDER or GROUP must be included in the combined index and in the same order; and all columns in the query statement are included.
4. The method according to claim 1, It is characterized in that The selecting one or more optimal indexes again based on the virtual index evaluation value and automatically updating the indexes to the production database further includes: Record the feature F(f 1 ,f 2 ,…,f k );as well as Generate training data including the features for virtual index evaluation value model training, wherein the amount of data = number of virtual index combinations * number of SQL statements * number of tables * number of queries.
5. The method according to claim 4, It is characterized in that The virtual index evaluation value model is established by training using a method of fusing three models: XGBOOT, LIGHTGBM, and CATBOOST.
6. A database index optimization system based on virtual index, It is characterized in that The system comprises: A production module, wherein the production module is configured to: record log information, monitor query efficiency, and send an index update request at a fixed time or when the query efficiency is lower than a preset threshold; A replication module, the replication module being configured to: Synchronize production database, log information, and Creating a virtual index set based on analysis of corresponding query statements in the log information; and A self-analysis module, wherein the self-analysis module is configured to: Select one or more optimal indexes based on the actual query execution time of each virtual index in the virtual index set and automatically update them to the production database; and Reselecting one or more optimal indexes based on the virtual index evaluation value and automatically updating the indexes to the production database, wherein the virtual index evaluation value reflects the improvement of the query execution time before and after each index is created, wherein reselecting the one or more optimal indexes further includes: Obtain the actual query execution time of each virtual index in the virtual index set and the quantitative feature F′(f 1 ′,f′ 2 ,…,f′ k ); pass To train and obtain model M, where model M 1 、M 2 、M 3 The optimal parameters Para are obtained through training with XGBOOT, LIGHTGBM, and CATBOOST. 1 、Para 2 、Para 3 are obtained through five-fold cross validation; pass Fit the best model M best =a*M 1 +b*M 2 +c*M 3 , where Opt real (I,q) is the inverse of the difference in execution time before and after index creation. For different SQL statements related to the same table in the log information, the optimal model is used to simulate and predict the optimal index. Where F(f 1 ,f 2 ,…,f k ) is the feature of the query index; The optimal index is calculated using the index evaluation function Z, I best =min(Z|I i )i∈[0,k], where Z=∑ q∈S Opt real (I,q); and The index with the smallest virtual index evaluation value is compared with the current application index, and one or more optimal indexes are retained.
7. The system according to claim 6, It is characterized in that The log information includes user query status within a preset time period, including the original slow query log file, and the statistically generated slow query SQL statement, start execution time, statement execution time, table lock time, number of records returned by the query, number of scanned rows, and number of statement execution times.
8. The system according to claim 6, It is characterized in that The self-analysis module is further configured to: Record the feature F of the query index = (f 1 ,f 2 ,…,f k ); Generate training data including the features for training the virtual index evaluation value model, wherein the amount of data = number of virtual index combinations * number of SQL statements * number of tables * number of queries; and Based on the training data, the virtual index evaluation value model is trained and established by adopting the fusion method of three models: XGBOOT, LIGHTGBM and CATBOOST, and the optimal index prediction is performed based on the virtual index evaluation value model.
