Index intelligent recommendation method based on mysql slow query log word frequency analysis

Abstract

The invention relates to an index intelligent recommendation method based on mysql slow query log word frequency analysis. The method comprises the steps of adopting a new design strategy, slowly querying a log based on mysql; carrying out pattern matching on the recorded query statements, calculating the occurrence frequency of each query field and a query field combination, taking the query fields as nodes of the undirected graph and the field combination as edges of the graph, and carrying out improved breadth-first traversal on the generated undirected graph so as to search the longest subsequence as an optimal index; therefore, analysis is carried out based on slow query sql, and the obtained index is more objective and effective; and the combined index with the maximum coverage rateis searched as much as possible through the design method, so that the situation of multiple single-column indexes can be avoided as much as possible.

Description

technical field [0001] The invention relates to an index intelligent recommendation method based on word frequency analysis of mysql slow query logs, and belongs to the technical field of data index recommendation. Background technique [0002] An index is a separate, physical storage structure that sorts the values ​​of one or more columns in a database table, and provides pointers to the data values ​​stored in the specified columns of the table. A good index can greatly reduce the query time of SQL , Improve the response speed of the program. [0003] The current creation of indexes mainly depends on the experience of R&D engineers. When implementing system functions, R&D engineers use the sql in the project and past experience to create indexes at the same time when building tables, but there are two main problems here: 1. Due to the lack of actual running data of the project, the creation of indexes is subjective, and sometimes it cannot be well matched with the actual...

AI Technical Summary

Problems solved by technology

[0003] The current creation of indexes mainly depends on the experience of R&D engineers. When implementing system functions, R&D engineers use the sql in the project and past experience to create indexes at the same time when building tables, but there are two main problems here: 1. Due to the lack of actual running data of the project, the creation of indexes is subjective, and sometimes it cannot match the actual SQL execution well; 2. It is biased towards single-column indexes, and cannot give full play to the advantages of combined indexes

Images