你真的了解索引吗（上）？|MySQL系列（6）

作者 | 小汪哥写代码
来源 | 小汪哥(ID：xwgcoding)

前言

你知道索引长什么样吗?当磁盘剩余空间较小时，为什么我们加了索引会导致磁盘空间不足?为什么多加了几个索引，mysql 插入和删除的效率反而下降了呢?带着这些问题，我们开始今天的话题。

什么是索引?

索引(Index)是帮助数据库系统高效获取数据的数据结构，数据库索引本质上是以增加额外的写操作与用于维护索引数据结构的存储空间为代价的用于提升数据库中数据检索效率的数据结构。

总结一下就是，索引就是数据结构!!一种为了提升检索效率的数据结构。

常见的数据库的索引有：hash 表、B+树等。

那索引物理上是怎么表现的呢?其实我们上一篇《mysql的数据到底是怎么存的(下)|mysql系列(5)》中讲到：MySQL 的存储结构分为 5 级：表空间、段、簇、页、行。创建一个索引就会创建两个段：一个数据段、一个索引段。

InnDB的索引为什么是B+树?

二叉树的查找效率也非常高，比如平衡二叉树，我们在数据结构和算法中经常会用到二叉树的思想来解决问题，我们都知道mysql用的是B+树，那么为什么InnDB 不用二叉树呢?

因为平衡二叉树这种结构在内存里面的查找效率是比较快的。但是

索引是存在于索引文件中，是存在于磁盘中的。因为索引通常是很大的，因此无法一次将全部索引加载到内存当中，因此每次只能从磁盘中读取一个磁盘页的数据到内存中。而这个磁盘的读取的速度较内存中的读取速度而言是差了好几个级别。

因为二叉树序列化到磁盘的时候，其物理实现是数组，具体可以参考

《一文搞定二叉树---由二叉树到贪心算法》https://juejin.cn/post/6964730459800633375。

然后由于在逻辑结构上相近的节点在物理结构上可能会差很远。因此，每次读取的磁盘页的数据中有许多是用不上的。因此，查找过程中要进行许多次的磁盘读取操作。

二叉树做索引有什么问题?

二叉树应用在磁盘这类的搜索那么会有以下几个问题:

• 数据非常大时, 树高度会很高, 造成磁盘io扫描次数很高
• 一个节点只是存放一个数据, 数据与数据之间在物理内存相隔甚远, 磁盘扫描需要频繁转动
• 需要频繁的从磁盘读数据到内存中, 即使操作系统有预读, 但是带出来的大多是暂时用不上的无用数据, 造成浪费

这里我们复习一下几个知识点：

磁盘IO时间：

• 磁盘IO时间 = 寻道 + 磁盘旋转 + 数据传输时间

机械硬盘的连续读写和随机读写的性能差异：

• 顺序访问：内存访问速度是硬盘访问速度的6~7倍(kafka的特点，以后有机会的话再讲一讲)
• 随机访问：内存访问速度就要比硬盘访问速度快上10万倍以上随机读写时，磁头需要不停的移动，时间都浪费在了磁头寻址上。而在实际的磁盘存储里，是很少顺序存储的，因为这样的维护成本会很高。

局部性原理与磁盘预读：

• 由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率。

总结一句话：由于磁盘的存储及访问的特性及二叉树的物理存储方式导致二叉树在磁盘上的查询速度很慢，不适合做索引。

B+树的引入

鉴于以上几个问题, 有以下几点需要优化的:

• 减少磁盘io扫描次数。
• 减少磁盘io转动频率。
• 利用好操作系统的预读, 局部性原理。

B树是为了充分利用磁盘预读功能来而创建的一种数据结构，也就是说B树就是为了作为索引才被发明出来的的。

B+ 树的特点

• b+树拥有b树的所有优点, 并且b+树的非叶子节点不存放数据, 而是单单存放索引, 只有在叶子节点存放索引+数据, 并且叶子节点通过前后指针构成双向链表的结构, 因此通过树结构定位到索引后, 可以通叶子节点的链表指针很快的直接遍历得到范围查询的结果 这样更好的利用了顺序io比随机io性能更高的优化. 这个特点是b树不具备的.
• b+树是innodb的底层数据结构, 通过N叉树, 节点页, 叶子节点链表串起来, 避免了过多的磁盘io扫描, 转动次数, 并且利用了操作系统的预读和局部性原理, 更支持了范围查询的功能.

B+ 树的优点

• B树/B+树与红黑树、平衡二叉树等二叉树相比，最大的优势在于树高更小。
• Innodb中每个节点使用一个页(page)，页的大小为16KB，其中元数据只占大约128字节左右(包括文件管理头信息、页面头信息等等)，大多数空间都用来存储数据。
• 对于一颗3层B+树，第一层(根节点)有1个页面，可以存储1000条记录;第二层有1000个页面，可以存储1000*1000条记录;第三层(叶节点)有1000*1000个页面，每个页面可以存储100条记录，因此可以存储1000*1000*100条记录，即1亿条。而对于二叉树，存储1亿条记录则需要26层左右。

总结一下

现在我们可以回答开头的几个问题了。

InnoDB 就是一个B+树的数据结构。

每加一个索引就会生成两个文件，所以当服务器磁盘空间少时加了索引会导致磁盘空间不足。

索引多的话，每次添加删除数据都会维护多个文件，效率反而降低。