罗罗编程学习记录
本页内容 展开查看

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 减少往返次数、摊薄事务与日志开销,吞吐显著提升;但批次要控制大小,避免单批过大形成大事务。

概念解释

  • 往返成本:每条语句一次网络交互与解析执行。
  • 事务摊薄:批量在一个事务内提交,日志刷盘次数减少。
  • 索引维护:批量插入时索引页批量组织,比逐条随机插入更有序。
  • 批次权衡:批次过小往返多,批次过大锁与回滚压力大。

原理流程

  1. 收集待写数据为集合。
  2. 按批大小拆分,每批一条批量 INSERT(或批量值列表)。
  3. 逐批提交,监控批大小与事务时长。

代码 / 命令

-- 逐条(慢)
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,影响磁盘与复制。
  • 回滚代价:单批失败回滚量随批次增大。

原理流程

  1. 按行宽、索引数估算单批体积。
  2. 试跑不同批次大小,观察事务时长、锁等待、日志量与复制延迟。
  3. 选吞吐与并发影响平衡的批次,固定为任务默认值。

代码 / 命令

批次评估维度:
  单批行数 × 行宽 = 单批数据量
  观察:事务时长、锁等待、redo/WAL 生成速率、主从延迟、失败回滚量
  典型起步:每批 500~1000 行;大宽表/多索引下调小,按实测调整

常见误区

  • 误区:网上给的固定批次值可以直接套用。正解:批次与行宽、索引数、磁盘与并发强相关;必须在自身环境压测确定。
  • 误区:批量写入只看入库速度。正解:还要看对在线查询的锁影响与主从复制延迟;吞吐高但阻塞业务不可接受。

追问

  • 问:批量导入期间主从延迟上涨怎么办?答:降低批次提交频率/限速,评估从库回放能力;大批量加载错峰执行。
  • 问:批量写入能否关闭索引提速?答:特定一次性加载场景可先建表/后建索引;在线业务表不建议长期停索引。

05.1.3.5-003 批量更新/删除为什么必须分批?

频率 ★★★★★ | 难度 进阶

一句话结论

一次性更新/删除大表会形成长事务:持锁行数多、回滚日志膨胀、阻塞并发、主从回放延迟;按主键/条件分批小批量处理,每批提交,控制单批锁范围与事务时长。

概念解释

  • 长事务:大事务长时间持锁与快照,阻塞版本回收与并发写入。
  • 锁范围:单语句锁定匹配行;批量越大锁越多,冲突概率越高。
  • 回滚膨胀:大事务 undo/WAL/回滚段累积,失败回滚代价高。
  • 复制压力:大事务在从库回放集中产生延迟。

原理流程

  1. 按主键区间/游标圈定一批待处理行。
  2. 小事务内更新/删除该批并提交。
  3. 循环下一批,直到完成;监控锁等待与延迟。

代码 / 命令

-- 危险:无分批、无明确小范围的全表更新/删除
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 与崩溃回滚量越大。
  • 复制延迟:大事务在从库集中回放,追平时间长。

原理流程

  1. 识别长事务:批量写、循环内远程调用、事务中混入耗时操作。
  2. 缩小事务边界:批量拆小批,远程调用/消息发送移出事务。
  3. 每批独立提交,监控事务时长与锁等待。

代码 / 命令

大事务典型构成:
  大批量写入/更新/删除
  事务内调用外部接口、RPC、消息发送
  长循环内逐条数据库操作

拆分:
  数据库写按批小事务提交
  外部交互放在事务提交之后(或用最终一致补偿)
  事务内只做必须原子完成的数据库操作

常见误区

  • 误区:事务包得越大越安全。正解:大事务降低并发、放大故障面;只把必须原子的操作放进同一事务。
  • 误区:长事务只是慢一点,结束后自动恢复。正解:期间造成的阻塞、版本堆积与复制延迟可能持续扩散,需在设计上避免。

追问

  • 问:业务要求多条操作原子怎么办?答:在保证原子性前提下尽量缩小涉及的数据量;无法缩小时评估锁策略与隔离级别,必要时重构业务边界。
  • 问:如何发现线上长事务?答:监控事务持续时间、未提交会话与 undo/版本堆积;具体视图见 05.1.2 / 引擎章节。

05.1.3.5-005 批量操作的锁范围由什么决定?

频率 ★★★★☆ | 难度 高级

一句话结论

锁范围由匹配行数、索引使用、隔离级别与语句类型决定:走索引精确定位只锁匹配行;无索引或条件扫描会扩大扫描与锁定范围;更新/删除持锁到事务提交,批量越大持锁集合越大。

概念解释

  • 行锁匹配:通过索引定位的行加记录锁;扫描过但不匹配的行是否加锁取决于引擎与隔离级别。
  • 扫描扩大:无可用索引时扫描大量行,锁定/间隙范围扩大。
  • 持锁时长:行锁持有到事务提交;分批缩短单事务持锁时间。
  • 隔离级别:RR/RC 下加锁规则与间隙行为不同(见 05.1.2)。

原理流程

  1. 检查批量语句是否走索引定位待处理行。
  2. 评估单批锁定行数与持锁时长。
  3. 分批 + 索引定位,把锁范围限制在小批匹配行内。

代码 / 命令

-- 走主键/索引定位一批,锁范围小
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. 圈定回填范围,估算总行数与批次。
  2. 抽样小批试跑,校验计算逻辑与结果。
  3. 全量分批执行,记录位点;失败中断后续跑;完成后对账。

代码 / 命令

-- 回填示例:补写冗余统计列
-- 批 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 数据归档与冷热分离怎么做?

频率 ★★★☆☆ | 难度 进阶

一句话结论

按时间/业务维度把冷数据迁移到归档表或归档库,在线库只留热数据,减小在线表体积、提升扫描与索引效率;归档流程需保证可追溯、可查询、迁移后校验并安全清理原数据。

概念解释

  • 冷热划分:按访问频率、时间或业务状态划分热/冷数据。
  • 归档载体:同库归档表、独立归档库或廉价存储。
  • 迁移流程:选出冷数据 → 写入归档 → 校验 → 分批删除在线数据。
  • 可查性:冷数据仍需按业务可检索,不能简单丢弃。

原理流程

  1. 制定冷热划分规则与保留周期。
  2. 分批把冷数据复制到归档存储并校验一致。
  3. 确认归档完整后分批删除在线冷数据;保留回滚观察期。

代码 / 命令

-- 归档:先复制到归档表
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/索引变更在从库回放,可能造成延迟。

原理流程

  1. 评估表规模、变更类型与可接受窗口。
  2. 选在线 DDL 方案,低峰执行。
  3. 监控锁等待、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 回收,表膨胀需重建/整理。
  • 回收操作:重建表/索引、整理碎片、引擎相关收缩工具。
  • 代价:回收重建占用资源与锁窗口,需低峰评估。

原理流程

  1. 观察表/索引体积与碎片/死元组统计。
  2. 判断空间是内部可复用还是物理膨胀需回收。
  3. 低峰执行重建/整理;回收后核对体积与计划。

代码 / 命令

-- 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 多次扫描;用完及时清理,控制临时表体积与空间占用,区分会话临时表与持久中间表。

概念解释

  • 临时表:会话隔离,结束自动释放,适合单批次中间加工。
  • 中间表:持久化的结果暂存,跨任务使用,需主动清理。
  • 使用场景:多步聚合、分批处理落点、对账中间结果。
  • 风险:中间表膨胀、残留数据、索引缺失导致二次扫描慢。

原理流程

  1. 判断一条 SQL 是否可完成;多步加工才引入中间表。
  2. 临时表加必要索引加速二次查询。
  3. 任务结束清理临时表/中间表数据,避免残留。

代码 / 命令

-- 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 与主从回放压力。批量加载前评估索引数量与日志承受力。

概念解释

  • 索引维护:每行写入更新相关二级索引,随机页修改与页分裂。
  • 日志生成:写前日志记录所有修改,批量操作日志量随行数线性增长。
  • 复制放大:主库日志传到从库回放,批量写造成从库延迟。
  • 权衡:一次性加载可先卸索引后重建;在线写入表保留必要索引。

原理流程

  1. 统计目标表索引数量与批量行数。
  2. 估算索引维护与日志量,观察磁盘 IO 与复制延迟。
  3. 一次性加载评估后建索引;在线表控制索引与批次节奏。

代码 / 命令

批量写入开销 ≈ 行写入 + 各二级索引维护 + 日志生成
观察:IO 吞吐、日志生成速率、从库 Seconds Behind / 回放延迟

常见误区

  • 误区:批量写入慢只怪磁盘顺序写。正解:索引随机维护与日志刷盘共同构成瓶颈;减少无用索引比单纯换盘更有效。
  • 误区:从库延迟与主库批量写无关。正解:批量事务集中回放是从库延迟常见原因;需限速与错峰。

追问

  • 问:批量加载时先删索引后重建可行吗?答:仅限一次性离线加载场景;在线业务表删索引会影响查询,需评估窗口。
  • 问:批量写日志量怎么估算?答:按行宽、索引数与变更类型估算;以实测日志生成速率为准。

05.1.3.5-012 批量任务如何控制并发与限速?

频率 ★★★☆☆ | 难度 进阶

一句话结论

批量任务需要并发度与单批节奏控制:并发过高挤占在线资源、放大锁冲突;过低则迁移周期长。按数据库负载、复制延迟与锁等待动态限速,分批串行或有限并发。

概念解释

  • 并发度:同时处理的批次数/连接数。
  • 限速信号:CPU/IO 水位、锁等待、主从延迟、慢查询。
  • 退避节奏:负载升高自动降速,负载回落再恢复。
  • 批次内串行:单批内顺序处理,避免同批冲突。

原理流程

  1. 设定初始并发与批次大小,压测观察负载信号。
  2. 运行中按延迟/锁等待指标动态调整并发与间隔。
  3. 高峰限速、低峰提速;记录任务进度与速率。

代码 / 命令

批量任务控制参数:
  并发 worker 数(建议初始 1~2,按负载调整)
  每批行数、批间间隔
  限速触发:主从延迟超阈值 / 锁等待增多 / IO 水位高 → 降速或暂停

常见误区

  • 误区:多开几个线程并行刷数最快。正解:并发争抢同一库资源,锁与日志放大可能使总吞吐下降;限速比盲目并发重要。
  • 误区:批量任务跑完即可,不用看在线指标。正解:任务不能影响在线 SLA;必须把负载信号纳入控制。

追问

  • 问:分布式多机跑同一批数据怎么避免重复?答:按分片/区间分配给不同 worker,路由键不重叠;位点记录与幂等兜底。
  • 问:任务中途遇到延迟飙升怎么处理?答:暂停批次、排查锁与资源,恢复后从断点续跑。

05.1.3.5-013 大批量数据导入有哪些加载方式?

频率 ★★★☆☆ | 难度 进阶

一句话结论

除批量 INSERT 外,引擎提供专用高速加载工具/命令(如 MySQL 批量装载、PG COPY),面向整批数据导入,绕过部分单行解析开销;选择时权衡数据来源、并发约束、索引与日志开销。

概念解释

  • 批量 INSERT:应用层分批值列表,灵活但走常规语句路径。
  • 专用加载:引擎原生批量装载命令,吞吐更高,适合文件/批量导入。
  • 前置准备:评估索引、约束、外键与唯一校验开销。
  • 一致性:导入中途失败的回滚与重跑策略。

原理流程

  1. 明确数据来源(文件、接口、迁移)与规模。
  2. 选批量 INSERT 或专用加载命令,安排索引/约束策略。
  3. 导入后校验行数、抽样数据与索引状态。

代码 / 命令

-- 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 大批量结果集导出要注意什么?

频率 ★★★☆☆ | 难度 进阶

一句话结论

大结果集导出要避免一次性全量加载到内存:游标/分批读取、控制排序范围、减少宽列与大字段;导出期间注意长查询对快照与资源的影响,必要时从只读副本导出。

概念解释

  • 分批/游标:按主键游标分批拉取,避免结果集撑爆应用内存。
  • 排序成本:大范围排序落盘且耗时;导出按有序键分批。
  • 资源来源:重导出可走只读副本,减轻主库压力。
  • 一致性:导出快照点与增量数据的处理需明确。

原理流程

  1. 确认导出范围与列清单,去掉不必要大字段。
  2. 游标分批导出,记录位点。
  3. 大库导出走副本;完成后核对行数与抽样。

代码 / 命令

-- 游标分批导出,而非一次性 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 批量任务的失败重试与幂等怎么设计?

频率 ★★★☆☆ | 难度 高级

一句话结论

批量任务必须可重跑:操作幂等、失败可从断点续跑、重试有退避与上限;区分可重试错误(锁等待、超时)与不可重试错误(数据错误、约束冲突),失败记录便于排查与补偿。

概念解释

  • 幂等:同一批重复执行结果一致,不重复累加/重复写。
  • 断点续跑:记录位点/批次状态,失败后从未完成批次继续。
  • 重试策略:指数退避、最大次数;区分临时错误与永久错误。
  • 失败记录:批次、行号、错误原因,便于人工介入。

原理流程

  1. 写入/更新逻辑设计为幂等(覆盖写、唯一键、状态机)。
  2. 批次状态持久化:待处理/完成/失败;失败批入重试队列。
  3. 重试达上限转人工,记录错误上下文。

代码 / 命令

任务状态表:batch_id, range, status, retry_count, last_error, updated_at
幂等写:按唯一键覆盖/状态机推进,重复执行不产生重复结果
重试:临时错误退避重试 N 次;永久错误落失败队列人工处理

常见误区

  • 误区:失败重试直接重跑整个任务。正解:应按批次断点续跑;全量重跑浪费资源且可能重复污染。
  • 误区:所有错误都无限重试。正解:数据错误、约束冲突等永久错误重试无用;应告警人工处理。

追问

  • 问:幂等更新怎么实现?答:用唯一键冲突处理、状态条件更新(WHERE status = 'INIT')或覆盖写;避免自增累加无保护。
  • 问:跨批部分成功怎么对账?答:按批次状态与业务汇总对账,定位未完成批次补齐。

05.1.3.5-016 预聚合/汇总表适合解决什么问题?

频率 ★★★☆☆ | 难度 进阶

一句话结论

高频聚合统计(按维度计数、汇总)每次扫明细代价高;预聚合表按维度提前算好结果,查询直接读汇总,以写入时维护成本换查询性能;需保证汇总与明细一致,支持增量更新与重算。

概念解释

  • 预聚合:离线/增量按维度汇总成汇总表。
  • 增量维护:新数据到来时增量更新汇总值。
  • 重算兜底:汇总偏差时可按明细重建。
  • 适用边界:维度固定、查询频繁、明细量大。

原理流程

  1. 梳理高频聚合查询与维度组合。
  2. 设计汇总粒度(天/用户/类别)与增量更新机制。
  3. 查询走汇总表;定期对账,必要时全量重算。

代码 / 命令

-- 汇总表示例:按天 + 用户汇总订单金额
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 在线刷数据/订正脚本上线要注意什么?

频率 ★★★★☆ | 难度 进阶

一句话结论

订正脚本必须先在测试环境验证、小批量灰度、可回滚、可对账:明确订正范围与条件,先备份/可追溯,分批小事务执行,记录变更前后值,完成后抽样与总量校验。

概念解释

  • 范围明确:订正条件精确,避免误更新无关行。
  • 可追溯:变更前留备份或记录旧值,支持回滚。
  • 灰度分批:先小批验证结果,再全量推进。
  • 对账校验:前后对比受影响行与汇总指标。

原理流程

  1. 梳理订正条件与预期结果,测试环境 dry-run 核对命中行。
  2. 备份/记录旧值,小批执行并核对结果。
  3. 分批全量执行,完成后抽样与聚合对账,保留回滚方案。

代码 / 命令

-- 先 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 注意什么?答:类型映射、自增/主键、批量加载命令与索引重建顺序分别核对。
  • 问:通用批量原则有哪些是跨库不变的?答:分批、小事务、索引定位、限速、幂等与对账,这些与引擎无关。

参考来源