Explain详解与索引最佳实践

听课问题(听完课自己查资料)

type中常用类型详细解释 null <- system <- const <- er_ref <- ref <- range <- index <- all

Explain 各列解释

EXPLAIN SELECT
	* 
FROM
	actor
	LEFT JOIN film_actor ON actor_id = actor.id;

1. id

代表执行的先后顺序比如现在依次有 3、2、1、2 那么执行顺序就是 3、2(第一次出现的2) 、2(第二次出现的2)、1

总结：越大执行越靠前，如果id形同那么最先出现的最先执行

2. select_type

分为四种类型 simple、subquery、derived、primary

a. simple

简单查询比如就一个简单的单条语句查询 EXPLAIN SELECT * FROM actor;

例如这样单表查询没有进行关联查询也没有关联其他表有临时表查询

b. subquery

在select后面的称为 subquery类型比如这个查询

EXPLAIN SELECT (SELECT id FROM film_actor) as id FROM actor;

其中select后面括号中的就是一个subquery类型

c. derived

是跟在from后面组成的临时表，如下SQL

EXPLAIN SELECT te.* FROM (SELECT * FROM actor) as te;

其中from后面又跟了一个子查询并且这个子查询自己构建了一个临时表所以这个子查询就是derived类型

d. primary

最外层的查询语句

3. table

就是当前这一列正在查询的表名称
但是如下图

explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

因为有构建一个临时表，所以上边id=1的一列是一个临时表并没有真正的名称，但是id=3那一条正是构建这个临时表的子查询sql 所以显示 <derived3>后面的3就是指向的id 这个示例表示临时表是id=3的子查询sql生成的。

4. type

分为好多种，常见的有(最优在前)： null - system - const - eq_ref - ref - range - index - all

a. null

速度是最快的，相当于没有经过这张表查询或者索引

explain select min(id) from film;

上边这个根据主键id 查询最小的主键id，在主键索引中都是排序的，所以第一个id肯定就是整张表中最小的，所以查询的时候什么都不用管无脑拿表中第一个id就行了，那么肯定是最快的，因为后面有什么数据根本不关心，只需要拿第一个id

b. system

仅次于null

流程: 相当于在一个只有一条数据的表中查询

EXPLAIN SELECT te.* FROM (SELECT * FROM actor WHERE id = 1) as te

id = 1 的执行计划中可以看到 type = system

因为在查询的时候from后面有一个子查询SQL ，这个子查询SQL有一个条件只能查询出来一条数据组成一个临时表，但是只有一个数据所以最外边的数据相当于什么都没干直接拿这条数据就可以了；使用show wranings查看就可以发现

select '1' AS `id`,'a' AS `name`,'2017-12-22 15:27:18' AS `update_time` from dual

最终优化后的结果 select 后面都是常数 from查了一个虚拟的空表，意思就是表只有一个数据的时候直接就将值覆盖给了select中，不会再去查表了，这样肯定是很快的

c. const

EXPLAIN SELECT
	* 
FROM
	actor 
WHERE
	id = 1;
SHOW WARNINGS;

SELECT
	'1' AS `id`,
	'a' AS `name`,
	'2017-12-22 15:27:18' AS `update_time` 
FROM
	`hmh_test`.`actor` 
WHERE
TRUE

比system慢

代表了是使用了主键索引或者唯一键索引，走了索引并且只能查出来一条那么肯定也是很快的，其实也是相当于常量,和system比会慢，因为const总归还是要筛选表中数据的，而system是直接将这条数据拿出来即可根本不需要筛选

d. eq_ref

是使用了联合查询，并且这个联合查询是走的唯一索引的

e. ref

MySQL表中索引有唯一索引还有非唯一索引当使用的索引是非唯一索引，虽然走了索引但是可能有多条数据就会是ref

f. range

是使用的范围查询比如 select * from actor where id > 10

这样虽然id为主键索引，走主键索引应该会快的，但是因为是使用的范围查询，在表中说不定有成千上百万数据都是id > 10 ，在查询这么多的数据效率也不会快的

g. index

其实是全表全表扫描二级索引，这样是比较慢的，其实也就相当于扫描了全部数据了，只不过是二级索引，会比all性能好，但是遇到了index也是需要优化的

h. all

全表查询，可以是试着将select中查询数据返回的值设置为联合索引中的字段这样就会走index二级索引了

5. possible_keys

代表了可能会走的索引如果是null那么很有可能会走全表扫描

6. key列

是实际上sql会走得索引

7. key_len列

是当前所走索引中走到的字段所有的长度和

比如 key_test(name,age,gend)

但是 where name = '' and age = '';

只用到了 name 和 age 没有用到gend但是也走了key_test索引这时候key_len长度就是 name和age总和没有gend

CREATE TABLE `actor` (

`id` int NOT NULL,

`name` varchar(45) DEFAULT NULL,

`update_time` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `test` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

上边表中比如写一个 explain select * from actor where name = ''; 并且数据库编码格式为 utf8

那么 key_len 就为 138 因为 utf8 并且走了索引那么计算方式就是 3n+2 其中n就是设置字段的长度 3 * 45 + 2 = 137 但是因为 name 是可以为 null 那么该字段会额为添加 1 长度用来计算该字段是否为 null 所以总的下来是 137 + 1 = 128

如果name不为null 那么就是128

key_len计算规则如下：

字符串，char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字

占3个字节

char(n)：如果存汉字长度就是 3n 字节

varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串

数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节

时间类型

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索

引。

8. ref列

以上就是说该条sql中所用到的索引字段的值是什么。比如显示const 代表常量、表中字段名

比如

explain select * from actor ac left join film_actor fa on fa.id = ac.id where ac.name = 'a';

使用两个表的id进行关联并且 ac.name 为索引所以该条sql走了索引

第一个ref = const 是因为 where ac.name = 'a' 中直接写死了 a 所以 a 代表常量

第二条是 ac.id 代表film_actor表关联使用索引 id关联的，相当于一个条件 id值等于 ac.id

9. rows列

mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

10. Extra列

using index 覆盖索引：如果为null 代表是走了索引但是是二级索引而且该sql所需字段二级索引中不能全部包含所以需要去主键索引中回表如果是 using index 代表不需要回表，二级索引已经全部包含该sql所需的全部字段或者直接走的主键索引

using where 使用where条件处理，没走索引

Using temporary 代表没有走索引而且后面还没有跟where条件全表扫描需要优化

Using index condition 使用索引，但是比如使用了联合索引 age、name 但是只使用age 而且条件为 where age > 10 这种情况就会出现 Using index condition

Using filesort 代表使用了 order by name 但是name并不是索引如果name是索引那么就会是 using index

面试问题

1. 关于为什么有时候不走主键索引而是走二级索引？

表如下

CREATE TABLE `film` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

可以看到film只有两个字段一个是主键id 和索引 name 所以这个表会构建两个树形索引一个是主键索引一个是name构建的二级索引

当我们查询的时候例如 EXPLAIN SELECT id,name FROM film;

上图可以看到这个语句走的是 name二级索引，但是index也就是二级索引会慢，那么为什么默认还会走二级索引呢？

答案如下：

因为二级索引使用的是name 所以叶子节点都是 name 而非叶子节点都是主键id

他们叶子节点 + 非叶子节点就能组成一个完整的SELECT后面所需字段信息，那么就会走二级索引，如果发现满足不了select后面需要的信息那么就会走主键索引。

比如

CREATE TABLE `actor` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `test` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

表中写一个 EXPLAIN SELECT * FROM actor;没走索引

但是如果加了

EXPLAIN SELECT id,name FROM actor; 会走test索引，因为id是非叶子节点 + 叶子节点name 正好构成了select后面需要的信息

a. 为什么会走二级索引？

因为主键索引中非叶子节点存储的都是完整信息，占用内存比较大，并且MySQL内部会对SQL进行优化，会自动分析走主键索引比较好还是走二级索引比较好。这是MySQL自己优化算法选择的。

而如果select语句后面所需的字段二级索引都包含那么就会走二级索引，因为二级索引叶子节点只有主键id存储小，二级索引自身就可以拼出来这条语句所需字段也不需要回表，肯定是比走唯一索引好的

比如表firm 中name字段为非唯一索引

select name from firm；其实type = index

为什么会走二级索引：因为二级索引里面就是使用的name作为索引的而且select后面正好是只有name，二级索引可以满足当前查询，所以找到叶子节点的主键id给主键索引中找到该数据，可以减少内存的占用

b. 为什么还有规避掉 type = index呢

为什么不推荐index：因为index其实扫描整个二级索引，找到所有的唯一键id找去主键索引中找数据相当于二级索引全部遍历查询并且去主键索引查找回表肯定速度会慢

优化方式：后面添加索引条件比如 where name = ？这样就不会导致二级索引全表扫描了，即使回表也是回表的次数减少了

2. 为什么使用like '%aa%' 就会索引失效而 like 'aa%'就不会失效

因为 like '%aa%' 在索引树中字段都是有序的，这也是mysql索引快的主要原因，但是如果使用了一个字段前后都模糊查询就会导致字段变得无序需要全表查询比如 name like '%l%'

会发现 l 在第一个区间和第二个区间都出现了，但是第一、二、三区间都是排序好的，肯定是不能从第一区间直接就知道第三区间也包含 I

如果使用后面模糊查询，前面不模糊查询 name like 'B%' 就会知道第一个区间找到以后第二区间第一个H开头，后面首字母只会越来越大就不会再去找了，这样就可以走索引

使用mysql注意点

使用左走匹配原则
函数类型转换避免比如 age 是int 类型查询的时候 where age = '1' MySQL也可以接受后面 string写法因为会自动将类型转换这样就会导致不走索引不同版本优化不一样可能会将age转为 string 可能会将 '1' 转为 int 1
尽量使用覆盖索引如果查询是查询 type = all 那么可以将返回的数据尽量是联合索引中的字段这样 type = index
比如 key_test(name,age,gend) 查询语句可以为三个字段打乱组合比如 where age = '' and name = '' and gend = ''；mysql会优化为最左前缀但是不能where 没有 name 这样不符合最左前缀原则也优化不出来最左前缀
尽量不适用 in 、 or 、 > 、<因为in就算是索引字段但是如果 in中太多 mysql优化的时候认为还没有全表快也可能走全表扫描
不使用 != 、is null 、is not null 会全表扫描
like 使用 %a% 会导致索引失效
联合索引中间字段不使用范围的条件比如 key_test(name,age,gend) 然后sql条件为 where name = 'a' and age > 10 and gend = 10 那么 name = 'a' and age > 10 都会走索引而 gend = 10 不会走索引，按道理 key_test联合索引是有 gend的但是为什么没有走索引就是因为使用 age > 10 找到数据以后后面的 gend就是无序的了