准备测试数据
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;
1. simple :简单select查询,查询中不包含子查询或union
2. primary :复杂查询中最外层的select
3. derived : 包含在from子句中的子查询。派生出一张表临时存放子查询的结果集
4. union : 在union中的第二个随后的select并集去重查询
5. union result :从union临时表检索结果的select,并集去重查询后派生出一张临时表所存放的结果
6. 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;
展示的额外信息
1. distinct 一旦mysql找到了与行相关联匹配的行,就不再搜索了
2. using index 发生在对表的请求列都是同一索引的部分的时候,返回的列数据只使用了索引中的信息,而没有再去访问表中的杭机路, 是性能高的表现。
3. using where mysql服务器将在存储引擎检索行后再进行过滤,就是先读取整行数据,再按照where条件进行检查,符合就留下,不符合就丢弃。
4. using temporary 使用临时表来中间操作。效率低下,解决: 为查询的列创建索引
5. using filesort 采用文件扫描对结果进行计算排序,效率低下 解决: order by 的列要出现在select字段中。