共計(jì) 3382 個(gè)字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

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

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

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

下面分享下 auto_increment 自增鎖的一些信息.
講自增鎖, 就講一下 innodb_autoinc_lock_mode 參數(shù):
  在 mysql5.1.22 之前，mysql 的“INSERT-like”語句（包 INSERT, INSERT…SELECT, REPLACE,REPLACE…SELECT, and LOAD DATA) 會在執(zhí)行整個(gè)語句的過程中使用一個(gè) AUTO-INC 鎖將表鎖住，直到整個(gè)語句結(jié)束（而不是事務(wù)結(jié)束）。
  因此在使用 INSERT…SELECT、INSERT…values(…),values(…) 時(shí)，LOAD DATA 等耗費(fèi)時(shí)間較長的操作時(shí)，會將整個(gè)表鎖住，而阻塞其他的“INSERT-like”、Update 等語句，推薦使用程序?qū)⑦@些語句分成多條語句，一一插入，減少單一時(shí)間的鎖表時(shí)間。
 mysql5.1.22 之后 mysql 進(jìn)行了改進(jìn)，引入了參數(shù) innodb_autoinc_lock_mode，通過這個(gè)參數(shù)控制 mysql 的鎖表邏輯。
  在介紹這個(gè)之前先引入幾個(gè)術(shù)語，方便說明 innodb_autoinc_lock_mode。
 1.“INSERT-like”：
 INSERT, INSERT … SELECT, REPLACE, REPLACE … SELECT, and LOAD DATA, INSERT … VALUES(),VALUES()
 2.“Simple inserts”：
  就是通過分析 insert 語句可以確定插入數(shù)量的 insert 語句, INSERT, INSERT … VALUES(),VALUES()
 3.“Bulk inserts”：
  就是通過分析 insert 語句不能確定插入數(shù)量的 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，傳統(tǒng)模式)。
  這種方式就和 mysql5.1.22 以前一樣，為了向后兼容而保留了這種模式，如同前面介紹的一樣，這種方式的特點(diǎn)就是“表級鎖定”，并發(fā)性較差。
  二、innodb_autoinc_lock_mode = 1 (“consecutive”lock mode，連續(xù)模式)。
  這種方式是新版本中的默認(rèn)方式，推薦使用，并發(fā)性相對較高，特點(diǎn)是“consecutive”，即保證同一條 insert 語句中新插入的 auto_increment id 都是連續(xù)的。
  這種模式下：
 “Simple inserts”：直接通過分析語句，獲得要插入的數(shù)量，然后一次性分配足夠的 auto_increment id，只會將整個(gè)分配的過程鎖住。
 “Bulk inserts”：因?yàn)椴荒艽_定插入的數(shù)量，因此使用和以前的模式相同的表級鎖定。
 “Mixed-mode inserts”：直接分析語句，獲得最壞情況下需要插入的數(shù)量，然后一次性分配足夠的 auto_increment id，只會將整個(gè)分配的過程鎖住。
  需要注意的是，這種方式下，會分配過多的 id，而導(dǎo)致“浪費(fèi)”。
  比如 INSERT INTO t1 (c1,c2) VALUES (1,’a), (NULL,’b), (5,’c), (NULL,’d 會一次性的分配 5 個(gè) id，而不管用戶是否指定了部分 id；
 INSERT … ON DUPLICATE KEY UPDATE 一次性分配，而不管將來插入過程中是否會因?yàn)?duplicate key 而僅僅執(zhí)行 update 操作。
  注意：當(dāng) master mysql 版本 5.1.22，slave mysql 版本 =5.1.22 時(shí)，slave 需要將 innodb_autoinc_lock_mode 設(shè)置為 0，因?yàn)槟J(rèn)的 innodb_autoinc_lock_mode 為 1，對于 INSERT … ON DUPLICATE KEY UPDATE 和 INSERT INTO t1 (c1,c2) VALUES (1,’a), (NULL,’b), (5,’c), (NULL,’d 的執(zhí)行結(jié)果不同，現(xiàn)實(shí)環(huán)境一般會使用 INSERT … ON DUPLICATE KEY UPDATE。
  三、innodb_autoinc_lock_mode = 2 (“interleaved”lock mode，交叉模式)。
  這種模式是來一個(gè)分配一個(gè)，而不會鎖表，只會鎖住分配 id 的過程，和 innodb_autoinc_lock_mode = 1 的區(qū)別在于，不會預(yù)分配多個(gè)，這種方式并發(fā)性最高。
  但是在 replication 中當(dāng) binlog_format 為 statement-based 時(shí)（簡稱 SBR statement-based replication）存在問題，因?yàn)槭莵硪粋€(gè)分配一個(gè)，這樣當(dāng)并發(fā)執(zhí)行時(shí)，“Bulk inserts”在分配的時(shí)會同時(shí)向其他的 INSERT 分配，會出現(xiàn)主從不一致（從庫執(zhí)行結(jié)果和主庫執(zhí)行結(jié)果不一樣），因?yàn)?binlog 只會記錄開始的 insert id。
  測試 SBR，執(zhí)行 begin;insert values(),();insert values(),();commit; 會在 binlog 中每條 insert values(),(); 前增加 SET INSERT_ID=18/*!*/;。
  但是 row-based replication RBR 時(shí)不會存在問題。
  另外 RBR 的主要缺點(diǎn)是日志數(shù)量在包括語句中包含大量的 update delete（update 多條語句，delete 多條語句）時(shí)，日志會比 SBR 大很多；假如實(shí)際語句中這樣語句不是很多的時(shí)候（現(xiàn)實(shí)中存在很多這樣的情況），推薦使用 RBR 配合 innodb_autoinc_lock_mode，不過話說回來，現(xiàn)實(shí)生產(chǎn)中“Bulk inserts”本來就很少，因此 innodb_autoinc_lock_mode = 1 應(yīng)該是夠用了。

到此，關(guān)于“mysql auto_increment 鎖帶來的表鎖舉例分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注丸趣 TV 網(wǎng)站，丸趣 TV 小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！