Skip to main content

MySQL 慢查询

什么是慢查询

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

慢查询需要知道的 “点”

  • 企业级开发中,慢查询日志是会打开的。但是这同样会带来一定的性能影响。
  • 慢查询日志支持将日志记录写入文件,也支持将日志记录写入数据库表
  • 默认的阈值(long_query_time)是 10,这个显然不可用,通常,对于用户级应用而言,我们将它设置为 0.2

慢查询相关的变量

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 慢查询日志,并打印其内容摘要。

使用语法

mysqldumpslow [options] [log_file ...]

常用选项(options)解释

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

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

默认情况下,mysqldumpslow按平均查询时间(相当于-s at)排序。

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

使用示例

# 显示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

mysqldumpslow 结果信息 Count: 这种类型的语句执行了几次 Time: 这种类型的语句执行的最大时间 Lock: 这种类型语句执行时等待锁的时间 Rows: 单次返回的结果数

EXPLAIN

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

使用方法

explain select * from ad_unit_it where it_tag like '%球';

EXPLAIN 输出信息及解释

mysql> explain select * from ad_unit_it where it_tag like '%球'G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ad_unit_it
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where

id: 查询的唯一标识符 select_type: 查询的类型 table: 查询的表 partitions: 匹配的分区 type: join 类型 possible_keys: 查询中可能用到的索引 key: 查询中使用到的索引 key_len: 查询优化器使用了的索引字节数 ref: 哪个字段或常量与 key 一起被使用 rows: 当前的查询一共扫描了多少行(估值) filtered: 查询条件过滤的数据百分比 Extra: 额外信息

  • 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