MySQL：非分区表想改成分区表，MySQL不支持直接ALTER TABLE ADD PARTITION，只能CREATE TABLE+INSERT SELECT重建，几亿行怎么停业务？

一、问题定义：为什么"加分区"不是一次元数据变更

先把问题说清楚。很多团队的第一反应是"分区表不就是多几个分区吗，加一个 ALTER TABLE t ADD PARTITION 不就行了"。这个直觉在 Oracle、PostgreSQL 的部分场景下成立，在 MySQL 上不成立，原因不在语法糖，而在存储引擎的物理组织方式。

MySQL 官方手册对分区表有一句关键表述：分区表由"一组底层表"实现，每个分区在文件系统层面是一个独立的表空间或独立文件（取决于 innodb_file_per_table 与通用表空间配置）。更关键的是约束：分区表的分区表达式所引用的每一列，都必须包含在每一个唯一键（含主键）中。这条规则直接决定了"原地改造"在多数业务表上不可行——因为业务表的主键通常是自增 id，而分区键往往是 created_at 或 tenant_id，两者不一致。

本文评述：这条约束常被误读为"MySQL 分区功能弱"。笔者认为它恰恰是 InnoDB 聚簇索引设计的必然推论——聚簇索引的键就是数据物理排序的依据，如果分区键不在主键里，同一分区内就无法保证主键唯一性的局部可判定，全局唯一校验将退化为跨分区扫描。所以"不能原地加分区"不是实现缺陷，而是索引组织表（IOT）模型的代价。

于是问题被重新定义为：如何在不长时间停写的前提下，把一张几亿行的非分区表，替换成一张结构等价、数据一致、主键与分区键相容的分区表。注意这里有三个硬性条件——结构等价（列、类型、字符集、排序规则、索引全部对齐）、数据一致（切换瞬间零丢失零错位）、主键相容（可能需要重新设计主键）。第三个条件往往被低估，它意味着"重建"不只是搬数据，还可能是一次主键模型迁移。

把停机窗口作为主线，是因为它才是工程上真正稀缺的资源。几亿行数据在普通云盘上全量搬迁，按 100 MB/s 有效吞吐、单行 500 字节估算，约 250 GB 数据需要 40 分钟以上纯 IO；若叠加二级索引重建、binlog 写入放大、从库延迟，实际耗时通常是理论值的 2–4 倍。这意味着"停业务做"的方案在多数在线系统上不可接受，必须走"在线重建 + 短切换"的路线。

二、原理层：InnoDB 分区表的物理结构与约束边界

2.1 分区是"逻辑视图 + 物理分表"

InnoDB 分区表在数据字典中是一张表，但在存储层是 N 个独立表空间。以 RANGE 分区为例，每个分区拥有自己的聚簇索引 B+ 树和二级索引 B+ 树。查询时优化器做分区裁剪（partition pruning），只访问满足条件的分区；写入时按分区函数路由到目标分区。这个模型带来两个直接后果：其一，跨分区查询的代价接近 UNION 多表；其二，分区键上的唯一性只能在单分区内保证，跨分区唯一性需要全局约束，而 InnoDB 不提供。

这也解释了为什么分区表的主键必须包含分区键：只有把分区键并入主键，才能保证"同一主键值必然落在同一分区"，从而把全局唯一性降维为分区内唯一性。本文评述：这是一次典型的"用约束换性能"的设计取舍，理解这一点，后面所有关于主键改造的讨论才不会显得突兀。

2.2 原生 ALTER 能做什么、不能做什么

MySQL 8.0 的 Online DDL 支持 ALGORITHM=INPLACE 的操作清单里，明确包含"分区管理"中的 ADD/DROP/COALESCE/REORGANIZE 等，但前提是表已经是分区表。对非分区表执行 ALTER TABLE t PARTITION BY RANGE(...)，MySQL 会走 ALGORITHM=COPY，即创建影子表、逐行拷贝、重建索引、原子换名。COPY 算法在拷贝期间对原表加共享锁（8.0 中为 MDL 读锁 + 允许并发读，但阻塞写），这正是"停业务"的来源。

上表整理自 MySQL 8.0 官方手册 Online DDL Operations 章节（Oracle, 2024）。需要提醒的是，"允许并发 DML"不等于"无代价"：INPLACE 操作依赖 online log 缓冲并发期间的增量，缓冲大小由 innodb_online_alter_log_max_size 控制，默认 128 MB，大表上极易打满并导致操作失败回滚。这一点在重建场景中同样适用。

2.3 停机窗口的构成公式

把停机窗口拆开看，它由四段组成：T_stop = T_fence + T_catchup + T_switch + T_verify。T_fence 是停写栅栏时间（让双写与主表状态对齐），T_catchup 是追平最后一段增量 binlog 的时间，T_switch 是 RENAME 换名时间（通常毫秒级，但受 MDL 等待影响），T_verify 是切换后校验时间（可异步化）。在线重建的全部工程努力，本质上都是在压缩 T_catchup，并让 T_fence 与 T_verify 趋近于零。

本文评述：很多方案文档只谈"用 gh-ost 就行"，却忽略了 T_catchup 与业务写入速率的关系。若业务峰值写入 5000 TPS，而追平程序单线程只能回放 3000 TPS，那么无论全量搬得多快，切换前都会无限逼近却永远追不平。因此追平吞吐必须大于业务峰值写入吞吐，这是方案可行性的第一道硬门槛，也是后文所有并行化设计的出发点。

三、主线方案：在线重建的四个阶段与停机窗口测算

3.1 阶段一：结构对齐与主键改造决策

第一步不是搬数据，而是确定目标表 DDL。这里最容易翻车的是主键。假设源表是 orders(id BIGINT PK, tenant_id INT, created_at DATETIME, ...)，想按 created_at 做 RANGE 月分区，则目标主键必须改为 (id, created_at) 或 (created_at, id)。前者保持 id 前缀，点查仍高效；后者让分区裁剪更自然，但点查需带 created_at。这是一个必须与业务方共同确认的接口契约变更。

-- 目标表（RANGE 月分区，主键含分区键）
CREATE TABLE orders_new (
  id          BIGINT UNSIGNED NOT NULL,
  tenant_id   INT UNSIGNED    NOT NULL,
  created_at  DATETIME        NOT NULL,
  amount      DECIMAL(18,2)   NOT NULL,
  status      TINYINT         NOT NULL DEFAULT 0,
  PRIMARY KEY (id, created_at),
  KEY idx_tenant_time (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE COLUMNS(created_at) (
  PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
  PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

注意 RANGE COLUMNS 与 RANGE 的区别：前者直接对列值比较，支持 DATETIME/字符串，避免了 TO_DAYS() 这类函数表达式带来的裁剪不确定性。官方文档建议在 8.0 上优先使用 COLUMNS 形式。此外必须预留 pmax 兜底分区，否则插入超出范围的数据会直接报错。

3.2 阶段二：存量搬迁与并发写入捕获

存量搬迁有两种主流做法：一是按主键区间分片做 INSERT ... SELECT，二是用工具（gh-ost / pt-osc）自动分块。手工方案更可控，适合需要精细调优的场景。核心是分块大小与并发度的平衡：块太小则事务开销占比高，块太大则单事务 undo 膨胀、主从延迟加剧。经验区间是每块 1000–5000 行，并发 4–8 个 worker，且每个 worker 走不同主键区间以避免锁竞争。

-- 分片搬迁示例（按 id 区间，worker 间不重叠）
INSERT INTO orders_new (id, tenant_id, created_at, amount, status)
SELECT id, tenant_id, created_at, amount, status
FROM orders
WHERE id >= ? AND id < ?
ORDER BY id;

搬迁期间必须同时捕获增量。常用手段有三种：触发器双写、binlog 解析（如 canal / Debezium / gh-ost 内置的 binlog 流）、以及应用层双写。触发器双写实现简单但有明显代价——每个 DML 多两次触发器执行，且触发器内不能做复杂逻辑，高并发下容易成为瓶颈；binlog 解析对主库零侵入，但需要处理事务边界与 DDL 变更；应用层双写最灵活但侵入业务代码，且难以覆盖所有写入路径（如批量导入、后台脚本）。

本文评述：三者不是互斥关系。笔者认为最稳的组合是"binlog 解析为主 + 触发器兜底校验"——binlog 负责主体追平，触发器只记录变更主键到一个轻量日志表，用于切换前做抽样比对。这样既避免触发器成为写路径瓶颈，又保留了一条独立的一致性证据链。纯依赖单一通道的方案，一旦通道静默丢事件，很难在切换前发现。

3.3 阶段三：追平与一致性校验

追平的本质是把"全量快照位点"到"当前 binlog 位点"之间的增量应用到目标表。若用 binlog 方案，需记录全量开始时的一致性位点（SHOW MASTER STATUS 或 GTID），全量结束后从该位点回放。回放程序必须幂等：INSERT 转 REPLACE 或 INSERT IGNORE，UPDATE 按主键覆盖，DELETE 按主键删除。幂等性在"全量与增量有重叠"时是正确性的最后防线。

一致性校验不能只靠"行数相等"。行数相等但内容错位的案例在工程中并不罕见，尤其是主键改造后。建议三层校验：第一层，分区级行数与校验和（CHECKSUM TABLE 或对关键列做 BIT_XOR(CRC32(...)) 聚合）；第二层，按时间窗口抽样逐行比对；第三层，切换后对最近 N 分钟的热数据做全量比对。校验本身要限速，避免把主库 IO 打满。

3.4 阶段四：原子切换与回滚预案

切换的核心是 RENAME TABLE 的原子性。MySQL 的 RENAME 支持一次语句内重命名多张表，且整个过程持有 MDL 排他锁，对外表现为瞬时完成。标准动作是：先停写（或让双写进入"只写新表"模式），确认追平位点一致，然后执行 RENAME TABLE orders TO orders_old, orders_new TO orders，最后恢复业务写入并观察。

-- 切换（原子）
RENAME TABLE orders TO orders_bak_20240601,
             orders_new TO orders;

-- 回滚（同样原子，前提是 orders_bak 未被写入）
RENAME TABLE orders TO orders_new,
             orders_bak_20240601 TO orders;

回滚预案的关键在于"旧表在新表上线后是否还可写"。若切换后业务已写新表，旧表数据即过期，回滚将丢失这段时间的写入。因此回滚窗口应严格限定在"切换后、业务确认前"的几分钟内，且切换后立即做一轮冒烟校验（关键查询、写入、事务）。本文评述：把回滚设计成"可逆的几分钟"而非"随时可退"，是避免团队产生虚假安全感的关键——真正可靠的方案不是能回滚，而是切换前的一致性证据足够充分，使回滚概率趋近于零。

四、工程落地：双写、追平、切换、回滚的可执行步骤

4.1 双写触发器的写法与坑

若选择触发器方案，务必注意三点：触发器必须用 AFTER 而非 BEFORE（BEFORE 阶段新值尚未落盘，且自增 id 不可见）；触发器内避免 SELECT 大表，否则会放大锁等待；触发器要能容忍目标表暂时不存在（用条件判断或先建空表）。另外，MySQL 触发器不支持动态 SQL，无法根据分区键路由，只能固定写目标表。

触发器的性能影响需要实测。按公开的基准测试经验（如 Percona 博客中关于 trigger overhead 的讨论），简单 AFTER INSERT 触发器在 5000 TPS 下可能带来 10%–30% 的写延迟上升，具体取决于行大小与索引数量。因此在高写入业务上，更推荐 binlog 方案。触发器更适合作为"变更审计"的补充，而非主通道。

4.2 binlog 追平程序的实现要点

自研 binlog 回放程序时，需要处理四类边界：其一，ROW 格式下 UPDATE 的前镜像与后镜像，必须按主键定位；其二，大事务（如批量 UPDATE 十万行）会一次性产生大量事件，回放时要分批提交，避免目标表长事务；其三，DDL 事件（如源表加列）会导致列序变化，回放程序需按列名而非列序映射；其四，GTID 与位点的选择，建议用 GTID 以便断点续传与主从切换后的位点对齐。

吞吐方面，单线程回放通常只能达到数千 TPS。若业务峰值更高，需要按主键哈希分片并行回放，但要注意同一主键的变更必须串行（否则乱序）。一个可行的设计是：解析层单线程保序，分发层按主键哈希到 N 个队列，应用层 N 个 worker 各自串行消费。这样既保序又并行。本文评述：这套"保序解析 + 哈希分发 + 分区串行"的模型，与 Kafka 分区消费的语义高度同构，工程上可直接借鉴成熟的消息队列设计，减少自研风险。

4.3 切换检查清单

切换后要重点观察分区裁剪是否生效。可用 EXPLAIN 查看 partitions 列，若查询条件未命中分区键而扫描全部分区，说明业务 SQL 需要补条件或加索引。这一步常被忽略，导致"分了区但没提速"的尴尬。延伸阅读可参考 MySQL 官方分区裁剪文档与 Percona 的相关实践文章（MySQL Partition Pruning、Percona Blog）。

五、替代路线对比：gh-ost、pt-osc、原生 INPLACE 与逻辑复制

gh-ost 与 pt-osc 是社区最常用的两类在线改表工具，但它们的定位是"改表结构"，不是"改分区"。gh-ost 通过模拟从库拉取 binlog 实现增量追平，对主库压力小，支持暂停与限流；pt-osc 用触发器捕获增量，实现简单但在高并发下有额外开销。两者都能完成"建新表 + 搬数据 + 追平 + 换名"，但分区表的主键约束需要人工在 DDL 里写对，工具不会替你决策。

本文评述：工具选型的真正分水岭不是"哪个更快"，而是"增量通道是否可观测"。gh-ost 提供进度、延迟、限流状态等指标，便于在切换前判断追平趋势；触发器方案的状态更多依赖自建监控。对于几亿行、切换窗口以分钟计的场景，可观测性比峰值吞吐更重要——因为你需要的是"确定能追平"，而不是"理论上追得平"。

还有一条常被忽视的路线：先在从库或独立实例上完成重建，再通过主从切换把流量导过去。这要求复制拓扑支持（如 MHA、Orchestrator 或云厂商的托管切换），且新表结构要能被复制链路正确应用。它的优势是把重建的 IO 压力从主库剥离，代价是拓扑复杂度上升。适合已有成熟切换体系的团队。

六、局限、失败模式与前沿预判

6.1 典型失败模式

第一类失败是"追不平"：业务峰值写入持续高于回放吞吐，延迟曲线单调上升。对策是并行化回放、临时降级非核心写入、或选择低峰窗口。第二类失败是"主键冲突"：主键改造后，原表存在重复 (id, created_at) 组合（理论上不应有，但历史脏数据可能存在），导致搬迁中断。对策是搬迁前做一次重复检测。第三类失败是"MDL 等待"：切换时 RENAME 被长事务阻塞，导致锁等待雪崩。对策是切换前检查 information_schema.innodb_trx，设置 lock_wait_timeout，并准备 kill 长事务的预案。

第四类失败最隐蔽：切换后应用仍按旧主键查询，导致全分区扫描。表现为 QPS 没降但 RT 飙升、IO 打满。对策是切换前做 SQL 审计，确认所有高频查询都带分区键或走覆盖索引。本文评述：这类问题无法靠数据库层解决，本质是"数据模型变更未同步到访问层"，属于组织协作问题而非技术问题，必须在项目启动时就拉通应用团队。

6.2 边界条件

分区并非万能。若查询模式以"按主键点查"为主，分区带来的收益有限，反而增加路由开销与运维复杂度；若业务需要跨分区唯一约束（如全局订单号唯一），分区表无法直接满足，需要额外的去重表或应用层保证。此外，分区表的 ALTER TABLE ... EXCHANGE PARTITION 虽可用于归档，但要求表结构完全一致，实践中限制不少。

从学术与产业趋势看，NewSQL 与分布式数据库（如 TiDB、OceanBase）把分区/分片作为一等公民，支持在线重分布（rebalance），部分系统已提供"在线改分区键"的能力。但这类能力依赖分布式架构的元数据管理与多副本调度，单机 MySQL 短期内不会具备。本文评述：对仍在使用单机 MySQL 的团队，务实的判断是——把"分区改造"当作一次性的架构升级项目来管理，而不是期待数据库版本升级自动解决。

七、结论与展望

回到最初的问题：几亿行非分区表要改成分区表，怎么停业务？答案是——不停业务，只停几秒到几分钟的写。路径是"结构对齐 → 存量分片搬迁 → 增量并行追平 → 原子 RENAME 切换 → 短窗口回滚预案"。全程的技术难点不在搬数据，而在追平吞吐与一致性证据。把这两点做扎实，停机窗口可以压缩到业务可接受的范围。

需要强调的是，分区改造的收益必须与成本一起评估。它带来的是"按时间范围快速归档、按分区并行维护、裁剪扫描"的能力，代价是主键模型变更、应用改造、运维复杂度上升。若业务查询并不以时间范围为主，改造的性价比可能不高。工程决策的价值，恰恰在于识别"不该做"的场景。

展望未来，随着 MySQL 8.4 LTS 与后续版本在 Online DDL、并行复制、instant DDL 上的持续演进，重建类操作的窗口有望进一步压缩；而分布式数据库的在线重分布能力，也为超大规模表的结构演进提供了另一条路径。对当下的团队而言，掌握本文这条"可观测、可回滚、可验证"的重建主线，仍是应对几亿行大表改造最可靠的工程方法。