MySQL主鍵自增值為什么有“空洞”?

作者：小蝦米&蟹黃瓜子 2022-07-03 22:00:49

本文在測(cè)試 insert、insert ignore、replace into 三種數(shù)據(jù)插入方式的時(shí)候，發(fā)現(xiàn)插入數(shù)據(jù)的時(shí)候在表內(nèi)存在帶有“唯一特性”的值重復(fù)的情況下三種語(yǔ)句的處理方式。最終發(fā)現(xiàn)了MySQL主鍵自增值“空洞”了

一.場(chǎng)景準(zhǔn)備

測(cè)試場(chǎng)景為MySQL 8.0：

主鍵重復(fù)場(chǎng)景
唯一鍵重復(fù)場(chǎng)景

1、建表，包含主鍵及唯一約束

CREATE TABLE t1(
  id int(11) NOT NULL auto_increment,
  c1 varchar(64) DEFAULT NULL,
  c2 int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_c1 (c1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、寫(xiě)入初始測(cè)試數(shù)據(jù)

insert into t1 (c1,c2) values ('a',1),('b',2),('c',3);

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
+----+------+------+
3 rows in set (0.00 sec)

二.開(kāi)始測(cè)試

insert into

# 測(cè)試主鍵重復(fù)
mysql> insert into t1 values (1,'aaa', 111);
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'

# 測(cè)試唯一鍵重復(fù)
mysql> insert into t1 (c1,c2) values('a', 4);
ERROR 1062 (23000): Duplicate entry 'a' for key 't1.uk_c1'

insert ignore into

insert方式插入數(shù)據(jù)在處理過(guò)程中發(fā)生主鍵傳統(tǒng)等錯(cuò)誤時(shí)候，語(yǔ)句會(huì)被終止，并告知錯(cuò)誤的原因。而使用insert ignore的方式進(jìn)行數(shù)據(jù)插入，則會(huì)忽略插入錯(cuò)誤的行繼續(xù)插入沒(méi)有問(wèn)題的行記錄，最終以warning進(jìn)行提示。

# 測(cè)試主鍵重復(fù)
mysql> insert ignore into t1 values (1,'aaa', 111);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 't1.PRIMARY' |
+---------+------+------------------------------------------+
1 row in set (0.01 sec)

# 測(cè)試唯一鍵重復(fù)
mysql> insert ignore into t1 (c1,c2) values('a', 4);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1062 | Duplicate entry 'a' for key 't1.uk_c1' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

在測(cè)試過(guò)程中驚奇地發(fā)現(xiàn)測(cè)試表中的主鍵自增列發(fā)生了改變，經(jīng)過(guò)之前的操作已經(jīng)變成了7：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
# 可是表的行數(shù)據(jù)并沒(méi)有增加
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
+----+------+------+
3 rows in set (0.00 sec)
# 新寫(xiě)入一條數(shù)據(jù)后，自增 id 變成 7
mysql> insert into t1 (c1,c2) values('d', 4);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  2 | b    |    2 |
|  3 | c    |    3 |
|  7 | d    |    4 |
+----+------+------+
4 rows in set (0.00 sec)

replace into

最后，replace into的方式導(dǎo)致如果插入數(shù)據(jù)是原值的情況，然后主鍵沖突，就對(duì)該主鍵的內(nèi)容進(jìn)行替換，如果唯一鍵沖突，唯一值所在行就會(huì)刪除，重新插入新的行，如果都不沖突則正常插入數(shù)據(jù)。

# 測(cè)試主鍵重復(fù)
mysql> replace into t1 values (1,'aaa', 111);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aaa  |  111 |
|  2 | b    |    2 |
|  3 | c    |    3 |
|  7 | d    |    4 |
+----+------+------+
4 rows in set (0.00 sec)

# 測(cè)試唯一鍵重復(fù)
mysql> replace into t1 (c1,c2) values('b', 4);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | aaa  |  111 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
+----+------+------+
4 rows in set (0.00 sec)

上文測(cè)試了三種插入數(shù)據(jù)的方式，可是測(cè)試過(guò)程中發(fā)現(xiàn)插入失敗的時(shí)候，自增列的自增值居然變大了。

三.問(wèn)題分析

為了更好地理解，首先讓我們具體認(rèn)識(shí)一下 AUTO_INCREMENT 屬性在不同的存儲(chǔ)引擎當(dāng)中，其自增值的保存策略有所不同：

MyISAM引擎的自增值是保存在數(shù)據(jù)文件中的。
InnoDB引擎的自增值，其實(shí)是保存在了內(nèi)存里，并且到了MySQL 8.0版本后，將自增值的變更記錄在了redo log中，當(dāng)MySQL發(fā)生重啟的時(shí)候依靠redo log恢復(fù)重啟之前的自增值。在此前，現(xiàn)在該表的自增值是7，重啟后又變成4了。

可是理解了這個(gè)并不能馬上理解現(xiàn)在的這個(gè)問(wèn)題，我們知道當(dāng)數(shù)據(jù)進(jìn)行數(shù)據(jù)插入的時(shí)候，如果插入的數(shù)據(jù)中自增列不指定其值的時(shí)候，該列就會(huì)以當(dāng)前自增值作為其值，如果指定其值就會(huì)插入指定的值，當(dāng)然也有滿足唯一的原則，同時(shí)插入指定值大于自增值時(shí)，自增值也會(huì)隨之改變。而自增值使用的算法是以 auto_increment_offset 參數(shù)決定開(kāi)始，以 auto_increment_increment 決定步長(zhǎng)來(lái)實(shí)現(xiàn)的,默認(rèn)情況都是1：

mysql> select @@auto_increment_offset;
+-------------------------+
| @@auto_increment_offset |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

那么，為什么會(huì)出現(xiàn)插入數(shù)據(jù)未成功，自增值卻變大了的情況呢？原因很簡(jiǎn)單，用插入數(shù)據(jù)的流程來(lái)進(jìn)行分析：

MySQL主鍵自增值為什么有“空洞”?

因?yàn)樽栽鲋档谋４媸窃诓迦霐?shù)據(jù)真正執(zhí)行前完成的，因此就會(huì)出現(xiàn)這種問(wèn)題了。

這個(gè)時(shí)候有人就會(huì)想了，可以把 AUTO_INCREMENT 值改回去嗎？簡(jiǎn)單測(cè)試一下：

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
+----+------+------+
4 rows in set (0.00 sec)
# 自增值修改為15
mysql> alter table t1 auto_increment = 15;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 修改成功
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
# 未插入任何值，修改回去，修改成功
mysql> alter table t1 auto_increment = 9;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)
# 修改回自增中間的值
mysql> alter table t1 auto_increment = 5;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 不會(huì)報(bào)錯(cuò)但無(wú)法修改
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `c1` varchar(64) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

顯然，如果自增值往大的方向修改是沒(méi)有問(wèn)題的，但如果往小的修改就要看目前數(shù)據(jù)庫(kù)插入的值是否會(huì)將修改后的自增值“卡”在中間，如果出現(xiàn)這種情況是沒(méi)辦法改回去的，原因顯而易見(jiàn)，自增屬性與主鍵配套使用，如果現(xiàn)在表里id=4和id=6之間差了個(gè)5的值，將自增值改回5，當(dāng)插入數(shù)據(jù)時(shí)，自增值就會(huì)插入5的值并且把自增值加1，問(wèn)題就出現(xiàn)了，此時(shí)自增值再進(jìn)行插入就違背了唯一的原則了

四.問(wèn)題拓展

在生產(chǎn)環(huán)境中還存在很多類似的問(wèn)題，如：

# 目前的插入值為8，自增值為9
mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
+----+------+------+
4 rows in set (0.00 sec)
# 插入數(shù)據(jù)相當(dāng)于(9,'t1', 1)
mysql> insert into t1 values (null,'t1', 1);
Query OK, 1 row affected (0.00 sec)
# 開(kāi)啟事務(wù)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 插入數(shù)據(jù)相當(dāng)于(10,'t2', 2)
mysql> insert into t1 values (null,'t2', 2);
Query OK, 1 row affected (0.00 sec)
# 事務(wù)回滾
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
# 插入數(shù)據(jù)相當(dāng)于(11,'t3', 3)
mysql> insert into t1 values (null,'t3', 3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
|  9 | t1   |    1 |
| 11 | t3   |    3 |
+----+------+------+
6 rows in set (0.00 sec)

在插入過(guò)程中，開(kāi)啟了一個(gè)事務(wù)，在插入的時(shí)候發(fā)生了事務(wù)的回滾，當(dāng)回滾后再次插入數(shù)據(jù)，發(fā)現(xiàn)自增值又出現(xiàn)了“空洞”，那么問(wèn)題又來(lái)了，為什么在插入數(shù)據(jù)的時(shí)候發(fā)生了回滾，數(shù)據(jù)回滾了，自增值卻沒(méi)有回滾呢？為了更直觀，繼續(xù)測(cè)試，假設(shè)有兩個(gè)事務(wù)。

測(cè)試前數(shù)據(jù)：

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
|  9 | t1   |    1 |
| 11 | t3   |    3 |
| 13 | t4   |    3 |
+----+------+------+
7 rows in set (0.00 sec)

進(jìn)行測(cè)試：

session1	session2
begin;
insert into t1 values (null,'s1', 1);	begin;
insert into t1 values (null,'s2', 2); commit;
rollback;

測(cè)試后數(shù)據(jù)：

mysql> select * from t1;
+----+------+------+
| id | c1   | c2   |
+----+------+------+
|  1 | a    |    1 |
|  3 | c    |    3 |
|  7 | d    |    4 |
|  8 | b    |    4 |
|  9 | t1   |    1 |
| 11 | t3   |    3 |
| 13 | t4   |    3 |
| 15 | s2   |    2 |
+----+------+------+
8 rows in set (0.00 sec)

發(fā)現(xiàn)還是“空洞”了，而且此時(shí)答案也十分清楚了，在不同事務(wù)在進(jìn)行寫(xiě)入操作的時(shí)候申請(qǐng)自增值，為了避免兩個(gè)事務(wù)申請(qǐng)到相同的自增值，所以需要對(duì)其加鎖，按照一定順序進(jìn)行申請(qǐng)自增值。根據(jù)前面的例子來(lái)看：

首先兩個(gè)session都開(kāi)啟了事務(wù)，session1前面的是id=14的自增值，session2則申請(qǐng)到id=15的自增值
接著當(dāng)session2插入成功后提交了事務(wù)，而此時(shí)，session1插入成功或出現(xiàn)插入失敗時(shí)進(jìn)行了事務(wù)回滾

此時(shí)就出現(xiàn)了前面說(shuō)到的問(wèn)題了，沒(méi)辦法回滾，回滾就會(huì)出現(xiàn)自增值“卡”在中間的情況了，以后有機(jī)會(huì)再繼續(xù)聊聊自增鎖的問(wèn)題。?

責(zé)任編輯：華軒來(lái)源： GreatSQL社區(qū)