前言
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)
MYSQL主要分為客戶端和服務(wù)端指么,其中客戶端負責(zé)對服務(wù)端進行連接酝惧,服務(wù)端主要包含兩個部分,其中存儲引擎層(Storage Engines)決定數(shù)據(jù)在磁盤上具體的存儲形式伯诬,典型的存儲引擎包括InnoDb和MyISAM晚唇,而目前MYSQL甚至支持混合存儲引擎,即可能一張表一半存儲在InnoDb上盗似,一半存儲在MyISAM哩陕。
除此以外的其它服務(wù)端組建則不關(guān)心數(shù)據(jù)用什么形式存儲,主要負責(zé)執(zhí)行具體的SQL語句赫舒,
- 鏈接池(Connections/Thread handling)組件負責(zé)管理客戶端和服務(wù)端建立的所有連接悍及,
- 解析器(Parser)負責(zé)解析并校驗SQL語句
- 查詢緩存(Query Cache)負責(zé)對執(zhí)行過的SQL語句結(jié)果進行緩存,當(dāng)發(fā)現(xiàn)有類似的查詢請求命中緩存時接癌,則會直接返回緩存中的查詢結(jié)果心赶。但是,因為緩存的維護存在一定的開銷缺猛,比如數(shù)據(jù)更新時需要同時去更新緩存缨叫,因此有些線上環(huán)境的DB會將這個功能關(guān)閉
- 優(yōu)化器(Optimizer)負責(zé)對解析后的SQL語句進行優(yōu)化,如緩存數(shù)據(jù)優(yōu)化荔燎,執(zhí)行計劃優(yōu)化耻姥。這個階段還會對用戶的權(quán)限進行校驗
- 元數(shù)據(jù)緩存(Table Metadata Cache)表單/DB等的元數(shù)據(jù)信息的緩存
這里簡單比較一下InnoDB和MyISAM這兩個存儲引擎。
InnoDB的特性如下:
- 支持事務(wù)及ACID
- 提供行鎖/表鎖
- MVCC能力
MyISAM的特性如下:
- 非事務(wù)型引擎
- 支持全文檢索(目前最新的InnoDB也支持)
- 只提供表鎖
本文主要基于InnoDB對鎖的特性進行介紹有咨。
SQL語句執(zhí)行順序
一個查詢請求在整個MYSQL服務(wù)端的鏈路如下:
- 在鏈接池處創(chuàng)建鏈接
- 前往查詢緩存(若開啟)判斷是否有相似的SQL的查詢結(jié)果可以直接命中
- 通過解析器對SQL語句進行解析和校驗琐簇,并為SQL生成sql_id
- 優(yōu)化器對SQL語句進行優(yōu)化,生成執(zhí)行計劃
- 前往存儲引擎執(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í)行順序如下:
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- 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ù)如下:
列名 | 含義 |
---|---|
id | SELECT語句的SQL_ID,它是指這個語句在查詢中的第n條語句挤牛,如果兩個id相同莹痢,則代表按照順序執(zhí)行從上到下執(zhí)行,id值越大墓赴,優(yōu)先級越高竞膳,越先被執(zhí)行 |
select_type | SELECT語句類型, 如SIMPLE是指不使用UNION或子查詢 |
table | 輸出行所屬的表格,derivex是指從第x步生成的衍生表 |
type | 訪問類型诫硕,說明表是如何關(guān)聯(lián)的 |
possible keys | 可選擇的索引 |
key | 真正選擇的索引坦辟。要想強制MySQL使用或忽視possible_keys列中的索引,在查詢中使用FORCE INDEX痘括、USE INDEX或者IGNORE INDEX长窄。 |
key_len | 選中索引的長度,顯示的是索引字段的最大可能長度纲菌,是根據(jù)表定義得來挠日,而非表內(nèi)檢索 |
ref | 哪些列或常量被用來查找索引列上的值 |
rows | 預(yù)估需要掃描的行數(shù) |
filtered | 預(yù)計多少比例的行數(shù)會被過濾出來 |
其中訪問類型(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分析語句時可以按照如下思路進行分析:
- 查看possible_keys和keys列,判斷是否充分利用了主鍵/唯一鍵/索引
- 查看key_len撮珠,判斷關(guān)鍵字長度是否過長
- 接著查看ref列沮脖,判斷是否能夠往const優(yōu)化
- 去除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個隔離級別:
- 讀未提交辑舷,會出現(xiàn)臟讀喻犁,不可重復(fù)度,幻讀何缓,
- 讀已提交肢础,會出現(xiàn)不可重復(fù)讀,幻讀
- 可重復(fù)度碌廓,InnoDB通過MVCC解決了幻讀問題传轰,MVCC全稱Multiple Version Concurrency Control,其核心為一個在t0時刻開啟的事務(wù)只能讀到t0時刻以及之前的提交的數(shù)據(jù)狀態(tài)
- 序列化
臟讀:一個事務(wù)中未提交的語句會被另一個事務(wù)察覺
不可重復(fù)讀:一個事務(wù)中提交的update語句會被另一個事務(wù)察覺
幻讀:一個事務(wù)中提交的insert語句會被另一個事務(wù)察覺
鎖
鎖主要分為表鎖和行鎖谷婆。顧名思義路召,表鎖就是指對整張表進行上鎖,而行鎖則是指針對一行數(shù)據(jù)進行上鎖波材。表鎖通常在服務(wù)器層面實現(xiàn),而行鎖往往在存儲引擎層實現(xiàn)身隐。行鎖并不是只對數(shù)據(jù)行上鎖廷区,還可以對索引/索引區(qū)間進行上鎖,即強調(diào)的是粒度更小的鎖贾铝。
鎖的類型
鎖可以分為以下四類:
- 共享鎖(S)可重復(fù)獲取共享鎖隙轻,但是不能獲取排他鎖(select ... lock in share mode)
- 排他鎖(X)不能獲取數(shù)據(jù)行的任何鎖 (select ... for update, update, delete)
- 意向鎖(IS/IX)表級別鎖埠帕,當(dāng)獲得該表/行的共享/排他鎖時,會對該表加上意向共享/排他鎖玖绿。這樣別的表級別鎖來試圖鎖表時敛瓷,可以直接通過意向鎖來判斷該表中是否存在共享/排他鎖,而無需對表中的每一行判斷是否有行級鎖斑匪,降低封鎖成本呐籽,提高并發(fā)性能
意向鎖和意向鎖之間是兼容的,而意向鎖和行鎖之間也是兼容蚀瘸。意向鎖主要是對表鎖的優(yōu)化狡蝶。假如現(xiàn)在有一個事務(wù)需要對表a加排他鎖,如果沒有意向鎖贮勃,就需要對全表進行掃描贪惹,直到找到第一個共享/排他鎖。而通過判斷是否有意向鎖寂嘉,可以極大的提高鎖互斥判斷的性能奏瞬。加意向鎖是在所有鎖(行鎖/表鎖)之前進行判斷和執(zhí)行的。
行鎖
行鎖具體有三種實現(xiàn):
- record lock 記錄鎖:鎖定索引記錄本身
- gap lock:在索引記錄的間隙加鎖泉孩,鎖定范圍硼端,不包括記錄本身
- 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條件中的字段不是主鍵/索引/唯一索引订咸,則會先對全部索引上排他鎖曼尊,在找到符合條件的記錄后,解鎖不滿足條件的鎖脏嚷。