一喊熟、自增主鍵不連續(xù)的原因：
??對于表：
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;
??其中id是自增主鍵字段、c是唯一索引姐刁。
1芥牌、自增值保存位置：
表的結(jié)構(gòu)定義存放在后綴名為.frm的文件中，不會保存自增值聂使。
-
不同的引擎對于自增值的保存策略不同壁拉。
- MyISAM引擎的自增值保存在數(shù)據(jù)文件中。
- InnoDB引擎的自增值柏靶，其實是保存在了內(nèi)存里弃理，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力屎蜓，也就是才實現(xiàn)了“如果發(fā)生重啟痘昌，表的自增值可以恢復為MySQL重啟前的值”，具體情況是：
- 在MySQL 5.7及之前的版本炬转，自增值保存在內(nèi)存里辆苔，并沒有持久化。每次重啟后扼劈，第一次打開表的時候姑子，都會去找自增值的最大值max(id)，然后將max(id)+1作為這個表當前的自增值测僵。舉例來說，如果一個表當前數(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恢復重啟之前的值统诺。
2、自增值修改機制：
-
在MySQL里面疑俭，如果字段id被定義為AUTO_INCREMENT粮呢，在插入一行數(shù)據(jù)的時候，自增值的行為如下：
- 如果插入數(shù)據(jù)時id字段指定為0钞艇、null 或未指定值啄寡，那么就把這個表當前的 AUTO_INCREMENT值填到自增字段；
- 如果插入數(shù)據(jù)時id字段指定了具體的值哩照，就直接使用語句里指定的值挺物。
-
根據(jù)要插入的值和當前自增值的大小關(guān)系，自增值的變更結(jié)果也會有所不同飘弧。假設识藤，某次要插入的值是X，當前的自增值是Y眯牧。
- 如果X<Y蹋岩，那么這個表的自增值不變；
- 如果X≥Y学少，就需要把當前自增值修改為新的自增值剪个。
新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長版确，持續(xù)疊加扣囊，直到找到第一個大于X的值，作為新的自增值绒疗。其中侵歇，auto_increment_offset 和 auto_increment_increment是兩個系統(tǒng)參數(shù)，分別用來表示自增的初始值和步長吓蘑，默認值都是1惕虑。
注意：在一些場景下，使用的就不全是默認值磨镶。比如溃蔫，雙M的主備結(jié)構(gòu)里要求雙寫的時候，就可能會設置成auto_increment_increment=2琳猫，讓一個庫的自增id都是奇數(shù)伟叛，另一個庫的自增id都是偶數(shù)，避免兩個庫生成的主鍵發(fā)生沖突脐嫂。-
當auto_increment_offset和auto_increment_increment都是1的時候统刮，新的自增值的生成邏輯：
- 如果準備插入的值>=當前自增值紊遵，新的自增值就是“準備插入的值+1”；
- 否則侥蒙，自增值不變暗膜。
3、自增值的修改時機：
??假設表t里面已經(jīng)有了(1,1,1)這條記錄辉哥，這時再執(zhí)行一條插入數(shù)據(jù)命令：
insert into t values(null, 1, 1);
<1>桦山、該語句的執(zhí)行流程：
執(zhí)行器調(diào)用InnoDB引擎接口寫入一行，傳入的這一行的值是(0,1,1);
InnoDB發(fā)現(xiàn)用戶沒有指定自增id的值醋旦，獲取表t當前的自增值2恒水；
將傳入的行的值改成(2,1,1);
將表的自增值改成3；
-
繼續(xù)執(zhí)行插入數(shù)據(jù)操作饲齐，由于已經(jīng)存在c=1的記錄钉凌，所以報Duplicate key error，語句返回捂人。
insert(null, 1,1)唯一鍵沖突
??可以看到御雕，這個表的自增值由2改成3，是在真正執(zhí)行插入數(shù)據(jù)的操作之前滥搭。這個語句真正執(zhí)行的時候酸纲，因為碰到唯一鍵c沖突，所以id=2這一行并沒有插入成功瑟匆，但也沒有將自增值再改回去闽坡。所以，在這之后愁溜，再插入新的數(shù)據(jù)行時疾嗅，拿到的自增id就是3。也就是說冕象，出現(xiàn)了自增主鍵不連續(xù)的情況代承。
<2>、完整復現(xiàn)：
4渐扮、自增值不能回退的原因：
- 假設有兩個并行執(zhí)行的事務论悴，在申請自增值的時候，為了避免兩個事務申請到相同的自增id墓律，肯定要加鎖意荤，然后順序申請。
- 假設事務A申請到了id=2只锻， 事務B申請到id=3，那么這時候表t的自增值是4紫谷，之后繼續(xù)執(zhí)行齐饮。
- 事務B正確提交了捐寥，但事務A出現(xiàn)了唯一鍵沖突。
- 如果允許事務A把自增id回退祖驱，也就是把表t的當前自增值改回2握恳，那么就會出現(xiàn)這樣的情況：表里面已經(jīng)有id=3的行，而當前的自增id值是2捺僻。
- 接下來乡洼，繼續(xù)執(zhí)行的其他事務就會申請到id=2，然后再申請到id=3匕坯。這時束昵，就會出現(xiàn)插入語句報錯“主鍵沖突”。
- 對于主鍵沖突葛峻，有兩種方法解決方法：
- 每次申請id之前锹雏，先判斷表里面是否已經(jīng)存在這個id。如果存在术奖，就跳過這個id礁遵。但是，這個方法的成本很高采记。因為佣耐，本來申請id是一個很快的操作，現(xiàn)在還要再去主鍵索引樹上判斷id是否存在唧龄。
- 把自增id的鎖范圍擴大兼砖，必須等到一個事務執(zhí)行完成并提交，下一個事務才能再申請自增id选侨。這個方法的問題掖鱼，就是鎖的粒度太大，系統(tǒng)并發(fā)能力大大下降援制。
- 可見戏挡，這兩個方法都會導致性能問題。造成這些麻煩的罪魁禍首晨仑，就是我們假設的這個“允許自增id回退”的前提導致的褐墅。因此，InnoDB放棄了這個設計洪己，語句執(zhí)行失敗也不回退自增id妥凳。也正是因為這樣，所以才只保證了自增id是遞增的答捕，但不保證是連續(xù)的逝钥。
5、自增鎖的優(yōu)化：
<1>拱镐、自增鎖：
在MySQL 5.0版本的時候艘款，自增鎖的范圍是語句級別持际。也就是說，如果一個語句申請了一個表自增鎖哗咆，這個鎖會等語句執(zhí)行結(jié)束以后才釋放蜘欲。顯然，這樣設計會影響并發(fā)度晌柬。
-
MySQL 5.1.22版本引入了一個新策略姥份，新增參數(shù)innodb_autoinc_lock_mode，默認值是1年碘。
這個參數(shù)的值被設置為0時澈歉，表示采用之前MySQL 5.0版本的策略，即語句執(zhí)行結(jié)束后才釋放鎖盛泡；
-
這個參數(shù)的值被設置為1時：
- 普通insert語句闷祥，自增鎖在申請之后就馬上釋放；
- 類似insert … select這樣的批量插入數(shù)據(jù)的語句傲诵，自增鎖還是要等語句結(jié)束后才被釋放凯砍；
這個參數(shù)的值被設置為2時，所有的申請自增主鍵的動作都是申請后就釋放鎖拴竹。
<2>悟衩、默認設置下，insert … select 要使用語句級的鎖的原因：
??這么設計是為了數(shù)據(jù)的一致性栓拜。對于下面的場景：
- 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)。
??此時就導致了session A和session B不一致中狂，而當binlog_format=statement時凫碌，由于兩個session是同時執(zhí)行插入數(shù)據(jù)命令的，所以binlog里面對表t2的更新日志只有兩種情況：要么先記session A的胃榕，要么先記session B的盛险。但不論是哪一種惧眠，這個binlog拿去從庫執(zhí)行性湿，或者用來恢復臨時實例川背，備庫和臨時實例里面届宠，session B這個語句執(zhí)行出來，生成的結(jié)果里面鸟蜡，id都是連續(xù)的。這時挺邀，這個庫就發(fā)生了數(shù)據(jù)不一致揉忘。
<3>、庫發(fā)生數(shù)據(jù)不一致的原因及解決方法：
(1)端铛、原因：
??因為原庫session B的insert語句泣矛，生成的id不連續(xù)。這個不連續(xù)的id禾蚕，用statement格式的binlog來串行執(zhí)行您朽，是執(zhí)行不出來的。
(2)换淆、解決方法：
- 一種思路是哗总，讓原庫的批量插入數(shù)據(jù)語句，固定生成連續(xù)的id值倍试。所以讯屈，自增鎖直到語句執(zhí)行結(jié)束才釋放，就是為了達到這個目的县习。
- 另一種思路是涮母，在binlog里面把插入數(shù)據(jù)的操作都如實記錄進來，到備庫執(zhí)行的時候躁愿，不再依賴于自增主鍵去生成叛本。這種情況，其實就是innodb_autoinc_lock_mode設置為2彤钟，同時binlog_format設置為row来候。
??因此，在生產(chǎn)上样勃，尤其是有批量插入數(shù)據(jù)的場景時吠勘，從并發(fā)插入數(shù)據(jù)性能的角度考慮，建議這樣設置：innodb_autoinc_lock_mode=2 峡眶，并且 binlog_format=row.這樣做剧防，既能提升并發(fā)性，又不會出現(xiàn)數(shù)據(jù)一致性問題辫樱。
需要注意的是峭拘，這里說的批量插入數(shù)據(jù)，包含的語句類型是insert … select、replace … select和load data語句鸡挠。
<4>辉饱、對于批量插入數(shù)據(jù)的語句，MySQL有一個批量申請自增id的策略：
- 語句執(zhí)行過程中拣展，第一次申請自增id彭沼，會分配1個；
- 1個用完以后备埃，這個語句第二次申請自增id姓惑，會分配2個；
- 2個用完以后按脚，還是這個語句于毙，第三次申請自增id，會分配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)贰剥。
