面试题：MySQL中如何识别重复索引

Question

在MySQL数据库中，描述一下你通过什么方法或工具能够识别出哪些索引属于重复索引，例如从数据库的哪些系统表或者命令来查看相关信息。

Answer 1

1. 使用SHOW INDEX命令：
   • 对于每个表，可以使用SHOW INDEX FROM table_name;命令。该命令会列出指定表的所有索引信息。
   • 手动分析输出结果，对比不同索引的列顺序和包含的列，如果两个或多个索引包含相同的列，且顺序也相同（或其中一个索引列是另一个索引列的前缀），那么可能存在重复索引。例如，若有索引idx_col1_col2（列col1在前，col2在后）和idx_col1（仅包含col1列），idx_col1就是idx_col1_col2的前缀索引，这种情况可能需要进一步评估是否可删除idx_col1。
2. 查询系统表information_schema.statistics：
   • information_schema.statistics表存储了MySQL中所有数据库表的索引信息。
   • 可以通过如下查询来找出可能的重复索引：

SELECT 
    table_schema, 
    table_name, 
    index_name, 
    group_concat(column_name ORDER BY seq_in_index ASC SEPARATOR ',') AS columns
FROM 
    information_schema.statistics
GROUP BY 
    table_schema, table_name, index_name
HAVING 
    COUNT(*) > 1;

• 这个查询会将每个表的索引按表名和索引名分组，并将每个索引包含的列连接成一个字符串。如果某个组内的索引数量大于1，就意味着可能存在重复索引。后续可以进一步分析这些组内的索引具体情况来确认是否是真正的重复索引。

3. 使用工具：
   • pt-duplicate-key-checker：这是Percona Toolkit中的一个工具。
     • 安装Percona Toolkit后，使用命令pt-duplicate-key-checker --user=username --password=password --host=host。
     • 它会连接到MySQL数据库，自动分析并找出数据库中所有可能的重复索引和冗余索引，并给出详细的报告，指出哪些索引可能是重复的以及相关的表和索引信息。