Mysql全文索引解析

parser与N-gram Parser分词器选择 及 ft_min_word_len与innodb_ft_min_token_size的区分及界定

学习《高性能mysql（第三版）》这本书时，学到了全文索引这一张节，但作者当时使用的版本是MySQL 5.5，届时只有MyISAM引擎支持latin（拉丁语）语法分词器的全文索引，而5.6版本后才在InnoDB引擎实验性地支持全文索引，如今以是8.0以上的版本了，InnoDB引擎下的全文索引，也比较成熟了。本文参照《高性能mysql（第三版）》的MyISAM引擎全文索引对比笔者当前使用的MySQL 5.7.16 的InnoDB引擎全文索引的使用差异做简要和说明，不足之处欢迎指正讨论。

一：分词器是啥

分词器我们可以理解为对我们查询的关键词进行拆分的一种规则设置。MySQL最初支持全文索引时，使用的是parser （拉丁语法分词器，通过空格来分词），但对于像中文这类不以空格拆分词语的语言来说无法适用，在MYSQL5.7.6后提供了n_gram parser（字符长度分词器) ，对中文的全文索引支持更友好，分词器的使用也很简单，创建索引时添加 WITH PARSER ngram即为使用n_gram parser（字符长度分词器)，不加则默认使用传统parser（拉丁语法空格分词器）。区别参见图1.1

由此可知，在分词器的选择上，依据检索内容的分词方式来选择分词器类型。如果是latin语法的语言，就用parser，否则用n_gram parser；
如果在中文内容检索上使用parser，则按照latin分词语法检索，根本检索不到内容，除非真有用到空格的分词；如果在英文内容检索上使用n_gram parser，则会按照字符长度分词器，这两种方式都能够正常使用全文检索，但检索准确率和效率都会下降，所以在语法上来说这是两种错误用法，以下讲解和实例，也将跳过这两种错误用法。

图1.1

parser不用过多解释，就是遇到空格就拆分，n_gram parser拆分与字面意思不太一样，请自行查找。

二：影响全文索引的系统参数 （未测试停用词）

前文提到，MySQL5.6版本后，MyISAM引擎和InnoDB引擎均支持全文索引，为了区分不同引擎和分词器的参数配置，产生了如下三组参数：（注意：修改系统参数后需要重启MySQL并使用OPTIMIZE TABLE命令重置表格才能使修改生效）

1：n_gram parser（字符长度分词器）参数
ngram_token_size  -- n_gram parser（字符长度分词器）的分词长度   默认为2，

该参数不区分搜索引擎，也就是说，当使用 n_gram parser时，搜索关键词长度设置将无效，搜索关键词长度必须大于等于ngram_token_size，才能使用索引，否则不会被索引，返回空数据

----------结论验证：设置对应参数 ngram_token_size =3；ft_min_word_len =4；innodb_ft_min_token_size =4。搜索中文长度大于等于3时即可搜索到数据，说明使用n_gram parser时，搜索关键词长度确实无效。如有疑问请自行验证

2：parser（拉丁语法空格分词器）MyISAM引擎参数
ft_min_word_len    --搜索关键词最小长度  默认为4
ft_max_word_len   --搜索关键词最大长度  默认为 84

MyISAM引擎下使用parser（拉丁语法空格分词器）时,n_gram parser（字符长度分词器）相关参数及InnoDB引擎搜索关键词长度相关参数将无效，搜索时的关键词长度必须在ft_min_word_len 和 ft_max_word_len 之间(包含下边界，上边界未测试)，才能使用索引，否则不会被索引，返回空数据

-------结论验证：设置对应参数 ngram_token_size =3；ft_min_word_len =4；innodb_ft_min_token_size =3.搜索英文长度大于等于4时才能搜索到数据，说明使用parser时，n_gram parser相关参数及InnoDB引擎相关参数确实无效。如有疑问请自行验证

3：parser（拉丁语法空格分词器）InnoDB引擎参数
innodb_ft_min_token_size    -- 搜索关键词最小长度 默认为3
innodb_ft_max_token_size   -- 搜索关键词最大长度 默认为 84

InnoDB引擎下使用parser（拉丁语法空格分词器）时，n_gram parser（字符长度分词器）相关参数及MyISAM引擎搜索关键词长度相关参数将无效，搜索时的关键词长度必须在innodb_ft_min_token_size 和 innodb_ft_max_token_size之间(包含下边界，上边界未测试)，才能使用索引，否则不会被索引，返回空数据

-------结论验证：设置对应参数 ngram_token_size =3；ft_min_word_len =3；innodb_ft_min_token_size =4，搜索英文长度大于等于4时才参能搜索到数据，说明使用parser时，n_gram parser相关参数及MyISAM引擎相关参数确实无效。如有疑问请自行验证

本节标题已经说了，未测试停用词，这里只提一下相关参数，具体结论请自己验证
A:ft_stopword_file---- myisam引擎下，用于置定一组尾部文件来使用自定义的停用词，没有默认停用词表
B:innodb_ft_enable_stopword -- InnoDB引擎是否开启停用词,INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD为InnoDB默认停用词表

三：全文索引创建及查询分类（参考《高性能Mysql》）

1：创建全文索引（以n_gram parser为例）

创建索引
	create fulltext index title_fulltext on table_name(column1,column2,....) WITH PARSER ngram;

修改索引
	alter table table_name  add fulltext index title_fulltext(column1,column2,....) WITH PARSER NGRAM;

2：查询全文索引（与分词器类型无关）

• 1：自然语言的全文索引（默认全文索引类型） – IN NATURAL LANGUAGE MODE (可以不加)
  自然语言的全文索引，会按照分词器将搜索关键词拆分成多个，查询其并集，按照相关度降序排列查询结果。例句如下：

select sclassID,Name_J,Name_JS,match(Name_J, Name_JS)  AGAINST('巴西国家' IN NATURAL LANGUAGE MODE) as relevance 	from zlk_sclass WHERE match(Name_J, Name_JS)  AGAINST('巴西国家');

• 2：布尔全文索引
  布尔全文索引，会 将搜索关键词看作一个“短语”进行搜索，不会拆分词语。使用修饰符时，可以拆分成多个短语，进行相应修饰符功能的短语查询。例句如下：

select sclassID,Name_J,Name_JS,match(Name_J, Name_JS)  AGAINST('巴西国家' in boolean MODE) as relevance 	from zlk_sclass WHERE match(Name_J, Name_JS)  AGAINST('巴西国家' in boolean MODE) ;

注意事项如下：
A：无论使用哪种搜索引擎的哪种全文索引，相应分词器都会对搜索的关键词进行分词处理。
B：在MATCH()函数中指定的列必须和在全文索引中指定的列完全相同，否则就无法使用全文索引。这是因为，全文索引不会记录相关字/词是来自哪一列的。
C：自然语言的全文索引，是按照拆分词并集（or）进行查询，并按照相关度降序排序返回数据
D：布尔全文索引，默认按照“短语搜索”， 返回按照短语精确匹配(不是交集)的数据，只使用全文索引是无法判断是否精确匹配短语的，通常还需要查询原文确定记录中是否包含完整的短语。由于需要进行回表过滤，所以速度会比较慢。除非使用可以通过索引直接定位的修饰符，布尔全文索引最常用的通用修饰符见图3.1，需要注意的是，parser按照空格拆分关键词，拼接修饰符比较容易，但n_gram parser按字符长度拆分，处理修饰符是非常麻烦的，所以n_gram parse的全文索引不建议使用修饰符
E：我们指定 MATCH()两次：一次在 SELECT列表中，一次在 WHERE子句中。返回结果按相关性递减的顺序对行进行排序。这不会产生额外的开销，因为MySQL优化器注意到这两个 MATCH()调用是相同的，只调用一次全文搜索代码。但如果你将MATCH()函数放到ordery by子句中，MySQL将无法再使用索引排序，而只能使用文件排序，会降低查询效率。

图3.1

四：全文索引的限制（参考《高性能Mysql》）

全文索引可以理解成基于相似度的查询而不是精确的数值比较，本身有很多限制，简要介绍以下几点：
1：全文索引支持char、varchar、text类型字符内容的搜索。
2：MySQL的全文索引只有全部在内存中的时候，性能才非常好。如果内存无法装载全部索引，那么性能可能会非常慢（可以为全文索引设置单独的键缓存（key cache），保证不会被其他的索引缓存挤出内存）
3：相比其它的索引类型，当insert、update和delete操作进行时，全文索引的操作代价非常大。而且全文索引会有更多的碎片，可能需要做更多的optimize table操作。
4：一旦使用了全文索引，即便这时有更合适的索引可用，MySQL也会放弃性能比较，置之不理。
5：全文索引不存储索引列的实际值，也就不可能用作索引覆盖扫描。
6：除了相关性排序，全文索引不能用作其他的排序。如果查询需要做相关性以外的排序操作，都需要使用文件排序。

全文索引限制就罗列这么多，基于这些限制，在选择使用MySQL的全文索引时，需要谨慎考虑。
在次感谢您的阅读，不足之处欢迎指正讨论。