05.1.3.5 批量与大规模数据操作
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力 / 05.1.3 SQL 优化与执行计划
覆盖范围
- 批量写入/更新/删除的批次划分与事务控制
- 大事务危害、锁范围与回滚代价
- 历史数据回填、归档、冷热分离
- 批量 DDL、空间回收与在线变更影响
- 批量任务并发限速、失败重试与幂等、预聚合
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.1.3.5-003 | 批量更新/删除为什么分批 | 进阶 | ★★★★★ |
| 05.1.3.5-004 | 大事务的危害与拆分 | 进阶 | ★★★★★ |
| 05.1.3.5-006 | 批量回填历史数据方案 | 进阶 | ★★★★☆ |
| 05.1.3.5-017 | 在线刷数据脚本注意事项 | 进阶 | ★★★★☆ |
| 05.1.3.5-005 | 批量操作的锁范围 | 高级 | ★★★★☆ |
批量操作主线
明确规模与目标 → 分批(主键/游标)→ 每批小事务提交
→ 控制锁范围与日志量 → 限速与并发 → 失败重试/幂等 → 校验与对账
反面:单大事务全表更新/删除 → 长锁、回滚膨胀、复制延迟、主从追赶困难
05.1.3.5-001 批量写入为什么要合并成批次?
频率 ★★★★☆ | 难度 基础
一句话结论
逐条写入每条都有网络往返、事务提交与索引维护成本;合并为批量 INSERT 减少往返次数、摊薄事务与日志开销,吞吐显著提升;但批次要控制大小,避免单批过大形成大事务。
概念解释
- 往返成本:每条语句一次网络交互与解析执行。
- 事务摊薄:批量在一个事务内提交,日志刷盘次数减少。
- 索引维护:批量插入时索引页批量组织,比逐条随机插入更有序。
- 批次权衡:批次过小往返多,批次过大锁与回滚压力大。
原理流程
- 收集待写数据为集合。
- 按批大小拆分,每批一条批量
INSERT(或批量值列表)。 - 逐批提交,监控批大小与事务时长。
代码 / 命令
-- 逐条(慢)
INSERT INTO t (a, b) VALUES (1, 'x');
INSERT INTO t (a, b) VALUES (2, 'y');
-- 批量(控制每批行数,例如每批 500~1000 行,按实测调整)
INSERT INTO t (a, b) VALUES
(1, 'x'), (2, 'y'), (3, 'z');
常见误区
- 误区:批量合并越多越好。正解:单批过大会长事务、锁范围扩大、回滚段膨胀;需按行宽、索引数与日志承受力定批大小。
- 误区:批量写入不用考虑索引。正解:索引越多批量写入维护成本越高;批量加载场景可评估先禁/后建索引的策略。
追问
- 问:批量插入失败如何处理?答:按批组织事务,失败批重试或补偿;跨批一致性靠对账与重试保障。
- 问:一条
VALUES列表行数有上限吗?答:受数据包大小、参数数量与max_allowed_packet(MySQL)等限制;超限需再分批。
05.1.3.5-002 批量写入的批次大小怎么确定?
频率 ★★★★☆ | 难度 进阶
一句话结论
批次大小按单批事务时长、锁范围、日志生成量与回滚代价权衡:太小则往返与提交次数多,太大则长事务与主从压力大;通常先取几百行一批起步,压测观察锁等待、日志量与复制延迟后调整。
概念解释
- 事务时长:单批提交耗时应控制在秒级以内。
- 锁范围:单批锁定行数越少,并发冲突越小。
- 日志量:批量写产生 redo/undo/WAL 与 binlog,影响磁盘与复制。
- 回滚代价:单批失败回滚量随批次增大。
原理流程
- 按行宽、索引数估算单批体积。
- 试跑不同批次大小,观察事务时长、锁等待、日志量与复制延迟。
- 选吞吐与并发影响平衡的批次,固定为任务默认值。
代码 / 命令
批次评估维度:
单批行数 × 行宽 = 单批数据量
观察:事务时长、锁等待、redo/WAL 生成速率、主从延迟、失败回滚量
典型起步:每批 500~1000 行;大宽表/多索引下调小,按实测调整
常见误区
- 误区:网上给的固定批次值可以直接套用。正解:批次与行宽、索引数、磁盘与并发强相关;必须在自身环境压测确定。
- 误区:批量写入只看入库速度。正解:还要看对在线查询的锁影响与主从复制延迟;吞吐高但阻塞业务不可接受。
追问
- 问:批量导入期间主从延迟上涨怎么办?答:降低批次提交频率/限速,评估从库回放能力;大批量加载错峰执行。
- 问:批量写入能否关闭索引提速?答:特定一次性加载场景可先建表/后建索引;在线业务表不建议长期停索引。
05.1.3.5-003 批量更新/删除为什么必须分批?
频率 ★★★★★ | 难度 进阶
一句话结论
一次性更新/删除大表会形成长事务:持锁行数多、回滚日志膨胀、阻塞并发、主从回放延迟;按主键/条件分批小批量处理,每批提交,控制单批锁范围与事务时长。
概念解释
- 长事务:大事务长时间持锁与快照,阻塞版本回收与并发写入。
- 锁范围:单语句锁定匹配行;批量越大锁越多,冲突概率越高。
- 回滚膨胀:大事务 undo/WAL/回滚段累积,失败回滚代价高。
- 复制压力:大事务在从库回放集中产生延迟。
原理流程
- 按主键区间/游标圈定一批待处理行。
- 小事务内更新/删除该批并提交。
- 循环下一批,直到完成;监控锁等待与延迟。
代码 / 命令
-- 危险:无分批、无明确小范围的全表更新/删除
UPDATE orders SET status = 'CLOSED' WHERE created_at < '2025-01-01';
DELETE FROM orders WHERE created_at < '2025-01-01';
-- 分批:按主键游标循环,每批限定量
-- 第一批
DELETE FROM orders
WHERE id IN (
SELECT id FROM orders
WHERE created_at < '2025-01-01'
ORDER BY id LIMIT 500
);
-- 记录最后 id,循环直到无匹配行;每批独立提交
常见误区
- 误区:大事务只是执行慢,不影响别人。正解:长事务持锁与快照,阻塞其他事务与版本回收,连锁影响并发性能。
- 误区:分批删除能马上释放磁盘空间。正解:删除先标记/版本回收,物理空间回收受引擎机制控制;见 05.1.3.5-009。
追问
- 问:分批更新如何避免漏掉或重复处理?答:按稳定主键游标推进,记录处理位点;条件集合不变,循环到无匹配为止。
- 问:批量删除期间新写入的数据会被误删吗?答:条件按时间/主键圈定;新数据落在条件范围外即可;边界语义在脚本中明确。
05.1.3.5-004 大事务有哪些危害?如何拆分?
频率 ★★★★★ | 难度 进阶
一句话结论
大事务持有锁与快照时间长,导致锁等待、版本无法回收、回滚段膨胀、复制延迟与崩溃恢复变慢;拆分原则是缩短事务边界:短事务、小批次、把非必要操作移出事务,只读与写入分离。
概念解释
- 持锁时间:事务越长,锁定资源占用越久。
- 快照与版本:长事务阻止旧版本回收,表/索引膨胀。
- 回滚代价:事务越大,undo 与崩溃回滚量越大。
- 复制延迟:大事务在从库集中回放,追平时间长。
原理流程
- 识别长事务:批量写、循环内远程调用、事务中混入耗时操作。
- 缩小事务边界:批量拆小批,远程调用/消息发送移出事务。
- 每批独立提交,监控事务时长与锁等待。
代码 / 命令
大事务典型构成:
大批量写入/更新/删除
事务内调用外部接口、RPC、消息发送
长循环内逐条数据库操作
拆分:
数据库写按批小事务提交
外部交互放在事务提交之后(或用最终一致补偿)
事务内只做必须原子完成的数据库操作
常见误区
- 误区:事务包得越大越安全。正解:大事务降低并发、放大故障面;只把必须原子的操作放进同一事务。
- 误区:长事务只是慢一点,结束后自动恢复。正解:期间造成的阻塞、版本堆积与复制延迟可能持续扩散,需在设计上避免。
追问
- 问:业务要求多条操作原子怎么办?答:在保证原子性前提下尽量缩小涉及的数据量;无法缩小时评估锁策略与隔离级别,必要时重构业务边界。
- 问:如何发现线上长事务?答:监控事务持续时间、未提交会话与 undo/版本堆积;具体视图见 05.1.2 / 引擎章节。
05.1.3.5-005 批量操作的锁范围由什么决定?
频率 ★★★★☆ | 难度 高级
一句话结论
锁范围由匹配行数、索引使用、隔离级别与语句类型决定:走索引精确定位只锁匹配行;无索引或条件扫描会扩大扫描与锁定范围;更新/删除持锁到事务提交,批量越大持锁集合越大。
概念解释
- 行锁匹配:通过索引定位的行加记录锁;扫描过但不匹配的行是否加锁取决于引擎与隔离级别。
- 扫描扩大:无可用索引时扫描大量行,锁定/间隙范围扩大。
- 持锁时长:行锁持有到事务提交;分批缩短单事务持锁时间。
- 隔离级别:RR/RC 下加锁规则与间隙行为不同(见 05.1.2)。
原理流程
- 检查批量语句是否走索引定位待处理行。
- 评估单批锁定行数与持锁时长。
- 分批 + 索引定位,把锁范围限制在小批匹配行内。
代码 / 命令
-- 走主键/索引定位一批,锁范围小
DELETE FROM orders
WHERE id BETWEEN :start AND :end AND status = 'DONE';
-- 无索引条件:扫描范围大,锁与阻塞风险高
DELETE FROM orders WHERE remark LIKE '%temp%';
常见误区
- 误区:批量更新只锁最终被修改的行。正解:扫描路径上的加锁行为受引擎规则影响;无索引扫描可能锁定更多范围,需看锁视图确认。
- 误区:RC 级别下批量操作不会间隙锁。正解:不同引擎与级别规则不同;具体加锁规则见 05.1.2,不能凭级别名称下结论。
追问
- 问:批量任务如何降低与在线业务的锁冲突?答:低峰执行、小批提交、索引定位、限速;必要时与业务错峰。
- 问:锁等待如何观测?答:查阻塞链与等待事件视图,定位批量任务持锁行与阻塞的在线事务。
05.1.3.5-006 历史数据批量回填(刷数)方案怎么设计?
频率 ★★★★☆ | 难度 进阶
一句话结论
历史回填按只读游标分批、小事务提交、可重跑幂等、先抽样校验再全量设计:分批读取待处理行,按批计算并写回,记录进度位点,失败可从断点续跑,前后做数据对账。
概念解释
- 游标分批:按主键/更新时间顺序推进,避免大结果集。
- 幂等可重跑:同一行重复处理结果一致,失败重试不产生脏写。
- 进度位点:记录已处理最大主键/时间,支持断点续跑。
- 对账校验:抽样与总量对比新旧值,确认回填正确。
原理流程
- 圈定回填范围,估算总行数与批次。
- 抽样小批试跑,校验计算逻辑与结果。
- 全量分批执行,记录位点;失败中断后续跑;完成后对账。
代码 / 命令
-- 回填示例:补写冗余统计列
-- 批 1:取游标之后的一批
SELECT id, amount FROM orders
WHERE id > :lastId ORDER BY id LIMIT 500;
-- 按批计算后写回(幂等:重复执行结果相同)
UPDATE orders SET total_amount = :calc
WHERE id IN (:batchIds);
-- 记录 :lastId,循环至无行;完成后抽样/聚合对账
常见误区
- 误区:回填脚本一次全表跑完最快。正解:全量单事务锁与日志风险高;分批可控、可续跑、可对账。
- 误区:回填不需要幂等。正解:任务重试/重跑不可避免;非幂等会重复累加或污染数据,写入逻辑必须可重复执行。
追问
- 问:回填期间数据还在被业务更新怎么办?答:明确回填基线与并发冲突策略;必要时按版本/更新时间避让,冲突行后续二次校准。
- 问:如何校验回填正确性?答:抽样对比新旧计算值,按维度聚合对账;关键表全量比对差值为零。
05.1.3.5-007 数据归档与冷热分离怎么做?
频率 ★★★☆☆ | 难度 进阶
一句话结论
按时间/业务维度把冷数据迁移到归档表或归档库,在线库只留热数据,减小在线表体积、提升扫描与索引效率;归档流程需保证可追溯、可查询、迁移后校验并安全清理原数据。
概念解释
- 冷热划分:按访问频率、时间或业务状态划分热/冷数据。
- 归档载体:同库归档表、独立归档库或廉价存储。
- 迁移流程:选出冷数据 → 写入归档 → 校验 → 分批删除在线数据。
- 可查性:冷数据仍需按业务可检索,不能简单丢弃。
原理流程
- 制定冷热划分规则与保留周期。
- 分批把冷数据复制到归档存储并校验一致。
- 确认归档完整后分批删除在线冷数据;保留回滚观察期。
代码 / 命令
-- 归档:先复制到归档表
INSERT INTO orders_archive
SELECT * FROM orders WHERE created_at < '2025-01-01';
-- 校验行数/汇总一致后,分批删除在线表冷数据(见 05.1.3.5-003)
DELETE FROM orders WHERE created_at < '2025-01-01' ORDER BY id LIMIT 500;
常见误区
- 误区:归档就是把老数据删掉。正解:删除前必须先完整迁移并校验;归档数据要保留查询与恢复能力。
- 误区:归档表和在线表放一起就达到减负目的。正解:同库归档仍占用在线库资源;冷数据应迁出热库或独立存储。
追问
- 问:归档删除前为什么要观察期?答:确认归档查询无误、无业务依赖后再清理在线数据;观察期内可回滚。
- 问:分区表能替代归档吗?答:分区可按时间裁剪扫描,但数据仍在在线库;冷数据长期不访问仍应迁出。
05.1.3.5-008 批量 DDL(加列、加索引)对在线业务有什么影响?
频率 ★★★☆☆ | 难度 高级
一句话结论
DDL 会锁表或重建表/索引,期间阻塞读写或消耗大量 IO/CPU;大表应使用在线变更方案(在线索引构建、在线 DDL 工具),低峰执行并监控锁等待与复制延迟,提前评估回滚。
概念解释
- 锁表风险:传统 DDL 重建表期间可能阻塞写入。
- 资源消耗:建索引全表扫描排序,占用 IO/CPU/临时空间。
- 在线方案:引擎原生在线 DDL 或第三方在线变更工具,减少锁表窗口。
- 复制影响:DDL/索引变更在从库回放,可能造成延迟。
原理流程
- 评估表规模、变更类型与可接受窗口。
- 选在线 DDL 方案,低峰执行。
- 监控锁等待、IO、复制延迟;准备回滚与中止预案。
代码 / 命令
-- MySQL:建索引(ALGORITHM/LOCK 子句控制在线行为,语法以版本为准)
-- CREATE INDEX idx_x ON t (col), ALGORITHM=INPLACE, LOCK=NONE;
-- PG:CREATE INDEX CONCURRENTLY 不阻塞常规写入(可能需多次失败重试)
CREATE INDEX CONCURRENTLY idx_x ON t (col);
常见误区
- 误区:加索引只是元数据操作,很快。正解:二级索引要遍历表构建,大表耗时与资源消耗显著。
- 误区:在线 DDL 完全无锁无影响。正解:多数在线方案只是缩短/避免排他锁,构建期间仍有资源开销与回放延迟。
追问
- 问:在线 DDL 中途失败怎么办?答:按工具/引擎机制清理半成品对象;PG
CONCURRENTLY失败会留下无效索引需重建。 - 问:多张大表变更顺序怎么排?答:错峰执行,避免叠加 IO 与复制延迟;关键变更分批窗口推进。
05.1.3.5-009 大批量删除后磁盘空间为什么不释放?怎么回收?
频率 ★★★☆☆ | 难度 高级
一句话结论
删除先标记旧版本/空闲空间,物理磁盘空间不一定立刻归还操作系统;空间回收受引擎机制控制(InnoDB 页复用、PG 死元组回收与膨胀),需要配合重建/整理表或引擎回收机制,且回收操作本身有代价。
概念解释
- 逻辑删除:删除产生旧版本/空闲槽位,空间先在表/索引内部复用。
- 引擎差异:InnoDB 页内复用、表空间不一定收缩;PG 死元组由 VACUUM 回收,表膨胀需重建/整理。
- 回收操作:重建表/索引、整理碎片、引擎相关收缩工具。
- 代价:回收重建占用资源与锁窗口,需低峰评估。
原理流程
- 观察表/索引体积与碎片/死元组统计。
- 判断空间是内部可复用还是物理膨胀需回收。
- 低峰执行重建/整理;回收后核对体积与计划。
代码 / 命令
-- PG:回收死元组
VACUUM ANALYZE orders;
-- PG:表膨胀严重时重建表(锁/在线方案评估)
-- REINDEX / 重建表工具;具体见 05.3.1
-- MySQL:查看表与索引大小、碎片(具体视图见 05.2.4)
常见误区
- 误区:
DELETE后磁盘占用立刻下降。正解:空间多为先标记、内部复用;物理回收需专门操作。 - 误区:回收空间一定能显著提速。正解:若热点行仍在内存、内部空间已复用,回收收益有限;先看碎片与实际查询瓶颈。
追问
- 问:TRUNCATE 与 DELETE 空间行为有何不同?答:
TRUNCATE整体清空并释放空间(DDL),DELETE逐行标记;业务分批删除不能用TRUNCATE替代。 - 问:回收操作为什么要低峰做?答:重建/扫描大表消耗 IO/CPU 并可能锁表,高峰执行影响在线业务。
05.1.3.5-010 批量处理中临时表/中间表怎么用?
频率 ★★★☆☆ | 难度 进阶
一句话结论
中间结果复杂、需多步加工时可用临时表/中间表暂存分批结果,避免一条超大 SQL 多次扫描;用完及时清理,控制临时表体积与空间占用,区分会话临时表与持久中间表。
概念解释
- 临时表:会话隔离,结束自动释放,适合单批次中间加工。
- 中间表:持久化的结果暂存,跨任务使用,需主动清理。
- 使用场景:多步聚合、分批处理落点、对账中间结果。
- 风险:中间表膨胀、残留数据、索引缺失导致二次扫描慢。
原理流程
- 判断一条 SQL 是否可完成;多步加工才引入中间表。
- 临时表加必要索引加速二次查询。
- 任务结束清理临时表/中间表数据,避免残留。
代码 / 命令
-- PG 临时表示例
CREATE TEMP TABLE tmp_batch AS
SELECT id, amount FROM orders WHERE created_at >= '2026-01-01' LIMIT 500;
-- 基于临时表加工后处理;会话结束自动释放
常见误区
- 误区:中间表越多步骤越清晰越好。正解:多余中间表增加写入与维护;能在单语句内完成的聚合不必落表。
- 误区:临时表不用建索引。正解:临时表二次查询无索引同样会扫描;按访问路径补索引。
追问
- 问:临时表与内存表选择依据?答:结果集大小、加工复杂度与会话生命周期;具体引擎支持见对应章节。
- 问:持久中间表残留有什么风险?答:占空间、干扰统计与后续任务;需明确清理策略与保留周期。
05.1.3.5-011 批量操作的索引与日志开销有多大?
频率 ★★★☆☆ | 难度 进阶
一句话结论
批量写入要同步维护所有二级索引,索引越多写入越慢;同时生成大量 redo/undo/WAL 与复制日志,放大磁盘 IO 与主从回放压力。批量加载前评估索引数量与日志承受力。
概念解释
- 索引维护:每行写入更新相关二级索引,随机页修改与页分裂。
- 日志生成:写前日志记录所有修改,批量操作日志量随行数线性增长。
- 复制放大:主库日志传到从库回放,批量写造成从库延迟。
- 权衡:一次性加载可先卸索引后重建;在线写入表保留必要索引。
原理流程
- 统计目标表索引数量与批量行数。
- 估算索引维护与日志量,观察磁盘 IO 与复制延迟。
- 一次性加载评估后建索引;在线表控制索引与批次节奏。
代码 / 命令
批量写入开销 ≈ 行写入 + 各二级索引维护 + 日志生成
观察:IO 吞吐、日志生成速率、从库 Seconds Behind / 回放延迟
常见误区
- 误区:批量写入慢只怪磁盘顺序写。正解:索引随机维护与日志刷盘共同构成瓶颈;减少无用索引比单纯换盘更有效。
- 误区:从库延迟与主库批量写无关。正解:批量事务集中回放是从库延迟常见原因;需限速与错峰。
追问
- 问:批量加载时先删索引后重建可行吗?答:仅限一次性离线加载场景;在线业务表删索引会影响查询,需评估窗口。
- 问:批量写日志量怎么估算?答:按行宽、索引数与变更类型估算;以实测日志生成速率为准。
05.1.3.5-012 批量任务如何控制并发与限速?
频率 ★★★☆☆ | 难度 进阶
一句话结论
批量任务需要并发度与单批节奏控制:并发过高挤占在线资源、放大锁冲突;过低则迁移周期长。按数据库负载、复制延迟与锁等待动态限速,分批串行或有限并发。
概念解释
- 并发度:同时处理的批次数/连接数。
- 限速信号:CPU/IO 水位、锁等待、主从延迟、慢查询。
- 退避节奏:负载升高自动降速,负载回落再恢复。
- 批次内串行:单批内顺序处理,避免同批冲突。
原理流程
- 设定初始并发与批次大小,压测观察负载信号。
- 运行中按延迟/锁等待指标动态调整并发与间隔。
- 高峰限速、低峰提速;记录任务进度与速率。
代码 / 命令
批量任务控制参数:
并发 worker 数(建议初始 1~2,按负载调整)
每批行数、批间间隔
限速触发:主从延迟超阈值 / 锁等待增多 / IO 水位高 → 降速或暂停
常见误区
- 误区:多开几个线程并行刷数最快。正解:并发争抢同一库资源,锁与日志放大可能使总吞吐下降;限速比盲目并发重要。
- 误区:批量任务跑完即可,不用看在线指标。正解:任务不能影响在线 SLA;必须把负载信号纳入控制。
追问
- 问:分布式多机跑同一批数据怎么避免重复?答:按分片/区间分配给不同 worker,路由键不重叠;位点记录与幂等兜底。
- 问:任务中途遇到延迟飙升怎么处理?答:暂停批次、排查锁与资源,恢复后从断点续跑。
05.1.3.5-013 大批量数据导入有哪些加载方式?
频率 ★★★☆☆ | 难度 进阶
一句话结论
除批量 INSERT 外,引擎提供专用高速加载工具/命令(如 MySQL 批量装载、PG COPY),面向整批数据导入,绕过部分单行解析开销;选择时权衡数据来源、并发约束、索引与日志开销。
概念解释
- 批量 INSERT:应用层分批值列表,灵活但走常规语句路径。
- 专用加载:引擎原生批量装载命令,吞吐更高,适合文件/批量导入。
- 前置准备:评估索引、约束、外键与唯一校验开销。
- 一致性:导入中途失败的回滚与重跑策略。
原理流程
- 明确数据来源(文件、接口、迁移)与规模。
- 选批量 INSERT 或专用加载命令,安排索引/约束策略。
- 导入后校验行数、抽样数据与索引状态。
代码 / 命令
-- PG:从文件批量复制(示意)
COPY orders FROM '/backup/orders.csv' WITH (FORMAT csv);
-- MySQL:批量装载命令(LOAD DATA,语法与路径以版本为准)
-- LOAD DATA INFILE '/backup/orders.csv' INTO TABLE orders ...;
常见误区
- 误区:专用加载工具不产生日志/不维护索引。正解:加载仍需维护索引与日志,只是批量路径更高效;约束与唯一校验照常。
- 误区:导入完成不用校验。正解:必须核对行数、关键字段与约束状态;加载异常可能静默丢行。
追问
- 问:导入期间唯一约束冲突怎么处理?答:提前清洗重复数据;加载策略按错误容忍度选择中止或跳过记录。
- 问:跨库迁移导入用什么?答:导出/导入工具配合分批与校验;迁移流程见 05.1.4.5。
05.1.3.5-014 大批量结果集导出要注意什么?
频率 ★★★☆☆ | 难度 进阶
一句话结论
大结果集导出要避免一次性全量加载到内存:游标/分批读取、控制排序范围、减少宽列与大字段;导出期间注意长查询对快照与资源的影响,必要时从只读副本导出。
概念解释
- 分批/游标:按主键游标分批拉取,避免结果集撑爆应用内存。
- 排序成本:大范围排序落盘且耗时;导出按有序键分批。
- 资源来源:重导出可走只读副本,减轻主库压力。
- 一致性:导出快照点与增量数据的处理需明确。
原理流程
- 确认导出范围与列清单,去掉不必要大字段。
- 游标分批导出,记录位点。
- 大库导出走副本;完成后核对行数与抽样。
代码 / 命令
-- 游标分批导出,而非一次性 SELECT 全表
SELECT id, status, amount FROM orders
WHERE id > :lastId ORDER BY id LIMIT 1000;
-- 循环导出,记录 lastId
常见误区
- 误区:导出查询放主库无所谓。正解:大扫描与长快照占用主库资源;应评估走只读副本。
- 误区:导出文件行数对了就没问题。正解:还需抽样核对字段值、编码与截断情况;大字段尤其注意长度限制。
追问
- 问:导出过程中数据被更新怎么办?答:明确一致性要求;可按快照导出或记录导出时点,后续增量补齐。
- 问:超大规模归档导出用什么方式?答:数据库原生导出工具/分片并行导出,配合分批校验;具体工具见 05.1.4.4。
05.1.3.5-015 批量任务的失败重试与幂等怎么设计?
频率 ★★★☆☆ | 难度 高级
一句话结论
批量任务必须可重跑:操作幂等、失败可从断点续跑、重试有退避与上限;区分可重试错误(锁等待、超时)与不可重试错误(数据错误、约束冲突),失败记录便于排查与补偿。
概念解释
- 幂等:同一批重复执行结果一致,不重复累加/重复写。
- 断点续跑:记录位点/批次状态,失败后从未完成批次继续。
- 重试策略:指数退避、最大次数;区分临时错误与永久错误。
- 失败记录:批次、行号、错误原因,便于人工介入。
原理流程
- 写入/更新逻辑设计为幂等(覆盖写、唯一键、状态机)。
- 批次状态持久化:待处理/完成/失败;失败批入重试队列。
- 重试达上限转人工,记录错误上下文。
代码 / 命令
任务状态表:batch_id, range, status, retry_count, last_error, updated_at
幂等写:按唯一键覆盖/状态机推进,重复执行不产生重复结果
重试:临时错误退避重试 N 次;永久错误落失败队列人工处理
常见误区
- 误区:失败重试直接重跑整个任务。正解:应按批次断点续跑;全量重跑浪费资源且可能重复污染。
- 误区:所有错误都无限重试。正解:数据错误、约束冲突等永久错误重试无用;应告警人工处理。
追问
- 问:幂等更新怎么实现?答:用唯一键冲突处理、状态条件更新(
WHERE status = 'INIT')或覆盖写;避免自增累加无保护。 - 问:跨批部分成功怎么对账?答:按批次状态与业务汇总对账,定位未完成批次补齐。
05.1.3.5-016 预聚合/汇总表适合解决什么问题?
频率 ★★★☆☆ | 难度 进阶
一句话结论
高频聚合统计(按维度计数、汇总)每次扫明细代价高;预聚合表按维度提前算好结果,查询直接读汇总,以写入时维护成本换查询性能;需保证汇总与明细一致,支持增量更新与重算。
概念解释
- 预聚合:离线/增量按维度汇总成汇总表。
- 增量维护:新数据到来时增量更新汇总值。
- 重算兜底:汇总偏差时可按明细重建。
- 适用边界:维度固定、查询频繁、明细量大。
原理流程
- 梳理高频聚合查询与维度组合。
- 设计汇总粒度(天/用户/类别)与增量更新机制。
- 查询走汇总表;定期对账,必要时全量重算。
代码 / 命令
-- 汇总表示例:按天 + 用户汇总订单金额
CREATE TABLE order_daily_sum (
day date, user_id bigint, order_cnt bigint, total_amount numeric,
PRIMARY KEY (day, user_id)
);
-- 增量更新(新批次数据聚合后 upsert 到汇总表)
常见误区
- 误区:所有统计都该建汇总表。正解:维度多变、查询低频或明细量不大时,汇总表维护成本不划算。
- 误区:汇总表更新一次就长期准确。正解:明细订正/回滚会使汇总偏差;需要对账与重算机制。
追问
- 问:汇总表增量更新与事务一致性怎么保证?答:随业务事务或 binlog 增量维护;不一致时以对账重算兜底。
- 问:汇总粒度怎么选?答:按查询维度与时效要求平衡;粒度越细维护成本越高。
05.1.3.5-017 在线刷数据/订正脚本上线要注意什么?
频率 ★★★★☆ | 难度 进阶
一句话结论
订正脚本必须先在测试环境验证、小批量灰度、可回滚、可对账:明确订正范围与条件,先备份/可追溯,分批小事务执行,记录变更前后值,完成后抽样与总量校验。
概念解释
- 范围明确:订正条件精确,避免误更新无关行。
- 可追溯:变更前留备份或记录旧值,支持回滚。
- 灰度分批:先小批验证结果,再全量推进。
- 对账校验:前后对比受影响行与汇总指标。
原理流程
- 梳理订正条件与预期结果,测试环境 dry-run 核对命中行。
- 备份/记录旧值,小批执行并核对结果。
- 分批全量执行,完成后抽样与聚合对账,保留回滚方案。
代码 / 命令
-- 先 dry-run:只查不改,核对命中范围
SELECT id, old_col, :new_value FROM target_table
WHERE <订正条件>;
-- 小批订正(幂等、带条件保护)
UPDATE target_table SET col = :new_value
WHERE id IN (:batchIds) AND <条件保护>;
-- 对账:抽样旧值/新值对比,按维度聚合核对
常见误区
- 误区:订正脚本直接在生产全量执行。正解:必须先 dry-run 看范围、小批灰度、备份旧值;误更新范围大时回滚代价极高。
- 误区:订正完成不做对账。正解:需确认命中行全部订正、未命中行未被误改;抽样 + 总量双重校验。
追问
- 问:订正条件写错范围怎么办?答:上线前 dry-run 核对行数;执行中带主键批次与条件保护,发现异常立即停批,按备份旧值回滚。
- 问:订正期间并发写入冲突怎么处理?答:条件中加版本/状态保护,冲突行二次处理;必要时低峰窗口执行。
05.1.3.5-018 MySQL 与 PostgreSQL 批量操作有哪些差异?
频率 ★★★☆☆ | 难度 进阶
一句话结论
批量操作的通用原则一致(分批、小事务、控锁、可重试),但日志机制、在线 DDL 语法、空间回收与批量加载命令不同;迁移批量脚本时需按目标引擎核对命令与行为。
概念解释
| 维度 | MySQL InnoDB | PostgreSQL |
|---|---|---|
| 批量加载 | 批量 INSERT / LOAD DATA | COPY |
| 在线索引 | ALGORITHM=INPLACE, LOCK=NONE 等 |
CREATE INDEX CONCURRENTLY |
| 空间回收 | 页内复用,重建表/索引整理 | VACUUM + 重建/整理表 |
| 批量删除 | 分批小事务,undo/redo 维护 | 分批 + VACUUM 回收死元组 |
| 统计配合 | ANALYZE TABLE |
ANALYZE |
原理流程
- 通用主线:分批控锁 → 小事务提交 → 限速监控 → 对账重试;命令与回收机制按引擎替换。
代码 / 命令
-- MySQL 分批删除/更新思路(按主键游标)
-- PG 对应:同样分批,回收配合 VACUUM ANALYZE
常见误区
- 误区:批量脚本可以跨库直接复制运行。正解:加载命令、DDL 语法与回收机制不同;直接复制可能锁表或行为不符。
- 误区:两库批量删除后空间表现一致。正解:InnoDB 与 PG 回收机制不同,空间归还与膨胀治理分别见 05.2.4 / 05.3.4。
追问
- 问:批量迁移从 MySQL 到 PG 注意什么?答:类型映射、自增/主键、批量加载命令与索引重建顺序分别核对。
- 问:通用批量原则有哪些是跨库不变的?答:分批、小事务、索引定位、限速、幂等与对账,这些与引擎无关。
参考来源
- MySQL 8.0 Reference Manual: Bulk Data Loading:https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html
- MySQL 8.0 Reference Manual: Online DDL:https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl.html
- PostgreSQL Documentation: Populating a Database:https://www.postgresql.org/docs/current/populate.html
- PostgreSQL Documentation: VACUUM / Reindex:https://www.postgresql.org/docs/current/maintenance.html