MySQL 查询分析

定位 MySQL 性能瓶颈主要有两种方法:慢查询与 explain 命令。

慢查询

慢查询,就是查询超过指定时间的 SQL 语句查询称为”慢查询”。 慢查询帮我们找到执行慢的 SQL,方便我们对这些 SQL 进行优化。

long_query_time 是用来定义慢于多少秒的才算”慢查询”。

查询 long_query_time 值:

show variable like 'long_query_time';

设置 long_query_time 值:

set long_query_time=2;	

开启慢查询

有两个方法开启慢查询日志:一是通过在配置文件 my.cnf 或 my.ini 中设置配置参数,二是可以通过命令行设置变量来即时启动慢查询日志 。

slow_query_log 设置是否开启慢查询日志。

slow_query_log_file 设置日志存放位置。

 

查看慢查询日志

MySQL 提供了 MySQLdumpslow 命令来分析慢查询日志。

MySQLdumpslow 的主要功能是统计不同慢 sql 的:

  • 执行次数(count)
  • 执行最长时间(time)
  • 累计总耗费时间(time)
  • 等待锁的时间(lock)
  • 发送给客户端的行总数(rows)
  • 扫描的行总数(rows)

 

进入 MySQL/bin 目录,输入 MySQLdumpslow -help 或–help 可以看到这个工具的参数。

  • -s,是表示按照何种方式排序,c、t、l、r 分别是按照执行次数、执行时间、等待锁时间、返回的记录数来排序,ac、at、al、ar 表示相应的平均值;
  • -r,是前面排序的逆序;
  • -t,是 top n 的意思,即为返回排序后前面多少条的数据;
  • -g,后边可以写一个正则匹配模式,大小写不敏感的;

比如,执行./MySQLdumpslow -s c -t 5 /data/MySQLData/MySQL-slow.log,得到执行次数最多的前 5 个查询。

 

explain 分析查询

EXPLAIN 关键字一般放在 SELECT 查询语句的前面,使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。这可以帮助分析查询语句效率低下的原因或是表结构的性能瓶颈。

通过 explain 命令可以得到:

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

 

Explain的用法

 

Explain tablename 或

Explain [EXTENDED] SELECT select_options

前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息。

首先看看 explain 的输出参数:

  • Id:本次 select 的标识符。在查询中每个 select 都有一个顺序的数值。
  • Select_type:select 类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。主要有这几种:
    • SIMPLE:这个是简单的 sql 查询,不使用 UNION 或者子查询。PRIMARY:子查询中最外层的 select。
    • UNION:UNION 中的第二个或后面的 SELECT 语句。
    • DEPENDENT UNION:UNION 中的第二个或后面的 SELECT 语句,取决于外面的查询。
    • UNION RESULT:UNION 的结果。
    • SUBQUERY:子查询中的第一个 SELECT。
    • DEPENDENT SUBQUERY:子查询中的第一个 SELECT,取决于外面的查询。
    • DERIVED:派生表的 SELECT(FROM 子句的子查询)。
  • Table:输出行所引用的表。
  • Type:联合查询所使用的类型。

type 显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说,得保证查询至少达到 range 级别,最好能达到 ref。

  • possible_keys:指出 MySQL 能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验 WHERE 子句,看是否引用某些字段,或者检查字段不是适合索引。
  • Key:显示 MySQL 实际决定使用的键。如果没有索引被选择,键是 NULL。
  • key_len:显示 MySQL 决定使用的键长度。如果键是 NULL,长度就是 NULL。文档提示特别注意这个值可以得出一个多重主键里 MySQL 实际使用了哪一部分。
  • Ref:显示哪个字段或常数与 key 一起被使用。
  • Rows:这个数表示 MySQL 要遍历多少数据才能找到,在 innodb 上是不准确的。
  • Extra:如果是 Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。

如果是 where used,就是使用上了 where 限制。

如果是 impossible where 表示用不着 where,一般就是没查出来啥。

如果此信息显示 Using filesort 或者 Using temporary 的话会很吃力,WHERE 和 ORDER BY 的索引经常无法兼顾,如果按照 WHERE 来确定索引,那么在 ORDER BY 时,就必然会引起 Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

 

上述讲的几种方法是用来定位 MySQL 的性能瓶颈,那定位出性能问题后,接下来就是对这些低效的 sql 语句进行优化。

发表评论

电子邮件地址不会被公开。 必填项已用*标注