MySQL 性能优化基础

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

1. 本节定位

《执行计划与慢查询定位》解决"怎么知道慢在哪"，本节解决"知道之后怎么改"。

但先要纠正一个最常见的误区：

text

误区：系统慢 → 加缓存 → 还慢 → 加机器 → 还慢 → 加索引
正确：系统慢 → 先测量 → 找到瓶颈 → 改最小的地方 → 再测量

本节的第一个知识点不是技术，是顺序。

2. 学习目标

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

说出优化的优先级顺序（从"改 SQL"到"加机器"），并解释为什么这个顺序；
说出建索引的三项代价（写入、空间、优化器负担），并能用实测数据说明；
说出覆盖索引的原理与收益；
说出深分页为什么慢，并会用游标（Keyset Pagination）改写；
说出 N+1 查询问题的识别方法与修复；
说出"批量操作"为什么比"循环单条"快；
知道"缓存、读写分离、分库分表"各自解决什么问题，以及它们的代价；
说出"不要过早优化"的具体含义（不是"不要优化"，而是"不要在没有证据时优化"）。

3. 前置知识检查

知道 B+ 树索引、复合索引与最左前缀吗？（《索引》）
会读执行计划吗？（《执行计划与慢查询定位》）

4. 为什么需要这个知识

优化的成本是不对称的：

手段	工作量	引入的复杂度	效果
改写一条 SQL	几分钟	零	可能快 100 倍 ✓
加一个索引	几分钟（+ 上线窗口）	低（写入变慢）	可能快 100 倍 ✓
加缓存	几天	中（一致性、失效、穿透） ⚠️	看命中率
读写分离	几周	高（主从延迟、读不一致） ⚠️	分摊读压力
分库分表	几个月	极高（改所有 SQL、分布式事务） ✗	解决容量上限
加机器（垂直扩容）	半天	低	有上限且贵

问题在于"顺序常常是反的"：

因为"加缓存/加机器"看起来更"高级"，而"改写一条 SQL"看起来太朴素 ✗
结果：花了两周加缓存，而真正的问题是一个索引缺失 ✓

一条朴素的判断：

越靠近数据的地方优化，收益越大、成本越低、副作用越小。
所以顺序是：SQL → 索引 → 表结构 → 架构 → 机器。 ✓

5. 核心概念

5.1 优化的优先级顺序

从"收益/成本比"最高的开始：

优先级	手段	什么时候用	代价
① 删掉不必要的查询	缓存了又查一遍、循环里重复查	最先检查——最好的优化是不做这件事 ✓	零
② 改写 SQL	索引失效的写法、`SELECT *`、不必要的排序	执行计划里有问题	零
③ 加/改索引	缺索引、复合索引列序不对、覆盖索引	执行计划显示全表扫描	写入变慢（5.2）
④ 改表结构	冗余字段、拆分大字段（`TEXT`）、归档历史数据	表太宽/太大	需要迁移
⑤ 改架构	缓存、读写分离、异步化	单库读写到瓶颈	一致性复杂 ✗
⑥ 分库分表	单表数据量到千万级且无法归档	最后手段	极高 ✗
⑦ 加机器	以上都做过	—	钱

⚠️ 有一条贯穿始终的原则：

每一步都要有"改动前后"的测量数据。
没有测量就没有优化，只有"试试看"。 ✓

5.2 索引不是免费的

这是本节最重要的实测。

实验设计：插入 10 万行，其他条件不变，只改"表上有没有索引"。

实测结果（本机 H2 内存库，跑两次）：

运行	无索引	4 个索引	写入成本增加
第 1 次	1123 ms	1864 ms	+66%
第 2 次	988 ms	1364 ms	+38%

两次都显示"写入变慢" ✓ 绝对值有波动（本机环境的常态），趋势一致。

⚠️ 而且这个数字被严重低估了：

环境	为什么真实差距更大
H2 内存库	索引维护在内存里，随机写不痛
真实 MySQL（机械盘/云盘）	每次插入要在多个 B+ 树上做随机 IO ✗
批量导入场景	索引多时可能需要"先删索引、导完重建"

索引的三项代价：

代价	说明
写入变慢	每次 `INSERT`/`UPDATE`/`DELETE` 都要维护所有相关索引
占用空间	索引本身可能达到数据大小的 30%~100%
优化器负担	索引太多，选错计划的概率上升 ⚠️

对应的三条实践规则：

规则	理由
按查询建索引，不要按列建	每个索引都该有明确的"服务对象" ✓
定期清理没人用的索引	MySQL 8.0 的 `sys.schema_unused_indexes` 能列出从未被使用过的索引 ✓
写多读少的表少建索引	日志表、事件表要克制 ⚠️

顺带一个"读的收益"对照（同一次实验）：

场景	耗时
无索引查 `user_id`（返回 11 行）	11 ms
有索引查 `user_id`	0 ms

11 ms → 0 ms —— 这就是"用写换读"的具体样子 ✓

⚠️ 但注意"写多读少"和"读多写少"必须分开算：

text

日志表：每秒写 10000 条，每天查 10 次
  → 建索引：写入慢 40%，查询快 100 倍
  → 但"查询快"总共只省下几百毫秒；"写入慢"每天多花几十分钟 ✗✗
  → 结论：不加索引，或者只加一个最必要的 ✓

5.3 覆盖索引：不回表

概念：如果索引里已经包含了查询需要的所有列，就不必回表读数据行 ✓

sql

-- 索引：idx_user_created (user_id, created_at)

-- ✗ 需要回表（要读 amount，索引里没有）
SELECT user_id, created_at, amount FROM orders WHERE user_id = 7777;

-- ✓ 覆盖索引（需要的三列都在索引里）
SELECT user_id, created_at FROM orders WHERE user_id = 7777;

	普通索引	覆盖索引
查找过程	索引树 → 拿主键 → 回表读行	索引树里就拿到全部数据 ✓
IO	两次（索引 + 数据）	一次 ✓
MySQL 的计划里	`Extra: Using index condition`	`Extra: Using index` ✓

实践建议：

场景	做法
列表页只查几列（不带 `SELECT *`）	把这几列放进复合索引 → 覆盖索引 ✓
需要全部列	覆盖索引也没用（索引不可能包含所有列）——改为"先查主键再查详情"两段式 ✓

⚠️ 别忘了索引太长也有代价：把 TEXT 字段放进索引会让索引膨胀 ✗

5.4 深分页：`LIMIT OFFSET` 的代价

现象：

sql

SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 199000;

看起来"只取 20 行"，实际呢？

text

数据库必须：按 id 顺序扫过前 199000 行（读完就丢）→ 再返回第 199001~199020 行

扫描行数 = OFFSET + LIMIT ✗

实测对比（《执行计划与慢查询定位》的同一张 20 万行表）：

写法	耗时
`LIMIT 20 OFFSET 199000`	18 ms
游标式：`WHERE id > 199000 ORDER BY id LIMIT 20`	0 ms

改写方式（Keyset Pagination / 游标分页）：

sql

-- ✗ 深分页：OFFSET 越大越慢
SELECT id, user_id FROM orders ORDER BY id LIMIT 20 OFFSET 199000;

-- ✓ 游标式：带上"上一页最后一条的 id"
SELECT id, user_id FROM orders WHERE id > 199000 ORDER BY id LIMIT 20;

原理：

	`OFFSET`	游标
数据库怎么找第 199001 行	从头数过去 ✗	直接在索引上定位 ✓
复杂度	O(offset + limit)	O(limit) ✓
翻到第 1 万页	慢得无法接受 ✗	还是很快 ✓

代价与适用边界：

限制	说明
不能跳页	只能"下一页"（想知道"第 500 页"，游标做不到）✗
排序字段必须唯一或有唯一兜底	否则可能重复或漏数据（通常用 `(created_at, id)` 组合）
前端要改	从"页码"改成"加载更多"（无限滚动）✓

什么时候必须用：

场景	选择
无限滚动 / 加载更多	游标 ✓
只允许翻前几页（如搜索结果的 1~10 页）	`OFFSET` 可接受 ✓
需要"跳到第 N 页"	`OFFSET`（但要限制最大页码，如最多 100 页）⚠️
导出全部数据	游标（分批拉取）✓

另一条思路（如果必须支持跳页）：

sql

-- 先只查主键（覆盖索引，不用回表），再按主键取详情
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 199000;
-- 然后 SELECT * FROM orders WHERE id IN (...20 个 id...)

收益：扫过的 20 万行只读了主键索引（比读整行数据便宜得多）✓

⚠️ 但它没有改变"要扫 20 万行"这个事实——只是每行的成本更低 ✓ 所以页码特别大时仍然要换方案。

5.5 `COUNT` 优化

问题：分页时几乎都要 COUNT(*)——而大表上的 COUNT(*) 本身就是个慢查询 ✗

先澄清三个常见误解：

说法	真相
"`COUNT(1)` 比 `COUNT(*)` 快"	❌ MySQL 的优化器会把它们处理成一样 ✓
"`COUNT(列)` 更快"	❌ 它还要判断列是否非空，通常更慢 ✗
"`COUNT(*)` 会读全部数据"	⚠️ InnoDB 需要扫描索引（不一定要读行），但确实是 O(n)

为什么会慢：InnoDB 不保存"表的总行数"（因为 MVCC 下不同事务看到的行数可能不同）✗ 所以只能真去数。

四种优化思路：

思路	做法	代价
① 不需要精确值时不查 ★	前端显示"约 1 万条"，或用"是否还有下一页"判断	改交互设计 ✓
② 用近似值	`SHOW TABLE STATUS` 的 `Rows`（InnoDB 是估算值）	不准 ⚠️
③ 限制 COUNT 的范围	`SELECT COUNT(*) FROM (SELECT 1 FROM t WHERE ... LIMIT 10000) x`	只精确到 1 万以内 ✓
④ 维护计数表	单独一张表记录总数，增删时更新	一致性要自己保证 ✗

思路 ① 值得展开（最推荐）：

text

"共 12,345 条，当前第 3 页"          ← 需要精确 COUNT
"还有更多"（滚动加载）               ← 不需要 COUNT ✓
"约 1.2 万条"                        ← 近似值就够 ✓

很多"性能问题"其实是"需求问题"：前端真的需要精确总数吗？还是"看起来专业"？ ✓
这是本节 5.1 节"优先级 ①：删掉不必要的查询"的真实例子。

5.6 N+1 查询问题

现象：一次请求发出了 N+1 条 SQL ✗

java

// ✗ 典型 N+1
List<Order> orders = orderMapper.selectList(null);          // 1 条：查订单
for (Order order : orders) {
    User user = userMapper.selectById(order.getUserId());   // N 条：每条订单查一次用户 ✗
    order.setUserName(user.getName());
}

100 个订单就是 101 次数据库往返 ✓

为什么难发现：单条 SQL 都很快（1 ms），但 101 次往返的"网络 RTT + 连接开销"就很可观 ✗

数据量	1 条 SQL 耗时	N+1 总耗时
10 条订单	1 ms	~11 ms（感觉不出来）⚠️
100 条	1 ms	~110 ms（开始有感觉）
1000 条	1 ms	~1100 ms（明显慢） ✗

这就是"测试环境还好、生产很慢"的常见原因之一 ✓

识别方法：

方法	说明
开 SQL 日志数条数	《MyBatis 数据持久化》的 `log-impl` 打开后，数一次请求发了几条 ✓
应用层埋点	统计"单次请求的 SQL 条数"，超过阈值告警 ✓
review 代码	看到"循环里调 Mapper"就要警觉 ★

三种修复：

方案	做法	适用
① 一次 JOIN 查完 ★	写 XML 用 `LEFT JOIN` + `resultMap` 嵌套映射（《MyBatis 数据持久化》的 5.4 节）	最常用 ✓
② 批量查再内存组装	先收集所有 `userId`，一次 `SELECT ... WHERE id IN (...)`，再在 Java 里 map 起来	JOIN 太复杂时 ✓
③ 用 MyBatis-Plus 的 `@TableField(exist=false)` + 二次填充	查完主表后批量补全	同上 ✓

方案 ② 的示例（很实用，值得记住）：

java

// ✓ 两条 SQL 搞定
List<Order> orders = orderMapper.selectList(null);                     // 1 条
Set<Long> userIds = orders.stream().map(Order::getUserId).collect(toSet());
Map<Long, User> userMap = userMapper.selectBatchIds(userIds)           // 1 条（IN 查询）
        .stream().collect(toMap(User::getId, identity()));
orders.forEach(order -> order.setUserName(userMap.get(order.getUserId()).getName()));

1 + N 变成 1 + 1 ✓

5.7 批量操作

已在《JDBC 与数据库连接》的 5.6 节实测过：

text

插入 2000 行 —— 逐条 executeUpdate：148 ms
插入 2000 行 —— addBatch/executeBatch：66 ms

⚠️ 一个关键的补充：MySQL 下必须配驱动参数才真的快 ✗

text

jdbc:mysql://host:3306/db?rewriteBatchedStatements=true
                                   ↑ 不加它，JDBC 的"批处理"会被驱动拆成逐条 ✗

验证方式：看 SQL 日志里是多少条 INSERT ✓

"用了批处理接口但没开驱动参数"是典型的"以为优化了其实没有" ✓

5.8 缓存、读写分离与分库分表

本节只讲"它们各自解决什么问题、代价是什么"（具体实现留给 S4）。

手段	解决什么	代价	什么时候该用
缓存（Redis）	读压力、热点数据延迟	一致性（S4 展开）、穿透/击穿/雪崩	读多写少 + 能接受短暂不一致
读写分离	读压力（分摊到从库）	主从延迟（写完立刻读可能读不到）✗	读远多于写
分库分表	容量上限（单表过大、单库 IO 到顶）	改所有 SQL、分布式事务、运维复杂 ✗✗	单表 > 千万行且无法归档

⚠️ 三个"必须先想清楚"的问题：

手段	先问
加缓存	"缓存失效的瞬间，数据库扛得住吗？"（这是"雪崩"的本质）✗
读写分离	"写完立刻读的场景，能接受读到旧数据吗？"（强一致场景不能用）✗
分库分表	"真的到了容量上限吗？还是 SQL/索引没优化？" ——绝大多数项目属于后者 ✓

最后一条尤其重要：

分库分表是"不可逆"的架构决策——它会把简单的事情永久变复杂 ✗
在做它之前，先确认"单个表的数据量真的降不下来"（历史数据归档、冷热分离往往就够了）✓

5.9 "不要过早优化"到底是什么意思

Knuth 的原话常被断章取义：

"过早优化是万恶之源" —— 不是说"不要优化" ✗
而是说"不要在没有测量数据时优化" ✓

两种极端都要避免：

极端	表现	后果
过度	一上来就分库分表、给每列建索引、"这个循环会不会慢"	复杂度爆炸、工期延误 ✗
不足	明知会全表扫描也不建索引、"上线再说"	上线即崩 ✗

正确的态度：

text

① 写的时候就避免"已知的低效写法"
   - 不在循环里查数据库（N+1）
   - 不给索引列套函数
   - 不用 SELECT *
   ↓ 这些是"常识"，不需要测量
② 上线前用"接近生产的数据量"验证关键路径
   ↓ 有测量才谈得上优化
③ 上线后靠监控发现"没预料到的问题"
   ↓ 新的场景、新的数据分布

判断标准：

"这条优化的成本是多少？"
成本接近零（改写一条 SQL、少写一个 SELECT *）→ 直接做，不用测量 ✓
成本高（加缓存层、分表、改架构）→ 必须有数据支撑 ✓

6. 递进示例

6.1 最小示例：把 `SELECT *` 改成具体列

sql

-- ✗ 取回不需要的列（网络传输 + 无法用覆盖索引）
SELECT * FROM orders WHERE user_id = 7777;

-- ✓ 只取需要的列
SELECT id, amount_in_cents FROM orders WHERE user_id = 7777;

6.2 业务示例：N+1 的两种修法

见 5.6 节。

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);
CREATE INDEX idx_e ON orders (user_id, status, amount_in_cents, created_at);   -- 又长又杂

三重代价（5.2 的实测）：写入变慢、空间膨胀、优化器更容易选错 ✗

正确做法：

步骤	做什么
1	从慢查询日志/`performance_schema` 找出真实的慢查询
2	看它们的 `WHERE` / `ORDER BY` / `JOIN` 条件
3	为这些查询设计 2~3 个复合索引（等值列在前、范围列在后）
4	上线后监控 `sys.schema_unused_indexes`，删掉没人用的 ✓

7. 完整可运行代码

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

环境说明：示例跑在 H2 的 MySQL 兼容模式内存库上。绝对值与真实 MySQL 不同，但**"写入变慢、读取变快"的趋势是一致的** ✓
⚠️ 真实环境上的差距更大：H2 的索引维护在内存里做，随机 IO 不痛；MySQL 的索引维护是磁盘随机写 ✗

核心代码：

java

public class PerformanceLesson {
    private static final int ROWS = 100_000;

    public static void main(String[] args) throws SQLException {
        System.out.println("===== 1. 索引的代价：写入变慢 =====");
        long noIndex = insertRows("orders_no_index", false);
        long withIndex = insertRows("orders_with_index", true);
        System.out.printf("插入 %d 行 —— 表上无索引　：%d ms%n", ROWS, noIndex);
        System.out.printf("插入 %d 行 —— 表上 4 个索引：%d ms%n", ROWS, withIndex);
        // ...
    }
}

运行命令：

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.PerformanceLesson

预期输出（真实运行结果）：

text

===== 1. 索引的代价：写入变慢 =====
插入 100000 行 —— 表上无索引　：988 ms
插入 100000 行 —— 表上 4 个索引：1364 ms
写入成本增加：376 ms（约 38%）
注意：这是 H2 内存库的数字；在真实数据库上，索引带来的随机 IO 会让差距更大。

===== 2. 索引的收益：读取变快 =====
无索引表查一个 user_id：
  返回 11 行，最快耗时 11 ms
给有索引的表再加 user_id 索引后：
  返回 11 行，最快耗时 0 ms

===== 3. 两张表各自的索引清单 =====
  orders_no_index → []
  orders_with_index → []
  ← 本机 H2 的 information_schema 结构与 MySQL 不同，这里没取到元数据；
     MySQL 里用 SHOW INDEX FROM orders_with_index 可以看到 3 个索引

===== 4. 结论 =====
索引是「用写入变慢 + 占用空间」换「读取变快」。
所以：按查询建索引，不要给每一列都建；并且定期清理没人用的索引。

两处关键数据：

对比	数据	结论
写入	988 ms → 1364 ms（+38%）	索引不是免费的——`INSERT` 要同时维护多个 B+ 树
读取	11 ms → 0 ms	这就是"用写换读"

⚠️ 关于波动的诚实说明：同一份代码另一次运行的写入数据是 1123 ms → 1864 ms（+66%）。

要看的不是绝对值，而是"方向与量级"：加了索引之后写入一定变慢、读取一定变快 ✓ 这个结论在任何一次运行里都成立。

第 3 节的 [] 也是一个真实的教学点：

information_schema / 元数据查询是"方言差异最大"的地方——同一条 SQL 在 MySQL / PostgreSQL / H2 上往往都跑不通 ✗
所以跨数据库的运维脚本要格外小心（这也是为什么 T1-017 强调"用 Testcontainers 跑和生产一样的数据库"）✓

8. Debug 观察点

把 ROWS 从 10 万改成 1 万：观察写入差异缩小（索引的绝对代价随数据量增长）；
把索引数量从 2 个加到 5 个：观察写入进一步变慢（代价与索引数量近似成正比）；
只保留 user_id 索引、去掉 created_at 索引：观察读取是否仍然快（确认收益来自"这一个"索引）；
对比两次运行的写入数字（988/1364 与 1123/1864）：理解**"性能测试必须多次运行"**；
把 TABLE 换成一张只有 1 万行的表：观察"加索引的写入代价"是否还明显（小表上索引的收益与代价都不大）。

9. 常见错误

错误 1：先加缓存，不看 SQL

症状：加了 Redis 之后"快了一阵"，但数据量一涨又慢了 ✗ —— 因为根本问题（全表扫描）没解决。

正确顺序：先看执行计划，再考虑缓存（5.1）✓

错误 2：以为 `COUNT(1)` 比 `COUNT(*)` 快

真相：MySQL 的优化器对它们的处理完全一样 ✓（5.5）

真正该优化的是"要不要查总数" ✓

错误 3：深分页直接用 `OFFSET`

症状：前几页很快，翻到第 1000 页变成几秒 ✗

修复：游标分页（5.4）✓ 或限制最大页码 ✓

错误 4：循环里查数据库（N+1）

识别：看到"循环里调 Mapper" 就要警觉 ✓

修复：JOIN 或批量 IN 查询（5.6）✓

错误 5：批量操作没开 `rewriteBatchedStatements`

见 5.7。"以为优化了其实没有" ✗

错误 6：为了"性能"提前分库分表

症状：业务还在验证期，团队先花三个月做了分片 ✗ —— 上线后发现单表 100 万行根本不需要分 ✗

修复思路：先归档历史数据（往往数据量就降下来了）✓

错误 7：只看"平均耗时"，不看 P99

text

平均 50 ms，看起来很好 ✓
但 P99 是 3 秒 ✗ —— 1% 的用户体验极差

原因：慢请求往往是"走了不同路径"的那批（数据量大、缓存未命中、锁等待）⚠️

正确做法：看 P95 / P99，而不是平均值 ✓

10. 动手实验

目标：把"索引的代价"与"深分页"两件事亲手测一遍。

步骤：

运行第 7 节的命令，记录本次的写入与读取数字；
连跑三次，记录三组数字——理解"性能数据要多次测量"；
做"索引数量的边际效应"：把索引从 2 个加到 5 个，观察写入耗时如何变化；
验证深分页：用《执行计划与慢查询定位》的 ExplainLesson，把 OFFSET 从 1000 改到 50000、199000，记录耗时增长曲线；
把深分页改成游标式，再测同样的页数——对比两者的差异；
测算"该不该加这个索引"：假设某表的写入频率是 100 次/秒、查询频率是 1 次/秒，加一个索引让写入慢 40%、查询快 100 倍——算一下"每天总共省/亏多少毫秒"。

验收方式：能说出第 3 步的规律、第 6 步的结论（提示：这个场景下不该加）✗

扩展任务：给《MyBatis 数据持久化》的 MyBatis 项目加一个"N+1 检测"：

用 MyBatis 拦截器统计单次请求内执行的 SQL 条数（可以用 RequestContextHolder 或 ThreadLocal 计数）；
超过阈值（如 20 条）就打 WARN，带上 traceId 与 SQL 条数；
说明：为什么"SQL 条数"比"单条 SQL 耗时"更能暴露 N+1 问题？

11. 练习题

基础题

判断：给表加索引一定会让系统变快。（对 / 错）
填空：优化的优先级顺序是：删掉不必要的查询 → ______ → ______ → 改表结构 → 改架构 → 加机器。
简答：为什么 LIMIT 20 OFFSET 199000 很慢，而 WHERE id > 199000 LIMIT 20 很快？

代码阅读题

java

// 订单列表接口
public PageResponse<OrderResponse> list(int page, int size) {
    // ① 查总数
    long total = orderMapper.selectCount(null);
    // ② 查当前页数据
    List<Order> orders = orderMapper.selectPage(Page.of(page, size), null).getRecords();
    // ③ 补全用户名
    for (Order order : orders) {
        User user = userMapper.selectById(order.getUserId());
        order.setUserName(user.getName());
    }
    // ④ 补全商品名
    for (Order order : orders) {
        Product product = productMapper.selectById(order.getProductId());
        order.setProductName(product.getName());
    }
    return new PageResponse<>(toResponses(orders), page, size, total, (int) Math.ceil((double) total / size));
}

这段代码有几处性能问题？分别是什么？
如果 size = 20，这个接口一共会发多少条 SQL？修完之后是多少条？

编程题

把上面那段代码重构成"低 SQL 条数 + 支持深分页"的版本：
- 要求：
  - 总数查询改为可选（提供一个不查 total 的重载）；
  - 用户名与商品名用批量 IN 查询补全（不要循环单查）；
  - 支持游标分页（lastId 参数），并说明什么情况下必须用它；
  - 写出两个版本的方法签名与关键实现；
- 回答：为什么"selectCount(null) 无条件统计"在大表上是个问题？有哪些替代方案？

综合任务

为一个"订单列表页"做完整的性能优化方案：
- 现状：订单表 800 万行，列表接口 P99 是 4 秒；用户抱怨"翻到后面几页特别慢"；
- 要求：
  - 按 5.1 的优先级列出优化步骤，每一步说明"要做什么、预期收益、代价"；
  - 针对深分页给出具体方案（含前端交互的改动建议）；
  - 针对"总数"给出方案（考虑交互改动的可能性）；
  - 说明索引设计（哪几个复合索引、列序为什么这样）；
  - 估算每一步的收益量级（不求精确，但要说明依据）；
  - 说明怎么验证"真的优化了"；
- 额外要求：说明"如果 800 万行里 700 万行是三年前的订单，你还会选择分库分表吗？"，并给出你的方案。

分级提示

提示 1（方向）：第 1 题的关键是"索引的收益和代价分别在谁身上"。

提示 2（关键点）：第 5 题数一下：三条 SQL 路径里，哪些是"一次"，哪些是"循环 N 次"。

提示 3（接近答案）：第 7 题的"800 万行里 700 万行是旧数据"——这正是"归档"的典型场景。

12. 参考答案

基础题

错。索引有明确的代价（本节实测：写入 +38%~66%）。

"加索引一定变快"错在哪：

维度	影响
读	通常变快 ✓（但低选择性查询可能仍然走全表扫）
写	一定变慢 ✗（每次要维护 N 个 B+ 树）
空间	增大（索引可能占数据量的 30%~100%）
优化器	索引多了，选错计划的风险上升 ⚠️

正确的表述：

"给"某条具体的高频查询"加合适的索引"，在"读远多于写"的表上，通常收益大于代价。" ✓
而"给表加索引"这种没有对象的说法，没有意义。

改写 SQL；加/改索引。

完整的顺序：

text

① 删掉不必要的查询（最好的优化是不做）
② 改写 SQL（索引失效的写法、SELECT *、不必要的排序）
③ 加/改索引（覆盖索引、复合索引列序）
④ 改表结构（冗余、拆分、归档）
⑤ 改架构（缓存、读写分离、异步）
⑥ 分库分表（最后手段）
⑦ 加机器

记住这个顺序的理由：越靠前，成本越低、副作用越小、收益越直接 ✓

因为"找第 199001 行"的方式完全不同。

	`LIMIT 20 OFFSET 199000`	`WHERE id > 199000 LIMIT 20`
数据库要做的	从第一行开始数过 199000 行（读完丢掉）✗	直接在 B+ 树上定位到 `id = 199000` 的位置 ✓
扫描行数	199020	20
复杂度	O(offset + limit)	O(limit)
翻到第 1 万页	慢到不可接受 ✗	一样快 ✓

类比：

OFFSET 像"翻到一本书的第 1 万页，只能从第 1 页一张张翻过去" ✗
游标像"书有索引，直接翻到那一页" ✓

⚠️ 但游标有个硬限制：它需要"有一个严格递增或有唯一兜底的排序键" ✓

sql

-- ✓ 可以（id 唯一）
WHERE id > 199000 ORDER BY id LIMIT 20

-- ⚠️ 有风险（created_at 可能重复 → 可能漏行或重复行）
WHERE created_at > '2026-09-21 10:00:00' ORDER BY created_at LIMIT 20

-- ✓ 加唯一列兜底
WHERE (created_at, id) > ('2026-09-21 10:00:00', 199000) ORDER BY created_at, id LIMIT 20

最后一条是关键细节：排序字段不唯一时，游标分页会漏数据或重复数据 ✗ ——很多"滚动加载偶尔重复一条"的 bug 就来自这里 ✓

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

第 4 题：四处问题。

#	问题	影响
1	`selectCount(null)` 无条件统计	*800 万行的 `COUNT()`** ✗ 每次请求都要扫一遍
2	`selectPage(Page.of(page, size), null)`	`null` = 无筛选条件 → 深分页问题（`OFFSET` 随页码线性变慢）✗
3	循环里查用户（N 次）	N+1 ✗
4	循环里查商品（N 次）	又一个 N+1 ✗

还有两个隐藏问题：

#	问题
5	没有 `ORDER BY`（或隐式按主键）→ 分页结果可能不稳定（不同次的排序可能不同）⚠️
6	没有"是否需要补全"的判断——列表页也许根本不需要商品名（前端只显示订单号与金额）→ 多查了用不到的数据 ✗

第 5 题：size = 20 时，一共 42 条 SQL。

步骤	SQL 条数
① 查总数	1
② 查当前页	1
③ 补用户名	20 ✗
④ 补商品名	20 ✗
合计	42

修完之后：3 条。

步骤	SQL 条数
① 查总数（如果不需要就 0）	1
② 查当前页	1
③ 批量查用户（`IN`）	1
④ 批量查商品（`IN`）	1
合计	3（或 2） ✓

42 → 3，是 14 倍的数据库往返减少 ✓

修复后的代码：

java

public PageResponse<OrderResponse> list(int page, int size, boolean withTotal) {
    // ① 总数：按需查询
    long total = withTotal ? orderMapper.selectCount(null) : -1;

    // ② 当前页（生产里应该加 ORDER BY 与筛选条件）
    List<Order> orders = orderMapper.selectPage(
            Page.of(page, size), Wrappers.<Order>lambdaQuery().orderByDesc(Order::getId)
    ).getRecords();

    if (orders.isEmpty()) {
        return new PageResponse<>(List.of(), page, size, total, 0);
    }

    // ③④ 批量补全：各一次 IN 查询
    Map<Long, String> userNames = batchUserNames(orders.stream().map(Order::getUserId).toList());
    Map<Long, String> productNames = batchProductNames(orders.stream().map(Order::getProductId).toList());

    List<OrderResponse> responses = orders.stream()
            .map(order -> new OrderResponse(
                    order.getOrderNo(),
                    userNames.get(order.getUserId()),
                    productNames.get(order.getProductId()),
                    order.getAmountInCents()))
            .toList();

    return new PageResponse<>(responses, page, size, total, (int) Math.ceil((double) total / size));
}

private Map<Long, String> batchUserNames(List<Long> userIds) {
    if (userIds.isEmpty()) {
        return Map.of();
    }
    return userMapper.selectBatchIds(userIds).stream()      // 一条 IN 查询
            .collect(Collectors.toMap(User::getId, User::getName));
}

⚠️ 两个实现细节：

细节	为什么
`if (orders.isEmpty())` 提前返回	空集合会造成 `IN ()` 语法错误（《JDBC 与数据库连接》的 5.2 节讲过）✗
去重（`distinct()`）	20 个订单可能属于同一个用户，`IN` 里没必要重复 ✓

编程题（第 6 题）

java

package cn.kb.learn.data;

import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public interface OrderQueryService {

    /** 页码式分页：保留 total（代价是一次 COUNT） */
    PageResponse<OrderResponse> listByPage(int page, int size, boolean withTotal);

    /** 游标式分页：不查 total，翻到第 1 万页也一样快 */
    CursorPage<OrderResponse> listByCursor(Long lastId, int size);
}

java

@Service
public class OrderQueryServiceImpl implements OrderQueryService {

    private final OrderMapper orderMapper;
    private final UserMapper userMapper;
    private final ProductMapper productMapper;

    @Override
    public PageResponse<OrderResponse> listByPage(int page, int size, boolean withTotal) {
        long total = withTotal ? orderMapper.selectCount(null) : -1;
        List<Order> orders = orderMapper.selectPage(
                Page.of(page, size), Wrappers.<Order>lambdaQuery().orderByDesc(Order::getId)
        ).getRecords();
        return new PageResponse<>(enrich(orders), page, size, total,
                total < 0 ? -1 : (int) Math.ceil((double) total / size));
    }

    @Override
    public CursorPage<OrderResponse> listByCursor(Long lastId, int size) {
        // 游标式：只多查一条，用来判断"还有没有下一页"
        List<Order> orders = orderMapper.selectList(Wrappers.<Order>lambdaQuery()
                .lt(lastId != null, Order::getId, lastId)          // ★ 不带 OFFSET
                .orderByDesc(Order::getId)
                .last("LIMIT " + (size + 1)));

        boolean hasMore = orders.size() > size;
        List<Order> pageData = hasMore ? orders.subList(0, size) : orders;
        Long nextCursor = pageData.isEmpty() ? null : pageData.get(pageData.size() - 1).getId();

        return new CursorPage<>(enrich(pageData), nextCursor, hasMore);
    }

    /** 批量补全：把 2N 条 SQL 压成 2 条 */
    private List<OrderResponse> enrich(List<Order> orders) {
        if (orders.isEmpty()) {
            return List.of();
        }
        Map<Long, String> userNames = userMapper.selectBatchIds(
                        orders.stream().map(Order::getUserId).distinct().toList())
                .stream().collect(Collectors.toMap(User::getId, User::getName));
        Map<Long, String> productNames = productMapper.selectBatchIds(
                        orders.stream().map(Order::getProductId).distinct().toList())
                .stream().collect(Collectors.toMap(Product::getId, Product::getName));

        return orders.stream()
                .map(order -> new OrderResponse(order.getOrderNo(),
                        userNames.get(order.getUserId()),
                        productNames.get(order.getProductId()),
                        order.getAmountInCents()))
                .toList();
    }
}

"什么情况下必须用游标分页"：

场景	是否必须
无限滚动 / 加载更多	✅ 必须（这是它的主场）
导出全部数据（分批拉）	✅ 必须（否则越到后面越慢）
消息流、时间线	✅ 必须（天然按时间倒序 + 游标）
需要"跳到第 N 页"	❌ 做不到（改用页码式 + 限制最大页码）
数据量小（< 10 万行）	⚠️ 用 `OFFSET` 也行

"为什么 selectCount(null) 在大表上是个问题"：

原因	说明
InnoDB 不缓存总行数	MVCC 下"不同事务看到的行数不同"，只能真去数 ✗
它是 O(n)	800 万行 → 要扫 800 万行的索引
每次请求都做一次	列表接口的 QPS 有多高，它就被执行多少次 ✗✗

替代方案（按推荐程度）：

方案	做法	适用
★ 不要精确总数	用"是否还有下一页"代替"共 N 条"	滚动加载 ✓
给一个上限	只数到 10000 条就停（`LIMIT 10001` 子查询）	"共 1 万+ 条" 就够了 ✓
近似值	`SHOW TABLE STATUS` 的 `Rows`；或定期任务写入估算值	显示用（如"约 8 万条"）✓
维护计数表	单独一张 `order_count` 表，增删时更新	强一致要求 + 能接受复杂度 ⚠️
缓存总数	把总数缓存 1 分钟	大多数列表页都能接受 ✓

最实用的一条：

先问产品："这个数字是必须精确的吗？"
很多"性能问题"只要改一句话的交互文案就能解决 ✓ ——这是本节 5.1 节"优先级 ①"的又一次应用。

综合任务（第 7 题）

第一步：确认问题（不写代码）

要确认什么	手段
是"所有页都慢"还是"只有后面几页慢"	用户说"翻到后面几页特别慢"→ 深分页是主因 ✓
是"一直慢"还是"最近变慢"	慢查询日志的时间分布
慢在 SQL 还是别处	应用层埋点（SQL 耗时占比）
P99 vs 平均	监控看板

第二步：按优先级列方案

优先级	动作	预期收益	代价
① 删掉不必要的查询	列表页不再查 `total`（改成"还有更多"）	省掉一次 800 万行的 COUNT（可能占 50% 耗时）✓	改前端交互 ⚠️
① 补全数据改批量	用户名/商品名批量查	42 条 SQL → 3 条（省 90% 往返）✓	零
② 加 `ORDER BY id DESC`	保证分页稳定 + 让游标可用	稳定性	零
③ 深分页改游标	`WHERE id < lastId ORDER BY id DESC LIMIT 20`	翻到第 1 万页也是 1 ms ✓✓	改前端（无限滚动）+ 后端接口签名变化
③ 建复合索引	`(status, id DESC)`（如果有状态筛选）	筛选 + 排序一起走索引	写入变慢
④ 历史数据归档	3 年前的订单移到归档表	订单表从 800 万降到 100 万 ✓✓	需要迁移 + 归档表的查询入口
⑤ 加缓存	首页/前几页缓存	高频请求不吃数据库	一致性复杂

第三步：索引设计

sql

-- 无筛选条件、按 id 倒序（游标分页用）
-- 主键索引已经满足，不需要额外建 ✓

-- 有状态筛选、按 id 倒序
CREATE INDEX idx_status_id ON orders (status, id DESC);
--                 ↑ 等值列在前     ↑ 排序列在后（最左前缀）

-- 按用户查 + 按时间倒序
CREATE INDEX idx_user_created_id ON orders (user_id, created_at DESC, id DESC);
--                                                  ↑ 加 id 兜底，避免时间重复时游标漏行

列序的三条规则：

规则	理由
等值条件在前	能一次定位到"某个范围"
范围/排序列在后	遇到范围后，后面的列无法再用于定位
游标分页的排序键要唯一	用 `(created_at, id)` 组合兜底 ✓

第四步：收益量级估算

动作	依据	估算
去掉 COUNT	`COUNT(*)` 扫 800 万行，约 1~2 秒	省 1~2 秒 ✓
批量补全	40 次往返 × 1 ms（同机房）+ 连接开销	省 100~300 ms ✓
游标分页	第 1000 页的 `OFFSET` 是 20000，扫 20020 行 ✗；游标扫 20 行	从几百毫秒降到 1 ms ✓✓
归档旧数据	数据量降 87%	所有查询都快一档 ✓

合计：P99 从 4 秒降到 100 ms 以内是可期待的 ✓

第五步：怎么验证"真的优化了"

验证	强度
执行计划显示走了索引、扫描行数从 8 万降到 20	✅ 高
接口 P99 从 4s → 100ms（线上监控）	✅ 最高
慢查询日志里这条 SQL 消失	✅ 高
数据库 CPU 使用率下降	⚠️ 中（受其他因素影响）

第六步（额外要求）：800 万行里 700 万行是旧数据怎么办

答案：不该分库分表，应该归档。

方案	数据量	复杂度	结论
分库分表	仍需处理 800 万	极高（改所有 SQL + 分布式事务）✗	❌ 不值得
归档	热表降到 100 万 ✓	低（定期任务 + 归档表）✓	✅ 正解

归档方案设计：

text

① 建归档表 orders_archive（结构与 orders 相同）
② 定期任务（每月一次）：
     INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < 三年前;
     DELETE FROM orders WHERE created_at < 三年前 AND id IN (...刚搬走的那批...);
   ⚠️ 分批删（每批 1000 条），避免长事务与大锁
③ 查询入口：
     默认查 orders（热表）✓
     历史查询走单独的"历史订单"入口（查归档表），明确告知用户"查询较慢"
④ 归档表可以再压缩（或干脆只保留必要字段）

为什么归档优于分片：

维度	归档	分库分表
改动范围	只加一个任务 + 一个入口 ✓	改所有 SQL、所有 Mapper、事务处理 ✗
可逆性	可以回退（把数据搬回来）	基本不可逆 ✗
性能收益	数据量降 87% → 所有查询都快 ✓	分摊写入/存储，单条查询不一定快 ⚠️
运维复杂度	低	极高（路由、扩容、跨片查询）✗

一条重要的判断：

"数据多"和"数据访问频繁"是两个问题。
旧数据多 → 归档（它不参与日常访问）✓
热数据也多 → 才需要考虑分片
大多数"表太大"的案例，本质是"没清理过历史数据" ✓
这与本节 5.1 节的开头呼应：先做成本最低、收益最直接的那一步。

13. 掌握标准

[ ] 能说出优化的优先级顺序，并知道"分层推进"的理由；
[ ] 能用实测数据说明"索引有写入代价"；
[ ] 知道覆盖索引能省掉回表，并会用它；
[ ] 能说出深分页慢的原因，会用游标分页改写，并知道它的代价（不能跳页、排序键要唯一）；
[ ] 知道 COUNT(*) 与 COUNT(1) 没有性能差别；
[ ] 能识别 N+1，会用"批量 IN 查询"替代；
[ ] 知道 MySQL 的 rewriteBatchedStatements 参数；
[ ] 知道缓存/读写分离/分库分表各自解决什么、代价是什么；
[ ] 知道"不要过早优化"的准确含义（不是不优化，而是要有证据）；
[ ] 会看 P95/P99 而不是平均值。

14. 扩展资源

15. 下一步学习

MySQL 优化到此完成。接下来可以：

《Git 分支、合并与回退》：工程协作的基础工具
《HTTP 请求响应与 Servlet》：Web 底层的原理
T2-003 JMeter 压力测试建模：用压测验证这些优化

前置知识

S3-015 执行计划与慢查询定位

后续知识

暂无

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

MySQL 性能优化基础 ​

1. 本节定位 ​

2. 学习目标 ​

3. 前置知识检查 ​

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

5. 核心概念 ​

5.1 优化的优先级顺序 ​

5.2 索引不是免费的 ​

5.3 覆盖索引：不回表 ​

5.4 深分页：LIMIT OFFSET 的代价 ​

5.5 COUNT 优化 ​

5.6 N+1 查询问题 ​

5.7 批量操作 ​

5.8 缓存、读写分离与分库分表 ​

5.9 "不要过早优化"到底是什么意思 ​

6. 递进示例 ​

6.1 最小示例：把 SELECT * 改成具体列 ​

6.2 业务示例：N+1 的两种修法 ​

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

7. 完整可运行代码 ​

8. Debug 观察点 ​

9. 常见错误 ​

错误 1：先加缓存，不看 SQL ​

错误 2：以为 COUNT(1) 比 COUNT(*) 快 ​

错误 3：深分页直接用 OFFSET ​

错误 4：循环里查数据库（N+1） ​

错误 5：批量操作没开 rewriteBatchedStatements ​

错误 6：为了"性能"提前分库分表 ​

错误 7：只看"平均耗时"，不看 P99 ​

10. 动手实验 ​

11. 练习题 ​

12. 参考答案 ​

13. 掌握标准 ​

14. 扩展资源 ​

15. 下一步学习 ​

前置知识

后续知识

MySQL 性能优化基础

1. 本节定位

2. 学习目标

3. 前置知识检查

4. 为什么需要这个知识

5. 核心概念

5.1 优化的优先级顺序

5.2 索引不是免费的

5.3 覆盖索引：不回表

5.4 深分页：`LIMIT OFFSET` 的代价

5.5 `COUNT` 优化

5.6 N+1 查询问题

5.7 批量操作

5.8 缓存、读写分离与分库分表

5.9 "不要过早优化"到底是什么意思

6. 递进示例

6.1 最小示例：把 `SELECT *` 改成具体列

6.2 业务示例：N+1 的两种修法

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

7. 完整可运行代码

8. Debug 观察点

9. 常见错误

错误 1：先加缓存，不看 SQL

错误 2：以为 `COUNT(1)` 比 `COUNT(*)` 快

错误 3：深分页直接用 `OFFSET`

错误 4：循环里查数据库（N+1）

错误 5：批量操作没开 `rewriteBatchedStatements`

错误 6：为了"性能"提前分库分表

错误 7：只看"平均耗时"，不看 P99

10. 动手实验

11. 练习题

12. 参考答案

13. 掌握标准

14. 扩展资源

15. 下一步学习