讨论MySQL索引底层实现

MySQL支持多种索引类型，如BTree索引，哈希索引，全文索引等待。

本文主要讨论BTree索引，这也是我们平时用得最多的索引。

索引的本质

MySQL官方对于索引的定义为：索引是帮助MySQL高效获取数据的数据结构。即可以理解为：索引是数据结构。

我们知道，数据库查询是数据库最主要的功能之一，我们都希望查询数据的速度尽可能的快，因此数据库系统的设计者会从查询算法的角度进行优化。最基本的查询算法当然是顺序查找，当然这种时间复杂度为O(n)的算法在数据量很大时显然是糟糕的，于是有了二分查找、二叉树查找等。但是二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树，但是数据本身的组织结构不可能完全满足各种数据结构。所以，在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

B-Tree和B+Tree

目前大部分数据库系统及文件系统都采用B-Tree和B+Tree作为索引结构。

B-Tree

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

1.d为大于1的正整数，称为B-Tree的度；

2.h为一个正整数，称为B-Tree的高度；

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

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

5.所有叶节点具有相同的深度，等于树高h；

6.key和指针相互隔离，节点两端是指针；

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

8.所有节点组成树结构；

9.每个指针要么为null，要么指向另外一个节点；

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

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

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

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

B-Tree上的查找算法如下：

<span style="font-size:14px;"><span style="font-family:Microsoft YaHei;font-size:14px;"><span style="font-size:14px;">BTree_Search(node,key){
		if(node == null){
			return null;
		}
		foreach(node.key){
			if(node.key[i] == key)
				return node.data[i];
			if(node.key[i] > key)
				return BTree_Search(point[i]->node);
		}
		return BTree_Search(point[i+1]->node);
	} 
	data = BTree_Search(root,my_key);</span></span></span>

由于插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree的性质。

B+Tree

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

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

1.每个节点的指针上限为2d而不是2d+1

2.内节点不存储data，只存储Key；叶子节点不存储指针。

由于并不是所有节点都具有相同的域，因此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的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有的数据节点，极大提高了区间查询效率。

为什么使用B-Tree(B+Tree)

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

主存存取原理

目前计算机使用的主存基本上都是随机读写存储器（RAM），现代RAM的结构和存取原理比较复杂，这里抽象出一个十分简单的存取模型来说明RAM的工作原理。

从抽象角度看，主存是一系列的存储单元组成的矩阵，每个存储单元存储固定大小的数据。每个存储单元有唯一的地址，现代主存的编址规则比较复杂，这里将其简化成一个二维地址：通过一个行地址和一个列地址可以唯一定位到一个存储单元。

主存读取过程如下：

当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其他部件读取。

写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。

磁盘读取原理

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

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

局部性原理和磁盘预读

局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。

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

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

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

B-/+Tree索引性能的分析

根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统设计利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要如下技巧：

每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。

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

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

InnoDB索引的实现

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

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

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

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

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