MySQL 索引优化实战：覆盖索引、最左前缀与失效场景

索引优化真正要解决的不是“加不加索引”，而是三件事：让查询能走索引、让索引能覆盖查询、别用写法把索引废掉——覆盖索引消除回表，最左前缀决定联合索引能不能被命中，失效场景则是把索引白建的常见原因。下面按“先看执行计划 → 覆盖索引 → 最左前缀 → 失效场景 → 落地清单”的顺序讲，示例表以社区论坛常见的帖子表、回复表为例（Clara BBS 这类系统要求 MySQL 5.7+，本文结论同样适用）。

一切从 EXPLAIN 开始，别凭感觉加索引

结论：没有 EXPLAIN 验证的索引优化都是猜测，看四个字段就够了：type、key、rows、Extra。

• `type`：访问类型，`const/eq_ref/ref/range` 算好，`index` 是扫全索引，`ALL` 是全表扫描。
• `key`：实际用上的索引，如果显示 NULL，说明白建了。
• `rows`：预估扫描行数，是判断索引好坏最直观的数字。
• `Extra`：信息量最大。`Using index` = 覆盖索引，`Using index condition` = 索引下推（ICP），`Using where` = 回表后再过滤，`Using filesort` / `Using temporary` = 排序或分组没走索引。

正确姿势是先开慢查询日志（`slow_query_log=ON`、`long_query_time=1`）捞出慢 SQL，再逐条 EXPLAIN，而不是给每个 WHERE 列都建一根索引。

覆盖索引：让查询彻底不回表

结论：当 SELECT 需要的所有列都存在于同一个索引中时，MySQL 直接在索引里拿到结果，省掉回表，这是性价比最高的一类优化。

InnoDB 的二级索引叶子节点只存索引列 + 主键，普通查询拿到主键后还要回聚簇索引取整行。举例：

-- 高频查询：某用户的帖子列表
SELECT id, title, created_at FROM posts
WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;

建 `INDEX idx_user_time (user_id, created_at, title)`，EXPLAIN 的 Extra 会出现 `Using index`。这条 SQL 只在索引上完成定位、排序、取值，不回表。

注意点有两个：一是覆盖索引会让索引变宽，写入和更新成本上升，只对高频、列少的查询做；二是 `SELECT *` 天然无法覆盖，把不需要的列去掉往往比加索引更有效。

最左前缀：联合索引是“从左往右连续匹配”

结论：联合索引 `(a, b, c)` 能被 `a`、`a+b`、`a+b+c` 命中，但单独查 `b` 或 `c` 用不上它——这是联合索引最容易被误解的规则。

INDEX idx (status, board_id, created_at)

WHERE status = 1                          -- 命中
WHERE status = 1 AND board_id = 5         -- 命中
WHERE board_id = 5                        -- 不命中（缺最左列）

还有一个更隐蔽的坑：范围查询会截断后面的列。`WHERE status = 1 AND board_id > 5 AND created_at > '2024-01-01'` 中，`created_at` 只能靠 ICP 做过滤，不能用于索引定位。所以建联合索引的顺序一般是：等值条件列 → 排序列 → 范围条件列。

排序列也要顺着最左前缀来。`ORDER BY created_at DESC` 想免掉 `Using filesort`，`created_at` 必须在索引里紧跟在等值列之后，且排序方向与索引一致（MySQL 8.0 支持真正的降序索引，5.7 的 DESC 会被忽略）。另外 MySQL 8.0.13+ 有索引跳跃扫描（Index Skip Scan），能在特定条件下跳过最左列，但限制不少，不要指望它救场。

索引失效的典型场景：多半是写法的问题

结论：索引失效通常不是优化器“犯懒”，而是 SQL 写法让 B+ 树没法用来定位，常见的有 8 种。

1. 列上做函数或运算：`WHERE DATE(created_at) = '2024-06-01'` 失效，改成 `created_at >= '2024-06-01' AND created_at < '2024-06-02'`。
2. 隐式类型转换：`phone` 是 varchar，写 `WHERE phone = 13800000000` 会把列转成数字比较，索引失效；加引号即可。
3. 前导通配符：`LIKE '%关键词%'` 无法定位，`LIKE '关键词%'` 可以；全文检索需求另想办法。
4. 不满足最左前缀：见上一节。
5. OR 连接了非索引列：整条语句可能退化为全表，可用 `UNION ALL` 拆成两条走索引的查询。
6. `!=`、`<>`、`NOT IN`、`NOT LIKE`：选择性高时优化器常直接放弃索引。
7. 区分度太低的列单独建索引：如状态位、性别，扫描成本接近全表，不如放进联合索引当过滤列。
8. 统计信息不准：大批量增删后优化器可能选错执行计划，执行一次 `ANALYZE TABLE` 通常能纠正。

排查时优先看 `key` 是不是 NULL、`type` 是不是 ALL，再对照上面 8 条逐一对号入座。

落地清单：按这个顺序做就不会乱

结论：索引优化的正确顺序是“先定位慢查询，再改写法，最后才加索引”，三者顺序颠倒会白干很多活。

实操五步：① 开慢查询日志，捞出 TOP N 慢 SQL；② 对每条 SQL 跑 EXPLAIN，看 type/key/rows/Extra；③ 先试着改写 SQL（去函数、补引号、去 `SELECT *`），很多问题到这一步就解决了；④ 确实需要索引时，按“等值列 → 排序列 → 范围列”设计联合索引，高频查询优先做成覆盖索引；⑤ 上线后复查 EXPLAIN，并留意写入性能——索引不是越多越好，每多一根都是写操作的负担。

回到开头那句：覆盖索引管“少回表”，最左前缀管“用得上”，失效场景管“别白建”。这三件事做对了，大部分 MySQL 慢查询都能在不加机器的情况下解决。