一、B+树介绍

mysql索引b树 mysql索引b+树原理_mysql索引b树


如上图所示，即为一个简化的3阶B+树。所谓3阶，指的是每个非叶子节点最多可以有3个子节点。图中数字代表关键字（可以理解为数据库中的字段的值），P1、P2、P3代表指针。

一棵m阶B+树的特点如下：

  • 每个节点最多可以有m个关键字；
  • 每个关键字左边的子节点关键字都比自己小，右边的子节点关键字都等于自己或比自己大。
  • 所有叶子节点组成链表，按关键字大小排序。（注意：在mysql中，该链表为双向指针链表。）
  • 所有叶子节点都在同一层。
  • 所有的数据只保存在叶子节点，非叶子节点只保存关键字和指针。
  • 初始关键字个数是cei(m/2)，即最小个数。

二、mysql中索引如何使用B+树

在mysql中，B+树存在两种数据库引擎，Innodb（5.5版本之后）和Myisam。两种数据库引擎的B+树索引基本一致，主要区别在于，叶子节点上存储数据的不同。以下表为例，分别在id和name两个列建立索引。

mysql索引b树 mysql索引b+树原理_b+树_02

1、Innodb引擎中的B+树索引

  • 在Innodb引擎中，索引可以分为聚集索引（主键索引）和普通索引。由于聚集索引是跟数据存储在一起的，所以Innodb的表一定会有聚集索引，如果本身没有主键，Innodb也会创建隐藏的字段作为聚集索引。
  • 聚集索引只能有一个，而普通索引可以有多个。
  • B+树索引可以分为聚集索引和辅助索引（区别于hash索引）。如上所述，聚集索引的叶子节点中存放表的行记录数据，而辅助索引的叶子节点中存放的只是对应数据的聚集索引键（主键）。

1）聚集索引B+树结构

mysql索引b树 mysql索引b+树原理_数据库_03

  • 如上图所示，则为表中id列的4阶B+树索引结构图，即聚集索引结构图（id列是主键列）。
  • 上图中，节点的关键字即为ID列的值，data代表的是对应行整行的数据信息，P1等为指针。
  • 所有节点内的关键字都是按顺序排列，各叶子节点之间也是按顺序排列。
  • 叶子节点按从小到大的顺序，由双向指针两两相连。

2）索引增加关键字的顺序

假如现在我们往表中增加一条id=17，name=q的数据，则id列的索引也需要同时增加关键字17以及相关的行数据信息。其添加过程如下所示：

  • 从根节点按二分查法查找17应该添加到哪个叶子节点，可以判断出17应该添加到P4指针对应的叶子节点中，如下图所示。
  • 假设每个叶子节点最多只能存放4个数据（实际中存储大小后续会讲），那么此时17就无法放入P4所对应的叶子节点，只能新加一个叶子节点，并在根节点增加一个p5指针指向新加的叶子节点，如下图所示。

3）辅助索引B+树结构

以name列这类非主键列创建B+树索引，则为辅助索引。假设上面图为id列的聚集索引，则name列的索引如下图索引，其中叶子节点数据中存放的为ID列索引值而非整个数据。

mysql索引b树 mysql索引b+树原理_mysql索引b树_04


如上图所示为以name列建立辅助索引示例，使用辅助索引查找时，实际上是先通过辅助索引树找到对应聚集索引的索引值，再通过聚集索引再查找一次数据。

2、Myisam引擎中的B+树索引

在Myisam引擎中，不存在聚集索引的说法，所以它的表不要求存在主键。其主索引和辅助索引的B+树的存储方式一样，与Innodb引擎的聚集索引相似，唯一区别就是聚集索引的叶子节点存放的是数据，而它的叶子节点存放的是数据存储所在的地址。

三、Innodb引擎中B+树查询过程

1、系统的数据读取

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，同一个磁盘块中的数据会被一次性读取出来。

2、Innodb引擎中的页

在Innodb引擎中，是以页作为磁盘管理的最小单位。每个页默认的大小为16KB（可通过参数innodb_page_size将页的大小设置为4KB、8KB、16KB）。
由于系统中的磁盘块一般都没有那么大，所以Innodb引擎每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。前面B+树图中的每一个节点都可以理解为是一个页，每一个节点中存储的信息即是每一个页中存储的信息。

3、Innodb引擎通过B+树查找数据

mysql索引b树 mysql索引b+树原理_数据库_05


如上所说，在Innodb引擎中是以页为单位进行查询的，即以节点为单位进行数据获取查询。以上图为例，假设要获取id=11的数据，则需要如下进行查询：

  • 直接从内存获取根节点页（Innodb直接将根节点页常驻内存，不需要从磁盘获取），判断11>9且11<13，获取到指针P3所指向的页所在磁盘位置。
  • 通过P3从磁盘读取到叶子节点页，按顺序获取到id=11的数据。
四、MYSQL使用B+树结构做索引的原因
  • 1、减少磁盘读取次数，提高索引效率。如上所说，除了根节点页常驻内存外，其他索引都是在磁盘中的。如果使用平衡二叉树、B树等结构，则每个节点页都会存入数据，导致同一个页能存储的关键字比B+树少，查询时不断读取磁盘获取数据，效率低。
  • 2、使用范围查找效率高。由于B+树的所有数据都只存在子节点中，子节点间可以直接互相访问，所以进行范围查找时，只需要通过树查找找到第一个数据后便可获取所有数据，而其他树结构只能不断通过树查找获取数据。