MySQL索引背后的数据结构与算法原理学习记录

MySQL索引背后的数据结构与算法原理学习记录

参考文章：MySQL索引背后的数据结构与算法原理

文章主要分三个部分

第一部分：主要从数据结构及算法理论层面讨论MySQL数据库索引的数理基础。

第二部分：结合MySQL数据库中MyISAM和InnoDB数据库存储引擎中索引的架构实现讨论聚集索引、非聚集索引及覆盖索引等话题。

第三部分：根据上面的理论基础，讨论MySQL中高性能使用索引的策略。

数据结构与算法基础

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

数据库查询是数据库最主要的功能之一，为了使查询的速度尽可能的快，数据库的设计者会从查询算法的角度进行优化。最基本的查询算法有顺序查询（linear search）、二分查找（binary search）、二叉树查找（binary tree）等，经过分析发现，每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索的数据有效，而二叉查找只能应用于二叉查找树上，对于顺序查找算法，这种时间复杂度为O(n)的算法在数据量很大时效率是非常低的。数据本身的组织结构不可能完全满足各种数据结构，所以在数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引。

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(key_1))，其中(v(key_1))为node的第一个key的值。
• 如果某个指针在节点node最右边且不为null，则其指向节点的所有key大于(v(key_m))，其中(v(key_m))为node的最后一个key的值。
• 如果某个指针在节点node的左右相邻key分别是(key_i)和(key_{i+1})且不为null，则其指向节点的所有key小于(v(key_{i+1}))且大于(v(key_i))。

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

B+Tree

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。与B-Tree相比，B+Tree有一下不同点：

• 每个节点的指针上限为2d而不是2d+1。
• 内节点不存储data，只存储key；叶子节点不存储指针。

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

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

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

在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。做这个优化的目的是为了提高区间访问的性能，例如图中如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

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

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。

主存存取原理

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

图5

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

主存的存取过程如下：

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

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

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

磁盘存取原理

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

图6是磁盘的整体结构示意图。

图6

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

图7是磁盘结构的示意图。

图7

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

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

局部性原理与磁盘预读

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

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

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

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

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

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

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

• 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上页存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node秩序一次I/O。
• B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），一般实际应用中，出度d时非常大的数字，通常超过100，因此h非常小（通常不超过3）。

所以用B-Tree作为索引结构效率是非常高的。

MySQL的索引实现

MyISAM索引的实现

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

这里相当于表有三列，假设用col1作为主键，那么上图是一个MyISAM表的主键索引（Primary key）示意。从图中可以看出，MyISAM的索引文件只保存了数据记录的地址。在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果在col2上建立一个辅助索引，则此索引机构如下图：

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

MyISAM的索引方式叫做“非聚集索引”的，而InnoDB的索引方式被称为“聚集索引”。

InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，但是具体的实现方式和MyISAM并不相同。

1. 第一点区别是InnoDB的数据文件本身就是索引文件。我们知道MyISAM索引文件和数据文件是分离的，索引文件只保存数据记录的地址。而在InnoDB中，表数据文件本身就是按照B+Tree坐直的一个索引结构，在这颗B+Tree的叶子节点的data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是索引文件。

   

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

2. 与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值，而不是地址。InnoDB的所有辅助索引都引用主键作为data域。如图在col3上定义一个辅助索引。

   

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

再次理解

为什么不建议使用过长的字段作为主键：因为所有辅助搜因都引用主索引，过长的主索引会令辅助索引变得过大。

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

索引用策略及优化

MySQL的优化主要分为结构优化和查询优化，接下来讨论的高性能索引策略主要属于结构优化范畴。

最左前缀原理与相关优化

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

1. 全列匹配
   • 当按照索引中所有列进行精确匹配（这里精确匹配指“=”或“in”匹配）时，索引可以被用到。
   • 索引对顺序时敏感的，但是MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。
2. 最左前缀匹配
   • 当查询条件精确匹配索引的左边连续一个或几个列时，索引可以被用到，但只能被用到一部分，即条件所组成的最左前缀。
3. 查询条件用到了索引中列的精确匹配，但是中间某个条件未提供。
4. 查询条件没有指定索引第一列
   • 由于不是最左前缀，所以这样的查询用不到索引。
5. 匹配某列的前缀字符串
   • 使用like匹配前缀字符串当%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀。
6. 范围查询
   • 范围查询可以用到索引（必须时最左前缀），但是范围列后面的列无法用到索引。
   • 如果查询条件中有两个范围列则无法全用到索引。索引最多用于一个范围列。
7. 查询条件中含有函数或表达式
   • 如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引。

索引选择性与前缀索引

索引虽然可以加快查询速度，但并不是只要查询语句需要就建上索引。因为索引虽然加快了查询速度，但索引也有代价：索引文件本身就要消耗存储空间，同时索引会加重插入、删除、和修改记录时的负担；MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。这两种情况下不建议建索引：

1. 表的记录非常少，只有一千条甚至只有几百条记录的表，没有必要建索引，查询时进行全表扫描就行了。当表中的记录超过2000条时可以考虑建索引。

2. 索引的选择性较低。表的选择性（Selectivity）是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity = Cardinality / #T

   选择性越高的索引价值越大。

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

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

InnoDB的主键选择和插入优化

在InnoDB存储引擎中，如果没有特别的需要，使用一个与业务无关的自增字段作为主键。

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

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

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

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

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

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

文章大部分均为手打以便于自己理解，作为自己的学习记录。如果想详细阅读MySQL索引的数据结构请阅读原文。