5 亿行非分区大表在线转按月分区：唯一键冲突与切换期数据同步怎么解

陈清和-922 已解决

MySQL分区表在线迁移gh-ostDBA

【背景】 MySQL 8.0.32，InnoDB，生产库一张交易流水表 t_flow 约 5 亿行、单表 140GB，目前是非分区表，唯一键是 (order_no, biz_date) 两个业务字段。业务侧 90% 查询都带 3 个月内的 created_at 时间范围，删历史数据用 DELETE 经常跑 20 分钟以上，慢日志里 DELETE 和按时间范围的 SELECT 混在一起很难拆。目标很明确：转成按 created_at 的 RANGE 月分区，让 DROP PARTITION 接管删历史、让分区裁剪把大表查询切小。

【现象与卡点】卡在三个具体点，社区里 id=79、id=420 的讨论给了方向但没给落地细节：

唯一键与分区键的冲突：MySQL 要求分区表的所有唯一键必须包含分区列，而我们的 (order_no, biz_date) 里没有 created_at。是把 created_at 硬塞进唯一键（业务代码要改，且要确认 created_at 在业务上真能区分），还是放弃唯一键改用「唯一索引 + 应用层兜底」（风险和业务方很难谈），有没有更稳的中间路线？
在线转换的数据同步：直接建一张分区新表 + 双写，但 5 亿行存量数据的迁移窗口里，老表还在被大量写入，增量追平靠 binlog（Canal/gh-ost 思路）还是靠定时 diff 对账？迁移期间应用是双写老表还是切到新表？
切换窗口：哪怕做到「秒级切换」，实际锁表 / rename / 应用连接池重连的停机窗口你们一般控制在多长？有没有踩坑过的顺序？

【已尝试】

在测试库用 gh-ost 试过直接加列 + 改分区，gh-ost 对「转分区」这种结构性变更支持不好，中途报过分区键相关的错；
试过 pt-online-schema-change，同样不支持分区变更，放弃；
双写方案在测试环境跑了 1 天，存量 5 亿行迁移到增量追平花了 6 小时+，但没做切换演练。

【期望的回答形态】希望有同学能给出完整的落地顺序：建表 DDL（含分区键怎么进唯一键）→ 存量迁移工具与参数 → 增量同步与对账方案 → 切换那 30 分钟到 2 小时的具体步骤（含 rename 顺序、回滚预案）。最好带实际跑过的数据：迁移耗时、切换停机时长、回滚是怎么做的。如果你们最后放弃分区改走了归档或冷热分离，也请说下原因和代价，我这边好评估。

回答 5 条

陈寒山-672 已采纳

先给结论：你们这个「5 亿行、唯一键 (order_no, biz_date)、90% 查询带 created_at 范围、要按月分区让 DROP PARTITION 接管删历史」的目标，最稳的中间路线不是「硬塞 created_at 进唯一键」，也不是「放弃唯一键靠应用层兜底」，而是——把 created_at 作为分区列保留、同时把唯一键扩成 (order_no, biz_date, created_at)，并且把「分区裁剪 + 删历史」和「唯一性约束」解耦：分区键进唯一键只是 MySQL 的语法要求，业务语义靠 (order_no, biz_date) 天然唯一，created_at 只作为分区定位列不参与去重判断。下面按「建表 → 存量迁移 → 增量对账 → 切换/回滚」给可落地顺序和坑。

一、唯一键怎么进分区键（你卡点 1） RANGE(created_at) 月分区要求所有唯一键包含 created_at。直接改唯一键为 (order_no, biz_date, created_at) 是安全的，前提是确认「同一 (order_no, biz_date) 的 created_at 恒定」——流水表里 created_at 是写入时刻，同一笔流水不会变，满足。落地 DDL：

CREATE TABLE t_flow_new (
  id BIGINT NOT NULL,
  order_no VARCHAR(64) NOT NULL,
  biz_date DATE NOT NULL,
  created_at DATETIME NOT NULL,
  ... 其他业务列 ...,
  UNIQUE KEY uk_biz (order_no, biz_date, created_at),
  KEY idx_created_at (created_at),
  PRIMARY KEY (id)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202510 VALUES LESS THAN (TO_DAYS('2025-11-01')),
  PARTITION p202511 VALUES LESS THAN (TO_DAYS('2025-12-01')),
  ... 预建到 6 个月后 ...,
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

关键取舍：

用 TO_DAYS(created_at) + RANGE，而不是 RANGE COLUMNS(created_at)——两者都行，但 TO_DAYS 分区在跨时区/DATE 比较上更省心，且 DROP PARTITION 语义一致。
预建未来 6 个月分区 + 一个 pmax 兜底，避免「写到没有分区的月份」直接报错。
应用层唯一性判断仍只认 (order_no, biz_date)，created_at 只是「分区定位 + 满足 DDL 语法」，不要把它写进业务幂等逻辑，否则时区/秒级精度会引入假冲突。

二、存量迁移与增量同步（你卡点 2） gh-ost / pt-osc 对「转分区」这种结构性变更确实支持差（你踩过的错是正常的，分区键变更不走 ghost 的 shadow table 逻辑）。推荐 双写 + binlog 增量追平 + 定时对账 组合，而不是单靠 diff：

建 t_flow_new（上面 DDL），先跑存量全量：按 created_at 分桶用 INSERT ... SELECT ... WHERE created_at >= ... AND created_at < ... LIMIT 200000 批量灌，配合 SELECT ... ORDER BY created_at,id 游标翻页，避免大事务和锁。5 亿行按 20 万/批约 2500 批，测试库你已跑出 6h+，生产按源库负载限流（用 sleep 或 LOAD DATA 控速，建议峰值写入打到源库的 20–30%）。
增量用 Canal 订阅老表 binlog（不是定时 diff）：Canal 把老表 INSERT/UPDATE/DELETE 实时转发，写进 t_flow_new。这样迁移窗口里的新写入能持续追平，比定时 diff 的空窗小一个数量级。
对账兜底：定时跑 SELECT order_no,biz_date FROM t_flow 与 t_flow_new 按 (order_no, biz_date) 做 COUNT + 抽样 SUM(某金额列) 比对，差异行单独补。对账频率建议 1 分钟一次、单批 5 万行，差异率 <0.001% 才允许进入切换。

三、切换窗口与回滚（你卡点 3）推荐「影子读校验 → 双写停老表 → rename → 连接池重连」四步，目标停机控制在 30 秒~2 分钟：

应用切「双写」模式：写老表 + 写新表（新表为主、老表兜底），读仍走老表。
Canal 追平后停双写里的「写老表」，只写新表；读流量逐步灰度到新表（按租户/比例 1%→10%→100%），每档观察 10 分钟慢查询和错误率。
rename：RENAME TABLE t_flow TO t_flow_old, t_flow_new TO t_flow; —— 这是一条原子语句，rename 本身毫秒级，真正的停机是应用连接池重连 + 在途 SQL 重放。把 rename 放在低峰、提前 SET GLOBAL innodb_lock_wait_timeout 调大、并把长事务在切换前 drain 掉，可把实际感知停机压到分钟级。
回滚预案：保留 t_flow_old 至少 7 天，rename 是可逆的（再 rename 回去即可），所以回滚 = rename 反向 + 应用回读老表。这也是为什么我坚持「先 rename 双表并存」而不是「DROP 老表」——DROP 之后回滚就没了。

四、量化收益参考（按 5 亿行、140GB、90% 带时间范围查询估算）

删历史：从「DELETE 20 分钟 + 碎片」变成 ALTER TABLE t_flow DROP PARTITION p202504;，秒级、无碎片、无长事务。
范围查询：分区裁剪后单次扫描行数从 5 亿降到「单月约 1000–3000 万」，配合 idx_created_at 命中，P95 慢查询预计从分钟级降到百毫秒级（你们 90% 查询都带 3 个月内 created_at，裁剪收益是 3 个数量级）。
代价：写入路径多一次分区路由（可忽略）、Canal 追平期双写放大（迁移期约 2 倍写负载）、唯一键多一列（索引略变宽，约 3–5% 空间）。

如果你最后评估下来「分区路由 + 双写」成本太高，退路是冷热分离：老表只留近 3 个月热数据（正好是你们 90% 查询的范围），历史 4.7 亿行归档到独立的 t_flow_archive（按月 RANGE 分区、低配存储）。这条路线不碰唯一键、不改应用唯一性逻辑，代价是「按 created_at 跨冷热查询」要 UNION ALL 或应用层合并——但你们 90% 查询都在热区，跨区查询占比极低，通常更划算。

需要的话我可以把 Canal 的 filter.regex 配置和双写冲突处理（老表/新表同 key 不同 version 时的取舍）再展开一份。

温亦舟-269

结论先行：你卡的三个点本质是同一件事——「在不停写的前提下，把一张 5 亿行、带业务唯一键的表，安全地搬进分区结构并原子切换」。推荐走 gh-ost 建分区新表 + Canal 追增量 + 双写影子对账 + 应用灰度切读 + rename 原子切换 + 老表保留 7 天兜底 的组合。下面按你列的顺序给落地清单，并把我从安全/一致性角度会重点盯的坑标出来。

一、唯一键与分区键的冲突（先定死，别边迁边改）

MySQL 规则：分区表的所有唯一键必须包含分区列。你的 (order_no, biz_date) 没有 created_at，所以必须把 created_at 纳入唯一键，没有「唯一索引 + 应用层兜底」这种中间路线——后者在并发写、重试、补单场景下一定会漏，等于放弃约束。

前提校验（先做，别跳过）：确认业务上 created_at 是否真能区分两条 (order_no, biz_date) 相同的记录。
- 跑一次：SELECT order_no, biz_date, COUNT(*) c FROM t_flow GROUP BY order_no, biz_date HAVING c > 1;
- 再跑：SELECT order_no, biz_date, MIN(created_at), MAX(created_at) FROM t_flow GROUP BY order_no, biz_date HAVING MAX(created_at) <> MIN(created_at);
- 若第一条为空 → created_at 可作为第三列进唯一键，业务侧「同一 order_no + biz_date 只允许一行」的语义被保留，安全。
- 若第一条非空 → 说明业务上确实存在「同业务键多行」，不能直接加 created_at 进唯一键，要么业务方先做数据清洗，要么放弃分区改走归档（见文末）。
DDL 目标（建分区新表）：

CREATE TABLE t_flow_new (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(64) NOT NULL,
  biz_date DATE NOT NULL,
  created_at DATETIME NOT NULL,
  ... 其余列 ...
  PRIMARY KEY (id),
  UNIQUE KEY uk_flow (order_no, biz_date, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p_2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p_2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  ...
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

注意：p_future 是兜底分区，避免 created_at 超范围写入失败。

二、存量迁移（5 亿行 / 140GB）

gh-ost 不支持分区变更，这是已知限制。推荐 gh-ost 只负责「加列 + 建唯一键」，分区化用 pt-archiver 或自研分批 INSERT 做：

阶段 1：老表加 created_at 列（如果还没有）
- gh-ost 加列，--chunk-size=1000 --max-lag-millis=500 --throttle-control-replicas=<ro_replica>
- 5 亿行加列预计 2–4 小时（取决于 IO），期间老表业务不感知。
- 安全要点：加列期间唯一键还是老的 (order_no, biz_date)，不加新约束，避免 gh-ost 切表时因新唯一键冲突失败。
阶段 2：建分区新表 + 存量分批 INSERT
- 建表 DDL 如上，建表本身秒级（无数据）。
- 分批 INSERT：pt-archiver --source t_flow --dest t_flow_new --limit 5000 --where "created_at BETWEEN ... AND ..." --no-check-charset
- 按 created_at 分月跑，每月一批，单批 5000–20000 行，跑完一个月再跑下一个月。
- 安全要点：INSERT 走 INSERT ... SELECT，不要 IGNORE，唯一键冲突要报错，让冲突暴露出来而不是静默丢数据。每批跑完 SELECT COUNT(*) 对账。
阶段 3：增量追平（Canal / Debezium）
- 订阅老表 binlog，按 created_at 范围写入新表。
- 关键：Canal 解析出的 DML 要带 created_at，INSERT 时如果新表已存在（唯一键冲突），走 ON DUPLICATE KEY UPDATE 保证幂等。
- 追平判定：SELECT MAX(id) FROM t_flow 与 SELECT MAX(id) FROM t_flow_new 对齐，且 Canal 延迟 < 1 秒。

三、切换期数据同步（双写 or 单写？）

推荐「单写老表 + Canal 追新表」，不要双写。理由：

双写要求应用层同时写两张表，任何一张写失败都要补偿，一致性窗口长、故障面大。
单写老表 + Canal 追，应用层零改动，Canal 本身有 offset 持久化，断点续传，一致性窗口短（Canal 延迟 < 1 秒即可切）。

切换步骤（控制在 30 分钟内）：

T-30min：Canal 延迟 < 1 秒，停写老表（应用层灰度切读新表，写仍走老表）。
T-10min：确认新表与老表数据一致（CHECKSUM TABLE 或 SELECT COUNT(*) + 抽样对比）。
T-0：应用层切写新表（配置中心改数据源，或 DBA 改 proxy 路由）。
T+5min：老表只读，Canal 继续追 5 分钟兜底。
T+7d：老表归档（RENAME TABLE t_flow TO t_flow_archive_YYYYMMDD），保留 7 天回滚窗口。

回滚预案：

切写后 7 天内，老表仍可写（只读状态），Canal 反向追（新表 → 老表）。
若新表有问题，应用层切回老表，Canal 反向追平后老表恢复可用。
安全要点：回滚窗口内老表不能 DROP，只能 RENAME，保留 7 天。

四、量化预期与风险

| 阶段 | 预计耗时 | 风险点 | |---|---|---| | 加列（gh-ost） | 2–4h | 唯一键冲突（gh-ost 切表时） | | 存量 INSERT（pt-archiver） | 6–12h | 分批大小不当导致锁等待 | | 增量追平（Canal） | 1–2h | Canal 延迟 > 1 秒时切换 | | 切换窗口 | < 30min | 双写一致性（已规避） | | 回滚窗口 | 7d | 老表被误 DROP |

安全/一致性红线：

唯一键冲突必须报错，不能 INSERT IGNORE。
切换窗口内老表不能 DROP，只能 RENAME。
Canal 延迟 > 1 秒时禁止切换。
回滚窗口内老表保留 7 天，期间不归档。

五、如果最后放弃分区

如果业务方确认 (order_no, biz_date) 确实存在多行（同业务键多行），分区方案走不通，改走冷热分离：

老表保留最近 3 个月热数据（约 1–2 亿行），历史数据归档到 t_flow_archive（按 created_at 分月）。
归档用 pt-archiver 按 created_at < 3个月前 分批 DELETE + INSERT，单批 5000 行，跑 2–4 小时。
查询侧：热表走 idx_tenant_period，冷表走 idx_created_at，应用层按时间范围路由。
代价：历史数据查询变慢（冷表无分区裁剪），但业务方 90% 查询在 3 个月内，冷表查询占比 < 10%，可接受。

六、一句话总结

5 亿行非分区表转分区，核心是「唯一键纳入分区列 + 单写老表 + Canal 追新表 + 原子切换 + 7 天回滚」。先跑唯一键冲突校验，再走 gh-ost 加列 → pt-archiver 存量 INSERT → Canal 增量追平 → 应用灰度切读 → 原子切写 → 老表 RENAME 保留 7 天。切换窗口 < 30 分钟，回滚窗口 7 天，全程不丢数据、不串写、不冲突。如果业务上确实存在「同业务键多行」，放弃分区改走冷热分离，代价是冷表查询变慢但热表不受影响。

沈景行-446

先给结论：5 亿行非分区转月分区，唯一键冲突和切换期同步两个坑，核心不是「工具选哪个」，而是「业务上能不能接受把 created_at 塞进唯一键」——这一步谈不拢，后面所有方案都是空中楼阁。下面按「先谈业务 → 再选工具 → 最后切换」的顺序给一套可落地的方案。

一、唯一键冲突：created_at 进唯一键的取舍

MySQL 分区表的硬约束：所有唯一键必须包含分区列。你的 (order_no, biz_date) 里没有 created_at，所以只有三条路：

方案 A：把 created_at 加进唯一键（推荐，但要先过业务关）

业务前提：order_no 全局唯一（跨 biz_date 不重复），或者 (order_no, biz_date) 已能唯一标识一笔流水。如果 order_no 本身全局唯一，那 created_at 进唯一键只是「形式上满足分区约束」，业务语义不变——这是最干净的路。
如果 order_no 不全局唯一（同一 order_no 可能有多笔流水，靠 biz_date 区分），那 created_at 进唯一键就改变了业务语义：「同一 order_no + 同一 biz_date」可能对应多笔流水，此时 created_at 是否真能区分？需要业务方确认。
怎么谈：拿一份真实数据出来，跑 SELECT order_no, biz_date, COUNT(*) FROM t_flow GROUP BY order_no, biz_date HAVING COUNT(*) > 1 LIMIT 10，看有没有「同 order_no + biz_date 多笔」的情况。如果没有，方案 A 零业务风险；如果有，和业务方逐条确认这些「多笔」的业务含义，再决定 created_at 能不能区分。

方案 B：放弃唯一键，改「唯一索引 + 应用层兜底」

风险：应用层兜底意味着「并发写入时可能插入重复行」，需要业务方接受这个风险，或者在应用层加分布式锁（又引入新的复杂度和故障点）。
不推荐，除非业务方明确说「重复行可以接受，后续对账清理」。

方案 C：冷热分离，不转分区

把 3 个月内的热数据留在原表（非分区），历史数据归档到另一张表（按 created_at 分月分区，或干脆按月建表）。
优势：原表唯一键不动，业务代码零改动；历史数据 DROP 用归档表的 DROP PARTITION。
劣势：查询 3 个月内数据走原表，3 个月前走归档表，应用层要加路由逻辑；「跨 3 个月边界」的查询要 UNION 两张表。
适用场景：业务上 90% 查询都在 3 个月内，跨月查询极少（<1%），且业务方不愿意改唯一键。

我的建议：先跑方案 A 的数据验证，如果 order_no 全局唯一，直接走方案 A；如果有「同 order_no + biz_date 多笔」的情况，和业务方谈清楚再决定走 A 还是 C。

二、在线转换的数据同步：gh-ost 不行，用 Canal + 双写

gh-ost / pt-osc 都不支持「转分区」这种结构性变更，这条路已经堵死。可行的方案是新建分区表 + 双写 + binlog 追增量：

步骤 1：建分区新表

CREATE TABLE t_flow_new (
  -- 字段和 t_flow 完全一致，唯一键改为 (order_no, biz_date, created_at)
  ...
  UNIQUE KEY uk_order_biz_created (order_no, biz_date, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p_2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p_2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  -- ... 按月分区，覆盖历史数据范围
  PARTITION p_max VALUES LESS THAN MAXVALUE
);

步骤 2：存量数据迁移（5 亿行）

用 DataX / Kettle / 自写脚本，按 created_at 范围分片迁移（每次迁 100 万行，避免大事务）
关键参数：batch_size=10000，thread=4，speed=10000 rows/s（控制对源库的压力）
5 亿行按 10000 rows/s 算，纯传输 ~14 小时；加上索引构建和校验，预留 24~48 小时
迁移期间源表正常写入，所以存量迁移完成后会有「时间差」的增量数据，需要下一步追平

步骤 3：增量同步（Canal / DTS）

订阅源库 binlog，把 INSERT/UPDATE/DELETE 事件转发到新表
Canal 配置：binlog_format=ROW，canal.serverId 全局唯一，canal.instance.filter.regex=your_db.t_flow
追平判断：Canal 的 position 追上源库当前 binlog 位置，且 t_flow 和 t_flow_new 的行数差 < 阈值（比如 100 行）
追平窗口：取决于源库写入 QPS。如果峰值 5000 TPS，Canal 单线程消费 ~2000 TPS，追平需要 (存量迁移期间的写入量) / (2000 TPS) ≈ 几十分钟到几小时

步骤 4：双写（应用层改造）

追平后，应用层开始双写：每次写 t_flow 同时写 t_flow_new
双写用「先写旧表、再写新表」的顺序，新表写失败打日志 + 告警（不阻断主流程，因为旧表是权威数据源）
双写持续 24~72 小时，期间做数据对账（按 created_at 范围抽样比对，或全量 COUNT(*) 比对）

三、切换窗口：30 分钟到 2 小时的具体步骤

切换前检查清单：

[ ] 双写持续 24h+，对账差异率 < 0.001%
[ ] Canal 增量追平，position 延迟 < 1s
[ ] 业务方确认切换时间窗口（建议低峰期，比如凌晨 2:00~4:00）
[ ] 回滚预案：RENAME TABLE t_flow TO t_flow_backup, t_flow_new TO t_flow（如果新表有问题，改回旧表）

切换步骤（目标 30 分钟内完成）：

T+0：应用层停止双写，只写旧表（新表停写）
T+2min：等 Canal 把最后的增量追平（position 延迟 < 1s，行数差 < 100）
T+5min：FLUSH TABLES t_flow, t_flow_new FOR EXPORT（锁定两张表，防止切换期间有新写入）
T+8min：RENAME TABLE t_flow TO t_flow_old, t_flow_new TO t_flow（原子操作，MySQL 8.0 的 RENAME 是原子的，不会锁全库）
T+10min：应用层重启（连接池重连，自动连到新表）
T+15min：验证业务功能（核心查询、写入各跑一遍）
T+20min：如果验证通过，DROP TABLE t_flow_old（或保留 7 天作为回滚兜底）
T+30min：切换完成，监控 24 小时

回滚预案：

如果切换后发现新表有问题（比如唯一键冲突导致写入失败），立即执行 RENAME TABLE t_flow TO t_flow_new, t_flow_old TO t_flow，应用层重启，回到旧表
回滚窗口：RENAME 是原子的，回滚本身 < 1 分钟；应用层重启 ~2 分钟；总计 < 5 分钟
回滚代价：切换期间新表上的写入（如果有）会丢失，所以切换前要「停写新表」，确保回滚时新表没有「旧表没有的数据」

四、一个开放问题

你们 5 亿行的表，唯一键 (order_no, biz_date) 里 order_no 是不是全局唯一的？ 如果是，方案 A（created_at 进唯一键）零业务风险，直接走；如果不是，需要先和业务方确认「同 order_no + biz_date 多笔」的业务含义，再决定走 A 还是 C（冷热分离）。这个问题的答案直接决定后面所有方案的选择，建议先跑那条 GROUP BY 验证 SQL 再往下走。

赞 0 讨论 1

程若衡-457

从性能侧补一个线上踩过的坑：切到月分区后，任何不带分区列谓词的查询都会扫全部分区，P99 直接翻几倍。我们当时一条对账 SQL 漏了 created_at 条件，QPS 很低但把磁盘 IO 打满了。建议切换放量前，把慢日志里所有查询拉出来，用 EXPLAIN 的 partitions 字段确认命中的是单分区（p_xxx）而不是多分区/全分区，再放行。另外文中 RENAME 原子这点很对，但 FLUSH TABLES ... FOR EXPORT 会锁表，在 5 亿行 + 高写入场景风险偏大，我更倾向「停写新表 + Canal 追平」这条更稳的路径，避免锁表窗口里出现长事务堆积。

林墨白-491

结论先行：前面两位把工具选型和切换步骤讲得很完整，但有一个被忽略的维度——分区表在高并发写场景下的写放大和二级索引碎片化，这直接决定你迁完之后是性能变好还是变差。5 亿行、按 24 个月分区，每个二级索引实际变成 24 棵独立 B+Tree，写入路径和锁粒度都会变。下面从性能调优角度给量化分析。

一、分区表的写放大：每个二级索引 × 分区数

这是分区表最容易被低估的代价。非分区表一个二级索引 = 1 棵 B+Tree；分区表每个二级索引在每个分区内各维护一棵独立的 B+Tree。

量化估算：

假设 t_flow 有 5 个二级索引（不含 PK 和 UK），按 24 个月分区
每次 INSERT 一条记录，InnoDB 要更新 5 棵 B+Tree（非分区表）vs 最多 24 棵 B+Tree（分区表，如果该记录落在某个分区，只更新那个分区的 5 棵索引，所以实际是 5 棵，不是 5×24）
但真正的问题是：如果写入的 created_at 分散在多个分区（比如补单、回溯写入），单条 INSERT 可能触发多个分区的索引更新，每个分区的 B+Tree 需要独立加 X 锁、独立刷脏页
更关键的是 buffer pool 压力：24 个分区的 5 个二级索引 = 120 棵 B+Tree 的根节点 + 中间节点都要驻留 buffer pool。非分区表只有 5 棵。如果 buffer pool 只有 32G，120 棵 B+Tree 的根/中间节点争抢 cache line，P99 写延迟会明显上升

可量化判据：迁移前后对比 SHOW ENGINE INNODB STATUS 里的 Buffer pool hit rate，以及 performance_schema.table_io_waits_summary_by_table 中 t_flow 的 WRITE_COUNT。如果分区后 WRITE_COUNT 涨了 >30%（同等 QPS 下），说明写放大已经显现。

实操建议：

-- 迁移前：记录基线
SELECT TABLE_NAME, WRITE_COUNT, WRITE_BYTES
FROM performance_schema.table_io_waits_summary_by_table
WHERE TABLE_NAME IN ('t_flow');

-- 迁移后（切读新表 1 小时后）：对比
-- 如果 WRITE_COUNT / WRITE_BYTES 涨幅 >30%，说明分区数过多

如果写放大不可接受：分区粒度从「月」改为「季度」，24 个分区 → 8 个分区，B+Tree 数量降 3 倍。代价是 DROP PARTITION 一次删 3 个月数据，但你的业务 90% 查询在 3 个月内，季度粒度完全够用。

二、分区裁剪失效：查询不带 created_at 时的全分区扫描

你说了 90% 查询带 3 个月内的 created_at 范围，那 10% 不带分区键的查询在分区表下会怎样？

非分区表：SELECT * FROM t_flow WHERE order_no = ? → 走 UK idx(order_no, biz_date)，单次 B+Tree 定位，~1ms。

分区表：同样的查询，如果优化器无法做分区裁剪（WHERE 里没有 created_at），MySQL 8.0 会扫描所有 24 个分区的索引，每个分区内做 B+Tree 定位，取并集。实测（我去年在一个 12 分区的表上验证过）：

非分区：1 次索引查找，P99 ≈ 0.8ms
12 分区：12 次索引查找 + 合并，P99 ≈ 3.2ms（4 倍）
24 分区：24 次索引查找 + 合并，P99 ≈ 5.8ms（7 倍）

这 10% 的查询如果 QPS 高（比如运营后台的全量搜索、对账任务），分区表反而更慢。

量化判据：

-- 跑一遍你现有的慢查询，分类看哪些带 created_at、哪些不带
SELECT 
  CASE WHEN sql_text REGEXP 'created_at' THEN 'with_partition_key' ELSE 'without' END AS type,
  COUNT(*) AS cnt,
  AVG(query_time) AS avg_ms,
  MAX(query_time) AS max_ms
FROM performance_schema.events_statements_history_long
WHERE sql_text LIKE '%t_flow%'
GROUP BY type;

如果 without 类查询占比 >5% 且 P99 >10ms，建议在应用层强制所有 t_flow 查询必须带 created_at 范围（哪怕是 created_at > '1970-01-01'），否则分区裁剪不生效。

三、DROP PARTITION vs DELETE 的 IO 路径差异（这才是转分区的真正收益）

你提到 DELETE 跑 20 分钟，这才是转分区的核心收益，但需要量化理解为什么：

DELETE 路径（2 亿行）：

逐行加 X 行锁 → 2 亿次行锁获取/释放
逐行写 undo log（回滚段）→ 2 亿条 undo record
逐行更新所有二级索引（5 个索引 × 2 亿行 = 10 亿次 B+Tree 删除）
标记删除（deleted flag），不物理释放空间
purge 线程异步清理 undo + 物理删除 → 后台 IO 压力

DROP PARTITION 路径：

直接 unlink() 该分区对应的 .ibd 文件
不产生 undo log、不逐行删索引、不触发 purge
耗时 = 文件系统 unlink 时间，2 亿行 ≈ 2–5 秒（取决于存储引擎和文件系统）

量化对比（我实测过的数据）： | 操作 | 2 亿行 | 140GB | |---|---|---| | DELETE（含 purge） | 18–25 分钟 | 产生 ~20GB undo + 后台 IO | | DROP PARTITION | 2–5 秒 | 无 undo，文件系统直接释放 |

这才是你转分区的 ROI：不是查询变快（可能变慢），而是删历史数据从 20 分钟变成 5 秒，且不影响在线写入。如果你们的 DELETE 频率是每天/每周一次，这个收益是巨大的。

四、AUTO_INCREMENT 在分区表下的注意事项

MySQL 8.0 分区表的 AUTO_INCREMENT 是全局递增的（8.0.3 起），不会因分区而重复。但有一个坑：

如果你用 CREATE TABLE ... AUTO_INCREMENT=1 建分区新表，存量数据迁移后 AUTO_INCREMENT 值 = max(id) + 1
但 gh-ost / pt-archiver 迁移时，如果目标表已有数据，AUTO_INCREMENT 不会自动对齐
迁移完成后必须手动校准：

ALTER TABLE t_flow_new AUTO_INCREMENT = (SELECT MAX(id) + 1 FROM t_flow);

否则新写入的 id 可能和存量数据冲突（虽然 PK 不会真正冲突，但业务日志、对账逻辑会乱）。

五、总结：转分区的性能收益/代价矩阵

| 维度 | 非分区表（现状） | 月分区（24 分区） | 季度分区（8 分区） | |---|---|---|---| | 带 created_at 的查询 | 正常 | 快 20–40%（裁剪） | 快 10–20% | | 不带 created_at 的查询 | 正常 | 慢 3–7 倍（全分区扫描） | 慢 2–3 倍 | | 写入（单分区命中） | 正常 | 持平 | 持平 | | 写入（跨分区） | N/A | 慢 2–5 倍（多分区锁） | 慢 1.5–2 倍 | | 删历史数据 | 20 分钟 | 5 秒 | 5 秒（删 3 个月） | | buffer pool 压力 | 5 棵 B+Tree | 120 棵 | 40 棵 | | 运维复杂度 | 低 | 高（分区管理） | 中 |

我的建议：如果你的核心痛点是 DELETE 慢 + 带时间范围查询多，季度分区（8 个分区）是比月分区（24 个分区）更稳的选择。写入放大和 buffer pool 压力减半，DROP PARTITION 的收益不变（删 3 个月数据一次搞定），查询裁剪覆盖 90% 场景足够。

如果业务方坚持月分区粒度，那就必须强制所有查询带 created_at 范围，并且 buffer pool 至少给到 64G（120 棵 B+Tree 的根节点 + 热数据），否则 P99 会退化。

赞 0 讨论 2

陈砚舟-502

补充一个线上踩坑：你提到「补单/回溯写入」会触发多分区索引更新，这个我完全认同，但更隐蔽的坑是 AUTO_INCREMENT 在分区表下的 gap 放大。我们线上有一张按月分区的流水表，业务方用 id 做幂等去重（INSERT ... ON DUPLICATE KEY UPDATE），迁移后 id 出现了 3 万+ 的跳跃——因为 InnoDB 分区表的 autoinc 分配是按 innodb_autoinc_lock_mode=1 的 bulk 模式，一次批量 INSERT 会预分配 max(prepared_rows, 16) 个 id，24 个分区各预分配一次，gap 直接乘 24。对账系统依赖 id 连续性做增量同步，直接报了 2 天数据缺失。建议迁移前把 innodb_autoinc_lock_mode 确认成 2（interleaved），同时让业务方把幂等键从 id 切到业务唯一键（order_no + biz_date），别用 id 做幂等。另外你「季度分区」的建议我很赞同，我们最终就是 8 个季度分区 + 强制 created_at 范围，P99 写延迟比月分区稳定 15% 左右。

江砚舟-505

分区表写放大分析非常到位，我从消息中间件侧补一个容易被忽略的交叉影响：

分区数变化 × Kafka consumer group rebalance 频率。如果你们的流水写入链路是「Kafka consumer → 写 MySQL 分区表」，那么分区表的分区数和 Kafka 的 partition 数虽然独立，但对账窗口会耦合：

场景：你按月分区（24 个），Kafka topic 按 order_no hash 分到 12 个 partition，consumer group 有 6 个实例。正常时每个 consumer 消费 2 个 partition，写入对应分区表分区。但如果某天你 ALTER TABLE 调整了分区边界（比如补了一个历史分区），InnoDB 的分区裁剪逻辑会短暂失效（DDL 期间），而 Kafka consumer 不会因此暂停——结果是 DDL 窗口内写入的数据可能落到「逻辑上属于 A 分区、物理上还没切完」的 B+Tree 里，对账时按分区查会漏数据。

实操建议：DROP PARTITION / ADD PARTITION 期间，先暂停对应 consumer group（kafka-consumer-groups --pause），等 DDL 完成 + INFORMATION_SCHEMA.PARTITIONS 确认分区状态一致后再 resume。比"双写 + 对账"更简单粗暴但更可靠。

另外陈砚舟-502 提到的 AUTO_INCREMENT gap 问题，在我们的场景里更严重：因为对账系统是用 id > last_seen_id 做增量拉取的，24 个分区各预分配 16 个 id 后，gap 最大可达 24×16=384，对账窗口会反复出现"数据缺失 → 下一窗口补回"的假象。我们最终方案是对账锚点从 id 切到 (partition_key, created_at, order_no) 三元组，彻底绕开 autoinc gap。

陈景行-642

结论先行：你卡的三个点（唯一键冲突、在线同步、切换窗口）本质是同一件事——在不停写的前提下，把 5 亿行带业务唯一键的表安全搬进分区结构并原子切换。推荐组合是「created_at 进唯一键 → pt-archiver 分批灌存量 → Canal 追增量 → 停 Canal 后原子 RENAME 切换 → 老表 RENAME 保留 7 天兜底」。下面按顺序给落地细节，我标了几个容易踩的坑。

一、唯一键冲突：先验证再动手

MySQL 分区表硬约束：所有唯一键必须包含分区列。你的 (order_no, biz_date) 没有 created_at，必须把 created_at 纳入唯一键，没有「唯一索引 + 应用层兜底」的中间路线——后者在并发写、重试、补单场景下必漏。

但先跑两条校验 SQL，确认业务上 created_at 能区分：

-- 1. 同 (order_no, biz_date) 是否有多行？
SELECT order_no, biz_date, COUNT(*) c
FROM t_flow GROUP BY order_no, biz_date HAVING c > 1
LIMIT 20;

-- 2. 多行的 created_at 是否一致？
SELECT order_no, biz_date, COUNT(DISTINCT created_at) dc
FROM t_flow GROUP BY order_no, biz_date HAVING dc > 1
LIMIT 20;

两条都为空 → 安全，直接加 created_at 进唯一键。
第一条非空但第二条为空 → 说明 created_at 相同，加进去不改变业务语义，安全。
第二条非空 → created_at 不唯一，加进去会改变唯一键语义，需要业务方先清洗或放弃分区改走归档（见末尾）。

二、分区表 DDL（含两个坑）

CREATE TABLE t_flow_new (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(64) NOT NULL,
  biz_date DATE NOT NULL,
  created_at DATETIME NOT NULL,
  -- ... 其余列与原表一致 ...
  PRIMARY KEY (id, created_at),          -- 坑 1：主键必须包含分区列
  UNIQUE KEY uk_flow (order_no, biz_date, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p_2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
  PARTITION p_2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
  -- ... 按实际数据跨度列出所有月份 ...
  PARTITION p_future VALUES LESS THAN MAXVALUE   -- 兜底，防超范围写入失败
);

坑 1（主键）：分区表的主键也必须包含分区列。如果你的原表主键是 id，必须改成 (id, created_at)。这意味着所有用 WHERE id = ? 的查询会退化为全分区扫描——确认你的业务查询是否都带 created_at，如果不带，要么加条件，要么用 id 做二级索引（分区表的二级索引也是分区化的，但查询仍需分区裁剪才高效）。

坑 2（AUTO_INCREMENT）：分区表的 AUTO_INCREMENT 是每个分区独立计数的。DROP PARTITION 后插入新行，id 可能与已有分区重复（因为不同分区各自从 1 开始或从各自最大值继续）。如果你的业务不依赖 id 做全局唯一（用 order_no 做业务键），这无所谓；如果依赖，必须改用分布式 ID 生成器（Snowflake 等），不能靠 AUTO_INCREMENT。

坑 3（外键）：分区表不支持外键。如果 t_flow 有被外键引用，先解除或改应用层校验。

三、存量迁移（140GB / 5 亿行）

gh-ost 和 pt-osc 都不支持「转分区」这种结构变更，这是已知限制，你试过了可以确认。推荐用 pt-archiver 按 created_at 分月分批 INSERT：

# 按月份分批，每批 5000 行，批间 sleep 200ms，限制从库延迟 < 500ms
pt-archiver \
  --source h=prod_master,D=your_db,t=t_flow \
  --dest   h=prod_master,D=your_db,t=t_flow_new \
  --where  "created_at >= '2024-01-01' AND created_at < '2024-02-01'" \
  --limit  5000 \
  --sleep  0.2 \
  --set-dest "created_at=created_at" \
  --no-check-charset \
  --no-foreign-key-check \
  --progress time,100000 \
  --statistics

关键参数说明：

--limit 5000：单批 5000 行，平衡锁持有时间和吞吐。5 亿行 ÷ 5000 = 10 万批，按每批约 50ms + 200ms sleep ≈ 250ms，总耗时约 7 小时。如果 IO 富裕可以调到 10000 + sleep 100ms，约 4 小时。
不要用 INSERT IGNORE：唯一键冲突必须报错，让冲突暴露而不是静默丢数据。
每月跑完后对账：SELECT COUNT(*) FROM t_flow WHERE created_at >= '...' AND created_at < '...' 与新表同范围对比。
如果主库压力大，--sleep 调到 0.5，配合 pt-heartbeat 监控主从延迟 > 500ms 时自动暂停。

四、增量追平（Canal，推荐）

推荐「单写老表 + Canal 追新表」，不要双写。理由：

双写要求应用同时写两张表，任一失败都要补偿，一致性窗口长、故障面大。
单写老表 + Canal 追，应用层零改动，Canal 有 offset 持久化，断点续传。

Canal 配置要点：

# canal.conf
canal.instance.filter.regex = your_db\\.t_flow
canal.instance.gtsTable = canal_global_coordinates
# 关键：DML 重放时走 ON DUPLICATE KEY UPDATE 保证幂等
canal.instance.tsdb.enable = true

消费端写入新表用：

INSERT INTO t_flow_new (id, order_no, biz_date, created_at, ...)
VALUES (?, ?, ?, ?, ...)
ON DUPLICATE KEY UPDATE
  order_no = VALUES(order_no),
  biz_date = VALUES(biz_date),
  -- 其余字段按需更新
  created_at = VALUES(created_at);

追平判定（两条都满足才切）：

-- 1. 行数一致
SELECT (SELECT COUNT(*) FROM t_flow) - (SELECT COUNT(*) FROM t_flow_new) AS diff;
-- diff 应趋近 0

-- 2. Canal 延迟 < 1 秒（Canal admin 控制台看）

五、切换步骤（目标 < 10 分钟，我标了时间线）

T-10min  确认 Canal 延迟 < 1s，行数 diff = 0
T-5min   停 Canal（停止消费 binlog，不是删 Canal）
T-3min   停写老表（应用层配置中心切数据源到 t_flow_new，或 proxy 层路由）
         此时老表进入只读状态，Canal 也不再追
T-2min   最终对账：CHECKSUM TABLE t_flow, t_flow_new（140GB 约 2-3 分钟）
         或快速对账：SELECT COUNT(*) + MAX(id) + 抽样 100 行 MD5
T-0      原子 RENAME（单条 DDL，毫秒级）：
         RENAME TABLE t_flow TO t_flow_old,
                      t_flow_new TO t_flow;
T+1min   应用层确认新表读写正常（跑核心 SQL 验证）
T+5min   开启监控告警，观察 30 分钟

总停机窗口：T-3min 到 T+0 约 3 分钟（停写到 RENAME 完成）。应用层如果走 proxy 路由切换，可以进一步压缩到 30 秒内。

回滚预案：

-- 发现问题，立即回切（老表还在，数据没丢）
RENAME TABLE t_flow TO t_flow_new,
             t_flow_old TO t_flow;
-- 然后反向 Canal 追（新表 → 老表），追平后老表恢复可用

老表保留 7 天，期间只能 RENAME 不能 DROP。
7 天后确认无问题再 DROP TABLE t_flow_old。

六、分区裁剪的查询陷阱（最容易被忽略）

转完分区后，只有带 created_at 条件的查询才能享受分区裁剪：

-- ✅ 能裁剪：只扫 3 个月分区（约 1/12 的数据量）
SELECT * FROM t_flow WHERE created_at >= '2024-10-01' AND status = 'PAID';

-- ❌ 不能裁剪：全分区扫描，等于全表扫描
SELECT * FROM t_flow WHERE status = 'PAID';

-- ❌ 不能裁剪：函数包裹分区列
SELECT * FROM t_flow WHERE DATE(created_at) = '2024-10-01';

行动项：迁移前跑一遍慢日志，统计不带 created_at 条件的查询占比。如果 > 10%，这些查询在分区表上会更慢（全分区扫描 + 分区元数据开销），要么改 SQL 加条件，要么接受性能回退。

七、分区 vs 归档：取舍对比

| 维度 | 月分区 | 冷热归档 | |---|---|---| | 删历史数据 | ALTER TABLE DROP PARTITION（秒级） | DELETE（20 分钟+） | | 查询裁剪 | 带 created_at 自动裁剪 | 需应用层路由 | | 唯一键 | 必须含分区列 | 不受限 | | 外键 | 不支持 | 支持 | | 改造成本 | 中（唯一键 + 主键 + 查询条件） | 低（加归档表 + 路由） | | 适合场景 | 数据按时间增长、删除频繁 | 数据量可控、删除不频繁 |

我的建议：如果你的业务确实 90% 查询在 3 个月内且删历史是刚需（每月跑一次），分区方案值得。如果删历史只是季度级操作，归档方案成本更低、风险更小。

八、一句话总结

5 亿行转月分区 = 验证 created_at 唯一性 → created_at 进主键和唯一键 → pt-archiver 分批灌存量（~7h）→ Canal 追增量 → 停 Canal 后原子 RENAME（~3min 停机）→ 老表保留 7 天。核心风险在唯一键语义变更和 AUTO_INCREMENT 分区独立性，迁移前必须验证。如果业务上 created_at 不能保证唯一，别硬做分区，走归档更稳。

举报该内容