当前位置:   article > 正文

explain详解_

explain详解

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

explain select name from student where id= 100
  • 1

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

image-20210929071332966

1. id

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

2. select_type

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

3. table

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

4. partitions

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

5. type

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

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

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

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

    explain SELECT name from student走name索引需要4simage-20211006204639971image-20211006204436793

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

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

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

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

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

6. possible_keys

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

7. key

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

8. key_len

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

key_typekey_len的长度计算公式
char(10)固定字段且不允许NULL10 * ( character set:utf8=3,utf8mb4=4,gbk=2,latin1=1)
char(10)固定字段且允许NULL10 * ( character set:utf8=3,utf8mb4=4,gbk=2,latin1=1)+1(NULL)
varchr(10)变长字段且不允许NULL10 *( character set:utf8=3,utf8mb4=4,gbk=2,latin1=1)+2(变长字段)
varchr(10)变长字段且允许NULL10 * ( character set:utf8=3,utf8mb4=4,gbk=2,latin1=1)+1(NULL)+2(变长字段)
bigint8 字节
int4 字节
typeint1 字节
float4 字节
double8 字节

举例:现创建如下三个字段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)=44image-20211007151551983image-20211007151651264

9. ref

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

10. rows

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

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

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

11. filtered

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

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

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

所以filtered=1/5=20%

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

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

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

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

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

12. extra

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

  • using filesort: 使用一个没有索引的字段排序。name没有索引image-20211007163125548
  • using index: 索引覆盖,查询的内容可以直接在索引中拿到。name 有索引image-20211007163501833
  • using where: 表示mysql服务器将在存储引擎检索行后再进行过滤.image-20211007163801491
  • using index 和using where同时出现,查询的内容可以直接在索引中拿到,并且需要进行过滤image-20211007165744577
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/神奇cpp/article/detail/1006617
推荐阅读
相关标签
  

闽ICP备14008679号