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 如果包含子查询,该类型表示在select或where中的子查询
  • DERIVED 如果在from列表中包含了子查询会被标记为DERIVED,mysql会递归执行这些子查询,并将结果放在临时表中
  • UNION 联合查询如果用来关联两个或多个select的话为从第二个SELECT开始的后面所有的select为UNION,如果UNION包含在from子句中,则外层select为DERIVED
  • DEPENDENT UNION 子查询中的UNION,且UNION中从第二个SELECT开始的后面所有的select,依赖了外部查询的结果集
  • UNION RESULT UNION中的合并结果

table字段

显示数据是哪张表的;如果显示为尖括号括起来的<derived N>就表示这个是临时表,后边的N就是执行计划中的id,表示结果来自于这个查询产生;如果是尖括号括起来的<union M,N>,与<derived 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字段

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

  • 字符串
    • char(n) n字节长度
    • varchar(n)
1
2
--  char()、varchar()索引长度计算公式
(Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
  • 数值类型

    • tinyint 1字节
    • smallint 2字节
    • int 4字节
    • bigint 8字节

    如果字段允许为null,需要1字节记录是否为null

  • 时间类型

    • date 3字节
    • timestamp 4字节
    • datetime 8字节

    如果字段允许为null,需要1字节记录是否为null

ref字段

显示在key列记录的索引中,表查找值所用到的列或常量,常见的有const(常量)、字段名

rows字段

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

filtered字段

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

extra字段

表示一些额外信息

  • using filesort 文件排序,mysql无法利用索引来完成的排序,则使用文件排序(出现此种情况,最好进行优化)

    filesort可以使用的内存排序空间大小为sort_buffer_size,默认2M,当不够用时,会使用临时文件来存储,使用临时文件存储会进行文件的合并

    show GLOBAL status like 'Sort_merge_passes'查看merge次数,如果次数过大,建议增大sort_buffer_size

    explain不会显示到底使用了哪种排序

  • using temporary 使用了临时表保存中间结果,常见于排序和分组查询(出现此种情况,尽快优化,速度极慢),使用group by分组查询时,最好按照索引顺序来进行分组

    临时表可能是在内存/磁盘上创建的,内存临时表最大容量为tmp_table_sizemax_heap_table_size的最小值,大于该值时会使用磁盘临时表

    show GLOBAL status like '%tmp%'查看

    1
    2
    3
    4
    5
    6
    7
    +-------------------------+---------+
    | Variable_name | Value |
    +-------------------------+---------+
    | Created_tmp_disk_tables | 4025 |
    | Created_tmp_files | 6366 |
    | Created_tmp_tables | 2096332 |
    +-------------------------+---------+

    Created_tmp_disk_tables表示创建的磁盘临时表的总数

    Created_tmp_tables表示创建临时表的总数

    如果在磁盘上创建的临时表次数过多,可增大tmp_table_sizemax_heap_table_size参数配置

  • using index 表示相应的select行使用了覆盖索引(覆盖索引为查询结果为索引列,不必读取数据行),防止访问数据行,速度提升。如果同时出现了Using where,表示索引也被用来执行查询动作

  • using where 不是读取表中的所有数据或者除了索引之外还使用了其他非索引列,使用where条件进行过滤

  • using join buffer 在使用join进行链表查询的时候,如果表的连接条件没有用到索引,需要有一个缓冲区来存储中间结果,需要添加索引进行优化

  • impossible where where条件总是false,无法查到数据

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