MYSQL全文索引的深入理解

袁志蒙 次浏览

摘要:在开发中经常会有这样的一个功能:就是一篇文章,可能会添加多个TAG标签,而数据库设计的话通常是用一个字段来存储这些标签的,如字段名为“tag”的值为“1,3,4,5,7”这样的,用户可能会通过这些标签...

在开发中经常会有这样的一个功能:

就是一篇文章,可能会添加多个TAG标签,而数据库设计的话通常是用一个字段来存储这些标签的,如字段名为“tag”的值为“1,3,4,5,7”这样的,用户可能会通过这些标签来搜索相关内容,之前如果想查询标签包含1的内容的时候,用的都是 find_in_set('1',tag) 这样的,那么现在问题来了,如果我想查询包含两个或者两个以上的时候,怎么查询呢?

要用 find_in_set('2',tags) OR find_in_set('4',tags),这样的查询方式吗?

这样的查询也能出现结果,但似乎并不是最好的,而且用 find_in_set 用不到索引,如果数据量大的话,执行时间就更别提了,于是用到了全文索引,不仅能用到索引,执行效率也很高!

添加索引:

ALTER TABLE `yzm_test` ADD FULLTEXT INDEX (`tag`) ;

添加完全文索引后,我们就可以用全文索引来检索了:

SELECT * FROM `yzm_test` WHERE MATCH(tag) AGAINST ('1');

奇怪了,怎么没有查询出结果?

原来是MySQL指定了最小字符长度,默认是4,必须要匹配大于4的才会有返回结果,可以用 SHOW VARIABLES LIKE 'ft_min_word_len'  来查看指定的字符长度,也可以在mysql配置文件my.ini 更改最小字符长度,方法是在my.ini 增加一行 比如:ft_min_word_len = 2,改完后重启mysql即可。

有时候我们无法修改mysql的配置,这时候,我们就需要在表中通过添加“前缀”的方式来增加字符的长度了,比如之前的“1”我们可以变成“tag1”,“2”我们可以变成“tag2”....


嗯,这个问题解决了,但又出现新的问题了,有的时候,查询一个词,明明很多条数据都包括这个词,但返回的结果却是空的,原来MySQL还会计算一个词的权值,以决定是否出现在结果集中,具体如下:


mysql在集和查询中的对每个合适的词都会先计算它们的权重,一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重),因为在这个特定的集中,它有较低的语义值。否则,如果词是较少的,它将得到一个较高的权重,mysql默认的阀值是50%,上面‘you’在每个文档都出现,因此是100%,只有低于50%的才会出现在结果集中。


使用检索方式“IN BOOLEAN MODE”

比如说,每个行都有this这个字的话,那用this去查时,会找不到任何结果,这在记录条数特别多时很有用,原因是数据库认为把所有行都找出来是没有意义的,这时,this几乎被当作是stopword(中断词);但是若只有两行记录时,是啥鬼也查不出来的,因为每个字都出现50%(或以上),要避免这种状况,就要用到布尔全文检索:IN BOOLEAN MODE。


布尔全文检索语法:

“+”表示必须包含

“-”表示必须排除

“>”表示出现该单词时增加相关性

“<”表示出现该单词时降低相关性

“*”表示通配符

“~”允许出现该单词,但是出现时相关性为负

“""”表示短语


例子:

SELECT * FROM `yzm_test` WHERE MATCH(tag) AGAINST ('tag1 tag2' IN BOOLEAN MODE);
tag1 和 tag2 之间是空格,空格表示OR,即至少包含 tag1 或者 tag2 中的一个。

SELECT * FROM `yzm_test` WHERE MATCH(tag) AGAINST ('+tag1 -tag2' IN BOOLEAN MODE);
必须包含tag1 ,并且不包含 tag2

SELECT * FROM `yzm_test` WHERE MATCH(tag) AGAINST ('+tag1 tag6' IN BOOLEAN MODE);
必须包含tag1 ,但是如果同时也包含 tag6 则会获得更高的权重。

SELECT * FROM `yzm_test` WHERE MATCH(tag) AGAINST ('+tag1 ~tag6' IN BOOLEAN MODE);
~ 是我们熟悉的异或运算符。返回的记录必须包含 tag1 ,但是如果同时也包含 tag6 会降低权重。但是它没有 +tag1 -tag6 严格,因为后者如果包含 tag6 压根就不返回。

SELECT * FROM `yzm_test` WHERE MATCH(tag) AGAINST ('+tag1 +(>tag2 <tag6)' IN BOOLEAN MODE);
返回同时包含 tag1 和 tag2 或者同时包含 tag1 和 tag6 的记录。但是同时包含 tag1 和 tag2 的记录的权重高于同时包含 tag1 和 tag6 的记录。

注意:

A. 只有存储引擎类型为MyISAM类型的表,并且MySQL版本为4.X或者以上才能使用MySQL内置的全文检索支持

B. MySQL全文检索默认不支持中文,且对英文检索时忽略大小写

C. MySQL全文检索时,默认检索长度为4,即关键词的长度必须大于5才能被捕获

D. MySQL全文检索时,所有FULLTEXT索引列必须使用相同的字符集

E. MySQL全文检索返回结果集时还会考虑权重

F. MySQL全文检索还支持灵活的布尔全文检索模式


更新:

MySQL 5.6.4以上版本InnoDB引擎支持全文索引

MySQL 5.7开始,MySQL内置了ngram全文检索插件,用来支持中文分词,并且对MyISAM和InnoDB引擎有效。

随机新闻

表情

共0条评论
  • 这篇文章还没有收到评论,赶紧来抢沙发吧~