MySQL中AUTO_INCREMENT介绍-云栖社区-阿里云

1、AUTO_INCREMENT介绍

1.1 AUTO_INCREMENT

MySQL 使用 AUTO_INCREMENT 关键字来执行 auto-increment 任务，表示的是下次插入字段的数值。默认地，AUTO_INCREMENT的开始值是1（auto_increment_offset），每条新记录递增（auto_increment_increment）。
MySQL必须将AUTO_INCREMENT列定义为索引的一部分，以便可以在表上执行等效的索引SELECT MAX（ai_col）查找以获取最大列值，否则报如下错误：ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key。

1.2 有关AUTO_INCREMENT操作

添加auto_increment：alter table tb_name change 字段新字段类型 auto_increment;
删除auto_increment：alter table tb_name change 字段新字段类型;
修改auto_increment：alter table tb_name auto_increment=xxx;

2、MySQL InnoDB AUTO_INCREMENT 锁模式（Lock Mode）

我们再来看一个自增长问题，如下测试：

# 第一种
mysql> create table t1(id int auto_increment, a int, primary key (id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1,2),(2,2),(3,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> insert into t1 values (4,2),(null,2),(null,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table t1\G                        
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

# 第二种
mysql> create table t1(id int auto_increment primary key,name varchar(255));
Query OK, 0 rows affected (0.12 sec)

mysql> create table t2(name varchar(255))engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t2 values('aa'),('bb');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into t1(name) select * from t2;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
+----+------+
2 rows in set (0.00 sec)

mysql> show create table t1;
| Table | Create Table                                                                                          
| t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

我们可以看到第一种带NULL值的写法，自增长值会多增加一个值；第二种insert .. select，自增长也会多增加一个值。这个会带来什么问题呢？你会发现从库自增长值却是正常的（当复制格式为ROW时），这个时候其实也就是主从数据不一致了，但影响不大，除非出现记录ID大于自增长ID，那样插入数据重复会报错。
插入操作会依据这个自增长的计数器加1赋予自增长列。这个实现方式称作AUTO-INC Locking（自增锁）。这种自增锁是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql 语句后立即释放。
AUTO-INC Locking 从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。

首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入完成。
其次，对于 insert …select 的大数据量的插入会影响插入的性能，因为另一个事务中插入会被阻塞。

从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为1。在继续讨论新的自增长实现方式之前，需要对自增长的插入进行分类。如下说明：

INSERT-like：指所有的插入语句；包括“simple-inserts”,“bulk-inserts”,“mixed-mode”；包括INSERT、REPLACE、INSERT…SELECT，REPLACE…SELECT、LOAD DATA等。
Simple inserts：指能在插入前就确定插入行数的语句，这些语句包括INSERT、REPLACE等。需要注意的是：simple inserts不包含INSERT…ON DUPLICATE KEY UPDATE这类SQL语句。
Bulk inserts：指在插入前不能确定得到插入行数的语句，如INSERT…SELECT，REPLACE…SELECT，LOAD DATA。
Mixed-mode inserts：这些是“simple-inserts”语句，它指定一些（但不是全部）新行的自动增量值，下面是一个例子，其中c1是表t1的AUTO_INCREMENT列：INSERT INTO t1(c1,c2) VALUES(1,'a'),(NULL,'a'),(3,’a')；另一种类型的“mixed-mode inserts”是INSERT ... ON DUPLICATE KEY UPDATE，在最坏的情况下实际上是INSERT后跟UPDATE，其中AUTO_INCREMENT列的分配值可能会或可能不会在更新阶段。

innodb_autoinc_lock_mode 在不同设置下对自增长的影响（默认为1）：

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)
MySQL 5.1.22版本之前自增长的实现方式。在此锁定模式下，所有“INSERT-like”语句都会获得一个特殊的表级AUTO-INC锁，用于插入具有AUTO_INCREMENT列的表。此锁通常保持在语句的末尾（而不是事务的结尾），以确保为给定的INSERT语句序列以可预测和可重复的顺序分配自动增量值，并确保自动增量任何给定语句分配的值都是连续的。
innodb_autoinc_lock_mode = 1(“consecutive” lock mode)
对于『simple inserts』，该值会用互斥量对内存中的计数器进行累加操作。对于『bulk inserts』会用传统的 AUTO-INC Locking 方式。这种配置下，如果不考虑回滚，自增长列的增长还是连续的。需要注意的是：如果已经使用 AUTO-INC Locking 方式去产生自增长的值，而此时需要『simple inserts』操作时，还需要等待 AUTO-INC Locking 的释放。
innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)
对于所有『insert-like』自增长的产生都是通过互斥量，而不是AUTO-INC Locking方式。这是性能最高的方式。但会带来一些问题：因为并发插入的存在，每次插入时，自增长的值是不连续的，基于statement-base replication会出现问题。因此，使用这种方式，任何情况下都需要使用row-base replication，这样才能保证最大并发性能和replication的主从数据的一致。

3、MySQL 5.7和8.0计数初始化（Count Initialization）对比

MySQL 8.0修复了一个MySQL十年之久的自增列Bug，在2003年由Percona的CEO提出的bug#199。先重现一下这个BUG。

### MySQL 5.7
mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
|  2 |    2 |
|  3 |    2 |
+----+------+
3 rows in set (0.00 sec)

# 删除一些记录
mysql> delete from t1 where id=2 or id=3;
mysql> select * from t1;
+----+------+
| id | a    |
+----+------+
|  1 |    2 |
+----+------+
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

### 重启MySQL后查看AUTO_INCREMENT
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

### MySQL 8.0
mysql> select * from t;
+----+------+---------------------+
| id | name | time                |
+----+------+---------------------+
|  1 | l    | 2019-08-07 19:06:32 |
|  2 | y    | 2019-08-07 19:06:49 |
|  4 | c    | 2019-08-07 19:08:08 |
|  6 | h    | 2019-08-07 19:11:25 |
+----+------+---------------------+
4 rows in set (0.00 sec)

mysql> delete from t where id=4;
Query OK, 1 row affected (0.00 sec)

mysql> delete from t where id=6;
Query OK, 1 row affected (0.01 sec)

mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

### 重启MySQL后查看AUTO_INCREMENT
mysql> show create table t;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

如果为InnoDB表指定AUTO_INCREMENT列，则内存中表对象包含一个称为自动增量计数器的特殊计数器，该计数器在为列分配新值时使用。

在MySQL 5.7及更早版本中，自动增量计数器仅存储在内存中，而不存储在磁盘上。要在服务器重新启动后初始化自动增量计数器，InnoDB将在第一次插入到包含AUTO_INCREMENT列的表中执行以下语句：

SELECT MAX（ai_col）FROM table_name FOR UPDATE;

上面的测试反映了mysqld重启后，InnoDB存储引擎的表自增id可能出现重复利用的情况。这在很多场景下可能导致问题，包括但不限于：主备切换、历史数据迁移等场景。在bug#199下面一大堆的回复里，可以看到大量的同行抱怨。

在MySQL 8.0中，此问题已修改。每次更改时，当前最大自动增量计数器值将写入redo log，并保存到每个检查点上的引擎专用系统表中。这些更改使当前最大自动增量计数器值在服务器重新启动时保持不变。

在正常关闭后重新启动mysqld时，InnoDB使用存储在数据字典系统表中的当前最大自动增量值初始化内存中自动递增计数器。
在crash recovery期间重新启动mysqld时，InnoDB使用存储在数据字典系统表中的当前最大自动增量值初始化内存中自动增量计数器，并扫描重做日志以查找自上一个检查点以来写入的自动增量计数器值。如果重做日志值大于内存中计数器值，则应用重做日志值。但是在mysqld crash的情况下，无法保证重用先前分配的自动增量值，每次由于INSERT或UPDATE操作而更改当前最大自动增量值时，新值将写入redo log，但如果在redo log刷新到磁盘之前发生崩溃，则可以重用以前分配的值在重新启动mysqld后初始化自动增量计数器。

InnoDB在MySQL 8.0及更高版本中使用等效的SELECT MAX（ai_col）FROM table_name FOR UPDATE语句来初始化自动增量计数器的唯一情况是导入没有.cfg元数据文件的表空间。否则，从.cfg元数据文件中读取当前最大自动增量计数器值。

另外：在MySQL 5.7和8.0开启一个事务后，有类INSERT操作，自增值就会增加；但是当事务回滚后，自增值并不会减小。也就是说自增值会有空洞。但是MySQL 5.7重启会自增值会减小，MySQL 8.0自增值不会减小。

4、MySQL表中有自增列id数据不连续原因

虽然表定义了自增主键，但是插入时从全局ID生成器获取ID，导致每个表分配到的ID并不是连续的
表中数据进行过删除
session A插入了数据，获取了自增id为10，session B也插入了数据，但session A回滚了，导致id=10的空洞
设置了innodb_autoinc_lock_mode=2，高并发或批量插入的情况下导致自增ID不连续
手动调整过AUTO_INCREMENT，导致中间有空洞
设置步长非1，导致每次自增并不是加1