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、寫入初始測(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)
# 新寫入一條數(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)行分析：
因?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)行寫入操作的時(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)題。?
