A learning-based index recommendation and evaluation method

Abstract

The application discloses a learning-based index recommendation and evaluation method, including expert experience-driven index recommendation and sampling-based index evaluation. The expert experience-driven index recommendation extracts pre-defined features from the real index establishment situation of technicians on various SQL queries and their related data tables in historical data, trains a machine learning classification model, and performs index recommendation on the given SQL query and database statistical information. The sampling-based index evaluation extracts a certain amount of data from the source database to generate a test database using a sampling method, evaluates the effect of the recommended index from multiple dimensions on the test database, and gives the final recommended result. The application performs index recommendation work from two dimensions, that is, efficiently gives the recommended index configuration through the learning method, and improves the confidence of the final recommended index result through the evaluation method.

Description

TECHNICAL FIELD

[0001] The application belongs to the technical field of database, and particularly relates to an index recommendation and evaluation method based on learning. BACKGROUND

[0002] Without changing the mode of the table or the application code, a reasonable index is one of the key factors affecting whether the database can be queried and updated efficiently. In the case of an index, the structured query language (SQL) can retrieve data by scanning the index tree, reducing query time and system resource consumption. In a complex and dynamically evolving business system, the database query optimizer cannot consider all possible execution plans, and the use of some inappropriate indexes may even slow down the query speed. Professional database administrators need to consider the impact of all queries on the table before and after the index is established when creating an index, but even they need to spend some analysis time when facing some specific scene problems. Professional database administrators have always been a scarce talent in the industry, and as the database scale continues to expand, the traditional optimization method based on manpower has been unable to meet the current large-scale slow query optimization.

[0003] In order to automate this process, the traditional heuristic rule and query optimizer-based method takes query cost and index storage cost as optimization objectives for automatic index recommendation. It takes the workload as the system input, analyzes the indexable attribute columns in the SQL of each query, constructs the candidate index space, iterates the candidate space using the greedy algorithm and pruning strategy, and selects the index set that minimizes the query cost. The what-if tool is used to assume that the index actually exists, avoiding the huge overhead caused by directly creating a large number of indexes in the iteration process. Then, the recommended index set of all single queries is appropriately merged as the candidate index space of the entire load. Finally, the greedy algorithm is used again to select the optimal index set of the entire load from the load candidate index space. It has the following limitations: first, it needs to calculate a better index configuration in a large search space, and many candidate indexes and query plans need to be evaluated, so it will cause huge computational consumption. Pruning the index candidates can reduce the search space, but it also brings the risk of losing useful indexes; second, there are multiple physical operator types, multiple table joins and complex subqueries in the workload. The query optimizer may make inaccurate estimates of the query cost, and directly adding recommended indexes may cause performance regression, which is very dangerous in a production environment. SUMMARY

[0004] In order to overcome the problems of large calculation resource consumption and low confidence of recommendation result of the existing index recommendation method, the present application aims to provide a learning-based index recommendation and evaluation method, which uses machine learning to obtain the expert experience of business personnel in adding and deleting indexes in historical data to recommend indexes, and uses a sampling method to evaluate the effect of the recommended indexes, thereby realizing efficient and accurate index recommendation.

[0005] The specific technical solution for realizing the purpose of the present application is:

[0006] The learning-based index recommendation and evaluation method has the characteristics that the method comprises expert experience-driven index recommendation and sampling-based index evaluation; the expert experience-driven index recommendation realizes index recommendation based on index establishment experience in historical data; and the sampling-based index evaluation realizes index evaluation on a sampling database to give a recommended result with high confidence; the specific steps are as follows:

[0007] Step 1: expert experience-driven index recommendation

[0008] A1: extracting table information and index information of a corresponding database according to user input database connection information and saving; the table information is table name, column name, Cardinality and table row number; the index information is index corresponding column name;

[0009] A2: calling a SQL parsing program to obtain SQL information according to user-provided structured query language (SQL) statements, wherein the SQL information comprises extracted single column information and multi-column relationship information;

[0010] A3: extracting and splicing the table information in step A1 and the single column information in step A2 as single column features, taking the index information in step A1 as labels of the single column features, and then training a single column index model; specifically comprising:

[0011] A31: extracting the data type of the column;

[0012] A32: extracting the position of the column in the SQL;

[0013] A33: extracting the condition expression in which the column is located;

[0014] A34: extracting the function information in which the column is located;

[0015] A35: numbering and splicing all the extracted features, and taking the index information as labels;

[0016] A36: using the single column features for model training to train a single column index model;

[0017] A4: According to the single column feature, a hash table is constructed, the multi-column relationship information in step A2 is extracted, and is spliced with the single column feature in step A3 as a multi-column feature, the index information in step A1 is taken as a label of the multi-column feature, and then a multi-column index model is trained; specifically comprising:

[0018] A41: According to the single column feature, a hash table is constructed, the Key value is a single column feature identifier, and the Value value is a single column feature possessed by the feature vector;

[0019] A42: Any multi-column appearing in the same SQL of the same Table is combined, the single column feature identifiers corresponding to the columns are spliced, a preliminary multi-column feature is constructed, and the preliminary multi-column feature set is named as fn_start_train;

[0020] A43: fn_start_train is traversed, multi-column relationship information is extracted according to the single column feature identifiers contained in each preliminary multi-column feature in fn_start_train, and is put into a multi-column relationship feature set fn_merge_train;

[0021] A44: fn_start_train and fn_merge_train are transversely spliced according to the single column feature identifiers, corresponding index information is taken as a label, and finally a multi-column feature fn_end_train is obtained;

[0022] A45: The multi-column feature is used for model training, and a multi-column index model is trained;

[0023] A5: A SQL parsing program is called to extract features from a user input SQL statement, and is spliced with database table information saved in a model training stage to generate single column and multi-column features, a single column index model in step A3 and a multi-column index model in step A4 are input to perform index recommendation;

[0024] A6: The index recommendation result is output in a JSON file, and the file includes a SQL statement and recommended indexes;

[0025] Step 2: Index evaluation based on sampling

[0026] B1: The JSON file of the index recommendation result is parsed, all data table Schemas involved in each query are acquired, including primary key, foreign key and index information, and a test environment library table is created;

[0027] B2: Data is sampled and dumped according to a user set proportion from an online database, and the data tables appearing in the query are imported into a test database;

[0028] B3: traverse all queries under different recommended index configurations, obtain evaluation indexes before and after the query establishes recommended indexes under the verification environment data scale, and the evaluation indexes include execution time, scanned row number and query cost;

[0029] B4: output the index evaluation result in a JSON file, and the file includes a SQL statement, an evaluation index and an evaluation index.

[0030] The beneficial effects of the present application include:

[0031] Based on the characteristics of database indexes and the research on existing index recommendation algorithms, the present application proposes an expert experience driven index recommendation. The historical data contains the execution records of normal queries and slow queries, as well as the mode and index information of related library tables, which can be understood as the expert experience of professional technicians to establish reasonable indexes. The key SQL semantics and table column information are extracted as feature information, and a plurality of corresponding classification models are trained for single column indexes and multi-column indexes, so as to recommend missing indexes for a query or a group of workloads, and significantly improve the query efficiency. The present application can effectively avoid the need for database what-if function in the traditional heuristic rule driven index recommendation algorithm, and the large amount of computing resource consumption caused by the polling of the query optimizer under different index configurations; at the same time, the present application is compatible with different databases through different training data, and has a wide range of applications.

[0032] The sampling based index evaluation is to prevent potential security problems caused by directly creating indexes in the production environment, and a certain amount of data is extracted from the production environment to the test environment by using an efficient sampling method, so as to minimize the impact on the production database. On the sampled database, the query time, scanned row number, execution cost and other information of a single query or an entire workload are verified under different recommended index configurations, and the index configuration with better effect is selected as the final recommended index result. The present application combines the actual scene of the database query optimizer processing query statements, utilizes the similarity between the test environment and the production environment data obtained by sampling, improves the confidence of the recommended index result, and reduces the risk of establishing indexes. BRIEF DESCRIPTION OF DRAWINGS

[0033] Figure 1 is the data collection information schematic diagram of the index recommendation involved in the present application;

[0034] Figure 2 is the model modeling schematic diagram of the index recommendation involved in the present application;

[0035] Figure 3 is the index recommendation and evaluation method architecture diagram involved in the present application. DETAILED DESCRIPTION

[0036] The application is further described in detail in connection with the following specific embodiments and drawings. The process, conditions, experimental methods and the like for implementing the application are the general knowledge and common sense in the art, and the application has no special restrictions.

[0037] Figure 1 The data collection information for index recommendation is described. The training data collected from the online source database instance includes index definitions, table schema and historical queries. In order to reduce the performance impact on the online database, all index definitions are collected first, then the schema of the table where the index is located is collected, and the SQL query statement accessing the index column is found. In addition, the number of rows in the table, the number of different values of the column in the index and other statistical information need to be collected. When collecting the statistical information of a column, since there may be no index on the column, the query optimizer cannot directly collect the relevant statistical information. In this case, full table scan or random sampling is needed to obtain the data.

[0038] Figure 2 The model modeling example of index recommendation is described, i.e. converting index recommendation into a traditional classification problem. For a table in the database, there may be multiple indexes, which are composed of one or more columns. If a SQL contains the table, the query optimizer can select these indexes to specify the execution plan of the query. Figure 2 In the example, for a given query q1, first construct single-column index candidates for each table in the query, and each column appearing in the query q1 is a single-column candidate index. Therefore, the customer table has 4 single-column candidate indexes, i.e. c_d_id, c_id, c_w_id, c_last; the warehouse table has 1 single-column candidate index w_id. According to the permutation and combination of single-column candidate indexes, two-column and three-column candidate indexes can be obtained.

[0039] For each single or multi-column candidate index, a feature vector can be generated. The feature vector encodes the SQL semantic information and data distribution characteristics of the columns in the candidate index. Since single and multi-column index candidates have different feature dimensions, a classifier needs to be built for each index width. For each candidate index, the statistical features of the table where the index is located need to be extracted, including, for example, the total number of rows in the table, the number of distinct values of the columns, and the estimated number of rows in the RANGE condition, etc. For single column candidate index c1, its feature vector contains the features defined on c1, and its statistical features. For two-column candidate index <c1, c2> and three-column candidate index <c1, c2, c3>, in addition to the features defined on <c1, c2> and <c1, c2, c3>, the features defined on each single column c1, c2 and c3 also need to be added. The feature vector of a candidate index is a kind of generalized representation, which is independent of the specific database. Therefore, the present application supports learning index creation knowledge from various source databases and applying the learned classifier to the target database. In the modeling process of the above index recommendation, false positive samples can be generated, because even if all the columns in a candidate index appear in the SQL statement, according to expert experience, this index is created, there is still a possibility that this index is not selected by the query optimizer. Since the present application does not identify these false positive samples by asking the online database instance whether the query execution uses the index. These false positive samples will cause the recommendation model to recommend some additional indexes, so a sampling-based index evaluation is used to filter the invalid indexes recommended.

[0040] For single column candidate index c1, two-column candidate index <c1, c2>, and three-column candidate index <c1, c2, c3>, the feature vectors contain the following multiple dimensions of information, respectively:

[0041]

[0042] The indexes created by technicians in the historical data are regarded as expert experience, so the feature vector is labeled using the real existing index record. If the candidate index appears in the index record, the feature vector is labeled as a positive sample, otherwise as a negative sample, that is, if the candidate index is a real created index, its label is +1, otherwise its label is -1. Figure 2 The single column index I(c_w_id) and the three-column index I(c_d_id, c_id, c_last) exist on the customer table, so the corresponding feature vector label is +1, and the other feature vector labels are -1.

[0043] The present application adopts a tree-based lightweight classification model XGBoost to obtain better interpretability in learning tasks and similar accuracy to DNN models. Since most indexes in actual production database instances have no more than three columns. The present application trains three classification models for recommending single-column, two-column and three-column indexes respectively. Index creation cases come from a large number of online databases, which are divided into training and test data sets at the database level, and all index cases from the same database instance only appear in the training data or test data. The measurement of model accuracy is used to guide the model hyperparameter tuning and model selection in the training process. At the feature vector level, the accuracy of the model is measured by precision, recall and F1-score. According to the prediction results of the feature vector, it is calculated which columns are recommended as indexes. By comparing the recommended index set with the real index record of the historical data, the precision, recall and F1-score at the index level are calculated.

[0044] The sampling-based index evaluation confirms whether the performance of slow queries can be improved if these indexes are added, and reports whether the added indexes cause performance regression of other queries, based on the index configuration given by the expert experience-driven index recommendation. The test database is obtained by random sampling of the online database, and the test database only needs to contain the tables accessed by slow queries. The sampling-based index evaluation method performs full scan on the tables related to the query in the online database, and randomly determines whether to dump the row according to the user-defined sampling rate ratio. The sampled data is inserted into the test database through multiple threads, and the indexes on the related library tables are synchronized to the test database. On the test database, it is checked whether the query plan changes before and after creating the recommended index, i.e. whether the recommended index is used. Since the query optimizer may incorrectly estimate the query cost and not use the recommended index. Therefore, the "forceindex" is used to force the optimizer to use the recommended index, execute the query and observe the effect. The query performance improvement is measured by measuring the execution time, scanned row number and query cost of this query before and after adding the recommended index. If the optimization of execution time exceeds the threshold specified by the user, the recommended index is finally recommended, otherwise it is discarded.

[0045] Figure 3 The architecture diagram of the index recommendation and evaluation method is described. The deployed online service database instance is the training database, from which index creation cases are collected, including table information, index information and query information. The index creation cases are converted into features, and single-column index models and multi-column index models are trained. When slow queries occur in the online business database, they are input into the trained classification model to give index recommendation results online, and the recommended results are filtered and selected on the test database through data sampling, and finally the index creation statement is given.

[0046] The scope of protection of the present application is not limited to the above embodiments. Variations and improvements based on the spirit of the present application, which can be conceived by those skilled in the art, are included in the present application and are protected by the scope of the claims attached herewith.

Claims

1. A learning-based index recommendation and evaluation method, characterized in that, The method comprises expert experience driven index recommendation and sampling based index evaluation; the expert experience driven index recommendation realizes index recommendation based on index establishment experience in historical data; the sampling based index evaluation realizes index evaluation on a sampled database to give a recommended result with high confidence; the specific steps are as follows: Step 1: expert experience driven index recommendation A1: extracting table information and index information of a corresponding database according to user input database connection information; the table information is table name, column name, Cardinality and table row number; the index information is index corresponding column name; A2: calling a SQL parsing program to obtain SQL information according to a user provided structured query language (SQL) statement; the SQL information comprises extracted single column information and multi-column relationship information; A3: extracting and splicing the table information in step A1 and the single column information in step A2 as single column features, taking the index information in step A1 as labels of the single column features, and then training a single column index model; A4: constructing a hash table according to the single column features, extracting the multi-column relationship information in step A2, splicing the multi-column relationship information with the single column features in step A3 as multi-column features, taking the index information in step A1 as labels of the multi-column features, and then training a multi-column index model; A5: calling the SQL parsing program to extract features of the user input SQL statement, splicing the extracted features with saved database table information to generate single column and multi-column features, and inputting the single column index model in step A3 and the multi-column index model in step A4 to perform index recommendation; A6: outputting the index recommendation result in a JSON file, which comprises a SQL statement and recommended indexes; Step 2: sampling based index evaluation B1: parsing the JSON file of the index recommendation result to obtain all data table schemas involved in each query, including primary key, foreign key and index information, and creating a test environment library table; B2: sampling and dumping data from an online database according to a user set proportion, and importing data tables appearing in the query into the test database; B3: traversing all queries under different recommended index configurations, obtaining evaluation indexes before and after establishing recommended indexes under the data scale of the verification environment, and the evaluation indexes comprise execution time, scanned row number and query cost; B4: outputting the index evaluation result in a JSON file, which comprises a SQL statement, evaluation indexes and evaluation indexes; wherein: The step A4 specifically comprises the following steps: A41: constructing a hash table according to the single column features, taking the single column feature identifier as the Key value and the corresponding single column feature as the Value value; A42: combining any multi-columns appearing in the same SQL of the same Table, splicing the column corresponding single column feature identifiers to construct preliminary multi-column features, and naming the preliminary multi-column feature set as fn_start_train; A43: Traverse fn_start_train, extract inter-column relationship information according to the single-column feature identifiers contained in each preliminary multi-column feature in fn_start_train, and put it into the inter-column relationship feature set fn_merge_train; A44: Horizontally splice fn_start_train and fn_merge_train according to the single-column feature identifiers, and take the corresponding index information as the label to obtain the final multi-column feature fn_end_train; A45: Use the multi-column feature for model training to train a multi-column index model.

2. The index recommendation and evaluation method of claim 1, wherein, The step A3 specifically comprises the following steps: A31: Extract the data type of the column; A32: Extract the position of the column in SQL; A33: Extract the conditional expression where the column is located; A34: Extract the function information where the column is located; A35: Number and splice all the extracted features, and take the index information as the label; A36: Use the single-column feature for model training to train a single-column index model.

Images