Skip to content

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_refjoin 时按主键关联,每行只匹配一行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 都记下来)执行时间、扫描行数
② 看计划EXPLAINtype = 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解释查看执行计划
OptimizerOptimize 优化 + r 者优化器:选执行计划
Slow Query LogSlow 慢 + Query 查询 + Log 日志慢查询日志:记录超过阈值的 SQL
filesortfile 文件 + sort 排序不能利用索引顺序,要额外排序
Nested Loop JoinNested 嵌套 + Loop 循环 + Join 连接嵌套循环连接
NormalizationNormal 规范 + 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 关联列为什么要建索引
  • 能说出范式和反范式各自的好处和代价

下一步该懂什么 ​