Mysql的索引深度讲解

jerry_dyy

已于 2022-07-25 11:17:13 修改

阅读量719

点赞数

分类专栏： Mysql 文章标签： mysql 数据库索引

于 2022-07-12 19:34:17 首次发布

本文链接：https://blog.csdn.net/weixin_35794878/article/details/125744075

版权

Mysql 专栏收录该内容

19 篇文章 5 订阅

订阅专栏

数据页的存储结构：

数据库最终所有的数据都是存储在磁盘上的文件里的，然后在文件里存放的物理格式就是数据页，那么大量的数据页在磁盘文件里是如何存储的呢？

大量的数据页是按顺序一页一页存储的，然后两两相邻的数据页之间采用双向列表的格式相互引用，如下图：

那么这个图在磁盘文件中是如何存储的呢？

一个数据页在磁盘文件里就是一段数据，可能是二级制或其他特殊格式的数据，然后数据页里面包含两个指针，一个指向自己上一个数据页的物理地址，一个指向自己下一个数据页的物理地址。大概可以认为是下面这样子：

DataPage: xx=xx, xx=xx, linked_list_pre_pointer = 15367, linked_list_next_pointer = 34126 || DataPage: xx=xx, xx=xx, linked_list_pre_pointer = 23576, linked_list_next_pointer = 87234 || ...

每个数据页可以认为就是DataPge打头开始，直到||符号中的一段磁盘里的连续的数据。Linked_list_pre_pointer指向了上一个数据页的物理地址，linked_list_next_pointer指向了下一个数据页的物理地址。15367可以认为就是磁盘文件里的position或者offset。

然后一个数据页里面会存储一行一行的数据，然后数据页里面的每一行数据都会按照主键大小进行排序存储，同时每一行数据都有指针指向下一行数据的位置，组成单向列表，如下图：

全表扫描：

每个数据页中都会有一个页目录，里面根据数据行的主键存放了一个目录，同时数据行是分散存储到不同的槽位里去的，所以每个数据页的目录里，就是这个页里每个主键跟所在槽位的对应关系，如下图所示：

如果数据量用一个数据页就可以容纳，那么查询很简单，到数据页的页目录中根据主键进行二分查找，找到对应的槽位，然后遍历槽位中的每一行数据，就能快速找到主键对应的数据了。

但如果是根据非主键的字段进行查找，就没办法利用数据页中的页目录了，只能进入到数据页里，根据单向链表依次遍历，性能很差。

如果数据量很多，需要很多数据页来存储，那么此时，就需要遍历数据页了：

根据数据页的双向链表，从第一个数据页开始遍历所有数据页。从一个数据页开始，得把数据页从磁盘读取到内存的buffer pool中的缓存页中来，如果是主键就根据页目录中进行二分查找定位槽位，如果是非主键，只能一个一个遍历数据行。

如果第一个数据页没有找到，那么就沿着双向链表去找下一个数据页，然后读取到buffer pool的缓存页中去，然后按一样的方法继续找：

最坏的情况是，你得把所有数据页中的每一行数据全部遍历一遍，才能找到你要的数据，这就是全表扫描！

一般来说，没有索引的情况下，所有的数据查询，其实在物理层都是全表扫描。

页分裂：

我们在往表里插入数据的过程中，会涉及到一个页分裂的过程，也就是说表里是如何出现一个又一个数据页的。

一个数据页的容量是有限的，默认为16KB，当数据量多的时候，自然会发生页分裂。

同时页分裂过程中才会涉及到数据的挪动，尤其是主键为UUID的时候。因为数据页必须保证的一点是：上一个数据页的最大值小于下一个数据页的最小值。

思考：UUID为主键时，触发页分裂的条件是什么？主键自增时，触发页分裂的条件又是什么？

主键索引：

现在是这样的，假设我们有多个数据页，然后我们想要根据主键来查询数据，那么直接查询的话也是不行的，因为我们也不知道主键到底是在哪里，是不是？

比如下图：

现在假如要搜id=4的主键，你怎么知道它在哪个数据页里面？假设还是这个样子的话，也就只能全表扫描了。

所以此时，就需要针对主键设计一个索引了。就是创建一个主键目录，把每个数据页的页号，还有数据页里的最小主键值放在一起，组成一个目录，如下图：

现在有了这个主键目录，就可以直接到主键目录中去搜索，比如你要找id=3的数据，那么可以直接定位到数据页2。如果有很多数据页，主键目录中有很多条记录，那么此时就可以根据二分法去查找到你要找的数据在哪个数据页里。

B+树：

上面示例中的主键目录实际上也是放在数据页中的，我们可以认为是索引页，因为里面存放的都是索引。

那么如果你的表里有几百万、几千万的数据，那么此时，一个索引页肯定放不下了，会有很多的索引页，如下图：

那么上一次的那个问题又出现了，我该到那个索引页里面去找呢？

于是，我们可以把索引页多出来一个层级，在更高层级的索引页中，保存了每个索引页页号和索引页里的最小主键值，如下图：

现在好了，我们可以从最上层的索引页开始，一层一层往下找，最终定位到某个数据页，然后根据页目录定位到某个槽位。

随着数据量的继续增大，如果最底层的索引页也放不下那么多数据了的话，那么就会出现更高层次的索引页。一层一层组成一个树，也就是我们常说的B+树。

当你为一个表的主键建立起索引之后，其实这个主键的索引就是一颗B+树，然后当你要根据主键来查找数据的时候，直接就是从B+树的顶层开始，进行二分查找（B+树的每个节点，也就是每一个索引页，都是有很多条数据的，所以需要进行二分查找，定位到下一层的索引页或数据页地址），一层一层往下定位，最终一直定位到数据页里，在数据页的页目录中进行二分查找，找到那条数据。