慢查询日志相关参数
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 = 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;
三. 如何分析慢查询日志
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代替
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对呐喊教程的支持。
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:notice#nhooo.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。