MySQL 索引结构用B+树的原因

索引(Index)

二叉树、平衡二叉树、红黑树、B树、B+树与B*树

一、MySQL 索引为什么用 B+ 树而不是 B 树

1. 【B+树的磁盘读写代价更低】B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对 IO 读写次数就降低了。

2. 【B+树的查询效率更加稳定】由于非叶子结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3. 由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可。但是B树因为其分支结点同样存储着数据，要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况。

4. B树在提高了IO性能的同时并没有解决元素遍历的效率低下的问题，正是为了解决此问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

二、MySQL 索引为什么用 B+ 树而不是红黑树

Mysql 如何衡量查询效率呢？磁盘IO次数。红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以选取 B+ 树。B+ 树可以有多个子女，从几十到上千，可以降低树的高度。

三、MySQL 索引为什么用 B+ 树而不是跳表

【B+树结构】
B+数是平衡多路查找树。特点是一个结点上可以存储多个元素，带来了扇出较高的优点。只需要很少的层数就可以存储大量的数据。和B树相比，它在叶子结点上存储所有的数据。

【跳表】
跳表也是分层的。每一层都是一个链表。除了最下面的一层，每个元素除了有指向下一个元素的指针，还有一个指针指向下一层中和自己相同的元素。上一层的链表做为索引加快对下层链表元素的查找。每一层元素都是顺序排列的。当插入一个元素时，使用随机函数决定其是否在每一层插入。在最底层插入的概率是100%，在倒数第二层是50%，依次类推。这样在从最上层往下查询时就实现了二分查找的效果。

B+ 树具有较高的扇出，即一个结点可以有成百上千个子节点。这种结构导致了B+数只需要3层就大概可以存储2kw的数据。而存储同样数据量的数据，跳表如果要达到二分查找的效果需要20多层。MySQL 的数据是存储在磁盘中的，层数越多磁盘 IO 的次数就越多。所以选择层数较少的 B+ 树做为 MySQL 索引的底层结构。

Redis 的 zset 为什么又用跳表实现

因为Redis是内存数据库，没有磁盘IO的问题。层数多一些不是明显的问题。

四、InnoDB 的 B+ 树可以存放约 2 千万行数据

为什么？要搞清楚这个问题，先从 InnoDB 索引数据结构、数据组织方式说起。计算机在存储数据的时候，有最小存储单元，这就好比今天进行现金的流通最小单位是一毛。

• 计算机磁盘存储数据最小单元是扇区，一个扇区的大小是 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 行这样的数据。如果数据库只按这样的方式存储，那么如何查找数据就成为一个问题。因为不知道要查找的数据存在哪个页中，也不可能把所有的页遍历一遍，那样太慢了。所以用 B+ 树的方式组织这些数据，如图：

先将数据记录按主键进行排序，分别存放在不同的页中(便于理解此处一个页中只存放 3 条记录，实际可以存放很多)。除了存放数据的页以外，还有存放键值+指针的页，如图中 page number=3 的页，该页存放键值和指向数据页的指针，这样的页由 N 个键值+指针组成。当然它也是排好序的。这样的数据组织形式，称为索引组织表。现在来看下，要查找一条数据，怎么查？如：

select * from user where id=5;

这里 id 是主键，通过这棵 B+ 树来查找，首先找到根页，如何知道 user 表的根页位置？

其实每张表的根页位置在表空间文件中是固定的，即 page number=3 的页。找到根页后通过二分查找法，定位到 id=5 的数据应该在指针 P5 指向的页中，那么进一步去 page number=5 的页中查找，同样通过二分查询法即可找到 id=5 的记录：

5 zhao2 27 

小结：

1. InnoDB 存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在 B+ 树中叶子节点存放数据，非叶子节点存放键值+指针。
2. 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据。

回到开始的问题，通常一棵 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 操作即可查找到数据。

五、如何得到 InnoDB 主键索引 B+ 树的高度

在 InnoDB 的表空间文件中，约定 page number 为 3 的代表主键索引的根页，而在根页偏移量为 64 的地方存放了该 B+ 树的 page level。

如果 page level 为 1，树高为 2，page level 为 2，则树高为 3。即 B+ 树的高度=page level+1。下面将从实际环境中尝试找到这个 page level。

在实际操作之前，可以通过 InnoDB 元数据表确认主键索引根页的 page number 为 3，也可以从《InnoDB 存储引擎》这本书中确认：

SELECT 
b.name, a.name, index_id, type, a.space, a.PAGE_NO 
FROM 
information_schema.INNODB_SYS_INDEXES a, 
information_schema.INNODB_SYS_TABLES b 
WHERE 
a.table_id = b.table_id AND a.space <> 0; 

执行结果：

可以看出数据库 dbt3 下的 customer 表、lineitem 表主键索引根页的 page number 均为 3，而其他的二级索引 page number 为 4。

下面对数据库表空间文件做想相关的解析：

因为主键索引 B+ 树的根页在整个表空间文件中的第 3 个页开始，所以可以算出它在文件中的偏移量：16384*3=49152(16384 为页大小)。

另外根据《InnoDB 存储引擎》中描述在根页的 64 偏移量位置前 2 个字节，保存了 page level 的值。

因此想要的 page level 的值在整个文件中的偏移量为：16384*3+64=49152+64=49216，前 2 个字节中。

接下来用 hexdump 工具，查看表空间文件指定偏移量上的数据：

• linetem 表的 page level 为 2，B+ 树高度为page level+1=3。
• region 表的 page level 为 0，B+ 树高度为 page level+1=1。
• customer 表的 page level 为 2，B+ 树高度为 page level+1=3。

这三张表的数据量如下：

六、总结

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

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

为什么 MySQL 的索引要使用 B+ 树而不是其他树形结构，比如 B 树？因为 B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出)。指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。