在開發中有遇到很簡單的SQL卻執行的非常慢，甚至只查詢一行數據。

咔咔遇到的只有兩種情況，一種是MySQL伺服器CPU佔用率很高，所有的SQL都執行的很慢直到超時，程式也直接502，另一種情況是行鎖造成的鎖等待。

接下來咔咔帶領大家看看各種為難SQL執行的場景，本期文章帶大家再熟悉一下MySQL中的鎖

最新文章

死磕MySQL系列總目錄

什麼？還在用delete刪除數據《死磕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指的是資料庫的增刪改查。

「

堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的互聯網上能給你帶來一點幫助，我是咔咔，下期見。

」