用MySQL分析SQL的耗時問題

一个新手
發布: 2017-09-09 14:03:47
原創
2331 人瀏覽過


慢日誌查詢

MySQL的慢日誌查詢是MySQL提供的日誌記錄,它花了記錄在MySql中回應時間超過閾值的語句,具體運行時間超過long_query_time值的SQL,則會被記錄到慢日誌中。 long_query_time的預設時間為10,意思是執行10以上的語句。

例如一條sql執行超過5秒鐘,我們就算慢SQL,MySQL會記錄超過5秒的sql,我們可以結合explain進行全面分析。

預設情況下,MySQL資料庫沒有開啟慢查詢日誌,需要我們手動來設定這個參數。當然,如果不是調優需要,一般不建議啟動該參數,因為慢日誌會或多或少帶來一定的效能影響。

是否開啟以及設定

#查看是否开启show variables like '%slow_query_log%';#开启set global slow_query_log = 1;
登入後複製

使用set global slow_query_log=1開啟了慢查詢日誌只對目前資料庫生效,如果MySQL重新啟動後則會失效。如果要永久生效,就必須修改設定檔my.cnf。

注意設定慢查詢閾值時間後,你可能看不到值發生了變化,即沒有生效,這時需要重新連接或新開一個會話才能看到修改值。

show variables like '%long_query_time%'
登入後複製
登入後複製

或不重開連線也可以使用下面的指令:

show variables like '%long_query_time%'
登入後複製
登入後複製

哪些sql會被慢日誌記錄,這是由參賽long_query_time控制,預設為long_query_time的值為10秒,指令:

show variables like '%long_query_time%';
登入後複製

假如運行時間剛好等於long_query_time的情況,並不會被記錄下來。也就是說,在mysql是判斷大於long_query_time,而非大於等於。

可以用下面的語句做個測試

SELECT sleep(4)
登入後複製

如果你設定的long_query_time為3秒,那麼這條語句就會被記錄下來。

用MySQL分析SQL的耗時問題

查看慢日誌條數

show global status like '%slow_queries%'
登入後複製

 日誌分析工具mysqldumpslow

在生產環境中,如果要手動分析日誌,查找、分析SQL,顯然是個體力活,MySql提供了日誌分析工具mysqldumpslow。

例如:

#得到返回记录集最多的10个SQL
Mysqldumpslow –s r –t 10 D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log#得到访问次数最多的10个SQL
Mysqldumpslow –s c –t 10 D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log#得到按照时间排序的前10条里面含有左连接的查询
Mysqldumpslow –s t –t 10 –g “left join” D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log#另外建议在使用这些命令时结合|和more使用,否则可能出现爆破情况
Mysqldumpslow –s r –t 10 D:\Program Files\mysql\data\DESKTOP-VN2D5OU-slow.log|more
登入後複製
参数含义
s: 表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
t:返回前面多少条的数据
g:后面搭配一个正则表达式
登入後複製

用show profile進行sql分析

#show profile指令可以分析目前會話中語句執行的資源消耗情況。用於查找SQL耗時瓶頸 。預設為關閉狀態,並保存最近15次的運行結果。

檢視是否開啟(show variables like ‘profiling’;)
開啟功能(set profiling = on;)

開啟之後就可以記錄接下來sql的運作情況。之後透過show profiles來查看結果:

用MySQL分析SQL的耗時問題

進一步透過指令(show profile cpu, block io for query 3;)分析某個SQL語句執行情況,例如下面分析3號SQL的情況。

用MySQL分析SQL的耗時問題

Show profile後面的一些參數:

  • #All:顯示所有的開銷資訊

  • Block io:顯示區塊IO相關開銷

  • Context switches:上下文切換相關開銷

  • Cpu:顯示cpu相關開銷

  • Memory:顯示記憶體相關開銷

  • #Source:顯示和source_function,source_file,source_line相關的開銷資訊

#全域查詢日誌

(永遠不要在生產環境開啟,查看所有執行的SQL語句)

設定指令:

set global general_log = 1;#以表的形式输出set global log_output = ‘TABLE’
登入後複製

此後,mysql所執行的SQL語句將會記錄到mysql.genearl_log表,可用下面的指令查看:

select * from mysql.general_log;
登入後複製

也可以在設定檔中配置,設定如下:

#开启General_log = 1#记录日志文件的路径General_log_file = D://path/logfile#输出格式Log_output=file
登入後複製

以上是用MySQL分析SQL的耗時問題的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:php.cn
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板