Explain详解与索引最佳实战

已于 2023-07-02 22:14:13 修改
阅读量382 收藏
点赞数
分类专栏： mysql 文章标签： mysql
于 2023-06-22 19:35:02 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_37174301/article/details/131343472
版权

文章目录

Explain中的列

所用的到的建表语句

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

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;

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;

id

id的列就是select的序号，有几个select就有几个ID，id越大先执行，id相同从上往下执行

select_type

select_type 主要表示是简单查询还是复杂查询
包含（simple,primary,subquery）

#关闭mysql5.7新特性对衍生表的合并优化
set session optimizer_switch='derived_merge=off';
explain 
select #primary
(select 1 from actor where id =1) #subquery
from
(select * from film where id =1)der; #derived

在这里插入图片描述
我们看到最先执行的为id=3的
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

table

这一列表示 explain 的一行正在访问哪个表。
当有子查询的时候会有 格式
在这里插入图片描述

type

表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。
从最优到最差分别是system>const>eq_ref>ref>range>index>all
我们最好优化到range

const ，system

就是where 后面条件用的主键或者唯一索引匹配一个常量，system是const的特例，表里只有一条元组匹配时为system

explain extended select * from (select * from film where id = 1) tmp; 
select * from film where id = 1

在这里插入图片描述

eq_ref

就是主键或者唯一索引被作为连接使用

explain select * from film_actor left join film on film_actor.film_id = film.id

在这里插入图片描述

ref

就是用的普通索引作为连接使用，或者唯一索引的部分前缀，或者索引要和某个值比较

1. 简单 select 查询，name是普通索引（非唯一索引）
 explain select * from film where name = 'film1';

在这里插入图片描述

2.关联表查询，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

在这里插入图片描述

range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。前提是使用了索引

explain select * from actor where id > 1;

在这里插入图片描述

index

扫描全索引就能拿到结果，一般是扫描二级索引。这种扫描一般不会从索引树根节点去扫描，直接对二级索引的叶子节点遍历和扫描

explain select id,name from film

在这里插入图片描述

ALL

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

explain select * from actor;

在这里插入图片描述

possible key

这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

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计算规则如下：
字符串：char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节，一个汉字占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, 需要一个字节记录是否为null
索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

row

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

extra

这一列展示的是额外的信息。

using index

使用的覆盖索引
覆盖索引的定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

 explain select film_id from film_actor where film_id = 1

在这里插入图片描述

Using where

使用的where 语句处理结果，并且查询的列违背索引覆盖

explain select * from actor where name = 'a'

在这里插入图片描述

Using index condition

查询的列不完全被索引覆盖，where的条件中是一个前导列的范围

explain select * from film_actor where film_id >1

在这里插入图片描述

Using temporary

mysql 需要创建一张临时表来处理查询。出现，这种情况一般是要进行优化的，首先是想到用索引来优化。

  1. actor.name 没有索引，此时创建了张临时表来distinct
explain select distinct name from  actor

在这里插入图片描述
2. film.name 建立了idx_name 索引，此时extra是using index，没有使用临时表

explain select distinct name from film;

在这里插入图片描述

Using filesort

将使用外部排序，而不是索引排序，数据较小是从内存排序，否则需要在磁盘中排序这种情况下也是要考虑使用索引的来优化的

  1. actor.name未创建索引，会浏览actor整个表，保存跑排序关键字name和对应的id，然后使用name并检索行记录
explain select * from actor order by name;

在这里插入图片描述
2. film.name建立了idx_name索引,此时查询时extra是using index

explain select * from film order by name;

在这里插入图片描述

Select tables optimized away

使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

explain select min(id) from film;

在这里插入图片描述

索引最佳实战

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=8 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',23,'dev',NOW());
 INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

全值匹配

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';

在这里插入图片描述
此例中我们看出，随着where条件的增加，即我们联合索引的被匹配的增加，我们的key_len也在增加

最左前缀发则

EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;

在这里插入图片描述

 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE position = 'manager'

在这里插入图片描述

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

在这里插入图片描述
给hire_time 增加一个普通索引

ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2018‐09‐30';

在这里插入图片描述
转换日期范围，查询有可能会走索引

EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <='2018‐09‐30 23:59:59';
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;

在这里插入图片描述

存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees 
WHERE name= 'LiLei' AND age > 22 AND position ='manager';

在这里插入图片描述

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

EXPLAIN SELECT name,age FROM employees 
WHERE name= 'LiLei' AND age = 23 AND position='manager';

在这里插入图片描述

EXPLAIN SELECT * FROM employees
 WHERE name= 'LiLei' AND age = 23 AND position ='manager';

在这里插入图片描述

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';

在这里插入图片描述

is null,is not null 一般情况下也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

在这里插入图片描述

like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

在这里插入图片描述

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

在这里插入图片描述
问题：解决like’%字符串%'索引不被使用的方法？
使用覆盖索引，查询字段必须是建立覆盖索引字段
在这里插入图片描述

字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;

在这里插入图片描述

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

 EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

在这里插入图片描述

范围查询优化

给年龄添加单值索引

ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;

在这里插入图片描述
原因：mysql内部优化器会根据检索比例，表大小等多个因数整体评估是否走索引，比如这个例子，可能是由于单词数量查询过大导致优化器选择不走索引
优化方法：可以将大范围拆分多个小范围

explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
ALTER TABLE `employees` DROP INDEX `idx_age`;

在这里插入图片描述
在这里插入图片描述

总结

在这里插入图片描述

面试

1. 主键索引有 二级索引也有 优先使用二级索引

2. 问题：解决like’%字符串%'索引不被使用的方法？

使用覆盖索引，查询字段必须是建立覆盖索引字段

 explain select  name,age,position from employees where name like '%zhuge%'

在这里插入图片描述

Using where和Using index

当这两个同时出现的时候使用了覆盖索引，并且是范围查找

 explain select  name from employees where name like
 'zhuge%'

在这里插入图片描述
需要注意的是，当Using where和Using index同时出现的时候，此时Using where只是用来从索引中查找数据，此数据如果不是用来过滤，那么就是用来读取，以避免回表读取数据行。

小王爷很帅
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Explain详解与索引最佳实践
qq_27740127的博客
06-15 1246
mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键(即不需要回表)，再通过主键去主键索引树里获取其它字段值。
Mysql Explain详解与索引实践
cyy的博客
12-06 1416
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句。分析你的查询语句或是表结构的性能瓶颈通过EXPLAIN，我们可以分析出以下结果：用法：在select语句前添加explainselect查询的序列，表示查询中执行select子句或操作表的顺序。id列越大执行优先级越高用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询表示 explain 的一行正在访问哪个表表示的是查询使用了哪种类型；type包含的类型，依次从最优到最差分别syste
explain详解与索引最佳实践
最新发布
m0_56176751的博客
05-22 958
mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。
MySQL索引最佳实践及Explain详解
大猩猩的博客
01-01 314
MySQL索引最佳实践及Explain详解
索引之explain分析使用详情---一文足以解忧愁
bobo_supper的博客
09-27 541
一、什么是索引 索引是一种排好序的快速查找的数据结构,它帮助数据库高效的查询数据 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高效的查找算法.这种数据结构,就是索引 一般来说索引本身也很大,不可能全部存储在内存中,因此往往以索引文件的形式存放在磁盘中 我们平常所说的索引,如果没有特别说明都是指BTree索引(平衡多...
Mysql中Explain详解及索引的最佳实践
张三博客
03-08 1084
使用关键字可以模拟优化器执行SQL语句，分析查询语句在 SELECT 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。
ecplain详解和索引最佳实战.doc
04-26
通过深入分析EXPLAIN输出，我们可以发现潜在的性能问题，比如不必要的全表扫描、索引未被有效利用或者连接操作效率低下等，然后针对性地改进表结构、创建更适合的索引或者调整查询语句，以实现更高效的数据库操作。
mysql索引共2页.pdf.zip
11-22
【MySQL索引详解】 在MySQL数据库管理系统中，索引是一种数据结构，用于快速查找数据库表中的特定记录。索引能够显著提升查询性能，减少数据检索的时间，尤其在处理大量数据时，其作用尤为明显。本文件"mysql索引共...
深入云计算 MongoDB管理与开发实战详解pdf.part1
05-20
《深入云计算(MongoDB管理与开发实战详解)》系统全面的介绍了MongoDB开发、管理、维护和性能优化等方方面面。详细而深入，对MongoDB的开发和管理方法进行了详细的讲解，也对MongoDB的工作机制进行了深入的探讨。注重...
深入云计算 MongoDB管理与开发实战详解pdf.part2
05-20
《深入云计算(MongoDB管理与开发实战详解)》系统全面的介绍了MongoDB开发、管理、维护和性能优化等方方面面。详细而深入，对MongoDB的开发和管理方法进行了详细的讲解，也对MongoDB的工作机制进行了深入的探讨。注重...
Mysql优化order by语句的方法详解
12-16
3. **排序字段顺序与索引列顺序**：对于组合索引，排序字段顺序应与索引列顺序一致，以利用索引。例如，如果有一个`(store_id, email)`的索引，`SELECT * FROM customer WHERE store_id > 5 ORDER BY store_id, ...
mysql调优--根据explain结果分析索引有效性,正确使用索引
嘎嘎的博客
02-14 4086
定位低效率sql 对于mysql性能优化，除了宏观层面的网络、设备等优化，sql语句的优化是极为重要的一环，需要想办法找到对应的执行效率低的sql语句进行优化。 慢查询日志 慢查询日志是定位低效率sql的手段之一，通过以下命令，设置开启慢查询日志。 #显示是否开启了慢查询日志 show variables like 'slow_query_log'; #开启慢查询日志 set global sl...
什么是索引，如何优化索引（explain性能分析）
易柏州的博客
09-07 747
什么是索引，如何优化索引（explain性能分析）     索引： 帮助Mysql高效获取数据的数据结构 在数据库中，维护着这样的一种数据结构（索引），这种数据结构（索引）以某种方式指向数据。所以索引一般也很大，存在磁盘中。 我们平时说的索引，如果没有特别指明，一般指B树。其中有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引、默认都是B+树索引。     索引分类： 单值索引： 一个索引只包含单个列，一个表可以有多个单列索引。 唯一索引： 索引列的值必须唯一但允许是空值。 复合索引： 一个索引包
mysql中索引利用情况（explain用法）
热门推荐
Jackson Zhang
06-06 1万+
使用explain查看，如下 1、首先创建表test，语句如下 create table test(a int,b varchar(10),c varchar(10)); 2、在表中的a，b都创建索引，先后顺序是a,b create index idx_a_b on test(a,b); 3、分别往里面插入三条数据，插入后结果如下： 4、使用explain查询，查看索引使用情况（这
Mysql-Explain与索引详解
qq1071643762的博客
04-20 177
Mysql系列文章目录 提示：使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 文章目录Mysql系列文章目录前言一、Explain分析explain中的列二、索引实践1.全值匹配2.最左前缀法则3.不在索引列上做任何操作（计算、函数、（自动or手动）
mysql---索引及explain的作用
weixin_30307267的博客
07-14 234
索引：是一种数据结构，以增加存储开销和减慢DML(增、删、改)操作来提高查询速度。 常见的索引结构:btree索引（myisam，innodb，memory，heap），hash索引（memory，heap）。 常见的索引包括：主键索引、唯一索引、普通索引、全文索引 注意：查询一次后会将这次的查询结果缓存起来，若下次再查询则直接取结果 一、创建索引： 1、主键索引：每个表...
Mysql索引及explain
TigerwolfC的博客
03-12 666
索引：是一种数据结构，以增加存储开销和减慢DML(增、删、改)操作来提高查询速度。 常见的索引结构:btree索引（myisam，innodb，memory，heap），hash索引（memory，heap）。 索引本身也很大，不可能全部存储在内存中，所以索引往往以索引文件的形式存储在磁盘上。 常见的索引包括：主键索引、唯一索引、普通索引、全文索引 注意：查询一次后会将这次的查询结果缓存起...
mysql 索引 以及 explain 的参数详解
小学徒的博客
05-05 822
索引： 1.  类型： 普通索引、唯一索引、全文索引（FULLTEXT）、联合索引 MYsql索引的优化 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保    存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值