深色模式
05 · SQL 执行与优化:一条慢 SQL 怎么查
学完你能说出:
- 优化器怎么选执行计划,为什么会选错。
- EXPLAIN 的 type、key、rows、Extra 怎么看。
- 一条慢 SQL 的排查顺序,以及深分页、join、批量写入这几个高频优化。
先看地图
前四篇讲了索引、事务、MVCC、锁的原理。这一篇把它们用起来:拿到一条慢 SQL,一步步查出慢在哪、怎么改。本篇讲 5 个点:
① 优化器怎么选计划 → ② EXPLAIN 怎么看 → ③ 慢查询的排查顺序 → ④ join 怎么优化 → ⑤ 深分页、COUNT、批量写、范式与反范式
下一篇会把视角拉高:不只是「一条 SQL」,而是「数据越来越多时,整个数据库怎么优化」。
一句话本质
SQL 慢,绝大多数是扫描的行太多。用 EXPLAIN 看优化器打算怎么查,再让它少扫行:建对索引、避免索引失效、少取数据。
生活类比
快递员送 100 个包裹:
- 优化器 = 规划路线的导航软件,按「估计耗时」选路;地图过时了(统计信息旧),就会选错路。
- EXPLAIN = 出发前先看导航给的路线:走高速(索引)还是穿小巷挨家挨户找(全表扫描)。
- 深分页 = 让快递员「跳过前 100 万户,送第 100 万零 1 户」,他还是得一户户数过去。
一步步看
1. 优化器:按「估算成本」选计划
同一条 SQL 可能有多种执行方式:走 A 索引、走 B 索引、全表扫描、join 时先查哪张表。优化器估算每种方式要读多少页、比较多少行,选成本最低的。
为什么会选错:
- 统计信息过期(大量增删之后),可以用
ANALYZE TABLE更新; - 数据分布很偏(比如 99% 的行 status = 1);
- 估算模型不考虑多列之间的相关性。
选错时可以用 FORCE INDEX 强制,但更好的办法是改写 SQL 或调整索引。
2. EXPLAIN 怎么看
sql
EXPLAIN SELECT id, name FROM user WHERE age = 20;| 列 | 意思 | 重点 |
|---|---|---|
| type | 访问方式 | 最重要,从好到差:const > eq_ref > ref > range > index > ALL |
| key | 实际用的索引 | NULL = 没走索引 |
| rows | 估计要扫多少行 | 越小越好 |
| Extra | 额外信息 | Using index(覆盖索引,好);Using filesort(额外排序,差);Using temporary(临时表,差) |
| type | 意思 | 例子 |
|---|---|---|
| const | 主键 / 唯一索引等值,最多一行 | WHERE id = 5 |
| eq_ref | join 时按主键关联,每行只匹配一行 | JOIN user u ON o.user_id = u.id |
| ref | 普通索引等值,可能多行 | WHERE age = 20 |
| range | 索引范围 | WHERE age BETWEEN 20 AND 30 |
| index | 扫整棵索引树 | 比全表快一点,因为索引小 |
| ALL | 全表扫描 | 看到它就要优化 |
3. 慢查询的排查顺序
不要一上来就说「加索引」。按这个顺序:
| 步骤 | 做什么 | 看到什么说明问题 |
|---|---|---|
| ① 确认慢 | 打开慢查询日志(超过 1 秒的 SQL 都记下来) | 执行时间、扫描行数 |
| ② 看计划 | EXPLAIN | type = ALL、rows 很大、Using filesort |
| ③ 区分原因 | 没索引 → 建索引;有索引没用上 → 查失效原因(函数、类型转换、前导 %、最左前缀) | |
| ④ 少取数据 | 不写 SELECT *;深分页改写 | 返回行多、字段大 |
| ⑤ 看锁 | 查有没有长事务占着锁 | SQL 本身快,但在等锁 |
| ⑥ 看规模 | 数据本身太大 → 下一篇的分层优化 | 单表上亿行 |
关键认知:慢 SQL 的原因不一定在 SQL 本身,可能是锁等待、数据量或硬件。
4. join 怎么优化
MySQL 最常用的 join 方式是嵌套循环:驱动表每取一行,就去被驱动表找匹配的行。
订单表 1 万行 join 用户表 100 万行(按 user_id 关联)
被驱动表关联列没索引:1 万 × 100 万 = 100 亿次比较
被驱动表关联列有索引:1 万 × 3 次索引查找 = 3 万次要点:
- 被驱动表的关联列一定要有索引;
- 小表驱动大表:驱动表要全扫,越小越好(优化器一般会自己选);
- 一条 SQL join 的表别太多,三四张以内为宜。
- MySQL 8.0.18 之后,没有索引的等值 join 可以用哈希 join:把小表建成哈希表,再扫大表去匹配。
5. 几个高频优化
深分页:
sql
-- 慢:要先扫过前 100 万行再丢掉
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 快:记住上一页最后一条的 id,从那里接着查(游标分页)
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 10;
-- 必须跳页时:先在索引里查出 10 个 id(覆盖索引,不回表),再回表取整行(延迟关联)
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t ON o.id = t.id;COUNT:COUNT(*) 和 COUNT(1) 一样,都数所有行;COUNT(列) 不数 NULL。InnoDB 没有存总行数,大表 COUNT 会扫一整棵索引树;需要实时总数的场景,可以单独维护一张计数表或放进缓存。
批量写入:一次插 1000 行(INSERT ... VALUES (...), (...), ...)放在一个事务里,比 1000 次单条插入快一两个数量级:网络往返从 1000 次变 1 次,日志刷盘也从 1000 次变 1 次。
范式与反范式:
| 范式 | 反范式 | |
|---|---|---|
| 做法 | 消除冗余:用户名只存在用户表里 | 故意冗余:订单表也存一份用户名 |
| 好处 | 改一处就行,不会不一致 | 查订单不用 join,快 |
| 代价 | 查询要 join | 用户改名要同步更新所有订单 |
互联网业务读多写少,常常适度反范式;数仓里的宽表也是这个思路。
为什么这么设计
为什么要有优化器,不让开发者自己写执行计划?
- 问题:数据量和分布一直在变,今天最优的计划,下个月可能就很慢;开发者也不可能为每条 SQL 手写计划。
- 方案:开发者只写「要什么」(SQL),优化器根据统计信息决定「怎么取」。
- 好处:写法和执行解耦;数据变了,优化器会自动换计划。代价是偶尔选错,要用 EXPLAIN 检查。
一句话答法
SQL 只描述要什么,优化器根据统计信息估算成本、决定怎么取,这样数据变了计划也能跟着变;偶尔选错,所以要会用 EXPLAIN 检查。
为什么深分页慢?
- 问题:
LIMIT 1000000, 10不是「直接跳到第 100 万行」,而是先按顺序读出 100 万零 10 行,再丢掉前 100 万行;如果是SELECT *,还要回表 100 万次。 - 方案:游标分页(
WHERE id > 上一页最后的 id),直接在索引上定位起点;或延迟关联,先用覆盖索引拿 id,只回表 10 次。 - 好处:扫描行数从 100 万降到 10。
一句话答法
深分页要先扫过前面所有行再丢掉,偏移越大越慢。改成按上一页最后一个 id 往后查,直接在索引上定位起点,只扫需要的那几行。
为什么 join 的关联列一定要有索引?
- 问题:嵌套循环里,驱动表的每一行都要去被驱动表找一遍;没索引就是每次全表扫,次数是两表行数相乘。
- 方案:给被驱动表的关联列建索引,每次查找只要 3 次左右的页访问。
- 好处:1 万 × 100 万次比较,变成 1 万 × 3 次查找。
一句话答法
join 是驱动表每一行去被驱动表里找匹配,被驱动表关联列没索引就要每次全表扫,次数是两表行数相乘,所以关联列必须建索引,并且小表驱动大表。
英文术语拆解
| 术语 | 拆开看 | 一句话意思 |
|---|---|---|
| EXPLAIN | 解释 | 查看执行计划 |
| Optimizer | Optimize 优化 + r 者 | 优化器:选执行计划 |
| Slow Query Log | Slow 慢 + Query 查询 + Log 日志 | 慢查询日志:记录超过阈值的 SQL |
| filesort | file 文件 + sort 排序 | 不能利用索引顺序,要额外排序 |
| Nested Loop Join | Nested 嵌套 + Loop 循环 + Join 连接 | 嵌套循环连接 |
| Normalization | Normal 规范 + ization 化 | 范式化:消除冗余 |
面试怎么问
Q:一条 SQL 很慢,怎么排查?
先看慢查询日志确认执行时间和扫描行数;再 EXPLAIN 看执行计划,重点看 type 是不是 ALL、key 有没有用上索引、rows 多大、Extra 有没有 filesort 和临时表;然后判断是没建索引还是索引失效(函数、隐式转换、前导模糊、最左前缀);再看是不是取了太多数据,比如 SELECT * 或深分页;如果 SQL 本身不慢,还要看是不是在等锁、有没有长事务。
Q:EXPLAIN 你主要看哪几列?
type:访问方式,从好到差 const、eq_ref、ref、range、index、ALL,看到 ALL 说明全表扫描;key:实际用了哪个索引;rows:估计扫描行数;Extra:Using index 说明覆盖索引,Using filesort、Using temporary 说明有额外排序或临时表,需要优化。
Q:深分页为什么慢?怎么优化?
LIMIT 1000000, 10要先读出前面 100 万行再丢弃,偏移越大越慢。优化:游标分页,记住上一页最后一条的 id,用WHERE id > ? LIMIT 10;必须跳页时用延迟关联,子查询走覆盖索引只拿 id,再回表取 10 行。
Q:COUNT(*)、COUNT(1)、COUNT(列) 有什么区别?
COUNT(*) 和 COUNT(1) 一样,统计所有行,InnoDB 会选最小的索引树来扫;COUNT(列) 只统计该列不为 NULL 的行。
容易混的地方
- type = index 不是「走了索引就好」:它是扫整棵索引树,只比全表扫好一点。
- rows 是估计值:不是精确的扫描行数。
- 加索引不是万能药:锁等待、数据量太大、返回数据太多,加索引都解决不了。
- 反范式不是不讲规范:是有意识地用冗余换查询速度,并且要想好怎么同步。
和其他知识的连接
- ← 来自 索引与 B+ 树:索引失效的原因在这里用来排查。
- ← 来自 锁与死锁:SQL 本身不慢却在等锁。
- → 影响 数据库优化的分层答法:从一条 SQL 扩展到整个数据库。
- → 影响 大数据里的 SQL 与 Hive:执行顺序、join、窗口函数在 Hive 里同样适用。
自测 3 问
Q1:EXPLAIN 看到 type = ALL、key = NULL、rows = 2000000,说明什么?下一步怎么做?
点开看答案
没走索引,全表扫描约 200 万行。下一步看 WHERE 条件:没有索引就按区分度建索引;有索引却没用上,就查是不是对列用了函数、类型不匹配、前导 %、违反最左前缀,改写 SQL。
Q2:为什么 LIMIT 1000000, 10 很慢?给出两种改法。
点开看答案
要先扫出前 100 万零 10 行再丢掉前 100 万行。改法一:游标分页 WHERE id > 上一页最后 id ORDER BY id LIMIT 10;改法二:延迟关联,子查询用覆盖索引只取 10 个 id,再 join 回表。
Q3:订单表 1 万行 join 用户表 100 万行,用户表关联列没有索引,大约要比较多少次?加了索引呢?
点开看答案
没索引:1 万 × 100 万 = 100 亿次比较;有索引:1 万次 × 每次约 3 次页访问 ≈ 3 万次。
✅ 验收清单
- 能说出 EXPLAIN 的 4 个重点列,并按好坏排出 6 种 type
- 能按 6 步说出慢查询的排查顺序
- 能写出深分页的两种改法
- 能用数字说明 join 关联列为什么要建索引
- 能说出范式和反范式各自的好处和代价
下一步该懂什么
- 数据越来越多时整个数据库怎么优化(面试高频)→ 数据库优化的分层答法