根本原因是innodb自增锁机制：innodb_autoinc_lock_mode=1下，insert...select等语句会持表级auto_inc锁；mode=2仅对simple insert有效，bulk/mixed插入仍卡顿，且需row格式binlog配合。
为什么批量INSERT卡在Waiting for table level lock
这不是行锁或间隙锁的问题，是InnoDB在分配自增值时被卡住了。现象很具体：QPS上不去、show processlist里一堆Waiting for table level lock、慢日志里单条INSERT耗时从几毫秒跳到几百毫秒甚至秒级。
根本原因在于默认的innodb_autoinc_lock_mode = 1：普通INSERT INTO t VALUES ()只加轻量互斥锁；但只要语句里带SELECT（比如INSERT INTO t SELECT * FROM s）、REPLACE SELECT或LOAD DATA，InnoDB就会升级为语句级AUTO_INC表锁，直到整条语句执行完才释放——哪怕只有一条这样的语句在跑，其他所有插入都得排队。
innodb_autoinc_lock_mode=2到底能关掉锁吗
能，但仅限于Simple inserts（如INSERT INTO t VALUES ()）。Bulk inserts（含SELECT）和Mixed-mode inserts（部分行显式指定自增值）在mode=2下依然退化为语句级锁，不会提速——所以开了innodb_autoinc_lock_mode = 2却还是卡，大概率是你写法本身就不走轻量路径。
mode=2的真实行为是“预分配段”，比如一次拿100个ID，事务回滚后这些ID不回收，导致主键空洞（如刚插完1001，下一条变成1057）。这不是bug，是设计取舍。空洞不影响查询或索引结构，但如果你业务里硬编码了“ID+1就是下一条”或用ID做分页推算，就会出问题。
- 必须确认
binlog_format = ROW，否则mode=2不可用 -
SET GLOBAL innodb_autoinc_lock_mode = 2可动态生效，但需重启客户端连接才感知；写入my.cnf更稳妥 - 该模式对
INSERT ... SELECT、REPLACE、INSERT IGNORE等都不起作用，别指望它们变快
批量提交+自增锁优化必须配合的硬条件
这两项单独调优效果有限，合起来用才出真性能，但极易踩坑：
- 主键乱序插入（如按时间戳倒序）会导致二级索引频繁分裂，吞掉一半提速收益；务必保证
VALUES中的主键值单调递增 - 没关非主键索引时，每插一条仍要更新所有索引树；建议导入前
ALTER TABLE t DISABLE KEYS，导入完ENABLE KEYS -
innodb_flush_log_at_trx_commit = 1（默认）下，即使批量提交，每次COMMIT仍强制刷盘；临时改成0可再提速30%，但断电会丢最近1秒事务 - 单个事务别塞超过5000条——InnoDB行锁升级阈值附近，否则容易触发锁升级或长事务阻塞
替代方案：什么时候该放弃自增主键
如果业务允许且数据源可控，直接用UUID或雪花ID（BIGINT）替代自增主键，能彻底绕开AUTO_INC锁机制。但这不是“优化”，而是架构层面的规避：
- 需要应用层生成ID，确保全局唯一、有序（如Snowflake）
- 二级索引会变宽（16字节UUID vs 8字节BIGINT），影响缓冲池利用率和查询性能
- 主键不再是紧凑的整数序列，范围扫描效率下降，
ORDER BY id可能变慢 - 如果已有大量依赖
LAST_INSERT_ID()的代码，改造成本高
真正容易被忽略的点是：innodb_autoinc_lock_mode=2 + 批量提交这套组合拳，只对“主键有序+无SELECT+无索引干扰+ROW格式binlog”的纯VALUES场景有效。现实中的ETL脚本、迁移工具、ORM批量写入，往往悄悄混入了ON DUPLICATE KEY UPDATE或子查询，结果参数调了也没用——先看执行计划，再动手改配置。
