InnoDB的全文索引

InnoDB全文索引是基于下面这篇论文来实现的：

http://drdobbs.com/database/231902587

下面是这篇论文自己的翻译：

Mysql版本：5.6

全文索引的设计

InnoDB的全文索引是用倒排索引（inverted index）来实现的，即输入的text被划分为token，即带有属性的term，这些token也叫word，被存在了一个或多个辅助表中。对于每一个word，文档id和word的位置作为一个序列存了起来。我们称（文档id，word位置）的序列为“ilist”。因此，在辅助的索引表中，两个重要的列，一个就是word，一个就是ilist。InnoDB的全文索引支持相似搜索，MyISAM是不支持的。

InnoDB用6张表来代替每一个搜索索引，词在这些表中基于它们的首字母划分，当前划分标准是硬编码，是针对拉丁字符集的。

分离的设计可以提高并行化，在当前的版本中，InnoDB只能并行化建立索引这一个操作，即CREATE INDEX，查询和全文索引也是可以做到的，在后面的版本中。

下面通过一个例子，来介绍下InnoDB全文索引的辅助表

从MySQL5.6开始，用于可以通过INFORMATION_SCHEMA数据库的某些表，查看InnoDB的元数据，即辅助表auxiliary table。但是这个辅助表对于InnoDB来说是特殊的，不支持一般的SQL语句查询。

这里是quotes表的定义

创建完表后，创建对表quotes的列quote创建fulltext索引idx

create fulltext index idx on quotes(quote);

然后通过这句话，可以查innodb的所有表：

SELECT table_id, name FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

生成了下面这个表：

这个展示了，这有多个带有FTS_名称前缀的辅助表，后缀是INDEX_*。这些都是表quotes倒排索引的表，从table_id这个列就能看出来，quotes的table_id是547，其16进制是223，223不就是倒排索引辅助表的第一段吗，另一个16进制的值，跟在后面的是全文索引的id。

这个索引信息可以通过

INFORMATION_SCHEMA.INNODB_SYS_INDEXES

这个表查到，其语句是：

SELECT index_id, table_id, name FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE table_id=547;

得到：

 其中索引idx，便是刚刚建立的。主键也会建立索引，这里还有一个索引：FTS_DOC_ID_INDEX在表中。这是在FTS_DOC_ID这一列的索引，这个索引是随着全文索引一块建成的。

通过InnoDB索引缓存，将插入的值进行分批

 在硬盘上，InnoDB的全文索引是通过辅助表（auxiliary tables）的形式呈现的，包含了word和ilist（docid，position）。InnoDB有额外的在内存中的结构，FTS Index Cache，这个是将插入的值进行切分batch用的，在它们同步到硬盘上的辅助表之前。Cache的角色就像是InnoDB的数据交换缓冲，用于存放I/O，在大量的DML操作的时候。

FTS Index Cache将word的值建立索引，用一个红黑树来存的。有缓存的话，可以降低io负担，在做搜索的过程中，初步的phrase过滤可以不读磁盘，后面将内存中的单词列表和文档id进行合并，产生最终的结果集。

这个cache提供的缓冲，避免了在大量插入和更新时，对底层FTS索引表的更新。全文索引只需要在cache满的时候进行同步。batching技术也最小化了在全文索引中每个word的访问数量。在插入doc的时候，会先将这个doc进行tokenize生成word，然后再对这些word插入doc_id。若插一个doc访问一次word，这效率很低。cache就从多个文档doc中，收集一个word的doc_id/position对，对于一个word一次性插入多个ilist元素。这项技术减小了io的开销，且降低冗余的数据，让全文索引更小。

索引cache的大小是可以被修改的，通过变量innodb_ft_cache_size可以配置，这个选项在DML操作特性时会被提到。

InnoDB FTS Document ID

另一个重要的概念就是文档id，即Document ID管理。大部分FTS引擎，从word到record的映射都是通过一个独立ID映射实现的。在这里，它是FTS_DOC_ID列。如果这一列没有定义，InnoDB会在用户创建全文索引时，加到用户的表。这一列的属性是：大正整数，非空。有一个独立的index称为FTS_DOC_ID。数据库载入数据过后，当创建全文索引时，这个列节约了大量的时间，因为InnoDB不需要识别这个表。在这种情况下，许哟啊管理文档ID列，来避免空的活着重复的数据。

使用InnoDB的全文索引

InnoDB的全文索引语法与MyISAM类似。

在一个InnoDB的表上创建FTS Index

对于全文索引，先载入数据，再创建全文索引，比，创建带全文索引的表，在插入文档要快很多。

创建全文索引

create fulltext index idx on quotes(quote);

InnoDB可以并行创建索引，根据InnoDB索引快速创建（FIC）的特性，支持并行排序。tokenize，排序，创建索引都是并行的。可以通过innodb_ft_sort_pll_degree这个变量控制并行的力度

 每一个有全文索引的InnoDB的表都包含一个列FTS_DOC_ID。在原表的定义中指定这一列，能省去整个表的重建，当一个全文索引被创建的时候。示例如下：

CREATE TABLE fts_test (
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, 
title VARCHAR(200),
body TEXT) ENGINE=InnoDB;

-- The unique "FTS_DOC_ID_INDEX" index on FTS_DOC_ID is also optional,

-- without it, InnoDB will automatically create it.:
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on fts_test(FTS_DOC_ID);

然后load data我理解的就是执行insert，插入完后，再创建全文索引：

CREATE FULLTEXT INDEX idx on fts_test (title, body);

需要注意，这个特殊的列必须取名为FTS_DOC_ID，且名字需要严格大小写。就算不写这个，InnoDB也会加上这个，作为一个隐藏的列，且这是一个开销很大的操作，表要重建。因此，自己手动建立是非常节约时间的。

Query, Natural Language Search

一旦数据被装载和提交，可以执行查询语句：match(columns) against(search expression)

也可以结合where语句和similar语句，例子如下：

-- Search for a single word.
select author as "Monolith" from quotes
where match(quote) against ('monolith' in natural language mode);

Query, Boolean Search

对于更多更复杂的搜索，可以有多个词和短语，且根据需要，对term进行不同组合的搜索，比如+要这个词，-不要这词，顺序也可以打乱，例子如下：

-- Search for a combination of words, 
-- not in the same order as the original. 
-- select author as "Ago and Years" from quotes
where match(quote) against ('+ago +years' in boolean mode);

Proximity Search

近似搜索是InnoDB全文搜索中的一个新特性，是布尔查询的一个特殊情况，结合against使用@符号。可以提供两个或更多的词，用双引号扩起来，后面跟一个@distance去指定这些词有多远，这个距离代表了可接受的最大数量的不同字节数。

-- The starting points for these words are too far apart -- (not within 20 bytes), so no results.
select quote as "Too Far Apart" from quotes
where match(quote) against ('"early wise" @20' in boolean mode); Empty set (0.00 sec)
-- But the starting points of all words are within 100 bytes, -- so this query does give results.
select quote as "Early...Wise" from quotes
where match(quote) against ('"early wise" @100' in boolean mode);

 

Transactions（事务）

全文索引这个特性与事务是兼容的。全文索引的数据不需要处理为read-only或者read-mostly。

需要注意一点，就像是大部分支持事务的存储引擎中，tokenization只是在提交的时候进行，所以全文索引看不见未提交的数据。

(这块就不展示)

DML（增删改）

一个插入的字符串的tokenization的过程，只是在事务提交的时候进行。这个行为与大部分有全文索引的数据库是一样的，因为全文索引是个开销特别大的操作。一般，你同步DML的操作结果到全文索引的过程，不是实时，而是周期性的，这应该也是cache的实现问题。只有cache满的时候才会写入到磁盘上。

一个典型的例子，Oracle数据库同步索引数据就是周期性或者需要人手动。现在允许用户指定数据更新的时间，或者手动提交，或者一个固定的时间周期提交一次。遵循事务的提交/回滚以及隔离级别。

插入

插入的doc在提交的时候被tokenize，插入到FTS的索引的cache中。这个cache有一个可设置的大小，前文提高过。一旦这个cache满了，就同步到硬盘上的存放索引的表中。若服务器正常关闭，内容也会同步到index的cache中。但如果服务器崩溃，那么FTS的内容是不会存到硬盘上的，服务器重启后，数据会同步，当你第一次执行查询或者插入全文索引时。任何在全文索引上的丢失，都会从原来的那个表读取，重新tokenization，加入到cache中。

删除

当你从一个存储InnoDB全文索引节点的表上删除行时，InnoDB不会删除在FTS辅助表上word的地址。在提交的时候，InnoDB会记录删除的doc的ID，在另一个辅助表，名为DELETED。对于每一次查询，InnoDB会查这个删除表，过滤已经删除了的doc文件，这个操作会使得删除操作更简单，更快速，不需要更新上千个word的entry，对于每一个已经删除的文档。因为word的entry没有从全文索引的节点上移走，你需要定期执行索引优化，来保证索引大小是合理的。

更新

对于影响全文索引支持的列的更新，更新是被当错删除+插入两个操作执行的。原地更新的发生，当且仅当在更新没有影响任何被全文索引引用的列。

索引优化

正如刚刚提到的，如果这存在着大量的更新和删除，那么索引会变得很臃肿，因为InnoDB记录了删除的doc_id在DELETED的辅助表中。全文索引会变得很大，就算是rows全从表中删除。为了解决这个问题，可以优化索引。这个操作主要是两个事：1、从单词的ilist中去除已经删了的doc_id。2、合并同一个word的多个entry为一个entry，可以的话，合并ilist。

现在，MySQL如何执行优化操作呢，执行OPTIMIZE TABLE这个命令就可以了，当然这个优化的不只是全文索引了。可以设置：

mysql> set global innodb_optimize_fulltext_only=1;

mysql> optimize table articles;

表可以变大，优化可能会花大量的时间，通常做优化要分步骤。配置变量innodb_ft_num_word_optimize指定了多少word来优化，对于OPTIMIZE TABLE这条指令。它默认的是2000个词。当下一个优化执行时，服务器会从上次结束的地方继续优化。

停用词处理

停用词就是常用词或者无关紧要的词，比如：“的”，“这”，“个”，需要从全文索引中剔去。

InnoDB提供了两类停用词：  

1、MySQL预处理好的停用词。如果没有专门定义stopword的话，那么就默认用MySQL自带的。通过查这个表INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD，可以看。指令如下：

mysql> select * from INNODB_FT_DEFAULT_STOPWORD;

2、用户定义的停用词。你可以通过创建一个只有单列的名称为value的表，数据类型varchar，定义自己的停用词表。还需要将全局变量innodb_ft_server_stopword_table指向这个表。再创建FTS的时候，MySQL从用户的表中读取停用词，然不是从默认的表中读了，之前创建的还是之前的，不会因为这个更新了而重建。用例如下：

# Define a correctly formatted user stopword table
create table user_stopword(value varchar(30)) engine = innodb;
# Point to this stopword table with "db name/table name"
set global innodb_ft_server_stopword_table = "test/user_stopword";

查询性能

作者对InnoDB的全文索引的性能进行了测试。

用2.7GB的百科数据作为数据集，查询包含了各种操作：include、exclude和通配符，和近似操作符。

 

（全文索引处理的时间，基本是是索引扫描的时间）

限制

以下几点还有限制：

1、搜索结果排序，只有一个非常简单的排序机制（term出现的频率，doc的频率）。

2、词干化，需要匹配衍生词，比如单复数，过去式。

3、复杂语言，中日韩语简称CJK，没有词的定界符，都是单一划分的。

4、单一字符集。尽管在一个表内用多个字符集是支持的。全文索引中的所有列都只能使用相同的字符集。以及字符集的排序规则collation。