mysql优化利器之explain使用介绍

MySQL 优化利器之 Explain 使用介绍

什么是 Explain ？

Explain 是 MySQL 内置的一个用于分析查询语句的工具，在分析查询语句时，我们可通过 Explain 得到一份详细的优化建议。

Explain 使用方法

Explain 的语法如下：

EXPLAIN [EXTENDED] SELECT * FROM 表名 WHERE 条件;

在上述语句中，EXTENDED关键字是可选的，当加上此关键字时，会会显示更多详细信息。

解读 Explain 结果

Explain 的结果会输出一些列的路线（rows）。每一列路线代表了一个查询的执行过程。下面是 Explain 结果中常见列的含义：

id：查询序列号，每个 SELECT 查询都有一个独立的、唯一的 id 。
select_type：查询类型，有以下几种类型：
SIMPLE：简单 SELECT 查询，不包括子查询或 UNION 查询。
PRIMARY ：最外层的查询(主查询)。
UNION：UNION 中第二个或后面的查询语句。
DEPENDENT UNION：UNION 中第二个或后面的查询语句，依赖于外部查询的结果。
SUBQUERY：子查询中的第一个 SELECT。
DEPENDENT SUBQUERY：子查询中的第一个 SELECT，依赖于外部查询的结果。
DERIVED：派生表的 SELECT，包含在 FROM 子句内的子查询。
table：显示这一行的数据是关于哪张表的。
partitions：匹配到的分区表的信息，如果查询没使用分区表，则值为 NULL。
type：表连接类型或查询类型。
system：表只有一行记录（等同于 system 表），这是 const 类型的特例，平均来说，这个类型很少出现。
const：表中的一个记录的最大值能够匹配这个查询，这个类型通常出现于 primary key 或 unique 类型的索引中。
eq_ref：类似于 const，使用了索引，只有更高的查找速度。该类型通常出现在关联查询中。
ref：使用了非唯一性索引。
range：使用了索引，索引是用于查询某个范围值的。
index：全索引扫描，对于每一行都需要扫描整个索引，这个比全表扫描还慢，一般就是没用好索引。
all：全表扫描。
NULL：这个表示依赖于引擎，可能也表示 subquery （从属查询）中的第一个 select 或不包括在其他任何类型中。
possible_keys：查询时，可能会用到的索引。
key：实际用到的索引。
key_len：用到索引字段的长度。
ref：哪个字段或常量与 key 一起被使用。
rows：这个操作根据查询条件可能扫描到的表中行数，是一个估计值。
filtered：按照表统计信息估计的扫描行数所占的百分比。这个值是大约的，并不精确。
Extra：包含了 MySQL 在查询过程中的详细信息，通常会给出：
Using filesort：看到这个的时候，就意味着 MySQL 需要进行排序操作，这通常发生在查询中有 ORDER BY子句、GROUP BY 子句或者 DISTINCT 时。
Using temporary：出现这个说明 MySQL 需要使用一个临时表来存储中间结果。我的理解是：MySQL 需要将两个表的相关数据先汇总到一个临时表里，然后再进行后续的操作。
Using where：这个代表 MySQL 需要在获取存储引擎的数据后再进行一次筛选，也就是需要进行回表操作。
Using join buffer (Block Nested Loop) 说明使用的是Block Nested Loop 算法做Join 操作。前提是查询的左右表中都有符合关联条件的索引。
Using index：使用了覆盖索引（Covering Index），只访问了索引中的数据而没有通过索引去访问数据表。

Explain 实例说明

简单查询：

mysql> EXPLAIN SELECT * FROM users WHERE id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | users | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------------+

解读：

表示此语句仅针对一个表进行，表名为 users。
type 为 const，表示是一个常量查询。
key 列包含 PRIMARY，表示这是针对主键的查询。
多表关联查询：

mysql> EXPLAIN SELECT * FROM users AS u LEFT JOIN orders AS o ON u.id = o.user_id WHERE u.id = 1;
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | u     | const  | PRIMARY       | PRIMARY | 4       | const       |    1 |   100.00 | Using index |
|  1 | SIMPLE      | o     | ref    | user_id       | user_id | 4       | test.u.id   |    2 |   100.00 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------+------+----------+-------------+

解读：