An intelligent index recommendation method based on word frequency analysis of MySQL slow query log

Abstract

The invention relates to an index intelligent recommendation method based on mysql slow query log word frequency analysis, adopts a new design strategy, and based on mysql slow query log, performs pattern matching on recorded query statements, calculates the appearance of each query field and the combination of query fields The number of times, the query field is used as the node of the undirected graph, and the field combination is used as the edge of the graph, and an improved breadth-first traversal is performed on the generated undirected graph to find the longest subsequence as the optimal index; so based on the slow query sql for analysis, The obtained index is more objective and effective; and through the designed method, try to find the combined index with the largest coverage rate, and try to avoid the situation of multiple single-column indexes.

Description

technical field [0001] The invention relates to an index intelligent recommendation method based on word frequency analysis of MySQL slow query log, 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, providing pointers to the data values ​​stored in the specified columns of the table. A good index can greatly reduce the query time of SQL , to improve the responsiveness of the program. [0003] At present, creating indexes mainly depends on the experience of R&D engineers. When R&D engineers implement system functions, they create indexes at the same time when building tables through sql in the project and past experience, but there are two main problems here: 1. Lack of actual operation data of the project, the creation of the index is subjective, and sometimes it cannot be well matched with the actual SQL executi...

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