Explain详解与索引最佳实践

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

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

Explain 各列解释

EXPLAIN SELECT* 
FROMactorLEFT 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* 
FROMactor 
WHEREid = 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注意点

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

索引使用总结：

like KK%相当于=常量，%KK和%KK% 相当于范围

自学笔记

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/237297.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

ffmpeg[学习(四)](代码实现) 实现音频数据解码并且用SDL播放

ffmpeg[学习(四)](代码实现) 实现音频数据解码并且用SDL播放

0、作者杂谈 CSDN大多数都是落后的&#xff0c;要么是到处复制粘贴的&#xff0c;对于初学者我来说困惑了很久&#xff0c;大多数CSDN文章都是使用旧的API &#xff0c;已经被否决了&#xff0c;于是我读一些官方文档&#xff0c;和一些开源项目音视频的输出过程&#xff0c;写…
阅读更多...
React Hooks中useState的介绍，并封装为useSetState函数的使用

React Hooks中useState的介绍，并封装为useSetState函数的使用

useState 允许我们定义状态变量&#xff0c;并确保当这些状态变量的值发生变化时&#xff0c;页面会重新渲染。 useState 返回值 const [state, setState] useState(initialState);useState 返回一个长度为 2 的数组。通常&#xff0c;我们这样定义状态变量&#xff1a; co…
阅读更多...
ActiveMQ反序列化RCE漏洞复现（CVE-2023-46604）

ActiveMQ反序列化RCE漏洞复现（CVE-2023-46604）

漏洞名称 Apache ActiveMQ OpenWire 协议反序列化命令执行漏洞 漏洞描述 Apache ActiveMQ 是美国阿帕奇&#xff08;Apache&#xff09;软件基金会所研发的一套开源的消息中间件&#xff0c;它支持Java消息服务、集群、Spring Framework等。 OpenWire协议在ActiveMQ中被用于…
阅读更多...
C语言如何提高程序的可读性?

C语言如何提高程序的可读性?

一、问题 可读性是评价程序质量的一个重要标准&#xff0c;直接影响到程序的修改和后期维护&#xff0c;那么如何提高程序的可读性呢? 二、解答 提高程序可读性可以从以下几方面来进行。 &#xff08;1&#xff09;C程序整体由函数构成的。 程序中&#xff0c;main()就是其中…
阅读更多...
ArchVizPRO Interior Vol.8 URP

ArchVizPRO Interior Vol.8 URP

ArchVizPRO Interior Vol.8 URP是一个在URP中制作的建筑可视化项目。这是一个完全可导航的现代公寓，包括一个带开放式厨房的客厅、休息区、两间卧室和两间浴室。从头开始构建每一个细节，这个室内有130多件家具和道具、自定义着色器和4K纹理。所有家具和道具都非常详细，可以在…
阅读更多...
使用 C++/WinRT 创作 API

使用 C++/WinRT 创作 API

如果 API 位于 Windows 命名空间中 这是你使用 Windows 运行时 API 最常见的情况。 对于元数据中定义的 Windows 命名空间中的每个类型&#xff0c;C/WinRT 都定义了 C 友好等效项&#xff08;称为投影类型 &#xff09;。 投影类型具有与 Windows 类型相同的完全限定名称&…
阅读更多...
【.NET Core】Lazy＜T＞ 实现延迟加载详解

【.NET Core】Lazy＜T＞ 实现延迟加载详解

【.NET Core】Lazy 实现延迟加载详解 文章目录 【.NET Core】Lazy<T> 实现延迟加载详解一、概述二、Lazy<T>是什么三、Lazy基本用法3.1 构造时使用默认的初始化方式3.2 构造时使用指定的委托初始化 四、Lazy.Value使用五、Lazy扩展用法5.1 实现延迟属性5.2 Lazy实现…
阅读更多...
【LeetCode:30. 串联所有单词的子串 | 滑动窗口 + 哈希表】

【LeetCode:30. 串联所有单词的子串 | 滑动窗口 + 哈希表】

&#x1f680; 算法题 &#x1f680; &#x1f332; 算法刷题专栏 | 面试必备算法 | 面试高频算法 &#x1f340; &#x1f332; 越难的东西,越要努力坚持&#xff0c;因为它具有很高的价值&#xff0c;算法就是这样✨ &#x1f332; 作者简介&#xff1a;硕风和炜&#xff0c;…
阅读更多...
K8S后渗透横向节点与持久化隐蔽方式探索

K8S后渗透横向节点与持久化隐蔽方式探索

前言 通常在红蓝对抗中&#xff0c;我们可能会通过各种方法如弱口令、sql注入、web应用漏洞导致的RCE等方法获得服务器的权限&#xff1b;在当前云原生迅猛发展的时代&#xff0c;这台服务器很可能是一个容器&#xff0c;在后续的后渗透由传统的提权变为容器逃逸&#xff0c;内…
阅读更多...
【MySQL】导入导出SQL脚本及远程备份---超详细介绍

【MySQL】导入导出SQL脚本及远程备份---超详细介绍

目录 前言&#xff1a; 一 navcat导入导出 1.1 导入 1.2 导出 二 mysqldump 导入导出 2.1 导入 2.2 导出 三 load data infile命令导入导出 3.1 导入 3.2 导出 四 远程备份 五 思维导图 前言&#xff1a; 随着当今企业发展&#xff0c;数据库的数据越来越多&…
阅读更多...
POI：对Excel的基本写操作 整理1

POI：对Excel的基本写操作 整理1

首先导入相关依赖 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><!--xls(03)--><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.2.2</version></depend…
阅读更多...
Java副本的概念

Java副本的概念

在Java中&#xff0c;"副本"&#xff08;copy&#xff09;一词可以用于描述不同的概念&#xff0c;具体取决于上下文。以下是两个常见的用法&#xff1a; 对象的副本&#xff1a;在Java中&#xff0c;当你创建一个对象并将其赋值给另一个变量时&#xff0c;实际上是创…
阅读更多...
electron自定义窗口和右键菜单样式

electron自定义窗口和右键菜单样式

前言 electron默认沿用系统UI&#xff0c;并没有提供很多接口供使用者定制样式&#xff0c;如果想要完全自定义的样式&#xff0c;目前我能想到的方案只能是通过前端自定义样式&#xff0c;然后通过进程通信来实现系统基础功能&#xff1a;最大/小化、关闭、拖动窗口等。 效果…
阅读更多...
迈入AI智能时代！ChatGPT国内版免费AI助手工具 peropure·AI正式上线 一个想法写一首歌？这事AI还真能干！

迈入AI智能时代！ChatGPT国内版免费AI助手工具 peropure·AI正式上线 一个想法写一首歌？这事AI还真能干！

号外&#xff01;前几天推荐的Peropure.Ai迎来升级&#xff0c;现已支持联网模式&#xff0c;回答更新更准&#xff0c;欢迎注册体验&#xff1a; https://sourl.cn/5T74Hu 相信很多人都有过这样的想法&#xff0c;有没有一首歌能表达自己此时此刻的心情&#xff1a; 当你在深…
阅读更多...
xtu oj 1329 连分式

xtu oj 1329 连分式

题目描述 连分式是形如下面的分式&#xff0c;已知a,b和迭代的次数n&#xff0c;求连分式的值。 输入 第一行是一个整数T(1≤T≤1000)&#xff0c;表示样例的个数。 每行一个样例&#xff0c;为a,b,n(1≤a,b,n≤9) 输出 每行输出一个样例的结果&#xff0c;使用x/y分式表达…
阅读更多...
Go-安装与基础语法

Go-安装与基础语法

TOC 1. Go 安装与环境变量 1.1 下载 需要从Go语言的官方网站下载适合你操作系统的Go语言安装包。Go语言支持多种操作系统&#xff0c;包括Windows、Linux和Mac OS。 对于Windows用户&#xff0c;下载.msi文件&#xff0c;然后双击该文件&#xff0c;按照提示进行安装即可。…
阅读更多...
学习Qt笔记

学习Qt笔记

前言&#xff1a; 学习笔记的内容来自B站up主阿西拜编程 《Qt6 C开发指南 》2023&#xff08;上册&#xff0c;完整版&#xff09;_哔哩哔哩_bilibili《Qt6 C开发指南 》2023&#xff08;上册&#xff0c;完整版&#xff09;共计84条视频&#xff0c;包括&#xff1a;00书籍介…
阅读更多...
FreeRTOS系统配置

FreeRTOS系统配置

一、前言 在实际使用FreeRTOS 的时候我们时常需要根据自己需求来配置FreeRTOS&#xff0c;而且不同架构 的MCU在使用的时候配置也不同。FreeRTOS的系统配置文件为FreeRTOSConfig.h&#xff0c;在此配置文件中可以完成FreeRTOS的裁剪和配置&#xff0c;这是非常重要的一个文件&a…
阅读更多...
git修改历史（非最新）提交信息

git修改历史（非最新）提交信息

二、修改最近第二次或更早之前的commit信息 当前有三次提交&#xff0c;从近到远分别为1、2、3 以修改第2次提交为例&#xff08;从最新往前数&#xff09; 1、使用命令git rebase -i HEAD~2 按i进入编辑模式&#xff0c;将对应的pick改为edit&#xff0c;然后ctrlc退出。最…
阅读更多...
C++学习笔记（二十八）：c++ 静态库及动态库的使用

C++学习笔记（二十八）：c++ 静态库及动态库的使用

静态库的使用 库的使用会很大程度减少我们的工作&#xff0c;本节对c中静态库和动态库的使用进行简单的介绍。静态链接库意味着这个库会被放到可执行文件中&#xff0c;在生成的exe中。动态链接库是在程序运行时链接的&#xff0c;可以在程序运行时调用加载库函数的方法来实现&…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023