MySQL 索引之FULLTEXT的配置及使用

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `col` )

UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE (`col`)

INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name (`col`)

FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `col` )

组合索引 (复合索引)

ALTER TABLE `table_name` ADD INDEX index_name (`a`, `b`, `c` )

全文索引的时候一般就是like '%key%' 这样的形式了，要么就是建个全文检索的服务，然后配置中文分词之类的

今天给介绍一下mysql自带的fulltext索引，默认支持中文分词，数量太大的还是建议redisSearch或ES或sphinx等其它的手段吧

中小的话非常实用

开搞

第一步，检查mysql是否有插件

show plugins

看看是否有个叫ngram，反正我用的5.7和8.0里面都有…据传默认自带

第二步，配置my.ini中修改或增加相关参数

ngram_token_size = 2
ft_max_word_len    = 84
ft_min_word_len = 2

默认这个ft_min_word_len是4，也就是说关键词两个字的时候是搜不到的，这样我折腾了好久，其实改小点，让分词更细化就搜到了，同样的代价就是mysql的缓存体量更大了
配置完参数后，可以重启服务，通过show variables like 'ft%' 和 show variables like '%ngram%'查看相关参数是否生效

第三步创建索引

网上说的是，就是说要带上ngram解析器

ALTER TABLE `table_name` ADD FULLTEXT INDEX `content` (`content`) WITH PARSER ngram;

我在本地8.0测试的时候，它是不需要后面的尾巴的，所以我的创建sql就成了

ALTER TABLE `表名` ADD FULLTEXT INDEX `索引名` ( `字段名`)
或
ALTER TABLE `表名` ADD FULLTEXT(`字段名`);

要注意的是，字段只支持VARCHAR、TEXT、CHAR这三类

第四步使用

SELECT * FROM `表名` WHERE MATCH (`content`) AGAINST ('滔哥');

可以通过explain 检查一下type 是不是走了索引

这样就OK了…余下的工作就是…将以前的like换换或者在sql中做个判断之类的

其它

三种全文搜索模式
1：自然语言模式（IN NATURAL LANGUAGE MODE，默认情况下为该模式）
例：

SELECT * FROM user WHERE MATCH(userName) AGAINST (‘张三’ );

如果最小搜索长度为1的话，则查找包含张，或三，或张三的记录；与布尔搜索模式中的‘+张三’结果相同

2：布尔搜索模式（IN BOOLEAN MODE）

【+】----------必须包含此字符串
【-】----------必须不包含此字符串
【""】--------双引号内作为整体不能拆词
【>】--------提高该词的相关性，查询的结果靠前
【<】--------降低该词的相关性，查询的结果靠后
【*】---------通配符，只能接在词后面

等+ -><()~*:""&|
例：

SELECT * FROM user WHERE MATCH(userName) AGAINST (’+“美女” & +“动人”’ IN BOOLEAN MODE);

查询有‘美女’的又有‘动人’的记录

3：查询扩展搜索
具体去mysql官方找吧…

吕滔博客