MySQL - 索引详解以及优化；Explain执行计划

 官网文档

MySQL :: MySQL 5.7 Reference Manual :: 8.3 Optimization and Indexes

Mysql优化（出自官方文档） - 第八篇（索引优化系列）

目录

  • Mysql优化（出自官方文档） - 第八篇（索引优化系列）
    • Optimization and Indexes
    • 1 Foreign Key Optimization
    • 2 Column Indexes
      • Indexes in the MEMORY Storage Engine
    • 3 Column Indexes && Multiple-Column Indexes
    • 4 Comparison of B-Tree and Hash Indexes
      • B-Tree Index Characteristics
      • Hash Index Characteristics
    • 5 Use of Index Extensions
    • 6 Invisible Indexes
    • 7 Descending Indexes
声明：以下内容出自动力节点！！！

一、索引

（一）索引

MySQL官方对于索引的定义：索引是帮助MySQL高效获取数据的数据结构。

MySQL在存储数据之外，数据库系统中还维护着满足特定查找算法的数据结构，这些数据结构以某种引用(指向)表中的数据，这样我们就可以通过数据结构上实现的高级查找算法来快速找到我们想要的数据。而这种数据结构就是索引。

索引简单理解为“排好序的可以快速查找数据的数据结构”。

MySQL中常用的的索引数据结构有BTree索引(Myisam普通索引)，B+Tree索引（Innodb普通索引），Hash索引(memory存储引擎)等等

（二）索引优势

1、提高数据检索的效率，降低数据库的IO成本

2、通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

（三）索引劣势

索引实际上也是一张表，保存了主键和索引的字段，并且指向实体表的记录，所以索引也是需要占用空间的。

在索引大大提高查询速度的同时，却会降低表的更新速度，在对表进行数据增删改的同时，MySQL不仅要更新数据，还需要保存一下索引文件。

每次更新添加了的索引列的字段，都会去调整因为更新带来的减值变化后的索引的信息

（四）索引使用场景

1、哪些情况需要创建索引：

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引(where 后面的语句)

3、查询中与其它表关联的字段，外键关系建立索引

4、多字段查询下倾向创建组合索引

5、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

6、查询中统计或者分组字段

2、哪些情况不推荐建立索引:

 1、表记录太少

 2、经常增删改的表

 3、Where条件里用不到的字段不建立索引

（五）索引分类

1、主键索引

1、表中的列设定为主键后，数据库会自动建立主键索引。

2、单独创建和删除主键索引语法：

创建主键索引语法： alter table 表名 add primary key (字段);

删除主键索引语法： alter table 表名 drop primary key;

2、 唯一索引

 1、表中的列创建了唯一约束时，数据库会自动建立唯一索引。

 2、单独创建和删除唯一索引语法：

创建唯一索引语法：alter table 表名 add unique 索引名(字段)；

        或  create unique index 索引名 on 表名(字段)；

删除唯一索引语法：drop index 索引名 on 表名;

​​​​​​​3、 单值索引 

即一个索引只包含单个列，一个表可以有多个单值索引。

 1、建表时可随表一起建立单值索引

 2、单独创建和删除单值索引：

创建单值索引： alter table 表名 add index 索引名(字段);

    或   create index 索引名 on 表名(字段);

删除单值索引：drop index 索引名 on 表名;

​​​​​​​4、 复合索引 

即一个索引包含多个列：

 1、建表时可随表一起建立复合索引

 2、单独创建和删除复合索引：

创建复合索引：create index 索引名 on 表名(字段1,字段2);

                  或 alter table 表名 add index 索引名(字段,字段2);

删除复合索引: drop index 索引名 on 表名;

二、性能分析；Explain查看SQL执行计划

​​​​​​​（一） Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句的。可以用来分析查询语句或是表的结构的性能瓶颈。其作用:

 1、表的读取顺序

 2、哪些索引可以使用

 3、数据读取操作的操作类型

 4、那些索引被实际使用

 5、表之间的引用

 6、每张表有多少行被优化器查询 

EXPLAIN关键字使用起来比较简单： explain + SQL语句

（二）Explain重要字段名

 1、id

select查询的序列号，表示查询中执行select子句或操作表的顺序。

id相同时，执行顺序由上至下。

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，则先被执行。

id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越大，优先级越高越先执行。

2、​​​​​​​ select_type

查询的类型，常见值有：

SIMPLE :简单的 select 查询,查询中不包含子查询或者UNION。

PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary。

DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询, 把结果放在临时表里。

SUBQUERY: 在SELECT或WHERE列表中包含了子查询。

​​​​​​​3、 table

显示这一行的数据是关于哪张表的。

​​​​​​​4、type

访问类型排序:

System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。

Const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

Ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

Range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引

一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

Index：Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的。

All：Full Table Scan，将遍历全表以找到匹配的行。

从最好到最差依次是：system>const>eq_ref>ref>range>index>All 

一般来说，最好保证查询能达到range级别，最好能达到ref

​​​​​​​5、 possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上如果存在索引，则改索引将会被列出来，但不一定会被查询实际使用上。

​​​​​​​6、 key

查询中实际使用的索引，如果为NULL，则没有使用索引。

​​​​​​​7、 ref

显示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

​​​​​​​8、 rows

rows列显示MySQL认为它执行查询时必须检查的行数。一般越少越好。

​​​​​​​9、 extra

一些常见的重要的额外信息：

Using filesort：MySQL无法利用索引完成的排序操作称为“文件排序”。

Using temporary：Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。

Using index：表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。

Using where：表示使用了where过滤

三、索引优化

（一）索引口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能丢，中间兄弟不能断；
索引列上不计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写 *；
不等空值还有or，索引失效要少用；
字符单引不可丢，SQL高级也不难。

1、全值匹配我最爱

建立几个复合索引字段，最好就用上几个字段。且按照顺序来用。

2、最佳左前缀原则

最前缀法则，必须有车头，中间车厢不能断

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

4、存储引擎不能使用索引中范围条件右边的列（范围之后全失效，不包括本身）

若中间索引列用到了范围（>、<、like等），则后面的索引全失效

5、尽量使用覆盖索引（尽量不用*）

6、like左模糊查询会导致索引失效

有时候右模糊查询，并不能查到想得到的内容；如果想需要使用两边都是%号，又不想索引失效，
此时应使用覆盖查询（查询范围在已经建立的索引范围之内，尽量与索引的个数，顺序相同）

使用select *

使用覆盖查询

7、is null 和 is not null将无法使用索引

8、字符串不加引号，索引失效

9、少用or，用它连接会可能会造成索引失效

（二）排序，分组优化 

1、尽量避免使用Using FileSort方式排序。

2、order by语句使用索引最左前列或使用where子句与order by子句条件组合满足索引最左前列。

3、where子句中如果出现索引范围查询会导致order by索引失效

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

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

相关文章

玩转硬件之Micro:bit的玩法(一)

玩转硬件之Micro:bit的玩法(一)

写在前面 这么长时间以来一直在玩软件, 好像软件还没有研究明白&#xff0c;因为工作的转变&#xff0c;又开始接触到硬件&#xff0c;既然开始触碰到硬件了&#xff0c;也想记录一下。有的时候想想要不要写这段前言&#xff0c;但是不写又觉得比较突兀&#xff0c;好端端的怎…
阅读更多...
SpringBoot整合Gateway 的Demo（附源码）

SpringBoot整合Gateway 的Demo（附源码）

源码&#xff0c;可直接下载 Gateway模块 Gateway 的父pom.xml <?xml version"1.0" encoding"UTF-8"?> <project xmlns"http://maven.apache.org/POM/4.0.0" xmlns:xsi"http://www.w3.org/2001/XMLSchema-instance"xsi:sc…
阅读更多...
二叉树OJ题（检查两颗数是否相同、另一棵树的子树、翻转二叉树、判断平衡二叉树）

二叉树OJ题（检查两颗数是否相同、另一棵树的子树、翻转二叉树、判断平衡二叉树）

文章目录 二叉树OJ题一、 检查两颗数是否相同1.思路2.解题步骤3.代码 二、另一棵树的子树1.思路2.代码 三、翻转二叉树1.思路2.解题步骤3.代码 四、判断平衡二叉树1.思路2.代码 二叉树OJ题 一、 检查两颗数是否相同 1.思路 1.两个树&#xff0c;在保证结构相同的同时&#xff0…
阅读更多...
iOS 使用dsym符号化线上crash日志（ips文件）

iOS 使用dsym符号化线上crash日志（ips文件）

1.获取崩溃日志 可以iphone连接mac复制当时的崩溃日志。 Xcode->Window->Devices View Device Logs 如果是testflight的崩溃是可以分享的&#xff0c;分享出来可能是ips文件。 把文件名称改成my.crash 使用脚本把新版本崩溃日志转成老版本格式 这一步不是必须的&…
阅读更多...
如何知道服务器的某个端口是否打开

如何知道服务器的某个端口是否打开

注意&#xff1a;服务器的TCP端口&#xff0c;比如1886端口&#xff0c;出方向 和进方向 都打开才可以用 1、telnet 命令&#xff1a;telnet ip port&#xff0c;port即端口&#xff0c;我们一般最常见的命令就是telnet&#xff0c;但是telnet使用的是tcp协议&#xff0c;换句…
阅读更多...
[云原生案例1.] 构建LNMP架构并运行Wordpress个人博客平台

[云原生案例1.] 构建LNMP架构并运行Wordpress个人博客平台

文章目录 1. 当前需求2. 前置准备3. 搭建过程3.1 创建自定义网络3.2 部署并配置nginx3.2.1 创建工作目录并上传相关软件包3.2.2 解压缩相关软件包3.2.3 编写Dockerfile文件3.2.4 编写nginx.conf文件3.2.5 创建nginx镜像3.2.6 运行容器 3.3 部署并配置mysql3.3.1 创建工作目录3.…
阅读更多...
在科技展厅设计中，如何通过空间规划来突出展品和主题？

在科技展厅设计中，如何通过空间规划来突出展品和主题？

数字多媒体技术在各行业内的广泛应用&#xff0c;使内容展览展示技术得到了更新&#xff0c;尤其是在科技展厅设计中&#xff0c;更是将各类多媒体互动装置的优势发挥到了极致&#xff0c;为观众提供现代化的感官体验&#xff0c;而这其中有效的空间规划对于现代化科技展厅的效…
阅读更多...
3D模拟场景开发引擎

3D模拟场景开发引擎

在3D工程模拟开发中&#xff0c;有一些专门的引擎和工具可供选择&#xff0c;以帮助您创建逼真的三维模拟和模型。以下是一些用于3D工程模拟的开发引擎和工具&#xff0c;希望对大家有所帮助。北京木奇移动技术有限公司&#xff0c;专业的软件外包开发公司&#xff0c;欢迎交流…
阅读更多...
最新Ubuntu20.04安装教程（图文）

最新Ubuntu20.04安装教程（图文）

总的来说&#xff0c;安装Ubantu包含以下三个步骤&#xff1a; 一、安装虚拟机 二、Ubuntu镜像下载 三、虚拟机配置 一、安装虚拟机 选择安装VMware Workstation&#xff0c;登录其官网下载安装包&#xff0c;链接如下&#xff1a; 下载 VMware Workstation Pro | CN 下载…
阅读更多...
iOS实现弹簧放大动画

iOS实现弹簧放大动画

效果图 实现代码 - (void)setUpContraints {CGFloat topImageCentery (SCREEN_HEIGHT - 370 * PLUS_SCALE) / 2;[self.topIconView mas_makeConstraints:^(MASConstraintMaker *make) {make.centerX.mas_equalTo(0);make.centerY.equalTo(self.view.mas_top).with.offset(t…
阅读更多...
面试官：聊聊kafka线上使用会有哪些问题？

面试官：聊聊kafka线上使用会有哪些问题？

哪些环节会造成消息丢失&#xff1f; 首先说说哪些环节会丢消息 消息生产者&#xff1a; &#xff08;1&#xff09;acks0&#xff1a; 表示producer不需要等待任何broker确认收到消息的回复&#xff0c;就可以继续发送下一条消息。性能最高&#xff0c;但是最容易丢消 息。大…
阅读更多...
关键点检测、姿态识别、目标检测、车牌识别等项目部署代码+数据集汇总

关键点检测、姿态识别、目标检测、车牌识别等项目部署代码+数据集汇总

一、AI健身计数 1、图片视频检测 &#xff08;cpu运行&#xff09;&#xff1a; 注&#xff1a;左上角为fps&#xff0c;左下角为次数统计。 1.哑铃弯举&#xff1a;12&#xff0c;14&#xff0c;16 详细环境安装教程&#xff1a;pyqt5AI健身CPU实时检测mediapipe 可视化界面…
阅读更多...
VS LiveShare使用操作介绍

VS LiveShare使用操作介绍

VS LiveShare的使用教程 文章简介下载过程 文章简介 本篇文章主要介绍了如何安装和使用LiveShare的过程。 下载过程 1.在扩展->管理扩展&#xff0c;搜索Live Share后&#xff0c;下载对应的安装包&#xff0c;安装后对VS进行重启 2.安装后界面右上角会出现Live Share标…
阅读更多...
docker环境安装+maven依赖继承问题

docker环境安装+maven依赖继承问题

1&#xff0c;docker环境安装 我们使用yum指令进行安装&#xff0c;分别cmd运行&#xff1a; yum install -y yum-utils device-mapper-persistent-data lvm2 yum-contig-manager --add-repo https://download.docker.com/linux/centos/docker-ce.rep具体解释如下&#xff1a;…
阅读更多...
目标检测中常见指标 - mAP

目标检测中常见指标 - mAP

文章目录 1. 评价指标2. 计算示例3. COCO评价指标 1. 评价指标 在目标检测领域&#xff0c;比较常用的两个公开数据集&#xff1a;pascal voc和coco。 目标检测与图像分类明显差距是很大的&#xff0c;在图像分类中&#xff0c;我们通常是统计在验证集当中&#xff0c;分类正…
阅读更多...
【Azure】存储服务：Azure 的存储账户

【Azure】存储服务：Azure 的存储账户

文章目录 一、前提知识&#xff08;建议了解&#xff09;二、介绍 Azure 存储帐户三、使用 Microsoft Azure 门户创建存储帐户 一、前提知识&#xff08;建议了解&#xff09; 在每一个云厂商中&#xff0c;都有自身的云存储&#xff0c;也有根据不同功能进行区分的不同类型的…
阅读更多...
SPSS两独立样本t检验

SPSS两独立样本t检验

前言&#xff1a; 本专栏参考教材为《SPSS22.0从入门到精通》&#xff0c;由于软件版本原因&#xff0c;部分内容有所改变&#xff0c;为适应软件版本的变化&#xff0c;特此创作此专栏便于大家学习。本专栏使用软件为&#xff1a;SPSS25.0 本专栏所有的数据文件请点击此链接下…
阅读更多...
循环神经网络（RNN）与长短期记忆网络（LSTM)

循环神经网络（RNN）与长短期记忆网络（LSTM)

前言&#xff1a; 通过前面的学习&#xff0c;我们以BP神经网络为基础&#xff0c;认识到了损失函数&#xff0c;激活函数&#xff0c;以及梯度下降法的原理&#xff1b;而后学习了卷积神经网络&#xff0c;知道图像识别是如何实现的。今天这篇文章&#xff0c;讲述的就是计算机…
阅读更多...
【Linux】第七站：vim的使用以及配置

【Linux】第七站：vim的使用以及配置

文章目录 一、vim1.vim的介绍2.vim基本使用3.vim的命令模式常用命令4.底行模式 二、vim的配置 一、vim 1.vim的介绍 vim编辑器&#xff0c;用来文本编写&#xff0c;可以写代码 它是一个多模式的编辑器 它有很多的模&#xff0c;不过我们暂时先只考虑这三种模式 命令模式插入模…
阅读更多...
v-bind动态改变样式

v-bind动态改变样式

通过v-bind切换样式&#xff0c;:class"{ active:true}"为true展示样式&#xff0c;false不展示。也可以由:style"{ width:percent %}"动态控制宽度。 注意后面是JS对象&#xff0c;所以后面的值不可以包含-&#xff0c;比如background-color会解析出错&a…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023