再一次学习 MySQL 索引

微信扫一扫,分享到朋友圈

再一次学习 MySQL 索引

前言

提到数据库索引,大家肯定很熟悉,在日常工作中经常会接触到。这几天看了不少相关文章、书籍和课程。决定自己总结一篇文章,虽然我写的这篇文章肯定不如网上各路大神的好文,但是自己总结一遍总归记得更牢固 。这应该也是一种好的学习习惯,别人写的字再漂亮都是别人的,自己写的字就算再潦草起码自己也能认识吧 :joy:。

索引是一种提高我们查询效率的数据结构。就好像是字典的目录,一本几百页的字典,如果想快速查询到某个字,总不能靠硬翻吧 :tired_face:。

索引结构

结论

MySQL 索引一般是哈希表或 B+ 树,常用的 InnoDB 引擎默认使用的是 B+ 树来作为索引的数据结构。

为什么不用哈希表?

如果使用 B+ 树作为索引数据结构,那么访问或修改一条数据的时间复杂度是 O(log n),但是使用哈希表作为索引结构干这些活的时候,时间复杂度 O(1)。如果只是查一条数据或者修改一条数据,用哈希表做索引肯定给力呀!但是一般业务系统不会这么简单。

在业务开发中,经常会遇到范围查询、排序查询等需求。这个时候哈希表索引就没办法高效的处理这些需求了。它只能通过扫表来实现这些功能,扫表应该是数据库的噩梦吧 :weary:。

MySQL 使用 B+ 树数据结构非叶子节点只储存键值,叶子节点会储存数据或者是主键。并且在叶子节点中键是按照顺序存储的,使得范围查询、排序查询等变得异常简单。

虽然哈希表索引在操作单列数据的时候十分高效,但是需要范围查询、排序查询的时候,B+ 树数据结构显然更合适。在我们业务开发中,不可能只操作一行数据。综合考虑,还是 B+ 树更适合作为索引的数据结构。

哈希表索引不支持范围查询,不能利用索引来排序,不支持联合索引最左匹配原则,如果重复键值比较多,还容易造成哈希碰撞导致效率进一步降低 :joy:。

为什么不用 B 树?

B+ 树的非叶子节点上只储存键值,而 B 树的非叶子节点上不仅储存键值还储存数据。在 MySQL 数据库中数据页的大小是固定的,Innodb 引擎数据页默认大小为 16 KB。B+ 树这种做法是为了让树的阶数更大,让树更矮胖。进行查询的时候,磁盘 IO 次数就会减少,查询效率也会更快。

B+ 树的所有数据均储存在叶子节点中,并且是按键值有序排列。但是 B 树的数据分散在各个节点。进行范围查询,排序查询的时候,B 树的效率肯定不如 B+ 树。

B+ 树查找过程

磁盘块 1 中存储 17 和 35 数据项,还有 P1、P2、P3 指针,P1 表示数据项小于 17 的磁盘块,P2 表示数据项在 17 和 35 之间的数据项,P3 表示数据项大于 35 的数据项。非叶子节点不储存数据,只储存指引搜索方向的数据项。

我们知道每次 IO 读取一个数据页的大小,也就是一个磁盘块。假设我们要查找 29 这个数据项,首先进行第一次 IO 将磁盘块 1 读进内存,发现 17 < 29 < 35,然后选用 P2 指针进行第二次 IO 将磁盘块 3 读进内存,发现 26 < 29 < 30,然后选用 P2 指针将磁盘块 8 读进内存,在内存中做二分查找,找到 29,结束查询。

通过分析查询过程,我们可以知道 IO 次数和 B+ 树的高度成正比。H 为树的高度,M 为每个磁盘块的数据项个数,N 为数据项总数。从下面的公式可以看出如果数据量 N 一定,M 越大相应的 H 就越小。

M 等于磁盘块的大小除以数据项大小,由于磁盘块大小一般是固定的,所以减小数据项大小才能使得 M 更大从而让树更矮胖。这也是为什么 B+ 树把真实数据放在叶子节点而不是非叶子节点的原因,如果真实数据放在非叶子结点,磁盘块存储的数据项会大幅度减少,树就会增高相应查询数据时的 IO 次数就会变多。

B+ 树一般能储存多少数据?

这里我们先假设 B+ 树高为 2,即存在一个根节点和若干个叶子节点,假设一行记录的数据大小为 1 KB,那么单个叶子节点(页)中的记录数等于 16 KB / 1 KB = 16 条数据。

然后要计算出非叶子节点能存放多少指针,我们假设主键 ID 为 bigint 类型,长度为 8 字节,而指针大小在 InnoDB 源码中设置为 6 字节,这样一共 14 字节,我们一个页中能存放多少这样的单元,其实就代表有多少指针,即 16 KB / 14 B = 1170。那么可以算出一棵高度为 2 的 B+ 树,大概就能存放下 1170 * 16 = 18720 条数据。

根据同样的原理我们可以算出一个高度为 3 的 B+ 树就可以存放下 21902400 条数据。所以在 InnoDB 中 B+ 树高度一般为 1 – 3 层,它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次 IO,所以通过主键索引查询通常只需要 1 – 3 次逻辑 IO 操作即可查找到数据。

总结

  1. 哈希表索引操作单数据行的时候很快,但是不支持范围查询,不能利用索引来排序,不支持联合索引最左匹配原则。
  2. B 树的数据可以存储在非叶子节点中,范围查询时可能会有额外的随机磁盘 IO。而且由于真实数据存放在非叶子节点中,B 树的高度肯定要高于同样情况下的 B+ 树。这样也不利于提升效率。
  3. B+ 树把真实数据存储在叶子节点中是为了让树更矮胖,减少 IO 次数,提升效率。

最后

这是我学习 MySQL 索引结构记录下的一些笔记 :memo:,之后也还会总结一篇索引使用的相关注意事项。我发现阅读完各路大神的文章之后,再自己写一遍印象会深刻许多。虽然是炒旧饭,但也是炒给自己吃 :joy:。希望大家多多给我鼓励,哈哈哈哈哈。

微信扫一扫,分享到朋友圈

再一次学习 MySQL 索引

美国疫情经济奇景:任意一只宠物狗 至少价值5千美元

上一篇

欧盟同意阿尔斯通收购庞巴迪 全球轨交行业座次改写

下一篇

你也可能喜欢

再一次学习 MySQL 索引

长按储存图像,分享给朋友