一条 DELETE 如何拖垮三台从库：一次 MySQL 主从延迟事故复盘

一条 DELETE 如何拖垮三台从库：一次 MySQL 主从延迟事故复盘

前言

一条删除历史数据的 SQL，最终让三台从库同时出现小时级同步延迟，核心业务流程被卡住，并造成了实际资损。

事故发生前，我们的数据库采用一主三从架构：主库处理写请求，三台从库通过 binlog 同步数据并共同承担读流量。这样的架构看起来具备一定冗余，但这次事故暴露了一个容易被忽视的问题：

多个副本可以提高读能力，却不一定能够隔离复制链路中的共同故障。

本次事故的触发点，是一次按日期范围执行的千万级数据删除。删除条件没有可用索引，任务也没有拆分为小批次。从库当时只有一个工作线程负责顺序应用事务，大删除长时间占住唯一的同步通道，后面的订单、账户状态等核心变更全部被堵在 relay log 中。

本文将从事故链路、MySQL 复制机制、应急处置和长期治理四个方面，完整复盘这次问题。

为保护业务信息，文中的表名、日期和部分业务场景均已做脱敏处理。

一、事故是如何发生的

1. 数据库架构

事故发生时，数据库拓扑如下：

flowchart LR W[业务写请求] --> M[(MySQL 主库)] M -->|binlog / relay log| R1[(从库 1)] M -->|binlog / relay log| R2[(从库 2)] M -->|binlog / relay log| R3[(从库 3)] Q[业务读请求] --> R1 Q --> R2 Q --> R3

主库负责处理写请求，三台从库共同承担读请求。主库提交事务后，将变更写入 binlog；从库接收日志并写入 relay log，再由应用线程按顺序执行其中的事务。

当时每台从库只有一个工作线程处理同步逻辑。在日常小事务场景下，这套架构运行平稳，但它存在一个天然限制：前面的事务没有执行完成，后面的事务就只能继续等待。

2. 触发事故的 SQL

事故中的 SQL 可以抽象为：

DELETE FROM business_history
WHERE business_date >= '2026-01-01'
  AND business_date <  '2026-04-01';

这条 SQL 看起来并不复杂，但同时踩中了三个高风险点：

  1. business_date 没有可用索引，需要扫描大量数据；
  2. 日期范围过大，一次命中数千万行；
  3. 没有通过 LIMIT 或主键游标拆分为小批次事务。

执行后，主库需要完成大量数据扫描，并产生大量 Undo、Redo 和 binlog。更关键的是，这次操作形成了一个巨大的复制工作单元，并被传递到三台从库。

3. 业务侧的表现

大删除进入复制链路后，三台从库的同步延迟快速扩大。主库后续产生的订单更新、支付结果、账户状态等核心变更虽然已经提交，却迟迟无法在从库上完成回放。

业务读请求仍然访问从库，于是出现了以下现象：

  • 查询不到刚刚写入或更新的数据；
  • 读取到已经过期的业务状态；
  • 依赖状态推进的后续流程无法继续；
  • 上游不断重试，进一步放大系统压力；
  • 人工补偿和数据核对成本持续增加。

事故持续数小时，并最终造成资损。

二、一条 DELETE 为什么能堵住所有核心数据

1. 单线程复制就像一条单车道

当时的从库只有一个工作线程顺序应用事务，可以把它理解为一条不能超车的单车道。

假设 relay log 中的事务顺序如下：

事务 A：删除数千万条历史数据
事务 B：更新订单状态
事务 C：写入支付结果
事务 D：更新账户余额
事务 E：推进业务流程

事务 B 到 E 本身可能只修改一两行，正常执行时间只有几毫秒。但只要事务 A 没有完成，后面的事务就无法越过它。

这就是典型的队头阻塞：不是后续所有 SQL 都执行缓慢，而是一个极慢的操作占住了唯一处理通道。

flowchart LR D[千万级 DELETE] -->|长时间占用| S[唯一复制应用线程] C1[订单状态变更] --> Q[relay log 持续堆积] C2[支付结果] --> Q C3[账户变更] --> Q Q --> S S --> L[主从延迟扩大] L --> O[从库读取旧数据] O --> F[核心流程阻塞与资损]

2. 主库执行完成，不代表风险已经结束

很多人评估慢 SQL 时，只关注它在主库执行了多久。这对于数据修改语句是不够的。

一次大范围删除至少会经过以下环节：

主库扫描和删除
    ↓
生成 Undo、Redo 和 binlog
    ↓
binlog 传输到从库
    ↓
从库读取 relay log
    ↓
从库定位并删除对应数据
    ↓
后续事务继续回放

主库提交成功，只说明写入阶段结束了。复制链路仍需要承担传输、定位、删除和提交成本。当从库的回放速度低于主库产生 binlog 的速度时，延迟就会持续增长。

三、技术关键：从库为什么会出现低效回放

1. 先区分 STATEMENT 和 ROW

MySQL 的复制行为与 binlog_format 有关。

在 STATEMENT 模式下，binlog 记录原始 SQL，从库会重新执行删除语句。日期条件没有索引时，从库同样要进行大范围扫描。

在 ROW 模式下，binlog 记录被修改行的镜像。从库不会简单地重新执行原始 WHERE 条件，但它仍然需要在本地表中找到对应记录，然后完成删除。

对于 ROW 模式，MySQL 会优先使用主键或合适的唯一索引定位目标行。如果表缺少可用主键、唯一索引，或者行镜像无法覆盖可用索引，从库的行定位可能退化为普通索引扫描、哈希辅助扫描，甚至全表扫描。

目标数据达到数千万行时，这种定位成本会被迅速放大。

2. 一个需要避免的错误结论

复盘时不能简单写成：

DELETE 的 WHERE 条件没有索引，所以 ROW 模式下从库每删除一行都会全表扫描。

这句话并不严谨。ROW 模式下是否发生全表扫描，还要取决于：

  • 表中是否存在主键或合适的唯一索引；
  • binlog_row_image 的配置；
  • 行事件中是否包含可用于索引定位的列；
  • 从库实际选择的行搜索算法；
  • 表结构在主库和从库上是否一致。

根据本次事故现象可以确定的是：删除任务在从库回放阶段没有获得足够高效的定位和执行路径，导致复制应用速度远低于正常业务 binlog 的产生速度。

要把技术结论完全坐实，还应结合事故时的表结构、binlog 配置、relay log、慢日志和复制线程状态进行确认。

四、为什么有三台从库，业务仍然全部受影响

三台从库都接收同一个主库的 binlog，并采用相同的回放配置。危险删除进入复制链路后，三台从库会处理同一批变更，也就会遇到相同的性能问题。

这意味着它们提供的是实例数量上的冗余，而不是故障模式上的隔离。

如果三台从库同时具备以下特征，那么同一复制事件就可能击穿全部副本：

  • 接收完全相同的 binlog；
  • 使用相同的单线程回放模式；
  • 表结构和索引情况相同；
  • 全部承载实时业务查询；
  • 业务没有复制延迟下的读降级策略。

所以，“一主三从”并不天然等于高可用。副本数量只有与角色隔离、故障切换和业务降级结合起来，才能真正提升系统韧性。

五、根因不只是一条慢 SQL

把事故归因于“同事执行了一条错误 SQL”很容易，但这种结论无法阻止事故再次发生。

1. 直接原因

一次性删除数千万条数据，过滤字段缺少索引，并且没有分批提交，导致主库执行和从库回放都承担了远超日常事务的处理成本。

2. 放大因素

从库只有一个工作线程顺序回放事务，大删除长时间占住唯一应用通道，后续核心事务全部发生队头阻塞。

3. 架构问题

三台从库共享同一种故障模式；业务读链路又高度依赖从库，没有根据一致性要求准备切主读、限流、熔断或暂停流程等降级方案。

4. 流程问题

高风险数据变更缺少足够的执行前门禁：

  • 没有强制检查执行计划；
  • 没有估算扫描行数和影响行数；
  • 没有评估事务大小和 binlog 规模；
  • 没有确认目标表主键及相关索引；
  • 没有规定大数据删除必须分批；
  • 没有设置主从延迟的暂停阈值；
  • 没有明确等待、跳过或重建从库的决策标准。

5. 用“五个为什么”继续向下追

为什么核心业务流程被卡住？
因为业务从从库读取不到最新状态，后续流程无法确认前置操作已经完成。

为什么从库长时间没有最新状态？
因为复制应用线程被一个大批量删除任务长时间占用。

为什么删除任务回放这么慢？
因为删除规模达到数千万行，缺少高效的扫描或行定位路径，并且没有分批提交。

为什么高风险 SQL 能直接进入生产？
因为变更流程没有对无索引大范围 UPDATE/DELETE、影响行数和事务规模设置强制门禁。

为什么三台从库同时失去实时服务能力？
因为所有从库共享相同的复制事件和回放模式，业务也没有复制延迟下的读路由与降级能力。

因此，人的操作只是触发点，SQL 治理、表结构、复制架构、监控告警和业务降级多层防线的缺失，才决定了事故最终规模。

六、事故发生后应该如何止血

应急处置的目标不是让延迟数字尽快归零，而是在数据一致性、恢复时间和业务影响之间作出可解释的选择。

1. 先确认复制链路的真实状态

需要同时确认：

  • 主库删除事务是否已经提交；
  • 从库接收线程和应用线程是否正常；
  • 当前正在应用的事务及 relay log 位置；
  • relay log 的增长速度和实际回放速度；
  • 从库 CPU、磁盘、IOPS 和锁等待情况；
  • 哪些业务必须读取最新数据，哪些业务能够容忍延迟。

不能只看单一的延迟秒数。大事务执行期间，部分延迟指标可能长时间不刷新，也无法准确反映剩余工作量。

2. 停止继续增加复制压力

暂停同类清理任务和其他大事务，冻结非必要数据库变更，避免继续向已经拥堵的复制链路增加负载。

如果主库事务尚未提交，是否终止需要评估回滚成本。一个已经执行很久的大删除，即使执行 KILL，也可能进入漫长回滚，继续占用主库资源。

3. 优先恢复核心业务链路

可以根据一致性要求采取分级措施：

  • 将少量强一致核心查询临时切到主库，并设置严格流量上限；
  • 暂停报表和非核心查询，为关键流程释放资源；
  • 对可以容忍旧数据的功能明确降级；
  • 暂停依赖最新状态的任务消费，将请求放入可补偿队列；
  • 避免客户端和服务端进行无上限重试。

将全部读流量直接切到主库，可能把复制事故升级为主库事故，所以必须限流、分级并逐步切换。

4. 选择等待、跳过还是重建

方案 适用情况 主要风险
等待从库完成回放 剩余时间可以接受，一致性要求高 业务恢复较慢
优化资源后继续回放 瓶颈明确，能够安全提升回放速度 效果有限，操作不当会扩大故障
跳过事务并人工补数 删除允许暂缓，且有可靠补偿方案 主从立即不一致，后续校验复杂
从新快照重建从库 预计回放时间明显高于重建时间 占用主库、网络和存储资源

不能为了让监控恢复绿色而盲目跳过事务。跳过只改变复制位置，不会自动保证数据一致性。任何跳过操作都必须记录 GTID 或 binlog 位置、影响表、补偿方式和最终校验结果，并经过明确授权。

七、如何从根上避免同类事故

1. 在线业务表必须具备稳定主键

在 ROW 模式下，从库回放更新和删除事件时需要定位目标行。主键或合适的非空唯一索引，通常是最高效、最确定的定位路径。

建议对存量表进行扫描，识别无主键大表，并把“在线业务表必须有主键”加入建表规范和发布平台门禁。

2. 大数据删除必须拆成可观测的小批次

首先建立与清理条件匹配的索引，例如：

CREATE INDEX idx_business_date_id
ON business_history (business_date, id);

然后按小批次删除并提交：

DELETE FROM business_history
WHERE business_date < '2026-01-01'
ORDER BY business_date, id
LIMIT 2000;

任务循环执行时，每批之间适当让出资源，并持续观察：

  • 单批执行时间和影响行数；
  • 主库锁等待、磁盘和 IO；
  • binlog 增长速度；
  • 三台从库的复制延迟；
  • 核心业务错误率。

当延迟或数据库负载超过阈值时，清理任务应自动暂停。批次大小需要通过压测和低峰期验证确定，不能机械地认为 2000 适合所有表。

如果历史数据长期按时间淘汰，还应评估时间分区、归档表和冷热分层。可以通过数据生命周期设计解决的问题，不应长期依赖超大 DELETE。

3. 建立危险 SQL 强制门禁

生产环境中的大范围 UPDATE、DELETE 至少应检查：

  • 执行计划是否命中预期索引；
  • 预计扫描行数和影响行数；
  • 是否存在主键或合适的唯一索引；
  • 预计事务大小和 binlog 规模；
  • 是否支持分批、暂停和续跑；
  • 执行窗口、观察指标和停止条件；
  • 回滚、补偿和数据校验方案。

关键规则应该由数据库平台自动拦截，而不是只依赖执行人的经验和谨慎。

4. 评估并行复制，但不要把它当成万能解法

可以根据 MySQL 版本、硬件资源和事务依赖关系，评估启用或调优并行复制。MySQL 8.0.26 及以后使用 replica_parallel_workers，更早版本对应 slave_parallel_workers。

多线程复制可以并行回放相互独立的事务，减少普通事务之间的排队，但它不能把一个超大事务自动拆成许多小事务，也无法修复无主键、无索引带来的高回放成本。

合理的顺序应该是：先治理表结构和大事务，再通过压测选择合适的并行度。

5. 按业务一致性要求设计读策略

读写分离不能只有“读主库”和“读从库”两个全局开关。不同业务的要求并不相同：

  • 写入后必须立即读取的请求，可以在限定时间内读主库；
  • 支付、余额、状态机推进等操作，应采用更强的一致性策略；
  • 列表、报表和历史记录可以容忍一定延迟；
  • 延迟超过阈值时，非核心功能应限流、熔断或提示处理中。

这样才能避免一次复制延迟将所有业务流程同时拖住。

6. 完善复制监控和应急预案

除了常见的主从延迟，还应监控：

  • relay log 堆积量和增长速度；
  • 接收线程、协调线程和工作线程状态；
  • 长事务、大事务和异常 binlog 增长；
  • 从库 CPU、磁盘、IOPS 和锁等待；
  • 业务侧旧数据、状态不一致和重试指标。

预案中需要明确谁负责数据库判断、谁负责业务降级、什么条件下允许切主读、何时选择重建从库、何时可以跳过事务，以及恢复后如何完成数据校验和业务补偿。

八、这次事故带来的四点认识

1. 副本数量不等于可用性

如果所有副本处理同一份危险事件、使用同一种策略并承载同一类流量，它们就可能同时失效。高可用不仅是增加机器数量，还要隔离故障模式。

2. 慢 SQL 的影响不会止于主库

评审数据修改 SQL 时，不仅要看主库执行时间，还要考虑事务大小、binlog 规模、从库行定位方式和回放吞吐。

3. 大事务是复制系统的天然敌人

一个数千万行的大事务会同时消耗 Undo、Redo、binlog、网络、relay log 和复制线程。即使使用并行复制，单个超大事务仍然难以被拆分。最有效的办法通常不是继续堆资源，而是从源头拆小。

4. 不要把系统问题归结为个人粗心

如果一条明显高风险的 SQL 可以未经执行计划检查、影响评估和限速控制直接进入生产，那么系统本身就缺少必要护栏。人的操作是触发条件，流程和技术防线决定了事故规模。

九、总结

本次事故的完整链路可以概括为：

无索引的大范围删除进入主库 → 数千万行变更形成巨大复制负担 → 从库回放效率过低 → 唯一应用线程被长时间占用 → 后续核心事务队头阻塞 → 三台从库同时出现小时级延迟 → 业务读取旧数据并停滞 → 最终造成资损。

要避免同类事故再次发生，不能只留下一句“以后执行 SQL 要注意索引”，而应该同时建立四层防线：

  1. 数据层：补齐主键和索引，做好分区与数据生命周期治理；
  2. 执行层：大任务分批、可暂停、可续跑，达到延迟阈值自动熔断；
  3. 复制层：评估并行回放，做好副本角色隔离、监控和恢复预案；
  4. 业务层：按一致性要求设计读路由、限流、降级和补偿。

只有把危险 SQL 从“依赖个人经验”变成“依靠系统强制约束”，把复制延迟从“数据库指标”升级为“业务风险”，才能真正降低下一次事故发生的概率。

参考资料

posted @ 2026-09-17 10:03  松鼠航  阅读(37)  评论(0)    收藏  举报