Mysql全文索引的使用

前言

在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词.

在学习之前,请确认自己的MySQL版本大于5.7.6.我的版本为5.7.20.同时文中的所有操作都基于InnoDB存储引擎.

什么是全文索引?

如果有搞过lucene,solr,es之类的,理解起来会方便许多.

日常我们使用MySQL查询时,大部分的查询都是定值或者范围查询.类似于下面这样:

代码语言：javascript复制

select *
from table 
where id = 1

select *
from table 
where id > 20

但是当在MySQL中存储了文本,比如某个字段的值为坚决贯彻党的十八大精神,我们想用贯彻和十八大作为关键字时都可以搜索到这条记录.那么只能使用like关键字.而对于like我们知道,当不是用左边前缀搜索的时候,无法命中索引,因此对于这条语句select * from articles where content like '%贯彻%',MySQL只能进行全表扫描,逐一进行匹配.这样的效率极其低下.

而全文索引呢,通过建立倒排索引,可以极大的提升检索效率.

倒排索引（英语：Inverted index），也常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。

对于倒排索引,这里不再展开,有兴趣的朋友可以自行了解一下.

目前,MySQL仅可以在char,varchar,text属性的列上建立全文索引.

如何创建全文索引?

创建全文索引的时机与创建其他类型的索引没什么不同,可以在建表时候创建,也可以通过alter语句创建.这里贴一下建表的同时建立全文索引.

代码语言：javascript复制

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

上述语句建立了一个article表,且对其中的title和body字段建立了全文索引.

使用alter语句建立索引示例如下:

代码语言：javascript复制

ALTER TABLE articles ADD FULLTEXT INDEX title_body_index (title,body) WITH PARSER ngram;

如何使用全文索引进行搜索?

MySQL的全文索引查询有多种模式,我们一般经常使用两种.

1. 自然语言搜索

就是普通的包含关键词的搜索.

2. BOOLEAN MODE

这个模式和lucene中的BooleanQuery很像,可以通过一些操作符,来指定搜索词在结果中的包含情况.比如嘻哈表示必须包含嘻哈, -嘻哈表示必须不包含,默认为误操作符,代表可以出现可以不出现,但是出现时在查询结果集中的排名较高一些.也就是该结果和搜索词的相关性高一些.

具体包含的所有操作符可以通过MySQL查询来查看:

代码语言：javascript复制

mysql> show variables like '%ft_boolean_syntax%';
 ------------------- ---------------- 
| Variable_name     | Value          |
 ------------------- ---------------- 
| ft_boolean_syntax |   -><()~*:""&| |
 ------------------- ---------------- 
1 row in set (0.05 sec)

使用自然语言搜索如下:

代码语言：javascript复制

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神' IN NATURAL LANGUAGE MODE);
 ---- ----------------- ------------------------- 
| id | title           | body                    |
 ---- ----------------- ------------------------- 
|  1 | 弘扬正能量      | 贯彻党的18大精神        |
 ---- ----------------- ------------------------- 
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神');
 ---- ----------------- ------------------------- 
| id | title           | body                    |
 ---- ----------------- ------------------------- 
|  1 | 弘扬正能量      | 贯彻党的18大精神        |
 ---- ----------------- ------------------------- 
1 row in set (0.00 sec)

可以看到,搜索结果命中了一条,且在不指定搜索模式的情况下,默认模式为自然语言搜索.

使用boolean搜索如下:

代码语言：javascript复制

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' 精神' IN BOOLEAN MODE);
 ---- ----------------- ------------------------- 
| id | title           | body                    |
 ---- ----------------- ------------------------- 
|  1 | 弘扬正能量      | 贯彻党的18大精神        |
 ---- ----------------- ------------------------- 
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST (' 精神 -贯彻' IN BOOLEAN MODE);
Empty set (0.01 sec)

当搜索必须命中精神时,命中了一条数据,当在加上不能包含贯彻的时候,无命中结果.

总结

InnoDB支持全文索引,当然是个好消息,在一些小的全文搜索场景下,可以只使用关系型数据库就搞定了.

他的效率比起like当然是高了不少,但是我没有测试过在千万级数据量下的搜索效率,因为搞出千万级的测试数据是在是太麻烦了.不过我想在大数据量的情景下表现应该不是很好.

对于全文索引的需求,如果只是很小的数据量,且对搜索结果的精确度和可定制化程度要求不高的话,可以使用MySQL的全文索引,如果是专门的做搜索,对搜索中的分词以及结果都有较高的要求,建议还是使用lucene,es相关的哪一套全文搜索工具包来做.

参考文章

https://www.jianshu.com/p/c48106149b6a

完。

ChangeLog

2019-05-19 完成

以上皆为个人所思所得，如有错误欢迎评论区指正。

欢迎转载，烦请署名并保留原文链接。

联系邮箱：huyanshi2580@gmail.com

更多学习笔记见个人博客——>呼延十

数据库 sql 全文检索云数据库SQLServer lucene/solr

0 人点赞

Mysql全文索引的使用

目录

前言

什么是全文索引?

如何创建全文索引?

如何使用全文索引进行搜索?

总结

参考文章

ChangeLog