innodb的auto_increment锁是“间隙锁+语句级锁”，非行级锁；myisam为表级锁。innodb使用auto-inc lock（轻量表级意向锁），语句结束即释放，但insert ... select等大事务会持锁至提交，导致后续自增分配阻塞。
MyISAM 的 AUTO_INCREMENT 是表级锁，InnoDB 是行级？错，其实是“间隙锁+语句级锁”
MyISAM 对 AUTO_INCREMENT 的处理简单粗暴：每次插入都对整张表加一个写锁，取最大值 +1，再解锁。并发插入时会排队，但不会出错。
InnoDB 完全不是行级锁控制自增——它用的是 auto-inc lock，一种特殊的、轻量的表级意向锁，只在执行 INSERT 语句期间持有，语句一结束就释放。这意味着：同一时刻只能有一个 INSERT（含批量）在分配自增值，但其他非插入操作（如 SELECT、UPDATE）完全不受影响。
常见错误现象：SHOW ENGINE INNODB STATUS 里看到 auto-inc lock 被长时间占用，其实是某条大事务里的 INSERT ... SELECT 或 LOAD DATA 没提交，卡住了后续所有自增分配。
- 使用场景：高并发单行插入（如日志写入）时，InnoDB 的
auto-inc lock实际比 MyISAM 表锁更轻；但遇到INSERT INTO t SELECT ...这类语句，InnoDB 会升级为持续到事务结束的锁，MyISAM 反而更“稳” -
innodb_autoinc_lock_mode参数决定行为：0（传统模式，全表锁）、1（默认，混合模式）、2（交错模式，无锁但可能不连续）。生产环境别乱调成 2，主从复制下STATEMENT格式会丢数据 - 性能影响：模式 1 下，普通
INSERT不锁表，吞吐高；但INSERT ... ON DUPLICATE KEY UPDATE仍会触发自增锁（哪怕没真正插入新行）
自增值不连续？InnoDB 和 MyISAM 都会，但原因完全不同
MyISAM 的不连续基本来自崩溃恢复：它把当前最大 AUTO_INCREMENT 值存在内存，重启后靠 SELECT MAX(id) 重新推算，如果上次是 INSERT ... SELECT 中断，就漏掉一批。
InnoDB 更隐蔽：事务回滚会导致已分配的自增值作废，且永不复用。比如事务 A 插入了 id=100，事务 B 插入了 id=101，A 回滚，B 提交，那下一个插入就是 id=102，100 永远空缺。
常见错误现象：监控发现自增列“跳号”严重，第一反应是“被黑了”或“配置错了”，其实只是业务里大量使用 INSERT ... SELECT + 回滚，或 ORM 自动生成了带条件的插入语句（如 INSERT IGNORE 后又手动 UPDATE）。
- MyISAM 无法避免重启后重算导致的跳跃；InnoDB 的跳跃是 ACID 的代价，不能关
-
ALTER TABLE ... AUTO_INCREMENT = N只能设成大于当前最大值的数，设小了会被忽略（5.7+ 直接报错） - 不要用自增值做“序号”或“版本号”，它不保证连续，也不保证单调递增（在并行插入+回滚下，后开始的事务可能先拿到更小的值）
INSERT ... ON DUPLICATE KEY UPDATE 为什么还会消耗自增值？
这条语句本质是“先尝试插入，冲突则更新”。但 InnoDB 在执行前，已经向自增计数器申请了一个新值——哪怕最终走的是 UPDATE 分支，这个值也已被占用并丢弃。
MyISAM 同样如此，而且更彻底：只要语句里有 INSERT 关键字，就一定触发自增锁并分配值。
常见错误现象：用该语句做“存在则更新，不存在则插入”的 upsert 场景，QPS 上去后发现自增值疯涨，表才几万行，id 已到百万级。
- 替代方案：先
SELECT判断是否存在，再决定用INSERT还是UPDATE（需加FOR UPDATE避免竞态），或改用REPLACE INTO（但会隐式删除再插入，触发两次自增） - InnoDB 8.0+ 支持
INSERT ... ON DUPLICATE KEY UPDATE的VALUES()函数可引用原值，但不改变自增分配逻辑 - 如果你的业务允许，把唯一约束建在业务字段上，用自然键代替自增 ID 做 upsert 主键，就能绕过这个问题
主从复制下 AUTO_INCREMENT 错位？重点盯 innodb_autoinc_lock_mode 和 binlog 格式
当主库用 STATEMENT 格式记录 binlog，且 innodb_autoinc_lock_mode = 2（交错模式）时，从库重放时自增值会和主库不一致——因为主库并发分配的顺序无法在从库还原。
这是 MySQL 官方明确不支持的组合，但很多人在调优时误开，线上出问题才查。
常见错误现象：主从 SHOW CREATE TABLE 看起来一样，但某天发现从库的 AUTO_INCREMENT 值比主库小几十，甚至主键冲突报错 ERROR 1062 (23000): Duplicate entry 'X' for key 'PRIMARY'。
- 安全组合只有两个：
STATEMENT + innodb_autoinc_lock_mode = 0 or 1，或ROW格式（推荐）+ 任意模式 -
mysqldump --single-transaction备份时，如果表里有AUTO_INCREMENT，dump 文件里会显式写AUTO_INCREMENT=N，但这个值只在CREATE TABLE时生效，不影响后续插入 - 跨库迁移或搭建新从库时，别直接
CREATE TABLE LIKE，要用SHOW CREATE TABLE手动核对AUTO_INCREMENT值是否合理
SELECT）和“完全卡死写入”串起来的机制——尤其当你在慢日志里看到一堆 Waiting for table level lock 却查不到 DDL 时，得翻翻是不是某条 INSERT ... SELECT 没提交。
