5.2 InnoDB存储引擎之索引与算法（B+树索引的使用、哈希算法、全文检索）

最新推荐文章于 2023-11-22 14:09:00 发布

土拨鼠饲养员

最新推荐文章于 2023-11-22 14:09:00 发布

阅读量180

点赞数

分类专栏： # mysql存储文章标签：算法全文检索数据库

本文链接：https://blog.csdn.net/lxsxkf/article/details/120547755

版权

mysql存储专栏收录该内容

20 篇文章 0 订阅

订阅专栏

5.6 B+树索引的使用

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

需要根据自己的具体生产环境来使用索引，并观察索引使用的情况，判断是否需要添加索引。不要盲从任何人给你的经验意见，Think Different。
在 OLTP应用中，查询操作只从数据库中取得小部分数据，一般可能都在 10条记录以下，甚至在很多时候只取1条记录，如根据主键值来取得用户信息，根据订单号取得订单的详细信息，这都是典型 OLTP应用的查询语句。在这种情况下，B+ 树索引建立后，对该索引的使用应该只是通过该索引取得表中少部分的数据。这时建立B+树索引才是有意义的，否则即使建立了，优化器也可能选择不使用索引。
在 OLAP应用中，都需要访问表中大量的数据，根据这些数据来产生查询的结果，这些查询多是面向分析的香询，目的是为决策者提供支持。如这个月每个用户的消费情况，销售额同比、环比增长的情况。因此在 OLAP中索引的添加根据的应该是宏观的信息，而不是微观，因为最终要得到的结果是提供给决策者的。例如不需要在 OLAP中对姓名字段进行索引，因为很少需要对单个用户进行查询。但是对于OLAP 中的复杂查询，要涉及多张表之间的联接操作，因此索引的添加依然是有意义的。但是，如果联接操作使用的是 Hash Join，那么索引可能又变得不是非常重要了，所以这需要 DBA 或开发人员认真并仔细地研究自己的应用。不过在 OLAP应用中，通常会需要对时间字段进行索引，这是因为大多数统计需要根据时间维度来进行数据的筛选。

5.6.2 联合索引

联合索引是指对表上的多个列进行索引。前面讨论的情况都是只对表上的一个列进行索引。联合索引的创建方法与单个索引创建的方法一样，不同之处仅在于有多个索引列。

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

则优化器的选择为 (userid 和 userid,buy_date ) 索引

possible keys在这里有两个索引可供使用，分别是单个的userid索引和（userid，buy date）的联合索引。但是优化器最终的选择是索引| userid，因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多。
接着假定要取出 userid为1的最近3次的购买记录，其 SOL语句如下

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

这里优化器也可以选择 (userid 和 userid,buy_date ) 索引。但是这次优化器使用了（userid，buy_date）的联合索引userid 2，因为在这个联合索引中 buy_date 已经排序好了。根据该联合索引取出数据，无须再对 buy_date 做一次额外的排序操作。若强制使用userid索引，则执行计划会出现 Using filesort，会产生额外的一次排序。
正如前面所介绍的那样，联合索引（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存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（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操作，故优化器的选择为：
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 查询是统计操作，并且可以利用到覆盖索引的信息。因此优化器会选择该联合索引。其执行计划如：
可以发现列possible keys依然为NULL，但是列key为userid_2，即表示（userid，buy_date）的联合索引。在列 Extra同样可以发现Using index提示，表示为覆盖索引。

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

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

	SELECT * FROM orderdetails
	WHERE orderid>10000 and orderid<102000;

表 orderdetails有（orderid，productid）的联合主键，此外还有对于列orderid的单个索引。然而通过 EXPLAIN命令，用户会发现优化器并没有按照 orderid上的索引来查找数据。
在 possible_keys 一列可以看到查询可以使用PRIMARY、OrderID、OrdersOrder_Details 三个索引，但是在最后的索引使用中，优化器选择了PRIMARY聚集索引，也就是表扫描（table scan），而非 OrderID辅助索引扫描（index scan）。
这是为什么呢?原因在于用户要选取的数据是整行信息，而OrderID索引不能覆盖到我们要查询的信息，因此在对 OrderID索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然 OrderID索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是 20%左右），优化器会选择通过聚集索引来查找数据。因为之前已经提到过，顺序读要远远快于离散读。
因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字 FORCE INDEX来强制使用某个索引，如∶

5.6.5 索引提示

MySQL数据库支持索引提示（INDEX HINT），显式地告诉优化器使用哪个索引。个人总结以下两种情况可能需要用到INDEX HINT∶

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

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

	INSERT INTO t SELECT 1,1;
	INSERT INTO t SELECT 1,2;
	INSERT INTO t SELECT 2,3;
	INSERT INTO t SELECT 2,4;
	INSERT INTO t SELECT 1,2;

	explain select * from t where a=1 and b =2;

在这里插入图片描述

possible_keys显示了上述 SQL语句可使用的索引为a，b，而实际使用的索引为列 key 所示，同样为a，b。也就是 MySQL 数据库使用a，b两个索引来完成这一个查询。列Extra提示的Using intersect（b，a）表示根据两个索引得到的结果进行求交的数学运算，最后得到结果。
如果我们使用USE INDEX的索引提示来使用 a这个索引，如∶

	SELECT * FROM t USE INDEX(a) WHERE a=1 AND b = 2;

在这里插入图片描述

可以看到，虽然我们指定使用a索引，但是优化器实际选择的是通过表扫描的方式。因此，USE INDEX 只是告诉优化器可以选择该索引，实际上优化器还是会再根据自己的判断进行选择。而如果使用 FORCE INDEX 的索引提示，如∶

可以看到，这时优化器的最终选择和用户指定的索引是一致的。因此，如果用户确定指定某个索引来完成查询，那么最可靠的是使用FORCE INDEX，而不是USE INDEX。

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的排序顺序来访问实际的数据文件。

此外，若InnoDB存储引擎或者 MyISAM存储引擎的缓冲池不是足够大，即不能存放下一张表中的所有数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池。若是按照主键顺序进行访问，则可以将此重复行为降为最低。如下面这句 SQL语句∶
SELECT * FROM salaries WHERE salary>10000 AND salary<40000;
salary 上有一个辅助索引idx_s，因此除了通过辅助索引查找键值外，还需要通过书签查找来进行对整行数据的查询。若启用Mulit-Range Read特性，则除了会在列Extra看到Using index condition外，还会看见 Using MRR选项，如图5-35所示。
而在实际的执行中会体会到两个的执行时间差别非常巨大，如表 5-4所示。
此外，Multi-Range Read还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据，例如∶

	SELECT * FROM t  
	WHERE key_part1 >= 1000 AND key_part1 < 2000 AND key_part2 = 10000;

表t有（key_part1，key_part2）的联合索引，因此索引根据key_part1，key_part2的位置关系进行排序。若没有 Multi-Read Range，此时查询类型为Range，SQL优化器会先将 key_part1大于1000且小于2000的数据都取出，即使 key_part2不等于1000。待取出行数据后再根据 key_part2的条件进行过滤。这会导致无用数据被取出。如果有大量的数据且其 key_part2不等于1000，则启用Mulit-Range Read优化会使性能有巨大的提升。倘若启用了Multi-Range Read优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会将查询条件拆分为（1000，1000），（1001，1000），（1002，1000），…，（1999，1000），最后再根据这些拆分出的条件进行数据的查询。
可以来看一个实际的例子，查询如下∶

	SELECT * FROM salaries
	WHERE(from_date between '1986-01-01'AND '1995-01-01')AND (salary between 38000 and 40000);

若启用了Multi-Range Read优化，则执行计划如图5-36所示。
在这里插入图片描述

表 salaries上有对于salary的索引 idx_s，在执行上述SOL语句时，因为启用了Multi-Range Read优化，所以会对查询条件进行拆分，这样在列 Extra 中可以看到Using MRR选项。
是否启用Multi-Range Read优化可以通过参数optimizer switch中的标记（flag）来控制。当mrr为on 时，表示启用 Multi-Range Read优化。mrr_cost_based标记表示是否通过cost based 的方式来选择是否启用mrr。若将mrr设为 on，mrr cost based设为off，则总是启用Multi-Range Read 优化。例如，下述语句可以将 Multi-Range Read优化总是设为开启状态∶

		mysql> SET @@optimizer_switch='mrr=on,mrr_cost_based=off';
		Query OK,0 rows affected (0.00 sec)

参数read_rnd_buffer_size 用来控制键值的缓冲区大小，当大于该值时，则执行器对已经缓存的数据根据RowID进行排序，并通过RowID来取得行数据。该值默认为256K:
在这里插入图片描述

5.6.7 Index Condition Pushdown（ICP）优化

和 Multi-Range Read一样，Index Condition Pushdown 同样是MySQL 5.6开始支持的一种根据索引进行查询的优化方式。之前的 MySQL数据库版本不支持Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据WHERE 条件来过滤记录。在支持Index Condition Pushdown后，MySQL数据库会在取出索引的同时，判断是否可以进行 WHERE 条件的过滤，也就是将 WHERE 的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层 SQL 层对记录的索取（fetch），从而提高数据库的整体性能。
Index Condition Pushdown 优化支持range、ref、eq_ref、ref_or_null类型的查询，当前支持 MyISAM和InnoDB存储引擎。当优化器选择Index Condition Pushdown优化时，可在执行计划的列 Extra 看到 Using index condition 提示。
假设某张表有联合索引（zip_code，last_name，first_name），并且查询语句如下∶

		SELECT * FROM people
		WHERE zipcode=' 95054'
		AND last_nameLIKE '%etrunia%' AND address LIKE '%Main Street%';

对于上述语句，MySQL 数据库可以通过索引来定位zipcode 等于95054的记录，但是索引对 WHERE条件的 last_nameLIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’; 没有任何帮助。若不支持 Index Condition Pushdown 优化，则数据库需要先通过索引取出所有 zipcode等于95054的记录，然后再过滤WHERE 之后的两个条件。
若支持Index Condition Pushdown 优化，则在索引取出时，就会进行WHERE条件的过滤，然后再去获取记录。这将极大地提高查询的效率。当然，WHERE 可以过滤的条件是要该索引可以覆盖到的范围。来看下面的 SQL 语句∶

		SELECT* FROM salaries
		WHERE(from_date between'	1986-01-01'	AND  '1995-01-01')AND (salary between 38000 and 40000);

在这里插入图片描述
可以看到列Extra有Using index condition的提示。但是为什么这里的idx_s索引会使用Index Condition Pushdown优化呢?因为这张表的主键是（emp_no，from_date）的联合索引，所以 idx_s索引中包含了from_date 的数据，故可使用此优化方式。
表5-5对比了在 MySQL 5.5和 MySQL5.6中上述 SQL语句的执行时间，并且同时比较开启 MRR后的执行时间。
在这里插入图片描述

5.7 哈希算法

哈希算法是一种常见算法，时间复杂度为 O（1），且不只存在于索引中，每个数据库应用中都存在该数据库结构。设想一个问题，当前服务器的内存为128GB时，用户怎么从内存中得到某一个被缓存的页呢?虽然内存中查询速度很快，但是也不可能每次都要遍历所有内存来进行查找，这时对于字典操作只需 O（1）的哈希算法就有了很好的用武之地。

5.7.1 哈希表

直接寻址表在一个少量数据中比较有效，哈希表可以通过关键字计算出槽的位置，如果两个位置映射到了同一个槽，就会形成一个链表。

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 可以看到当前自适应哈希索引的使用状况，如∶
在这里插入图片描述

注意的是，哈希索引只能用来搜索等值的查询，如

	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）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。
从InnoDB 1.2.x 版本开始，InnoDB存储引擎开始支持全文检索，其支持 MyISAM 存储引擎的全部功能，并且还支持其他的一些特性，这些将在后面的小节中进行介绍。

5.8.2 倒排索引

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

inverted file index，其表现形式为 {单词，单词所在文档的 ID}
fullinverted index，其表现形式为 {单词，（单词所在文档的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∶

在这里插入图片描述

对于其他数据库，如 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 中。增大该参数可以提高全文检索的性能，但是在宕机时，未同步到磁盘中的索引信息可能需要更长的时间进行恢复。

…P246-253先放着