索引与查询优化

查询慢，先从索引查起

SQL 语法在上一篇《SQL 语法详解》讲过，这篇解决的是「会写但跑得慢」的问题：慢查询基本都是索引没建对，而不是数据库本身慢。先学会设计索引，再学会用 EXPLAIN 验证优化器到底走没走索引，最后搞清楚事务隔离级别对查询行为的影响。

索引设计原则

索引是查询性能的命门，但不是越多越好：每个索引都要占空间、拖慢写入、增加优化器选错的概率。设计时守住四条原则。

原则一：最左前缀

联合索引 (a, b, c) 相当于建了 (a)、(a, b)、(a, b, c) 三个索引，但不能跳过 a 直接用 b：

-- 索引 idx_abc (a, b, c)
SELECT * FROM t WHERE a = 1;                    -- 走索引
SELECT * FROM t WHERE a = 1 AND b = 2;          -- 走索引
SELECT * FROM t WHERE a = 1 AND c = 3;          -- 只用到 a，c 用不上
SELECT * FROM t WHERE b = 2 AND c = 3;          -- 不走索引（全表扫）
SELECT * FROM t WHERE a = 1 AND b > 2 AND c = 3; -- b 是范围，c 用不上

所以联合索引的列顺序很关键：等值条件在前、范围条件在后、排序列紧跟等值列。

原则二：区分度（选择性）

区分度 = 不重复的值数 / 总行数。越接近 1 越好，说明这个列能把数据分得很开：

-- 看某列的区分度，越接近 1 越适合建索引
SELECT COUNT(DISTINCT status) / COUNT(*) AS status_sel,
       COUNT(DISTINCT user_id) / COUNT(*) AS user_sel
FROM orders;

status 只有 5 个值，区分度 0.00001，单独建索引意义不大（优化器大概率不用）。但如果业务 99% 的查询都是「查待支付订单」，那它反而是个好索引——区分度是参考，实际查询模式才是决定性的。

原则三：覆盖索引

查询的列全在索引里就免回表：

-- 索引 idx_user_created (user_id, created_at)
SELECT user_id, created_at FROM orders WHERE user_id = 1001;
-- Extra 显示 Using index，说明是覆盖索引

SELECT user_id, created_at, amount FROM orders WHERE user_id = 1001;
-- 多了 amount，必须回表

给高频查询补齐联合索引的列，代价是索引变大，收益是回表归零。

原则四：别让索引用不上

以下几件事会让索引直接失效，是最常见的「有索引还是慢」原因：

写法	问题	改法
WHERE DATE(created_at) = '2024-01-01'	列上套函数	WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
WHERE user_id = '1001'（列是 BIGINT）	隐式类型转换	参数类型和列类型一致
WHERE order_no LIKE '%abc'	前导通配符	改成 LIKE 'abc%'，或用全文索引
WHERE a = 1 OR b = 2	OR 两边有一边没索引	都建索引，或改 UNION ALL
WHERE status != 0	不等号通常不划算	改成枚举范围 status IN (1, 2, 3)

EXPLAIN：看懂执行计划

EXPLAIN 是 SQL 调优的第一工具。一条查询加上它，优化器的决策就摊开了：

EXPLAIN SELECT o.order_no, o.amount
FROM orders o
WHERE o.user_id = 1001 AND o.status = 1
ORDER BY o.created_at DESC
LIMIT 10;

+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys     | key               | key_len | ref         | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-----------------------------+
|  1 | SIMPLE      | o     | NULL       | ref  | idx_user_created  | idx_user_created  | 8       | const       |   42 |    10.00 | Using where; Using filesort |
+----+-------------+-------+------------+------+-------------------+-------------------+---------+-------------+------+----------+-----------------------------+

字段不要求全记住，盯住四个：

type（访问类型） —— 决定性能等级，从好到差：

type	含义	评价
system / const	主键或唯一索引等值命中，最多一行	最好
eq_ref	联表时被驱动表用主键/唯一索引匹配	很好
ref	普通索引等值匹配，可能多行	可接受
range	索引范围扫描（>、BETWEEN、IN）	可接受
index	全索引扫描（扫整棵索引树）	偏差，数据量大要警惕
ALL	全表扫描	最差，大表必须优化

key —— 实际用的索引。NULL 表示没走索引，possible_keys 有值但 key 是 NULL，说明优化器算下来走索引更贵（通常因为区分度太差或回表成本高）。

rows —— 预估扫描行数。不是精确值，但数量级很重要：预估 100 万行而结果只有 10 行，说明索引没设计好。它是累乘的，多表 JOIN 时看驱动表的 rows 最有用。

Extra —— 补充信息，含金量最高：

Extra	含义	要不要处理
Using index	覆盖索引，不回表	理想状态
Using where	拿到行后再过滤	正常，但如果 rows 很大就要优化
Using index condition	索引条件下推（ICP），在引擎层过滤	好事，减少回表
Using filesort	需要额外排序，没利用索引顺序	大结果集要处理
Using temporary	用了临时表（常见于 GROUP BY）	大结果集要处理
Using join buffer	被驱动表没索引，用了 JOIN Buffer	给关联列补索引

EXPLAIN 的三种形态

• EXPLAIN：只出计划，不执行。
• EXPLAIN ANALYZE（8.0.18+）：真正执行并给出实际耗时和行数，用来验证预估准不准。
• EXPLAIN FORMAT=JSON：输出更细的成本信息，配合 optimizer_trace 排查选错索引。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持（8.0 默认 REPEATABLE-READ）：

隔离级别	脏读	不可重复读	幻读	实现方式
READ UNCOMMITTED	可能	可能	可能	几乎不加锁
READ COMMITTED	不可能	可能	可能	每条语句一个新快照
REPEATABLE READ	不可能	不可能	InnoDB 基本解决	首次快照读建 Read View（事务内复用）+ 间隙锁
SERIALIZABLE	不可能	不可能	不可能	读也加共享锁，串行化

-- 查当前会话和全局的隔离级别
SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;

-- 只对当前会话生效（推荐，别动不动改全局）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

RR 怎么处理幻读：分两种读法。

• 快照读（普通 SELECT）：走 MVCC，读事务第一次快照读时建立的 Read View（RR 下整个事务复用），看不到别的事务后来插入的行，天然没有幻读。
• 当前读（SELECT ... FOR UPDATE、UPDATE、DELETE）：读最新数据并加锁，InnoDB 用 Next-Key Lock（记录锁 + 间隙锁）锁住范围，别的事务插不进来。

-- 当前读：锁住 user_id = 1001 的记录以及两侧间隙
SELECT * FROM orders WHERE user_id = 1001 FOR UPDATE;

间隙锁的副作用

Next-Key Lock 锁的是范围，两个事务锁不同但相邻的值也可能互相阻塞，这是 RR 下死锁比 RC 多的原因。很多互联网业务把隔离级别降到 RC（READ COMMITTED）来减少间隙锁、提升并发，代价是 binlog 必须用 ROW 格式。改之前要确认业务能接受不可重复读。

小结

• 索引四原则：最左前缀、区分度、覆盖索引、别让索引用不上（函数、隐式转换、前导通配符是三大杀手）。
• EXPLAIN 看四个字段：type 看等级、key 看用没用上、rows 看扫描量、Extra 看 filesort/temporary/covering。
• 隔离级别默认 RR，快照读靠 MVCC 避免幻读，当前读靠 Next-Key Lock；高并发场景可以评估降到 RC。

会写 SQL、会看执行计划，下一步是把数据的安全兜底做好。下一篇《备份与恢复》讲 mysqldump 与 xtrabackup 的用法、binlog 时间点恢复和备份策略。