为啥 SQL 加了索引会使数据查找更快？

短命女 2023-07-20 10:37 17阅读 0赞

**点击关注上方“五分钟学算法”，**

**设为“置顶或星标”，第一时间送达干货。**

![format_png][]

转自码海

## 前言 ##

每当我们执行某个 SQL 发现很慢时，都会下意识地反应是否加了索引，那么大家是否有想过加了索引为啥会使数据查找更快呢，索引的底层一般又是用什么结构存储的呢，相信大家看了标题已经有答案了，没错！B+树！那么它相对于一般的链表，哈希等有何不同，为何多数存储引擎都选择使用它呢，今天我就来揭开 B+ 树的面纱，相信看了此文，B+ 树不再神秘，对你理解以下高频面试题会大有帮助！

*  为啥索引常用 B+ 树作为底层的数据结构
 *  除了 B+ 树索引，你还知道什么索引
 *  为啥推荐自增 id 作为主键，自建主键不行吗
 *  什么是页分裂，页合并
 *  怎么根据索引查找行记录

本文将会从以下几个方面来讲解 B+ 树

1.  定义问题
2.  几种常见的数据结构对比
3.  页分裂与页合并

## 定义问题 ##

要知道索引底层为啥使用 B+ 树，得看它解决了什么问题，我们可以想想，日常我们用到的比较多的 SQL 有哪些呢。

假设我们有一张以下的用户表：

CREATE  TABLE  `user` (
      `id` int(11) unsigned  NOT  NULL AUTO_INCREMENT,
      `name` varchar(20) DEFAULT  NULL COMMENT '姓名',
      `idcard` varchar(20) DEFAULT  NULL COMMENT '身份证号码',
      `age` tinyint(10) DEFAULT  NULL  COMMENT '年龄',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB  DEFAULT  CHARSET=utf8 COMMENT='用户信息';

一般我们会有如下需求：

**1、根据用户 id 查用户信息**

select * from  user  where  id = 123;

**2、根据区间值来查找用户信息**

select * from  user  where  id > 123  and  id < 234;

**3、按 id 逆序排列，分页取出用户信息**

select * from  user  where  id <  1234  order  by  id  desc  limit  10;

从以上的几个常用 SQL 我们可以看到索引所用的数据结构必须满足以下三个条件

1.  根据某个值精确快速查找
2.  根据区间值的上下限来快速查找此区间的数据
3.  索引值需要排好序，并支持快速顺序查找和逆序查找

接下来我们以主键索引（id 索引）为例来看看如何用相应的数据结构来构造它

## 几种常见的数据结构对比 ##

接下来我们想想有哪些数据结构满足以上的条件

**1、散列表**

散列表（也称哈希表）是根据关键码值(Key value)而直接进行访问的数据结构，它让码值经过哈希函数的转换映射到散列表对应的位置上，查找效率非常高。哈希索引就是基于散列表实现的，假设我们对名字建立了哈希索引，则查找过程如下图所示：

![format_png 1][]

对于每一行数据，存储引擎都会对所有的索引列（上图中的 name 列）计算一个哈希码（上图散列表的位置），散列表里的每个元素指向数据行的指针，由于索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引查找速度非常快！但是哈希索引也有它的劣势，如下：

1.  针对哈希索引，只有精确匹配索引所有列的查询才有效，比如我在列（A,B）上建立了哈希索引，如果只查询数据列 A，则无法使用该索引。
2.  哈希索引并不是按照索引值顺序存存储的，所以也就无法用于排序，也就是说无法根据区间快速查找
3.  哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用索引中的值来避免读取行，不过，由于哈希索引多数是在内存中完成的，大部分情况下这一点不是问题
4.  哈希索引只支持等值比较查询，包括 =,IN()，不支持任何范围的查找，如 age > 17

综上所述，哈希索引只适用于特定场合， 如果用得对，确实能再带来很大的性能提升，如在 InnoDB 引擎中，有一种特殊的功能叫「自适应哈希索引」，如果 InnoDB 注意到某些索引列值被频繁使用时，它会在内存基于 B+ 树索引之上再创建一个哈希索引，这样就能让 B+树也具有哈希索引的优点，比如快速的哈希查找。

**2、链表**

双向链表支持顺序查找和逆序查找，如图下

![format_png 2][]

但显然不支持我们说的按某个值或区间的**快速查找**，另外我们知道表中的数据是要不断增加的，索引也是要及时插入更新的，链表显然也不支持数据的快速插入，所以能否在链表的基础上改造一下，让它支持快速查找，更新，删除。有一种结构刚好能满足我们的需求，这里引入跳表的概念。

什么是跳表?简单地说，跳表是在链表之上加上多层索引构成的。如下图所示

![format_png 3][]

假设我们现在要查找区间 7- 13 的记录，再也不用从头开始查找了，只要在上图中的二级索引开始找即可，遍历三次即可找到链表的区间位置，时间复杂度是 O(logn)，非常快，这样看来，跳表是能满足我们的需求的，实际上它的结构已经和 B+ 树非常接近了，只不过 B+ 树是从平衡二叉查找树演化而来的而已，接下来我们一步步来看下如何将平衡二叉查找树改造成 B+ 树。

先来看看什么是平衡二叉查找树，平衡二叉查找树具有如下性质：

1.  若左子树不空，则左子树上所有节点的值均小于它的根节点的值；
2.  若右子树不空，则右子树上所有节点的值均大于或等于它的根节点的值；
3.  每个非叶子节点的左右子树的高度之差的绝对值（平衡因子）最多为1。

下图就是一颗平衡二叉查找树

![format_png 4][]

从其特性就可以看到平衡二叉查找树查找节点的时间复杂度是 O(log2n)

现在我们将其改造成 B+ 树

![format_png 5][]

可以看到主要区别就是所有的节点值都在最后叶节点上用双向链表连接在了一起，仔细和跳表对比一下 ，是不是很像，现在如果我们要找15 ~ 27 这个区间的数只要先找到 15 这个节点（时间复杂度 logn = 3 次）再从前往后遍历直到 27 这个节点即可，即可找到这区间的节点，这样它完美地支持了我们提的三个需求：快速查找值，区间，顺序逆序查找。

假设有 1 亿个节点，每个节点要查询多少次呢，显然最多为 log21亿 = 27 次，如果这 1 亿个节点都在内存里，那 27 次显然不是问题，可以说是非常快了，但一个新的问题出现了，这 1 亿个节点在内存大小是多少呢，我们简单算一下，假设每个节点 16 byte，则 1 亿个节点大概要占用 1.5G 内存！对于内存这么宝贵的资源来说是非常可怕的空间消耗，这还只是一个索引，一般我们都会在表中定义多个索引，或者库中定义多张表，这样的话内存很快就爆满了！所以在内存中完全装载一个 B+ 树索引显然是有问题的，如何解决呢。

内存放不下， 我们可以把它放到磁盘嘛，磁盘空间比内存大多了，但新的问题又来了，我们知道内存与磁盘的读取速度相差太大了，通常内存是纳秒级的，而磁盘是毫秒级的，读取同样大小的数据，两者可能相差上万倍，于是上一步我们计算的 27 次查询如果放在磁盘中来看就非常要命了（查找一个节点可以认为是一次磁盘 IO，也就是说有 27 次磁盘 IO！），27 次查询是否可以优化？

可以很明显地观察到查询次数和树高有关，那树高和什么有关，很明显和每个节点的子节点个数有关，即 N 叉树中的 N，假设现在有 16 个数，我们分别用二叉树和五叉树来构建，看下树高分别是多少

![format_png 6][] ![format_png 7][]

可以看到如果用二叉树 ，要遍历 5 个节点，如果用五叉树 ，只要遍历 3 次，一下少了两次磁盘 IO，回过头来看 上文的一亿个节点，如果我们用 100 叉树来构建，需要几次 IO 呢

![format_png 8][]

可以看到，最多遍历五次（实际上根节点一般存在内存里的，所以可以认为是 4 次）！磁盘 IO 一下从 27 减少到了 5！性能可以说是大大提升了,有人说 5 次还是太多，是不是可以把 100 叉树改成 1000 或 10000 叉树呢，这样 IO 次数不就就能进一步减少了。

这里我们就需要了解页（page）的概念，在计算机里，无论是内存还是磁盘，操作系统都是按页的大小进行读取的（页大小通常为 4 kb），磁盘每次读取都会**预读**，会提前将连续的数据读入内存中，这样就避免了多次 IO，这就是计算机中有名的**局部性原理**，即我用到一块数据，很大可能这块数据附近的数据也会被用到，干脆一起加载，省得多次 IO 拖慢速度， 这个连续数据有多大呢，必须是操作系统页大小的整数倍，这个连续数据就是 MySQL 的页，默认值为 16 KB，也就是说对于 B+ 树的节点，最好设置成页的大小（16 KB），这样一个 B+ 树上的节点就只会有一次 IO 读。

那有人就会问了，这个页大小是不是越大越好呢，设置大一点，节点可容纳的数据就越多，树高越小，IO 不就越小了吗，这里要注意，页大小并不是越大越好，InnoDB 是通过内存中的缓存池（pool buffer）来管理从磁盘中读取的页数据的。页太大的话，很快就把这个缓存池撑满了，可能会造成页在内存与磁盘间频繁换入换出，影响性能。

通过以上分析，相信我们不难猜测出 N 叉树中的 N 该怎么设置了，只要选的时候尽量保证每个节点的大小等于一个页（16kb）的大小即可。

## 页分裂与页合并 ##

现在我们来看看开头的问题， 为啥推荐自增 id 作为主键，自建主键不行吗，有人可能会说用户的身份证是唯一的，可以用它来做主键，假设以身份证作主键，会有什么问题呢。

B+ 树为了维护索引的有序性，每插入或更新一条记录的时候，会对索引进行更新。假设原来基于身份证作索引的 B+ 树如下（假设为二叉树 ，图中只列出了身份证的前四位）

![format_png 9][]

现在有一个开头是 3604 的身份证对应的记录插入 db ，此时要更新索引，按排序来更新的话，显然这个 3604 的身份证号应该插到左边节点 3504 后面（如下图示，假设为二叉树）

![format_png 10][]

如果把 3604 这个身份证号插入到 3504 后面的话，这个节点的元素个数就有 3 个了，显然不符合二叉树的条件，此时就会造成**页分裂**，就需要调整这个节点以让它符合二叉树的条件

![format_png 11][]

*如图示：调整过后符合二叉树条件*

这种由于页分裂造成的调整必然导致性能的下降，尤其是以身份证作为主键的话，由于身份证的随机性，必然造成大量的随机结点中的插入，进而造成大量的页分裂，进而造成性能的急剧下降，那如果是以自增 id 作为主键呢，由于新插入的表中生成的 id 比索引中所有的值都大，所以它要么合到已存在的节点（元素个数未满）中，要么放入新建的节点中（如下图示）所以如果是以自增 id 作为主键，就不存在页分裂的问题了，推荐！

![format_png 12][]

有页分裂就必然有页合并，什么时候会发生页合并呢，当删除表记录的时候，索引也要删除，此时就有可能发生页合并，如图示

![format_png 13][]

当我们删除 id 为 7，9 对应行的时候，上图中的索引就要更新，把 7，9 删掉，此时 8，10 就应该合到一个节点，不然 8，10 分散在两个节点上，可能造成两次 IO 读，势必会影响查找效率! 那什么时候会发生页合并呢，我们可以定个阈值，比如对于 N 叉树来说，当节点的个数小于 N/2 的时候就应该和附近的节点合并，不过需要注意的是合并后节点里的元素大小可能会超过 N，造成页分裂，需要再对父节点等进行调整以让它满足 N 叉树的条件。

## 怎么根据索引查找行记录 ##

相信大家看完以上的 B+ 树索引的介绍应该还有个疑惑，怎么根据对应的索引值查找行记录呢，其实相应的行记录就放在最后的叶子节点中，找到了索引值，也就找到了行记录。如图示

![format_png 14][]

可以看到，非叶子节点只存了索引值，只在最后一行才存放了行记录，这样极大地减小了索引了大小，而且只要找到索引值就找到了行记录，也提升了效率，

这种在叶节点存放一整行记录的索引被称为聚簇索引，其他的就称为非聚簇索引。

## 关于 B+ 树的总结 ##

综上所述，B+树有以下特点：

*  每个节点中子节点的个数不能超过 N，也不能小于 N/2（不然会造成页分裂或页合并）
 *  根节点的子节点个数可以不超过 m/2，这是一个例外
 *  m 叉树只存储索引，并不真正存储数据，只有最后一行的叶子节点存储行数据。
 *  通过链表将叶子节点串联在一起，这样可以方便按区间查找

## 总结 ##

本文由日常中常用的 SQL 由浅入深地总结了 B+ 树的特点，相信大家应该对 B+ 树索引有了比较清晰地认识，所以说为啥我们要掌握底层原来，学完了 B+ 树，再看开头提的几个问题，其实也不过如此，深挖底层，有时候确实能让你以不变应万变。

**END**

--------------------

![format_png 15][]

● [我这些年从来没有用过算法，除了出去面试的时候][Link 1]

[● ][Link 2][火爆全网！这个 Python 项目很骚气！][Python]

● [面试了15位来自985/211高校的2020届研究生，熬夜赶出了这篇文章][15_985_211_2020]

● [Chrome，你够了！][Chrome]

![format_png 16][]

点“在看”你懂得

![format_png 17][]

[format_png]: /images/20230528/cfb263dd539e49e4b3cd2536fffddd70.png
[format_png 1]: /images/20230528/56ded6e63a574ec8b019dd64bbeec57e.png
[format_png 2]: /images/20230528/fbd7c6dcee644dafb76f164eebfef056.png
[format_png 3]: /images/20230528/8a2e7bd615764a56bddd2eaa104434b5.png
[format_png 4]: /images/20230528/210061b685004e2280fc3f2667fc1235.png
[format_png 5]: /images/20230528/cdd3beab15964b9fa431f9136acf19d9.png
[format_png 6]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X3BuZy9PeXdleXNDU2VMVTBic05lQ2RNQkNiblBiakpnWGdyTWljZ2RnZk1uaWNEUHBicVlzVHJmdWliRUlTRmdxYzJzWlFmbkhxVlhGczZDVncxMkxod3pTRFljZy82NDA?x-oss-process=image/format,png
[format_png 7]: /images/20230528/38abd62c434d41748f748a17c5b56ffc.png
[format_png 8]: /images/20230528/72064d8ae5b645249d00c4cf158445d1.png
[format_png 9]: /images/20230528/26c4491ff02148b685250cd1196579dc.png
[format_png 10]: /images/20230528/5f08fbd8d24b4e38a438b2fab3626daa.png
[format_png 11]: /images/20230528/c153fa4e7a8047d19243454e58e521c7.png
[format_png 12]: /images/20230528/86d09b945add44c3af4df1061f000895.png
[format_png 13]: /images/20230528/9ae92a70be1147ba8d6ea8bf760adb56.png
[format_png 14]: /images/20230528/616cf4e3a5db4f6cb9e4e8d5bf8e93b8.png
[format_png 15]: /images/20230528/f9cd1d532b1a45158365aad71c7ba8b0.png
[Link 1]: https://blog.csdn.net/kexuanxiu1163/article/details/105190257
[Link 2]: http://mp.weixin.qq.com/s?__biz=Mzg2MzE5MjMxNQ%3D%3D&chksm=ce7ef418f9097d0e01bfe9410a9e40e9f4337c835c87e28332e6a8d8fd8b91b4794164f4c1a1&idx=1&mid=2247491980&scene=21&sn=5ef03c0108a5730fa28e492ec77e22d9#wechat_redirect
[Python]: https://blog.csdn.net/kexuanxiu1163/article/details/105212791
[15_985_211_2020]: https://blog.csdn.net/kexuanxiu1163/article/details/105172002
[Chrome]: https://blog.csdn.net/kexuanxiu1163/article/details/105212789
[format_png 16]: /images/20230528/8442eb18097f4c8faaf42a9e48fd4552.png
[format_png 17]: /images/20230528/74b24ecd417949cf8892ff012c386832.png