Mysql使用全文索引(FullText index)

什么是全文索引

全文索引,通过建立倒排索引,可以极大的提升检索效率,解决判断字段是否包含的问题. 例如: 有title字段,需要查询所有包含 "政府"的记录. 需要 like "%政府%"方式查询,查询速度慢,当查询包含"政府" OR "中国"的需要是,sql难以简单满足.全文索引就可以实现这个功能.

倒排索引（英语：Inverted index），也常被称为反向索引、置入档案或反向档案，是一种索引方法，

被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。

它是文档检索系统中最常用的数据结构。

注意

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

创建全文索引

• 创建表的时候创建

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='文章表';

创建了一个给title和body字段添加全文缩影的表

• 给字段添加全文索引 ALTER TABLE articles ADD FULLTEXT INDEX title_body_index (title,body) WITH PARSER ngram;

使用全文索引

MySQL的全文索引查询有多种模式

自然语言搜索

普通的判断是否包含

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 MODE

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

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

show variables like '%ft_boolean_syntax%'

+-------------------+----------------+
| Variable_name     | Value          |
+-------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
+-------------------+----------------+
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)
当搜索必须命中精神时,命中了一条数据,当在加上不能包含贯彻的时候,无命中结果.