关于索引应用的一些问题

叁歲伎倆 2023-10-13 14:15 94阅读 0赞

索引是啥:加快检索速度的数据结构

## 索引的优点和缺点 ##

索引的优点:

1.建立索引后,数据库检索数据速度直线上升(使用正确的话),数据量越大越明显

2.分组和排序的时候,可以利用索引加快速度

3.通过建立唯一索引可以确保数据唯一,不需要加其他限制条件(既建立了索引 又保证了唯一性)

4.连表查询时，基于主外键字段上建立索引，可以带来十分明显的性能提升。

缺点:

1.建立索引会生成本地磁盘文件，需要额外的空间存储索引数据，磁盘占用率会变高。

2.写入数据时，需要额外维护索引结构，增、删、改数据时，都需要额外操作索引。

3.写入数据时维护索引需要额外的时间开销，执行写`SQL`时效率会降低，性能会下降。

> ## 各种索引的优劣 ##
> 
> ### 主键索引 ###
> 
> 首先过长的字段不应该作为主键索引,且主键索引字段应该唯一
> 
> 所以我们一般用自增ID作为主键索引,能满足这两种需求
> 
> 但是我们为啥不用UUID作为主键索引呢?这也满足我们的要求啊
> 
> 因为InnoDB储存引擎在储存索引的时候,使用的是B+树作为数据结构 而B+树是基于二分功能的 也就是它的节点是有序的 (详情可以看下这篇博客[B+树][B]) 但UUID是随机的 也就是说我们在建立索引的时候要进行随机插入 而随机插入可能会打乱B+树的现有的形状 而主键索引的叶节点里面包含所有数据 一移动节点会移动数据 这样进行数据迁移的代价极大
> 
> 但使用自增`ID`就不会有这个问题，所有新插入的数据都会放到最后。
> 
> 所以主键又加了一个要求 有序
> 
> ### 联合索引 ###
> 
> 联合索引可以实现索引覆盖 减少二级索引的回表操作从而提高检索速度 或者在分组及排序的时候加快速度
> 
> 但是举个例子 我们对 a b c字段建立联合索引
> 
> 那么有三条语句
> 
> SELECT \* FROM table WHERE a = 1 and b = 1 and c = 1;
> 
> SELECT \* FROM table WHERE a = 1 and c = 1;
> 
> SELECT \* FROM table WHERE b = 1 and c = 1;
> 
> 只有第三条语句不能使用联合索引
> 
> 因为查询条件的最左边的条件要和索引的最左边匹配也就是说这里的查询条件的第一个条件一定要是a 才能使用联合索引
> 
> 且联合索引当遇到范围查询 (>、<) 就会停止匹配 比如
> 
>     select * from t_user  where age > 20 and reward = 100000;
> 
> age可以利用到联合索引 而reward不行
> 
> ### 前缀索引 ###
> 
> 前缀索引就是利用这个字段的前几个字符创建索引 相较于普通的索引 可以节省储存空间 当数据数量高到一定程度时 节省的储存空间将十分可观
> 
> 但是它的索引节点中，未存储一个字段的完整值，所以`MySQL`也无法通过前缀索引来完成`ORDER BY、GROUP BY`等分组排序工作，同时也无法完成覆盖扫描等操作。
> 
> ### 全文索引 ###
> 
> InnoDB在MySQL 5.6及以后的版本引入的全文索引
> 
> 全文索引是将存储在数据库中的大段文本中的任意内容信息查找出来的技术
> 
> 假如说 我们百度搜索 MYSQL很酷 然后看搜索结果 标红的部分就是 和关键词对应的地方
> 
> 我们的全文索引也就是这样的效果 把要搜索的字段 断成几个词根 然后再全文里面检索
> 
> ![7532f70fc08e4faa9e661ae6cd5c463e.png][]
> 
> 有没有感觉和模糊匹配的作用差不多(好像是个大模糊匹配)模糊匹配虽然能够实现效果，但随着表越来越大，数据越来越多时，其性能会出现明显下降，而全文索引的推出则能够完美解决该问题，可以利用全文索引代替`like%`语法实现模糊查询，它的性能会比`like%`快上`N`倍。
> 
> 全文索引的缺陷:
> 
> ①由于全文索引是基于分词实现的，所以对一个字段建立全文索引后，`MySQL`会对该字段做分词处理，这些分词结果也会被存储在全文索引中，因此全文索引的文件会额外的大！
> 
> ②由于全文索引对每个字段值都会做分词，因此当修改字段值后，分词是需要时间的，所以修改字段数据后不会立马自动更新全文索引，此时需要咱们写存储过程，并调用它手动更新全文索引中的数据。
> 
> ③除开上述两点外，全文索引最大的硬伤在于对中文支持不够友好，类似于英文可以直接通过符号、空格来分词，但中文呢？一个词语来形容就是博大精深，无法精准的对一段文字做分词，因此全文索引在检索中文时，存在些许精准度问题。
> 
> ### 唯一索引 ###
> 
> 唯一索引可以保证数据唯一性 当它搜索的时候只会返回一条数据不会往下查看,而普通索引会向下查看知道不满足条件,所以检索速度会快一些(实际上快不了多少 因为读取行为一次读一个数据页到内存中 而内存中的查询操作很快 详情可见-[普通索引VS唯一索引][VS])
> 
> 插入的时候呢
> 
> 当当前数据页已经被加载到内存中时
> 
> 唯一索引:先找到插入位置 看看冲不冲突 不冲突再插入
> 
> 普通索引: 直接插
> 
> 差了个cpu操作 也大差不差
> 
> 但是如果当前数据
> 
> 就不行了 就是唯一索引是用不了change buffer的 所以每次插入操作都要进行一次I/O操作 而我们知道I/O操作贼\*\*费劲
> 
> ### 哈希索引 ###
> 
> (我是没见过 但是了解总比不了解强对吧)
> 
> 哈希索引，也就是数据结构为`Hash`类型的索引，不过估计大家接触的比较少，毕竟创建索引时都默认用的`B+`树结构。但要比起查询速度，哈希索引绝对是`MySQL`中当之无愧的魁首！因为采用哈希结构的索引，会以哈希表的形式存储索引字段值，当基于该字段查询数据时，只需要经过一次哈希计算就可获取到数据。
> 
> 但哈希结构的致命问题在于无序，也就是无法基于哈希索引的字段做排序、分组等工作。
> 
> 因此如果你确定一个表中，不会做排序这类的工作，那可以适当选用哈希结构作为索引的数据结构，它会给你带来意想不到的性能收益

##  ##

## 索引失效 ##

就是建立好的索引没用上 直接全表扫描了

### 左或者左右模糊匹配 ###

索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。那左模糊匹配和左右模糊匹配都是前缀未知的

### 使用函数 ###

假如说我们对value列建立了索引 然后查询的时候用了 WHERE LENGTH(name)=6 这时候就不会使用索引 原因很简单 我们是对value建立了索引 又不是对函数建立了索引

不过呢 现在MYSQL还真只是函数索引了 我们如果建立 LENGTH(name)索引的话 那再查询的时候就可以走索引了呗

### 使用表达式 ###

这里失效的理由和使用函数的失效是一样的 例如 WHERE id + 1 = 10

但是呢 如果用 WHERE id = 10 - 1 就可以 因为MYSQL懒的判断 所以我们自己改一改还是可以用的

### 隐式类型转换 ###

比如我们刚开始 定义了value VARCHAR(20) 但是在查询的时候忘记写单引号了

比如 WHERE value = 60 MYSQL就会认为你查的是int类型的 然后就索引失效了

而反过来 定义value INT 查询的时候加了单引号 value = '60' 就不会索引失效

为啥呢,因为:**MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较**。而这个转换行为会调用函数

也就是第一种方式就等效于

where CAST(value AS signed int) = 60;

也就是发生了使用函数造成的索引失效

而第二种方式

where value  = CAST(60 AS signed int);

这次我们的被判断字段没使用函数

### 联合索引非最左匹配 ###

在上面联合查询的部分介绍了

### 使用了OR ###

如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

### 不同字段值对比 ###

如果我们用 WHERE name = 'zhangsan' 不会索引失效 但是

如果用 WHERE name\_student = name\_teacher 就会失效 不能用两个字段(参数)进行比较(即使建立了 name)

### 反向范围操作导致索引失效 ###

一般来说，如果`SQL`属于正向范围查询，例如`>、<、between、like、in...`等操作时，索引是可以正常生效的，但如果`SQL`执行的是反向范围操作，例如`NOT IN、NOT LIKE、IS NOT NULL、!=、<>...`等操作时，就会出现问题，

不过IS NULL虽然是正向查询 但是也会索引失效 IS NOT NULL也会失效

### MYSQL优化器造成的索引失效 ###

有时候二级索引的大量I/O操作(回表造成的) 还不如直接全表查询来的快 MYSQL就会自动执行成本更低的操作

例如:SELECT \* FROM table WHERE a>1000000 and b <500000;

这个数据有很多行(按几万行估计) 我们的流程为

(在联合索引遇到> <的时候会停止 也就是a使用索引 b不使用)

判断a是否符合要求 然后拿着a的id 回表操作拿到b的数据 判断

而不用索引呢  
顺序遍历每一条数据 由于每次加载一个数据页进来 比每条进行回表 减少了很多I/O操作 成本大量减少了

## 一些索引优化行为 ##

### 索引覆盖 ###

当要查询的数据全包含在联合索引时 就会直接在索引中取值而避免多次回表操作

例如 SELECT name,age FROM table WHERE.....如果name和age包含在联合索引中 就不会按照正常的二级索引逻辑(找到对应主键 再用主键找到整条记录的数据)

### 索引下推 ###

也就是将`Server`层筛选数据的工作，下推到引擎层处理

例如:

SELECT \* FROM table where a > 500 and b = 20;

这条SQL会部分使用索引 只适用a的索引

如果没有索引下推 那么它的执行过程为 首先找到a>500的所有条目 然后回表扫描 判断b是否等于20 然后把符合的结果返回给用户

有了索引下推 就是 找到a>500的条目 同时在联合索引中判断是否符合b=20 不符合的直接筛掉

然后直接去回表拿数据 就减少了几次回表操作

###    MRR(Multi-Range Read)机制 ###

`Multi-Range Read`简称为`MRR`机制，这也是和索引下推一同在`MySQL5.6`版本中引入的性能优化措施，那什么叫做`MRR`优化呢？

> 一般来说，在实际业务中我们应当尽量通过索引覆盖的特性，减少回表操作以降低`IO`次数，但在很多时候往往又不得不做回表才能查询到数据，但回表显然会导致产生大量磁盘`IO`，同时更严重的一点是：还会产生大量的离散`IO`，下面举个例子来理解。

``SELECT * FROM `zz_student_score` WHERE `score` BETWEEN 0 AND 59;``

上述这条`SQL`所做的工作很简单，就是在学生成绩表中查询所有成绩未及格的学生信息，假设成绩字段上存在一个普通索引，那思考一下，这条`SQL`的执行流程是什么样的呢？

*  ①先在成绩字段的索引上找到`0`分的节点，然后拿着`ID`去回表得到成绩零分的学生信息。
 *  ②再次回到成绩索引，继续找到所有`1`分的节点，继续回表得到`1`分的学生信息。
 *  ③再次回到成绩索引，继续找到所有`2`分的节点......
 *  ④周而复始，不断重复这个过程，直到将`0~59`分的所有学生信息全部拿到为止。

那此时假设此时成绩`0~5`分的表数据，位于磁盘空间的`page_01`页上，而成绩为`5~10`分的数据，位于磁盘空间的`page_02`页上，成绩为`10~15`分的数据，又位于磁盘空间的`page_01`页上。此时回表查询时就会导致在`page_01、page_02`两页空间上来回切换，但`0~5、10~15`分的数据完全可以合并，然后读一次`page_01`就可以了，既能减少`IO`次数，同时还避免了离散`IO`。

> 而`MRR`机制就主要是解决这个问题的，针对于辅助索引的回表查询，减少离散`IO`，并且将随机`IO`转换为顺序`IO`，从而提高查询效率。

`MRR`机制中，对于辅助索引中查询出的`ID`，会将其放到缓冲区的`read_rnd_buffer`中，然后等全部的索引检索工作完成后，或者缓冲区中的数据达到`read_rnd_buffer_size`大小时，此时`MySQL`会对缓冲区中的数据排序，从而得到一个有序的`ID`集合：`rest_sort`，最终再根据顺序`IO`去聚簇/主键索引中回表查询数据。

### Index Skip Scan索引跳跃式扫描 ###

限制太多 了解即可

我们建立了关于 a b c的索引

SELECT \* FROM WHERE b = 1 and c = 1;

本来这条SQL是不应该使用索引的 但是MYSQL帮助我们"跳过"了a 强制使用索引

其实也就是`MySQL`优化器会自动对联合索引中的第一个字段的值去重，然后基于去重后的值全部拼接起来查一遍

## 使用索引的建议 ##

这里引用https://juejin.cn/post/7149074488649318431的原文

*  ①经常频繁用作查询条件的字段应酌情考虑为其创建索引。
 *  ②表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。
 *  ③建立索引的字段，一般值的区分性要足够高，这样才能提高索引的检索效率。
 *  ④建立索引的字段，值不应该过长，如果较长的字段要建立索引，可以选择前缀索引。
 *  ⑤建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合。
 *  ⑥经常根据范围取值、排序、分组的字段应建立索引，因为索引有序，能加快排序时间。
 *  ⑦对于唯一索引，如果确认不会利用该字段排序，那可以将结构改为`Hash`结构。
 *  ⑧尽量使用联合索引代替单值索引，联合索引比多个单值索引查询效率要高。

同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：

❶值经常会增删改的字段，不合适建立索引，因为每次改变后需维护索引结构。

❷一个字段存在大量的重复值时，不适合建立索引，比如之前举例的性别字段。(但是低选择性 数据有倾斜的可以 比如三个状态 原则上是大量重复的 但是如果有一种状态特别少一种状态特别多 那么也可以用索引(CBO自动优化))

❸索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。

❹一张表中的索引数量并不是越多越好，一般控制在`3`，最多不能超过`5`。

❺建立联合索引时，一定要考虑优先级，查询频率最高的字段应当放首位。

❻当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。

❼索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

虽然前八点不一定要遵守 但是后七点一定要避免

[B]: https://blog.csdn.net/chara9885/article/details/131567923?spm=1001.2014.3001.5501
[7532f70fc08e4faa9e661ae6cd5c463e.png]: https://img-blog.csdnimg.cn/7532f70fc08e4faa9e661ae6cd5c463e.png
[VS]: https://blog.csdn.net/chara9885/article/details/131614832?spm=1001.2014.3001.5502