【数据库】EXPLAIN

https://www.cnblogs.com/gomysql/p/3720123.html

 

Explain 查看优化器如何执行查询的主要方法， 可以了解MySQL基于开销的优化器

 

查询结果包含的列有：

 

id | select_type  | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra  

1. id： 表示查询表中执行select语句的操作表顺序

ID相同，执行顺序从上至下。 

如果是子查询，ID序号会增加，ID 越大优先级越高，越先被执行

2. select_type 表示每个select子句的类型（简单or复杂）

 

a. SIMPLE 查询中不包含子查询或者 UNION

b. 查询中包含任意复杂的子部分， 最外层查询被标记为： PRIMARY

c. 在SELECT或者 WHERE 列表中包含了子查询，该子查询被标记为： SUBQUERY

d. 在FROM列表中包含在子查询被标记为，DERIVED（衍生） 用来表示包含在 from 字句中的select， MySQL会将递归执行的结果放到一个临时表（服务器内部称为派生表， 因为该表是从子查询中派生出来的）中

e. 若第二个Select 出现在UNION之后，被标记位UNION，若UNINO出现在FROM语句中，外层SELECT被标记位 DERIVED

f. 从UNION表中获取结果的SELECT被标记为UNION RESULT

 

(root@yayun-mysql-server) [test]>explain select d1.name, ( select id from t3) d2 from (select id,name from t1 where name='')d1 union (select name,id from t2);

+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+

| id | select_type  | table      | type   | possible_keys | key  | key_len | ref  | rows | Extra                    |

+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+

|  1 | PRIMARY      | <derived3> | system | NULL          | NULL | NULL    | NULL |    0 | const row not found      |

|  3 | DERIVED      | t1         | ref    | name          | name | 63      |      |    1 | Using where; Using index |

|  2 | SUBQUERY     | t3         | index  | NULL          | age  | 5       | NULL |    6 | Using index              |

|  4 | UNION        | t2         | index  | NULL          | name | 63      | NULL |    4 | Using index              |

| NULL | UNION RESULT | <union1,4> | ALL    | NULL          | NULL | NULL    | NULL | NULL |                          |

+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+

rows in set (0.00 sec)

# 第一行：id列为1，表示第一个select，select_type列的primary表 示该查询为外层查询，table列被标记为<derived3>，表示查询结果来自一个衍生表，其中3代表该查询衍生自第三个select查询，即id为3的select。

# 第二行：id为3，表示该查询的执行次序为2（ 4 => 3），是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。

# 第三行：select列表中的子查询，select_type为subquery，为整个查询中的第二个select。

# 第四行：select_type为union，说明第四个select是union里的第二个select，最先执行。

# 第五行：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。

SUBQUERY 和 UNION 还会被标记为 DEPENDENT 和 UNCHANGABLE

DEPENDENT 意味着 select 依赖于外层查询中发现的数据

UNCHANGABLE 意味着 select 中的某些特性阻止被缓存在一个 item_cache 中

 

3. type 表示 MySQL在表中查询时候所需要的方式， 又称为“访问类型”

类型包括

ALL， index， range， ref， eq_ref， const， system， NULL

从左到右 性能从低到高

a. ALL: Full Table Scan MySQL 遍历全表找到匹配的行

 

explain select * from t_device;

 

b. index : Full index Scan 遍历索引树

 

explain SELECT id from t_device;

c. range: 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行（一般是索引范围性扫描，带有 between 或者 where 字句中带有 < > 的 或者  in or

 

d. ref: 使用非唯一索引扫描，返回匹配某个单独值的记录行

 

explain select * from t1 where name='yayun'

 

e. eq_ref: 类似于 ref 区别是使用的索引是唯一索引， 对于每个索引值，表中只有一条记录。（多表连接中的pk 或者 unique key 作为关联条件）

 

explain select t1.name from t1, t2 where t1.id=t2.id;

f. const、System: 当MySQL对查询某部分优化时，并转化为一个常量，使用这些类型访问。 如果将主键置于where中，MySQL就能转化为常量

 

explain select * from ( select * from t1 where id=1)b1;

g. NULL： MySQL在优化过程中分解语句，执行中不用访问索引或者表，例如计算主键最小值，可以通过单独索引计算完成

 

explain select * from t1 where id = (select min(id) from t2);

 

4. possible_keys

指的是，MySQL使用哪个索引可以在表中查找到记录，查询涉及到的字段存在索引，则列出（可能不使用）

 

5. key

显示MySQL在查询中使用的索引，如果没有索引，则显示NULL

 

6. key_len

索引中使用到的字节数，可以通过列计算查询中使用的索引长度（key_len 显示的值为索引字段的最大长度，并非实际使用长度， 也就是根据表的定义计算而得）

 

7. ref

表示上述表的连接配置条件，即哪些列或常量用于查找索引列上的值

 

8. rows

表示 MySQL 根据表统计及索引选用情况，估算的找到所需要记录所需要读取的行数

 

9. Extra

a. Using index 该值表示相对应的select操作中使用了覆盖索引

    覆盖索引： 如果一个索引包含或覆盖了查询所需要的查询字段的值， 只需要扫描索引， 不需要回表

 

b. Using where 

    表示MySQL 服务器在存储索引之后再进行过滤。 许多where条件设计索引的列（不是所有的where 都会有 using where），需要回表查询

 

c. Using temporary

    表示 MySQL 需要临时表来存储结果数据，常见排序和分组

 

d. Using filesort

    mysql 无法使用索引完成的排序操作，

 

e. Using join buffer

    强调在获取连接时候没有使用索引

 

f. Impossible where

    强调了where会导致没有符合条件的行

 

g. Select tables optimized away

    意味着通过索引，优化器从聚合函数中返回一行

 

h. Index merges

    当 MySQL在给定的表上使用超过两个索引时候，

 

    Using sort_union(...)

    Using union(...)

    Using intersect(...)

 

 

 

总结：

• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况

• EXPLAIN不考虑各种Cache

• EXPLAIN不能显示MySQL在执行查询时所作的优化工作

• 部分统计信息是估算的，并非精确值

• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。