mysql 中文全文搜索总结

最新推荐文章于 2024-10-10 14:59:46 发布

藏羚骸

最新推荐文章于 2024-10-10 14:59:46 发布

阅读量1.4k

点赞数

分类专栏： mysql sql 踩坑文章标签： mysql sql

本文链接：https://blog.csdn.net/lin5188230/article/details/124034532

版权

踩坑同时被 3 个专栏收录

4 篇文章 0 订阅

订阅专栏

mysql

2 篇文章 0 订阅

订阅专栏

sql

1 篇文章 0 订阅

订阅专栏

转载请注明：藏羚骸的博客~mysql 中文全文搜索总结.

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。
从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。
本文使用的MySQL 版本是5.7.22，InnoDB数据库引擎。

为什么要用全文索引呢？

一般的数据库搜索都是用的SQL 的 like 语句，like 语句是不能利用索引的，每次查询都是从第一条遍历至最后一条，查询效率极其低下。一般数据超过10万或者在线人数过多,like查询都会导致数据库崩溃。这也就是为什么很多程序都只提供标题搜索的原因了，因为如果搜索内容，那就更慢了，几万数据就跑不动了。

Mysql 全文索引是专门为了解决模糊查询提供的，可以对整篇文章预先按照词进行索引，搜索效率高，能够支持百万级的数据检索。

如果您使用的是自己的服务器，请马上进行设置，不要浪费了这个功能。

创建倒排索引

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名称 (字段1,字段2,字段3) WITH PARSER ngram;

注意：后面要加上WITH PARSER ngram 才可以进行中文检索，ngram是一个全文解析器
且建立MySQL倒排索引时，尽量用一条语句去创建多个字段的倒排索引，比如你需要将三个字段都创建上倒排索引，那么不要用三条语句去执行，否则可能会出现 “找不到倒排索引的问题”。

mysql原生全文解析器(ngram)

MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。因为中文单词最少是两个汉字，推荐使用默认值2。如果你想查询到单个字，那么我们需要设置为1。ngram_token_size的值设置的越小，全文索引占用的空间也越小。一般来说，查询正好等于ngram_token_size的词，速度会更快，但是查询比它更长的词或短语，则会变慢。

show VARIABLES like 'ngram_token_size';

可以看的我的分词长度为2 也就是说如果输入“我爱学习”四个字，那么就会拆分成 “我爱”，“爱学”，“学习”，最小两个字进行检索，但MySQL默认此长度为4，搜索较长的字符没有问题，搜索四个字以下的内容就不能正常使用了。

修改方法：在my.ini文件下的 [mysqld] 下面加上 ngram_token_size = 2 即可，当然也可以设置成1。如果是Linux系统则修改my.cnf文件。

修改完成后重启 MySQL服务，重新建立倒排索引后方可生效。

检索语句

MySQL全文检索语句如下：

SELECT * FROM 表名 WHERE MATCH(列名1,列名2) AGAINST(检索内容1 检索内容2);

检索方式

1、自然语言检索： IN NATURAL LANGUAGE MODE（‘我爱学习’中我爱||爱学||学习的所有量都拿到）

     2、布尔检索： IN BOOLEAN MODE(和模糊查询%{$val}%几乎一样，但是大数据下效率比模糊查询高太多)
          剔除一半匹配行以上都有的词，譬如说，每个行都有this这个字的话，那用this去查时，会找不到任何结果，这在记录条数特别多时很有用，
          原因是数据库认为把所有行都找出来是没有意义的，这时，this几乎被当作是stopword(中断词)；但是若只有两行记录时，是啥鬼也查不出来的，
          因为每个字都出现50%（或以上），要避免这种状况，请用IN BOOLEAN MODE。

       ● IN BOOLEAN MODE的特色：
          ·不剔除50%以上符合的row。
          ·不自动以相关性反向排序。
          ·可以对没有FULLTEXT index的字段进行搜寻，但会非常慢。
          ·限制最长与最短的字符串。
          ·套用Stopwords。

       ● 搜索语法规则：
         +   一定要有(不含有该关键词的数据条均被忽略)。
         –   不可以有(排除指定关键词，含有该关键词的均被忽略)。
         >   提高该条匹配数据的权重值。
         <   降低该条匹配数据的权重值。
         ~   将其相关性由正转负，表示拥有该字会降低相关性(但不像 – 将之排除)，只是排在较后面权重值降低。
         *   万用字，不像其他语法放在前面，这个要接在字符串后面。
         ” ” 用双引号将一段句子包起来表示要完全相符，不可拆字。

SELECT * FROM articles WHERE MATCH (title,content) AGAINST (‘+apple -banana’ IN BOOLEAN MODE);
+ 表示AND，即必须包含。- 表示NOT，即必须不包含。即：返回记录必需包含 apple，且不能包含 banner。

SELECT * FROM articles WHERE MATCH (title,content) AGAINST (‘apple banana’ IN BOOLEAN MODE);
apple和banana之间是空格，空格表示OR。即：返回记录至少包含apple、banana中的一个。

SELECT * FROM articles WHERE MATCH (title,content) AGAINST (‘+apple banana’ IN BOOLEAN MODE);
返回记录必须包含apple，同时banana可包含也可不包含，若包含的话会获得更高的权重。

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST (‘+apple ~banana’ IN BOOLEAN MODE);
         ~ 是我们熟悉的异或运算符。返回记录必须包含apple，若也包含了banana会降低权重。
         但是它没有 +apple -banana 严格，因为后者如果包含banana压根就不返回。

         SELECT * FROM articles WHERE MATCH (title,content) AGAINST (‘+apple +(>banana <orange)’ IN BOOLEAN MODE);
         返回必须同时包含“apple banana”或者必须同时包含“apple orange”的记录。
         若同时包含“apple banana”和“apple orange”的记录，则“apple banana”的权重高于“apple orange”的权重。

3、查询扩展检索： WITH QUERY EXPANSION

mysql全文搜索注意事项：

只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
全文索引只支持InnoDB和MyISAM引擎。
MATCH (columnName) AGAINST (‘keywords’)。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的。

举例

ngram_token_size=2时,搜索单字时

当为布尔检索时

select id,title,urlpath,akey from  audios where MATCH  (title,akey)  AGAINST  ('{$key}' IN BOOLEAN MODE

当为自然语言时

select id,title,urlpath,akey from  audios where MATCH  (title,akey)  AGAINST  ('{$key}' IN NATURAL LANGUAGE MODE

select id,title,urlpath,akey, MATCH (title,akey) AGAINST ('{$key}') as id from audios where MATCH (title,akey) AGAINST ('{$key}' IN NATURAL LANGUAGE MODE

查询结果，默认会按照id ，从高到低排序

做爬虫是爬取了几十万的数据，衍生品中使用了全文索引

爬虫小马甲-音频

转载请注明：藏羚骸的博客~mysql 中文全文搜索总结.