为什么一张 MySQL 表到千万行就变慢：B+ 树、页分裂与索引膨胀

「单表不要超过 2000 万行」大概是 MySQL 圈流传最广的经验数字。但很少有人能说清：这个数是哪来的？为什么是 2000 万而不是 500 万或 1 个亿？一张表变慢的瞬间，到底发生了什么？

这篇文章把这件事拆开讲透：慢的不是行数，而是 B+ 树变高、缓存放不下、页在疯狂分裂。文末给出排查清单和判断「该不该分表」的实际标准。

一、先复习：InnoDB 的 B+ 树长什么样

InnoDB 的表本质上是一棵主键聚簇索引 B+ 树，数据行就挂在叶子节点上。理解「千万行变慢」只需要记住三个物理事实：

1. 一切以页为单位。默认 innodb_page_size = 16KB，无论读一行还是读一页，磁盘 IO 的粒度都是 16KB。
2. 非叶子节点只存「键 + 指针」。一个条目 = 主键值（bigint 是 8B）+ 子页指针（6B）≈ 14 字节。
3. 叶子节点存完整数据行，页内有序，页与页之间双向链表串联。

所以一次主键点查的成本 ≈ 树有几层，就访问几个页。树高决定了 IO 次数，这就是一切问题的源头。

二、「2000 万」是算出来的，不是玄学

用上面三个事实做个估算（假设 bigint 主键、平均行宽 1KB）：

非叶子页扇出 = 16KB / 14B ≈ 1170 个条目
叶子页行数   = 16KB × 15/16（页填充率） / 1KB ≈ 15 行

三层 B+ 树的容量：

根(1页) → 内部(1170页) → 叶子(1170 × 1170页)
       ≈ 1170 × 1170 × 15 ≈ 2050 万行

「2000 万」就是三层树撑满的临界点。 第 2051 万行插入时，树不得不分裂出一个新的内部层——树高从 3 变 4，之后每一次点查都比以前多一次页访问。

注意两个变量会显著移动这个临界点：

• 行宽：行宽 200B 时，叶子页能装 70+ 行，三层可容纳近 1 亿行；行宽 5KB 时，几百万行就会顶到 4 层。
• 主键类型：int 主键的非叶子条目 10B，扇出 ≈ 1630；bigint 是 14B。主键越宽，扇出越小，同层容量越低。

所以更准确的说法是：别盯行数，盯树高。查询时跑一句：

-- 5.7+/8.0 可用 X$ 体系或 information_schema 观察；最直接的验证是压测对比
-- 这里给一个可落地的估算：用表大小除以叶子容量反推层数
SELECT
  table_name,
  ROUND(data_length/1024/1024)  AS data_mb,
  ROUND(index_length/1024/1024) AS index_mb,
  ROUND(data_free/1024/1024)    AS free_mb
FROM information_schema.tables
WHERE table_schema = 'your_db' AND table_name = 'your_table';

data_mb / 16MB 就是叶子页数量级，再除以扇出²，就能粗判当前是 3 层还是 4 层。

三、树变高只慢一次 IO，真正持续变慢的是这三件事

树高从 3 到 4，每次点查多访问一个 16KB 页——如果都在内存里，开销不足 1μs，这本身几乎无感。千万行表真正持续变慢，是下面三件事叠加的结果。

1. Buffer Pool 装不下热数据了

2000 万行 × 1KB ≈ 20GB 数据。多数实例的 innodb_buffer_pool_size 只有 8~16GB。一旦热数据超过缓存：

• 原本纯内存的页访问，变成真·磁盘随机 IO（机械盘 ~10ms，SSD ~0.1ms，差 100 倍起）；
• 范围扫描和全表扫会把大量冷页灌进 LRU，把热页挤出去（InnoDB 有 mid-point insertion 缓解，但缓解不等于根治）；
• 表现就是：同一个 SQL，昨天 5ms，今天 500ms，且随时间越来越抖。

-- 缓存命中率是第一诊断指标
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 ≈ 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
-- 健康值应 > 99%；跌到 95% 以下说明内存已经明显不够

2. 页分裂在制造碎片和随机写

• 自增主键：永远在最后一页追加，页写满即封页，填充率 ~15/16，近似顺序写。
• 随机主键（UUID、雪花前的随机 ID）：新行落在树的任意位置，满页要裂成两半，新页填充率只有 ~50%。同样的数据，索引体积膨胀近 40%，Buffer Pool 能容纳的有效页数同比减少。

页分裂还带来页级碎片：大量填充率 50%~70% 的页散落在磁盘各处，范围扫描的顺序性被破坏。information_schema.tables 里的 data_free 明显偏大就是碎片信号。

3. 二级索引回表越来越贵

二级索引也是一棵 B+ 树，同样会到 4 层。更关键的是回表：SELECT * WHERE key_col = x 要先查二级索引拿到主键，再回聚簇索引查整行——两次点查，各走一遍 4 层树。

树高 +1 意味着一次回表从 6 次页访问变 8 次。查询越多列、二级索引越多，放大越明显。这也是为什么优化大表时覆盖索引的收益格外显著：把查询列塞进二级索引，直接砍掉回表那一整棵树的遍历。

四、排查清单：你的表到底「慢在哪一步」

按顺序过一遍，基本能定位到具体层：

1. 先看执行计划：EXPLAIN 确认走没走索引、type 是否退化到 ALL/index。大表上的慢 SQL，八成还是索引问题而不是行数问题。
2. 看缓存命中率：上面的 Innodb_buffer_pool_read%。命中率低 → 加内存比什么都有效。
3. 看页填充与碎片：SHOW TABLE STATUS 的 Data_free 占比 > 20% 时，找低峰期 OPTIMIZE TABLE（或 pt-online-schema-change 重建）。
4. 看写入模式：主键是 UUID/随机值？填充率和分裂频率都差。有序主键（雪花 ID、自增）是硬前提。
5. 看慢日志聚合：pt-query-digest 拉一周慢日志，按指纹聚合。如果集中在少数几条 SQL，是索引/SQL 问题；如果是全表普遍变慢、越来越抖，才是容量问题。
6. 压测对比：同一主键点查，在 100 万行副本和线上表各跑一次。差距 < 2 倍说明树高影响可控，差距 10 倍+ 说明缓存已经兜不住。

五、那么，到底什么时候该分表

回到最初的问题。「2000 万行」本身不该是触发线，下面才是可操作的判断标准：

| 信号 | 阈值参考 | 动作 |

|---|---|---|

| Buffer Pool 命中率 | < 99% | 先加内存 / 优化查询，还压不住再考虑架构 |

| 树高 | 到 4 层且写入随机 | 先换有序主键 + 重建索引 |

| 单表物理大小 | > Buffer Pool 的 1.5~2 倍 | 评估归档冷数据 |

| 行数 | 与行宽有关，1KB 行约 2000 万 | 见上三条，而不是看行数本身 |

实际上大多数「千万行变慢」，归档冷数据 + 修复主键顺序 + 补覆盖索引三板斧之后还能再战很久。真正的分库分表留给：写入 TPS 打满单实例、归档后核心表仍然巨大、或者多维度查询已经无法用索引覆盖的场景——这部分方案选型（ShardingSphere、分片键设计、分布式事务代价）在我上一篇文章《分库分表方案》里展开过，两篇对照着看正好。

六、小结

• 「2000 万」= 三层 B+ 树在 1KB 行宽下的容量临界点，本质是树高临界点，不是行数魔数。
• 变慢的持续动力：热数据溢出 Buffer Pool → 随机 IO、随机主键页分裂 → 碎片与膨胀、树高增加 → 回表变贵。
• 诊断顺序：执行计划 → 缓存命中率 → 页碎片 → 主键顺序性 → 慢日志聚合。
• 分表是最后手段，触发标准是缓存/树高/物理大小的组合信号，不是行数本身。

相关阅读：《MySQL 锁机制》《慢 SQL 定位与优化》《分库分表方案》