近期聽聞寒潮來臨帖蔓，作為一個熱愛學(xué)習(xí)的人企软，勇于面對疾風(fēng)庐扫，在第二次寒潮來臨時，與某大廠面試官鏖戰(zhàn)了近1個多小時后仗哨，本以為大獲全勝形庭，誰料大佬隨口給來一記重拳：自增主鍵連續(xù)嗎？

我們都知道厌漂，創(chuàng)建表時一般都會寫上id int(11) NOT NULL AUTO_INCREMENT代表主鍵的連續(xù)性萨醒，那么當(dāng)然要毫不客氣的告訴他，連續(xù)苇倡！必須連續(xù)富纸！
如果這么說了，那么恭喜你雏节，可能就掛了啊胜嗓，兄dei.
聽我娓娓道來
首先你要這么告訴他

1.主鍵連續(xù)性的意義

自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂钩乍，索引更緊湊辞州。所以一般都會設(shè)置為主鍵自增。

但并不是所有情況下自增主鍵都會連續(xù)寥粹。下面我會舉幾個例子來說明下变过，什么情況下，主鍵自增但不是連續(xù)的涝涤。
在這之前呢媚狰，我大大方方的告訴面試官了2個事情，自增值保存在哪里阔拳，自增值是如何修改的崭孤。

2.自增值保存在哪里

先來構(gòu)建一張表

CREATE TABLE `user2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(6) NOT NULL DEFAULT '0',
  `phone` varchar(24) NOT NULL DEFAULT '',
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='用戶信息'

看 AUTO_INCREMENT=0 證明下一個插入的值ID應(yīng)該從1開始
其實，這個輸出結(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ù)重啟之前的值麦箍。

3.自增值怎么修改（what, how， when ）

3.1如何修改

當(dāng)定義主鍵為AUTO_INCREMENT時陶珠，插入一條數(shù)據(jù)時挟裂，會做如下行為
1.當(dāng)插入數(shù)據(jù)時，ID指定為0 或者 null時揍诽，就把這個表當(dāng)前的AUTO_INCREMENT填寫到自增字段ID上
2.如果ID指定了值诀蓉，就用指定的值插入栗竖。

3.2 什么時候修改

1.首先插入insert into user2(id,name, age, phone) values (null, '主鍵', 12, '32432432');,因為沒有指定id，先獲取表的自增值當(dāng)做主鍵（假設(shè)此時為1）
2.將傳入的值改為insert into user2(id,name, age, phone) values (1,'主鍵', 12, '32432432');
3.將自增值改為2
4.繼續(xù)執(zhí)行操作

可以看到渠啤，這個表的自增值改成 2划滋，是在真正執(zhí)行插入數(shù)據(jù)的操作之前。

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

4.1 事務(wù)回滾

假設(shè)主鍵連續(xù)的情況下埃篓，t5時刻和t3 時刻的主鍵都應(yīng)該是1，因為事務(wù)A 發(fā)生了回滾根资，假如沒有發(fā)生回滾架专，那么事物B的ID應(yīng)該是4.
看效果：

結(jié)果卻是主鍵不是1而是4，為什么會是這樣呢玄帕？
為什么在出現(xiàn)回滾的時候部脚，MySQL 沒有把表 的自增值改回去呢？如果把表 的當(dāng)前自增值從 4 改回 1裤纹，再插入新數(shù)據(jù)的時候委刘，不就可以生成 id=2 的一行數(shù)據(jù)了嗎？其實鹰椒，MySQL 這么設(shè)計是為了提升性能锡移。接下來，分析一下這個設(shè)計思路漆际，看看自增值為什么不能回退淆珊。

假設(shè)有兩個并行執(zhí)行的事務(wù)，在申請自增值的時候奸汇，為了避免兩個事務(wù)申請到相同的自增 id施符，肯定要加鎖，然后順序申請擂找。

1. 假設(shè)事務(wù) A 申請到了 id=1戳吝，2，3贯涎， 事務(wù) B 申請到 id=4听哭，那么這時候表的自增值是 5，之后繼續(xù)執(zhí)行柬采。
2. 事務(wù) B 正確提交了欢唾，但事務(wù) A 出現(xiàn)了回滾
3. 如果允許事務(wù) A 把自增 id 回退，也就是把表 的當(dāng)前自增值改回 2粉捻，那么就會出現(xiàn)這樣的情況：表里面已經(jīng)有 id=4的行礁遣，而當(dāng)前的自增 id 值是 2。
4. 接下來肩刃，繼續(xù)執(zhí)行的其他事務(wù)就會申請到 id=2祟霍，然后再申請到 id=3杏头，最后到ID=4。這時沸呐，就會出現(xiàn)插入語句報錯“主鍵沖突”醇王。

那么如果要解決這個問題，有2種可行方案：

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ù)的声畏。

所以你會看到當(dāng)事務(wù)A執(zhí)行完撞叽，但未提交時，事務(wù)B 的show create table user2 的 AUTO_INCREMENT=4也就證實了這個說法插龄。

4.2 唯一健沖突

其實當(dāng)唯一健沖突時愿棋，也同樣發(fā)生了回滾，所以具體情況和4.1所說的事務(wù)回滾一樣均牢。

4.3自增主鍵的批量申請

上SQL

insert into user2 (id,name,age)values(null, 1,1);
insert into user2 (id,name,age) values(null, 2,2);
insert into user2 (id,name,age) values(null, 3,3);
insert into user2 (id,name,age) values(null, 4,4);
create table user3 like user2;
insert into user3(name,age) select name,age from user2;
show create  table user3;
insert into user3 (id,name,age) values(null, 5,5);
show create  table user3;

結(jié)果：

可以看到此時主鍵發(fā)生了不連續(xù)性糠雨。
這是什么情況呢。那么一起來看下主鍵鎖吧徘跪。

5.自增鎖

5.1自增鎖的設(shè)計歷史

在 MySQL 5.0 版本的時候甘邀，自增鎖的范圍是語句級別琅攘。也就是說，如果一個語句申請了一個表自增鎖松邪，這個鎖會等語句執(zhí)行結(jié)束以后才釋放坞琴。顯然，這樣設(shè)計會影響并發(fā)度逗抑。

MySQL 5.1.22 版本引入了一個新策略剧辐，新增參數(shù) innodb_autoinc_lock_mode，默認(rèn)值是 1邮府。

• 這個參數(shù)的值被設(shè)置為 0 時浙于，表示采用之前 MySQL 5.0 版本的策略，即語句執(zhí)行結(jié)束后才釋放鎖挟纱；
• 這個參數(shù)的值被設(shè)置為 1 時：普通 insert 語句，自增鎖在申請之后就馬上釋放腐宋；類似 insert … select 這樣的批量插入數(shù)據(jù)的語句紊服，自增鎖還是要等語句結(jié)束后才被釋放；
• 這個參數(shù)的值被設(shè)置為 2 時胸竞，所有的申請自增主鍵的動作都是申請后就釋放鎖欺嗤。

當(dāng)我說完這個的時候，隱約像是給自己挖了一個大坑卫枝，面試官抓住機(jī)會問到：為什么默認(rèn)設(shè)置下煎饼，insert … select 要使用語句級的鎖？為什么這個參數(shù)的默認(rèn)值不是 2校赤？

大大方方的告訴他吆玖，這么設(shè)計是為了數(shù)據(jù)一致性。
回想下4.3的SQL

你可以設(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 會怎么記錄呢？

binlog 拿去從庫執(zhí)行田晚，或者用來恢復(fù)臨時實例嘱兼，備庫和臨時實例里面，session B 這個語句執(zhí)行出來贤徒，生成的結(jié)果里面芹壕，id 都是連續(xù)的。這時接奈，這個庫就發(fā)生了數(shù)據(jù)不一致踢涌。

其實，這是因為原庫 session B 的 insert 語句序宦，生成的 id 不連續(xù)睁壁。這個不連續(xù)的 id，用 statement 格式的 binlog 來串行執(zhí)行互捌，是執(zhí)行不出來的潘明。

所以解決辦法如下2種：
1.讓原庫的批量插入數(shù)據(jù)語句，固定生成連續(xù)的 id 值秕噪。所以钉疫，自增鎖直到語句執(zhí)行結(jié)束才釋放，就是為了達(dá)到這個目的巢价。

2.在 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ù)一致性問題测垛。（到這里不信面試官不給你豎個大拇指。）

但是秧均，在普通的 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 個；
2.2 個用完以后帆离，還是這個語句蔬蕊，第三次申請自增 id，會分配 4 個哥谷；
3.依此類推岸夯，同一個語句去申請自增 id麻献，每次申請到的自增 id 個數(shù)都是上一次的兩倍。
所以你看4.3的結(jié)果圖自增主鍵已經(jīng)到了8

現(xiàn)在這般猜扮，面試官漏出了滿意的笑容勉吻。