在開發中有遇到很簡單的SQL卻執行的非常慢，甚至只查詢一行資料。
咔咔遇到的只有兩種情況，一種是MySQL伺服器CPU佔用率很高，所有的SQL都執行的很慢直到超時，程式也直接502，另一種情況是行鎖造成的鎖等待。
接下來咔咔帶領大家看看各種為難SQL執行的場景，本期文章帶大家再熟悉一下MySQL中的鎖
最新文章
MySQL統計總數就用count(*)，別花裡胡哨的《死磕MySQL系列 十》
為什麼MySQL字串不加引號索引失效？《死磕MySQL系列 十一》
開啟order by的大門，一探究竟《死磕MySQL系列 十二》
一、MDL鎖
現在你應該知道要聊的是MDL，這個鎖很少有開發人員去關注，在開發中並沒有實際的語法來開啟或關閉鎖。
這個特性是在MySQL5.5引入的，目的是為了解決一張表同時在做查詢和修改表結構，這種情況必定會造成查詢結果跟表結構無法對應。
所以，當你訪問一個表時會預設加上MDL鎖，MDL鎖的互斥關係跟共享鎖、排它鎖是一樣的，讀寫互斥，寫寫互斥。
MDl鎖是在事務提交後才會釋放，執行期間一直持有。
同時你需要知道MDL鎖的操作會形成一個佇列，佇列中寫鎖獲取優先順序高於讀鎖，一旦出現MDL寫鎖等待，會阻塞後續該表的所有CURL操作。
也就說，一旦你在一個未提交事務之後執行了DDL操作，那麼等到的結果就是MySQL掛掉，客戶端會有重試機制，DDL後所有CURD會在超時後重新發起請求，這個庫的執行緒會很快爆滿。
當執行緒A通過DDL時手裡握著表的MDL寫鎖，而執行緒B的查詢需要獲取MDL讀鎖，所以執行緒B就一直處於鎖等待狀態。
在生產環境是堅決不可以直接修改表結構的，如果你的表非常大的話會很容易造成業務所有的CURD處於堵塞。
解決方案
大表DDL可以使用pt-online-schema-change這個工具來處理，具體怎麼用後續文章會跟大家分享出來。
若不小心線上上執行了修改表結構，可以通過show processlist命令來查詢，不過這個命令在查詢上很不方便，可以使用performance_schema和sys系統庫來進行查詢。前提是你的MySQL引數performance_schema=on，在MySQL8.0.26版本中，這個引數是預設開啟的，若你所在的版本沒有開啟時可以開啟。
然後就可以執行select blocking_pid from sys.schema_table_lock_waits，就可以看到當前持有MDL鎖的執行緒ID，直接使用kill命令即可。
二、全域性鎖
在MySQL強人“鎖”難《死磕MySQL系列 三》的文章中給大家聊到了全域性鎖，使用語法flush table t with read lock 或者 flush table with read lock
指定表名時就鎖定指定表，未指定時表示鎖定所有表。
這兩個語句執行是非常快的，一般不會造成SQL堵塞，但防火、防盜你也防不住有其它執行緒的語句把flush語句堵塞住。
執行緒A執行大事物，需要執行10s
執行緒B執行flush table t with read lock
執行緒C執行select * from evt_sms where id = 1
所以執行緒C哪怕是隻查詢一條資料在10s內也是返回不了結果的，執行緒B的flush 命令需要等執行緒A的事務執行完畢，而執行緒C此時卻被未執行的執行緒B堵塞著。
解決方案
一般出現這種情況只需要執行show processlist就可以看到堵塞執行緒C的執行緒是那個，同樣直接使用kill掉對應的執行緒即可。
三、行鎖
這個場景是非常好模擬的，接下來讓我們一起看看
執行緒A正常修改大批量資料執行語句為update evt_sms set code = 123 where id > 11089
執行緒B執行select * from evt_sms where id = 120365 lock in share mode
在文章開頭就跟大家簡單的說了一句，MySQL中讀鎖與寫鎖、寫鎖與寫鎖互斥，所以執行緒B會一直等待執行緒A的事務提交之後才能返回結果。
解決方案
分析一下，執行緒B執行的語句新增的是讀鎖，能被堵住的只有是寫鎖，所以可以直接在sys.innodb_lock_waits表中查到佔著這個寫鎖的是誰。
執行語句select * from evt_sms sys.innodb_lock_waits where lock_table='kaka.evt_sms'\G
這個試驗就不演示了，復現過程也十分簡答可以自己看一下哈！輸出結果的最後一行就是解決方案，帶著你的答案來到評論區
四、快照讀引發的問題
瞭解過MVCC實現原理的大概率都會看到過當前讀、快照讀這兩個詞，如果你還不知道它們是什麼就好好記一下。
當前讀
執行select語句時加上共享鎖、排它鎖的操作就是當前讀。
例：select * from evt_sms where id = 1 lock in share mode
這裡的共享鎖、排它鎖也就是常說的讀鎖、寫鎖
在MySQL的Innodb儲存引擎中進行DML操作時會預設新增排它鎖
上邊這個例子，select語句一旦加上了共享鎖其它執行緒是不能修改當前記錄的，因此當前讀讀取的資料庫就是最新的資料
快照讀
快照讀的前提是隔離級別不是序列級別，序列級別的快照讀會退化為當前讀，快照讀的出現是為了提高事務併發性，其實現也是基於MVCC的
MVCC在某種情況下可以認為是行鎖的一個變種，但要知道的是在很多情況是不會有加鎖行為的
這時你應該記住快照讀獲取的資料不是最新的，有可能是之前版本的資料
實現MVCC的三大因素隱式欄位、undo log、read-view，read-view就是通過快照讀產生的，它是由查詢的那一時間所有未提交事務ID組成的陣列，和已經建立的最大事務ID組成的。然後通過本執行緒的事務ID在read-view中進行對比
為什麼說快照讀會引發查詢遲遲不返回結果
上文給大家提了一個東西undo log，都知道undo log是回滾日誌，查詢慢的原因也在這裡
執行緒A先開啟一個事務
執行緒B開啟對id為1的資料行進行更新
由於id = 1的資料很多所以會產生很多的版本鏈，這裡就認為是5萬個
執行緒A執行了select * from evt_sms where id = 1就會遲遲返回不了結果
此時執行緒B並沒有提交事務，所以執行緒A的查詢需要根據版本鏈一直回退到5W個undo log之前，也就是這裡導致查詢非常慢
下圖是一個咔咔之前做的undo log版本鏈圖
執行緒A的查詢是快照讀，執行查詢時會產生read-view，read-view會把執行緒A、執行緒B的事務存放在一個陣列中，然後用一定的規則進行判斷執行緒A能看到的資料是什麼。
比對規則是什麼
trx_id為當前的事務ID，min_id、max_id為當前啟動事務的最大事務ID和最小事務ID
如果落在trx_id<min_id，表示此版本是已經提交的事務生成的，由於事務已經提交所以資料是可見的
如果落在trx_id>max_id，表示此版本是由將來啟動的事務生成的，是肯定不可見的
若在min_id<=trx_id<=max_id時
如果row的trx_id在陣列中，表示此版本是由還沒提交的事務生成的，不可見，但是當前自己的事務是可見的 如果row的trx_id不在陣列中，表明是提交的事務生成了該版本，可見 在這裡還有一個特殊情況那就是對於已經刪除的資料，在之前的undo log日誌講述時說了update和delete是同一種型別的undo log，同樣也可以認為delete就是update的特殊情況。
當刪除一條資料時會將版本鏈上最新的資料複製一份，然後將trx_id修改為刪除時的trx_id，同時在該記錄的頭資訊中存在一個delete flag標記，將這個標記寫上true，用來表示當前記錄已經刪除。
在查詢時按照版本鏈的規則查詢到對應的記錄，如果delete flag標記位為true，意味著資料已經被刪除，則不返回資料。
五、總結
本期文章通過MDL鎖、全域性鎖、行鎖、undo log說明查詢一條資料頁遲遲不返回的問題，可以看到大多數都是一些理論知識，有些東西看著看著也就理解其中的含義了。
這裡需要注意的是不要把MDL和DML搞混淆了，這可是兩個東西，MDL指的是鎖、而DML指的是資料庫的增刪改查。
“堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助，我是咔咔，下期見。
”