MySQL索引原理篇：深入数据库底层揭开索引机制的神秘面纱

最新推荐文章于 2024-06-21 15:10:12 发布

java卖客

最新推荐文章于 2024-06-21 15:10:12 发布

阅读量590

点赞数

文章标签： sql java 数据库

本文链接：https://blog.csdn.net/m0_74132984/article/details/127319503

版权

引言

MySQL的索引机制，自始至终对于我们都是一个黑盒般的存在，我们并不清楚建立索引后MySQL会发生什么，也并不清楚使用索引查询时会如何检索......。对于索引咱们也留下了很多很多的疑惑：

相信大家多多少少都有了解过，毕竟这也是面试过程中出现次数较为频繁的一个技术点。在本文中就来一窥MySQL索引底层的神秘面纱！

一、MySQL索引为何使用B+树结构？

MySQL的索引机制中，有一点可谓是路人皆知，既默认使用B+Tree作为底层的数据结构，但为什么要选择B+树呢？有人会说树结构是以二分法查找数据，所以会在很大程度上提升检索性能，这点确实没错，但树结构有那么多，MySQL为什么不选二叉树、AVL树、红黑树或B树呢？下面一起先聊一聊这个话题。

对于索引为什么不支持数组、链表、队列等结构就不做过多解释了，因为这些结构中的元素都是按序并排存储，如果选择这些结构来实现索引，那走索引依旧等价于走全表，并未带来查询时的效率提升，反而带来了额外的存储开销，这是没有意义的。

1.1、普通SQL的全表扫描过程

想要真正理解MySQL为何选B+树结构之前，你必须要先了解一条普通SQL的全表扫描过程，否则你很难真正切身感受出有索引和没索引的区别。当然，这里就不再以伪逻辑的形式讲解全表扫描了，而是真正的为大家讲解MySQL全表扫描的实际过程。以下面这张用户表为例：

首先假设表中不存在任何索引，此时来执行下述这条SQL：

SELECT * FROM `zz_user` WHERE `name` = "黑熊"; 复制代码

因为表中不具备索引，所以这里会走全表扫描的形式检索数据，但不管是走全表亦或索引，本质上由于数据都存储在磁盘中，因此首先都会触发磁盘IO，所以先来说说磁盘寻道的过程：

如果磁盘不是SSD类型，大致长上面这个样子，里面有一个个的盘面和磁针，当发生磁盘IO时，首先会根据给出的磁盘地址，在盘面上寻道。这个寻道过程是怎么回事呢？就跟小时候VCD、DVD放光碟类似，盘面会开始转圈圈，在盘面上有一个磁道的概念，当转到了对应的地址时，磁道和磁针会相互吸引，然后以一上一下的方式读取0、1二进制数据，最终从磁盘中将目标地址中的数据读取出来。

磁盘寻道的大致过程如上，具体的细节没写出来，重点是大家要感受这个过程即可。

当走全表扫描时，会发生磁盘IO，但是磁盘寻道是需要有一个地址的，这个地址最开始就是本地表数据文件中的起始地址，也就是从表中的第一行数据开始读，读到数据后会载入内存，然后MySQL-Server会根据SQL条件对读到的数据做判断，如果不符合条件则继续发生磁盘IO读取其他数据（如果表比较大，这里不会以顺序IO的形式走全表检索，而是会触发随机的磁盘IO）。

那来看一下，上面给出的用户表中，「黑熊」这条数据位于表的第五行，那这里会发生五次磁盘IO吗？答案是NO，为什么呢？因为OS、MySQL中都有一个优化措施，叫做局部性读取原理。

1.1.1、局部性原理

局部性原理的思想比较简单，比如目前有三块内存页x、y、z是相连的，CPU此刻在操作x页中的数据，那按照计算机的特性，一般同一个数据都会放入到物理相连的内存地址上存储，也就是当前在操作x页的数据，那么对于y，z这两页内存的数据也很有可能在接下来的时间内被操作，因此对于y，z这两页数据则会提前将其载入到高速缓冲区（L1/L2/L3），这个过程叫做利用局部性原理“预读”数据。

但是一次性到底预读多大的数据放入到高速缓冲区中呢？

这个是由缓存行大小决定的，比如因特尔的MESI协议中，缓存行的默认大小为64k，也就是说在因特尔的CPU中，一次性会将“当前操作数据”附近的64K数据（16页数据）提前载入进高速缓冲区。

OK~，上述内容讲的是操作系统高速缓冲区的知识，在CPU中利用局部性原理，提前将数据从内存先放入L1/L2/L3三级缓冲区中，主要是为了减小CPU寄存器与内存之间的性能差异。

OK~，由于CPU寄存器和内存之间的性能差异太大，所以逐个读数据的形式会导致CPU工作期间的大量时间会处于等待数据状态，所以利用局部性原理将数据“预读”到高速区。而对于MySQL而言，亦是同理，存储数据的磁盘和内存之间的性能差异也是巨大的，因为MySQL也会利用局部性原理，提前“预读”数据。

这是什么意思呢？其实就是指MySQL一次磁盘IO不仅仅只会读取一条表数据，而是会读取多条数据，那到底读多少条数据呢？在InnoDB引擎中，一次默认会读取16KB数据到内存。

1.1.2、全表扫描过程

回到前面分析全表扫描的阶段，由于MySQL中会使用局部性原理的思想，所以对于给出的用户表数据而言，可能只需发生一次磁盘IO就能将前五条数据全部读到内存，然后会在内存中对本次读取的数据逐条判断，看一下每条数据的姓名字段是否为「黑熊」：

如果发现不符合SQL条件的行数据，则会将当前这条数据放弃，同时在本次SQL执行过程中会排除掉这条数据，不会对其进行二次读取。
如果发现当前的数据符合SQL条件要求，则会将当前数据写入到结果集中，然后继续判断其他数据。

当本次磁盘IO读取到的所有数据全部筛选完成后，紧接着会看一下表中是否还有其他数据，如果还有则继续触发磁盘IO检索数据，如果没有则将内存中的结果集返回。

有人或许会疑惑，为什么这里已经读到了符合条件的数据，还需要继续发生磁盘IO呢？因为表中的字段没有建立唯一索引或唯一约束，因此MySQL不确定是否还有其他同名的数据，所以需要将整个表全部扫描一遍，才能得到最终结论。

好的，到这里就将MySQL全表扫描的过程讲明白了，紧着来看看全表扫描有什么问题呢？

其实按目前的情况来看，似乎不会有太大的问题，因此表中数据不多，一次磁盘IO几乎就能读完。但思考一下，如果当表的数据量变为百万级别、千万级别呢？假设表中一条数据大小为512Bit，一次磁盘IO也只能读32条，假设表中有320w条数据，一次全表就有可能会触发10W次磁盘IO，每次都需要在硬件上让那个盘面转啊转，其过程的开销可想而知.....

因此建立索引的原因就在于此处，为了避免查询时走全表扫描，因此全表扫描的开销会随着数据量增长而越来越大。

1.2、索引为何不选择二叉树？

数据结构与算法，这门学科从诞生到现在，自始至终都让人难以理解，但国外有一个比较厉害的程序员，为了帮助他人更好的理解数据结构，自己搭建了一个数据结构的动画演示平台，里面提供了非常多丰富的数据结构类型，我们在其中能以动画的形式观测数据结构的变化。