MySQL自增鎖模式innodb_autoinc_lock_mode參數(shù)詳解
innodb_autoinc_lock_mode這個(gè)參數(shù)控制著在向有auto_increment 列的表插入數(shù)據(jù)時(shí)，相關(guān)鎖的行為；
通過對(duì)它的設(shè)置可以達(dá)到性能與安全(主從的數(shù)據(jù)一致性)的平衡
【0】我們先對(duì)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有三個(gè)取值：
1、0 這個(gè)表示tradition 傳統(tǒng)
2、1 這個(gè)表示consecutive 連續(xù)
3、2 這個(gè)表示interleaved 交錯(cuò)
【1.1】tradition(innodb_autoinc_lock_mode=0) 模式:
1、它提供了一個(gè)向后兼容的能力
2、在這一模式下，所有的insert語句("insert like") 都要在語句開始的時(shí)候得到一個(gè)
表級(jí)的auto_inc鎖，在語句結(jié)束的時(shí)候才釋放這把鎖，注意呀，這里說的是語句級(jí)而不是事務(wù)級(jí)的，一個(gè)事務(wù)可能包涵有一個(gè)或多個(gè)語句。
3、它能保證值分配的可預(yù)見性，與連續(xù)性，可重復(fù)性，這個(gè)也就保證了insert語句在復(fù)制到slave
的時(shí)候還能生成和master那邊一樣的值(它保證了基于語句復(fù)制的安全)。
4、由于在這種模式下auto_inc鎖一直要保持到語句的結(jié)束，所以這個(gè)就影響到了并發(fā)的插入。
【1.2】consecutive(innodb_autoinc_lock_mode=1) 模式:
1、這一模式下去simple insert 做了優(yōu)化，由于simple insert一次性插入值的個(gè)數(shù)可以立馬得到
確定，所以
mysql可以一次生成幾個(gè)連續(xù)的值，用于這個(gè)insert語句；總的來說這個(gè)對(duì)復(fù)制也是安全的
(它保證了基于語句復(fù)制的安全)
2、這一模式也是mysql的默認(rèn)模式，這個(gè)模式的好處是auto_inc鎖不要一直保持到語句的結(jié)束，只要
語句得到了相應(yīng)的值后就可以提前釋放鎖
【1.3】interleaved(innodb_autoinc_lock_mode=2) 模式
1、由于這個(gè)模式下已經(jīng)沒有了auto_inc鎖，所以這個(gè)模式下的性能是最好的；但是它也有一個(gè)問題，就是
對(duì)于同一個(gè)語句來說它所得到的auto_increment值可能不是連續(xù)的。
【2】如果你的二進(jìn)制文件格式是mixed | row 那么這三個(gè)值中的任何一個(gè)對(duì)于你來說都是復(fù)制安全的。
由于現(xiàn)在mysql已經(jīng)推薦把二進(jìn)制的格式設(shè)置成row，所以在binlog_format不是statement的情況下最好是innodb_autoinc_lock_mode=2 這樣可能知道更好的性能。
三種模式簡要說明:
0：traditonal （每次都會(huì)產(chǎn)生表鎖）
1：consecutive （會(huì)產(chǎn)生一個(gè)輕量鎖，simple insert會(huì)獲得批量的鎖，保證連續(xù)插入）
2：interleaved （不會(huì)鎖表，來一個(gè)處理一個(gè)，并發(fā)最高）
總結(jié)：
1 innodb row復(fù)制時(shí)，可將innodb_autoinc_lock_mode設(shè)置為2，這時(shí)可在所有insert情況下表獲得最大并發(fā)度
2 innodb statement復(fù)制時(shí)，可將innodb_autoinc_lock_mode設(shè)置為1，保證復(fù)制安全的同時(shí)，獲得簡單insert語句的最大并發(fā)度
3 myisam引擎情況下，無論什么樣自增id鎖都是表級(jí)鎖，設(shè)置innodb_autoinc_lock_mode參數(shù)無效（測試略）
4 實(shí)際上提問者說到的在innodb引擎下自增id值作為主鍵的情況下，相比uuid或者自定義的主鍵，是可以提到插入速度的，因?yàn)閕nnodb是主鍵聚集索引，實(shí)際的主鍵值必須按照主鍵順序存取，那么自增id本身就是升序的，那么在插入數(shù)據(jù)時(shí)，底層就不必再做額外的排序操作，也減少了索引頁分裂的次數(shù)，從而大大增加insert速度（除非其他方案也能保證主鍵完全自增）
修改自增鎖級(jí)別方法:
編輯/etc/my.cnf，加入如下行:
innodb_autoinc_lock_mode=2
直接通過命令修改會(huì)報(bào)錯(cuò):
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
