MySQL全文索引使用注意事项

前文我做了mysql全文性能测试，测试中发现使用MySQL全文索引的一些注意事项，特此记录分享。

配置

my.cnf配置（配置完了别忘了重启mysql）如下：

[mysqld]
#英文分词长度默认为4，<4通常不建索引；英文单词长度<4的通常查询不到
ft_min_word_len = 4  
 #中文分词长度位2，每个字都可以查出
ngram_token_size=2

增加全文索引

增加全文索引：
alter table table_name add fulltext index idxName(column1,…);
alter table table_name add fulltext index idxName(column1,…) with parser ngram; – 使用中文分词

全文检索模式

常用的全文检索模式有两种：
2.1 自然语言模式(NATURAL LANGUAGE MODE) ，
自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
2.2 BOOLEAN模式(BOOLEAN MODE)
BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。
BOOLEAN模式下运算符的使用方式：
‘apple banana’ 无操作符，表示或，要么包含apple，要么包含banana
‘+apple +juice’ 必须同时包含两个词
‘+apple macintosh’ 必须包含apple，但是如果也包含macintosh的话，相关性会更高。
‘+apple -macintosh’ 必须包含apple，同时不能包含macintosh。
‘+apple ~macintosh’ 必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。
‘+apple +(>juice <pie)’ 查询必须包含apple和juice或者apple和pie的记录，但是apple juice的相关性要比apple pie高。
‘apple*’ 查询包含以apple开头的单词的记录，如apple、apples、applet。
‘“some words”’ 使用双引号把要搜素的词括起来，效果类似于like ‘%some words%’，即把引号内的内容做一个完整匹配。例如“some words of wisdom”会被匹配到，而“some noise words”就不会被匹配。

使用注意事项

1、 只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
2、 全文索引只支持InnoDB和MyISAM引擎。
3、 MATCH (columnName) AGAINST (‘keywords’)。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
4、 如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的。
5、 如果不是英文单词，是字符串，用串中子字符串查不到（同分词长度有一定关系）；但如果串中是用空格、‘,’,‘，’,';'等符号隔开，则可以查询；举例如下:
'304402207854CD95FFCE01D6A211CEAEDA0057007FCE61AD789F73C1BAABAA1283E1DCDE02201DA1B56B1A812EC7D4DCFEDE8D761B429197E77E72E8F6A745A8E7421EC10D34’ 查’0220’是查不到的
'3044 0220,7854CD95FFCE01D6A211CEAEDA0057007FCE61AD789F73C1BAABAA1283E1DCDE02201DA1B56B1A812EC7D4DCFEDE8D761B429197E77E72E8F6A745A8E7421EC10D34’ 查’0220’则可查到
6、如果一个字段中英文混合，用英文模式建，默认单词长度位4。查中文要求：查询内容3个字或以上，且是单独句型，前后用语法分隔符分隔的；用中文模式建，英文查找慢，且结果不正确，匹配项大量增加
7、 多字段共建成一个全文索引，则存储少，但查询速度慢。
8、 多字段共建成一个全文索引，如果中英文混合，按英文模式建，中文（查询内容3个字或以上，且是单独句型，前后用语法分隔符分隔的）；按中文模式建，中文查找模式准确，也快，但英文查找慢且匹配项大量增加
9、设计建议：
9.1 一个表最好只有一个全文索引字段；
9.2 字段内容最好是全英文或全中文。

我做的mysql全文性能测试，见链接：Mysql全文索引性能测试对比