虽然我们习惯于给主键ID指定AUTO_INCREMENT属性，但是AUTO_INCREMENT也是可以指定到非主键字段的，唯一的约束就是这个字段上面得加索引，有了索引，就可以通过类似SELECT MAX(ai_col)的语句快速读到这列数据的最大值。
本文要探讨的话题是MySql的InnoDB引擎处理自增数据列的原理

MySql 5.1之前的实现

在这个版本之前，用AUTO_INCREMENT修饰的数据列确实是严格连续自增的。MySql的实现是会针对每个插入语句加一个全表维度的锁，这个锁可以保证每次只有一条插入语句在执行，每插入一行数据，就会生成一个自增数据。

  1. mysql> CREATE TABLE t1 (
  2.     -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3.     -> c2 CHAR(1)
  4.     -> ) ENGINE=InnoDB AUTO_INCREMENT=100;

假如我们在数据库中新建上面的这张表，接着我们执行插入语句。

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

针对这条MySql执行的流程为：

  1. 全表加 AUTO-INC锁
  2. 1.1 生成主键ID：101
  3. 1.2 将行(101, 'a')插入表中
  4. 1.3 生成主键ID: 102
  5. 1.4 将行(102, 'b')插入表中
  6. ...
  7. 释放 AUTO-INC锁

MySql5.1之前的这种实现方式可以保证AUTO_INCREMENT严格自增，但是并发程度也最差，因为AUTO_INCREMENT锁是全表加锁直到这条语句结束

MySql 5.1版本带来的优化

前文中的insert语句是比较简单的，所谓简单的insert语句指的是插入的的数据行数是可以提前确定的，与之相对的是Bulk insert比如INSERT … SELECT这类语句，这类插入语句的插入行数不能提前确定。
在这个版本以及之后，对于简单语句的插入，不再加全表的AUTO-INC锁，只会在产生自增列数据的时候加一个轻量级的互斥锁，等自增数据分配好，锁就释放了，因此像上面的例子，在MySql5.1之后的执行流程如下

  1. 加轻量级互斥锁
  2. 分配自增数据
  3. 释放锁
  4. 将行(101, 'a')插入表中
  5. 将行(102, 'b')插入表中
  6. ...

可以看到，对于简单的插入语句，并发情况下的临界区变小了，且不再持有全表的锁，提升了并发性能。当然，如果在尝试加锁的过程中遇到有其他事务持有全表的AUTO-INC锁，还是要等待全表的AUTO-INC锁释放再执行本次插入操作
对于Bulk insert的插入语句，仍然避免不了全局的AUTO-INC锁，这类语句，他们的执行流程仍然保持和5.1之前版本一致，比如以下表为例

  1. CREATE TABLE t1 (
  2.   c1 INT(11) NOT NULL AUTO_INCREMENT,
  3.   c2 VARCHAR(10) DEFAULT NULL,
  4.   PRIMARY KEY (c1)
  5. ) ENGINE=InnoDB;

执行下面两条语句

  1. Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
  2. Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

由于在执行Tx1时，InnoDB无法知道要插入的具体行数，因此会获取一个全表的锁，每执行一条插入语句就会给自增列赋新的值。因为有全表的锁，所以Tx1这条语句插入的所有行数都是连续自增的，Tx2自增列的值要么小于Tx1自增列的最小值，要么大于Tx1自增列中的最大值，这取决于这两条语句的执行顺序
InnoDB采取这样的决策一个重要的原因是主从复制，在MySql8.0之前，MySql的主从是基于语句复制的。在刚才的例子中，如果Tx1执行的时候没有全表的锁，那有可能在Tx1执行的过程中Tx2也在执行，这就会导致Tx1和Tx2自增列的数据每次执行结果都不相同，也就无法在从库中通过语句回放复制。

MySql 8.0版本之后的优化

虽然MySql5.1版本对简单的插入语句做了优化，避免了全表加锁，但对于INSERT … SELECT这样的复杂插入语句，仍然避免不了全表的AUTO-INC锁，主要是基于执行语句的主从复制要能在从库完全回放复制主库，所有的语句执行结果就不能和执行顺序有关。
在MySql 8.0以及之后默认的主从复制策略变成了基于数据行实现，在这样的背景下INSERT … SELECT这样的复杂插入语句也不需要全表加锁来生成自增列数据了，所有的插入语句只有在生成自增列数据的时候要求持有一个轻量级的互斥锁，等到自增数据生成好之后释放锁。在这种实现下，所有插入语句的自增列都不能保证连续自增，但是并发性能确实最好的。

总结

需要说明的是，如果插入语句所处的事务回滚了，生成的自增列数据是不会回滚的，这种情况下会造成自增列数据非连续增长。
以上所述都是各个MySql版本的默认实现，MySql 5.1引入了一个新的参数 innodb_autoinc_lock_mode 通过修改这个字段的值，可以改变InnoDB生成自增列的策略，其值总结如下：
InnoDB 主健自增原理 - 图1
不推荐显式指定自增列数据，因为在5.7以及之前的版本，如果通过update语句显式指定一个比SELECT MAX(ai_col)还大的自增列值，后续insert语句可能会抛”Duplicate entry”错误，这一点在8.0版本之后也有了改变，如果通过显式的update语句显式指定一个比SELECT MAX(ai_col)还大的自增列值，那该值就会被持久化，后续的自增列值都从该值开始生成。
假如有下面这张表

  1. mysql> CREATE TABLE t1 (
  2.     -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  3.     -> c2 CHAR(1)
  4.     -> ) ENGINE = INNODB AUTO_INCREMENT=100;

试想，在我们执行完下面这条语句之后表的内容变成了什么？

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

MySql 5.1之前，或者innodb_autoinc_lock_mode设置为0

  1. mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
  2. +-----+------+
  3. | c1  | c2   |
  4. +-----+------+
  5. |   1 | a    |
  6. | 101 | b    |
  7. |   5 | c    |
  8. | 102 | d    |
  9. +-----+------+

在这种模式下，每插入一行数据就会生成一个自增值赋到c1这一行，因此c1的下一个自增值是103
MySql 8.0之前，或者innodb_autoinc_lock_mode设置为1

  1. mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
  2. +-----+------+
  3. | c1  | c2   |
  4. +-----+------+
  5. |   1 | a    |
  6. | 101 | b    |
  7. |   5 | c    |
  8. | 102 | d    |
  9. +-----+------+

当前表的数据与前一个场景一致，但是下一个自增值却是105，因为在这个场景下，自增数据是在插入语句执行的最开始一次性生成的
MySql 8.0之后，或者innodb_autoinc_lock_mode设置为2

  1. mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
  2. +-----+------+
  3. | c1  | c2   |
  4. +-----+------+
  5. |   1 | a    |
  6. |   x | b    |
  7. |   5 | c    |
  8. |   y | d    |
  9. +-----+------+

在这种场景下，因为同时可能有其他的插入语句执行，因此x和y的值是不确定的，下一个自增值也是未知的。

转自：https://www.toutiao.com/i6920048948153713156/?timestamp=1632608129&app=news_article&use_new_style=1&req_id=202109260615280102120400201373541A&share_token=175fdea9-d812-4685-a980-b4e47737346a&group_id=6920048948153713156