用MySQL分析SQL的耗時問題

慢日志查詢

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?FilesmysqldataDESKTOP-VN2D5OU-slow.log#得到訪問次數最多的10個SQL  Mysqldumpslow?–s?c?–t?10?D:Program?FilesmysqldataDESKTOP-VN2D5OU-slow.log#得到按照時間排序的前10條里面含有左連接的查詢  Mysqldumpslow?–s?t?–t?10?–g?“left?join”?D:Program?FilesmysqldataDESKTOP-VN2D5OU-slow.log#另外建議在使用這些命令時結合|和more使用,否則可能出現爆破情況  Mysqldumpslow?–s?r?–t?10?D:Program?FilesmysqldataDESKTOP-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

? 版權聲明
THE END
喜歡就支持一下吧
點贊9 分享