0%

执行计划

执行计划

如果不知道执行计划,那就不可能进行SQL优化,那么执行计划是什么呢?

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL的,进而分析性能瓶颈

用起来其实很简单,使用explain + sql语句即可,也可以与show WARNINGS来进行组合

1
2
3
4
5
explain select * from user;

-- show WARNINGS又是受可以解释为什么索引没有使用
explain select * from user;
show WARNINGS;

重点是如何分析结果

1
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |

这写就是所打印出来的字段,接下里就一个个地分析一下是什么意思

id字段

表示的含义:select查询的序号,表示查询中执行select子句或者操作表的顺序

id值数字越大越先执行

id有三种情况

第一种情况:id相同

id相同

id相同的情况下,是按照table的顺序从上到下开始操作的

第二种情况:id不同

id不同

id不同的情况下,**id越大优先级越高,id大的先被执行**

第三种情况:id相同和不同

id相同不同

先执行id大的,id相同的顺序执行

select_type字段

该字段来表示查询类型,用于区分普通查询、联合查询、子查询等复杂查询常用的有六种

  • SIMPLE 简单查询,不包含子查询和union联合查询
  • PRIMARY 如果包含子查询,该类型表示最外层查询
  • SUBQUERY 如果包含子查询,该类型表示内层查询
  • DERIVED 如果在from列表中包含了子查询会被标记为DERIVED,mysql会递归执行这些子查询,并将结果放在临时表中
  • UNION 联合查询如果用来关联两个或多个select的话为从第二个SELECT开始的后面所有的select为UNION,如果UNION包含在from子句中,则外层select为DERIVED
  • DEPENDENT UNION 子查询中的UNION,且UNION中从第二个SELECT开始的后面所有的select,依赖了外部查询的结果集
  • UNION RESULT UNION中的合并结果

table字段

显示数据是哪张表的;如果显示为尖括号括起来的就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生;如果是尖括号括起来的<union M,N>,与类似,也是一个临时表,表示这个结果来自于union查询的id为M,N的结果集

partitions字段

匹配记录的分区,对于未分区的表,显示null

type字段

检索的类型

从最好到最差依次为

system>const>eq_ref>ref>range>index>ALL

至少要达到range级别,最好能是ref

  • system 表中只有一行数据,属于const的特例,该类型可忽略不计

  • const 使用主键查询或者unique索引的等值查询,只匹配一条数据,可以认为const是最优的

    1
    explain select * from plan where id = 127

    执行计划之const

  • eq_ref 出现在连接查询中,使用唯一索引或者主键作为连接条件,唯一性索引扫描,对于每个索引键,表中只有一条与之匹配(如使用主键或者unique索引)

    1
    explain select * from inventory, plan where plan.id = inventory.plan_id

    执行计划之eq_ref

  • ref 如果是在表连接查询中,使用索引字段作为连接条件;非唯一性索引扫描,匹配返回某个单独值得所有行

    1
    explain select * from rule where plan_id = 127

    执行计划之ref

  • range 检索指定范围的数据,使用索引,where语句中使用between…and、in、<、>等

    1
    explain select * from rule where plan_id > 127

    执行计划之range

  • index full index scan,全索引扫描,与all相比,index只遍历索引树;当查询时索引覆盖的,extra中有using index;以索引顺序从索引中查找数据行的全表扫描,extra中没有using index;如果extra中同时有using index和using where,则是利用索引查找键值的意思

    1
    explain select id from rule

    执行计划之index

  • index_merge 索引合并

    1
    explain select * from rule where plan_id = 5 or id = 10 

    执行计划之index_merge

  • all 全表扫描

possible_keys字段

查询使用的条件可能涉及到多个字段,所对应的多个索引,但是实际不一定会使用到所列出来的索引

key字段

查询实际使用到的索引

key_len字段

索引中使用的字节数,在不损失精度的情况下,长度越短越好(为索引值的最大可能长度,并非索引实际长度,是根据表结构计算得出,而非数据),对于确认索引的有效性以及多列索引中用到的列的数目很重要

1
2
--  char()、varchar()索引长度计算公式
(Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

ref字段

显示索引的哪一列被使用了

rows字段

估算出找到需要的数据需要读取的行数

filtered字段

表示符合查询条件的数据百分比,最大100,使用rows*filtered%可以获得和下一张表进行连接的行数

extra字段

表示一些额外信息

  • using filesort 文件排序,mysql无法利用索引来完成的排序,则使用文件排序(出现此种情况,最好进行优化)
  • using temporary 使用了临时表保存中间结果,常见于排序和分组查询(出现此种情况,尽快优化,速度极慢),使用group by分组查询时,最好按照索引顺序来进行分组
  • using index 表示相应的select行使用了覆盖索引(覆盖索引为查询结果为索引列,不必读取数据行),防止访问数据行,速度提升
  • using where 不是读取表中的所有数据或者除了索引之外还使用了其他非索引列,使用where条件进行过滤
  • using join buffer 在使用join进行链表查询的时候,如果表的连接条件没有用到索引,需要有一个缓冲区来存储中间结果,需要添加索引进行优化
  • impossible where where条件总是false,无法查到数据

欢迎关注我的其它发布渠道