MySQL如何由慢查询寻问题SQL

96
波罗学的草稿
2016.12.22 12:22* 字数 2762

简书不维护了,欢迎关注我的知乎:[波罗学的个人主页](https://www.zhihu.com/people/xue-jian-27/activities)

最近一直在研究学习MySQL的性能优化。影响MySQL性能的原因来自多个方面,包括硬件、操作系统、系统配置、表结构以及SQL语句和索引。就优化效果与成本的考量,SQL语句与索引的优化是我们最先需要考虑的优化策略,而这类优化的前提需要我们先找出有问题的SQL。

我们如何找出有问题的SQL呢?总结有以下几种方式:

通过分析慢查询日志,慢查询日志专注于慢SQL记录,寻找问题SQL最佳途径

通过MySQL性能监控,如引言所述,性能问题多样,未必SQL问题

通过应用层性能剖析,考量范围更大,可能是如代码设计、外部服务等非MySQL问题

通过用户反馈,比如哪些页面存在严重卡顿,此方式太被动,通过反馈的问题基本已严重影响了用户体验

以上几种最直接有效的方式就是分析MySQL慢查询日志。这是一项技术活,但对你而言或许是一项体力活。本篇文章重点在于如何利用慢查询日志找出问题SQL。

实验环境:Centos7 + MySQL-5.6.34

开启慢查询日志

主要是介绍一些慢查询日志的配置选项:

slow_query_log:是否开启慢查询,0或者OFF为关闭,1或者ON为开启,默认值为OFF,即为关闭

slow_query_log_file:指定慢查询日志存放路径

long_query_time:大于等于此时间记录慢查询日志,精度可达微秒级别,默认为10s。当设置为0时表示记录所谓查询记录

通过如下命令查看当前配置:

其他相关配置项

log_queries_not_using_indexes:是否记录未使用索引的查询,默认OFF

log_throttle_queries_not_using_indexes:每分钟允许写入到慢查询日志的未使用索引的语句,MySQL 5.6.5新增,默认为0,无限制

log_slow_admin_statements:是否记录管理类指令,默认OFF

b. 配置方式:

第一种方式set global, 请注意重启MySQL后配置无效。 配置如下:

mysql>set global slow_query_log=ON;

mysql>set global long_query_log=0.100000;

mysql>set global slow_query_log_file

第二种方式配置文件

可通过如下命令查看配置文件查找顺序

$ mysqld --help --verbose | grep -A1"Default options"

Default options arereadfrom the following filesinthe given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

...

从输出看出配置文件查找顺序/etc/my.cnf=>/etc/mysql/my.cnf=>/usr/etc/my.cnf=>~/.my.cnf

打开/etc/my.conf,添加如下配置:

[mysqld]

...

slow_query_log=ON

long_query_time=0.100000

slow_query_log_file=/var/lib/mysql/localhost-slow.log

关于long_query_time阈值时间设定,根据自己具体的实际情况来设置。

如版本大于5.6.5可以开启log_queries_not_using_indexes记录未使用索引的记录,通过log_throttle_queries_not_using_indexes限制每分钟写入的条数。

克服了对慢查询日志的心理担忧和正确配置之后,请重启MySQL服务。下面我们就可以静静等待每天产生的慢查询日志了。

慢查询日志的组成

记录慢查询日志是为了从中分析出有价值的信息。在此之前,首先需要看看慢查询日志包含哪些内容,不同版本的MySQL的慢查询日志组成也是不尽相同。下面以MySQL-5.6.34为例说明:

# Time: 161211 16:35:09

# User@Host: root[root] @ localhost []  Id:     4

# Query_time: 5.007305  Lock_time: 0.000112 Rows_sent: 5  Rows_examined: 10

SET timestamp=1481470509;

select customer_id, sleep(1) from customer cwherec.customer_idin(select customer_id p from payment)limit5;

下面说明下各组成部分的含义:

第一部分如下:

# Time: 161211 16:35:09

慢查询产生时间。同一时间内多个慢sql,只有第一条sql会显示此时间

注意 这个时间是语句执行结束时间,对这时间理解有误将会得到错误结论。

第二部分如下:

# User@Host: root[root] @ localhost [127.0.0.1] Id: 4

客户端相关信息,如连接用户,主机和连接线程id。上图可以知道

连接用户User:root,

连接主机Host:localhost,ip为.0.0.1

连接线程Id:7,连接线程id可通过select connection_id获取

第三部分如下:

# Query_time: 5.007305 Lock_time: 0.000112 Rows_sent: 5 Rows_examined: 10

包含查询时间、等待锁的时间、获取行数和扫描行数

查询时间Query_time:5.007305,查询花费的总时间

等待锁时间Lock_time:0.000112,这些锁具体有哪些?初步理解,很少是因为数据的行级锁导致,可能查询缓存的锁,日志文件锁等需要保持原子操作的锁,有深刻理解的大神求指教

获取行数Rows_sent:5,实际获取的数据行数

扫描行数Rows_examined:10,实际扫描的数据行数

第四部分如下:

SET timestamp=1481470509;

select customer_id, sleep(1) from customer c where c.customer_id in (select customer_id p from payment) limit 5;

具体的慢SQL,SET timestamp可以忽略,查看下面一句。

select customer_id, sleep(1) from customer c where c.customer_id in (select customer_id p from payment) limit 5;

MySQL的慢查询日志主要组成就是这些。MySQL的PerconaServer版本慢查询日志内容更为丰富,更利于问题定位,包含如执行是否被killed、发送的bytes、是否Full_scan、是否Full_join、是否使用query_cache、是否使用临时表和是否使用filesort等等信息,如有兴趣可以研究一下。

如何分析慢查询日志

拿到一份慢查询日志,请不要直接打开。否则你会发现在耗费了很长一段时间之后,依然毫无结论。正如开头所说,分析慢查询日志本来是一门技术活,但对于你而言,或许是门体力活。总结几点慢查询日志中本人比较关心的点:

慢查询的总条数

慢查询执行时间大于指定时间条数

慢查询指定时间段数量,如只想分析公司大促期间慢查询

哪些语句耗时最严重,可能因某条语句导致服务器整体性能下降

哪些语句平均耗时严重,如执行次数不多但是本身执行时间太长的语句

哪些语句执行时间分布不均,可能因查询条件不同选择不同索引或查询缓存导致

不合理的sql,如代码问题导致select * from table无条件查询大表

语句是否已经使用了索引

using filesort或者using tempary,大表排序且未正确使用索引

只查询某个用户某张表的慢查询

某慢查询在总值占比情况等等

随机总结了几条,有些或许不是很合理。重点在于说明,要有正确的方向再进行分析,才不会只做体力劳动。

有了基本的思路之后,是不是需要完全由我们自己去编写脚本去分析慢查询日志呢?答案当然是否。遵循不重复造轮子的思想,这里给大家推荐几款工具:

mysqlslowdump,官方自带的慢查询日志分析工具,支持如执行时间,锁定时间和发送行数的总值和平均值排序,支持pattern过滤等功能,可以满足基本使用。但无具体分析数据,且无法进行更精细的搜索,如指定的日期范围等

pt-query-digest 个人认为这个工具是神器,percontoolkit中的慢查询分析工具,除了上述基本功能外,还支持慢查询报表统计,占比分析,离差指数,精确的搜索如指定用户,指定时间范围,指定IP等等,匹配此工具的还有一款PHP开发的web监控工具Query-digest-UI,不过暂时还比较简陋。perconaToolkit这个工具集还有其他很多对于mMySQL有用的工具,如pt-online-schema-change不锁表改表、pt-config-diff检测服务器配置等等。本人暂时也没有太多了解,有兴趣可以一起看一下。

## pt-query-digest

- 查询近时间慢查询

  如统计最近12小时的慢查询

$ pt-query-digest --since 12h mysql-slow-1110-1111.log > recent_12h-all-parse

- 分析指定范围内的查询

  如统计2016年11月10日 09:50:00至11月10日 10:10:00的慢查询

$ pt-query-digest --since '2016-11-10 09:50:00' --until '2016-11-10 10:10:00' mysql-slow-1110-1111.log > 1110095000_1110101000-all-parse

- 分析指定语句的慢查询

  如统计只含有select语句的慢查询

$ pt-query-digest --filter '$event->{fingerprint}=~ m/^select/i' mysql-slow-1110-1111.log > 1110_1111-select-parse

- 针对某个用户的慢查询

  如统计db_user用户的慢查询

$ pt-query-digest --filter '($event->{user} || "")=~ m/^db_user/i' mysql-slow-1110-1111.log > 1110_1111-db_user-parse

- 分析所有的全表扫描

  统计所有全表扫描慢查询

$ pt-query-digest --filter '($event->{Full_scan} || "") eq "yes"' mysql-slow-1110-1111.log > 1110_1111-full_scan-parse

- 查询保存到query_review表

$ pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_review--create-review-table  slow.log

- 把查询保存到query_history表

$ pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history --create-review-table  slow.log_20140401

$ pt-query-digest  --user=root –password=abc123--review  h=localhost,D=test,t=query_history --create-review-table  slow.log_20140402

- 通过tcpdump抓取mysql的tcp协议数据,然后再分析

  $ tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

  $ pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

- 分析binlog

mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql

pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log

- 分析general log

pt-query-digest  --type=genlog  localhost.log > slow_report11.log

官方文档:http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

这些工具本篇文章不做细致介绍,具体细节可看后续文章。最终目标是针对这些工具开发出一套切实可行的MySQL慢查询监控方案,虽然pt-query-digest足够强大,但可能完全依靠其实现所有需求未必现实。

MYSQL笔记
Gupao