44.7 SQL 与查询优化

一句话

SQL 优化不是“加索引”，而是让优化器用更少的页读拿到同样的结果：选择性决定走索引还是全表扫，覆盖索引消除回表，连接算法决定是 10¹³ 还是 10⁷ 次操作，深分页则必须换写法。

一条 SQL 的执行路径

SQL → 解析（语法/语义）→ 重写（视图展开、子查询展开）→ 优化器（选索引、选连接顺序与算法）
    → 执行器（按计划取行）→ 存储引擎（页读、缓冲池）

你能影响的是“优化器有没有正确的信息”和“SQL 有没有给优化器留出选择”：统计信息是否新鲜、索引是否存在、谓词是否可下推、有没有隐式类型转换让索引失效。

数字一：索引的高度决定点查成本

设定：1000 万行、每页放 100 行、B+ 树扇出 500。

操作	页读次数
B+ 树点查	3 次（根 + 内部 + 叶子）
全表扫描	10 万页

高度的算法：log₅₀₀(10,000,000) ≈ 2.6 → 向上取整 3 层。这就是“索引让点查从 O(N) 变 O(log N)”的具体数字：3 次页读 vs 10 万次。

🔑 索引的收益来自“少读页”，不是“少读行”。一个回表查询读了 10 万行，但每行都触发一次随机页读，成本可能比顺序扫全表还高。

数字二：1.1% 的选择率，索引反而更慢

订单表 1000 万行、3 年数据，查“最近 30 天 + status='paid'（占 40%）”：

时间选择率 = 30 / (3×365) = 2.74%
命中行数   = 10,000,000 × 2.74% × 40% ≈ 109,589 行（1.1%）

按“顺序读 0.04ms/页、随机读 0.10ms/页”估算：

执行路径	页读构成	估算成本
全表扫描	10 万页顺序读	4000ms
单列索引 `(created_at)` 回表	索引页 + 10.9 万次随机回表	11233ms
联合索引 `(status, created_at)` 回表	索引页 + 10.9 万次随机回表	11068ms
覆盖索引（索引里含所有列）	只读索引页，不回表	44ms

同一份数据，最慢和最快的写法差 250 倍。而且“加索引”在这里会让查询变慢 2.8 倍——因为 10.9 万次随机页读比 10 万页顺序读更贵。

交叉点可以算出来：

全表扫描成本 / 单次随机读成本 = 100,000 × 0.04 / 0.10 = 40,000 行 = 0.40%

命中行数超过 0.40% 时，全表扫描通常更快。本例 1.1% 已超过，所以优化器大概率选全表扫描——除非有覆盖索引，或者能通过更好的谓词把选择率压下去。

选择率	优化器倾向	你该做什么
< 0.1%	索引	确保谓词可索引、无隐式转换
0.1%–1%	看成本，可能走索引	用覆盖索引消除回表
1%–10%	常走全表扫	减少返回列、加 LIMIT、分区
> 10%	全表扫/分区裁剪	别指望索引，改查询或改模型

数字三：连接算法差 6 个数量级

1000 万订单 × 100 万用户，orders.user_id = users.id：

算法	操作量	适用
嵌套循环（无索引）	10⁷ × 10⁶ = 10¹³ 次比较	只适合小表
哈希连接	10⁷ + 10⁶ = 1.1×10⁷	大表等值连接，内存放得下 build 侧
索引嵌套循环（INLJ）	10⁷ × 3 层 = 3×10⁷ 次页读	驱动表小、被驱动表有索引

10¹³ 次比较在 10⁹ 次/秒的机器上也要 2.8 小时——所以“两张大表没索引做连接”不是慢，是跑不完。哈希连接把复杂度降到线性，代价是内存；INLJ 把“比较”换成“页读”，适合驱动表很小、每次都命中索引的查询。

最左前缀：联合索引 (a, b, c)

条件	能用到索引的列	说明
`a = ?`	a	最左列可用
`a = ? AND b = ?`	a, b	前缀连续
`a = ? AND b = ? AND c = ?`	a, b, c	全用上
`a = ? AND c = ?`	只用 a	c 只能在回表后过滤
`b = ?`	用不了	跳过最左列，索引树按 a 有序，无法定位
`c = ?`	用不了	同上

范围条件会截断后续列：a = ? AND b > ? AND c = ? 只能用到 a, b（b 是范围，c 无法再用于定位）。这是“最左前缀”最容易被忽略的细节——很多慢查询是列顺序排错了，而不是索引没建。

深分页：从 100 万行到 20 行

-- 慢：要先读出前 1,000,020 行再丢掉
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 快：从上一页最后一条的 id 继续（keyset / seek 分页）
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

写法	读取行数	相对成本
`OFFSET 1000000 LIMIT 20`	1,000,020	50001×
keyset（`id > ?`）	20	1×

深分页慢的根因是偏移量必须被“走过”：数据库不知道第 100 万行在哪，只能顺序数过去。keyset 分页把“位置”变成“条件”，直接定位。代价是不能跳页（只能上一页/下一页），适合信息流、导出、遍历。

⚠️ SELECT * 不只是“多传几个字段”：它会让覆盖索引失效（必须回表）、增加网络传输、破坏“索引即数据”的可能。在宽表（200 字节/行 vs 40 字节/行）上，IO 差 5 倍。

可运行的成本模型

package main

import (
	"fmt"
	"math"
)

const (
	rows         = 10_000_000.0 // orders 表行数
	rowsPerPage  = 100.0        // 每页放 100 行
	seqPageMs    = 0.04         // 顺序读一页 0.04ms（约 200MB/s）
	randomPageMs = 0.10         // 随机读一页 0.10ms
	fanout       = 500.0        // B+ 树每个内部节点的分叉数
)

func pages(rs float64) float64 { return rs / rowsPerPage }

func main() {
	// ① B+ 树高度：点查要读几页
	height := math.Ceil(math.Log(rows) / math.Log(fanout))
	fmt.Printf("① %.0f 万行、扇出 %.0f → B+ 树高 %.0f 层；点查约 %.0f 次页读；全表 %.0f 万页\n",
		rows/10000, fanout, height, height, pages(rows)/10000)

	// ② 选择性：3 年数据，查最近 30 天 + status='paid'（占 40%）
	dateSel := 30.0 / (3 * 365)
	statusSel := 0.40
	matched := rows * dateSel * statusSel
	fmt.Printf("② 时间选择率 %.2f%%，status 选择率 %.0f%% → 命中 %.0f 行（%.1f%%）\n",
		dateSel*100, statusSel*100, matched, matched/rows*100)

	// ③ 三种执行路径的 IO 成本
	fullScan := pages(rows) * seqPageMs
	idxDate := pages(rows*dateSel)*randomPageMs + matched*randomPageMs
	idxBoth := pages(matched)*randomPageMs + matched*randomPageMs
	covering := pages(matched) * seqPageMs
	fmt.Printf("③ 执行成本（ms）：全表扫描 %.0f；单列索引(created_at)回表 %.0f；联合索引(status,created_at)回表 %.0f；覆盖索引 %.0f\n",
		fullScan, idxDate, idxBoth, covering)

	// ④ 索引回表 vs 全表扫描的交叉点
	crossover := math.Floor(pages(rows) * seqPageMs / randomPageMs)
	fmt.Printf("④ 回表行数超过 %.0f 行（%.2f%% 的选择率）时，全表扫描反而更快\n",
		crossover, crossover/rows*100)
	fmt.Printf("   本例命中 %.0f 行 > %.0f → 优化器大概率选全表扫描，除非用覆盖索引\n", matched, crossover)

	// ⑤ 连接：10M 订单 × 1M 用户
	orderRows, userRows := rows, 1_000_000.0
	fmt.Printf("⑤ 连接 1000 万订单与 100 万用户：嵌套循环无索引 %.0e 次比较；哈希连接 %.1e 次；索引嵌套循环 %.0e 次页读\n",
		orderRows*userRows, orderRows+userRows, orderRows*3)

	// ⑥ 深分页：LIMIT 1000000, 20
	offset := 1_000_000.0
	fmt.Printf("⑥ OFFSET %.0f LIMIT 20：要读 %.0f 行；keyset（WHERE id > ?）只读 20 行，差 %.0f 倍\n",
		offset, offset+20, (offset+20)/20)

	// ⑦ 最左前缀：联合索引 (a,b,c)
	fmt.Println("⑦ 联合索引 (a,b,c) 能用到哪几列：")
	for _, q := range []struct{ cond, use string }{
		{"a = ?", "a"},
		{"a = ? AND b = ?", "a,b"},
		{"a = ? AND b = ? AND c = ?", "a,b,c"},
		{"a = ? AND c = ?", "只用 a（c 回表过滤）"},
		{"b = ?", "用不了（跳过最左列）"},
		{"c = ?", "用不了（跳过最左列）"},
	} {
		fmt.Printf("   %-32s → %s\n", q.cond, q.use)
	}
}

输出：

① 1000 万行、扇出 500 → B+ 树高 3 层；点查约 3 次页读；全表 10 万页
② 时间选择率 2.74%，status 选择率 40% → 命中 109589 行（1.1%）
③ 执行成本（ms）：全表扫描 4000；单列索引(created_at)回表 11233；联合索引(status,created_at)回表 11068；覆盖索引 44
④ 回表行数超过 40000 行（0.40% 的选择率）时，全表扫描反而更快
   本例命中 109589 行 > 40000 → 优化器大概率选全表扫描，除非用覆盖索引
⑤ 连接 1000 万订单与 100 万用户：嵌套循环无索引 1e+13 次比较；哈希连接 1.1e+07 次；索引嵌套循环 3e+07 次页读
⑥ OFFSET 1000000 LIMIT 20：要读 1000020 行；keyset（WHERE id > ?）只读 20 行，差 50001 倍
⑦ 联合索引 (a,b,c) 能用到哪几列：
   a = ?                            → a
   a = ? AND b = ?                  → a,b
   a = ? AND b = ? AND c = ?        → a,b,c
   a = ? AND c = ?                  → 只用 a（c 回表过滤）
   b = ?                            → 用不了（跳过最左列）
   c = ?                            → 用不了（跳过最左列）

EXPLAIN 该看什么

列	关注点
`type`	`ALL`（全表）、`index`（全索引扫）、`range`、`ref`、`eq_ref`、`const`：越靠右越好
`key` / `key_len`	实际用了哪个索引、用了联合索引的几列（`key_len` 能看出最左前缀用了几列）
`rows`	优化器估算的扫描行数；和实际差很多说明统计信息过期
`filtered`	扫描后剩余比例，低说明索引选择性差
`Extra`	`Using index`（覆盖索引，好）、`Using filesort`（排序无索引）、`Using temporary`（临时表）

三个常见误判：① 看到 type=ALL 就以为必须加索引——1.1% 选择率下全表扫是对的；② 看到 Using index 就以为最优——它可能是全索引扫描（type=index），扫了整棵索引树；③ 只看单条 SQL 不看执行频率——一天跑一次的慢查询不如一秒跑一万次的“稍慢查询”值得优化。

慢日志 → EXPLAIN ANALYZE → 回归：一条闭环

先让慢查询自己冒出来（my.cnf 片段，mysqld --validate-config 校验过）：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.2                # 按接口 P99 目标定，别用默认 10s
min_examined_row_limit = 1000        # 扫描不足 1000 行不记，滤掉噪声
log_queries_not_using_indexes = OFF  # 开了会写爆日志，只可短时打开
innodb_buffer_pool_size = 40G          # 机器内存的 60%–70%（64G 机器给 40G）

再按“总耗时”而不是“单次最慢”排序：

SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1e9, 1) AS total_ms,
       ROUND(AVG_TIMER_WAIT/1e6, 2) AS avg_ms,
       SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

一条 5ms 但每秒 1 万次的查询（总耗时 50 秒/秒），比一条 2s 每分钟一次的查询（0.03 秒/秒）更值得修——优化对象是总耗时，不是单条最慢。

EXPLAIN ANALYZE 看“实际扫描行数”（20 万行测试表实测）：

写法	执行计划	实际扫描	耗时
`WHERE DATE(created_at) = CURDATE()`	Table scan	200000 行，命中 547	34.5ms
`WHERE created_at >= ? AND created_at < ?`	Index range scan（`idx_created`）	547 行	0.8ms
`WHERE user_id = ? ORDER BY created_at DESC LIMIT 20`	Index lookup（`idx_user_created`，reverse）	20 行	0.04ms
`ORDER BY id LIMIT 100000, 20`	Index scan	100020 行	—
`WHERE id > ? ORDER BY id LIMIT 20`	Index range scan（PRIMARY）	20 行	0.57ms

修复后的回归看三条，缺一条都可能是“把成本挪走了”：

EXPLAIN ANALYZE 的 rows 从 20 万降到几百；
该 digest 的 SUM_ROWS_EXAMINED 下降一个量级；
接口 P99 与 DB CPU 同步下降（只改计划不看 P99，可能只是把成本挪到了排序/回表）。

回滚也要提前写：

-- 加错索引：Online DDL 回滚，先确认磁盘余量与复制延迟
ALTER TABLE orders DROP INDEX idx_created, ALGORITHM=INPLACE, LOCK=NONE;

SQL 改写靠代码版本回退；别只把 SQL 改回去、忘了把新加的索引删掉——写放大和优化器误判会留下来。

参数怎么给

症状	先查	改法
点查慢	是否命中索引、有无隐式转换	修正类型/加索引
范围查慢	选择率、`EXPLAIN rows`	覆盖索引、减少返回列
排序慢	`Using filesort`	索引顺序与 `ORDER BY` 对齐
连接慢	驱动表大小、被驱动表有无索引	小表驱动 + 被驱动表索引，或哈希连接
深分页慢	`OFFSET` 大小	keyset 分页
偶发慢	统计信息、锁等待、缓冲池命中	更新统计、看 `innodb_buffer_pool`
写慢	索引数量、唯一约束、二级索引维护	精简索引（每个索引都是写放大）

反模式

反模式	后果
见慢查询就加索引	写放大、优化器选择变差、1.1% 选择率下反而更慢
`SELECT *`	覆盖索引失效、回表、网络放大
在索引列上做函数/运算（`WHERE DATE(created_at)=?`）	索引失效，全表扫
隐式类型转换（字符串列传数字）	索引失效
联合索引列顺序随意	最左前缀用不上
`OFFSET` 翻到几十万页	线性变慢，拖垮 DB
用 `LIKE '%x%'` 做搜索	索引失效，走全表
大表 `JOIN` 无索引	10¹³ 次比较，跑不完
不更新统计信息	优化器选错计划，且难以复现
只优化 SQL 不看表结构	宽表、无分区，单条再快也扛不住量

自检

Q1 命中 10.9 万行（1.1%）时，走索引回表要 11233ms，全表扫描只要 4000ms。为什么“用了索引”反而更慢？

▸ 答案：因为成本由页读方式决定，不是“有没有用索引”。全表扫描是 10 万页顺序读（0.04ms/页）；索引回表是 10.9 万次随机读（0.10ms/次），每次只取一行所在页，页里的其余 99 行浪费了。总成本 10.9 万 × 0.1ms ≈ 11s，是顺序扫的两倍多。所以优化器的选择是对的：低选择率（本例 1.1%）下全表扫更便宜。要让它变快，得减少回表——覆盖索引把 11233ms 降到 44ms。

Q2 覆盖索引为什么能把 11233ms 降到 44ms？它成立的条件是什么？

▸ 答案：覆盖索引把查询需要的所有列都放进索引，不需要回表：只读 109,589 行 / 100 行每页 ≈ 1096 个索引页，顺序读约 44ms。条件有三：① SELECT 的列全部在索引里（不能 SELECT *）；② 过滤条件能用到索引（最左前缀 + 范围）；③ 索引本身不太宽（宽索引的页数会变多，收益下降）。代价是索引更大、写放大更高，所以覆盖索引要针对高频查询设计，不是给每张表都加。

Q3 联合索引 (a,b,c)，为什么 WHERE b = ? 用不了索引，而 WHERE a = ? AND c = ? 至少能用 a？

▸ 答案：B+ 树按“a 然后 b 然后 c”排序，只有 a 确定时，b 才在索引里局部有序；跳过 a 直接查 b，b 在整个索引里无序，无法二分定位，只能全索引扫。a = ? AND c = ? 里 a 能定位到一段连续区间，c 在这段区间内无序，所以只能回表后过滤——索引仍然缩小了扫描范围。列顺序要按“等值在前、范围在后、高频组合优先”排。

Q4 LIMIT 1000000, 20 为什么要读 100 万行？换成 keyset 分页后为什么只读 20 行？代价是什么？

▸ 答案：OFFSET 的语义是“跳过前 N 行”，数据库必须真的数过这 N 行（即使不返回），所以读了 1,000,020 行。keyset 用 WHERE id > 上一页最后一个 id 把“位置”变成“索引上的起点”，B+ 树直接定位，只读 20 行。代价：① 不能随机跳页（只能上一页/下一页）；② 需要单调且唯一的排序键（id/时间戳 + 去重）；③ 排序键变了分页就失效。

Q5 哈希连接和索引嵌套循环，什么时候选哪个？

▸ 答案：哈希连接适合两张大表做等值连接、内存能放下 build 侧（小表）：一次建表 + 一次探测，复杂度线性，但只支持等值、内存不够会落盘。索引嵌套循环适合驱动表很小（几十~几千行）、被驱动表连接列有索引：每行一次 3 层索引查找，成本 = 驱动行数 × 3 页读，不需要大内存。优化器会根据统计信息选；如果你发现它选错了（比如统计过期），可以用 hint 或改写查询，但先确认统计信息是否新鲜。

关联

前置：34.1 性能指标、34.4 优化手段