执行计划详解:
一、概述
1、可以在select 语句前加EXPLAIN或者DESCRIBE,分析出来的效果一样
2、5.7以上才能用EXPLAIN update , delete,之前只能EXPLAIN select;
3、执行计划并未真正执行sql语句。每行数据代表一个单表的查询信息
二、字段分析:
1、id:
(1)表示当前sql有几个select,实际上显示的是查询优化器优化后,真正执行的sql里面有几个select;
如:
EXPLAIN SELECT * FROM user_points WHERE id in(SELECT id FROM admin);
查询优化器会优化成:
EXPLAIN SELECT * FROM user_points up,admin a WHERE up.id=a.id ;
看着有2个select,实际上只有一个;
(2)id的值越大,说明执行的优先级越高;
(3)优化:id的个数越多,代表查询的select越多,因此优化时可能通过减少id的个数来减少select的个数;
2、select_type: select在整个sql语句中扮演什么角色,
simple: 不使用union和子查询,使用了关联查询,还是simple;简单的
primary:最外层的select; 主要的,首要的
union:union中的第二个或最后一个select,不依赖于外部查询的结果集 两个表数据的合集
dependent union:union中的第二个或最后一个select,依赖与外部结构集
union result:临时表,union后的结果集查询,由于union是取两张表的并集,会生成一张临时表进行去重,这个类型就代表那个临时表,但union all不会去重,因此不会有这张临时表的查询计划
subquery:子查询,子查询中的第一个select,与最外层的select无关
dependent subquery:子查询中的第一个select,依赖外部查询结果集
derived:from里面有子查询的情况,mysql会递归执行这些子查询,把结果集放临时表
2、table:查询哪张表,有时是表的别名
3、partitions:分区
4、type:使用到的索引类型
system :针对系统表
const: 命中主键索引或者唯一二级索引,并且where语句使用等值常量匹配,即where 字段=常量,只可能匹配到一行数据,效率最高
如:
EXPLAIN SELECT * FROM population WHERE id=1; ---- 匹配到主键,且where后面是等于一个常量;
EXPLAIN SELECT * FROM t_pop_label WHERE pop_id=1 AND label_id=1; ---- 匹配到唯一二级索引,且where后面是等于一个常量;
eq_ref: 针对关联查询,
在此之前先搞清楚驱动表和被驱动表:进行关联查询的时候,mysql查询优化器会根据查询性能判定哪个表是驱动表,哪个表是被驱动表,与from后面的表的顺序无关,
一般情况下是数据量少的表(小表)驱动数据量大的表(大表),数据库少的表称为驱动表,数据量大的表被称为被驱动表;
当被驱动表(大表)是通过主键或唯一索引进行等值匹配的时候,被驱动表就是eq_ref类型:
(1)首先,通过mysql查询优化器判定哪个表是被驱动表;
(2)被驱动表通过主键或唯一索引与驱动表进行等值匹配,type就是eq_ref,
如:
EXPLAIN SELECT * FROM population pop,t_pop_label tpl WHERE pop.id=tpl.id;
(1)mysql通过查询优化器判定,t_pop_label是被驱动表,
(2)t_pop_label表只有一个主键id,和一个联合唯一索引:
idx_pop_id_label_id_unique pop_id, label_id Unique
(3)当WHERE pop.id=tpl.id; t_pop_label是通过主键id与驱动表关联,所以type是eq_ref;
(4)当WHERE pop.id=tpl.pop_id; t_pop_label不是通过主键id或唯一索引,而是tpl.pop_id与驱动表关联,tpl.pop_id只有二级索引(非主键)或普通索引(非唯一),所以type是ref;
ref: 有两种情况会出现ref,针对单表和关联查询
(1)单表:where 字段有二级索引且进行常量等值匹配,如:
id_card字段有普通二级索引(单列索引):
EXPLAIN SELECT * FROM population WHERE id_card='510122202005630035';
注意:如果id_card是与其他字段有联合索引,且id_card不是索引最左侧的字段,则会走全表扫描;
(2)关联查询,参考eq_ref中第(4)点
fulltext: 走的全文索引
ref_or_null: 在ref的情况下,单个允许字段匹配null值,如:
EXPLAIN SELECT * FROM population WHERE id_card='510122202005630035' or id_card is null;
index_merge:
在ref_or_null的情况下,where多个字段,且使用or查询;
EXPLAIN SELECT * FROM population WHERE id_card='510122202005630035' or `name` = 'abc';
多个字段进行or,且多个字段都有二级索引,type=index_merge说明进行了索引合并,而不是使用2个索引
unique_subquery: 针对子查询中的in()查询,
如果mysql的查询优化器认为in()查询必须优化为exist();
且子查询中的where条件与外部查询关联,
且子查询使用了主键进行等值匹配
range: 使用了了范围查询,< > in(),between and
index: 使用了覆盖索引,哪怕只是使用到覆盖索引中的一部分,没有进行回表查询
all:全表扫描,应该是指到聚簇索引表里面查找数据
阿里规范:sql优化的要求,至少达到range级别,要求是达到ref,最好是const;
5、possible_keys: 可能会使用到的索引
6、key:实际使用到的索引:
当type=ALL,key=null说明全表扫描,没用到索引
7、key_len: 使用的索引字节长度,越长说明索引索引越充分
8、ref: 关联信息,
当进行索引列等值查询时,ref=const;
当进行索引列关联查询时,ref=关联的库名.表名(别名).字段;
9、rows:表示扫描的数据行数