mysql explain分析

目录

思维导图

id

select_type

SIMPLE

PRIMARY

SUBQUERY

DEPENDENT SUBQUREY

UNCACHEABLE SUBQUREY：

UNION

UNION RESULT

DERIVED

MATERIALIZED

table

partitions

type

ALL

index

range

ref

eq_ref

const

system

possible_keys

keys

key_len

ref

rows

filtered

Extra

---

思维导图

序号	列名	列名注释
1	字段	含义
2	id	操作标识符，从 1 开始递增。
3	select_type	查询类型，例如 SIMPLE、PRIMARY、SUBQUERY 等等。
4	table	操作的表名。
5	partitions	操作的分区。
6	type	操作的连接类型，例如 const、eq_ref、ref、range、index、ALL 等等。
7	possible_keys	操作可能使用的索引。
8	key	操作实际使用的索引。
9	key_len	操作使用的索引的长度。
10	ref	操作使用的索引的参考。
11	rows	操作返回的行数的估计值。
12	filtered	操作返回的行的过滤率。
	Extra	操作的额外信息，例如 Using where、Using index 等等。

id

表示查询中执行select子句或操作表的顺序，

id相同：执行顺序由上至下。

id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

id为NULL：最后执行。

id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好。

select_type

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

SIMPLE

简单查询。查询中不包含子查询或者UNION。

PRIMARY

主查询。查询中若包含子查询，则最外层查询被标记为PRIMARY。

SUBQUERY

子查询。在SELECT或WHERE列表中包含了子查询。

SQL示例：

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content= 'a');

DEPENDENT SUBQUREY

如果包含了子查询，并且查询语句不能被优化器转换为连接查询，并

且子查询是 相关子查询（子查询基于外部数据列） ，则子查询就是DEPENDENT SUBQUREY。

SQL示例：

EXPLAIN SELECT * FROM t3 WHERE id = ( SELECT id FROM t2 WHERE content = t3.content);

UNCACHEABLE SUBQUREY：

表示这个subquery的查询要受到外部系统变量的影响

UNION

对于包含UNION或者UNION ALL的查询语句，除了最左边的查询是PRIMARY，其余的查

询都是UNION。

UNION RESULT

UNION会对查询结果进行查询去重，MYSQL会使用临时表来完成UNION查询

的去重工作，针对这个临时表的查询就是"UNION RESULT"。

EXPLAIN SELECT * FROM t3 WHERE id = 1 UNION SELECT * FROM t2 WHERE id = 1;

DERIVED

在包含 派生表（子查询在from子句中） 的查询中，MySQL会递归执行这些子查询，把结果放在临时表里。

EXPLAIN SELECT * FROM ( SELECT content, COUNT(*) AS c FROM t1 GROUP BY content ) AS derived_t1 WHERE c > 1;

这里的 就是在id为2的查询中产生的派生表。

补充：MySQL在处理带有派生表的语句时，优先尝试把派生表和外层查询进行合并，如果不行，再把派

生表物化掉（执行子查询，并把结果放入临时表），然后执行查询。下面的例子就是就是将派生表和外层查

询进行合并的例子：

EXPLAIN SELECT*FROM(SELECT*FROMt1WHEREcontent='t1_832')ASderived_t1;

MATERIALIZED

优化器对于包含子查询的语句，如果选择将子查询物化后再与外层查询连接查询，

该子查询的类型就是MATERIALIZED。如下的例子中，查询优化器先将子查询转换成物化表，然后

将t1和物化表进行连接查询。

EXPLAIN SELECT*FROM t1 WHERE content IN(SELECT content FROM t2);

table

单表：显示这一行的数据是关于哪张表的

EXPLAIN SELECT * FROM t1;

多表关联：t1为驱动表，t2为被驱动表。

注意：内连接时，MySQL性能优化器会自动判断哪个表是驱动表，哪个表示被驱动表，和书写的顺序无

关

EXPLAIN SELECT * FROM t1 INNER JOIN t2;

partitions

代表分区表中的命中情况，非分区表，该项为NULL

type

此列表示关联类型或访问类型。也就是MySQL决定如何查找表中的行。依次从最优到最差分别为：

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

比较重要的system > const > eq_ref > ref > range > index > all。

SQL 性能优化的目标：至少要达到里巴巴开发手册要求 至少要达到range级别，要求是ref级别，最好是const级别

ALL

全表扫描。Full Table Scan，将遍历全表以找到匹配的行。

index

扫描全部索引获取到结果。

覆盖索引：如果能通过读取索引就可以得到想要的数据，那就不需要读取用户记录，或者不用再做回表

操作了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

range

通常出现在范围查询中，比如in、between、大于、小于等。使用索引来检索给定范围的行。这种范围扫描索引扫描比全表扫描要好，

因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

ref

不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引和某个值比较，会找到多个符合条件的行。

eq_ref

主键或者不允许NULL值的唯一键索引被连接使用，最多只会返回一条符合条件的记录。简单的select查询不会出现这种type。

const

根据主键或者唯一二级索引列与常数进行匹配时

system

MyISAM引擎中，当表中只有一条记录时。

possible_keys

possible_keys表示执行查询时可能用到的索引，一个或多个。查询涉及到的字段上若存在索

引，则该索引将被列出，但不一定被查询实际使用。

keys

表示实际使用的索引。如果为NULL，则没有使用索引。

key_len

表示索引使用的字节数，根据这个值可以判断索引的使用情况，检查是否充分利用了索引，针对联合索引值

越大越好。

ref

显示与key中的索引进行比较的列或常量。

rows

MySQL认为它执行查询时必须检查的行数。值越小越好。

filtered

最后查询出来的数据占所有服务器端检查行数（rows）的百分比。值越大越好。

Extra

包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来理解MySQL到底将如何执行当前

的查询语句。MySQL提供的额外信息有好几十个，这里只挑介绍比较重要的介绍。

Impossible WHERE：where子句的值总是false

Using where：使用了where，但在where上有字段没有创建索引

Using temporary：使了用临时表保存中间结果

Using filesort：在对查询结果中的记录进行排序时，是可以使用索引的

Using index：使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引

回表

Using index condition：叫作Index Condition Push down Optimization（索引下推优化）

如果没有使用索引下推（ICP）：那么MySQL在存储引擎层找到满足content1>'z'条件的第一

条二级索引记录。主键值进行回表，返回完整的记录给server层，server层再判断其他的搜索

条件是否成立。如果成立则保留该记录，否则跳过该记录，然后向存储引擎层要下一条记录。

如果使用了索引下推（ICP）：那么MySQL在存储引擎层找到满足content1>'z'条件的第

一条二级索引记录。不着急执行回表，而是在这条记录上先判断一下所有关于idx_content1

索引中包含的条件是否成立，也就是content1>'z'ANDcontent1LIKE'%a'是否成

立。如果这些条件不成立，则直接跳过该二级索引记录，去找下一条二级索引记录；如果这些

条件成立，则执行回表操作，返回完整的记录给server层。