我只多查了一个字段，为什么慢了 10 倍？——MySQL 大字段与溢出页入门

目录

• • 一、一个常见的困惑
  • 二、先分清两个概念：回表 ≠ 溢出页
  • 三、MySQL 的数据是怎么摆在磁盘上的
  • 四、一个页 16KB，一篇文章 3 万字，怎么办
  • • 4.1 一条硬性规定
    • 4.2 问题来了
    • 4.3 InnoDB 的解法：搬走，只留个地址
    • 4.4 20 字节指针里装了什么
    • 4.5 一个大字段可能不止一个溢出页
    • 4.6 这个机制有个好的副作用
  • 五、行格式：决定「挪多少」的隐藏开关
  • • 5.1 四种行格式
    • 5.2 `COMPACT` 的 768 字节前缀坑
    • 5.3 检查并修复
    • 5.4 一个常见误解：「TEXT 一定行外，VARCHAR 一定行内」
  • 六、亲手做个实验，看见这件事
  • • 6.1 准备两张表
    • 6.2 灌 5 万行数据，每行正文约 9.4KB
    • 6.3 看看两张表占了多少空间
    • 6.4 实验一：只查小字段，大字段存不存在有影响吗
    • 6.5 实验二：这才是重点——多取一个大字段
    • 6.6 实验三：最隐蔽的坑——去重/分组时带上大字段
  • 七、八种会被大字段拖慢的操作
  • • 第 7 条值得单独说：为什么模糊搜索要上搜索引擎
    • 顺便说个冷知识：`COUNT(*)` 和 `SUM(LENGTH(content))` 的差别
  • 八、六个优化手法（带代码）
  • • 8.1 永远显式列字段，别用 `SELECT *`
    • 8.2 深分页：用延迟关联
    • 8.3 批量扫描：用主键游标代替 OFFSET
    • 8.4 排序/去重阶段不要带大字段（两段式查询）
    • • 场景一：排序取 Top N
      • 场景二：去重
      • 场景三：按内容大小切批次
    • 8.5 垂直拆表：冷热分离
    • 8.6 其他
  • 九、怎么诊断：`EXPLAIN` 的盲区
  • • 9.1 先承认 `EXPLAIN` 在这件事上无能
    • 9.2 方法一：对比 Buffer Pool 读请求数（最直接）
    • 9.3 方法二：看临时表和排序有没有落磁盘
    • 9.4 方法三：从 Performance Schema 找慢语句
    • 9.5 方法四：估算大字段到底占了多少
    • 9.6 一个值得知道的原理：优化器也看不见溢出页
  • 十、总结：五句话记住
  • • 一张判断流程图
    • 最后一条反直觉的对照
  • 附：延伸阅读

本文是小马真实遇到的场景分享。 面向人群：会写 SQL、知道「索引能让查询变快」，但没深入研究过 MySQL 存储结构的同学。

读完你会知道：TEXT 字段到底存在哪、为什么 SELECT * 这么危险、为什么 EXPLAIN 有时候骗人、以及 6 个立刻能用的优化手法。

环境：MySQL 5.7 / 8.0，InnoDB 引擎。第六节的实验可以直接复制执行，文中给出的耗时和空间数字均为示意量级，请以你自己机器上跑出来的结果为准。

一、一个常见的困惑

假设你有一张文章表，几千万行。你写了两个查询：

— 查询 A
SELECT id, user_id, like_count FROM t_article WHERE cate_id = 3 LIMIT 1000;

— 查询 B
SELECT id, user_id, like_count, content FROM t_article WHERE cate_id = 3 LIMIT 1000;

只多了一个 content 字段。你用 EXPLAIN 看，两条的输出一模一样：

+—-+————-+——+—————+————-+——+
| id | table | type | key | rows | Extra|
+—-+————-+——+—————+————-+——+
| 1 | t_article | ref | idx_cate | 1024 | |
+—-+————-+——+—————+————-+——+

同样的执行计划、同样的索引、同样的扫描行数。但实际跑起来：

查询 A： 8 ms
查询 B： 95 ms ← 慢了 10 倍以上

EXPLAIN 没有任何一列能解释这个差距。

答案藏在一个很多人没听过的词里：行外存储（off-page storage）/ 溢出页（overflow page）。

二、先分清两个概念：回表 ≠ 溢出页

很多人问「字段太大会不会回表」，这其实把两件不相关的事混在一起了。它们是完全独立的两个机制：

划重点：回表跟字段多大没有关系；溢出页跟你走哪个索引没有关系。 它们可以任意组合：

走主键 + 取 content → 不回表，但要读溢出页
走二级索引 + 只取小字段 → 回表了，但不读溢出页
走二级索引 + 取 content → 既回表又读溢出页 ← 最贵
覆盖索引 → 两个都没有 ← 最便宜

有个反直觉的结论先放在这儿，后面会证明：

「不回表」不等于「快」。 当字段很大时，读溢出页的代价可能高于一次回表——因为回表要走的非叶子节点通常已经缓存在内存里，而溢出页往往是一次实打实的随机 I/O，且字段越大要读的页越多。

本文只讲右边那一列。

三、MySQL 的数据是怎么摆在磁盘上的

要理解溢出页，得先知道 InnoDB 存数据的基本单位不是「行」，而是页（page），默认 16KB。

SHOW VARIABLES LIKE 'innodb_page_size';
— +——————+——-+
— | innodb_page_size | 16384 |
— +——————+——-+

你的表数据长这样（这叫聚簇索引，叶子节点就是真实数据）：

┌──────────────┐
│ 非叶子节点 │ ← 只存「主键 → 页号」的路由信息
│ id: 1,500 │
└───┬──────┬───┘
│ │
┌─────────────┘ └─────────────┐
▼ ▼
┌─── 叶子页（16KB）───┐ ┌─── 叶子页（16KB）───┐
│ id=1 | 完整一行数据 │ ──next──▶ │ id=500| 完整一行数据 │
│ id=2 | 完整一行数据 │ │ id=501| 完整一行数据 │
│ … │ │ … │
└────────────────────┘ └────────────────────┘

关键认知：读数据的最小代价单位是「读一个 16KB 的页」，不是「读一行」。

所以性能的本质问题变成了：一个页里能塞下多少行？ 塞得越多，扫描 1000 行需要读的页就越少，就越快。

而「一行有多胖」直接决定了这个数字。记住这条线索，后面全靠它。

四、一个页 16KB，一篇文章 3 万字，怎么办

4.1 一条硬性规定

InnoDB 要求：每个数据页至少要能放下 2 行。

为什么？因为如果一个页只能放 1 行，B+ 树就退化成了链表，索引层级会爆炸式增长，彻底失去意义。

于是单行有了上限：

16384 字节 ÷ 2 ≈ 8192 字节
再扣掉页头、页尾、行指针槽等管理开销
→ 单行实际上限 ≈ 8126 字节

你大概见过这个报错：

ERROR 1118 (42000): Row size too large (> 8126).
Changing some columns to TEXT or BLOB may help.

现在你知道 8126 这个数字是怎么来的了。

4.2 问题来了

一篇 3000 字的文章：

3000 个汉字 × utf8mb4 最多 4 字节/字 = 12000 字节
→ 已经超过 8126 的单行上限

如果 InnoDB 死守「整行必须放进一个页」，这条数据根本存不下。

4.3 InnoDB 的解法：搬走，只留个地址

┌─────────── 聚簇索引叶子页（16KB）────────────┐
│ 行1: id=1001 | user_id=55 | cate_id=3 │
│ like_count=120 | created_at=… │
│ content → ［20 字节指针］──────────┐ │
│ 行2: id=1002 | … │ │
│ 行3: id=1003 | … │ │
│ … │ │
│ ↑ 因为大字段搬走了，这个页能塞下很多行 │ │
└──────────────────────────────────────────┼──┘
│
▼
┌──── 溢出页 / BLOB Page（16KB）────┐
│ 「这是文章的正文内容，可能有几千 │
│ 字甚至几万字……」 │
│ next_page → NULL 或下一个溢出页 │
└──────────────────────────────────┘

这就是「行外存储」。 大字段被搬到独立的 BLOB 页，行内只留一个 20 字节的「地址」。

4.4 20 字节指针里装了什么

┌──────────┬──────────┬──────────┬────────────────┐
│ space_id │ page_no │ offset │ 字段总长度 │
│ 4 字节 │ 4 字节 │ 4 字节 │ 8 字节 │
└──────────┴──────────┴──────────┴────────────────┘
表空间ID 首个溢出页 页内偏移 用于预分配缓冲区

4.5 一个大字段可能不止一个溢出页

溢出页也是 16KB，装不下就链式串起来：

行内 20 字节指针
└─▶ BLOB 页 #1 ──next──▶ BLOB 页 #2 ──next──▶ BLOB 页 #3 ──▶ NULL

所以开销不是固定一次，而是和字段长度成正比：

content = 5 千字（约 15KB） → 1 个溢出页 → 1 次额外 I/O
content = 5 万字（约 150KB） → 约 10 个溢出页 → 约 10 次额外 I/O（而且是串行跳转）

4.6 这个机制有个好的副作用

因为大字段被搬走了，行变瘦了，一个叶子页能装的行数大幅增加。

假设一行的小字段加起来 100 字节：

大字段搬走后：一个页 ≈ 16384 / 120 ≈ 136 行
如果不搬走： 一个页 ≈ 2 行（被单行上限限制）

这解释了为什么本文开头的查询 A 那么快——它只取小字段，content 的溢出页压根没被碰过，而且每读一个页就能拿到 100 多行。

五、行格式：决定「挪多少」的隐藏开关

这一节是实战中最容易踩坑、也最容易被忽略的地方。

5.1 四种行格式

InnoDB 有四种行格式，对大字段的处理方式差别巨大：

5.2 COMPACT 的 768 字节前缀坑

这是最阴险的一个点。在 COMPACT 格式下，每个大字段都要在行内留 768 字节的前缀：

┌──── 聚簇索引叶子页（16KB）──────────────────┐
│ 行1: id | user_id | … | content 前 768B │ ← 白白吃掉行内空间！
│ + 20B 指针 │
│ 行2: … │
└───────────────────────────────────────────┘

后果非常严重。假设你的表有 3 个 TEXT 字段（比如 content、content_html、summary）：

光前缀就占用 768 × 3 = 2304 字节，约等于单行上限的 28%

行变得极胖 → 一个页能装的行数暴跌 → 哪怕你只查 SELECT id，也会变慢（因为扫描同样行数要读更多页）。

粗算一下（假设小字段合计约 100 字节，页内可用约 16200 字节）：

COMPACT + 3 个 TEXT：行宽 ≈ 100 + 3×(768+20) = 2464 字节 → 一页约 6 行
DYNAMIC + 3 个 TEXT：行宽 ≈ 100 + 3×20 = 160 字节 → 一页约 100 行

同一张表、同一条查询，只因为行格式不同，要读的页数可能差十几倍。 而这件事 EXPLAIN 完全不会提醒你。

5.3 检查并修复

先查清楚你的表是什么格式：

SELECT TABLE_NAME,
ROW_FORMAT,
ENGINE,
TABLE_ROWS,
ROUND(DATA_LENGTH / 1024 / 1024) AS data_mb,
ROUND(INDEX_LENGTH / 1024 / 1024) AS index_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY DATA_LENGTH DESC
LIMIT 20;

如果看到 COMPACT，而这张表有多个大字段，考虑改掉：

— 注意：这是重建表操作，大表务必在低峰期执行并评估耗时
— MySQL 5.6+ 支持 ONLINE DDL，但仍会产生大量 I/O 和 redo
ALTER TABLE t_article ROW_FORMAT=DYNAMIC, ALGORITHM=INPLACE, LOCK=NONE;

建新表时直接指定清楚，别依赖默认值：

CREATE TABLE t_article (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL DEFAULT 0,
cate_id INT UNSIGNED NOT NULL DEFAULT 0,
title VARCHAR(255) NOT NULL DEFAULT '',
content LONGTEXT, — 大字段
like_count INT UNSIGNED NOT NULL DEFAULT 0,
created_at INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_cate_time (cate_id, created_at)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
ROW_FORMAT=DYNAMIC; — ← 显式写出来

5.4 一个常见误解：「TEXT 一定行外，VARCHAR 一定行内」

都不对。

DYNAMIC 之所以叫 dynamic，是因为它按整行是否超限动态决定：

整行能放进 16KB 页吗？
├─ 能 → 所有字段都留在行内（哪怕它是 TEXT 类型）
└─ 不能 → 挑当前最长的那个变长列，整列搬到溢出页，行内留 20 字节指针
→ 搬完还放不下？继续挑下一个最长的搬
→ 直到整行能放进页内为止

两个直接推论：

所以「取一行的 content 要不要多读一次页」，取决于这一行具体有多长。

这带来一个很烦人的现象：

同一张表、同一条 SQL、同样取 1000 行，今天跑 10ms，明天跑 300ms。

因为今天捞到的是一批短评论（都在行内），明天捞到的是一批长文章（全在行外）。

如果你的表里既有「3 个字的短评论」又有「3 万字的长文」，那批处理任务的耗时波动会非常大。这时候按「行数」切批次是错的，应该按「内容总字节数」切。 后面第八节有代码。

六、亲手做个实验，看见这件事

光说不练没感觉。下面这段可以直接跑（MySQL 8.0，5.7 请把递归 CTE 换成循环存储过程）。

6.1 准备两张表

同样的小字段，区别只在于有没有大字段：

DROP TABLE IF EXISTS t_with_text;
DROP TABLE IF EXISTS t_without_text;

— 表 1：带一个大字段
CREATE TABLE t_with_text (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
cate_id INT UNSIGNED NOT NULL DEFAULT 0,
like_count INT UNSIGNED NOT NULL DEFAULT 0,
content LONGTEXT,
PRIMARY KEY (id),
KEY idx_cate (cate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

— 表 2：不带大字段（结构其余完全一致）
CREATE TABLE t_without_text (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
cate_id INT UNSIGNED NOT NULL DEFAULT 0,
like_count INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_cate (cate_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

6.2 灌 5 万行数据，每行正文约 9.4KB

— 用递归 CTE 造数据（MySQL 8.0）
SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO t_with_text (cate_id, like_count, content)
WITH RECURSIVE seq(n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < 50000
)
SELECT n % 10,
n % 1000,
— 16 个汉字 × 3 字节 × 200 = 9600 字节，已超过 8126 上限，必然行外
REPEAT('这是一段用来撑大正文的测试文本。', 200)
FROM seq;

INSERT INTO t_without_text (cate_id, like_count)
SELECT cate_id, like_count FROM t_with_text;

ANALYZE TABLE t_with_text, t_without_text;

验算一下：SELECT LENGTH(content) FROM t_with_text LIMIT 1; 应该返回 9600。 LENGTH() 算的是字节，CHAR_LENGTH() 算字符，别搞混。

6.3 看看两张表占了多少空间

SELECT TABLE_NAME,
TABLE_ROWS,
ROUND(DATA_LENGTH / 1024 / 1024, 1) AS data_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('t_with_text', 't_without_text');

结果大致是这样（数值因版本和环境而异，量级才是重点）：

+—————-+————+———+
| TABLE_NAME | TABLE_ROWS | data_mb |
+—————-+————+———+
| t_with_text | 50000 | 780+ | ← 绝大部分是溢出页
| t_without_text | 50000 | 1.7 |
+—————-+————+———+

这里有个很多人不知道的细节，也是上面数字的来源：

溢出页不会被多行共用。 每个行外字段都有自己独立的 BLOB 页链。

所以一个 9600 字节的 content，照样要独占一个完整的 16KB 页，剩下的 6.7KB 纯属浪费。

50000 行 × 16KB ≈ 780MB——这就是为什么实际占用比「5 万 × 9.4KB ≈ 470MB」的理论值大了近一倍。

顺带一个实践结论：让大字段的长度刚好略微超过行内上限，是最浪费空间的情况。

6.4 实验一：只查小字段，大字段存不存在有影响吗

— FLUSH STATUS 的作用是把本会话的状态计数器清零，和查询缓存无关
FLUSH STATUS;
SELECT SUM(like_count) FROM t_with_text;
SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read_requests';

FLUSH STATUS;
SELECT SUM(like_count) FROM t_without_text;
SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read_requests';

你会发现两者差别不大。这正是行外存储的好处：大字段搬走了，扫小字段几乎不受影响。

为什么选 Innodb_buffer_pool_read_requests 这个指标？

它统计的是逻辑页访问次数（命中内存的 + 需要从磁盘读的，都算）。

所以它不受缓存是否预热的影响，比直接看耗时稳定得多——后者第一次跑和第二次跑可能差几十倍。

如果你想看真正落到磁盘的部分，看 Innodb_buffer_pool_reads（没有 _requests）。

6.5 实验二：这才是重点——多取一个大字段

FLUSH STATUS;
SELECT id, like_count FROM t_with_text WHERE cate_id = 3 LIMIT 1000;
SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read_requests';
— 假设得到 A = 1200

FLUSH STATUS;
SELECT id, like_count, content FROM t_with_text WHERE cate_id = 3 LIMIT 1000;
SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read_requests';
— 假设得到 B = 2400

B – A 就是读溢出页带来的额外页访问次数。1000 行、每行 9600 字节，差值大致就是 1000 上下（每行正好一个溢出页）。

这个差值，EXPLAIN 一个字都不会告诉你。

6.6 实验三：最隐蔽的坑——去重/分组时带上大字段

先说清一个很多文章讲错的前提：

光有 ORDER BY 不会创建临时表，它走的是 filesort（排序缓冲区，必要时溢出到磁盘临时文件）。

真正需要临时表的是 DISTINCT、GROUP BY、UNION、派生表物化这类操作。

所以实验要用 DISTINCT：

FLUSH STATUS;
SELECT DISTINCT cate_id, like_count FROM t_with_text;
SHOW SESSION STATUS LIKE 'Created_tmp%';
— 结果集不含大字段

FLUSH STATUS;
SELECT DISTINCT cate_id, like_count, content FROM t_with_text;
SHOW SESSION STATUS LIKE 'Created_tmp%';
— 结果集含 LONGTEXT

— Created_tmp_tables 创建的临时表总数
— Created_tmp_disk_tables 其中落到磁盘的数量

但是！这里的结论取决于你的 MySQL 版本，这是本节最需要注意的地方：

查一下自己的配置：

SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine'; — 8.0 默认 TempTable
SHOW VARIABLES LIKE 'temptable_max_ram'; — 默认 1073741824（1GB）

所以准确的说法是：

在 MySQL 5.7 上，结果集里只要带 TEXT/BLOB，内部临时表一定建在磁盘上。

在 MySQL 8.0.13+ 上，TempTable 引擎能把大字段放在内存里，但 1GB 的 temptable_max_ram 很容易被几万条正文撑爆，一旦超限同样落盘。

想在 8.0 上复现 5.7 的行为，可以临时改一下再跑上面的实验：

SET SESSION internal_tmp_mem_storage_engine = MEMORY;
— 跑完记得改回 TempTable

无论哪个版本，EXPLAIN 两次都只会显示 Using temporary，绝不会告诉你它在内存还是磁盘上。这就是必须看状态变量的原因。

记得清理实验表：

DROP TABLE t_with_text, t_without_text;

七、八种会被大字段拖慢的操作

第 3、4 条经常被混为一谈，但机制不同：DISTINCT/GROUP BY 用临时表，ORDER BY 用 filesort。 两者都会被大字段拖累，但诊断指标不一样——前者看 Created_tmp_disk_tables，后者看 Sort_merge_passes。

第 7 条值得单独说：为什么模糊搜索要上搜索引擎

很多人知道 LIKE '%xxx%' 用不上索引，但那只是第一层代价：

LIKE '%关键词%' 在 5000 万行的文章表上：
① 无法使用索引 → 全表扫描
② 每一行还要读 content 的溢出页 ← 这一层几乎总是被忽略
= 几千万次随机 I/O

两层叠加，才是「为什么数据量一上来就必须引入 Elasticsearch」的完整答案。不是只因为「索引用不上」，更因为「每行都要额外跳一次溢出页」。

顺便说个冷知识：COUNT(*) 和 SUM(LENGTH(content)) 的差别

— 完全不读溢出页：COUNT(*) 不访问任何列值，优化器会选最小的索引去扫
SELECT COUNT(*) FROM t_article WHERE cate_id = 3;

— 要读溢出页：LENGTH 虽然只要长度，但 MySQL 仍需把值取出来
SELECT SUM(LENGTH(content)) FROM t_article WHERE cate_id = 3;

不过第二条仍然比「把正文全查回应用层再算长度」划算得多——因为内容不经过网络传输、不占应用内存。这个思路在第八节会变成一个具体手法。

八、六个优化手法（带代码）

8.1 永远显式列字段，别用 SELECT *

这是收益最高、成本最低的一条。

# ❌ 坏：表结构一改就悄悄变慢
cursor.execute("SELECT * FROM t_article WHERE id = %s", (aid,))

# ✅ 好：只取真正用到的
cursor.execute(
"SELECT id, user_id, title, like_count FROM t_article WHERE id = %s",
(aid,)
)

ORM 尤其容易踩，因为默认行为就是 SELECT *：

# ❌ Django：默认查所有字段，包括那个 10KB 的 content
articles = Article.objects.filter(cate_id=3)[:50]

# ✅ 只要列表页用的字段
articles = Article.objects.filter(cate_id=3).only('id', 'title', 'like_count')[:50]

# ✅ 或者反向排除大字段
articles = Article.objects.filter(cate_id=3).defer('content')[:50]

// ❌ Laravel Eloquent：同样默认 select *
$list = Article::where('cate_id', 3)->limit(50)->get();

// ✅
$list = Article::where('cate_id', 3)->select('id', 'title', 'like_count')->limit(50)->get();

// ❌ MyBatis-Plus
List<Article> list = articleMapper.selectList(new QueryWrapper<Article>().eq("cate_id", 3));

// ✅
List<Article> list = articleMapper.selectList(
new QueryWrapper<Article>().eq("cate_id", 3).select("id", "title", "like_count"));

一个容易忽略的长期风险：今天 SELECT * 不慢，是因为表里恰好没有大字段。等半年后有人加了一列 content_html LONGTEXT，你所有的 SELECT * 会在同一天一起变慢，而且没有任何代码改动能让你定位到原因。

8.2 深分页：用延迟关联

— ❌ 坏：扫描并回表 100010 行、读 100010 次溢出页，最后只要 10 行
SELECT id, title, content
FROM t_article
WHERE cate_id = 3
ORDER BY created_at DESC
LIMIT 100000, 10;

— ✅ 好：先只用索引定位出 10 个主键（不碰任何大字段），再去取内容
SELECT a.id, a.title, a.content
FROM t_article a
INNER JOIN (
SELECT id
FROM t_article
WHERE cate_id = 3
ORDER BY created_at DESC
LIMIT 100000, 10
) AS t ON a.id = t.id;

子查询那一步如果用上了 (cate_id, created_at) 这样的索引，就是覆盖索引，一次溢出页都不读。只有最后那 10 行才真正去读正文。

这个技巧叫「延迟关联」（deferred join），是深分页优化的标准答案。

8.3 批量扫描：用主键游标代替 OFFSET

# ❌ 坏：offset 越大越慢，而且前面所有行的溢出页都被读了又扔掉
offset = 0
while True:
rows = db.query(
"SELECT id, content FROM t_article ORDER BY id LIMIT %s, %s",
(offset, 1000)
)
if not rows:
break
process(rows)
offset += 1000

# ✅ 好：主键游标，每批代价恒定
last_id = 0
while True:
rows = db.query(
"SELECT id, content FROM t_article "
"WHERE id > %s ORDER BY id ASC LIMIT 1000",
(last_id,)
)
if not rows:
break
process(rows)
last_id = rows[–1]['id']

8.4 排序/去重阶段不要带大字段（两段式查询）

这是最值得掌握的一个模式。核心思想：把「决定要哪些行」和「取出这些行的内容」拆成两步。

场景一：排序取 Top N

— ❌ 坏：filesort 的排序缓冲区里塞满了正文
SELECT id, title, content
FROM t_article
WHERE cate_id = 3
ORDER BY like_count DESC
LIMIT 50;

— ✅ 第一步：只取主键。配合 (cate_id, like_count) 索引即为覆盖索引，一次溢出页都不读
SELECT id
FROM t_article
WHERE cate_id = 3
ORDER BY like_count DESC
LIMIT 50;

— ✅ 第二步：拿着这 50 个主键精准取正文（50 次主键点查）
SELECT id, title, content
FROM t_article
WHERE id IN (/* 上一步返回的 50 个 id */)
ORDER BY FIELD(id, /* 同样的 id 顺序 */); — 保持第一步的排序

这两步和 8.2 的延迟关联是同一个思路，区别只是：延迟关联交给 MySQL 一条 SQL 搞定，两段式把中间结果拿回应用层。

能用延迟关联就用延迟关联（少一次网络往返）；但如果主键列表还要被别的逻辑复用，两段式更灵活。

场景二：去重

— ❌ 坏：临时表里塞满了正文
SELECT DISTINCT user_id, content
FROM t_article
WHERE cate_id = 3;

— ✅ 第一步：只在小字段上去重，顺便拿到每组的代表主键
— 语义：每个作者取 id 最小的那篇
SELECT MIN(id) AS id
FROM t_article
WHERE cate_id = 3
GROUP BY user_id;

— ✅ 第二步：按主键取正文
SELECT id, user_id, content
FROM t_article
WHERE id IN (/* 上一步的主键 */);

注意这两步不是字面等价的：原 SQL 的去重键包含 content，改写后只按 user_id 分组。改写前务必确认业务真正要的是哪种语义——这是两段式最容易出 bug 的地方。

场景三：按内容大小切批次

还记得第 5.4 节那个「同一条 SQL 耗时忽快忽慢」的问题吗？根因是按行数切批次，而行的胖瘦差异巨大。正确做法是按字节数切：

MAX_BYTES_PER_BATCH = 2 * 1024 * 1024 # 每批最多 2MB 正文

def plan_batches(db, since):
"""先用一次廉价的聚合查询算出分批方案，再按方案去取正文。"""
# 第一步：只查「尺寸」，不查内容
# SUM(LENGTH()) 虽然仍要读溢出页，但正文不经过网络、不占应用内存
size_rows = db.query(
"SELECT cate_id, COUNT(*) AS row_cnt, SUM(LENGTH(content)) AS byte_cnt "
"FROM t_article WHERE created_at >= %s GROUP BY cate_id",
(since,),
)

# 第二步：根据真实字节数规划批次，而不是拍脑袋按行数切
batch, cur_bytes = [], 0
for r in size_rows:
if batch and cur_bytes + r["byte_cnt"] > MAX_BYTES_PER_BATCH:
yield batch
batch, cur_bytes = [], 0
batch.append(r["cate_id"])
cur_bytes += r["byte_cnt"]
if batch:
yield batch

# 第三步：按规划好的分组，精准取正文
for cate_ids in plan_batches(db, since):
placeholders = ",".join(["%s"] * len(cate_ids))
rows = db.query(
f"SELECT id, content FROM t_article "
f"WHERE cate_id IN ({placeholders}) AND created_at >= %s",
(*cate_ids, since),
)
process(rows)

对比一下：

8.5 垂直拆表：冷热分离

如果一张表的大字段只在少数场景用到（比如详情页），而列表、统计、搜索等高频场景都不用，那就把它拆出去：

— 热表：窄、小、扫得飞快，高频查询都只碰它
CREATE TABLE t_article (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL DEFAULT 0,
cate_id INT UNSIGNED NOT NULL DEFAULT 0,
title VARCHAR(255) NOT NULL DEFAULT '',
like_count INT UNSIGNED NOT NULL DEFAULT 0,
created_at INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY idx_cate_time (cate_id, created_at)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

— 冷表：只存大字段，只在详情页按主键点查
CREATE TABLE t_article_content (
article_id BIGINT UNSIGNED NOT NULL,
content LONGTEXT,
PRIMARY KEY (article_id)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

效果：

列表页 / 统计 / 排行榜 → 只查 t_article，一个页能装 100+ 行
详情页 → 主键点查 t_article_content，一次就命中

很多项目的表结构里其实已经隐含了这个思路——一张主表配一张 xxx_detail / xxx_content 的从表。现在你知道它不只是「字段太多放不下」，更是性能上的主动设计。

拆表的代价也要说清楚：

• 详情页多一次查询（但是主键点查，很便宜）
• 写入要保证两张表一致（同一事务内写，或容忍最终一致）
• 不能再用一条 SQL 把列表和正文一起查出来

判断标准很简单：如果大字段的访问频率远低于小字段，就该拆。

8.6 其他

— 1) 大字段用 TEXT/LONGTEXT，而不是超长 VARCHAR
— 注意原因不是「VARCHAR 一定行内」（第 5.4 节已澄清，DYNAMIC 下两者行为一致），
— 而是 VARCHAR 的「声明长度」会算进 65535 字节的行定义上限，更容易撞墙

— 2) 要在大字段上做前缀匹配，用前缀索引（注意：它无法覆盖查询）
ALTER TABLE t_article ADD KEY idx_title_prefix (title(32));

— 3) 全文检索交给 Elasticsearch，或用 MySQL 自带的全文索引
— 中文必须配 ngram 分词器，且 ngram_token_size 要在建索引前设好（默认 2）
SHOW VARIABLES LIKE 'ngram_token_size';
ALTER TABLE t_article ADD FULLTEXT KEY ft_content (content) WITH PARSER ngram;

— 4) 临时表用完即删。注意 DELETE 只标记空间可复用，不还给操作系统
DELETE FROM t_import_tmp WHERE task_id = 123;
— 长期积累碎片后需要重建表才能真正回收空间
OPTIMIZE TABLE t_import_tmp; — 对 InnoDB 等价于 ALTER TABLE … ENGINE=InnoDB

— 5) 一次性清空整张临时表，用 TRUNCATE 而不是 DELETE
— TRUNCATE 直接丢弃并重建表空间文件，溢出页占的空间立刻归还
TRUNCATE TABLE t_import_tmp;

九、怎么诊断：EXPLAIN 的盲区

9.1 先承认 EXPLAIN 在这件事上无能

EXPLAIN SELECT id, content FROM t_article WHERE cate_id = 3 LIMIT 100;

输出里有 type、key、rows、filtered、Extra，没有任何一列反映溢出页。两个只差一个字段的查询，EXPLAIN 输出可以完全一致，而实际耗时差 10 倍。

EXPLAIN FORMAT=JSON 里的 data_read_per_join 稍有参考价值，但它算的也是行内部分。

9.2 方法一：对比 Buffer Pool 读请求数（最直接）

FLUSH STATUS;
SELECT id FROM t_article WHERE cate_id = 3 LIMIT 1000;
SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read_requests'; — 记下 A

FLUSH STATUS;
SELECT id, content FROM t_article WHERE cate_id = 3 LIMIT 1000;
SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read_requests'; — 记下 B

— B – A ≈ 读溢出页带来的额外页访问次数

9.3 方法二：看临时表和排序有没有落磁盘

这两个是不同的机制，要分别看：

FLUSH STATUS;
— 跑你怀疑的那条 SQL
SHOW SESSION STATUS WHERE Variable_name IN (
'Created_tmp_tables', — 创建的内部临时表总数
'Created_tmp_disk_tables', — 其中落到磁盘的数量 ← DISTINCT/GROUP BY 看这个
'Sort_merge_passes', — filesort 的归并轮次 ← ORDER BY 看这个，> 0 说明排序缓冲不够
'Sort_rows'
);

MySQL 8.0 上 Created_tmp_disk_tables 的统计口径随小版本有调整（TempTable 引擎的 mmap 文件是否计入曾变动过）。如果数字看起来可疑，用 9.2 的页访问次数法交叉验证。

9.4 方法三：从 Performance Schema 找慢语句

SELECT DIGEST_TEXT,
COUNT_STAR,
ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
SUM_ROWS_EXAMINED,
SUM_CREATED_TMP_DISK_TABLES — ← 重点看这一列
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

SUM_CREATED_TMP_DISK_TABLES 不为 0 的语句，大概率就是「排序/去重时带了大字段」。

9.5 方法四：估算大字段到底占了多少

— ⚠️ 大表上务必加时间/主键范围限制，否则这条查询本身就会把溢出页读爆
SELECT COUNT(*) AS rows_scanned,
ROUND(AVG(LENGTH(content))) AS avg_bytes,
MAX(LENGTH(content)) AS max_bytes,
SUM(LENGTH(content) > 8000) AS maybe_overflow,
ROUND(100 * SUM(LENGTH(content) > 8000) / COUNT(*), 1) AS overflow_pct
FROM t_article
WHERE id BETWEEN 1000000 AND 1010000; — 采样

overflow_pct 是「有多少比例的行会读溢出页」的粗略估计。

为什么阈值取 8000 而不是 8126？因为 8126 是整行的上限，content 之外的字段也要占位置。实际触发行外存储的门槛比 8126 更低，取 8000 是个偏保守的近似。

想精确判断，得看 information_schema.INNODB_BUFFER_PAGE 里 PAGE_TYPE='BLOB' 的页。

9.6 一个值得知道的原理：优化器也看不见溢出页

MySQL 优化器的代价模型，基本不计算溢出页成本。它按「行数 × 行宽」估算 I/O，而行宽算的是行内部分（也就是那 20 字节指针），不算溢出页的真实体积。

结果就是：在大字段表上，优化器会系统性地低估成本，因此更容易选错执行计划。

如果你在某个项目里看到大量 FORCE INDEX / USE INDEX 的提示，而这些表恰好都带着大文本字段——这很可能不是写代码的人乱加，而是优化器在这类表上确实不太可靠。

十、总结：五句话记住

① 一行放不进 16KB 页 → 大字段被挪到溢出页，行内只留 20 字节指针
② 取那个字段 → 多一次（或多次，与长度成正比）随机 I/O
③ 不取那个字段 → 零成本，而且因为行变瘦，扫描反而更快
④ EXPLAIN 完全看不见这件事，要靠 Buffer Pool 页访问数 / Created_tmp_disk_tables / Sort_merge_passes
⑤ 排序、去重、分页的「决策阶段」绝不要带大字段，拆成两段式

一张判断流程图

我的查询会不会读溢出页？
│
├─ SELECT 列表里有 TEXT / BLOB / 长 VARCHAR / JSON 吗？
│ ├─ 没有 ──────────────────────────────▶ 不会，放心
│ └─ 有
│ └─ 这些字段的实际内容超过行内空间了吗？
│ ├─ 大部分行都很短 ─────────────▶ 多数留在行内，影响小
│ └─ 大部分行很长 ───────────────▶ 会，且代价与长度成正比
│ │
│ ├─ 这个字段是业务必需的吗？
│ │ ├─ 是 ─▶ 无法消除，改为控制「每批字节数」而非行数
│ │ └─ 否 ─▶ 从 SELECT 列表里删掉它
│ │
│ └─ 同时还有 ORDER BY / GROUP BY / DISTINCT 吗？
│ ├─ DISTINCT / GROUP BY ─▶ 🔴 临时表带大字段，改两段式
│ └─ ORDER BY ───────────▶ 🔴 排序缓冲被正文占满，改两段式

最后一条反直觉的对照

所以别再把「有没有回表」当成唯一的优化目标了。 真正该问的是：

这个查询，一共要访问多少个 16KB 的页？

附：延伸阅读

官方文档（强烈建议对照着看，本文的结论都能在这里找到依据）：

• InnoDB Row Formats —— 四种行格式与 768 字节前缀
• The Physical Structure of an InnoDB Index
• Internal Temporary Table Use in MySQL —— 什么情况下会用临时表、什么情况下落磁盘
• Limits on Table Column Count and Row Size —— 8126 和 65535 两个上限的来历
• Optimizing ORDER BY —— filesort 与大字段的关系

动手查证用：

— 直接看 Buffer Pool 里有多少 BLOB（溢出）页，以及属于哪张表
SELECT TABLE_NAME, PAGE_TYPE, COUNT(*) AS pages
FROM information_schema.INNODB_BUFFER_PAGE
WHERE PAGE_TYPE IN ('BLOB', 'INDEX')
GROUP BY TABLE_NAME, PAGE_TYPE
ORDER BY pages DESC
LIMIT 20;

⚠️ 这张表在大 Buffer Pool 上查询很重，生产环境慎用。