設(shè)計(jì)MySQL表時(shí)，我們一般會(huì)設(shè)置一個(gè)自增主鍵，從而讓主鍵索引盡可能的保持遞增的趨勢(shì)，這樣可以避免頁分裂，讓MySQL順序?qū)懭?，大大提高M(jìn)ySQL的性能。

但是，自增主鍵只能保持大致遞增，無法保證順序遞增。

當(dāng)我們創(chuàng)建完一個(gè)表后，通過show create table命令，可以看到MySQL定義了AUTO_INCREMENT來指定主鍵的遞增值。

在MySQL5.7之前，這個(gè)遞增值是直接保存在內(nèi)存里面的，當(dāng)服務(wù)器重啟后，MySQL會(huì)讀取表里面的最大主鍵id，然后將最大值+1作為下次遞增的值。

在MySQL8.0時(shí)，將其優(yōu)化為了保存在redo log中，從而實(shí)現(xiàn)了遞增值的持久化。

那都有哪些情況可能導(dǎo)致主鍵不能連續(xù)遞增呢？

首先我們要知道的是，MySQL對(duì)于主鍵遞增值得使用是一次性的，即每次獲取完遞增值之后，不管接下來的語句是否能真正執(zhí)行成功，這個(gè)遞增值都不會(huì)再回收利用了。

1、唯一索引沖突導(dǎo)致的主鍵不連續(xù)

有時(shí)為了滿足業(yè)務(wù)的需要，我們有時(shí)會(huì)對(duì)表中的字段設(shè)置唯一索引。但是當(dāng)唯一索引沖突時(shí)，會(huì)產(chǎn)生什么問題呢？

以上面的user表為例，我們對(duì)name設(shè)置唯一索引。

我們執(zhí)行兩次以下語句：

INSERT into user values (null,'張三','123456');

不難猜到，第二次的執(zhí)行結(jié)果肯定會(huì)報(bào)錯(cuò)：

我們?cè)谏厦嬉呀?jīng)提到，MySQL對(duì)于遞增值的使用是一次性的，那么第二次執(zhí)行插入時(shí)，不管語句成功還是失敗，那么這個(gè)遞增值就會(huì)浪費(fèi)掉。

這時(shí)，我們?cè)賵?zhí)行一條正常的不沖突的插入語句，會(huì)發(fā)現(xiàn)主鍵id產(chǎn)生了間隔。

2、事務(wù)回滾會(huì)造成主鍵不連續(xù)

與唯一索引沖突類似，當(dāng)我們?cè)谝粋€(gè)事務(wù)中執(zhí)行插入語句時(shí)，那么必然會(huì)向MySQL申請(qǐng)一個(gè)遞增值作為主鍵id，如果最后事務(wù)沒有提交，而是回滾，那么這個(gè)遞增值自然也就浪費(fèi)掉了。

3、批量插入會(huì)造成主鍵不連續(xù)

為了保證主鍵id的唯一性，在申請(qǐng)自增id時(shí)，MySQL會(huì)對(duì)申請(qǐng)操作加鎖。一般情況下，這個(gè)申請(qǐng)動(dòng)作會(huì)很快。

對(duì)于一般的批量插入，比如insert into ... values(xxx)，由于插入的Value個(gè)數(shù)可以提前計(jì)算得出，MySQL會(huì)一次性的申請(qǐng)足夠數(shù)量的id，以保證性能。

但是對(duì)于insert into ... select 這種語句就有點(diǎn)麻煩了，由于無法確定到底需要申請(qǐng)多個(gè)主鍵id，如果插入一條申請(qǐng)一個(gè)的話，假設(shè)要插入100萬條記錄，那就得申請(qǐng)100萬次，可想而知性能會(huì)有多么差勁。

所以對(duì)于這種批量插入的語句，MySQL采用了一種翻倍申請(qǐng)的優(yōu)化策略：

語句執(zhí)行時(shí)，第一次申請(qǐng)一個(gè)自增id，第二次申請(qǐng)2個(gè)自增id，第三次申請(qǐng)4個(gè)自增id...

即每次申請(qǐng)的數(shù)量都比上次多一倍，這樣雖然會(huì)浪費(fèi)一些自增id，但是可以保證插入的效率，從性能角度來看，是可以接受的。

自增id為什么不回退復(fù)用

大家可能會(huì)有點(diǎn)疑問，為什么自增id是一次性使用的？

其實(shí)原因也很簡(jiǎn)單，大家稍微一想就明白了。

假設(shè)有兩個(gè)事務(wù)在同時(shí)執(zhí)行，為了保證自增id的唯一性，MySQL會(huì)對(duì)申請(qǐng)動(dòng)作加鎖，然后兩個(gè)事務(wù)各獲得一個(gè)自增id。比如事務(wù)1申請(qǐng)到了自增id100，事務(wù)2申請(qǐng)到了自增id101。

當(dāng)事務(wù)2成功提交，事務(wù)1因?yàn)槟承┰蚧貪L了。

如果我們要回退復(fù)用事務(wù)1的id，將AUTO_INCREMENT又設(shè)置成了100+1，那么下一個(gè)事務(wù)來申請(qǐng)自增id時(shí)，就會(huì)拿到101，而這時(shí)101已經(jīng)被事務(wù)2用掉了，就會(huì)造成主鍵沖突。

當(dāng)然我們也可以每次都讓MySQL檢查一下主鍵是否沖突，如果沖突就跳過這個(gè)id，但是這樣一來，本來申請(qǐng)自增id這個(gè)很輕的動(dòng)作就會(huì)變得很重，對(duì)性能的影響就會(huì)很大。

所以，從性能角度考慮，InnoDB只保證了主鍵id是大致遞增的，而不保證是順序遞增的。?