INCREMENT用法实例详解

之前有碰到过开发同事指出一张InnoDB表的自增列 AUTO_INCREMENT 值莫明的变大，由于这张表是通过MySQLdump导出导入的。

问题排查：

1、首先，查看表表义的sql部分的 auto_increment 值部分是正常，所以排除是导入表问题所引起的；
2、最后，经过沟通了解怀疑是插入时指定自增列的值，并且值过大，随之发现自增列的值出错时又进行大量删除时引起的问题。

为了验证这个怀疑的准确性，同时学习下InnoDB处理 AUTO_INCREMENT 的机制，因此在测试环境做了测试总结。

本文使用的MySQL版本为官方社区版 5.7.24。

(root@localhost) [test] > select version(); +------------+ | version() | +------------+ | 5.7.24-log | +------------+ 1 row in set (0.00 sec)

测试环境测试表参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

(root@localhost) [test] > show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) (root@localhost) [test] > show columns from t1; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | c1 | int(11) | NO | PRI | NULL | auto_increment | | c2 | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) AUTO_INCREMENT锁模式

InnoDB自增锁的模式由参数 innodb_autoinc_lock_mode 在启动时指定，这是一个只读参数，并不能在实例运行中动态修改参数值。参数值选项如下：

innodb_autoinc_lock_mode = {0|1|2}

InnoDB表insert语句主要可以分为三种类型：

Simple inserts(简单插入)
可以通过语句预先判断插入的行数。包括不包含子查询的单行、多行 INSERT 和 REPLACE 语句，还有语句 INSERT ... ON DUPLICATE KEY UPDATE。

Bulk inserts(大量插入)
无法通过语句预先判断插入的行数。包括 INSERT ... SELECT 、 REPLACE ... SELECT 和 LOAD DATA 语句。InnoDB每处理一行才会为 AUTO_INCREMENT 列分配一个值。

Mixed-mode inserts(混合模式插入)
在简单插入语句当中，有的行有为自增列指定值，而有的行没有为自增列指定值。例如：

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

其中c1为自增列。

还有一种混合模式插入语句 INSERT ... ON DUPLICATE KEY UPDATE ，这类语句InnoDB为自增列预分配的值有可能被使用，也有可能不被使用。

传统锁模式 (traditional)

innodb_autoinc_lock_mode = 0

在这个模式下，所有的 INSERT 语句在插入有自增属性的列时都要获取一个特殊的 AUTO-INC 表级锁。该锁的持有时间到语句结束(而不是到事务结束，一个事务中可能包含多条语句)，它能够确保为有自增属性列在 INSERT 一行或者多行数据时分配连续递增的值。

连续锁模式 (consecutive)

innodb_autoinc_lock_mode = 1

这是默认的锁模式。在这个模式下，大量插入每条语句执行时都将获得特殊的表级 AUTO-INC 锁，语句执行完成后释放。每次只有一条语句可以执行并持有 AUTO-INC 锁。

Bulk inserts(大量插入)

如果大量插入的源表和目标表是不同的，则在源表第一行数据获得共享锁之后，目标表就加上 AUTO-INC 表锁；

如果大量插入的源表和目标表是同一张表，当源表选取所有行获得共享锁之后，目标表才加上 AUTO-INC 表锁。

Simple inserts(简单插入)

通过 MUTEX(轻量级的锁) 而不是 AUTO-INC特殊表锁控制插入分配自增属性列的值；

MUTEX 只在分配值的过程中持有，而无需等到语句结束，并且性能花销更少；

简单插入不持有 AUTO-INC 锁，但如果其他事务持有，需等待其他事务释放，就像大量插入那样。

交叉锁模式 (interleaved)

innodb_autoinc_lock_mode = 2

在这种锁模式下，没有插入语句使用 AUTO-INC 表级锁，并且多条语句可以并发执行。这是最快并且扩展性最好的锁模式，但是如果binlog使用基于语句级复制的在从库重放SQL语句时是不安全的。

AUTO_INCREMENT锁模式使用说明 用于复制