java如何理解在Mysql上使用“explain”的结果中的“type”字段?
当我在Mysql上使用“explain”查看有关语句执行的一些信息时,字段类型的值让我非常困惑
表格创建语句
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-2 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
当我执行
explain select * from film
left join film_actor on film.id = film_actor.film_id
left join actor on actor.id = film_actor.actor_id
where film.id = 1;
每个表的类型如下所示 |表|类型| | ---------- | ----- | |胶片常数| |电影演员|参考| |演员|全部|
当我执行
explain select * from film
left join film_actor on film.id = film_actor.film_id
left join actor on actor.id = film_actor.actor_id
eq_ref
被解释为One row is read from this table for each combination of rows from the previous tables.
ref
被解释为All rows with matching index values are read from this table for each combination of rows from the previous tables.
但这两个查询之间的区别只是where条件,没有修改相关字段。为什么在这两个查询中film_actor
和actor
表的类型完全不同?如何理解这一现象
提前谢谢
共 (0) 个答案