准备测试数据:
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
;