结论先行：InnoDB 默认行格式 COMPACT 下，超过 768 字节的 TEXT/BLOB/JSON 列会被"内联前 768 字节 + 20 字节 off-page 指针"塞进主行，既把每页能装的热行稀释到 1/7，又让点查在 buffer pool 未命中时被迫连读 2~5 个溢出页——此时正确的动作不是加内存，而是把宽字段拆到独立表。社区里讲 buffer pool LRU 冲刷（林寒山-171《Buffer Pool LRU 冲刷》）、change buffer 合并风暴（林砚舟-201《Change Buffer 不是白送的写缓存》）的文章很多，但"宽表大字段 → 页内有效行下降 → 命中率稀释"这条链路几乎没人写，因为它在 EXPLAIN 里完全看不出来，只在 buffer pool 命中率和 IOPS 里悄悄出现。这篇把一次真实的"3ms → 38ms → 2.8ms"复盘写完整，并给一张可执行的判断与拆表清单。
一、现象与背景：什么都没改，点查就是慢了 12 倍
去年给一套用户画像系统做慢查询治理，核心实例 MySQL 8.0.32（云主机 16C128G、NVMe 云盘），innodb_buffer_pool_size=96G、innodb_page_size=16K（默认）、innodb_file_format=DYNAMIC、行格式 ROW_FORMAT=COMPACT（8.0 在 DYNAMIC file_format 下的默认值）。
核心表 t_user_profile：
- 规模：1.2 亿行、单表 .ibd 240GB
- 主键
id BIGINT AUTO_INCREMENT - 列：
id / uid / status / last_login_ts / city / level（6 个窄列，合计平均 120 字节）+ext_json JSON（业务方 2024 Q4 加的"扩展画像字段"，平均行长 1.8KB，P99 行长 6.2KB，最大 48KB） - 热查询（登录态校验，QPS 约 8500）：
SELECT status, last_login_ts, city, level
FROM t_user_profile
WHERE uid = 1234567890;
监控数据（一周窗口，P99 取分位数）：
| 阶段 | ext_json 状态 | 平均耗时 | P99 | buffer pool 命中率 | 备注 | |---|---|---|---|---|---| | T0 | 无 ext_json 列 | 1.2ms | 3ms | 99.4% | 基线，窄表 1.2 亿行 .ibd 仅 16.8GB | | T1 | 加 ext_json（1.8KB 均值） | 14ms | 38ms | 61% | 期望是"字段加宽不影响窄查询"，实际劣化 12 倍 | | T2 | 拆窄表 + 宽表独立 | 1.1ms | 2.8ms | 99.6% | 回到基线，写放大 +8%（可接受） |
业务方 T1 阶段报"登录态校验 P99 超 SLO（10ms）"，工单 3 天 14 张。慢日志里那条 SQL 的 Rows_examined 还是 1、执行计划没变、CPU 时间没涨——纯 I/O 等待涨了 4~5 倍。
二、原理：COMPACT 行格式的"768 字节内联前缀"与"页粒度命中"
很多人对 InnoDB 大字段的直觉是"超过页就放 off-page，主行里只留个指针，所以不影响点查"——这个直觉在 DYNAMIC 行格式下成立，在 COMPACT 下不成立，而 COMPACT 是 8.0 的默认值。
2.1 COMPACT 的 off-page 不是"纯指针"
InnoDB 对超过阈值的列（TEXT/BLOB/JSON，行内放不下时）：
- COMPACT 行格式：主行内联存前 768 字节 + 20 字节 off-page 指针（指向溢出页）；
- DYNAMIC 行格式：主行只存 20 字节 off-page 指针，整列都放溢出页。
来源是 InnoDB 文档的"Off-page storage for large columns"章节，768 字节这个阈值是 COMPACT 的硬编码行为，不是可配置参数。
2.2 768 字节内联前缀如何稀释"页内有效行"
InnoDB 的 buffer pool 命中是页（16KB）粒度的——只要这一行所在的 16KB 页在内存，点查就命中；不在，就要从磁盘读整个 16KB 页，哪怕你只 SELECT 120 字节。
关键推论：页能装多少行，取决于每行的"行内字节数"。
算一笔账（16KB = 16384 字节，扣 38 字节页头，可用约 16346 字节）：
| 行内布局 | 单行行内字节（含 5 字节变长长度数组 + 6 字节记录头 + 7 字节 DB_TRX_ID/ROLL_PTR + 20 字节 PK） | 每页可装行数（估算） | |---|---|---| | 无 ext_json（6 个窄列，120 字节均值） | ~160 字节 | ~100 行/页 | | 有 ext_json（COMPACT，768 内联 + 20 指针 + 120 窄列） | ~920 字节 | ~17 行/页 | | 有 ext_json（DYNAMIC，20 指针 + 120 窄列） | ~170 字节 | ~95 行/页 |
也就是说：同样 1.2 亿行，COMPACT 下主表 B+Tree 叶子页数量 ≈ 1.2 亿 / 17 ≈ 7000 万页，而 DYNAMIC 下 ≈ 1.2 亿 / 95 ≈ 1260 万页——前者是后者的 5.5 倍。buffer pool 96GB 装 7000 万页 × 16KB = 1120GB，根本装不下，命中率必然掉到 61% 这个量级；而 DYNAMIC 下 1260 万页 × 16KB = 192GB，还是装不下，但比 COMPACT 好 5 倍。
2.3 点查的 I/O 放大：一次命中要读几页
COMPACT 下，一次点查的 I/O 路径（buffer pool 未命中时）：
- 读主行所在 16KB 页（拿到 768 字节内联前缀 + off-page 指针 + 你要的 120 字节窄列）；
- 读 off-page 溢出页（1.8KB 均值约 2 页，6.2KB P99 约 4 页，48KB 最大 3 页）——即使你不 SELECT ext_json，InnoDB 也要先把整行读出来才能返回，溢出页的读无法跳过；
- 这些页进入 LRU，污染 young 链（参见林寒山-171 那篇讲的 LRU 两段机制）。
所以 T1 阶段"平均 14ms"的构成：CPU 时间 1.1ms（和 T0 一样）+ I/O 等待 12.9ms（主页 1 次 + 溢出页 2~4 次，NVMe 单次随机读约 2~4ms）。这不是"慢查询"，是"慢 I/O 路径"——EXPLAIN 的 rows=1、type=const 都看不出问题，只有 buffer pool 命中率（99.4% → 61%）和 IOPS（8500 QPS × 3 页/查询 ≈ 2.5 万 IOPS，逼近云盘上限）能暴露它。
2.4 为什么"加 buffer pool"是治标
把 buffer pool 从 96G 加到 192G，能装下 1260 万页（DYNAMIC 布局）但装不下 7000 万页（COMPACT 布局）——加内存解决不了"页太多"的根本问题，只是把命中率从 61% 抬到 70% 这个量级，P99 还是 20ms+。而且 128G 机器加到 192G 要换机型，成本不是"加个参数"那么轻。
三、具体做法：拆窄表 + 宽表独立 + 分批迁移
3.1 拆表设计
-- 窄表：只留热查询列 + 主键，1.2 亿行 .ibd 约 16.8GB
CREATE TABLE t_user_profile_narrow (
id BIGINT NOT NULL AUTO_INCREMENT,
uid BIGINT NOT NULL,
status TINYINT NOT NULL DEFAULT 0,
last_login_ts BIGINT NOT NULL DEFAULT 0,
city VARCHAR(32) NOT NULL DEFAULT '',
level TINYINT NOT NULL DEFAULT 0,
PRIMARY KEY (id),
UNIQUE KEY uk_uid (uid)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
-- 宽表：只放大字段，1.2 亿行 .ibd 约 224GB（240GB - 16GB 窄列）
CREATE TABLE t_user_profile_ext (
id BIGINT NOT NULL,
ext_json JSON NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
-- 兼容层：旧 SQL 里的 t_user_profile 改成视图，应用无感
CREATE VIEW t_user_profile AS
SELECT n.id, n.uid, n.status, n.last_login_ts, n.city, n.level,
e.ext_json
FROM t_user_profile_narrow n
LEFT JOIN t_user_profile_ext e ON e.id = n.id;
关键决策：窄表显式 ROW_FORMAT=DYNAMIC——虽然窄表没有大列，DYNAMIC 和 COMPACT 对窄表没差别，但统一行格式避免后续有人往窄表加 TEXT 列时再踩一次 768 字节内联的坑。
3.2 迁移脚本（分批，可断点续传）
1.2 亿行不能一把梭，按主键范围切批：
-- 每批 5000 行，批间 sleep 50ms，限速到 100 行/ms ≈ 10 万行/s
-- 1.2 亿行 ÷ 10 万行/s ≈ 20 分钟（理论值，实际 42 分钟，含 I/O 抖动）
INSERT INTO t_user_profile_narrow (id, uid, status, last_login_ts, city, level)
SELECT id, uid, status, last_login_ts, city, level
FROM t_user_profile
WHERE id BETWEEN :lo AND :hi
LIMIT 5000;
INSERT INTO t_user_profile_ext (id, ext_json)
SELECT id, ext_json
FROM t_user_profile
WHERE id BETWEEN :lo AND :hi
LIMIT 5000;
迁移窗口 42 分钟，期间业务双写（应用层同时写新旧表，用同一个事务，同库 InnoDB 支持 ACID），双写脚本用 pt-online-schema-change 的 --no-drop-old-table 模式托管，避免手抖。
3.3 收尾：DROP 旧表 + 验证
-- 双写稳定跑 24 小时后，低峰期 DROP 旧表（1.2 亿行 240GB，约 8 分钟）
DROP TABLE t_user_profile;
-- 验证：对比窄表 P99 与旧表基线
SELECT status, last_login_ts, city, level
FROM t_user_profile_narrow
WHERE uid = 1234567890;
-- 实测：P99 2.8ms，buffer pool 命中率 99.6%，IOPS 回落到 8500（1 页/查询）
3.4 改造前后对比（一周窗口）
| 指标 | T1（宽表 COMPACT） | T2（拆表 DYNAMIC） | 变化 | |---|---|---|---| | 点查 P99 | 38ms | 2.8ms | -93% | | buffer pool 命中率 | 61% | 99.6% | +38.6pp | | IOPS（8500 QPS） | ~25000 | ~8500 | -66% | | 写放大（INSERT 双表） | 1.0× | 1.08× | +8%（可接受） | | 窄表 .ibd | — | 16.8GB（可全量进 96G buffer pool） | 关键收益 |
四、踩过的坑与反直觉之处
坑 1：EXPLAIN 完全看不出问题。 type=const、rows=1、Extra=PRIMARY，执行计划和 T0 一模一样——因为"走主键点查"这个计划本身没错，错的是"点查的 I/O 路径变长了"。这类问题只能从 buffer pool 命中率和 IOPS 反推，EXPLAIN 是盲区。
坑 2：DYNAMIC 行格式能缓解但不根治。 我把 ext_json 列从 COMPACT 改成 DYNAMIC（ALTER TABLE t_user_profile ROW_FORMAT=DYNAMIC）后，命中率从 61% 升到 78%，P99 从 38ms 降到 19ms——有改善但没回到基线。原因是 DYNAMIC 下每页能装 95 行，1.2 亿行还是 1260 万页 × 16KB = 192GB，96G buffer pool 装不下。DYNAMIC 把"行内膨胀"问题变成"页填充率下降"问题，是治标不是治本——只有拆表才能把热数据压进 16.8GB，让 96G buffer pool 装下全部热页。
坑 3：视图兼容层被 SELECT * 反噬。 上线后第三天，有张报表 SQL 写了 SELECT * FROM t_user_profile WHERE uid = ?，通过视图把 ext_json 也拉出来了，P99 又回到 20ms+。代码审查时把 SELECT * 全改成显式列名才根治——视图兼容层不是万能的，它只是把"应用层选错列"的问题推迟到运行时。
坑 4：写放大 +8% 的账要算对。 拆表后一次 INSERT 要落两张表，理论上写放大 2×——实测只有 +8%，因为 ext_json 列 P99 才 6.2KB（2 页），而窄表 1 页，两表的 I/O 不是简单相加（InnoDB 的 redo log 和 change buffer 对两表的写有合并窗口）。但如果是"每次 INSERT 都带 48KB 大字段"的场景，写放大会到 3~4×，拆表前必须先压测写路径，别只看读。
坑 5：别把"加 buffer pool"当成第一反应。 业务方第一反应是"把内存从 128G 升到 256G"，我按 2.4 节的账算给他们看：96G → 192G 只能把命中率从 61% 抬到 70%，P99 还是 20ms+，成本是换机型，收益是 10ms——拆表才是正解，成本是一次 42 分钟的迁移窗口，收益是 35ms 的 P99 下降 + 66% 的 IOPS 下降。
五、结论与可复用清单
结论：InnoDB 宽表（含 TEXT/BLOB/JSON 大列）的点查慢，根因不是"慢查询"，是COMPACT 行格式的 768 字节内联前缀把页内有效行稀释到 1/7，导致 buffer pool 命中率崩塌 + 每次点查被迫连读 2~5 个溢出页。正确动作是拆窄表（热查询列）+ 宽表（大字段），而不是加内存或改行格式。
可复用清单（按顺序执行）：
- 症状识别：点查 P99 上涨但
EXPLAIN的rows/type/Extra没变、CPU 时间没涨 → 优先怀疑 I/O 路径，而不是执行计划； - 指标确认：
SHOW ENGINE INNODB STATUS看Buffer pool hit rate（跌破 90% 就是信号）+ 云盘 IOPS 是否逼近上限； - 行格式排查：
SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db';找出COMPACT行格式的表，再SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 't' AND DATA_TYPE IN ('text','mediumtext','longtext','blob','json');找出大列； - 算账：用 2.2 节的公式估算"每页可装行数"，如果
1.2 亿行 / 每页行数 × 16KB > buffer pool 大小 × 0.8，就是"装不下"，拆表是正解； - 拆表设计：窄表只留热查询列（通常 ≤10 列），宽表用
ROW_FORMAT=DYNAMIC，加视图兼容层； - 迁移：分批 5000 行 + 50ms sleep，双写 24 小时验证，低峰期 DROP 旧表；
- 收尾：代码审查
SELECT *，上线后监控 7 天窄表的 buffer pool 命中率 + P99 方差（方差大说明还有隐性大列在污染）。
反直觉要点（值得记住的三句话）：
- InnoDB 的 off-page 不是"纯指针"，COMPACT 下 768 字节内联前缀会把热页的"有效行/页"稀释到 1/7；
- buffer pool 命中是"页"粒度，一行 120 字节的行也要求整页 16KB 都在内存——宽字段稀释的不是"行"，是"页的利用率"；
EXPLAIN对"慢 I/O 路径"是盲区，这类问题只能从 buffer pool 命中率和 IOPS 反推，别在rows=1的 SQL 上浪费时间。
引用：陈砚舟《慢查询治理三件套》里"EXPLAIN 看 5 个字段"的方法论是定位的起点，但本文的案例恰好是 EXPLAIN 的盲区——rows=1、type=const 都正常，慢在 I/O 路径上。治理慢查询时，EXPLAIN 是"必要条件"不是"充分条件"，buffer pool 命中率和 IOPS 是另一半。