慢查詢日志相關參數
MySQL 慢查詢的相關參數解釋：slow_query_log ：是否開啟慢查詢日志，1表示開啟，0表示關閉。
一. 設置方法
使用慢查詢日志里捕獲
啟用之前需要先進行一些設置
方法一：全局變量設置
設置慢查詢日志的日志文件位置
set global slow_query_log_file = "D:/slow_log/slow_log.log" ;
設置是否對未使用索引的SQL進行記錄
set global log_queries_not_using_indexes = on;
設置只要SQL執行時間超過n秒的就記錄
set global long_query_time = 0.001 ;
此處設置的0.001秒，便于測試，一般情況比這個大
啟用mysql慢查詢日志
set global slow_query_log = on;
方法二：配置文件設置
修改配置文件my.cnf，在[mysqld]下的下方加入
[mysqld]slow_query_log = ONlog_queries_not_using_indexes = ON;slow_query_log_file = /usr/local/mysql/data/slow.loglong_query_time = 1
查看設置后的參數
show variables like 'slow_query%';show variables like 'long_query__time';
二. 慢查詢日志記錄的內容
Time Id Command Argument# Time: 2019-01-08T04:12:09.269315Z # User@Host: h5_test[h5_test] @ localhost [::1] Id: 12 # Query_time: 0.000831 Lock_time: 0.000198 Rows_sent: 1 Rows_examined: 3 use mc_productdb;SET timestamp=1546920729;SELECT t.customer_id,t.title,t.content FROM (SELECT customer_id FROM product_comment WHERE product_id =199726 AND audit_status = 1 LIMIT 0,15)a JOIN product_comment t ON a.customer_id = t.comment_id;
三. 如何分析慢查詢日志
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time
由于慢查詢日志中會含有大量的重復的SQL,為了方便，可以通過mysql提供的命令行工具 mysqldumpslow 來分析日志
$ mysqldumpslow.pl slow_log.logReading mysql slow query log from slow_log.logCount: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts C:/Program Files/MySQL/MySQL Server N.N/bin/mysqld.exe, Version: N.N.N-log (MySQL Community Server (GPL)). started with: TCP Port: N, Named Pipe: MySQL # Time: N-N-08T04:N:N.269315Z # User@Host: h5_test[h5_test] @ localhost [::N] Id: N # Query_time: N.N Lock_time: N.N Rows_sent: N Rows_examined: N use mc_productdb; SET timestamp=N; SELECT t.customer_id,t.title,t.content FROM ( SELECT customer_id FROM product_comment WHERE product_id =N AND audit_status = N LIMIT N,N )a JOIN product_comment t ON a.customer_id = t.comment_id
與慢查詢日志中記錄的數據是相似的，只是多出了一行Count，這一行記錄的是這條SQL在記錄慢查詢日志期間的執行次數，如果一個SQL多次被執行，用這個命令分析時，只會出現一個SQL日志，Count里的數值代表執行次數，其他數字為了合并表示用N代替
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對VeVb武林網的支持。
新聞熱點
疑難解答
