為什么要建議用自增列做主鍵

第一部分 InnoDB引擎表的特點

1、InnoDB引擎表是基于B+樹的索引組織表(IOT)

關于B+樹

B+ 樹的特點:

（1）所有關鍵字都出現在葉子結點的鏈表中(稠密索引)，且鏈表中的關鍵字恰好是有序的;

（2）不可能在非葉子結點命中;

（3）非葉子結點相當于是葉子結點的索引(稀疏索引)，葉子結點相當于是存儲(關鍵字)數據的數據層;

2、如果我們定義了主鍵(PRIMARY KEY)，那么InnoDB會選擇主鍵作為聚集索引、如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引、如果也沒有這樣的唯一索引，則InnoDB會選擇內置6字節長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

3、數據記錄本身被存于主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節點內（大小為一個內存頁或磁盤頁）的各條數據記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB默認為15/16），則開辟一個新的頁（節點）

4、如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節點的后續位置，當一頁寫滿，就會自動開辟一個新的頁

5、如果使用非自增主鍵（如果×××號或學號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數據，甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過OPTIMIZE TABLE來重建表并優化填充頁面。

綜上總結，如果InnoDB表的數據寫入順序能和B+樹索引的葉子節點順序一致的話，這時候存取效率是最高的，也就是下面這幾種情況的存取效率最高：

1、使用自增列(INT/BIGINT類型)做主鍵，這時候寫入順序是自增的，和B+數葉子節點分裂順序一致；

2、該表不指定自增列做主鍵，同時也沒有可以被選為主鍵的唯一索引(上面的條件)，這時候InnoDB會選擇內置的ROWID作為主鍵，寫入順序和ROWID增長順序一致；
除此以外，如果一個InnoDB表又沒有顯示主鍵，又有可以被選擇為主鍵的唯一索引，但該唯一索引可能不是遞增關系時(例如字符串、UUID、多字段聯合唯一索引的情況)，該表的存取效率就會比較差。

《高性能MySQL》中的原話

第二部分 關于自增鎖的分析

自增鎖,在提交前釋放,并發插入高，共享鎖和排它鎖在COMMIT提交后釋放。對于自增列的值不能回滾。

insert -like:

    simple-insert:插入前就能確定插入行數語句

    bulk insert :插入前不確定插入行數的語句 replace ... select

    mixed-mode inserts：insert into t1(c1,c2) values(1,"a"),(null,"b"),(4,"c"),(null,"d")
   insert ...  on duplicate key update:自身擴展  (任何KEY 重復,就執行 )

參數innodb_autoinc_lock_mode有三種模式:

0:傳統方式 ,

simple insert:傳統方式

bulk insert :傳統方式

對于 INSERT ... SELECT ... 些時其他事務不能插,分配的ID是連續得 ,其他事務不能插入

SQL執行完才釋放自增鎖

1.（默認配置）

simple insert 并發

bulk insert 傳統方式

2.最寬松方式
所有自增都以并發方式
同一SQL語句自增可能不連接
row-based binlog

工作模式1:

工作原理:

BULK INSERT：

ACQUIRE AI

INSERT ..SELECT ：如果執行時間長,自增鎖持有時間就長,不確定插入的記錄數,只能等插入完 才自增,其他事務等待插入

AI=AI+N

RELEASE AI

SIMPLE INSERT : 無SQL 語句執行等待

ACQURE AI

AI=AI+N

RELESE AI

工作模式為 2時的工作原理:

FOR I=AI;I++； //對BULK INSERT 也能并發插入,對單線插入變差,無益,對多線程插入是益的,自增值可能不連續的

{

ACQUIRE AI LOCK

INSERT ONE REC

AI=Ai+1

RELEAS AI LOCK

}

這樣做的好處是，對于批量的、耗時的插入，SQL不會長時間的持有AI自增鎖，而是插入 一條 （有且僅插入一條，而simple inserts是確定好的M條）語句后就 釋放 ，這樣可以給別的事物使用，實現并發。

但是這種方式 并發度是增加了 ，但是性能不一定變好，尤其是單線程導入數據時，要 不斷的申請和釋放鎖

對于批量插入來說，自增就可能變的不連續了（需要和開發溝通，是否可以接受）

innodb_autoinc_lock_mode 是 read-only 的， 需要 修改后 重啟 MySQL實例。

自增列的創建:

對于聯合索引,自增列必須放在第一個列

create table jjj ( a int auto_increment,b int ,key( a,b)); // KEY（b,a)

自增鎖：

AUTO_INCREMENT PK 不能持久化，速度快

當重起MYSQL 服務器重新計算值：

SELECT MAX（AUTO_INC_COL) FROM XX 基于索引查找,而不是全表掃

自增鎖相關參數:

　　auto_increment_increment:步長值

　　auto_increment_offset:初始值