#准备测试数据
CREATE TABLE actor
(
id
int(22) NOT NULL AUTO_INCREMENT,
name
varchar(255) DEFAULT NULL,
update_time
datetime DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into actor
(id
,name
,update_time
) values (1,‘a’,‘2019-10-21 14:03:45’),(2,‘b’,‘2019-10-22 14:03:49’),(3,‘c’,‘2019-10-23 14:03:52’);
CREATE TABLE film
(
id
int(22) NOT NULL AUTO_INCREMENT,
name
varchar(255) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into film
(id
,name
) values (1,‘film1’),(2,‘film2’),(3,‘film0’);
CREATE TABLE film_actor
(
id
int(22) NOT NULL AUTO_INCREMENT,
film_id
int(22) DEFAULT NULL,
actor_id
int(22) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
insert into film_actor
(id
,film_id
,actor_id
) values (1,1,1),(2,1,2),(3,2,1);
一. explain 之 id
EXPLAIN SELECT (SELECT 1 FROM actor WHERE id = 1)
FROM (SELECT * FROM film WHERE id = 1) der;
三种情况
1.id相同,表示加载表的顺序是从上到下
2.id不同,值越大优先级越高,越先执行
3.id有相同也有不同,id相同的可以认为是一组,从上往下执行,在所有组中,id值越大,优先级越高,越先执行。
二. explain 之 select type
EXPLAIN SELECT * FROM film;
EXPLAIN
SELECT (SELECT 1 FROM actor WHERE id = 1)
FROM (SELECT * FROM film WHERE id = 1 UNION SELECT * FROM film WHERE id = 1) der;
simple :简单select查询,查询中不包含子查询或union
primary :复杂查询中最外层的select
derived : 包含在from子句中的子查询。派生出一张表临时存放子查询的结果集
union : 在union中的第二个随后的select并集去重查询
union result :从union临时表检索结果的select,并集去重查询后派生出一张临时表所存放的结果
subquery: :包含在select中的查询,子查询
三. explain 之 table
从哪个表中读取数据
四. explain 之 partitions
分区表
如果没有设置分区的话,则为null
五. explain 之 type
EXPLAIN SELECT * FROM (SELECT * FROM film WHERE id = 1) tmp;
EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id
= film.id
;
EXPLAIN SELECT * FROM actor WHERE id > 1;
EXPLAIN SELECT COUNT(0) FROM film;
EXPLAIN SELECT * FROM actor;
type 含义:关联类型或访问类型,MYSQL决定如何查找表中的行
NULL MySQL不访问任何表,索引,直接返回结果
system 表只有一行记录(等于系统表),这是const类型的特例,一般不会出现
const 表示通过索引一次就找到了,const 用于比较primary key 或者 unique 索引。因为只匹配一行数据,所以很快。
如将主键置于where列表中,MySQL 就能将该查询转换为一个常量。const于将"主键" 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询,关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref 非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,返回所有匹配某个单独值的所有行(多个)
range 只检索给定返回的行,使用一个索引来选择行。 where 之后出现 between , < , > , in 等操作。
index index 与 ALL的区别为 index 类型只是遍历了索引树, 通常比ALL 快, ALL 是遍历数据文件。
all 将遍历全表以找到匹配的行
结果值从最好到最坏依次是:
最好 system > const > eq_ref > ref > range > index > ALL 最坏
六. explain 之 possible_keys(可能)
显示查询可能使用哪些索引来查找
七. explain 之 key(实际)
显示mysql实际采用哪个索引来优化对该表的访问
八. explain 之 key_len
显示mysql在索引李使用的字节数,通过这个值可以算出具体使用了索引中的那个列
九. explain 之 ref
这一列显示了在key列记录的索引中,表查找值所用到的列或常量
十. explain 之 rows
mysql估计要读取并检测的行数,这个不是结果集的行数
十一. explain 之 filtered
是一个百分比的值, rows * filtered / 100 这个结果将与前表产生交互
十二. explain 之 extra
在这里插入图片描述
EXPLAIN SELECT DISTINCT NAME FROM film LEFT JOIN film_actor ON film.id = film_actor.film_id
;
EXPLAIN SELECT id FROM film ORDER BY id;
EXPLAIN SELECT * FROM film WHERE id > 1;
EXPLAIN SELECT * FROM actor ORDER BY NAME;
展示的额外信息
distinct 一
旦mysql找到了与行相关联匹配的行,就不再搜索了
using index 发生在对表的请求列都是同一索引的部分的时候,返回的列数据只使用了索引中的信息,而没有再去访问表中的杭机路, 是性能高的表现。
using where mysql服务器将在存储引擎检索行后再进行过滤,就是先读取整行数据,再按照where条件进行检查,符合就留下,不符合就丢弃。
using temporary 使用临时表来中间操作。效率低下,解决: 为查询的列创建索引
using filesort 采用文件扫描对结果进行计算排序,效率低下 解决: order by 的列要出现在select字段中。