MySQL慢查询日志的基本使用教程

慢查询日志相关参数

MySQL 慢查询的相关参数解释：slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

• slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。
• log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
• slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
• long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。
• log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。
• log_output：日志存储方式。log_output=’FILE’表示将日志存入文件，默认值是’FILE’。log_output=’TABLE’表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需<br>要能够获得更高的系统性能，那么建议优先记录到文件。

一. 设置方法

使用慢查询日志里捕获

启用之前需要先进行一些设置

方法一：全局变量设置

设置慢查询日志的日志文件位置

  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 FilesMySQLMySQL Server N.Nbinmysqld.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代替。