"字段都加了索引，还是慢"是最常见的开场。原因往往不在索引数量，而在索引没用上、用错了、或用上了仍要回表。优化的第一步不是加索引，是读执行计划：看清语句走了哪条路径，再决定改 SQL、改索引还是改表结构。

以下除特别说明均指 MySQL 8.0 InnoDB，具体数字按你的版本与数据量实测核对。

---

1. 执行计划：最该看的两列

EXPLAIN SELECT id, status FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;

列	含义	怎么读
type	访问类型	const > eq_ref > ref > range > index > ALL，越靠右越差
possible_keys	可能用的索引	有值但 key 为空 → 索引存在却没选上
key	实际用的索引	空 = 全表扫或走了别的路径
key_len	用到的索引字节数	联合索引里看出用了几列
rows	预估扫描行数	估算值，量级参考
Extra	补充信息	filesort、temporary、Using index 是关键

只先看两列：type 和 key。type=ALL 或 key=NULL，这条语句的问题就在这里；两者都正常再去看 rows 与 Extra。

Extra 里三个高频信号：

Extra	含义	处理方向
Using index	覆盖索引，不回表	好事，说明查询列都在索引里
Using filesort	额外排序，索引没提供顺序	让 ORDER BY 列进索引，或减小结果集
Using temporary	用了临时表，常见于 GROUP BY/DISTINCT	补与分组列匹配的索引
Using where	取出后再过滤	过滤列没进索引，考虑联合索引

---

2. 索引的三条规则

2.1 最左前缀

联合索引 (a, b, c) 能被 a、a,b、a,b,c 使用；b 单独、b,c 单独用不上。

-- 索引 idx_abc (a, b, c)
SELECT * FROM t WHERE a = 1;                 -- 用上 a
SELECT * FROM t WHERE a = 1 AND b = 2;       -- 用上 a,b
SELECT * FROM t WHERE b = 2;                 -- 用不上，全表扫
SELECT * FROM t WHERE a = 1 AND c = 3;       -- 只用上 a，c 不行

2.2 覆盖索引

查询需要的列全在索引里，就不必回表。

-- 索引 idx_user_status (user_id, status)
SELECT status FROM orders WHERE user_id = 42;         -- 覆盖，Extra: Using index
SELECT * FROM orders WHERE user_id = 42;              -- 要回表取其余列

2.3 函数与隐式转换会让索引失效

WHERE DATE(created_at) = '2024-01-01'   -- 失效：列上做运算
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'   -- 有效

WHERE phone = 13800138000               -- phone 是 varchar，隐式转数字，失效
WHERE phone = '13800138000'             -- 类型一致，有效

规则本质：比较两侧必须可直接比较。左边被函数包住或发生类型转换，B+Tree 的顺序就无法用于定位。

---

3. 回表与索引下推

回表指先在二级索引里找到主键，再按主键回聚簇索引取整行；次数多，随机 IO 就多。

索引下推（Index Condition Pushdown，ICP）让能在索引里判断的条件先判断，减少回表次数：

-- 索引 idx_name_age (name, age)
SELECT * FROM users WHERE name LIKE '张%' AND age > 30;
-- 无 ICP：按 name 前缀取出全部候选，逐行回表再判断 age
-- 有 ICP：在索引层先用 age 过滤，只对剩下的回表（Extra: Using index condition）

概念	发生在哪	代价
回表	二级索引 → 聚簇索引	随机 IO，次数等于候选行数
ICP	索引层内过滤	几乎无额外代价，减少回表
覆盖索引	完全不回表	索引列变宽，写入稍慢

---

4. ORDER BY / GROUP BY 与索引

索引本身有序。若 ORDER BY 的列顺序与索引前缀一致，就能直接按索引顺序读，省掉排序。

-- 索引 (user_id, created_at)
... WHERE user_id = 42 ORDER BY created_at DESC   -- 走索引顺序
... WHERE user_id > 42 ORDER BY created_at DESC    -- 范围后无法保证顺序 → filesort

GROUP BY 同理：分组列作为索引前缀可流式分组，否则容易出 Using temporary。口诀：等值条件 + 有序索引 = 免排序；范围条件之后顺序就断了。

---

5. 联合索引的列顺序

原则	说明	反例
等值列在前	= 条件的列放最左	(created_at, user_id) 让等值列用不满
范围列在后	>、<、BETWEEN 之后列不再用于定位	(age, name)，age>30 后 name 失序
区分度高的优先	等值列之间按选择性排	拿 status（几个值）打头
覆盖常用查询	把 SELECT 常用列附在末尾	每次都要回表

选择性的粗略估算：

SELECT COUNT(DISTINCT status) / COUNT(*) AS sel_status,
       COUNT(DISTINCT user_id) / COUNT(*) AS sel_user
FROM orders;

sel 越接近 1 越适合打头。但查询形态优先于理论选择性：若绝大多数查询是"按 user_id 等值 + 按时间排序"，(user_id, created_at) 就是唯一正确答案。

---

6. 哪些写法用不上索引

写法	能否用索引	原因与替代
LIKE 'x%'	能（前缀）	前缀匹配可定位
LIKE '%x' / '%x%'	不能	无前缀无法定位，考虑全文索引或搜索引擎
OR 连接不同列	常不能	改 UNION ALL 拆分，或建联合索引
NOT IN / != / <>	通常不能	否定条件无法用有序性定位
列在表达式里	不能	把运算移到常量侧

-- OR 的两种改写
SELECT * FROM orders WHERE user_id = 42 OR status = 'PAID';      -- 可能全表扫
SELECT * FROM orders WHERE user_id = 42
UNION ALL
SELECT * FROM orders WHERE status = 'PAID' AND user_id <> 42;    -- 各自走索引

---

7. join：驱动表与 ON/WHERE

优化器选小结果集做驱动表，再用它到被驱动表上按索引查找。所以被驱动表的连接列必须有索引，否则每行都要全表扫。

SELECT o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'PAID' AND u.deleted = 0;

关注点	看哪里	结论
谁是驱动表	EXPLAIN 第一行	通常是 rows 小的那个
被驱动表有无索引	第二行 key	为空则每行一次全表扫
ON 与 WHERE 差别	语义	内连接等价；外连接不等价

外连接要特别小心：LEFT JOIN 里把右表条件写进 WHERE，会把外连接退化成内连接（右表没匹配到时该列为 NULL，条件不成立即被过滤）。正确位置是 ON 子句。

---

8. 慢查询日志与深翻分页

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5          # 超过多少秒记录
log_queries_not_using_indexes = 1

字段	含义	用途
Query_time	总耗时	排优先级
Lock_time	等锁时间	高则不是 SQL 本身的问题
Rows_examined	扫描行数	与返回行数对比看效率
Rows_sent	返回行数	examined/sent 比值越大越该优化

深翻页的两种改法：

-- 原始：LIMIT 100000, 20 —— 要丢弃前 10 万行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 改法一：游标式，记住上一页最后的 id
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- 改法二：延迟关联，先在索引里翻页再回表
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) t ON o.id = t.id;

---

9. count(*) 与写变慢

count(*) 在 InnoDB 里要真实遍历（MVCC 下每行可见性不同，不能像 MyISAM 那样存一个数）。量级上，大表精确 count(*) 是重操作。

需求	更合适的做法
精确总数，但可异步	计数表 + 增量维护
只要"有多少"的量级	EXPLAIN 的 rows 估算，或近似统计
带条件的计数	确保条件列在索引里，尽量覆盖

写变慢的三个常见原因：

原因	表现	处理
索引过多	每次写要维护多棵 B+Tree	删掉长期无人使用的索引
逐条提交	每次 COMMIT 一次 fsync	批量提交，控制单批大小

---

10. 最小改法示例

1. 慢日志锁定语句：
   SELECT id, status FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20;
2. EXPLAIN：type=ALL, key=NULL, rows≈百万级, Extra=Using filesort
3. 建联合索引：ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
4. 再 EXPLAIN：type=ref, key=idx_user_created, key_len 覆盖 user_id, Extra 无 filesort
5. 覆盖索引尝试：把 status 附在末尾 → (user_id, created_at, status)，
   Extra 出现 Using index，彻底不回表

第 5 步有代价：索引变宽、写入维护成本上升，且只对这一类查询有效。先测再定，别把所有列都塞进索引。

---

11. 排错

现象	根因	处理
有索引但 key=NULL	列上有函数、类型不一致、LIKE '%x'	改写成可比较形式后再看计划
possible_keys 有值但没选	优化器估算全表更便宜	ANALYZE TABLE 更新统计，或强制 FORCE INDEX 验证
小表很快，大表很慢	只在小数据上验证过	用接近生产的数据量与分布测
执行计划与预期不符	统计信息过期	更新统计信息后重看，别只看一次
单条快、并发慢	锁等待、连接池、IO	看 Lock_time 与 SHOW PROCESSLIST

---

12. 边界

场景	更合适的做法	理由
报表类复杂聚合	预聚合表 / OLAP 引擎	行存 + 索引救不了大范围扫描聚合
模糊搜索、多条件任意组合	搜索引擎	B+Tree 无法覆盖任意前缀组合
数据量已到单表上限	分区或分库分表	索引优化有物理上限
事务里夹着外部调用	先拆事务	SQL 再快也挡不住锁被长时间持有

一句话：索引优化解决"定位慢"，解决不了"数据量大"和"事务设计错"。

---

13. 自测

1. EXPLAIN 里最先该看的两列是什么，为什么？
2. possible_keys 有值而 key 为空，说明什么？
3. 联合索引 (a, b, c) 下，WHERE a=1 AND c=3 用到了几列？
4. Extra: Using index 代表什么，它省掉了哪一步？
5. LEFT JOIN 里把右表条件写进 WHERE 会发生什么？
6. 深翻页为什么慢，两种改法各自的前提是什么？