mysql自增主键_关于MySQL自增主键的几点问题（上）

前段时间遇到一个InnoDB表自增锁导致的问题，最近刚好有一个同行网友也问到自增锁的疑问，所以抽空系统的总结一下，这两个问题下篇会有阐述。

1. 划分三种插入类型

这里区分一下几种插入数据行的类型，便于后面描述：(纯逻辑上的划分)

“Simple inserts”

简单插入，就是在处理sql语句的时候，能够提前预估到插入的行数，包括 INSERT / REPLACE 的单行、多行插入，但不含嵌套子查询以及 INSERT ... ON DUPLICATE KEY UPDATE。

“Bulk inserts”

本文暂且叫做 大块插入，不能提前预知语句要插入的行数，也就无法知道分配多少个自增值，包括 INSERT ... SELECT, REPLACE ... SELECT, 以及 LOAD DATA 导入语句。InnoDB会每处理一行记录就为 AUTO_INCREMENT 列分配一个值。

“Mixed-mode inserts”

混合插入，比如在 “简单插入” 多行记录的时候，有的新行有指定自增值，有的没有，所以获得最坏情况下需要插入的数量，然后一次性分配足够的auto_increment id。比如:

1

2

# c1 是 t1 的 AUTO_INCREMENT 列

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

又比如 INSERT ... ON DUPLICATE KEY UPDATE，它在 update 阶段有可能分配新的自增id，也可能不会。

2. 三种自增模式：innodb_autoinc_lock_mode

在以 5.6 版本，自增id累加模式分为：

传统模式

traditional，innodb_autoinc_lock_mode = 0

在具有 AUTO_INCREMENT 的表上，所有插入语句会获取一个特殊的表级锁 AUTO-INC ，这个表锁是在语句结束之后立即释放(无需等到事务结束)，它可以保证在一个insert里面的多行记录连续递增，也能保证多个insert并发情况下自增值是连续的(不会有空洞)。

连续模式

consecutive，innodb_autoinc_lock_mode = 1

MySQL 5.1.22开始，InnoDB提供了一种轻量级互斥的自增实现机制，在内存中会有一个互斥量(mutex)，每次分配自增长ID时，就通过估算插入的数量(前提是必须能够估算到插入的数量，否则还是使用传统模式)，然后更新mutex，下一个线程过来时从新 mutex 开始继续计算，这样就能避免传统模式非要等待每个都插入之后才能获取下一个，把“锁”降级到 只在分配id的时候 锁定互斥量。

在 innodb_autoinc_lock_mode = 1(默认) 模式下，“简单插入”采用上面的 mutex 方式，“大块插入”(insert/replace … select … 、load data…)依旧采用 AUTO-INC 表级锁方式。当然如果一个事务里已经持有表 AUTO-INC 锁，那么后续的简单插入也需要等待这个 AUTO-INC 锁释放。这能够保证任意insert并发情况下自增值是连续的。

交叉模式

interleaved，innodb_autoinc_lock_mode = 2

该模式下所有 INS