十三、索引的優(yōu)點(diǎn)

• 減少磁盤掃描，提高檢索效率，避免了全表掃描。

• 提高排序和分組的效率。

• 將隨機(jī)IO轉(zhuǎn)化為順序IO。

• 提高部分聚合函數(shù)的效率，比如min()，max()等。

十四、如何創(chuàng)建高效的索引

• 在經(jīng)常用于排序和分組查詢的字段上建立索引，可以避免了內(nèi)存排序和隨機(jī)I/O。

• 在選擇性較高的字段上建立索引，查看選擇性公式select count(distinct a)/count(*) from t1，越接近1越好，一般超過33%就算是比較高效的索引了。

• 如果沒有強(qiáng)烈的業(yè)務(wù)需求，建議建立自增主鍵，這樣的主鍵占用空間小，順序?qū)懭?，減少頁分裂。

• 利用較短的鍵值作為索引性能比較好，可能的話盡量使用整數(shù)類型。

• 對于where條件中涉及多個(gè)字段時(shí)可以考慮建立聯(lián)合索引，建議將選擇性高的列放到 索引最左列，SQL查詢時(shí)滿足最左原則。

• 對于select后面經(jīng)常用到的字段可以考慮創(chuàng)建索引，查詢時(shí)使用覆蓋索引查詢，避免回表。

• 索引字段盡量設(shè)置為NOT NULL，NULL值會更加運(yùn)算的復(fù)雜度。

• 如果有 order by 的場景，盡量利用索引的有序性，避免出現(xiàn)using filesort 的情況，影響查詢性能。

• SQL投產(chǎn)前查看執(zhí)行計(jì)劃，SQL 性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級別，要求是 ref 級別，最好是 consts級別。

• SQL語句中盡量避免使用左模糊或者全模糊查詢，無法利用B+Tree 最左前綴匹配特性。

• 考慮針對較長字符串型列使前綴索引，區(qū)分度可以使用 count(distinct left(列名, 索引長度))/count(*)來確定，請參看上一章的前綴索引部分。

• 業(yè)務(wù)上具有唯一特性的字段，即使是組合字段，也建議建成唯一索引，數(shù)據(jù)庫層面避免了臟數(shù)據(jù)的產(chǎn)生，對insert的影響可以忽略（阿里巴巴開發(fā)手冊要求）。

• 在表查詢中，建議明確字段，不要使用 * 作為查詢的字段列表。

• 索引不宜過多，一般建議不超過6個(gè)，由于索引的創(chuàng)建和維護(hù)是有代價(jià)的，所以請不要?jiǎng)?chuàng)建不必要的索引。

十五、常見的索引失效的場景

• 通過索引掃描的行數(shù)超過全表的20%-30%時(shí)，引擎會認(rèn)為走全表掃描更有效。

• 使用聯(lián)合索引時(shí)沒有遵循最左原則。

• where后面出現(xiàn) or 條件 ，且沒有建立單列索引會導(dǎo)致失效。

• 對索引使用了函數(shù)計(jì)算。

• 統(tǒng)計(jì)信息不真實(shí)(嚴(yán)重不真實(shí))，導(dǎo)致執(zhí)行計(jì)劃錯(cuò)誤。

• 訪問小表時(shí)，更傾向于全表掃描。

• Where條件中對索引列使用左模糊或者全模糊查詢。

十六、mysql執(zhí)行計(jì)劃-type類型（const>eq_ref>ref>range>index>all）

• const：查詢索引字段，并且表中最多只有一行匹配（好像只有主鍵查詢只匹配一行才會是const，有些情況唯一索引匹配一行會是ref）

• eq_ref：主鍵或者唯一索引

• ref：非唯一索引（主鍵也是唯一索引）

• range：索引的范圍查詢

• index： (type=index extra = using index 代表索引覆蓋，即不需要回表)

• all：全表掃描（通常沒有建索引的列)

十七、MVCC（Mutil Version Concurrency Control 多版本并發(fā)控制）

mvcc是為了實(shí)現(xiàn)快照讀，也就是以樂觀鎖的形式進(jìn)行讀操作，通過版本鏈的方式，實(shí)現(xiàn)了讀-寫，寫-讀的并發(fā)執(zhí)行，提升了系統(tǒng)的性能。

當(dāng)前讀：讀取的是數(shù)據(jù)的最新版本（總是讀取到最新的數(shù)據(jù)）

• select ...... lock in share mode（共享鎖）

• select ...... for update（排它鎖）

• update

• delete

• insert

快照讀：讀取的是歷史版本的記錄

• select ......

事務(wù)隔離級別

• 讀未提交（READ UNCOMMITTED）

• 讀已提交（READ COMMITTED）

• 可重復(fù)讀（REPEATABLE READ）

• 可串行化（SERIALIZABLE）

mvcc的實(shí)現(xiàn)原理主要依賴于記錄中的三個(gè)隱藏字段以及undolog、read view來實(shí)現(xiàn)的。

隱藏字段：

每行記錄除了我們自定義的字段外，還有數(shù)據(jù)庫隱式定義的DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID等字段。

• DB_TRX_ID：6字節(jié)，最近修改的事務(wù)ID，記錄創(chuàng)建這條記錄或者最后一次修改該記錄的事務(wù)ID

• DB_ROLL_PTR：7字節(jié)，回滾指針，指向這條記錄的上一個(gè)版本，用于配合undolog，指向上一個(gè)舊版本。

• DB_ROW_ID：6字節(jié)， 隱藏主鍵，如果數(shù)據(jù)表沒有主鍵，那么innodb會自動(dòng)生成一個(gè)6字節(jié)的row_id。

undolog：

undolog 被稱為回滾日志，表示在進(jìn)行insert、delete、update操作的時(shí)候產(chǎn)生的方便回滾的日志。
當(dāng)進(jìn)行insert操作的時(shí)候，產(chǎn)生的undolog只在事務(wù)回滾的時(shí)候需要，并且在事務(wù)提交之后可以被立刻丟棄。

當(dāng)進(jìn)行update和delete操作的時(shí)候，產(chǎn)生的undolog不僅僅在事務(wù)回滾的時(shí)候需要，在快照讀的時(shí)候也需要，所以不能隨便刪除，只有在快照讀或事務(wù)回滾不涉及該日志時(shí)，對應(yīng)的日志才會被purge線程統(tǒng)一清除（當(dāng)數(shù)據(jù)發(fā)生更新和刪除操作的時(shí)候都只是設(shè)置一下老記錄的deleted_bit，并不是真正的將過時(shí)的記錄刪除，因?yàn)闉榱斯?jié)省磁盤空間，innodb有專門的purge線程來清除deleted_bit為true的記錄，如果某個(gè)記錄的deleted_bit為true，并且DB_TRX_ID相對于purge線程的read view 可見，那么這條記錄一定是可以被清除的）

不同事務(wù)或者相同事務(wù)的對同一條記錄的修改，會導(dǎo)致該記錄的undolog生成一條記錄版本線性表，即鏈表，undolog的鏈?zhǔn)拙褪亲钚碌呐f記錄，鏈尾就是最早的舊記錄。

Read View：

Read View是事務(wù)進(jìn)行快照讀操作的時(shí)候生成的讀視圖，在該事務(wù)執(zhí)行快照讀的那一刻，會生成一個(gè)數(shù)據(jù)系統(tǒng)當(dāng)前的快照，記錄并維護(hù)系統(tǒng)當(dāng)前活躍事務(wù)的ID，事務(wù)的ID是遞增的。

其實(shí)Read View的最大作用是用來做可見性判斷的，也就是說當(dāng)某個(gè)事務(wù)在執(zhí)行快照讀的時(shí)候，對該記錄創(chuàng)建一個(gè)Read View的視圖，把它當(dāng)做條件去判斷當(dāng)前事務(wù)能夠看到哪個(gè)版本的數(shù)據(jù)，有可能讀取到的是最新的數(shù)據(jù)，也有可能讀取的是當(dāng)前行記錄的undolog中某個(gè)版本的數(shù)據(jù)。

Read View遵循的可見性算法主要是將要被修改的數(shù)據(jù)的最新記錄中的DB_TRX_ID（當(dāng)前事務(wù)ID）取出來，與系統(tǒng)當(dāng)前其他活躍事務(wù)的ID去對比，如果DB_TRX_ID跟Read View的屬性做了比較，不符合可見性，那么就通過DB_ROLL_PTR回滾指針去取出undolog中的BD_TRX_ID做比較，即遍歷鏈表中的DB_TRX_ID，直到找到滿足條件的DB_TRX_ID，這個(gè)DB_TRX_ID所在的舊記錄就是當(dāng)前事務(wù)能看到的最新老版本數(shù)據(jù)。

Read View的可見性規(guī)則如下所示：

首先要知道Read View中的三個(gè)全局屬性：
trx_list：一個(gè)數(shù)值列表，用來維護(hù)Read View生成時(shí)刻系統(tǒng)正活躍的事務(wù)ID
up_limit_id：記錄trx_list列表中事務(wù)ID最小的ID
low_limit_id：Read View生成時(shí)刻系統(tǒng)尚未分配的下一個(gè)事務(wù)ID。

具體的比較規(guī)則如下：

1. 首先比較DB_TRX_ID < up_limit_id，如果小于，則當(dāng)前事務(wù)能看到DB_TRX_ID所在的記錄，如果大于等于則進(jìn)入下一個(gè)判斷。
2. 接下來判斷DB_TRX_ID >= low_limit_id，如果大于等于則代表DB_TRX_ID所在的記錄在Read View生成后才出現(xiàn)，那么對于當(dāng)前事務(wù)肯定不可見，如果小于，則進(jìn)入下一個(gè)判斷。
3. 判斷DB_TRX_ID是否在活躍事務(wù)中，如果在，則代表在Read View生成時(shí)刻，這個(gè)事務(wù)還是活躍狀態(tài)，還沒有commit，修改的數(shù)據(jù)，當(dāng)前事務(wù)也是看不到，如果不在，則說明這個(gè)事務(wù)在Read View生成之前就已經(jīng)開始commit，那么修改的結(jié)果是能夠看見的。

RC、RR級別下的innodb快照讀有什么不同：
因?yàn)镽ead View生成的時(shí)機(jī)不同，從而造成RC、RR級別下的快照讀的結(jié)果不同。

總結(jié)：在RC隔離級別下，是每個(gè)快照讀都會生成并獲取最新的Read View，而在RR隔離級別下，則是同一個(gè)事務(wù)中的第一個(gè)快照讀才會創(chuàng)建Read View，之后的快照讀獲取的都是同一個(gè)Read View。

在RR隔離級別下，只靠 MVCC 實(shí)現(xiàn)，可以保證可重復(fù)讀，還能防止幻讀，但并不是完全防止。

在“快照讀”的情況下，是可以防止幻讀的，但是在“當(dāng)前讀”的情況下，只能依靠臨鍵鎖（next-key locks）。

比如事務(wù)A開始后，執(zhí)行普通select語句，創(chuàng)建了快照；之后事務(wù)B執(zhí)行insert語句；然后事務(wù)A再執(zhí)行普通select語句，得到的還是之前B沒有insert過的數(shù)據(jù)，因?yàn)檫@時(shí)候A讀的數(shù)據(jù)是符合read view可見性條件的數(shù)據(jù)。這就防止了幻讀，此時(shí)事務(wù)A是快照讀。

但是，如果事務(wù)A執(zhí)行的不是普通select語句，而是select ... for update等語句，這時(shí)候，事務(wù)A是當(dāng)前讀，每次語句執(zhí)行的時(shí)候都是獲取的最新數(shù)據(jù)。在未使用臨鍵鎖（next-key locks）時(shí)，A先執(zhí)行 select ... where ID between 1 and 10 … for update；然后事務(wù)B再執(zhí)行 insert … ID = 5 …；然后 A 再執(zhí)行 select ... where ID between 1 and 10 … for update，就會發(fā)現(xiàn)，多了一條B insert進(jìn)去的記錄。這就產(chǎn)生幻讀了，所以單獨(dú)靠MVCC并不能完全防止幻讀。

十八、事務(wù)隔離級別

READ UNCOMMITTED 讀取未提交內(nèi)容

在這個(gè)隔離級別，所有事務(wù)都可以"看到"未提交事務(wù)的執(zhí)行結(jié)果。在這種級別上，可能會產(chǎn)生很多問題，除非用戶真的知道自己在做什么，并有很好的理由選擇這樣做。本隔離級別很少用于實(shí)際應(yīng)用，因?yàn)樗男阅芤膊槐仄渌阅芎枚嗌?，而別的隔離級別還有其他更多的優(yōu)點(diǎn)。讀取未提交數(shù)據(jù)，也被稱為"臟讀"。

READ COMMITTED 讀取已提交內(nèi)容

大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認(rèn)隔離級別(但是不是MySQL的默認(rèn)隔離級別)，滿足了隔離的早先簡單定義：一個(gè)事務(wù)開始時(shí)，只能“看見" 已經(jīng)提交事務(wù)所做的改變，一個(gè)事務(wù)從開始到提交前，所做的任何數(shù)據(jù)改變都是不可見的，除非已經(jīng)提交。這種隔離級別也支持所謂的"不可重復(fù)讀"。這意味著用戶運(yùn)行同一個(gè)語句兩次，看到的結(jié)果可能是不同的。

REPEATABLE READ 可重復(fù)讀

MySQL數(shù)據(jù)庫默認(rèn)的隔離級別。該級別解決了READ UNCOMMITTED隔離級別導(dǎo)致的問題。它保證同一事務(wù)的多個(gè)實(shí)例在并發(fā)讀取事務(wù)時(shí)，會“看到同樣的“數(shù)據(jù)行。不過，這會導(dǎo)致另外一個(gè)棘手問題"幻讀"。InnoDB和Falcon存儲引擎通過多版本并發(fā)控制機(jī)制解決了幻讀問題。

SERIALIZABLE 可串行化

該級別是最高級別的隔離級。它通過強(qiáng)制事務(wù)排序，使之不可能相互沖突，從而解決幻讀問題。簡而言之，SERIALIZABLE是在每個(gè)讀的數(shù)據(jù)行上加鎖。在這個(gè)級別，可能導(dǎo)致大量的超時(shí)Timeout和鎖競爭Lock Contention現(xiàn)象，實(shí)際應(yīng)用中很少使用到這個(gè)級別，但如果用戶的應(yīng)用為了數(shù)據(jù)的穩(wěn)定性，需要強(qiáng)制減少并發(fā)的話，也可以選擇這種隔離級。

• 臟讀

  臟讀是指一個(gè)事務(wù)讀取了未提交事務(wù)執(zhí)行過程中的數(shù)據(jù)。當(dāng)一個(gè)事務(wù)的操作正在多次修改數(shù)據(jù)，而在事務(wù)還未提交的時(shí)候，另外一個(gè)并發(fā)事務(wù)來讀取了數(shù)據(jù)，就會導(dǎo)致讀取到的數(shù)據(jù)并非是最終持久化之后的數(shù)據(jù)，這個(gè)數(shù)據(jù)就是臟讀的數(shù)據(jù)。

• 不可重復(fù)讀

  不可重復(fù)讀是指對于數(shù)據(jù)庫中的某個(gè)數(shù)據(jù)，一個(gè)事務(wù)執(zhí)行過程中多次查詢返回不同查詢結(jié)果，這就是在事務(wù)執(zhí)行過程中，數(shù)據(jù)被其他事務(wù)提交修改了。
  不可重復(fù)讀同臟讀的區(qū)別在于，臟讀是一個(gè)事務(wù)讀取了另一未完成的事務(wù)執(zhí)行過程中的數(shù)據(jù)，而不可重復(fù)讀是一個(gè)事務(wù)執(zhí)行過程中，另一事務(wù)提交并修改了當(dāng)前事務(wù)正在讀取的數(shù)據(jù)。

• 虛讀（幻讀）

  幻讀是事務(wù)非獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象，例如事務(wù)A批量對一個(gè)表中某一列列值為1的數(shù)據(jù)修改為2的變更，但是在這時(shí)，事務(wù)B對這張表插入了一條列值為1的數(shù)據(jù)，并完成提交。此時(shí)，如果事務(wù)A查看剛剛完成操作的數(shù)據(jù)，發(fā)現(xiàn)還有一條列值為1的數(shù)據(jù)沒有進(jìn)行修改，而這條數(shù)據(jù)其實(shí)是B剛剛提交插入的，這就是幻讀。
  幻讀和不可重復(fù)讀都是讀取了另一條已經(jīng)提交的事務(wù)（這點(diǎn)同臟讀不同），所不同的是不可重復(fù)讀查詢的都是同一個(gè)數(shù)據(jù)項(xiàng)，而幻讀針對的是一批數(shù)據(jù)整體（比如數(shù)據(jù)的個(gè)數(shù)）。

十九、ACID是通過什么實(shí)現(xiàn)的

A：原子性（通過undolog保證原子性的實(shí)現(xiàn)）
C：一致性（是最終的追求，通過AID實(shí)現(xiàn)的）
I：隔離性（通過鎖）
D：持久性（通過redolog保證持久性的實(shí)現(xiàn)）

二十、mysql日志

binlog：
歸屬于mysql server層，binlog 是邏輯日志，它記錄的是操作語句涉及的每一行修改前后的值，在任何存儲引擎下都可以使用。
二進(jìn)制日志，用于主從復(fù)制、崩潰恢復(fù)，默認(rèn)不開啟。

undolog：
歸屬于innodb引擎，是Innodb MVCC的重要組成部分，主要用于記錄歷史版本數(shù)據(jù)，用于事務(wù)回滾。

redolog：
歸屬于innodb引擎，redolog 是物理日志，它記錄的是數(shù)據(jù)頁修改邏輯以及 change buffer 的變更，只能在innodb引擎下使用。

redolog 是搭配緩沖池、change buffer 使用的，緩沖池的作用是緩存磁盤上的數(shù)據(jù)頁，減少磁盤的IO；change buffer 的作用是將寫操作先存在內(nèi)存中，等到下次需要讀取這些操作涉及到的數(shù)據(jù)頁時(shí)，就把數(shù)據(jù)頁加載到緩沖池中，然后在緩沖池中更新；

事務(wù)的持久性是通過redolog實(shí)現(xiàn)的（write ahead log（WAL）），即先寫日志再寫數(shù)據(jù)；而因?yàn)閎inlog和redolog兩種日志屬于不同的組件，所以為了保證數(shù)據(jù)的一致性，要保證binlog和redolog的一致，所以有了二階段提交的概念。

二階段提交

詳細(xì)執(zhí)行流程：

1. 執(zhí)行器先從引擎中找到數(shù)據(jù)，如果在內(nèi)存中則直接返回，如果不在內(nèi)存中，查詢后返回。
2. 執(zhí)行器拿到數(shù)據(jù)之后會先修改數(shù)據(jù)，然后調(diào)用引擎接口重新寫入數(shù)據(jù)。
3. 引擎將數(shù)據(jù)更新到內(nèi)存，同時(shí)寫數(shù)據(jù)到redolog中，此時(shí)處于prepare階段，并通知執(zhí)行器執(zhí)行完成，隨時(shí)可以操作。
4. 執(zhí)行器生成這個(gè)操作的binlog日志。
5. 執(zhí)行器調(diào)用引擎的事務(wù)提交接口，引擎把剛剛寫完的redolog中的狀態(tài)改成commit狀態(tài)，更新完成。

如果不拆分成兩個(gè)階段提交：

• 先寫redolog，后寫binlog： 假設(shè)在redolog寫完，binlog還沒有寫完的時(shí)候，mysql進(jìn)程異常重啟。mysql可以把數(shù)據(jù)恢復(fù)回來，但是由于binlog還沒寫完就崩潰了，這時(shí)候binlog里面就沒有記錄這條數(shù)據(jù)。因此，之后備份日志的時(shí)候，存起來的binlog里面就沒有這條語句，所以如果要用這個(gè)binlog來恢復(fù)臨時(shí)庫或者進(jìn)行主從復(fù)制的時(shí)候，就會造成數(shù)據(jù)與原庫不相等。

• 先寫binlog，后寫redolog： 如果在binlog寫完之后，mysql崩潰，由于redolog還沒有寫入，mysql重啟后這個(gè)事務(wù)無效，所以這一條數(shù)據(jù)丟失，但是binlog里面已經(jīng)記錄了這一條數(shù)據(jù)，所以在之后用binlog進(jìn)行恢復(fù)臨時(shí)庫或者主從復(fù)制的時(shí)候，就會造成數(shù)據(jù)與原庫不相等。

預(yù)寫日志 WAL（wite ahead log）：

先寫日志，再寫數(shù)據(jù)

因?yàn)殡S機(jī)讀寫的效率要低于順序讀寫，為了保證數(shù)據(jù)的一致性，可以先將數(shù)據(jù)通過順序讀寫的方式寫到日志文件中，然后再將數(shù)據(jù)寫入到對應(yīng)的磁盤文件中，這個(gè)過程順序IO的效率要遠(yuǎn)遠(yuǎn)高于隨機(jī)IO，換句話說，如果實(shí)際的數(shù)據(jù)沒有寫入到磁盤，那么只要日志文件保存成功了，數(shù)據(jù)就不會丟失，可以根據(jù)日志來進(jìn)行數(shù)據(jù)的恢復(fù)。

二十一、頁合并、頁分裂

mysql底層的數(shù)據(jù)結(jié)構(gòu)采用的B+tree，葉子節(jié)點(diǎn)中的每一頁是由雙向鏈表連接起來的，且順序排列，一個(gè)頁默認(rèn)大小是16kb。

頁的內(nèi)部原理：

• 頁可以空或者填充滿(100%)，行記錄會按照主鍵順序來排列。例如在使用AUTO_INCREMENT時(shí)，你會有順序的ID 1、2、3、4等。

• 頁還有另一個(gè)重要的屬性：MERGE_THRESHOLD。該參數(shù)的默認(rèn)值是50%頁的大小，它在InnoDB的合并操作中扮演了很重要的角色

• 當(dāng)你插入數(shù)據(jù)時(shí)，如果數(shù)據(jù)(大小)能夠放的進(jìn)頁中的話，那他們是按順序?qū)㈨撎顫M的。若當(dāng)前頁滿，則下一行記錄會被插入下一頁(NEXT)中。

• 根據(jù)B+樹的特性，它可以自頂向下遍歷，但也可以在各葉子節(jié)點(diǎn)水平遍歷。因?yàn)槊總€(gè)葉子節(jié)點(diǎn)都有著一個(gè)指向包含下一條(順序)記錄的頁的指針。例如，頁#5有指向頁#6的指針，頁#6有指向前一頁(#5)的指針和后一頁(#7)的指針。這種機(jī)制下可以做到快速的順序掃描(如范圍掃描)。

頁合并：
當(dāng)你刪了一行記錄時(shí)，實(shí)際上記錄并沒有被物理刪除，記錄被標(biāo)記為刪除并且它的空間變得允許被其他記錄聲明使用，當(dāng)頁中刪除的記錄達(dá)到MERGE_THRESHOLD（默認(rèn)頁體積的50%），InnoDB會開始尋找最靠近的頁（前或后）看看是否可以將兩個(gè)頁合并以優(yōu)化空間使用。

頁分裂：
頁可能填充至100%，在頁填滿了之后，下一頁會繼續(xù)接管新的記錄。但如果存在這種情況呢，插入了一條新數(shù)據(jù)，新數(shù)據(jù)應(yīng)該由頁#10進(jìn)行管理，但是頁#10滿了，而它的下一頁，頁#11也滿了，數(shù)據(jù)也不可能不按順序的插入，這個(gè)時(shí)候怎么辦呢？由于每一頁是由雙向鏈表連接起來的。

所以mysql的做法是（簡化版）：

1. 創(chuàng)建新頁
2. 判斷當(dāng)前頁（頁#10）可以從哪里進(jìn)行分裂（記錄行層面）
3. 移動(dòng)記錄行
4. 重新定義頁之間的關(guān)系

二十二、mysql鎖

按照鎖的粒度：

• 表鎖：意向鎖、自增鎖
• 行鎖：間隙鎖、臨鍵鎖、記錄鎖

按照鎖的方式：

• 共享鎖：讀鎖（S）

• 排它鎖：寫鎖（X）

• 意向共享鎖（IS）

• 意向排它鎖（I）

1. 表級鎖

MySQL里面表級別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lock，MDL）。

• 表鎖

  表鎖的語法是lock tables … read/write。可以用unlock tables主動(dòng)釋放鎖，也可以在客戶端斷開的時(shí)候自動(dòng)釋放。lock tables語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象。

  如果在某個(gè)線程A中執(zhí)行l(wèi)ock tables t1 read,t2 wirte;這個(gè)語句，則其他線程寫t1、讀寫t2的語句都會被阻塞。同時(shí)，線程A在執(zhí)行unlock tables之前，也只能執(zhí)行讀t1、讀寫t2的操作。連寫t1都不允許。

• MDL（meta data lock）元數(shù)據(jù)鎖

  另一類表級的鎖是MDL。MDL不需要顯式使用，在訪問一個(gè)表的時(shí)候會被自動(dòng)加上。MDL 不需要我們記命令，它是隱式使用的，訪問表會自動(dòng)加上。它的主要作用是防止 DDL（改表結(jié)構(gòu)） 和 DML（CRUD 表數(shù)據(jù)） 并發(fā)的沖突。如果一個(gè)查詢正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線程對這個(gè)表結(jié)構(gòu)做了變更，刪了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對不上，肯定不行。

  在MySQL5.5版本引入了MDL，當(dāng)對一個(gè)表做增刪改查操作的時(shí)候，加MDL讀鎖；當(dāng)要對表做結(jié)構(gòu)變更操作的時(shí)候，加MDL寫鎖。

  • 讀鎖之間不互斥，因此可以有多個(gè)線程同時(shí)對一張表增刪改查。
  • 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另一個(gè)執(zhí)行完才能開始執(zhí)行。

給一個(gè)表加字段，或者修改字段，或者加索引，需要掃描全表的數(shù)據(jù)。在對大表操作的時(shí)候，需要特別小心，以免對線上服務(wù)造成影響。

前提：注意，我這里的事務(wù)是手動(dòng)開啟和提交的。而 MDL 鎖是語句開始時(shí)申請，事務(wù)提交才釋放。所以，如果是自動(dòng)提交就不會出現(xiàn)下面的問題。

session A先啟動(dòng)，這時(shí)候會對表t加一個(gè)MDL讀鎖。由于session B需要的也是MDL讀鎖，因此可以正常執(zhí)行。之后sesession C會被blocked，是因?yàn)閟ession A的MDL讀鎖還沒有釋放，而session C需要MDL寫鎖，因此只能被阻塞。如果只有session C自己被阻塞還沒什么關(guān)系，但是之后所有要在表t上新申請MDL讀鎖的請求也會被session C阻塞。所有對表的增刪改查操作都需要先申請MDL讀鎖，就都被鎖住，等于這個(gè)表現(xiàn)在完全不可讀寫了。

事務(wù)中的MDL鎖，在語句執(zhí)行開始時(shí)申請，但是語句結(jié)束后并不會馬上釋放，而會等到整個(gè)事務(wù)提交后再釋放

• 如何安全地給小表加字段？

  首先要解決長事務(wù)（一個(gè)事務(wù)包括 session A、B、C、D 的操作），事務(wù)不提交，就會一直占著MDL 鎖。在MySQL的information_schema庫的innodb_trx表中，可以查到當(dāng)前執(zhí)行的事務(wù)。如果要做DDL變更的表剛好有長事務(wù)在執(zhí)行，要考慮先暫停DDL，或者kill掉這個(gè)長事務(wù)。

• 如果要變更的表是一個(gè)熱點(diǎn)表，雖然數(shù)據(jù)量不大，但是上面的請求很頻繁，而又不得不加個(gè)字段，該怎么做？

  在alter table語句里面設(shè)定等待時(shí)間，如果在這個(gè)指定的等待時(shí)間里面能夠拿到MDL寫鎖最好，拿不到也不要阻塞后面的業(yè)務(wù)語句，先放棄。之后再通過重試命令重復(fù)這個(gè)過程。

// N 以秒為單位
alter table tableName wait N add column .....

2. 行鎖

mysql 的行索是在引擎實(shí)現(xiàn)的，但并不是所有引擎都支持行鎖，不支持行鎖的引擎只能使用表鎖。行鎖比較容易理解：行鎖就是針對數(shù)據(jù)表中行記錄的鎖。比如：事務(wù) A 先更新一行，同時(shí)事務(wù) B 也要更新同一行，則必須等事務(wù) A 的操作完成后才能進(jìn)行更新。

• 兩階段鎖協(xié)議：

  先舉個(gè)栗子：事務(wù) A 和 B 對 student 中的記錄進(jìn)行操作。

  其中事務(wù) A 先啟動(dòng)，在這個(gè)事務(wù)中更新兩條數(shù)據(jù)；事務(wù) B 后啟動(dòng)，更新 id = 1 的數(shù)據(jù)。由于 A 更新的也是 id = 1 的數(shù)據(jù)，所以事務(wù) B 的 update 語句從事務(wù) A 開始就會被阻塞，直到事務(wù) A 執(zhí)行 commit 之后，事務(wù) B 才能繼續(xù)執(zhí)行。

  在事務(wù)期間，事務(wù) A 實(shí)際上持有 id = 1 和 id = 2 這兩行的行鎖。如果事務(wù) B 更新的是 id = 2 的數(shù)據(jù)，那么它阻塞的時(shí)間就是從 A 更新 id = 2 這行開始（事務(wù) A 更新 id = 1 時(shí)，它并沒有阻塞），到事務(wù) A 提交結(jié)束，比更新 id = 1 數(shù)據(jù)阻塞的時(shí)間要短。 PS：理解這句話很重要。

  在 InnoDB 事務(wù)中，行鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放。這個(gè)就是兩階段鎖協(xié)議，分為加鎖階段和解鎖階段，所有的 lock 操作都在 unlock 操作之后。
  根據(jù)這個(gè)特性，對于高并發(fā)的行記錄的操作語句就可以盡可能的安排到最后面，以減少鎖等待的時(shí)間，提高并發(fā)性能。

  假設(shè)你負(fù)責(zé)實(shí)現(xiàn)一個(gè)電影票在線交易業(yè)務(wù)，顧客 A 要在影院 B 購買電影票。我們簡化一點(diǎn)，這個(gè)業(yè)務(wù)需要涉及到以下操作：

  1. 從用戶 A 賬戶余額中扣除電影票價(jià)；
  2. 給影院 B 的賬戶余額增加這張電影票價(jià)；
  3. 記錄一條交易日志。

  也就是說，要完成這個(gè)交易，需要 update 兩條記錄，并 insert 一條記錄。當(dāng)然，為了保證交易的原子性，我們要把這三個(gè)操作放在一個(gè)事務(wù)中。那么，怎樣安排這三個(gè)語句在事務(wù)中的順序呢？ 分析一下：

  用戶余額是個(gè)人的，并發(fā)很低；
  影院賬戶表每個(gè)用戶都要訪問，并發(fā)很高；
  交易記錄是插入操作問題不大。

  這時(shí)將事務(wù)步驟安排成 3、1、2 這樣的順序是最佳的。因?yàn)榇藭r(shí)如果有別的用戶買票，它的事務(wù)在順序 1、2 并不會阻塞，而是到了順序 3 更新影院賬戶表才會引起阻塞。但它的阻塞時(shí)間是最短的，其他操作不需要等待鎖。

• 死鎖：

  不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時(shí)，就會導(dǎo)致這幾個(gè)線程都進(jìn)入無限等待的狀態(tài)，稱為死鎖。舉個(gè)行鎖死鎖的例子：兩個(gè)事物相互等待對方持有的鎖。

  操作開始：

  • 事務(wù) A 持有 id = 1 的行鎖，事務(wù) B 持有 id = 2 的行鎖；

  • 事務(wù) A 想更新 id = 2 行數(shù)據(jù)，不料事務(wù) B 已持有，事務(wù) A 只能等待事務(wù) B 釋放 id = 2 的行鎖；

  • 同理，事務(wù) B 想更新 id = 1 行數(shù)據(jù)，不料事務(wù) A 已持有，事務(wù) B 只能等事務(wù) A 釋放 id = 1 的行鎖。

  兩者互相等待，這就是死鎖。

  • 如何解決死鎖？

    有兩個(gè)解決策略：

    1. 進(jìn)入等待，直到超時(shí)（加入等待時(shí)間）

       首先是第一種：直接進(jìn)入等待，直到超時(shí)。這個(gè)超時(shí)時(shí)間可以通過參數(shù) innodb_lock_wait_timeout 設(shè)置。 這個(gè)參數(shù)，默認(rèn)設(shè)置的鎖等待時(shí)間是 50s在 MySQL 中，像下面這樣執(zhí)行即可：

        // 設(shè)置等待時(shí)間
        set global innodb_lock_wait_timeout = 500;
       

       上面這個(gè)語句表示：當(dāng)出現(xiàn)死鎖以后，第一個(gè)被鎖住的線程要過 500s 才會超時(shí)退出，然后其他線程才有可能繼續(xù)執(zhí)行。鎖等待時(shí)間不能設(shè)置過小，有些線程可能并沒有發(fā)生死鎖，只是正常的等待鎖。這就會造成本來正常的鎖機(jī)制出問題，當(dāng)然也不能太長。

    2. 進(jìn)行死鎖檢測，主動(dòng)回滾某個(gè)事務(wù)

       再看第二種：死鎖檢測，主動(dòng)回滾某個(gè)事務(wù)。 MySQL 通過設(shè)置 innodb_deadlock_detect 的值決定是否開啟檢測，默認(rèn)值是 on（開啟）。

       主動(dòng)死鎖檢測在發(fā)生死鎖的時(shí)候，可以快速發(fā)現(xiàn)并進(jìn)行處理的，但是它也有額外負(fù)擔(dān)。
       什么負(fù)擔(dān)呢？循環(huán)依賴檢測，過程如下：

       新來的線程 F，被鎖了后就要檢查鎖住 F 的線程（假設(shè)為 D）是否被鎖，如果沒有被鎖，則沒有死鎖，如果被鎖了，還要查看鎖住線程 D 的是誰，如果是 F，那么肯定死鎖了，如果不是 F（假設(shè)為 B），那么就要繼續(xù)判斷鎖住線程 B 的是誰，一直走知道發(fā)現(xiàn)線程沒有被鎖（無死鎖）或者被 F 鎖住（死鎖）才會終止

       如果大量并發(fā)修改同一行數(shù)據(jù)，死鎖檢測又會怎樣呢？
       假設(shè)有 1000 個(gè)并發(fā)線程同時(shí)更新同一行，那么死鎖檢測操作就是 1000 x 1000 達(dá)到 100 萬量級的。即便最終檢測結(jié)果沒有死鎖，但這期間要消耗大量 CPU 資源。所以，就會出現(xiàn) CPU 利用率很高，但是每秒?yún)s執(zhí)行不了幾個(gè)事務(wù)的情況。

       解決熱點(diǎn)行更新問題：

       那前面兩種方案都有弊端，死鎖的問題應(yīng)該怎么解決呢？
       一種比較依賴運(yùn)氣的方法就是：如果你能確保這個(gè)業(yè)務(wù)一定不會出現(xiàn)死鎖，可以臨時(shí)把死鎖檢測關(guān)掉。 但是這可能會影響到業(yè)務(wù)：開啟死鎖檢測，出現(xiàn)死鎖就回滾重試，不會影響到業(yè)務(wù)。如果關(guān)閉，可能就會大量超時(shí)，嚴(yán)重就會拖垮數(shù)據(jù)庫。

       另一種就是在服務(wù)端（消息隊(duì)列或者數(shù)據(jù)庫服務(wù)端）控制并發(fā)度： 之所以擔(dān)心死鎖檢測會造成額外的負(fù)擔(dān)，是因?yàn)椴l(fā)線程很多的時(shí)候，假設(shè)我們能在服務(wù)端做下限流，比如同一樣最多只能允許 10 個(gè)線程同時(shí)修改。
       一個(gè)思想：減少死鎖的主要方向，就是控制訪問相同資源的并發(fā)事務(wù)量。

3. 共享鎖（Share Lock）

共享鎖又稱為讀鎖，簡稱S鎖，顧名思義，共享鎖就是多個(gè)事務(wù)對于同一數(shù)據(jù)可以共享一把鎖，都能訪問到最新數(shù)據(jù)。Mysql會對查詢結(jié)果中的每行都加共享鎖，當(dāng)沒有其他線程對查詢結(jié)果集中的任何一行使用排他鎖時(shí)，可以成功申請共享鎖，否則會被阻塞。

用法：SELECT ... LOCK IN SHARE MODE;

1. 多個(gè)事務(wù)的查詢語句可以共用一把共享鎖；
2. 如果只有一個(gè)事務(wù)拿到了共享鎖，則該事務(wù)可以對數(shù)據(jù)進(jìn)行 UPDATE DETELE 等操作；
3. 如果有多個(gè)事務(wù)拿到了共享鎖，則所有事務(wù)都不能對數(shù)據(jù)進(jìn)行 UPDATE DETELE 等操作。

使用場景：

1. 確保某個(gè)事務(wù)查到最新的數(shù)據(jù)；
2. 這個(gè)事務(wù)不需要對數(shù)據(jù)進(jìn)行修改、刪除等操作；
3. 也不允許其它事務(wù)對數(shù)據(jù)進(jìn)行修改、刪除等操作；
4. 其它事務(wù)也能確保查到最新的數(shù)據(jù)。

對于性能的影響：“雖然共享鎖可以給多個(gè)事務(wù)共享，但一旦有多個(gè)事務(wù)同時(shí)擁有共享鎖，則所有事務(wù)都不能對數(shù)據(jù)進(jìn)行 UPDATE DETELE 等操作，也會導(dǎo)致其它事務(wù)的鎖等待、鎖等待超時(shí)、死鎖等問題；

4. 排它鎖（eXclusive Lock）

排他鎖又稱為寫鎖，簡稱X鎖，顧名思義，排它鎖不能與其它鎖并存，而且只有一個(gè)事務(wù)能拿到某一數(shù)據(jù)行的排它鎖，其余事務(wù)不能再獲取該數(shù)據(jù)行的所有鎖。Mysql會對查詢結(jié)果中的每行都加排他鎖，當(dāng)沒有其他線程對查詢結(jié)果集中的任何一行使用排他鎖時(shí)，可以成功申請排他鎖，否則會被阻塞。

用法：SELECT ... FOR UPDATE;

1. 只有一個(gè)事務(wù)能獲取該數(shù)據(jù)的排它鎖；
2. 一旦有一個(gè)事務(wù)獲取了該數(shù)據(jù)的排它鎖之后，其余事務(wù)對于該數(shù)據(jù)的操作將會被阻塞，直至鎖釋放。

使用場景：

1. 確保某個(gè)事務(wù)查到最新的數(shù)據(jù)；
2. 并且只有該事務(wù)能對數(shù)據(jù)進(jìn)行修改、刪除等操作。

對于性能的影響： 因?yàn)榕潘i只允許一個(gè)事務(wù)獲取，所以如果是業(yè)務(wù)繁忙的情況下，一旦有某個(gè)業(yè)務(wù)不能及時(shí)的釋放鎖，則會導(dǎo)致其它事務(wù)的鎖等待、鎖等待超時(shí)、死鎖等問題；

5. 意向鎖

意向鎖意向鎖是一種不與行級鎖沖突的表級鎖，其設(shè)計(jì)目的主要是為了在一個(gè)事務(wù)中揭示下一行將要被請求鎖的類型。

InnoDB中的兩個(gè)表鎖：

• 意向共享鎖（IS）：表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入共享鎖，也就是說一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖

• 意向排他鎖（IX）：類似上面，表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排他鎖，說明事務(wù)在一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

用戶無法操作意向鎖，意向鎖是由InnoDB自己維護(hù)的。說白了，意向鎖是幫助InnoDB提高效率的一種手段。

對于insert、update、delete，InnoDB會自動(dòng)給涉及的數(shù)據(jù)加排他鎖（X）；對于一般的select語句，InnoDB不會加任何鎖，事務(wù)可以通過以下語句加共享鎖或排他鎖。

• 共享鎖：SELECT ... LOCK IN SHARE MODE;

• 排他鎖：SELECT ... FOR UPDATE;

意向鎖解決了什么問題？

假設(shè)，事務(wù)A獲取了某一行的排它鎖，尚未提交，此時(shí)事務(wù)B想要獲取表鎖時(shí)，必須要確認(rèn)表的每一行都不存在排他鎖，很明顯效率會很低，引入意向鎖之后，效率就會大為改善：

1. 如果事務(wù)A獲取了某一行的排它鎖，實(shí)際此表存在兩種鎖，表中某一行的排他鎖和表上的意向排他鎖。
2. 如果事務(wù)B試圖在該表級別上加鎖時(shí)，則受到上一個(gè)意向鎖的阻塞，它在鎖定該表前不必檢查各個(gè)頁或行鎖，而只需檢查表上的意向鎖。

打個(gè)比方，就像有個(gè)游樂場，很多小朋友進(jìn)去玩，看門大爺如果要下班鎖門，他必須確保每個(gè)角落都要去檢查一遍，確保每個(gè)小朋友都離開了，才可以鎖門。假設(shè)鎖門是件頻繁發(fā)生的事情，大爺就會非常崩潰。那大爺想了一個(gè)辦法，每個(gè)小朋友進(jìn)入，就把自己的名字寫在本子上，小朋友離開，就把自己的名字劃掉，那大爺就能方便掌握有沒有小朋友在游樂場里，不必每個(gè)角落都去尋找一遍。例子中的“小本子”，就是意向鎖，他記錄的信息并不精細(xì)，不會記下小明是在玩木馬還是在玩蹦床，他只是提醒大爺，小明在游樂場里。

InnoDB行鎖模式兼容性列表：

6. 記錄鎖（Record Locks）

記錄鎖是 封鎖記錄，記錄鎖也叫行鎖，例如：

select * from test where id = 1 for update;
它會在 id=1 的記錄上加上記錄鎖，以阻止其他事務(wù)插入，更新，刪除 id=1 這一行。

記錄鎖、間隙鎖、臨鍵鎖都是排它鎖。

7. 間隙鎖（Gap Locks）

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時(shí)，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對于鍵值在條件范圍內(nèi)但不存在的記錄，叫做“間隙(GAP)”，InnoDB也會對這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖(NEXT-KEY)鎖，間隙鎖只有在事務(wù)隔離級別 RR 中才會產(chǎn)生。

在MySQL中select稱為快照讀，不需要鎖，而insert、update、delete、select for update則稱為當(dāng)前讀，需要給數(shù)據(jù)加鎖，幻讀中的“讀”即是針對當(dāng)前讀。

RR事務(wù)隔離級別允許存在幻讀，但I(xiàn)nnoDB RR級別卻通過Gap鎖避免了幻讀

舉個(gè)栗子，介紹間隙鎖是怎么產(chǎn)生的：
假設(shè)有以下表t_student：（其中id為PK，name為非唯一索引）

t_student

這個(gè)時(shí)候我們發(fā)出一條這樣的加鎖sql語句：
select id,name from t_student where id > 0 and id < 5 for update;
這時(shí)候，我們命中的數(shù)據(jù)為以下著色部分：

t_student

細(xì)心的朋友可能就會發(fā)現(xiàn)，這里缺少了條id為2的記錄，我們的重點(diǎn)就在這里。
select ... for update這條語句，是會對數(shù)據(jù)記錄加鎖的，這里因?yàn)槊辛怂饕拥氖切墟i。從數(shù)據(jù)記錄來看，這里排它鎖鎖住數(shù)據(jù)是id為1、3和4的這3條數(shù)據(jù)。
但是，看看前面我們的介紹——對于鍵值在條件范圍內(nèi)但不存在的記錄，叫做“間隙(GAP)”，InnoDB也會對這個(gè)“間隙”加鎖。
好了，我們這里，鍵值在條件范圍但是不存在的記錄，就是id為2的記錄，這里會對id為2數(shù)據(jù)加上間隙鎖。假設(shè)這時(shí)候如果有id=2的記錄insert進(jìn)來了，是要等到這個(gè)事務(wù)結(jié)束以后才會執(zhí)行的。

間隙鎖的作用：
總的來說，有2個(gè)作用，防止幻讀和防止數(shù)據(jù)誤刪/改。

1. 防止幻讀

如果沒有間隙鎖，事務(wù)A在T1和T4讀到的結(jié)果是不一樣的，有了間隙鎖，讀的就是一樣的了。

2. 防止數(shù)據(jù)誤刪/改

   這個(gè)作用比較重要，假設(shè)以下場景：

這種情況下，如果沒有間隙鎖，會出現(xiàn)的問題是：id為2的記錄，剛加進(jìn)去，就被刪除了，這種情況有時(shí)候?qū)I(yè)務(wù)，是致命性的打擊。加了間隙鎖之后，由于insert語句要等待事務(wù)A執(zhí)行完之后釋放鎖，避免了這種情況。

3. 使用間隙鎖的隱患

   最大的隱患就是性能問題
   前面提到，假設(shè)這時(shí)候如果有id=2的記錄insert進(jìn)來了，是要等到這個(gè)事務(wù)結(jié)束以后才會執(zhí)行的，假設(shè)是這種場景

這種情況，對插入的性能就有很大影響了，必須等到事務(wù)結(jié)束才能進(jìn)行插入，性能大打折扣
更有甚者，如果間隙鎖出現(xiàn)死鎖的情況下，會更隱晦，更難定位。

總結(jié)：

1. 對于指定查詢某一條記錄的加鎖語句，如果該記錄不存在，會產(chǎn)生記錄鎖和間隙鎖，如果記錄存在，則只會產(chǎn)生記錄鎖， 如：WHERE id = 5 FOR UPDATE;
2. 對于查找某一范圍內(nèi)的查詢語句，會產(chǎn)生間隙鎖，如：WHERE id BETWEEN 5 AND 7 FOR UPDATE;
3. 在普通索引列上，不管是何種查詢，只要加鎖，都會產(chǎn)生間隙鎖，這跟唯一索引不一樣；
4. 在普通索引跟唯一索引中，數(shù)據(jù)間隙的分析，數(shù)據(jù)行是優(yōu)先根據(jù)普通索引排序，再根據(jù)唯一索引排序。

8. 臨鍵鎖(Next-key Locks)

臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖范圍，既包含索引記錄，又包含索引區(qū)間，即會鎖住一段左開右閉區(qū)間的數(shù)據(jù)。

例如查詢id1-5的數(shù)據(jù)，間隙鎖只會鎖住2、3、4的行數(shù)據(jù)，而臨建鎖會鎖住1、2、3、4、5這幾行數(shù)據(jù)。

注：臨鍵鎖的主要目的，也是為了避免幻讀(Phantom Read)。如果把事務(wù)的隔離級別降級為RC，臨鍵鎖則也會失效。

9. 自增鎖

自增鎖是一種比較特殊的表級鎖。并且在事務(wù)向包含了AUTO_INCREMENT列的表中新增數(shù)據(jù)時(shí)就會去持有自增鎖，假設(shè)事務(wù) A 正在做這個(gè)操作，如果另一個(gè)事務(wù) B 嘗試執(zhí)行INSERT語句，事務(wù) B 會被阻塞住，直到事務(wù) A 釋放自增鎖。
但是上面這種說法也不完全對，因?yàn)樽栽鲦i的實(shí)現(xiàn)方式有多種。

鎖模式

• 傳統(tǒng)模式（Traditional）
• 連續(xù)模式（Consecutive）
• 交叉模式（Interleaved）

分別對應(yīng)配置項(xiàng) innodb_autoinc_lock_mode 的值0、1、2。
在這三種模式下，InnoDB 對并發(fā)的處理是不一樣的，而且具體選擇哪種鎖模式跟你當(dāng)前使用的 MySQL 版本還有關(guān)系。
在 MySQL 8.0 之前，InnoDB 鎖模式默認(rèn)為連續(xù)模式，值為1，而在 MySQL 8.0 之后，默認(rèn)模式變成了交叉模式。

傳統(tǒng)模式

傳統(tǒng)模式（Traditional），說白了就是還沒有鎖模式這個(gè)概念時(shí)，InnoDB 的自增鎖運(yùn)行的模式。只是后面版本更新，InnoDB 引入了鎖模式的概念，然后 InnoDB 給了這種以前默認(rèn)的模式一個(gè)名字，傳統(tǒng)模式。
當(dāng)向包含了 AUTO_INCREMENT 列的表中插入數(shù)據(jù)時(shí)，都會持有這么一個(gè)特殊的表鎖——自增鎖（AUTO-INC），并且當(dāng)語句執(zhí)行完之后就會釋放。這樣一來可以保證單個(gè)語句內(nèi)生成的自增值是連續(xù)的。這樣一來，傳統(tǒng)模式的弊端就自然暴露出來了，如果有多個(gè)事務(wù)并發(fā)的執(zhí)行 INSERT 操作，AUTO-INC的存在會使得 MySQL 的性能略有下降，因?yàn)橥瑫r(shí)只能執(zhí)行一條 INSERT 語句。

連續(xù)模式

連續(xù)模式（Consecutive）是 MySQL 8.0 之前默認(rèn)的模式，之所以提出這種模式，是因?yàn)閭鹘y(tǒng)模式存在影響性能的弊端，所以才有了連續(xù)模式。
在鎖模式處于連續(xù)模式下時(shí)，如果 INSERT 語句能夠提前確定插入的數(shù)據(jù)量，則可以不用獲取自增鎖，舉個(gè)例子，像 INSERT INTO 這種簡單的、能提前確認(rèn)數(shù)量的新增語句，就不會使用自增鎖，這個(gè)很好理解，在自增值上，我可以直接把這個(gè) INSERT 語句所需要的空間流出來，就可以繼續(xù)執(zhí)行下一個(gè)語句了。
但是如果 INSERT 語句不能提前確認(rèn)數(shù)據(jù)量，則還是會去獲取自增鎖。例如像 INSERT INTO ... SELECT ... 這種語句，INSERT 的值來源于另一個(gè) SELECT 語句。

交叉模式

交叉模式（Interleaved）下，所有的 INSERT 語句，包含 INSERT 和 INSERT INTO ... SELECT ，都不會使用 AUTO-INC 自增鎖，而是使用較為輕量的 mutex 鎖。這樣一來，多條 INSERT 語句可以并發(fā)的執(zhí)行，這也是三種鎖模式中擴(kuò)展性最好的一種。
并發(fā)執(zhí)行所帶來的副作用就是單個(gè) INSERT 的自增值并不連續(xù)，因?yàn)?AUTO_INCREMENT 的值分配會在多個(gè) INSERT 語句中來回交叉的執(zhí)行。

交叉模式缺點(diǎn)
優(yōu)點(diǎn)很明確，缺點(diǎn)是在并發(fā)的情況下無法保證數(shù)據(jù)一致性。
要了解缺點(diǎn)具體是什么，還得先了解一下 MySQL 的 Binlog。Binlog 一般用于 MySQL 的數(shù)據(jù)復(fù)制，通俗一點(diǎn)就是用于主從同步。在 MySQL 中 Binlog 的格式有 3 種，分別是：

• Statement：基于語句，只記錄對數(shù)據(jù)做了修改的SQL語句，能夠有效的減少binlog的數(shù)據(jù)量，提高讀取、基于binlog重放的性能。

• Row：只記錄被修改的行，所以Row記錄的binlog日志量一般來說會比Statement格式要多。基于Row的binlog日志非常完整、清晰，記錄了所有數(shù)據(jù)的變動(dòng)，但是缺點(diǎn)也可能會非常多，例如一條update語句，有可能是所有的數(shù)據(jù)都有修改；再例如alter table之類的，修改了某個(gè)字段，同樣的每條記錄都有改動(dòng)。

• Mixed：Statement和Row的結(jié)合，怎么個(gè)結(jié)合法呢。例如像alter table之類的對表結(jié)構(gòu)的修改，采用Statement格式。其余的對數(shù)據(jù)的修改例如update和delete采用Row格式進(jìn)行記錄。

如果 MySQL 采用的格式為 Statement ，那么 MySQL 的主從同步實(shí)際上同步的就是一條一條的 SQL 語句。如果此時(shí)我們采用了交叉模式，那么并發(fā)情況下 INSERT 語句的執(zhí)行順序就無法得到保障。
INSERT 同時(shí)交叉執(zhí)行，并且 AUTO_INCREMENT 交叉分配將會直接導(dǎo)致主從之間同行的數(shù)據(jù)主鍵 ID 不同。而這對主從同步來說是災(zāi)難性的。

交叉模式

由于insert語句并行執(zhí)行，所以就會出現(xiàn)上面的情況，兩個(gè)庫生成的主鍵不一致。
換句話說，如果你的 DB 有主從同步，并且 Binlog 存儲格式為 Statement，那么不要將 InnoDB 自增鎖模式設(shè)置為交叉模式，會有問題。
而后來，MySQL 將日志存儲格式從 Statement 變成了 Row，這樣一來，主從之間同步的就是真實(shí)的行數(shù)據(jù)了，而且 主鍵ID 在同步到從庫之前已經(jīng)確定了，就對同步語句的順序并不敏感，就規(guī)避了上面 Statement 的問題。
基于 MySQL 默認(rèn) Binlog 格式從 Statement 到 Row 的變更，InnoDB 也將其自增鎖的默認(rèn)實(shí)現(xiàn)從連續(xù)模式，更換到了效率更高的交叉模式。

魚和熊掌不可兼得
但是如果你的 MySQL 版本仍然默認(rèn)使用連續(xù)模式，但同時(shí)又想要提高性能，該怎么辦呢？這個(gè)其實(shí)得做一些取舍。
如果你可以斷定你的系統(tǒng)后續(xù)不會使用 Binlog，那么你可以選擇將自增鎖的鎖模式從連續(xù)模式改為交叉模式，這樣可以提高 MySQL 的并發(fā)。并且，沒有了主從同步，INSERT 語句在從庫亂序執(zhí)行導(dǎo)致的 AUTO_INCREMENT 值不匹配的問題也就自然不會遇到了。

如何解決自增鎖引起的插入性能的問題

• 自已寫一個(gè)分布式自增id的發(fā)號器，然后把主鍵上的 AUTO_INCREMENT 去掉；
• 避免 insert … select … ，這樣會導(dǎo)致Bulk inserts（插入的記錄行數(shù)不能馬上確定的），產(chǎn)生表鎖；
• 如果binlog-format是row模式，而且不關(guān)心一條bulk-insert的auto值連續(xù)（一般不用關(guān)心），那么設(shè)置innodb_autoinc_lock_mode = 2 可以提高更好的寫入性能

tips:
查看引擎狀態(tài)語句（包括鎖、事務(wù)相關(guān)）：
show engine innodb status\G
查看完整描述
set global innodb_status_output_locks=1;