MySQL5.7建立全文索引与基本使用

（mysql字段，认为非字母数字，都是分词符，比如：a|b，那么就已经认为a和b是一个词，在分词时就会拆分出来）

MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。

[mysqld] 
ngram_token_size=2

查看设置

show variables like '%ngram_token_size%';

全文检索模式

常用的全文检索模式有两种：

• 自然语言模式（默认模式，不支持操作符）
• boolean 模式（支持使用操作符，比如 +，-等语法）

不加操作符：表示该词是可选的，含有该词相关性将更高（如 against(‘安卓 分类’) in boolean mode）

+ ：一定要有该关键词（不含有的数据条目将忽略）

- ：不可以有该关键词（排除指定关键词，含有该词的条目将忽略）

> ：提高该词的权重（影响结果排序）

< ：降低该词的权重

~ ：将该关键词权重由正转负（含有该词的条目，将降低权重排在后面）

* ：关键字后面，表示通配符

“” ：双引号括起来表示为一个短句（可用来解决空格短句问题，如 against(’“安卓 分类”’ in boolean mode)，此处"安卓 分类"是一个不可拆分的短句，而不是搜索含有安卓或分类两词的结果）

查询示例：（默认模式）

SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路 一带');

该操作相当于：（显示指定模式）

SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('一路 一带' IN NATURAL LANGUAGE MODE);

使用 boolean 模式，更精确的控制：

// 必须包含"腾讯"，但是不能包含"通讯工具"
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+腾讯 -通讯工具' IN BOOLEAN MODE);

注意：

• MATCH (columnName) AGAINST ('keywords')。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。也就是说，建立全文索引时，指定了(title, body)就必须是这两个一起，如果只想 match ( title )，则需要单独再建立一个全文索引在 title 上，其他同理。
• 另外多个字段不能跨表，也就是  title ，body 应该在一张表上。

返回结果的排序？

上面的示例返回结果会自动按照相关性排序，相关性高的在前面。相关性的值是一个非负浮点数，0表示无相关性。

// 获取相关性的值，按 score 降序排序
SELECT id,title,
MATCH (title,body) AGAINST ('手机' IN NATURAL LANGUAGE MODE) AS score
FROM articles
ORDER BY score DESC;

另外这里有个坑，也就是默认情况下，如果不 limit ，是返回所有数据的，即使它的 score 为 0。

查询作为条件：

SELECT * FROM bp3_cache_file where MATCH (path) AGAINST ('美女' IN NATURAL LANGUAGE MODE);

select * from bp3_cache_file where path like '%美女%';

作为条件时，就和 like 相似，测试了一下当前 16w 行数据，执行的结果分别是：0.6秒、0.8秒。

考虑设置大一些的缓存（设置为20%可用ram）：

show variables like 'key_buffer%';

得到的结果 /1024/1042 ，即可知道为多少 mb，一般是 32 mb或128mb，可考虑增加这个值（对MyIsam引擎有效）

对 innodb 引擎，该参数是：innodb_buffer_pool_size （设置为 70% 可用ram）

临时设置：（或修改 my.cnf）

set global key_buffer_size=512*1024*1024;

最佳实践

1h2g服务器（centos7），使用MySQL5.7.31，使用InnoDB引擎，单表2000万记录（单表体积2g），全文检索（N-gram）单字段。搜索（自然语言模式）响应速度0.3-0.4秒（同等 like，十几秒或几十秒）。

如果数据再大，全文检索性能可能断崖式下跌，此时的性能瓶颈容易发生在cpu和内存。

在不同版本的MySQL，不同系统，不同cpu或内存，都会影响全文检索的速度，2000w数据，即使是1h2g这样的最低配置，仍毫无压力，效果非常棒（能自动分词，比如输入一个句子，自动拆分为不同的单词并自动搜索，响应速度也是绝佳，小型的搜索引擎）。

文章作者: 朱丰华

文章链接: https://smart.52dixiaowo.com/blog/post-52.html

版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。