MySQL数据库全文索引的简单使用方法

最简单的处理手段就是使用mysql自带的全文索引来实现基础的查询（如果要使用ES等花费代价太大）。
0x00 概念
全文索引（FULL TEXT INDEX）与其他索引类似， 只是它会拆分指定列内容里面的词语， 并分析出现的频率。
0x01 版本支持
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况:
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
0x02 全文索引的配置
执行SHOW VARIABLES LIKE '%ft_%':
上图中下你是了与全文索引有关的2个分词长度(最大/小搜索长度)设置:
MyISAM: ft_max_word_len, ft_min_word_len
InnoDB: inodb_ft_max_token_size, innodb_ft_min_token_size
有关min的配置, 是分词最小长度. 对于英文来说, 是直接按空格来拆分单词, 中文分词就需要用到组件ngram. 对于拆分出来的词语长度不小于最小配置的词才进行索引(统计)。与分词有关的"停止词"(stopword)请自行度娘。
innodb引擎默认只对单词长度 >= 3的进行索引，我们先设置为1试试。
在mysql配置文件的mysqld节点下添加设置:
[mysqld]
innodb_ft_min_token_size = 1
然后重启服务。
0x03 全文索引操作
-- 建表
CREATE TABLE `test` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`content` text NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB;
-- 建索引
ALTER TABLE `test` ADD FULLTEXT INDEX `FIX_content`(`content`) WITH PARSER ngram; /*这里必须指定中文分词器*/
-- 插入数据
INSERT INTO `test` (`content`) VALUES ('我'),('你'),('他')
,('我们'),('你们'),('他们')
,('我们的'),('你们的'),('他们的')
,('我们的爱'),('你们的猫'),('他们的狗')
,('我们的爱永久'),('你们的猫很可爱'),('他们的狗很凶猛')
,('我们的爱永久不变'),('你们的猫很可爱的样子'),('他们的狗很凶猛的样子');
如果修改了单词长度限制参数, 则建议重建索引, 先执行 ALTER TABLE test DROP INDEX FIX_content然后再add即可。
和普通索引一样, 全文索引页支持对多了操作:
ALTER TABLE tbname ADD FULLTEXT INDEX ix_name(col1, col2, ...)
0x04 全文索引的查询
语法是:
MATCH(col [,col...]) AGAINST('keyword')
那我们来查询看看:
SELECT *, MATCH(content) AGAINST('我们') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们');
执行结果:
这个查询结果比较正常，是我们所期望的。
再看一下mysql自动分词的查询:
SELECT *, MATCH(content) AGAINST('我们的爱') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们的爱');
执行结果:
默认是按相关度进行排序的， 但是后面那几个数据，相关度太低, 就不是我们想要的了。这个是否与我们前面设置的最小分词数等于1有关呢？
我们现在去设置 innodb_ft_min_token_size = 2:
[mysqld]]
innodb_ft_min_token_size = 2
重启mysql, 重建全文索引:
ALTER TABLE `test` DROP INDEX `FIX_content`;
ALTER TABLE `test` ADD FULLTEXT INDEX `FIX_content`(`content`) WITH PARSER ngram; /*这里必须指定中文分词器*/
通过上图的参数查询结果能看到, 设置已经生效. 然后重新执行查询:
SELECT *, MATCH(content) AGAINST('我们的爱') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们的爱');
结果还是那样, oh my god! 从测试来看，类似“的”这种助词，mysql8.0 默认的全文索引查询仍然没有处理好。
我们换个关键词:
SELECT *, MATCH(content) AGAINST('我们不变') AS rel FROM `test` WHERE MATCH(content) AGAINST('我们不变');
上面我们查询"我们不变", 系统拆分并自动匹配了"我们" 和"不变"。
我们现在配置的最小搜索长度是2, 那我们搜索一个字行不行呢?
sorry, 搜索词长度 < 全文索引设置的最小搜索长度 是没有结果的。
所以这个最小搜索长度, 无法限制mysql查询时分词的长度.
0x05 两种全文索引
自然语言的全文索引 (默认)
默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。
自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。
这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。
布尔全文索引
在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。
MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册
+ 必须包含该词
- 必须不包含该词
> 提高该词的相关性，查询的结果靠前
< 降低该词的相关性，查询的结果靠后
(*) 星号 通配符，只能接在词后面