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 版本修复。
Auto-Increment Counter Persistence in MySQL 8: Comparing the Evolution From MySQL 5.7

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 报错，所以主键（自增列）尽量不要去修改。