阅读 529

MySQL执行计划

你是否遇到表加了索引之后,查询执行依旧很慢?是否困惑索引有没有生效?
带着问题,我们来学习下一个MySQL数据库开发者必备的利器--查看执行计划。

查看执行计划方法

1、命令行方式:EXPLAIN + sql

2、界面方式:Navicat 自带

其中比较重要的列有id、type、key、rows、Extra。

各个字段详解

id

表示每一条SQL语句执行计划中表加载的顺序,有如下三种情况:

1、id值相同的时候,自上往下执行;

如上所示,对应表的加载顺序为t1>t3>t2(这里的表为别名)

2、id值不同的时候,id值大的优先执行;

如上所示,,对应表的加载顺序为t3>t2>t1(这里的表为别名)

3、id值相同且存在id值不同的时候,id相同的为一组,自上往下执行,id不同的,id值大的优先执行;

如上所示,,对应表的加载顺序为t3>derived2>t2>t4(这里的表为别名),derived2是id为2这一步产生的衍生表。

select_type 

查询的类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询

1、SIMPLE:简单的select查询,查询中不包含子查询或者union 

2、PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为primary  

3、SUBQUERY:在select 或 where列表中包含了子查询  

4、DERIVED:在from列表中包含的子查询被标记为derived(衍生),mysql或递归执行这些子查询,把结果放在零时表里 

5、UNION:若第二个select出现在union之后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived  

6、UNION RESULT:从union表获取结果的select 

type

访问类型,sql查询优化中一个很重要的指标,结果值从好到坏依次是:

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

 而在实际开发场景中,比较常见的几种类型如下:

const > eq_ref > ref > range > index > ALL(顺序从好到差),

 一般来说,好的sql查询至少达到range级别,最好能达到ref。

1、const:表示通过索引一次就找到数据,const常作用于primary key或者unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const;

2、eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描;

3、ref:非唯一性索引扫描,返回匹配某个单独值的所有行;

4、range:使用一个索引检索指定范围的行,一般在where语句中会出现between、<、>、in等范围查询;

5、index:Full Index Scan,全索引扫描,只遍历索引树;只查询索引列,对索引进行全表扫描

6、ALL:Full Table Scan,全表扫描,找到匹配行;查询的列包含非索引数据,全表扫描;与index比较,ALL需要扫描磁盘数据,index值需要遍历索引树;

possible_keys 

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

key 

实际使用到的索引,如果为NULL代表没有使用到索引,这也是判断是否用上索引的关键。

查询中如果使用了覆盖索引,则该索引仅出现在key列表中 ;

key_len 

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

ref

显示索引的哪些列被引用了,通常是对应字段或const;

rows

根据表统计信息和索引的使用情况,大概估算出找到所需记录数据所扫描的数据行数;

Extra 

包含不适合在其他列中显示,但十分重要的额外信息;通常会出现以下几种信息:

1、Using index

表示相应的select操作中使用了覆盖索引,避免了访问表的数据行,效率高 

覆盖索引:也叫索引覆盖。就是select列表中的字段,只用从索引中就能获取,不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。 
注意: 
a、如需使用覆盖索引,select列表中的字段只取出需要的列,不要使用select *
b、如果将所有字段都建索引会导致索引文件过大,反而降低crud性能

2、Using filesort

表示MySQL会使用一个外部索引对数据进行排序(文件排序),而不是使用表内索引,也就是说mysql无法利用索引完成的排序操作成为“文件排序” ;这种情况在SQL查询需要避免,最好不要在Extra中出现此类型;

通常会是使用ORDER BY语句导致,使用无索引的字段进行排序会出现,同样如果使用有索引的字段,但用法不对也会出现,比如使用组合索引不规范时。

3、Using temporary

产生临时表保存中间结果,这种SQL是不允许的,遇见数据量大的场景,基本就跑不动;  

这种类型常常因为ORDER BY 和 GROUP BY导致,所以在进行数据排序和分组查询时,要注意索引的合理利用。 

4、Using where:使用where过滤数据 

5、Using join buffer

表示使用到了表连接缓存; 当表数据量大,可能导致buffer过大,查询效率比较低,这种情况注意在表连接字段上正确使用索引;如果表连接查询慢时,在连接字段上加个索引试试。

6、Impossible WHERE

表示where后面的条件永远为false,匹配不到数据;

文章分类
后端
文章标签