在開發(fā)中有遇到很簡單的SQL卻執(zhí)行的非常慢牌捷，甚至只查詢一行數(shù)據(jù)。
咔咔遇到的只有兩種情況涡驮，一種是MySQL服務(wù)器CPU占用率很高暗甥，所有的SQL都執(zhí)行的很慢直到超時，程序也直接502捉捅，另一種情況是行鎖造成的鎖等待撤防。
接下來咔咔帶領(lǐng)大家看看各種為難SQL執(zhí)行的場景，本期文章帶大家再熟悉一下MySQL中的鎖
最新文章
什么棒口？還在用delete刪除數(shù)據(jù)《死磕MySQL系列 九》
MySQL統(tǒng)計總數(shù)就用count(*)寄月，別花里胡哨的《死磕MySQL系列 十》
為什么MySQL字符串不加引號索引失效？《死磕MySQL系列 十一》
打開order by的大門无牵，一探究竟《死磕MySQL系列 十二》
一漾肮、MDL鎖
現(xiàn)在你應(yīng)該知道要聊的是MDL，這個鎖很少有開發(fā)人員去關(guān)注茎毁，在開發(fā)中并沒有實際的語法來開啟或關(guān)閉鎖克懊。
這個特性是在MySQL5.5引入的乾翔，目的是為了解決一張表同時在做查詢和修改表結(jié)構(gòu)皂甘，這種情況必定會造成查詢結(jié)果跟表結(jié)構(gòu)無法對應(yīng)戈毒。
所以憎茂，當(dāng)你訪問一個表時會默認(rèn)加上MDL鎖度迂，MDL鎖的互斥關(guān)系跟共享鎖肝劲、排它鎖是一樣的突雪，讀寫互斥闪唆，寫寫互斥碧库。
MDl鎖是在事務(wù)提交后才會釋放柜与，執(zhí)行期間一直持有。
同時你需要知道MDL鎖的操作會形成一個隊列谈为，隊列中寫鎖獲取優(yōu)先級高于讀鎖旅挤，一旦出現(xiàn)MDL寫鎖等待，會阻塞后續(xù)該表的所有CURL操作伞鲫。
也就說粘茄，一旦你在一個未提交事務(wù)之后執(zhí)行了DDL操作，那么等到的結(jié)果就是MySQL掛掉，客戶端會有重試機(jī)制柒瓣，DDL后所有CURD會在超時后重新發(fā)起請求儒搭，這個庫的線程會很快爆滿。
當(dāng)線程A通過DDL時手里握著表的MDL寫鎖芙贫，而線程B的查詢需要獲取MDL讀鎖搂鲫，所以線程B就一直處于鎖等待狀態(tài)。
在生產(chǎn)環(huán)境是堅決不可以直接修改表結(jié)構(gòu)的磺平，如果你的表非常大的話會很容易造成業(yè)務(wù)所有的CURD處于堵塞魂仍。
解決方案
大表DDL可以使用pt-online-schema-change這個工具來處理，具體怎么用后續(xù)文章會跟大家分享出來拣挪。
若不小心在線上執(zhí)行了修改表結(jié)構(gòu)擦酌，可以通過show processlist命令來查找，不過這個命令在查找上很不方便菠劝，可以使用performance_schema和sys系統(tǒng)庫來進(jìn)行查詢赊舶。前提是你的MySQL參數(shù)performance_schema=on，在MySQL8.0.26版本中赶诊，這個參數(shù)是默認(rèn)開啟的笼平，若你所在的版本沒有開啟時可以打開。
然后就可以執(zhí)行select blocking_pid from sys.schema_table_lock_waits舔痪，就可以看到當(dāng)前持有MDL鎖的線程ID寓调，直接使用kill命令即可。
二辙喂、全局鎖
在MySQL強(qiáng)人“鎖”難《死磕MySQL系列 三》的文章中給大家聊到了全局鎖捶牢，使用語法flush table t with read lock 或者 flush table with read lock
指定表名時就鎖定指定表，未指定時表示鎖定所有表巍耗。
這兩個語句執(zhí)行是非城雉铮快的，一般不會造成SQL堵塞炬太，但防火灸蟆、防盜你也防不住有其它線程的語句把flush語句堵塞住。
線程A執(zhí)行大事物亲族，需要執(zhí)行10s
線程B執(zhí)行flush table t with read lock
線程C執(zhí)行select * from evt_sms where id = 1
所以線程C哪怕是只查詢一條數(shù)據(jù)在10s內(nèi)也是返回不了結(jié)果的炒考，線程B的flush 命令需要等線程A的事務(wù)執(zhí)行完畢，而線程C此時卻被未執(zhí)行的線程B堵塞著霎迫。
解決方案
一般出現(xiàn)這種情況只需要執(zhí)行show processlist就可以看到堵塞線程C的線程是那個斋枢，同樣直接使用kill掉對應(yīng)的線程即可。
三知给、行鎖
這個場景是非常好模擬的瓤帚，接下來讓我們一起看看
線程A正常修改大批量數(shù)據(jù)執(zhí)行語句為update evt_sms set code = 123 where id > 11089
線程B執(zhí)行select * from evt_sms where id = 120365 lock in share mode
在文章開頭就跟大家簡單的說了一句描姚，MySQL中讀鎖與寫鎖、寫鎖與寫鎖互斥戈次，所以線程B會一直等待線程A的事務(wù)提交之后才能返回結(jié)果轩勘。
解決方案
分析一下，線程B執(zhí)行的語句添加的是讀鎖怯邪，能被堵住的只有是寫鎖绊寻，所以可以直接在sys.innodb_lock_waits表中查到占著這個寫鎖的是誰。
執(zhí)行語句select * from evt_sms sys.innodb_lock_waits where lock_table='kaka.evt_sms'\G
這個試驗就不演示了悬秉，復(fù)現(xiàn)過程也十分簡答可以自己看一下哈澄步！輸出結(jié)果的最后一行就是解決方案，帶著你的答案來到評論區(qū)
四和泌、快照讀引發(fā)的問題
了解過MVCC實現(xiàn)原理的大概率都會看到過當(dāng)前讀驮俗、快照讀這兩個詞，如果你還不知道它們是什么就好好記一下允跑。
當(dāng)前讀
執(zhí)行select語句時加上共享鎖、排它鎖的操作就是當(dāng)前讀搪柑。
例：select * from evt_sms where id = 1 lock in share mode
這里的共享鎖聋丝、排它鎖也就是常說的讀鎖、寫鎖
在MySQL的Innodb存儲引擎中進(jìn)行DML操作時會默認(rèn)添加排它鎖
上邊這個例子工碾，select語句一旦加上了共享鎖其它線程是不能修改當(dāng)前記錄的弱睦，因此當(dāng)前讀讀取的數(shù)據(jù)庫就是最新的數(shù)據(jù)
快照讀
快照讀的前提是隔離級別不是串行級別，串行級別的快照讀會退化為當(dāng)前讀渊额，快照讀的出現(xiàn)是為了提高事務(wù)并發(fā)性况木，其實現(xiàn)也是基于MVCC的
MVCC在某種情況下可以認(rèn)為是行鎖的一個變種，但要知道的是在很多情況是不會有加鎖行為的
這時你應(yīng)該記住快照讀獲取的數(shù)據(jù)不是最新的旬迹，有可能是之前版本的數(shù)據(jù)
實現(xiàn)MVCC的三大因素隱式字段火惊、undo log、read-view奔垦，read-view就是通過快照讀產(chǎn)生的屹耐，它是由查詢的那一時間所有未提交事務(wù)ID組成的數(shù)組，和已經(jīng)創(chuàng)建的最大事務(wù)ID組成的椿猎。然后通過本線程的事務(wù)ID在read-view中進(jìn)行對比
為什么說快照讀會引發(fā)查詢遲遲不返回結(jié)果
上文給大家提了一個東西undo log惶岭，都知道undo log是回滾日志，查詢慢的原因也在這里
線程A先開啟一個事務(wù)
線程B開啟對id為1的數(shù)據(jù)行進(jìn)行更新
由于id = 1的數(shù)據(jù)很多所以會產(chǎn)生很多的版本鏈犯眠，這里就認(rèn)為是5萬個
線程A執(zhí)行了select * from evt_sms where id = 1就會遲遲返回不了結(jié)果
此時線程B并沒有提交事務(wù)按灶，所以線程A的查詢需要根據(jù)版本鏈一直回退到5W個undo log之前，也就是這里導(dǎo)致查詢非常慢
下圖是一個咔咔之前做的undo log版本鏈圖
線程A的查詢是快照讀筐咧，執(zhí)行查詢時會產(chǎn)生read-view鸯旁，read-view會把線程A、線程B的事務(wù)存放在一個數(shù)組中，然后用一定的規(guī)則進(jìn)行判斷線程A能看到的數(shù)據(jù)是什么羡亩。
比對規(guī)則是什么
trx_id為當(dāng)前的事務(wù)ID摩疑，min_id、max_id為當(dāng)前啟動事務(wù)的最大事務(wù)ID和最小事務(wù)ID
如果落在trx_id<min_id畏铆，表示此版本是已經(jīng)提交的事務(wù)生成的雷袋，由于事務(wù)已經(jīng)提交所以數(shù)據(jù)是可見的
如果落在trx_id>max_id，表示此版本是由將來啟動的事務(wù)生成的辞居，是肯定不可見的
若在min_id<=trx_id<=max_id時
如果row的trx_id在數(shù)組中楷怒，表示此版本是由還沒提交的事務(wù)生成的，不可見瓦灶，但是當(dāng)前自己的事務(wù)是可見的 如果row的trx_id不在數(shù)組中鸠删，表明是提交的事務(wù)生成了該版本，可見 在這里還有一個特殊情況那就是對于已經(jīng)刪除的數(shù)據(jù)贼陶，在之前的undo log日志講述時說了update和delete是同一種類型的undo log刃泡，同樣也可以認(rèn)為delete就是update的特殊情況。
當(dāng)刪除一條數(shù)據(jù)時會將版本鏈上最新的數(shù)據(jù)復(fù)制一份碉怔，然后將trx_id修改為刪除時的trx_id烘贴，同時在該記錄的頭信息中存在一個delete flag標(biāo)記，將這個標(biāo)記寫上true撮胧，用來表示當(dāng)前記錄已經(jīng)刪除桨踪。
在查詢時按照版本鏈的規(guī)則查詢到對應(yīng)的記錄，如果delete flag標(biāo)記位為true芹啥，意味著數(shù)據(jù)已經(jīng)被刪除锻离，則不返回數(shù)據(jù)。
五墓怀、總結(jié)
本期文章通過MDL鎖汽纠、全局鎖、行鎖捺疼、undo log說明查詢一條數(shù)據(jù)頁遲遲不返回的問題疏虫，可以看到大多數(shù)都是一些理論知識，有些東西看著看著也就理解其中的含義了啤呼。
這里需要注意的是不要把MDL和DML搞混淆了卧秘，這可是兩個東西，MDL指的是鎖官扣、而DML指的是數(shù)據(jù)庫的增刪改查翅敌。
“
堅持學(xué)習(xí)、堅持寫作惕蹄、堅持分享是咔咔從業(yè)以來所秉持的信念蚯涮。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來一點幫助治专，我是咔咔，下期見遭顶。
”
