数据库优化提速做到第四期了。前几篇我们聊过索引、慢查询、执行计划这些偏“体检”的内容现在终于要碰一个特别容易翻车的环节数据批量补齐。先说清楚这篇里的“补齐”不是开发时初始化数据也不是往新表里灌测试数据而是对线上已有数据做定向回填、补漏、统一。比如角色表里副本进度字段是空的比如联盟成员表缺了一批官职记录比如活动配置表只有今天的数据没有明天的——这些情况没法靠改代码自动恢复只能靠人工补。而人工补又最怕两件事补漏了、补错了。这篇就把我在“仙盟创梦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 里打开数据库连接之前先确认三件事当前连的是哪个环境。测试库、预发库、生产库连接配置长得几乎一样稍不注意就连错。当前账号的权限范围。批量补齐通常需要 SELECT、INSERT、UPDATE、DELETE 权限部分场景还要临时建表权限。如果权限不足先找 DBA 申请别用 root 顺手解决。当前库的字符集和时区。尤其是补齐字段涉及时间或中文内容时字符集不一致会导致乱码时区不一致会导致时间错位。建议每次补数前在 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_20250115CREATE 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可读性很好后续审计也方便。但要注意两点CASE 里要写 ELSE。漏掉 ELSE匹配不上的行会被更新成 NULL那是另一种灾难。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 生成执行计划重点看三个地方关联字段是否走索引。INSERT ... SELECT 和 UPDATE 关联时JOIN 字段没有索引会触发全表扫描数据量大时直接卡死。临时表是否用了文件排序。如果 SELECT 里带了ORDER BY或GROUP BY执行计划出现 Using filesort 时要留意数据量不大还好量大就会明显变慢。影响行数是否超出预期。执行计划里会显示预计扫描行数如果跟实际表行数差不多说明条件可能没走索引。以 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 时目标表的索引越多插入越慢。每插一行所有二级索引都要同步更新。如果目标表有七八个索引插入百万行数据会非常痛苦。这时候可以考虑在补数前先删除不必要的二级索引补完后再重建。如果目标表数据量巨大重建索引耗时较长需要评估业务是否接受。对于 UPDATE 操作则反过来。WHERE 条件里的字段必须有索引否则就是全表扫描。平时设计表时就该注意补数场景下索引的作用会被放大。提示不要为了补数而把表上所有索引删光。删除前充分评估补数完成后记得在低峰期重建并验证索引状态是否正常如SHOW INDEX FROM的结果中 Cardinality 是否有值。4.4 事务与提交间隔怎么设置单个事务写过大会产生两个问题一是 undo log 膨胀二是主从复制延迟。小批量多次提交能降低这两个风险。InnoDB 默认是自动提交也就是每条 SQL 一个事务。用存储过程循环分批时每批 UPDATE 完成后就提交一次。这里要注意不要在存储过程里显式开启一个大事务包住所有批次那跟不分批没有区别。对于 INSERT ... SELECT 这种整体操作建议也按主键区间拆开执行每批一个事务这样出错时只需要回滚当前批次之前的批次已经落库。5. 常见问题与排查技巧实录5.1 补数后出现主键冲突或重复数据原因几乎都是判断“是否缺失”的条件不对。排查思路检查关联条件里有没有漏掉逻辑删除标记、状态过滤条件。检查目标表是否已有部分数据被补过一遍补数脚本重复执行导致的。用两条 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 一张补数问题速查表现象常见原因优先排查动作插入时主键冲突重复补数或漏判断已有记录检查目标表现有数据补 NOT EXISTS 条件更新后部分行值不对GROUP BY 子查询未按粒度正确汇合先转为 SELECT 校验确认汇总粒度补数期间线上卡顿未分批长事务持有行锁过久KILL 当前会话重写为分批提交补数脚本跑着跑着没反应关联字段无索引全表扫描EXPLAIN 查看执行计划补建索引补完数据后查询变慢索引缺失或统计信息过期EXPLAIN 定位补索引或 ANALYZE TABLE6. 最后分享几个我在实际项目中悟出来的细节这些内容不一定能写进标准文档但确实影响了补数成功率。第一养成“先 SELECT 后 UPDATE/INSERT”的肌肉记忆。补数脚本里UPDATE 的前一步永远是同条件 SELECT。哪怕你觉得自己已经检查过三遍也先跑一遍 SELECT 看行数和数据样例。我一个同事有一次把 UPDATE 的 WHERE 条件写错了原文是WHERE alliance_id 1001漏了个等号相关条件结果把某一大类联盟全部改了教训深刻。第二补数脚本一定要留痕。在 IDE 里跑完补数 SQL 后顺手把脚本保存为带日期和用途的文件。我会按这种格式归档20250115_补联盟建筑初始记录.sql。一个项目跑两三年后这些脚本就是最详细的数据变更历史比任何文档都可靠。第三补完数不等于结束要做对账。补数后马上写几条对账 SQL比如“联盟总数和有建筑的联盟数是否匹配”“积分字段为空的行数是否归零”。对账通过才算闭环。对账 SQL 也一并保存下次同类补数可以直接复用。第四所有操作前做好快照备份。这里再强调一次快照不是为了好看它是最后的退路。没做快照就敢跑批量 UPDATE 的只能说明还没经历过失手。数据库的批量补齐本质上就是在“数据规则变更”和“存量数据现实”之间做一次强制同步。只要准备充分、方案清晰、留好退路这项操作可以做得又快又稳。如果你正准备对线上数据做一次批量补齐建议严格按照上文流程走一遍尤其不要跳过影响评估和快照备份这两步。