MySQL面试中如何高效回答索引优化相关问题？

本文共计909个文字，预计阅读时间需要4分钟。

不是索引本身变慢，而是MySQL在满足查询时被强迫忽略覆盖索引（Covering Index），转而回表查询。只要SELECT的字段没有全部包含在索引中，比如只有id字段在索引中，也可能会触发回表查询。

• 复合索引 (a, b, c) 可以高效响应 SELECT a, b FROM t WHERE a = 1 AND b > 10（覆盖索引）
• 但 SELECT a, b, d FROM t WHERE a = 1 就必须回表取 d，即使 d 是主键——因为 d 不在索引列中
• SELECT * 几乎必然导致回表，尤其当表有大字段（TEXT、BLOB）时，I/O 成倍增加

LIKE '%abc' 为什么用不上索引

前导通配符破坏了 B+ 树的有序查找路径。索引本质是按字典序存储的有序结构，LIKE 'abc%' 可以定位到 abc 开头的范围，但 '%abc' 需要扫描全部叶子节点匹配后缀，等价于全索引扫描。

• LIKE 'abc%' → 走索引（range）
• LIKE '%abc' → 不走索引（除非全文索引或倒排索引扩展）
• LIKE '%abc%' → 同样不走索引；MySQL 8.0+ 对某些 utf8mb4 排序规则支持函数索引：CREATE INDEX idx_name ON t ((LOWER(name)))，但不能直接解决后缀模糊

ORDER BY 和 GROUP BY 怎么避免 Using filesort

关键看排序字段是否构成索引的最左前缀，且方向一致。MySQL 只能利用索引的天然顺序，无法“跳着”用。

• 索引 (a, b, c) 支持：ORDER BY a, b、ORDER BY a DESC, b DESC（方向统一）
• 不支持：ORDER BY a ASC, b DESC（混合方向，8.0 前基本失效；8.0+ 对单列索引支持混合，但联合索引仍受限）
• GROUP BY a, b 同理；若写成 GROUP BY b, a，即使索引是 (a,b)，也无法避免 filesort
• 注意：如果 WHERE 条件已用掉部分索引列，剩余列仍需连续且顺序匹配才能用于排序，例如 WHERE a = 1 ORDER BY b, c 可用 (a,b,c) 索引，但 WHERE b = 1 ORDER BY a, c 就不行

哪些场景下索引会“失效”但执行计划仍显示 type: ref

执行计划里的 type 只反映访问方式（如是否走索引查找），不等于实际高效。常见假象：

• 隐式类型转换：WHERE phone = 13800138000（phone 是 VARCHAR）→ MySQL 转成数字比较，索引失效，但可能仍显示 ref（因优化器误判）
• 对索引列使用函数：WHERE YEAR(create_time) = 2023 → 索引失效；应改写为 create_time BETWEEN '2023-01-01' AND '2023-12-31'
• 索引列参与运算：WHERE score * 2 > 100 → 失效；应改为 score > 50
• 统计信息过期：ANALYZE TABLE 没跑，优化器基于错误行数估算选错索引，此时 EXPLAIN 显示走了索引，但实际性能差

真正判断是否生效，得看 key 列是否非 NULL、rows 是否接近真实过滤量、以及 Extra 里有没有 Using index condition 或 Using where。