电影一级毛片,欧美在线观看一区二区,免费av在线播放

從開始接觸MySQL，我們就知道在設計主鍵時，要設置為自增主鍵，使用自增主鍵有以下幾個優點：

效率高：使用自增主鍵可以避免頻繁生成主鍵值的操作，節省了數據庫的資源，提高了查詢效率。
索引優化：自增主鍵一般是整數類型，可以方便地使用B-tree索引來加速數據查詢。
數據唯一性：自增主鍵可以保證數據的唯一性，防止重復插入數據。
方便性：使用自增主鍵可以方便地進行更新、刪除和查詢操作，不需要復雜的聯合主鍵或其他索引操作。

我們在使用自增主鍵統計數據庫的數據量時，也會經常使用id的最大值與最小值之間的差值作為數據庫當前已有數據的條數，但是這種統計方式是否正確？是否存在誤差？

筆者先給出本文結論：自增主鍵可以保持主鍵遞增順序插入，避免頁分裂，索引更為緊湊，但是自增主鍵并不能保證連續遞增，即出現空洞。

但是問題再次出現，為什么明明是自增主鍵，為什么不能保證連續遞增？為什么會出現空洞？

在本文中，我們使用如下的數據庫配置：

MySQL：MySQL的自增主鍵是連續自增嗎？

1 自增值的存儲

在如上的空表 t 里面執行 insert into t values(null, 1, 1); 插入一行數據，再執行 show create table 命令，就可以看到如下圖所示的結果：

MySQL：MySQL的自增主鍵是連續自增嗎？

表定義里面出現了一個 AUTO_INCREMENT=2，表示下一次插入數據時，如果需要自動生成自增值，會生成 id=2。

不同的引擎對于自增至的保存策略不同：

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

在 MySQL 5.7 及之前的版本，自增值保存在內存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個表當前的自增值。舉例來說，如果一個表當前數據行里最大的id 是10，AUTO_INCREMENT=11。這時候，我們刪除 id=10 的行，AUTO_INCREMENT 還是 11。但如果馬上重啟實例，重啟后這個表的 AUTO_INCREMENT 就會變成 10。也就是說，MySQL 重啟可能會修改一個表的 AUTO_INCREMENT 的值。
在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時候依靠 redo log 恢復重啟之前的值。

2 自增值修改機制

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

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

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

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

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

但是在一些場景下，使用的就不全是默認值。比如，雙 M 的主備結構里要求雙寫的時候，我們就可能會設置成 auto_increment_increment=2，讓一個庫的自增 id 都是奇數，另一個庫的自增 id 都是偶數，避免兩個庫生成的主鍵發生沖突。

當 auto_increment_offset 和 auto_increment_increment 都是 1 的時候，新的自增值生成邏輯很簡單，就是：

如果準備插入的值 >= 當前自增值，新的自增值就是“準備插入的值 +1”；
否則，自增值不變。

3 自增值修改時機

3.1 唯一鍵沖突

假設表t有了存在(1,1,1)這條記錄，再次執行一次數據命令：

insert into t values(null, 1, 1);

這個語句的執行流程就是：

執行器調用 InnoDB 引擎接口寫入一行，傳入的這一行的值是 (0,1,1);
InnoDB 發現用戶沒有指定自增 id 的值，獲取表 t 當前的自增值 2；
將傳入的行的值改成 (2,1,1);
將表的自增值改成 3；
繼續執行插入數據操作，由于已經存在 c=1 的記錄，所以報 Duplicate key error，語句返回。

可以看到，這個表的自增值修改為3之后也不會再回退，之后再插入拿到的自增id就是3，自增主鍵不再連續。

3.2 事務回滾

insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

如上語句就會出現不連續自增id的情況。MySQL不允許做回退，看如下的假設：假設有兩個并行執行的事務，在申請自增值時，為了避免兩個事務申請到相同自增id，肯定加鎖，然后順序申請。

假設事務A申請到了id=2，事務B申請到id=3，那么表t的自增值是4，之后繼續執行；
事務B正確提交后，事務A出現唯一鍵沖突；
如果允許事務A把自增id回退，也就是表t當前自增值改回2；
接下來繼續執行的其他事務就會申請到id=2，然后再申請id=3，就會出現插入語句報錯“主鍵沖突"；

為了解決這個主鍵沖突，有兩種方法：

每次申請 id 之前，先判斷表里面是否已經存在這個 id。如果存在，就跳過這個 id。但是，這個方法的成本很高。因為，本來申請 id 是一個很快的操作，現在還要再去主鍵索引樹上判斷 id 是否存在。
把自增 id 的鎖范圍擴大，必須等到一個事務執行完成并提交，下一個事務才能再申請自增 id。這個方法的問題，就是鎖的粒度太大，系統并發能力大大下降。

出于性能考慮，如果設計為必須連續，那就需要每次都去檢查當前申請的ID是否已存在，浪費性能；或者提升鎖粒度，會導致申請ID退化為串行申請