自增主鍵去哪了?---一次開發(fā)過程中的思考

前情提要：

最近新接了一個需求，需要去創(chuàng)建兩張表，其中有一張表需要根據(jù)業(yè)務(wù)id和業(yè)務(wù)類型建立唯一索引，對數(shù)據(jù)唯一性進(jìn)行約束。

因?yàn)樯婕暗綐I(yè)務(wù)嘛，表結(jié)構(gòu)就進(jìn)行縮略了

表結(jié)構(gòu)示例如下：

CREATE TABLE `example_table` ( 
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主鍵', 
    `business_id` bigint(20) unsigned NOT NULL COMMENT '業(yè)務(wù)ID', 
    `business_type` tinyint(3) unsigned NOT NULL COMMENT '業(yè)務(wù)類型，', 
    `del` tinyint(1) unsigned DEFAULT '0' COMMENT '刪除標(biāo)識，0表示未刪除，1表示刪除', 
    `creator` varchar(50) NOT NULL COMMENT '創(chuàng)建人PIN', 
    `modify_date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', 
    `create_date` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間', 
     PRIMARY KEY (`id`), 
     UNIQUE KEY `uk_business_id_and_type` (`business_id`,`business_type`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='示例表'

既然表建立好，那么就是發(fā)揮我們編碼能力的時候了...此處省略一堆編碼時間。

編碼結(jié)束，自測結(jié)束，信心滿滿的找前端同學(xué)進(jìn)行聯(lián)調(diào)。

因?yàn)槁?lián)調(diào)嘛，mock了很多同樣的business_id和bussiness_type的數(shù)據(jù)，結(jié)果到了數(shù)據(jù)庫，因?yàn)槲ㄒ凰饕募s束，報了一堆錯誤，插入都失敗了。

終于調(diào)整了一下mock數(shù)據(jù)，插入成功了。

但是發(fā)生了一個比較神奇的現(xiàn)象

主鍵不是連續(xù)自增的了~~ 中間丟失的自增主鍵去哪了??

關(guān)于自增主鍵

自增主鍵是我們在設(shè)計數(shù)據(jù)庫表結(jié)構(gòu)時經(jīng)常使用的主鍵生成策略，主鍵的生成可以完全依賴數(shù)據(jù)庫,在新增數(shù)據(jù)的時候，我們只需要將主鍵設(shè)置為null，0或者不設(shè)置該字段，數(shù)據(jù)庫就會為我們自動生成一個主鍵值。

首先,我們要知道 自增主鍵保存在哪里~

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

1.MyISAM引擎的自增值保存在數(shù)據(jù)文件中

2.InnoDB引擎的自增值，在MySQL5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值max(id)，然后將max(id)+步長（建表語句中的指定步長）作為這個表當(dāng)前的自增值。在MySQL8.0版本，將自增值的變更記錄在了redo log中，重啟的時候依靠redo log恢復(fù)重啟之前的值。

了解了自增主鍵的保存機(jī)制，再了解一下主鍵這個"自增"邏輯~

插入一條語句分配自增主鍵id值的流程如圖所示。

自增主鍵不連續(xù)的情況

細(xì)心的小伙伴一定發(fā)現(xiàn)了~咦，這個ID=聲明值的話，ID就可以能被隨意指定了，那么ID就可能存在不是自增的情況了！

是的，這其實(shí)就是第一種自增主鍵不連續(xù)的情況。

第二種不連續(xù)的情況就是我們在聯(lián)調(diào)中遇到的問題了

簡單來做個測試,目前數(shù)據(jù)就像一開始的圖一樣,id自增到了24,下一個插入的應(yīng)該是25,那么執(zhí)行一條sql

insert into example_table  values (null,111,1,0,'mock',now(),now());

插入成功了一條數(shù)據(jù),主鍵是連續(xù)自增的。

那么我們模擬一條錯誤的sql呢(`creator`字段指定錯類型)~：

insert into example_table  values (null,112,1,0,mock,now(),now());

果然，執(zhí)行sql 的時候報出異常：

繼續(xù)執(zhí)行一條正確的正常的sql，插入結(jié)果：

主鍵還是連續(xù)自增的。這個發(fā)生錯誤為什么自增主鍵還是連續(xù)的呢。我們模擬一下之前聯(lián)調(diào)遇到的情況，插入一條 sql：

insert into example_table  values (null,112,1,0,'mock',now(),now());

因?yàn)閕d=26的數(shù)據(jù)buiness_id和bussiness_type 跟新插入的這條數(shù)據(jù)一樣，那么肯定會因?yàn)槲ㄒ凰饕迦氩怀晒?，果然，?zhí)行結(jié)果如下：

那么，我們修改一下sql繼續(xù)插入呢？

insert into example_table  values (null,113,1,0,'mock',now(),now())

主鍵發(fā)生了"斷代",27的主鍵跑丟了...

明明都是sql插入的時候錯誤，為什么結(jié)果會有差異呢，有的時候主鍵會丟失，有的時候主鍵不會丟失呢，想要弄明白這個問題，就需要先明白一下一條sql的執(zhí)行過程：

這里只是針對本文需要關(guān)注的點(diǎn)（相信小伙伴對這個執(zhí)行過程肯定也是非常了解的

審核編輯 黃宇