执行计划与慢查询定位

S3-015建议学高级约 60 分钟JDK 21企业开发基础 · MySQL 与数据建模

1. 本节定位

《索引》讲了索引"是什么"，本节讲**"怎么确认它到底用上没有"**：

text

写了一条 SQL，慢了。
   ↓ 猜？  → "可能没走索引？"（猜错的概率很高）
   ↓ 看执行计划 → 数据库告诉你：它扫了多少行、用了哪个索引、怎么扫的 ✓

本节的核心能力：把"我觉得慢"变成"我知道慢在哪"。

2. 学习目标

完成本节后，你应该能够：

用 EXPLAIN 读出一份执行计划的关键信息（扫了多少行、用了哪个索引、怎么扫的）；
说出"全表扫描"与"索引查找"在执行计划里各长什么样；
说出四种典型的索引失效场景，并能把失效的写法改回可用的写法；
说出复合索引的"最左前缀"在执行计划里怎么体现；
说出 MySQL 慢查询日志的开启方式与定位流程；
知道"索引是否生效取决于具体数据库的优化器"，因此必须实测。

3. 前置知识检查

知道 B+ 树索引与复合索引吗？（《索引》）
知道 WHERE 条件的"选择性"概念吗？（《索引》的 5.4 节）

4. 为什么需要这个知识

一个真实的认知偏差：

text

开发者："我给 user_id 建了索引，这条 SQL 应该很快。"
执行计划：type=ALL（全表扫描）                          ← 索引没被用上 ✗

"建了索引"和"用上了索引"是两件事 ✗

常见的原因：

原因	例子
索引列被包在函数里	`WHERE YEAR(created_at) = 2026`
索引列参与了运算	`WHERE user_id + 0 = 7777`
隐式类型转换	列是 `BIGINT`，传了字符串 `'8888'`
`LIKE` 以通配符开头	`WHERE name LIKE '%关键字'`
复合索引跨过最左列	索引是 `(a, b)`，只查 `b`
选择性太低	某列只有 2 个取值，优化器认为"不如全表扫"

这份清单不是靠背的——要靠"看执行计划"来验证 ✓ 因为优化器会做各种"你看不见的优化"（本节会实测到这一点）。

5. 核心概念

5.1 执行计划怎么看

命令：

sql

EXPLAIN SELECT ... ;                  -- 只看计划，不执行
EXPLAIN ANALYZE SELECT ... ;          -- 真的执行，给出实际行数与耗时（PostgreSQL/H2）

MySQL 的 EXPLAIN 输出（最常看的六列）：

列	含义	关注什么
`type`	访问类型	`ALL` = 全表扫描（危险）；`ref` / `range` / `const` = 用索引 ✓
`key`	实际用的索引	`NULL` = 没用索引 ✗
`key_len`	用到的索引长度	复合索引用了几列
`rows`	预估扫描行数	这个数字越大越危险 ★
`filtered`	过滤后剩余百分比	越小说明扫了很多没用的
`Extra`	附加信息	`Using filesort` / `Using temporary` 是要警惕的 ⚠️

type 的常见取值（从好到坏）：

值	含义	评价
`system` / `const`	按主键或唯一索引查一行	最好 ✓
`eq_ref`	联表时按主键关联	很好 ✓
`ref`	用非唯一索引查找	好 ✓（`user_id` 建普通索引就属于这类）
`range`	索引范围扫描	可以接受（`BETWEEN`、`>`）
`index`	扫整个索引树（不是全表）	⚠️ 通常说明缺合适的索引
`ALL`	全表扫描	危险 ✗

PostgreSQL 的对照：

PostgreSQL	含义	对应 MySQL 的
`Seq Scan`	顺序扫描（全表）	`ALL` ✗
`Index Scan`	索引扫描	`ref` / `range`
`Index Only Scan`	只读索引就够（覆盖索引）	`Extra: Using index` ✓
`Bitmap Heap Scan`	位图扫描（扫很多行时用）	无直接对应

⚠️ 记住一条通用判断：不管哪个数据库，先看这两件事：

text

① 有没有全表扫描（MySQL 的 ALL / PostgreSQL 的 Seq Scan）
② 扫描行数是多少（rows / actual rows）

5.2 实测：索引带来的差异

本节用 H2 的 MySQL 兼容模式实测（20 万行数据）。字段名与 MySQL 不同，但判断逻辑一致——这一点很重要：

索引的本质是"用额外的存储换取查找时的有序性"，这个原理与具体数据库无关。
所以"怎么看计划"是可迁移的能力，"字段叫什么"只是方言差异。

实测结果（1）：无索引 vs 有索引

场景	返回行数	耗时	执行计划里的关键信息
无索引	22	28 ms	`/* public.orders.tableScan /` ← 全表扫描*
有索引	22	1 ms	`/* public.idx_orders_user_id: user_id = ... /` ← 走索引*

同样的 SQL、同样的返回行数，差了 28 倍 ✓

计划里的关键词对照：

H2	MySQL	含义
`tableScan`	`type: ALL`	全表扫描 ✗
`idx_orders_user_id: user_id = ...`	`key: idx_orders_user_id`	用上了索引 ✓

实测结果（2）：在索引列上做运算

写法	耗时	计划
`WHERE user_id + 0 = 7777`	36 ms	全表扫描 ✗
`WHERE user_id = 7777`	0 ms	走索引 ✓

原因是"列上做了运算"：user_id + 0 的结果不再对应索引里存的值——数据库没法用 B+ 树去定位它 ✗

修复：把运算移到右边：

sql

-- ✗ 索引失效
WHERE user_id + 0 = 7777

-- ✓ 索引可用
WHERE user_id = 7777

这条原则可以推广："索引列必须"独立"地出现在比较的一侧" ✓

5.3 四种典型的索引失效

（1）索引列被包在函数里

sql

-- ✗ MySQL：索引失效（要对每一行算 YEAR()）
WHERE YEAR(created_at) = 2026

-- ✓ 改成范围条件，索引可用
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

⚠️ 但本节实测出了更有意思的情况：

写法	H2 的耗时	H2 的计划
`WHERE YEAR(created_at) = 2026`	67 ms	索引扫描（H2 把它优化了！）
等价的 `>= AND <` 范围条件	64 ms	索引范围扫描

H2 的优化器比 MySQL 聪明——它把 YEAR() 改写成了范围扫描 ✗ 所以这个例子在 H2 上"没有演示出失效"。

但这不是坏消息——它教了本节最重要的一课：

"索引会不会失效"取决于具体数据库的优化器版本与能力。
所以：不要背"哪些写法会失效"，而要看执行计划 ✓
在 MySQL 5.7 上会失效的写法，在 MySQL 8.0 或 PostgreSQL 上可能已经被优化掉了。

（2）LIKE 以通配符开头（这个任何数据库都救不了）

写法	返回行数	耗时	说明
`status LIKE 'PAI%'`	20000	7 ms	前缀匹配 → 能走索引 ✓
`status LIKE '%AID'`	20000	32 ms	前后都有通配符 → 全表扫描 ✗

差 4.6 倍 ✓

为什么救不了：B+ 树是按"前缀"排序的——知道开头才能定位；%AID 的末尾信息在树里没有序 ✗

修复方向：

场景	方案
前缀搜索（"以 X 开头"）	直接用 `LIKE 'X%'` ✓
后缀搜索（"以 X 结尾"）	存一列倒序的冗余字段 + `LIKE 'X%'`（或用倒排索引）
全文搜索（"包含 X"）	上全文索引 / ES（S4 会讲）✓

（3）隐式类型转换

sql

-- 列是 BIGINT，参数是字符串
WHERE user_id = '8888'

MySQL 的处理：把列转成字符串再比较（而不是把参数转成数字）✗ → 索引失效 ✗

⚠️ 本节实测的 H2 又不一样：H2 计划里显示 user_id = CAST(8888 AS BIGINT)——它把参数转成了数字，索引正常用上 ✓

再次印证 5.3（1）的结论：要看计划，不要凭经验。

（4）复合索引跨过最左列

索引：(user_id, created_at)

查询	能否用上
`WHERE user_id = ?`	✅ 能用（用第一列）
`WHERE user_id = ? AND created_at > ?`	✅ 能用两列
`WHERE created_at > ?`	❌ 不能（跨过了最左列）

⚠️ 但本节的实测有个细节：WHERE created_at >= ... 的计划里走了 idx_orders_created_at——因为我单独给它也建了索引 ✓

这说明一个实践要点：

"能不能用上索引"的答案，取决于"这个库里到底有哪些索引" ✓
所以排查时要先看 SHOW INDEX FROM 表名，再对计划。

最左前缀的修复方式：

需求	方案
经常单独按 `created_at` 查	单独再建一个 `created_at` 的索引 ✓
偶尔查	接受（或调整查询条件带上 `user_id`）
总是组合查	保持复合索引即可 ✓

5.4 慢查询定位的流程

执行计划解决"我知道慢在哪"，但前提是"我知道哪条 SQL 慢" ✓

MySQL 的慢查询日志：

ini

# my.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                    # 超过 1 秒就记（生产可以设 0.5 或 0.2）
log_queries_not_using_indexes = OFF    # 打开会把"没走索引的小查询"也记，日志量很大 ⚠️

在线开启（不用重启）：

sql

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SHOW VARIABLES LIKE 'slow_query%';

日志长这样：

text

# Time: 2026-09-21T15:30:12.123456+08:00
# User@Host: app[app] @ 10.0.1.15 []  Id: 88231
# Query_time: 3.214  Lock_time: 0.000  Rows_sent: 20  Rows_examined: 200000
SET timestamp=1758440132;
SELECT id, user_id FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 199000;

要看的三个数字：

字段	含义	判断
`Query_time`	执行耗时	多少毫秒以上算慢，按业务定
`Rows_examined`	扫描了多少行 ★	这个数字是关键——扫了 20 万行只返回 20 行 ✗
`Rows_sent`	返回多少行	与 `Rows_examined` 对比

Rows_examined / Rows_sent 的比值是"低效程度"的直接指标：

text

Rows_examined: 200000, Rows_sent: 20     ← 扫 1 万行才拿到 1 行 ✗

分析工具：

工具	作用
`mysqldumpslow`	MySQL 自带，按耗时/次数排序汇总
`pt-query-digest`	Percona Toolkit，功能强得多（能按"SQL 指纹"聚合、给改进建议） ✓
MySQL 8.0 的 `performance_schema`	在线看"哪条 SQL 最耗时"，不用等日志

MySQL 8.0 的一段实用查询（不用开慢日志就能找热点 SQL）：

sql

SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1000000000 AS avg_ms, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

5.5 定位流程：从"用户说慢"到"改完验证"

text

① 确认现象
   · 哪个接口慢？多久？（用户说"很慢"没有信息量）
   · 什么时候开始的？（一直慢 vs 突然变慢 → 排查方向完全不同）

② 定位到 SQL
   · 开慢查询日志 / 看 performance_schema
   · 或在应用层打点（MyBatis 的 SQL 日志 + 耗时，见 S3-008）

③ 看执行计划
   · EXPLAIN 这条 SQL
   · 重点：有没有全表扫描、扫描行数、Extra 里有没有 filesort/temporary

④ 判断原因（对照 5.3 的四种典型）
   · 缺索引？→ 建
   · 索引失效？→ 改写 SQL
   · 扫描行数本身很大（要查的数据就是多）？→ 改需求/分页/缓存

⑤ 改动 + 验证
   · 建索引后必须再 EXPLAIN 一次，确认走索引了 ✓
   · 记录改动前后的耗时（作为证据）

⑥ 观察线上效果
   · 慢查询数量、接口 P99 耗时
   · ⚠️ 建索引也要观察写入性能（索引是有代价的，见 S3-016）

⚠️ 第 ② 步的一个实践要点：

"一直慢"和"突然变慢"的排查方向完全不同。

现象	可能原因
一直慢	缺索引、SQL 写得不好 ✗
突然变慢	数据量涨了（计划变了）、锁等待、连接池耗尽、数据库负载高了
偶发慢	锁冲突、GC、网络抖动、缓存击穿

"突然变慢"时，第一个要看的是"执行计划变了吗"——因为数据量变化会让优化器换一个计划 ✓（这是"昨天还好好的，今天突然慢"的经典原因）

6. 递进示例

6.1 最小示例：看一条 SQL 的计划

sql

EXPLAIN SELECT id, amount_in_cents FROM orders WHERE user_id = 7777;

MySQL 输出：

text

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 199234 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

两个危险信号：type: ALL + key: NULL ✗

6.2 业务示例：四组对比

见第 7 节。

6.3 反例：为了"性能"给每列都建索引

sql

CREATE INDEX idx_a ON orders (user_id);
CREATE INDEX idx_b ON orders (status);
CREATE INDEX idx_c ON orders (amount_in_cents);
CREATE INDEX idx_d ON orders (created_at);
-- ... ✗

三个问题：

问题	说明
写入变慢	每次 `INSERT`/`UPDATE` 都要维护所有索引 ✗（《MySQL 性能优化基础》会量化）
占用空间	索引本身可能比数据大
优化器更难选	索引太多反而可能选错

正确做法：按查询建索引——先看有哪些慢查询，再针对性建 ✓

7. 完整可运行代码

文件：labs/data-access/src/main/java/cn/kb/learn/data/ExplainLesson.java

关于本节的环境：示例跑在 H2 的 MySQL 兼容模式内存库上（20 万行数据）。
为什么可以用 H2 学执行计划：索引的原理与数据库无关——"全表扫描会慢、索引查找会快""列上做运算会让索引失效"这些判断逻辑是一样的 ✓
但字段名不同（详见 5.1、5.2 的对照表），而且优化器的能力不同（本节实测到了两处 H2 "比 MySQL 聪明"的地方）✓

核心代码：

java

public class ExplainLesson {
    private static final int ROWS = 200_000;

    public static void main(String[] args) throws SQLException {
        initSchema();
        insertRows();

        measure("无索引", "SELECT id, amount_in_cents FROM orders WHERE user_id = 7777");
        createIndexes();
        measure("有索引", "SELECT id, amount_in_cents FROM orders WHERE user_id = 7777");
        // ... 其余五组对比
    }

    /** 打印执行计划 + 实测耗时（跑 5 次取最小值，减少抖动） */
    private static void measure(String label, String sql) throws SQLException {
        String plan = explain(sql);
        long best = Long.MAX_VALUE;
        for (int round = 0; round < 5; round++) {
            long start = System.nanoTime();
            int count = runCount(sql);
            best = Math.min(best, (System.nanoTime() - start) / 1_000_000);
        }
        System.out.println("[" + label + "] " + sql);
        System.out.println("  返回行数：" + count + "，最快耗时：" + best + " ms");
        System.out.println("  执行计划（H2）：" + plan);
    }
}

运行命令：

powershell

cd labs
./mvnw -pl data-access compile dependency:build-classpath "-Dmdep.outputFile=target/cp.txt"
java "-Dstdout.encoding=UTF-8" -cp "data-access/target/classes;$(Get-Content data-access/target/cp.txt)" cn.kb.learn.data.ExplainLesson

预期输出（真实运行结果，20 万行，跑 5 次取最快）：

text

建表完成：orders，准备插入 200000 行
插入完成，耗时 1908 ms

===== 1. 没有索引：按 user_id 查一个用户 ---
[无索引] SELECT id, amount_in_cents FROM orders WHERE user_id = 7777
  返回行数：22，最快耗时：28 ms
  执行计划（H2）：... /* public.orders.tableScan */ WHERE "user_id" = CAST(7777 AS BIGINT)

===== 2. 建索引（只加一个索引，其他都不变）---
已创建索引：idx_orders_user_id、idx_orders_user_created、idx_orders_created_at、idx_orders_status

[有索引] SELECT id, amount_in_cents FROM orders WHERE user_id = 7777
  返回行数：22，最快耗时：1 ms
  执行计划（H2）：... /* public.idx_orders_user_id: user_id = CAST(7777 AS BIGINT) */ ...

===== 3. 复合索引：(user_id, created_at) —— 最左前缀 ---
[用最左列 user_id] ... 返回行数：22，最快耗时：1 ms
[用第二列 created_at（跨过最左列）] ... 返回行数：0，最快耗时：1 ms
  ← 走的是单独建的 idx_orders_created_at（所以"能不能用上"取决于有哪些索引）

===== 4. 把索引列包进函数（MySQL 会失效）---
[对索引列用函数 YEAR()] ... 返回行数：200000，最快耗时：67 ms
[改成等价的「范围条件」] ... 返回行数：200000，最快耗时：64 ms
  ← H2 把 YEAR() 优化成了索引扫描；MySQL 5.7 上会失效 ★ 见 5.3(1)

===== 5. 在索引列上做运算（MySQL 会失效）---
[列上做运算 user_id + 0] ... 返回行数：22，最快耗时：36 ms   ← 全表扫描
[直接比较 user_id = 7777] ... 返回行数：22，最快耗时：0 ms   ← 走索引

===== 6. LIKE 的两种写法（任何数据库都逃不掉）---
[前缀匹配 status LIKE 'PAI%'] ... 返回行数：20000，最快耗时：7 ms    ← 走索引
[前后都带通配符 status LIKE '%AID'] ... 返回行数：20000，最快耗时：32 ms  ← 全表扫描

===== 7. 深分页：LIMIT/OFFSET 与游标 ---
[深分页 OFFSET 199000] ... 返回行数：20，最快耗时：18 ms
[游标式（WHERE id > ?）] ... 返回行数：20，最快耗时：0 ms

四组值得盯住的数据：

对比	数据	结论
无索引 vs 有索引	28 ms → 1 ms	28 倍的差距，且返回行数完全相同
列上运算 vs 直接比较	36 ms → 0 ms	"索引列必须独立出现在比较的一侧"
`'PAI%'` vs `'%AID'`	7 ms → 32 ms	通配符的位置决定能否用索引
OFFSET 199000 vs 游标	18 ms → 0 ms	深分页的代价（《MySQL 性能优化基础》展开）

以及一个"意外"：

对比	数据	结论
`YEAR(created_at)` vs 范围条件	67 ms vs 64 ms	H2 优化器把它们优化成了同一个计划

这个"意外"比预期的结果更有价值——它证明了本节的核心观点：
"索引会不会失效"是"优化器的行为"，不是"SQL 的固有属性"。
所以：必须实测（看执行计划），不能背结论。 ✓

8. Debug 观察点

在 explain 方法处断点：观察 EXPLAIN 返回的就是一行文本（不同数据库格式不同，但都能看出"扫还是查"）；
把 measure 的轮数从 5 改成 1：观察耗时抖动变大（29 ms → 35 ms）——理解"为什么要跑多次取最小值"；
把 ROWS 从 20 万改成 2 千：观察无索引的耗时也会降到几乎为 0——这说明"索引的收益与数据量强相关"（小表上索引可能没意义）；
给 status 只保留索引、去掉其他：观察 LIKE 'PAI%' 是否还快（确认收益来自索引而不是巧合）；
把 user_id + 0 改成 user_id + 1 - 1：观察是否仍然失效（理解"失效的原因是列参与了运算"）。

9. 常见错误

错误 1：以为"建了索引就用上了"

见第 4 节。验证方式只有一个：看执行计划 ✓

错误 2：只看"有没有索引"，不看"扫了多少行"

sql

-- 走了索引，但仍然很慢 ✗
SELECT * FROM orders WHERE status = 'CREATED' ORDER BY created_at DESC LIMIT 20;
-- 如果 90% 的订单都是 CREATED，优化器可能选择全表扫（因为走索引要回表 18 万次）

关键指标是 rows（扫描行数），不是 key 有没有值 ✓

当 rows 很大时，优化器"故意不用索引"往往是对的**决策 ✓（回表的随机 IO 比顺序扫全表更慢）

错误 3：在 `WHERE` 里对索引列做任何运算

sql

WHERE DATE(created_at) = '2026-09-21'      -- ✗ 函数
WHERE user_id + 0 = 7777                   -- ✗ 运算
WHERE user_id = '7777'                     -- ⚠️ 隐式转换（MySQL 上会失效）
WHERE amount * 100 > 500000                -- ✗ 运算

统一修复思路：把"对列的计算"改写成"对常量的计算"：

sql

WHERE created_at >= '2026-09-21' AND created_at < '2026-09-22'    -- ✓
WHERE user_id = 7777                                              -- ✓
WHERE amount > 5000                                               -- ✓

错误 4：`LIKE '%xxx'` 用在需要索引的列上

见 5.3（2）。这个无法用"改写 SQL"解决——需要改数据结构（倒排索引 / 全文索引 / 冗余列）✓

错误 5：只看单条 SQL，不看调用频率

text

单条 5 ms，看起来不慢 ✓
但它每秒被调用 10000 次 → 数据库 CPU 打满 ✗

判断"哪条 SQL 值得优化"要综合两个维度：

维度	工具
单次耗时	`EXPLAIN` / 慢查询日志
调用频率	`performance_schema` / 应用层埋点

优化收益 = 单次节省的时间 × 调用频率 ✓

错误 6：在从库/测试库上看计划，到主库就变了

原因：数据分布不同（行数、选择性）→ 优化器可能选不同计划 ✗

实践建议：

在"数据量与分布接近生产"的环境上验证计划 ✓
上线后观察真实效果（慢查询数量、P99），不要只看测试环境的数字

10. 动手实验

目标：亲手把四种失效场景跑一遍，并验证"优化器差异"。

步骤：

运行第 7 节的命令，核对四组关键数据；
改数据量做对比：把 ROWS 从 20 万改成 2 万、再改成 2 千万（如果机器撑得住）——画出"数据量 vs 无索引耗时"的关系，理解"索引收益随数据量增长"；
验证"优化器故意不用索引"：把 status 改成"99% 都是同一个值"，然后 WHERE status = '那 99% 的值'——观察计划是不是变成了全表扫描（提示：选择性太低）；
验证"跑多次取最小值"的必要性：把轮数改成 1，连续跑三次，记录三次的差异；
加一个覆盖索引：建 (user_id, amount_in_cents) 索引，查 SELECT user_id, amount_in_cents FROM orders WHERE user_id = 7777——观察计划里是否出现"只读索引"的迹象（H2 里表现为计划不再需要回表）；
对照 MySQL 的字段名：把本节的一段 EXPLAIN 输出，逐列翻译成 MySQL 的 type / key / rows / Extra（这是本节最重要的迁移能力）。

验收方式：能说出第 3 步"优化器为什么放弃索引"、第 5 步覆盖索引省掉了什么。

扩展任务：给《MyBatis 数据持久化》的 MyBatis 项目加一段"慢 SQL 打点"：

用一个 MyBatis 拦截器（Interceptor）统计每条 SQL 的执行耗时；
超过阈值（如 100 ms）就打 WARN 日志，带上 SQL 与耗时；
说明：为什么"应用层的慢 SQL 打点"与"数据库的慢查询日志"两者都需要？（提示：前者知道"哪个接口触发的"，后者知道"数据库内部怎么执行的"）

11. 练习题

基础题

判断：只要建了索引，WHERE 条件里用到这一列就一定会走索引。（对 / 错）
填空：MySQL 里 type 为 ______ 表示全表扫描；PostgreSQL 里对应的是 ______；判断一条 SQL 是否低效，最该看的数字是 ______。
简答：为什么 LIKE '%关键字' 无法使用 B+ 树索引？

代码阅读题

sql

-- 表结构：orders(id BIGINT PK, user_id BIGINT, status VARCHAR(16), amount INT, created_at DATETIME)
-- 索引：idx_user (user_id), idx_created (created_at)

-- ①
SELECT id FROM orders WHERE user_id = 1001;

-- ②
SELECT id FROM orders WHERE DATE(created_at) = '2026-09-21';

-- ③
SELECT id FROM orders WHERE status = 'PAID' LIMIT 10;

-- ④
SELECT id FROM orders WHERE user_id = '1001';

-- ⑤
SELECT id FROM orders WHERE user_id = 1001 AND created_at >= '2026-01-01';

这五条 SQL 里，哪些能走索引、哪些不能？分别在什么情况下？
第 ⑤ 条能否用上 idx_created？为什么？（注意它的 WHERE 里有两个条件）

编程题

为一份"慢查询分析报告"写工具代码：
- 输入：MySQL 慢查询日志文件（格式见 5.4 节）；
- 要求：
  - 解析出每条慢 SQL 的 Query_time、Rows_examined、Rows_sent、SQL 文本；
  - 按"SQL 指纹"聚合（把 WHERE user_id = 123 与 WHERE user_id = 456 归为同一条，提示：用正则把数字/字符串字面量替换成 ?）；
  - 输出 Top 10：按"总耗时"排序（单次耗时 × 次数）；
  - 对每条 SQL 标出可疑信号（Rows_examined / Rows_sent > 100、Query_time > 1s）；
- 回答：为什么"按总耗时排序"比"按单次耗时排序"更有用？

综合任务

设计一套"慢查询治理方案"：
- 背景：系统上线半年，用户反馈"列表页偶尔很慢"，但开发在本地复现不出来；
- 要求：
  - 设计采集层：怎么把慢 SQL 收集起来（慢查询日志？应用层埋点？两者都要？），各自的盲区是什么；
  - 设计判定标准：什么算"慢"？（绝对阈值？相对基线？还是按接口的 P99？）说明你的选择理由；
  - 设计分析流程：从"告警响了"到"定位到 SQL"的每一步；
  - 设计修复与验证：修完之后怎么确认"真的好了"（不能只看"我本地跑得快了"）；
  - 设计防复发：怎么让"新写的低效 SQL"在合并前就被发现（提示：测试环境的数据量问题、SQL 评审、压测）；
- 额外要求：说明"为什么'本地复现不出来'是很常见的情况"，以及怎么解决它（提示：数据量、数据分布、并发、缓存命中率）。

分级提示

提示 1（方向）：第 1 题的关键是"索引列有没有被'处理'过"。

提示 2（关键点）：第 5 题注意 idx_created 是单列索引，而 WHERE 里有 user_id——这决定了优化器的选择。

提示 3（接近答案）：第 7 题的"本地复现不出来"，最典型的原因是数据量差了几个数量级——本地 1000 行怎么跑都快，生产 1000 万行才会暴露。

12. 参考答案

基础题

错。"用到这一列"和"能用这个索引"是两件事 ✓

四种常见的"用到了但用不上"（5.3 节）：

写法	为什么用不上
`WHERE YEAR(created_at) = 2026`	要对每行算函数，索引用不了
`WHERE user_id + 0 = 7777`	列参与了运算
`WHERE name LIKE '%x'`	B+ 树按前缀排序
`WHERE status = 'PAID'`（选择性极低）	优化器主动放弃（回表代价 > 全表扫）

⚠️ 最后一条最容易被忽略："没走索引"不一定是坏事——优化器可能算得比你准 ✓

所以判断标准不是"有没有用索引"，而是"扫描行数与耗时是否可接受" ✓

ALL；Seq Scan；扫描行数（MySQL 的 rows / EXPLAIN ANALYZE 的 actual rows / 慢查询日志的 Rows_examined）。

为什么"扫描行数"比"耗时"更该看：

对比	说明
耗时	受机器负载、缓存命中、并发影响，波动大 ✗
扫描行数	由 SQL 与索引结构决定，稳定可比较 ✓

一条经验："返回 20 行却扫了 20 万行"是一个明确的病灶——即使它当前"还没慢到报警"，数据量翻倍后一定会爆 ✗

因为 B+ 树是按"前缀有序"组织的。

具体地说：

text

B+ 树的查找过程：
  根节点 → 比较 "PAI..."     ← 能比较，因为知道开头
           → 进入某个子树 → ... → 定位到叶子节点

  但 "…AID" 的查询：
  根节点 → 我要找"以 AID 结尾"的 → **不知道该去哪个子树** ✗
           → 只能遍历所有叶子节点（＝全表扫描）

更本质的说法：B+ 树是一种"有序结构"，它只能帮你定位"有序的那一端" ✓

这也解释了为什么"前缀匹配能用索引、中间匹配不能"——有序性只体现在从开头开始的比较上 ✓

代码阅读题（第 4、5 题）

第 4 题：逐条分析。

#	SQL	能否走索引	原因
①	`WHERE user_id = 1001`	✅ 能（`idx_user`）	列独立、等值、类型一致
②	`WHERE DATE(created_at) = '2026-09-21'`	❌ 不能	索引列被包在函数里 ✗（改写成范围条件即可）
③	`WHERE status = 'PAID' LIMIT 10`	❌ 没有 `status` 的索引	表上只有 `idx_user` 与 `idx_created` ✗
④	`WHERE user_id = '1001'`	⚠️ 取决于数据库	MySQL：可能失效（隐式类型转换）；H2/PostgreSQL：能走（本节实测过）
⑤	`WHERE user_id = 1001 AND created_at >= '2026-01-01'`	✅ 能（`idx_user`）	用 `user_id` 过滤后，再筛 `created_at`

第 ② 条的修复：

sql

-- ✗
WHERE DATE(created_at) = '2026-09-21'

-- ✓ 等价改写：范围条件
WHERE created_at >= '2026-09-21 00:00:00' AND created_at < '2026-09-22 00:00:00'

第 ③ 条的修复（两种）：

sql

-- 方案一：给 status 建索引（如果这类查询很多）
CREATE INDEX idx_status ON orders (status);
-- ⚠️ 但 status 选择性低（重复值多），单列索引效果可能有限

-- 方案二：建复合索引，把"过滤 + 排序"一起覆盖
CREATE INDEX idx_status_created ON orders (status, created_at);

第 ④ 条的说明（值得记住）：

数据库	`user_id = '1001'` 的行为
MySQL	把列转成字符串比较 → 索引失效 ✗
PostgreSQL	把参数转成数字 → 索引可用 ✓
H2	同 PostgreSQL ✓（本节实测计划里是 `CAST(1001 AS BIGINT)`）

实践结论：参数类型一定要与列类型一致——即使某个数据库能容忍，也不要依赖它 ✓

第 5 题：能用上 idx_user，但用不上 idx_created。

分析：

sql

WHERE user_id = 1001 AND created_at >= '2026-01-01'

有几种可能的计划：

计划	什么时候会选它
用 `idx_user`	某个用户的订单不多（如几十条）→ 先用等值条件大幅缩小范围，再逐行筛 `created_at` ✓ 最可能
用 `idx_created`	时间范围很窄、命中行很少时
全表扫描	如果 `user_id = 1001` 命中的行非常多（比如这个用户有 100 万订单）✗

为什么用不上 idx_created：

WHERE 里有两个条件，但优化器通常只选"一个"索引（MySQL 的"索引合并"是例外，但代价往往更高）✓
created_at >= '2026-01-01' 这个范围太宽（几乎覆盖全表），用它还不如用 user_id ✓

真正的高效解法：建复合索引 (user_id, created_at) ✓

sql

CREATE INDEX idx_user_created ON orders (user_id, created_at);

这样一条索引同时解决了两件事：

作用	说明
过滤 `user_id`	用索引第一列精确匹配
过滤 `created_at`	在同一个索引里继续用第二列做范围扫描 ✓（最左前缀）

这就是"复合索引的列顺序要看查询"的实际应用（《索引》讲过）✓

一个从这道题能总结出的规则：

"等值条件"应放在复合索引的前面，"范围条件"放在后面。
原因：一旦遇到范围条件，后面的列就无法再用索引定位了（B+ 树在范围内只能顺序扫）✓

编程题（第 6 题）

java

package cn.kb.learn.data;

import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/** 解析 MySQL 慢查询日志，按 SQL 指纹聚合出最值得优化的 Top 10 */
public class SlowQueryAnalyzer {

    /** 一条慢查询记录 */
    public record SlowQuery(String sql, double queryTime, long rowsExamined, long rowsSent) { }

    /** 聚合后的统计 */
    public record SqlStat(String fingerprint, String sample, int count,
                          double totalTime, double maxTime, long totalRowsExamined) {

        /** 可疑信号：扫描行数远超返回行数 */
        boolean isLowEfficiency(int threshold) {
            return count > 0 && totalRowsExamined / count > threshold;
        }
    }

    /** 把字面量替换成 ? —— 这是"SQL 指纹"的核心：让同类 SQL 聚合到一起 */
    private static final Pattern LITERAL = Pattern.compile(
            "'(?:[^'\\\\]|\\\\.)*'|\\b\\d+(?:\\.\\d+)?\\b");

    static String fingerprint(String sql) {
        String normalized = LITERAL.matcher(sql).replaceAll("?");
        return normalized.replaceAll("\\s+", " ").trim();
    }

    public static void main(String[] args) throws IOException {
        Path logFile = Path.of(args.length > 0 ? args[0] : "slow.log");
        List<SlowQuery> queries = parse(Files.readAllLines(logFile, StandardCharsets.UTF_8));

        Map<String, List<SlowQuery>> grouped = queries.stream()
                .collect(Collectors.groupingBy(q -> fingerprint(q.sql())));

        List<SqlStat> stats = grouped.entrySet().stream()
                .map(entry -> {
                    List<SlowQuery> list = entry.getValue();
                    SlowQuery sample = list.get(0);
                    return new SqlStat(
                            entry.getKey(),
                            sample.sql(),
                            list.size(),
                            list.stream().mapToDouble(SlowQuery::queryTime).sum(),
                            list.stream().mapToDouble(SlowQuery::queryTime).max().orElse(0),
                            list.stream().mapToLong(SlowQuery::rowsExamined).sum());
                })
                .sorted(Comparator.comparingDouble(SqlStat::totalTime).reversed())   // ★ 按总耗时排序
                .limit(10)
                .toList();

        System.out.println("=== 慢查询 Top 10（按总耗时排序）===");
        for (SqlStat stat : stats) {
            System.out.printf("%.2fs 总耗时 | %d 次 | 最大 %.2fs | 累计扫描 %d 行%s%n",
                    stat.totalTime(), stat.count(), stat.maxTime(), stat.totalRowsExamined(),
                    stat.isLowEfficiency(100) ? "  ⚠️ 低效：扫描行数远超返回行数" : "");
            System.out.println("   " + stat.sample());
        }
    }

    /** 解析 mysqldumpslow / 原生慢日志的常见格式 */
    static List<SlowQuery> parse(List<String> lines) {
        List<SlowQuery> result = new ArrayList<>();
        double queryTime = -1;
        long rowsExamined = -1;
        long rowsSent = -1;
        for (String line : lines) {
            if (line.startsWith("# Query_time:")) {
                Matcher matcher = Pattern.compile(
                        "Query_time:\\s*([\\d.]+).*Rows_sent:\\s*(\\d+)\\s+Rows_examined:\\s*(\\d+)")
                        .matcher(line);
                if (matcher.find()) {
                    queryTime = Double.parseDouble(matcher.group(1));
                    rowsSent = Long.parseLong(matcher.group(2));
                    rowsExamined = Long.parseLong(matcher.group(3));
                }
            } else if (line.startsWith("SELECT") || line.startsWith("UPDATE") || line.startsWith("DELETE")) {
                if (queryTime >= 0) {
                    result.add(new SlowQuery(line.trim(), queryTime, rowsExamined, rowsSent));
                }
                queryTime = -1;
            }
        }
        return result;
    }
}

为什么"按总耗时排序"比"按单次耗时排序"更有用：

排序方式	会先看到什么	优化收益
按单次耗时	那条 10 秒的复杂报表 SQL	优化它 = 省下 10 秒 × 每天 2 次 = 20 秒/天 ⚠️
按总耗时 ★	那条 50 ms 但每秒调用 1000 次的查询	优化它 = 省下 50 ms × 8640 万次/天 ≈ 1200 小时/天 ✓✓

一句话：

优化收益 = 单次节省 × 调用频率。
只看"单次最慢的那条"会优化错对象 ✗ ——数据库的 CPU 往往是被"又快又频繁"的查询吃掉的 ✓

这也解释了 performance_schema 的 SUM_TIMER_WAIT 为什么是按"累计"排序的 ✓

SQL 指纹的两个作用：

作用	说明
聚合同类 SQL	`WHERE user_id = 123` 与 `= 456` 是同一个问题，不该分开看 ✓
识别"参数化缺失"	如果同一类 SQL 出现了几百个不同的指纹，说明可能没做参数化（每次拼串）→ 还会导致执行计划缓存失效 ✗

综合任务（第 7 题）

采集层设计

手段	能拿到什么	盲区
数据库慢查询日志	SQL 文本、耗时、扫描行数、锁等待	不知道是哪个接口/用户触发的 ✗；只记录超阈值的
应用层埋点（MyBatis 拦截器 / AOP）	接口名、traceId、参数量级、调用来源 ✓	不知道数据库内部怎么执行的 ✗（看不到扫描行数/是否走索引）
`performance_schema`	全量 SQL 的聚合统计（不用等超阈值）	默认可能有性能开销；数据会滚动

结论：三者都要，而且靠 traceId 串起来 ✓

text

慢日志：Query_time 3.2s，Rows_examined 200000
   ↓ 找到具体是哪一次执行（时间戳对齐）
应用日志（按 traceId）：GET /api/orders?page=10000 由 user=1001 触发
   ↓ 两者结合
结论：深分页查询，缺 (user_id, id) 复合索引

判定标准：为什么"绝对阈值"不够

标准	优点	缺点
绝对阈值（如 > 1s）	简单、易懂	会漏掉"50ms 但每秒 1000 次"的查询 ✗
相对基线（与上周同时间对比）	能发现"退化" ✓	需要历史数据
按接口 P99	贴近用户体验 ✓	需要完善的监控体系

推荐组合：

用途	标准
紧急告警	绝对阈值（P99 > 2s 或错误率上升）
日常治理	按总耗时排序的 Top 20（编程题 6 的思路）✓
防退化	与上周基线对比（涨了 50% 就查）

分析流程（从告警到定位）

text

① 告警："订单列表接口 P99 超过 2s"
② 看监控：是"一直慢"还是"刚刚变慢"？→ 决定方向
③ 找 SQL：慢日志 Top / performance_schema Top / 应用埋点
④ 看计划：EXPLAIN 这条 SQL
⑤ 定位原因：对照 5.3 的四种典型 + "数据量变化导致计划变了"
⑥ 修正 + 再 EXPLAIN 确认
⑦ 观察线上指标（不是"我看它快了"，而是"P99 从 2s 降到 200ms"）

修复与验证：怎么确认"真的好了"

验证方式	可信度
"我本地跑得快了"	❌ 最低（数据量不同）
"测试环境跑得快了"	⚠️ 中（数据分布可能仍不同）
"执行计划确认走索引了 + 扫描行数从 20 万降到 20"	✅ 高（这是根本原因的直接证据）
"线上 P99 从 2s 降到 200ms"	✅ 最高（用户体验的直接证据）

建议：至少做到"执行计划 + 扫描行数"的对比——因为它不依赖机器性能，是可比较的硬指标 ✓

防复发

手段	强度	说明
SQL 评审（新写的复杂 SQL 要有人看）	⚠️ 弱	靠人
测试环境数据量对齐（至少百万级）	✅ 强	让问题在测试期暴露 ✓
压测（T2-015）	✅ 强	在预发环境用生产量级数据压
慢 SQL 单测/集成测试（断言"扫描行数 < N"）	✅ 强	可以用 `EXPLAIN` 的结果做断言 ✓
上线后的持续监控 + 基线告警	✅ 强	兜底

"为什么本地复现不出来"——四个常见原因：

#	原因	为什么致命
1	数据量差几个数量级	1000 行时"无索引"也是 1ms；1000 万行时是 10s ✗
2	数据分布不同	本地数据"均匀"，生产数据"某个值占 90%"→ 优化器行为完全不同 ✗
3	并发不同	本地单请求；生产几百并发 → 锁等待、连接池排队 ✗
4	缓存命中率不同	本地首次查就已经预热；生产缓存频繁失效 ✗

解决这些的统一思路：

让"测试环境"尽可能接近生产——数据量级、数据分布、并发压力三个维度都要靠近 ✓
而 T1-017 讲的 Testcontainers 只解决了"数据库版本一致"，解决不了"数据量级一致" ✗ ——后者要靠"脱敏后的生产数据导入"或"逐级压测" ✓

13. 掌握标准

[ ] 会用 EXPLAIN，并能说出 MySQL 的 type / key / rows / Extra 各自看什么；
[ ] 知道 PostgreSQL 的 Seq Scan / Index Scan 与 MySQL 的对应关系；
[ ] 知道"扫描行数"比"耗时"更该作为判断依据；
[ ] 能说出四种索引失效场景，并会改写；
[ ] 知道"失效与否取决于优化器"，因此必须实测；
[ ] 能说出复合索引最左前缀的机制，以及"等值在前、范围在后"的列序原则；
[ ] 会开 MySQL 慢查询日志，会读 Query_time 与 Rows_examined；
[ ] 知道"按总耗时排序"比"按单次耗时排序"更能找到优化对象；
[ ] 知道"一直慢"与"突然变慢"排查方向不同。

14. 扩展资源

15. 下一步学习

《MySQL 性能优化基础》：从"知道慢在哪"到"知道怎么改"。

前置知识

S3-003 索引

后续知识

S3-016 MySQL 性能优化基础

进度只保存在本机浏览器，可随时清除

外部学习资源

面试题库索引

项目与练手索引

工具与软件索引

执行计划与慢查询定位

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 执行计划怎么看

5.2 实测：索引带来的差异

5.3 四种典型的索引失效

5.4 慢查询定位的流程

5.5 定位流程：从"用户说慢"到"改完验证"

6. 递进示例

6.1 最小示例：看一条 SQL 的计划

6.2 业务示例：四组对比

6.3 反例：为了"性能"给每列都建索引

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：以为"建了索引就用上了"

错误 2：只看"有没有索引"，不看"扫了多少行"

错误 3：在 `WHERE` 里对索引列做任何运算

错误 4：`LIKE '%xxx'` 用在需要索引的列上

错误 5：只看单条 SQL，不看调用频率

错误 6：在从库/测试库上看计划，到主库就变了

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习

前置知识

后续知识

执行计划与慢查询定位 ​

1. 本节定位 ​

2. 学习目标 ​

3. 前置知识检查 ​

4. 为什么需要这个知识 ​

5. 核心概念 ​

5.1 执行计划怎么看 ​

5.2 实测：索引带来的差异 ​

5.3 四种典型的索引失效 ​

5.4 慢查询定位的流程 ​

5.5 定位流程：从"用户说慢"到"改完验证" ​

6. 递进示例 ​

6.1 最小示例：看一条 SQL 的计划 ​

6.2 业务示例：四组对比 ​

6.3 反例：为了"性能"给每列都建索引 ​

7. 完整可运行代码 ​

8. Debug 观察点 ​

9. 常见错误 ​

错误 1：以为"建了索引就用上了" ​

错误 2：只看"有没有索引"，不看"扫了多少行" ​

错误 3：在 WHERE 里对索引列做任何运算 ​

错误 4：LIKE '%xxx' 用在需要索引的列上 ​

错误 5：只看单条 SQL，不看调用频率 ​

错误 6：在从库/测试库上看计划，到主库就变了 ​

10. 动手实验 ​

11. 练习题 ​

12. 参考答案 ​

13. 掌握标准 ​

14. 扩展资源 ​

15. 下一步学习 ​

前置知识

后续知识

执行计划与慢查询定位

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 执行计划怎么看

5.2 实测：索引带来的差异

5.3 四种典型的索引失效

5.4 慢查询定位的流程

5.5 定位流程：从"用户说慢"到"改完验证"

6. 递进示例

6.1 最小示例：看一条 SQL 的计划

6.2 业务示例：四组对比

6.3 反例：为了"性能"给每列都建索引

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：以为"建了索引就用上了"

错误 2：只看"有没有索引"，不看"扫了多少行"

错误 3：在 `WHERE` 里对索引列做任何运算

错误 4：`LIKE '%xxx'` 用在需要索引的列上

错误 5：只看单条 SQL，不看调用频率

错误 6：在从库/测试库上看计划，到主库就变了

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习