如何在CockroachDB中基于派生数据迁移数百万行数据？

原模型与问题 #

原数据模型（简化版）：

table Workspace {
  id
}

table Posts {
  id
  workspaceId
  typeId      
}

table PostTypes {
  id
  typeName
  workspaceId
}

存在的问题：

• 设计时预期的Post类型扩展需求未出现，冗余了PostTypes表
• 无数据库级验证确保Posts、PostTypes、Workspace三者关联一致性
• 查询时需多表关联，影响性能

目标模型 #

enum PostType {
  BLOG,
  PERSONAL,
  ... etc
}

table Posts {
  id          String
  workspaceId String
  postType    PostType
}

高效迁移步骤（生产环境友好） #

1. 新增postType字段

在Posts表中添加允许为空的postType字段，类型匹配目标枚举（如MySQL用ENUM('BLOG','PERSONAL',...)，PostgreSQL用TEXT或自定义枚举类型）：

ALTER TABLE Posts ADD COLUMN postType ENUM('BLOG','PERSONAL') NULL;

注：多数现代数据库（如MySQL 8.0+、PostgreSQL）支持在线DDL，此操作不会长时间锁表。

2. 批量映射更新数据

用数据库原生SQL批量关联PostTypes表，一次性完成类型映射，替代ORM逐行遍历的低效方式：

UPDATE Posts p
JOIN PostTypes pt ON p.typeId = pt.id AND p.workspaceId = pt.workspaceId
SET p.postType = CASE pt.typeName
    WHEN 'BLOG' THEN 'BLOG'
    WHEN 'PERSONAL' THEN 'PERSONAL'
    -- 补充其他类型的映射规则
END;

加入p.workspaceId = pt.workspaceId确保数据关联的一致性，解决原模型的核心问题。

3. 分批执行更新（超大规模数据）

若数据量达千万级，一次性更新可能导致锁表时间过长，可分批次执行：

UPDATE Posts p
JOIN PostTypes pt ON p.typeId = pt.id AND p.workspaceId = pt.workspaceId
SET p.postType = CASE pt.typeName
    WHEN 'BLOG' THEN 'BLOG'
    WHEN 'PERSONAL' THEN 'PERSONAL'
END
WHERE p.postType IS NULL
LIMIT 10000;

循环执行此语句，直到所有postType字段均被填充。每次仅处理10000条数据，大幅降低对生产环境的影响。

4. 切换应用代码逻辑

修改应用代码，新创建的Post直接设置postType字段，不再依赖typeId和PostTypes表。确保代码部署后，所有新写入数据均使用新模型。

5. 验证数据一致性

执行查询验证迁移后数据的准确性：

SELECT COUNT(*)
FROM Posts p
LEFT JOIN PostTypes pt ON p.typeId = pt.id AND p.workspaceId = pt.workspaceId
WHERE p.postType != CASE pt.typeName
    WHEN 'BLOG' THEN 'BLOG'
    WHEN 'PERSONAL' THEN 'PERSONAL'
END;

若结果为0，说明所有数据映射正确。

6. 清理冗余结构

确认数据无误后，删除旧字段和冗余表：

-- 删除Posts表的typeId字段
ALTER TABLE Posts DROP COLUMN typeId;
-- 删除PostTypes表
DROP TABLE PostTypes;

生产环境注意事项 #

• 选择业务低峰期执行迁移操作
• 先在测试环境完整验证迁移流程和SQL语句
• 执行迁移前全量备份数据库
• 实时监控数据库CPU、锁等待时间等指标，出现异常及时暂停迁移

内容的提问来源于stack exchange，提问作者Scalahansolo