MySQL 数据太大以后怎么办：分区、归档、分库分表与在线 DDL

“这张表已经有一亿行，要不要分库分表？”是一个没有足够信息的问题。同样是一亿行，只按主键读取、索引和内存配置合理的表，可能运行得很平稳；另一张只有几百万行的表，也可能因为宽行、低选择性索引、全表扫描、长事务或频繁 DDL 而不断出问题。

数据量增长真正改变的是一组成本：热点数据能否留在 Buffer Pool，索引树需要访问多少页，写入会产生多少 redo 和复制流量，一次备份与恢复要多久，删除历史数据是否拖住 purge，一次结构变更还能否在维护窗口内完成。行数只是这些成本的粗略代理，不是扩容方案的开关。

因此，大表治理不是在“分区”和“分库分表”之间二选一。它是一条从低成本治理走向架构拆分的决策链：先确认瓶颈，再缩小热数据与单次操作的范围；只有单实例资源、写入能力或故障恢复边界确实无法满足目标时，才引入跨库路由。

MySQL 数据增长后的治理决策图

本文讨论的是 MySQL 侧的判断与操作边界。分片键、基因法、跨片查询和扩容迁移协议已在《分库分表：分片键、路由、扩容与数据迁移》中展开，这里不再重复。

一、先把“表太大”改写成可观测的问题

工程上没有一个普适的“超过多少行必须拆表”。决定方案的，是已经违反或即将违反哪项服务目标。

查询慢，未必是容量问题

先看慢 SQL 的执行计划、扫描行数、返回行数和等待事件。如果一次订单查询只返回 20 行，却扫描数十万行，优先修复访问路径。缺少联合索引、条件发生隐式转换、排序无法利用索引、深分页和统计信息失真，都可能让小表也变慢。

若执行计划稳定、每次只读少量索引页，表继续增长不一定线性拖慢查询。B+ 树高度增加得很慢，真正明显的变化往往来自工作集超过内存：过去频繁访问的索引页都在 Buffer Pool，后来随机查询开始持续触盘，尾延迟才陡增。

判断时至少分开观察：

SQL 的 rows examined / rows sent、P95/P99 延迟和执行计划是否变化；
Buffer Pool 命中、脏页比例、磁盘读写延迟与 IOPS；
redo 产生速度、checkpoint 压力、事务提交延迟；
主从复制延迟、备份时长、恢复演练时间；
长事务、undo history 与 purge 是否积压；
DDL 的预计时长、临时空间和可接受阻塞窗口。

如果问题只是某条 SQL 扫描过多，分成 100 张表可能只是让它同时扫描 100 个更小的错误索引。若问题是保留了十年几乎不访问的历史明细，先把冷数据移出在线表，常常比引入分片中间件更直接。

先给容量和恢复设预算

大表治理需要回答的不只是“现在能不能跑”，还要回答故障后多久能回来。可以给单实例设一组明确预算：磁盘使用不超过多少、热点索引需要多少内存、峰值写入留下多少余量、全量备份多久完成、恢复到新实例的 RTO 是多少、最多允许丢失多少数据。

例如，数据库延迟仍然正常，但从备份恢复需要八小时，而业务要求两小时恢复，这已经是容量问题。反过来，如果磁盘只用了 30%，恢复演练也在预算内，只因行数越过某个整数就拆库，没有解决真实风险，反而增加了路由、迁移和跨片查询成本。

用增长速度推算时间，不只看当前水位

容量规划还要加入时间。先从最近几个月统计每日新增行、数据文件与索引增长、峰值写入和业务季节性，再分别估算正常增长与活动峰值。一个简化模型是：

预计可用天数 = 可用容量 × 安全利用率 ÷ 每日净增长量

这里的“可用容量”不能直接取磁盘剩余空间。还要为临时文件、Online DDL、备份、故障恢复和突发增长预留空间；“每日净增长”也应扣除稳定运行的归档和删除量。若一张表每天新增 300 GB，同时稳定归档 220 GB，长期净增长是 80 GB，但 DDL 演练仍可能需要接近一份表或索引的额外空间，两个数字都要纳入计划。

同样要为写入和恢复计算时间余量。当前峰值写入只占压测安全上限的 45%，不等于一年后仍安全；备份文件能生成，也不等于能在 RTO 内下载、校验并恢复。把预计触达高水位的日期写进容量看板，才能给归档改造、硬件扩容或分库迁移留下交付周期。

这也解释了为什么不能等磁盘达到 90% 才讨论方案。分库迁移需要双写、回放和校验，反而会暂时增加存储与写入成本。容量治理应该在还有余量时启动，但启动依据是增长曲线和交付周期，而不是一个脱离业务的固定行数。

二、第一步通常是减少在线表承担的工作

在改变数据分布前，先处理低成本且可逆的部分。

修正访问路径和行宽

索引应围绕真实 SQL 设计，而不是给每个条件字段各建一个单列索引。只返回列表需要的列，避免 SELECT * 把大字段反复从聚簇索引和网络中搬运。低频读取的订单快照、原始报文、长文本和大 JSON 可以拆到详情表或对象存储，主表只保留高频状态与定位信息。

这不会改变总数据量，却会缩小热工作集，让更多高频页留在内存，也会降低二级索引、复制和备份的放大成本。表结构的具体取舍见《MySQL 表结构怎样设计》。

读压力优先考虑读扩展，但别混淆一致性

如果瓶颈来自大量可容忍延迟的只读请求，可以通过缓存、只读副本和查询隔离分担主库压力。它们扩展的是读能力，不会缩小主库数据，也不会提高单主写入上限。刚写完必须读到自己的结果时，还要处理复制延迟，不能把所有查询机械地发往从库。

读写分离与故障切换的边界已在《MySQL 复制、读写分离与故障切换》中说明。若写入、存储或恢复已碰到单实例上限，加从库并不能消除这个上限。

三、历史数据归档：先让热表只保存在线业务需要的数据

很多所谓“大表”，真正的问题是把在线交易和长期留存混在了一起。业务页面只查询最近 90 天，补偿任务只扫描最近 7 天，三年前的记录却仍与今天的订单共享同一棵聚簇索引和同一组二级索引。

归档的目标不是简单删除，而是建立数据生命周期：

在线热表 -> 归档表或归档库 -> 对象存储/数仓 -> 到期销毁
   90 天       1~3 年             更长期留存

每一层都要明确查询入口、留存期限、校验方式和恢复流程。客服偶尔查历史订单，不意味着所有历史记录都必须留在主交易表；可以由专门的历史查询接口访问归档库。

不要用一条巨大 DELETE 清历史

下面的语句语义简单，在线执行却可能制造大事务：

DELETE FROM orders
WHERE created_at < '2025-01-01';

大量删除会持有更多锁，产生大量 undo、redo 和 binlog，推高复制延迟，并让 purge 在之后继续工作。事务失败时回滚也可能很慢。更稳妥的方式是按稳定索引小批处理，每批提交，并根据复制延迟和实例负载节流：

SELECT id
FROM orders
WHERE created_at < ?
  AND id > ?
ORDER BY id
LIMIT 1000;

DELETE FROM orders
WHERE id IN (?, ?, ...);

真实实现不能只循环 DELETE ... LIMIT 1000 而不保存进度，否则重启后难以确认边界。归档任务应记录游标、批次、源端数量、目标端数量与校验结果，流程通常是“复制一批—校验一批—删除一批”。出现错误时，要能从已确认游标继续，而不是从头重扫。

删除行也不等于操作系统立刻得到同等磁盘空间。InnoDB 可以复用页内空闲空间，但表空间文件是否缩小取决于表空间布局和后续重建等操作。若治理目标是立刻归还磁盘，仅完成 DELETE 不代表任务结束，还要评估重建表的空间、时间和线上影响。

归档是数据产品，不是一次脚本

归档后仍要回答几件事：唯一性是否只约束热表，历史数据能否重复写入；跨冷热数据怎样查询；合规删除是否同时覆盖备份和归档；恢复历史记录会不会与当前数据冲突；应用发布回滚后是否还能识别新旧存储位置。

这些问题决定归档接口与元数据。一次性脚本能搬走数据，却不能长期维护数据生命周期。

四、分区表解决的是管理与裁剪，不是增加一组数据库

MySQL 分区把一张逻辑表的行按规则放到多个物理分区中。应用仍访问同一个表名，分区仍在同一实例的资源和故障域内。它没有把写入分散到多台机器，也没有让单实例凭空获得更多 CPU、内存或日志吞吐。

RANGE 分区最常用于时间序列数据：

CREATE TABLE event_log (
    id          BIGINT      NOT NULL,
    created_at  DATETIME(3) NOT NULL,
    payload     JSON        NOT NULL,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
    PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
    PARTITION p202611 VALUES LESS THAN ('2026-12-01'),
    PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

当查询条件能够限定分区表达式时，优化器可以进行 partition pruning，只访问相关分区。按月删除历史数据时，也可以通过分区维护把整个时间范围移除，避免逐行删除产生同等规模的 undo 与索引维护。MySQL 官方文档分别说明了分区裁剪和RANGE/LIST 分区管理。

分区适合什么

分区最有价值的两个场景是：查询天然带时间或范围条件，可以稳定裁剪；数据按同一范围到期，可以以分区为单位维护生命周期。日志、事件、监控明细和按月到期的流水比随机点查业务表更容易满足这个条件。

它也能把某些维护操作限制在单个分区，但不要把“物理上有多个分区”推导为“任何查询都会更快”。按 order_no 点查而条件里没有分区列时，可能需要检查多个分区；分区数量过多还会增加元数据和运维负担。

三条经常被忽略的硬约束

第一，MySQL 要求分区表的每一个唯一键都包含分区表达式涉及的全部列。若原订单表是 PRIMARY KEY(id)、UNIQUE(order_no)，现在想按 created_at 分区，不能原样套上分区定义；主键和订单号唯一键都必须包含 created_at。这会改变唯一性语义，也可能迫使调用方携带创建时间。官方的分区键与唯一键限制给出了具体规则。

第二，MySQL 8.4 的分区 InnoDB 表不能包含外键引用，也不能被外键引用。已有外键模型不是加一行 PARTITION BY 就能改造，参见官方的分区存储引擎限制。

第三，DROP PARTITION 会删除分区内的数据。分区切换、备份和删除都需要把边界算清楚，并经过数量与时间范围校验。DDL 执行成功不等于业务选择了正确的数据范围。

因此，分区不是“大表优化开关”。如果核心查询不带分区键、唯一键约束与分区列冲突，或者目标是突破单实例写入上限，分区就不是合适答案。

五、单库分表与分库分表不是同一层方案

单库分表缩小单棵树，但保留单实例上限

把 orders 拆成 orders_00 到 orders_63，每张表的索引树更小，单表 DDL、归档和某些扫描的范围也会缩小。它与原生分区的区别是：路由规则和多表查询由应用或中间件承担，每张表可以有独立名称和结构。

代价也很直接：所有 SQL 必须正确路由，批量查询可能散射到多表，唯一约束无法天然跨表生效，结构变更要编排 64 次。更关键的是，这些表仍共享同一实例的 CPU、Buffer Pool、redo、磁盘和故障恢复。若瓶颈是单实例写入或存储，单库分表只改变了组织形式。

分库分表才是在增加资源与故障域

将数据分散到多台数据库，可以增加总存储和写入吞吐，并把单个故障或恢复任务限制在一个分片。但系统从此必须承担分片路由、跨片查询、全局唯一性、容量均衡、扩容迁移和多分片事务。

方案	主要解决	不能解决或新增的问题
归档	热表过大、历史数据拖累在线维护	不提升当前热点写入上限
MySQL 分区	范围裁剪、按分区管理生命周期	仍是单实例；受唯一键和外键限制
单库分表	缩小单表索引和单次维护范围	仍共享实例资源；应用路由与多表 SQL 变复杂
分库分表	单实例容量、写入或故障恢复上限	引入路由、迁移、跨片查询和一致性成本

选择分库分表前，应有证据说明低成本手段已不足：热点工作集无法在可接受成本内承载；峰值写入长期逼近单实例安全水位；磁盘或恢复时间无法满足容量预算；或者需要把故障域拆小。然后再进入分片键、路由和迁移设计，而不是从“建多少张表”开始。

六、Online DDL 在线的含义，是允许并发，不是没有代价

大表最终都会遇到结构变更：增加索引、修改字段、调整默认值或重建表。MySQL InnoDB 支持多种 DDL 算法，但“Online DDL”很容易被误解为“线上随时执行都不会影响业务”。

MySQL 8.4 中常见的算法可以这样理解：

INSTANT 主要修改数据字典，不重建表，通常最快，但只支持特定操作；
INPLACE 避免传统意义上的整表复制，但某些操作仍要重建表或索引；
COPY 创建新表并复制数据，成本与阻塞通常最高。

具体 ALTER 是否支持某个算法和并发级别，取决于操作类型、表结构和版本，不能只凭名称判断。官方的InnoDB Online DDL 操作表逐项列出了能力边界。

执行关键变更时，可以显式写出自己允许的上限：

ALTER TABLE orders
    ADD INDEX idx_status_created_id (status, created_at, id),
    ALGORITHM = INPLACE,
    LOCK = NONE;

这里的价值不是强迫 MySQL 一定做到，而是在不支持时让语句失败，避免悄悄退化成不可接受的算法或锁级别。执行前仍应在相同版本、相近数据量和结构的环境验证。

LOCK=NONE 也躲不开 MDL

DDL 需要元数据锁（Metadata Lock，MDL）保护表定义。其他事务使用过这张表后，相关 MDL 通常会持有到事务结束；未提交的长事务可能让 ALTER 在开始或收尾阶段等待。更危险的是，排队中的 DDL 还可能使后续请求继续堆积。

MySQL 官方的元数据锁文档说明了锁的持有与释放规则。上线前应检查长事务和 MDL 等待，设置合理的锁等待策略，并确保失败能够安全重试，而不是让 DDL 无限等待。

并发 DML 不代表资源免费

重建索引或表仍会消耗 CPU、I/O、临时磁盘与 Buffer Pool，期间的并发修改还要被记录并在合适阶段合并。它可能推高业务 P99、增加 redo 和复制延迟，甚至因为临时空间不足而失败。MySQL 官方也单独列出了Online DDL 的性能与并发影响和空间要求。

Online DDL 的执行阶段与线上风险

一份可执行的变更计划至少包括：

在目标版本确认算法与锁能力，估算重建数据量和临时空间；
检查长事务、MDL、磁盘余量、复制延迟和峰值时段；
先在相近数据规模环境演练，再从低流量实例或分片灰度；
运行中监控业务 P99、I/O、redo、临时空间和副本延迟；
预先定义暂停、终止和重试条件，并验证终止本身的成本；
完成后检查索引、执行计划、数据一致性和所有副本状态。

外部 Online Schema Change 工具做了什么

当原生 ALTER 的算法、锁时间或可控性不能满足要求时，常见工具会创建影子表，按小批复制旧数据，同时捕获增量变更，追平后再通过短暂的元数据操作切换表名。pt-online-schema-change 和 gh-ost 是这类思路的典型实现。

它们把一次巨大操作拆成可节流、可观察的阶段，却没有消除复制数据、保存增量和最终切换的成本。触发器、binlog 格式、外键、磁盘空间、主从拓扑以及切换阶段的 MDL 都可能成为约束。采用工具前要按当前架构验证其工作机制，而不是把“在线变更工具”当作免风险按钮。

七、一个订单大表该怎样选择

假设订单表保存了三年数据，产品请求的 95% 只访问最近 90 天，超时扫描只处理最近 7 天；旧订单因合规要求需要保留，但访问频率很低。近期出现三个问题：Buffer Pool 中热索引竞争加剧，清理历史数据导致复制延迟，给订单列表增加联合索引的窗口越来越长。

第一步不是立刻分库，而是把问题拆开。

历史数据采用“归档—校验—批量删除”的流水线，在线表只保留业务需要的时间范围；历史查询走归档服务。主表的大 JSON 和低频快照迁到详情表，订单列表只访问窄表。慢查询按真实过滤与排序条件补联合索引，并在相近数据规模上演练 Online DDL。

接着评估时间分区。它看起来很适合按月归档，但原表若有 PRIMARY KEY(id) 和 UNIQUE(order_no)，按 created_at 分区会触发“所有唯一键必须包含分区列”的限制。把主键改成 (id, created_at)、唯一键改成 (order_no, created_at) 会改变约束和引用方式。若业务必须保证 order_no 跨所有时间全局唯一，且大量查询只拿订单号，那么为了归档而改分区未必值得，继续采用归档表可能更清晰。

最后重新测量。若归档和结构优化后，主库写入、磁盘、恢复时长与尾延迟都回到预算内，就没有必要仅为“未来也许更大”立刻分库。如果订单持续增长后，单实例写入与恢复边界仍然无法满足目标，再进入分库分表方案，并把路由、跨片查询与迁移当作一个完整系统设计。

这个过程的关键不是选中了哪项技术，而是每项措施都对应一个已证明的瓶颈。

八、决策清单

面对大表，可以按下面的顺序评审：

1. 慢在哪里：SQL、内存工作集、磁盘、写入、复制，还是恢复？
2. 能否先修访问路径、缩窄行、拆低频大字段？
3. 哪些数据仍属于在线业务，哪些应归档或到期删除？
4. 查询和生命周期是否天然包含同一个分区键？
5. 唯一键、外键与访问方式是否允许使用 MySQL 分区？
6. 单库分表是否真的解决实例瓶颈，还是只把表名变多？
7. 哪项指标证明必须跨库：容量、写入、恢复还是故障域？
8. DDL 支持什么算法，MDL、临时空间和副本延迟如何控制？
9. 回滚与恢复如何做，谁来验证数据和执行计划？

大表治理最容易犯的错误，是用更复杂的拓扑掩盖没有诊断的问题。归档负责数据生命周期，分区负责范围裁剪与管理，单库分表负责缩小单表操作范围，分库分表负责突破单实例边界，Online DDL 负责在明确约束下演进结构。它们不是相互替代的流行方案，而是针对不同瓶颈的工具。

先把问题写成指标，再选择成本最低、边界最匹配的机制。这样即使最后确实需要分库，迁移的也是经过整理的热数据和稳定访问路径，而不是把一张失控的大表原样复制成很多张小表。