Mysql關於自增主鍵，自增主鍵優化總結

Mysql自增主鍵

自增主鍵如何建立

CREATE TABLE `blog`.`Idv_Inf_Tbl` (
	`Idv_Inf_No` INT(11) NOT NULL AUTO_INCREMENT,
	`Acct_No` VARCHAR(45) NOT NULL,
	`nickname`VARCHAR(45) NOT NULL,
  PRIMARY KEY (`Idv_Inf_No`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

其中在建表時，在欄位後面加上AUTO_INCREMENT，該欄位即為自增欄位。

如何使用自增主鍵

以上面建立的表格為例

Idv_Inf_No	Acct_No	nickname

即Idv_Inf_Tbl為空表，Idv_Inf_No為自增主鍵
現在，我們往裡面新增資料

#第一種方式
insert into Idv_Inf_Tbl(`Idv_Inf_No`,`Acct_No`,`nickname`)
		values(1,"ddd","bbb");
#第二種方式
insert into Idv_Inf_Tbl(`Acct_No`,`nickname`)
		values("ddd","bbb");		

這兩種方式的作用

Idv_Inf_No	Acct_No	nickname
1	ddd	bbb

都會增加一條資料，並且自增主鍵預設從1開始

現在，我們再次新增一條資料

#那現在使用
insert into Idv_Inf_Tbl(`Acct_No`,`nickname`)
		values("aaa","ccc");		

Idv_Inf_No	Acct_No	nickname
1	ddd	bbb
2	aaa	ccc

執行流程

自增主鍵的執行流程：

1.執行器呼叫InnoDB引擎介面寫入一行，傳入的這一行的值是(0,“aaa”,“bbb”)

2.InnoDB發現用於沒有指定自增id的值，獲取表t當前的自增值2

3.將傳入的行的值改成(2,“aaa”,“bbb”)

4.將表的自增值改成3

自增鎖的優化

自增id鎖並不是一個事務鎖，而是每次申請完就馬上釋放，以便允許別的事務再申請

但在MySQL5.0版本的時候，自增鎖的範圍是語句級別。也就是說，如果一個語句申請了一個表自增鎖，這個鎖會等語句執行結束以後才釋放

MySQL5.1.22版本引入了一個新策略，新增引數innodb_autoinc_lock_mode，預設值是1

1.這個引數設定為0，表示採用之前MySQL5.0版本的策略，即語句執行結束後才釋放鎖

2.這個引數設定為1

普通insert語句，自增鎖在申請之後就馬上釋放
類似insert … select這樣的批量插入資料的語句，自增鎖還是要等語句結束後才被釋放

3.這個引數設定為2，所有的申請自增主鍵的動作都是申請後就釋放鎖

為了資料的一致性，預設設定為1

如果sessionB申請了自增值以後馬上就釋放自增鎖，那麼就可能出現這樣的情況：

sessionB先插入了兩行資料(1,1,1)、(2,2,2)
sessionA來申請自增id得到id=3，插入了(3,5,5)
之後，sessionB繼續執行，插入兩條記錄(4,3,3)、(5,4,4)

當binlog_format=statement的時候，兩個session是同時執行插入資料命令的，所以binlog裡面對錶t2的更新日誌只有兩種情況：要麼先記sessionA的，要麼先記錄sessionB的。無論是哪一種，這個binlog拿到從庫執行，或者用來恢復臨時例項，備庫和臨時例項裡面，sessionB這個語句執行出來，生成的結果裡面，id都是連續的。這時，這個庫就發生了資料不一致

解決這個問題的思路：

1）讓原庫的批量插入資料語句，固定生成連續的id值。所以，自增鎖直到語句執行結束才釋放，就是為了達到這個目的

2）在binlog裡面把插入資料的操作都如實記錄進來，到備庫執行的時候，不再依賴於自增主鍵去生成。也就是把innodb_autoinc_lock_mode設定為2，同時binlog_format設定為row

如果有批量插入資料（insert … select、replace … select和load data）的場景時，從併發插入資料效能的角度考慮，建議把innodb_autoinc_lock_mode設定為2，同時binlog_format設定為row，這樣做既能併發性，又不會出現資料一致性的問題

對於批量插入資料的語句，MySQL有一個批量申請自增id的策略：

1.語句執行過程中，第一次申請自增id，會分配1個

2.1個用完以後，這個語句第二次申請自增id，會分配2個

3.2個用完以後，還是這個語句，第三次申請自增id，會分配4個

4.依次類推，同一個語句去申請自增id，每次申請到的自增id個數都是上一次的兩倍

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行資料。但是，這四行資料是分三次申請的自增id，第一次申請到了id=1，第二次被分配了id=2和id=3，第三次被分配到id=4到id=7

由於這條語句實際上只用上了4個id，所以id=5到id=7就被浪費掉了。之後，再執行insert into t2 values(null, 5,5)，實際上插入了的資料就是(8,5,5)

這是主鍵id出現自增id不連續的第三種原因

自增主鍵用完了

自增主鍵欄位在達到定義型別上限後，再插入一行記錄，則會報主鍵衝突的錯誤

以無符號整型（4個位元組，上限就是
）為例，通過下面這個語句序列驗證一下：

CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
INSERT INTO t VALUES(NULL);
INSERT INTO t VALUES(NULL);

第一個insert語句插入資料成功後，這個表的AUTO_INCREMENT沒有改變（還是4294967295），就導致了第二個insert語句又拿到相同的自增id值，再試圖執行插入語句，報主鍵衝突錯誤