SQL慢？吐血整理MySQL中Explain分析工具

JAVA旭阳
2,442 阅读10分钟

欢迎关注MySQL专栏 MySQL历险记
强烈建议收藏本导航文【MySQL历险记】MySQL的核心特性汇总

前言

当我们遇到一条SQL慢的时候，往往需要使用一些性能分析工具，看看这个SQL究竟是为什么慢？那本文就分享MySQL中的一个性能分析神器——Explain, 通过读懂它的结果，我们可以知道SQL的执行计划，也就是具体执行查询的方式，比如多表连接的顺序是什么，或者查询是否用到索引，用到了哪些索引等等，这也是每个后端程序猿必备的技能。

Explain介绍

对于每个SQL来说，当它被客户端发送到Mysql服务端之后，会经过Mysql的优化器部件的分析，主要包括一些特殊的处理、执行顺序的改变以确保最优的执行效率，最终生成对应的执行计划。所谓的执行计划，实际就是在存储引擎层面如何获取数据的，是通过索引获取数据还是进行全表扫描获取数据，获取到数据后需不需要回表，等等，简单理解就是Mysql获取数据的过程。

值得注意的是，Explain生成的执行计划只是它认为的最优的数据检索方式，但不见得是你或者DBA认为是最优的。

如何使用

Explain使用非常简单，只要在要执行的SQL前面添加explain或者describe关键字即可：

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

比如下图所所示：

输出的上述信息就是所谓的执行计划。在这个执行计划的辅助下，我们需要知道应该怎样改进自己的查询语句以使查询执行起来更高效。

最最重要的是读懂下面的输出报告，下面我们一一详细解释。

输出结果解读

EXPLAIN 语句输出的各个列的作用如下：

字段含义
idselect查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type查询类型 或者是 其他操作类型
table正在访问哪个表
partitions匹配的分区信息
type访问方式
possible_keys显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
key实际使用到的索引，如果为NULL，则没有使用索引
key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered表示符合查询条件的数据百分比
Extra附加信息

输出结果中的id, type, possible_keys,key,rows,Extra字段都是非常重要的。

为了通过例子来解释，各个字段的含义，我们建立两个表，如下所示：

CREATE TABLE s1 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE s2 (
    id INT AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    INDEX idx_key1 (key1),
    UNIQUE INDEX idx_key2 (key2),
    INDEX idx_key3 (key3),
    INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
  • 表s1和s2字段和索引内容都是一样，就是表名不一样。
  • 每个表都创建了5个索引，包括唯一索引、普通索引、组合索引。

id☆

id表示SQL执行的顺序的标识, SQL从大到小的执行，id相同的情况，从上向下顺序执行。

  1. 连接2个表查询，会出现多条记录，id相同

  1. 类似子查询，有执行依赖关系的, id的序号会递增，id值越大优先级越高，越先被执行

table

table表示这一行的数据是关于哪张表的，有时不是真实的表名字, 可能是别名，也可能是中间表名等。

  1. 涉及几个表就会有几条记录

  • 这里s1和s2都比较好理解，都是表名
  • 这里的subquery2是一个临时表名，2是个数字,我的理解是第几步执行的结果

type☆

type是一个非常关键的字段，表示MySQL在表中找到对应行的方式，又称“访问类型”。

type的取值如下：

type值含义例子
NULL不访问任何表或者索引explain select 1;
system表只有一行记录，是const类型的特例
const针对主键或唯一索引的等值查询扫描, 最多只返回一行数据explain select * from s1 where id = 10002;
eq_ref用于联表查询，索引必须是主键或者唯一索引explain select * from s1 left join s2 on s1.id = s2.id;
ref使用普通索引进行单表或者联表查找,支持最左前缀原则explain select * from s1 where key1 = 'cxw';
fulltext全文索引
ref_or_null类似于ref,但是相比于ref额外多了去查NULL的数据explain select * from s1 where key1 = 'cxw' or key1 is null;
index_merge索引合并优化，表示一个查询里面用到了多个索引explain select * from s1 where key1 = 'cxw' or key3 = 'cxw2';
unique_subquery和eq_ref类似，但是使用了IN子查询，且子查询用的主键或者唯一索引EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a'
index_subquery和unique_subquery类似，唯一区别是子查询是用的是普通二级索引EXPLAIN SELECT * FROM s1 WHERE common_field IN (SELECT key3 FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
range范围扫描，表示检索了指定范围的行，主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
index遍历索引树，常见的就是“覆盖索引的场景”EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
ALL全表扫描， 性能最糟糕EXPLAIN SELECT * FROM s1;
  • 按照表格顺序，从上向下，性能从好到坏排序
  • 根据阿里巴巴开发手册要求，SQL性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 const级别。

key_len☆

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）。

可以用来检查是否充分的利用了索引，值越大越好，主要针对于联合索引，有一定的参考意义。

例子：对联合索引idx_key_part(key_part1, key_part2, key_part3) 搜索

mysql> EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

  • 看到key_len等于606，key1 VARCHAR(100) 一个字符占3个字节，100*3，是否为NULL占用一个字节，varchar的长度信息占两个字节，根据最左匹配原则，两个字段命中索引，所以key1 + key2等于606。

ref

ref表示将哪个字段或常量和key列所使用的字段进行比较。

举例：

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

rows☆

rows表示预估的需要读取的记录条数，值越小越好。

filtered

filtered表示符合查询条件的数据百分比，最大100。用rows × filtered可获得和下一张表连接的行数。例如rows = 1000，filtered = 50%，则和下一张表连接的行数是500。

例子：

  • 驱动表s1表的执行计划的rows列为9688，filtered列为10.00，这意味着驱动表s1的扇出值就是9688 x 10.00% = 968.8
  • 说明还要对被驱动表执行大约968次查询

Extra☆

Extra列展示了执行计划的一些额外信息，也是非常重要的。该列的内容有很多，这边挑选几个常见且重要的做个分享：

  1. Using index

数据查询的时候，数据库使用了覆盖索引，就是查询的列被索引覆盖，使用到覆盖索引查询速度会非常快。

例子：

  • 查询的列也是索引列key1, 不需要进行回表操作，这种情况叫做索引覆盖，显示了Using index
  1. Using where

查询时未找到可用的索引，但是有where条件过滤获取所需数据，注意并不是不是所有带where语句的查询都会显示Using where。

例子：

  1. Using temporary

表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。

例子：

  • common_field不是一个索引字段，所以会建立临时表进行group by
  • 执行计划中出现Using temporary并不是一个好的征兆，因为建立与维护临时表要付出很大的成本的, 我们可以为common_field建立索引，就不需要临时表了，直接使用索引结构。
  1. Using filesort

MySQL中无法利用索引完成的排序操作，只能在内存或者磁盘中进行排序，叫做Using filesort。

例子：

  • 使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为索引进行排序。
  1. Using join buffer

在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。

例子：

  • common_field字段没有添加索引，会通过内存块加快查询速度。
  • 如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
  1. Using index condition

有些搜索条件中虽然出现了索引列，但却不能使用到索引，会显示Using index condition。

例子：

  • 因为key1 like '%b'不满足最左匹配原则，无法用到索引，显示Using index condition。

possible_keys

表示哪些索引可以被Mysql的优化器进行选择，也就是索引候选者有哪些。

Key☆

key列显示MySQL实际决定使用的键。

select_type

select_type表示的执行计划的对应的查询是什么类型, 具体的取值如下：

查询类型作用
SIMPLE简单查询（未使用UNION或子查询）
PRIMARY最外层的查询
UNION在UNION中的第二个和随后的SELECT被标记为UNION。如果UNION被FROM子句中的子查询包含，那么它的第一个SELECT会被标记为DERIVED。
DEPENDENT UNIONUNION中的第二个或后面的查询，依赖了外面的查询
UNION RESULTUNION的结果
SUBQUERY子查询中的第一个 SELECT
DEPENDENT SUBQUERY子查询中的第一个 SELECT，依赖了外面的查询
DERIVED用来表示包含在FROM子句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table（派生表），因为该临时表是从子查询派生出来的
DEPENDENT DERIVED派生表，依赖了其他的表
MATERIALIZED物化子查询
UNCACHEABLE SUBQUERY子查询，结果无法缓存，必须针对外部查询的每一行重新评估
UNCACHEABLE UNIONUNION属于UNCACHEABLE SUBQUERY的第二个或后面的查询

partitions

表示分区表中的命中情况，非分区表，该项为NULL，不重要，略过。

总结

本篇文章主要讲解了Explain的使用，以及对Explain输出的执行计划做了一个详细的讲解。后续大家在遇到慢SQL的时候，可以根据SQL的执行情况，分析是否需要添加索引、优化SQL逻辑等手段进行优化。而且，在执行EXPLAIN时并没有真正的执行后面的语句，因此可以安全的查看执行计划。如果本文对大家有帮助的话，请随手留下一个赞吧。

参考

segmentfault.com/a/119000002…

www.itmuch.com/mysql/expla…

ost.51cto.com/posts/11856

本文正在参加「金石计划 . 瓜分6万现金大奖」

avatar
文章
阅读
粉丝