Index Evaluation For Slow Query Optimization

Adding appropriate indexes for slow queries is an effective way to improve query performance.It is mainly selected manually through the expert experience of database administrators and other technicians,or automatically recommended by algorithms based on rules,costs,and learning.Regardless of the method,it is not considered to evaluate the index before it goes online to the real business system,and its effectiveness and security cannot be guaranteed.This is because the relational database generally uses the cost model as the main basis for the selection of the execution plan,but it only rudely costizes various predefined operations,and the statistical information obtained by the query optimizer is not necessarily due to data changes,data skew and other factors.precise.The above reasons together lead to the fact that the optimization effect of ”theoretically excellent indexes” on queries in actual execution is unknown.Useless indexes will bring additional update and storage costs to the database,and even more serious will lead to this query or other associations.The performance of the query is further degraded,which is very dangerous in a production environment.As a follow-up to index recommendation,index evaluation is to verify the effect of the index before it goes online to the production environment,and it is also one of the key steps to help automate the whole process of index optimization and slow query.Index evaluation needs to obtain the actual query execution information under different index configurations,so it relies on an offline test environment.The design characteristics of the query optimizer,the test environment needs to have relatively consistent data distribution and sufficient data volume with the online,how to accurately and efficiently generate the test environment is the focus of index evaluation.In addition,index recommendation is oriented to a single query or workload.For two different evaluation objectives,how to design a reasonable evaluation index to select an optimal set of indexes within the constraints is also one of the problems to be solved in the evaluation work.Therefore,this paper proposes an index evaluation method for slow query optimization.For the recommended index given by manual or algorithm,the test environment is accurately and efficiently generated based on random sampling and data simulation.The recommended index is evaluated in multiple dimensions to verify its optimization effect on query performance,and an evaluation index with high confidence is given to ensure the validity and security of the index after it goes online.In addition,since the impact of indexes on queries can be reflected in the execution plan,this paper analyzes and visualizes the output information of the query execution plan,so that the effect of evaluating the index can be displayed more intuitively.The work of this paper can be summarized as the following points:(1)Designed index evaluation system Index Tuner to verify the validity of the recommended index:Use two methods of random sampling or data simulation to generate the test environment,in which random sampling uses the block scan idea to sample and merge partitions from the production environment according to the Inno DB data organization form;improve the test by changing the timing of index synchronization,row merging,explicit transactions,etc.Environmental data import efficiency;design a variety of evaluation indicators from physical and logical perspectives,select the optimal index based on score calculation for a single query,and design a greedy algorithm for workloads to obtain an index set with better overall performance within the constraints of storage cost.Based on the test environment,the query evaluation information before and after adding the recommended index,and finally the index evaluation result is given.(2)Developed data simulation and generation tool Data Simulator to generate test environment for index evaluation method based on data simulation:In order to further reduce the impact of random sampling methods on online database intensive I/O and lock contention and the dependence on network transmission,according to the characteristics of the query optimizer that pays attention to statistical information rather than specific data content when selecting an execution plan,online The database designs reasonable data distribution information collection indicators and collection methods,and designs corresponding random algorithms for different My SQL data types,simulates the generation of data similar to online distribution in the test environment,and conducts index evaluation based on the simulated data.(3)Developed Query Viewer based on EXPLAIN to realize query information prompt and visualization:In order to solve the problems of unclear meaning of the output information of My SQL EXPLAIN function and confusion of execution plan representation,help users to intuitively understand the impact of indexes on queries,prompt related fields of indexes in query information in text format,and design multiple analysis of execution plans in JSON format.The function performs tree visualization,which more conveniently displays the execution order and information of each sub-operation of the query.Based on the relational database My SQL,this paper implements the above solutions and technologies in the form of third-party tools,and through related experiments,it is verified that the index evaluation system Index Tuner can use random sampling or data simulation to generate the test environment correctly and efficiently.Index selection algorithms and evaluation results for queries and workloads,while filtering recommended indexes that have no effect or side effects,while maintaining the ability of recommended indexes to optimize slow queries in the production environment.In addition,the experiment shows Query Viewer’s visualization of query information prompts and execution plans,helping users to intuitively understand the impact and optimization of indexes on slow queries.