explain详解

explain详解

在日常工作中, 我们会记录一些执行时间比较久的SQL语句, 找出这些SQL语句并不意味着完事了, 我们常常用到explain这个命令来查看一个这些SQL语句的执行计划, 查看该SQL语句有没有使用上了索引, 有没有做全表扫描。使用很简单，就是在sql前加上explain

explain select name from student where id= 100

这是我的表，里面有一千万条数据。用来测试索引。

1. id

1. id 值相同时，被视为一组从上向下执行。
2. 如果是子查询，id 值会递增，id 值越高，优先级越高。
3. id为NULL最后执行。

2. select_type

1. simple: 简单的select, 查询中不包含子查询或者 union。例如: select name from student where id= 100
2. primary: 子查询中最外层查询, 查询中若包含任何复杂的子部分, 最外层的select被标记为primary.。
3. derived：在 from 的列表中包含的子查询被标记成 derived（派生表）。例如: explain select id from (select id,name from student) student1 where name= 'name100'
4. subquery：在 select 或 where 列表中包含了子查询，则子查询被标记成 subquery。例如: explain select id from student where score = (select score from student where name='name100');
5. union: union中的第二个或后面的select语句. 例如: EXPLAIN select id from student where id<12691055 UNION all select id from student where id<12691060;

3. table

显示这一步所访问数据库中表名称. 有时候不是真实的表名, 可能是简称。例如: explain select id from (select id,name from student) student1 where name= 'name100' id为1用的表是：<derived2> 。<derived2>表示id为2的derived的查询结果

4. partitions

该字段看table所在的分区, 值为NULL表示表未被分区。

5. type

1. all：“全表扫描”。all是一种非常暴力和原始的查找方法，非常的耗时而且低效。举例：explain select * from student where score=99其中score没有索引，所以只能走全表。

   举例：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！

   你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。

2. index：全索引扫描。它扫描的全索引树，索引树相对全表存储的数据更少，因此索引文件比数据文件小.。所以它比全表更快。例如：explain SELECT score from student走全表需要6s。

   explain SELECT name from student走name索引需要4s

3. range：有范围的索引扫描，range是基于索引的。相对于index的全索引扫描，它有范围限制，因此要优于index。。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。例如：SELECT * from student where id<12791148查十万条数据只需要0.08s。

4. ref：非唯一性索引扫描。条件是： 使用了索引而且不是主键和unique。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表。因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。 因此要优于range。例如：SELECT name from student where name='name16'只需要0.001s。

5. eq_ref: 唯一性索引扫描。类似ref, 区别在于使用的索引是常见于主键或唯一索引， 对于每个索引键值, 表中只有一条记录匹配。所以当我们首次查找到数据，便立即停止了查询。因此查找效率高于ref。但这个出现的并不多，因为大多数情况mysql优化器会将这次查询优化转化为一个常量。变为const

6. const：常量索引。通常情况下，如果将一个主键或唯一索引放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。

一般来说, 需要保证查询至少达到range级别, 最好能达到ref级别.

6. possible_keys

可能用到的索引, 查询涉及到的字段若存在索引, 则该索引将被列出, 但不一定被查询实际使用。

7. key

实际中使用的索引, 如为NULL, 则表示未使用索引。

8. key_len

索引使用的字节数, key_len是根据表定义计算而得。根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。通过这个我们可以看组合索引。

key_type	key_len的长度计算公式
char(10)固定字段且不允许NULL	10 * ( character set：utf8=3,utf8mb4=4，gbk=2,latin1=1)
char(10)固定字段且允许NULL	10 * ( character set：utf8=3,utf8mb4=4，gbk=2,latin1=1)+1(NULL)
varchr(10)变长字段且不允许NULL	10 *( character set：utf8=3,utf8mb4=4，gbk=2,latin1=1)+2(变长字段)
varchr(10)变长字段且允许NULL	10 * ( character set：utf8=3,utf8mb4=4，gbk=2,latin1=1)+1(NULL)+2(变长字段)
bigint	8 字节
int	4 字节
typeint	1 字节
float	4 字节
double	8 字节

举例：现创建如下三个字段a,b,c,如下图。字符集是utf8mb4，并创建了组合索引abc。SELECT * FROMdecimalwhere a=1 and b='1'and c='1'使用了abc索引，key_len=86=4+(10*4)+(10*4+2)。所以a，b，c三个字段都使用了。如果只使用a字段key_len=4，如果只使用a,b字段key_len=4+(10*4)=44

9. ref

显示关联的字段. 如果使用常数等值查询, 则显示const, 如果是连接查询, 则会显示关联的字段.

10. rows

出找到所需记录所要读取的行数。并非拿到结果的行数。

举例：explain select * from student where score=99其中score没有索引，所以只能遍历全表10030614条数据。

而真正符合的数据只有99947条。

11. filtered

百分比值, 表示存储引擎返回的数据经过滤后, 剩下多少满足查询条件记录数量的比例.

举例：SELECT * from test_data where name='name'其中name没有索引，所以只能遍历全表5条数据。

而真正符合的数据只有1条。

所以filtered=1/5=20%

……………………………………………………………………………………………………………………

但是我发现只要数据量一大，这个就变了。

举例：explain select * from student where score=99其中score没有索引，所以只能遍历全表10030614条数据。

而真正符合的数据只有99947条。

所以filtered=99947/10030614=1%并不会等于10%。有大佬知道这是怎么回事吗？

12. extra

显示十分重要的额外信息. 其取值有以下几个：

• using filesort: 使用一个没有索引的字段排序。name没有索引
• using index: 索引覆盖，查询的内容可以直接在索引中拿到。name 有索引
• using where: 表示mysql服务器将在存储引擎检索行后再进行过滤.
• using index 和using where同时出现,查询的内容可以直接在索引中拿到，并且需要进行过滤