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

前言

当我们遇到一条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​​ 语句输出的各个列的作用如下：

输出结果中的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相同

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

table

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

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

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

type☆

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

type的取值如下：

• 按照表格顺序，从上向下，性能从好到坏排序
• 根据阿里巴巴开发手册要求，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​​

2. Using where

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

例子：

3. Using temporary

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

例子：

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

4. Using filesort

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

例子：

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

5. Using join buffer

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

例子：

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

6. Using index condition

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

例子：

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

possible_keys

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

Key☆

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

select_type

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

partitions

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

总结

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