linuxsir首页 LinuxSir.Org | Linux、BSD、Solaris、Unix | 开源传万世,因有我参与欢迎您!
网站首页 | 设为首页 | 加入收藏
您所在的位置:主页 > Linux数据库 >

MySQL慢查询日志的使用详解

时间:2019-01-26  来源:未知  作者:admin666
设置慢查询日志的日志文件位置
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 = ON
log_queries_not_using_indexes = ON;
slow_query_log_file = /usr/local/mysql/data/slow.log
long_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;

Time:执行查询的日期时间
User@Host:执行查询的用户和客户端IP
Id:是执行查询的线程Id
Query_time:SQL执行所消耗的时间
Lock_time:执行查询对记录锁定的时间
Rows_sent:查询返回的行数
Rows_examined:为了返回查询的数据所读取的行数

三. 如何分析慢查询日志
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.log
Reading mysql slow query log from slow_log.log
Count: 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代替。

Linux公社的RSS地址:https://www.linuxidc.com/rssFeed.aspx

友情链接