mysql定义数组_面试官问：MySQL索引的本质？为什么是B+Tree

前言

MySQL系列文章：

• MySQL索引底层数据结构与算法
• MySQL性能优化原理-前篇
• MySQL性能优化-实践篇1
• MySQL性能优化-实践篇2
• MySQL锁与事物隔离级别

索引的本质

索引是帮助MySQL高效获取数据的排好序的数据结构

索引数据结构，主要包含以下几类，我们来对比下

• 二叉树
• 平衡二叉树
• 红黑树
• Hash表
• B-Tree

二叉树

定义：每个节点最多有两个子树，左子树比父节点小，右子树比父节点大。

缺点：会出现极端情况导致整棵树只有左子树或只有右子树。

平衡二叉树（AVL Tree）

定义：平衡二叉树又称AVL树，是一种特殊的二叉查找树，其左右子数都是平衡二叉树，且左右子树高度差的绝对值不超过 1 。

缺点：AVL树是高度平衡的，频繁的插入和删除，会引起频繁的 rebalance​，导致效率下降。

更多内容详见《AVL 树和红黑树的实现和特性》

红黑树

定义：

• 性质1. 节点是红色或黑色。
• 性质2. 根节点是黑色。
• 性质3 每个红色节点的两个子节点都是黑色。(从每个叶子到根的所有路径上不能有两个连续的红色节点)
• 性质4. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。

缺点：数据量大会导致树层数比较多，这样就会造成查找数据慢。

Hash数据结构

定义：散列表（Hash table，也叫哈希表），是根据关键码值(Key value)而直接进行访问的数据结构。也就是说，它通过把关键码值映射到表中一个位置来访问记录，以加快查找的速度。这个映射函数叫做散列函数，存放记录的数组叫做散列表。 对目标值进行hash运算得到hash值和数据磁盘指针地址保存到hash表，这样就达到快速定位数据位置。

缺点：精确查找十分快速，但范围查找就碰壁了。

BTree

定义：

• 一个节点可以存储多个数据，这样可以避免黑红树的缺点，树的层数很变小；
• 叶节点具有相同的深度，叶节点的指针为空；
• 所有索引元素不重复；
• 节点中的数据索引从左到右递增排列。

缺点：节点里面数组数据：每个数据的结构=索引数据+数据记录（即叶子节点存储键值和数据记录）。

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

1. 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
2. 比较关键字29在区间（17,35），找到磁盘块1的指针P2。
3. 根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
4. 比较关键字29在区间（26,30），找到磁盘块3的指针P2。
5. 根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
6. 在磁盘块8中的关键字列表中找到关键字29。

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B+Tree

定义：B+Tree是在B-Tree基础上的一种优化。节点里面数组数据：每个数据只存储键信息，这样不存数据可以腾出空间放更多的键信息，让树层数越小

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示：

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。

可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算：

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为 INT（占用4个字节）或 BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值。（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和 辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

查看mysql文件页大小（16K）：SHOW GLOBAL STATUS like 'Innodb_page_size’;