(6) MySQL慢查询日志的使用

2022-05-09 11:38:53

一. 设置方法

使用慢查询日志里捕获

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

方法一：全局变量设置

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

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代替

码农公寓