面试题：MySQL 中使用索引一定有效吗？如何排查索引效果？

1. MySQL 中使用索引一定有效吗？

不一定。虽然索引可以加速查询，但在某些情况下，索引可能不会生效，甚至可能降低性能。以下是一些索引可能无效的情况：

• 数据量小：当表中的数据量非常少时，MySQL 可能会选择全表扫描而不是使用索引，因为全表扫描的成本更低。
• 索引选择性低：如果索引列的值重复率很高（如性别列），MySQL 可能不会使用索引，因为索引的效果不明显。
• 查询条件不符合索引顺序：如果查询条件中的列顺序与复合索引的顺序不一致，索引可能不会被使用。
• 使用了函数或表达式：如果在查询条件中对索引列使用了函数或表达式（如 WHERE YEAR(column) = 2023），索引可能不会被使用。
• OR 条件：如果查询条件中有 OR 连接多个条件，且这些条件涉及的列没有全部被索引，索引可能不会被使用。
• LIKE 查询以通配符开头：如 LIKE '%value'，这种查询无法使用索引。
• 索引列类型不匹配：如果查询条件中的数据类型与索引列的数据类型不匹配，索引可能不会被使用。

2. 如何排查索引效果？

可以通过以下方法来排查索引的使用效果：

• EXPLAIN 命令：使用 EXPLAIN 命令可以查看 MySQL 如何执行查询，包括是否使用了索引、使用了哪些索引、扫描的行数等信息。EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';在 EXPLAIN 的输出中，关注以下字段：
  • type：表示访问类型，如 ALL（全表扫描）、index（索引扫描）、range（范围扫描）等。
  • key：表示实际使用的索引。
  • rows：表示扫描的行数，行数越少，性能越好。
  • Extra：包含额外的信息，如 Using where、Using index 等。
• SHOW INDEX 命令：查看表的索引信息，了解哪些列被索引。SHOW INDEX FROM table_name;
• 慢查询日志：启用慢查询日志，记录执行时间超过指定阈值的查询，分析这些查询是否没有使用索引或索引效果不佳。

  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 1;  -- 设置慢查询阈值为1秒

• 索引选择性分析：计算索引的选择性，选择性越高，索引效果越好。选择性可以通过以下公式计算：SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;
  选择性越接近 1，索引效果越好。
• 强制使用索引：在某些情况下，可以使用 FORCE INDEX 强制 MySQL 使用某个索引，观察查询性能是否有提升。SELECT * FROM table_name FORCE INDEX (index_name) WHERE column_name = 'value';

总结

索引并不总是有效的，具体效果取决于查询条件、数据分布、索引设计等因素。通过 EXPLAIN、慢查询日志、索引选择性分析等工具和方法，可以有效地排查索引的使用效果，并优化查询性能。