MySQL(InnoDB剖析):---全文检索（倒排索引、全文索引/全文检索）

一、引言

演示说明

• 之前已经讲解过相关内容。
• B+树索引的一个显著特点是基于索引字段前缀进行查找。
• 该系统对于查询中的B+树结构支持良好。

>     select * from blog where content like 'xxx%';

在某些情况下, 我们需要检索博客内容中涉及'xxx'的部分而非以'xxx'作为起始. 因此应为以下所述的SQL语句

>     select * from blog where content like '%xxx%';

由此可见，在现有SQL语句中即使增添了B+树索引也依然需要通过执行索引查找操作来获取结果

• 全文检索（Full-Text Search） 是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、词等信息，也可以进行各种统计和分析
• MySQL全文检索历史发展 ： * **在之前的MySQL数据库中，InnoDB不支持全文检索技术。 ，**只有MyISAM存储引擎支持。大多数的用户转向MyISAM存储引擎，还可能需要进行表的拆分，并将需要进行全文检索的数据存储为MyISAM表。这样的确能够解决需求，但是却丧失了InnoDB存储引擎的事务性
  • **从InnoDB 1.2.x版本开始，InnoDB开始支持全文检索 ，**其支持MyISAM存储引擎的全部功能，并且还支持其他一些特性

二、倒排索引

• 在信息检索中，通常采用倒排索引技术来实现搜索功能。
• 倒排索引与B树结构类似地作为一种高效的索引方法
• 该方法通过辅助表记录每个单词与其在多个文档中的具体位置
• 这种数据结构通常由关键数组支持
• 倒排索引包含两种主要类型：

  • 倒置文件指针：以{单词, 文档ID}的形式呈现

  • 全面的倒排索引则进一步细化记录每个单词的具体位置信息

  • 例如下面是一张表t。其中有DocumentId、Text两个字段

①inverted file index

• **如果采用inverted file index的关联数组实现 ，**则其存储的内容如下表所示，可以看到：
  • 单词code存在于文档1和4中
  • 单词days存在于文档3和6中
  • 等等......

一旦完成任务流程设置 ，就能实现对 Documents 中包含查询关键字的文档的快速检索

②full inverted index

• **如果采用full inverted index的关联数组实现 ，**则其存储的内容如下表所示，可以看到：
  • code单词为（1:6），（4,8）：表示code这个单词出现在文档1的第6个单词处，和文档4的第8个单词处
  • 等等......

该索引不仅包含逆向索引的结构特征，并且记录了每个单词在文本中的具体位置信息。
然而由于这种设计特性带来的额外存储需求。
从而能够更精准地定位所需数据内容，并支持一些额外的检索功能。

②full inverted index

• 如果采用full inverted index的关联数组实现， 则其存储的内容如下表所示，可以看到：
  • code单词为（1:6），（4,8）：表示code这个单词出现在文档1的第6个单词处，和文档4的第8个单词处
  • 等等......

• full inverted index不仅实现了对单词位置信息的存储，而且通过这一设计实现了以下功能：尽管其占用更多的存储空间（memory），但这种设计能够更加精准地定位数据内容，并为用户提供更为丰富的搜索功能。

三、InnoDB全文检索的实现

• 自1.2.x版本起InnoDB引入了全词索引技术（full word indexing），该技术通过构建"全词倒排索引"（full inverted index）实现高效的全文检索功能。
• 在InnoDB存储引擎中，默认情况下将（DocumentId,Position）字段对视为一个列表列（ilist）。
• 在设计专门用于全文检索功能的支持表（辅助表）中有两个关键字段：
  • word字段: 该字段具有专门设计的索引以支持快速查询
  • ilist字段: 该字段用于存储多个文档的位置信息
• 此外，在InnoDB存储引擎中由于其特殊的 ilist字段设计特性使得其支持实现邻近词搜索功能（Proximity Search）, 而MyISAM等其他存储引擎不具备这一特性
• 当前InnoDB系统中的全文检索索引设计存在以下限制条件：
  • 每个文档表最多只能创建一个全文检索类型的索引
  • 构建多字段组合式的全文检索索引时必须遵循相同的字符编码和排序标准
  • 支持语言范围受限, 即无法处理无分隔符语言种的语言文本

Auxiliary Table（辅助表）

• 根据前面所述的内容，在InnoDB存储引擎中建立一个用于存储单词word的表格 ，该表格被命名为Auxiliary Table（辅助表）
• 为了提升全文检索的并行性能，在InnoDB存储引擎中共有6个辅助表 ，目前每张表格都是根据单词的拉丁码对单词进行分区
• 辅助表是一种持久化的存储结构 ，其数据会直接保存在磁盘上

innodb_ft_aux_table参数（辅助表的查看）

• InnoDB允许用户查看指定倒排索引的Auxiliary Table中分词的信息，可以通过这个参数来观察倒排索引的Auxiliary Table
• 例如下面的SQL语句设置查看test数据库下表fts_a的Auxiliary Table：

• 例如下面的SQL语句设置查看test数据库下表fts_a的Auxiliary Table：

>     set global innodb_ft_aux_table='test/fts_a';

设置完成后, 即可通过查看information_schema数据库下的innodb_ft_index_table表来获取表fts_a中的分词索引信息

FTS Index Cache（全文检索索引缓存）

• 然而在InnoDB存储引擎的全文索引中，还有另外一个重要的概念**FTS Index Cache（全文检索索引缓存 ），**其用来提高全文检索的性能
• FTS Index Cache是一个红黑树结构 ，其根据（word，ilist）进行排序
• 这意味着插入的数据已经更新了对应的表，但是 对全文索引的更新可能在分词操作后还在FTS Index Cache中，Auxiliary Table可能还没有更新
• FTS Index Cache的更新：
  • InnoDB会批量对FTS Index Cache进行更新 ，而不是每次插入后更新一次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_ft_cache_size参数

• 该参数用于管理FTS索引缓存容量，默认设置为32M
• 当缓存达到上限时（即当缓存满时），系统会将其中包含（word,ilist）分词信息的内容归档到磁盘上的辅助表中
• 提升该参数值能够优化搜索效率，在发生数据故障时（如宕机情况），由于未归档到磁盘中的索引信息可能需要更长时间才能恢复

事务提交时机式索引缓存维护

【
对于其他数据库（如） Oracle 11g 等系统而言，则允许用户根据实际需求采取以下任一策略：（1）手动选择在事务提交时刻完成倒排索引数据更新至磁盘空间；（2）设定固定间隔时间自动完成倒排索引更新至磁盘空间的操作。
而对于 InnoDB 数据库系统而言，则采用了完全不同的机制：其始终会在每次事务提交阶段进行分词数据存储至 FTSS索引缓存表中，并随后会批量地完成数据更新至磁盘空间。尽管 InnoDB 采用延后方式进行数据处理以提升性能效果（即先对当前事务的数据执行一次性全表扫描并生成临时结果集），但这些操作均受限于每次事务提交的时间节点。

数据库关闭时、宕机时FTS IndexCache与AuxiliaryTable的更新

• 数据库关闭时： 位于FTSIndexCache中的数据库会被自动复制至磁盘上的AuxiliaryTable中
• **数据库宕机时：**某些位于FTSIndexCache中的数据库可能并未被自动复制至磁盘上。当系统下次启动后，在执行表全文检索（查询或插入）操作时，InnoDB会在下次操作中读取这些未完成文档，并将其分词结果插入到FTSIndexCache中。

FTS Document ID（FTS_DOC_ID列）

• 另外一种关键数据标识符
• 在InnoDB存储引擎中
  • 为了支持全文检索功能
  • 必须为每个单词建立一个唯一映射关系：
    • 在InnoDB架构中，默认会分配一个唯一的FTS_DOC_ID值给每个文档
    • 该字段的数据类型必须定义为bigint unsigned not null
    • 同时，在该字段上自动创建了一个唯一的索引（FTS_DOC_ID_INDEX）来辅助查询优化
• 需要注意的是，在MySQL中，默认赋予每个文档文件一个唯一的FTS_DOC_ID值
• 如果在自定义表结构中手动指定该字段类型为int而非bigint，则会导致报错信息

>       1. create table fts_a(

>  
>       2.     FTS_DOC_ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
>  
>       3.     body TEXT,
>  
>       4.     primary key(FTS_DOC_ID)
>  
>       5. );
>  
>  
>

• 当修改FTS_DOC_ID列的类型之后就可以创建成功了

>       1. create table fts_a(

>  
>       2.     FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
>  
>       3.     body TEXT,
>  
>       4.     primary key(FTS_DOC_ID)
>  
>       5. );
>  
>  
>

FTS Document ID（FTS_DOC_ID列）

• 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数据库会抛出错误
• 例如下面自己手动创建一个FTS_DOC_ID列，但类型是int，而非bigint，因此抛出了错误。

>       1. create table fts_a(

>  
>       2.     FTS_DOC_ID INT UNSIGNED AUTO_INCREMENT NOT NULL,
>  
>       3.     body TEXT,
>  
>       4.     primary key(FTS_DOC_ID)
>  
>       5. );
>  
>  
>

• 当修改FTS_DOC_ID列的类型之后就可以创建成功了

>       1. create table fts_a(

>  
>       2.     FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
>  
>       3.     body TEXT,
>  
>       4.     primary key(FTS_DOC_ID)
>  
>       5. );
>  
>  
>

• 文档中分词的执行操作是在提交事务时完成的，在处理删除操作时有所不同。
• 它们不会从磁盘Auxiliary Table中直接删除相关记录，在这种情况下只会从FTS Index Cache中进行相关记录的删除。
• 对于Auxiliary Table中存在的被删除记录，在这种情况下InnoDB会将这些信息存储到Deleted auxiliary table中。
• 用户可以通过查询information_schema数据库下的表innodb_ft_being_deleted来获取相关信息。

OPTIMIZE TABLE命令

虽然文档的DML操作不会直接从索引中删除数据，
反而会在对应的DELETED表中插入记录。
当应用程序允许时，
在这种情况下，
即使索引列中有某些数据被移除了，
查询也不会使用到这些移除的数据。

为了帮助用户手动清除已从索引中被移除的数据，
InnoDB存储引擎提供了OPTIMIZE TABLE这一命令。

>     ooptimize table table_name;

InnoDB优化全文本唯一键参数

>       1. set global innodb_optimize_fulltext_only=1;

>  
>       2.  
>  
>       3. optimize table table_name;
>  
>  
>

innodb_ft_num_word_optimize参数

• 当大量文档被删除时，执行OPTIMIZE TABLE操作可能会占用较多的时间。这将导致应用程序并发性下降，并显著提高用户的响应时间。
• 用户可以通过设置此参数，默认情况下允许每次仅处理2000个分词。建议根据实际情况调整该值以平衡性能与负载能力。

stopword列表

• stopword列表，其表示该列表中的word不需要对其进行索引分词操作。例如，对于the这个单词，由于不具有具体的意义，因此将其视为stopword
• InnoDB默认有一张stopword列表，其在information_schema数据库下，表名为innodb_ft_default_stopword，默认共有36个stopword

innodb_ft_server_stopword_table参数

此参数允许用户自定义停用词列表

举例而言，在指定的数据库中创建名为user_stopword的停用词表，并将其标记为停用词表

>       1. create table user_stopword(

>  
>       2.     value varchar(30)
>  
>       3. )ENGINE=InnoDB;
>  
>       4.  
>  
>       5. set global innodb_ft_server_stopword_table="test/user_stopword";
>  
>  
>

演示案例

• 创建一个表fts_a：

>       1. create table fts_a(

>  
>       2.     FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
>  
>       3.     body TEXT,
>  
>       4.     primary key(FTS_DOC_ID)
>  
>       5. );
>  
>  
>

• 插入一些数据

>       1. insert into fts_a select NULL,'Pease porridge in the pot';

>  
>       2.  
>  
>       3. insert into fts_a select NULL,'Pease porridge hot,pease porridge cold';
>  
>       4.  
>  
>       5. insert into fts_a select NULL,'Nine days old';
>  
>       6.  
>  
>       7. insert into fts_a select NULL,'Some like it hot,some like it cold';
>  
>       8.  
>  
>       9. insert into fts_a select NULL,'Nine days old';
>  
>       10.  
>  
>       11. insert into fts_a select NULL,'I like code days';
>  
>  
>

由于body字段是用于全文搜索的字段变量,从而建议建立一个类型指定为FULLTEXT的索引表.

>     create fulltext index idx_fts on fts_a(body);

• 查看数据

>     select * from fts_a;

配置参数innodb_ft_aux_table后

接着，在表内查找分词的相关信息

每个单词都会关联一个DOC_ID和POSITION字段

此外还会记录FITST_DOC_ID、LAST_DOC_ID以及DOC_COUNT字段

分别表示该单词首次出现在哪个文档ID、最后出现在哪个文档ID以及该单词在整个数据库中出现了多少次。

>       1. -- fts_a表在数据库demo下

>  
>       2. set global innodb_ft_aux_table='demo/fts_a';
>  
>       3.  
>  
>       4. select * from information_schema.innodb_ft_index_table;
>  
>  
>

• 若执行下面的语句可以删除FTS_DOC_ID为6的文档

>     delete from demo.fts_a where FTS_DOC_ID=6;

• 之前提到过，在InnoDB中并不会直接删除索引中的对应记录。
• 而是会将被删除的文档ID转移至DELETED表中。
• 这样用户就可以执行相应的查询操作。

>     select * from information_schema.innodb_ft_deleted;

• 已将被删除的文档ID存入innodb_ft_deleted变量中；
  如果用户希望完全删除该文档的相关分词信息，请运行以下命令：

>       1. set global innodb_optimize_fulltext_only=1;

>  
>       2.  
>  
>       3. optimize table demo.fts_a;
>  
>  
>

• 该命令一旦被施加后会使得数据被完全删除，并且相应的文件标识符会被记录到指定的表innodb_ft_being_deleted中：

>       1. select * from information_schema.innodb_ft_deleted;

>  
>       2.  
>  
>       3. select * from information_schema.innodb_ft_being_deleted;
>  
>  
>

此外，在此之前由于该文档ID已被删除的原因下，并禁止再次插入该文档ID以避免系统出现异常情况

>     insert into demo.fts_a select 6,'I like this days';

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

四、全文检索的语法

全文检索的查询，其语法如下 ：

• 该字段名对应于要进行匹配的目标列。*
• 该字段用于确定所需查询的方法类型。*
• 查询模式NATURAL LANGUAGE、BOOLEAN MODE、QUERY EXPANSION在下面介绍。

Natural Language查询模式

• 该模式是全文检索的标准方式，在此框架下表示用于搜索包含特定单词的文档。

演示案例：

• 例如对上面创建的表fts_a，查询body字段中带有Pease的文档，**若不使用全文检索技术 ，**使用下述SQL语句来进行查询：
  • 显然下述SQL语句不能使用B+树检索

>     select * from fts_a where body like '%Pease%';

• **当采用全文检索技术时 ，**搜索BODY字段中包含关键词PORRIDGE的内容，则对应的SQL语句为：

>       1. select * from fts_a where match(body) against ('Porridge' in natural language mode);

>  
>       2.  
>  
>       3. -- 由于NATURAL LANGUAGE MODE是默认的全文检索模式，因此用户可以简略为下述SQL语句
>  
>       4. select * from fts_a where match(body) against ('Porridge');
>  
>  
>

分析以下MySQL查询语句的执行计划

>     explain select * from fts_a where match(body) against ('Porridge');

在where子句中使用MATCH函数进行查询时,查询结果将按照相关性的大小进行降序排列,即相关性越高排位越靠前。其值为非负浮点数,其中0表示两者之间没有相关性. 根据MySQL官方文档可知,匹配度计算依据如下四个条件:

• word在文档中是否存在
• word在文档中的出现频率
• word在索引列中的分布情况
• 匹配项所涵盖的文档数量
  基于上述参数计算得出匹配度后,系统会将具有最高匹配度的条目排至顶部.
  为了统计MATCH函数产生的结果数量 ,可以执行以下SQL语句:

>     select count(*) from fts_a where match(body) against ('Porridge' in natural language mode);

• 上述SQL语句也可以更改为下面的形式：

>       1. select count(if(match(body) against ('Porridge' in natural language mode),1,NULL))

>  
>       2. as count from fts_a;
>  
>  
>

虽然两个 SQL 语句产生的逻辑结果相同但第二 SQL 在执行效率上更为出色。
由于第一 SQL 语句需要进行相关性排序统计操作而第二 SQL 语句无需完成此步骤。
另外用户还可以通过 SQL 语句来查看相关性：

>       1. select fts_doc_id,body,match(body) against ('Porridge' in natural language mode)

>  
>       2. as Relevance from fts_a;
>  
>  
>

对于InnoDB全文搜索功能而言，在执行相关操作之前需要考虑以下几个关键因素：

首先，在执行全文搜索时若查询的单词存在于停用词列表中，则应忽略该字符串。

其次，在执行全文搜索时需确保所查询单词的字符长度落在指定区间[innodb_ft_min_token_size, innodb_ft_max_token_size]内。

如果遇到停用词，则应跳过该单词不进行检索。

例如，在文档1中发现the这个词出现时由于其属于停用词其相关性评分为零。

>       1. select fts_doc_id as id,body,match(body) against('the' in natural language mode)

>  
>       2. as r1 from fts_a;
>  
>  
>

Boolean查询模式

当使用该修饰符进行操作时，查询字符串前后位置具有特殊意义

基于以下fts_a表的数据库设计，请问您是否需要查询包含pease和hot标签的相关条目？

>     select * from fts_a where match(body) against ('+Pease +hot' in boolean mode);

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

>     select * from fts_a where match(body) against ('+Pease -hot' in boolean mode);

• 下面的语句返回有pease或有hot的文档

>     select * from fts_a where match(body) against ('Pease hot' in boolean mode);

此处采用Proximity Search（有误待修正）：

• 此处计算得到Pease与pot的距离为22个字节。理论上而言, 第二个SQL语句应当为空表结果; 然而实际运行后发现结果依然存在。

>       1. select fts_doc_id,body from fts_a where match(body) against ('"Pease pot" @30' in boolean mode);

>  
>       2.  
>  
>       3. select fts_doc_id,body from fts_a where match(body) against ('"Pease pot" @10' in boolean mode);
>  
>  
>

• 文档的相关性评估基于包含"like"或"pot"的关键词，并且在发现使用"pot"的情况下将相关性提高。
  文档4虽然包含了两个"like"关键词但未使用"pot"这一关键短语因此其相关性未能达到与文档1和文档8相同的水平。

>       1. select fts_doc_id,body,match(body) against ('like>pot' in boolean mode)

>  
>       2. as Relevance from fts_a;
>  
>  
>

• 对于下面的查询加入了‘’的条件后运行结果如下：
  • 可以看到文档4变为负的原因主要是因为它包含了both like和some单词

>       1. select fts_doc_id,body,match(body) against ('like>pot <some' in boolean mode)

>  
>       2. as Relevance from fts_a;
>  
>  
>

• 接着运行下面的语句，查询文档中以“po”开头的单词：

下面的SQL语句查询关于短语，例如：

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

>       1. select * from fts_a where match(body) against ('like hot' in boolean mode);

>  
>       2.  
>  
>       3. select * from fts_a where match(body) against ('"like hot"' in boolean mode);
>  
>  
>

Query Expansion查询模式

• 这种查询通常在查询的关键词太短， 用户需要implied knowledge（隐含知识）时进行
• 例如，对于单词“database”的查询，用户可能希望查询的不仅仅是包含database的文档，可能还指哪些包含MySQL、Oracle、DB2、RDBMS的单词。而这时可以使用Query Expansion模式来开启全文检索的implied knowledge
• 通过在查询短语中添加WITH QUERY EXOANSION或IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION可以开启blind query expansion（又称为automatic relevance feedback）
• 该查询分为两个阶段：
  • 第一阶段： 根据搜索的单词进行全文索引查询
  • 第二阶段： 根据第一阶段产生的分词再进行一次全文检索的查询

演示案例

• 创建一个测试表articles：
  • 在这个表中没有显示创建FTS_DOC_ID列，因此InnoDB会自动创建该列，并添加唯一索引
  • 此外，表的全文检索索引是根据列title和body的联合索引

• 基于数据库关键字进行的全面检索查询。 经查看得知，共有三条记录被返回，在body字段中包含了与数据库相关的关键词。

随后启动Query Expansion操作后
观察到最终返回了8条结果
其中不仅包含了之前数据库中的记录
还新增了几篇基于title或body字段涉及MySQL及DB2文档的内容
这正是Query Expansion的作用所在

• 因为Query Expansion进行全面检索可能导致大量非相关性查询,所以在应用时需特别注意