Mysql B+树索引

分手后的思念是犯贱 2023-10-12 12:53 57阅读 0赞

备注：先说下，在MySQL文档里，实际上是把B+树索引写成了BTREE

unique key(uid) USING BTREE,

一个经典的B+树索引数据结构见下图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm_size_16_color_FFFFFF_t_70][]

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。

在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

因此，B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下，xfs文件系统比ext3/ext4效率高很多的原因之一就是，它的文件及目录索引结构全部采用B+树索引，而ext3/ext4的文件目录结构则采用Linked list, hashed B-tree、Extents/Bitmap等索引数据结构，因此在高I/O压力下，其IOPS能力不如xfs。

而哈希索引的示意图则是这样的：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm_size_16_color_FFFFFF_t_70 1][]

简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

从上面的图来看，B+树索引和哈希索引的明显区别是：

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；当然了，这个前提是，键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据；  
从示意图中也能看到，如果是范围查询检索，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；  
同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；  
哈希索引也不支持多列联合索引的最左匹配规则；  
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。  
在MySQL中，只有HEAP/MEMORY引擎表才能显式支持哈希索引（NDB也支持，但这个不常用），InnoDB引擎的自适应哈希索引（adaptive hash index）不在此列，因为这不是创建索引时可指定的。

还需要注意到：HEAP/MEMORY引擎表在mysql实例重启后，数据会丢失。

通常，B+树索引结构适用于绝大多数场景，像下面这种场景用哈希索引才更有优势：

在HEAP表中，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，特别适合采用哈希索引

例如这种SQL：

SELECT … FROM t WHERE C1 = ?; — 仅等值查询

在大多数场景下，都会有范围查询、排序、分组等查询特征，用B+树索引就可以了。

为什么MySQL索引要用B+树，而不是B树？

一个面试题：InnoDB 一棵 B+ 树可以存放多少行数据?这个问题的简单回答是：约 2 千万。

在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节

而文件系统(例如 XFS/EXT4)他的最小单元是块，一个块的大小是 4K。

而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页(Page)，一个页的大小是 16K。

文件系统中一个文件大小只有 1 个字节，但不得不占磁盘上 4KB 的空间。

![20191114095345422.png][]

InnoDB 的所有数据文件(后缀为 ibd 的文件)，他的大小始终都是 16384(16K)的整数倍。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm_size_16_color_FFFFFF_t_70 2][]

在 MySQL 中我们的 InnoDB 页的大小默认是 16K，当然也可以通过参数设置：

mysql> show variables like 'innodb\_page\_size';

\+------------------+-------+

| Variable\_name | Value |

\+------------------+-------+

| innodb\_page\_size | 16384 |

\+------------------+-------+

1 row in set (0.00 sec)

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢?假设一行数据的大小是 1K，那么一个页可以存放 16 行这样的数据。

InnoDB 存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在 B+ 树中叶子节点存放数据，非叶子节点存放键值+指针。  
索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据。  
那么回到我们开始的问题，通常一棵B+树可以存放多少行数据?

这里我们先假设 B+ 树高为 2，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：根节点指针数\*单个叶子节点记录行数。

上文我们已经说明单个叶子节点(页)中的记录数=16K/1K=16。(这里假设一行记录的数据大小为 1K，实际上现在很多互联网业务数据记录大小通常就是 1K 左右)。

那么现在我们需要计算出非叶子节点能存放多少指针?其实这也很好算，我们假设主键 ID 为 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节。

我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170。

那么可以算出一棵高度为 2 的 B+ 树，能存放 1170\*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为 3 的 B+ 树可以存放：1170\*1170\*16=21902400 条这样的记录。

所以在 InnoDB 中 B+ 树高度一般为 1-3 层，它就能满足千万级的数据存储。

在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

总结

lineitem 表的数据行数为 600 多万，B+ 树高度为 3，customer 表数据行数只有 15 万，B+ 树高度也为 3。

可以看出尽管数据量差异较大，这两个表树的高度都是 3。换句话说这两个表通过索引查询效率并没有太大差异，因为都只需要做 3 次 IO。

那么如果有一张表行数是一千万，那么他的 B+ 树高度依旧是 3，查询效率仍然不会相差太大。region 表只有 5 行数据，当然他的 B+ 树高度为 1。

为什么 MySQL 的索引要使用 B+ 树而不是其他树形构?比如 B 树?

他的简单版本回答是：因为 B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出)。

指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。  
————————————————  
版权声明：本文为CSDN博主「私念」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/tiancityycf/article/details/103062003

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191114095241508.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191114095310482.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm,size_16,color_FFFFFF,t_70
[20191114095345422.png]: https://img-blog.csdnimg.cn/20191114095345422.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191114095414483.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RpYW5jaXR5eWNm,size_16,color_FFFFFF,t_70