mysql优化（3）——explain详解索引优化实战

最新推荐文章于 2022-04-02 18:02:55 发布

hello_wang_world

最新推荐文章于 2022-04-02 18:02:55 发布

阅读量269

点赞数

文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/hello_wang_world/article/details/121735763

版权

explain 详解

#示例表
DROP TABLE IF EXISTS `actor`;
 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','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);

explain select * from actor

根据这个命令查询结构
在这里插入图片描述
这个结果是根据查询的表，查询语句有两个表就两条结果，一个表就一个

explain 两个变形语句

官方文档参考
1). explain extended: 这个语句在版本5.8中已经不能使用了，只能使用在5.8以下使用这个语句，配合 show warnings; 的到一个优化后的sql语句下图2的红框部分就是优化的语句，对比自己的语句知道优化了什么；还另外多了个filtered这个列，这是一个半分比的值；根据rows*filtered/100 估算出扫描的explain前一个表的连接行数（前一个表是explain比explain 当前id 小的表）

explain extended  select * from actor where id =1; # 5.8 一下使用 explain extended  ;8直接使用 explain
show warnings;

在这里插入图片描述

2). explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区其实这个5.8的版本里也是有了只要使用 explain 就可以了

explain 工具中的列

1.id列
id的编号是查询的需要，有几个表就有几个id ,id越高优先级越大越先执行；id相同就是从上而下执行，id为null 最后执行
2.select_type列
select_type 表示的是查询的复杂程度；
1）.SIMPLE 简单查询
2）.PRIMARY 复杂查询的最外层select;
3). SUBQUERY 包含在select 子查询中，但是不包含from后
4). DERIVED 包含在from 子查询中；一般mysql查询结果集的临时表，也叫派生表

set session optimizer_switch='derived_merge=off';# 关闭对衍生表的合并优化5.7以后的特新
explain SELECT (select 1 from film where id=1) from (SELECT * FROM actor where id=1) der

在这里插入图片描述
5).union 第二个语句或者在union关键字后面的表
6）union result 这个给使用有了union的语句

explain select 1 from film where id=1 UNION select 1 FROM actor where id=1

在这里插入图片描述
还有很多我只是介绍部分
3.table 列
表是explain 在访问哪个表；当from 有子查询的时候table 值为derivedN 这个N等于最先执行的表ID也可以认为是最后的结果集表但是没有真实的表指向
当union result 是我们的table值为union1,2;1，2就是两个explain的id ,其实也就是没有真是的表指向结果集
4.parittions列
这个表指向的是分区当我们的表进行分表操作的时候就会出现这个列的值
5.type 列
这一列表示关联类型或者访问类型 如何查询表中行和查询的大概范围
依次从最优到最差：system>const>eq_ref>ref>range>index>all
一般来讲我们优化sql就是优化到range,最好能到达ref
1).NULL：mysql 能够分解查询语句,在查询的时候不需要在查询索引或表；例如查询表中最大的ID，执行的时候直接访问到我们的索引，不需要访问表

explain select MAX(id) FROM actor

在这里插入图片描述
2).system ,const: 查询语句的更具show warining;给出的优化提示可以优化成一个常量的存在一般使用就是system 或者const ;system 是const的特殊情况，且system 是存在在虚拟表上面的

explain select * FROM (select *from actor where id in(1)) der;
show warnings;

在这里插入图片描述

3). eq_ref: 主键和非主键关联查询，且没有重复数据的，这个要比const 之外最好的链路查询，简单查询不可能出现；

explain select *from actor a INNER   JOIN  film_actor b on a.id=b.actor_id ;

在这里插入图片描述
**4).ref: ** 可以是二级缓存的查询；还有也可以是关联查询使用普通索引或者唯一索引的前缀最左前原则；查询出来的值可能重复
**简单查询 **

explain select *from  film  where name='film'

在这里插入图片描述
关联查询

	#其实这个语句有个问题去思考id 我这么写的用意是想走主键索引，但是实际没走为啥呢？因为我们的mysql的优化器回去判断开销显然我们的主键索引大于二级索引所以走二级索引，那当我们去掉二级索引会不会走主键索引呢？这个后面我会讲	
	explain select b.id from film a LEFT JOIN film_actor b on a.id=b.film_id

在这里插入图片描述
5).range : 范围扫描，通常出现 in(),between,>等；使用索引来检索的范围

EXPLAIN select * from film_actor where film_id>1

在这里插入图片描述
6).index: 这个一般就是扫描我们二级索引的所有的叶子；

EXPLAIN select *  from film

在这里插入图片描述
7).ALL: 这个就是扫描聚簇索引，通常这个索引是表中最大的索引所以比较慢一般这种情况是必须要去优化的

EXPLAIN select *  from actor

在这里插入图片描述
** 注意：上面讲解只是在某种情况下会发生的，只要演示一下在什么大的sql语句之下得到什么样的执行类型，实际这些类型都是根据sql的优化器去算去一个cost 的值根据值的大小来推算是否去走索引**

6. possible_keys列
这一列实际是它预计使用哪个索引，但是如果key 为空就是没有使用该索引，这个可能是应为数据少走全表扫描的速度快于索引从而没有使用索引。
当这列为空的时候，我们就需要是否创建一个where 的来使用一个索引，而达到优化的目的

7.key 列
这列就是当我们sql发生运行了实际是否使用到的索引名称；当然我们也可以强制使用索引或者忽略索引但是前提是possible_keys 有值
在语句中使用 force index(索引名称)、ignore index(索引名称)。
8.key_len列
这一列就是记录了索引的字节，通过这个可以计算出符合索引使用那些索引；
举例 film_actor这个表有一个idx_flim_actor_id的联合索引使用flim_id,actor_id列每列都是4个字节int类型，所以这个索引的字节就是8个

EXPLAIN select *FROM film_actor where film_id=1

在这里插入图片描述
从这个例子看我们ken_len的值是4 所以我们因该是使用了一个列作为了所以，遵循左前缀原则那么我们是用来联合索引的film_id列；
ken_len 计算的字节如下：
字符串： varchar(n): 3n+2 ,char(n):3n
数据类型： int: 4 , tinyint:1 , binint:8 , smalint:2
时间类型： date: 3 , timestamp: 4 , datetime:8
索引的最长长度是768个字节如果超过这个长度mysq就会做一个类似左前缀原则，截取前半部分作为索引；
9.ref 列
这列主要显示key中使用的列或者常量（const）
10.rows 列
这个就是我们扫描的行数，这个行数不是结果集行数
Extra 列
这个列用来记录一些额外的值
1).Using index 使用覆盖索引
覆盖索引就是我们在使用查询语句的时候会使用索引这时候key就会有值出现如果我们的查询的值，字段完全属于我们的索引的话且不需要回表这时候我们就会使用到覆盖索引

EXPLAIN select film_id from film_actor where film_id=1

在这里插入图片描述
2).Using where 使用where 来处理结果，查询的列未使用到索引


explain select * from actor where name='a'

在这里插入图片描述
3).Using temporary 这个就是当查询建立临时表的时候可能会发生这种情况
注意：这一列里面的值我就讲到这里，官方文档里有很多；这些情况也不都是一定这样啊，这里也就是能简单演示，为啥呢？随着数据的变化同一个语句执行的着这种结构可能都不一样；这个也是跟它mysql 优化器有关系

索引实践

#示例数据
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
 PRIMARY KEY (`id`),
 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

 INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei',21,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

1.全值匹配
通过上面的表数据知道这个表创建了一个联合索引idx_name_age_position;这个联合索引使用到了name,age,position 这三个列；

explain select *from employees where name="lilei"

在这里插入图片描述
从上面使用到联合索引的name字段,ken_len为74 ，因为varchar(24)则24*3+2

explain select* from employees where name="lilei" and age=22

在这里插入图片描述

explain select* from employees where name="lilei" and age=22 and position='manager'

在这里插入图片描述
2.最左前缀原则
就是在我们查询中如果想使用联合索引，我们的查询条件必须是从左往右的并且不能跳过列；
向我们的上面的几个示例都是走的索引因为他们的条件语句都是从name 开始的而且遵循了最左前缀原则
示例

explain select *  from employees where  position='manager' #这个语句就不会走索引

在这里插入图片描述

explain select *  from employees where name='lilei' and  position='manager' #这个会走索引那也只会是name 走了而已

在这里插入图片描述
为什么索引必须遵循最左前缀原则？
其实这个问题我第一章数据结构就讲的比较清楚了。我这边在简单的概述一下我们索引其实就是排好序的数据类型；联合索引就是根据列去对比排序，在我们这个示例当中他是先去判断name的大小，如果name有大小区别则按照我们name列排序；如果name列完全相同则按照age 排序，同理如果age也排不出那么会继续向下；那么问题来如果你的查询没有遵循这个原则我们就没有办法通过二分查找到你的索引上的数据，那么这时候只能全表扫描了

3. 不在索引列上做操作（计算，函数等），会让我们索引失效转成全表扫描

explain select *from employees where LEFT(name,5)="lilei"

在这里插入图片描述
4.不能使用范围查询右边的列，当前使用的范围列可以用索引，这个列后面列走不到索引


explain select name ,age,position from employees where name="lilei" and age>22 and position='manager'

在这里插入图片描述
**5.尽量使用覆盖所有去查询，少使用select * **

explain select name ,age,position from employees where name="lilei" and age=22 and position='manager'

在这里插入图片描述 6.在sql 语句中使用我们不等于（<>,!=）not in ，>,<,<=,>= 这些会根据优化器的优化来确定是否使用我们的索引

EXPLAIN SELECT * FROM employees WHERE age != 22

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE name != 'lilei'

在这里插入图片描述
7. is null 和is not null 一般的情况也不会去索引
8.使用模糊查询其实也要遵循最左前缀原则才能走索引否则可以优化成覆盖索引，如果是用不了覆盖那就没有办法优化了，只能借助第三方搜索引擎，这个搜索引擎我会在更新完mysql优化后更新ELK


EXPLAIN SELECT * FROM employees WHERE name like '%lilei' # 这个语句可以写成 SELECT name,age,position FROM employees WHERE name like '%lilei' 覆盖索引

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE name like 'lilei%' # 这样就满足了最左前缀得原则所以一般都会走索引

在这里插入图片描述
9.少用 or 或者in 因为这个也不缺定会走索引会根据我们mysql 得优化器来判断
**10.查询范围优化这种范围如果数据小的时候可能不会走索引，我们可以将范围得数据变小让他走索引 **

在这里插入图片描述
**总结: 我们在开发中使用得mysql索引，根据sql优化索引，使我们得查询得效率变高，但是我们MySQL得优化也不只是这一方面，因为有的优化单从一方面得考虑是不能达到完善得，比如我们在使用多表联查得时候使用索引优化就不可能优化得很好，那我们可能就需要借助java或者其他语言代码来进行差分后在优化sql; **

hello_wang_world

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql优化（3）——explain详解索引优化实战

explain 详解#示例表DROP TABLE IF EXISTS `actor`; 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
复制链接

扫一扫