【从删库到跑路 | MySQL总结篇】索引的详细使用

偏执的太偏执、 2024-04-27 07:05 49阅读 0赞

> 个人主页：[兜里有颗棉花糖][Link 1]  
> 欢迎 点赞? 收藏✨ 留言✉ 加关注?本文由 [兜里有颗棉花糖][Link 1] 原创  
> 收录于专栏【[MySQL学习专栏][MySQL]】?  
> 本专栏旨在分享学习MySQL的一点学习心得，欢迎大家在评论区讨论?  
> ![在这里插入图片描述][1337874c55e845d29c2a4eafa759e6cb.png]

#### 目录 ####

*  一、索引
 *   *  索引介绍
     *  MySQL中索引的使用
     *   *  查看索引
         *  创建索引（危险危险）
         *  删除索引（比较危险）
 *  二、索引底层数据结构
 *   *  B-树
     *  B+树
 *  三、索引总结

## 一、索引 ##

### 索引介绍 ###

**索引就相当于一本书的目录(`index`)**，比如我们如果想要快速查找到书中的某一部分内容，我们就可以查看索引来找到我们指定的内容。

而在MySQL中，表中存储的数据是非常庞大的，最基本的方式就是把表中的数据进行一条一条的进行遍历，但是这样的遍历方式的速度太慢了，因为数据库中的信息量是非常庞大的。**所以，我们就需要给数据库中的表建立一个索引（`建立索引的方式是按照列的维度进行建立的`）来提高我们的查找速度。**

> 我们不妨来举个例子，请看下面这张表：![在这里插入图片描述][601daf6f5d61428db460b964ce7375ad.png]  
> **我们首先要知道，一张表中一般是存在很多列的，而针对列的不同，查询的方式也是会有所区别的。比如上面这张表，有时候是按照`id`进行查询的，有时候是按照`name`来进行查询的，也有时候是按照`qq_email`来进行查询的等等**  
> **假设我们现在来按照`id`来进行查询，此时在数据库中就会开辟出额外的一块空间来维护和`id`相关的信息（`这里是通过特定的数据结构来维护表示id相关索引的情况`）：**  
> **如id 为 1 => 对应表中的某个数据  
> 如id 为 2 => 对应表中的某个数据；  
> 如果后续再来按照id进行查询的时候，此时就不需要一条一条遍历数据了。`而是从索引中直接进行查询，根据索引就能够初步锁定数据所在的位置`。**  
> **现在有这么一种情况：如果我们是按照id来建立索引的话，而后面是按照`name`来进行查询的，此时是不能提高效率的；如果想要name加快查找的话，我们可以给name建立索引，**

> 我们现在已经知道索引是用来提高查询速度的。  
> 但是查询速度的提高是要付出一定的代价的：即要消耗额外的空间（主要是硬盘空间）。如果我们想要向表中添加数据的话，此时我们还要修改索引；如果我们要修改或者删除的数据涉及到索引列的删除或者修改，此时我们依然是需要维护索引的。

### MySQL中索引的使用 ###

#### 查看索引 ####

> **查看索引**  
> **语法格式：`show index from table_name;`**

> **举个栗子1：**  
> 输入命令：`create table student(id int primary key,name varchar(20));`  
> **查看当前表的结果，如下图：**  
> ![在这里插入图片描述][767e649ded8d41de81a31fb0bb684c37.png]  
> **我们可以看到上表中存在主键id，mysql中当表中存在主键的时候系统就会自动地给这个列来创建索引；同时由于主键是不允许重复的，因此要想插入或者修改数据的话，就需要先进行查询，来看看插入或者删除后的结果是否存在。**  
> **举个栗子2：**  
> **输入命令：`create table student(id int unique,name varchar(20));`和`show index from student;`**  
> **查询结果如下：**  
> ![在这里插入图片描述][fb060c7322d348b4bf4eecbbb33f43e8.png]  
> **可以看到上述中，如果使用`unique约束`的话也会自动生成索引。**

举个栗子3测试数据的sql语句:
    create table classes(classes_Id int primary key,name varchar(20));
    create table student(id int primary key,name varchar(20),classes_Id int,foreign key (classes_Id) references classes(classes_Id));
    
    // 查看索引
    show index from classes;
    show index from student;

> **举个栗子3：**  
> **查询结果如下：**  
> ![在这里插入图片描述][fb2340247fe645bda44a1cc29072f9e3.png]  
> **其中classes表和student表中各有一个主键约束自动创建的索引，同时student表中存在一个外键约束自动创建的索引。**

> **如上图，外键约束也是会自动生成索引的；当然，`外键约束也是会涉及到自动查询的`。这里我们来举两个栗子，在这之前，我们再来回顾一下下面需要用到的`student表`和`classes表`，请看：**  
> ![在这里插入图片描述][642f60a0f8f5467381dc5345df85fc4c.png]  
> 现在来进行`外键约束也是会涉及到自动查询的`的举例。  
> **举个栗子1：当我们需要在学生表中插入数据的时候就需要查询`classes_Id`在`classes表`中是否存在，此处的查询就需要用到`classes表`的`classes_Id(即classes表中主键自动申城的索引)`。**  
> **举个栗子2：当我们想要删除`classes表`中的一条记录，我们就需要需要查询`classes表中的classes_Id是否在student表中存在`。如果存在的话，此时就是删除失败（`删除失败原因就是子表对父表的反向约束作用`）。`而此处的查询就用到了student表中的classes这一列对应的索引（此索引就是外键约束自动生成的）`。**

**上述三种情况：`主键`、`unique`、`外键`都会使表自动创建出索引。所以此时我们就不需要再对单独的这一列创建单独的索引了。**

#### 创建索引（危险危险） ####

**手动给指定的列创建索引语法格式：`create index 索引名 on 表名(字段名);`**

> 这里依然使用上述的两张表（`student表`和`classes表`），请看：  
> ![在这里插入图片描述][9a72e8771cad420b8c91a4d74b8da2f4.png]  
> **下面我们来简单的创建索引：  
> 输入命令：`create index idx_student_name on student(name);`  
> 创建索引结果如下：**  
> ![在这里插入图片描述][ba98dae7a87a4f2fb70222c08b4137d7.png]  
> **我们来查看一下我们刚刚创建的索引，输入命令：`show index from student;`**  
> ![在这里插入图片描述][2e87cbbc975b4036bcef2d572a279493.png]  
> **如上图我们可以观察到我们刚刚新创建出来的索引（在上图中的最后一行）。**

> **`然而这里我们不得不提到：创建索引的操作是非常危险的。（真是的，怎么我写个sql语句就这么困难呢？到处都是危险操作）`。**  
> **如果我们创建的索引所在的表的数据是空的或者表中的数据本身就不多，此时我们创建索引的操作就不是危险操作**  
> **`但是，如果表是非空的，并且表中的数据量是非常庞大的，那么此时如果我们创建索引的话就会引起非常庞大的硬盘IO操作。甚至会消耗大量的cpu资源`，从而进一步导致数据库被卡死。**  
> **所以我们创建表的时候一定要想好到底要不要创建索引，提前考虑好表的结构中的哪一列需要创建索引，因为后期一旦我们再想创建索引的话就有点不切实际了，`所以凡事要趁早，提前做好规划。`**

#### 删除索引（比较危险） ####

**删除索引只能删除手动创建的索引。**

> **删除索引语法格式（指定要删除的索引名和表名即可）：`drop index 索引名 on 表名;`**  
> **还是来举个栗子，现在我们要删除刚刚给`student表`中的`name`创建的索引，如果你忘记了刚刚student表结构，那么请看下图来回忆以下：**  
> ![在这里插入图片描述][57ecf2208ff341d49e53d090d261909b.png]  
> **输入命令：`drop index idx_student_name on student;`**  
> **结果如下（检验是否删除索引成功），请看下图：**  
> ![在这里插入图片描述][7a4f873087a84ae5a8b4776f00ea69f3.png]  
> **可以看到我们刚刚创建的`idx_student_name`索引删除成功了。**

> **上面我们提到过只能删除手动创建的索引，我们试试能不能删除`student表`自动创建的索引`classesId`，输入命令：`drop index classes_Id on student;`结果如下：**  
> ![在这里插入图片描述][802e8a25b8714453b2212d35b06d584c.png]  
> **报错原因：外键约束需要`索引classes_Id`。**  
> **由此我们可以看出我们只能删除手动创建的索引，而自动创建的索引是不能被删除的。**

**删除索引这个操作也是比较危险的，不能轻易删除索引。**

**假设有这样一个场景：现在我们有一个有很多很多数据的表，同时这个数据库此时是处于生产环境的一个数据库。但是我们现在必须要创建删除索引。此时怎么办呢？我们要知道数据库服务器往往不仅仅是单台服务器，为了整个系统的稳定性，通常会搞出多个mysql服务节点，`这些节点的数据往往都是一样的，能够提出相同的服务。`（`以防万一某个mysql服务器节点挂了，而其它服务器依然可以很正常运行`）**

**此时我们可以这样做：先准备好一个新的mysql服务器，把表和索引都创建好，然后把数据都导过来，再把要替换的mysql服务器关闭掉，然后再把新的mysql服务器替换掉就好了。**

## 二、索引底层数据结构 ##

MySQL底层的数据结构并不是一个定式，而是**取决于MySQL使用哪个存储引擎**。

> **那什么又是存储引擎呢？**  
> MySQL这个程序中包含了许多模块：有的模块是负责解析sql的；有的模块是负责网络通信的；而有的模块则是用来存储数据的。我们**用来存储数据的相关的模块称为存储引擎（本质上就是代码中的一个模块，这个模块中包含了若干个代码文件以及一堆具体的代码）。**  
> **而具体如何来进行数据的存储，MySQL支持多种存储方案。`Innodb`是当下最主流的一种存储方案。**  
> **`数据库中组织数据所使用的数据结构是在硬盘上的。（这里就与内存中的数据结构就有所区别了）`**

> **内存上的数据结构（比如我们平常实现的链表、二叉树、顺序表等都是存储在代码内存中的）对于访问操作来是是非常不敏感的。（`其实内存中的数据结构进行访问所占用的时间并不多，真正占用时间的而是找数据的过程`）**  
> **硬盘上的数据结构对于访问操作来说是比较敏感的，`读写一次硬盘的开销是远远大于内存的，此时我们就不得不考虑访问所带来的开销了`且读写一次硬盘相当于读写约10000次内存。**

> **好了，我们现在来看看索引到底是使用哪种数据结构比较合适吧！在这之前我们首先要明白索引是用来干什么的，嗯对，索引的目的其实就是方便我们进行快速的查找。回顾我们以往学到的数据结果中，能够做到快速查找的一个是`二叉搜索树（带有平衡机制的二叉搜索树）`，另外一个就是`哈希表`了。**  
> **但是哈希表并不适合作为索引的底层结构，因为哈希表（哈希表基本执行过程就是`把给定的key通过hash函数映射出一个具体的下标才能定位到具体位置`）无法做到范围查询或者说模糊查询，哈希表只能完成精确查询的任务。**

> **那红黑树其实也不适合作为索引的数据结构，虽然红黑数种的元素是有序的，可以进行范围查询，但是之所以红黑树不能作为索引的数据结构最大的问题就是红黑树的高度（当元素比较多的时候，树就会变的非常高、或者非常深），而树的深度越深比较的次数就越多。而索引这样的数据结构是存储在硬盘上的，每次比较都意味着硬盘IO操作（`读写一次硬盘就相当于约10000次内存读写`）。`所以红黑树不适合作为索引的底层数据结构`**

**在MySQL中的Innodb存储引擎中，索引的底层数据结构是`B+树`；而在其它存储引擎中，也可能会用到hash索引，此时就只能应对精准匹配查询的这种情况（所以hash并不是不能作为索引，只是丧失了部分功能而已）。**

**想要清楚什么是`B+树，我们就需要先知道什么是B - 树`**。

### B-树 ###

**B - 树（`本质是N叉搜索树`）**：**B 树的一个节点上可以保存多个`key`，`N个key`就可以延伸出`N+1`个分支。查找元素的流程就是拿着要查找的元素从根节点出发（判断要查找的元素是否在根节点上），如果不存在的话，我们就需要看看这个元素最终落到哪个区间内，然后沿着这个区间的路线继续往后找，如果最终的叶子节点依然不存在我们要查找的元素，那么这个要查找的元素就真的不存在了。**

![在这里插入图片描述][45f8e0fc07054c5e858619c22e9d5376.png]

**与二叉搜索树相比，`B - 树`的每个节点可以存储多个元素，在元素总个数相同的情况下，`B - 树`的节点数和高度都会大大降低；同时`B - 树`在同一个层次中会存在多个分叉，这也会降低节点数和树的高度。**

**对于数据库来说，每个节点上的数据都需要从数据库中读取出来才能进行比较（每个节点访问的时候都会进行硬盘的IO操作。）随着B树的节点减少的同时，节点的访问次数就会减少，硬盘IO操作也当然会随之减少。还有一点我们需要知道：一个节点中有一个key和一个节点中有多个key的节点访问时的硬盘IO操作的开销是差不多的。**

> **B树的插入元素和删除元素会涉及到`拆分`和`合并`的操作，解释如下：**  
> **B树中的一个节点的确可以保存多个key，但是也不可以无限的进行存储，当key的数量到达一定程度的时候，此时就需要把这个节点进行拆分，即把这个节点中的一部分key以树的子节点的方式进行重新组织，这样就可以保证每个节点中的key的数量不会太多（当然也会衍生出新的叶子几点）。**  
> **B树的合并操作：B树的合并操作一般是在节点删除后执行的，因为当删除一个节点时，会导致 B 树的高度降低，可能会破坏 B 树的平衡性，需要对 B 树进行合并操作以保持平衡。**  
> **关于B树的拆分和合并操作：具体什么时候进行拆拆分，怎么拆分；什么时候合并，怎么合并需要看具体的实现，同时不同场景下会有不同的应对策略。**

### B+树 ###

好了，现在我们来了解下数据库中索引的主角：B+树（**N叉搜索树**）。B+树在B树的基础上又做出了一些改进。

**B+树特点：**

*  **N个key分出了N个区间，每个节点上的最后一个key就是最大值（也可能是第一个key，即最小值）。**
 *  **父节点中的key会在子节点中重复出现（以最大值或者最小值的身份），这样就会出现一个效果，即叶子节点这一层的的数据包含了整个数据的全集。**
 *  **把叶子节点进行链表方式的首尾相连，此时叶子节点这样的一个连接方式就可以快速找到上一个或者下一个节点，方便进行范围查询（只要能确定开头和结尾，开头和结尾中间的子链表就是我们查询出来的结果）。**

![在这里插入图片描述][6ed94c87267c4a488743393ca8fcb8ed.png]

**`好了，以上就是本文的全部内容，就到这里吧！！！再见啦友友们~~~`**

## 三、索引总结 ##

**好了，现在再来重新回顾一下索引吧！！！**

索引简单点来说就相当于一本书中的目录，可以加快我们的查找速度。并且**索引是按照列来进行查询的。**

**索引的优劣：**

*  **优点**：**提高查询速度。**
 *  **缺点**：**占据额外的内存空间。一定程度上会拖慢插入修改删除数据的速度。**

**索引具体的使用方式：**

*  **创建索引：会自动创建的索引：`主键`、`外键`、`unique`。手动创建索引语法格式：`create index 索引名称 on 表名(列名);`**
 *  **查看索引：`show index from 表名;`**
 *  **删除索引：`drop index 索引名 on 表名;`**

**索引的数据结构：**

*  **hash不适合作为索引的数据结构，原因是因为hash不能够进行范围查询，不能够进行模糊匹配查询。**
 *  **红黑树也不适合作为索引的数据结构，虽然红黑树可以进行范围查询，但是需要引入更多的`硬盘IO操作`。**
 *  **`B+树就非常适合索引的数据结构`，在MySQL中的Innodb存储引擎中的索引就是以B+树作为底层数据结构的。**

**B+树的优势：**

*  B+树非常擅长范围查询，即只要找到开头和结尾，那么中间那一部分的链表就是我们要查找的数据。
 *  所以的查询操作的查询时间非常稳定，因为所有的查询操作最终都会落在叶子节点上。而B树的话有点数据可能在根节点找到，有的数据可能在叶子节点找到，这样的话查询速度就很不稳定。（**某些时候，稳定比快速更加重要**）
 *  **由于叶子节点上是完整的数据全集，因此表的每一行数据的其它列都可以保存在叶子节点上（我们要知道物理结构中并不存在`表格`这样的数据结构，所以我们可以使用B+树来存储表中的数据，`表格只是看起来像一个表格而已`），而非叶子节点只存储构建索引的key即可（ID）。此时非叶子节点的存储空间是非常小的以至于我们可以在内存中缓存一份，这样的话在进行数据查询的时候就可以通过内存来直接进行数据的比较，从而更快速的找到叶子节点上的记录（`这里又进一步的减少了硬盘IO操作的次数`）。而B树的话由于B树的整个数据的全集并非只有叶子节点，非叶子节点也有可能是数据的全集。**
 *  **B树如果也要把元素存储到每个节点上，非叶子节点所占用的空间就会变大，从而无法在内存中进行缓存了。当然对应的硬盘IO操作也会增多。**

![在这里插入图片描述][5c6030f8b8df49a5a84e19b15ca5caa7.png]  
嗯，**本文就到这里吧，再见啦友友们！！！**  
**记得一键三连哦！！！**  
![在这里插入图片描述][9e7754fb0954413c9f747ce182568e01.png]

[Link 1]: https://xiaofeizhu.blog.csdn.net/
[MySQL]: https://blog.csdn.net/m0_74352571/category_12430542.html
[1337874c55e845d29c2a4eafa759e6cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/5eb23064b0ba4aceb44325eeeaa8f080.png
[601daf6f5d61428db460b964ce7375ad.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/491c5959f48d42eebdcbc2725e81a82a.png
[767e649ded8d41de81a31fb0bb684c37.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/28a13bff0d104c5cafd2025a454c10cf.png
[fb060c7322d348b4bf4eecbbb33f43e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/8dba5e48a9f5406891178bb2c5f61ebc.png
[fb2340247fe645bda44a1cc29072f9e3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/405fe06b30e2414985a5675b85bb93c3.png
[642f60a0f8f5467381dc5345df85fc4c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/3017975515b3407ab2248cda24fdd1ad.png
[9a72e8771cad420b8c91a4d74b8da2f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/ac6897c13f964a84afa1a9683f46be9c.png
[ba98dae7a87a4f2fb70222c08b4137d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/9d911246c3174dd38f6fb2d040d1bac0.png
[2e87cbbc975b4036bcef2d572a279493.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/010af88a25e842f1aef78b373f83ecb4.png
[57ecf2208ff341d49e53d090d261909b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/733118a47c9d4810ab897eba3082fdd3.png
[7a4f873087a84ae5a8b4776f00ea69f3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/b9891347b4ca4e6c955e0a06ae480eee.png
[802e8a25b8714453b2212d35b06d584c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/348e5569b8f54e37b77bd644172cbc7c.png
[45f8e0fc07054c5e858619c22e9d5376.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/5934f1bae3ea4a8e9a7962d9f4a98936.png
[6ed94c87267c4a488743393ca8fcb8ed.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/69a5eb79e8de443c8d3d5f470f4412ea.png
[5c6030f8b8df49a5a84e19b15ca5caa7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/fb9622605e5249e9a367750ab047f7a6.png
[9e7754fb0954413c9f747ce182568e01.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/27/60bd949e59aa4d6480fcd06b249a409d.png