开发者日记

外观

64 张 MySQL 分表合并 PG 分区表：ID 重建实战 ​

问题背景：64 张 MySQL 分表为什么要合并 ​

业务早期使用 MySQL 按用户 ID 哈希拆成 64 张订单分表，典型命名为 order_00 到 order_63。随着查询维度增多，跨分片聚合、分页、事务和运维变更越来越困难：一次商家维度统计往往要扫描全部表；全局唯一 ID 依赖应用层雪花算法；扩容时数据搬迁成本高。迁移到 PostgreSQL 后，更合理的目标不是继续复制 64 张物理表，而是使用声明式分区表：逻辑上一张 orders，底层按月或按哈希分区，由优化器做分区裁剪。

这次迁移的难点不在“把数据导进去”，而在两件事：

  1. 数据重排：MySQL 分片键与 PostgreSQL 分区键可能不同，需要重新分布数据。
  2. ID 重建：原自增 ID 在各分表内独立，直接合并会冲突；需要保留旧 ID，同时建立新的全局 BIGINT 标识。

方案对比：离线拷贝、双写切换与逻辑回放 ​

常见迁移方案有三种。

方案适用场景优点风险
停机全量导入数据量小、可接受维护窗口实现简单、一致性最好停机时间长
全量加增量双写7×24 业务、表规模较大停机窗口短、可灰度双写一致性复杂
CDC 逻辑回放存量大、持续写入自动化程度高类型映射与回放顺序要求高

对 64 表合并，推荐“全量文件加载 + 短时间双写切换”：先通过 CSV 或 Parquet 将历史数据导入 PostgreSQL 分区表；切换前应用同时写 MySQL 与 PostgreSQL，以旧业务 ID 做幂等；追平增量后冻结 MySQL 写入，完成校验并切读。

MySQL 64 张分表order_00 ~ order_63CSV 批量落地类型清洗与重排PG 分区父表orders 逻辑统一全局序列重建 ID旧 ID 保留为 legacy_id

PostgreSQL 分区表设计：先统一逻辑模型 ​

不要把 MySQL 的 AUTO_INCREMENT、反引号、tinyint 直接照搬。PostgreSQL 推荐使用 BIGINT GENERATED ... AS IDENTITY 或序列，并用声明式分区。下面示例按 created_at 月度范围分区，适合大多数按时间归档和查询的订单系统；如果主要按用户路由，也可以使用 LIST 或 HASH 分区。

sql
-- 目标分区父表：逻辑上只有一张订单表
CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    legacy_id   BIGINT NOT NULL,
    shard_no    INT NOT NULL,
    user_id     BIGINT NOT NULL,
    amount      NUMERIC(12,2) NOT NULL,
    status      SMALLINT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT pk_orders PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);

-- 建立月度分区，实际可用存储过程批量生成
CREATE TABLE orders_2024_01
PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02
PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- 常用查询索引会自动继承到分区
CREATE INDEX idx_orders_user_time ON orders (user_id, created_at DESC);
CREATE UNIQUE INDEX uk_orders_legacy ON orders (shard_no, legacy_id, created_at);

注意 PostgreSQL 分区表的唯一约束必须包含分区键。因此主键不能只写 id，示例使用 (created_at, id)；旧分片 ID 的幂等键使用 (shard_no, legacy_id, created_at)。

数据重排：用暂存表承接 64 张 MySQL 表 ​

推荐先用结构宽松的暂存表接收导出文件，完成清洗后再插入正式分区表。相比直接写入目标表，暂存表可以反复修正时区、字符集和数值精度，也便于按批次提交。

sql
-- 所有 MySQL 分表导出到同一张落地表
CREATE TABLE stage_orders (
    shard_no     INT NOT NULL,
    legacy_id    BIGINT NOT NULL,
    user_id      BIGINT NOT NULL,
    amount       TEXT NOT NULL,
    status       INT NOT NULL,
    created_at   TEXT NOT NULL,
    updated_at   TEXT NOT NULL,
    loaded_batch BIGINT NOT NULL
);

-- 假设通过 COPY 导入 CSV：
-- \COPY stage_orders(shard_no, legacy_id, user_id, amount, status, created_at, updated_at, loaded_batch)
-- FROM '/data/orders_all.csv' WITH (FORMAT csv, HEADER on);

-- 清洗并写入分区表；id 由身份列自动生成
INSERT INTO orders
    (legacy_id, shard_no, user_id, amount, status, created_at, updated_at)
SELECT
    legacy_id,
    shard_no,
    user_id,
    amount::numeric(12,2),
    status::smallint,
    to_timestamp(created_at, 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Shanghai',
    to_timestamp(updated_at, 'YYYY-MM-DD HH24:MI:SS') AT TIME ZONE 'Asia/Shanghai'
FROM stage_orders
WHERE loaded_batch = 1
ORDER BY created_at
ON CONFLICT (shard_no, legacy_id, created_at) DO NOTHING;

如果文件极大，不建议单条 INSERT 跑到底。可以按 created_at 或 loaded_batch 切批，每批 10 万到 100 万行，并在导入前暂时删除非必要二级索引，导入后并行重建。

ID 重建：保留 legacy_id，重置全局序列 ​

历史数据插入时由 identity 列生成新 id，但序列值不会自动追平手动导入的值。必须显式校准序列，否则切换后新订单可能撞上已导入 ID，报 duplicate key value violates unique constraint。

sql
-- 查看身份列使用的内部序列
SELECT pg_get_serial_sequence('public.orders', 'id');

-- 将序列推进到当前最大 ID 之后
SELECT setval(
    pg_get_serial_sequence('public.orders', 'id'),
    COALESCE((SELECT max(id) FROM orders), 1),
    true
);

-- 验证下一个值，必须大于 max(id)
SELECT nextval(pg_get_serial_sequence('public.orders', 'id'));

如果不希望由数据库自动生成 ID，也可以使用统一序列表达式，但要保证全量和双写阶段共用同一序列：

sql
CREATE SEQUENCE global_order_id START WITH 1;

-- 仅在明确放弃 identity 列时使用这种方式
UPDATE stage_orders s
SET legacy_id = s.legacy_id; -- 旧 ID 不覆盖，只用于说明保留字段

实践中更推荐 identity 列：权限、默认值、元数据更清晰，应用无需显式传递 ID。旧系统 URL、消息队列或外部回调中仍可能出现旧 ID，因此 legacy_id + shard_no 至少保留一个兼容周期。

校验与双写切换：用数量、金额和指纹三层比对 ​

切换前不能只比对总行数。建议做三层校验：行数、关键金额汇总、抽样或全量指纹。

sql
-- 1. 按分片比对行数
SELECT shard_no, count(*) AS pg_count
FROM orders
GROUP BY shard_no
ORDER BY shard_no;

-- 2. 按分片比对金额与最大旧 ID
SELECT
    shard_no,
    count(*) AS cnt,
    sum(amount) AS total_amount,
    max(legacy_id) AS max_legacy_id,
    min(created_at) AS min_created_at,
    max(created_at) AS max_created_at
FROM orders
GROUP BY shard_no
ORDER BY shard_no;

-- 3. 使用哈希指纹发现内容差异
SELECT
    shard_no,
    md5(string_agg(
        legacy_id || ':' || user_id || ':' || amount || ':' || status
        ORDER BY legacy_id, '|'
    )) AS fingerprint
FROM orders
GROUP BY shard_no
ORDER BY shard_no;

双写阶段所有增量写入都应带旧分片号和旧 ID，并以唯一键实现幂等。失败消息写入重试队列，不能只打日志。切读流程建议为：灰度读 PostgreSQL → 对比 MySQL 返回结果 → 停止 MySQL 写入 → 最后一次增量校验 → 释放读流量 → 保留旧库只读回滚通道。

避坑总结：迁移中最容易踩的 5 个坑 ​

  1. 主键少了分区键导致建表失败：现象是 unique constraint must include all partitioning columns。PostgreSQL 无法只靠分区内唯一索引保证全局唯一性，修正方法是将主键改为 (created_at, id)，业务幂等键也加入分区键。

  2. 序列未校准，切换后主键冲突：历史数据通过批量插入获得了较大 ID，但序列仍从 1 开始，应用新建订单时报重复键。导入完成后必须执行 setval，并在上线检查单中验证 nextval 大于 max(id)。

  3. 直接覆盖旧 ID，造成外部链接失效：把新 id 写回旧编号字段后，回调通知、收藏链接和对账文件全部无法追溯。正确做法是新 ID 作为主键，旧编号保存在 legacy_id，源分片保存在 shard_no。

  4. 时区和字符集处理不一致：MySQL 导出的 datetime 没有时区信息，按会话时区解释容易偏移 8 小时；中文备注出现乱码多为导出连接字符集不是 UTF-8。应在暂存层统一转换为 TIMESTAMPTZ，并固定导出文件编码。

  5. 一次性导入后才建全部索引，却忽略双写延迟：全量导入可以删索引提速，但双写开始前必须恢复唯一约束和查询索引，否则幂等冲突检测失效、读流量灰度时慢查询打爆数据库。上线前还应使用 EXPLAIN 验证 user_id + created_at 等语句是否发生分区裁剪。

64 张 MySQL 分表合并到 PostgreSQL 分区表，本质是用数据库原生分区替代应用层分片路由。只要把旧标识保留、全局序列校准、数据按目标分区键重排，并通过可重复执行的校验脚本闭环，迁移风险就可以从“不可回退的大爆炸”变成可灰度、可核对、可回滚的标准工程。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。