MySQL索引初探

最新推荐文章于 2025-04-21 09:48:44 发布

容器( ु⁎ᴗ_ᴗ⁎)ु.｡oO

最新推荐文章于 2025-04-21 09:48:44 发布

阅读量1.3k

点赞数 10

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/2301_79985750/article/details/142298390

版权

MySQL 专栏收录该内容

4 篇文章

订阅专栏

索引的相关介绍

索引是什么，为什么要使用索引？

注：MySQL是关系型数据库。
设想一个场景，现在你要去新华字典里找一个你不认识的字或者去确定一个字的读音是否准确，你可以按照这个字的部首进行查询，或者是按照这个字的大致读音进行查询，利用这样的方法总能在新华字典里快速查出你想要的字。但是现在如果是换成另一种的“字典”，这本字典记录了所有现代汉字，但是只是仅仅记录了汉字，没有提供任何方便去查询的手段，那么这本字典可能就相对于新华字典来说，查询一个字没有那么的方便。在数据库中也是一样的，如果没有索引，就很难在数据库中高效的去查询数据。因此，MySQL索引可以帮助数据库高效地查询，更新数据库中的数据。索引通过一系列的规则排列数据表中的记录，使其对表的查询可以通过对索引的搜索来加快访问速度。并且，索引是一种数据结构，由于不同的数据结构有自己的实现规则，不同规则实现的导致效率不同，最终的时间和空间复杂度也不同。那么是哪一种数据结构才能担当此任呢？

MySQL索引应该选择哪种数据结构？

在使用MySQL数据库时，大多是查询一个范围的数据，例如：查询某平台总积分前100的用户名，这就是一个范围查询。
先来看hash表，它通过hash函数将键直接映射到一个索引位置，意味着知道键值是什么可以立即找到对应的值，因此平均情况下时间复杂度O(1)；由于hash表的数据不是按照顺序进行存储，要执行范围查询，最坏的情况下可能达到O(N)，因此不适合范围查询，而且为了保证较低的冲突率，还需要在内存中预留空间，因此空间利用率较低。但是特殊场景可以使用hash表，例如内存数据库Redis使用hash表来存储键值，提供非常快速的访问速度。
其次看二叉搜索树，由于二叉搜索树每个节点左边子树的键值小于根节点的键值，右子树的键值大于根节点的键值，因此二叉搜索树支持范围查询，但是最坏的情况下，可能退化成单项链表，查询数据时还应遍历链表，时间复杂度为O(N)；如果利用AVL树和红黑树进行优化，在进行插入和删除数据时，经过大量的旋转用来保持平衡，维护成本高，而且两者都是每个节点存放两个子节点，导致内存使用率较低。而且插入数据后层级很高，数据库中的数据在磁盘中保存的，每次找子节点都要经过一次磁盘IO，导致频繁的IO，但是磁盘访问数据很慢，因此不适合使用二叉搜素树。可以得到，磁盘IO是限制数据库查询效率的主要因素，减少IO次数，可以提高数据库的查询效率。
之后看下B树，如图，每个节点有超过2个节点（可根据度自行进行调整），可以解决层级很高的问题且时间复杂度为O(logN).因此可以减少磁盘IO的次数来寻找节点，提高了数据库的查询效率。

但是对于MySQL的索引来说，还不能满足要求，因此引入B+树，借此来分析B树和B+数的不同点。如图：

B+树继承了B树的良好特性
1.叶子节点之间有一个相互连接的引用，可以通过一个叶子节点找到它的兄弟节点。而且MySQL组织叶子节点时使用的时双向链表。
2.B+树非叶子节点的值都包含在叶子节点中。MySQL非叶子节点只保存了对子节点的引用，可以通过它快速查找到需要查询数据的范围，没有保存真实的数据，所有真实的数据都保存在叶子节点中。而B树每个节点都保存了真实的数据。
3.对于B+树，在相树高的情况下，查找任一元素的时间复杂度都一样，性能均衡。（因为B+树都要遍历到叶子节点）

MySQL的存储结构

MySQL中的页

当创建一个数据库后，会生成一个文件目录，在数据库中创建的表，这些表的就是独立表空间，这些储存空间是由一个个页组成，表空间在MySQL中的后缀为.ibd, （innodb存储引擎生成的表空间后缀为.ibd）页是内存与磁盘交互的最小单元，MySQL中默认为16kb,每次内存与磁盘交互至少读取一页，连续页可以一次性的读入内存，减少磁盘IO的次数，因此页内部的地址是连续的，即可以连续的从页的第一条数据读取到页的最后一条数据。当读取一页数据，当下次查询的数据还放在内存中，从而减少了IO的次数，提升性能。当一个页数据即时没有16kb,也会使用16kb的存储空间，同时页与索引的B+树中节点对应。如图：

为何是16kb,可以去查询，如下图：

在Linux操作系统中，管理文件最小单位是4kb,当需要查询页时，将其从磁盘移动到内存，当对内存中的数据修改完成之后，还需要放入到磁盘中，此时16kb的文件被分为4份，每次4kb写入磁盘中。但是当写入到磁盘过程中时，如果发生断电等情况，那么如何保证数据的安全写入？在落盘之前会记录各种日志，保证重启之后可以找到没有落盘的数据内容。

.ibd文件

当创建一张表后，生成一个保存数据文件，我们将它称为独立表空间文件，文件中有很多页，这里只讨论数据页（或者称为索引页）相关示意图如下：

页文件头和页文件尾

它们记录了当前页文件的主要信息，相关示意图如下：

其中上一个页号和下一个页号，通过这两个属性可以把页和页之间连接起来，形成双向链表。
通过页号和页大小，可以计算出下一页和上一页在磁盘中的偏移量。

页主体

页主体部分是保存真实数据的主要区域，每当创建一个新页，都会自动分配两个行，一个是页内最小行，另一个是页内最大行，这两个行不存储任何信息，而是作为数据行的头和尾，通过next_record将数据行组成一个单向链表，数据填入时插入到最大和最小行之间，当没有数据时如图：

当存储一些数据时，如果指定了主键，数据按照主键从小到大的形式进行排列,从下图也可以看出存放数据是一条单向链表。

当去查询一条数据时，单向链表遍历时间复杂度为O(N)，那么如何保证快速的查询到所需要的数据，因此可以引出非常重要的结构--页目录。

页目录

分组时会在页目录中创建槽，槽的数量与分组的数量是一致的，槽会指定对应分组的最后条记录，同时保存这条记录的主键值。

1.最小行单独为一组。
2.数据行的分组，每组可以容纳8条记录，超过8条会分裂出来一个新的组。
3.最大行永远在最后一个分组。

写道这里，那么寻找一条记录的步骤是：1.找到这个记录所有的页（B+树上）；2.在页中找到对应的槽；3.在分组中找到对应的记录

数据页头：它包含用于管理和操作数据页所需的元数据，它提供了有关页的状态，结构以及其他关键信息。通过页头信息，数据库管理系统可以有效管理数据页，确保数据的完整性和一致性，并提供了高效的储存管理。

B+树在MySQL索引中的应用

非叶子节点保存索引数据，叶子节点保存真实数据。索引页保存的主键的值以及子节点的引用，数据页保存的是真实的数据，页与页之间通过页号建立关联关系，之间用双向链表进行连接，最终所有的叶子节点形成一个双向循环链表（对的，第一个叶子节点和最后一个叶子节点也有连接）。那么如果三层的B+树，寻找一条数据，先是加载根索引页，在加载下一层索引页，最后加载数据页，经过3次IO。
理论上3层的B+树可以存放多少条记录？
首先是数据页默认是16kb，假设一条数据1kb，那么就是16条数据。对于索引页来说，存的是主键值和对索引页的引用，其中主键值的数据类型为bigint--8byte，引用是6byte，因此一个索引记录是14byte,一个索引页可以存16*1024/14=1170条数据，那么三层高的B+树可以存1170*1170*16=219,024,00条记录，就是说，表中有21902400条记录，通过3次IO就能够完成数据的查询。当把索引页加载到内存中后，实际通过一次IO就能完成查询。

索引的分类

主键索引

主键：用于唯一标识表中每一条记录，一个表中只能有一条主键，并且主键值不能重复，也不能为NULL。
当在一个表定义一个主键primary key时，数据库会自动创建一个索引来维护这个主键。InnoDB使用它作为聚集索引，也成为聚簇索引。

普通索引

为了提升查询效率，通常为查询频繁的列创建索引，可以包含一个列以及多个列

唯一索引

当在表上定义一个唯一键UNQUE时，自动创建唯一索引，唯一索引的列不允许有重复的值

全文索引

文本列char,varchar和txt上创建，用于全文搜索

聚集索引

如果没有为表定义 PRIMARY KEY ，InnoDB使用第一个 UNIQUE 和 NOT NULL 的列作为聚集索引。如果没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB会为新插入的行生成一个行号并用6字节的ROW_ID字段记录，ROW_ID单调递增，并使用ROW_ID作为索引。

非聚集索引

聚集索引以外的索引，也称为二级索引。
二级索引的每条记录都包含该行的主键列。怎么理解？以普通索引为例，例如：主键索引是id号，现在name创建一个索引，值是小明，数据中不仅包含name的值，也包含主键值比如3，这是就创建好了普通索引。那么由普通索引创建的索引树（这是一棵新的B+树，不要和之前的树弄混了），索引那里不再是原来主键id号对应的值了，变成了name对应的值了，但是这时主键还是3。