由于自增主鍵可以讓主鍵索引盡量地保持遞增順序插入铝条，避免了頁分裂靖苇，因此索引更緊湊。但業(yè)務(wù)設(shè)計依賴于自增主鍵的連續(xù)性是不對的班缰，也就是說贤壁，這個設(shè)計假設(shè)自增主鍵是連續(xù)的。但實際上埠忘，這樣的假設(shè)是錯的脾拆，因為自增主鍵不能保證連續(xù)遞增。

本文我們就來說說這個問題莹妒，看看什么情況下自增主鍵會出現(xiàn) “空洞”名船？為了便于說明，我們創(chuàng)建一個表 t旨怠，其中 id 是自增主鍵字段渠驼、c 是唯一索引。

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

自增值保存在哪兒鉴腻？

• 在這個空表 t 里面執(zhí)行 insert into t values(null, 1, 1); 插入一行數(shù)據(jù)迷扇，再執(zhí)行 show create table 命令，就可以看到如下圖所示的結(jié)果：

自動生成的 AUTO_INCREMENT 值

• 可以看到爽哎，表定義里面出現(xiàn)了一個 AUTO_INCREMENT=2蜓席，表示下一次插入數(shù)據(jù)時，如果需要自動生成自增值课锌，會生成 id=2厨内。
• 其實，這個輸出結(jié)果容易引起這樣的誤解：自增值是保存在表結(jié)構(gòu)定義里的产镐。實際上隘庄，表的結(jié)構(gòu)定義存放在后綴名為.frm 的文件中，但是并不會保存自增值癣亚。
• 不同的引擎對于自增值的保存策略不同丑掺。
  • MyISAM 引擎的自增值保存在數(shù)據(jù)文件中。
  • InnoDB 引擎的自增值述雾，其實是保存在了內(nèi)存里街州，并且到了 MySQL 8.0 版本后兼丰，才有了“自增值持久化”的能力，也就是才實現(xiàn)了“如果發(fā)生重啟唆缴，表的自增值可以恢復(fù)為 MySQL 重啟前的值”鳍征，具體情況是：
    • 在 MySQL 5.7 及之前的版本，自增值保存在內(nèi)存里面徽，并沒有持久化艳丛。每次重啟后，第一次打開表的時候趟紊，都會去找自增值的最大值 max(id)氮双，然后將 max(id)+1 作為這個表當(dāng)前的自增值。
    • 舉例來說霎匈，如果一個表當(dāng)前數(shù)據(jù)行里最大的 id 是 10戴差，AUTO_INCREMENT=11。這時候铛嘱，我們刪除 id=10 的行暖释，AUTO_INCREMENT 還是 11。但如果馬上重啟實例墨吓，重啟后這個表的 AUTO_INCREMENT 就會變成 10球匕。
    • 也就是說，MySQL 重啟可能會修改一個表的 AUTO_INCREMENT 的值肛真。
      在 MySQL 8.0 版本谐丢，將自增值的變更記錄在了 redo log 中爽航，重啟的時候依靠 redo log 恢復(fù)重啟之前的值蚓让。
• 理解了 MySQL 對自增值的保存策略以后，我們再看看自增值修改機(jī)制讥珍。

自增值修改機(jī)制

• 在 MySQL 里面历极，如果字段 id 被定義為 AUTO_INCREMENT，在插入一行數(shù)據(jù)的時候衷佃，自增值的行為如下：
  • 如果插入數(shù)據(jù)時 id 字段指定為 0趟卸、null 或未指定值，那么就把這個表當(dāng)前的 AUTO_INCREMENT 值填到自增字段氏义；
  • 如果插入數(shù)據(jù)時 id 字段指定了具體的值锄列，就直接使用語句里指定的值。
• 根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系惯悠，自增值的變更結(jié)果也會有所不同邻邮。假設(shè)，某次要插入的值是 X克婶，當(dāng)前的自增值是 Y筒严。
  1. 如果 X<Y丹泉，那么這個表的自增值不變；
  2. 如果 X≥Y鸭蛙，就需要把當(dāng)前自增值修改為新的自增值摹恨。
• 新的自增值生成算法是：從 auto_increment_offset 開始，以 auto_increment_increment 為步長娶视，持續(xù)疊加晒哄，直到找到第一個大于 X 的值，作為新的自增值肪获。
• 其中揩晴，auto_increment_offset 和 auto_increment_increment 是兩個系統(tǒng)參數(shù)，分別用來表示自增的初始值和步長贪磺，默認(rèn)值都是 1硫兰。

備注：在一些場景下，使用的就不全是默認(rèn)值寒锚。比如桦他，雙 M 的主備結(jié)構(gòu)里要求雙寫的時候儒将，我們就可能會設(shè)置成 auto_increment_increment=2，讓一個庫的自增 id 都是奇數(shù)，另一個庫的自增 id 都是偶數(shù)郭厌，避免兩個庫生成的主鍵發(fā)生沖突。

• 當(dāng) auto_increment_offset 和 auto_increment_increment 都是 1 的時候承粤，新的自增值生成邏輯很簡單妆距，就是：
  1. 如果準(zhǔn)備插入的值 >= 當(dāng)前自增值，新的自增值就是“準(zhǔn)備插入的值 +1”拣技；
  2. 否則千诬，自增值不變。
• 這就引入了我們文章開頭提到的問題膏斤，在這兩個參數(shù)都設(shè)置為 1 的時候徐绑，自增主鍵 id 卻不能保證是連續(xù)的，這是什么原因呢莫辨？

自增值的修改時機(jī)

• 假設(shè)傲茄，表 t 里面已經(jīng)有了 (1,1,1) 這條記錄，這時我再執(zhí)行一條插入數(shù)據(jù)命令：

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

• 這個語句的執(zhí)行流程就是：
  1. 執(zhí)行器調(diào)用 InnoDB 引擎接口寫入一行沮榜，傳入的這一行的值是 (0,1,1);
  2. InnoDB 發(fā)現(xiàn)用戶沒有指定自增 id 的值盘榨，獲取表 t 當(dāng)前的自增值 2；
  3. 將傳入的行的值改成 (2,1,1);
  4. 將表的自增值改成 3蟆融；
  5. 繼續(xù)執(zhí)行插入數(shù)據(jù)操作草巡，由于已經(jīng)存在 c=1 的記錄，所以報 Duplicate key error振愿，語句返回捷犹。

insert(null, 1,1) 唯一鍵沖突

• 可以看到弛饭，這個表的自增值改成 3，是在真正執(zhí)行插入數(shù)據(jù)的操作之前萍歉。這個語句真正執(zhí)行的時候侣颂，因為碰到唯一鍵 c 沖突，所以 id=2 這一行并沒有插入成功枪孩，但也沒有將自增值再改回去憔晒。
• 所以，在這之后蔑舞，再插入新的數(shù)據(jù)行時拒担，拿到的自增 id 就是 3。也就是說攻询，出現(xiàn)了自增主鍵不連續(xù)的情況从撼。如下圖所示：

自增主鍵 id 不連續(xù)的復(fù)現(xiàn)步驟

• 可以看到，這個操作序列復(fù)現(xiàn)了一個自增主鍵 id 不連續(xù)的現(xiàn)場 (沒有 id=2 的行）钧栖〉土悖可見，唯一鍵沖突是導(dǎo)致自增主鍵 id 不連續(xù)的第一種原因拯杠。
• 同樣地掏婶，事務(wù)回滾也會產(chǎn)生類似的現(xiàn)象，這就是第二種原因潭陪。如下所示：

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)

• 你可能會問雄妥，為什么在出現(xiàn)唯一鍵沖突或者回滾的時候，MySQL 沒有把表 t 的自增值改回去呢依溯？如果把表 t 的當(dāng)前自增值從 3 改回 2老厌，再插入新數(shù)據(jù)的時候，不就可以生成 id=2 的一行數(shù)據(jù)了嗎誓沸？
• 其實梅桩，MySQL 這么設(shè)計是為了提升性能壹粟。接下來拜隧，我們分析一下這個設(shè)計思路，看看自增值為什么不能回退趁仙。
• 假設(shè)有兩個并行執(zhí)行的事務(wù)洪添，在申請自增值的時候，為了避免兩個事務(wù)申請到相同的自增 id雀费，肯定要加鎖干奢，然后順序申請。
  1. 假設(shè)事務(wù) A 申請到了 id=2盏袄， 事務(wù) B 申請到 id=3忿峻，那么這時候表 t 的自增值是 4薄啥，之后繼續(xù)執(zhí)行。
  2. 事務(wù) B 正確提交了逛尚，但事務(wù) A 出現(xiàn)了唯一鍵沖突垄惧。
  3. 如果允許事務(wù) A 把自增 id 回退，也就是把表 t 的當(dāng)前自增值改回 2绰寞，那么就會出現(xiàn)這樣的情況：表里面已經(jīng)有 id=3 的行到逊，而當(dāng)前的自增 id 值是 2。
  4. 接下來滤钱，繼續(xù)執(zhí)行的其他事務(wù)就會申請到 id=2觉壶，然后再申請到 id=3。這時件缸，就會出現(xiàn)插入語句報錯“主鍵沖突”铜靶。
• 而為了解決這個主鍵沖突，有兩種方法：
  1. 每次申請 id 之前他炊，先判斷表里面是否已經(jīng)存在這個 id旷坦。如果存在，就跳過這個 id佑稠。但是秒梅，這個方法的成本很高。因為舌胶，本來申請 id 是一個很快的操作捆蜀，現(xiàn)在還要再去主鍵索引樹上判斷 id 是否存在。
  2. 把自增 id 的鎖范圍擴(kuò)大幔嫂，必須等到一個事務(wù)執(zhí)行完成并提交辆它，下一個事務(wù)才能再申請自增 id。這個方法的問題履恩，就是鎖的粒度太大锰茉，系統(tǒng)并發(fā)能力大大下降。
• 可見切心，這兩個方法都會導(dǎo)致性能問題飒筑。造成這些麻煩的罪魁禍?zhǔn)祝褪俏覀兗僭O(shè)的這個“允許自增 id 回退”的前提導(dǎo)致的绽昏。
• 因此协屡，InnoDB 放棄了這個設(shè)計，語句執(zhí)行失敗也不回退自增 id全谤。也正是因為這樣肤晓，所以才只保證了自增 id 是遞增的，但不保證是連續(xù)的。

自增鎖的優(yōu)化

• 可以看到补憾，自增 id 鎖并不是一個事務(wù)鎖漫萄，而是每次申請完就馬上釋放，以便允許別的事務(wù)再申請盈匾。其實卷胯，在 MySQL 5.1 版本之前，并不是這樣的威酒。
• 在 MySQL 5.0 版本的時候窑睁，自增鎖的范圍是語句級別。也就是說葵孤，如果一個語句申請了一個表自增鎖担钮，這個鎖會等語句執(zhí)行結(jié)束以后才釋放。顯然尤仍，這樣設(shè)計會影響并發(fā)度箫津。
• MySQL 5.1.22 版本引入了一個新策略，新增參數(shù) innodb_autoinc_lock_mode宰啦，默認(rèn)值是 1苏遥。
  1. 這個參數(shù)的值被設(shè)置為 0 時，表示采用之前 MySQL 5.0 版本的策略赡模，即語句執(zhí)行結(jié)束后才釋放鎖田炭；
  2. 這個參數(shù)的值被設(shè)置為 1 時：
     • 普通 insert 語句，自增鎖在申請之后就馬上釋放漓柑；
     • 類似 insert … select 這樣的批量插入數(shù)據(jù)的語句教硫，自增鎖還是要等語句結(jié)束后才被釋放；
  3. 這個參數(shù)的值被設(shè)置為 2 時辆布，所有的申請自增主鍵的動作都是申請后就釋放鎖瞬矩。
• 為什么默認(rèn)設(shè)置下，insert … select 要使用語句級的鎖锋玲？為什么這個參數(shù)的默認(rèn)值不是 2景用？答案是，這么設(shè)計還是為了數(shù)據(jù)的一致性惭蹂。如下圖所示：

批量插入數(shù)據(jù)的自增鎖

• 在這個例子里伞插，往表 t1 中插入了 4 行數(shù)據(jù)，然后創(chuàng)建了一個相同結(jié)構(gòu)的表 t2剿干，然后兩個 session 同時執(zhí)行向表 t2 中插入數(shù)據(jù)的操作蜂怎。
• 你可以設(shè)想一下，如果 session B 是申請了自增值以后馬上就釋放自增鎖置尔，那么就可能出現(xiàn)這樣的情況：
  • session B 先插入了兩個記錄，(1,1,1)氢伟、(2,2,2)榜轿；
  • 然后幽歼，session A 來申請自增 id 得到 id=3，插入了（3,5,5)谬盐；
  • 之后甸私，session B 繼續(xù)執(zhí)行，插入兩條記錄 (4,3,3)飞傀、 (5,4,4)皇型。
• 你可能會說，這也沒關(guān)系吧砸烦，畢竟 session B 的語義本身就沒有要求表 t2 的所有行的數(shù)據(jù)都跟 session A 相同弃鸦。
• 是的，從數(shù)據(jù)邏輯上看是對的幢痘。但是唬格，如果我們現(xiàn)在的 binlog_format=statement，你可以設(shè)想下颜说，binlog 會怎么記錄呢购岗？
• 由于兩個 session 是同時執(zhí)行插入數(shù)據(jù)命令的，所以 binlog 里面對表 t2 的更新日志只有兩種情況：要么先記 session A 的门粪，要么先記 session B 的喊积。
• 但不論是哪一種，這個 binlog 拿去從庫執(zhí)行玄妈，或者用來恢復(fù)臨時實例注服，備庫和臨時實例里面，session B 這個語句執(zhí)行出來措近，生成的結(jié)果里面溶弟，id 都是連續(xù)的。這時瞭郑，這個庫就發(fā)生了數(shù)據(jù)不一致辜御。
• 其實，這是因為原庫 session B 的 insert 語句屈张，生成的 id 不連續(xù)擒权。這個不連續(xù)的 id，用 statement 格式的 binlog 來串行執(zhí)行阁谆，是執(zhí)行不出來的碳抄。
• 而要解決這個問題，有兩種思路：
  • 一種思路是场绿，讓原庫的批量插入數(shù)據(jù)語句剖效，固定生成連續(xù)的 id 值。所以，自增鎖直到語句執(zhí)行結(jié)束才釋放璧尸，就是為了達(dá)到這個目的咒林。
  • 另一種思路是，在 binlog 里面把插入數(shù)據(jù)的操作都如實記錄進(jìn)來爷光，到備庫執(zhí)行的時候垫竞，不再依賴于自增主鍵去生成。這種情況蛀序，其實就是 innodb_autoinc_lock_mode 設(shè)置為 2欢瞪，同時 binlog_format 設(shè)置為 row。
• 因此徐裸，在生產(chǎn)上遣鼓，尤其是有 insert … select 這種批量插入數(shù)據(jù)的場景時，從并發(fā)插入數(shù)據(jù)性能的角度考慮倦逐，建議你這樣設(shè)置：innodb_autoinc_lock_mode=2 譬正，并且 binlog_format=row. 這樣做，既能提升并發(fā)性檬姥，又不會出現(xiàn)數(shù)據(jù)一致性問題曾我。
• 需要注意的是，這里說的批量插入數(shù)據(jù)健民，包含的語句類型是 insert … select抒巢、replace … select 和 load data 語句。
• 但是秉犹，在普通的 insert 語句里面包含多個 value 值的情況下蛉谜，即使 innodb_autoinc_lock_mode 設(shè)置為 1，也不會等語句執(zhí)行完成才釋放鎖崇堵。因為這類語句在申請自增 id 的時候型诚，是可以精確計算出需要多少個 id 的，然后一次性申請鸳劳，申請完成后鎖就可以釋放了狰贯。
• 也就是說，批量插入數(shù)據(jù)的語句赏廓，之所以需要這么設(shè)置涵紊，是因為“不知道要預(yù)先申請多少個 id”。
• 既然預(yù)先不知道要申請多少個自增 id幔摸，那么一種直接的想法就是需要一個時申請一個摸柄。但如果一個 select … insert 語句要插入 10 萬行數(shù)據(jù)，按照這個邏輯的話就要申請 10 萬次既忆。顯然驱负，這種申請自增 id 的策略嗦玖，在大批量插入數(shù)據(jù)的情況下，不但速度慢电媳，還會影響并發(fā)插入的性能踏揣。
• 因此庆亡，對于批量插入數(shù)據(jù)的語句匾乓，MySQL 有一個批量申請自增 id 的策略：
  1. 語句執(zhí)行過程中，第一次申請自增 id又谋，會分配 1 個拼缝；
  2. 1 個用完以后，這個語句第二次申請自增 id彰亥，會分配 2 個咧七；
  3. 2 個用完以后，還是這個語句任斋，第三次申請自增 id继阻，會分配 4 個；
  4. 依此類推废酷，同一個語句去申請自增 id瘟檩，每次申請到的自增 id 個數(shù)都是上一次的兩倍。

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

• insert…select澈蟆，實際上往表 t2 中插入了 4 行數(shù)據(jù)墨辛。但是，這四行數(shù)據(jù)是分三次申請的自增 id趴俘，第一次申請到了 id=1睹簇，第二次被分配了 id=2 和 id=3， 第三次被分配到 id=4 到 id=7寥闪。
• 由于這條語句實際只用上了 4 個 id太惠，所以 id=5 到 id=7 就被浪費掉了。之后疲憋，再執(zhí)行 insert into t2 values(null, 5,5)凿渊，實際上插入的數(shù)據(jù)就是（8,5,5)。
• 這是主鍵 id 出現(xiàn)自增 id 不連續(xù)的第三種原因柜某。

小結(jié)

• 在 MyISAM 引擎里面嗽元，自增值是被寫在數(shù)據(jù)文件上的。而在 InnoDB 中喂击，自增值是被記錄在內(nèi)存的剂癌。MySQL 直到 8.0 版本，才給 InnoDB 表的自增值加上了持久化的能力翰绊，確保重啟前后一個表的自增值不變佩谷。
• MySQL 5.1.22 版本開始引入的參數(shù) innodb_autoinc_lock_mode旁壮，控制了自增值申請時的鎖范圍。從并發(fā)性能的角度考慮谐檀，我建議你將其設(shè)置為 2抡谐，同時將 binlog_format 設(shè)置為 row。在前面的文章中其實多次提到桐猬，binlog_format 設(shè)置為 row麦撵，是很有必要的。今天的例子給這個結(jié)論多了一個理由溃肪。