MySQL深入学习——第五章 慢查询&索引优化实战学习笔记

一、什么是慢查询？

慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

1、慢查询配置

1.1、慢查询基本配置

slow_query_log 启动停止技术慢查询日志

slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）

long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）

log_queries_not_using_indexes  是否记录未使用索引的SQL

log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

配置了慢查询后，它会记录符合条件的SQL

包括：查询语句，数据修改语句，已经回滚得SQL

实操：通过下面命令查看下上面的配置：

show VARIABLES like '%slow_query_log%';

show VARIABLES like '%slow_query_log_file%';

show VARIABLES like '%long_query_time%';  -- 10秒 默认

show VARIABLES like '%log_queries_not_using_indexes%';  -- 默认off

show VARIABLES like 'log_output';

参数设置：

set global long_query_time=0;   --- 默认10秒，这里为了演示方便设置为0

set GLOBAL slow_query_log=1;   -- 开启慢查询日志

set global log_output='FILE,TABLE';  -- 项目开发中日志只能记录在日志文件中，不能记表中

设置完成后，查询一些列表可以发现慢查询的日志文件里面有数据了。

cat /data1/localhost-slow.log  -- 多实例

cat /usr/local/mysql/data/localhost-slow.log  -- 单实例

1.2、慢查询解读

从慢查询日志里面摘选一条慢查询日志，数据组成如下：

# User@Host: root[root] @  [192.168.30.199]  Id:    21

# Query_time: 0.000078  Lock_time: 0.000000 Rows_sent: 4  Rows_examined: 4

SET timestamp=1561389688;

# administrator command: Init DB;

# Time: 2019-06-24T15:21:28.258812Z

# User@Host: root[root] @  [192.168.30.199]  Id:    21

# Query_time: 0.000402  Lock_time: 0.000167 Rows_sent: 8  Rows_examined: 8

SET timestamp=1561389688;

SHOW COLUMNS FROM `hankin`.`t_user`;

慢查询格式显示：

第一行：用户名 、用户的IP信息、线程ID号

第二行：执行花费的时间【单位：毫秒】

第三行：执行获得锁的时间

第四行：获得的结果行数

第五行：扫描的数据行数

第六行：这SQL执行的具体时间

第七行：具体的SQL语句

2、慢查询分析

慢查询的日志记录非常多，要从里面找寻一条查询慢的日志并不是很容易的事情，一般来说都需要一些工具辅助才能快速定位到需要优化的SQL语句，下面介绍两个慢查询辅助工具

2.1、Mysqldumpslow

常用的慢查询日志分析工具，汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

语法：mysqldumpslow -s r -t 10 slocalhost-slow.log

-s order (c,t,l,r,at,al,ar)

c:总次数， t:总时间，l:锁的时间， r:总数据行

at,al,ar  :t,l,r平均数  【例如：at = 总时间/总次数】

-t  top   指定取前面几天作为结果输出

mysqldumpslow -s t -t 10

cat /data1/localhost-slow.log （多实例路劲）

cat /usr/local/mysql/data/localhost-slow.log

2.1、pt_query_digest

是用于分析mysql慢查询的一个工具，与mysqldumpshow工具相比，py-query_digest 工具的分析结果更具体，更完善。有时因为某些原因如权限不足等，无法在服务器上记录查询，这样的限制我们也常常碰到。

perl的模块：

yum install -y perl-CPAN perl-Time-HiRes

安装步骤：

方法一：rpm安装

cd /usr/local/src

wget percona.com/get/percona-toolkit.rpm

yum install -y percona-toolkit.rpm

工具安装目录在：/usr/bin

方法二：源码安装

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

首先来看下一个命令：

yum -y install 'perl(Data::Dumper)';

yum -y install perl-Digest-MD5

yum -y install perl-DBI

yum -y install perl-DBD-MySQL

查看慢查询命令：

perl ./pt-query-digest --explain h=192.168.30.130,u=root,p=root /usr/local/mysql/data/localhost-slow.log

汇总信息【总的查询时间】、【总的锁定时间】、【总的获取数据量】、【扫描的数据量】、【查询大小】

Response: 总的响应时间。

time: 该查询在本次分析中总的时间占比。

calls: 执行次数，即本次分析总共有多少条这种类型的查询语句。

R/Call: 平均每次执行的响应时间。

Item : 查询对象

1）扩展阅读：

语法及重要选项

pt-query-digest [OPTIONS] [FILES] [DSN]

--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。

--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。

--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析

--limit 限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。

--host mysql服务器地址

--user mysql用户名

--password mysql用户密码

--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。

--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。

--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。

--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。

--until 截止时间，配合—since可以分析一段时间内的慢查询。

2）分析pt-query-digest输出结果

第一部分：总体统计结果

Overall：总共有多少条查询

Time range：查询执行的时间范围

unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询

total：总计 min：最小 max：最大 avg：平均

95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值

median：中位数，把所有值从小到大排列，位置位于中间那个数

# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小

# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz

# 工具执行时间

# Current date: Fri Nov 25 02:37:18 2016

# 运行分析工具的主机名

# Hostname: