Published on

MySQL 索引优化实战：从原理到 EXPLAIN

Authors
  • avatar
    Name
    Allen Wang
    Twitter

MySQL 索引优化实战：从原理到 EXPLAIN

索引不是“建了就快”，而是“建对了才快”。

这篇文章给你一个新手也能落地的路径：

  1. 先理解索引为什么快
  2. 再掌握哪些 SQL 能吃上索引
  3. 最后用 EXPLAIN 验证优化是否有效

目录

1 - 索引为什么快

没有索引时，MySQL 往往只能全表扫描：

SQL
SELECT id, username FROM users WHERE username = 'john';

如果 username 没有索引，优化器可能读取大量无关行。

有索引时：

SQL
CREATE INDEX idx_users_username ON users(username);

优化器可通过索引快速定位目标记录，再按需回表。

2 - B+Tree 索引核心机制

MySQL InnoDB 默认使用 B+Tree 索引（常口语称 B-Tree）。

关键点：

  • 非叶子节点：仅存键与子指针
  • 叶子节点：存键值与记录定位信息
  • 叶子链表：支持范围查询与排序扫描

所以这类查询通常很友好：

  • 等值：=
  • 范围：>, <, BETWEEN
  • 排序：ORDER BY（与索引顺序一致时）

3 - 最左前缀与复合索引

给定复合索引：

SQL
CREATE INDEX idx_user_status_time ON orders(user_id, status, created_at);

可高效利用的常见写法：

SQL
-- 命中 (user_id)
SELECT * FROM orders WHERE user_id = 1001;

-- 命中 (user_id, status)
SELECT * FROM orders WHERE user_id = 1001 AND status = 'paid';

-- 命中 (user_id, status, created_at)
SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid' AND created_at >= '2025-01-01';

容易失效的写法：

SQL
-- 跳过了最左列 user_id，通常无法高效命中该复合索引
SELECT * FROM orders WHERE status = 'paid';

4 - 回表、覆盖索引与索引下推

4.1 回表

如果二级索引不包含查询所需列，MySQL 需要先查索引再回主键索引取完整行。

4.2 覆盖索引

当查询列都在同一个索引内，可避免回表：

SQL
CREATE INDEX idx_users_name_age ON users(name, age);

SELECT age FROM users WHERE name = 'John';

4.3 索引下推（ICP）

对部分范围条件，MySQL 可在索引层提前过滤，减少回表次数。

5 - EXPLAIN 实战判读

SQL
EXPLAIN SELECT age FROM users WHERE name = 'John';

重点看：

  • type：访问方式（const / ref / range 通常优于 ALL）
  • key：实际使用的索引
  • rows：预估扫描行数
  • Extra：如 Using index（覆盖索引）、Using filesort（额外排序）

一个实用目标：

  • type 从 ALL 变为 ref/range
  • rows 显著下降
  • Extra 去掉 Using filesort / Using temporary（若可行）

6 - 常见索引失效场景

  1. 对索引列做函数操作
SQL
-- 可能失效
SELECT * FROM users WHERE DATE(created_at) = '2025-01-01';

-- 改写为范围，通常可命中
SELECT * FROM users
WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02';
  1. 隐式类型转换
SQL
-- phone 是 varchar，传 int 可能导致转换
SELECT * FROM users WHERE phone = 13800138000;
  1. 前导通配符
SQL
-- '%abc' 一般无法利用普通 B+Tree 前缀能力
SELECT * FROM docs WHERE title LIKE '%mysql';

7 - 交互式可视化

8 - 可执行优化清单

  • 核心慢 SQL 是否已抓取（慢查询日志 / APM）
  • 是否先 EXPLAIN 再改索引
  • 复合索引列顺序是否按过滤选择性与查询模式设计
  • 是否确认“读提升”与“写放大”之间的权衡
  • 是否定期清理无用/重复索引

如果你刚开始做性能优化，建议先从“Top 5 慢 SQL + EXPLAIN 对比”入手，收益通常非常直接。

View on GitHub