执行计划
MySQL的Explain关键字可以模拟优化器执行SQL语句,从而知道MySQL如何处理你的SQL语句,最终能够定位分析出你的查询语句的性能瓶颈。在select语句之前增加explain关键字,MySQL会在查询上设置一个标记,执行查询是,会返回执行计划信息;
explain有两种变种:
- explain extended : 会在explain的基础上额外提供一些查询优化的信息。紧随其后通过show warnings命令可以得到优化后的查询语句,从而看出优化器优化了什么。额外还有filtered列,是一个半分比的值,rows * flitered/100可以估算出将要和explain中前一个表进行连接的行数(前一个表指explain中的id值比当前表id小的值得表)
- explain partitons: 相比explain多了个partitions字段,如果查询基于分区表的话,会显示查询将访问的分区;
数据库安装
下载mysql (mysql-5.6.48下载地址),选择开发者模式按照好后,会有默认数据库sakia:
执行计划返回列
执行SQL获得执行计划:
explain select (select 1 from actor where film_id = 10 limit 1 ) from film
获得返回数据
explain返回信息一共有10列,分别是:id、select_type、table、type、possible_keys、key、key_len、ref、rows、Exra
(1) id
id列表示的是sql执行的顺序标记,id的编号是select执行的序列号:
- id相同时,执行顺序由上至下
- sql语句中存在子查询,id序号会递增,id越大越先被执行;
(2) select_type
select 查询子句的类型:
- SIMPLE 简单查询select,不使用UNION或子查询等
- PRIMARY 复杂查询的最外层
- UNION 在UNION中的第二个和随后的select
- UNION RESULT 从union临时表检索结果的select
- DEPENDENT UNION
- SUBQUERY 包含在select中的子查询
- DERIVED 包含在from子句中的子查询,mysql会将结果存放在一个临时表中,也称为派生表
- DEPENDENT SUBQUERY 子查询中的第一个select,取决于外面的查询
- UNCACHEABLE SUBQUERY 不能被缓存结果的子查询,必须重新评估外链接的第一行
(3) table
表示explain的一行正在访问哪个表,当from子句中有子查询时,table列是格式,表示当前查询依赖的是id=N的查询,于是先执行id=N的查询,当有UNION时,UNIONRESULT的table列<union 1,2>,1,2表示参与union的select 行id:
explain select 1 from film where film_id =10 union all select 1 from actor where actor_id = 10
(4) type
type列表示MySQL在表中找到所需行的方式,又称访问类型,常用的访问类型(性能依次从最差到最优): NULL > system > const > eq_ref > ref > range > index > ALL:
NULL mysql能够在优化阶段分解查询语句,在执行阶段不用再访问表或索引,例如:在索引列中选取最小值,可以单独查找索引列来完成,不需要在执行时访问表:
explain select min(actor_id) from actor
const、system mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings的结果)。用于primary key或者unique key的所在列与常数比较,所以表最多有一个匹配行,读取一次,速度比较快。system是const的特例,表里只有一条数据时为system
explain extended select * from (select * from film where film_id = 1) tmp;
eq_ref : primary key 或者 unique key索引的所有部分被连接使用,最多只会返回一条符合条件的记录,这可能是在const之外最好的连接类型;
explain select * from film_actor left join film f on film_actor.film_id = f.film_id
ref 相比eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的前缀部分,索引要和某个值想比较,可能会找到多个符合条件的行:
explain select * from film where title = 'AFRICAN EGG'
range 范围扫描通常出现在in(),between, > , < >=等操作中,使用一个索引来检索给定的范围:
explain select * from actor where actor_id > 2
index: 扫描全表索引,这通常比ALL快一些,index是从索引中读取的,而ALL是从硬盘中读取
# 查询索引列数据
explain select film_id,title from film
ALL : full table scan 全表扫描,MySQL将遍历全表以找到匹配的行;
explain select * from payment
(5) possible_keys
显示查询可能使用的索引,explain时可能出现possible_keys有列,而key显示null的情况,这种情况因为表数据不多,mysql认为索引对查询帮助不大,选择了全表查询。
如果该列为NULL,则没有相关索引,在这种情况下,可以通过检查where子句看是否可以床罩一个适当的索引来提高查询性能,然后用explain查看优化结果。
(6) key
显示实际使用的索引,如果没有使用索引,则该列是NULL,如果想强制mysql使用或忽视possible_keys中的索引,在查询时使用force index、ignore index。
(7) key_len
这一列显示了mysql在索引力使用的字节数,通过这个值可以计算出具体使用了索引(特别是联合索引)中的那些列:
explain select * from film_actor where film_id =10
key_len,一般取表定义的字段长度:
类型 | key_len | 说明 |
---|---|---|
char(n) | n字节长度 | |
varchar(n) | n+2,2字节存储字符串长度 | 如果是utf-8,则长度3n+2 |
tinyint | 1字节 | |
smallint | 2字节 | |
int | 4字节 | |
bigint | 8字节 | |
date | 3字节 | |
timestamp | 4字节 | |
datetime | 8字节 |
如果字段允许为NULL,则需要额外的1字节记录是否为NULL
(8) ref
此列表示在key列对应的索引中,表查找所用的的列或者常量,常见的有:const 常量,字段名
(9) rows
预估的需要读取的数据行数
(10) Extra
额外的信息,常见的值有:
Using index: 查询的列被索引覆盖,并且where筛选条件时索引的前导列,是性能高的表现,一般是使用了覆盖索引(索引包含了所有查询字段),对innodb来说,如果是辅助索引性能会有不少提高:
explain select film_id from film_actor where film_id =10
Using where : 查询的列未被索引覆盖,where筛选条件非索引的前导列
explain select * from actor where first_name = 'b'
Using where Using index: : 查询的列被索引覆盖,并且where筛选条件时索引列之一但不是索引的前导列,意味着无法直接通过索引来查询符合条件的数据:
explain select rental_date from rental where customer_id >10
NULL 查询的列未被索引覆盖,并且where筛选条件时索引的前导列,意味着用到了索引,但是部分字段未被索引覆盖,必须通过回表来实现,不是纯粹的用到了索引,也不是完全没用到索引
explain select * from film_actor where film_id = 1
Using index conditon 与Using where类似,查询的列不完全被索引覆盖,where条件中是一个前导列的范围:
explain select * from film where language_id > 1
Using temporary mysql需要创建一张临时表来处理查询,出现这种情况一般是要进行优化的,首先想到是用索引来优化
explain select distinct first_name from actor;
Using filesort mysql 会对结果使用一个外部索引排序,而不是按照索引次序从表里读取行,此时mysql会根据连接类型扫描所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息,这种情况下,一般也要考虑使用索引来优化:
explain select * from actor order by first_name;
Impossible WHERE
where语句没付符合条件的行
explain select * from actor where 1!=1
Select tables optimized away 仅通过索引,优化器可仅仅从聚合函数结果中返回一条记录,据查询资料了解到,仅仅当引擎是engine=MyISAM,查询中有min() max() count() 聚合查询函数时,返回Select tables optimized away :
// 创建myisam引擎表
drop table if exists actor_myisam;
create table actor_myisam
(
actor_id smallint(5) unsigned auto_increment
primary key,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update timestamp default CURRENT_TIMESTAMP not null
on update CURRENT_TIMESTAMP
) engine = MyISAM
charset = utf8;
create index idx_actor_myisam_last_name
on actor (last_name);
// 导入actor 表
insert into actor_myisam select * from actor;
// max
explain select max(actor_id) from actor_myisam;
// min
explain select min(actor_id) from actor_myisam;
// count
explain select count(actor_id) from actor_myisam;