一次月度绩效核算批量插入从 10 分钟陡增至 13 分钟仍未完成。没有改过 SQL没有加过索引备份任务恰好在跑…… 但真相往往藏在最不起眼的参数里。一、现象熟悉的 SQL 突然“不认人”了每月月初业务老师会触发一次绩效核算底层逻辑是一条INSERT INTO ... SELECT的大批量插入语句将源表数据处理后写入目标表。过去一直稳定在10 分钟左右完成但这个月却跑了超过 13 分钟仍未结束。第一反应是“是不是刚好碰上备份任务在跑I/O 抢占了”——但直觉不能替代证据用数据说话。二、定位用 sys 快速“揪出”真凶 SQL既然目标表是target_table直接查询performance_schema中按 SQL 指纹汇总的统计信息找出耗时最长的相关语句SELECTDIGEST_TEXT,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAITFROMperformance_schema.events_statements_summary_by_digestWHEREDIGEST_TEXTLIKE%target_table%ORDERBYSUM_TIMER_WAITDESCLIMIT1;很快拿到了该 SQL 的DIGEST指纹标识随后便可以精确追踪它的执行细节。三、执行计划没毛病但“耗时”露了馅MySQL 8.0 提供的EXPLAIN ANALYZE不仅能展示预估计划还能真实输出每个阶段的实际执行耗时比传统EXPLAIN直观得多EXPLAINANALYZEINSERTINTOtarget_table(id,col1,col2,...)SELECTNULL,col1,col2,...FROMsource_tableWHERE...;结果让人困惑索引使用正常扫描行数合理但“插入”阶段耗时极高并且伴随大量锁等待提示。这说明瓶颈不在查询而在写入过程中的锁竞争。四、抓现行锁等待事件“一锤定音”借助sys.innodb_lock_waits视图一眼就能看到当前谁在等锁、等什么锁SELECT*FROMsys.innodb_lock_waits;输出中赫然出现了多个会话同时等待AUTO-INC表级锁。再配合performance_schema.data_locks确认对象正是target_table的自增主键索引。至此疑点聚焦于自增列AUTO_INCREMENT的锁机制。五、自增锁的三种模式MySQL 通过参数innodb_autoinc_lock_mode控制自增 ID 分配时的加锁策略它直接决定了批量插入的并发性能。模式值名称行为适用场景0传统模式traditional所有INSERT均持表级AUTO-INC锁直到语句结束兼容旧版本安全性最高但并发最差1连续模式consecutive简单插入行数确定用轻量互斥锁INSERT ... SELECT等批量插入仍退化表级锁MySQL 8.0 之前的默认值兼顾性能与安全2交错模式interleaved所有插入均使用互斥锁不再持有表级锁8.0 默认并发性能最佳但基于 STATEMENT 复制时需注意为什么INSERT ... SELECT在模式 1 下会退化因为 MySQL 在语句执行前无法预知 SELECT 会返回多少行为了保证连续分配且不与其它插入冲突只能采用表级锁来“独占”自增生成器直到整条语句执行完毕。用一张流程图来梳理排查过程0 或 12发现批量插入变慢用 sys 定位 SQL 指纹EXPLAIN ANALYZE 查看实际耗时发现插入阶段锁等待严重查询 sys.innodb_lock_waits锁定 AUTO-INC 表级锁检查 innodb_autoinc_lock_mode当前模式?瓶颈批量插入退化表级锁排查其他因素调整为模式 2压测验证 上线六、现场检查与调整登录数据库执行SHOWVARIABLESLIKEinnodb_autoinc_lock_mode;返回值为1——这正是症结所在。该实例是从 MySQL 5.7 升级而来保留了旧版默认值导致每月大批量插入时频繁发生表级锁争用。立即在线调整无需重启SETGLOBALinnodb_autoinc_lock_mode2;提醒如果binlog_format仍是STATEMENT交错模式可能造成主从数据不一致因为自增值分配顺序不可预测。但当前生产普遍使用ROW格式此风险可控。可通过SHOW VARIABLES LIKE binlog_format确认。七、压测验证数据说话在测试环境准备相同表结构和数据量对比两种模式下的插入耗时模式数据量耗时模式 1连续150 万行16 分 52 秒模式 2交错146 万行14 分 12 秒耗时缩短约10%~15%且在高并发下提升会更明显。生产环境应用调整后当月核算任务回归正常问题终结。八、反思与 takeaways备份 ≠ 元凶不要被同时运行的任务带偏监控数据才是唯一可靠的裁判。执行计划不变 ≠ 性能不变计划只反映“怎么查”不反映“怎么等”。锁等待往往藏在执行计划的“额外信息”之外。等待事件是排障的“第一性原理”sys.innodb_lock_waits能让你瞬间看到阻塞源比漫无目的地看系统指标高效得多。版本升级 ≠ 参数升级MySQL 8.0 虽然默认innodb_autoinc_lock_mode2但升级上来的实例会保留旧参数。务必主动检查尤其涉及大批量INSERT ... SELECT的业务。参数调整未必需要重启但需要评估复制影响在线SET GLOBAL即可生效只要确认 binlog 格式为 ROW便可放心切换。