浅析MySQL存储引擎序列属性

vlambda
2020-09-08

浅析MySQL存储引擎序列属性

墨墨导读：为了达到标识的目的，许多应用程序需要生成唯一编号，比如：商品编号、交易流水号等。MySQL数据库同样能够支持这样的需求场景，AUTO_INCREMENT就是为MySQL实现序列的方式，它会自动生成序列编号。

一、MySQL序列概述

为了达到标识的目的，许多应用程序需要生成唯一编号，比如：商品编号、交易流水号等。MySQL数据库同样能够支持这样的需求场景，AUTO_INCREMENT就是为MySQL实现序列的方式，它会自动生成序列编号。但是它的使用是有要求的，比如：

每个表只能有一个列具备AUTO_INCREMENT属性，并且为整数型
AUTO_INCREMENT列不能包含NULL值（MySQL会自动设置为NOT NULL）
AUTO_INCREMENT列上必选要有索引，常见为primary key和unique index

二、MyISAM存储引擎

MyISAM存储引擎是支持AUTO_INCREMENT属性的，它有如下几个特点：

单调递增，每次序列使用完后，下一个序列将从当前最大序列开始。每次序列值都会存在数据文件中，因此当服务重启后，依旧可以进行序列递增。

备注：两种情况比较特殊，第一种是使用truncate 后，序列将重新开始。第二种，如果是使用复合索引产生的多个序列，那么这些序列将会被重用。

mysql> CREATE TABLE myisamdemo1 ( -> id int unsigned not null auto_increment, -> city varchar(10) NOT NULL, -> name varchar(30) NOT NULL, -> primary key(city,id)) ENGINE = MyISAM;Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO myisamdemo1 (city,name) VALUES ('GuiYang','albert');Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO myisamdemo1 (city,name) VALUES ('GuiYang','tony');Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO myisamdemo1 (city,name) VALUES ('ChenDu','jake');Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO myisamdemo1 (city,name) VALUES ('ShangHai','Jessica');Query OK, 1 row affected (0.00 sec)
mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 2 | GuiYang | tony || 1 | ChenDu | jake || 1 | ShangHai | Jessica |+----+----------+---------+4 rows in set (0.00 sec)

如果使用了这种复合索引来创建多个序列，那么从各个序列顶端的值，也是可以进行复用的。

Query OK, 1 row affected (0.00 sec)

mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 1 | ChenDu | jake || 1 | ShangHai | Jessica |+----+----------+---------+3 rows in set (0.00 sec)
mysql> INSERT INTO myisamdemo1 (city,name) VALUES ('XiAn','368');Query OK, 1 row affected (0.00 sec)
mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 1 | ChenDu | jake || 1 | ShangHai | Jessica || 1 | XiAn | 368 |+----+----------+---------+4 rows in set (0.00 sec)
mysql> INSERT INTO myisamdemo1 (city,name) VALUES ('GuiYang','Kevin');Query OK, 1 row affected (0.00 sec)
mysql> select * from myisamdemo1;+----+----------+---------+| id | city | name |+----+----------+---------+| 1 | GuiYang | albert || 2 | GuiYang | Kevin || 1 | ChenDu | jake || 1 | ShangHai | Jessica || 1 | XiAn | 368 |+----+----------+---------+5 rows in set (0.00 sec)

默认序列从1开始，当然也可以在建表中指定起始序列号

 -> id int unsigned not null auto_increment, -> nname varchar(30) NOT NULL, -> primary key(id)) ENGINE = MyISAM auto_increment=100;Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO myisamdemo (nname) VALUES ('Albert');Query OK, 1 row affected (0.00 sec)
mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert |+-----+--------+1 row in set (0.00 sec)
mysql> INSERT INTO myisamdemo (nname) VALUES ('tony');Query OK, 1 row affected (0.01 sec)
mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 101 | tony |+-----+--------+2 rows in set (0.00 sec)

可以通过alter table来修改已存在的序列号。
可以调整下一个序列的起始值，比如：

mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 101 | tony |+-----+--------+2 rows in set (0.00 sec)
mysql> alter table myisamdemo auto_increment=200;Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO myisamdemo (nname) VALUES ('sunny');Query OK, 1 row affected (0.00 sec)
mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 101 | tony || 200 | nike |+-----+--------+3 rows in set (0.00 sec)

还可以重用顶端的序列，比如当前最大的序列为202，删除后通过指定起始值，是可以重新使用该序列。但是需要注意的是，不能在序列包含里面，比如100至201之间。

mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 200 | nike || 201 | jake || 202 | harry |+-----+--------+4 rows in set (0.00 sec)
mysql> delete from myisamdemo where id=202;Query OK, 1 row affected (0.00 sec)
mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 200 | nike || 201 | jake |+-----+--------+3 rows in set (0.00 sec)
mysql> alter table myisamdemo auto_increment=201;Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO myisamdemo (nname) VALUES ('sunny');Query OK, 1 row affected (0.00 sec)
mysql> select * from myisamdemo;+-----+--------+| id | nname |+-----+--------+| 100 | Albert || 200 | nike || 201 | jake || 202 | sunny |+-----+--------+4 rows in set (0.00 sec)

三、MEMORY存储引擎

MEMORY存储引擎同样是支持AUTO_INCREMENT属性的：

默认序列从1开始，当然也可以在建表中指定起始序列号

mysql> CREATE TABLE memorydemo ( -> id int unsigned not null auto_increment, -> name varchar(30) NOT NULL, -> primary key(id)) ENGINE = memory auto_increment=100;Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO memorydemo (name) VALUES ('tony');Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO memorydemo (name) VALUES ('Tommy');Query OK, 1 row affected (0.00 sec)
mysql> select * from memorydemo;+-----+-------+| id | name |+-----+-------+| 100 | tony || 101 | Tommy |+-----+-------+2 rows in set (0.00 sec)

可以通过alter table 调整调整初始序列

mysql> select * from memorydemo;+-----+-------+| id | name |+-----+-------+| 100 | tony || 101 | Tommy |+-----+-------+2 rows in set (0.00 sec)
mysql> alter table memorydemo auto_increment=400;Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0
mysql>mysql> INSERT INTO memorydemo (name) VALUES ('Jessica');Query OK, 1 row affected (0.00 sec)
mysql> select * from memorydemo;+-----+---------+| id | name |+-----+---------+| 100 | tony || 101 | Tommy || 400 | Jessica |+-----+---------+3 rows in set (0.00 sec)

顶端序列被删除后，不能被重用

+-----+---------+| id | name |+-----+---------+| 100 | tony || 101 | Tommy || 400 | Jessica |+-----+---------+3 rows in set (0.00 sec)
mysql> delete from memorydemo where id=400;Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO memorydemo (name) VALUES ('marry');Query OK, 1 row affected (0.00 sec)
mysql> select * from memorydemo;+-----+-------+| id | name |+-----+-------+| 100 | tony || 101 | Tommy || 401 | marry |+-----+-------+3 rows in set (0.00 sec)

不能使用复合索引生成多个序列，因为有且只能定义一个主键列

四、INNODB存储引擎

关于InnoDB的AUTO_INCREMENT属性，在文章《删除行对MySQL序列有什么影响呢？》有提及，下面简单回顾一下。

顶端序列被删除后，不能被重用
可以通过alter table 调整调整初始序列
默认序列从1开始，当然也可以在建表中指定起始序列号
如果生成AUTO_INCREMENT序列的事务回滚，那么序列会发生断层不连续
不能使用复合索引生成多个独立的序列

备注：MySQL 5.7 版本之前在内存中存储 AUTO_INCREMENT 计数器，实例重启后会根据表中的数据重新设置，在删除记录后重启就可能出现重复的主键，该问题在 8.0 版本使用重做日志解决，保证了主键的单调性。

这里分享的是InnoDB的AUTO_INCREMENT涉及到一个特殊的锁：自增锁（即：AUTO-INC lock）它属于表锁的一种，在insert结束后立即释放。

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.
The innodb_autoinc_lock_mode configuration option controls the algorithm used for auto-increment locking. It allows you to choose how to trade off between predictable sequences of auto-increment values and maximum concurrency for insert operations.

锁模式中LOCK_AUTO_INC，即auto_increment的表锁

/* Basic lock modes */enum lock_mode { LOCK_IS = 0, /* intention shared */ LOCK_IX, /* intention exclusive */ LOCK_S, /* shared */ LOCK_X, /* exclusive */ LOCK_AUTO_INC, /* locks the auto-inc counter of a table in an exclusive mode */ LOCK_NONE, /* this is used elsewhere to note consistent read */ LOCK_NUM = LOCK_NONE/* number of lock modes */};

这里涉及到innodb_autoinc_lock_mode这个参数，它用于控制获取自增值的加锁方式

innodb_autoinc_lock_mode,它有0、1、2三个值。0表示traditional（旧的表锁方式），1表示consecutive（连续的，对于bulk insert，使用传统的auto-inc锁），2表示interleaved（交替式，一般最大性能最大化可以使用row-based）
每一个statement获取一个排他lock，直到statement结束，保证statement执行过程的id是连续的。
单条确定insert影响的条数的时候，使用mutex。如果是insert select，load data这样的，使用排他lock。
多条statement产生的id会穿插在一起，如果是statement复制，会产生不一致的情况。

下面详细说明一下关于innodb_autoinc_lock_mode属性

（1） innodb_autoinc_lock_mode=0
代表传统模式，也就是说，在对有自增属性的字段插入记录时，会持续持有一个表级别的自增锁，直到语句执行结束为止。

会话1：

mysql> select @@global.tx_isolation,@@session.tx_isolation;+-----------------------+------------------------+| @@global.tx_isolation | @@session.tx_isolation |+-----------------------+------------------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+------------------------+1 row in set, 2 warnings (0.01 sec)
mysql> select @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 0 |+----------------------------+
mysql> CREATE TABLE t (a int unsigned auto_increment primary key) ENGINE=InnoDB;Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values(1),(3),(4),(5),(6),(7);Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t;+---+| a |+---+| 1 || 3 || 4 || 5 || 6 || 7 |+---+6 rows in set (0.00 sec)
mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where a>4;Query OK, 3 rows affected (0.01 sec)

会话2：

mysql> select @@global.tx_isolation,@@session.tx_isolation;+-----------------------+------------------------+| @@global.tx_isolation | @@session.tx_isolation |+-----------------------+------------------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+------------------------+1 row in set, 2 warnings (0.01 sec)
mysql> select @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 0 |+----------------------------+
mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(null);

发生阻塞

会话3：

mysql> select @@global.tx_isolation,@@session.tx_isolation;+-----------------------+------------------------+| @@global.tx_isolation | @@session.tx_isolation |+-----------------------+------------------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+------------------------+1 row in set, 2 warnings (0.01 sec)
mysql> select @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 0 |+----------------------------+
mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(2);Query OK, 1 row affected (0.00 sec)

同时也发生阻塞

mysql> select trx_id,trx_state,trx_requested_lock_id,trx_weight,trx_mysql_thread_id,trx_query, trx_operation_state from information_schema.INNODB_TRX;+--------+-----------+-----------------------+------------+---------------------+---------------------------+-----------------------+| trx_id | trx_state | trx_requested_lock_id | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state |+--------+-----------+-----------------------+------------+---------------------+---------------------------+-----------------------+| 1590 | LOCK WAIT | 1590:72 | 3 | 85 | insert into t values(2) | setting auto-inc lock || 1584 | LOCK WAIT | 1584:55:3:1 | 3 | 84 | insert into t select null | inserting || 1579 | RUNNING | NULL | 5 | 83 | NULL | NULL |+--------+-----------+-----------------------+------------+---------------------+---------------------------+-----------------------+

会话2是自增锁，会一直处于setting auto-inc lock，等待语句执行完成

（2） innodb_autoinc_lock_mode=1
默认值，代表连续模式，和传统模式差不多，不同的点在于对于简单的插入语句，只在分配新的 ID 过程中持有一个轻量级的互斥锁（线程级别，而不是事务级别），而不是直到语句结束才释放的表锁。

会话1：

mysql> select @@global.tx_isolation,@@session.tx_isolation;+-----------------------+------------------------+| @@global.tx_isolation | @@session.tx_isolation |+-----------------------+------------------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+------------------------+1 row in set, 2 warnings (0.01 sec)
mysql> select @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 1 |+----------------------------+
mysql> CREATE TABLE t (a int unsigned auto_increment primary key) ENGINE=InnoDB;Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values(1),(3),(4),(5),(6),(7);Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0
mysql> select * from t;+---+| a |+---+| 1 || 3 || 4 || 5 || 6 || 7 |+---+6 rows in set (0.00 sec)
mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> delete from t where a>4;Query OK, 3 rows affected (0.01 sec)

会话2：

mysql> select @@global.tx_isolation,@@session.tx_isolation;+-----------------------+------------------------+| @@global.tx_isolation | @@session.tx_isolation |+-----------------------+------------------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+------------------------+1 row in set, 2 warnings (0.01 sec)
mysql> select @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 1 |+----------------------------+
mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(null);

会话3：

mysql> select @@global.tx_isolation,@@session.tx_isolation;+-----------------------+------------------------+| @@global.tx_isolation | @@session.tx_isolation |+-----------------------+------------------------+| REPEATABLE-READ | REPEATABLE-READ |+-----------------------+------------------------+1 row in set, 2 warnings (0.01 sec)
mysql> select @@innodb_autoinc_lock_mode;+----------------------------+| @@innodb_autoinc_lock_mode |+----------------------------+| 1 |+----------------------------+
mysql> begin;Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values(2);Query OK, 1 row affected (0.00 sec)

会话3能够进行正常的插入，没有被阻塞。

mysql> select * from information_schema.INNODB_LOCKS;+-------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |+-------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+| 1584:55:3:1 | 1584 | X | RECORD | `sakila`.`t` | PRIMARY | 55 | 3 | 1 | supremum pseudo-record || 1579:55:3:1 | 1579 | X | RECORD | `sakila`.`t` | PRIMARY | 55 | 3 | 1 | supremum pseudo-record |+-------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+2 rows in set, 1 warning (0.00 sec)
mysql> select trx_id,trx_state,trx_requested_lock_id,trx_weight,trx_mysql_thread_id,trx_query, trx_operation_state from information_schema.INNODB_TRX;+--------+-----------+-----------------------+------------+---------------------+---------------------------+---------------------+| trx_id | trx_state | trx_requested_lock_id | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state |+--------+-----------+-----------------------+------------+---------------------+---------------------------+---------------------+| 1584 | LOCK WAIT | 1584:55:3:1 | 3 | 84 | insert into t select null | inserting || 1579 | RUNNING | NULL | 5 | 83 | NULL | NULL |+--------+-----------+-----------------------+------------+---------------------+---------------------------+---------------------+2 rows in set (0.00 sec)

这是由于它是simple-insert且innodb_autoinc_lock_mode=1所以不需要获取AUTO-INC表级锁,没有阻塞成功插入。

现在继续，此时会话1还没commit和rollback

会话3：

mysql> rollback;Query OK, 0 rows affected (0.00 sec)

会话2：

mysql> insert into t select null;

此时会话3被阻塞
会话3：

mysql> insert into t values(2);
mysql> select * from information_schema.INNODB_LOCKS;+-------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |+-------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+| 1590:72 | 1590 | AUTO_INC | TABLE | `sakila`.`t` | NULL | NULL | NULL | NULL | NULL || 1584:72 | 1584 | AUTO_INC | TABLE | `sakila`.`t` | NULL | NULL | NULL | NULL | NULL || 1584:55:3:1 | 1584 | X | RECORD | `sakila`.`t` | PRIMARY | 55 | 3 | 1 | supremum pseudo-record || 1579:55:3:1 | 1579 | X | RECORD | `sakila`.`t` | PRIMARY | 55 | 3 | 1 | supremum pseudo-record |+-------------+-------------+-----------+-----------+--------------+------------+------------+-----------+----------+------------------------+4 rows in set, 1 warning (0.00 sec)
mysql> select trx_id,trx_state,trx_requested_lock_id,trx_weight,trx_mysql_thread_id,trx_query, trx_operation_state from information_schema.INNODB_TRX;+--------+-----------+-----------------------+------------+---------------------+---------------------------+-----------------------+| trx_id | trx_state | trx_requested_lock_id | trx_weight | trx_mysql_thread_id | trx_query | trx_operation_state |+--------+-----------+-----------------------+------------+---------------------+---------------------------+-----------------------+| 1590 | LOCK WAIT | 1590:72 | 3 | 85 | insert into t values(2) | setting auto-inc lock || 1584 | LOCK WAIT | 1584:55:3:1 | 3 | 84 | insert into t select null | inserting || 1579 | RUNNING | NULL | 5 | 83 | NULL | NULL |+--------+-----------+-----------------------+------------+---------------------+---------------------------+-----------------------+

结论：如果是单一的insert SQL，可以立即获得该锁，并立即释放，而不必等待当前SQL执行完成（除非在其他事务中已经有session获取了自增锁）。另外当SQL是一些批量insert sql时，比如insert into …select …,load data,replace …select…时，这时还是表级锁，可以理解成退化为必须等待当前SQL执行完才释放。

（3） innodb_autoinc_lock_mode=2
代表交错模式。这个模式下放弃了自增表锁，产生的值会不连续。不过这是性能最高的模式，多条插入语句可以并发执行。MySQL 8.0 默认就是交错模式。

那针对复制安全来说，以上三种模式，0 和 1 对语句级别安全，也就是产生的二进制日志复制到任何其他机器都数据都一致；2 对于语句级别不安全；三种模式对二进制日志格式为行的都安全。

备注：在innodb_autoinc_lock_mode=2的时候，由于是来一个分配一个，故当replication模式为SBR的时候，如果发生Bulk inserts会在分配的时候向其他insert分配，就会出现主从不一致的情况，但是如果改为RBR就不会出现这种情况。也即是在RBR模式下，innodb_autoinc_lock_mode=2是安全的，其他情况还是建议设置为1.

墨天轮原文链接：https://www.modb.pro/db/29406（复制到浏览器中打开或者点击“阅读原文”）