怎么理解mysql innodb_autoinc_lock_mode的與數(shù)據(jù)庫(kù)行為

這篇文章主要介紹“怎么理解mysql innodb_autoinc_lock_mode的與數(shù)據(jù)庫(kù)行為”，在日常操作中，相信很多人在怎么理解mysql innodb_autoinc_lock_mode的與數(shù)據(jù)庫(kù)行為問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對(duì)大家解答”怎么理解mysql innodb_autoinc_lock_mode的與數(shù)據(jù)庫(kù)行為”的疑惑有所幫助！接下來(lái)，請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧！

mysql 的auto_incremet 都知道了 

對(duì)應(yīng)的還有一個(gè)這個(gè)行為的一個(gè)鎖的類型的參數(shù),對(duì)數(shù)據(jù)庫(kù)的行為會(huì)有影響. 

innodb_autoinc_lock_mode  有3個(gè)取值 0 , 1 ,2 對(duì)應(yīng)于 
“traditional”,“consecutive”, or “interleaved” lock mode

針對(duì)有3中行為: 

1. insert_like  語(yǔ)句 包括: insert ,  insert ... select,  replace ,replace ... select  已經(jīng) load data 語(yǔ)句. 

2.simple insert  語(yǔ)句:  insert , replace 這樣. 

3.bulk insert  語(yǔ)句:  insert ...select , replace...select , load data.

4.mixed insert 語(yǔ)句: 例如 INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d'); 
           還有   INSERT ... ON DUPLICATE KEY UPDATE 這種語(yǔ)句. 

innodb_autoinc_lock_mode  取值為 0 : 

這個(gè)參數(shù)是從5.1 引入的, 對(duì)應(yīng)于傳統(tǒng)的模式.  對(duì)于insert like 語(yǔ)句這個(gè)auto_incremet  取得的是一個(gè)表級(jí)鎖. 

持有鎖的時(shí)間是到當(dāng)前語(yǔ)句結(jié)束. 確保語(yǔ)句的寫入是可以預(yù)見(jiàn)以及可以預(yù)測(cè),可以重現(xiàn)的,也就是可以保證語(yǔ)句入庫(kù)的數(shù)據(jù)時(shí)順序的. 

對(duì)于使用statement format 的 binlog 模式的復(fù)制,slave 跟主庫(kù)同一行記錄取得的值是一致. 

例如 : 
CREATE TABLE t1 (  c1 INT(11) NOT NULL AUTO_INCREMENT,  c2 VARCHAR(10) DEFAULT NULL,  PRIMARY KEY (c1)) ENGINE=InnoDB;

執(zhí)行語(yǔ)句:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...

Tx2: INSERT INTO t1 (c2) VALUES ('xxx');

 在statement 的復(fù)制中, 日志重演這個(gè)sql執(zhí)行,是可以確保主從取得的值是一致的. 

tx1 執(zhí)行的時(shí)候, 是可以保證 這1000筆記錄是連續(xù)的取得 autoinc 字段的值, tx2 要等到tx1 執(zhí)行完后才能拿到autoinc的值. 

這樣限制了并發(fā). 

innodb_autoinc_lock_mode 取值 1 : 

取值為1 為默認(rèn)值. 

對(duì)于 bulk insert  語(yǔ)句.  每個(gè)語(yǔ)句只有這個(gè)鎖到語(yǔ)句結(jié)束,但是每個(gè)語(yǔ)句持有這個(gè)鎖只能執(zhí)行一次. 

對(duì)于simple inset  語(yǔ)句是可預(yù)計(jì)的插入的值的數(shù)量,那么語(yǔ)句是可以不需要持有這個(gè)表級(jí)鎖到語(yǔ)句結(jié)束. 而是更高級(jí)別的mutex 鎖.

對(duì)于不可以預(yù)見(jiàn)插入數(shù)量的語(yǔ)句, 起行為跟bulk insert 是一樣的,持有鎖到語(yǔ)句結(jié)束,每次只能執(zhí)行一次. 

對(duì)于mixed inset  數(shù)據(jù)庫(kù)會(huì)分派多于插入插入數(shù)量的 autoinc 值 , 語(yǔ)句結(jié)束后,多余的值將丟棄, 這個(gè)時(shí)候, autoinc 字段的值可能不是連續(xù)的. 

innodb_autoinc_lock_mode  取值 2 : 

insert like 語(yǔ)句 可以持有這個(gè)表級(jí)鎖,并執(zhí)行相同的時(shí)間. 

這樣對(duì)于statement based  復(fù)制來(lái)說(shuō), 在恢復(fù)的時(shí)候,結(jié)果就變得不確定了. 也就是可能主從庫(kù)的autoinc 字段的值是不一致的. 

這樣會(huì)對(duì)業(yè)務(wù)邏輯代理困擾. 

這種模式下,每個(gè)語(yǔ)句都可以幾乎在同時(shí)產(chǎn)生autoinc值,這樣每個(gè)語(yǔ)句的所取得的autoinc字段的值是不確定的.也不可預(yù)測(cè). 

對(duì)于simple insert 如果在執(zhí)行前 可以預(yù)測(cè)到要插入的值的數(shù)量,這個(gè)sql的數(shù)據(jù)獲取的autoinc值是連續(xù)的.  否則就不一定是連續(xù)的. 

對(duì)于mixed insert 語(yǔ)句 取得autoinc字段的值是不可預(yù)測(cè)的. 

對(duì)于bulk insert 語(yǔ)句 取得autoinc 的值可能存在空缺 (gap)  即取得的值可能不是連續(xù)的. 

innodb_autoinc_lock_mode 的取值與復(fù)制的關(guān)系.

取值 0 跟 1  對(duì)復(fù)制來(lái)說(shuō)都是安全的, 即可以保證主從數(shù)據(jù)庫(kù)的值是一致的. 

如果卻只為2  在 基于 statement based 復(fù)制來(lái)說(shuō),從庫(kù)的數(shù)據(jù)時(shí)不確定的. 即不安全. 

對(duì)應(yīng) row based 或者 mixed based 的復(fù)制  從庫(kù)的數(shù)據(jù)時(shí)確定跟主庫(kù)一致的,是安全. 

可以看出在 取值2 的時(shí)候,可以獲得更大的性能. 

另外如果指定了 autoinc字段的值 insert 可能會(huì)觸發(fā) key 沖突的 錯(cuò)誤. 

如果insert 指定的值大于autoinc 字段的最大值, 其結(jié)果是不確定的. 

innodb_autoinc_lock_mode 取值為2的時(shí)候?qū)τ赽ulk insert 會(huì)產(chǎn)生空洞(gap)   同一個(gè)sql 寫入數(shù)據(jù)庫(kù)的值不一定是連續(xù)的. 

到此，關(guān)于“怎么理解mysql innodb_autoinc_lock_mode的與數(shù)據(jù)庫(kù)行為”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章！