- SIMPLE:表示简单查询
- PRIMARY:表示复杂查询中的最外层的select查询语句
- SUBQUERY:表是子查询语句 跟在select 关键字后面的select查询语句;
- derived: 派生查询,跟在一个select查询语句的from关键字后面的select查询语句
如果这一列有值,但是key列为null,那是因为mysql觉得可能会使用索引,但是又因为表中的数据很少,使用索引反而没有全表扫描效率高,那么mysql就不会使用索引查找
该列如果是NULL,则没有相关的索引,考虑where条件语句的字段加索引
详情参考 http://blog.wingflare.com/2019/10/y2x1d5wv3q6prl3m.html
https://blog.csdn.net/it_tifarmer/article/details/111566731
有以下几张表的情况:
-- 演员表 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‐22 15:27:18'), (2,'b','20 17‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18'); -- 电影表 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'); -- 演员和电影中间表 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);
从好到差的顺序为:system > const > eq_ref > ref > range > index > all; 一般来说我们优化到range就可以了 最好到ref
null:type字段的值如果为null,那么表示当前的查询语句不需要访问表,只需要从索引树中就可以获取我们需要的数据
explain select id from actor where id =1;
system/const:用户主键索引或者唯一索引查询时,只能匹配1条数据 一般可以对sql查询语句优化成一个常量,那么type一般就是system或者const,system是const的一个特例。
explain select * from (select * from film where id = 1) tmp;
eq_ref:在进行连接查询时,例如left join 时,如果是使用主键索引或者唯一索引连接查询 ,结果返回一条数据,则type的值为一般为eq_ref。
ref:相比较eq_ref,不使用主键索引或者唯一索引,使用的是普通索引或者唯一索引的部分前缀,索引与一个值进行比较后可能获取到多个符合条件的行,不在是唯一的行了。
唯一索引的部分前缀:组合索引是唯一索引的一类,这里指组合索引的前缀部分入组合索引a_b_c,那么a和a_b是唯一索引a_b_c的前缀部分
range:通常使用范围查找,例如between,in,<,>,>=等使用索引进行范围检索。
explain select * from film where id >2;
index:扫描索引树就能获取到的数据,一般是扫描二级索引,并且不会从根节点扫描,一般直接扫描二级索引的叶子节点,速度比较慢。因为二级索引叶子节点不保存表中其他字段数据 只保存主键,所以二级索引还是比较小的,扫描速度相比All还是很快的。这里用到了覆盖索引,什么是覆盖索引:可以直接遍历索引树就能获取数据叫做覆盖索引。这里遍历name索引树就可以获取到主键id的值就是覆盖索引。
explain select id from film ;
ALL:这是一种效率最低的type,需要扫描主键索引树的叶子节点,获取数据是表中其他列的数据,即全表扫描(扫描主索引树的叶子节点)。
explain select remark from film ;
注意:如果字段允许为 NULL,需要多1字节记录是否为 NULL
假如为某个表的一下三个字段建立组合索引(name_age_phone)
name
varchar(50) DEFAULT NULL COMMENT '姓名',
age
int(11) DEFAULT NULL COMMENT '年龄',
phone
varchar(12) DEFAULT NULL,则完全使用索引的key_len计算为:key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)