在mysql的InooDB存儲(chǔ)引擎中，常用的是自增（AUTO_INCRMENT）主鍵，因?yàn)镮nnoDB是聚簇索引。
使用自增主鍵可以保證數(shù)據(jù)行是按順序?qū)懭氲摹Ｈ羰鞘褂脽o(wú)序的聚簇索引疟呐，例如UUID，不僅索引占用的空間更大东且，而且也會(huì)導(dǎo)致頁(yè)分裂和碎片化現(xiàn)象启具。
使用自增索引，在高并發(fā)的情況下可能存在性能問(wèn)題珊泳，一個(gè)就是間隙鎖影響性能鲁冯，而另一個(gè)就是AUTO_INCREMENT鎖機(jī)制會(huì)影響性能拷沸。
1. InnoDB中AUTO_INCREMENT處理
若是在InnoDB中使用AUTO_INCREMENT機(jī)制，那么自增列必須是索引的一部分薯演，以便可以等效于對(duì)表進(jìn)行索引查找以獲取最大列值撞芍。
innodb_autoinc_lock_mode配置可以控制在向auto_increment列表插入數(shù)據(jù)時(shí)相關(guān)鎖的行為以及主從數(shù)據(jù)一致性的平衡。
1.1 插入（INSERT LIKE）的分類
1.1.1. Simple inserts（簡(jiǎn)單插入）
是可以預(yù)先確定將要插入行數(shù)的insert語(yǔ)句跨扮，包括單行和多行INSERT語(yǔ)句序无，例如INSERT, INSERT … VALUES(),VALUES()語(yǔ)法。
1.1.2. Bulk inserts（大量插入）
事先不知道要插入的行數(shù)的語(yǔ)句衡创，包括INSERT ... SELECT帝嗡，REPLACE ... SELECT和LOAD DATA語(yǔ)句，但不是純INSERT璃氢，每處理一行時(shí)哟玷，一次為一列分配新增的AUTO_INCRMENT。
1.1.3. Mixed inserts（混合模式插入）
- 一些特殊的“簡(jiǎn)單插入”語(yǔ)句一也，用于指定部分（但不是全部）新行的自動(dòng)增量值巢寡，例如
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');； - 例如
INSERT … ON DUPLICATE KEY UPDATE椰苟，在最壞的情況下實(shí)際上是INSERT緊跟著UPDATE抑月，其中AUTO_INCREMENT在更新階段可能會(huì)或者不會(huì)使用為列分配的值。
1.2 innodb_autoinc_lock_mode配置
1.2.1. 版本更新：
- 在mysql5.1.22版本之前尊剔，是不支持innodb_autoinc_lock_mode配置的。
- 在mysql8.0之前菱皆，默認(rèn)innodb_autoinc_lock_mode=1须误，即連續(xù)鎖定模式。
- 在mysql8.0開(kāi)始仇轻，默認(rèn)innodb_autoinc_lock_mode=2京痢，即交錯(cuò)鎖定模式。
因?yàn)镸ysql8.0之前篷店，備份（binlog）默認(rèn)是基于語(yǔ)句（statement模式）的復(fù)制祭椰。而Mysql8.0開(kāi)始，binlog默認(rèn)是基于行（row模式）的復(fù)制疲陕。
基于語(yǔ)句的復(fù)制需要連續(xù)的自動(dòng)增量鎖定模式方淤，以確保為給定的SQL語(yǔ)句序列以可預(yù)測(cè)和可重復(fù)的順序分配自動(dòng)增長(zhǎng)值。而基于行的復(fù)制對(duì)SQL語(yǔ)句的執(zhí)行順序不敏感蹄殃。
1.2.2. 配置詳情
innodb_autoinc_lock_mode = 0 （“ 傳統(tǒng) ”鎖定模式）
該模式是Mysql5.1之前未引入innodb_autoinc_lock_mode配置參數(shù)時(shí)的行為携茂，提供了傳統(tǒng)的鎖定模式選項(xiàng)以實(shí)現(xiàn)向后兼容。
在這種模式下诅岩，所有INSERT語(yǔ)句（INSERT LIKE）都會(huì)獲得特殊的表級(jí)AUTO-INC鎖定讳苦，將其插入具有AUTO_INCREMENT列的表中带膜。此鎖定通常保持在語(yǔ)句的末尾（而不是事務(wù)的末尾）。
舉例分析：假設(shè)有兩個(gè)事務(wù)正在運(yùn)行鸳谜，每個(gè)事務(wù)都將行插入具有AUTO_INCREMENT列的表中膝藕。一個(gè)事務(wù)使用insert ... select插入1000行的語(yǔ)句，另一事務(wù)使用insert插入一個(gè)行的簡(jiǎn)單語(yǔ)句：
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
InooDB無(wú)法預(yù)先判斷Tx1會(huì)插入多少行咐扭，Tx1使用表級(jí)鎖（保持在該語(yǔ)句的末尾）去阻塞Tx2的并發(fā)執(zhí)行芭挽，Tx1每次插入時(shí)均分配一個(gè)自增id，最終t1語(yǔ)句生成的遞增主鍵是連續(xù)的草描。在主從復(fù)制或者故障恢復(fù)時(shí)览绿，若binlog使用statement模式（基于語(yǔ)句的復(fù)制），主從服務(wù)器執(zhí)行insert語(yǔ)句得到的結(jié)果是完全相同的穗慕。
innodb_autoinc_lock_mode = 1 （“ 連續(xù) ”鎖定模式）
是Mysql5.1.2-Mysql8.0中innodb_autoinc_lock_mode的默認(rèn)配置饿敲。
- simple inserts：直接通過(guò)分析語(yǔ)句，獲取要插入的的數(shù)量逛绵，然后一次性分配足夠的
auto_increment id怀各，只會(huì)將整個(gè)分配過(guò)程鎖住。 - Bulk inserts：
[[b?lk]]因?yàn)椴荒艽_定插入的數(shù)量术浪，因此還會(huì)將表鎖灼岸浴； - Mixed-mode inserts：直接分析語(yǔ)句胰苏，獲取最壞情況下需要插入的數(shù)量硕蛹，然后一次性分配足夠的
auto increment id，只會(huì)將整個(gè)分配過(guò)程鎖住硕并。
innodb_autoinc_lock_mode = 2 (“交叉”鎖定模式)
這種模式下插入語(yǔ)句（INSERT LIKE）來(lái)一個(gè)分配一個(gè)法焰，而不會(huì)鎖表，只會(huì)鎖住分配ID的過(guò)程倔毙，和innodb_autoinc_lock_mode = 1區(qū)別bulk insert是不會(huì)預(yù)分配多個(gè)埃仪，這種方式的并發(fā)性最高。
若是binlog的模式為statement模式陕赃，由于并發(fā)情況下Bulk inserts在分配的時(shí)會(huì)同時(shí)向其他insert分配卵蛉，會(huì)出現(xiàn)主從不一致的情況。
所以需要配合binlog的row模式使用么库。
2. InnoDB的自增主鍵特性
- 主從復(fù)制下鎖定策略
如果binlog為statement模式（基于語(yǔ)句的復(fù)制）傻丝，請(qǐng)將innodb_autoinc_lock_mode設(shè)置為0或1，并在主設(shè)備及從設(shè)備上使用相同的值诉儒。
如果binlog是row模式或者mixed模式桑滩，則innodb_autoinc_lock_mode均是安全的。因?yàn)榛谛械膹?fù)制對(duì)于SQL語(yǔ)句的執(zhí)行順序不敏感。
- “丟失”自增值和序列間隙
在所有鎖定模式（0,1和2）中运准，如果生成自增值的事務(wù)回滾幌氮，則這些自增值將“丟失”。為自動(dòng)遞增列生成值后胁澳，無(wú)論“INSERT like”語(yǔ)句是否完成该互，以及包含的事務(wù)是否回滾，都無(wú)法回滾該值韭畸。這樣丟失的值不會(huì)被重用宇智。因此，自增列中的值有間隙胰丁。
- 自增列上的NULL和0
在所有鎖模式（0,1和2）中随橘，如果用戶未INSERT中的AUTO_INCREMENT列指定NULL或0，InnoDB會(huì)將該行視為未指定值并為其生成新值锦庸。
- 為自動(dòng)遞增列指定負(fù)值
在所有鎖定模式（0,1和2）中机蔗，如果為“auto_increment”列指定負(fù)值，則自增特性不會(huì)生效（持久化的為負(fù)值）甘萧。
- “大量插入（bulk inserts）”下自增列與間隙
當(dāng)innodb_autoinc_lockm_mode設(shè)置為0或1時(shí)萝嘁，任何給定語(yǔ)句生成的自增值都是連續(xù)的，沒(méi)有間隙扬卷。因?yàn)闀?huì)使用表鎖一直到語(yǔ)句結(jié)束牙言，并且一次只能執(zhí)行一個(gè)這樣的語(yǔ)句。
在innodb_autoinc_lockm_mode=2的情況下怪得，在“bulk inserts”并發(fā)執(zhí)行時(shí)咱枉，生成的自增列可能存在間隙。
6. 更新自增列的值
第一步：插入數(shù)據(jù)（0和null會(huì)執(zhí)行自增策略）
create table t(x int auto_increment not null primary key);
insert into t(x) values(0),(null),(3);
select * from t;
+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
+---+
第二步：修改sql中的自增列徒恋。
update t set x=4 where x=1;
select * from t;
+---+
| x |
+---+
| 2 |
| 3 |
| 4 |
+---+
第三步：再次插入
insert into t(x) values(0);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
第四步：分析
執(zhí)行完第一步后蚕断，mysql的自增序列生成器知道下一個(gè)自增值為4；
執(zhí)行第二步因谎，mysql并不知道自增列4已經(jīng)被人占用基括，所以執(zhí)行第三步的時(shí)候就出現(xiàn)異常颜懊。
’
