MySQL的执行计划和索引详解
使用explain关键字可以模拟优化器执行sql语句,从而知道mysql是如何处理sql语句的,分析你的查询语句或者是结构性能。
我们通过几张表来使用explain的例子:
在select语句之前增加explain关键字,MySQL会在查询的基础上设置一个标记,执行查询时,会返回执行计划的信息,而不是执行这条sql语句(如果from中包含子查询,仍会执行该子查询的,将结果放入临时表中)
使用的的表
1.actor表(不设置索引,存储引擎是InnoDB)
2.film表(设置name索引(辅助索引),存储引擎是InnoDB)
3.film-actor表(设置联合辅助索引(film_id,actor_id))
使用的表我们建立完毕了,那接下来就让我们熟悉一下explain的始终和返回执行计划中字段的含义
mysql>EXPLAIN SELECT (SELECT 1 from actor ) from film;
在查询中的每一个表会输出一行,如果有两个表通过join连接查询,那么会输出两行,表的意思相当广泛:可以是子查询,一个union结果等。
explain有两个辅助写法:
1)explain extended:会在explain的基础上额外提供一些查询的优化的信息,紧随其后的通过show warnings 命令可以的到优化后的查询语句,从而看出优化器优化了什么,二外有filtered列,是一个半分比的值,rows*filtered/100可以估算出来将要和explain中前一个表进行连接的行数(前一个表指的是explain中的id值比当前id值小的表)
mysql>EXPLAIN EXTENDED SELECT * from film where id = 1;
mysql>show WARNINGS;(Note 得到优化后的语句)
2)explain partitions :相比explain多了个partition字段,如果查询是基于分表的的话,会显示查询将访问的分区
那接下来我们介绍一下explain中每个列的信息
1.id列
id列的编号是select的***,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的。MySQL将 select查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。
复杂查询分为三类:简单子查询、派生表(from语句中的子查询) 、union查询。
id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行1)简单子查询
1)简单子查询
mysql>EXPLAIN SELECT (SELECT 1 FROM actor LIMIT 1) FROM film;
2)from字句中的子查询(在mysql5.7的版中没有派生表的返回,具体原因不明,可能是5.7的优化了)
mysql>EXPLAIN SELECT id FROM (SELECT id FROM film) f;
这个查询执行时有个临时表别名为f,外部的select查询就是引用了这个临时表
3)union查询
mysql>EXPLAIN select 1 from film union all select 1 from actor;
union 结果总是放在一个匿名的临时表中,临时表不在sql中出现,因此他的id是null
2.select_type列
select_type表示对应行是简单的还是复杂的查询,如果是复杂的查询,又是上述三种哪一种
1)simple:简单查询,查询不包含子查询和union
mysql>explain select * from film where id =1;
2)primary:复杂查询中的最外层select
3)subquery:包含select中的子查询(不在from子句中)
4)derived:包含在from子句中的子查询,mysql会将结果存放在一个临时表中,也称派生表(derived的英文义)
用这个例子来解析primary,subquery,derived类型
mysql>explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
5)union :在union中的第二和随后的select
6)union result:从union临时表检索结果的select
用这个例子来了解union和union result 类型:
mysql> explain select 1 from film union select 1 from film ;
3.table 列
这一列表示explain的一行正在访问哪个表。
当from子句中有子查询的时候,table列是<derivenN>格式,表示当前查询依赖id=N的查询,于是先执行id=N的查询。
当有union时,union result的table列的值为<union1,2>,1和2表示参与的select行id
4.type列
这一列表示关联类型和访问类型,即MySQL决定如何查找表中的行,查找的数据记录的大概范围。
依次从最优到最差分别为:system>const>eq_ref>ref>range>index>all(常用的)
一般来说,得到保证查询打到range级别,最好打到ref级别
NULL:MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如再索引列中选区最小值,可以单独擦查找索引来完成,不需要再执行时访问表。
mysql>explain select min(id) from film;
注意:mysql中主键就是索引,mysql会帮主键主动建立索引的
const,system:mysql能对查询的某部分进行优化并将其转化成一个常量(可以看着show warnings的结果)。用于primary_key和union key的所有列和常数比较的时候,所以表最多一个匹配行,读一次,速度比较快,system时const的特例,表中只有一条元组数据匹配就是system
mysql>explain extended select * from (select * from film where id = 1) f;
注意:const就是查询条件时主键或者唯一性约束的,查询出来的就只有一条数据的,而system查询的表就只有一条数据的。
mysql>show warnings;(优化后的sql)
eq_ref:primary_key或者union key索引的所有部分被连接使用,最多返回一条符合条件的记录,这可能再const之外最好的连接类型了,简单的select查询就不会出现这种类型
mysql>explain select * from film_actor left join film on film_id = film.id ;
注意:film_actor也是使用索引查询的原因时覆盖索引(后面有说)
ref相比eq. ret,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。
1.简单select查询,name是普通索引(非唯一索引)
mysql>explain select * from film where name = "film1";
2.关联表查询,idx_ film_ actor_ id是film_ 1d和actor_ id的联合索引,这里使用到了film_ actor的左边前缀film_id部分。
mysql> explain select * from film LEFT JOIN film_actor on film.id = film_actor.film_id;
range:范围扫描通常出现在in(), between,> <, >=等操作中。使用一个索引来检索给定范围的行。
mysql>explain select * from actor WHERE id > 1
index:扫描全表索引,这通常比all快一些,(index是从索引中读取的,而all是从硬盘中读取)
mysql>explain select * from film ;
ALL:即全表扫描,意味着mysql需要从头到尾去查询所需要的好行,通常情况下这需要增加索引来进行优化
mysql>explain select * from actor;
5.possible_keys列
这一列显示查询可能使用哪些索引来查找。
explain时可能出现posible. keys有列,而key显示NULL的情况,这种情况是因为表中数据不多, mysq认为索引对此查询帮助不大,选择了全表查询。
如果该列是NULL,则没有相关的素引,在这种情况下,可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能,然后用explain查看效果。
6.key列
这一列是示mysq实际采用索引来优化対該表的访问
如果没有使用索引,則垓列是NULL.如果想强制mysq使用或忽视possible. keys列中的索引,在査洵中使用force index. ignore index。
7.key_len列
mysql>explain select * from film_actor where film_id = 2;
key_len计算规则如下:
8. ref列
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有: const (常量) ,字段名(例: film.id)
9. rows列
这一列是mysq估计要读取并检测的行数,注意这个不是结果集里的行数。
10. Extra列
这一列展示的是额外信息。常见的重要值如下:
Using index: 查询的列被索引覆盖,并且where筛选条件是索引的前导列,是性能高的表现。一般是使用了覆盖索引索引包含了所有查询的字段)。对于innodb来说,如果是辅助索引性能会有不少提高.
mysql>explain select film_id from film_actor where film_id = 1;
Using where : 查询的列未被索引覆盖 where筛选条件非索引的前导列
mysql>explain select * from actor where name = 'a';
Using where Using index: 查询的列被索引覆盖,并且where筛选条件是索引列之一但是不是索引的前导列,意味着无法直接通过索引查找来查询到符合条件的数据
mysql> explain select film_id from film_actor where actor_id = 1;
NULL:查询的列未被索引覆盖,并且where筛选条件是索引的前导列,意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表“来实现,不是纯粹地用到了索引,也不是完全没用到索引.
Using temporary: mysql需要創建一張临时表来处理査洵. 出现这种情况一般是要迸行优化的, 首先是想到用索引来优化.
1. actor.name没有索引,此时创建了张临时表来dstinct.
mysql>explain select distinct name from actor;
2.film.name 建立了idx_name索引,此时查询时extra时using index,没有使用临时表
mysql>explain select distinct name from film;
Using filesort: mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。此时mysql会根据连接类型浏览所有符合条件的记录,井保存排序关键字和指针,然后排序关键字并按順序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
1. actor .name未创建索引,会浏览actor整个表, 保存排序关键字name和对那个的id,然后排序name并检索记录
mysql>explain select * from actor order by name;
2.film.name 建立了idx_name索引,此时查询时extra时using index