MySQL索引背后的数据结构及算法原理

数据结构及算法基础

MySQL官方对索引的的定义是：索引(Index)是帮助MySQL高效获取数据的数据结构，提取句子的主干索引(Index)是一种数据结构。

数据查询是数据库的主要功能之一，我们都希望数据库在查询数据时能够更快的返回数据。因此数据库设计者会从查询算法的角度进行优化。

试想一个生活中的实例：

我们是搬家公司

1、如果物件很少的情况下，我们都不需要打包、不需要分类。直接往车上一扔，都不会妨碍我们后期整理物件归位的速度。因为物件少，我们能一眼看到相应的物件在什么地方。

2、如果物件相对较多，我们需要将相同类型的物件打包存放在一起，然后贴上标签，到时候我们只需要按照标签寻找，速度也能很快。

3、如果我们是个一栋楼或者一个小区搬家的时候，问题就有点棘手了，虽然我们给每个用户的物件都贴上了标签，但是当某个用户来找我们要包裹的时候 ，我们只能遍历所有的包裹。这样效率将会变得特别低。

所以，我们在给用的包裹贴标签的时候，可以单独做一份记录文件。记录每一个用户的包裹存放在哪个位置。当用户来找我们拿包裹的时候，我们只需要看记录文件上用户的包裹存放信息，然后去响应的位置上把包裹给用户就行了。

在这个例子中，记录文件就是索引(Index)。

在上面情况1中，我们去遍历所有的包裹，其实在计算机里面就是最基础顺序查找法，顺序查找法，复杂度为0(n)，当数据量很大时，这种算法是很糟糕的。因此有前辈提供了很有更优秀的算法：二分查找法、二叉树查找等等。但是你稍微分析下来就会发现，每一种算法都针对一种特定的数据结构(也就包裹如何存放)。二分查找法要求数据有序，二叉树查找只能用于二叉查找树上。但是数据的本身的组织结构不可能完全满足各种数据结构，所以在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据以某种方式指向数据。这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

上图展示了一种可能的索引方式，左边是数据表，一共7条数据，最左边的数据记录的物理地址(逻辑上相邻的数据在磁盘上并不一定是相连的)。为了加快Col2的查找速度，可以维护一个右边所示的二叉树，每个节点包含索引键值和一个指向数据存放的物理地址的指针。这样就可以运用二叉查找树获取数据。

B-Tree和B+Tree

目前大多数数据库系统和文件系统都是采用B-Tree和其变种B+Tree作为索引结构。

B-Tree

为了描述B-Tree，首先定义一条数据记录为一个二元组[key,data]，key为数据记录的键值，对于不同的数据记录，key是互不相同的；data为数据记录除key外的数据。那么B+Tree是满足下列条件的数据结构：

d为大于1的一个整数，称为B-Tree的度。

h为一个正整数，称为B-tree的高度。

每个非叶子节点由n-1个key和n个指针组成，其中d<=n<=2d。

每个叶子节点最少包含一个key和两个指针，最多包含2d-1个key和2d个指针，叶节点的指针均为null 。

所有叶子结点具有相同的深度，等于树的高度h。

key和指针互相间隔，节点的两端是指针。

一个节点中的key从左到右非递减排列。

所有节点组成树结构。

每个指针要么为null，要不指向另外一个节点。

如果某个指针在节点node最左边且不为null，则其指向节点的所有key小于v(key1)v(key1)，其中v(key1)v(key1)为node的第一个key的值。

如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于v(keym)v(keym)，其中v(keym)v(keym)为node的最后一个key的值。

如果某个指针在节点node的左右相邻key分别是keyikeyi和keyi+1keyi+1且不为null，则其指向节点的所有key小于v(keyi+1)v(keyi+1)且大于v(keyi)v(keyi)。

下图展示的是B-Tree示意图

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

在插入新的数据或者删除数据时会破坏B-Tree的特性，这些操作都需对树进行分裂、合并、转移等操作来保持B-Tree的特性。

B+Tree

B+Tree是B-Tree的变种，B-Tree树有很多的变种，B+Tree是他最常见的一种变种。

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

B+Tree的非叶子结点，也就是内节点不存储数据。叶子结点不存储指针。

下图展示的B+Tree示意图

 

由于并不是所有的节点都具有相同的域，因此B+Tree中页节点和非叶子结点的大小一般不同，这点与B-tree不同，虽然在B-Tree中不同节点存放的指针和key 数量可能不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree对每个节点申请的大小是一致。

一般来说B+Tree比B-Tree更适合做外存储索引，这和外存储器原理和计算机存取原理有关系。

带有顺序访问指针的B+Tree

一般在数据库系统或者文件系统中使用的B+Tree结构，都在经典的B+Tree结构上做了优化，增加了带顺序访问的指针。

如上图所示，B+Tree在每个叶子结点增加了一个指向相邻叶子结点的指针，从而形成了带有顺序访问指针的B+Tree,做这个的目的是提高了区间访问的性能，例如上图如果需要查找key为18到49的数据，只需要找到key为18的值，然后顺着节点和指针遍历就可以一次性取出所有的数据节点，极大的提高了区间访问的性能。

为什么使用B-Tree或者B+Tree

上面我们说过了，二叉树等也可以用来实现索引，但是文件系统和数据库系统普遍采用B+Tree或者B-Tree来作为索引结构。我们可以从计算机组成原理来试图说明原因。

一般来说，索引本身也很大，不可能全部存放在内存中。因此索引一般都是以索引文件的形式存放在磁盘上，这样的话，查找索引的话，就会产生磁盘 I/O 的消耗，相对于内存存储，磁盘 I/O 的存储往往要高好几个数量级。所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘 I/O操作次数的渐进复杂度。换句话说，索引结构就是要尽可能的减少在查找数据磁盘 I/O的存取次数。

磁盘存取原理

索引一般以文件的形式存储在磁盘上，索引检索需要磁盘的 I/O 操作，与主存不同，磁盘 I/O 存在机械运动损耗，因此磁盘 I/O 的时间消耗是非常巨大的。

下图展示磁盘结构示意图

一个磁盘由大小相同且同轴的圆形盘面组成，磁盘可以转动。在磁盘的一侧由磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容，磁头不能转动，但是可以沿着磁盘半径方向运动，每个磁头同一时刻也必须是同轴的，如果从正上方往下看(俯视图)，所有磁头任何时刻都是重叠的。

如上图所示，盘面被划分成一系列同心环，圆心是盘面的中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小小的段，每个段叫做一个扇区。每个扇区是磁盘最小的存储单元。为了方便起见，我们假设磁盘只有一个盘片和一个磁头。

当需要从磁盘读取数据时，系统会将数据的逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在那个磁道那个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区的上方。为了实现这一点，磁头需要移动到相应的磁道，这个过程叫寻道，所耗费时间叫寻道时间，然后磁盘旋转将目标扇区放到磁头下面，这个时间叫做旋转时间。

局部性原理和磁盘预读

由于存储介质的特性，磁盘本身存取就比主存存取慢得多，在加上机械运动损耗，磁盘的存取速度往往是主存存取速度的几百分之一，因此为了提高效率，要尽可能的减少磁盘 I/O 。为了达到这个目的，磁盘往往不是按需读取，而是每次都会预读，即使只需一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用。

程序运行期间所需要的数据通常会比较集中。

由于磁盘顺序读取的效率很高(只需要很短的旋转时间，不需要寻道时间)，因此对于具有局部预读性的程序来说，预读往往能提高 I/O 效率。

预读的长度一般为页的整数倍，页是计算机存储器的逻辑块，硬件及操作系统往往将主存和硬件存储器分割为大小相等的连续的块，每个存储块称为一页(在许多操作系统中，页的大小通常为4k)，主存和磁盘以页为大小交换数据。当程序要读取的数据不再主存中时，会触发一个缺页异常，此时系统向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后顺序读取一页或者几页数据放到主存中。然后返回，程序继续运行。

MySQL索引实现

在MySQL中索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式不一样，我们接下来主要讨论InnoDB 和MyISAM 两种存储引擎的实现方式。

MyISAM实现方式

MyISAM引擎使用B+Tree作为索引结构，页节点的data域存放的是数据记录的地址，如下图所示：

这里设表一共有三列，我们假设Col1为主键，上图就是MyISAM存储引擎的主索引示意图。可以看出MyISAM的主索引文件仅仅保存数据记录的地址。MyISAM 的主索引和辅助索引没有任何区别，只是主索引要求Key唯一，而辅助索引可以重复。下图是我们在Col2上面建立的辅助索引

M页ISAM的索引方式是“非聚集”的，这么叫是为了与InnoDB的聚集索引区分开。

InnoDB索引实现

InnoDB也使用B+Tree结构作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别就是InnoDB数据文件本身就是索引文件。从上文知道，MyISAM的索引文件和数据文件是区分开来的，索引文件仅保存数据记录的物理地址，而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的页节点的data域保存了完整的数据记录。这个索引的key就是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB的主索引(同时也是数据文件)，可以看到节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB 的数据文件本身要按主键聚集。所以InnoDB 必须要求表有主键，如果没有显示的指定主键，MySQL系统会自动的选择一个可以唯一标识数据记录的列为主键。如果没有该列，则MySQL系统会为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

第二个与MyISAM索引不同的是，InnoDB 辅助索引存储的相应记录的主键而不是数据地址。也就是说InnoDB的辅助索引都引用主键作为data域，例如，图11为定义在Col3上的一个辅助索引：

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。