mysql索引本质是_【最通俗易懂】MySQL索引原理

MySQL底层使用的存储结构是B+树，那为什么选择B+树，我们对不同的存储结构对比分析一下，请看下文，尽量用通俗的话讲清楚。

首先假设我们有这么一张MySQL表user

在没有给name这一列创建索引的情况下，当我们要执行SELECT * FROM user WHERE name='Gavin'时，MySQL需要从第一条数据开始(Richard)，逐行对比。这种情况下查询操作的时间复杂度是O(n)，当数据量达到上百万时，查询操作就会变得很慢，这时就需要采取一些手段进行优化。

时间复杂度，是指执行算法所需要的计算工作量。用O表示，O(n)，表示算法执行n次。

什么是索引

A database index is a data structure that improves the speed of operations in a table. Indexes can be created using one or more columns, providing the basis for both rapid random lookups and efficient ordering of access to records.

数据库索引是一种数据结构，可以提高表中操作的速度。可以使用一列或多列创建索引，这为快速随机查找和对记录访问的有效排序提供了基础。

可以用来优化查询的数据结构有哈希表，二叉树查找，红黑树，AVL树，B树，B+树等等。那么MySQL索引该用哪一种数据结构呢？

哈希表

散列表(Hash table，也叫哈希表)，是根据键(Key)而直接访问在内存储存位置的数据结构。也就是说，它通过计算一个关于键值的函数，将所需查询的数据映射到表中一个位置来访问记录，这加快了查找速度。这个映射函数称做散列函数，存放记录的数组称做散列表。

上述user表的数据，如果按哈希表来存储，结构如下图所示。哈希表的优点是即使数据量非常大，也只需要进行一次hash运算(通过链表解决哈希碰撞问题)，就能快速找到某一条数据，时间复杂度O(1)。

可以用哈希表作为底层存储结构吗？

即使哈希表的查询效率很高，但是MySQL并没有采用哈希表来作为索引的存储结构。原因是业务中经常需要进行一些范围查询，比如SELECT * FROM user where id > 5，哈希表对这种场景的支持比较差。

二叉查找树(BST)

二叉查找树(英语：Binary Search Tree)，也称为二叉搜索树、有序二叉树(ordered binary tree)或排序二叉树(sorted binary tree)，是指一棵空树或者具有下列性质的二叉树：

若任意节点的左子树不空，则左子树上所有节点的值均小于它的根节点的值；

若任意节点的右子树不空，则右子树上所有节点的值均大于或等于它的根节点的值；

任意节点的左、右子树也分别为二叉查找树；

二叉查找树相比于其他数据结构的优势在于查找、插入的时间复杂度较低，为O(log n)

对user的name列建立索引后，数据存储结构如图所示：

最大查询次数为二叉树的最大高度log n。二叉树和哈希表相比，虽然单条数据查询不如哈希表快，但是O(log n)的时间复杂度也可以接受，并且能支持范围查找。

但是BST有一个致命缺点：假设我们的id列是一个自增整型，给一列建立索引时，对应的BST如下图所示：

可以用二叉查找树作为底层存储结构吗？

可以看到，当我们按顺序进行插入数据时，BST退化成了一个单向链表，时间复杂度又成了O(n)

如果BST能够在增加数据的时候，自动调整树的高度，让树能够尽量平衡就好了。接下来介绍的红黑树和AVL树，都是能够进行自动平衡的二叉查找树。

红黑树

红黑树(Red–black tree)是一种自平衡二叉查找树，是在计算机科学中用到的一种数据结构，典型用途是实现关联数组。它在1972年由鲁道夫·贝尔发明，被称为"对称二叉B树"。红黑树的结构复杂，但它的操作有着良好的最坏情况运行时间，并且在实践中高效：它可以在O(log n)时间内完成查找、插入和删除。

用红黑树存储索引，插入数据时会自动旋转调整树的高度，结构如下：

AVL树

在计算机科学中，AVL树是最早被发明的自平衡二叉查找树。在AVL树中，任一节点对应的两棵子树的最大高度差为1，因此它也被称为高度平衡树。查找、插入和删除在平均和最坏情况下的时间复杂度都是O(log n)。增加和删除元素的操作则可能需要借由一次或多次树旋转，以实现树的重新平衡。

AVL树比红黑树更加平衡，用AVL树存储索引，查询效率会比红黑树稍高，但插入效率比红黑树差。结构如下：

可以用AVL树或是红黑树作为底层存储结构吗？

如果我们要在上述AVL树中，查找11这个元素，那么就需要经过4次查找：4 -> 8 -> 10 -> 11

在MySQL中，每查找一个节点，都需要进行一次IO操作，查找11这个元素就需要进行4次IO操作。当数据量达到10k时，查找一个元素可能需要进行14次IO，这显示是不能接受的。

根据实际情况，在内存中操作数据的耗时与IO耗时相比起来，几乎可以忽略不计。如果每一个树结点，都存储多个元素的话，就可以降低树的高度，减少IO次数了。这就是B树/B+树的原理。

B树

在计算机科学中，B树(英语：B-tree)是一种自平衡的树，能够保持数据有序。这种数据结构能够让查找数据、顺序访问、插入数据及删除的动作，都在对数时间内完成。

B树，概括来说是一个一般化的二叉查找树(binary search tree)一个节点可以拥有2个以上的子节点。与自平衡二叉查找树不同，B树适用于读写相对大的数据块的存储系统，例如磁盘。B树减少定位记录时所经历的中间过程，从而加快存取速度。B树这种数据结构可以用来描述外部存储。这种数据结构常被应用在数据库和文件系统的实现上。

B 树就是常说的“B- 树”，又名平衡多路(即不止两个子树)查找树，它和平衡二叉树的不同有这么几点：

平衡二叉树节点最多有两个子树，而 B 树每个节点可以有多个子树，M 阶 B 树表示该树每个节点最多有 M 个子树

平衡二叉树每个节点只有一个数据和两个指向孩子的指针，而 B 树每个中间节点有 k-1 个关键字和 k 个子树(k 介于阶数 M 和 M/2 之间，M/2 向上取整)

B 树的所有叶子节点都在同一层，并且叶子节点只有关键字，指向孩子的指针为 null

B 树的节点数据大小也是按照左小右大

示例：将user表中的11条数据全部存在一个7阶B树中(每个节点最多存储6个数据)

规定m阶B-tree中，根结点至少有2个子结点。

根结点中关键字的个数为1~m-1，比节点数目少一个；

非根结点至少有[m/2](向上取整)个子结点，相应的，关键字个数为[m/2]-1~m-1

关键字：树的索引标识。

可以看到查找11这个元素，只需要进行2次IO，比之前的AVL树减少了2次。

那么用B树作为底层存储结构吗？

B树中的每个元素，除了存放关键字外，还会存放额外的数据(比如对应数据记录的地址或者数据内容)。而实际情况是，每个节点的存储空间是有上限的，在内部节点中存储这些数据，会导致内部节点能够存储的元素数据变小。阶数变小就会导致树变高，IO次数增加，比如将上述数据存在3阶B树中，查找11就需要3次IO：

并且针对B树进行范围查找，效率并不太高。比如对上图B树，执行SELECT * FROM user WHERE id > 4，需要进行7次IO。

针对B树的一些局限，衍生了一些变种：B+树、B*树，而MySQL底层使用的存储结构，就是B+树

B+树

B+ 树是一种树数据结构，通常用于数据库和操作系统的文件系统中。B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入，这与二叉树恰好相反。

B+树和B树类似，但是多了一些规则：

非叶子结点的子树指针个数与关键字(节点中的元素个数)个数相同

非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树(B-树是开区间)

所有叶子结点都有一个指针，连接成一个链表

所有关键字都在叶子结点出现

只有叶子节点存储值，内部节点只存储关键字

通过B+树来存储以上数据的结构如下：

B+树的优点：

内部节点只保存关键字，提高了阶数，加快查找时间。

叶子节点中包含所有数据，所有叶子节点形成一个有序链表，让范围查找更高效。

B树相对于B+树的优点是，如果经常访问的数据离根节点很近，而B树的非叶子节点本身存有关键字其数据的地址，这种情况下数据检索会要比B+树快。

基于B+树的那么多优点，MySQL最终将其作为了索引的底层存储结构。

知乎账号：鹅厂程序小哥

CSDN账号：鹅厂程序小哥