MySQL的explain

2017-08-29 14:24 abce 阅读(396) 评论(0) 编辑收藏举报

explain命令的语法：

{EXPLAIN}
    [explain_type]
    {explainable_stmt | FOR CONNECTION connection_id}
 
explain_type: {
    EXTENDED　　　　　　　　　　　　# 在5.7.18中已经过期，后期版本会被移除(本测试是基于5.7.18)
  | PARTITIONS　　　　　　　　　　　# 在5.7.18中已经过期，后期版本会被移除(本测试是基于5.7.18)
  | FORMAT = format_name
}
 
format_name: {
    TRADITIONAL
  | JSON
}
 
explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

其中explain extended命令，显示sql语句的详细的查询执行计划，之后可以通过“show warnings”命令查看详细的信息；explain partitions命令，显示sql语句的带有分区表信息的查询执行计划。

explain可以解释的语句有：

1	`-select、delete、insert、replace、update`

示例：

mysql> explain  extended select count(distinct rs.id_)
    ->   from ac_ru_task rs
    ->  inner join ac_ru_execution are
    ->     on rs.proc_inst_id_ = are.id_
    ->  inner join ac_hi_procinst ahp
    ->     on rs.proc_inst_id_ = ahp.proc_inst_id_
    ->   left join ac_ru_identitylink i
    ->     on i.task_id_ = rs.id_
    ->  where (rs.assignee_ = '0019450815' or
    ->        (i.user_id_ = '0019450815' or
    ->        i.group_id_ in (select g.group_id_
    ->                            from ac_id_membership g
    ->                           where g.user_id_ = '0019450815')) or
    ->        rs.assignee_ in (select g.group_id_
    ->                             from ac_id_membership g
    ->                            where g.user_id_ = '0019450815'));
+----+-------------+-------+------------+--------+----------------------+---------------+---------+-------------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys        | key           | key_len | ref                     | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+----------------------+---------------+---------+-------------------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | rs    | NULL       | ALL    | AC_FK_TASK_PROCINST  | NULL          | NULL    | NULL                    |  235 |   100.00 | Using where                                        |
|  1 | PRIMARY     | are   | NULL       | eq_ref | PRIMARY              | PRIMARY       | 194     | abce.rs.PROC_INST_ID_   |    1 |   100.00 | Using index                                        |
|  1 | PRIMARY     | ahp   | NULL       | eq_ref | PROC_INST_ID_        | PROC_INST_ID_ | 194     | abce.rs.PROC_INST_ID_   |    1 |   100.00 | Using index                                        |
|  1 | PRIMARY     | i     | NULL       | ALL    | AC_FK_TSKASS_TASK    | NULL          | NULL    | NULL                    |  680 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  3 | SUBQUERY    | g     | NULL       | ref    | PRIMARY              | PRIMARY       | 194     | const                   |    1 |   100.00 | Using index                                        |
|  2 | SUBQUERY    | g     | NULL       | ref    | PRIMARY              | PRIMARY       | 194     | const                   |    1 |   100.00 | Using index                                        |
+----+-------------+-------+------------+--------+----------------------+---------------+---------+-------------------------+------+----------+----------------------------------------------------+
6 rows in set, 2 warnings (0.00 sec)
 
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| Note    | 1003 | /* select#1 */ select count(distinct `abce`.`rs`.`ID_`) AS `count(distinct rs.id_)` from `abce`.`ac_ru_task` `rs` join `abce`.`ac_ru_execution` `are` join `abce`.`ac_hi_procinst` `ahp` left join `abce`.`ac_ru_identitylink` `i` on((`abce`.`i`.`TASK_ID_` = `abce`.`rs`.`ID_`)) where ((`abce`.`are`.`ID_` = `abce`.`rs`.`PROC_INST_ID_`) and (`abce`.`ahp`.`PROC_INST_ID_` = `abce`.`rs`.`PROC_INST_ID_`) and ((`abce`.`rs`.`ASSIGNEE_` = '0019450815') or (`abce`.`i`.`USER_ID_` = '0019450815') or <in_optimizer>(`abce`.`i`.`GROUP_ID_`,`abce`.`i`.`GROUP_ID_` in ( <materialize> (/* select#2 */ select `abce`.`g`.`GROUP_ID_` from `abce`.`ac_id_membership` `g` where (`abce`.`g`.`USER_ID_` = '0019450815') ), <primary_index_lookup>(`abce`.`i`.`GROUP_ID_` in <temporary table> on <auto_key> where ((`abce`.`i`.`GROUP_ID_` = `materialized-subquery`.`group_id_`))))) or <in_optimizer>(`abce`.`rs`.`ASSIGNEE_`,`abce`.`rs`.`ASSIGNEE_` in ( <materialize> (/* select#3 */ select `abce`.`g`.`GROUP_ID_` from `abce`.`ac_id_membership` `g` where (`abce`.`g`.`USER_ID_` = '0019450815') ), <primary_index_lookup>(`abce`.`rs`.`ASSIGNEE_` in <temporary table> on <auto_key> where ((`abce`.`rs`.`ASSIGNEE_` = `materialized-subquery`.`group_id_`))))))) |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
 
mysql> 

解释说明：

1) id：          每个被独立执行的操作的标识，表示对象被操作的顺序；id值大，先被执行；如果相同，执行顺序从上到下。
2) select_type： 查询中每个select子句的类型。select_type有不同的取值：<strong>见下文附录一</strong>
3) table：       名字，被操作的对象名称，通常是表名，但有其他格式。
4) partitions：      匹配的分区信息（对于非分区表值为NULL）。
5) type：        表连接操作的类型。type有不同的取值：<strong>见下文 附录二</strong>
6) possible_keys：   备选的索引（列出可能被使用到的索引）。
7) key：     经优化器选定的索引；常用“ANALYZE TABLE”命令可以使优化器正确地选择索引。
8) key_len：     被优化器选定的索引键的长度，单位是字节。
9) ref：     表示本行被操作的对象的参照对象（被参照的对象可能是一个常量用“const”表示，也可能是其他表的key指向的对象）。
10) rows：       查询执行所扫描的元组个数（对于InnoDB，此值是个估计值）。
11) filtered：       按照条件表上数据被过滤的元组个数的百分比，“rows×filtered/100”可以求出过滤后的元组数即实际的元组数。

附录一：select_type的各种取值

--simple：       简单的select语句(不包含union和子查询操作)
--primary：      最外层的查询(如果两表union、或者有子查询，外层的表叫做primary，内层的表叫做union)
--union：        union操作中第二个或者之后的select语句，但是和最外层的查询没有关联；如果union包含子查询且在from中，union中的第一个select被标记为derived
--dependent：        在一个union中，第二个或者之后的select语句，但是依赖于最外层的查询
--union result： union的结果集
--subquery：     select列表中的子查询(即不是from子句中的子查询)
--dependent subquery：   子查询中的第一个select，但是依赖于外层查询
--derived：      from子句中的子查询(from子句中的子查询会产生一个临时表，因临时表是来自子查询，故被标记为derived)
--materialized： 物化视图子查询
--uncacheable subquery：子查询的结果不能缓存，每次执行外查询都要执行一边子查询
--uncacheable union：    union操作中，uncacheable subquery中的第二个之后的select查询

附录二：type的各种取值：

--system：       表只有一行记录（等于系统表）。这是 const表连接类型的一个特例
--const：        表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个const值。const表查询起来非常快，因为只要读取一次。const用在和primary key 或unique索引中有固定值比较的情形。    
--eq_ref：       每次和外表连接，只会读取内表中的一条记录。相比system和const，er_ref可能是最好的join类型了。主要用于有primary key或unique not null索引的join语句。即对内表做基于唯一索引的扫描，使得对外表的一条记录，内表只有唯一一条记录与之对应。
--ref：          内表基于索引扫描时，对应外表的一条记录可能有若干条记录与之对应。
--ref_or_null：  这种连接类型类似 ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录
--fulltext：     表连接使用了全文索引
--index_merge：      使用了索引合并
--unique_subquery：  在有in子查询的时候，基于唯一索引进行扫描，类似eq_ref
--index_subquery：   在有in子查询的时候，基于除唯一索引进行扫描，类似unique_subquery
--range：        范围扫描，基于索引范围扫描，为比如between、in、>=、like类操作提供支持
--index：        全索引扫描
--all：          全表扫描

需要关注的3个地方：
1、type = ALL
2、Extra中，出现 using filesort
using filesort说明没有办法利用现有索引进行排序，需要额外排序
建议：根据排序需要，创建相应合适的索引
3、Extra中，出现 using temporary
using temporary说明需要临时表存储结果集，通常是因为group by的列上没有索引。也可能是因为同时有group by和order by，但是group by和order by的列又不一样。