前言

MYSQL是在大小公司中使用率極高的開源的關(guān)系型數(shù)據(jù)庫，以其良好的易用性和在分布式場景下的高性能而著稱，也是所有新手在數(shù)據(jù)庫入門時的產(chǎn)品首選。最近因為聽了公司的一位師兄關(guān)于MYSQL InnoDB鎖的講座，收獲很多防症，所以將MYSQL鎖相關(guān)的必備知識在此進行梳理。這些知識不僅可以幫助面試哎甲，也可以在日常開發(fā)進行性能優(yōu)化或死鎖問題排查時派上用場蔫敲。當(dāng)然，最重要的是烧给，在對數(shù)據(jù)進行上鎖時燕偶，就能夠梳理出相應(yīng)的上鎖流程，從而避免真正走到故障時再去排查础嫡。

本文主要包括

• MYSQL基礎(chǔ)架構(gòu)
• 語句執(zhí)行順序
• ACID原則
• 事務(wù)分類
• 事務(wù)隔離級別
• 行鎖/表鎖/意向鎖

MYSQL基礎(chǔ)架構(gòu)

image.png

MYSQL主要分為客戶端和服務(wù)端指么，其中客戶端負責(zé)對服務(wù)端進行連接酝惧，服務(wù)端主要包含兩個部分，其中存儲引擎層（Storage Engines）決定數(shù)據(jù)在磁盤上具體的存儲形式伯诬，典型的存儲引擎包括InnoDb和MyISAM晚唇，而目前MYSQL甚至支持混合存儲引擎，即可能一張表一半存儲在InnoDb上盗似，一半存儲在MyISAM哩陕。

除此以外的其它服務(wù)端組建則不關(guān)心數(shù)據(jù)用什么形式存儲，主要負責(zé)執(zhí)行具體的SQL語句赫舒，

1. 鏈接池（Connections/Thread handling）組件負責(zé)管理客戶端和服務(wù)端建立的所有連接悍及，
2. 解析器（Parser）負責(zé)解析并校驗SQL語句
3. 查詢緩存（Query Cache）負責(zé)對執(zhí)行過的SQL語句結(jié)果進行緩存，當(dāng)發(fā)現(xiàn)有類似的查詢請求命中緩存時接癌，則會直接返回緩存中的查詢結(jié)果心赶。但是，因為緩存的維護存在一定的開銷缺猛，比如數(shù)據(jù)更新時需要同時去更新緩存缨叫，因此有些線上環(huán)境的DB會將這個功能關(guān)閉
4. 優(yōu)化器（Optimizer）負責(zé)對解析后的SQL語句進行優(yōu)化，如緩存數(shù)據(jù)優(yōu)化荔燎，執(zhí)行計劃優(yōu)化耻姥。這個階段還會對用戶的權(quán)限進行校驗
5. 元數(shù)據(jù)緩存（Table Metadata Cache）表單/DB等的元數(shù)據(jù)信息的緩存

這里簡單比較一下InnoDB和MyISAM這兩個存儲引擎。
InnoDB的特性如下：

1. 支持事務(wù)及ACID
2. 提供行鎖/表鎖
3. MVCC能力

MyISAM的特性如下：

1. 非事務(wù)型引擎
2. 支持全文檢索（目前最新的InnoDB也支持）
3. 只提供表鎖

本文主要基于InnoDB對鎖的特性進行介紹有咨。

SQL語句執(zhí)行順序

一個查詢請求在整個MYSQL服務(wù)端的鏈路如下：

1. 在鏈接池處創(chuàng)建鏈接
2. 前往查詢緩存（若開啟）判斷是否有相似的SQL的查詢結(jié)果可以直接命中
3. 通過解析器對SQL語句進行解析和校驗琐簇，并為SQL生成sql_id
4. 優(yōu)化器對SQL語句進行優(yōu)化，生成執(zhí)行計劃
5. 前往存儲引擎執(zhí)行并獲取數(shù)據(jù)

那么SQL語句在經(jīng)過解析器和優(yōu)化器時是什么樣的一個鏈路呢座享？
一個標(biāo)準(zhǔn)的Select SQL語句包含以下幾個部分：

select t1.column1 as column1, t2,column2 as column2... 
from TABLE t1, TABLE t2 ... 
WHERE condition1 
GROUP BY condition2 
HAVING condition3 
ORDER BY column1
LIMIT N

而這條語句的標(biāo)準(zhǔn)邏輯執(zhí)行順序如下：

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. DISTINCT
9. ORDER BY

這里有一點需要注意鸽嫂，select語句是在group by和having之后執(zhí)行，因此select中as出來的列名在group by和having中是不可以引用的征讲，但是order by中是可以引用的。

但是真正的的執(zhí)行順序和標(biāo)準(zhǔn)邏輯執(zhí)行順序并不一定相同橡娄，因為優(yōu)化器會對SQL的執(zhí)行順序進行變更诗箍，從而盡可能提高SQL的執(zhí)行效率。比如：

select * from table1 t1 join table2 t2 on t1.id = t2.id where t1.count > 10 and t2.count > 100

標(biāo)準(zhǔn)的執(zhí)行順序會先將表格t1和t2進行join操作挽唉，再對join后的結(jié)果針對where語句進行篩選滤祖。而優(yōu)化器可能會變化一下執(zhí)行順序，先根據(jù)where t1.count > 10 and t2.count > 100篩選出t1表和t2表中符合條件的數(shù)據(jù)瓶籽，再執(zhí)行join匠童。

那么有沒有辦法看到SQL在真實執(zhí)行的時候的執(zhí)行計劃呢？這就需要Explain語法塑顺。

Explain

Explain關(guān)鍵字的使用方法很簡單汤求，只要將其加在具體的SELECT語句之前就可以俏险，Explain也只能解析SELECT語句。通過Explain關(guān)鍵字可以觀察表的索引是否合理扬绪，語句的真實執(zhí)行順序是否符合預(yù)期竖独。Explain執(zhí)行后生成的數(shù)據(jù)如下：

其中訪問類型（type）按照從好到壞包括

• system：只有一行
• const：表格中最多只有一行匹配的數(shù)據(jù)，如使用主鍵進行查詢 如select * from user_info where id = 2
• eq_ref: 使用唯一索引翰舌，對于每個索引鍵值只有一條記錄匹配嚣潜，如使用primary key或者unique key作為多表鏈接的關(guān)聯(lián)條件，即前表的每一個結(jié)果椅贱，在后表都只能找到一條匹配的記錄懂算，只支持等號查詢。 SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id
• ref: 針對非唯一或非主鍵索引庇麦，或是使用了最左前綴規(guī)則索引的查詢计技，支持非等號查詢。如 SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5
• fulltext: 全文檢索
• ref_or_null: 除了利用索引之外山橄，MYSQL執(zhí)行了額外的查詢來處理NULL值
• index_merge: 對索引進行多段索引掃描垮媒，并且將結(jié)果進行合并
• unique_subquery: 適用于IN語句，且IN中查詢出得數(shù)據(jù)唯一 如value IN (SELECT primary_key FROM single_table WHERE some_expr)
• index_subquery: 同上航棱，只是IN中查詢數(shù)據(jù)不唯一
• range：獲取特定范圍內(nèi)的數(shù)據(jù)睡雇，使用索引來決定哪些是這個范圍內(nèi)的數(shù)據(jù)。所有的等值饮醇，非等值處理它抱，判空等都可以使用range類型
• index：類似于ALL，只不過全掃描的是索引樹朴艰。如果只需要掃描索引樹观蓄，無需訪問具體表混移，則會在Extra列展示Using index。如果查詢中使用的索引是某個大索引的其中一部分時蜘腌，也會使用這種檢索類型
• ALL：全表掃描

在知道這些之后沫屡，使用Explain分析語句時可以按照如下思路進行分析：

1. 查看possible_keys和keys列，判斷是否充分利用了主鍵/唯一鍵/索引
2. 查看key_len撮珠，判斷關(guān)鍵字長度是否過長
3. 接著查看ref列沮脖，判斷是否能夠往const優(yōu)化
4. 去除type=ALL的全表掃描連接

這里建議看一下參考文章中的Explain實戰(zhàn)例子文章來加深通過Explain進行優(yōu)化的思路

事務(wù)特性ACID

ATOMICY原子性：事務(wù)要么全部執(zhí)行，要么全部不執(zhí)行
CONSISTENCY一致性：事務(wù)執(zhí)行前和執(zhí)行后數(shù)據(jù)狀態(tài)應(yīng)當(dāng)一致
ISOLATION隔離性：事務(wù)之間不會相互影響
DURABILITY持久性：事務(wù)執(zhí)行完成后結(jié)果不會丟失芯急，因此需要能夠?qū)?shù)據(jù)進行恢復(fù)

事務(wù)分類

隱式事務(wù)：在autocommit為true的情況下勺届，默認每一條語句都會開啟一個事務(wù)執(zhí)行，執(zhí)行完畢后提交事務(wù)娶耍。因此不在事務(wù)上下文中執(zhí)行select * from user where id = 1 for update語句在語句執(zhí)行完后就會釋放排他鎖免姿，這在大多數(shù)情況下都是不合理的。
顯式事務(wù)：每個事務(wù)以start transaction開啟榕酒，以commit或rollback結(jié)束胚膊。Spring中使用@Transactional或是transactionTemplate包圍的代碼段

如果你也在學(xué)習(xí)，在入門學(xué)習(xí)的過程當(dāng)中有遇見學(xué)習(xí)想鹰，行業(yè)方面的問題紊婉，或者說缺乏系統(tǒng)的學(xué)習(xí)路線和系統(tǒng)學(xué)習(xí)視頻，你都可以自行去下載：https://shimo.im/docs/VqQR6tPrpR3C3tjq/

事務(wù)隔離級別

事務(wù)總共有4個隔離級別：

1. 讀未提交辑舷，會出現(xiàn)臟讀喻犁，不可重復(fù)度，幻讀何缓，
2. 讀已提交肢础，會出現(xiàn)不可重復(fù)讀，幻讀
3. 可重復(fù)度碌廓，InnoDB通過MVCC解決了幻讀問題传轰，MVCC全稱Multiple Version Concurrency Control，其核心為一個在t0時刻開啟的事務(wù)只能讀到t0時刻以及之前的提交的數(shù)據(jù)狀態(tài)
4. 序列化

臟讀：一個事務(wù)中未提交的語句會被另一個事務(wù)察覺
不可重復(fù)讀：一個事務(wù)中提交的update語句會被另一個事務(wù)察覺
幻讀：一個事務(wù)中提交的insert語句會被另一個事務(wù)察覺

鎖

鎖主要分為表鎖和行鎖谷婆。顧名思義路召，表鎖就是指對整張表進行上鎖，而行鎖則是指針對一行數(shù)據(jù)進行上鎖波材。表鎖通常在服務(wù)器層面實現(xiàn)，而行鎖往往在存儲引擎層實現(xiàn)身隐。行鎖并不是只對數(shù)據(jù)行上鎖廷区，還可以對索引/索引區(qū)間進行上鎖，即強調(diào)的是粒度更小的鎖贾铝。

鎖的類型

鎖可以分為以下四類：

1. 共享鎖（S）可重復(fù)獲取共享鎖隙轻，但是不能獲取排他鎖（select ... lock in share mode)
2. 排他鎖（X）不能獲取數(shù)據(jù)行的任何鎖 （select ... for update, update, delete)
3. 意向鎖（IS/IX）表級別鎖埠帕，當(dāng)獲得該表/行的共享/排他鎖時，會對該表加上意向共享/排他鎖玖绿。這樣別的表級別鎖來試圖鎖表時敛瓷，可以直接通過意向鎖來判斷該表中是否存在共享/排他鎖，而無需對表中的每一行判斷是否有行級鎖斑匪，降低封鎖成本呐籽，提高并發(fā)性能

意向鎖和意向鎖之間是兼容的，而意向鎖和行鎖之間也是兼容蚀瘸。意向鎖主要是對表鎖的優(yōu)化狡蝶。假如現(xiàn)在有一個事務(wù)需要對表a加排他鎖，如果沒有意向鎖贮勃，就需要對全表進行掃描贪惹，直到找到第一個共享/排他鎖。而通過判斷是否有意向鎖寂嘉，可以極大的提高鎖互斥判斷的性能奏瞬。加意向鎖是在所有鎖（行鎖/表鎖）之前進行判斷和執(zhí)行的。

行鎖

行鎖具體有三種實現(xiàn)：

1. record lock 記錄鎖：鎖定索引記錄本身
2. gap lock：在索引記錄的間隙加鎖泉孩，鎖定范圍硼端，不包括記錄本身
3. next key：record lock + gap lock

只在可重復(fù)度REPEATABLE READ或以上的隔離級別下的特定操作才會取得gap lock或nextkey lock。
讀已提交REPEATABLE COMMIT級別下只有record lock
MYSQL默認為RR

因此當(dāng)判斷語句如何加行鎖時棵譬，需要根據(jù)事務(wù)隔離級別+是否使用主鍵/唯一鍵/索引進行判斷显蝌。

加鎖順序本質(zhì)上和索引的查詢順序是一致的
這里有一種最糟糕的情況，即如果where條件中的字段不是主鍵/索引/唯一索引订咸，則會先對全部索引上排他鎖曼尊，在找到符合條件的記錄后，解鎖不滿足條件的鎖脏嚷。