读书笔记《MySQL技术内幕：InnoDB存储引擎》第5章 索引与算法

索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。

一些开发人员总是在事后才想起添加索引——我一直认为，这源于一种错误的开发模式。如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对于数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或者认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需要的时间，并且可能会遗漏一部分的索引。

当然索引也并不是越多越好，我曾经遇到这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。

这一章的主旨是对InnoDB存储引擎支持的索引做一个概述，并对索引内部的机制做一个深入的解析，通过了解索引内部构造来了解哪里可以使用索引。本章的风格和别的有关MySQL的书有所不同，更偏重于索引内部的实现和算法问题的讨论。

5.1　InnoDB存储引擎索引概述

InnoDB存储引擎支持以下几种常见的索引：
❑B+树索引
❑全文索引
❑哈希索引

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

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

注意　B+树中的B不是代表二叉（binary），而是代表平衡（balance），因为B+树是从最早的平衡二叉树演化而来，但是B+树不是一个二叉树。

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

5.2　数据结构与算法

B+树索引是最为常见，也是在数据库中使用最为频繁的一种索引。在介绍该索引之前先介绍与之密切相关的一些算法与数据结构，这有助于读者更好的理解B+树索引的工作方式。

5.2.1　二分查找法

略

5.2.2　二叉查找树和平衡二叉树

在介绍B+树前，需要先了解一下二叉查找树。

B+树是通过二叉查找树，再由平衡二叉树，B树演化而来。相信在任何一本有关数据结构的书中都可以找到二叉查找树的章节，二叉查找树是一种经典的数据结构。图5-2显示了一棵二叉查找树。

图　5-2　二叉查找树

图5-2中的数字代表每个节点的键值，在二叉查找树中，左子树的键值总是小于根的键值，右子树的键值总是大于根的键值。因此可以通过中序遍历得到键值的排序输出，图5-2的二叉查找树经过中序遍历后输出：2、3、5、6、7、8。

对图5-2的这棵二叉树进行查找，如查键值为5的记录，先找到根，其键值是6，6大于5，因此查找6的左子树，找到3；而5大于3，再找其右子树；一共找了3次。如果按2、3、5、6、7、8的顺序来找同样需要3次。用同样的方法再查找键值为8的这个记录，这次用了3次查找，而顺序查找需要6次。

计算平均查找次数可得：顺序查找的平均查找次数为（1+2+3+4+5+6）/6=3.3次，二叉查找树的平均查找次数为（3+3+3+2+2+1）/6=2.3次。二叉查找树的平均查找速度比顺序查找来得更快。

二叉查找树可以任意地构造，同样是2、3、5、6、7、8这五个数字，也可以按照图5-3的方式建立二叉查找树。

图5-3的平均查找次数为（1+2+3+4+5+5）/6=3.16次，和顺序查找差不多。显然这棵二叉查找树的查询效率就低了。因此若想最大性能地构造一棵二叉查找树，需要这棵二叉查找树是平衡的，从而引出了新的定义——平衡二叉树，或称为AVL树。

图　5-3　效率较低的一棵二叉查找树

平衡二叉树的定义如下：
首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为1。显然，图5-3不满足平衡二叉树的定义，而图5-2是一棵平衡二叉树。平衡二叉树的查找性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需建立一棵平衡二叉树即可。

平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要1次或多次左旋和右旋来得到插入或更新后树的平衡性。对于图5-2所示的平衡树，当用户需要插入一个新的键值为9的节点时，需做如图5-4所示的变动。

图　5-4　插入键值9，平衡二叉树的变化

这里通过一次左旋操作就将插入后的树重新变为平衡的了。但是有的情况可能需要多次，如图5-5所示。

图　5-5　需多次旋转的平衡二叉树

图5-4和图5-5中列举了向一棵平衡二叉树插入一个新的节点后，平衡二叉树需要做的旋转操作。除了插入操作，还有更新和删除操作，不过这和插入没有本质的区别，都是通过左旋或者右旋来完成的。因此对一棵平衡树的维护是有一定开销的，不过平衡二叉树多用于内存结构对象中，因此维护的开销相对较小。

5.3　B+树

B+树和二叉树、平衡二叉树一样，都是经典的数据结构。B+树由B树和索引顺序访问方法（ISAM，是不是很熟悉？对，这也是MyISAM引擎最初参考的数据结构）演化而来，但是在现实使用过程中几乎已经没有使用B树的情况了。

B+树的定义在任何一本数据结构书中都能找到，其定义十分复杂，在这里列出来只会让读者感到更加困惑。这里，我来精简地对B+树做个介绍：B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树。在B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。先来看一个B+树，其高度为2，每页可存放4条记录，扇出（fan out）为5，如图5-6所示。

图　5-6　一棵高度为2的B+树

从图5-6可以看出，所有记录都在叶子节点上，并且是顺序存放的，如果用户从最左边的叶子节点开始顺序遍历，可以得到所有键值的顺序排序：5、10、15、20、25、30、50、55、60、65、75、80、85、90。

5.3.1　B+树的插入操作

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

这里用一个例子来分析B+树的插入。例如，对于图5-6中的这棵B+树，若用户插入28这个键值，发现当前Leaf Page和Index Page都没有满，直接进行插入即可，之后得图5-7。

图　5-7　插入键值28

接着再插入70这个键值，这时原先的Leaf Page已经满了，但是Index Page还没有满，符合表5-1的第二种情况，这时插入Leaf Page后的情况为55、55、60、65、70，并根据中间的值60来拆分叶子节点，可得图5-8。

图　5-8　插入键值70

因为图片显示的关系，这次没有能在各叶子节点加上双向链表指针。不过和图5-6、图5-7一样，它还是存在的。
最后插入键值95，这时符合表5-1中讨论的第三种情况，即Leaf Page和Index Page都满了，这时需要做两次拆分，如图5-9所示。

图　5-9　插入键值95

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

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

图　5-10　B+树的旋转操作

从图5-10可以看到，采用旋转操作使B+树减少了一次页的拆分操作，同时这棵B+树的高度依然还是2。

5.3.2　B+树的删除操作

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

根据图5-9的B+树来进行删除操作。首先删除键值为70的这条记录，该记录符合表5-2讨论的第一种情况，删除后可得到图5-11。

图　5-11　删除键值70

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

图　5-12　删除键值25

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

图　5-13　删除键值60

5.4　B+树索引

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

数据库中的B+树索引可以分为聚集索引（clustered inex）和辅助索引（secondary index）[1]，但是不管是聚集还是辅助的索引，其内部都是B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

5.4.1　聚集索引

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

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

接着来看一张表，这里以人为的方式让其每个页只能存放两个行记录，如：

CREATE TABLE t（
	a INT NOT NULL,
	b VARCHAR(8000),
	c INT NOT NULL,
	PRIMARY KEY(a),
	KEY idx_c(c)
)ENGINE=INNODB;

INSERT INTO t SELECT 1,REPEAT('a',7000),-1;
INSERT INTO t SELECT 2,REPEAT('a',7000),-2;
INSERT INTO t SELECT 3,REPEAT('a',7000),-3;
INSERT INTO t SELECT 4,REPEAT('a',7000),-4;

在上述例子中，插入的列b长度为7000，因此可以以人为的方式使目前每个页只能存放两个行记录。接着用py_innodb_page_info工具来分析表空间，可得：

[root@nineyou0-43 data]#py_innodb_page_info.py-v mytest/t.ibd
page offset 00000000,page type＜File Space Header＞
page offset 00000001,page type＜Insert Buffer Bitmap＞
page offset 00000002,page type＜File Segment inode＞
page offset 00000003,page type＜B-tree Node＞,page level＜0001＞
page offset 00000004,page type＜B-tree Node＞,page level＜0000＞
page offset 00000005,page type＜B-tree Node＞,page level＜0000＞
page offset 00000006,page type＜B-tree Node＞,page level＜0000＞
page offset 00000000,page type＜Freshly Allocated Page＞
Total number of page:8:
Freshly Allocated Page:1
Insert Buffer Bitmap:1
File Space Header:1
B-tree Node:4
File Segment inode:1

page level为0000的即是数据页，而前面的章节也对数据页进行了分析，所以这不是当前所需要关注的部分。要分析的是page level为0001的页，当前聚集索引的B+树高度为2，故该页是B+树的根。通过hexdump工具来观察索引的根页中所存放的数据：

0000c000 c2 33 62 95 00 00 00 03 ff ff ff ff ff ff ff ff|.3b.............|
0000c010 00 00 00 0a b6 8c ce 57 45 bf 00 00 00 00 00 00|.......WE.......|
0000c020 00 00 00 00 00 f9 00 02 00 a2 80 05 00 00 00 00|................|
0000c030 00 9a 00 02 00 02 00 03 00 00 00 00 00 00 00 00|................|
0000c040 00 01 00 00 00 00 00 00 01 e2 00 00 00 f9 00 00|................|
0000c050 00 02 00 f2 00 00 00 f9 00 00 00 02 00 32 01 00|.............2..|
0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00|...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 00 10 00 11 00 0e 80 00|supremum........|
0000c080 00 01 00 00 00 04 00 00 00 19 00 0e 80 00 00 02|................|
0000c090 00 00 00 05 00 00 00 21 ff d6 80 00 00 04 00 00|.......!........|
0000c0a0 00 06 00 00 00 00 00 00 00 00 00 00 00 00 00 00|................|
0000c0b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|................|
0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00|................|
0000fff0 00 00 00 00 00 70 00 63 73 d8 52 3a b6 8c ce 57|.....p.cs.R:...W|

这里可以直接通过页尾的Page Directory来分析此页。从00 63可以知道该页中行开始的位置，接着通过Recorder Header来分析，0xc063开始的值为69 6e 66 69 6d 75 6d 00，就代表infimum为行记录，之前的5字节01 00 02 00 1b就是Recorder Header，分析第4位到第8位的值1代表该行记录中只有一个记录（需要记住的是，InnoDB的Page Directory是稀疏的），即infimum记录本身。通过Recorder Header中最后的两个字节00 1b来判断下一条记录的位置，即c063+1b=c07e，读取键值可得80 00 00 01，这就是主键为1的键值（表定义时INT是无符号的，因此二进制是0x80 00 00 01，而不是0x0001），80 00 00 01后的值00 00 00 04代表指向数据页的页号。同样的方式可以找到80 00 00 02和80 00 00 04这两个键值以及它们指向的数据页。

通过以上对非数据页节点的分析，可以发现数据页上存放的是完整的每行的记录，而在非数据页的索引页中，存放的仅仅是键值及指向数据页的偏移量，而不是一个完整的行记录。因此这棵聚集索引树的构造大致如图5-14所示。

图　5-14　B+树索引

许多数据库的文档会这样告诉读者：聚集索引按照顺序物理地存储数据。如果看图5-14，可能也会有这样的感觉。但是试想一下，如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点：一是前面说过的页通过双向链表链接，页按照主键的顺序排序；另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。

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

mysql＞EXPLAIN
-＞SELECT*FROM Profile ORDER BY id LIMIT 10\G; -- \G:结果单独行呈现
***************************1.row***************************
id:1
select_type:SIMPLE
table:Profile
type:index
possible_keys:NULL
key:PRIMARY
key_len:4
ref:NULL
rows:10
Extra:
1 row in set(0.00 sec)

可以看到虽然使用ORDER BY对记录进行排序，但是在实际过程中并没有进行所谓的filesort操作，而这就是因为聚集索引的特点。

另一个是范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可，又如：

mysql＞EXPLAIN
-＞SELECT*FROM Profile
-＞WHERE id＞10 AND id＜10000\G;
***************************1.row***************************
id:1
select_type:SIMPLE
table:Profile
type:range
possible_keys:PRIMARY
key:PRIMARY
key_len:4
ref:NULL
rows:14868
Extra:Using where
1 row in set（0.01 sec）

执行EXPLAIN得到了MySQL数据库的执行计划（execute plan），并且在rows列中给出了一个查询结果的预估返回行数。要注意的是，rows代表的是一个预估值，不是确切的值，如果实际执行这句SQL的查询，可以看到实际上只有9946行记录：

mysql＞SELECT COUNT(*)from Profile
-＞WHERE id＞10 AND id＜10000;
***************************1.row***************************
COUNT(1):9946
1 row in set(0.00 sec)

[1]辅助索引有时也称非聚集索引（non-clustered index）。

5.4.2　辅助索引

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

图　5-15　辅助索引与聚集索引的关系

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

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

有的Microsoft SQL Server数据库DBA问过我这样的问题，为什么在Microsoft SQL Server数据库上还要使用索引组织表？堆表的书签使非聚集查找可以比主键书签方式更快，并且非聚集可能在一张表中存在多个，我们需要对多个非聚集索引进行查找。而且对于非聚集索引的离散读取，索引组织表上的非聚集索引会比堆表上的聚集索引慢一些。

当然，在一些情况下，使用堆表的确会比索引组织表更快，但是我觉得大部分原因是由于存在OLAP（On-Line Analytical Processing，在线分析处理）的应用。其次就是前面提到的，表中数据是否需要更新，并且更新是否影响到物理地址的变更。此外另一个不能忽视的是对于排序和范围查找，索引组织表通过B+树的中间节点就可以找到要查找的所有页，然后进行读取，而堆表的特性决定了这对其是不能实现的。最后，非聚集索引的离散读，的确存在上述的情况，但是一般的数据库都通过实现预读（read ahead）技术来避免多次的离散读操作。因此，具体是建堆表还是索引组织表，这取决于应用，不存在哪个更优的问题。这和InnoDB存储引擎好还是MyISAM存储引擎好这个问题的答案是一样的，It all depends。
接着通过阅读表空间文件来分析InnoDB存储引擎的非聚集索引的实际存储。还是分析上一小节所用的表t。不同的是，在表t上再建立一个列c，并对列c创建非聚集索引：

mysql＞ALTER TABLE t
-＞ADD c INT NOT NULL;
Query OK,4 rows affected(0.24 sec)
Records:4 Duplicates:0 Warnings:0
mysql＞UPDATE t SET c=0-a;
Query OK,4 rows affected(0.04 sec)
Rows matched:4 Changed:4 Warnings:0
mysql＞ALTER TALBE t ADDKEY idx_c(c);
Query OK,4 rows affected(0.28 sec)
Records:4 Duplicates:0 Warnings:0
mysql＞SHOW INDEX FROM t\G;
***************************1.row***************************
Table:t
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:a
Collation:A
Cardinality:2
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
***************************2.row***************************
Table:t
Non_unique:1
Key_name:idx_c
Seq_in_index:1
Column_name:c
Collation:A
Cardinality:2
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
2 rows in set（0.00 sec）
mysql＞select a,c from t;
+---+----+
|a|c|
+---+----+
|4|-4|
|3|-3|
|2|-2|
|1|-1|
+---+----+
4 rows in set（0.00 sec）

然后用py_innodb_page_info工具来分析表空间，可得：

[root@nineyou0-43 mytest]#py_innodb_page_info.py-v t.ibd
page offset 00000000,page type＜File Space Header＞
page offset 00000001,page type＜Insert Buffer Bitmap＞
page offset 00000002,page type＜File Segment inode＞
page offset 00000003,page type＜B-tree Node＞,page level＜0001＞
page offset 00000004,page type＜B-tree Node＞,page level＜0000＞
page offset 00000005,page type＜B-tree Node＞,page level＜0000＞
page offset 00000006,page type＜B-tree Node＞,page level＜0000＞
page offset 00000007,page type＜B-tree Node＞,page level＜0000＞
page offset 00000000,page type＜Freshly Allocated Page＞
Total number of page:9:
Freshly Allocated Page:1
Insert Buffer Bitmap:1
File Space Header:1
B-tree Node:5
File Segment inode:1

对比前一次分析，我们可以看到这次多了一个页。分析page offset为4的页，该页即为非聚集索引所在页，通过工具hexdump分析可得：

00010000 b9 aa 8e d0 00 00 00 04 ff ff ff ff ff ff ff ff|................|
00010010 00 00 00 0a ec ea 4e 27 45 bf 00 00 00 00 00 00|......N'E.......|
00010020 00 00 00 00 01 02 00 02 00 ac 80 06 00 00 00 00|................|
00010030 00 a4 00 01 00 03 00 04 00 00 00 00 00 52 d4 8b|.............R..|
00010040 00 00 00 00 00 00 00 00 01 f2 00 00 01 02 00 00|................|
00010050 00 02 02 72 00 00 01 02 00 00 00 02 01 b2 01 00|...r............|
00010060 02 00 41 69 6e 66 69 6d 75 6d 00 05 00 0b 00 00|..Ainfimum......|
00010070 73 75 70 72 65 6d 75 6d 00 00 10 ff f3 7f ff ff|supremum........|
00010080 ff 80 00 00 01 00 00 18 ff f3 7f ff ff fe 80 00|................|
00010090 00 02 00 00 20 ff f3 7f ff ff fd 80 00 00 03 00|...............|
000100a0 00 28 ff f3 7f ff ff fc 80 00 00 04 00 00 00 00|.（..............|
……
00013ff0 00 00 00 00 00 70 00 63 f3 46 77 f2 ec ea 4e 27|.....p.c.Fw...N'|

由于只有4行数据，并且列c只有4字节，因此在一个非聚集索引页中即可完成，整理分析可得如图5-16所示的关系。

图　5-16　辅助索引分析

图5-16显示了表t中辅助索引idx_c和聚集索引的关系。可以看到辅助索引的叶子节点中包含了列c的值和主键的值。因为这里我特意将键值设为负值，所以会发现-1以7f ff ff ff的方式进行内部存储。7（0111）最高位为0，代表负值，实际的值应该取反后加1，即得-1。

5.4.3　B+树索引的分裂

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

B+树索引页的分裂并不总是从页的中间记录开始，这样可能会导致页空间的浪费。例如下面的记录：
1、2、3、4、5、6、7、8、9

插入是根据自增顺序进行的，若这时插入10这条记录后需要进行页的分裂操作，那么根据5.3.1节介绍的分裂方法，会将记录5作为分裂点记录（split record），分裂后得到下面两个页：
P1：1、2、3、4
P2：5、6、7、8、9、10

然而由于插入是顺序的，P1这个页中将不会再有记录被插入，从而导致空间的浪费。而P2又会再次进行分裂。

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

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

来看一个向右分裂的例子，并且定位到的记录之后还有3条记录，则分裂点记录如图5-17所示。

图　5-17　向右分裂的一种情况

图5-17向右分裂且定位到的记录之后还有3条记录，split record为分裂点记录最终向右分裂得到如图5-18所示的情况。

图　5-18　向右分裂后页中记录的情况

对于图5-19的情况，分裂点就为插入记录本身，向右分裂后仅插入记录本身，这在自增插入时是普遍存在的一种情况。

图　5-19　向右分裂的另一种情况

5.4.4　B+树索引的管理

1.索引管理

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

ALTER TABLE tbl_name
|ADD{INDEX|KEY}[index_name]
[index_type](index_col_name,...)[index_option]...
ALTER TABLE tbl_name
DROP PRIMARY KEY
|DROP{INDEX|KEY}index_name

CREATE/DROP INDEX的语法同样很简单：

CREATE[UNIQUE]INDEX index_name
[index_type]
ON tbl_name(index_col_name,...)
DROP INDEX index_name ON tbl_name

用户可以设置对整个列的数据进行索引，也可以只索引一个列的开头部分数据，如前面创建的表t，列b为varchar（8000），但是用户可以只索引前100个字段，如：

mysql＞ALTER TABLE t
-＞ADD KEY idx_b(b(100));
Query OK,4 rows affected(0.32 sec)
Records:4 Duplicates:0 Warnings:0

若用户想要查看表中索引的信息，可以使用命令show index。下面的例子使用之前的表t，并加一个对于列（a，c）的联合索引idx_a_c，可得：

mysql＞ALTER TABLE t
-＞ADD KEY idx_a_c(a,c);
Query OK,4 rows affected(0.31 sec)
Records:4 Duplicates:0 Warnings:0
mysql＞SHOW INDEX FROM t\G;
***************************1.row***************************
Table:t
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:a
Collation:A
Cardinality:2
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
***************************2.row***************************
Table:t
Non_unique:1
Key_name:idx_b
Seq_in_index:1
Column_name:b
Collation:A
Cardinality:2
Sub_part:100
Packed:NULL
Null:YES
Index_type:BTREE
Comment:
***************************3.row***************************
Table:t
Non_unique:1
Key_name:idx_a_c
Seq_in_index:1
Column_name:a
Collation:A
Cardinality:2
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
***************************4.row***************************
Table:t
Non_unique:1
Key_name:idx_a_c
Seq_in_index:2
Column_name:c
Collation:A
Cardinality:2
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
***************************5.row***************************
Table:t
Non_unique:1
Key_name:idx_c
Seq_in_index:1
Column_name:c
Collation:A
Cardinality:2
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
5 rows in set（0.00 sec）

通过命令SHOW INDEX FROM可以观察到表t上有4个索引，分别为主键索引、c列上的辅助索引、b列的前100字节构成的辅助索引，以及（a、c）的联合辅助索引。

接着具体阐述命令SHOW INDEX展现结果中每列的含义。
❑Table：索引所在的表名。
❑Non_unique：非唯一的索引，可以看到primary key是0，因为必须是唯一的。
❑Key_name：索引的名字，用户可以通过这个名字来执行DROP INDEX。
❑Seq_in_index：索引中该列的位置，如果看联合索引idx_a_c就比较直观了。
❑Column_name：索引列的名称。
❑Collation：列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A，即排序的。如果使用了Heap存储引擎，并且建立了Hash索引，这里就会显示NULL了。因为Hash根据Hash桶存放索引数据，而不是对数据进行排序。
❑Cardinality：非常关键的值，表示索引中唯一值的数目的估计值。Cardinality表的行数应尽可能接近1，如果非常小，那么用户需要考虑是否可以删除此索引。
❑Sub_part：是否是列的部分被索引。如果看idx_b这个索引，这里显示100，表示只对b列的前100字符进行索引。如果索引整个列，则该字段为NULL。
❑Packed：关键字如何被压缩。如果没有被压缩，则为NULL。
❑Null：是否索引的列含有NULL值。可以看到idx_b这里为Yes，因为定义的列b允许NULL值。
❑Index_type：索引的类型。InnoDB存储引擎只支持B+树索引，所以这里显示的都是BTREE。
❑Comment：注释。

Cardinality值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。上面显示的结果主键的Cardinality为2，但是很显然我们的表中有4条记录，这个值应该是4。如果需要更新索引Cardinality的信息，可以使用ANALYZE TABLE命令，如：

mysql＞analyze table t\G;
***************************1.row***************************
Table:mytest.t
Op:analyze
Msg_type:status
Msg_text:OK
1 row in set(0.01 sec)
mysql＞show index from t\G;
***************************1.row***************************
Table:t
Non_unique:0
Key_name:PRIMARY
Seq_in_index:1
Column_name:a
Collation:A
Cardinality:4
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:
……

这时的Cardinality值就对了。不过，在每个系统上可能得到的结果不一样，因为ANALYZE TABLE现在还存在一些问题，可能会影响最后得到的结果。另一个问题是MySQL数据库对于Cardinality计数的问题，在运行一段时间后，可能会看到下面的结果：

mysql＞show index from Profile\G;
***************************1.row***************************
Table:Profile
Non_unique:0
Key_name:UserName
Seq_in_index:1
Column_name:username
Collation:A
Cardinality:NULL
Sub_part:NULL
Packed:NULL
Null:
Index_type:BTREE
Comment:

Cardinality为NULL，在某些情况下可能会发生索引建立了却没有用到的情况。或者对两条基本一样的语句执行EXPLAIN，但是最终出来的结果不一样：一个使用索引，另外一个使用全表扫描。这时最好的解决办法就是做一次ANALYZE TABLE的操作。因此我建议在一个非高峰时间，对应用程序下的几张核心表做ANALYZE TABLE操作，这能使优化器和索引更好地为你工作。

2.Fast Index Creation

MySQL 5.5版本之前（不包括5.5）存在的一个普遍被人诟病的问题是MySQL数据库对于索引的添加或者删除的这类DDL操作，MySQL数据库的操作过程为：
❑首先创建一张新的临时表，表结构为通过命令ALTER TABLE新定义的结构。
❑然后把原表中数据导入到临时表。
❑接着删除原表。
❑最后把临时表重名为原来的表名。

可以发现，若用户对于一张大表进行索引的添加和删除操作，那么这会需要很长的时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。而这对于Microsoft SQL Server或Oracle数据库的DBA来说，MySQL数据库的索引维护始终让他们感觉非常痛苦。

InnoDB存储引擎从InnoDB 1.0.x版本开始支持一种称为Fast Index Creation（快速索引创建）的索引创建方式——简称FIC。
对于辅助索引的创建，InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB存储引擎只需更新内部视图，并将辅助索引的空间标记为可用，同时删除MySQL数据库内部视图上对该表的索引定义即可。

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

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

3.Online Schema Change

Online Schema Change（在线架构改变，简称OSC）最早是由Facebook实现的一种在线执行DDL的方式，并广泛地应用于Facebook的MySQL数据库。所谓“在线”是指在事务的创建过程中，可以有读写事务对表进行操作，这提高了原有MySQL数据库在DDL操作时的并发性。

Facebook采用PHP脚本来现实OSC，而并不是通过修改InnoDB存储引擎源码的方式。OSC最初由Facebook的员工Vamsi Ponnekanti开发。此外，OSC借鉴了开源社区之前的工具The openarkkit toolkit oak-online-alter-table。实现OSC步骤如下：
❑init，即初始化阶段，会对创建的表做一些验证工作，如检查表是否有主键，是否存在触发器或者外键等。
❑createCopyTable，创建和原始表结构一样的新表。
❑alterCopyTable：对创建的新表进行ALTER TABLE操作，如添加索引或列等。
❑createDeltasTable，创建deltas表，该表的作用是为下一步创建的触发器所使用。之后对原表的所有DML操作会被记录到createDeltasTable中。
❑createTriggers，对原表创建INSERT、UPDATE、DELETE操作的触发器。触发操作产生的记录被写入到deltas表。
❑startSnpshotXact，开始OSC操作的事务。
❑selectTableIntoOutfile，将原表中的数据写入到新表。为了减少对原表的锁定时间，这里通过分片（chunked）将数据输出到多个外部文件，然后将外部文件的数据导入到copy表中。分片的大小可以指定，默认值是500 000。
❑dropNCIndexs，在导入到新表前，删除新表中所有的辅助索引。
❑loadCopyTable，将导出的分片文件导入到新表。
❑replayChanges，将OSC过程中原表DML操作的记录应用到新表中，这些记录被保存在deltas表中。
❑recreateNCIndexes，重新创建辅助索引。
❑replayChanges，再次进行DML日志的回放操作，这些日志是在上述创建辅助索引中过程中新产生的日志。
❑swapTables，将原表和新表交换名字，整个操作需要锁定2张表，不允许新的数据产生。由于改名是一个很快的操作，因此阻塞的时间非常短。

上述只是简单介绍了OSC的实现过程，实际脚本非常复杂，仅OSC的PHP核心代码就有2200多行，用到的MySQL InnoDB的知识点非常多，建议DBA和数据库开发人员尝试进行阅读，这有助于更好地理解InnoDB存储引擎的使用。
由于OSC只是一个PHP脚本，因此其有一定的局限性。例如其要求进行修改的表一定要有主键，且表本身不能存在外键和触发器。此外，在进行OSC过程中，允许SET sql_bin_log=0，因此所做的操作不会同步slave服务器，可能导致主从不一致的情况。

4.Online DDL

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

此外，不仅是辅助索引，以下这几类DDL操作都可以通过“在线”的方式进行操作：
❑辅助索引的创建与删除
❑改变自增长值
❑添加或删除外键约束
❑列的重命名
通过新的ALTER TABLE语法，用户可以选择索引的创建方式：

ALTER　TABLE tbl_name
|ADD{INDEX|KEY}[index_name]
[index_type](index_col_name,...)[index_option]...
ALGORITHM[=]{DEFAULT|INPLACE|COPY}
LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}

ALGORITHM指定了创建或删除索引的算法，COPY表示按照MySQL 5.1版本之前的工作模式，即创建临时表的方式。INPLACE表示索引创建或删除操作不需要创建临时表。DEFAULT表示根据参数old_alter_table来判断是通过INPLACE还是COPY的算法，该参数的默认值为OFF，表示采用INPLACE的方式，如：

mysql＞SELECT@@version\G;
***************************1.row***************************
@@version:5.6.6-m9
1 row in set(0.00 sec)
mysql＞SHOW VARIABLES LIKE'old_alter_table'\G;
***************************1.row***************************Variable_name:old_alter_table
Value:OFF
1 row in set(0.00 sec)

LOCK部分为索引创建或删除时对表添加锁的情况，可有的选择为：

（1）NONE
执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。因此这种模式可以获得最大的并发度。

（2）SHARE
这和之前的FIC类似，执行索引创建或删除操作时，对目标表加上一个S锁。对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作。如果存储引擎不支持SHARE模式，会返回一个错误信息。

（3）EXCLUSIVE
在EXCLUSIVE模式下，执行索引创建或删除操作时，对目标表加上一个X锁。读写事务都不能进行，因此会阻塞所有的线程，这和COPY方式运行得到的状态类似，但是不需要像COPY方式那样创建一张临时表。

（4）DEFAULT
DEFAULT模式首先会判断当前操作是否可以使用NONE模式，若不能，则判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式。

InnoDB存储引擎实现Online DDL的原理是在执行创建或者删除操作的同时，将INSERT、UPDATE、DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。

这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认的大小为128MB。若用户更新的表比较大，并且在创建过程中伴有大量的写事务，如遇到innodb_online_alter_log_max_size的空间不能存放日志时，会抛出类似如下的错误：

Error:1799SQLSTATE:HY000(ER_INNODB_ONLINE_LOG_TOO_BIG)
Message:Creating index'idx_aaa'required more than'innodb_online_alter_log_max_size'bytes of modification log.Please try again.

对于这个错误，用户可以调大参数innodb_online_alter_log_max_size，以此获得更大的日志缓存空间。此外，还可以设置ALTER TABLE的模式为SHARE，这样在执行过程中不会有写事务发生，因此不需要进行DML日志的记录。

需要特别注意的是，由于Online DDL在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建过程中，SQL优化器不会选择正在创建中的索引。

5.5　Cardinality值

5.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+树索引是非常有必要的。如：
SELECT*FROM member WHERE usernick=‘David’
表member大约有500万行数据。usernick字段上有一个唯一的索引。这时如果查找用户名为David的用户，将会得到如下的执行计划：

mysql＞EXPLAIN SELECT*FROM member
-＞WHERE usernick='David'\G;
***************************1.row***************************
id:1
select_type:SIMPLE
table:member
type:const
possible_keys:usernick
key:usernick
key_len:62
ref:const
rows:1
Extra:
1 row in set(0.00 sec)

可以看到使用了usernick这个索引，这也符合之前提到的高选择性，即SQL语句选取表中较少行的原则。

5.5.2　InnoDB存储引擎的Cardinality统计

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

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

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

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

接着考虑InnoDB存储引擎内部是怎样来进行Cardinality信息的统计和更新操作的呢？同样是通过采样的方法。默认InnoDB存储引擎对8个叶子节点（Leaf　Page）进行采用。采样的过程如下：
❑取得B+树索引中叶子节点的数量，记为A。
❑随机取得B+树索引中的8个叶子节点。统计每个页不同记录的个数，即为P1，P2，…，P8。
❑根据采样信息给出Cardinality的预估值：Cardinality=（P1+P2+…+P8）*A/8。

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

SHOW INDEX FROM OrderDetails

上述这句SQL语句会触发MySQL数据库对于Cardinality值的统计，第一次运行得到的结果如图5-20所示。

图　5-20　第一次运行SHOW INDEX FROM OrderDetails的结果

在上述测试过程中，并没有通过INSERT、UPDATE这类操作来改变表OrderDetails中的内容，但是当第二次再运行SHOW INDEX FROM语句时，Cardinality值还是会发生变化，如图5-21所示。

图　5-21　第二次运行SHOW INDEX FROM OrderDetails的结果

可以看到，第二次运行SHOW INDEX FROM语句时，表OrderDetails中索引的Cardinality值都发生了变化，虽然表OrderDetails本身并没有发生任何的变化，但是，由于Cardinality是对随机取8个叶子节点进行分析，所以即使表没有发生变化，用户观察到的索引Cardinality值还是会发生变化，这本身并不是InnoDB存储引擎的Bug，只是随机采样而导致的结果。

当然，有一种情况可能使得用户每次观察到的索引Cardinality值都是一样的，那就是表足够小，表的叶子节点数小于或者等于8个。这时即使随机采样，也总是会采取到这些页，因此每次得到的Cardinality值是相同的。

在InnoDB 1.2版本之前，可以通过参数innodb_stats_sample_pages用来设置统计Cardinality时每次采样页的数量，默认值为8。同时，参数innodb_stats_method用来判断如何对待索引中出现的NULL值记录。该参数默认值为nulls_equal，表示将NULL值记录视为相等的记录。其有效值还有nulls_unequal，nulls_ignored，分别表示将NULL值记录视为不同的记录和忽略NULL值记录。例如某页中索引记录为NULL、NULL、1、2、2、3、3、3，在参数innodb_stats_method的默认设置下，该页的Cardinality为4；若参数innodb_stats_method为nulls_unequal，则该页的Caridinality为5；若参数innodb_stats_method为nulls_ignored，则Cardinality为3。

当执行SQL语句ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX以及访问INFORMATION_SCHEMA架构下的表TABLES和STATISTICS时会导致InnoDB存储引擎去重新计算索引的Cardinality值。若表中的数据量非常大，并且表中存在多个辅助索引时，执行上述这些操作可能会非常慢。虽然用户可能并不希望去更新Cardinality值。
InnoDB1.2版本提供了更多的参数对Cardinality统计进行设置，这些参数如表5-3所示。

5.6　B+树索引的使用

5.6.1　不同应用中B+树索引的使用

在了解了B+树索引的本质和实现后，下一个需要考虑的问题是怎样正确地使用B+树索引，这不是一个简单的问题。这里所总结的可能并不适用于所有的应用场合。我所能做的只是概括一个大概的方向。在实际的生产环境使用中，每个DBA和开发人员，还是需要根据自己的具体生产环境来使用索引，并观察索引使用的情况，判断是否需要添加索引。不要盲从任何人给你的经验意见，Think Different。

根据第1章的介绍，用户已经知道数据库中存在两种类型的应用，OLTP和OLAP应用。

在OLTP应用中，查询操作只从数据库中取得一小部分数据，一般可能都在10条记录以下，甚至在很多时候只取1条记录，如根据主键值来取得用户信息，根据订单号取得订单的详细信息，这都是典型OLTP应用的查询语句。在这种情况下，B+树索引建立后，对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B+树索引才是有意义的，否则即使建立了，优化器也可能选择不使用索引。

对于OLAP应用，情况可能就稍显复杂了。不过概括来说，在OLAP应用中，都需要访问表中大量的数据，根据这些数据来产生查询的结果，这些查询多是面向分析的查询，目的是为决策者提供支持。如这个月每个用户的消费情况，销售额同比、环比增长的情况。因此在OLAP中索引的添加根据的应该是宏观的信息，而不是微观，因为最终要得到的结果是提供给决策者的。例如不需要在OLAP中对姓名字段进行索引，因为很少需要对单个用户进行查询。但是对于OLAP中的复杂查询，要涉及多张表之间的联接操作，因此索引的添加依然是有意义的。但是，如果联接操作使用的是Hash Join，那么索引可能又变得不是非常重要了，所以这需要DBA或开发人员认真并仔细地研究自己的应用。不过在OLAP应用中，通常会需要对时间字段进行索引，这是因为大多数统计需要根据时间维度来进行数据的筛选。

5.6.2　联合索引

联合索引是指对表上的多个列进行索引。前面讨论的情况都是只对表上的一个列进行索引。

联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。例如，以下代码创建了一张t表，并且索引idx_a_b是联合索引，联合的列为（a，b）。

CREATE TABLE t(
	a INT,
	b INT,
	PRIMARY KEY(a),
	KEY idx_a_b(a,b)
)ENGINE=INNODB

那么何时需要使用联合索引呢？在讨论这个问题之前，先来看一下联合索引内部的结果。从本质上来说，联合索引也是一棵B+树，不同的是联合索引的键值的数量不是1，而是大于等于2。接着来讨论两个整型列组成的联合索引，假定两个键值的名称分别为a、b，如图5-22所示。

图　5-22　多个键值的B+树

从图5-22可以观察到多个键值的B+树情况。其实和之前讨论的单个键值的B+树并没有什么不同，键值都是排序的，通过叶子节点可以逻辑上顺序地读出所有数据，就上面的例子来说，即（1，1）、（1，2）、（2，1）、（2，4）、（3，1）、（3，2）。数据按（a，b）的顺序进行了存放。

因此，对于查询SELECT*FROM TABLE WHERE a=xxx and b=xxx，显然是可以使用（a，b）这个联合索引的。
对于单个的a列查询SELECT*FROM TABLE WHERE a=xxx，也可以使用这个（a，b）索引。
但对于b列的查询SELECT*FROM TABLE WHERE b=xxx，则不可以使用这棵B+树索引。

可以发现叶子节点上的b值为1、2、1、4、1、2，显然不是排序的，因此对于b列的查询使用不到（a，b）的索引。

联合索引的第二个好处是已经对第二个键值进行了排序处理。例如，在很多情况下应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引可以避免多一次的排序操作，因为索引本身在叶子节点已经排序了。

来看一个例子，首先根据如下代码来创建测试表buy_log：

CREATE TABLE buy_log(
userid INT UNSIGNED NOT NULL,
buy_date DATE
)ENGINE=InnoDB;

INSERT INTO buy_log VALUES(1,'2009-01-01');
INSERT INTO buy_log VALUES(2,'2009-01-01');
INSERT INTO buy_log VALUES(3,'2009-01-01');
INSERT INTO buy_log VALUES(1,'2009-02-01');
INSERT INTO buy_log VALUES(3,'2009-02-01');
INSERT INTO buy_log VALUES(1,'2009-03-01');
INSERT INTO buy_log VALUES(1,'2009-04-01');
ALTER TABLE buy_log ADD KEY(userid);
ALTER TABLE buy_log ADD KEY(userid,buy_date);

以上代码建立了两个索引来进行比较。两个索引都包含了userid字段。如果只对于userid进行查询，如：

SELECT*FROM buy_log WHERE userid=2;

则优化器的选择为如图5-23所示。

图　5-23　查询条件仅为userid的执行计划

从图5-23中可以发现，possible_keys在这里有两个索引可供使用，分别是单个的userid索引和（userid，buy_date）的联合索引。但是优化器最终的选择是索引userid，因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多。

接着假定要取出userid为1的最近3次的购买记录，其SQL语句如下，执行计划如图5-24所示。

SELECT*FROM buy_log
WHERE userid=1 ORDER BY buy_date DESC LIMIT 3

图　5-24　SQL语句的执行计划

同样的，对于上述的SQL语句既可以使用userid索引，也可以使用（userid，buy_date）索引。但是这次优化器使用了（userid，buy_date）的联合索引userid_2，因为在这个联合索引中buy_date已经排序好了。根据该联合索引取出数据，无须再对buy_date做一次额外的排序操作。若强制使用userid索引，则执行计划如图5-25所示。

图　5-25　强制使用userid索引的执行计划

在Extra选项中可以看到Using filesort，即需要额外的一次排序操作才能完成查询。而这次显然需要对列buy_date排序，因为索引userid中的buy_date是未排序的。

正如前面所介绍的那样，联合索引（a，b）其实是根据列a、b进行排序，因此下列语句可以直接使用联合索引得到结果：

SELECT...FROM TABLE WHERE a=xxx ORDER BY b

然而对于联合索引（a，b，c）来说，下列语句同样可以直接通过联合索引得到结果：

SELECT...FROM TABLE WHERE a=xxx ORDER BY b
SELECT...FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c

但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次filesort排序操作，因为索引（a，c）并未排序：

SELECT...FROM TABLE WHERE a=xxx ORDER BY c

5.6.3　覆盖索引

==InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。==使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

注意　覆盖索引技术最早是在InnoDB Plugin中完成并实现。这意味着对于InnoDB版本小于1.0的，或者MySQL数据库版本为5.0或以下的，InnoDB存储引擎不支持覆盖索引特性。

对于InnoDB存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primary key1，primary key2，…，key1，key2，…）。例如，下列语句都可仅使用一次辅助联合索引来完成查询：

SELECT key2 FROM table WHERE key1=xxx；
SELECT primary key2,key2 FROM table WHERE key1=xxx；
SELECT primary key1,key2 FROM table WHERE key1=xxx；
SELECT primary key1,primary key2，key2 FROM table WHERE key1=xxx；

覆盖索引的另一个好处是对某些统计问题而言的。还是对于上一小节创建的表buy_log，要进行如下的查询：

SELECT COUNT(*)FROM buy_log;

InnoDB存储引擎并不会选择通过查询聚集索引来进行统计。由于buy_log表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少IO操作，故优化器的选择为如图5-26所示。

图　5-26　COUNT（*）操作的执行计划

通过图5-26可以看到，possible_keys列为NULL，但是实际执行时优化器却选择了userid索引，而列Extra列的Using index就是代表了优化器进行了覆盖索引操作。

此外，在通常情况下，诸如（a，b）的联合索引，一般是不可以选择列b中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择，如下述语句：

SELECT COUNT(*)FROM buy_log
WHERE buy_date＞='2011-01-01'AND buy_date＜'2011-02-01'

表buy_log有（userid，buy_date）的联合索引，这里只根据列b进行条件查询，一般情况下是不能进行该联合索引的，但是这句SQL查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引，其执行计划如图5-27所示。

图　5-27　利用覆盖索引执行统计操作

从图5-27中可以发现列possible_keys依然为NULL，但是列key为userid_2，即表示（userid，buy_date）的联合索引。在列Extra同样可以发现Using index提示，表示为覆盖索引。

5.6.4　优化器选择不使用索引的情况

在某些情况下，当执行EXPLAIN命令进行SQL语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN链接操作等情况下。例如：

SELECT*FROM orderdetails
WHERE orderid＞10000 and orderid＜102000;

上述这句SQL语句查找订单号大于10000的订单详情，通过命令SHOW INDEX FROM orderdetails，可观察到的索引如图5-28所示。

图　5-28　表orderdetails的索引详情

可以看到表orderdetails有（OrderID，ProductID）的联合主键，此外还有对于列OrderID的单个索引。上述这句SQL显然是可以通过扫描OrderID上的索引进行数据的查找。然而通过EXPLAIN命令，用户会发现优化器并没有按照OrderID上的索引来查找数据，如图5-29所示。

图　5-29　上述范围查询的SQL执行计划

在possible_keys一列可以看到查询可以使用PRIMARY、OrderID、OrdersOrder_Details三个索引，但是在最后的索引使用中，优化器选择了PRIMARY聚集索引，也就是表扫描（table scan），而非OrderID辅助索引扫描（index scan）。

这是为什么呢？原因在于用户要选取的数据是整行信息，而OrderID索引不能覆盖到我们要查询的信息，因此在对OrderID索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然OrderID索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是20%左右），优化器会选择通过聚集索引来查找数据。因为之前已经提到过，顺序读要远远快于离散读。

因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字FORCE INDEX来强制使用某个索引，如：

SELECT*FROM orderdetails FORCE INDEX(OrderID)
WHERE orderid＞10000 and orderid＜102000;

这时的执行计划如图5-30所示

图　5-30　强制使用辅助索引

5.6.5　索引提示

MySQL数据库支持索引提示（INDEX HINT），显式地告诉优化器使用哪个索引。个人总结以下两种情况可能需要用到INDEX HINT：
❑MySQL数据库的优化器错误地选择了某个索引，导致SQL语句运行的很慢。这种情况在最新的MySQL数据库版本中非常非常的少见。优化器在绝大部分情况下工作得都非常有效和正确。这时有经验的DBA或开发人员可以强制优化器使用某个索引，以此来提高SQL运行的速度。
❑某SQL语句可以选择的索引非常多，这时优化器选择执行计划时间的开销可能会大于SQL语句本身。例如，优化器分析Range查询本身就是比较耗时的操作。这时DBA或开发人员分析最优的索引选择，通过Index Hint来强制使优化器不进行各个执行路径的成本分析，直接选择指定的索引来完成查询。

使用: 略

5.6.6　Multi-Range Read优化

MySQL5.6版本开始支持Multi-Range Read（MRR）优化。Multi-Range Read优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于IO-bound类型的SQL查询语句可带来性能极大的提升。Multi-Range Read优化可适用于range，ref，eq_ref类型的查询。

MRR优化有以下几个好处：
❑MRR使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
❑减少缓冲池中页被替换的次数。
❑批量处理对键值的查询操作。
对于InnoDB和MyISAM存储引擎的范围查询和JOIN查询操作，MRR的工作方式如下：
❑将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
❑将缓存中的键值根据RowID进行排序。
❑根据RowID的排序顺序来访问实际的数据文件。

略 (MRR)

5.6.7　Index Condition Pushdown（ICP）优化

略 (索引条件下推)

5.7.1　哈希表

哈希表（Hash Table）也称散列表，由直接寻址表改进而来。我们先来看直接寻址表。当关键字的全域U比较小时，直接寻址是一种简单而有效的技术。假设某应用要用到一个动态集合，其中每个元素都有一个取自全域U={0，1，…，m-1}[1]
的关键字。同时假设没有两个元素具有相同的关键字。

用一个数组（即直接寻址表）T[0…m-1]表示动态集合，其中每个位置（或称槽或桶）对应全域U中的一个关键字。图5-38说明了这个方法，槽k指向集合中一个关键字为k的元素。如果该集合中没有关键字为k的元素，则T[k]=NULL。

图　5-38　直接寻址表

直接寻址技术存在一个很明显的问题，如果域U很大，在一台典型计算机的可用容量的限制下，要在机器中存储大小为U的一张表T就有点不实际，甚至是不可能的。如果实际要存储的关键字集合K相对于U来说很小，那么分配给T的大部分空间都要浪费掉。

因此，哈希表出现了。在哈希方式下，该元素处于h（k）中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域U映射到哈希表T[0…m-1]的槽位上，如图5-39所示。

图　5-39　哈希表

哈希表技术很好地解决了直接寻址遇到的问题，但是这样做有一个小问题，如图5-39所示的两个关键字可能映射到同一个槽上。一般将这种情况称之为发生了碰撞（collision）。在数据库中一般采用最简单的碰撞解决技术，这种技术被称为链接法（chaining）。

在链接法中，把散列到同一槽中的所有元素都放在一个链表中，如图5-40所示。槽j中有一个指针，它指向由所有散列到j的元素构成的链表的头；如果不存在这样的元素，则j中为NULL。

图　5-40　通过链表法解决碰撞的哈希表

最后要考虑的是哈希函数。哈希函数h必须可以很好地进行散列。最好的情况是能避免碰撞的发生。即使不能避免，也应该使碰撞在最小程度下产生。一般来说，都将关键字转换成自然数，然后通过除法散列、乘法散列或全域散列来实现。数据库中一般采用除法散列的方法。

在哈希函数的除法散列法中，通过取k除以m的余数，将关键字k映射到m个槽的某一个去，即哈希函数为：

h(k)=k mod m

此处的m不是一个很大的数。

5.7.2　InnoDB存储引擎中的哈希算法

InnoDB存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。

对于缓冲池页的哈希表来说，在缓冲池中的Page页都有一个chain指针，它指向相同哈希函数值的页。而对于除法散列，m的取值为略大于2倍的缓冲池页数量的质数。例如：当前参数innodb_buffer_pool_size的大小为10M，则共有640个16KB的页。对于缓冲池页内存的哈希表来说，需要分配640×2=1280个槽，但是由于1280不是质数，需要取比1280略大的一个质数，应该是1399，所以在启动时会分配1399个槽的哈希表，用来哈希查询所在缓冲池中的页。

那么InnoDB存储引擎的缓冲池对于其中的页是怎么进行查找的呢？上面只是给出了一般的算法，怎么将要查找的页转换成自然数呢？

其实也很简单，InnoDB存储引擎的表空间都有一个space_id，用户所要查询的应该是某个表空间的某个连续16KB的页，即偏移量offset。

InnoDB存储引擎将space_id左移20位，然后加上这个space_id和offset，即关键字K=space_id＜＜20+space_id+offset，然后通过除法散列到各个槽中去。

5.7.3　自适应哈希索引

自适应哈希索引采用之前讨论的哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，DBA本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如SELECT*FROM TABLE WHERE index_col=‘xxx’。但是对于范围查找就无能为力了。通过命令SHOW ENGINE INNODB STATUS可以看到当前自适应哈希索引的使用状况，如：

mysql＞SHOW ENGINE INNODB STATUS\G;
***************************1.row***************************
Status:
=====================================
090922 11:52:51 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
……
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf:size 2249,free list len 3346,seg size 5596,
374650 inserts,51897 merged recs,14300 merges
Hash table size 4980499,node heap has 1246 buffer(s)
1640.60 hash searches/s,3709.46 non-hash searches/s
……

现在可以看到自适应哈希索引的使用信息了，包括自适应哈希索引的大小、使用情况、每秒使用自适应哈希索引搜索的情况。需要注意的是，哈希索引只能用来搜索等值的查询，如：

SELECT*FROM table WHERE index_col='xxx'

而对于其他查找类型，如范围查找，是不能使用哈希索引的。因此，这里出现了non-hash searches/s的情况。通过hash searches:non-hash searches可以大概了解使用哈希索引后的效率。

由于自适应哈希索引是由InnoDB存储引擎自己控制的，因此这里的这些信息只供参考。不过可以通过参数innodb_adaptive_hash_index来禁用或启动此特性，默认为开启。

5.8　全文检索

5.8.1　概述

通过前面章节的介绍，已经知道B+树索引的特点，可以通过索引字段的前缀（prefix）进行查找。例如，对于下面的查询B+树索引是支持的：

SELECT*FROM blog WHERE content like'xxx%'

上述SQL语句可以查询博客内容以xxx开头的文章，并且只要content添加了B+树索引，就能利用索引进行快速查询。然而实际这种查询不符合用户的要求，因为在更多的情况下，用户需要查询的是博客内容包含单词xxx的文章，即：

SELECT*FROM blog WHERE content like'%xxx%'

根据B+树索引的特性，上述SQL语句即便添加了B+树索引也是需要进行索引的扫描来得到结果。类似这样的需求在互联网应用中还有很多。例如，搜索引擎需要根据用户输入的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是B+树索引所能很好地完成的工作。

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

在之前的MySQL数据库中，InnoDB存储引擎并不支持全文检索技术。大多数的用户转向MyISAM存储引擎，这可能需要进行表的拆分，并将需要进行全文检索的数据存储为MyISAM表。这样的确能够解决逻辑业务的需求，但是却丧失了InnoDB存储引擎的事务性，而这在生产环境应用中同样是非常关键的。

从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索，其支持MyISAM存储引擎的全部功能，并且还支持其他的一些特性，这些将在后面的小节中进行介绍。

5.8.2　倒排索引

全文检索通常使用倒排索引（inverted index）来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：
❑inverted file index，其表现形式为{单词，单词所在文档的ID}
❑full invertedindex，其表现形式为{单词，(单词所在文档的ID，在具体文档中的位置)}

例如，对于下面这个例子，表t存储的内容如表5-6所示。

DocumentId表示进行全文检索文档的Id，Text表示存储的内容，用户需要对存储的这些文档内容进行全文检索。例如，查找出现过Some单词的文档Id，又或者查找单个文档中出现过两个Some单词的文档Id，等等。

对于inverted file index的关联数组，其存储的内容如表5-7所示。
可以看到单词code存在于文档1和4中，单词days存在与文档3和6中。之后再要进行全文查询就简单了，可以直接根据Documents得到包含查询关键字的文档。

对于inverted file index，其仅存取文档Id，而full inverted index存储的是对(pair)，即(DocumentId，Position)，因此其存储的倒排索引如表5-8所示。

full inverted index还存储了单词所在的位置信息，如code这个单词出现在（1∶6），即文档1的第6个单词为code。相比之下，full inverted index占用更多的空间，但是能更好地定位数据，并扩充一些其他的搜索特性。

5.8.3　InnoDB全文检索

InnoDB存储引擎从1.2.x版本开始支持全文检索的技术，其采用full inverted index的方式。在InnoDB存储引擎中，将(DocumentId，Position)视为一个“ilist”。因此在全文检索的表中，有两个列，一个是word字段，另一个是ilist字段，并且在word字段上有设有索引。此外，由于InnoDB存储引擎在ilist字段中存放了Position信息，故可以进行Proximity Search，而MyISAM存储引擎不支持该特性。

正如之前所说的那样，倒排索引需要将word存放到一张表中，这个表称为Auxiliary Table（辅助表）。在InnoDB存储引擎中，为了提高全文检索的并行性能，共有6张Auxiliary Table，目前每张表根据word的Latin编码进行分区。

Auxiliary Table是持久的表，存放于磁盘上。然而在InnoDB存储引擎的全文索引中，还有另外一个重要的概念FTS Index Cache（全文检索索引缓存），其用来提高全文检索的性能。

FTS Index Cache是一个红黑树结构，其根据（word，ilist）进行排序。这意味着插入的数据已经更新了对应的表，但是对全文索引的更新可能在分词操作后还在FTS Index Cache中，Auxiliary Table可能还没有更新。

InnoDB存储引擎会批量对Auxiliary Table进行更新，而不是每次插入后更新一次Auxiliary Table。当对全文检索进行查询时，Auxiliary Table首先会将在FTS Index Cache中对应的word字段合并到Auxiliary Table中，然后再进行查询。

这种merge操作非常类似之前介绍的Insert Buffer的功能，不同的是Insert Buffer是一个持久的对象，并且其是B+树的结构。然而FTS Index Cache的作用又和Insert Buffer是类似的，它提高了InnoDB存储引擎的性能，并且由于其根据红黑树排序后进行批量插入，其产生的Auxiliary Table相对较小。

InnoDB存储引擎允许用户查看指定倒排索引的Auxiliary Table中分词的信息，可以通过设置参数innodb_ft_aux_table来观察倒排索引的Auxiliary Table。下面的SQL语句设置查看test架构下表fts_a的Auxiliary Table：

mysql＞SET GLOBAL innodb_ft_aux_table='test/fts_a';
Query OK,0 rows affected(0.00 sec)

在上述设置完成后，就可以通过查询information_schema架构下的表INNODB_FT_INDEX_TABLE得到表fts_a中的分词信息。

对于其他数据库，如Oracle 11g，用户可以选择手工在事务提交时，或者固定间隔时间时将倒排索引的更新刷新到磁盘。对于InnoDB存储引擎而言，其总是在事务提交时将分词写入到FTS Index Cache，然后再通过批量更新写入到磁盘。虽然InnoDB存储引擎通过一种延时的、批量的写入方式来提高数据库的性能，但是上述操作仅在事务提交时发生。

当数据库关闭时，在FTS Index Cache中的数据库会同步到磁盘上的Auxiliary Table中。然而，如果当数据库发生宕机时，一些FTS Index Cache中的数据库可能未被同步到磁盘上。那么下次重启数据库时，当用户对表进行全文检索（查询或者插入操作）时，InnoDB存储引擎会自动读取未完成的文档，然后进行分词操作，再将分词的结果放入到FTS Index Cache中。

参数innodb_ft_cache_size用来控制FTS Index Cache的大小，默认值为32M。当该缓存满时，会将其中的(word，ilist)分词信息同步到磁盘的Auxiliary Table中。增大该参数可以提高全文检索的性能，但是在宕机时，未同步到磁盘中的索引信息可能需要更长的时间进行恢复。

FTS Document ID是另外一个重要的概念。在InnoDB存储引擎中，为了支持全文检索，必须有一个列与word进行映射，在InnoDB中这个列被命名为FTS_DOC_ID，其类型必须是BIGINT UNSIGNED NOT NULL，并且InnoDB存储引擎自动会在该列上加入一个名为FTS_DOC_ID_INDEX的Unique Index。上述这些操作都由InnoDB存储引擎自己完成，用户也可以在建表时自动添加FTS_DOC_ID，以及相应的Unique Index。由于列名为FTS_DOC_ID的列具有特殊意义，因此创建时必须注意相应的类型，否则MySQL数据库会抛出错误，如：

mysql＞CREATE TABLE fts_a(
-＞FTS_DOC_ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
-＞body TEXT,
-＞PRIMARY KEY(FTS_DOC_ID)
-＞);
ERROR 1166(42000):Incorrect column name'FTS_DOC_ID'

可以看到，由于用户手动定义的列FTS_DOC_ID的类型是INT，而非BIG INT，因此在创建的时候抛出了Incorrect column name’FTS_DOC_ID’，因此需将该列修改为对应的数据类型，如：

mysql＞CREATE TABLE fts_a(
-＞FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
-＞body TEXT,
-＞PRIMARY KEY(FTS_DOC_ID)
-＞);
Query OK,0 rows affected(0.02 sec)

文档中分词的插入操作是在事务提交时完成，然而对于删除操作，其在事务提交时，不删除磁盘Auxiliary Table中的记录，而只是删除FTS Cache Index中的记录。对于Auxiliary Table中被删除的记录，InnoDB存储引擎会记录其FTS Document ID，并将其保存在DELETED auxiliary table中。在设置参数innodb_ft_aux_table后，用户同样可以访问information_schema架构下的表INNODB_FT_DELETED来观察删除的FTS Document ID。

由于文档的DML操作实际并不删除索引中的数据，相反还会在对应的DELETED表中插入记录，因此随着应用程序的允许，索引会变得非常大，即使索引中的有些数据已经被删除，查询也不会选择这类记录。为此，InnoDB存储引擎提供了一种方式，允许用户手工地将已经删除的记录从索引中彻底删除，该命令就是OPTIMIZE TABLE。因为OPTIMIZE TABLE还会进行一些其他的操作，如Cardinality的重新统计，若用户希望仅对倒排索引进行操作，那么可以通过参数innodb_optimize_fulltext_only进行设置，如：

mysql＞SET GLOBAL innodb_optimize_fulltext_only=1;
mysql＞OPTIMIZE TABLEfts_a;

若被删除的文档非常多，那么OPTIMIZE TABLE操作可能需要占用非常多的时间，这会影响应用程序的并发性，并极大地降低用户的响应时间。用户可以通过参数innodb_ft_num_word_optimize来限制每次实际删除的分词数量。该参数的默认值为2000。

下面来看一个具体的例子，首先通过如下代码创建表fts_a：

CREATE TABLE fts_a(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body TEXT,
PRIMARY KEY(FTS_DOC_ID)
);
INSERT INTO fts_a
SELECT NULL,'Pease porridge in the pot';
INSERT INTO fts_a
SELECT NULL,'Pease porridge hot,pease porridge cold';
INSERT INTO fts_a
SELECT NULL,'Nine days old';
INSERT INTO fts_a
SELECT NULL,'Some like it hot,some like it cold';
INSERT INTO fts_a
SELECT NULL,'Some like it in the pot';
INSERT INTO fts_a
SELECT NULL,'Nine days old';
INSERT INTO fts_a
SELECT NULL,'I like code days';
CREATE FULLTEXT INDEX idx_fts ON fts_a(body);

上述代码创建了表fts_a，由于body字段是进行全文检索的字段，因此创建一个类型为FULLTEXT的索引。这里首先导入数据，然后再进行倒排索引的创建，这也是比较推荐的一种方式。创建完成后观察到表fts_a中的数据：

mysql＞SELECT*FROM fts_a;
+------------+-----------------------------------------+
|FTS_DOC_ID|body|
+------------+-----------------------------------------+
|1|Pease porridge in the pot|
|2|Pease porridge hot,pease porridge cold|
|3|Nine days old|
|4|Some like it hot,some like it cold|
|5|Some like it in the pot|
|6|Nine days old|
|7|I like code days|
+------------+-----------------------------------------+
7 rows in set(0.00 sec)
通过设置参数innodb_ft_aux_table来查看分词对应的信息：
mysql＞SET GLOBAL innodb_ft_aux_table='test/fts_a';
Query OK,0 rows affected(0.00 sec)
mysql＞SELECT*FROM information_schema.INNODB_FT_INDEX_TABLE;
+----------+--------------+-------------+-----------+--------+----------+
|WORD|FIRST_DOC_ID|LAST_DOC_ID|DOC_COUNT|DOC_ID|POSITION|
+----------+--------------+-------------+-----------+--------+----------+
|code|7|7|1|7|7|
|cold|2|4|2|2|35|
|cold|2|4|2|4|31|
|days|3|7|3|3|5|
|days|3|7|3|6|5|
|days|3|7|3|7|12|
|hot|2|4|2|2|15|
|hot|2|4|2|4|13|
|like|4|7|3|4|5|
|like|4|7|3|4|18|
|like|4|7|3|5|5|
|like|4|7|3|7|2|
|nine|3|6|2|3|0|
|nine|3|6|2|6|0|
|old|3|6|2|3|10|
|old|3|6|2|6|10|
|pease|1|2|2|1|0|
|pease|1|2|2|2|0|
|pease|1|2|2|2|20|
|porridge|1|2|2|1|6|
|porridge|1|2|2|2|6|
|porridge|1|2|2|2|20|
|pot|1|5|2|1|22|
|pot|1|5|2|5|20|
|some|4|5|2|4|0|
|some|4|5|2|4|18|
|some|4|5|2|5|0|
+----------+--------------+-------------+-----------+--------+----------+
27 rows in set(0.00 sec)

可以看到每个word都对应了一个DOC_ID和POSITION。此外，还记录了FIRST_DOC_ID、LAST_DOC_ID以及DOC_COUNT，分别代表了该word第一次出现的文档ID，最后一次出现的文档ID，以及该word在多少个文档中存在。
若这时执行下面的SQL语句，会删除FTS_DOC_ID为7的文档：

mysql＞DELETE FROM test.fts_a WHERE FTS_DOC_ID=7;
Query OK,1 row affected(0.00 sec)

由于之前的介绍，InnoDB存储引擎并不会直接删除索引中对应的记录，而是将删除的文档ID插入到DELETED表，因此用户可以进行如下的查询：

mysql＞SELECT*FROM INNODB_FT_DELETED;
+--------+
|DOC_ID|
+--------+
|7|
+--------+
1 row in set(0.00 sec)

可以看到删除的文档ID插入到了表INNODB_FT_DELETED中，若用户想要彻底删除倒排索引中该文档的分词信息，那么可以运行如下的SQL语句：

mysql＞SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK,0 rows affected(0.00 sec)
mysql＞OPTIMIZE TABLE test.fts_a;
+------------+----------+----------+----------+
|Table|Op|Msg_type|Msg_text|
+------------+----------+----------+----------+
|test.fts_a|optimize|status|OK|
+------------+----------+----------+----------+
1 row in set(0.01 sec)
mysql＞SELECT*FROM INNODB_FT_DELETED;
+--------+
|DOC_ID|
+--------+
|7|
+--------+
1 row in set(0.00 sec)
mysql＞SELECT*FROM INNODB_FT_BEING_DELETED;
+--------+
|DOC_ID|
+--------+
|7|
+--------+
1 row in set(0.00 sec)

通过上面的例子可以看到，运行命令OPTIMIZE TABLE可将记录进行彻底的删除，并且彻底删除的文档ID会记录到表INNODB_FT_BEING_DELETED中。此外，由于7这个文档ID已经被删除，因此不允许再次插入这个文档ID，否则数据库会抛出如下异常：

mysql＞INSERT INTO test.fts_a SELECT 7,'I like this days';
ERROR 182(HY000):Invalid InnoDB FTS Doc ID

stopword列表（stopword list）是本小节最后阐述的一个概念，其表示该列表中的word不需要对其进行索引分词操作。例如，对于the这个单词，由于其不具有具体的意义，因此将其视为stopword。InnoDB存储引擎有一张默认的stopword列表，其在information_schema架构下，表名为INNODB_FT_DEFAULT_STOPWORD，默认共有36个stopword。此外用户也可以通过参数innodb_ft_server_stopword_table来自定义stopword列表。如：

mysql＞CREATE TABLE user_stopword(
-＞value VARCHAR(30)
-＞)ENGINE=INNODB;
Query OK,0 rows affected(0.03 sec)
mysql＞SET GLOBAL
-＞innodb_ft_server_stopword_table="test/user_stopword";
Query OK,0 rows affected(0.00 sec)

当前InnoDB存储引擎的全文检索还存在以下的限制：
❑每张表只能有一个全文检索的索引。
❑由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。
❑不支持没有单词界定符（delimiter）的语言，如中文、日语、韩语等。

5.8.4　全文检索

MySQL数据库支持全文检索（Full-Text Search）的查询，其语法为：

MATCH(col1,col2,...)AGAINST(expr[search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE MODE
|IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
|IN BOOLEAN MODE
|WITH QUERY EXPANSION
}

MySQL数据库通过MATCH()…AGAINST()语法支持全文检索的查询，MATCH指定了需要被查询的列，AGAINST指定了使用何种方法去进行查询。下面将对各种查询模式进行详细的介绍。

1.Natural Language

全文检索通过MATCH函数进行查询，默认采用Natural Language模式，其表示查询带有指定word的文档。对于5.8.3小节中创建的表fts_a，查询body字段中带有Pease的文档，若不使用全文索引技术，则允许使用下述SQL语句：
mysql＞SELECT*FROM fts_a WHERE body LIKE’%Pease%';
显然上述SQL语句不能使用B+树索引。若采用全文检索技术，可以用下面的SQL语句进行查询：

mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)
-＞AGAINST('Porridge'IN NATURAL LANGUAGE MODE);
+------------+-----------------------------------------+
|FTS_DOC_ID|body|
+------------+-----------------------------------------+
|2|Pease porridge hot,pease porridge cold|
|1|Pease porridge in the pot|
+------------+-----------------------------------------+
2 rows in set(0.00 sec)

由于NATURAL LANGUAGE MODE是默认的全文检索查询模式，因此用户可以省略查询修饰符，即上述SQL语句可以写为：

SELECT*FROM fts_a WHERE MATCH(body)AGAINST('Porridge');

观察上述SQL语句的查询计划，可得：

mysql＞EXPLAIN SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('Porridge')\G;
***************************1.row***************************
id:1
select_type:SIMPLE
table:fts_a
type:fulltext
possible_keys:idx_fts
key:idx_fts
key_len:0
ref:NULL
rows:1
Extra:Using where
1 row in set(0.00 sec)

可以看到，在type这列显示了fulltext，即表示使用全文检索的倒排索引，而key这列显示了idx_fts，表示索引的名字。可见上述查询使用了全文检索技术。同时，若表没有创建倒排索引，则执行MATCH函数会抛出类似如下错误：

mysql＞SELECT*FROM fts_b
-＞WHERE MATCH(body)AGAINST('Porridge');
ERROR 1191(HY000):Can't find FULLTEXT index matching the column list

在WHERE条件中使用MATCH函数，查询返回的结果是根据相关性（Relevance）进行降序排序的，即相关性最高的结果放在第一位。相关性的值是一个非负的浮点数字，0表示没有任何的相关性。根据MySQL官方的文档可知，其相关性的计算依据以下四个条件：
❑word是否在文档中出现。
❑word在文档中出现的次数。
❑word在索引列中的数量。
❑多少个文档包含该word。

对于上述查询，由于Porridge在文档2中出现了两次，因而具有更高的相关性，故第一个显示。
为了统计MATCH函数得到的结果数量，可以使用下列SQL语句：

mysql＞SELECT count(*)
-＞FROM fts_a WHERE
-＞MATCH(body)AGAINST('Porridge'IN NATURAL LANGUAGE MODE);
+-------------------+
|count(FTS_DOC_ID)|
+-------------------+
|2|
+-------------------+
1 row in set(0.00 sec)
上述SQL语句也可以重写为：
mysql＞SELECT
-＞COUNT(IF(MATCH(body)
-＞AGAINST('Porridge'IN NATURAL LANGUAGE MODE),1,NULL))
-＞AS count
-＞FROM fts_a;
+-------+
|count|
+-------+
|2|
+-------+
1 row in set(0.00 sec)

上述两句SQL语句虽然得到的逻辑结果是相同的，但是从内部运行来看，第二句SQL的执行速度可能更快些。这是因为第一句SQL语句还需要进行相关性的排序统计，而在第二句SQL中是不需要的。
此外，用户可以通过SQL语句查看相关性：

mysql＞SELECT fts_doc_id,body,
-＞MATCH(body)AGAINST('Porridge'IN NATURAL LANGUAGE MODE)
-＞AS Relevance
-＞FROM fts_a;
+------------+-----------------------------------------+--------------------+
|fts_doc_id|body|Relevance|
+------------+-----------------------------------------+--------------------+
|1|Pease porridge in the pot|0.2960100471973419|
|2|Pease porridge hot,pease porridge cold|0.5920200943946838|
|3|Nine days old|0|
|4|Some like it hot,some like it cold|0|
|5|Some like it in the pot|0|
|6|Nine days old|0|
|7|I like hot and code days|0|
+------------+-----------------------------------------+--------------------+
7 rows in set(0.01 sec)

对于InnoDB存储引擎的全文检索，还需要考虑以下的因素：
❑查询的word在stopword列中，忽略该字符串的查询。
❑查询的word的字符长度是否在区间[innodb_ft_min_token_size，innodb_ft_max_token_size]内。

如果词在stopword中，则不对该词进行查询，如对the这个词进行查询，结果如下所示：

mysql＞SELECT fts_doc_id AS id,body,
-＞MATCH(body)AGAINST('the'IN NATURAL LANGUAGE MODE)
-＞AS rl
-＞FROM fts_a;
+----+-----------------------------------------+------+
|id|body|rl|
+----+-----------------------------------------+------+
|1|Pease porridge in the pot|0|
|2|Pease porridge hot,pease porridge cold|0|
|3|Nine days old|0|
|4|Some like it hot,some like it cold|0|
|5|Some like it in the pot|0|
|6|Nine days old|0|
|7|I like hot and code days|0|
+----+-----------------------------------------+------+
7 rows in set(0.00 sec)

可以看到，the虽然在文档1、5中出现，但由于其是stopword，故其相关性为0。

参数innodb_ft_min_token_size和innodb_ft_max_token_size控制InnoDB存储引擎查询字符的长度，当长度小于innodb_ft_min_token_size，或者长度大于innodb_ft_max_token_size时，会忽略该词的搜索。在InnoDB存储引擎中，参数innodb_ft_min_token_size的默认值为3，参数innodb_ft_max_token_size的默认值为84。

2.Boolean

MySQL数据库允许使用IN BOOLEAN MODE修饰符来进行全文检索。当使用该修饰符时，查询字符串的前后字符会有特殊的含义，例如下面的语句要求查询有字符串Pease但没有hot的文档，其中+和-分别表示这个单词必须出现，或者一定不存在。

mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('+Pease-hot'IN BOOLEAN MODE)\G;
***************************1.row***************************
FTS_DOC_ID:1
body:Pease porridge in the pot

Boolean全文检索支持以下几种操作符：
❑+表示该word必须存在。
❑-表示该word必须被排除。
❑（no operator）表示该word是可选的，但是如果出现，其相关性会更高
❑@distance表示查询的多个单词之间的距离是否在distance之内，distance的单位是字节。这种全文检索的查询也称为Proximity Search。如MATCH（body）AGAINST（'“Pease pot”@30’IN BOOLEAN MODE）表示字符串Pease和pot之间的距离需在30字节内。
❑＞表示出现该单词时增加相关性。
❑＜表示出现该单词时降低相关性。
❑～表示允许出现该单词，但是出现时相关性为负（全文检索查询允许负相关性）。
❑表示以该单词开头的单词，如lik，表示可以是lik、like，又或者likes。
❑"表示短语。

接着将根据上述的操作符及之前创建的表fts_a来进行具体的介绍。下面的SQL语句返回有pease又有hot的文档：

mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('+Pease+hot'IN BOOLEAN MODE)\G;
***************************1.row***************************
FTS_DOC_ID:2
body:Pease porridge hot,pease porridge cold
1 row in set(0.00 sec)

下面的SQL语句返回有pease但没有hot的文档：

mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('+Pease-hot'IN BOOLEAN MODE)\G;
***************************1.row***************************
FTS_DOC_ID:1
body:Pease porridge in the pot
1 row in set(0.00 sec)
下面的SQL语句返回有pease或有hot的文档：
mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('Pease hot'IN BOOLEAN MODE);
+------------+-----------------------------------------+
|FTS_DOC_ID|body|
+------------+-----------------------------------------+
|2|Pease porridge hot,pease porridge cold|
|1|Pease porridge in the pot|
|4|Some like it hot,some like it cold|
|7|I like hot and code days|
+------------+-----------------------------------------+
4 rows in set(0.00 sec)

下面的SQL语句进行Proximity Search：

mysql＞SELECT fts_doc_id,body FROM fts_a
-＞WHERE MATCH(body)
-＞AGAINST('"Pease pot"@30'IN BOOLEAN MODE)\G;
***************************1.row***************************
fts_doc_id:1
body:Pease porridge in the pot
1 row in set(0.01 sec)
mysql＞SELECT fts_doc_id,body FROM fts_a
-＞WHERE MATCH(body)
-＞AGAINST('"Pease pot"@10'IN BOOLEAN MODE);
Empty set(0.01 sec)

可以看到文档1中单词Pease和pot的距离为22字节，因此第一条@30的查询可以返回结果，而之后@10的条件不能返回任何结果。如：

mysql＞SELECT fts_doc_id,body,
-＞MATCH(body)AGAINST('like＞pot'IN BOOLEAN MODE)
-＞AS Relevance FROM fts_a;
+------------+-----------------------------------------+---------------------+
|fts_doc_id|body|Relevance|
+------------+-----------------------------------------+---------------------+
|1|Pease porridge in the pot|1.2960100173950195|
|2|Pease porridge hot,pease porridge cold|0|
|3|Nine days old|0|
|4|Some like it hot,some like it cold|0.27081382274627686|
|5|Some like it in the pot|1.4314169883728027|
|6|Nine days old|0|
|7|I like hot and code days|0.13540691137313843|
+------------+-----------------------------------------+---------------------+
7 rows in set(0.00 sec)

上述SQL语句查询根据是否有单词like或pot进行相关性统计，并且出现单词pot后相关性需要增加。文档4虽然出现两个like单词，但是没有pot，因此相关性没有文档1和5高。
下面的查询增加了“＜some”的条件，最后得到的结果：

mysql＞SELECT fts_doc_id,body,
-＞MATCH(body)AGAINST('like＞hot＜some'IN BOOLEAN MODE)
-＞AS Relevance
-＞FROM fts_a;
+------------+-----------------------------------------+---------------------+
|fts_doc_id|body|Relevance|
+------------+-----------------------------------------+---------------------+
|1|Pease porridge in the pot|0|
|2|Pease porridge hot,pease porridge cold|1.2960100173950195|
|3|Nine days old|0|
|4|Some like it hot,some like it cold|1.158843994140625|
|5|Some like it in the pot|-0.5685830116271973|
|6|Nine days old|0|
|7|I like hot and code days|0.13540691137313843|
+------------+-----------------------------------------+---------------------+
7 rows in set(0.00 sec)

可以发现文档5的相关性变为了负，这是因为虽然其中存在like单词，但是也存在some单词，所以根据查询条件，其相关性变为了负相关。
接着来看下面的SQL语句：

mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('po*'IN BOOLEAN MODE);
+------------+-----------------------------------------+
|FTS_DOC_ID|body|
+------------+-----------------------------------------+
|2|Pease porridge hot,pease porridge cold|
|1|Pease porridge in the pot|
|5|Some like it in the pot|
+------------+-----------------------------------------+
3 rows in set(0.00 sec)

可以看到最后结果中的文档包含以po开头的单词，如porridge，pot。
最后是关于短语的SQL查询，如：

mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('like hot'IN BOOLEAN MODE);
+------------+-----------------------------------------+
|FTS_DOC_ID|body|
+------------+-----------------------------------------+
|4|Some like it hot,some like it cold|
|7|I like hot and code days|
|2|Pease porridge hot,pease porridge cold|
|5|Some like it in the pot|
+------------+-----------------------------------------+
4 rows in set(0.00 sec)
mysql＞SELECT*FROM fts_a
-＞WHERE MATCH(body)AGAINST('"like hot"'IN BOOLEAN MODE);
+------------+---------------------------+
|FTS_DOC_ID|body|
+------------+---------------------------+
|7|I like hot and code days|
+------------+---------------------------+
1 row in set(0.00 sec)

可以看到第一条SQL语句没有使用"“将like和hot视为一个短语，而只是将其视为两个单词，因此结果共返回4个文档。而第二条SQL语句使用"like hot”，因此查询的是短语，故仅文档4符合查询条件。

3.Query Expansion

MySQL数据库还支持全文检索的扩展查询。这种查询通常在查询的关键词太短，用户需要implied knowledge（隐含知识）时进行。例如，对于单词database的查询，用户可能希望查询的不仅仅是包含database的文档，可能还指那些包含MySQL、Oracle、DB2、RDBMS的单词。而这时可以使用Query Expansion模式来开启全文检索的implied knowledge。

通过在查询短语中添加WITH QUERY EXPANSION或IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION可以开启blind query expansion（又称为automatic relevance feedback）。该查询分为两个阶段。
❑第一阶段：根据搜索的单词进行全文索引查询。
❑第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询。

接着来看一个具体的例子，首先根据如下代码创建测试表articles：

CREATE TABLE articles(
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT(title,body)
)ENGINE=InnoDB;
INSERT INTO articles(title,body)VALUES
('MySQL Tutorial','DBMS stands for DataBase...'),
('How To Use MySQL Well','After you went through a...'),
('Optimizing MySQL','In this tutorial we will show...'),
('1001 MySQL Tricks','1.Never run mysqld as root.2....'),
('MySQL vs.YourSQL','In the following database comparison...'),
('MySQL Security','When configured properly,MySQL...'),
('Tuning DB2','For IBM database...'),
('IBM History','DB2 hitory for IBM...');

在这个例子中，并没有显示创建FTS_DOC_ID列，因此InnoDB存储引擎会自动建立该列，并添加唯一索引。此外，表articles的全文检索索引是根据列title和body的联合索引。接着根据database关键字进行的全文检索查询。

mysql＞SELECT*FROM articles
-＞WHERE MATCH(title,body)
-＞AGAINST('database'IN NATURAL LANGUAGE MODE);
+----+-------------------+------------------------------------------+
|id|title|body|
+----+-------------------+------------------------------------------+
|1|MySQL Tutorial|DBMS stands for DataBase...|
|5|MySQL vs.YourSQL|In the following database comparison...|
|7|Tuning DB2|For IBM database...|
+----+-------------------+------------------------------------------+
3 rows in set(0.00 sec)

可以看到，查询返回了3条记录，body字段包含database关键字。接着开启Query Expansion，观察最后得到的结果如下所示：

mysql＞SELECT*FROM articles
-＞WHERE MATCH(title,body)
-＞AGAINST('database'WITH QUERY EXPANSION);
+----+-----------------------+------------------------------------------+
|id|title|body|
+----+-----------------------+------------------------------------------+
|5|MySQL vs.YourSQL|In the following database comparison...|
|1|MySQL Tutorial|DBMS stands for DataBase...|
|7|Tuning DB2|For IBM database...|
|8|IBM History|DB2 hitory for IBM...|
|3|Optimizing MySQL|In this tutorial we will show...|
|6|MySQL Security|When configured properly,MySQL...|
|2|How To Use MySQL Well|After you went through a...|
|4|1001 MySQL Tricks|1.Never run mysqld as root.2....|
+----+-----------------------+------------------------------------------+
8 rows in set(0.00 sec)

可以看到最后得到8条结果，除了之前包含database的记录，也有包含title或body字段中包含MySQL、DB2的文档。这就是Query Expansion。

由于Query Expansion的全文检索可能带来许多非相关性的查询，因此在使用时，用户可能需要非常谨慎。

5.9　小结

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