欧美三级欧美视频在线,国产一区二区三区欲,国产午夜精品一区二区三区四区

主頁 > 知識庫 > 為什么mysql自增主鍵不是連續的

為什么mysql自增主鍵不是連續的

一前言

提出這個問題，是因為在工作中發現 mysql 中的 user 表的 id 默認是自增的，但是數據庫存儲的結果卻不是連續的。

user 表結構：

CREATE TABLE `user` ( 
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '遞增id', 
	`name` varchar(20),
	`create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間', 
	`update_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新時間', 
	PRIMARY KEY (`id`),UNIQUE KEY `idx_name` (`name`)) 
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='user表'

user 表存儲：

二自增值存儲說明

1.1 MyISAM 引擎的自增值保存在數據文件中。

1.2 InnoDB 引擎的自增值，其實是保存在了內存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實現了“如果發生重啟，表的自增值可以恢復為 MySQL 重啟前的值”，具體情況是：

在 MySQL 5.7 及之前的版本，自增值保存在內存里。每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id) + 1 作為這個表當前的自增值。
在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時候依靠 redo log 恢復重啟之前的值。

三自增值修改機制

在 MySQL 里面，如果字段 id 被定義為 AUTO_INCREMENT，在插入一行數據的時候，自增值的行為如下：

如果插入數據時 id 字段指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增字段；
如果插入數據時 id 字段指定了具體的值，就直接使用語句里指定的值。

根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同。假設，某次要插入的值是 X，當前的自增值是 Y。

如果 XY，那么這個表的自增值不變；
如果 X≥Y，就需要把當前自增值修改為新的自增值。

新的自增值生成算法是：從 auto_increment_offset 開始，以 auto_increment_increment 為步長，持續疊加，直到找到第一個大于 X 的值，作為新的自增值。其中，auto_increment_offset 和 auto_increment_increment 是兩個系統參數，分別用來表示自增的初始值和步長，默認值都是 1。

四自增值修改時機

insert into user values(null, '張三');

1 當執行上述 SQL 時，執行器調用 InnoDB 引擎接口寫入一行，傳入的這一行的值是 (0,"張三")；

2 InnoDB 發現 SQL 沒有指定自增 id 的值，獲取 user 表當前的自增值 2；

3 將傳入的行的值改成 (2,"張三");

4 將表的自增值改成 3；

5 繼續執行插入數據操作。

五導致自增值不連續的原因

5.1 唯一鍵沖突

假設執行 SQL 的時候 user 表 id = 10，此時在內存中的自增 id 為11，此時發生唯一鍵沖突寫庫失敗，則 user 表沒有 id = 10 這條記錄，之后 id 從11開始寫入，因此 id 是不連續的。

5.2 事務回滾

假設同時需要對 user、staff 表進行寫庫操作，執行 SQL 的時候 user 表 id = 10，此時在內存中的自增 id 為11；staff 表 id = 20，此時內存中的自增 id 為21，一旦事務執行失敗，事務回滾，寫庫失敗，則 user 表沒有 id = 10 這條記錄，staff 表沒有 id = 20 這條記錄，user 表從11開始寫入，staff 表從21開始寫入，如此產生 id 不連續的現象。