聊聊什麼是慢查、如何監控？如何排查？

2020 年 11 月 11 日
筆記
MySQL

Hi，大家好！我是白日夢。

今天我要跟你分享的話題是：「聊聊什麼是慢查、如何監控？如何排查？」

使用推薦閱讀，有更好的閱讀體驗

二、什麼是慢查？

慢查，顧名思義就是很慢的查詢。SQL的執行總是有一個執行時間的，通過long_query_time參數告訴MySQL，當SQL的執行時間超過該參數的指定值後就將這條SQL記錄在慢查日誌中。

默認的long_query_time默認值為10s

三、怎麼調整界定慢查的時間？

設置全局慢查時間為0.2秒。

注意：long_query_time屬於dynamic類型的參數。意思是像上面這樣在會話A中通過命令行的方式設置全局 long_query_time為0.2秒後，再打開一個新的會話B查看該變數會發現 long_query_time=0.2

但是在會話A中查看session級別的long_query_time依然為默認的10s

四、慢查日誌像binlog那樣是真實的日誌文件嗎？

在MySQL5.1之前確實慢查日誌確實是以文件的形式存在。但是MySQL5.1之後MySQL允許我們可以將慢查日誌放入一個數據表中，便於我們查看分析。

現在公司使用的一般都是5.6～5.7版本。當然即使5.1版本之後支援了將數據放入Table中，默認配置依然是File。

當然你也可以通過下面的命令將慢查輸出類型改成Table

五、慢查有什麼危害？

研發同學都知道：對於Linux作業系統來說，一個進程能打開的Socket文件句柄是上限的。即使我們可以動態的調整它的大小，但是也做不到無限大。

通過命令: 查看進程被限制的使用各種資源的量

Copyulimit -a 

core file size: 進程崩潰是轉儲文件大小限制
man loaded memort 最大鎖定記憶體大小
open file 能打開的文件句柄數

大量的慢查佔據MySQL連接（Linux作業系統會為每一條連接創建socket文件），慢查累積到一定程度還會導致正常的SQL得不到連接執行從而變成慢查SQL，最終有可能導致MySQL的連接全部被耗光而夯死。這就是生產級別的事故了。

六、如何監控慢查？

查看曾經執行完成的慢查

如果你需要編寫一個監控程式探測MySQL的慢查詢。那完全可以探測分析MySQL的slow.log

如果你還不知道slow.log在哪裡，可以像下面這樣定位到它。slow.log中記錄的就是曾經執行過的慢查資訊。

這是你可以嘗試使用select sleep(2);模擬一條慢查SQL

然後去慢查sql中查看具體的慢查詳情。

查看正在進行的慢查SQL

我在A Session中發起SQL，select sleep(60);

然後在B Session中通過下圖的方式可以看到當前正在進行的慢查情況。大家在看的時候注意：Command的類型為Query

MySQL也為用戶提供了一些原生的慢查工具。比如：查看執行時間最長的10條SQL

mysqldumpslow -s a1 -n 10 mysql.slow_log

了解即可

七、線上出慢查了如何處理？

首先你得知道通常情況下每個公司都有自己監控系統，或者是監控腳本，具體的監控邏輯就是上節講述的思路。故一旦出現報警，DBA同學會在第一時間接到消息。

DBA同學一般都會去聯繫業務同學，由業務同學去處理這個慢查。（相信已經工作的同學深有這個體會）

有可能爆出慢查的這套資料庫集群是由多個業務同時使用。所以如果慢查影響很嚴重，DBA同學會詢問業務同學是否可以kill 慢查。堅決不能讓MySQL夯死！但是kill的方式其實是治標不治本，而且只要kill，就難免會誤傷用戶的SQL。

還是這樣圖：我們可以看到select sleep(60);已經執行了56秒了。

於是可以使用 kill 12 來斷開它佔用的連接。但是kill的方式其實是治標不治本，很可能你剛給它斷連，它馬上又建立連接了。而且只要kill，就難免會誤傷用戶的SQL。

接下來就需要研發同學通過explain分析SQL

通過explain你可能會發現，SQL壓根沒走任何索引，而且現在表中的數據量巨大無比。

這時就得根據select的內容創建合適索引。

當然，也可能是數據量太大了，即使走了索引依然超過了閾值。

這種情況其實挺糟糕的，DBA同學能做的依然只能是kill這些出事的SQL。最好的解決方案其實是分表，比如將大表拆分成128張小表。如何來不急做分表，可能這條SQL面臨被下線的風險。

通過explain查看SQL執行計劃中的key欄位。如果發現優化器選擇的Key和你預期的Key不一樣。那顯然是優化器選錯了索引。

當你發現優化器選擇的索引和你期望的索引不一致時，最快的解決方案是：force index , 強制指定索引。

  select a,b,c,d from table force index(keyX) where a between 1 and 9999;

當你發現即使你使用了force index之後，查詢依然很慢。這就意味著，你得設計一個更好的索引。

影響MySQL優化器選錯索引的可能有很多。如下：

基數：基數（cardinality）的統計是一個估算的結果，而且它也並不會實時的更新。所以這就可能出現一開始由於數據量小且沒有代表性。導致基數很低。導致優化器選錯了索引。針對這種情況，可以通過analyze table t 重新計算統計資訊。

掃描行數、排序：更少的掃描行數意味著可能需要更少的磁碟IO，所以MySQL選擇掃描行數少的key的可能性就更大。掃描行數並不是優化器選擇索引的唯一依據，很可能出現你期望SQL走一個二級索引keyX，但是優化器偏偏走了主鍵索引。這是因為優化器考慮到了你select a,b,c,d from xxx;可能還有回表,IO代價也很高。

如果你有order by語句需要mysql幫你做排序，那MySQL就更傾向選一個查詢出來的數據本來就有序的索引。

參考：//dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

八、關注我

為大家帶來MySQL面試專題！本文是第二篇、全文110篇！

以問答的方式，由淺入深的幫你應對各類MySQL面試題的狂轟濫炸！當然也不乏會分享一些高階讀寫分離資料庫中間件原理及落地的技術實現，為你揭開資料庫中間件神秘的面紗！

面試官都關注了！你還在猶豫什麼呢？

Tags: MySQL