调高innodb_autoinc_lock_mode可缓解insert锁争用，但mode=2会破坏id连续性且要求binlog_format=row；根本原因是批量插入（如insert select）在mode=1下持auto_inc表锁，导致并发阻塞；优化应优先拆分大insert、用insert on duplicate key update替代replace，并确保主键类型足够大。
直接改 innodb_autoinc_lock_mode 能显著缓解高并发 INSERT 的锁争用，但不是调成 2 就万事大吉——它会破坏自增 ID 连续性，且主从复制必须是 ROW 格式，否则可能丢数据或复制中断。
为什么 INSERT 语句会卡在 AUTO_INC 锁上
现象很典型：QPS 上不去、show processlist 里一堆 Waiting for table level lock、慢日志中单条 INSERT 耗时突增到几百毫秒。根本原因不是行锁或间隙锁，而是 InnoDB 对自增值分配过程的保护机制在起作用。
关键点在于：innodb_autoinc_lock_mode=1（默认）下，普通单行或多行 INSERT 不锁表，但只要语句里带 SELECT（比如 INSERT INTO t SELECT ... FROM s）、REPLACE SELECT 或 LOAD DATA，InnoDB 就会升级为语句级 AUTO_INC 表锁，直到整个语句执行完才释放。多个这样的语句并发，就互相堵死。
-
INSERT INTO user VALUES (NULL, 'a'), (NULL, 'b')→ 轻量互斥锁，快进快出 -
INSERT INTO user_backup SELECT * FROM user WHERE id > 1000→ 持有AUTO_INC表锁，期间所有其他 INSERT 都得等 - 哪怕只有一条
INSERT SELECT在跑，也能让整张表的插入吞吐掉一半以上
三种取值的真实行为差异
别被“0=保守、2=激进”这种说法带偏。每个值对应的是不同插入类型的锁策略组合，不是全局开关：
-
innodb_autoinc_lock_mode = 0：所有INSERT-LIKE语句（含单行INSERT）都走语句级AUTO_INC表锁；ID 绝对连续；仅适用于老架构 +BINLOG_FORMAT=STATEMENT+ 强制要求 ID 无空洞的场景 -
innodb_autoinc_lock_mode = 1（默认）：简单插入（Simple insert）用轻量互斥锁；批量插入（Bulk insert）仍用表锁；兼容性最好，但仍是性能瓶颈所在 -
innodb_autoinc_lock_mode = 2：所有插入都不加AUTO_INC表锁，改为预分配段（如一次拿 100 个 ID）；性能最高；但事务回滚后 ID 不回收，ID 必然不连续；强制要求BINLOG_FORMAT=ROW
注意：REPLACE 总是消耗新 ID（DELETE + INSERT），而 INSERT ON DUPLICATE KEY UPDATE 只有真正插入新行时才推进自增计数器——后者更省 ID，也更少触发锁竞争。
调整前必须验证的三件事
线上直接 SET GLOBAL innodb_autoinc_lock_mode = 2 是高危操作，可能立刻引发主从异常：
- 检查当前
BINLOG_FORMAT：运行SELECT @@binlog_format，如果不是ROW，先改配置文件并重启 MySQL，否则从库复制会失败 - 确认业务是否依赖自增 ID 连续性：比如导出 Excel 时按 ID 排序分页、前端用 ID 做列表锚点、审计日志按 ID 顺序校验——这些场景一旦出现空洞，逻辑可能出错
- 排查是否有
INSERT SELECT类语句混在事务中：这类语句在 mode=2 下虽不锁表，但因预分配机制，可能造成 ID 分配过快、耗尽 32 位 INT（尤其INT UNSIGNED最大值 4294967295）
真正有效的优化路径
参数调整只是最后一环。优先做这些实操动作，收益更大、风险更低：
- 把
INSERT INTO t SELECT ...拆成应用层分页：例如每次查 1000 行，用INSERT INTO t VALUES (),(),...批量插入；避免单语句扫描全表+持锁 - 用
INSERT ON DUPLICATE KEY UPDATE替代REPLACE，减少不必要的自增 ID 消耗和 DELETE 锁开销 - 对高频写入表，考虑将主键改为
BIGINT，避免 32 位自增溢出（尤其 mode=2 下 ID 分配更快） - 若已用
innodb_autoinc_lock_mode = 2，监控SHOW STATUS LIKE 'Innodb_autoinc_%%'中的Innodb_autoinc_wait_count是否归零——没归零说明还有隐式锁竞争残留
最常被忽略的一点：即使开了 mode=2，如果表上有多个唯一索引，INSERT ON DUPLICATE KEY UPDATE 的冲突检测顺序仍可能导致非预期的锁等待，这不是自增锁的问题，但会掩盖真正的瓶颈。
