关于 MySQL 慢查询的介绍

慢查询

什么是慢查询

MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，阈值指的是运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10秒 以上的语句。默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数。

慢查询需要知道的 “点”

• 企业级开发中，慢查询日志是会打开的。但是这同样会带来一定的性能影响。

• 慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表

• 默认的阈值（long_query_time）是 10，这个显然不可用，通常，对于用户级应用而言，我们将它设置为 0.2

慢查询相关的变量

查看变量的 SQL 语句

SQL	含义	备注
show variables like ‘slow_query_log’;	是否开启慢查询日志	ON 表示开启；OFF 表示关闭
show variables like ‘slow_query_log_file’;	慢查询日志存储路径	根据 MySQL 版本不同而不同
show variables like ‘long_query_time’;	慢查询阈值	默认值是 10s，使用慢查询则一定需要更改
show variables like ‘log_queries_not_using_indexes’;	未使用索引的查询也被记录到慢查询日志中	OFF 表示关闭，通常不会被开启
show variables like ‘log_output’;	慢查询日志存储方式	默认值是 FILE，表示将日志存入文件；修改为 TABLE，表示将日志存入数据库（mysql.slow_log 表）
show global status like ‘Slow_queries’;	查看有多少条慢查询记录

关于变量的说明

• ** 修改变量可以使用命令：set global long_query_time = 0.2; （更常见的做法是修改 MySQL 的配置 my.cnf） **

• ** 日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源。所以，不要将慢查询日志记录到表中。 **

慢查询日志的分析工具（优化慢查询）

mysqldumpslow

MySQL 内置了工具 mysqldumpslow 用于解析 MySQL 慢查询日志，并打印其内容摘要。

• 使用语法

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql">mysqldumpslow <span style="color:#999999">[</span>options<span style="color:#999999">]</span> <span style="color:#999999">[</span>log_file <span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">.</span><span style="color:#999999">]</span>
</code></span></span>

• 常用选项（options）解释

-g pattern：只显示与模式匹配的语句，大小写不敏感。
-r：反转排序顺序。
-s sort_type：如何排序输出，可选的 sort_type 如下

  t：按查询总时间排序。
  l：按查询总锁定时间排序。
  r：按总发送行排序。
  c：按计数排序。
  at：按查询时间或平均查询时间排序。
  al：按平均锁定时间排序。
  ar：按平均行发送排序。

默认情况下，mysqldumpslow按平均查询时间（相当于-s at）排序。

-t N：是top n的意思，即返回前面多少条的数据。
-v：详细模式。

• 使用示例

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-shell"># 显示2条结果，且按照查询总时间排序，且过滤group by语句
mysqldumpslow -t 2 -s t -g "group by" slow_query_log_file

# 按照时间排序的前 10 条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” slow_query_log_file

# 返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 slow_query_log_file

# 可以结合 more 一起使用，避免一次显示过多 SQL 语句
mysqldumpslow -s r -t 20 slow_query_log_file | more

# 访问次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 slow_query_log_file

...
</code></span></span>

• mysqldumpslow 结果信息

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-shell">Count: 这种类型的语句执行了几次
Time: 这种类型的语句执行的最大时间
Lock: 这种类型语句执行时等待锁的时间
Rows: 单次返回的结果数

Count: 2  Time=3.21s (7s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost
代表的含义是: 执行了2次，最大时间是3.21s，总共花费时间7s，等待锁的时间是0s，单次返回的结果数是1条记录，2次总共返回2条记录。
</code></span></span>

EXPLAIN

MySQL 提供了 EXPLAIN 命令，可以对慢查询（SELECT）进行分析，并输出 SELECT 执行的详细信息。我们可以针对输出的信息对慢查询语句进行合理的优化。

• 使用方法

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql"><span style="color:#36bcd6">explain</span> <span style="color:#36bcd6">select</span> <span style="color:#a67f59">*</span> <span style="color:#36bcd6">from</span> ad_unit_it <span style="color:#36bcd6">where</span> it_tag <span style="color:#a67f59">like</span> <span style="color:#78ab12">'%球'</span><span style="color:#999999">;</span>
</code></span></span>

• EXPLAIN 输出信息及解释

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-sql">mysql<span style="color:#a67f59">></span> <span style="color:#36bcd6">explain</span> <span style="color:#36bcd6">select</span> <span style="color:#a67f59">*</span> <span style="color:#36bcd6">from</span> ad_unit_it <span style="color:#36bcd6">where</span> it_tag <span style="color:#a67f59">like</span> <span style="color:#78ab12">'%球'</span>G
<span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span> <span style="color:#ae81ff">1</span><span style="color:#999999">.</span> <span style="color:#36bcd6">row</span> <span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span><span style="color:#a67f59">*</span>
           id: <span style="color:#ae81ff">1</span>
  select_type: <span style="color:#36bcd6">SIMPLE</span>
        <span style="color:#36bcd6">table</span>: ad_unit_it
   partitions: <span style="color:#ae81ff">NULL</span>
         <span style="color:#36bcd6">type</span>: <span style="color:#36bcd6">ALL</span>
possible_keys: <span style="color:#ae81ff">NULL</span>
          <span style="color:#36bcd6">key</span>: <span style="color:#ae81ff">NULL</span>
      key_len: <span style="color:#ae81ff">NULL</span>
          ref: <span style="color:#ae81ff">NULL</span>
         <span style="color:#36bcd6">rows</span>: <span style="color:#ae81ff">3</span>
     filtered: <span style="color:#ae81ff">33.33</span>
        Extra: <span style="color:#36bcd6">Using</span> <span style="color:#36bcd6">where</span>
</code></span></span>

<span style="color:#1c1f21"><span style="color:#333333"><code class="language-shell">id:             查询的唯一标识符
select_type:    查询的类型
table:          查询的表
partitions:     匹配的分区
type:           join 类型
possible_keys:  查询中可能用到的索引
key:            查询中使用到的索引
key_len:        查询优化器使用了的索引字节数
ref:            哪个字段或常量与 key 一起被使用
rows:           当前的查询一共扫描了多少行（估值）
filtered:       查询条件过滤的数据百分比
Extra:          额外信息
</code></span></span>

• select_type: 最常见的查询类型是 SIMPLE， 这表示查询中没有子查询，也没有 UNION 查询

• type: 这个字段是判断查询是否高效的重要提示。可以判断查询是全表扫描还是索引扫描。例如: all 表示全表扫描，性能最差；range 表示使用索引范围扫描，通常是
  where 条件中带有数学比对的；index 表示全索引扫描，扫描索引而不扫描数据

• possible_keys: 表示查询时可能会使用到的索引，但是并不表示一定会使用。真正的使用了哪些索引，由 key 决定

• rows: MySQL 优化器会估算此次查询需要扫描的数据记录数（行数），这个值越小，查询效率越高

• Extra: 这是查询语句所对应的“额外信息”， 常见的有 :

  • Using filesort: 表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果。这样的查询应该是需要避免的，CPU 消耗很高。
  • Using where: 在查找使用索引的情况下，需要回表去查询所需的数据
  • Using index: 表示查询在索引树中就可查找所需数据，不用扫描表数据文件
  • Using temporary: 查询过程会使用到临时表，常见于 order by、group by、join 等场景，性能较低

为什么会产生慢查询 ？

• 两张比较大的表进行 JOIN，但是没有给表的相应字段加索引

• 表存在索引，但是查询的条件过多，且字段顺序与索引顺序不一致

• 对很多查询结果进行 GROUP BY

• …