目录
1 实践准备
MySQL官方文档:MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format
示例SQL语句:
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`) #主键索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22
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);
partitions:是否使用分区;绝大多数不使用
filtered:利用公式:rows*filtered/100可以估算出与其它表的关联的行数
show warning:1 SQL错误信息 2 MySQL优化语句后的执行语句
2 字段含义
2.1 select_type
simple:简单查询;不包含子查询和union
primary:复杂查询中最外层select
subquery:包含在select中的子查询
derived:包含在from中的子查询;MySQL会将结果放在临时表中
union:在union中随后的select
示例:
set session optimizer_switch='derived_merge=off';
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
上述语句涉及三条查询,展示三种查询类型
2.2 id
语句执行的优先级&#x