MySQL索引的维护与优化——查找重复及冗余索引

时间：2022-05-01 23:22:45

方法一：通过MySQL的information_schema数据库 查找重复与冗余索引

SELECT a.table_schema AS '数据库', a.table_name AS '表名', a.index_name AS '索引1', b.index_name AS '索引2', a.column_name AS '重复列名'

FROM information_schema.statistics a

    JOIN statistics b ON a.table_schema = b.table_schema

        AND a.table_name = b.table_name

        AND a.seq_in_index = b.seq_in_index

        AND a.column_name = b.column_name

WHERE a.seq_in_index = 1

    AND a.index_name != b.index_name

方法二：通过工具

用pt-duplicate-key-checker 工具检查重复及冗余索引

使用方法 pt-duplicate-key-checker -hxxx -uxxx -pxxx

查找重复与冗余索引

参考慕课网：http://www.imooc.com/video/3998

相关文章