本篇文章帶大家了解一下mysql中的自增主鍵，介紹一下自增值修改機制、自增值的修改時機、自增鎖的優(yōu)化方法等等，有需要的朋友可以學習了解一下~
一、自增值保存在哪兒？
不同的引擎對于自增值的保存策略不同
1.MyISAM引擎的自增值保存在數(shù)據(jù)文件中
2.InnoDB引擎的自增值，在mysql5.7及之前的版本，自增值保存在內(nèi)存里，并沒有持久化。每次重啟后，第一次打開表的時候，都會去找自增值的最大值max(id)，然后將max(id)+步長作為這個表當前的自增值
select max(ai_col) from table_name for update;
在MySQL8.0版本，將自增值的變更記錄在了redo log中，重啟的時候依靠redo log恢復重啟之前的值
二、自增值修改機制
如果字段id被定義為AUTO_INCREMENT，在插入一行數(shù)據(jù)的時候，自增值的行為如下：
1.如果插入數(shù)據(jù)時id字段指定為0、NULL或未指定值，那么就把這個表當前的AUTO_INCREMENT值填到自增字段
2.如果插入數(shù)據(jù)時id字段指定了具體的值，就直接使用語句里指定的值
假設(shè)，某次要插入的值是X，當前的自增值是Y
1.如果X
2.如果X>=Y，就需要把當前自增值修改為新的自增值
新的自增值生成算法是：從auto_increment_offset（初始值）開始，以auto_increment_increment（步長）為步長，持續(xù)疊加，直到找到第一個大于X的值，作為新的自增值
三、自增值的修改時機
創(chuàng)建一個表t，其中id是自增主鍵字段、c是唯一索引，建表語句如下：
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;
假設(shè)，表t里面已經(jīng)有了(1,1,1)這條記錄，這時再執(zhí)行一條插入數(shù)據(jù)命令：
insert?into?t?values(null,?1,?1);
執(zhí)行流程如下：
1.執(zhí)行器調(diào)用InnoDB引擎接口寫入一行，傳入的這一行的值是(0,1,1)
2.InnoDB發(fā)現(xiàn)用于沒有指定自增id的值，獲取表t當前的自增值2
3.將傳入的行的值改成(2,1,1)
4.將表的自增值改成3
5.繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在c=1的記錄，所以報Duplicate key Error（唯一鍵沖突），語句返回
對應的執(zhí)行流程圖如下：
在這之后，再插入新的數(shù)據(jù)行時，拿到的自增id就是3。出現(xiàn)了自增主鍵不連續(xù)的情況
唯一鍵沖突和事務(wù)回滾都會導致自增主鍵id不連續(xù)的情況
四、自增鎖的優(yōu)化
自增id鎖并不是一個事務(wù)鎖，而是每次申請完就馬上釋放，以便允許別的事務(wù)再申請
但在MySQL5.0版本的時候，自增鎖的范圍是語句級別。也就是說，如果一個語句申請了一個表自增鎖，這個鎖會等語句執(zhí)行結(jié)束以后才釋放
MySQL5.1.22版本引入了一個新策略，新增參數(shù)innodb_autoinc_lock_mode，默認值是1
1.這個參數(shù)設(shè)置為0，表示采用之前MySQL5.0版本的策略，即語句執(zhí)行結(jié)束后才釋放鎖
2.這個參數(shù)設(shè)置為1
- 普通insert語句，自增鎖在申請之后就馬上釋放
- 類似insert … select這樣的批量插入數(shù)據(jù)的語句，自增鎖還是要等語句結(jié)束后才被釋放
3.這個參數(shù)設(shè)置為2，所有的申請自增主鍵的動作都是申請后就釋放鎖
為了數(shù)據(jù)的一致性，默認設(shè)置為1
如果SessionB申請了自增值以后馬上就釋放自增鎖，那么就可能出現(xiàn)這樣的情況：
- sessionB先插入了兩行數(shù)據(jù)(1,1,1)、(2,2,2)
- sessionA來申請自增id得到id=3，插入了(3,5,5)
- 之后，sessionB繼續(xù)執(zhí)行，插入兩條記錄(4,3,3)、(5,4,4)
當binlog_format=statement的時候，兩個session是同時執(zhí)行插入數(shù)據(jù)命令的，所以binlog里面對表t2的更新日志只有兩種情況：要么先記sessionA的，要么先記錄sessionB的。無論是哪一種，這個binlog拿到從庫執(zhí)行，或者用來恢復臨時實例，備庫和臨時實例里面，sessionB這個語句執(zhí)行出來，生成的結(jié)果里面，id都是連續(xù)的。這時，這個庫就發(fā)生了數(shù)據(jù)不一致
解決這個問題的思路：
1）讓原庫的批量插入數(shù)據(jù)語句，固定生成連續(xù)的id值。所以，自增鎖直到語句執(zhí)行結(jié)束才釋放，就是為了達到這個目的
2）在binlog里面把插入數(shù)據(jù)的操作都如實記錄進來，到備庫執(zhí)行的時候，不再依賴于自增主鍵去生成。也就是把innodb_autoinc_lock_mode設(shè)置為2，同時binlog_format設(shè)置為row
如果有批量插入數(shù)據(jù)（insert … select、replace … select和load data）的場景時，從并發(fā)插入數(shù)據(jù)性能的角度考慮，建議把innodb_autoinc_lock_mode設(shè)置為2，同時binlog_format設(shè)置為row，這樣做既能并發(fā)性，又不會出現(xiàn)數(shù)據(jù)一致性的問題
對于批量插入數(shù)據(jù)的語句，MySQL有一個批量申請自增id的策略：
1.語句執(zhí)行過程中，第一次申請自增id，會分配1個
2.1個用完以后，這個語句第二次申請自增id，會分配2個
3.2個用完以后，還是這個語句，第三次申請自增id，會分配4個
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)
這是主鍵id出現(xiàn)自增id不連續(xù)的第三種原因
五、自增主鍵用完了
自增主鍵字段在達到定義類型上限后，再插入一行記錄，則會報主鍵沖突的錯誤
以無符號整型（4個字節(jié)，上限就是 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 2 ? ? ? ? ? ? ? ? ? ? ? ? 32 ? ? ? ? ? ? ? ? ? ? ? ? ? ?? ? ? ? ? ? ? ? ? ? ?1 ? ? ? ? ? ? ? ? ? ? ? ? 2^{32}-1 ? ? ? ? ? ? ?232?1）為例，通過下面這個語句序列驗證一下：
CREATE?TABLE?t?(?id?INT?UNSIGNED?auto_increment?PRIMARY?KEY?)?auto_increment?=?4294967295; INSERT?INTO?t?VALUES(NULL); INSERT?INTO?t?VALUES(NULL);
第一個insert語句插入數(shù)據(jù)成功后，這個表的AUTO_INCREMENT沒有改變（還是4294967295），就導致了第二個insert語句又拿到相同的自增id值，再試圖執(zhí)行插入語句，報主鍵沖突錯誤
相關(guān)學習推薦：mysql教程(視頻)
