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

語言: CN / TW / HK

*   G r e a t S Q L 使

  • 1.場景準備

  • 2.開始測試

  • 3.問題分析

  • 4.問題拓展

本文在測試 insertinsert ignorereplace into 三種資料插入方式的時候,發現插入資料的時候在表記憶體在帶有“唯一特性”的值重複的情況下三種語句的處理方式。最終發現了MySQL主鍵自增值“空洞”了

1.場景準備

測試場景為MySQL 8.0:

  • 主鍵重複場景

  • 唯一鍵重複場景

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、寫入初始測試資料

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)

2.開始測試

insert into

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

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

insert ignore into

insert方式插入資料在處理過程中發生主鍵傳統等錯誤時候,語句會被終止,並告知錯誤的原因。而使用insert ignore的方式進行資料插入,則會忽略插入錯誤的行繼續插入沒有問題的行記錄,最終以warning進行提示。

# 測試主鍵重複
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)

# 測試唯一鍵重複
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)

在測試過程中驚奇的發現測試表中的主鍵自增列發生了改變,經過之前的操作已經變成了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)
# 可是表的行資料並沒有增加
mysql> select * from t1;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | a | 1 |
| 2 | b | 2 |
| 3 | c | 3 |
+----+------+------+
3 rows in set (0.00 sec)
# 新寫入一條資料後,自增 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的方式導致如果插入資料是原值的情況,然後主鍵衝突,就對該主鍵的內容進行替換,如果唯一鍵衝突,唯一值所在行就會刪除,重新插入新的行,如果都不衝突則正常插入資料。

# 測試主鍵重複
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)

# 測試唯一鍵重複
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)

上文測試了三種插入資料的方式,可是測試過程中發現插入失敗的時候,自增列的自增值居然變大了。

3.問題分析

為了更好地理解,首先讓我們具體認識一下 AUTO_INCREMENT 屬性在不同的儲存引擎當中,其自增值的儲存策略有所不同:

  • MyISAM引擎的自增值是儲存在資料檔案中的。

  • InnoDB引擎的自增值,其實是儲存在了記憶體裡,並且到了MySQL 8.0版本後,將自增值的變更記錄在了redo log中,當MySQL發生重啟的時候依靠redo log恢復重啟之前的自增值。在此前,現在該表的自增值是7,重啟後又變成4了。

可是理解了這個並不能馬上理解現在的這個問題,我們知道當資料進行資料插入的時候,如果插入的資料中自增列不指定其值的時候,該列就會以當前自增值作為其值,如果指定其值就會插入指定的值,當然也有滿足唯一的原則,同時插入指定值大於自增值時,自增值也會隨之改變。而自增值使用的演算法是以 auto_increment_offset 引數決定開始,以 auto_increment_increment 決定步長來實現的,預設情況都是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)

那麼,為什麼會出現插入資料未成功,自增值卻變大了的情況呢?原因很簡單,用插入資料的流程來進行分析:

因為自增值的儲存是在插入資料真正執行前完成的,因此就會出現這種問題了。

這個時候有人就會想了,可以把 AUTO_INCREMENT 值改回去嗎?簡單測試一下:

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
# 不會報錯但無法修改
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)

顯然,如果自增值往大的方向修改是沒有問題的,但如果往小的修改就要看目前資料庫插入的值是否會將修改後的自增值“卡”在中間,如果出現這種情況是沒辦法改回去的,原因顯而易見,自增屬性與主鍵配套使用,如果現在表裡id=4和id=6之間差了個5的值,將自增值改回5,當插入資料時,自增值就會插入5的值並且把自增值加1,問題就出現了,此時自增值再進行插入就違背了唯一的原則了

4.問題拓展

在生產環境中還存在很多類似的問題,如:

# 目前的插入值為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)
# 插入資料相當於(9,'t1', 1)
mysql> insert into t1 values (null,'t1', 1);
Query OK, 1 row affected (0.00 sec)
# 開啟事務
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
# 插入資料相當於(10,'t2', 2)
mysql> insert into t1 values (null,'t2', 2);
Query OK, 1 row affected (0.00 sec)
# 事務回滾
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
# 插入資料相當於(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)

在插入過程中,開啟了一個事務,在插入的時候發生了事務的回滾,當回滾後再次插入資料,發現自增值又從出現了“空洞”,那麼問題又來了,為什麼在插入資料的時候發生了回滾,資料回滾了,自增值卻沒有回滾呢?為了更直觀,繼續測試,假設有兩個事務。

測試前資料:

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)

進行測試:

session1 session2
begin;
insert into t1 values (null,'s1', 1); begin;

insert into t1 values (null,'s2', 2);

commit;

rollback;

測試後資料:

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)

發現還是“空洞”了,而且此時答案也十分清楚了,在不同事務在進行寫入操作的時候申請自增值,為了避免兩個事務申請到相同的自增值,所以需要對其加鎖,按照一定順序進行申請自增值。根據前面的例子:

  • 首先兩個session都開啟了事務,session1前的是id=14的自增值,session2則申請到id=15的自增值

  • 接著當session2插入成功後提交了事務,而此時,session1插入成功或出現插入失敗時進行了事務回滾

此時就出現了前面說到的問題了,沒辦法回滾,回滾就會出現自增值“卡”在中間的情況了,以後有機會再繼續聊聊自增鎖的問題。

En j o y   G r e a t S Q L   : )

《深 M G R

B

https://www.bilibi l i . c om/medialist/play/136385008 2? business=space_collection&business_id=343928&desc=0

G r e a t S Q L M y S Q L M G R I n n o D B M y S Q L

G i t e e :  

h t t p s : / / g i t e e . c o m / G r e a t S Q L / G r e a t S Q L

G i t H u b :  

h t t p s : / / g i t h u b . c o m / G r e a t S Q L / G r e a t S Q L

  B i l i b i l i

h t t p s : / / s p a c e . b i l i b i l i . c o m / 1 3 6 3 8 5 0 0 8 2 / v i d e o

& Q Q

G r e a t S Q L G r e a t S Q L / M G R G r e a t S Q L / M G R Q Q

Q Q