面试题：MySQL中如何发现未使用的索引

Question

请阐述在MySQL数据库中，有哪些常见方法可以用来发现未使用的索引？

Answer 1

1. 使用EXPLAIN关键字：
   • EXPLAIN语句用于分析SELECT查询，它会展示查询优化器如何执行查询。例如对于查询SELECT * FROM your_table;，可以使用EXPLAIN SELECT * FROM your_table;。在结果中，如果某个索引没有在key字段中出现，可能表示该索引未被使用。不过这种方式需要对每个查询手动分析，适用于少量特定查询场景。
2. 通过SHOW STATUS查看索引使用状态：
   • 执行SHOW STATUS LIKE 'Handler_read%';，这会显示一系列与索引读取相关的状态变量。Handler_read_rnd_next的值较高，而Handler_read_key的值相对较低，可能暗示存在未使用的索引。Handler_read_rnd_next表示按数据文件顺序读取下一行的请求数，通常意味着全表扫描；Handler_read_key表示通过索引读取一行的请求数。如果Handler_read_rnd_next过高，说明索引可能未被有效利用。
3. 利用performance_schema：
   • MySQL的performance_schema库提供了关于服务器性能的详细信息。可以通过查询performance_schema.table_io_waits_summary_by_index_usage表来查看索引的使用情况。例如：

   SELECT object_schema, object_name, index_name, count_star, sum_timer_wait
   FROM performance_schema.table_io_waits_summary_by_index_usage
   WHERE index_name IS NOT NULL AND count_star = 0;
   

   上述查询会列出那些一次都未被使用过的索引，count_star为0表示该索引从未被使用，sum_timer_wait表示等待该索引操作的总时间。
4. 使用pt-index-usage工具（Percona Toolkit的一部分）：
   • 安装Percona Toolkit后，使用pt - index - usage工具。它会分析MySQL的慢查询日志，从而找出未使用的索引。例如运行pt - index - usage --user=your_user --password=your_password --host=your_host slow - query.log，它会解析慢查询日志并报告哪些索引未被使用，这种方式适用于基于慢查询场景发现未使用索引。