05 · SQL 执行与优化

这篇是把前四篇"用起来"的地方。面试问"一条 SQL 很慢怎么排查"，考的就是这篇的综合运用。

一句话本质

SQL 优化的全部工作，就是让数据库少读一些数据。少读 = 少 IO = 快。所有技巧都是这一句话的变体。

生活类比：查资料

慢的做法：
  把整个图书馆的书搬到桌上，一本本翻（全表扫描）

快的做法：
  先查索引柜，定位到"3 排 2 号架"，直接去拿（走索引）
  如果只需要"作者和书名"，卡片上就有，不用去拿书（覆盖索引）

核心机制

一、一条 SQL 的执行流程（回顾 + 展开优化器）

① 连接器：TCP 握手（网络篇）、认证、权限
   v
② 解析器：词法 + 语法分析 > 语法树
   v
③ 优化器：< 这里决定 SQL 快不快
   a. 逻辑优化：子查询展开、谓词下推、常量折叠
   b. 物理优化：选哪个索引、join 用什么算法、join 顺序
   v
④ 执行器：调用存储引擎接口，逐行取数据
   v
⑤ 存储引擎：Buffer Pool > B+ 树 > 返回行

优化器是"基于代价"的（CBO）：它会估算每种执行计划的代价（主要看"要读多少页"），选代价最小的。

为什么优化器会选错：

• 统计信息过期（ANALYZE TABLE 可以更新）
• 数据分布不均（比如 99% 的行都是同一个值）
• 估算模型不精确（比如多列之间的相关性没考虑）

二、EXPLAIN：必须会看

EXPLAIN SELECT * FROM user WHERE age = 20;

列	含义	重点看什么
type	访问类型	最重要，从好到坏：const > eq_ref > ref > range > index > ALL
key	实际用的索引	为 NULL 说明没走索引
rows	预估扫描行数	越小越好
filtered	过滤后剩余百分比	越低说明过滤性越好
Extra	额外信息	Using index（覆盖索引，好）、Using filesort（要排序，差）、Using temporary（用临时表，差）

type 的六个等级：

const     ：主键或唯一索引等值查询，最多一行        < 最好
eq_ref    ：join 时用主键关联，每行只匹配一行
ref       ：普通索引等值查询，可能多行
range     ：索引范围查询（BETWEEN、>、<、IN）
index     ：扫描整个索引树（比全表快，因为索引小）
ALL       ：全表扫描                                < 最差，要优化

看到 type=ALL 就说明要加索引了。

三、慢查询的排查顺序（这是面试重点）

不要一上来就说"加索引"。按这个顺序：

① 确认真的慢
   看慢查询日志（slow_query_log），确认执行时间、扫描行数

② 看执行计划
   EXPLAIN > 看 type、key、rows、Extra
   - type=ALL > 没走索引
   - rows 很大 > 扫描行数多
   - Using filesort > 需要额外排序
   - Using temporary > 用了临时表（常见于 GROUP BY、子查询）

③ 判断是"没索引"还是"索引没用上"
   - 没索引 > 建索引（注意区分度）
   - 有索引但没用 > 检查索引失效的原因（函数、类型转换、前导模糊、OR）

④ 看是不是"返回了太多数据"
   - SELECT * > 改成只查需要的列（可能实现覆盖索引）
   - LIMIT 分页太深 > 用"游标分页"（WHERE id > 上一页最后一条）

⑤ 看是不是"锁等待"
   - 查 information_schema.innodb_trx 看有没有长事务
   - 查 performance_schema 看锁等待

⑥ 看是不是"数据量本身太大"
   - 分库分表、冷热分离、归档历史数据

⑦ 看是不是"数据库配置问题"
   - Buffer Pool 太小、连接数不够、磁盘 IO 瓶颈

关键认知：慢 SQL 的原因不一定是 SQL 本身。可能是锁等待、可能是数据量、可能是硬件。

四、Join 的三种算法

① Nested Loop Join（嵌套循环）
   for each row in A:            < 驱动表
     for each row in B:          < 被驱动表
       if 匹配: 输出
   复杂度 O(A × B)
   优化：给 B 的关联列建索引 > O(A × log B)

② Hash Join（MySQL 8.0.18+ 支持）
   把小表建成哈希表，扫大表去探测
   复杂度 O(A + B)，适合等值 join 且没有索引
   只能用于等值条件

③ Sort-Merge Join
   两边都排序，然后双指针合并
   适合有索引（已经有序）的情况

Join 的优化要点：

• 小表驱动大表（驱动表要全扫，被驱动表可以用索引）。
• 关联列必须建索引，否则每一行都要全表扫被驱动表。
• 避免 join 太多表（一般不超过 3 张），否则优化器的选择空间爆炸。

五、几个高频优化技巧

① 深分页优化
   SELECT * FROM t ORDER BY id LIMIT 1000000, 10;   ❌ 要扫 100 万行
   
   改成游标分页：
   SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;   ✅
   
   或者延迟关联：
   SELECT t.* FROM t JOIN (SELECT id FROM t ORDER BY id LIMIT 1000000,10) x
     ON t.id = x.id;   ✅ 子查询走覆盖索引，只回表 10 次

② COUNT 优化
   COUNT(*) 会扫索引（不是全表），InnoDB 没有存总行数
   大表统计用近似值（SHOW TABLE STATUS 的 rows）或单独维护计数表

③ 批量插入
   一次插入 1000 条 >> 1000 次单条插入（减少网络往返和日志刷盘）

④ 避免 SELECT *
   浪费网络带宽、无法用覆盖索引、可能读到大字段（TEXT/BLOB）

⑤ 用 UNION ALL 代替 UNION（如果不需要去重）
   UNION 要去重 > 需要排序/临时表

⑥ 把子查询改成 JOIN
   MySQL 对子查询的优化能力较弱，很多子查询会被改写成低效的半连接

六、范式与反范式

第一范式（1NF）：字段原子性（不能是数组/嵌套）
第二范式（2NF）：非主键字段完全依赖主键（消除部分依赖）
第三范式（3NF）：非主键字段不依赖其他非主键字段（消除传递依赖）

范式的目的：消除冗余，避免更新异常

反范式：故意冗余，用空间换查询性能（避免 join）
  例：订单表冗余"用户名"，查订单时不用 join 用户表
  代价：用户名改了要同步更新所有订单

工程实践：读多写少的场景适度反范式。互联网业务通常"查询性能优先"，所以会冗余一些字段。

为什么这么设计

反事实 1：如果优化器不存在，SQL 要手写执行计划

那开发者要自己决定"用哪个索引、join 顺序、用什么算法"——不可能，而且改数据量后计划就失效了。优化器让"写 SQL"和"怎么执行"解耦。

反事实 2：如果 Buffer Pool 很小

每次查询都要读磁盘。Buffer Pool 命中率从 99% 降到 90%，IO 次数增加 10 倍，性能直接崩塌。Buffer Pool 大小通常是数据库最重要的配置项（一般设为物理内存的 60%~80%）。

反事实 3：如果深分页用 LIMIT 1000000, 10

数据库必须先读出前 1000010 行，然后丢掉前 100 万行。丢掉的行也读了、也消耗了 IO 和 CPU。这就是深分页慢的原因——它做了 100 万次无用功。

面试怎么问

Q：一条 SQL 很慢，怎么排查？

按上面 ①~⑦ 的顺序答，不要一上来就说加索引。 重点：先 EXPLAIN 看 type 和 rows，再区分"没索引"还是"索引失效"，再考虑数据量和锁等待。

Q：EXPLAIN 你关注哪些字段？

type（访问类型）、key（用了哪个索引）、rows（预估行数）、Extra（有没有 filesort / temporary）。

Q：为什么深分页慢？怎么优化？

因为要扫描并丢弃前面 N 行。优化：游标分页（WHERE id > ?）或延迟关联（子查询走覆盖索引）。

Q：Join 怎么优化？

小表驱动大表、关联列建索引、控制 join 表数量。MySQL 8.0 之前只有 Nested Loop，所以要靠索引；8.0.18+ 有 Hash Join。

Q：什么是回表？怎么避免？

二级索引叶子只存主键，查其他列要回聚簇索引。用覆盖索引避免（把要查的列放进索引）。

追问链：

SQL 为什么慢？
  > 怎么确认？（EXPLAIN 看 type / rows）
    > type=ALL 说明什么？（没走索引）
      > 有索引为什么不用？（函数、类型转换、前导模糊、区分度低）
        > 走了索引还是慢？（回表太多 > 覆盖索引）
          > 数据量太大？（分库分表 / 归档）
            > 不是 SQL 的问题？（锁等待 / Buffer Pool / 硬件）

和其他知识的连接

• < 来自 索引与 B+ 树：优化器的核心工作就是"选索引"
• < 来自 事务与 ACID：长事务导致 undo log 堆积，查询变慢
• < 来自 锁与死锁：慢可能是锁等待，不一定是 SQL 本身
• < 来自 IO 模型与零拷贝：等设备的时候，CPU 在干什么：Buffer Pool 命中率决定 IO 次数
• > 影响 工程实践：读写分离、分库分表、缓存（Redis）、CQRS、冷热分离

自测 3 问

Q1：为什么 LIMIT 1000000, 10 很慢？

点开看答案

因为 MySQL 的执行方式是"先读、再丢"。

LIMIT offset, n 的语义是"跳过 offset 行，取 n 行"。但数据库没有"直接跳到第 100 万行"的能力（B+ 树只能按 key 定位，不能按"第几行"定位），所以它必须：

1. 从第一行开始，逐行读出来。
2. 丢掉前 100 万行。
3. 返回剩下的 10 行。

代价：读了 1000010 行，只用了 10 行。99.999% 的工作是浪费的。

优化：

• 游标分页：WHERE id > 上一页最后的id ORDER BY id LIMIT 10 > 直接从 B+ 树定位到起点。
• 延迟关联：先在索引上取 10 个 id（覆盖索引，不用回表），再用这 10 个 id 回表取数据。

Q2：COUNT(*) 和 COUNT(1) 和 COUNT(字段) 有区别吗？

点开看答案

性能上：COUNT(*) 和 COUNT(1) 基本没区别（MySQL 优化器会把 * 当成 1 处理），都会选最小的索引来扫描。

语义上：COUNT(字段) 有区别——它不统计 NULL。

表里有 3 行，其中 1 行的 name 是 NULL
COUNT(*)      > 3
COUNT(1)      > 3
COUNT(name)   > 2   < 跳过 NULL

性能上 COUNT(字段) 更差：如果字段没有索引，要回表读整行判断是否为 NULL；如果 COUNT(*) 可以用最小的二级索引，只扫索引就够。

结论：统计行数用 COUNT(*)，别用 COUNT(字段)（除非你确实要排除 NULL）。

Q3：为什么"小表驱动大表"？

点开看答案

因为 Nested Loop Join 的代价是：

驱动表行数 × 被驱动表每次查找的代价

• 驱动表：要全表扫描（或走索引扫描），每行都要处理。
• 被驱动表：每一行驱动都要去查一次，如果关联列有索引，每次是 O(log n)。

所以：

• 驱动表小 > 外层循环次数少 > 总代价低。
• 被驱动表有索引 > 内层查找快。

反过来（大表驱动小表）：外层循环 100 万次，每次都要查一次小表——即使小表只有 10 行，也要查 100 万次。

例外：如果 join 的列在被驱动表上没有索引，那无论谁驱动都是 O(A×B)，此时"小表驱动"仍然更优（因为内存中的哈希表更小，如果优化器选 Hash Join 的话）。

类比失效处 / 易错点

• "索引越多越快"是错的。索引会拖慢写入（每次写要维护所有索引），且优化器要在多个索引间选择，太多反而增加选择成本。
• EXPLAIN 的 rows 是估算值，不是精确值。它基于统计信息，可能偏差很大（尤其数据分布不均时）。
• 优化器不一定选对索引。统计信息过期、数据倾斜时会选错，可以用 FORCE INDEX 强制（但要谨慎，数据变了可能更糟）。
• 范式不是越高越好。互联网业务普遍反范式，因为 join 的代价比冗余高。
• "加索引就能解决慢 SQL"是最常见的错误答案。锁等待、数据量、Buffer Pool 配置、网络延迟都可能是原因。

✅ 验收清单

• 能完整讲出 SQL 的执行流程（连接器 > 解析器 > 优化器 > 执行器 > 引擎）
• 能说出 EXPLAIN 里 type 的六个等级，并知道哪个等级需要优化
• 能按顺序说出慢 SQL 的 7 步排查流程（不能一上来就说加索引）
• 能解释"深分页为什么慢"，并写出两种优化写法
• 能说出 Join 的三种算法和"小表驱动大表"的原因
• 能区分 COUNT(*) 和 COUNT(字段) 的语义与性能差异
• 能解释"什么时候该反范式"，并举一个具体例子
• 合上书，用自己的话讲 3 分钟，中途不卡壳