1、有朋友給我留言
為什么他的庫表里面，有些是連續的，有些不是連續的，而且他的表都是自增（`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵id'），他的潛意識里這個id應該是連續的才對，因此準備梳理梳理mysql自增主鍵的值的變化情況，讓朋友有個比較全面的了解，
2、自增值存盤說明
2.1 MyISAM 引擎的自增值保存在資料檔案中，
2.2 InnoDB 引擎的自增值，其實是保存在了記憶體里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才實作了“如果發生重啟，表的自增值可以恢復為 MySQL 重啟前的值”，具體情況是：
a）在 MySQL 5.7 及之前的版本，自增值保存在記憶體里，每次重啟后，第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id) + 1 作為這個表當前的自增值，
b）在 MySQL 8.0 版本，將自增值的變更記錄在了 redo log 中，重啟的時候依靠 redo log 恢復重啟之前的值，
3、自增值修改機制
在 MySQL 里面，如果欄位 id 被定義為 AUTO_INCREMENT，在插入一行資料的時候，自增值的行為如下：
如果插入資料時 id 欄位指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增欄位；
如果插入資料時 id 欄位指定了具體的值，就直接使用陳述句里指定的值，
根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同，假設，某次要插入的值是 i，當前的自增值是 j，
如果 i<j，那么這個表的自增值不變；
如果 i≥j，就需要把當前自增值修改為新的自增值，
新的自增值生成演算法是：從 auto_increment_offset 開始，以 auto_increment_increment 為步長，持續疊加，直到找到第一個大于i的值，作為新的自增值，其中，auto_increment_offset 和 auto_increment_increment 是兩個系統引數，分別用來表示自增的初始值和步長，默認值都是 1，
4、 導致自增值不連續的原因
4.1 唯一鍵沖突
假設執行 SQL 的時候 user 表 id = 10，此時在記憶體中的自增 id 為11，此時發生唯一鍵沖突寫庫失敗，則 user 表沒有 id = 10 這條記錄，之后 id 從11開始寫入，因此 id 是不連續的，
4.2 事務回滾
innodb設計的時候陳述句執行失敗也不回退自增id，也正是因為這樣，所有才只保證了自增id是遞增的，但不保證是連續的，假設同時需要對 user、staff 表進行寫庫操作，執行 SQL 的時候 user 表 id = 10，此時在記憶體中的自增 id 為11；staff 表 id = 20，此時記憶體中的自增 id 為21，一旦事務執行失敗，事務回滾，寫庫失敗，則 user 表沒有 id = 10 這條記錄，staff 表沒有 id = 20 這條記錄，user 表從11開始寫入，staff 表從21開始寫入，如此產生 id 不連續的現象，
4.3 批量寫庫操作
對于批量插入資料的陳述句，MySQL 有一個批量申請自增 id 的策略：
a). 陳述句執行程序中，第一次申請自增 id，會分配 1 個；
b). 1 個用完以后，這個陳述句第二次申請自增 id，會分配 2 個；
c). 2 個用完以后，還是這個陳述句，第三次申請自增 id，會分配 4 個；
依此類推，同一個陳述句去申請自增 id，每次申請到的自增 id 個數都是上一次的兩倍，
假設批量往 user 表中寫入四條記錄，則這四條記錄將分為三次申請id，
第一次分配到 id = 1，第二次分配到 id = 2、3 ，第三次分配到 id = 4、5、6、7，當批量寫入四條記錄之后，id = 1、2、3、4將會入庫，但是 id = 5、6、7就被廢棄了，下一個 id 從8開始，
4.4 大量delete操作，物理洗掉資料，delete from tbname where xxxx;這樣硬洗掉會導致id不連續，
5、如何實作自增id值遞增且連續
如果想讓自己的bigint型別主鍵id欄位值是連續的，要怎么實作呢，MySQL底層默認是實作不了的，需要我們自己做下特殊處理，
5.1、做成觸發器的方式
insert：每次進行insert插入操作時,都先執行查詢操作,得到該序列號,然后插入時再加1;當然這種辦法是最笨的方法,而且需要多執行一次操作
update: update的時候，如果update主鍵值，要判斷主鍵值是否是max（id）+1，如果不是設定成update（id）+1.
delete：不用做任何操作，甚至穩妥起見，不能物理delete，只能設定一個status=-1這樣的邏輯洗掉，
5.2、業務層的處理
在每次插入資料之后，修正那個AUTO_INCREMENT值，就是 : 先 執行 ALTER TABLE `table` AUTO_INCREMENT =1
執行之后，在執行插入操作,這樣就可以讓自增型別連續自增; (注:不一定再插入的id就是1；而是表中id最大值+1，)，而且不允許硬洗掉delete操作，
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/153009.html
標籤：其他