一、什么是全文索引
全文索引，通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题。例如：有title字段，需要查询所有包含 "冬奥会"的记录。需要 like "%冬奥会%"方式查询，由于这种方式会导致索引失效，所以查询速度慢，全文索引则可以弥补这个缺陷。
1.1 倒排索引
倒排索引（英语：Inverted index），也常被称为反向索引、置入档案或反向档案，是一种索引方法，被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构。
1.2 MySQL各版本全文索引
在MySQL 5.6版本以前，只有MyISAM存储引擎支持全文引擎。在5.6版本中，InnoDB加入了对全文索引的支持，但是不支持中文全文索引。在5.7.6版本，MySQL内置了ngram全文解析器，用来支持亚洲语种的分词。
二、MySQL使用全文索引
2.1 创建全文索引
2.1.1 建表时创建全文索引
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='文章表';
INSERT INTO articles (title, body) VALUES ('弘扬正能量', '贯彻党的18大精神');
INSERT INTO articles (title, body) VALUES ('北京冬奥会', '2022年北京冬奥会于2022年2月20日闭幕啦');
INSERT INTO articles (title, body) VALUES ('MySQL Tutorial', 'DBMS stands for Database');
INSERT INTO articles (title, body) VALUES ('IBM History', 'DB2 history for IBM');
2.1.2 给现有表字段添加全文索引
ALTER TABLE articles ADD FULLTEXT INDEX title_body_index (title,body) WITH PARSER ngram;
2.2 使用全文索引
MySQL全文搜索使用 MATCH() AGAINST()语法进行，其中，MATCH()采用逗号分隔的列表，命名要搜索的列。AGAINST()接收一个要搜索的字符串，以及一个要执行的搜索类型的可选修饰符。全文检索分为三种类型：自然语言搜索、布尔搜索、查询扩展搜索。
2.3 自然语言搜索-Natural Language
自然语言搜索将搜索字符串解释为自然人类语言中的短语，MATCH()默认采用 Natural Language 模式，其表示查询带有指定关键字的文档。
2.3.1 普通的判断是否包含：
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)
可以看到，搜索结果命中了一条，且在不指定搜索模式的情况下，默认模式为自然语言搜索。
2.3.2 通过SQL语句查询相关性
SELECT *,MATCH ( title, body ) against ( '精神' ) AS relevance FROM articles;
id title body relevance
1 弘扬正能量 贯彻党的18大精神 0.0906190574169159
2 北京冬奥会 2022年北京冬奥会于2022年2月20日闭幕啦 0
相关性的计算依据以下四个条件：
- word 是否在文档中出现
- word 在文档中出现的次数多
- word 在索引列中的数量
- 多少个文档包含该 word
对于 InnoDB 存储引擎的全文检索，还需要考虑以下的因素：
- 查询的 word 在 stopword 列表中，忽略该字符串的查询
- 查询的 word 字符长度是否在区间 [innodb_ft_min_token_size,innodb_ft_max_token_size] 内
2.4 布尔搜索-BOOLEAN MODE
这个模式和lucene中的BooleanQuery很像，可以通过一些操作符，来指定搜索词在结果中的包含情况。比如 + 表示必须包含 ， -表示必须不包含，默认为误操作符，代表可以出现可以不出现，但是出现时在查询结果集中的排名较高一些。也就是该结果和搜索词的相关性高一些。
具体包含的所有操作符可以通过MySQL查询来查看：
show variables like '%ft_boolean_syntax%'
+-------------------+----------------+
| Variable_name | Value |
+-------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
+-------------------+----------------+
Boolean全文检索支持的类型包括：
- +：表示该 word 必须存在
- -：表示该 word 必须不存在
- (no operator)表示该 word 是可选的，但是如果出现，其相关性会更高
- @distance表示查询的多个单词之间的距离是否在 distance 之内，distance 的单位是字节，这种全文检索的查询也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)语句表示字符串 Pease 和 hot 之间的距离需在30字节内
- >：表示出现该单词时增加相关性
- <：表示出现该单词时降低相关性
- ~：表示允许出现该单词，但出现时相关性为负
- * ：表示以该单词开头的单词，如 lik*,表示可以是 lik，like，likes
- " ：表示短语
2.4.1 +
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神' IN BOOLEAN MODE);
+----+-----------------+-------------------------+
| id | title | body |
+----+-----------------+-------------------------+
| 1 | 弘扬正能量 | 贯彻党的18大精神 |
+----+-----------------+-------------------------+
1 row in set (0.00 sec)
上述语句，查询的是包含 '精神' 的信息。
2.4.2 + -
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神 -贯彻' IN BOOLEAN MODE);
Empty set (0.01 sec)
上述语句，查询的是包含 '精神' 但不包含 '贯彻' 的信息。当搜索必须命中精神时，命中了一条数据，当在加上不能包含贯彻的时候，无命中结果。
2.4.3 no operator
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神' IN BOOLEAN MODE);
上述语句，查询的 '精神' 没有 '+'，'-'的标识，代表 word 是可选的，如果出现，其相关性会更高。
2.4.4 @
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"弘扬 能量"@3' IN BOOLEAN MODE);
上述语句，代表 "弘扬" ，"能量"两个词之间的距离在3字节之内。
2.4.5 > <
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+弘扬 +(>贯彻 <精神)' IN BOOLEAN MODE);
上述语句，查询同时包含 '弘扬'，'贯彻'，'精神' 的行信息，但不包含'精神'的行的相关性高于包含'精神'的行。
2.4.6 ~
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('贯彻 ~精神' IN BOOLEAN MODE);
上述语句，查询包含 '贯彻' 的行，但如果该行同时包含 '精神'，则降低相关性。
2.4.7 *
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('贯彻*' IN BOOLEAN MODE);
上述语句，查询关键字中包含'贯彻'的行信息。
2.4.8 "
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('"贯彻"' IN BOOLEAN MODE);
上述语句，查询包含确切短语 '贯彻' 的行信息。
2.5 查询扩展搜索-Query Expansion
查询扩展搜索是对自然语言搜索的修改，这种查询通常在查询的关键词太短，用户需要implied knowledge（隐含知识）时进行，例如，对于单词 database 的查询，用户可能希望查询的不仅仅是包含 database 的文档，可能还指那些包含 MySQL、DB2、RDBMS 的单词，而这时可以使用 Query Expansion 模式来开启全文检索的implied knowledge通过在查询语句中添加 WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION可以开启blind query expansion（又称为 automatic relevance feedback）。该查询分为两个阶段：
- 第一阶段：根据搜索的单词进行全文索引查询
- 第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询
接着来看一个例子，看看 Query Expansion 是如何使用的。
2.5.1 我们先用自然语言搜索
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
结果
id title body
3 MySQL Tutorial DBMS stands for Database
该语句只会查询出包含'database'字符的数据。
2.5.2 使用查询扩展搜索
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY expansion);
结果
id title body
3 MySQL Tutorial DBMS stands for Database
4 IBM History DB2 history for IBM
我们可以看到，该查询语句不光查询出了包含'database'字符的数据，还查询出了是'database'的数据(DB2是database)。
2.5.3 注意
由于 Query Expansion 的全文检索可能带来许多非相关性的查询，因此在使用时，用户可能需要非常谨慎。
三、删除全文索引
3.1 直接删除全文索引
DROP INDEX full_idx_name ON db_name.table_name;
3.2 使用 alter table 删除全文索引
ALTER TABLE db_name.table_name DROP INDEX full_idx_name;
四、全文索引带来的负面影响
-
占有存储空间更大，如果内存一次装不下全部索引，性能会非常差。
-
增删改代价更大，修改文本中10个单词，则要操作维护索引10次，而不是普通索引的一次。
-
如果一个列上有全文索引则一定会用上，即使有性能更好的其他索引也不会用上。由于只是存储文档指针，也就用不上索引覆盖。
总之就是性能不如普通索引，使用时要衡量一下。
网友评论