数据库批量补齐实战：从SQL方案到性能优化与避坑指南

数据库优化提速做到第四期了。前几篇我们聊过索引、慢查询、执行计划这些偏“体检”的内容，现在终于要碰一个特别容易翻车的环节：数据批量补齐。

先说清楚，这篇里的“补齐”不是开发时初始化数据，也不是往新表里灌测试数据，而是对线上已有数据做定向回填、补漏、统一。比如角色表里副本进度字段是空的，比如联盟成员表缺了一批官职记录，比如活动配置表只有今天的数据没有明天的——这些情况没法靠改代码自动恢复，只能靠人工补。而人工补又最怕两件事：补漏了、补错了。这篇就把我在“仙盟创梦IDE”里做批量补齐的完整思路和踩坑记录整理出来，给正在跟存量数据搏斗的同学一个能直接抄作业的方案。

文章会按这个顺序展开：先说什么样的情况才需要批量补齐，再说动手前必须做的准备，接着拆三类最常用的补齐方案及其取舍，然后讲性能控制和规避风险的操作细节，最后是实际排查中遇到的典型问题。内容偏实操，SQL以 MySQL 方言为主，其他数据库大同小异。

1. 批量补齐的真实需求：先搞清楚自己在补什么

1.1 哪几类场景需要批量补齐

我接触过的批量补齐需求，基本可以归成三类。

第一类是缺失记录的补齐。最常见的是关联表缺数据。比如“仙盟创梦”项目里，玩家联盟表和联盟建筑表本来应该有一条初始记录，但早版本代码里没写这段逻辑，导致一大部分老联盟没有建筑数据。这类问题的特点是：主表数据是好的，从表缺行。补法就是按主表已有数据生成从表记录。

第二类是字段级回填。表结构没问题，记录也都在，就是某几个字段是空的或者存的是旧格式。比如版本更新后新增了“盟战积分”字段，存量记录的积分全为 0，需要用历史战斗日志回算。或者之前存储的渠道 ID 是数字代号，新版本要求统一改成字符串编码。这类需求最考细心，因为字段的取值规则通常跟业务强相关，写错一个条件，影响的就是几千行数据。

第三类是规则性重算。数据本身不缺失，但值不对，需要按新规则重新生成。比如排行榜分数计算公式调整了，需要把近三个月的排名数据全部重算一遍。

三类需求的数据量级、复杂程度和风险完全不同。缺失记录补齐通常可以用一条 INSERT ... SELECT 解决；字段回填要看清关联条件和取值逻辑；规则性重算则往往要写存储过程或者临时脚本，分批处理。

1.2 批量补齐和手动改数、常规导入的区别

有人会问，数据量不大，手动改行不行？我的标准是：超过 20 行就别手动，超过 200 行就必须走批处理。手动改数有几个致命问题：

一是不可追踪。谁改的、什么时候改的、基于什么规则改的，事后全部说不清。二是容易漏改。人眼在 Excel 里筛数据，看多了必然眼花。三是没有回滚能力。改错了只能靠备份还原，而备份通常还是昨天的。

批量补齐则要求“规则可描述、过程可重复、结果可校验”。哪怕只是几十行数据，也应该写成 SQL 或脚本一次性执行，而不是开着 IDE 一顿改。这跟“能用 SQL 解决就不用 Python 脚本”是一个道理：批量补齐的核心价值，是把一次人工操作变成一条可复用的数据处理规则。

1.3 为什么选择在 IDE 里完成而非单独写程序

提一下仙盟创梦 IDE。这个工具集成了数据源管理、SQL 编辑器、存储过程调试、定时任务、版本管理等功能，我这两年的数据运维工作基本都在里面完成。用它做批量补齐，最大的好处是减少了上下文切换：同一份数据既可以在对象浏览器里直接预览，也可以马上打开 SQL 标签页写脚本，还能把补数 SQL 提交到版本库留痕。尤其复杂补齐需要拆成多个步骤反复验证时，IDE 比“命令行 + 编辑器”的方案舒服得多。

但工具只是载体，真正的门槛在思路。下面这些准备步骤，在任何工具里都一样。

2. 动手前的准备：把“补数”当成一次上线变更对待

我见过太多补数事故，源头都是同一句话：“就一条 SQL 的事，直接跑就行。”批量补齐跟上线代码本质上没区别，该有的预案一个都不能少。

2.1 环境确认与连接检查

在 IDE 里打开数据库连接之前，先确认三件事：

1. 当前连的是哪个环境。测试库、预发库、生产库，连接配置长得几乎一样，稍不注意就连错。
2. 当前账号的权限范围。批量补齐通常需要 SELECT、INSERT、UPDATE、DELETE 权限，部分场景还要临时建表权限。如果权限不足，先找 DBA 申请，别用 root 顺手解决。
3. 当前库的字符集和时区。尤其是补齐字段涉及时间或中文内容时，字符集不一致会导致乱码，时区不一致会导致时间错位。

建议每次补数前，在 IDE 里先执行一条 SELECT 确认当前会话的基本信息：

SELECT DATABASE(), CURRENT_USER(), @@session.time_zone, @@session.character_set_client;

这一步花不了几秒钟，但能省掉后续排查的很多弯路。

2.2 数据快照与回滚预案

补数前必须做数据快照。使用CREATE TABLE AS SELECT的方式定时备份保存需要修改的表数据。

比如要补t_alliance_building表，先把这张表完整备份成t_alliance_building_bak_20250115：

CREATE TABLE t_alliance_building_bak_20250115 AS SELECT * FROM t_alliance_building; -- 如果要连带备份相关主表，一并处理 CREATE TABLE t_alliance_bak_20250115 AS SELECT * FROM t_alliance;

快速生成表快照。注意：CREATE TABLE AS SELECT只复制数据，不复制索引、自增属性、外键等结构信息。快照表的用途是万一补错可以快速回滚，所以不需要结构完整，能 SELECT 出来恢复数据就行。

回滚的逻辑应该是这样的：

-- 如果补完发现数据不对，先清掉被污染的记录 DELETE FROM t_alliance_building WHERE id IN (SELECT id FROM t_alliance_building_bak_20250115); -- 再把快照数据插回去 INSERT INTO t_alliance_building SELECT * FROM t_alliance_building_bak_20250115;

建议用TRUNCATE再INSERT的方式，效果相同但速度更快。不过要注意先停掉相关业务写入，不然回滚时会把期间产生的新数据一起误删。

2.3 先做影响范围评估再动笔

写补数 SQL 之前，第一件事不是写 UPDATE 或 INSERT，而是先把“会被影响的数据”全查出来。

以“给老联盟补建筑记录”为例，补数逻辑是：所有没有建筑记录的联盟，都要插入一条默认 1 级议事厅。先别急着 INSERT，而是反过来查“哪些联盟已经存在建筑记录，却还有一条刻意造出来的假数据”。

等等，这里应该反过来想：先确认“哪些联盟缺建筑记录”。缺记录的判断标准是“联盟表里有该联盟，但建筑表里没有”。先写一条 SELECT 验证这个集合：

SELECT a.id AS alliance_id, a.name AS alliance_name, a.level AS alliance_level FROM t_alliance a LEFT JOIN t_alliance_building b ON b.alliance_id = a.id AND b.deleted = 0 WHERE b.id IS NULL AND a.status = 1;

这条 SQL 查出来的行数，就是要补的数据条数。核实这个数跟业务预期相符，才允许继续。如果左连接查询出来几百万行而预估只有几千行，说明关联条件或过滤条件有问题，这时候千万不能直接拿它去 INSERT。

评估影响范围的最佳产出是一份“补数影响清单”，包含表名、关联条件、预计影响行数、SQL 语句、执行时间、操作人。这份清单在事后复盘时价值巨大，别省。

3. 三类典型补齐方案：实现细节与取舍

3.1 缺失记录补齐：INSERT ... SELECT 的双保险写法

缺失记录补齐是最常见也最好写的一类。核心是 SELECT 出缺失的那部分数据，再插入目标表。仍以上面的联盟建筑为例，完整写法是：

INSERT INTO t_alliance_building ( alliance_id, building_id, level, create_time, update_time, deleted ) SELECT a.id, 1, -- 默认建筑：议事厅 1, -- 默认等级 NOW(), NOW(), 0 FROM t_alliance a LEFT JOIN t_alliance_building b ON b.alliance_id = a.id AND b.deleted = 0 WHERE b.id IS NULL AND a.status = 1;

需要注意两个坑。

**坑一：LEFT JOIN 的关联条件必须包含业务过滤字段。**比如建筑表里有逻辑删除标记deleted，关联时如果漏掉b.deleted = 0，会出现“该联盟实际上有一条已删除建筑记录，却被判断为缺失”的情况，导致重复插入。关联条件越贴近实际业务，判断才越准。

**坑二：插入语句要显示指定字段列表。**不要写INSERT INTO t_alliance_building SELECT ...这种简写，因为目标表字段一旦有调整，顺序就容易错位。写明字段列表，哪怕脚本长一点，也比事后对数据安全得多。

如果数据量较大，比如单次插入超过十万行，建议把 INSERT ... SELECT 改成三步：先建临时表存待插入数据，再做一次校验，最后用INSERT INTO ... SELECT FROM temp执行。临时表给了我们一个“先验证再落库”的机会。

-- 第一步：建立临时表并写入待补数据 CREATE TABLE tmp_alliance_building_20250115 AS SELECT a.id AS alliance_id, 1 AS building_id, 1 AS level, NOW() AS create_time, NOW() AS update_time, 0 AS deleted FROM t_alliance a LEFT JOIN t_alliance_building b ON b.alliance_id = a.id AND b.deleted = 0 WHERE b.id IS NULL AND a.status = 1; -- 第二步：校验临时表数据，确认行数、确认无异常值 SELECT COUNT(*) FROM tmp_alliance_building_20250115; SELECT * FROM tmp_alliance_building_20250115 LIMIT 20; -- 第三步：正式落库 INSERT INTO t_alliance_building ( alliance_id, building_id, level, create_time, update_time, deleted ) SELECT alliance_id, building_id, level, create_time, update_time, deleted FROM tmp_alliance_building_20250115;

这个流程看着笨，但执行计划可控、回滚点清晰。临时表算是一种很实用的中间状态。

3.2 字段级回填：UPDATE JOIN 与 CASE 的条件控制

字段回填最容易踩坑的地方，不是写不出 SQL，而是“条件边界不正确”。

场景：联盟表t_alliance中score字段在历史版本中一直为空，需要通过联盟日志表t_alliance_log按盟主 ID 回填。先写 SELECT 把回填逻辑验证出来：

SELECT a.id AS alliance_id, COALESCE(SUM(l.score_change), 0) AS total_score FROM t_alliance a LEFT JOIN t_alliance_log l ON l.alliance_id = a.id AND l.status = 1 WHERE a.score IS NULL AND a.status = 1 GROUP BY a.id;

确认无误后再转为 UPDATE。MySQL 支持 UPDATE 多表关联的写法：

UPDATE t_alliance a LEFT JOIN ( SELECT alliance_id, COALESCE(SUM(score_change), 0) AS total_score FROM t_alliance_log WHERE status = 1 GROUP BY alliance_id ) l ON l.alliance_id = a.id SET a.score = COALESCE(l.total_score, 0) WHERE a.score IS NULL AND a.status = 1;

这里有个重要细节：GROUP BY子查询是需要的，否则一条联盟日志会产生多行结果，直接 JOIN 会导致 UPDATE 的匹配行数翻倍甚至更多，数据被随机赋值。

字段回填还有一种常见写法，适合“按固定规则重新映射”的场景，比如渠道 ID 从数字改成字符串：

UPDATE t_alliance SET channel_code = CASE channel_id WHEN 1 THEN 'ios_official' WHEN 2 THEN 'android_official' WHEN 3 THEN 'web_h5' ELSE CONCAT('unknown_', channel_id) END WHERE channel_code IS NULL OR channel_code = '';

CASE WHEN的写法把映射规则写进 SQL，可读性很好，后续审计也方便。但要注意两点：

1. CASE 里要写 ELSE。漏掉 ELSE，匹配不上的行会被更新成 NULL，那是另一种灾难。
2. WHERE 条件里要限定只更新需要更新的行。如果把全表扫一遍，虽然结果正确，但会产生大量无意义的 binlog，增加主从同步延迟。

3.3 复杂业务规则的补齐：临时表 + 存储过程分段处理

当补齐逻辑需要多张表的数据参与、甚至要做循环重算时，单条 SQL 已经不够了。这时候我习惯用临时表做“数据准备 + 分批执行”。

以重新计算近三个月联盟排行榜积分为例。重算逻辑是：对联盟日志做汇总、剔除无效日志、按规则加权、更新联盟表。完整的 SQL 用一条 UPDATE 嵌套子查询也能写，但可读性很差，一旦算错很难定位。改用临时表 + 存储过程后，整体流程清晰了许多：

-- 预处理：把基础数据汇总到临时表 DROP TABLE IF EXISTS tmp_rank_score_20250115; CREATE TABLE tmp_rank_score_20250115 AS SELECT alliance_id, SUM(score_change * weight) AS final_score FROM t_alliance_log WHERE status = 1 AND log_time >= DATE_SUB(NOW(), INTERVAL 3 MONTH) GROUP BY alliance_id; -- 写一个简单的存储过程，分批次更新，避免一次性锁太多行 DELIMITER $$ CREATE PROCEDURE proc_fill_rank_score() BEGIN DECLARE v_offset INT DEFAULT 0; DECLARE v_batch_size INT DEFAULT 2000; DECLARE v_total INT DEFAULT 0; DECLARE v_affected INT DEFAULT 0; -- 先算一下总数，方便看进度 SELECT COUNT(*) INTO v_total FROM tmp_rank_score_20250115; WHILE v_offset < v_total DO UPDATE t_alliance a JOIN tmp_rank_score_20250115 t ON t.alliance_id = a.id SET a.rank_score = t.final_score WHERE a.id IN ( SELECT id FROM t_alliance ORDER BY id LIMIT v_offset, v_batch_size ); SET v_affected = ROW_COUNT(); SET v_offset = v_offset + v_batch_size; -- 每批处理完稍作停顿，让出资源 DO SLEEP(0.05); END WHILE; END$$ DELIMITER ; CALL proc_fill_rank_score();

写存储过程的方案虽然比一条 SQL 慢，但有两个核心优势：一是分批提交能控制长事务和锁持有时间；二是可以在每个批次的间隙记录进度，中途出错也能知道断在哪个区间。

不过要提醒一句：存储过程里如果用了动态 SQL 拼接表名或条件，一定要把变量类型和取值范围约束好，防止注入和隐式转换问题。尤其是拼接字符串时，条件值必须强转成数字或经过白名单验证。

4. 批量补齐的性能控制：执行计划、锁和事务

4.1 百万行级补数，先看执行计划再动手

数据量超过百万行时，不能直接拿 SQL 就跑。先让 IDE 生成执行计划，重点看三个地方：

1. 关联字段是否走索引。INSERT ... SELECT 和 UPDATE 关联时，JOIN 字段没有索引，会触发全表扫描，数据量大时直接卡死。
2. 临时表是否用了文件排序。如果 SELECT 里带了ORDER BY或GROUP BY，执行计划出现 Using filesort 时要留意，数据量不大还好，量大就会明显变慢。
3. 影响行数是否超出预期。执行计划里会显示预计扫描行数，如果跟实际表行数差不多，说明条件可能没走索引。

以 MySQL 为例，可以在 UPDATE 前面加 EXPLAIN 不能在 UPDATE 直接 EXPLAIN，因此需要转化为 SELECT 查看：

EXPLAIN SELECT a.id FROM t_alliance a LEFT JOIN t_alliance_building b ON b.alliance_id = a.id AND b.deleted = 0 WHERE b.id IS NULL AND a.status = 1;

看到type = ref或type = eq_ref说明关联条件走索引了，可以接受。如果看到type = ALL，先别急着跑补数，去检查关联字段的索引是否存在。

4.2 分批提交：宁可慢一点，不要堵死业务

大批量数据更新时，最怕一个 UPDATE 把所有行锁住。MySQL 的行锁机制决定了更新 100 万行期间，相关行的读写都会被阻塞。对线上业务来说，这往往是不能接受的。

解决思路很简单：拆成小批次，每批几百到几千行，分批提交。上面存储过程示例中按主键区间分批就是一种做法。也可以在 SQL 层面用BETWEEN分区间跑：

-- 按主键范围分片，每片 5000 行 UPDATE t_alliance SET score = ... WHERE id BETWEEN 0 AND 5000; UPDATE t_alliance SET score = ... WHERE id BETWEEN 5001 AND 10000; -- 以此类推

分批的大小取决于表行宽和更新操作的复杂度，一般控制在 1000 到 5000 行之间。MyISAM 表是表级锁，分批的意义不大；InnoDB 表行级锁，分批效果很明显。

4.3 索引策略：补数前的索引检查与补数后的索引重建

补数操作本身可能会被索引拖累，也可能会破坏索引效率。

执行 INSERT ... SELECT 时，目标表的索引越多，插入越慢。每插一行，所有二级索引都要同步更新。如果目标表有七八个索引，插入百万行数据会非常痛苦。这时候可以考虑：

1. 在补数前先删除不必要的二级索引，补完后再重建。
2. 如果目标表数据量巨大，重建索引耗时较长，需要评估业务是否接受。

对于 UPDATE 操作，则反过来。WHERE 条件里的字段必须有索引，否则就是全表扫描。平时设计表时就该注意，补数场景下索引的作用会被放大。

提示：不要为了补数而把表上所有索引删光。删除前充分评估，补数完成后记得在低峰期重建，并验证索引状态是否正常（如SHOW INDEX FROM的结果中 Cardinality 是否有值）。

4.4 事务与提交间隔怎么设置

单个事务写过大会产生两个问题：一是 undo log 膨胀，二是主从复制延迟。小批量多次提交能降低这两个风险。

InnoDB 默认是自动提交，也就是每条 SQL 一个事务。用存储过程循环分批时，每批 UPDATE 完成后就提交一次。这里要注意，不要在存储过程里显式开启一个大事务包住所有批次，那跟不分批没有区别。

对于 INSERT ... SELECT 这种整体操作，建议也按主键区间拆开执行，每批一个事务，这样出错时只需要回滚当前批次，之前的批次已经落库。

5. 常见问题与排查技巧实录

5.1 补数后出现主键冲突或重复数据

原因几乎都是判断“是否缺失”的条件不对。排查思路：

1. 检查关联条件里有没有漏掉逻辑删除标记、状态过滤条件。
2. 检查目标表是否已有部分数据被补过一遍，补数脚本重复执行导致的。
3. 用两条 SQL 对比：一条查目标表现有数据，一条查补数脚本 SELECT 出来的数据，做差集看冲突范围。

比如联盟建筑表如果补过一版，第二次再补就会撞唯一键。解决办法是给补数语句加“只处理不存在记录”的条件：

INSERT INTO t_alliance_building (...) SELECT ... FROM t_alliance a LEFT JOIN t_alliance_building b ON b.alliance_id = a.id AND b.deleted = 0 WHERE b.id IS NULL AND a.status = 1 AND NOT EXISTS ( SELECT 1 FROM t_alliance_building b2 WHERE b2.alliance_id = a.id AND b2.building_id = 1 );

NOT EXISTS比 LEFT JOIN 更好理解，条件更清晰，尤其在多层判断时不容易出错。

5.2 补数把线上业务堵住了

场景：白天跑批量 UPDATE，结果游戏玩家反馈联盟数据保存失败、登录超时。一查，大量 UPDATE 正在执行，相关表的锁一直没释放。

经验教训：补数必须选业务低峰期，并且开启分批。线上库如果无法接受长时间锁等待，建议做好以下操作：

• 用SHOW PROCESSLIST查看当前正在执行的补数 SQL，确认执行时间；
• 必要时用KILL <thread_id>终止补数线程；
• 重新设计补数策略，比如只在凌晨 4 点到 6 点执行。

5.3 补数中途失败，能否安全重跑

要看补数 SQL 是不是“幂等”的。幂等的意思是：同一段 SQL 重复执行，结果一致。

上面用NOT EXISTS或LEFT JOIN ... WHERE b.id IS NULL的 INSERT 语句，天然是幂等的——已经存在的记录不会再插入。但 UPDATE 类型不一定幂等，比如每次执行都让 score = score + 10，跑两遍就加了两遍，补数脚本务必避免这种写法。

最稳妥的幂等做法是：先把补数逻辑集中在临时表/中间层，再做一次“中间表到目标表”的同步。同步时要么先删后插，要么只在值不一致时才更新：

UPDATE t_alliance a JOIN tmp_alliance_fix t ON t.id = a.id SET a.score = t.final_score WHERE a.score != t.final_score;

这样中途断了，重跑结果也是正确的。

5.4 补完数据后，业务查询反而变慢

大多是对 JOIN 字段或查询条件字段缺失索引，补齐数据后扫描行数急剧增加，被隐藏的问题暴露了。解决方法是：检查慢查询日志，找到补数后变慢的 SQL，用 EXPLAIN 看执行计划，针对性补建索引。

另外还有一种情况：补数更新了大量行之后，表的统计信息没有及时更新，优化器选错了执行计划。执行ANALYZE TABLE更新统计信息即可：

ANALYZE TABLE t_alliance, t_alliance_building;

5.5 一张补数问题速查表

6. 最后分享几个我在实际项目中悟出来的细节

这些内容不一定能写进标准文档，但确实影响了补数成功率。

第一，养成“先 SELECT 后 UPDATE/INSERT”的肌肉记忆。补数脚本里，UPDATE 的前一步永远是同条件 SELECT。哪怕你觉得自己已经检查过三遍，也先跑一遍 SELECT 看行数和数据样例。我一个同事有一次把 UPDATE 的 WHERE 条件写错了，原文是WHERE alliance_id = 1001，漏了个等号相关条件，结果把某一大类联盟全部改了，教训深刻。

第二，补数脚本一定要留痕。在 IDE 里跑完补数 SQL 后，顺手把脚本保存为带日期和用途的文件。我会按这种格式归档：20250115_补联盟建筑初始记录.sql。一个项目跑两三年后，这些脚本就是最详细的数据变更历史，比任何文档都可靠。

第三，补完数不等于结束，要做对账。补数后马上写几条对账 SQL，比如“联盟总数和有建筑的联盟数是否匹配”“积分字段为空的行数是否归零”。对账通过才算闭环。对账 SQL 也一并保存，下次同类补数可以直接复用。

第四，所有操作前做好快照备份。这里再强调一次，快照不是为了好看，它是最后的退路。没做快照就敢跑批量 UPDATE 的，只能说明还没经历过失手。

数据库的批量补齐，本质上就是在“数据规则变更”和“存量数据现实”之间做一次强制同步。只要准备充分、方案清晰、留好退路，这项操作可以做得又快又稳。如果你正准备对线上数据做一次批量补齐，建议严格按照上文流程走一遍，尤其不要跳过影响评估和快照备份这两步。