索引为什么选择B+Tree

最新推荐文章于 2024-08-17 15:04:11 发布

原创最新推荐文章于 2024-08-17 15:04:11 发布 · 530 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #innodb #索引

MySQL 专栏收录该内容

4 篇文章

订阅专栏

本文深入探讨了数据库索引选择B+树的原因，对比了哈希、二叉搜索树、二叉平衡树、红黑树和B树等多种数据结构，分析了它们在存储和查询效率上的优缺点。

索引为什么选择B+Tree

文章目录

索引为什么选择B+Tree

一、简介

本文主要解答索引为什么选择B+Tree，而不使用哈希、二叉搜索树、二叉平衡树、红黑树、B Tree。

索引选择哪种数据结构，主要从两方面来考虑：

数据的存储效率；
数据的查询效率。

本文通过讲解每种数据结构在这两方面的效率，以及其他方面的优缺点，来回答为什么选择B+Tree。

本人还写了MySQL相关博文，有兴趣的研友可以点击如下链接，请各位研友指正并留言。
MySQL事务与隔离级别
 MySQL的锁及其MVCC
MySQL索引及优化

二、哈希

哈希数据结构如图所示：

哈希数据结构的哈希值是将键通过某种哈希算法转化而来，哈希算法是多种多样的，也可以自定义的。

优点：只要知道“键”，立马通过哈希算法得到哈希值，从而找到目标数据，查询速度快。

缺点如下：

为了防止哈希值冲突，往往需要复杂化的哈希算法；
查询数据时，需要将所有数据文件添加到内存中，若数据量大时，耗费内存空间；
哈希数据结构无法进行范围查找。实际工作中，往往是范围查找。

总结：哈希数据结构存储效率正常，对于定值查询效率高，但不支持范围查询。

三、树

树的结构为一个节点可以有多个叶节点。如图所示：

缺点：查询数据时，需要对数一层一层的查找，效率低。

四、二叉树

二叉树在树的结构上进行了优化，为了方便管理，规定每个节点最多只能有两个子节点。（这是为了进一步优化为二叉搜索树、二叉平衡树等）

缺点：查询数据时，需要对数一层一层的查找，效率低。

五、二叉搜索树

在二叉树的基础上，二叉搜索树作了进一步规定：

左子树必须小于根节点；
右子树必须大于根节点。

优点：根据二叉搜索树规定的节点与左右节点的关系，查询数据时，可使用二分查找，查询效率大大提升。

缺点：存储数据时，如果数据是有序的，并按顺序存储时，会导致某一字数过长，如，顺序存储1、2、3、4、5，如图所示：

由图可见，对于这种特殊插入情况，会导致二叉树搜索树失去树的结构，称为了一种链式结构，那么就会导致查询数据时效率低。

六、二叉平衡树

针对二叉搜索树的缺点，二叉平衡树在二叉树的基础上，加入了左右旋，以满足最长子树与最短子树相差不超过1，同时也满足二分查找。

顺序插入1、2、3时，二叉平衡树右旋，如图所示：

顺序插入4、5时，二叉平衡树右旋，最终树如图所示：

优点：查询数据时，可使用二分查找提高查询效率。

缺点：

左右旋频繁操作，导致存储数据效率变低；
随着存储数据的增多，树的层次加深，那么访问数据的IO次数也增加，导致查询数据效率逐渐变低。

七、红黑树

针对二叉平衡树的左右旋操作，红黑树进行了改进，目的在于，牺牲一点存储效率，以提升查询效率。

红黑树规定：

最长子树不超过最短子树的两倍即可。（也就减少了左右旋的次数）

红黑变色规则：

插入的每个节点必须是红色；
每一条路径不允许有两个连续的红色节点；
任何一个分支上，黑色节点个数必须相等。

顺序插入1、2、3、4、5、6时，红黑树如图所示：

优点：相对于二叉平衡树的插入效率要高，因为左右旋的次数降低。

缺点：随着存储数据的增多，树的层次加深，那么访问数据的IO次数也增加，导致查询数据效率逐渐变低。

八、B Tree

基于红黑树的缺点，要想降低树的深度，那么要打破每个节点只能存储两个子节点的限制。

允许每个节点有多个子节点，B Tree如图所示：

优点：相对于红黑树，在相同数据量的条件下，B Tree的深度较小。

缺点：

随着存储数量的增加，深度还是会加深，查询效率低；
叶子节点没有双向指针，范围查找效率低。

九、B+Tree

基于B Tree深度还是会加深的缺点，试图让一个节点存储更对的“键”，一个办法就是将节点中的数据data去掉，这样一个节点存储“键”的个数增加，所以深度就会降低。

基于B Tree不能范围查找的缺点，B+Tree在叶子节点中，增加双向链表，就可以进行范围查找。

经过从哈希数据结构到B+Tree数据结构的讲解，理解了每一种树要解决的问题，现在很容易理解索引选择B+Tree的原因了吧。

注意：

InnoDB是通过B+Tree结构对主键创建索引的，然后叶子接机点中存储记录，如果没有主键，那么会选择唯一键，如果没有唯一键，那么会生成一个6位的row_id作为主键；
如果创建索引的键是其他字段，那么在叶子节点中存储的是该记录的主键，然后再通过主键索引找到对应的记录，叫做回表。

十、InnoDB一棵三层B+树可以存放两千万行数据

InnoDB存储引擎的最小储存单元——页（Page），一个页的大小是16K。假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16*1024/14=1170。

可以算出一个高度为3的B+Tree可以存放：1170 * 1170 * 16=21902400条这样的记录。

十一、InnoDB与MyISAM中的B+Tree区别

InnoDB中B+Tree的叶子节点存储了数据，而MyISAM中B+Tree的叶子节点存储了数据的地址。

InnoDB的B+Tree：

MyISAM的B+Tree：

十二、索引的相关问题

12.1、聚集索引、非聚集索引

聚集索引：在B+Tree中，叶子节点存储了整行数据的索引。在InnoDB中，只有主键索引是聚集索引，当没有主键时，会选取唯一键；当没有唯一键时，会添加6位row_id作为主键。其他索引都是非聚集索引。

非聚集索引：在B+Tree中，叶子节点没有存储整行数据的索引。比如MyISAM中B+Tree叶子节点存储的是数据的地址。

12.2、回表

在InnoDB中，除主键索引外，比如组合索引，叶子节点存储的是数据的主键信息。当通过组合索引查询到对应的主键信息后，再到主键索引中查询整行数据，该过程称为回表。

12.3、覆盖索引

在InnoDB中，使用索引查询到对应数据的主键信息，该主键正是本次查询的数据，可以直接返回，而不用访问回表，该过程称为覆盖索引。

12.4、索引下推

当有组合索引name、age时，通过该索引查询会先根据name得到主键并回表，回表会增加查询的步骤、降低查询效率，回到主键索引再查询age。

而使用索引下推后，就会优先在索引上进行查询过滤。

使用explain查询会发现：

当Extra的值为Using index condition; 说明查询的字段全部在索引上完成了过滤操作，回表时会根据主键直接得到对应的行数据。
当Extra的值为Using where时，可能查询字段没有设置索引，这时会直接在主键索引上查询，效率低下。
当Extra的值为Using index condition; Using where时，说明查询的一部分部分字段有索引，另一部分没有索引，当有索引的字段完成查询时回表，并不会立马返回数据，而是还要在全表查询那些没有索引的字段。对应的行数据。