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

本文的有道云笔记：http://note.youdao.com/noteshare?id=4ff5b7b5dbfda95db012b9bab5c5b0d0

一、 摘要

特别需要说明的是，MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如 BTree索引，哈希索引，全文索引 等等。为了避免混乱，本文将只关注于BTree索引，因为这是平常使用MySQL时主要打交道的索引。

二、数据结构及算法基础

1.索引的本质

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质： 索引是数据结构 。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构（ BTree ），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

图1展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

2.B-Tree和B+Tree

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

B-Tree

B-Tree上查找算法的伪代码：

一个度为d的B-Tree，索引N个key，其树高h的上限为logd((N+1)/2)，检索一个key，其查找节点个数的时间复杂度为O(logdN)。从这点可以看出，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。做这个优化的目的是 为了提高区间访问的性能 。

3.为什么使用B-Tree（B+Tree）

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

1> 主存存取原理

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

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

主存的存取过程如下：

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

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

这里可以看出， 主存存取的时间仅与存取次数呈线性关系 ，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响，例如，先取A0再取A1和先取A0再取D3的时间消耗是一样的。

2> 磁盘存取原理

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

一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）

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

3>局部性原理与磁盘预读

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

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

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

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

先从B-Tree分析，根据B-Tree的定义，可知检索一次最多需要访问h个节点。数据库系统的设计者巧妙利用了磁盘预读原理， 将一个节点的大小设为等于一个页 ，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小（通常不超过3）。综上所述，用B-Tree作为索引结构效率是非常高的。

而 红黑树这种结构，h明显要深的多。 由于逻辑上很近的节点（父子）物理上可能很远， 无法利用局部性， 所以红黑树的I/O渐进复杂度也为O(h)，效率明显比B-Tree差很多。

B+Tree更适合外存索引，原因和内节点出度d有关 。从上面分析可以看到，d越大索引的性能越好，而出度的上限取决于节点内key和data的大小：dmax= floor(pagesize/(keysize+datasize+pointsize)) floor表示向下取整。由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，拥有更好的性能。（其实就是说 B+Tree没有data域，I/O操作一次行可以加载更多的Key域来索引目标数据，明显加快了查询速度 。）

三、MySQL索引的实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

1.MyISAM索引实现

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

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

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

MyISAM的索引方式也叫做 “非聚集” 的，之所以这么称呼是为了与InnoDB的聚集索引区分。

2.InnoDB索引实现

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

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

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

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

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是 辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如：

1.知道了 InnoDB 的索引实现后，就很容易明白为什么 不建议使用过长的字段作为主键 ，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

2 .用非单调的字段作为主键在InnoDB中不是个好主意 ，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而 使用自增字段作为主键则是一个很好的选择。

四、索引使用策略及优化

MySQL的优化主要分为 结构优化 （Scheme optimization）和 查询优化 （Query optimization）。本章讨论的高性能索引策略主要属于结构优化范畴。

MySQL官方文档中提供的示例数据库之一：employees。下图是这个数据库的E-R关系图

MySQL中的索引可以以一定顺序引用多个列，这种索引叫做 联合索引 ，一般的，一个联合索引是一个有序元组<a1, a2, …, an>，其中各个元素均为数据表的一列。另外， 单列索引 可以看成联合索引元素数为1的特例。

1>最左前缀原理

高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和B+Tree中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。

以employees.titles表为例，下面先查看其上都有哪些索引：

情况一：全列匹配。

很明显，当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“IN”匹配）时，索引可以被用到。这里有一点需要注意， 理论上索引对顺序是敏感的 ，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引，例如我们将where中的条件顺序颠倒：

效果是一样的。（即 精确匹配时，查询的条件尽量和表结构的顺序一致，索引对顺序是敏感的 ）

情况二：最左前缀匹配。

当查询条件精确匹配索引的左边连续一个或几个列时，如<emp_no>或<emp_no, title>，索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀。上面的查询从分析结果看用到了PRIMARY索引，但是key_len为4，说明只用到了索引的第一列前缀。 （优先使用最左边的索引）

情况三：查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。（尽量将坑填起来）

此时索引使用情况和情况二相同，因为title未提供，所以查询只用到了索引的第一列，而后面的from_date虽然也在索引中，但是由于title不存在而无法和左前缀连接，因此需要对结果进行扫描过滤from_date（这里由于emp_no唯一，所以不存在扫描）。如果想让from_date也使用索引而不是where过滤，可以 增加一个辅助索引 <emp_no, from_date>，此时上面的查询会使用这个索引。除此之外，还可以使用一种称之为 “隔离列”的优化方法，将emp_no与from_date之间的“坑”填上。

情况四：查询条件没有指定索引第一列。

由于不是最左前缀，索引这样的查询显然用不到索引。

情况五：匹配某列的前缀字符串。

此时可以用到索引，（如果 通配符%不出现在开头，则可以用到索引 ，但根据具体情况不同可能只会用其中一个前缀）

情况六：范围查询。

范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引 。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

情况七：查询条件中含有函数或表达式。

很不幸，如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）。例如：

个查询等价于查询emp_no为10001的函数，但是由于查询条件是一个 表达式，MySQL无法为其使用索引。 看来MySQL还没有智能到自动优化常量表达式的程度， 因此在写查询语句时尽量避免表达式出现在查询中， 而是先手工私下代数运算，转换为无表达式的查询语句。

2>索引选择性与前缀索引

一般两种情况下不建议建索引:

第一种情况是表记录比较少 ，一两千条甚至只有几百条记录的表，没必要建索引，让查询做全表扫描就好了。我个人的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索引，超过2000条可以酌情考虑索引。

第二种情况是索引的选择性较低 。所谓索引的选择性I是指不重复的索引值C与表记录数T的比值：

I= C / T

显然选择性的取值范围为(0, 1]， 选择性越高的索引价值越大 ，这是由B+Tree的性质决定的。

有一种与索引选择性有关的索引优化策略叫做 前缀索引 ，就是 用列的前缀代替整个列作为索引key ，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作 ，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

3>InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。 InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

如果表 使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页 。如下图所示：

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数 据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

因此， 只要可以，请尽量在InnoDB上采用自增字段做主键 。