mysql auto_increment鎖帶來的表鎖舉例分析

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

案例描述:
線上一張表有大概2億條,50個G左右大小的數據,業務進行重新規劃,需要將絕大部分數據進行歷史歸檔.當時為了方便,就新建一張相同結構的表,然后快速的rename成線上表,然后將備份表需要導入到線上表的數據進行insert into select操作.結果,線上表產生表鎖,業務全部堵住,發現情況時,已經沒辦法撤銷insert操作(因為已經插入很多了.在回滾,估計代價更高),所以就只有坐等insert完畢了.
create table new_table like old_table;   --創建一個跟線上表結構一樣的新表;
alter table new_table auto_increment=xxxx  --將新表的自增值設大一些,目的是為了跟老表數據留下空間和區別;
rename table old_tale to old_table_bak;
renmae table new_table to online_table;
-- 這兩行一起執行,減小切換表的時間,盡量減小對線上數據的影響;
insert into online_table select * from old_table_bak where xxxxxxx; --將歷史表中需要的數據導入新表;

案例分析:
也算是自己麻痹大意了,以為innodb的insert只會加行級鎖,沒考慮到auto_increment的自增鎖.產生了表鎖,影響了整個業務.

下面分享下auto_increment自增鎖的一些信息.
講自增鎖,就講一下innodb_autoinc_lock_mode參數:
 在mysql5.1.22之前，mysql的“INSERT-like”語句（包INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA)會在執行整個語句的過程中使用一個AUTO-INC鎖將表鎖住，直到整個語句結束（而不是事務結束）。
 因此在使用INSERT…SELECT、INSERT…values(…),values(…)時，LOAD DATA等耗費時間較長的操作時，會將整個表鎖住，而阻塞其他的“INSERT-like”、Update等語句，推薦使用程序將這些語句分成多條語句，一一插入，減少單一時間的鎖表時間。
 mysql5.1.22之后mysql進行了改進，引入了參數 innodb_autoinc_lock_mode，通過這個參數控制mysql的鎖表邏輯。
 在介紹這個之前先引入幾個術語，方便說明 innodb_autoinc_lock_mode。
 1.“INSERT-like”：
 INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()
 2.“Simple inserts”：
 就是通過分析insert語句可以確定插入數量的insert語句, INSERT, INSERT … VALUES(),VALUES()
 3.“Bulk inserts”：
 就是通過分析insert語句不能確定插入數量的insert語句, INSERT … SELECT, REPLACE … SELECT, LOAD DATA
 4.“Mixed-mode inserts”：
 不確定是否需要分配auto_increment id，一般是下面兩種情況
 INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');
 INSERT … ON DUPLICATE KEY UPDATE

一、innodb_autoinc_lock_mode = 0 (“traditional” lock mod，傳統模式)。
 這種方式就和mysql5.1.22以前一樣，為了向后兼容而保留了這種模式，如同前面介紹的一樣，這種方式的特點就是“表級鎖定”，并發性較差。
 二、innodb_autoinc_lock_mode = 1 (“consecutive” lock mode，連續模式)。
 這種方式是新版本中的默認方式，推薦使用，并發性相對較高，特點是“consecutive”，即保證同一條insert語句中新插入的auto_increment id都是連續的。
 這種模式下：
 “Simple inserts”：直接通過分析語句，獲得要插入的數量，然后一次性分配足夠的auto_increment id，只會將整個分配的過程鎖住。
 “Bulk inserts”：因為不能確定插入的數量，因此使用和以前的模式相同的表級鎖定。
 “Mixed-mode inserts”：直接分析語句，獲得最壞情況下需要插入的數量，然后一次性分配足夠的auto_increment id，只會將整個分配的過程鎖住。
 需要注意的是，這種方式下，會分配過多的id，而導致“浪費”。
 比如INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');會一次性的分配5個id，而不管用戶是否指定了部分id；
 INSERT … ON DUPLICATE KEY UPDATE一次性分配，而不管將來插入過程中是否會因為duplicate key而僅僅執行update操作。
 注意：當master mysql版本<5.1.22，slave mysql版本>=5.1.22時，slave需要將innodb_autoinc_lock_mode設置為0，因為默認的innodb_autoinc_lock_mode為1，對于INSERT … ON DUPLICATE KEY UPDATE和INSERT INTO t1 (c1,c2) VALUES (1,’a'), (NULL,’b'), (5,’c'), (NULL,’d');的執行結果不同，現實環境一般會使用INSERT … ON DUPLICATE KEY UPDATE。
 三、innodb_autoinc_lock_mode = 2 (“interleaved” lock mode，交叉模式)。
 這種模式是來一個分配一個，而不會鎖表，只會鎖住分配id的過程，和innodb_autoinc_lock_mode = 1的區別在于，不會預分配多個，這種方式并發性最高。
 但是在replication中當binlog_format為statement-based時（簡稱SBR statement-based replication）存在問題，因為是來一個分配一個，這樣當并發執行時，“Bulk inserts”在分配的時會同時向其他的INSERT分配，會出現主從不一致（從庫執行結果和主庫執行結果不一樣），因為binlog只會記錄開始的insert id。
 測試SBR，執行begin;insert values(),();insert values(),();commit;會在binlog中每條insert values(),();前增加SET INSERT_ID=18/*!*/;。
 但是row-based replication RBR時不會存在問題。
 另外RBR的主要缺點是日志數量在包括語句中包含大量的update delete（update多條語句，delete多條語句）時，日志會比SBR大很多；假如實際語句中這樣語句不是很多的時候（現實中存在很多這樣的情況），推薦使用RBR配合innodb_autoinc_lock_mode，不過話說回來，現實生產中“Bulk inserts”本來就很少，因此innodb_autoinc_lock_mode = 1應該是夠用了。

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