Mysql 慢查询日志分析篇

最新推荐文章于 2024-07-23 09:37:51 发布

Codeages

最新推荐文章于 2024-07-23 09:37:51 发布

阅读量898

点赞数

文章标签： Mysql 慢查询 EduSoho

本文链接：https://blog.csdn.net/weixin_43757847/article/details/84563407

版权

前言

有时我们会发现网站访问十分缓慢甚至于卡死，那么有哪些原因会导致这个问题呢？

MySQL 语句没有进行优化，数据库的读写耗费了大量的时间
可能访问的网页中存在调用远程（第三方）接口的业务，远程接口返回特别慢
大量的访问请求导致带宽撑满
服务器负载过大忙不过来，比如说 CPU 和内存消耗完了
本地网络波动
很多情况下经过排查我们会发现导致网站访问慢的原因在于数据库瓶颈，下面我们来讲讲对应数据库中的慢查询导致的访问慢这种情况如何排查和解决。

什么是慢查询日志

MySQL的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。
所以通过慢查询日志，我们就可以知道到底哪些 sql 语句导致了网页响应速度慢，再根据这些 sql 语句找到哪些具体的业务场景的响应速度特别慢，从而做出响应的优化和调整。
官方 slow_query_log 文档

开启慢查询日志

在开启慢查询日志前，我们要先查看 mysql 对慢查询日志的配置:

show variables  like '%slow_query_log%';

慢查询配置中的各个字段解释：

列名	说明
slow_query_log	是否开启慢查询日志，1表示开启，0表示关闭。
slow_query_log_file	MySQL数据库慢查询日志存储路径。
long_query_time	慢查询阈值，当查询时间多于设定的阈值时，记录日志。
log_queries_not_using_indexes	未使用索引的查询也被记录到慢查询日志中（可选项）
log_output	日志存储方式 log_output=’FILE’表示将日志存入文件，默认值是’FILE’

注意 long_query_time：一般我们对慢查询进行优化的时候，会逐步减小这个值（3s->2s->1s），每次减小后观察慢查询日志的情况来调优，以做到尽可能的优化数据库查询速度

开启慢查询的有两种方式：

临时性，mysql 中输入如下命令，重启 mysql 后会失效

set global slow_query_log=1;

修改 my.cnf 文件，需要重启 mysql 才能生效
(查看mysql启动时读取配置文件的默认目录 mysql --help|grep ‘my.cnf’)
修改配置文件中的如下字段后，重启mysql。

slow_query_log =1
slow_query_log_file=/tmp/mysql_slow.log
long_query_time = 3

当 mysql 服务在线上运行时无法重启时，可以采用第一种方式。

慢查询分析

正所谓工欲善其事必先利其器，在进行慢查询的分析和优化之前，我们要先学习两个分析工具。

日志分析工具 pt-query-digest

开启慢查询日志后，数据会记录在 slow_query_log_file 配置的文件中。产生的 slow_query_log 原始数据，推荐使用 pt-query-digest 进行分析。

pt-query-digest 是用于分析 mysql 慢查询的一个工具，它可以分析 binlog、General log、slowlog，也可以通过 SHOWPROCESSLIST 或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

pt-query-digest简介

pt-query-digest 的安装

#mac
brew install percona-toolkit

#ubuntu
sudo apt-get install percona-toolkit

#执行完上面后，执行下面
wget http://www.percona.com/downloads/percona-toolkit/2.2.4/percona-toolkit-2.2.4.tar.gz
tar percona-toolkit-2.2.4.tar.gz
cd percona-toolkit-2.2.4
perl Makefile.PL
make && make install

官方安装方法

pt-query-digest 的使用

这里只介绍最基本的用法
在终端输入：

pt-query-digest slow.log > slow_log_reports

该命令将slow_log 中的数据分析后导入至 slow_log_reports
pt-query-digest文档

pt-query-digest结果分析

使用 pt-query-digest 进行分析之后，生成的表报分为两部分： - 首先展示总体的慢查询情况，包括慢查询的总体时间和数量，慢查询语句的分布和排行

# Attribute       total   min    max    avg     95%  stddev  median
# ============   ====== ===== ====== ====== ======= ======= =======
# Exec time        715s    2s    15s     3s      8s      2s      3s
# Lock time        16ms  35us  955us   78us   125us    71us    63us
# Rows sent       1.06k     1     30   5.29    9.83    5.19    0.99
# Rows examine  305.79M 3.47k  2.65M  1.48M   2.62M   1.27M   2.62M
# Query size     60.05k    61 15.72k 298.50  234.30   1.07k  212.52

# Profile
# Rank Query ID                     Response time Calls R/Call V/M   I
# ==== ============================ ============== ==== ====== ===== =
#    1 0x14E439779C808EC504E181181D 400.4732 56.0%   84 4.7675 1.64 SELECT status
#    2 0x304061A22C6FD43505EA5E3A15 263.8186 36.9%  108 2.4428 0.05 SELECT course_lesson_learn
#    3 0xF3B5D5C8166183BAC01523D6C6  10.3960  1.5%    4 2.5990 0.04 SELECT testpaper_result
#    4 0x11B5BFA8FE52965EF9687FFB28   8.4920  1.2%    2 4.2460 0.11 SELECT course_lesson_learn
# MISC 0xMISC                        31.5240  4.4%    8 3.9405  0.0 <7 ITEMS>
total:总计
min:最小
max:最大
avg:平均
95%:95%的查询时间，重点分析
median:中位数，把所有值从小到大排列，位置位于中间那个数

其次展示具体语句的时间分布:

# Attribut   pct   total     min   max     avg     95%  stddev  median
# ========== === ======= ======= ===== ======= ======= ======= =======
# Count       40      84
# Exec time   56    400s      2s   15s      5s     10s      3s      4s
# Lock time   31     5ms    35us 128us    60us    93us    18us    54us
# Rows sent   77     840      10    10      10      10       0      10
# Rows examine 0   1.74M   3.47k 74.54k  21.20k  44.45k  14.13k  17.59k
# Query size  25  15.61k     144   249  190.31  223.14   26.46  183.58
# String:
# Databases    edusoho
# Hosts        172.17.75.2
# Users        esuser
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ###########################################################
#  10s+  ####
# Tables
#    SHOW TABLE STATUS FROM `edusoho` LIKE 'status'\G
#    SHOW CREATE TABLE `edusoho`.`status`\G
# EXPLAIN /*!50100 PARTITIONS*/

日志分析工具 Explain命令

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化。所以我们需要先了解 explain 命令的使用及相关参数说明。

#usage
explain SELECT * FROM testpaper WHERE target IN ('course-7169','course-7101')\G;

#result
           id: 1
  select_type: SIMPLE
        table: testpaper
   partitions: NULL
         type: range
possible_keys: target
          key: target
      key_len: 92
          ref: NULL
         rows: 38
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

列名	说明
select_type	显示本行是简单或复杂 select。如果查询有任何复杂的子查询，则最外层标记为 PRIMARY
table	访问引用哪个表
type	数据访问/读取操作类型（ALL、index、range、ref、eq_ref、const/system、NULL）
possible_keys	揭示哪一些索引可能有利于高效的查找
key	显示 mysql 决定采用哪个索引来优化查询
key_len	显示 mysql 在索引里使用的字节数
ref	显示了之前的表在 key 列记录的索引中查找值所用的列或常量
rows	为了找到所需的行而需要读取的行数，估算值，不精确

上表对每个 explain 结果的每个字段作了大致说明，下面我们详细讲讲实际分析中部分字段的作用。

type：指访问类型，是一个比较重要的指标。

ALL ：查询的最差情况，说明数据库通过扫描全表的方式得出了结果。在数据量十分庞大时需要的时间很高
index：按照索引次序进行的全表扫描，避免了排序的开销。
range：在一个范围内的索引扫描。
ref：一种索引扫描，它返回所有匹配某个单个值的行。
eq_ref：使用唯一性索引或主键查找，最多只返回一条符合条件的记录。
const：当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快

结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。
key_len：显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好。
Extra：显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。

Using filesort：通过索引生成了有序的结果
Using temporary：用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了
Using index：说明查询是覆盖了索引的，不需要读取数据文件，从索引树（索引文件）中即可获得信息。如果同时出现using where，表明索引被用来执行索引键值的查找，没有using where，表明索引用来读取数据而非执行查找动作。这是MySQL服务层完成的，但无需再回表查询记录。
Using where：使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。注意：Extra列出现Using where表示MySQL服务器将存储引擎返回服务层以后再应用WHERE条件过滤。

explain详细介绍

上一篇浅谈Scrum（一）-- 什么是Scrum？
下一篇 Mysql 慢查询日志优化篇

EduSoho官网 https://www.edusoho.com/
EduSoho开源地址 https://github.com/edusoho/edusoho
我们正在寻找开发者