自增主鍵是指在自增列上定義的主鍵诅妹。自增主鍵可以讓主鍵索引盡量地保持遞增順序插入罚勾，避免了頁(yè)分裂，索引也更緊湊吭狡。
在建表語(yǔ)句中一般是這么定義的：
NOT NULL PRIMARY KEY AUTO_INCREMENT
如：
CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`step` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
一尖殃、自增值保存在哪里
不同的數(shù)據(jù)庫(kù)引擎對(duì)于自增值的保存策略不同：
- MyISAM 引擎：自增值保存在數(shù)據(jù)文件中；
- InnoDB引擎：
- 在 MySQL 5.7 及之前的版本划煮，自增值保存在內(nèi)存里送丰，并沒(méi)有持久化。每次重啟后弛秋，第一次打開(kāi)表的時(shí)候器躏，都會(huì)去找自增值的最大值
max(id)，然后將max(id)+1作為這個(gè)表當(dāng)前的自增值蟹略。 - 在 MySQL 8.0 版本登失，將自增值的變更記錄在了 redo log 中，重啟的時(shí)候依靠 redo log 恢復(fù)重啟之前的值挖炬。
- 在 MySQL 5.7 及之前的版本划煮，自增值保存在內(nèi)存里送丰，并沒(méi)有持久化。每次重啟后弛秋，第一次打開(kāi)表的時(shí)候器躏，都會(huì)去找自增值的最大值
二揽浙、自增值修改機(jī)制
假設(shè)數(shù)據(jù)表當(dāng)前的自增值是 Y，在插入一行數(shù)據(jù)的時(shí)候，數(shù)據(jù)庫(kù)的執(zhí)行情況如下：
- 如果插入數(shù)據(jù)時(shí)自增字段指定了具體的值 X馅巷，就直接使用值 X 填到該自增字段膛虫，該表的自增值是否修改的判斷如下：
- 若 X<Y，則該表的自增值不變钓猬；
- 若 X≥Y走敌，則需要修改該表的自增值為 X + auto_increment_increment（步長(zhǎng)）。
- 如果插入數(shù)據(jù)時(shí)自增字段未指定值或指定為 0逗噩、null，則把該表當(dāng)前的自增值（即 Y）填到自增字段跌榔，并修改該表的自增值為 Y + auto_increment_increment（步長(zhǎng)）异雁。
實(shí)際上，MySQL 維護(hù)著兩個(gè)系統(tǒng)參數(shù)：auto_increment_offset 和 auto_increment_increment僧须，分別用來(lái)表示自增的初始值和步長(zhǎng)纲刀，默認(rèn)值都為 1。MySQL 修改數(shù)據(jù)表自增值是從 auto_increment_offset 開(kāi)始担平，以 auto_increment_increment 為步長(zhǎng)示绊，持續(xù)疊加，直到找到第一個(gè)大于 X 的值暂论，作為新的自增值面褐。
在雙 M 的主備結(jié)構(gòu)里要求雙寫(xiě)的時(shí)候，我們就可能會(huì)設(shè)置成 auto_increment_increment=2取胎，讓一個(gè)庫(kù)的自增 id 都是奇數(shù)展哭，另一個(gè)庫(kù)的自增 id 都是偶數(shù)，避免兩個(gè)庫(kù)生成的主鍵發(fā)生沖突闻蛀。
三匪傍、自增值的修改時(shí)機(jī)
數(shù)據(jù)表的自增值修改實(shí)際上是在真正插入數(shù)據(jù)的操作之前。如果沒(méi)有插入成功觉痛，MySQL 也不會(huì)將自增值再改回去役衡。也就是說(shuō)，自增主鍵有可能會(huì)出現(xiàn)不連續(xù)的情況薪棒。比如在插入數(shù)據(jù)時(shí)出錯(cuò)（如違反唯一鍵約束）手蝎，或者事務(wù)回滾的情況下，都會(huì)導(dǎo)致自增主鍵不連續(xù)俐芯。
可通過(guò)以下步驟驗(yàn)證：
-
創(chuàng)建表 t_auto柑船，包含主鍵、手機(jī)號(hào)和姓名三個(gè)字段泼各，其中主鍵自增鞍时，手機(jī)號(hào)限制唯一。表結(jié)構(gòu)如下：
CREATE TABLE `t_auto` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mobile` varchar(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `m` (`mobile`) ) ENGINE=InnoDB; -
插入一條記錄：
insert into t_auto values(null, '18500009999', '小明'); -
執(zhí)行
show create table命令：show create table t_auto \G;輸出結(jié)果如下，可以看到 AUTO_INCREMENT=2逆巍，表示下一次插入數(shù)據(jù)時(shí)及塘，如果需要自動(dòng)生成自增值，會(huì)生成 id=2锐极。
*************************** 1. row *************************** Table: t_auto Create Table: CREATE TABLE `t_auto` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mobile` varchar(11) COLLATE utf8_bin DEFAULT NULL, `name` varchar(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `m` (`mobile`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.01 sec) ERROR: No query specified查詢(xún)表數(shù)據(jù)：
select * from t_auto;結(jié)果如下：
id mobile name 1 18500009999 小明 -
再插入一條數(shù)據(jù)笙僚，手機(jī)號(hào)和上一條數(shù)據(jù)一樣：
insert into t_auto values(null, '18500009999', '小紅');因?yàn)榕龅轿ㄒ绘I m 沖突（手機(jī)號(hào)要求唯一），所以 id=2 這一行并沒(méi)有插入成功灵再，錯(cuò)誤信息如下：
ERROR 1062 (23000): Duplicate entry '18500009999' for key 'm'這個(gè)語(yǔ)句的執(zhí)行流程就是：
- 執(zhí)行器調(diào)用 InnoDB 引擎接口寫(xiě)入一行肋层，傳入的這一行的值是 (0, '18500009999', '小紅');
- InnoDB 發(fā)現(xiàn)用戶沒(méi)有指定自增 id 的值，獲取表 t 當(dāng)前的自增值 2翎迁；
- 將傳入的行的值改成 (2, '18500009999', '小紅');
- 將表的自增值改成 3栋猖；
- 繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在 mobile=18500009999 的記錄汪榔，所以報(bào) Duplicate key error蒲拉，語(yǔ)句返回。
-
執(zhí)行
show create table命令：show create table t_auto \G;輸出結(jié)果如下痴腌，可以看到 AUTO_INCREMENT=3雌团，MySQL 沒(méi)有將自增值再改回去：
*************************** 1. row *************************** Table: t_auto Create Table: CREATE TABLE `t_auto` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mobile` varchar(11) COLLATE utf8_bin DEFAULT NULL, `name` varchar(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `m` (`mobile`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.01 sec) ERROR: No query specified -
修改手機(jī)號(hào)，再重新插入：
insert into t_auto values(null, '18500009998', '小紅');此時(shí)查詢(xún)表數(shù)據(jù)士聪，
select * from t_auto;結(jié)果如下：
id mobile name 1 18500009999 小明 3 18500009998 小紅 也就是說(shuō)锦援，出現(xiàn)了自增主鍵不連續(xù)的情況。
同樣地剥悟，事務(wù)回滾也會(huì)出現(xiàn)自增主鍵不連續(xù)的情況雨涛。比如，
-
先執(zhí)行以下事務(wù)：
begin; insert into t_auto values(null, '18500009997', '小江'); rollback; -
再執(zhí)行
how create table命令：show create table t_auto \G;輸出結(jié)果如下懦胞，可以看到 AUTO_INCREMENT=5替久，MySQL 沒(méi)有將自增值改回去：
*************************** 1. row *************************** Table: t_auto Create Table: CREATE TABLE `t_auto` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mobile` varchar(11) COLLATE utf8_bin DEFAULT NULL, `name` varchar(20) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `m` (`mobile`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.01 sec) ERROR: No query specified -
再插入數(shù)據(jù)：
insert into t_auto values(null, '18500009996', '小青');查詢(xún)數(shù)據(jù)表，
select * from t_auto;結(jié)果如下：
id mobile name 1 18500009999 小明 3 18500009998 小紅 5 18500009996 小青
在數(shù)據(jù)插入失敗時(shí)躏尉，MySQL 不將自增值改回去是為了提升性能蚯根。在申請(qǐng)自增值時(shí)，為避免多個(gè)并行執(zhí)行的事務(wù)申請(qǐng)到同一個(gè)自增 id胀糜，在申請(qǐng)的過(guò)程中會(huì)加鎖颅拦，然后按順序執(zhí)行。自增 id 鎖并不是一個(gè)事務(wù)鎖教藻，而是每次申請(qǐng)完就馬上釋放距帅，以便允許別的事務(wù)再申請(qǐng)。
繼續(xù)以上述的表 t_auto 為例括堤，此時(shí)表當(dāng)前自增值為 6：
- 假設(shè)現(xiàn)在有兩個(gè)并行執(zhí)行的事務(wù) A 和 B碌秸，其中事務(wù) A 申請(qǐng)到了 id=6绍移，事務(wù) B 申請(qǐng)到了 id=7，這時(shí)數(shù)據(jù)表的自增值是 8（即 AUTO_INCREMENT=8）讥电，繼續(xù)執(zhí)行蹂窖；
- 事務(wù) B 正確提交，但事務(wù) A 提交失敗恩敌，比如出現(xiàn)唯一鍵沖突瞬测；
- 如果允許自增 id 回退的話，表 t_auto 會(huì)把當(dāng)前的自增值改為 6纠炮，此時(shí)會(huì)出現(xiàn)表的自增值為 6月趟，但表中卻存在 id=7 的數(shù)據(jù)；
- 接下來(lái)其他事務(wù)申請(qǐng)自增值就會(huì)申請(qǐng)到 id=6恢口，然后再申請(qǐng)到 id=7孝宗，這時(shí)就會(huì)出現(xiàn)插入語(yǔ)句報(bào)錯(cuò)“主鍵沖突”。
解決這個(gè)主鍵沖突弧蝇，有兩種方法：一種是每次申請(qǐng) id 時(shí)，判斷這個(gè) id 是否已經(jīng)存在于表中折砸，若存在看疗，則跳過(guò)這個(gè) id；另一種是把自增 id 的鎖范圍擴(kuò)大睦授，必須等到事務(wù)完成提交两芳，再釋放鎖，下一個(gè)事務(wù)才能再申請(qǐng)自增 id去枷。這兩種方法都存在性能問(wèn)題：一個(gè)是每次申請(qǐng) id 都要去主鍵索引樹(shù)判斷 id 是否存在怖辆，另一個(gè)是鎖的粒度太大，導(dǎo)致系統(tǒng)并發(fā)能力大大下降删顶。
因此竖螃，為保證 MySQL 的性能，InnoDB 放棄了這個(gè)設(shè)計(jì)逗余，語(yǔ)句執(zhí)行失敗也不回退自增 id特咆。也正是因?yàn)檫@樣，所以才只保證了自增 id 是遞增的录粱，但不保證是連續(xù)的腻格。
四、自增鎖的優(yōu)化
自增 id 鎖不是事務(wù)鎖啥繁，在每次申請(qǐng)完就立馬釋放菜职，以便允許其他事務(wù)可以申請(qǐng)。
其實(shí)旗闽，在 MySQL 5.0 版本的時(shí)候酬核，自增鎖的范圍是語(yǔ)句級(jí)別蜜另，即一個(gè)語(yǔ)句申請(qǐng)了表自增鎖，這個(gè)鎖要等到該語(yǔ)句執(zhí)行完以后才釋放愁茁，在此之前其他需要申請(qǐng)表自增鎖的語(yǔ)句會(huì)被阻塞蚕钦。此舉雖然保證了數(shù)據(jù)一致性，但是降低了并發(fā)度鹅很。
在 MySQL 5.1.22 版本引入了一個(gè)新策略嘶居，新增參數(shù) innodb_autoinc_lock_mode，用戶可根據(jù)實(shí)際情況定制鎖策略促煮，該參數(shù)有如下值 （默認(rèn)值是 1）：
值為 0（Traditional邮屁，傳統(tǒng)）時(shí)，采用傳統(tǒng)鎖模式菠齿，即采用之前 MySQL 5.0 版本的策略佑吝，所有 insert 操作都要申請(qǐng)自增鎖，語(yǔ)句執(zhí)行結(jié)束后才釋放鎖绳匀；
-
值為 1（Consecutive芋忿，連續(xù)）時(shí)：
- 普通 insert 語(yǔ)句，自增鎖在申請(qǐng)之后就馬上釋放疾棵；
- 批量 insert 語(yǔ)句戈钢，類(lèi)似 insert … select、replace...select 這樣的批量插入數(shù)據(jù)的語(yǔ)句是尔，自增鎖還是要等語(yǔ)句結(jié)束后才被釋放殉了；
值為 2（Interleaved，交錯(cuò)）時(shí)拟枚，所有的申請(qǐng)自增主鍵的動(dòng)作都是申請(qǐng)后就釋放鎖薪铜。
在普通的 insert 語(yǔ)句里面包含多個(gè) value 值的情況下，如以下的 insert 語(yǔ)句：
INSERT INTO `t_auto` (`mobile`, `name`)
VALUES
('13987890987', '王朝'),
('13987890988', '馬漢'),
('13987890989', '展昭');
因?yàn)檫@類(lèi)語(yǔ)句在申請(qǐng)自增 id 的時(shí)候恩溅，是可以精確計(jì)算出需要多少個(gè) id 的隔箍，然后一次性申請(qǐng)，申請(qǐng)完成后鎖就可以釋放了脚乡。
而對(duì)于批量插入數(shù)據(jù)的語(yǔ)句（select … insert鞍恢，replace … select 和 load data 語(yǔ)句），MySQL 有一個(gè)批量申請(qǐng)自增 id 的策略（注：該策略是導(dǎo)致自增 id 不連續(xù)的第三種原因每窖。）：
- 語(yǔ)句執(zhí)行過(guò)程中帮掉，第一次申請(qǐng)自增 id，會(huì)分配 1 個(gè)窒典；
- 1 個(gè)用完以后蟆炊，這個(gè)語(yǔ)句第二次申請(qǐng)自增 id，會(huì)分配 2 個(gè)瀑志；
- 2 個(gè)用完以后涩搓，還是這個(gè)語(yǔ)句污秆，第三次申請(qǐng)自增 id，會(huì)分配 4 個(gè)昧甘；
- 依此類(lèi)推良拼，同一個(gè)語(yǔ)句去申請(qǐng)自增 id，每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍充边。
我們可以通過(guò)以下這個(gè)語(yǔ)句序列驗(yàn)證：
-- 創(chuàng)建表step_log
CREATE TABLE `step_log` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`step` int(11) NOT NULL DEFAULT 1 COMMENT '步長(zhǎng)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入數(shù)據(jù)
insert into step_log values(null, 1);
insert into step_log values(null, 2);
insert into step_log values(null, 3);
insert into step_log values(null, 4);
-- 創(chuàng)建表step_log_2庸推，其結(jié)構(gòu)與表step_log相同
create table step_log_2 like step_log;
-- 批量插入數(shù)據(jù)
insert into step_log_2(step) select step from step_log;
-- 插入數(shù)據(jù)
insert into step_log_2 values(null, 5);
-- 查詢(xún)此時(shí)表step_log_2數(shù)據(jù)：
select * from step_log_2;
-- 輸出如下，id=5浇冰、id=6贬媒、id=7 就被跳過(guò)了：
+----+------+
| id | step |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 8 | 5 |
+----+------+
引申：為什么默認(rèn)設(shè)置下，insert … select 要使用語(yǔ)句級(jí)的鎖肘习？為什么這個(gè)參數(shù)的默認(rèn)值不是 2际乘？
這么設(shè)計(jì)還是為了數(shù)據(jù)的一致性。
新建數(shù)據(jù)表 step_log漂佩，一起來(lái)看一下這個(gè)場(chǎng)景：往表 step_log 中插入了 4 行數(shù)據(jù)脖含，然后創(chuàng)建了一個(gè)相同結(jié)構(gòu)的表 step_log_2，然后兩個(gè) session 同時(shí)執(zhí)行向表 step_log_2 中插入數(shù)據(jù)的操作投蝉。
CREATE TABLE `step_log` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `step` int(11) NOT NULL DEFAULT 1 COMMENT '步長(zhǎng)', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
session A session B insert into step_log values(null, 1);
insert into step_log values(null, 2);
insert into step_log values(null, 3);
insert into step_log values(null, 4);create table step_log_2 like step_log; insert into step_log values(null, 5); insert into step_log_2(step) select step from step_log; 設(shè)想一下养葵，如果 session B 是申請(qǐng)了自增值以后馬上就釋放自增鎖，那么就可能出現(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)臣樱。
你可能會(huì)說(shuō)，這也沒(méi)關(guān)系吧腮考，畢竟 session B 的語(yǔ)義本身就沒(méi)有要求表 t2 的所有行的數(shù)據(jù)都跟 session A 相同雇毫。
是的，從數(shù)據(jù)邏輯上看是對(duì)的踩蔚。但是棚放，如果我們現(xiàn)在的 binlog_format=statement，你可以設(shè)想下馅闽，binlog 會(huì)怎么記錄呢飘蚯？
由于兩個(gè) session 是同時(shí)執(zhí)行插入數(shù)據(jù)命令的馍迄，所以 binlog 里面對(duì)表 t2 的更新日志只有兩種情況：要么先記 session A 的，要么先記 session B 的局骤。
但不論是哪一種攀圈，這個(gè) binlog 拿去從庫(kù)執(zhí)行，或者用來(lái)恢復(fù)臨時(shí)實(shí)例峦甩，備庫(kù)和臨時(shí)實(shí)例里面赘来，session B 這個(gè)語(yǔ)句執(zhí)行出來(lái)，生成的結(jié)果里面穴店，id 都是連續(xù)的撕捍。這時(shí)，這個(gè)庫(kù)就發(fā)生了數(shù)據(jù)不一致泣洞。
你可以分析一下忧风，出現(xiàn)這個(gè)問(wèn)題的原因是什么？
其實(shí)球凰，這是因?yàn)樵瓗?kù) session B 的 insert 語(yǔ)句狮腿，生成的 id 不連續(xù)。這個(gè)不連續(xù)的 id呕诉，用 statement 格式的 binlog 來(lái)串行執(zhí)行缘厢，是執(zhí)行不出來(lái)的。
而要解決這個(gè)問(wèn)題甩挫，有兩種思路：
- 一種思路是贴硫，讓原庫(kù)的批量插入數(shù)據(jù)語(yǔ)句，固定生成連續(xù)的 id 值伊者。所以英遭，自增鎖直到語(yǔ)句執(zhí)行結(jié)束才釋放，就是為了達(dá)到這個(gè)目的亦渗。
- 另一種思路是挖诸，在 binlog 里面把插入數(shù)據(jù)的操作都如實(shí)記錄進(jìn)來(lái)，到備庫(kù)執(zhí)行的時(shí)候法精，不再依賴(lài)于自增主鍵去生成多律。這種情況，其實(shí)就是 innodb_autoinc_lock_mode 設(shè)置為 2搂蜓，同時(shí) binlog_format 設(shè)置為 row狼荞。
因此，在生產(chǎn)上帮碰，尤其是有 insert … select 這種批量插入數(shù)據(jù)的場(chǎng)景時(shí)相味，從并發(fā)插入數(shù)據(jù)性能的角度考慮，我建議你這樣設(shè)置：innodb_autoinc_lock_mode=2 收毫，并且 binlog_format=row. 這樣做攻走，既能提升并發(fā)性殷勘，又不會(huì)出現(xiàn)數(shù)據(jù)一致性問(wèn)題。
需要注意的是昔搂，我這里說(shuō)的批量插入數(shù)據(jù)玲销，包含的語(yǔ)句類(lèi)型是 insert … select、replace … select 和 load data 語(yǔ)句摘符。
五贤斜、自增值達(dá)到上限的邏輯
表定義的自增值達(dá)到上限后的邏輯是：再申請(qǐng)下一個(gè) id 時(shí)，得到的值保持不變逛裤。
可通過(guò)以下步驟驗(yàn)證：
-
創(chuàng)建表 t瘩绒，其中只包括一個(gè)主增主鍵，并設(shè)置 auto_increment=4294967295
create table t(id int unsigned auto_increment primary key) auto_increment=4294967295; -
插入一行數(shù)據(jù)：
insert into t values(null); -
查看此時(shí)表 t 的創(chuàng)建語(yǔ)句:
show create table t;結(jié)果如下：
CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -
再插入一行數(shù)據(jù)：
insert into t values(null);則提示錯(cuò)誤信息：
ERROR 1062 (23000): Duplicate entry '4294967295' for key 'PRIMARY'
可以看到带族，第一次插入數(shù)據(jù)成功后锁荔，這個(gè)表的 AUTO_INCREMENT 沒(méi)有改變（還是 4294967295），導(dǎo)致了第二次插入的時(shí)候又拿到相同的自增 id 值蝙砌，再試圖執(zhí)行時(shí)阳堕，報(bào)主鍵沖突錯(cuò)誤。
（END）
