【数据库】tdsql（mysql8.0）慢sql优化思考二
一次月度绩效核算批量插入，从 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 会返回多少行，为了保证连续分配且不与其它插入冲突，只能采用表级锁来“独占”自增生成器，直到整条语句执行完毕。
用一张流程图来梳理排查过程：
六、现场检查与调整
登录数据库，执行：
SHOWVARIABLESLIKE'innodb_autoinc_lock_mode';返回值为1——这正是症结所在。该实例是从 MySQL 5.7 升级而来，保留了旧版默认值，导致每月大批量插入时频繁发生表级锁争用。
立即在线调整（无需重启）：
SETGLOBALinnodb_autoinc_lock_mode=2;提醒：如果
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_mode=2，但升级上来的实例会保留旧参数。务必主动检查，尤其涉及大批量INSERT ... SELECT的业务。参数调整未必需要重启，但需要评估复制影响
在线SET GLOBAL即可生效，只要确认 binlog 格式为 ROW，便可放心切换。