MySQL的全文索引Fulltext Index | 包括ngram

InnoDB的全文索引使用反向索引的设计。反向索引存储了一个单词（word）列表，对于每个单词，都有一个文档的列表，来标识这个单词出现的地方。为了支持临近搜索（proximity search），每个单词的位置信息也以字节偏移的方式存储。

当创建了InnoDB全文索引，一系列的索引表会一同被创建，见下面的例子：

最前面的六个表包含了反向索引，它们被称作附属索引表（auxiliary index table）。当输入的表被索引（tokenized）后，每个独立的单词（亦称作“tokens”）会被携带其DOC_ID和位置信息插入到索引表中。根据单词第一个字符的字符集排序权重，在六个索引表中对单词进行完全排序和分区。

反向索引分区到六个附属索引表以支持并行的索引创建。默认有2个线程复制索引（Tokenize）、排序、插入单词和关联数据到索引表中。工作的线程的数量由 innodb_ft_sort_pll_degree 配置项控制的。对于大表的全文索引，可以考虑增加线程数量。

如果主表创建在 xx表空间，索引表存储在它们自己的表空间中。反之，索引表存储于其索引的表空间中。

前面例子展示的另外一种索引表被称作通用索引表，它们被用于全文索引的“删除处理（deletion handing）”和存储内部状态。不同于为每个全文索引都各自创建的反向索引表，这组表对特定表的所有全文索引都是共用的。

即使全文索引删掉了，通用索引（Common Index）也会被保留，当全文索引删除后，为这个索引而创建的FTS_DOC_ID列依然保留，因为移除FTS_DOC_ID列会导致重构之前被索引的表。管理FTS_DOC_ID列需要用到通用索引表。

为了防止大量并发读写附属表，InnoDB使用全文索引缓存去临时缓存最近的插入行。在存满并刷入磁盘之前，缓存的内容一直存储在内存之中，可以通过查询 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表去查看最近缓存的插入行。

缓存和批处理刷新行为避免了对辅助索引表的频繁更新，频繁更新可能会在繁忙的插入和更新期间导致并发访问问题。批处理还避免了对同一个word的多次插入，最大化的减少了重复的条目。相同的word会先merge再刷入到磁盘中，而不是为每个word单独插入，这样提高了插入效率并且使得索引附属表尽可能的小。

全文索引缓存只缓存最近插入的行，查询时，已经刷入磁盘（附属索引表）的数据不会再回到索引缓存中。附属索引表中的内容是直接查询的，最终返回的结果返回前需要将附属索引表的结果和索引缓存中的结果合并。

InnoDB使用被称作DOC_ID的唯一文件描述符，将全文索引中的单词与该单词在文档中的记录映射起来。映射关系需要索引表中的 FTS_DOC_ID 列。在创建全文索引时，如果没有定义 FTS_DOC_ID 列，InnoDB会自动的加入一个隐藏的 FTS_DOC_ID 列。下面是一个例子，

CREATE FULLTEXT INDEX ft_index ON xxxxxxxx(CONTEXT)

[2021-11-12 18:14:04] [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID

重点看一下这一行： [HY000][124] InnoDB rebuilding table to add column FTS_DOC_ID ，InnoDB重新构建了这个表，并且添加了一个列 FTS_DOC_ID 。

在CREATE TABLE的过程中添加 FTS_DOC_ID 的时间成本要低于在已经有数据的表上建立全文索引。如果在表加载数据之前定义 FTS_DOC_ID 列，这个表和它的索引都不需要为了新增列而重新构建。如果你不需要考虑 CREATE FULLTEXT INDEX 的性能，可以让InnoDB为你创建 FTS_DOC_ID 列。InnoDB会新增一个隐藏的 FTS_DOC_ID 列，并且在 FTS_DOC_ID 上建立唯一索引（FTS_DOC_ID_INDEX）。如果你想自行创建 FTS_DOC_ID 列，这个列必须定义为 BIGINT UNSIGNED NOT NULL 且命名为FTS_DOC_ID（全大写），如下例子：

如果你自行定义 FTS_DOC_ID 列的话，你需要负责管理这个列，避免空值（empty）或者重复值。 FTS_DOC_ID 的值是不能被重复利用的，所以也就是说 FTS_DOC_ID 的值是需要一直增加的。

或者，你可以在 FTS_DOC_ID 列上创建所必须的唯一索引FTS_DOC_ID_INDEX（全大写）。

mysql>CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID)

如果你没有创建FTS_DOC_ID_INDEX，InnoDB会自动创建。

在MySQL 5.7.13前，允许最大FTS_DOC_ID与最新的FTS_DOC_ID之间的间隔为10000，在MySQL 5.7.13及之后的版本中，这个允许的间隔为65535。

为了避免重新构建表，FTS_DOC_ID列在删除了全文索引之后依然被保留。

删除被索引文件的一个记录，可能会在附属索引表中产生非常多的小的删除项，在并发访问时，会产生热点问题。为了避免这个问题，每当被索引表中的记录被删除时，会将被删文档的DOC_ID记录在一个特别的 FTS_*_DELETED 表中，同时全文索引中已经索引了的记录依然被保存。在返回查询结果前，使用 FTS_*_DELETED 中的信息去过滤掉已经删除掉了的DOC_ID。这种设计的优势在于删除速度快且消耗低。不好的地方在于索引的大小不能随着记录的删除而立即减少。为了删除已删除记录在全文索引中的项，需要对被索引的表执行OPTIMIZE TABLE，配合[ innodb_optimize_fulltext_only=ON ]，去重构全文索引。

细节略，有例子： https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

全文搜索只能看到已经提交了的数据。

你可以通过查询下面的INFORMATION_SCHEMA表，来监控或测试InnoDB的一些特殊文本处理。

默认的分词器不支持中文，不能检索到中文中的英文单词。

InnoDB默认的Stopwords：

select * from information_schema.INNODB_FT_DEFAULT_STOPWORD

SQL中的关键词（保留关键字）：

Shell中的关键词：for，while，echo

其他：###, ***, --,

被索引表数据量、索引表数据量

模糊匹配与严格匹配的性能差距

需要将 innodb_optimize_fulltext_only 配置为ON，这里是否需要DBA在MySQL镜像中修改？

innodb_optimize_fulltext_only 设置为ON后，对系统有何影响需要评估。

innodb_optimize_fulltext_only

执行的时间、频率。

MySQL内建的全文检索解析器使用单词之间的空白作为分隔符以标识单词的头尾，但是这里有个限制，对于表意文字，它是没有单词分隔符的。为了解决这个限制，MySQL提供了支持中文、日语、韩语的 ngram 解析器。ngram解析器支持InnoDB和MyISAM。

Ngram是内建在服务中的插件，像其他自建在服务中的插件一样，服务启动时会自动加载它。全文检索的语法参考上面（ Section 12.10, “Full-Text Search Functions” ），这里只讨论一些不同的地方。除了单词的最小、最大长度配置项（[ innodb_ft_min_token_size ]innodb_ft_max_token_size,ft_min_word_len,ft_max_word_len，全文检索依赖一些配置项都是可以使用的。

Ngram默认索引的单词（token）的大小为2（ 2bigram ）。例如，索引的大小为2，Ngram解析器解析字符串“abc def”为四个单词元素(tokens)：“ab”, “bc”, “de” and “ef”。

ngram token size is configurable using the ngram_token_size configuration option, which has a minimum value of 1 and maximum value of 10.

作为只读变量, ngram_token_size 只能在启动配置或者配置文件中指定

与默认的解析器相差不大，多了一句： xxx WITH PARSER ngram

Ngram在解析时去除空格，如

MySQL内建的默认全文检索解析器将单词与Stopword列表中的做对比，如果单词与Stopword列表中的元素相同的话，这个单词则不会被索引。对于Ngram解析器，Stopword的处理方式不同。Ngram解析器不排除与stopword列表中的条目相等的token，而是排除包含stopwords的token。例如，假设 ngram_token_size=2 ，包含“a,b”的文档将被解析为 “a,” h和“,b”。如果将逗号（“,”）定义为停止字，则 “a,”和“,b”都将不会加入索引中，因为它们包含逗号。

例子：

默认Ngram解析器使用默认的Stopword列表，这里面含有英文的Stopword。如果需要中文的Stopword，需要你自己创建。

Stopword的长度超过 ngram_token_size则会被忽略。

有两个文档，一个包含“ab”，另一个包含“abc”。对于搜索文本“abc”将转换成“ab”，“bc”。

略。

For example, The search phrase “abc” is converted to “ab bc”, which returns documents containing “abc” and “ab bc”.

The search phrase “abc def” is converted to “ab bc de ef”, which returns documents containing “abc def” and “ab bc de ef”. A document that contains “abcdef” is not returned.

使用Ngram解析器好处是支持了中文的检索

1. Lucene Lucene的开发语言是Java，也是Java家族中最为出名的一个开源搜索引擎，在Java世界中已经是标准的全文检索程序，它提供了完整的查询引擎和索引引擎，没有中文分词引擎，需要自己去实现，因此用Lucene去做一个搜素引擎需要自己去架构.另外它不支持实时搜索，但linkedin和twitter有分别对Lucene改进的实时搜素. 其中Lucene有一个C++移植版本叫CLucene，CLucene因为使用C++编写，所以理论上要比lucene快. 2. Sphinx Sphinx是一个用C++语言写的开源搜索引擎，也是现在比较主流的搜索引擎之一，在建立索引的事件方面比Lucene快50%，但是索引文件比Lucene要大一倍，因此Sphinx在索引的建立方面是空间换取事件的策略，在检索速度上，和lucene相差不大，但检索精准度方面Lucene要优于Sphinx，另外在加入中文分词引擎难度方面，Lucene要优于Sphinx.其中Sphinx支持实时搜索，使用起来比较简单方便. 3. Xapian Xapian是一个用C++编写的全文检索程序，它的api和检索原理和lucene在很多方面都很相似，算是填补了lucene在C++中的一个空缺. 4. Nutch Nutch是一个用java实现的开源的web搜索引擎，包括爬虫crawler，索引引擎，查询引擎. 其中Nutch是基于Lucene的，Lucene为Nutch提供了文本索引和搜索的API. 对于应该使用Lucene还是使用Nutch，应该是如果你不需要抓取数据的话，应该使用Lucene，最常见的应用是：你有数据源，需要为这些数据提供一个搜索页面，在这种情况下，最好的方式是直接从数据库中取出数据，并用Lucene API建立索引. 5. DataparkSearch DataparkSearch是一个用C语言实现的开源的搜索引擎. 其中网页排序是采用神经网络模型. 其中支持HTTP，HTTPS，FTP，NNTP等下载网页.包括索引引擎，检索引擎和中文分词引擎(这个也是唯一的一个开源的搜索引擎里有中文分词引擎).能个性化定制搜索结果，拥有完整的日志记录. 6. Zettair Zettair是根据Justin Zobel的研究成果为基础的全文检索实验系统.它是用C语言实现的. 其中Justin Zobel在全文检索领域很有名气，是业界第一个系统提出倒排序索引差分压缩算法的人，倒排列表的压缩大大提高了检索和加载的性能，同时空间膨胀率也缩小到相当优秀的水平. 由于Zettair是源于学术界，代码是由RMIT University的搜索引擎组织写的，因此它的代码简洁精炼，算法高效，是学习倒排索引经典算法的非常好的实例. 其中支持linux，windows，mac os等系统. 7. Indri Indri是一个用C语言和C++语言写的全文检索引擎系统，是由University of Massachusetts和Carnegie Mellon University合作推出的一个开源项目. 特点是跨平台，API接口支持Java，PHP，C++. 来自网络。

---