MySQL（十一）：InnoDB 索引与算法（中篇）

╰半夏微凉° 2023-07-22 09:26 5阅读 0赞

### 文章目录 ###

*   *  1、简述
     *  2、二分查找法
     *   *  2.1、二分查找法在InnoDB存储引擎中的应用
     *  3、哈希表（Hash）
     *   *  3.1、哈希函数的构造
         *  3.2、Hash冲突的解决
         *  3.4、MySQL为什么没有采用hash表作为索引算法
     *  4、二叉查找树(BST)
     *   *  4.1、MySQL为什么没有采用二叉查找树(BST)作为索引算法
     *  5、红黑树
     *   *  5.1、MySQL为什么没有采用红黑树作为索引算法
     *  6、平衡二叉树（AVL）
     *   *  6.1、MySQL为什么没有采用平衡二叉树（AVL）作为索引算法
     *  7、B-Tree
     *   *  7.1、MySQL为什么没有采用B-Tree作为索引算法
     *  8、B+Tree
     *   *  8.1、MySQL为什么采用B+Tree作为索引算法
     *  9、参考文献

## 1、简述 ##

上文我们对InnoDB 索引有了一个基本的认识，本篇主要介绍 InnoDB 存储引擎实现索引时采用的算法及常见数据库索引算法比较。通过本节你将得到以下几个问题的答案。

InnoDB 存储引擎实现索引为什么不用二叉树、红黑树或其它算法？

InnoDB 存储引擎实现索引中 b tree 是什么？b+ tree 和 b- tree有什么区别？

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最为常用有效的索引。B+树索引的构造类似于二叉树，根据键值（Key Value）快速找到数据。

> B+树中的B不是代表二叉（binary），而是代表平衡（balance），因为B+树是从平衡二叉树演化而来的，但是B+树不是二叉树。B+树索引并不能找到指定键值的具体行。B+树索引能找到的只是数据行所在的数据页。然后数据库通过把页读入内存，然后在内存中进行查找，最后得到要查找的数据。

## 2、二分查找法 ##

二分查找法（binary search）也称为折半查找法，用来查找一组有序数组中的某一记录，其基本思想就是：将记录有序排列，在查找过程中先以有序数列的中点位置开始比较，如果要找的元素值小于该中间元素，则将待查询序列缩小为左半部分，反之缩小为右半部分。每查找一次，就会将查找范围缩小一半。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center]

如图，用了3次就找到了17这个数，如果顺序查找需要8次。可见二分查找的效率比顺序查找法效率要高。

### 2.1、二分查找法在InnoDB存储引擎中的应用 ###

在InnoDB存储引擎中，每页 Page Directory 中的槽是按照主键的顺序存放的，对于某一条具体记录的查询是通过对 Page Directory 进行二分查找得到的。

## 3、哈希表（Hash） ##

哈希函数也称为是散列函数，是Hash表的映射函数，它可以把任意长度的输入变换成固定长度的输出，该输出就是哈希值。哈希函数能使对一个数据序列的访问过程变得更加迅速有效，通过哈希函数，数据元素能够被很快的进行定位。

若关键字为k，则其值存放在f(k)的存储位置上。由此，不需比较便可直接取得所查记录。称这个对应关系f为散列函数，按这个思想建立的表为*散列表*。

对不同的关键字可能得到同一散列地址，即k1!=k2，而f(k1)=f(k2)，这种现象称为哈希冲突。具有相同函数值的关键字对该散列函数来说称做同义词。综上所述，根据散列函数f(k)和处理冲突的方法将一组关键字映射到一个有限的连续的地址集（区间）上，并以关键字所在地址集中地址作为记录在表中的存储位置，这种表便称为散列表，这一映射过程称为散列，所得的存储位置称*散列地址*。

若对于关键字集合中的任一个关键字，经散列函数映射到地址集合中任何一个地址的概率是相等的，则称此类散列函数为*均匀散列函数（Uniform Hash function）*，这时就需要使关键字经过散列函数得到一个“随机的地址”，从而减少冲突。

### 3.1、哈希函数的构造 ###

哈希表的构造方法是：假设要存储的数据元素个数为n，设置一个长度为m（m≥n）的连续存储单元，分别以每个数据元素的关键字 Ki(0<= i <=n-1) 为自变量，通过哈希函数 hash(Ki) 把 Ki 映射为内存单元的某个地址 hash(ki)，并将该数据元素存储在该内存单元中。

哈希函数实际上是关键字到内存单元的映射，因此我们希望用哈希函数通过尽量简单的运算，使得通过哈希函数计算出的哈希地址尽量均匀地被映射到一系列的内存单元中。

哈希函数的构造一般有以下几种方法：

*  直接定址法：

取关键字或关键字的某个线性函数值为散列地址。即hash(k)=k或 hash(k)=a \* k+b，其中a,b为常数（这种散列函数叫做自身函数）

![在这里插入图片描述][20200405120323375.png_pic_center]

此方法的优点是不会产生冲突，但缺点是空间复杂度可能会很高，适用于元素较少的情况下。

*  数字分析法：

假设关键字是以r为基的数，并且哈希表中可能出现的关键字都是事先知道的，则可取关键字的若干数位组成哈希地址，但是该方法只适合于所有关键字已知的情况。对于想要设计出更加通用的哈希表并不适用。

*  平方取中法：

取关键字平方后的中间几位为哈希地址。通常在选定哈希函数时不一定能知道关键字的全部情况，取其中的哪几位也不一定合适，而一个数平方后的中间几位数和数的每一位都相关，由此使随机分布的关键字得到的哈希地址也是随机的。取的位数由表长决定。

*  折叠法：

将关键字分割成位数相同的几部分（最后一部分的位数可以不同），然后取这几部分的叠加和（舍去进位）作为哈希地址。

*  随机数法
 *  除留余数法：

取关键字被某个不大于散列表表长m的数p除后所得的余数为散列地址。即

![在这里插入图片描述][20200405120423448.png_pic_center]

不仅可以对关键字直接取模，也可在折叠法、平方取中法等运算之后取模。对p的选择很重要，一般取素数或m，若p选择不好，容易产生冲突。

### 3.2、Hash冲突的解决 ###

在构造哈希表时，存在这样的问题，对于两个不同的关键字，通过我们的哈希函数计算哈希地址时却得到了相同的哈希地址，我们将这种现象称为哈希冲突（如图）:

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 1]

哈希冲突主要与两个因素相关：

1、填装因子，所谓的填装因子是指哈希表中已存入的数据元素个数与哈希地址空间大小的比值，即α=n/m，α越小，冲突的可能性就越小，相反则冲突可能性越大；但是α越小，哈希表的存储空间利用率也就很低，α越大，存储空间的利用率也就越高，为了兼顾哈希冲突和存储空间利用率，通常将α控制在0.6-0.9之间（JDK中取0.75）。

2、哈希函数，如果哈希函数选择合理，就可以使哈希地址尽可能的均匀分布在哈希地址空间上，从而减少冲突的产生，但一个合理的哈希函数需要经过大量的实践才能得到，不过幸运的是前辈们已经通过实践总结了高效的哈希函数，可以参考大牛Lucifer的文章：[数据结构 : Hash Table \[I\]][_ Hash Table _I]

哈希冲突通常是很难避免的，解决哈希冲突有很多种方法，通常分为两大类：

**1、开放定址法**

如果两个数据元素的哈希值相同，则在哈希表中为后插入的数据元素逐个探测存放地址的表，直到查找到一个空单元，把散列地址存放在该空单元。线性探测带来的最大问题就是冲突的堆积，改进的办法有二次方探测法和随机数探测法。开放定址法包括线性探测、二次探测以及双重散列等方法。

**2、链表法**  
将散列到同一个存储位置的所有元素保存在一个链表中。实现时，一种策略是散列表同一位置的所有冲突结果都是用栈存放的，新元素被插入到表的前端还是后端完全取决于怎样方便。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 2]

### 3.4、MySQL为什么没有采用hash表作为索引算法 ###

InnoDB 存储引擎支持的哈希索引是自适应的，InnoDB 存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预生成哈希索引。

从算法时间复杂度分析来看，哈希算法时间复杂度为 O（1），检索速度非常快。比如精确查找 ，哈希索引只需要计算一次就可以获取到对应的数据，检索速度非常快。

因为SQL 查询中除了精确查找还需要范围查找数据，比如以下这个 SQL 语句：

select * from user where id>0 and id<9

如果使用哈希算法实现的索引，范围查找怎么实现呢？一个简单的方法就是把所有数据加载到内存，然后在内存里筛选筛选目标范围内的数据，但是这个范围查找的方法代价太高。

所以，使用哈希算法实现的索引虽然可以做到快速检索数据，但是没办法做数据高效范围查找，因此哈希索引是不适合作为 Mysql 的底层索引的数据结构。

## 4、二叉查找树(BST) ##

二叉查找树（Binary Search Tree），又称二叉排序树（Binary Sort Tree），亦称二叉搜索树。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 3]

图中的数字代表每个节点的键值，在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的顺序输出，如图中中序遍历的结果为：2、3、5、6、7、8。

二叉查找树的时间复杂度是 O(logn)，比如针对上面这个二叉树结构，我们只需要比较3次就可以找到 8 ，相对于直接遍历查询省了一半的时间，从检索效率上看来高效的。

由上图可知道，二叉树的叶子节点都是按序排列的，从左到右依次升序排列，如果我们需要找 id>5 的数据，那我们取出节点为 6 的节点以及其右子树就可以了，范围查找也算是比较容易实现。

但是普通的二叉查找树有个致命缺点：极端情况下会退化为线性链表，二分查找也会退化为遍历查找，时间复杂退化为 O（N），检索性能急剧下降。

### 4.1、MySQL为什么没有采用二叉查找树(BST)作为索引算法 ###

在数据库中，数据表的主键 id，一般默认都是自增的，如果采取二叉树这种数据结构作为索引，就会退化成上图中的线性链表，检索数据时就会出现线性查找的问题。因此，简单的二叉查找树存在不平衡导致的检索性能降低的问题，是不能直接用于实现 Mysql 底层索引的。

## 5、红黑树 ##

红黑树 R-B Tree，全称是Red-Black Tree，它一种特殊的二叉查找树。

普通的二叉查找树在极端情况下可退化成链表，此时的增删查效率都会比较低下。为了避免这种情况，就出现了一些自平衡的查找树，比如 AVL，红黑树等。这些自平衡的查找树通过定义一些性质，将任意节点的左右子树高度差控制在规定范围内，以达到平衡状态。以红黑树为例，红黑树通过如下的性质定义实现自平衡：

*  节点是红色或黑色。
 *  根是黑色。
 *  所有叶子都是黑色（叶子是NIL节点）。
 *  每个红色节点必须有两个黑色的子节点。（从每个叶子到根的所有路径上不能有两个连续的红色节点。）
 *  从任一节点到其每个叶子的所有简单路径都包含相同数目的黑色节点（简称黑高）。

当二叉树处于一个不平衡状态时，红黑树就会自动左旋右旋节点以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为 O（logn）），也就保证了查找效率不会明显减低。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 4]

### 5.1、MySQL为什么没有采用红黑树作为索引算法 ###

观察上图，根据顺序插入 1~19 个节点 生产的红黑树，树的形态趋于右倾。由此可见红黑树并没有完全解决二叉查找树不平衡的问题，尽管这个“右倾”趋势远没有二叉查找树退化为线性链表那么严重，但是数据库中的主键一般都是顺序的，并且都是数十万数百万计的，如果红黑树存在这种问题，对于查找性能而言也是巨大的消耗，数据库是不可能容忍这种隐患存在的。

## 6、平衡二叉树（AVL） ##

AVL 树是最先发明的自平衡二叉查找树。在AVL树中任何节点的两个子树的高度最大差别为1，所以它也被称为高度平衡树。增加和删除可能需要通过一次或多次树旋转来重新平衡这个树。

AVL树本质上本质上是带了平衡功能的二叉查找树（二叉排序树，二叉搜索树），它的特点是：

1.本身首先是一棵二叉搜索树。

2.带有平衡条件：每个结点的左右子树的高度之差的绝对值（平衡因子）最多为1。

因为 AVL 树是个绝对平衡的二叉树，因此他在调整二叉树的形态上消耗的性能会更多。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 5]

AVL 树顺序插入 1~19 个节点，查找 19 需要比较的节点数为 6。从查找效率而言，  
AVL 树查找的速度要高于红黑树的查找效率（AVL 树是 5 次比较，红黑树是 6 次比较）。从树的形态看来，AVL 树不存在红黑树的“右倾”问题。也就是说，大量的顺序插入不会导致查询性能的降低，这从根本上解决了红黑树的问题。

### 6.1、MySQL为什么没有采用平衡二叉树（AVL）作为索引算法 ###

数据库查询数据的瓶颈在于磁盘 IO，如果使用的是 AVL 树，每一个树节点只存储了一个数据，我们一次磁盘 IO 只能取出来一个节点上的数据加载到内存里，那比如查询 19 这个数据我们就要进行磁盘 IO 5次。所以我们设计数据库索引时需要首先考虑怎么尽可能减少磁盘 IO 的次数。

磁盘 IO 有个特点，就是从磁盘读取 1B 数据和 1KB 数据所消耗的时间是基本一样的，我们就可以根据这个思路，可以在一个树节点上尽可能多地存储数据，一次磁盘 IO 就多加载点数据到内存，这就是 B 树，B+树的的设计原理了。

## 7、B-Tree ##

B-Tree 树是一种自平衡搜索树。要了解 B-Tree 的用法，我们必须考虑无法容纳在主内存中的大量数据。当键的数量很大时，将以块形式从磁盘读取数据。与主存储器访问时间相比，磁盘访问时间非常高。使用 B-Tree 的主要思想是减少磁盘访问次数。大多数树操作（搜索，插入，删除等）都需要O（h）磁盘访问，其中h是树的高度。

B-Tree 是一棵胖树。通过将最大可能的键放在B树节点中，来保证 B-Tree 的高度较低。通常，B-Tree 节点大小保持等于磁盘块大小。由于B-Tree的h值较低，因此与平衡的二进制搜索树（例如AVL树，红黑树等）相比，大多数操作的总磁盘访问量显着减少。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 6]

B-tree有以下特性：

1、关键字集合分布在整棵树中；

2、任何一个关键字出现且只出现在一个结点中；

3、搜索有可能在非叶子结点结束；

4、其搜索性能等价于在关键字全集内做一次二分查找；

5、自动层次控制；

由于B-Tree的特性，在B-Tree中按key检索数据的算法，首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少利用率，  
其中，M为设定的非叶子结点最多子树个数，N为关键字总数；

所以B-树的性能等价于二分查找（与M值无关），也就没有B树平衡的问题；  
由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占M/2的结点；删除结点时，需将两个不足M/2的兄弟结点合并。

鉴于B-tree具有良好的定位特性，其常被用于对检索时间要求苛刻的场合，例如：

1、B-tree索引是数据库中存取和查找文件(称为记录或键值)的一种方法。

2、硬盘中的结点也是B-tree结构的。与内存相比，硬盘必须花成倍的时间来存取一个数据元素，这是因为硬盘的机械部件读写数据的速度远远赶不上纯电子媒体的内存。与一个结点两个分支的二元树相比，B-tree利用多个分支（称为子树）的结点，减少获取记录时所经历的结点数，从而达到节省存取时间的目的。

### 7.1、MySQL为什么没有采用B-Tree作为索引算法 ###

由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对B-Tree进行一个分裂、合并、转移等操作以保持B-Tree性质。对于数据库而言，插入、删除操作是很常见的操作，因此如果MySQL采用B-Tree作为索引算法，为保证B-Tree的特性需要付出很大的代价。

## 8、B+Tree ##

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：

（1）B-Tree 的每个NODE都记录了 data，所以不是每次都要搜叶子节点才能拿到 data。  
B+Tree，只有叶子节点有DATA，因此，每次都要搜到叶子节点取 data。

（2）B+树的叶子节点用了一个链表将数据串联起来，便于范围查找。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 7]

### 8.1、MySQL为什么采用B+Tree作为索引算法 ###

通过 B 树和 B+树的对比我们看出，B+树节点存储的是索引，在单个节点存储容量有限的情况下，单节点也能存储大量索引，使得整个 B+树高度降低，减少了磁盘 IO。其次，B+树的叶子节点是真正数据存储的地方，叶子节点用了链表连接起来，这个链表本身就是有序的，在数据范围查找时，是很有效率的。因此 Mysql 的索引用的就是 B+树，B+树在查找效率、范围查找中都有着非常不错的性能。

## 9、参考文献 ##

1.  [《高性能MySQL(第3版)》][MySQL_3]
2.  [《MySQL技术内幕：InnoDB存储引擎(第2版)》][MySQL_InnoDB_2]
3.  [《MySQL源码库》][MySQL]
4.  [《MySQL参考手册》][MySQL 1]
5.  [《MySQL实战45讲》][MySQL_45]
6.  [《数据库内核月报》][Link 1]
7.  https://www.cs.usfca.edu/~galles/visualization/BTree.html
8.  https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
9.  https://baike.baidu.com/item/B-tree/6606402
10. https://en.wikipedia.org/wiki/B-tree?spm=a2c4e.10696291.0.0.7c4b19a4wjK9HV
11. https://en.wikipedia.org/wiki/B+\_tree?spm=a2c4e.10696291.0.0.54bd19a4c9noHZ

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center]: /images/20230528/8042628d05b243bd8417d472e9c966b6.png
[20200405120323375.png_pic_center]: /images/20230528/fd44f2b33e564b7ebe18dd73c56cf82f.png
[20200405120423448.png_pic_center]: /images/20230528/bbe53d1de0ff4c90a457050e7ebb5721.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20230528/d4b68163643f4d2b916ac4fabfca48d9.png
[_ Hash Table _I]: https://www.cnblogs.com/lucifer1982/archive/2008/06/18/1224319.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20230528/d144ca9706d546e797b8c0ce0992c24e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20230528/26703988f70547b3bac16bc32c3e099a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20230528/4a405d3cd036430aaf3e90edc5c8dc88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20230528/24bf3d5b4c624265b7149967e00deac0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 6]: /images/20230528/e682c41787c04cbfa62484596cba4839.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTA2NDcwMzU_size_16_color_FFFFFF_t_70_pic_center 7]: /images/20230528/2c4d48c0361c4664b859951526e56423.png
[MySQL_3]: http://e.dangdang.com/products/1900716972.html
[MySQL_InnoDB_2]: http://e.dangdang.com/products/1900432045.html
[MySQL]: https://dev.mysql.com/doc/dev/mysql-server/latest/
[MySQL 1]: https://dev.mysql.com/doc/refman/8.0/en/
[MySQL_45]: https://time.geekbang.org/column/intro/139
[Link 1]: http://mysql.taobao.org/monthly/2020/02/02/