MySQL Explan执行计划详解

Explan执行计划

首先我们采用explan执行计划执行一条sql，发现返回了12个列，下面会详细解释每一列

1、ID列

id列的值是代表了select语句执行顺序，是和select相关联的；id列的值大的会优先执行，如果id列为空最后执行，id列相同，则从上到下以此执行

2、select_type列

代表查询的类型，有如下几个值：

2.1 simple：

不包含子查询和join关键字

explan select * from home_attr

2.2 primary:

复杂查询最外层select语句或者union语句中最左边的select

EXPLAIN select *,(SELECT id from home_group where home_id = 8955) from home_attr;

最外层查的表就是home_attr，所以home_attr对应的就是PRIMARY

2.3 subquery：

仅限在from面前的select语句，不包括select后面的雨具

EXPLAIN select *,(SELECT id from home_group where home_id = 8955) from home_attr;

2.4 derived：

衍生表，如果from子句后面包含select语句，则会产生这种类型，他会把中间结果存放在临时表中，但是在5.7中需要使用 set session optimizer_switch='derived_merge=off';关闭mysql对衍生表的合并优化，我们先看下不关闭之前，我们执行如下sql的情况：

EXPLAIN select (select 1 from home_attr where id = 8955) from (select * from home_group where id = 1) der;

发现查询类型没有derived，我们关闭优化看下

set session optimizer_switch='derived_merge=off';
EXPLAIN select (select 1 from home_attr where id = 8955) from (select * from home_group where id = 1) der;

发现出现了derived的查询了

2.5 union

在union中的第二个和随后的select

EXPLAIN select 1 union select 2 union select 3

3、table列

table列代表当前select语句正在查询那张表

EXPLAIN select id from home_attr union select id from home_group

4、type列(主要是查看sql有没有使用索引，重要的字段之一)

type列的值分别为：

NULL >system >const >eq_ref >ref >range >index >ALL，执行效率依次递减

ALL: 表示需要对全表进行全表扫描，即对表中的每一行进行检查。这是最慢和最耗费资源的访问类型。
INDEX: 表示通过索引进行扫描，但仍需要读取索引中的数据行，通常结果比ALL快。
RANGE: 表示通过索引进行范围扫描，即只扫描满足一定条件范围内的数据。
REF: 表示使用非唯一索引来查找匹配某个常数值的行，通常用于连接操作。
EQ_REF: 类似于REF，但是使用的是唯一索引。
CONST: 表示通过常量条件获取单个行，这是最好的情况
system: 表示该查询只会返回一行结果，这是一个非常快速的访问类型。通常情况下，system类型在对系统表或者虚拟表进行查询时出现。系统表是MySQL内部使用的表，通常存储了关于数据库本身的元数据信息。由于系统表的设计使得查询非常快速，因此system访问类型代表着高效的查询操作
NULL: 表示无法归类到任何其他访问类型，可能是由于查询中包含了子查询或者函数等复杂操作而导致无法精确分类。在某些情况下，NULL类型可能意味着需要进一步优化查询以避免性能问题

4.1 NULL：

代表查询在mysql能够在优化阶段分解查询语句的时候直接能完成，不需要查询表和索引，例如获取逐渐最大列或最小列：

EXPLAIN select min(id),max(id) from home_attr

Select tables optimized away：代表从表中就能获取结果

4.2 system：

如果要达到system级别，那么它必须要达到以下几个条件：

1、是系统表或者是临时表

2、表中有且只有一条记录

我们在mysql数据库中找到了proxies_priv表，我们来看执行以下sql：

EXPLAIN select * from proxies_priv

可以看出已经到了system级别：

还有一种情况就是：派生表(临时表)

4.3 const：

当where后面是一个主键或者唯一索引与一个常量精确比较时，mysql会把查询优化为常量查询，执行以下sql：

EXPLAIN select * from home_attr where id = 8955

我们可以看一下mysql内部进行了什么样的优化：

EXPLAIN select * from home_attr where id = 8955; show WARNINGS;

可以看出mysql直接将其转换为常量进行查询

4.4 eq_ref：

主键或者唯一索引与其它表或字段进行关联查询，最多只会返回一条记录，如下代码：

EXPLAIN select * from home_attr right join home_loan on home_attr.id = home_loan.home_id

可以看出访问home_loan表的时候，type达到了eq_ref级别，因为home_id字段在home_loan表中是唯一的，所以查询home_loan表的时候按照home_id查询只会有一条记录与其关联

4.5 ref：

相对于eq_ref，ref只需要要求是普通索引或者联合索引的前缀匹配

普通索引查询

EXPLAIN select * from home_attr where home_title = '整租·浦东大道2511弄 2室1厅南';

联合索引前缀匹配

EXPLAIN SELECT home_attr.id FROM home_loan left join home_attr USE INDEX (index_name) ON home_loan.home_id = home_attr.id;

4.6 range：

范围索引，通常为in、> < >= 这样的比较符，会达到range级别：

EXPLAIN select * from home_attr where home_attr.id > 8955

4.7 index：

扫描全表索引：所查询的列都创建了索引，但是没有按照索引字段过滤(除了让索引失败的操作除外)

4.8 all：

扫描全表，通常情况下，是没有创建索引，需要增加索引优化

EXPLAIN select * from home_attr

5、possible_keys列

这一列显示查询可能使用哪些索引来查找。explan时可能出现possible_keyss这列，而key显示NULL的情况，这种情况是因为表中的数据不多，mysql认为索引对此查询帮助不大，选择了全表查询，如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查where子句看是否可以创建一个适当的索引来提高查询性能，然后用explan查看效果。