3、Explan执行计划

目录

 explain中的列说明

        1、id

        2、select_type

​        3、table列  

         4. type列

                NULL:

                system,const:

                eq_ref

                ref

                range

                index

                ALL

possible_keys列

6. key列

7.key_len列

8. ref列

9、rows列

10. Extra列

using index:

using where:

Using index condition：

Using temporary:

using filesort:

Select tables optimized away

---

是Mysql提供用于分析sql执行性能的工具，如果在sql前使用explan，mysql会对这次查询打上标记，不会真实执行查询，而是模拟sql优化器返回执行计划信息。

explain select * from bus_user;

 explain中的列说明

        1、id

        id代表的是select的顺序，id越大执行优先级越高，id相同则从上往下执行。

        2、select_type

        表示对应行是简单还是复杂的查询,共有5中类型：

SIMPLE：简单查询，不包含子查询和union语句

PRIMARY：复杂查询最外层的表

DERIVED：from后的临时表

SUBQUERY：select后的临时表

explain select id, name, 
(select count(*) from bus_order where bus_order.user_id = t1.id) as order_num 
from (select * from bus_user where age>18)t1;

UNION：在union后的select

explain select id from bus_user UNION select id from bus_order

        3、table列  

select 正在访问的表名，如果有子查询的时候，会显示derivedN，其中N表示依赖id=N的查询结果,如果有UNION查询，会显示<union X,Y>，其中X和Y表示参与union的select行id

         4. type列

判断sql效率最重要的列，按sql效率，null》system》const》eq_ref》ref》range》index》all，一般来说需要保证查询达到range级别，最好达到ref级别

                NULL:

mysql能够在优化阶段直接获得结果，在执行阶段不需要再访问表和索引，例：直接通过索引就可以得到最小id，不需要在执行阶段访问表

explain select max(id) from bus_user;

                system,const:

用到主键索引或者唯一索引时，可以将sql的部分进行优化转换成常量，最多匹配1行记录，速度最快。system是const的特例，当表里只有一条数据匹配时为system

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

show warnings;

                eq_ref

 通过主键或唯一索引，进行关联查询，最多只会返回一条符合条件的记录

explain select bus_user.id from bus_user left join bus_order on bus_user.id=bus_order.id

                ref

 使用普通索引或者使用满足唯一联合索引的左列原则，可能返回多条符合条件的记录

explain  select * from bus_user where name = '123'

 班级（class）和学号（num）组成联合唯一索引，使用左列原则查询class：

explain  select name from bus_user where class_ = '123'

                range

使用like 'a%'， in, between,>,<,>=等操作中，使用一个索引来检索给定范围的行

explain  select name from bus_user where name like '1%'

                index

 全表扫描，但是二级索引的字段已经包含需要返回的字段，不需要回表查询，属于覆盖索引

explain  select name from bus_user 

                ALL

全表扫描，扫描聚簇索引的所有叶子节点，性能最差

explain  select * from bus_user 

possible_keys列

这一列显示的是可能使用到的索引，但是只实际执行中不一定会被使用，这个取决于sql优化器的判断，比如is null 或者is not null，not in这种对实际检索帮助不大的索引，mysql认为还不如全表扫描来的来，就会使用全表扫描。

explain  select * from bus_user where id is not null

6. key列

在sql执行过程中会使用到的索引，如果这一列为null，建议优化sql或者创建合适的索引。在某些情况下possible_key有值，而key没有值一般是优化器任务索引的帮助性不大，如果你任务优化器的判断有误，你也可以使用force index强制使用索引或ignore index强制忽略索引。

7.key_len列

这是非常重要的一列，带表使用到的索引字节数，可以根据这个值判断用到了联合索引中的哪几个列

explain  select name from bus_user where class_ = '123'

explain  select name from bus_user where class_ = '123' and num_='111'

key_len计算规则如下： 

        字符串，char(n)，n均代表字符数，而不是字节数，utf-8中一个汉字占3个， 3n 字节

        varchar(n)，是 3n + 2个字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

        tinyint：1字节

        int：4字节

        bigint：8字节

        date：3字节

        datetime：8字节

        timestamp：4字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

8. ref列

        索引使用的资源引用，可能是列名也可能是常量

explain select bus_user.id from bus_user left join bus_order on bus_user.id=bus_order.id

9、rows列

 这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数，因为并没有具体执行，数值并不准确

10. Extra列

这一列展示的是额外信息。常见的重要值如下：

using index:

        覆盖索引：使用二级索引检索，且不需要回表，属于理想状态

explain  select name from bus_user where name = '123'

using where:

        没有用到索引，这种情况一般建议优化索引

explain  select *from bus_user where update_time = '123'

Using index condition：

        对索引使用了范围查询，例如>,<.between,like

explain  select * from bus_user where name like '123%'

Using temporary:

        使用了临时表来排列收集的数据，此时建议索引优化

explain  select distinct update_time from bus_user 

explain  select distinct name from bus_user 

using filesort:

需要特别注意，使用了非索引字段进行排序，如果数据少则在内存中排序，如果数据量大可能会使用在磁盘上处理，这种情况需要尽量避免，一般考虑使用索引优化

explain  select * from bus_user order by update_time

explain  select name from bus_user order by name

 Select tables optimized away

使用了某些函数来直接访问索引字段，不需要访问表数据

explain  select min(id) from bus_user