一. 设置方法
使用慢查询日志里捕获
启用之前需要先进行一些设置
方法一: 全局变量设置
设置慢查询日志的日志文件位置
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 代替.
来源: http://www.linuxidc.com/Linux/2019-01/156301.htm