关于java:InnoDB自增原理都搞不清楚还怎么CRUD

历史文章举荐：

Lombok 的 Builder 注解不好用，试试 SuperBuilder 吧

Arthas 原理系列(五)：watch 命令的实现原理

Arthas 原理系列(四)：字节码插装让所有变得有可能

Arthas 原理系列(三)：服务端启动流程

Arthas 原理系列(二)：总体架构和我的项目入口

Arthas 原理系列(一)：实现一个极简的 Arthas watch 命令

尽管咱们习惯于给主键 ID 指定 AUTO_INCREMENT 属性，然而 AUTO_INCREMENT 也是能够指定到非主键字段的，惟一的束缚就是这个字段下面得加索引，有了索引，就能够通过相似 SELECT MAX(*ai_col*) 的语句疾速读到这列数据的最大值。

本文要探讨的话题是 MySql 的InnoDB引擎解决自增数据列的原理

MySql 5.1 之前的实现

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

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

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

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

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

1. 全表加 AUTO-INC锁

   1.1 生成主键 ID：101

   1.2 将行 (101, ‘a’) 插入表中

   1.3 生成主键 ID: 102

   1.4 将行 (102, ‘b’) 插入表中

   …

2. 开释 AUTO-INC锁

MySql5.1 之前的这种实现形式能够保障 AUTO_INCREMENT 严格自增，然而并发水平也最差，因为 AUTO_INCREMENT 锁是全表加锁直到这条语句完结

MySql 5.1 版本带来的优化

前文中的 insert 语句是比较简单的，所谓简略的 insert 语句指的是插入的的数据行数是能够提前确定的，与之绝对的是 Bulk insert 比方 INSERT ... SELECT 这类语句，这类插入语句的插入行数不能提前确定。

在这个版本以及之后，对于简略语句的插入，不再加全表的 AUTO-INC 锁，只会在产生自增列数据的时候加一个轻量级的互斥锁，等自增数据调配好，锁就开释了，因而像下面的例子，在MySql5.1 之后的执行流程如下

1. 加轻量级互斥锁

   1.1 调配自增数据

2. 开释锁
3. 将行 (101, ‘a’) 插入表中

4. 将行 (102, ‘b’) 插入表中

   …

能够看到，对于简略的插入语句，并发状况下的临界区变小了，且不再持有全表的锁，晋升了并发性能。当然，如果在尝试加锁的过程中遇到有其余事务持有全表的 AUTO-INC 锁，还是要期待全表的 AUTO-INC 锁开释再执行本次插入操作

对于 Bulk insert 的插入语句，依然防止不了全局的 AUTO-INC 锁，这类语句，他们的执行流程依然放弃和 5.1 之前版本统一，比方以下表为例

CREATE TABLE t1 (c1 INT(11) NOT NULL AUTO_INCREMENT,
  c2 VARCHAR(10) DEFAULT NULL,
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

执行上面两条语句

Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
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 生成自增列的策略，其值总结如下：

不举荐显式指定自增列数据，因为在 5.7 以及之前的版本，如果通过 update 语句显式指定一个比 SELECT MAX(*ai_col*) 还大的自增列值，后续 insert 语句可能会抛 ”Duplicate entry” 谬误，这一点在 8.0 版本之后也有了扭转，如果通过显式的 update 语句显式指定一个比 SELECT MAX(*ai_col*) 还大的自增列值，那该值就会被长久化，后续的自增列值都从该值开始生成。

如果有上面这张表

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

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

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

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

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

在这种模式下，每插入一行数据就会生成一个自增值赋到 c1 这一行，因而 c1 的下一个自增值是 103

MySql 8.0 之前，或者 innodb_autoinc_lock_mode 设置为 1

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
| 101 | b    |
|   5 | c    |
| 102 | d    |
+-----+------+

以后表的数据与前一个场景统一，然而下一个自增值却是 105，因为在这个场景下，自增数据是在插入语句执行的最开始一次性生成的

MySql 8.0 之后，或者 innodb_autoinc_lock_mode 设置为 2

mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
+-----+------+
| c1  | c2   |
+-----+------+
|   1 | a    |
|   x | b    |
|   5 | c    |
|   y | d    |
+-----+------+

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

欢送关注作者的公众号：

本文由博客一文多发平台 OpenWrite 公布！