内部实现
InnoDB Full-Text Index Design
InnoDB的全文索引使用反向索引的设计。反向索引存储了一个单词（word）列表，对于每个单词，都有一个文档的列表，来标识这个单词出现的地方。为了支持临近搜索（proximity search），每个单词的位置信息也以字节偏移的方式存储。
注：下图有个纰漏，cold和code混淆了。
InnoDB Full-Text Index Tables
当创建了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全文索引缓存
为了防止大量并发读写附属表，InnoDB使用全文索引缓存去临时缓存最近的插入行。在存满并刷入磁盘之前，缓存的内容一直存储在内存之中，可以通过查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE表去查看最近缓存的插入行。
缓存和批处理刷新行为避免了对辅助索引表的频繁更新，频繁更新可能会在繁忙的插入和更新期间导致并发访问问题。批处理还避免了对同一个word的多次插入，最大化的减少了重复的条目。相同的word会先merge再刷入到磁盘中，而不是为每个word单独插入，这样提高了插入效率并且使得索引附属表尽可能的小。
innodb_ft_cache_size，用于配置每个表的全文索引缓存大小，他决定了全文索引缓存的刷入频率。默认为32MB。
innodb_ft_total_cache_size，用于配置全局的全文索引缓存大小，限制了实例中的所有表。
全文索引缓存只缓存最近插入的行，查询时，已经刷入磁盘（附属索引表）的数据不会再回到索引缓存中。附属索引表中的内容是直接查询的，最终返回的结果返回前需要将附属索引表的结果和索引缓存中的结果合并。
InnoDB全文索引DOC_ID和FTS_DOC_ID列
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不一定需要定义成AUTO_INCREAMENT列，但是这样做的话更方便加载数据。
mysql> CREATE TABLE opening_lines (
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200)
) ENGINE=InnoDB;
如果你自行定义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列在删除了全文索引之后依然被保留。
InnoDB全文索引的删除
删除被索引文件的一个记录，可能会在附属索引表中产生非常多的小的删除项，在并发访问时，会产生热点问题。为了避免这个问题，每当被索引表中的记录被删除时，会将被删文档的DOC_ID记录在一个特别的FTS_*_DELETED表中，同时全文索引中已经索引了的记录依然被保存。在返回查询结果前，使用FTS_*_DELETED中的信息去过滤掉已经删除掉了的DOC_ID。这种设计的优势在于删除速度快且消耗低。不好的地方在于索引的大小不能随着记录的删除而立即减少。为了删除已删除记录在全文索引中的项，需要对被索引的表执行OPTIMIZE TABLE，配合[innodb_optimize_fulltext_only=ON]，去重构全文索引。
InnoDB Full-Text Index Transaction Handling
细节略，有例子：https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html
全文搜索只能看到已经提交了的数据。
监控全文索引
你可以通过查询下面的INFORMATION_SCHEMA表，来监控或测试InnoDB的一些特殊文本处理。
问题
默认的分词器不支持中文，不能检索到中文中的英文单词。
MySQL全文索引调优
Stopwords
InnoDB默认的Stopwords：
select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;
SQL中的关键词（保留关键字）：
-
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL
-
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
Shell中的关键词：for，while，echo
其他：###, ***, --,
评估
被索引表数据量、索引表数据量
在已有的数据表中添加全文索引的成本
-
时间
-
存储大小
模糊匹配与严格匹配的性能差距
表优化
表优化的配置
需要将innodb_optimize_fulltext_only配置为ON，这里是否需要DBA在MySQL镜像中修改？
innodb_optimize_fulltext_only设置为ON后，对系统有何影响需要评估。
OPTIMIZE TABLE还对进行其他操作，如Cardinality的重新统计。
show variables like 'innodb_optimize_fulltext_only'; -- default false
set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE xxxxxxxxx;
-- 每次优化的数量，默认2000
show variables like 'innodb_ft_num_word_optimize'; -- default value: 2000
表优化的执行
执行的时间、频率。
Ngram Parser
MySQL内建的全文检索解析器使用单词之间的空白作为分隔符以标识单词的头尾，但是这里有个限制，对于表意文字，它是没有单词分隔符的。为了解决这个限制，MySQL提供了支持中文、日语、韩语的ngram解析器。ngram解析器支持InnoDB和MyISAM。
Note
MySQL还提供了支持日文的MeCab全文解析器插件，它将文档索引为有意义的单词。For more information, see Section 12.10.9, “MeCab Full-Text Parser Plugin”.
An ngram is a contiguous sequence ofncharacters from a given sequence of text. The ngram parser tokenizes a sequence of text into a contiguous sequence ofncharacters. For example, you can tokenize “abcd” for different values ofnusing the ngram full-text parser.
n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'
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 Size
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 只能在启动配置或者配置文件中指定
#### Startup String ####
mysqld --ngram_token_size=2
#### CONFIG FILE ####
[mysqld]
ngram_token_size=2
使用Ngram解析器创建全文索引
与默认的解析器相差不大，多了一句： xxx WITH PARSER ngram
CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
Ngram解析器对空格的处理
Ngram在解析时去除空格，如
- “ab cd” is parsed to “ab”, “cd”
- “a bc” is parsed to “bc”
Ngram解析器处理Stopword
MySQL内建的默认全文检索解析器将单词与Stopword列表中的做对比，如果单词与Stopword列表中的元素相同的话，这个单词则不会被索引。对于Ngram解析器，Stopword的处理方式不同。Ngram解析器不排除与stopword列表中的条目相等的token，而是排除包含stopwords的token。例如，假设ngram_token_size=2，包含“a,b”的文档将被解析为 “a,” h和“,b”。如果将逗号（“,”）定义为停止字，则 “a,”和“,b”都将不会加入索引中，因为它们包含逗号。
例子：
-- 搜索best 结果会有test。因为be是Stopword，所以best会解析为es，st正好匹配到test中三个（te es st）的两个（es st）。虽然搜的是best，结果等同于搜索est。
-- test te es st
-- best be es st
select * from xxxxxx where match(CCEXT) against('+best' IN BOOLEAN MODE);
默认Ngram解析器使用默认的Stopword列表，这里面含有英文的Stopword。如果需要中文的Stopword，需要你自己创建。
Stopword的长度超过 ngram_token_size则会被忽略。
鉴于上面的问题，需要将MySQL默认的stopword删除。但是删除的时候会报错。
-- 删除INNODB_FT_DEFAULT_STOPWORD中的记录报错。 RealtimeCompute> DELETE FROM information_schema.INNODB_FT_DEFAULT_STOPWORD WHERE value LIKE 'a' ESCAPE '#' -- [2021-11-16 14:52:24] [42000][1044] Access denied for user 'root'@'%' to database 'information_schema' -- 所以可以使用下面的方式停用stopword set GLOBAL innodb_ft_enable_stopword=1
Ngram解析器Term搜索
有两个文档，一个包含“ab”，另一个包含“abc”。对于搜索文本“abc”将转换成“ab”，“bc”。
- 对于自然语言搜索模式（natural language mode search），搜索结果采用UNION的结果。即，返回“ab”，“abc”。
- 对于布尔搜索模式（boolean mode search），搜索结果匹配”abc“。
对于代码检索的需求，考虑使用布尔搜索模式
Ngram解析器通配符搜索
略。
Ngram解析器短语（Phrase）搜索
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解析器好处是支持了中文的检索
网友评论