📖 本篇简介：单表从几百万行涨到几亿行，查询从毫秒退化到秒级，DDL 一改锁库几小时——这是每个业务成熟后都要面对的"亿级表"难题。本篇用 10 个场景，带你从"该不该分表、怎么选分片键"，讲到"大表 DDL 不锁表、不停机迁移、跨分片分页、count 优化、冷热分离、迁移后校验回滚"。示例基于 MySQL 8.0，学完你能系统回答"亿级大表怎么设计和迁移"这类架构题，而不是只说一句"分库分表"。
目录
- 1 本篇导读与亿级表挑战概览
- 2 场景一：单表亿级行查询变慢何时该分表
- 3 场景二：亿级大表怎么设计主键与索引
- 4 场景三：水平分表怎么选分片键
- 5 场景四：亿级表历史数据归档怎么做
- 6 场景五：大表加字段加索引怎么做不锁表
- 7 场景六：亿级表数据不停机迁移怎么做
- 8 场景七：分表后全局唯一 ID 与跨分片分页
- 9 场景八：亿级大表 count 很慢怎么优化
- 10 场景九：冷热数据分离与归档表怎么设计
- 11 场景十：迁移后数据一致性校验与回滚预案
- 12 亿级表设计迁移速查表
- 13 常见坑与注意事项
- 14 练习题与自测
1 本篇导读与亿级表挑战概览
MySQL InnoDB 单表能存多少行？技术上千万级还能优雅运转，到了几千万上亿就开始吃力——不是存不下，而是索引维护、查询、DDL 的代价指数上升。亿级表的核心矛盾有四个：
1. 查询慢：B+ 树层数变高，回表随机 IO 增多；
2. DDL 慢且锁：加个字段、加个索引可能要锁表几小时；
3. 写入瓶颈：单库写入 TPS 到顶；
4. 运维难：备份、恢复、迁移都变得极其缓慢。
应对亿级表的三板斧：索引优化（先榨干单表潜力）、归档（把冷数据搬走）、分库分表（实在不行再拆）。注意顺序——分库分表是最后手段，不是第一步，因为它会带来跨分片查询、分布式事务等一堆新问题。
-- 本示例基于 MySQL 8.0
-- 先看当前表的真实大小和行数，判断是否真到了该拆的量级
SELECT table_rows,
ROUND(data_length/1024/1024/1024, 2) AS data_gb, -- 数据体积
ROUND(index_length/1024/1024/1024, 2) AS index_gb -- 索引体积
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_name = 'orders';
2 场景一：单表亿级行查询变慢何时该分表
场景描述：订单表已经 1.2 亿行，开发者说"太慢了，赶紧分库分表"。但你觉得是不是分早了？怎么判断到底该不该分表？
考察点：考察分表的时机判断——分表有成本，不能一慢就分。
解答思路：
分表之前，先穷尽单表优化。下面这些做了还是慢，才轮到分表：
1. 索引是否合理：慢 SQL 加了合适联合索引后，查询是不是从秒级降到毫秒？
2. 归档是否做了：历史冷数据搬到归档库后，热表是不是已经小了一半？
3. 读写分离是否上了：读压力是不是已经被从库分担？
经验阈值：单表行数在 2000 万以内通常无需分表，靠索引和归档就能扛；超过 5000 万且持续增长、单表索引维护和 DDL 已经很痛苦时，才认真考虑分表。
-- 本示例基于 MySQL 8.0
-- 先做个实验：看看有没有可能靠归档把热表瘦身
-- 一年前的数据占了多少？
SELECT COUNT(*) AS total FROM orders;
SELECT COUNT(*) AS old_1y FROM orders WHERE create_time < '2025-10-07';
-- 如果一年前数据占了 70%，搬到归档库后热表立刻从 1.2 亿变 3600 万，问题缓解
追问：单表 5000 万就必须分表吗？
不是绝对的。如果访问都走索引、热点集中在最近数据，1 亿行也能跑得很好；分表看的是"访问模式是否打满单库能力"，不是单纯看行数。
3 场景二：亿级大表怎么设计主键与索引
场景描述：要新建一张亿级订单表，主键和索引怎么设计，才能让后面几年都不后悔？
考察点：考察大表的索引设计前瞻性——一次建错，后面改索引代价巨大。
解答思路：
大表主键设计的核心原则：单调递增、短小、尽量自增或雪花 ID。因为 InnoDB 是索引组织表，主键决定数据物理顺序。如果主键用无序的 UUID，插入会导致 B+ 树页分裂，写入性能暴跌：
-- 本示例基于 MySQL 8.0
-- 正确：主键用 BIGINT 自增或雪花 ID，物理顺序递增，插入不页分裂
CREATE TABLE orders (
order_id BIGINT UNSIGNED NOT NULL COMMENT '主键，雪花ID',
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
status TINYINT NOT NULL DEFAULT 0,
create_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
PRIMARY KEY (order_id),
-- 高频查询路径提前规划联合索引
KEY idx_user_ctime (user_id, create_time),
KEY idx_status_ctime (status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
大表索引要"少而精"：每个索引都占空间、拖慢写入。亿级表上每多一个索引，写入和存储成本都线性增加。只建查询真正需要的联合索引，杜绝冗余索引。
-- 本示例基于 MySQL 8.0
-- 定期检查冗余索引，大表上冗余索引要及时删
SELECT table_name, index_name, GROUP_CONCAT(column_name) AS cols
FROM information_schema.statistics
WHERE table_schema='shop'
GROUP BY table_name, index_name;
追问：为什么大表不建议用 UUID 做主键？
UUID 无序，作为 InnoDB 主键会让每次插入都随机落页，频繁页分裂和磁盘随机写，写入性能和空间利用率都差。要用有序的雪花 ID 或自增。
4 场景三：水平分表怎么选分片键
场景描述：决定分表了，订单表按什么字段分片？是 user_id、order_id 还是 create_time？选错分片键后患无穷。
考察点：考察分片键选择这一分库分表最关键的决策。
解答思路：
分片键选择三原则：访问最频繁的维度、分布均匀、尽量让高频查询只路由单分片。
| 候选分片键 | 优点 | 缺点 |
|---|---|---|
| user_id | "我的订单"查询命中率高，按用户聚合 | 商家/运营按订单查要广播 |
| order_id | 均匀分布、写入均衡 | 按用户查要跨分片 |
| create_time | 便于按时间归档 | 热点集中在最近分片 |
绝大多数电商订单表选 user_id 分片，因为最高频的查询是"用户看自己的订单"，按 user_id 能只路由一个分片。代价是"商家看全量订单""后台运营查询"这类要跨分片，需要额外方案（见场景八）。
-- 本示例基于 MySQL 8.0
-- 按 user_id 哈希取模分片示意：user_id % 64 落到第 N 张表
-- 实际由 ShardingSphere 等中间件路由，业务无感知
-- orders_0 ~ orders_63 共 64 张物理表
-- user_id=12345 → 12345 % 64 = 57 → 路由到 orders_57
分片数要提前规划好（比如一开始就 64 或 128 张），因为后续扩容分片要做数据迁移，非常痛苦。宁可一开始多分几张小表，也别等不够了再拆。
分片键和分片数定下来后，整个架构基本就定型了，所以这一步要想得很远。下面这张表把常见分片策略列清楚：
| 分片策略 | 做法 | 优点 | 缺点 |
|---|---|---|---|
| 哈希取模 | user_id % N | 分布均匀 | 扩容要迁移数据 |
| 范围分片 | 按时间区间 | 便于归档 | 热点集中新片 |
| 一致性哈希 | 环上取节点 | 扩容迁移少 | 实现复杂 |
| 按库分 + 按表分 | 先分库再分表 | 容量弹性大 | 路由复杂 |
追问：按 user_id 分片后，用户量大的大 V 会不会把某个分片打爆？
会，这就是数据倾斜。极端热点用户要单独处理，或改用一致性哈希让热点分散。
5 场景四：亿级表历史数据归档怎么做
场景描述：订单表 1.2 亿行，但 90% 是一年前的历史订单，几乎不再访问。怎么把冷数据搬走，让热表瘦身？
考察点：考察数据归档（archive）这一亿级表最常用、代价最低的优化手段。
解答思路：
归档的核心是"把不常访问的老数据搬到别处，热表只留近期数据"，对业务基本透明。
第一步，建一张归档表（结构和原表一致），或归档到独立归档库。第二步，分批把老数据搬过去，小批量、低峰期、避免大事务锁表：
-- 本示例基于 MySQL 8.0
-- 归档：每次搬 1000 行，循环执行，避免大事务长锁
-- 第一步：把一年前的数据插入归档表
INSERT INTO orders_archive
SELECT * FROM orders
WHERE create_time < '2025-10-07'
ORDER BY order_id
LIMIT 1000;
-- 第二步：确认归档成功后，删除原表这批数据
DELETE FROM orders
WHERE create_time < '2025-10-07'
ORDER BY order_id
LIMIT 1000;
-- 用脚本循环执行，每批间隔几秒，低峰期跑
关键点：插入归档表和删除原表要分开小批做，不要一次搬一亿行。一次大事务会锁表、产生巨大 undo、主从延迟爆炸。分批搬、每批提交，才能平滑。
# 本示例为 sqlite3 演示：模拟分批归档，每批搬 2 行
import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE orders(order_id INT, ctime TEXT)")
cur.execute("CREATE TABLE orders_archive(order_id INT, ctime TEXT)")
cur.executemany("INSERT INTO orders VALUES(?,?)",
[(1,'2024-01-01'),(2,'2024-02-01'),(3,'2026-01-01')]) # 前两条是冷数据
conn.commit()
# 分批：每次搬 ctime<'2025' 的数据，这里 LIMIT 2 演示
cur.execute("INSERT INTO orders_archive SELECT * FROM orders WHERE ctime<'2025' LIMIT 2")
cur.execute("DELETE FROM orders WHERE ctime<'2025' LIMIT 2")
conn.commit()
print("热表剩余:", cur.execute("SELECT COUNT(*) FROM orders").fetchone()[0]) # 1
print("归档表:", cur.execute("SELECT COUNT(*) FROM orders_archive").fetchone()[0]) # 2
conn.close()
上面脚本实跑输出热表剩 1 行、归档表 2 行。
追问：归档后用户查历史订单怎么办？
归档库要能查。一般做法是应用层判断：查近期订单走热表，查历史订单走归档库；或用中间件做透明路由。
6 场景五：大表加字段加索引怎么做不锁表
场景描述：亿级订单表要加一个索引和一个字段，直接 ALTER TABLE 估计要锁表 3 小时，业务不能停。怎么在线做 DDL？
考察点：考察 Online DDL 与 pt-online-schema-change 等在线变更工具。
解答思路：
MySQL 8.0 的 Online DDL 已经支持大部分变更不锁表：
-- 本示例基于 MySQL 8.0
-- 加索引：8.0 支持 INPLACE + LOCK=NONE，业务基本无感
ALTER TABLE orders
ADD INDEX idx_user_ctime (user_id, create_time),
ALGORITHM=INPLACE, LOCK=NONE;
-- 加字段：8.0.12+ 支持 Instant 加列，几乎瞬间完成
ALTER TABLE orders ADD COLUMN extra VARCHAR(64) DEFAULT NULL,
ALGORITHM=INSTANT; -- Instant 加列是 8.0 大杀器
但 Online DDL 仍有局限：有些变更（如改列类型、建全文索引）不支持 INPLACE；且即便 INPLACE 也会消耗 IO，要在低峰期做。更稳妥的大表 DDL 用 Percona 的 pt-online-schema-change：它的思路是"建新表结构 + 触发器同步增量 + 最后 rename"，对原表影响最小。
# pt-online-schema-change 示意（本环境无 MySQL，仅说明用法）
# 在原表上影子复制数据，通过触发器同步期间的增量变更，最后原子切换
pt-online-schema-change --alter "ADD INDEX idx_user_ctime(user_id,create_time)" \
D=shop,t=orders --execute
追问：Online DDL 期间主从延迟会不会暴涨？
会。DDL 在主库跑时，从库要重放同样的变更，大表 DDL 期间从库延迟会明显上升，所以要在低峰期、且密切盯主从延迟。
7 场景六：亿级表数据不停机迁移怎么做
场景描述：要把单表 orders 拆成按 user_id 分表的 64 张表，但业务 7x24 不能停。怎么做到不停机迁移？
考察点：考察双写/数据同步这类在线迁移方案，这是分库分表落地的硬骨头。
解答思路：
不停机迁移的经典套路是"双写 + 校验 + 切读 + 切写"四步：
1. 存量迁移：先用工具把老表历史数据批量搬到新分片表；
2. 增量同步：通过 binlog（Canal）把老表的增量变更实时同步到新表，追平迁移期间的新数据；
3. 双写过渡：新业务代码同时写老表和新表，保证两边数据一致；
4. 切读切写：校验一致后，先把读流量切到新表观察，没问题再把写也切过去，最后停掉老表。
-- 本示例基于 MySQL 8.0
-- 双写阶段示意：业务同时写老表 orders 和新分片表 orders_N
-- 老表保留作为兜底，新表承接新流量
INSERT INTO orders(...) VALUES(...); -- 老表（兜底）
INSERT INTO orders_57(...) VALUES(...); -- 新分片表（user_id%64=57）
这个过程要持续一段时间（几天到几周），期间一直双写，直到确认新表稳定才停老表。切忌一把切换——一旦新表有问题，回滚都来不及。
为什么要这么保守？因为亿级表迁移最怕的就是"切完发现少了一批数据"。双写期间老表一直是完整的真相来源，新表只是影子；任何时候新表出问题，把流量切回老表就完事了，数据零丢失。这个"老表兜底"的设计，是不停机迁移能睡得着觉的关键。
追问：双写期间两边数据不一致怎么办？
靠定时校验任务比对，不一致以一边为准做修复；切流前必须跑一遍全量校验，确认 100% 一致才能切。
8 场景七：分表后全局唯一 ID 与跨分片分页
场景描述：订单分了 64 张表，现在要做"后台订单列表按时间倒序分页"，跨了所有分片，怎么查？怎么分页？
考察点：考察分库分表后最头疼的跨分片查询与深分页问题。
解答思路：
跨分片分页的难点是：每片都要按时间排序取前 N 条，中间件再归并排序。如果查"第 1 页"还行，查"第 1000 页"就要从每片取前 1000×页大小条再归并，代价巨大。
-- 本示例基于 MySQL 8.0（分片后示意）
-- 按 user_id 分片后，带 user_id 的查询只走单分片，很快
SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 20;
-- 不带 user_id 的后台查询要广播到 64 个分片再归并，慢
-- SELECT * FROM orders ORDER BY create_time DESC LIMIT 0,20;
-- 优化：后台大屏/报表干脆不查分片库，走 binlog 同步到的 ES/ClickHouse
工程上的解法：
1. 后台查询走分析库：binlog 同步到 ES 或 ClickHouse，分页、搜索、聚合都在那里做；
2. 禁止深分页：后台列表限制最大翻页数，或用"游标分页"（记录上一页最后一条的 id，下一页从它之后查）；
3. 全局 ID 用雪花：分表后自增主键冲突，必须用全局唯一 ID。
追问：游标分页和传统 LIMIT offset 分页有什么区别？
LIMIT offset 越大越慢（要扫描 offset+N 行）；游标分页用上一页最后的排序值做条件，每次都从索引位置直接开始，深翻页也快。
9 场景八：亿级大表 count 很慢怎么优化
场景描述：SELECT COUNT(*) FROM orders 在亿级表上要跑几十秒，后台页面打不开。怎么优化？
考察点：考察大表 count(*) 的优化，这是高频面试题。
解答思路：
COUNT(*) 在 InnoDB 下要逐行扫描（因为 MVCC，每个事务看到的行数不同，不能像 MyISAM 那样直接存个数）。亿级表必然慢。
-- 本示例基于 MySQL 8.0
-- 粗略估算：直接从 information_schema 拿，秒出（但不精确）
SHOW TABLE STATUS LIKE 'orders'; -- 看 Rows 列，是统计估算值
-- 精确 count 但加了主键范围，会走索引扫描，比全表快些
SELECT COUNT(*) FROM orders WHERE order_id > 0;
优化方案：
1. 估算值：后台不要求精确数字时，用 information_schema.tables 的 table_rows 估算，秒出；
2. 缓存计数：用 Redis 维护一个计数器，插入+1 删除-1；
3. 异步预聚合：定时把 count 结果存到小表，后台查小表；
4. 走分析库：大表统计走 ClickHouse，它列存 count 极快。
追问：MyISAM 的 count(*) 为什么快？
MyISAM 没有 MVCC，表级锁下总行数固定，直接存了个计数；InnoDB 因为 MVCC 要对每个事务算可见行，没法缓存总数。
10 场景九：冷热数据分离与归档表怎么设计
场景描述：业务有大量历史订单永远不会被普通用户再查，但合规要求保留 3 年。怎么设计冷热分离，既省成本又合规？
考察点：考察冷热分离的存储分层设计。
解答思路：
核心思路是"热数据在高性能库、温数据在普通库、冷数据在廉价存储"，按访问频率分层：
| 数据温度 | 时间范围 | 存哪 | 访问特点 |
|---|---|---|---|
| 热数据 | 近 3 个月 | 主业务库（SSD） | 高频随机读写 |
| 温数据 | 3 个月~1 年 | 普通从库/归档库 | 偶发查询 |
| 冷数据 | 1 年以上 | 对象存储/HDFS | 几乎不访问，合规用 |
-- 本示例基于 MySQL 8.0
-- 归档表结构与热表一致，但可以更紧凑（去掉次要索引省空间）
CREATE TABLE orders_cold LIKE orders;
-- 冷表只留必要索引，因为查询少，索引可以精简
-- 老数据按分区表按月归档，DROP PARTITION 比 DELETE 快得多
ALTER TABLE orders
PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p2026q3 VALUES LESS THAN (TO_DAYS('2026-10-01')),
PARTITION p2026q4 VALUES LESS THAN (TO_DAYS('2027-01-01'))
);
-- 老分区直接 DROP，瞬间释放空间，比 DELETE 一亿行快几个数量级
追问：分区表和分库分表是一回事吗？
不是。分区表逻辑上还是一张表，由数据库内部管理，对应用透明；分库分表是物理上拆到多库多表，要中间件路由。分区适合单表内按时间归档，分库分表解决单库容量/写入上限。
11 场景十：迁移后数据一致性校验与回滚预案
场景描述：分表迁移做完了，你怎么确信新老表数据 100% 一致？万一发现问题，回滚预案是什么？
考察点：考察迁移收尾的严谨性——校验和回滚是架构师和新手的分水岭。
解答思路：
校验分三层：
1. 总量校验：新老表行数对不对得上；
2. 抽样校验：随机抽一批行，逐字段比对；
3. 全量校验：用 checksum 或逐行比对，确认无遗漏。
-- 本示例基于 MySQL 8.0
-- 1. 总量对比
SELECT COUNT(*) FROM orders; -- 老表
SELECT COUNT(*) FROM orders_sharded; -- 新表（所有分片之和）
-- 2. 抽样：随机取一批 order_id，比对关键字段
SELECT order_id, status, amount FROM orders WHERE order_id IN (1,2,3,...);
-- 与新分片表同 id 的记录逐字段比对
回滚预案：因为迁移期间一直双写老表，切流后如果新表出问题，把流量切回老表即可——老表一直是最新的。这就是为什么迁移期不能急着停老表。
# 本示例为 sqlite3 演示：模拟新老表数据一致性校验
import sqlite3
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE old_t(id INT, v TEXT)")
cur.execute("CREATE TABLE new_t(id INT, v TEXT)")
cur.executemany("INSERT INTO old_t VALUES(?,?)", [(1,'a'),(2,'b'),(3,'c')])
cur.executemany("INSERT INTO new_t VALUES(?,?)", [(1,'a'),(2,'b'),(3,'x')]) # 3号不一致
conn.commit()
# 找出新老表不一致的行（按 id 左连接比对）
q = """SELECT old_t.id FROM old_t LEFT JOIN new_t ON old_t.id=new_t.id
WHERE new_t.id IS NULL OR old_t.v <> new_t.v"""
for row in cur.execute(q):
print("不一致行 id:", row[0]) # 输出 不一致行 id: 3
conn.close()
上面脚本实跑输出 不一致行 id: 3，精准定位那条对不上的记录。
追问：全量校验 1 亿行要多久？怎么减少对业务影响？
用 checksum 分批校验、低峰期跑，或只校验增量时间段。全量一次性比对会把 IO 打满，要分批限速。
12 亿级表设计迁移速查表
| 问题 | 首选方案 | 关键提醒 |
|---|---|---|
| 该不该分表 | 先归档、再读写分离，最后才分 | 5000 万行以下通常不必分 |
| 主键设计 | BIGINT 自增/雪花，单调递增 | 忌用 UUID，防页分裂 |
| 分片键 | 选最高频查询维度（如 user_id） | 要分布均匀、提前定分片数 |
| 历史归档 | 小批量分批搬，低峰期 | 切忌一次大事务 |
| 大表 DDL | Online DDL / pt-osc | 盯主从延迟 |
| 不停机迁移 | 双写+binlog同步+切读切写 | 保留老表兜底 |
| 跨分片分页 | 走 ES/CK，游标分页 | 禁止深分页 |
| count(*) 慢 | 估算值/缓存计数/分析库 | InnoDB 不能存总数 |
| 冷热分离 | 按时间分区，DROP 老分区 | 分区表≠分库分表 |
| 迁移阶段 | 做什么 | 何时切 |
|---|---|---|
| 存量迁移 | 批量搬历史数据 | 低峰期 |
| 增量同步 | binlog 追平新数据 | 持续到一致 |
| 双写过渡 | 新老表同时写 | 观察几天 |
| 切读 | 读走新表 | 校验一致后 |
| 切写 | 写走新表，停老表双写 | 稳定后 |
13 常见坑与注意事项
1. 一慢就分表：没先做索引和归档，分表后一堆跨分片问题，得不偿失。
2. 分片键选错：选了低频查询维度，导致大量查询要广播全分片，最痛的教训。
3. 分片数后期才扩容：一开始只分 4 片，涨到不够再扩要全量迁移，痛苦不堪。
4. 一次大事务归档：搬一亿行不分批，锁表 + undo 暴涨 + 主从延迟。
5. UUID 做主键：写入页分裂，亿级表写入性能崩盘。
6. 大表冗余索引不清理：每个索引都拖慢写入，亿级表索引要少而精。
7. 一把切流不留老表：没有双写兜底，新表出问题无法回滚。
8. count(*) 硬查业务库：后台统计把主库查锁死，应走分析库。
14 练习题与自测
练习 1：单表 1.2 亿行，开发者要求立刻分库分表，你会怎么说服他先做别的？
参考答案：分表有跨分片代价。先排查索引、做历史归档（90% 是冷数据）、上读写分离，热表瘦身后大概率不需要分。
练习 2：亿级表主键为什么推荐自增/雪花而不是 UUID？
参考答案：InnoDB 是索引组织表，主键无序会导致插入页分裂、随机写，自增主键物理递增、写入顺序高效。
练习 3：订单表分表怎么选分片键？为什么？
参考答案：选 user_id，因为最高频的"我的订单"查询按 user_id 能路由单分片；代价是后台全量查询要走分析库。
练习 4：大表加索引如何不锁表？
参考答案：用 8.0 Online DDL（ALGORITHM=INPLACE, LOCK=NONE）或 pt-online-schema-change，低峰期执行并盯主从延迟。
练习 5（自测）：口述不停机分表迁移的五个阶段，并说明每个阶段结束的标志。
自测要点：能否说出存量迁移→增量同步→双写过渡→切读→切写，且老表保留兜底可回滚。
下一篇预告：第 25 章 主从延迟切换脑裂现场处置
文章回复
0 条公开回复