面试题：MySQL中如何判断一个索引是否合适

Question

在MySQL数据库场景下，当面对一张具有多个字段且数据量较大的表，描述一下判断已创建索引是否合适的方法和常用工具。

Answer 1

判断已创建索引是否合适的方法：

1. 查询性能分析：
   • 慢查询日志：开启MySQL的慢查询日志，它会记录执行时间超过指定阈值的SQL语句。分析这些慢查询语句，查看是否因为索引缺失或不当使用导致查询缓慢。例如，如果查询条件中的字段没有索引，全表扫描可能会使查询时间变长。
   • 执行计划：使用EXPLAIN关键字分析SQL查询的执行计划。关注key字段，若显示为NULL，可能表示未使用索引；type字段，如ALL代表全表扫描，而index、range等则表示使用了索引。例如EXPLAIN SELECT * FROM large_table WHERE column1 = 'value';，根据结果判断索引使用情况。
2. 索引覆盖：
   • 检查查询中所需的字段是否都包含在索引中。如果索引能覆盖查询所需的所有字段，查询就无需回表操作，性能会大幅提升。例如CREATE INDEX idx_column1_column2 ON large_table(column1, column2);，若查询为SELECT column1, column2 FROM large_table WHERE column1 = 'value';，就可以利用索引覆盖，减少磁盘I/O。
3. 索引选择性：
   • 计算索引的选择性，即索引列中不同值的数量与表中记录数的比值。选择性越高，索引的效率越高。例如，性别字段只有两个值，其选择性低，不适合单独创建索引；而ID字段值唯一，选择性高，适合创建索引。可以通过SELECT COUNT(DISTINCT column1) / COUNT(*) FROM large_table;来计算选择性。

常用工具：

1. MySQL自带工具：
   • SHOW INDEX：使用SHOW INDEX FROM table_name;命令查看表上的索引信息，包括索引名称、字段、是否唯一等。例如SHOW INDEX FROM large_table;，可以直观了解表上已有的索引结构。
   • Performance Schema：MySQL 5.5及以上版本提供的性能架构，可以收集关于服务器执行情况的详细信息，包括索引使用情况。通过查询相关的性能架构表，如performance_schema.table_io_waits_summary_by_index_usage，可以了解哪些索引被频繁使用，哪些很少被使用。
2. 第三方工具：
   • pt-query-digest：Percona Toolkit中的工具，可分析MySQL的慢查询日志，生成详细的报告，指出查询中可能存在的性能问题，包括索引使用不当的情况。例如通过pt-query-digest slow-query.log分析慢查询日志，获取优化建议。