【MySQL】（五）索引与算法

本篇文章的主旨是对InnoDB存储引擎支持的索引做一个概述，并对索引内部的机制做一个深入的解析，通过了解索引内部构造来了解哪里可以使用索引。

1、InnoDB存储引擎支持以下几种常见的索引：

• B+树索引
• 全文索引
• 哈希索引

前面已经提到过，InnoDB存储引擎支持的哈希索引时自适应的，InnoDB存储引擎会根据表的使用情况自动为表生成哈希索引，不能人为干预是否在一张表中生成哈希索引。

B+树索引就是传统意义上的索引，这是目前关系型数据库中查找最为常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值快速找到数据。

再就是，B+树并不能找到一个给定键值的具体行。B+树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到内存，再在内存中进行查找，最后得到要查找的数据。

2、数据结构与算法

B+树索引时最为常见的，也是在数据库中使用最为频繁的一种索引。在介绍该索引之前先介绍与之密切相关的一些算法与数据结构。

2.1、二分查找法

二分查找法也称折半查找法，用来查找一组有序的记录数组中的某一记录，其基本思想是：将记录按有序化（递增或递减）排列，在查找过程中采用跳跃式方式查找，即先以有序数列的中点位置为比较对象，如果要找的元素值小于该中点元素，则将带查找序列缩小为左半部分，否则为右半部分。通过一次比较，将查找区间缩小一半。

二分查找法的应用及其广泛，而且它的思想易于理解。表空间中每页Page Directory中的槽是按照主键的顺序存放的，对于某一条具体记录的查询时通过对Page Directory进行二分查找的。

2.2、二叉查找树和平衡二叉树

在介绍B+树之前，先了解一下二叉查找树。B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。

在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的排序输出。若想最大性能地构造一颗二叉查找树，需要这颗查找树是平衡的，从而引出了新的定义——平衡二叉树，或称AVL树。

平衡二叉树的定义如下：首先符合二叉查找树的定义，其次必须满足任何节点的两个字数的高度最大差为1。最好的想能需要建立一颗最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需要建立一颗平衡二叉树即可。

平衡二叉树的查询速度很快，但是维护一颗平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入或更新后树的平衡性。

3、B+树

B+树和二叉树、平衡二叉树一样，都是经典的数据结构。B+树是由B树和索引顺序访问方法演化而来，但是在实现使用过程中几乎已经没有使用B树的情况了。

B+树的简单定义：B+树是为磁盘或其他直接存储辅助设备设计的一种平衡查找树。在B+树中，所有的记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由叶子结点指针进行连接。

下面演示一个B+数结构，高度为2，每页可放4条记录，扇出(fan out)为5。从下图1可以看出，所有记录都在页节点中，并且为顺序存放，我们从最左边的叶节点开始遍历，可以得到所有键值的顺序排序：5、10、15、20、25、30、50、55、60、65、75、80、85、90。

3.1、B+树的插入操作

B+树的插入必须保证插入后叶子节点中的记录依然排序，同时需要考虑插入到B+树的三种情况，每种情况都可能会导致不同的算法。

这里用一个例子来分析B+树的插入。例如，对于上图的这颗B+树，若用户插入28这个键值，发现当前Leaf Page和Index page都没有满，我们直接将记录插入叶节点就可以了。如下图所示：



下面我们再插入70这个值，这时Leaf Page已经满了，但是Index Page还没有满，符合上面的第二种情况。这时插入Leaf Page的情况为

50、55、60、65、70.我们根据中间的值60拆分叶节点，可得到下图所示（双项链表指针依然存在，没有画出）：



最后我们再插入95，这个Leaf Page和Index Page都满了，符合上面第三种情况。需要做2次拆分，如下图所示：



可以看到，不管怎么变化，B+树总会保持平衡。但是为了保持平衡，对于新插入的键值可能需要做大量的拆分页操作。B+树主要用于磁盘，拆分意味着磁盘的操作，应该在可能的情况下尽量减少页的拆分。因此，B+树同样提供了类似二叉树的旋转功能。

旋转发生在Leaf Page已经满，但是其左右兄弟节点没有满的情况下。这时B+树并不会急于去做拆分页的操作，而是将记录转移到所在页的兄弟节点上。在通常情况下，左兄弟会被首先检查用来做旋转操作，因此若是最开始插入键值70，其实B+树并不会急于去拆分叶子结点，而是去做旋转操作，如下图所示：

3.2、B+树的删除操作

B+树使用填充因子来控制树的删除变化，50%填充因子可设的最小值。B+树的删除操作同样必须保证删除后叶子结点中的记录依然排序，同插入一样，B+树的删除操作同样需要考虑以下三种情况，如插入不同的是，删除根据填充因子的变化来衡量。



对上图B+树来进行删除操作。首先删除键值为70的这条记录，该记录符合表讨论的第一种情况，删除后可得到下图：



接着我们删除键值为25的记录，这也是表中讨论的最后一种情况，但是该值还是Index Page中的值，因此在删除Leaf Page中的25后，还应将25的右兄弟节点的28更新到Page Index中，最后可得到下图：



最后看删除键值为60的情况。删除Leaf Page中键值为60的记录后，Fill factor小于50%，这时需要做合并操作，同样，在删除Index Page中相关记录后需要做Index Page的合并操作，最后得到下图

4、B+树索引

前面讨论的都是B+树的数据结构及其一般操作，B+树索引的本质就是B+树在数据库中的实现。但是B+树索引在数据库中有一个特点就是高扇性，因此在数据库中，B+树的高度一般在2~4层，这也就是说查找某一个键值的行记录时最多只需2到4次IO，这倒不错。因为当前一般的机械磁盘每秒至少可以做100次IO，2~4次的IO意味着查询时间只需0.02秒~0.04秒。

数据库中的索引分为聚集索引和辅助索引，但是不断是聚集索引还是辅助的索引，其内部都是B+树，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

4.1、聚集索引

InnoDB存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的行记录数据，页将聚集索引的叶子节点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

由于实际的数据页只能按照一颗B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值的查询。查询优化器能够快速发现某一段范围的数据页需要扫描。

聚集索引上，数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。

聚集索引的另一个好处就是，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据。如用户需要查询一张注册用户的表，查询最后注册的10位用户，由于B+树索引是双向链表的，用户可以快速找到最后一个数据页，并取出10条记录。

4.2、辅助索引

对于辅助索引，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB存储引擎表是索引组织表，因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引（聚集索引）的主键，然后在通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为3的辅助索引中查找数据，那么需要对这颗辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树遍历3次查找，最终找到一个完整的行数据所在页，因此一共需要6次逻辑IO访问以得到最终的一个数据页。

对于其他的一些数据库，如Microsoft SQL Server数据库，其中有一种称为堆表的表类型，即行数据的存储按照插入的顺序存放。这与MySQL数据库的MyISAM存储引擎有些类似。堆表的特性决定了堆表上的索引都是非聚集的，主键与费主键的区别只是是否唯一且非空（NOT NULL）。因此这时书签是一个行标识符，可以用如“文件号：页号：槽号”的格式来定位实际的行数据。

4.3、B+树索引的分裂

在上文中介绍的B+树索引的分裂是最为简单的一种情况，这和数据库中B+树索引的情况可能略有不同。此外上文并没有涉及并发，而这才是B+树索引实现最为困难的部分。

B+树索引页的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费。

InnoDB存储引擎的Page Header中有以下几个部分用来保存插入的顺序信息：

• PAGE_LAST_INSERT
• PAGE_DIRECTION
• PAGE_N_DIRECTION

通过这些信息，InnoDB存储引擎可以决定是向左还是向右进行分裂，同时决定将分裂点记录为哪一个。若插入时随机的，则取页的中间记录作为分裂点的记录，这和之前介绍的相同。若往同一个方向进行插入的记录数量为5，并且目前已经定位到记录（InnoDB存储引擎插入时，首先需要进行定位，定位到的记录为待插入记录的前一条记录）之后还有3条记录，则分裂点的记录为定位到的记录后的第三条记录，否则分裂点记录就是待插入的记录。

4.4、B+树索引的管理

1、索引管理

索引的创建和删除可以通过两种方法，一种是ALERT TABLE，另一种是CREATE/DROP INDEX。

用户可以设置对整个列的数据进行索引，也可以只索引一个列的开头部分数据。

若用户想要查看表中索引的信息，可以使用命令SHOW INDEX。

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。

2、Fast Index Creation

对于索引的添加或者删除这类的DDL操作，MySQL数据库的操作过程为：

• 首先创建一张新的临时表，表结构为通过命令ALERT TABLE新定义的结构。
• 然后把原表中数据导入到临时表
• 接着删除原表。
• 最后把临时表重命名为原来的表名。

可以发现，若用对于一张大表进行索引的添加和删除操作，那么这会需要很长时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。

InnoDB存储引擎从InnoDB 1.0.x版本开始支持一种称为Fast Index Creation（快速索引创建）的索引创建方式——简称FIC。

对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，因此速度较之前提高了很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB存储引擎只需更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义即可。

这里需要特别注意的是，临时表的创建路径是通过参数tmpdir进行设置得。用户必须保证tmpdir有足够的空间可以存放临时表，否则会导致创建索引失败。

由于FIC在索引的创建过程中对表加上了S锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。此外，FIC方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表。

3、Online Schema Change

Online SchemaChange（在线架构改变，简称OSC）。所谓“在线”是指在事务创建过程中，可以有读写事务对表进行操作，这提高了原有MySQL数据库在DDL操作时的并发性。

4、Online DDL

虽然FIC可以让InnoDB存储引擎避免创建临时表，从而提高索引创建表的效率。但正如前面所说的，索引创建时会阻塞表上的DML操作。ODC虽然解决了上诉的部分问题，但是还是有很大的局限性。MySQL5.6版本开始支持Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如INSERT、UPSATE、DELETE这类DML操作，这极大提高了MySQL数据库在生产环境中的可用性。

此外，不仅是辅助索引，以下这几类DDL操作都可以通过“在线”的方式进行操作。

• 辅助索引的创建与删除
• 改变自增长值
• 添加或删除外键约束
• 列的重命名

5、Cardinality

5.1、什么是Cardinality

并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加B+树索引，一般的经验是，在访问表中很少一部分时使用B+树索引才有意义。对于性别字段、地区字段、类型字段，它们可取之值的范围很小，称为低选择性。如：

SELECT * FROM student WHERE sex=‘M’

按性别进行查询时，可取值的范围一般只有‘M’，‘F’。因此上诉SQL语句得到的结果可能是该表50%的数据（假设男女比例1：1），这时添加B+树索引时完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用B+树是最合适的。

怎样查看索引是否是高选择性的呢？可以通过SHOW INDEX结果中的列Cardinality来观察。Cardinality值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确值。在实际应用中，Cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加B+树索引时非常有必要的。

5.2、InnoDB存储引擎的Cardinality统计

上文介绍了Cardinality的重要性，并且告诉读者Cardinality表示选择性。建立索引的前提是列中的数据时高选择性的，这对数据库来说才具有实际意义。然而数据库怎样来统计Cardinality信息呢？因为MySQL数据库中有各种不同的存储引擎，而每种存储引擎对于B+树索引实现又各不相同，所以对Cardinality的统计是放在存储引擎层进行的。

此外需要考虑到的是，在生产环境中，索引的更新操作可能会非常频繁的。如果每次索引在发生操作时就对其进行Cardinality的统计，那么将会给数据库带来很大的负担。另外需要考虑的是，如果一张表的数据非常大，如一张表有50G的数据，那么统计一次Cardinality信息所需要的时间可能非常长。这在生产环境下，也是不能接受的。因此，数据库对于Cardinality的统计都是通过采样的方法来完成的。

在InnoDB存储引擎中，Cardinality统计信息的更新发生在两个操作中：INSERT和UPDATE。根据前面的叙述，不可能在每次发生INSERT和UPDATE时就去更新Cardinality信息，这样会增加数据库系统的负荷，同时对于大表的统计，时间上也不允许数据库这样去操作。因此，InnoDB存储引擎内部对于更新Cardinality信息的策略为：

• 表中1/16的数据已发生过变化
• stat_modified_counter>2000000000

第一种策略为自上次统计Cardinality信息后，表中1/16的数据已经发生过变化，这时需要更新Cardinality信息。第二种情况考虑的是，如果对表中某一行数据频繁地进行操作，这时表中的数据实际并没有增加，实际发生变化的还是这一行数据，则第一种更新策略就无法适用这种情况。故在InnoDB存储引擎内部有一个计数器stat_modified_counter，用来表示发生变化的次数，当stat_modified_counter大于2000000000时，则同样需要更新Cardinality信息。

接着考虑InnoDB存储引擎内部是怎样来进行Cardinality信息的统计和更新操作的呢？同样是通过采样的方法。默认InnoDB存储引擎对8个叶子节点进行采用。采样的过程如下：

• 取得B+树索引中叶子节点的数量，记为A
• 随机取得B+树索引中的8个叶子节点。统计每个页不同记录个数，即为P1，P2，。。。，P8。
• 根据采样信息给出Cardinality的预估值：Cardinality=（P1+P2+。。。+P8）*A/8。

通过上述的说明可以发现，在InnoDB存储引擎中，Cardinality值是通过对8个叶子节点预估而得的，不是一个实际精确的值。再者，每次对Cardinality值的统计，都是通过随机取8个叶子节点得到的，这同时又暗示了另一个Cardinality现象，即每次得到的Cardinality值可能是不同的。

6、B+树索引的使用

6.1、不用应用中B+树索引的使用

在了解了B+树索引的本质和实现之后，下一个需要考虑的问题是怎么正确地使用B+树索引。

在OLTP应用中，B+树索引建立后，对该索引的使用应该只是通过该索引取得表中少部分数据。这时建立B+树索引才是有意义的。

对于OLAP应用来说，情况稍显复杂。

6.2、联合索引

联合索引是指对表上的多个列进行索引。联合索引从本质上来说也是一颗B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。

6.3、覆盖索引

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。

如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

覆盖索引实例：

1、未建立覆盖索引前

select SQL_NO_CACHE count(name) from index_test where name = '小明' and
id_card = '142701199999999999' and type = '2' and hobby = '篮球' and timeline = '1505270575';

explain select SQL_NO_CACHE count(name) from index_test where name = '小明'
and id_card = '142701199999999999' and type = '2' and hobby = '篮球' and timeline = '1505270575';

2、添加覆盖索引

alter table index_test add index index_all(name,id_card,type,hobby,timeline);

3、总结

当一条查询语句符合覆盖索引条件时，sql只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

使用覆盖索引Innodb比MyISAM效果更好—-InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

注：遇到以下情况，执行计划不会选择覆盖查询

1.select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。

2.where条件中不能含有对索引进行like的操作。

6.4、优化器选择不适用索引的情况

这种情况多发生于范围查找，JOIN链接操作等情况下。

7、哈希算法

哈希算法是一种常见的算法，时间复杂度为O（1），且不只存在于索引中，每个数据库应用中都存在该数据库结构。

7.1、哈希表

哈希表也称散列表，由直接寻址表改进而来。主要内容：哈希函数，碰撞解决技术等。

由于这部分比较基础这里就不在讨论了。

7.2、InnoDB存储引擎中的哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找。其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同的哈希函数值。而对于除法散列，m的取值为略大于2倍的缓冲池数量的质数。

7.3、自适应哈希算法

自适应哈希算法采用哈希表实现。这仅是数据库自身创建并使用的，DBA本身并不能对其进行干预。

8、全文检索

8.1、概述

全文检索是将存储于数据库中的整本书或这篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。

8.2、到排索引

全文检索通常是使用倒排索引来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。

9、小结

本篇文章介绍了一些常用的数据结构，如二分查找树、平衡树、B+树、直接寻址表和哈希表，以及InnoDB1.2版本开始支持的全文检索。从数据结构的角度切入数据库中常见的B+树索引和哈希索引的使用，并从内部机制上讨论了使用上述索引的环境和优化方法。