MySQL最佳化(3)：慢SQL分析

標籤：查詢語句   預設   查詢   刪除   myisam   狀態   join   file   圖片   

對慢SQL最佳化一般可以按下面幾步的思路：

1、開啟慢查詢日誌，設定超過幾秒為慢SQL，抓取慢SQL

2、通過explain對慢SQL分析（重點）

3、show profile查詢SQL在Mysql伺服器裡的執行細節和生命週期情況（重點）

4、對資料庫伺服器的參數調優

一、慢查詢日誌

1、設定慢查詢

（1）設定開啟：SET GLOBAL slow_query_log = 1;　　　#預設未開啟，開啟會影響效能，mysql重啟會失效（2）查看是否開啟：SHOW VARIABLES LIKE ‘%slow_query_log%‘;（3）設定閾值：SET GLOBAL long_query_time=3;（4）查看閾值：SHOW 【GLOBAL】 VARIABLES LIKE ‘long_query_time%‘;　　#重連或新開一個會話才能看到修改值（5）通過修改設定檔my.cnf永久生效，在[mysqld]下配置：　　[mysqld]　　slow_query_log = 1;　　#開啟　　slow_query_log_file=/var/lib/mysql/atguigu-slow.log　　　#慢日誌地址，預設檔案名稱host_name-slow.log　　long_query_time=3;　　  #已耗用時間超過該值的SQL會被記錄，預設值>10　　log_output=FILE　　　　　　　　　　　

2、擷取慢SQL資訊　　　

查看慢查詢日誌記錄數：SHOW GLOBAL STATUS LIKE ‘%Slow_queries%‘;

　　類比語句：select sleep(4);

　　查看日誌：cat atguigu-slow.log

 　　

3、搭配日誌分析工具mysqldumpslow

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log     #得到返回記錄集最多的10個SQLmysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log     #得到訪問次數最多的10個SQLmysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/atguigu-slow.log   #得到按照時間排序的前10條裡面含有左串連的查詢語句mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more      #結合| more使用，防止爆屏情況s：表示按何種方式排序c：訪問次數l：鎖定時間r：返回記錄t：查詢時間al：平均鎖定時間ar：平均返回記錄數at：平均查詢時間t：返回前面多少條的資料g：後邊搭配一個正則匹配模式，大小寫不敏感

二、explain分析慢SQL

　　通過explain分析慢SQL很重要，單獨一章列舉，MySQL最佳化(4)：explain分析。

三、Show Profile分析慢SQL

 　  Show Profile也是分析慢SQL的一種手段，但它能獲得比explain更詳細的資訊，能分析當前會話中語句執行的資源消耗情況，能獲得這條SQL在整個生命週期的耗時，相當於執行時間的清單，也很重要。

1、預設關閉。開啟後，會在後台儲存最近15次的運行結果，然後通過Show Profile命令查看結果。

開啟：set profiling = on;查看：SHOW VARIABLES LIKE ‘profiling%‘;

2、通過Show Profile能查看SQL的耗時

　　 

3、通過Query_ID可以得到具體SQL從串連 - 服務 - 引擎 - 儲存四層結構完整生命週期的耗時

　　

可用參數type:ALL  　　#顯示所有的開銷資訊BLOCK IO　　#顯示塊IO相關開銷CONTEXT SWITCHES　　#環境切換相關開銷CPU     #顯示CPU相關開銷資訊IPC     #顯示發送和接收相關開銷資訊MEMORY　#顯示記憶體相關開銷資訊PAGE FAULTS　　#顯示分頁錯誤相關開銷資訊SOURCE　　#顯示和Source_function，Source_file，Source_line相關的開銷資訊SWAPS　　#顯示交換次數相關開銷的資訊

4、出現這四個status時說明有問題，group by可能會建立暫存資料表

#危險狀態：
converting HEAP to MyISAM  　　#查詢結果太大，記憶體不夠用了，在往磁碟上搬Creating tmp table         　　#建立了暫存資料表，回先把資料拷貝到暫存資料表，用完後再刪除暫存資料表Copying to tmp table on disk 　#把記憶體中暫存資料表複製到磁碟，危險！！！locked

　　

四、全域查詢日誌

只在測試環境用，別在生產環境用，會記錄所有使用過的SQL

1、開啟：

開啟：會將sql記錄到mysql庫的general_log表    set global general_log=1;    set global log_output=‘TABLE‘;設定檔的方式：    在my.cnf中配置    general_log=1    #開啟    general_log_file=/path/logfile    #記錄記錄檔的路徑    log_output=FILE    #輸出格式

2、查看

select * from mysql.general_log;

 　　

MySQL最佳化(3)：慢SQL分析