mysql 性能优化 - 覆盖索引(mysql 索引数据结构 / Explain 简单描述)

x33g5p2x  于2021-09-18 转载在 Mysql  
字(2.4k)|赞(0)|评价(0)|浏览(231)

前言

刚毕业时,公司大佬曾经说过:像关系型数据库这类东西,说不定哪天就被淘汰了,你们没必要花费太多精力在上面,应该着重于 非关系型数据库 ,像 mysql 之类的,会用就好了,不用太深入研究!!

现在我想说一句,老贼误我!!!

在这里插入图片描述

且不说 拥有靠谱的DBA的公司本身就少,而且作为公司的部门,想要用到DBA的资源也是不容易的,所以大部分的sql优化,还是要会的!!这是基础!!!

就比如当下,需要对数据库进行调优,我的第一个念头就是建立索引。然后,就没有然后了…

要先了解数据库底层的存储结构是什么样的,才能根据业务建立起相对可行的索引,才能调整查询语句,达到性能提升的地步,不然建立索引就是瞎建立,就是个愣头青!!(说的就是我)

在这里插入图片描述

本文主要讲述一下 覆盖索引 ,为什么要讲它??因为我竟然从来没听过这是个啥!!!结果被学弟好好鄙视了一番!!!

在这里插入图片描述

正文

mysql 索引数据结构

先说说啥是索引:索引是帮助 MySQL 高效获取数据的排好序的 数据结构。

这边为啥要突出排好序??
因为当是有序时,搜索的速度会快于乱序的 数据结构,正因为如此,阿里巴巴的开发手册中,才推荐不要再用 uuid ,而是用雪花算法作为主键索引。

MySql 的索引数据结构用的 B+Tree :简要说明:ps(这次画图用 processOn ,应该会比以往好看一点了)

在这里插入图片描述

特性:

  • 叶子节点包含所有的索引字段(蓝色区域)
  • 非叶子节点(除了黄色区域)不存储data,只存储索引,可以存放更多的索引
  • 叶子结点用指针连接,提高区间访问的性能(索引有序,加快访问,所以推荐主键雪花算法)
  • 所有的data存储在叶子节点上

现在市面上使用的存储引擎绝大部分是 InnoDb 而不是 MYISAM ,最大的原因是 InnoDb 的索引文件和数据文件是聚集的,而 MYISAM 的索引文件和数据文件是非聚集的。所以以下都是以 InnoDb 为例。

主键索引

在这里插入图片描述

主键索引如图:索引为主键值(蓝色),叶子阶段存储所有数据(黄色部分)。

二级索引

在这里插入图片描述

二级索引如图:索引是name(蓝色部分),而此处的叶子节点(黄色部分)并不会存储所有数据,仅仅是存储了主键的id,最终查询是会查询到此处叶子节点的id在进行回表查询。(非聚集索引)

联合索引

ps(此处举例非主键,所以值是主键id,而若是主键联合索引,则和上面一致,值是数据)

在这里插入图片描述

这里有一个 左列原则(最左前缀原则)

建立的联合索引是 name,age ,所以 索引的建立先以name为排序,若name相同再比较 age 为排序。

所以当查询的时候直接以条件为name,会走索引。直接以条件为age,则不走索引。

Explain 简单描述

想对于sql进行优化,Explain 绝对是要掌握的知识,不过本文并不以这个为主要核心,所以只是稍微描述一下。

举例:
只需要在查询语句上,加上 Explain 即可

在这里插入图片描述

查询结果如下:

在这里插入图片描述

id 列:id列的编号是 select 的序列号,有几个 select 就有几个id,并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。(当执行关联查询此处就会有多个)。
*
select_type:表示对应行是简单还是复杂的查询。
simple:简单查询。查询不包含子查询和union
primary:复杂查询中最外层的 select
subquery:包含在 select 中的子查询(不在 from 子句中)
derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)。
union:在 union 中的第二个和随后的 select。
*
table:表示 explain 的一行正在访问哪个表。
*
type:关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。
*
possible_keys:这一列显示查询可能使用哪些索引来查找。explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
*
key:这一列显示mysql实际采用哪个索引来优化对该表的访问。
*
key_len:这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。
*
ref:这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名
*
rows:这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。
*
Extra:这一列展示的是额外信息(此处值非常多,仅仅举例覆盖索引)。Using index。

覆盖索引

前面说了那么多,终于轮到正主登场~(若是对前面知识一无所知,怎么可能建的好索引呢??)

在这里插入图片描述

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

举例说明:
现在有一张表,我建立了联合索引
在这里插入图片描述

此时我对它进行查询:

在这里插入图片描述

此处发现 key 为null ,有建立索引,为什么它不走呢。也满足了最左前缀原则!! 这是因为,mysql底层 会再次进行优化,它判断不走索引会比走索引快,所以 possible_keys 有值,但是最后没走。具体分析可以使用 trace

而 Extra 并不是 Using index,所以没有使用 覆盖索引。

此时,更改查询条件

在这里插入图片描述

查询索引字段,发现它走索引了!走索引了!

在这里插入图片描述

这是因为,当查询的时候,他根据联合索引查到了值,但是由于搜索的字段是索引本身字段,所以值可以在索引上拿到,并不需要去回表查询,因此,效率杠杠的高!!!

相关文章