Mysql——》慢查询日志

推荐链接：
    总结——》【Java】
    总结——》【Mysql】
    总结——》【Redis】
    总结——》【Kafka】
    总结——》【Spring】
    总结——》【SpringBoot】
    总结——》【MyBatis、MyBatis-Plus】
    总结——》【Linux】
    总结——》【MongoDB】
    总结——》【Elasticsearch】

参考链接：官网Slow Query Log

一、慢查询的概念

慢查询：指查询花费大量时间的查询。
慢查询日志：指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。

二、慢查询的原因

• 扫描行数过多
• 查询不需要的记录
• 总是取出全部列
• 重复查询相同的数据

三、慢查询的配置

1、是否开启慢查询日志

-- 是否开启慢查询日志，on开启，off关闭，默认值为off
show variables like 'slow_query_log%';

Q：为什么慢查询日志默认关闭？
A：因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样）

2、慢查询日志的开启、关闭

方式1：动态修改参数（重启后失效）
方式2：修改my.cnf文件（重启后生效）

-- 开启
set @@global.slow_query_log=1; 

-- 关闭
set @@global.slow_query_log=0; 

3、执行超过多长时间的 SQL记录到慢查询日志

-- 查看慢查询时间阈值，默认值为10s
show variables like '%long_query_time%';

-- 设置超过3s的SQL记录到慢日志
set @@global.long_query_time=3;

4、没有使用索引的 SQL是否记录到慢查询日志

-- 是否开启，默认值为off关闭
show VARIABLES like '%log_queries_not_using_indexes%';

-- 开启
set @@global.log_queries_not_using_indexes=1;
-- 关闭
set @@global.log_queries_not_using_indexes=0;

5、慢查询日志的文件存放路径

-- 查看慢查询日志的路径：/data/mysql/data/dcbi-3306/log/slow.log
show variables like 'slow_query_log_file%';

三、慢查询的模拟

-- 方式一
select sleep(10);

-- 方式二
set global long_query_time=0;

四、慢查询日志：根据查询时间提取

cd /data/mysql/data/dcbi-3306/log
sed -n '/2022-10-21T00/,/2020-10-22T00:00/p' slow.log > 20221021.log

五、慢查询日志分析：mysqldumpslow工具

参数链接：官网mysqldumpslow

1）慢查询日志的文件路径

  -- 查看有多少慢查询
 show global status like 'slow_queries';
 
 -- 查看慢查询日志的文件路径：/data/mysql/data/dcbi-3306/log/slow.log
 show variables like 'slow_query_log_file%';

2）mysqldumpslow的路径

一般都在Mysql的bin目录下，如：cd /opt/app/install/mysql-5.7.19/bin

3）mysqldumpslow的参数

mysqldumpslow --help

参数	参数值	描述
-s order	al	平均锁定时间
	ar	平均返回记录数
	at	平均查询时间（默认）
	c	计数
	l	锁定时间
	r	返回记录
	t	查询时间
-r		返回记录
-t num		查询时间，倒序排列，前n条记录
-a		只显示前n个查询
-n num
-g pattern
-h hostname
-i name
-l

4）mysqldumpslow的示例

cd /data/mysql/data/dcbi-3306/log


-- 查询用时最多的10条慢sql
mysqldumpslow -s t -t 10 -g 'select' slow.log

-- 查询用时最多，并且包含左连接的的10条慢sql
mysqldumpslow -s t -t 50 -g "left join" slow.log

-- 访问次数最多的10个SQL语句
mysqldumpslow -s c -t 10 slow.log

-- 记录集最多的10个SQL语句
mysqldumpslow -s r -t 10 slow.log

-- 查询用时最多的10条慢sql，并输出到txt
mysqldumpslow -s t -t 10 -g 'select' slow.log > result.txt

-- 查询用时最多的10条慢sql，并输出到csv
mysqldumpslow -s t -t 10 -g 'select' slow.log > result.csv

Count：代表这个 SQL 执行了多少次
Time：代表执行的时间，括号是累计时间
Lock：表示锁定的时间，括号是累计时间
Rows：表示返回的记录数，括号是累计记录数