MySQL索引结构与原理分析

没有索引的数据库会怎样

• 以下案例改编自真实场景

在一个看似平静的下午，小毛同学刚完成手上项目的代码编写，坐在工位上悠闲的冲了一杯咖啡, 顺便在命令行敲下了git push把写好还没提交的代码推到了GitLab上并触发了代码的编译构建部署，一会代码已经成功部署到了预发环境，他拿起手边的咖啡喝了几口；可是没过一会线上环境的接口一堆报警，并且大量用户请求失败，应用首页直接无法打开。周围所有同事都在紧急排查问题，很快问题被定位到了，原因竟然是小毛在预发环境提交的代码有一个SQL查询没有走索引查询，查询直接扫描数据库全表记录，导致数据库所在机器CPU直接打满，由于预发环境和生产使用同一个数据库，线上所有相关涉及该数据库的服务全部不可用，最终触发了线上故障！悲剧就这样产生了，眼看马上年终考核，小毛同学的年终奖可能就这样没了…

经过如此惨痛的教训，小毛同学下定决心一定要好好学习一下数据库索引的知识

上面的案例反应出了数据库的索引的重要性，尤其在真实的互联网大规模用户的场景下，没有数据库离索引，很多服务随时会出现问题。既然索引这么重要，我们抓紧学习一下数据库索引的知识吧

索引到底是什么

提到索引（Index）大家可能都不陌生，但是如果具体阐述索引的本质，很多同学可能又很难准确的给出定义，本文主要介绍MySQL数据库索引的内涵及其底层实现的数据结构，帮助大家更好的理解数据库索引这个重要的知识。

索引这个词，很容易望文生义，我们可以理解为对要探索内容的指引。记得初中英语课本中最后的附录部分就有一个模块称为Index，这是作者关于这个单词最初的记忆，Index部分具体介绍了书中各个模块所在的具体页码，所以Index这个单词体现出的思想就是帮助读者定位要找到内容的具体位置，按图索骥；但是按照上面描述的，如果不从这个单词角度[1]分析，英语课本中的索引页本质内容是什么呢？本质是书中各个模块的组织！这里的组织不是组织机构（Organization），而是强调数据按照一定的规则进行的编排（Arrange）和排序（Sorting）

在工作中我们的日常对话：

• 程序员A: 这条SQL查询条件有索引吗
• 程序员B: 已经定义了，索引是XXXX

我们日常工作不离嘴边说的索引(Index)其实更多的内涵类似上述英语课本案例中单词角度[1]的用法，而从内涵的角度分析MySQL索引的真正本质是数据的组织和排序 ，那么怎样组织数据才能真正高效快速的查找到数据呢，数据库中的索引可不像英语课本中的索引一样几页纸就可以把数据组织好，动辄千万级别的数据，靠简单的数据组织要查找到可就费劲了。

什么数据结构适合做索引

上面已经讨论了索引的本质就是数据库中数据的组织和排序，那么对数据库某张表所有行(rows)数据记录进行组织和排序，要想保证高效的查询，结构必然要经过一番精心设计。那么到底什么数据结构适合做索引呢？为了方便阐述原理，下面的介绍我们先把每一行数据当做一个节点来看。

先回想一下《数据结构》课程中提到的各种数据结构，有没有哪种结构可以满足上述快速查找的特性呢。线性结构（数组，链表，队列和栈）我们就不考虑了，剩下我们首先想到的是哈希表，因为哈希表可以根据key快速的定位到value所在的位置，时间复杂度是O(1)级别，看似一切是那么完美，那么哈希表真的可以吗？我们来看下面的SQL：

SELECT * FROM `barbecue` WHERE `sku_stock` > 100;

注意到这条SQL是一个范围查询，然而哈希表不支持范围查询，对于经常按照范围进行数据检索的场景，哈希表要遍历全部数据去验证，这肯定不是不合适的。

那二叉查找树（Binary Search Tree）可以吗？他的平均查找时间复杂度是O(logN)级别的，查找起来应该很快吧？但是看下面的二叉树的情况，当插入的数据一直保持递增的时候，二叉树直接变成线性结构，这种情况时间复杂度已经变成O(N)。当数据量增长起来，要频繁的从磁盘读取数据，所以简单的二叉树存储也行不通。

这个时候有同学说，考虑一下平衡树（AVL），保证左右两边树的高度是不是就可以了？下图展示的是在工程中最常使用的一种平衡树——红黑树(Red-Black Tree)

从图上可以看到，树的平衡度的确得到了保证，但是还有重要的一点我们忽视掉了，MySQL单表存储的数量级要达到千万以上，如果采用平衡树，根据树的高度计算公式:

2 ^ h = N

N表示树中节点数量，对应我们数据库行记录数量。当N的值为1000万时，h的大小大约届于(23,24)之间，这意味着要进行23-24次左右的磁盘IO！ 这听起来都很可怕，因为从磁盘读数据到内存还是比较耗时的，遇上机械硬盘响应时间想想就知道有多慢了！这样看AVL平衡树的方案也被否定了。

那么下面我们要解决的问题就是在保证树的平衡的基础上，尽可能的去降低树的高度。根据上面的计算公式，当N确定的情况下，增加底数的值，那么h的大小自然就会降下来。有没有这样的结构呢？还真有，B-树就是这样一种平衡的多(M)路查找树，很适合来处理上述的情况，我们再重点回顾一下B树的定义（阶数为m）：

• 每个节点最多有m-1个关键字（可以存键值对key-value）
• 根节点最少可以只有1个关键字
• 非根节点至少有Math.ceil(m/2)个关键字
• 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
• 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同
• 每个节点都存有索引和数据，也就是对应的key和value

那么我们看一下当表的数据达到千万级别时，B-树的高度范围是怎样的。（之前为了方便分析，我们约定了把每一行当做一个节点来讨论分析，下面为了更符合实际来介绍，我们的描述中恢复行记录的客观存在）

在操作系统中，为了提高IO的效率，操作系统每次从磁盘中取数据的最小单位为页；同样的，在MySQL中，存储在磁盘中的数据，每次从磁盘读到内存同样是按照页为单位进行的，可以通过下面的命令来查看页的大小

SHOW GLOBAL STATUS LIKE 'Innodb_page_size';

返回结果

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+

从上面返回结果可以看到，MySQL的page_size大小是16384(字节) / 1024(字节) = 16KB， 我们计算一下按照B-树的结构来存储数据，树的高度大致是怎样的。既然从磁盘读取到内存是按照页为单位，MySQL实际在磁盘中也是按照页为单位进行数据存储的。我们按照1页对应树中的1个节点，1行记录我们按照占用1K存储空间来计算, 忽略B树中一个节点的Key值和孩子指针所占的空间，那么1个节点可以有大概16K / 1K+ ≈ 16个子节点，根据计算公式

16 ^ h = 10000000

可以得到h的范围为(5,6)之间，从结果看使用B-树来作为数据库索引，已经很大程度减少了树的高度，但是这样意味着磁盘IO还是要进行5-6次。对于互联网应用，在面临高并发的系统访问的时候，频繁的进行磁盘I/O操作，对服务器压力很大；服务端接口的响应时间（Response Time， RT）肯定也会受到影响，尤其是当服务器的磁盘还是机械式时，性能会更差一些。

MySQL为了进一步提升查询的效率，采用了更为科学的B+树作为InnoDB存储引擎的索引结构，B+树是基于B-树基础上进行优化得到的，下面我们看下一棵m阶的B+树与B-树的异同：

InnoDB中使用的B+树与B树在形状上来看主要区别在于：对于一颗m阶的树，
B+树最多可以有m个关键字，而B-树最多可以有m-1个关键字，即B+树的关
键字和子节点的数量是一样多的

除了上述的区别外，B+树与B-树还有以下的不同：

• B+树内部节点（也称为索引节点）不存储行记录数据，只存储关键字（索引）和孩子指针, B+树所有的行记录数据都存储在叶子节点，而B-树用户行数据存储在非叶子节点中
• B+树叶子节点之间通过双向链表连接，叶子结点按照关键字的大小自小而大顺序排列
• B树的节点key不重复，而B+树是冗余数据的

一个块中可以容纳更多的索引项，一是可以降低树的高度
B+树中每个叶子节点都包含指向下一个叶子节点的指针。所有叶子节点都是通过指针连接在一起，而B树不会。 叶子节点之间通过指针来连接，范围扫描将十分简单(优点2)，而对于B树来说，则需要在叶子节点和内部节点不停的往返移动。
B+树还有一个最大的好处，遍历更加高效，方便扫库（优点2），B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了，B+树支持range-query非常方便，而B树不支持。这是数据库选用B+树的最主要原因。（B+树的遍历更加高效，B树需要以中序的方式遍历节点，而B+树只需把所有叶子节点串成链表就可以从头到尾遍历）。