MySQL自增鎖模式innodb_autoinc_lock_mode引數詳解

innodb_autoinc_lock_mode這個引數控制著在向有auto_increment 列的表插入資料時，相關鎖的行為；


透過對它的設定可以達到效能與安全(主從的資料一致性)的平衡

【0】我們先對insert做一下分類


　　首先insert大致上可以分成三類：
 　　　　1、simple insert 如insert into t(name) values('test')
　　　　2、bulk insert 如load data | insert into ... select .... from ....
　　　　3、mixed insert 如insert into t(id,name) values(1,'a'),(null,'b'),(5,'c');



【1】innodb_autoinc_lock_mode 的說明


　　innodb_auto_lockmode有三個取值：
 　　　　1、0 這個表示tradition 傳統
 　　　　2、1 這個表示consecutive 連續
 　　　　3、2 這個表示interleaved 交錯


【1.1】tradition(innodb_autoinc_lock_mode=0) 模式:


　　 1、它提供了一個向後相容的能力
 　　2、在這一模式下，所有的insert語句("insert like") 都要在語句開始的時候得到一個
 　　　  表級的auto_inc鎖，在語句結束的時候才釋放這把鎖，注意呀，這裡說的是語句級而不是事務級的，一個事務可能包涵有一個或多個語句。
 　　3、它能保證值分配的可預見性，與連續性，可重複性，這個也就保證了insert語句在複製到slave
            的時候還能生成和master那邊一樣的值(它保證了基於語句複製的安全)。
       4、由於在這種模式下auto_inc鎖一直要保持到語句的結束，所以這個就影響到了併發的插入。


【1.2】consecutive(innodb_autoinc_lock_mode=1) 模式:

　　1、這一模式下去simple insert 做了最佳化，由於simple insert一次性插入值的個數可以立馬得到
          確定，所以mysql可以一次生成幾個連續的值，用於這個insert語句；總的來說這個對複製也是安全的
          (它保證了基於語句複製的安全)
　　2、這一模式也是mysql的預設模式，這個模式的好處是auto_inc鎖不要一直保持到語句的結束，只要
          語句得到了相應的值後就可以提前釋放鎖


【1.3】interleaved(innodb_autoinc_lock_mode=2) 模式
 　　1、由於這個模式下已經沒有了auto_inc鎖，所以這個模式下的效能是最好的；但是它也有一個問題，就是
          對於同一個語句來說它所得到的auto_increment值可能不是連續的。

【2】如果你的二進位制檔案格式是mixed | row 那麼這三個值中的任何一個對於你來說都是複製安全的。
　　 由於現在mysql已經推薦把二進位制的格式設定成row，所以在binlog_format不是statement的情況下最好是innodb_autoinc_lock_mode=2 這樣可能知道更好的效能。

三種模式簡要說明:
0：traditonal （每次都會產生表鎖）
1：consecutive （會產生一個輕量鎖，simple insert會獲得批次的鎖，保證連續插入）
2：interleaved （不會鎖表，來一個處理一個，併發最高）


總結：
1 innodb  row複製時，可將innodb_autoinc_lock_mode設定為2，這時可在所有insert情況下表獲得最大併發度
2 innodb statement複製時，可將innodb_autoinc_lock_mode設定為1，保證複製安全的同時，獲得簡單insert語句的最大併發度
3 myisam引擎情況下，無論什麼樣自增id鎖都是表級鎖，設定innodb_autoinc_lock_mode引數無效（測試略）
4 實際上提問者說到的在innodb引擎下自增id值作為主鍵的情況下，相比uuid或者自定義的主鍵，是可以提到插入速度的，因為innodb是主鍵聚集索引，實際的主鍵值必須按照主鍵順序存取，那麼自增id本身就是升序的，那麼在插入資料時，底層就不必再做額外的排序操作，也減少了索引頁分裂的次數，從而大大增加insert速度（除非其他方案也能保證主鍵完全自增）
  



修改自增鎖級別方法:
編輯/etc/my.cnf，加入如下行:
innodb_autoinc_lock_mode=2


直接透過命令修改會報錯:
mysql(mdba@localhost:(none) 09:32:19)>set global innodb_autoinc_lock_mode=2;
ERROR 1238 (HY000): Variable 'innodb_autoinc_lock_mode' is a read only variable