MySQL慢查询
什么是慢查询
慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。
慢查询配置慢查询基本配置配置项含义
slow_query_log
启动停止技术慢查询日志
slow_query_log_file
指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time
指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes
是否记录未使用索引的SQL
log_output
日志存放的地方【TABLE】【FILE】【FILE,TABLE】
适用范围
配置了慢查询后，它会记录符合条件的SQLsql语句查询范围，包括：
示例
通过下面命令查看下上面的配置
慢查询日志状态
1
COPY
show VARIABLES like '%slow_query_log%';
1
2
3
4
5
6
7
8
COPY
mysql> show VARIABLES like '%slow_query_log%';
+---------------------+------------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | D:Program FilesMySqldataDESKTOP-0VIUDQU-slow.log |
+---------------------+------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
慢查询日志得存储路径
1
COPY
show VARIABLES like '%slow_query_log_file%';
1
2
3
4
5
6
7
COPY
mysql> show VARIABLES like '%slow_query_log_file%';
+---------------------+------------------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------------------+
| slow_query_log_file | D:Program FilesMySqldataDESKTOP-0VIUDQU-slow.log |
+---------------------+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
慢查询日志SQL执行时间得伐值
1
COPY
show VARIABLES like '%long_query_time%';
1
2
3
4
5
6
7
COPY
mysql> show VARIABLES like '%long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)
是否记录未使用索引的SQL
1
COPY
show VARIABLES like '%log_queries_not_using_indexes%';
1
2
3
4
5
6
7
COPY
mysql> show VARIABLES like '%log_queries_not_using_indexes%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set, 1 warning (0.00 sec)
日志存放的地方
1
COPY
show VARIABLES like 'log_output';
1
2
3
4
5
6
7
COPY
mysql> show VARIABLES like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)
设置参数
1
2
3
4
5
COPY
set global long_query_time=0; --- 默认10秒，这里为了演示方便设置为0
set GLOBAL slow_query_log=1; -- 开启慢查询日志
set global log_output='FILE,TABLE'; -- 项目开发中日志只能记录在日志文件中，不能记表中
注意：这些设置需要重新连接后才会生效
设置完成后，查询一些列表可以发现慢查询的日志文件里面有数据了。
1
2
3
COPY
cat /data1/localhost-slow.log -- 多实例
cat /usr/local/mysql/data/localhost-slow.log -- 单实例
因为在window系统做测试 所以使用gitbash测试
1
COPY
tail -f DESKTOP-0VIUDQU-slow.log
慢查询解读
从慢查询日志里面摘选一条慢查询日志，数据组成如下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
COPY
#查询的执行时间
# Time: 2019-12-25T08:05:13.896259Z
#SQL执行的主机信息
# User@Host: root[root] @ localhost [::1] Id: 89
#SQL的执行信息，查询执行时间 锁定时间 发送的行数和扫描的行数
# Query_time: 0.000320 Lock_time: 0.000114 Rows_sent: 0 Rows_examined: 1
# SQL执行时间
SET timestamp=1577261113;
# 执行的SQL内容
UPDATE `account` SET balance = 100 WHERE id = 31;
慢查询分析
慢查询的日志记录非常多，要从里面找寻一条查询慢的日志并不是很容易的事情sql语句查询范围，一般来说都需要一些工具辅助才能快速定位到需要优化的SQL语句，下面介绍两个慢查询辅助工具
Mysqldumpslow
常用的慢查询日志分析工具，汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。
语法
mysqldumpslow -s r -t 10 slocalhost-slow.log
1
COPY
mysqldumpslow -s t -t 10 /usr/local/mysql/data/mysql-slow.log
pt_query_digest
是用于分析mysql慢查询的一个工具，与mysqldumpshow工具相比，py-query_digest 工具的分析结果更具体，更完善。有时因为某些原因如权限不足等，无法在服务器上记录查询，这样的限制我们也常常碰到。
安装步骤
perl的模块
1
COPY
yum install -y perl-CPAN perl-Time-HiRes
rpm安装
1
2
3
4
5
COPY
cd /usr/local/src
wget percona.com/get/percona-toolkit.rpm
yum install -y percona-toolkit.rpm
工具安装目录在：/usr/bin
源码安装
1
2
3
4
5
6
7
8
9
10
11
COPY
cd /usr/local/src
wget percona.com/get/percona-toolkit.tar.gz
tar zxf percona-toolkit.tar.gz
cd percona-toolkit-2.2.19
perl Makefile.PL PREFIX=/usr/local/percona-toolkit
make && make install
工具安装目录在：/usr/local/percona-toolkit/bin
首先来看下一个命令
1
2
3
4
5
6
7
COPY
yum -y install 'perl(Data::Dumper)';
yum -y install perl-Digest-MD5
yum -y install perl-DBI
yum -y install perl-DBD-MySQL
查看慢查询命令
1
COPY
perl ./pt-query-digest --explain h=192.168.30.130,u=root,p=root /usr/local/mysql/data/localhost-slow.log
汇总信息【总的查询时间】、【总的锁定时间】、【总的获取数据量】、【扫描的数据量】、【查询大小】