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