MYSQL鎖學(xué)習(xí)筆記

前言

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ù)信息的緩存

這里簡單比較一下InnoDBMyISAM這兩個存儲引擎。
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ù)如下:

列名 含義
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分析語句時可以按照如下思路進行分析:

  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條件中的字段不是主鍵/索引/唯一索引订咸,則會先對全部索引上排他鎖曼尊,在找到符合條件的記錄后,解鎖不滿足條件的鎖脏嚷。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末骆撇,一起剝皮案震驚了整個濱河市,隨后出現(xiàn)的幾起案子父叙,更是在濱河造成了極大的恐慌神郊,老刑警劉巖,帶你破解...
    沈念sama閱讀 211,042評論 6 490
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件趾唱,死亡現(xiàn)場離奇詭異涌乳,居然都是意外死亡,警方通過查閱死者的電腦和手機甜癞,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,996評論 2 384
  • 文/潘曉璐 我一進店門夕晓,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人悠咱,你說我怎么就攤上這事蒸辆≌髁叮” “怎么了?”我有些...
    開封第一講書人閱讀 156,674評論 0 345
  • 文/不壞的土叔 我叫張陵躬贡,是天一觀的道長谆奥。 經(jīng)常有香客問我,道長拂玻,這世上最難降的妖魔是什么酸些? 我笑而不...
    開封第一講書人閱讀 56,340評論 1 283
  • 正文 為了忘掉前任,我火速辦了婚禮纺讲,結(jié)果婚禮上擂仍,老公的妹妹穿的比我還像新娘。我一直安慰自己熬甚,他們只是感情好逢渔,可當(dāng)我...
    茶點故事閱讀 65,404評論 5 384
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著乡括,像睡著了一般肃廓。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上诲泌,一...
    開封第一講書人閱讀 49,749評論 1 289
  • 那天盲赊,我揣著相機與錄音,去河邊找鬼敷扫。 笑死哀蘑,一個胖子當(dāng)著我的面吹牛,可吹牛的內(nèi)容都是我干的葵第。 我是一名探鬼主播绘迁,決...
    沈念sama閱讀 38,902評論 3 405
  • 文/蒼蘭香墨 我猛地睜開眼,長吁一口氣:“原來是場噩夢啊……” “哼卒密!你這毒婦竟也來了缀台?” 一聲冷哼從身側(cè)響起,我...
    開封第一講書人閱讀 37,662評論 0 266
  • 序言:老撾萬榮一對情侶失蹤哮奇,失蹤者是張志新(化名)和其女友劉穎膛腐,沒想到半個月后,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體鼎俘,經(jīng)...
    沈念sama閱讀 44,110評論 1 303
  • 正文 獨居荒郊野嶺守林人離奇死亡哲身,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,451評論 2 325
  • 正文 我和宋清朗相戀三年,在試婚紗的時候發(fā)現(xiàn)自己被綠了贸伐。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片律罢。...
    茶點故事閱讀 38,577評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出误辑,到底是詐尸還是另有隱情,我是刑警寧澤歌逢,帶...
    沈念sama閱讀 34,258評論 4 328
  • 正文 年R本政府宣布巾钉,位于F島的核電站,受9級特大地震影響秘案,放射性物質(zhì)發(fā)生泄漏砰苍。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 39,848評論 3 312
  • 文/蒙蒙 一阱高、第九天 我趴在偏房一處隱蔽的房頂上張望赚导。 院中可真熱鬧,春花似錦赤惊、人聲如沸吼旧。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,726評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽圈暗。三九已至,卻和暖如春裕膀,著一層夾襖步出監(jiān)牢的瞬間员串,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,952評論 1 264
  • 我被黑心中介騙來泰國打工昼扛, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留寸齐,地道東北人。 一個月前我還...
    沈念sama閱讀 46,271評論 2 360
  • 正文 我出身青樓抄谐,卻偏偏與公主長得像渺鹦,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子斯稳,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 43,452評論 2 348

推薦閱讀更多精彩內(nèi)容