MySQL 自增列使用上的一些 “坑”
前言
MySQL 的规范中，一般都会建议表要有主键，常使用自增列作为主键字段，这和 MySQL 属于聚簇索引表有关，顺序增长的主键比较合适。最近有研发咨询，为什么有张表的自增主键变的非常大？而且偶尔还出现 Duplicate entry '4' for key 'PRIMARY' 的异常，这篇文章将介绍下此类问题。
1. 自增列空洞
1.1 手动指定
自增列的空洞一般指的就是自增列不是连续增长，中间出现一些数值上的断层，这种情况常发生在手动指定自增列的值，请看下面的 case：
-- 创建一张测试表，id 为自增主键 create table t1( id bigint auto_increment primary key, c1 varchar(10) not null, c2 varchar(10) not null );
插入测试数据：
insert into t1(c1, c2) VALUES ('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b');id | c1 | c2 |
1 | a | b |
2 | a | b |
3 | a | b |
4 | a | b |
5 | a | b |
此时再插入手动 ID 插入数据：
insert into t1 value (10, 'a', 'b');
再查询 t1 表的自增值：
select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME = 't1';
TABLE_NAME | AUTO_INCREMENT |
t1 | 11 |
查询表中的数据：
id | c1 | c2 |
1 | a | b |
2 | a | b |
3 | a | b |
4 | a | b |
5 | a | b |
10 | a | b |
此时可以看到表中的数据只有 6 行，但是自增值已经变为 6，浪费了中间四个值，如果为主键字段为 INT 类型，浪费过多会导致字段溢出，插入数据时就会报错，所以规范里也提到使用 bigint 类型作为表的主键。
2.2 分配未使用
自增列有一个特点，就是一旦分配后，就无法被 rollback 相当于被 “浪费”，请看下方 case：
-- 创建一张测试表，id 为自增主键 create table t1( id bigint auto_increment primary key, c1 varchar(10) not null, c2 varchar(10) not null );
开启事务，插入一条数据，然后回滚：
-- 开启事务
begin;
-- 插入一条记录
insert into t1(c1, c2) value('a', 'b');
-- 回滚
rollback;
-- 再次插入数据
insert into t1(c1, c2) value('a', 'b');id | c1 | c2 |
2 | a | b |
可以看到自增列是从 2 开始分配的，相当于第一个事务拿到了自增值，但是没有实际使用。
除了 Rollback 会产生分配未使用的情况外，还有 REPLACE 和 INSERT…ON DUPLICATE KEY UPDATE 在特定情况下也会出现分配未使用的情况：
-- 创建测试表，注意 c1 字段有唯一索引 create table t1 ( id bigint auto_increment, c1 varchar(10) not null, c2 varchar(10) not null, PRIMARY KEY (`id`), UNIQUE KEY uqk_c1(c1) );
插入测试数据：
insert into t1(c1, c2) VALUES ('a', 'b'),('b', 'b');使用 REPLACE 语句：
-- 如果有 c1 = a 将 c2 修改为 w
replace into t1(c1, c2) value ('a', 'w');
-- 上面已经修改了值，下面执行后数据没有实际变化
replace into t1(c1, c2) value ('a', 'w');
replace into t1(c1, c2) value ('a', 'w');
replace into t1(c1, c2) value ('a', 'w');
replace into t1(c1, c2) value ('a', 'w');
replace into t1(c1, c2) value ('a', 'w');此时表中只有两条记录：
id | c1 | c2 |
2 | b | b |
8 | a | w |
再看该表的 AUTO_INCREMENT 值，已经增长到 9 相当于产生了空洞：
select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME = 't1';
TABLE_NAME | AUTO_INCREMENT |
t1 | 9 |
除此之外，还有 DUPLICATE KEY UPDATE 语法也会有此类情况。这两个操作会获取自增列的值，但是经常不会触发 insert，而是 update。
insert into t1(c1, c2) value ('a', 'b') on duplicate key update c2 = 'vvvv';2. 自增列监控
2.1 sys 库监控
为了防止自增列的值 “用光”，导致业务报错。可以使用下方 SQL 监控自增列：
select * from sys.schema_auto_increment_columns;
2.2 通用查询
如果你的数据库不支持使用这条 SQL（版本问题，或者有些云厂商没有开放 sys 库）可以使用下方 SQL：
SELECT table_schema,
table_name,
column_name,
c.COLUMN_TYPE,
AUTO_INCREMENT,
POW(2, CASE data_type
WHEN 'tinyint' THEN 7
WHEN 'smallint' THEN 15
WHEN 'mediumint' THEN 23
WHEN 'int' THEN 31
WHEN 'bigint' THEN 63
END + (column_type LIKE '% unsigned')) - 1 AS max_int
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema, table_name)
WHERE c.extra = 'auto_increment'
and c.COLUMN_KEY = 'PRI'
AND t.TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'sys', 'performance_schema')
AND t.auto_increment IS NOT NULL;3. 一些 BUG
3.1 重启失效
MySQL 5.7 版本 auto_increment 是存储在内存中的，这就导致每次重启 MySQL 都会重新计算该值，计算逻辑是取该字段的 MAX VALUE 请看下方 case：
-- 创建一张测试表，id 为自增主键 create table t1( id bigint auto_increment primary key, c1 varchar(10) not null, c2 varchar(10) not null );
插入 8 行记录：
insert into t1(c1, c2) value ('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b');此时 AUTO_INCREMENT 的值为 9，下一条写入会被分配自增 ID 为 9：
select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME = 't1';
TABLE_NAME | AUTO_INCREMENT |
t1 | 9 |
然后删除后 4 条记录：
delete from t1 where id > 4;
重启数据库：
>$ service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL... SUCCESS!
查询 t1 表的自增 ID：
select TABLE_NAME, AUTO_INCREMENT from information_schema.TABLES where TABLE_NAME = 't1';
TABLE_NAME | AUTO_INCREMENT |
t1 | 5 |
可以看到 MySQL 在重启之后自增列的值被重置了。这个现象被称之为 BUG 主要是在现在的互联网业务中，支撑业务数据的不仅仅只有 MySQL，还可能会有 Redis，RabbitMQ 等缓存和消息队列或者是单独的 MySQL 日志归档库，自增列可能会被用来作为关联各个存储之间的“逻辑外键”，当 MySQL 重启之后，新写入的数据可能会用到已经被删除的值，导致数据库中的数据和外部系统之间的数据出现错误的关联。另外一种问题场景就是 MySQL 自身各个表之间有外键关系，但是没有建立外键约束，也会遇到类似的问题。
该 BUG 已在 MySQL 8.0 版本修复。
3.2 冲突问题
当自增列被修改过时，可能会出现 Duplicate entry '4' for key 'PRIMARY' 的异常，详细看下方 case：
-- 创建一张测试表，id 为自增主键 create table t1( id bigint auto_increment primary key, c1 varchar(10) not null, c2 varchar(10) not null );
插入测试数据：
insert into t1(c1, c2) VALUES ('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b'),('a', 'b');修改自增列的值：
update t1 set id = 6 where id = 1; -- 表中数据： +----+----+----+ | id | c1 | c2 | +----+----+----+ | 2 | a | b | | 3 | a | b | | 4 | a | b | | 5 | a | b | | 6 | a | b | +----+----+----+
再尝试获取自增值插入：
insert into t1(c1, c2) VALUES ('a', 'b');ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
这个 case 报错的原因是 UPDATE 修改了自增列的值，但是 UPDATE 不会触发自增值的分配，却占用了一个未来要被分配的自增值，当该值需要被分配出来的时候，由于被 UPDATE 修改的行占用了，导致 SQL 报错，所以主键（自增列）尽量不要去修改。
