B+树在数据库索引中的应用

╰+哭是因爲堅強的太久メ 2022-05-25 01:23 154阅读 0赞

目前大部分数据库系统及文件系统都采用[B-Tree或其变种B+Tree][B-Tree_B_Tree]作为索引结构(更少的磁盘I/O操作次数的渐进复杂度)

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

#### MyISAM的索引实现 ####

数据库不同的搜索引擎辅助索引的实现原理不一样，MyISAM是索引和文件分离的

一般以主键为索引的叫做主索引，而以其他键为索引的叫做辅助索引；

直接上MyISAM的实现原理，利用B+树实现，

![这里写图片描述][70]

由上图可以看出，col1是主键，而叶子结点存储的数据是一个地址，通过地址找到数据；

下面是Col2列的辅助索引（和主索引不同的是辅助索引的key是可以重复的）  
![这里写图片描述][70 1]

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

#### InnoDB的索引实现 ####

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从前文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

![这里写图片描述][70 2]

上图是InnoDB主键索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

**注意，和MyISAM不同的是叶子结点的数据域保存的是全部数据；**

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

![这里写图片描述][70 3]

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如**知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。**

#### MyISAM和InnoDB在索引上的优劣 ####

既然MyISAM和InnoDB是MySQL的两代引擎，肯定会有一个提升，而InnoDB是最新一代，那么它到底优在哪里？

试想，MyISAM和InnoDB都是以B+树为基础实现的，相对于B树的不同其实前面已经讲过，即数据域和结点分离；

而MyISAM更是索引和文件分离，B+树的叶子结点的数据域存放的是文件内容的地址，主索引和辅助索引的B+树都是如此，那么如果我改变了一个地址，是不是所有的索引树都得改变，正如前面我们讲的在磁盘上频繁的读写操作是效率很低的，而这块又不适用局部原理，因为逻辑上相邻的结点，物理上不一定相邻，那么这样就会造成效率上的降低；

于是乎，InnoDB就产生了，它让除了主索引以外的辅助索引的叶子结点的数据域都保存主键，**先通过辅助索引找到主键，然后通过主键找到叶子结点的所有数据，听起来貌似很麻烦，遍历了两棵树，但是，这样如果有了修改的话，改变的只是主索引，其它辅助索引都不用动**，而且，数据库中的树的每一个结点的key可不是咱们给的那么少，试想如果一个结点有1024个key，那么高度为2的B+树都有1024\*1024个key，所以一般树的高度都很低，所以，遍历树的消耗几乎忽略不计！

#### InnoDB的主键选择与插入优化 ####

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

经常看到有帖子或博客讨论主键选择问题，有人建议使用业务无关的自增主键，有人觉得没有必要，完全可以使用如学号或身份证号这种唯一字段作为主键。不论支持哪种论点，大多数论据都是业务层面的。**如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。**

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此，只要可以，请尽量在InnoDB上采用自增字段做主键。

参考：[http://blog.codinglabs.org/articles/theory-of-mysql-index.html][http_blog.codinglabs.org_articles_theory-of-mysql-index.html]

[B-Tree_B_Tree]: https://blog.csdn.net/canot/article/details/79999164
[70]: /images/20220525/a992c3c235844d84bcc541c64f3a0d19.png
[70 1]: /images/20220525/54630eb419ea42e6a9315673710087b1.png
[70 2]: /images/20220525/0e312ba4d92e4a9c830d937125abafb2.png
[70 3]: /images/20220525/f455a2545886460c829a10ecc483cc47.png
[http_blog.codinglabs.org_articles_theory-of-mysql-index.html]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html