Explain与索引实践

学习目标：

1. Explain熟练使用
2. B+树底层分析常见索引
3. 优化实践

---

学习内容：

1.explain
例如:一条sql:
explain select * from film where id = 2;

explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可 以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个半分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的 表）。

---

select_type列:
simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含 义）
例如:
sql:
set session optimizer_switch=‘derived_merge=off’;//关闭mysql5.7新特性对衍生表的合 并优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

以上的sql涉及到了三个查询外层的primary复杂查询、对actor的子查询subquery以及对演员表的衍生查询derived(在from后面生成的临时表)，
id代表sql的执行顺序，id越大代表sql越优先执行，如果id相等那么谁排在前面谁越先执行

key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通 过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
例如:explain select * from film_actor where film_id = 2;
查出来的key_len=4(一个整型字节=4)
就是用到了一个整形的film_id索引
如果:explain select * from film_actor where film_id = 2 and actor_id=2;
查出来key_len=8(两个整型)

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会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索 引。

type列
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL 一般来说，得保证查询达到range级别，最好达到ref

1.Null:
例如:explain select min(id) from film
这句sql会直接从主键索引中扫描拿到最小值，并不会去访问表
因此执行计划中的type为null.
2.system、const
例如:explain select * from (select * from actor where id=1) temp;

const:查询的条件用的是主键 如 where id=? 那么查询出来的结果只会有一条
，与查询出来的常量的查询效果差不多效率非常之高类比于:select 1 from dual.
system:select * from (一条记录的表) 从一条记录的表里面查询，效率非常之高，算是const的一种特殊情况。

3.eq_ref
例如sql: explain select * from film_actor left join film on film_actor.film_id = film.id;

如图所示执行顺序是先执行film_actor的全表扫描再去执行film中的id=film_actor中扫出来的film_id。
使用的是primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type，基本不需要再优化。

4.ref
根据二级索引查到记录(可能存在多条记录)
例如:select * from film where name =‘张三’;
还例如关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分(用到两个字段联合索引的左边那个做索引的左端去关联表);
explain select film_id from film left join film_actor on film.id = film_actor.film_id;

5.range
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
效率也还行，但是如果结果集过大效率也会底下，需要对条件去做优化。

6.index
扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些，效率并不是很高，底层是去遍历索引。
例如: explain select * from film;

这里扫描二级索引是因为扫描磁盘量的问题，同主键索引(聚簇索引)来比较二级索引只存放了索引字段-主键id，较主键索引来说扫描更快，而聚簇索引是存放着id-整行记录更加庞大扫描速度不如二级索引。因此在mysql成本计算的时候如果select +字段 from 表 中字段如果在主键索引中有且二级索引中也有，那么mysql会优先走二级索引

7.All
即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

Extra列
1.Using index：使用覆盖索引(一种查询方式)
例如:explain select film_id from film_actor where film_id = 1;
解释:在查询的过程中把结果字段已经查出，只需要扫描索引树即可，无需回表
2.Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围;
explain select * from film_actor where film_id > 1;
3.Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索 引来优化。
explain select distinct name from actor;(去重操作会用到临时表)
优化:给name加索引即可(mysql会在扫描索引树的时候去进行筛选数据，因为B+树是排好序的)
4.Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。
explain select * from actor order by name;
优化:给name加索引即可(索引树中已经排好序了，因此直接扫索引就结束了)

实践:
创建一张employees表
create table employees
(
id int auto_increment
primary key,
name varchar(24) default ‘’ not null comment ‘姓名’,
age int default 0 not null comment ‘年龄’,
position varchar(20) default ‘’ not null comment ‘职位’,
hire_time timestamp default CURRENT_TIMESTAMP not null comment ‘入职时间’
)
comment ‘员工记录表’ charset = utf8;

create index idx_name_age_position
on employees (name, age, position);
//插入三条数据
INSERT INTO employees(name,age,position,hire_time) VALUES(‘LiLei’,22,‘manager’,NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES(‘HanMeimei’, 23,‘dev’,NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES(‘Lucy’,23,‘dev’,NOW());

1.全值匹配
EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’;
EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age = 22;
EXPLAIN SELECT * FROM employees WHERE name= ‘LiLei’ AND age = 22 AND position =‘manager’;(这里条件换一下顺序也是会走索引，mysql会根据最左前缀法则帮我们重新排序，但是我们尽量根据自己创建索引的顺序去写不要让mysql去帮我们优化)

2.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
EXPLAIN SELECT * FROM employees WHERE name = ‘LiLei’;
EXPLAIN SELECT * FROM employees WHERE left(name,3) = ‘LiLei’;
(取了前面三位的话在索引树中，是无法走索引树。)
给hire_time加索引然后用date函数取条件也会导致索引失效(索引树中只有原生的值，因此索引树是没有)
EXPLAIN select * from employees where date(hire_time) =?;
优化:可以优化成范围查找 如查询2022-04-04那天的数据可以
改成>2022-04-04 00:00:00且<2022-04-04 23:59:59

3.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

– mysql5.7关闭ONLY_FULL_GROUP_BY报错
select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,‘’));

学习时间：

• 2022/04/04

---

学习产出：

• Explain熟练使用
• B+树底层分析常见索引
• 优化实践