慢 SQL、死锁、深分页与大表治理

“请求很慢”“锁等待”“提交失败”“迁移在线”都不是根因。本文把每个判断变成可采集的证据或阶段不变量：先找工作量，后解释等待，再定义恢复。索引结构与 ReadView 仅引用同主题权威正文，不重新讲一套。

基线 mysql/mysql-server · mysql-8.0.39 / InnoDB。2026-10-23用户提供本机服务，已用临时PyMySQL客户端实测 MySQL Community 8.4.7 的计划与双会话；Docker8.0.39镜像仍未运行。崩溃恢复、复制与在线DDL仍为协议/推演，不因锁实验通过而宣称完成。

用同一时间窗口解释慢

接口 P99 变坏不代表需要立刻加索引。一次数据库调用至少包括连接池等待、建连、服务端执行与锁等待、读取结果和客户端解码。slow log 看到的是服务端观测范围；应用埋点若从借连接前开始，二者本来就不会相等。先固定某条规范化 SQL、参数分布、调用频率、返回行数与时间窗口，再把延迟拆开，否则会把“连接池满”当成“表扫描”。

合成例子：每秒 1000 次查询，每次扫描 10 万条、返回 20 条，逻辑检查量约 1 亿条/秒；另一路每秒 100 次但每次发送 10MB，则网络输出约 1GB/秒。两者慢的瓶颈不同。优化前者可减少候选与回表；后者即使索引精确也需缩窄投影和分页。rows examined、rows sent、锁等待、CPU、读页、临时表和网络应在相同窗口对齐，不用累计计数除以单分钟请求量。

EXPLAIN 的行数是估算，EXPLAIN ANALYZE 会真正执行并给实际行数、loops 与时间。应由叶子往上定位误差：内表每次 5 行但循环 10 万次，成本是约 50 万行；不能只看最外层 LIMIT 返回 20 行。数据倾斜、联合列相关、统计信息过期会让计划估计失真。先拿慢参数而非随机参数复现，比较统计与实际，再决定刷新统计、改 SQL、改索引或改变数据模型。官方入口：EXPLAIN、统计估算。

FORCE INDEX 只是受控对照的工具。它可能在当前热点参数下好，在冷参数或数据量增长后差；全表扫描也可能比大量随机回表好。不要一次加多个索引而无法归因；记录新增索引的维护字节、写延迟、磁盘增长和副本追赶能力。优化结果必须同时保留原 SQL、改写 SQL、参数与执行计划，不能只有“降到 10ms”的单点故事。

收集顺序可落地为：observe.sql中的活动事务/锁边→应用连接池借出耗时→同参数慢查询→受控实际计划。没有锁边时仍可能是排队或I/O，不应为了凑解释强行说“死锁”。sys.innodb_lock_waits 是便利视图，底层 performance_schema.data_lock_waits 与 data_locks 才能补索引与记录线索；MDL 需要另看 metadata_locks。

OFFSET 为什么不能凭索引消失

固定 tag 的 composite_iterators.h 与 composite_iterators.cc 中 LimitOffsetIterator::Read 会在 offset 尚未消费时反复调用子迭代器的 Read 并丢弃结果。下面是教学改写：

教学伪代码

// 精简表达，省略错误码、计数统计与初始化分支。
while (skipped < offset) {
    if (source->Read() != 0) return END_OR_ERROR;
    ++skipped;
}
if (returned == limit) return END;
return source->Read(); // 实现还维护读数、limit 等状态

因此索引能让顺序满足 ORDER BY，仍不能随机跳到第 90001 个满足任意过滤条件的结果。先窄索引取主键再关联整行的延迟关联，可减少跳过部分的宽行回表，但仍要走过 offset；它是常数优化，不是阶数改变。

业务按 created_at DESC,id DESC 取下页，可以把上次最后一条作为严格边界：

SELECT id, created_at, amount
FROM study.orders
WHERE status='PAID'
  AND (created_at < '2025-06-01 12:00:00'
       OR (created_at='2025-06-01 12:00:00' AND id<50000))
ORDER BY created_at DESC,id DESC
LIMIT 20;

候选索引为 (status,created_at,id)；InnoDB 二级索引隐含主键后缀，不意味着所有显式排序都可凭直觉保证，应以计划核查。MySQL 行构造比较写法更短，但优化器能否充分利用范围须实测，因此实验保留展开条件。排序必须完整且唯一，timestamp 相同不加 id 会漏项；排序列若会被并发修改，keyset 也可能让记录移动到已翻过的页，不能承诺稳定快照。交互列表可以接受内容变化；审计导出需要明确截止水位或一致性快照，并承担长事务保留 undo 的代价。来源：行构造优化、LIMIT。

原码：OFFSET要不断从下游读取并丢弃

原码连续摘录（仅统一公共缩进，截取方法局部）：composite_iterators.cc，mysql-8.0.39，L115–L142。

原码

int LimitOffsetIterator::Read() {
  if (m_seen_rows >= m_limit) {
    // We either have hit our LIMIT, or we need to skip OFFSET rows.
    // Check which one.
    if (m_needs_offset) {
      // We skip OFFSET rows here and not in Init(), since performance schema
      // batch mode may not be set up by the executor before the first Read().
      // This makes sure that
      //
      //   a) we get the performance benefits of batch mode even when reading
      //      OFFSET rows, and
      //   b) we don't inadvertedly enable batch mode (e.g. through the
      //      NestedLoopIterator) during Init(), since the executor may not
      //      be ready to _disable_ it if it gets an error before first Read().
      for (ha_rows row_idx = 0; row_idx < m_offset; ++row_idx) {
        int err = m_source->Read();
        if (err != 0) {
          // Note that we'll go back into this loop if Init() is called again,
          // and return the same error/EOF status.
          return err;
        }
        if (m_skipped_rows != nullptr) {
          ++*m_skipped_rows;
        }
        m_source->UnlockRow();
      }
      m_seen_rows = m_offset;
      m_needs_offset = false;

循环次数由m_offset决定，每次都调用m_source->Read，失败/EOF立即返回；成功则记录跳过行并UnlockRow。索引可以让下游读取更便宜，却没有把此处循环次数变成常数。后面的m_seen_rows/m_needs_offset切换保证这段跳过只在需要时进行；keyset分页应从访问条件上减少需跳过的候选。

延迟关联示例保留“页内返回20行”与“跳过90000个窄结果”两种成本：

SELECT o.id,o.user_id,o.note
FROM study.orders o
JOIN (SELECT id FROM study.orders ORDER BY id LIMIT 90000,20) p ON p.id=o.id
ORDER BY o.id;

该例按主键读取，InnoDB 的主键叶本身已是整行，未必得到预期的窄二级优势；要观察减少宽行读取，使用满足过滤/排序的二级索引做受控对照。不要把“先取id”当作任何查询都有效的秘诀。Keyset 与 offset 的结果比较还须在相同数据状态下进行。

死锁不是某条 SQL 太慢

两个事务以相反顺序更新账户：A 拿到账户 1 再等 2，B 拿到 2 再等 1，等待图成环。单条 SQL 也可能扫描多个索引记录形成死锁；应用代码只写一条语句并不能排除循环等待。二级索引与聚簇记录、唯一键检查、插入间隙都可能参与，所以复盘应保留事务中此前所有 SQL，而不是只摘错误那条。

固定 tag 的 lock0wait.cc 中 lock_wait_find_and_handle_deadlocks 构造等待关系并搜索环，验证候选时还检查事务槽和等待状态，防止把已结束等待的陈旧图当作当前死锁；lock_wait_choose_victim / lock_wait_handle_deadlock 选择与处理牺牲者。不能依赖“总是最后一条 SQL 回滚”，牺牲者选择涉及事务代价等实现因素，业务必须接受任何一方被重试。

执行实验按以下顺序：A BEGIN 更新 id=1；B BEGIN 更新 id=2；A 更新 id=2 并等待；B 更新 id=1。预期一个连接收到 1213，另一个继续。两个连接最终都显式 ROLLBACK，保留 SHOW ENGINE INNODB STATUS\G 最近死锁。观察连接在成环前执行 performance_schema.data_lock_waits 联查 data_locks，记录索引、LOCK_MODE、LOCK_DATA、请求与阻塞事务。这里使用额外观察连接，制造竞争仍只有 A/B。

超时 1205 与死锁 1213 不同：默认锁等待超时通常回滚当前语句，死锁会回滚牺牲者整个事务；innodb_rollback_on_timeout 改变超时回滚范围。调用方遇到任一错误应先清理事务上下文，再按业务协议有界重试。把余额扣减与非幂等 HTTP 付款混在事务回调里，数据库可回滚但付款无法自动撤回。依据：死锁处理、错误处理。

治理优先级是统一加锁顺序、缩短持锁时间、用合适索引减少访问集合，并拆开外部慢调用；降低隔离级别只能减少某些锁，不会消除相反顺序的写写死锁。关闭检测可减少极端热点检测开销，却把环的恢复交给超时，必须量化等待与重试放大，不能当通用开关。对队列领取用 SKIP LOCKED 可能提高吞吐，但返回的是不完整视图，要有租约超时和回收；它不适合余额汇总与严格公平处理。

原码：处理已验证的环与牺牲者

原码连续摘录，仅截取局部、未改写：mysql/mysql-server · mysql-8.0.39 · storage/innobase/lock/lock0wait.cc · L1133–L1148。

原码

static void lock_wait_handle_deadlock(
    trx_t *chosen_victim, const ut::vector<uint> &cycle_ids,
    const ut::vector<waiting_trx_info_t> &infos,
    ut::vector<trx_schedule_weight_t> &new_weights) {
  /*  We now update the `schedule_weight`s on the cycle taking into account that
  chosen_victim will be rolled back.
  This is mostly for "correctness" as the impact on performance is negligible
  (actually it looks like it is slowing us down). */
  lock_wait_update_weights_on_cycle(chosen_victim, cycle_ids, infos,
                                    new_weights);

  lock_notify_about_deadlock(
      lock_wait_trxs_rotated_for_notification(cycle_ids, infos), chosen_victim);

  lock_wait_rollback_deadlock_victim(chosen_victim);
}

lock_wait_handle_deadlock 只接收上游验证仍在等待的环；它更新调度权重、通知并回滚选出的受害事务。若以陈旧观察图直接调用回滚，可能错误终止已不再等待的事务；上游验证的槽号/等待状态同样重要。受害者选择还考虑权重与优先级，不承诺“总杀B”。该方法没有实现应用重试，所以业务仍须清理并重建完整事务。

恢复策略的伪流程是 捕获1213/1205 → 显式回滚/释放连接状态 → 校验总时限 → 退避 → 以同一业务键重建完整事务。不能在旧连接未回滚时重跑最后一条，也不能换业务键重复扣款。受害者是运行时选择，测试应断言“恰好一个受害事务、最终不变量成立”，不硬编码A必死。

本机等待环（8.4.7）

实际先观察到A请求账户2的 X,REC_NOT_GAP 被B阻塞，再由B请求账户1闭环；本次B收到1213、A继续成功，二者随后显式ROLLBACK。证据保留本实验schema的最近死锁段与两方结果；不依赖每次都是B受害，也未改全局deadlock配置。服务观察值为检测开启、rollback_on_timeout=0。

该运行没有制造1205、提交断线、磁盘同步失败或故障切换，因此这些错误的恢复边界仍来自固定源码/手册与推演；不能用本次1213替代其验证。

undo、redo、binlog：提交与恢复的不同证据

undo 重建旧版本并支持回滚；redo 让崩溃后把持久页恢复到已记录的修改状态；binlog 是服务器层事务日志，用于复制与时间点恢复。不能用“有 binlog”推出内存页已落盘，也不能用“有 redo”替代业务备份。WAL 的关键是先保证相应日志耐久，再允许脏页持久化，而不是每提交就刷所有数据页。

固定 tag 的 binlog.cc 中 MYSQL_BIN_LOG::commit 接入 ordered_commit；group commit 分 flush、sync、commit 等阶段，process_flush_stage_queue 写入组内日志，sync_binlog_file 根据配置同步，process_commit_stage_queue 推进引擎提交。内部 2PC 的教学状态为：引擎 prepare → 事务 binlog 完成并按策略持久化 → 引擎 commit。恢复时可根据已准备事务与完整 binlog XID 的交集决定提交/回滚，不把不完整尾部视为成功事务。完整恢复还涉及 binlog 扫描和引擎恢复回调，不能把这三行伪时序当作所有错误路径。

innodb_flush_log_at_trx_commit=1 与 sync_binlog=1 是讨论正常提交耐久性的常见前提，仍需存储正确兑现 fsync、设备与文件系统故障模型；配置为其他值会放大进程崩溃/掉电下的窗口。group commit 可摊薄同步次数，不意味着每个事务从此不需要持久化。依据：binary log、InnoDB 启动与恢复。

客户端断线有三个可能位置：服务端尚未提交、已经提交但回复丢失、正在提交且结果未知。客户端错误不证明回滚。以 request_id 唯一约束写入业务流水，超时后查该键并按原始请求内容核对；不同请求重用同键必须拒绝，不能返回旧成功掩盖冲突。涉及外部支付时，数据库 outbox 与状态机把确认和补偿责任明确下来。

复制还多一条轴：接收 binlog、写 relay log、应用事务、供读可见、允许被提升，均不是同一事件。GTID 标识事务并有助于对齐历史，不会自己防止旧主继续写；异步主从不能无条件承诺已确认写零丢失。半同步也要注明等待点与副本确认阶段，收到日志不等于副本查询已看见。来源：半同步。

原码：同步策略的分支与明确错误

原码连续摘录，仅截取局部、未改写：mysql/mysql-server · mysql-8.0.39 · sql/binlog.cc · L8640–L8661。

原码

std::pair<bool, bool> MYSQL_BIN_LOG::sync_binlog_file(bool force) {
  bool synced = false;
  unsigned int sync_period = get_sync_period();
  if (force || (sync_period && ++sync_counter >= sync_period)) {
    sync_counter = 0;

    /*
      There is a chance that binlog file could be closed by 'RESET MASTER' or
      or 'FLUSH LOGS' just after the leader releases LOCK_log and before it
      acquires LOCK_sync log. So it should check if m_binlog_file is opened.
    */
    if (DBUG_EVALUATE_IF("simulate_error_during_sync_binlog_file", 1,
                         m_binlog_file->is_open() && m_binlog_file->sync())) {
      THD *thd = current_thd;
      thd->commit_error = THD::CE_SYNC_ERROR;
      return std::make_pair(true, synced);
    }
    synced = true;
  }
  return std::make_pair(false, synced);
}

sync_period 与计数决定本组是否调用文件 sync；force 覆盖周期策略。同步失败设置 CE_SYNC_ERROR 并返回错误标记，成功才标记 synced。删掉错误检测会把日志未达到约定耐久阶段的请求误称完成；但本函数的错误也不能反推引擎一定回滚，后续错误策略、恢复与客户端确认仍有独立状态。这里只摘 sync_binlog_file，没有用这22行冒充完整内部2PC实现。

内部2PC保证 binlog 与引擎的恢复协同，不把网络客户端纳入原子域。客户端提交超时需通过唯一请求键查询已提交事实；如果故障切换后新主可能缺失该历史，“查不到”也不能在外部支付中直接认定旧操作从未发生。应结合副本水位/旧主隔离、支付流水与对账。

大表迁移：把在线改写成阶段不变量

原生 DDL 分 INSTANT、INPLACE、COPY，不等于“秒改”“完全无锁”“全程只读”三种简单标签。算法支持取决于具体操作和表条件；INPLACE 也可能重建表并产生大量 I/O，起止阶段需要 metadata lock。长事务持有 MDL 时，排队中的 ALTER 又可能影响后续请求。先明确 ALGORITHM / LOCK 让不支持的条件失败，而不是静默退化为意外长操作。来源：在线 DDL 操作矩阵、metadata locking。

假设 5 亿订单、逻辑每行 400B，至少约 200GB 原始记录，不含索引与副本。要求加新金额字段、从分转为定点元，不能简单在线改列然后把所有旧值当新单位。采用 expand/backfill/verify/switch/contract：

1. Expand：加可空新列或新表，旧读仍工作；新应用定义双字段兼容读与写事务边界。
2. Backfill：按稳定主键范围分批，每批小事务；仅填尚未迁移且源版本仍匹配的记录。更新了源金额的在线事务同时维护新表示，否则回填可把新值覆盖。
3. Verify：核对主键覆盖、版本、金额转换和业务分布；count 相等不能发现互换两行。记录范围 checkpoint 与失败批，避免失败后全表重跑。
4. Switch：先让读流量分批转向新列/新表，持续对账；读开关与写权威切换分开记录。
5. Contract：旧应用完全退出且回滚窗口结束后才删除旧结构；如果新语义已无法映射回旧模型，回滚应前向修复而不是直接切回。

这五步有实际竞争反例：回填先读到 v=3 金额 100，在线更新到 v=4 金额 200，回填再写 100。正确方法是 UPDATE 带 WHERE id=? AND version=3 AND new_amount IS NULL，不命中就重新读取/跳过，或者所有写路径一致维护目标。回填发号 checkpoint 不能替代业务版本条件，因为 checkpoint 只表明扫描位置，不证明这行未被并发更新。

假定给回填 2000 行/秒，5 亿行理论至少 250000 秒，约 69.4 小时；实际还受重试、校验和流量限速影响。每批 1000 行约 0.5 秒只是目标预算，不是承诺。若主库或副本出现延迟、磁盘水位或线上 P99 超阈值，停止推进新批次并保留断点，不能靠固定 sleep 盲跑。对照应用双写方案与日志驱动影子表：前者侵入代码但可在同库事务维持一致，后者改动应用少却依赖日志保留、重放、DDL 与切换支持；选型必须讲删除和回滚。

把版本保护写成可检查的更新（假设已增加 new_amount 与 version）：

UPDATE orders
SET new_amount = CAST(:old_cents AS DECIMAL(18,2))/100
WHERE id=:id AND version=:observed_version AND new_amount IS NULL;
-- 立即检查影响行数，0行重读源状态，不盲目将checkpoint当作这一行完成。

命中并不说明之后永不变化；所有线上写仍须同步维护目标字段，或验证依赖的版本继续追增量。分片校验应在同一逻辑水位比较id、版本、金额/状态及删除，配合抽样或分桶校验；跨水位 checksum 不同可能只是更新尚未追齐，不能直接判断丢数。DDL 的 ALGORITHM=INSTANT 只处理支持的元数据变化，不能完成字段单位转换。

排障实战：先找证据再选修复

合成事故前提：订单列表P99目标200ms，流量1000req/s，某租户占60%，实际P99=2s。以下数字是训练输入，不是本机测量。

证据	可解释的根因	受控验证与反例
借连接耗时1.6s，数据库执行40ms	池排队/持有连接过久	查连接占用与事务年龄；直接加索引未必改善
无锁边，actual扫描90020、返回20	offset工作量	同数据keyset；仍需接受不能任意跳页
单次内表5行、loops100000	连接放大	改连接前过滤/聚合，对照结果不变；不是只改key名
发现A等B且B等A，1213	等待环	固定加锁顺序、短事务、完整重试；改RC不消除环
ALTER等待，后续新查询也排队	MDL排队链	查metadata_locks和持有事务；不可把data_locks空判无锁
主库快、读副本旧	应用水位滞后	关键读主库或等指定事务；随机sleep不是保证

首轮修复只改一个变量，保留慢参数与热/冷分布；同窗比较读P99、写P99、扫描/回表、锁等待、索引空间与副本积压。第二轮加10倍写入，如果覆盖索引导致写P99超标，应重审窄索引与异步报表；不能沿用第一次推荐。

主问题“慢 SQL 如何处理”的口述答案：先区分连接池、执行、锁和传输，再固定参数读实际计划，比较估算与真实候选、loops、排序和回表；按根因一次变更，验证读收益和写代价，异常时能回滚，并给出没有实测的部分。主答 4 分，定位范围、实际证据、改进依据、写风险各 1 分。

第一轮：计划命中索引为什么仍慢？低选择性、深 offset、大量回表、冷热缓存和锁等待都可能；给慢参数与计划对照，而不是只说“索引失效”。2 分：至少一条可检验假设 1 分，对应证据 1 分。

第二轮：死锁改 RC 就好了？相反顺序写写环仍在；统一顺序、短事务、完整重试。2 分：等待图 1 分，重试边界 1 分。

第三轮：迁移时业务金额更新与回填相撞怎么办？用源版本 CAS / 事务双写，失败行重读，切换前按范围和业务不变量核对。2 分：竞争时序 1 分，恢复闭环 1 分。总分 8 以上且不把错误响应当回滚，达到高级要求。

架构师条件变化

从允许停写 30 分钟改为不停写：离线拷贝变成持续增量与切换水位问题，单纯加回填线程无法解决。可接受 10 秒旧读改为付款读己之写：把关键查询路由主库或等待指定事务水位，计入等待超时与降级，不靠随机睡眠。容灾从进程故障改为机房整体损失：本机 fsync 与单机备份不够，还需异地副本、备份恢复演练、选主隔离和业务对账；明确哪些已确认事务受保护。

验收用表格记录 RPO/RTO、峰值写速率、回填速率、副本积压与回滚开关，给出至少两条迁移路线及停止条件。专家可选继续追 internal XA 的崩溃注入；架构师必须先把不变量、流量与恢复责任讲清楚。

闭卷验收

本页 8 道闭卷题收进了下方自测面板：先回忆，再展开参考答案与评分点，最后给三档自评。