IT源码网

Explain关键字解析讲解

wyy 2021年02月13日 数据库 165 0

Explain 用法

explain模拟Mysql优化器是如何执行SQL查询语句的,从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

语法:Explain + SQL 语句;

如:Explain select * from user; 会生成如下 SQL 分析结果,

 

 

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

下面详细对每个字段进行详解

id

id是一组数字,代表多个表之间的查询顺序,或者包含子句查询语句中的顺序,id 总共分为三种情况,依次详解

1.id 相同,执行顺序由上至下

 

 

2.id 不同,如果是子查询,id 号会递增,id 值越大优先级越高,越先被执行

 

 

3.id 相同和不同的情况同时存在

 id相同的为一组,组内从上到到下执行,在所有组中,id越大,越优先执行

 

select_type

select_type 包含以下几种值

  • simple
  • primary
  • subquery
  • derived
  • union
  • union result

simple

简单的 select 查询,查询中不包含子查询或者 union 查询

 

 

primary

如果 SQL 语句中包含任何子查询,那么子查询的最外层会被标记为 primary

 

 

 

subquery

在 select 或者 where 里包含了子查询,那么子查询就会被标记为 subQquery,见上例

derived

在 from 中包含的子查询,会被标记为衍生查询,会把查询结果放到一个临时表中

 

 

union / union result

如果有两个 select 查询语句,他们之间用 union 连起来查询,那么第二个 select 会被标记为 union,union 的结果被标记为 union result。它的 id 是为 null 的

 

 

 

table

表示这一行的数据是哪张表的数据

type

type 是代表 MySQL 使用了哪种索引类型,不同的索引类型的查询效率也是不一样的,type 大致有以下种类

  • system
  • const
  • eq_ref
  • ref
  • range
  • index
  • all

性能按type排序

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

 

 

system

表中只有一行记录,system 是 const 的特例,几乎不会出现这种情况,可以忽略不计

const

将主键索引或者唯一索引放到 where 条件中查询,MySQL 可以将查询条件转变成一个常量,只匹配一行数据,索引一次就找到数据了

 

 

eq_ref

在多表查询中,如 T1 和 T2,T1 中的一行记录,在 T2 中也只能找到唯一的一行,说白了就是 T1 和 T2 关联查询的条件都是主键索引或者唯一索引,这样才能保证 T1 每一行记录只对应 T2 的一行记录

举个不太恰当的例子,EXPLAIN SELECT * from t1 , t2 where t1.id = t2.id

 

 

 

ref

不是主键索引,也不是唯一索引,就是普通的索引,可能会返回多个符合条件的行。

 

 

range

体现在对某个索引进行区间范围检索,一般出现在 where 条件中的 between、and、<、>、in 等范围查找中。

 

 

index

将所有的索引树都遍历一遍,查找到符合条件的行。索引文件比数据文件还是要小很多,所以比不用索引全表扫描还是要快很多。

 

 

all

没用到索引,单纯的将表数据全部都遍历一遍,查找到符合条件的数据

possible_keys

此次查询中涉及字段上若存在索引,则会被列出来,表示可能会用到的索引,但并不是实际上一定会用到的索引

key

此次查询中实际上用到的索引

key_len

表示索引中使用的字节数,通过该属性可以知道在查询中使用的索引长度,注意:这个长度是最大可能长度,并非实际使用长度,在不损失精确性的情况下,长度越短查询效率越高

ref

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

  • tb_emp 表为非唯一性索引扫描,实际使用的索引列为 idx_name,由于 tb_emp.name='rose'为一个常量,所以 ref=const。
  • tb_dept 为唯一索引扫描,从 sql 语句可以看出,实际使用了 PRIMARY 主键索引,ref=db01.tb_emp.deptid 表示关联了 db01 数据库中 tb_emp 表的 deptid 字段。

rows

根据表信息统计以及索引的使用情况,大致估算说要找到所需记录需要读取的行数,rows 越小越好

extra

不适合在其他列显示出来,但在优化时十分重要的信息

using  fileSort(重点优化)

俗称 " 文件排序 " ,在数据量大的时候几乎是“九死一生”,在 order by 或者在 group by 排序的过程中,order by 的字段不是索引字段,

或者 select 查询字段存在不是索引字段,或者 select 查询字段都是索引字段,但是 order by 字段和 select 索引字段的顺序不一致,都会导致 fileSort

 

 

using temporary(重点优化)

使用了临时表保存中间结果,常见于 order by 和 group by 中。

 

 

USING index(重点)

表示相应的 select 操作中使用了覆盖索引(Coveing Index),避免访问了表的数据行,效率不错!

如果同时出现 using where,表明索引被用来执行索引键值的查找;如果没有同时出现 using where,表面索引用来读取数据而非执行查找动作。

 

 

Using wher

表明使用了 where 过滤

using join buffer

使用了连接缓存

impossible where

where 子句的值总是 false,不能用来获取任何元组

select tables optimized away

在没有 GROUPBY 子句的情况下,基于索引优化 MIN/MAX 操作或者 对于 MyISAM 存储引擎优化 COUNT(*)操作,不必等到执行阶段再进行计算, 查询执行计划生成的阶段即完成优化。

distinct

优化 distinct,在找到第一匹配的元组后即停止找同样值的工作

 

 

总结

性能按type排序

system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

性能按Extra排序

  • Using index:用了覆盖索引
  • Using index condition:用了条件索引(索引下推)
  • Using where:从索引查出来数据后继续用where条件过滤
  • Using join buffer (Block Nested Loop):join的时候利用了join buffer(优化策略:去除外连接、增大join buffer大小)
  • Using filesort:用了文件排序,排序的时候没有用到索引
  • Using temporary:用了临时表(优化策略:增加条件以减少结果集、增加索引,思路就是要么减少待排序的数量,要么就提前排好序)
  • Start temporary, End temporary:子查询的时候,可以优化成半连接,但是使用的是通过临时表来去重
  • FirstMatch(tbl_name):子查询的时候,可以优化成半连接,但是使用的是直接进行数据比较来去重

常见的优化手段

  • SQL语句中IN包含的值不应过多,不能超过200个,200个以内查询优化器计算成本时比较精准,超过200个是估算的成本,另外建议能用between就不要用in,这样就可以使用range索引了。
  • SELECT语句务必指明字段名称:SELECT * 增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性;当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。
  • 当只需要一条数据的时候,使用limit 1
  • 排序时注意是否能用到索引
  • 使用or时如果没有用到索引,可以改为union all 或者union
  • 如果in不能用到索引,可以改成exists看是否能用到索引
  • 使用合理的分页方式以提高分页的效率
  • 不建议使用%前缀模糊查询
  • 避免在where子句中对字段进行表达式操作
  • 避免隐式类型转换
  • 对于联合索引来说,要遵守最左前缀法则
  • 必要时可以使用force index来强制查询走某个索引
  • 对于联合索引来说,如果存在范围查询,比如between,>,<等条件时,会造成后面的索引字段失效。
  • 尽量使用inner join,避免left join,让查询优化器来自动选择小表作为驱动表
  • 必要时刻可以使用straight_join来指定驱动表,前提条件是本身是inner join

 

发布评论

分享到:

IT源码网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

MySQL索引介绍和实战讲解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。