MySql数据库索引的概述、结构及分类说明

电玩女神 2024-03-24 14:53 52阅读 0赞

### 2.1 索引概述 ###

#### 2.1.1 介绍 ####

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足 特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

#### 2.1.2 演示 ####

表结构及其数据如下：

![在这里插入图片描述][97e03f3f50094c9d98ee9e18a1191bc0.png]

假如我们要执行的SQL语句为 ：

select * from user where age = 45; 1). # 无索引情况

1). 在无索引情况下，就需要从第一行开始扫描，一直扫描到最后一行，我们称之为 全表扫描，性能很低。

![在这里插入图片描述][97ded944fe2b461e9b24f3fd0ce640ab.png]

2). 有索引情况

如果我们针对于这张表建立了索引，假设索引结构就是二叉树，那么也就意味着，会对age这个字段建 立一个二叉树的索引结构。

此时我们在进行查询时，只需要扫描三次就可以找到数据了，极大的提高的查询的效率。

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

#### 2.1.3 特点 ####

### 2.2 索引结构 ###

#### 2.2.1 概述 ####

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

![在这里插入图片描述][742bca4810ae4b5bab80792df47488ea.png]

上述是MySQL中所支持的所有的索引结构，接下来，我们再来看看不同的存储引擎对于索引结构的支持 情况。

![索引	InnoDB	MyISAM	Memory B+tree索引	支持	支持	支持 Hash 索引	不支持	不支持	支持 R-tree 索引	不支持	支持	不支持 Full-text	5.6版本之后支持	支持	不支持][InnoDB_MyISAM_Memory B_tree_ Hash _ R-tree _ Full-text_5.6]

#### 2.2.2 二叉树 ####

假如说MySQL的索引结构采用二叉树的数据结构，比较理想的结构如下：

如果主键是顺序插入的，则会形成一个单向链表，结构如下：

![在这里插入图片描述][9ec83bb3556d458380f258cdfaceddc7.png]

所以，如果选择二叉树作为索引结构，会存在以下缺点：

![在这里插入图片描述][37ff45ddfcbb44e7bd164f799311c5d7.png]

顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。

此时大家可能会想到，我们可以选择红黑树，红黑树是一颗自平衡二叉树，那这样即使是顺序插入数 据，最终形成的数据结构也是一颗平衡的二叉树,结构如下:

![在这里插入图片描述][9245d4b7de394f17875976a463a3fb4b.png]

但是，即使如此，由于红黑树也是一颗二叉树，所以也会存在一个缺点：

*  大数据量情况下，层级较深，检索速度慢。

所以，在MySQL的索引结构中，并没有选择二叉树或者红黑树，而选择的是B+Tree，那么什么是  
B+Tree呢？在详解B+Tree之前，先来介绍一个B-Tree。

#### 2.2.3 B-Tree ####

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key，5 个指针：  
![在这里插入图片描述][00795b4b93f34d4e80f00fbb1f241ca4.png]

我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~gall es/visualization/BTree.html

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88  
120 268 250 。然后观察一些数据插入过程中，节点的变化情况。  
![在这里插入图片描述][697fd4e2d9354c3d85f1d52e9571e313.png]

特点：

5阶的B树，每一个节点最多存储4个key，对应5个指针。  
一旦节点存储的key数量到达5，就会裂变，中间元素向上分裂。在B树中，非叶子节点和叶子节点都会存放数据。

![在这里插入图片描述][400b937ab55f45a09cca0a479a36302a.png]

#### 2.2.4 B+Tree ####

B+Tree是B-Tree的变种，我们以一颗最大度数（max-degree）为4（4阶）的b+tree为例，来看一 下其结构示意图：

我们可以看到，两部分：

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

绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

我们可以通过一个数据结构可视化的网站来简单演示一下。 https://www.cs.usfca.edu/~gall es/visualization/BPlusTree.html

插入一组数据： 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88  
120 268 250 。然后观察一些数据插入过程中，节点的变化情况。

![在这里插入图片描述][4873e9a946b640b0b1c1f1f9618b6c12.png]

最终我们看到，B+Tree 与 B-Tree相比，主要有以下三点区别：

*  所有的数据都会出现在叶子节点。
 *  叶子节点形成一个单向链表。
 *  非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

上述我们所看到的结构是标准的B+Tree的数据结构，接下来，我们再来看看MySQL中优化之后的B+Tree。  
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点 的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。

![在这里插入图片描述][0847c5e5189b46dfa8346b388900d00d.png]

#### 2.2.5 Hash ####

MySQL中除了支持B+Tree索引，还支持一种索引类型—Hash索引。1). 结构  
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在  
hash表中。

![在这里插入图片描述][74c716966d154932b16c12350fc35b96.png]

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可 以通过链表来解决。

![在这里插入图片描述][9fe2ef7270a84d449f800c74ee53f3a0.png]

2). 特点  
A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）

B. 无法利用索引完成排序操作  
C. 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索 引

3). 存储引擎支持  
在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是  
InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。

### 2.3 索引分类 ###

#### 2.3.1 索引分类 ####

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

![分类	含义	特点	关键字 主键索引 针对于表中主键创建的索引	默认自动创建, 只能有一个 PRIMARY 唯一索引 避免同一个表中某数据列中的值重复 可以有多个 UNIQUE 常规索引 快速定位特定数据 可以有多个 全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值 可以有多个 FULLTEXT][_ _ _ PRIMARY _ _ _ UNIQUE _ _ _ _ _ FULLTEXT]

#### 2.3.2 聚集索引&二级索引 ####

而在在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：

![分类	含义	特点 聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个 二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 可以存在多个][_Clustered Index_ _Secondary Index_]

聚集索引选取规则:

*  如果存在主键，主键索引就是聚集索引。
 *  如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
 *  如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索 引。

聚集索引和二级索引的具体结构如下：

![聚集索引的叶子节点下挂的是这一行的数据   。 二级索引的叶子节点下挂的是该字段值对应的主键值。][21c06bd65f0641f899e62e5d3eff80b8.png]

接下来，我们来分析一下，当我们执行如下的SQL语句时，具体的查找过程是什么样子的。

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

具体过程如下:  
①. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。

②. 由于查询返回的数据是\*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。  
③. 最终拿到这一行的数据，直接返回即可。

[97e03f3f50094c9d98ee9e18a1191bc0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/8db95e9eafab4cb1acdbb5280dd308dd.png
[97ded944fe2b461e9b24f3fd0ce640ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/c1ac8da323e24e80ad57a499fc03a05b.png
[d4c2fe5818c344e6bc6cf6fa772828df.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/5c9367c6536b4a3ca1aaf03dc47c20bb.png
[_IO_ _CPU_ _ _INSERT_UPDATE_DELETE]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/06e2c40d615248608ccb7725829c4bf3.png
[742bca4810ae4b5bab80792df47488ea.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d6984755a66041f2939d5026060f1134.png
[InnoDB_MyISAM_Memory B_tree_ Hash _ R-tree _ Full-text_5.6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/4c09b8389dec4587a88123e6af510ac5.png
[9ec83bb3556d458380f258cdfaceddc7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/f45fa9e80c4146caa822188371ee7997.png
[37ff45ddfcbb44e7bd164f799311c5d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/90d10257855e40c2b2b3a931881f47ef.png
[9245d4b7de394f17875976a463a3fb4b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/6b4bd3745d594ccdb31f3215d1c5738b.png
[00795b4b93f34d4e80f00fbb1f241ca4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/8f91a6fb7b1645dd90e502035f4071e3.png
[697fd4e2d9354c3d85f1d52e9571e313.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/f9f6e73af28d4fa18a0f37f9884cabd1.png
[400b937ab55f45a09cca0a479a36302a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/0aaa1f1274744e9b942dc9df5c2b9e15.png
[ed592e415fe94c0d9766f2debf87cf73.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/3dfbbf3529b84f77b9112a6d8e7621bf.png
[4873e9a946b640b0b1c1f1f9618b6c12.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/2677fef1df4a4eaf8fb856eba5218912.png
[0847c5e5189b46dfa8346b388900d00d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/53e5645bb5c641d687db574462132143.png
[74c716966d154932b16c12350fc35b96.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/00e310ca09a14639a10d162b4a6d3c20.png
[9fe2ef7270a84d449f800c74ee53f3a0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/ba6f8cdba19b4a4297fcc180cb93d22f.png
[_ _ _ PRIMARY _ _ _ UNIQUE _ _ _ _ _ FULLTEXT]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/04863d01841d436bb170f992e1d09409.png
[_Clustered Index_ _Secondary Index_]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/d3c8af5dd24740e28296f8cfaa521ad5.png
[21c06bd65f0641f899e62e5d3eff80b8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/bb16554d5f5b40c49d10a2283a33d816.png
[bfcc6dfb77074407975d1d8722c1e955.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/24/dd9d0e0824b443bfadb99b76beb7c354.png