怎么理解mysql innodb_autoinc_lock_mode的與數據庫行為

這篇文章主要介紹“怎么理解mysql innodb_autoinc_lock_mode的與數據庫行為”，在日常操作中，相信很多人在怎么理解mysql innodb_autoinc_lock_mode的與數據庫行為問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”怎么理解mysql innodb_autoinc_lock_mode的與數據庫行為”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

mysql 的auto_incremet 都知道了 

對應的還有一個這個行為的一個鎖的類型的參數,對數據庫的行為會有影響. 

innodb_autoinc_lock_mode  有3個取值 0 , 1 ,2 對應于 
“traditional”,“consecutive”, or “interleaved” lock mode

針對有3中行為: 

1. insert_like  語句 包括: insert ,  insert ... select,  replace ,replace ... select  已經 load data 語句. 

2.simple insert  語句:  insert , replace 這樣. 

3.bulk insert  語句:  insert ...select , replace...select , load data.

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

innodb_autoinc_lock_mode  取值為 0 : 

這個參數是從5.1 引入的, 對應于傳統的模式.  對于insert like 語句這個auto_incremet  取得的是一個表級鎖. 

持有鎖的時間是到當前語句結束. 確保語句的寫入是可以預見以及可以預測,可以重現的,也就是可以保證語句入庫的數據時順序的. 

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

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

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

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

 在statement 的復制中, 日志重演這個sql執行,是可以確保主從取得的值是一致的. 

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

這樣限制了并發. 

innodb_autoinc_lock_mode 取值 1 : 

取值為1 為默認值. 

對于 bulk insert  語句.  每個語句只有這個鎖到語句結束,但是每個語句持有這個鎖只能執行一次. 

對于simple inset  語句是可預計的插入的值的數量,那么語句是可以不需要持有這個表級鎖到語句結束. 而是更高級別的mutex 鎖.

對于不可以預見插入數量的語句, 起行為跟bulk insert 是一樣的,持有鎖到語句結束,每次只能執行一次. 

對于mixed inset  數據庫會分派多于插入插入數量的 autoinc 值 , 語句結束后,多余的值將丟棄, 這個時候, autoinc 字段的值可能不是連續的. 

innodb_autoinc_lock_mode  取值 2 : 

insert like 語句 可以持有這個表級鎖,并執行相同的時間. 

這樣對于statement based  復制來說, 在恢復的時候,結果就變得不確定了. 也就是可能主從庫的autoinc 字段的值是不一致的. 

這樣會對業務邏輯代理困擾. 

這種模式下,每個語句都可以幾乎在同時產生autoinc值,這樣每個語句的所取得的autoinc字段的值是不確定的.也不可預測. 

對于simple insert 如果在執行前 可以預測到要插入的值的數量,這個sql的數據獲取的autoinc值是連續的.  否則就不一定是連續的. 

對于mixed insert 語句 取得autoinc字段的值是不可預測的. 

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

innodb_autoinc_lock_mode 的取值與復制的關系.

取值 0 跟 1  對復制來說都是安全的, 即可以保證主從數據庫的值是一致的. 

如果卻只為2  在 基于 statement based 復制來說,從庫的數據時不確定的. 即不安全. 

對應 row based 或者 mixed based 的復制  從庫的數據時確定跟主庫一致的,是安全. 

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

另外如果指定了 autoinc字段的值 insert 可能會觸發 key 沖突的 錯誤. 

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

innodb_autoinc_lock_mode 取值為2的時候對于bulk insert 會產生空洞(gap)   同一個sql 寫入數據庫的值不一定是連續的. 

到此，關于“怎么理解mysql innodb_autoinc_lock_mode的與數據庫行為”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！