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官方找吧…