Mysql性能优化系列(1)_获取并分析慢sql

本文为Mysql性能优化系列的首篇文章,自然要从头讲起。所谓mysql性能优化很大一部分上指你写的sql语句好不好,以及表结构和索引是否建立合理。因此我们必须从发现“慢sql”为起点来构建整个mysql性能优化的蓝图。

所谓的慢sql很好理解,指的就是速度较慢的sql,一般来说99%的情况是select语句。我们必须明白慢sql所带来的危害是巨大的。它体现在两个方面:

  • 导致MySQL的cpu长时间占用100%
  • 输出巨量结果集,导致调用层(java端)的内存溢出(大结果集极有可能是慢sql)

这两个危害能直接影响到整个生产环境的正常运行,有时候我们说生产环境挂了,其实就是数据库扛不住了。

如何开启慢日志

修改mysql的配置文件my.conf或者my.ini,添加一下配置:

1
2
3
4
5
6
7
[mysqld]
#定义超过多长时间才算慢sql,单位是秒
long_query_time=0.5
#开启慢sql日志
slow-query-log=On
#日志存放地址
slow_query_log_file="mysql_slow_sql.log"

对于慢sql的定义,看你自己的业务的容忍度,一般来说互联网业务都是超过0.5秒就算是慢sql了。改完配置之后,重启mysql实例即可生效。

慢sql日志的格式

对于慢sql来说,需要积累一定的时间才有分析的价值。比如按1天的维度为单位去分析,如果你每个小时都去看一次的话,费时且应为采样不够得到的数据也不够准确。假设现在收集到了1天的慢sql,那么你将会得到这样的慢sql记录:

1
2
3
4
5
6
7
8
# Time: 2020-12-08T00:19:20.164885+08:00
# User@Host: jte_pms_business[jte_pms_business] @ [10.104.6.220] Id: 69601
# Query_time: 0.597593 Lock_time: 0.000129 Rows_sent: 1 Rows_examined: 12148
SET timestamp=1607357960;
select
count(1)
from t_history_order
WHERE order_code = '12232';

每一条慢sql都会包含以下几个信息:

  • 执行时间
  • 对应的数据库
  • 执行该SQL的用户名
  • 执行时间(Query_time)
  • 锁表时间(Lock_time)
  • 返回行数(Rows_sent)
  • 扫描的行数(Rows_examined:)
  • 具体的SQL

当你仅仅只有几十条慢sql的时候当然是非常好分析的,但是事实往往没有想象中的简单。在线上环境随便就给你整2000条慢sql。那么如果要用人工去检索所有的慢sql几乎是不可能的。因为优化是分优先级的,必须将最耽误功夫(总执行时间最长)的sql先优化,后面的小虾小蟹都不影响整体的运行。所以分析慢sql日志是需要借助工具的,不能光凭眼力看!这里的分析工具也比较多,比如mysqldumpslow、mysqlsla、pt-query-digest。下面我讲着重来将如何使用pt-query-digest来分析慢sql文件。

Percona Toolkit的安装

Percona Toolkit是一套工具箱,而pt-query-diges只是其中一个工具而已。Percona Toolkit官方的安装教程可以看这里,如果英文比较好的话。下面我以Centos为例来演示如何安装Percona Toolkit

  1. 安装仓库源

    1
    sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
  2. 直接安装Percona Toolkit

    1
    sudo yum install percona-toolkit
  3. 验证是否安装成功

    1
    pt-query-digest --version

使用pt-query-diges分析慢sql文件

单纯用起来还是比较简单的,执行下面的命令:

1
pt-query-digest 慢sql文件 > slow_log.result

slow_log.result这个文件就是pt-query-digest工具的分析结果。

pt-query-digest

打开slow_log.result里面你会发现所有的sql它都帮你归类汇总了。同类型的sql会归类到一起,统计出该类型sql的总执行时间。并且将所有的sql按照总执行时间的大小,从长到短排序。这个你就可以明显的知道,我们的慢sql优化重点在哪里。下面我来详细解释一下各个列的意思

  • rank:排名
  • Query ID :sql对应的id,方便你到下面找对应的sql
  • Response time:总的执行时间和所占的百分比
  • Calls:调用次数
  • R/Call:平均每次执行的响应时间
  • V/M:相应时间的方差
  • I:涉及的表

一般来说我们会挑总执行时间最长的先优化。那么具体到每一个sql,pt-qeury-digest也有详细的分析。下面来看这个sql:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
# Query 4: 0.02 QPS, 0.01x concurrency, ID 0xC0470B4D0549196F1E67E12E8D1A7CD9 at byte 10950451
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2020-12-08T00:45:09 to 2020-12-08T20:53:09
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 1553
# Exec time 8 759s 410ms 711ms 489ms 501ms 12ms 477ms
# Lock time 8 258ms 139us 380us 166us 224us 29us 152us
# Rows sent 1 11.75k 1 20 7.75 19.46 6.78 3.89
# Rows examine 28 473.62M 121.35k 313.47k 312.29k 298.06k 7.98k 298.06k
# Query size 16 2.62M 1.72k 1.75k 1.73k 1.69k 0 1.69k
# String:
# Databases jte_pms_business
# Hosts 10.104.7.141 (348/22%), 10.104.50.105 (323/20%)... 3 more
# Users jte_pms_business
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms ################################################################
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `jte_pms_business` LIKE 't_pms_resv_order_2018030718'\G
# SHOW CREATE TABLE `jte_pms_business`.`t_pms_resv_order_2018030718`\G
# SHOW TABLE STATUS FROM `jte_pms_business` LIKE 't_pms_resv_room_2018030718'\G
# SHOW CREATE TABLE `jte_pms_business`.`t_pms_resv_room_2018030718`\G
# EXPLAIN /*!50100 PARTITIONS*/
select
distinct
ro.id, ro.group_code, ro.hotel_code, ro.resv_order_code, ro.resv_person, ro.phone, ro.resv_order_type, ro.team_code, ro.team_name
from t_pms_resv_order ro
left join t_pms_resv_room rr on ro.resv_order_code = rr.resv_order_code
WHERE ro.group_code = '2223562'
and ro.hotel_code = '1234663'
and ro.pre_checkin_time >= '2020-12-08 00:00:00'
and ro.pre_checkin_time <= '2020-12-08 23:59:59'
order by
create_time DESC LIMIT 20\G

这里参数比较多,我们只挑比较重要的来看。

  • Exec time/max:最大执行时间,如果发现最大执行时间有比较长的,可能这个sql需要重点优化,不然可能搞垮数据库
  • Rows sent/max:最大返回行数,如果非常多超过5万行数据,可能就要考虑是不是这个sql条件有问题
  • Exec time/95%和Query_time distribution:这两个点都是查看执行时间的具体分布情况。如果大部分都超过2秒,则需要着重优化。

原文链接:https://www.jdkdownload.com/mysql_slow_sql.html