使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。explain得到的就是sql的执行计划，我们可以用它来分析查询语句或者表结构的性能瓶颈

执行计划的作用

  • 查看表的读取顺序
  • 查看数据库读取操作的操作类型
  • 查看哪些索引有可能被用到
  • 查看哪些索引真正被用到
  • 查看表之间的引用
  • 查看表中有多少行记录被优化器查询

语法

示例中所用的表结构如下：

create table t1(
  id int primary key,
  name varchar(20),
  col1 varchar(20),
  col2 varchar(20),
  col3 varchar(20)
);
create table t2(
  id int primary key,
  name varchar(20),
   col1 varchar(20),
   col2 varchar(20),
  col3 varchar(20)
);
create table t3(
  id int primary key,
  name varchar(20),
    col1 varchar(20),
   col2 varchar(20),
  col3 varchar(20)
);
insert into t1 values(1,'zs1','col1','col2','col3');
insert into t2 values(1,'zs2','col2','col2','col3');
insert into t3 values(1,'zs3','col3','col2','col3');

create index ind_t1_c1 on t1(col1);
create index ind_t2_c1 on t2(col1);
create index ind_t3_c1 on t3(col1);

create index ind_t1_c12 on t1(col1,col2);
create index ind_t2_c12 on t2(col1,col2);
create index ind_t3_c12 on t3(col1,col2);

先来看下执行计划的基本结构

下面逐个列的来看下其含义

id

  • id值相同，执行顺序由上而下；
  • id值不同，id值越大优先级越高,越先被执行
  • id值有相同的也有不同的，如果id相同，从上往下执行，id值越大，优先级越高,越先执行

select_type

查询类型,主要用于区分查询类型

  • SIMPLE : 简单的select查询,查询中不包含子查询或者UNION
  • PRIMARY: 查询中若包含复杂的子查询,最外层的查询则标记为PRIMARY
  • SUBQUERY : 在SELECT或者WHERE列表中包含子查询
  • DERIVED : 在from列表中包含子查询被标记为DRIVED衍生,MYSQL会递归执行这些子查询,把结果放到临时表中
  • UNION: 若第二个SELECT出现在union之后,则被标记为UNION, 若union包含在from子句的子查询中,外层select被标记为:derived
  • UNION RESULT: 从union表获取结果的select

table

显示这一行的数据是和哪张表相关

partitions

使用的哪个分区，需要结合表分区才可以看到

type

访问类型: all, index,range,ref,eq_ref, const,system,null
最好到最差依次是: system > const > eq_ref>ref >range > index > all , 开发中写下的sql最好能优化到range级别或则ref级别

  • system: 表中只有一行记录(系统表), 这是const类型的特例, 基本上不会出现
  • const: 通过索引一次查询就找到了,const用于比较primary key或者unique索引,因为只匹配一行数据,所以很快,如将主键置于where列表中,mysql就会将该查询转换为一个常量
  • eq_ref: 唯一性索引扫描, 对于每个索引键,表中只有一条记录与之匹配, 常见于主键或者唯一索引扫描
  • ref : 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有符合条件的行,然而它可能返回多个符合条件的行
  • range : 只检索给定范围的行, 使用一个索引来选择行.key列显示的是真正使用了哪个索引,一般就是在where条件中使用between,>,<,in 等范围的条件,这种在索引范围内的扫描比全表扫描要好,因为它只在某个范围中扫描,不需要扫描全部的索引
  • index : 扫描整个索引表, index 和all的区别为index类型只遍历索引树. 这通常比all快,因为索引文件通常比数据文件小,虽然index和all都是读全表,但是index是从索引中读取,而all是从硬盘中读取数据
  • all : full table scan全表扫描 ,将遍历全表以找到匹配的行

possible_keys

SQL查询中可能用到的索引,但查询的过程中不一定真正使用

key

查询过程中真正使用的索引,如果为null,则表示没有使用索引
查询中使用了覆盖索引,则该索引仅出现在key列表中

key_len

索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确度的情况下,长度越短越好, key_len显示的值为索引字段的最大可能长度,并非实际使用长度, 即key_len是根据表定义计算而得

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或者常量被用于查找索引列上的值

rows

根据表统计信息及索引选用的情况,估算找出所需记录要读取的行数 (有多少行记录被优化器读取)

filtered

按表条件筛选的表行的估计百分比。 最大值为100，这意味着不会对行进行过滤。 值从100开始减少表示过滤量增加。 rows显示检查的估计行数。
这个百分比值和rows列的值一起使用，可以估计出那些将要和explain中的前一个表进行连接的行的数目。前一个表就是指explain 的 id列(代表执行顺序的id)的值比当前表的id小的表。

extra

包含其它一些非常重要的额外信息

  • Using filesort : 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,Mysql中无法利用索引完成的排序操作称为文件排序

  • Using temporary : 使用了临时表保存中间结果,Mysql在对查询结果排序时使用了临时表,常见于order by 和分组查询group by

  • Using index :
    (1)、查询操作中使用了覆盖索引(查询的列和索引列一致),避免访问了表的数据行,效率好
    (2)、如果同时出现了using where, 表明索引被用来执行索引键值的查找
    (3)、如果没有同时出现using where, 表明索引用来读取数据而非执行查找动作

覆盖索引: 查询的列和索引列一致, 换句话说查询的列要被所键的索引覆盖,就是select中数据列只需从索引中就能读取,不必读取原来的数据行,MySql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件

  • using where : 表明使用了where条件过滤

  • using join buffer : 表明使用了连接缓存, join次数太多了可能会出现

  • impossible where : where子句中的值总是false,不能用来获取任何数据

  • select tables optimized away :
    在没有group by 子句的情况下, 基于索引优化min/max操作或者对于MyISAM存储引擎优化count(*)操作,不必等到执行阶段再进行计算,查询执行计划生成阶段即完成优化

  • distinct : 优化distinct操作,在找到第一个匹配的数据后即停止查找同样的值的动作

两个变种命令

explain extended

explain extended是在mysql5.6版本中的，用explain extended查看执行计划会比explain多一列 filtered。如果你是用的mysql5.7的话，那默认explain 就会输出 filtered 这一列，不需要使用explain extended了。
紧随其后通过 show warnings 命令可以 得到优化后的查询语句，从而看出优化器优化了什么

explain extended SELECT * FROM t1 where key1 = '11';
show warnings;

5.7之后，依然可以用 show warnings; 和 show errors;

explain partitions

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。
同样的，5.7版本自带

分析执行顺序一则


id=4, select_type为union, union后的select语句先被执行

id=3, 因为(select id,name from t1 where name='zs1')被当作一张表处理,所以为select_type 为derived

id=2, select_type为SUBQUERY,为select后面的子查询

id=1, 表示union中的第一个select, select_type为primary表示该查询为外层查询,table被标记为,表示结果来自衍生表

id=null,代表从union的临时表中读取行记录, <union1,4>表示将id=1和id=4的结果进行union操作

索引优化原则

尽可能全值匹配: 查询的条件列刚好和索引创建的列数量和顺序相同

最佳左前缀法则: 如果索引了多列,要遵循最左前缀法则. 查询从索引的最左列开始并且不跳过索引中的列

不在索引上做任何操作(计算,函数,(自动/手动)类型转换,这样会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

explain select * from t1 where col1='111' and col2 > 22 and col3='12121';
则只能使用col1上的索引

尽量使用覆盖索引(要查询的列和索引中的列一致)，避免使用select *

mysql在使用不等于条件判断的时候,索引会失效引发全表扫描

explain select * from t1 where col1 != '1111';

is null, is not null 索引会失效，无法使用索引

like以通配符开头(‘%xxx…’)索引会失效,导致全表扫描

字符串不加单引号索引失效

explain select * from t1 where col1=1000;
explain select * from t1 where col1='1000';

少用or,它会导致索引失效

索引是否被引用的小总结

where

order by

```sql
mysql有两种排序方式: 文件排序和扫描有序索引排序
mysql能为排序和查询使用相同的索引

index abc(a,b,c)
order by 能使用最左前缀
order by a
order by b
order by a,b,c
order by a desc,b desc,c desc

如果where使用索引的最前缀定义为常量,则order by能使用索引
where a=const order by b,c
where a=const and b = const order by c
where a=const order by b,c
where a=const and b > const order by b,c

不能使用索引进行排序
order by a asc,b desc, c desc /*排序顺序不一致*/
where g=const order by b,c /*丢失a索引*/
where a=const order by c /*丢失b索引*/
where a=const order by a,d /*d不是索引*/
where a in(…) order by b,c /*对于排序来说,in 相当于是范围查询*/

引用

MySQL Explain详解
SQL优化（三）—— 索引、explain分析
Explain 最完整总结，SQL优化不再难！
Mysql调优之Explain extend
MySQL explain 、explain extended用法