Mysql B+树索引常见面试题

参考：https://www.phpmianshi.com/?id=21

概念

 

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

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

 

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

 

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

 

 

​InnoDB 一棵 B+ 树可以存放多少行数据?

 

这个问题的简单回答是：约 2 千万。

 

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

 

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

 

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

 

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

 

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

 

在 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 操作变多，查询性能变低。