深入談?wù)凪ySQL中的自增主鍵

MySQL的主鍵可以是自增的，那么如果在斷電重啟后新增的值還會(huì)延續(xù)斷電前的自增值嗎？自增值默認(rèn)為1，那么可不可以改變呢？下面就說(shuō)一下 MySQL的自增值。

特點(diǎn)

保存策略

1、如果存儲(chǔ)引擎是 MyISAM，那么這個(gè)自增值是存儲(chǔ)在數(shù)據(jù)文件中的；

2、如果是 InnoDB引擎，1）在 5.6之前是存儲(chǔ)在內(nèi)存中，沒(méi)有持久化，在重啟后會(huì)去找最大的鍵值，舉個(gè)例子，如果一個(gè)表當(dāng)前數(shù)據(jù)行里最大 id是10，AUTO_INCREMENT=11。這時(shí)候，我們刪除 id=10 的行，AUTO_INCREMENT 還是 11。但如果馬上重啟實(shí)例，重啟后這個(gè)表的 AUTO_INCREMENT 就會(huì)變成 10；

　　　　　　　　　　　 2）在 8.0開(kāi)始，自增值就保存在 redo log中，重啟后會(huì)從 redo log中讀取之前保存的自增值。

自增值的確定

1、如果插入數(shù)據(jù)時(shí) id字段指定為0、null或未指定，那么就把這個(gè)表當(dāng)前的 AUTO_INCREMENT值填到自增字段，并且會(huì)以auto_increment_offset作為初始值，auto_increment_increment為步長(zhǎng)，找出第一個(gè)大于當(dāng)前自增值的值作為新的自增值。

2、如果插入的數(shù)據(jù)的 id字段指定了具體的值，就直接使用語(yǔ)句里的值。

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

自增值的修改

假設(shè)某次要輸入的值是 X，當(dāng)前的自增值是 Y。那么：

1、如果 XY，那么這個(gè)表的自增值不變；

2、如果X≥Y，那么就把當(dāng)前自增值修改為新的自增值。

執(zhí)行過(guò)程

假設(shè)有表t ，id是自增主鍵，在已有 (1,1,1)的情況下，插入一條 (null,1,1)，那么執(zhí)行過(guò)程就如下：

1、執(zhí)行器調(diào)用 InnoDB 引擎接口寫(xiě)入一行，傳入的這一行的值是 (0,1,1);

2、InnoDB 發(fā)現(xiàn)用戶沒(méi)有指定自增 id 的值，獲取表 t 當(dāng)前的自增值 2；

3、將傳入的行的值改成 (2,1,1);

4、將表的自增值改成 3；

5、繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在 c=1 的記錄，所以報(bào) Duplicate key error，語(yǔ)句返回。

帶來(lái)的問(wèn)題

由于上面說(shuō)得這種特性，在一些場(chǎng)景中會(huì)出現(xiàn)主鍵不連續(xù)的現(xiàn)象。

場(chǎng)景1：添加數(shù)據(jù)時(shí)唯一索引重復(fù)

在 c列索引重復(fù)后，原本要分配的主鍵值 2就會(huì)被丟棄，而下次再次插入就從 2 開(kāi)始計(jì)算，也就變成了 3。

場(chǎng)景2：事務(wù)回滾

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)

在第二條語(yǔ)句回滾后分配給其的主鍵 2也會(huì)被丟棄。

場(chǎng)景3：特殊批插入優(yōu)化導(dǎo)致

這里說(shuō)得特殊的批插入指的是insert … select、replace … select 和 load data 語(yǔ)句。為什么說(shuō)這些語(yǔ)句可能會(huì)導(dǎo)致？這就要說(shuō)到自增鎖了。首先自增鎖是為了避免多線程沖突，因?yàn)樵诙嗑€程下，如果同時(shí)有多個(gè)線程來(lái)獲取自增值，那么就可能會(huì)導(dǎo)致同一個(gè)自增值被分配給多條記錄，導(dǎo)致逐漸沖突。所以需要自增鎖，而為什么前面說(shuō)得這些批插入語(yǔ)句會(huì)導(dǎo)致主鍵不連續(xù)，在下面自增鎖部分會(huì)說(shuō)到。

問(wèn)題：在說(shuō)自增鎖之前，先思考一個(gè)問(wèn)題，為什么對(duì)于前兩個(gè)場(chǎng)景，不把自增主鍵值設(shè)為可以回滾的？這樣不就可以避免不連續(xù)了么？

答：因?yàn)樵O(shè)計(jì)成可回滾的會(huì)導(dǎo)致性能下降，看下面這個(gè)場(chǎng)景。

1、假設(shè)事務(wù) A 申請(qǐng)到了 id=2， 事務(wù) B 申請(qǐng)到 id=3，那么這時(shí)候表 t 的自增值是 4，之后繼續(xù)執(zhí)行。

2、事務(wù) B 正確提交了，但事務(wù) A 出現(xiàn)了唯一鍵沖突。

3、如果允許事務(wù) A 把自增 id 回退，也就是把表 t 的當(dāng)前自增值改回 2，那么就會(huì)出現(xiàn)這樣的情況：表里面已經(jīng)有 id=3 的行，而當(dāng)前的自增 id 值是 2。

4、接下來(lái)，繼續(xù)執(zhí)行的其他事務(wù)就會(huì)申請(qǐng)到 id=2，然后再申請(qǐng)到 id=3。這時(shí)，就會(huì)出現(xiàn)插入語(yǔ)句報(bào)錯(cuò)“主鍵沖突”。

而為了解決上面這個(gè)問(wèn)題，就需要從下面兩個(gè)方法中選一個(gè)。

方法一、每次申請(qǐng) id 之前，先判斷表里面是否已經(jīng)存在這個(gè) id。如果存在，就跳過(guò)這個(gè) id。但是，這個(gè)方法的成本很高。因?yàn)?，本?lái)申請(qǐng) id 是一個(gè)很快的操作，現(xiàn)在還要再去主鍵索引樹(shù)上判斷 id 是否存在。

方法二：把自增 id 的鎖范圍擴(kuò)大，必須等到一個(gè)事務(wù)執(zhí)行完成并提交，下一個(gè)事務(wù)才能再申請(qǐng)自增 id。這個(gè)方法的問(wèn)題，就是鎖的粒度太大，系統(tǒng)并發(fā)能力大大下降。

所以，綜合來(lái)看，比如取消自增值回滾的功能。

自增鎖

自增鎖是為了避免在多線程中多個(gè)線程獲取到同一個(gè)主鍵值，導(dǎo)致主鍵沖突。

加鎖策略

5.0版本：范圍是語(yǔ)句，只有等到語(yǔ)句執(zhí)行完后才會(huì)釋放。

5.1.22開(kāi)始：引入了一個(gè)innodb_autoinc_lock_mode參數(shù)，根據(jù)參數(shù)值的不同執(zhí)行不同的策略。默認(rèn)是1。

1、參數(shù)等于0，表示采用之前的策略，即語(yǔ)句執(zhí)行結(jié)束就會(huì)釋放。

2、參數(shù)等于1，對(duì)于普通 insert語(yǔ)句，自增鎖在申請(qǐng)之后立馬釋放；

　　　　　　　對(duì)于 insert...select這樣的批量插入數(shù)據(jù)的語(yǔ)句，會(huì)等到語(yǔ)句執(zhí)行完才會(huì)釋放。加鎖范圍是 select所涉及到的范圍和間隙。

3、參數(shù)等于3，所有的申請(qǐng)自增主鍵的動(dòng)作都是申請(qǐng)后就釋放鎖。

問(wèn)題：為什么默認(rèn)情況下， insert...select這樣的批操作要使用語(yǔ)句級(jí)的鎖？為什么參數(shù)默認(rèn)不是2？

答：因?yàn)閷?duì)于 insert...select這樣的批量插入數(shù)據(jù)的語(yǔ)句，可能會(huì)導(dǎo)致主從不一致的情況發(fā)生。

在 sessionB執(zhí)行完 "create table t2 like t"后，sessionA和 sessionB同時(shí)操作 t2。如果沒(méi)有鎖，那么執(zhí)行過(guò)程就可能會(huì)出現(xiàn)下面的情況。

session B 先插入了兩個(gè)記錄，(1,1,1)、(2,2,2)；然后，session A 來(lái)申請(qǐng)自增 id 得到 id=3，插入了（3,5,5)；之后，session B 繼續(xù)執(zhí)行，插入兩條記錄 (4,3,3)、 (5,4,4)。

雖然這樣看起來(lái)確實(shí)沒(méi)有什么問(wèn)題，但是如果是在集群中，主機(jī)這樣執(zhí)行，提示 binlog是 statement格式的，那么從機(jī)執(zhí)行的順序就有可能和主機(jī)不一致，最終導(dǎo)致主從不一致。所以需要在批量插入時(shí)加鎖。而如果設(shè)置為2，那么如果 binlog不是 row，就會(huì)導(dǎo)致主從數(shù)據(jù)不一致。

所以，要想保證數(shù)據(jù)一致，也保證系統(tǒng)的并發(fā)性，可以有兩種方案：

方案一：將 binlog格式設(shè)為 statement，innodb_autoinc_lock_mode設(shè)為1。

方案二：將 binlog格式設(shè)為 row，innodb_autoinc_lock_mode設(shè)為2。一般我們?yōu)榱吮ＷC MySQL的高可用，都將 binlog設(shè)為 row，所以一般選擇第二種方案。

批插入的優(yōu)化

在批插入時(shí)，由于不知道一次性插入的語(yǔ)句有多少，如果記錄多達(dá)幾千萬(wàn)甚至上億條，那么每次插入都需要分配一次自增值，這樣效率會(huì)很慢，所以 MySQL 對(duì)批操作進(jìn)行了優(yōu)化：

1、語(yǔ)句執(zhí)行過(guò)程中，第一次申請(qǐng)自增 id，會(huì)分配 1 個(gè)；

2、1 個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增 id，會(huì)分配 2 個(gè)；

3、2 個(gè)用完以后，還是這個(gè)語(yǔ)句，第三次申請(qǐng)自增 id，會(huì)分配 4 個(gè)；

4、依此類推，同一個(gè)語(yǔ)句去申請(qǐng)自增 id，每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍。

舉個(gè)例子，執(zhí)行下面的代碼

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，實(shí)際上往表 t2 中插入了 4 行數(shù)據(jù)。但是，這四行數(shù)據(jù)是分三次申請(qǐng)的自增 id，第一次申請(qǐng)到了 id=1，第二次被分配了 id=2 和 id=3， 第三次被分配到 id=4 到 id=7。由于這條語(yǔ)句實(shí)際只用上了 4 個(gè) id，所以 id=5 到 id=7 就被浪費(fèi)掉了。之后，再執(zhí)行 insert into t2 values(null, 5,5)，實(shí)際上插入的數(shù)據(jù)就是（8,5,5)。這就是前面說(shuō)到主鍵不連續(xù)的第三種情況。

insert...select前后操作同一個(gè)表會(huì)用到臨時(shí)表

假設(shè)有表結(jié)構(gòu)

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;

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

如果執(zhí)行的語(yǔ)句是：

insert into t2(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

如果我們查詢慢日志，會(huì)發(fā)現(xiàn)

掃描行數(shù)是1，也就是直接在 t上通過(guò)索引找到那一條記錄，然后插入 t2表。

如果將這條語(yǔ)句改成

insert into t(c,d) (select c+1, d from t force index(c) order by c desc limit 1);

那么此時(shí)查看慢日志就會(huì)發(fā)現(xiàn)變成了 5，這是為什么？就算全查出來(lái)也只會(huì)是4條，這時(shí)我們查看掃描行數(shù)的變化

發(fā)現(xiàn)前后變化是4行，所以確定了是使用了臨時(shí)表，那么就可以確定過(guò)程是：

1、創(chuàng)建臨時(shí)表，表里有兩個(gè)字段 c 和 d。

2、按照索引 c 掃描表 t，依次取 c=4、3、2、1，然后回表，讀到 c 和 d 的值寫(xiě)入臨時(shí)表。這時(shí)，Rows_examined=4。

3、由于語(yǔ)義里面有 limit 1，所以只取了臨時(shí)表的第一行，再插入到表 t 中。這時(shí)，Rows_examined 的值加 1，變成了 5。

至于為什么需要臨時(shí)表，這是為了防止在讀取時(shí)，讀到了剛剛插入的值。

優(yōu)化

因?yàn)閟elect返回的記錄數(shù)較少，所以可以使用內(nèi)存臨時(shí)表來(lái)優(yōu)化，

create temporary table temp_t(c int,d int) engine=memory;
insert into temp_t (select c+1, d from t force index(c) order by c desc limit 1);
insert into t select * from temp_t;
drop table temp_t;

這樣掃描的總行數(shù)只有 select的 1加上臨時(shí)表上的 1。

最后

對(duì)于唯一索引的沖突，可以使用insert into … on duplicate key update來(lái)進(jìn)行沖突后的更新處理，假設(shè)表 t中有(1,1,1)、(2,2,2)兩條記錄，那么執(zhí)行：

在插入時(shí)發(fā)現(xiàn)沖突就對(duì)沖突的記錄進(jìn)行修改操作。

總結(jié)

到此這篇關(guān)于MySQL中的自增主鍵的文章就介紹到這了,更多相關(guān)MySQL自增主鍵內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！