淺談性能瓶頸定位之MySQL慢查詢
- 2019 年 12 月 2 日
- 筆記
在性能測試過程中，我們會發現各種各樣的性能問題，其中資料庫相關的問題尤為居多。在鄙人做過的項目中，超過40%以上性能問題是跟開發人員編寫的SQL有關。今天從性能測試工程師角度談談如何定位mysql中的 SQL慢查詢。
一、通過慢查詢日誌獲取存在性能問題的SQL
鄙人所在公司的項目的測試流程如下：
問題來了，如何做到在功能測試結束後，性能測試開始前發現單元測試/API測試/功能測試中出現的慢查詢以及未走索引的查詢呢？答案就是：開啟這兩個測試環境的mysql資料庫慢查詢日誌，然後在日誌文件里自動記錄這些慢查詢，以及not using index的查詢。下面做一個簡單的演示：
通過客戶端連上mysql資料庫（這裡演示用的是Navicat Premium），執行下面幾個命令：
set global slow_query_log=on /*開啟慢查詢日誌*/set log_queries_not_using_indexes=on /*開啟未用查詢的日誌*/set global slow_query_log= 「你想要的路徑」 /*該演示里使用默認路徑*/
然後，執行如下命令，可以看到慢查詢日誌已經打開，並且可以看到慢查詢日誌存放的路徑。
Show variables like 『%slow%』
到這裡我們已經開啟了慢查詢以及未走索引的查詢日誌記錄，但是還差一步，那就是多慢的查詢算慢查詢？鄙人所在微服務項目開發和測試討論的結果是超過100毫秒的查詢，都得記錄分析調優。所以還需要執行：
Set long_query_time=0.1 /*超過100毫秒的都是慢查詢*/
在測試開發環境和測試環境做完上面設置後，就可以定期取日誌，看到我們想看的慢查詢記錄，以及沒用用到索引的查詢。下面是工作中的一個真實示例截圖：
此外，還有一款工具叫pt-query-digest，用來分析統計慢查詢日誌，有興趣可以下載使用試試。
pt-query-digest --explain h=127.0.0.1, u=root,p=wwwslow-mysql.log
二、實時獲取慢查詢
在性能測試執行過程中，有時會發現某一個非常慢，此刻就需要實時找出正在執行的慢查詢。我們可以藉助下面的命令實現：
select id, 'user','host,db,command,'time',state, infofrom information_schema.processlist where time >=0.1
這裡的0.1秒可以根據自己需要調整，information_schema對應資料庫的processlist表
三、分析利器Showprofiles
作為性能測試人員，在我的工作中比較少用showprofiles，其中一個主要原因是執行showprofiles後得到的數據雖然更詳細，但對這些數據的使用無從下手。我們看看如何使用這個工具：
set profiling = ON; /*開啟profiling功能*/show variables like "profiling"; /*查看開啟是否生效*/show profiles /*開啟profile收集到所有sql執行的profile*/
Show profile for query 65 /*查看query_id為65的query對應時間花在什麼地方了*/
show profile block io,cpu for query 65 /*查看query id為65的對應cpu, io等資訊*/
四、explain：真正的好幫手
上面主要講了怎麼找到慢查詢，有了慢查詢sql後，我們怎麼分析呢？藉助explain。
一個典型的sql語句執行過程如下：
①、客戶端發送sql請求給伺服器
②、伺服器端檢查是否可以在查詢快取中命中該sql
③、伺服器端進行sql解析，預處理，再由優化器生成執行計劃
④、依據執行計劃，調用存儲引擎API來查詢數據
⑤、結果返回給客戶端
這裡我們著重講講第四點執行計劃，如何用explain去查看分析執行計劃。：
簡單使用 explain+ sql 語句來查看執行計劃：
主要關注id(執行順序)、type(表的掃描方式)、rows(檢索數據需要掃描的行)、key(使用到的索引)、key_len(索引長度),extra(額外資訊)。
示例1：explain語句分析發現「索引欄位進行數學運算和函數運算，導致索引失效」
Payment表的paytime欄位建立了索引，執行explain + SQL語句，我們可以看到：
Type=ALL,key=null, rows=14661, extra: using where
說明該查詢做了全表掃描，索引沒有起作用。
在第二個查詢里去掉了date_format()，執行 exaplain + SQL語句，我們可以看到：
Type=range, key=payment_index3, rows=344, Extra:using index
這就是通過explain工具分析發現「索引欄位進行數學運算和函數運算，導致索引失效」
示例2：explain語句分析發現「包含or的多條件查詢無法通過索引檢索數據，走全表掃描」
select * fromXXX_login_account where actived=1 and (mobile =18504000006 orCREATE_time > '2019-07-03');
分析該表的索引，mobile和create_time都有索引，但是從執行計劃看，沒有走索引，走的是全表掃描：
同步到開發同事後，優化SQL，優化後的SQL分析如下：
如上圖所示，index生效。
