mysql中b树和r树_MySQL系列(九)--InnoDB索引原理

InnoDB在MySQL5.6版本后作为默认存储引擎，也是我们大部分场景要使用的，而InnoDB索引通过B+树实现，叫做B-tree索引。我们默认创建的

索引就是B-tree索引，所以理解B-tree索引的基本原理很重要，面试也是可能被问到的。

我们按照二叉查找树-->B树-->B+树-->B-tree索引-->页的顺序去了解

二叉查找树：

这里关于二分搜索树的原理就不做赘述了，可以参考：Java数据结构和算法(六)--二叉树

二分搜索树有个致命缺点就是数据如果是有序/倒叙插入的话，整棵树就变得极其不平衡，退化成链表，查找效率直接变成O(n)

B树：

B树是为了磁盘等存储设备的数据结构，用来存储数据的话，我们把数据比作键值对的形式key-value。如果对于数据库表中数据来说，key

就是主键，value就是一行中除主键其他列的数据

关于B树的详细特性请参考：从B树、B+树、B*树谈到R 树，这是大佬的文章

定义：

1、根节点至少包含两个孩子

2、每个节点最多包含m个孩子(m >= 2)，m为树的深度

3、除了根节点和叶子节点，其他节点至少有ceil(m/2)个孩子，ceil函数为取上限，例如ceil(1.2)=2，就是小数位多少，都入，不是四舍五入

4、叶子节点的高度相同

如果我们需要寻找key为28的数据，会经历3次磁盘I/O操作过程

PS：

1、我们从上图看到B树和二分搜索树有一点相似的地方，数据是有序的，也就是按照关键字进行排序

2、非叶子节点包含key和value，以及指向其子节点地址的指针

3、叶子节点只有key和value

B+树：

B+树是对B树的优化，如果通过B树来实现索引。InnoDB是按照页进行存储数据的，每一页的存储空间默认16k，如果data比较大的情况下，能

保存的key就少了，磁盘I/O的次数更加，效率就会降低。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每

个节点存储的key值数量，降低B+Tree的高度。

B+树相对于B树有几点不同：

1、非叶子节点只存储键值信息，子树指针与key数量相同(图中，B树key-2，指针-3，B+树key-3，指针也是3)。

2、所有叶子节点包含了全部关键字信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。

3、所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大(或最小)关键字。

对于文件索引来说B+树相对于B树的优点？

1、磁盘IO次数更低

因为非叶子节点不保存其数据，每个节点可以保存的关键字更多了，MySQL每页保存的数据更多，所以减少了磁盘IO

2、查询效率更稳定

每次查找过程就是从根节点到叶子节点的过程，几乎是稳定O(n)。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中

基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

3、B+树更利于对数据库的扫描

由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库范围统计，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样

存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

例如：

搜索id > 10的数据，找到10对应的叶子节点之后，直接通过链指针Q直接往右边统计即可。如果是B树，找到节点10之后，需要回到上层做中序遍历。

PS：

真实数据的B+树是真扁平的，深度很小的。B+树高度一般为1-3层，它就能满足千万级的数据存储。

存储引擎和索引

如果数据库没有索引的话，我们通过explain SQL语句就会发现Using filesort，这样的效率太低了，简直不能忍。对于一张表来说，主键

就是一个B+树，如果查询非主键的话，想要保证效率，你可能需要在非主键上建立单列索引/联合索引，这个索引也是一个独立的B+树。

解决多个B+树同时访问表数据，有两种方式：

1、聚簇索引(密集索引)：概念在之前讲述索引优化的时候，有介绍过，可以参考：MySQL系列(六)--索引优化

举个栗子：有一张表字段分别为id,name,department

行数据和主键索引存储在一起，辅助键索引只存储辅助键和主键，而不保存数据

InnoDB使用的是聚簇索引，行数据保存在叶子节点上。如果通过where id = 5，直接通过主键索引找到对应的关键字，然后返回行数据

如果通过where name = tom，首先通过辅助键索引找到对应id = 5，然后再通过主键索引找到行数据

PS：

1).如果存在主键，主键就是密集索引

2).如果没有主键，表中第一个唯一非空索引为密集索引

3).如果以上都没有，InnoDB生成一个隐藏主键作为密集索引，是一个6字节的列，随着数据的插入自增

所以，InnoDB必须有个密集索引，这是因为非主键索引叶子节点不保存行数据，而是保存着主键值

2、非聚簇索引(稀疏索引)：B+树叶子节点存储的是指向数据的指针

MyISAM使用的为非聚簇索引，主键索引保存了主键，非主键索引保存了非主键，而数据行保存在其他位置，检索的过程都是通过叶子节点内

保存的地址找到对应的数据行。

InnoDB为什么使用聚簇索引呢？

从上面索引过程我们可以看到，对于非主键查询来说，聚簇索引需要经过两次检索，好像效率更低了，那么聚簇索引的优势在哪？

1、行数据和叶子节点保存在一起，会一起被加载到内存，找到叶子节点就可以将数据库返回。

2、辅助索引的叶子节点保存主键的指针，而不使用地址值作为指针，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键

值当作指针会让辅助索引占用更多的空间，InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置(实现中通过16K的Page来定

位，后面会涉及)会随着数据库里数据的修改而发生变化(前面的B+树节点分裂以及Page的分裂)，使用聚簇索引就可以保证不管这个主键B+树的

节点如何变化，辅助索引树都不受影响。(这部分内容来自下面公众号地址)

页page:

InnoDB数据通过page存储，是最小的存储单位，page默认大小为16k，文件系统最小单位块为4k，通过下面参数设置

页可以用来存储数据也可以用来存储key和指针，分别对应非叶子节点和叶子节点。通过非叶子节点的二分查找和指针确定数据在哪一页，进

而查到对应的数据。

一棵B+树可以存放多少条数据？

假设B+树高度为2，一行数据记录大小为1k(实际上很多互联网业务数据就是1k左右)，单个叶子节点(页)中记录数16K/1K=16

B+树存在的数据总量 = 根节点节点指针数量 * 每页保存的数据量

假设主键为bigint，长度为8字节，指针大小在InnoDB源码中为6字节，这样一共14字节，我们一个页中存放16384/14=1170。那么一棵高度为

2的B+树，能存放1170*16=18720条这样的数据。

以此类推，一个高度为3的B+树可以存放：1170*1170*16=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

关于B+树的高度问题，可以参考下面的链接

这里有一点不明白，为啥所有的B-tree索引节点都是一个page，这是这个公式的前提，原作者没有回答，难道MySQL就是这样实现的？

PS：如果想要把InnoDB的索引了解的很清楚，可以把下面几个链接好好看一下

如果对于常见的树结构不了解的话，可以参考：

参考文章：