Mysql全文索引

项目开发过程中有时会遇到全文检索的需求，但是数据量有时比较小，不属于高并发高吞吐场景，这种场景搭建ES服务有点浪费资源，也把工程设计复杂了，所以需要采用更简单廉价的方案。

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

ngram全文解析器

ngram是一种基于统一语言模型的算法，简单来说，就是通过一个大小为n的滑动窗口，将一段文本分成多个由n个连续单元组成的词。ngram全文解析器能够对文本进行分词，每个单词是连续的n个字的序列。例如，用ngram全文解析器对“生日快乐”进行分词：

n=1: '生', '日', '快', '乐'
n=2: '生日', '日快', '快乐'
n=3: '生日快', '日快乐'
n=4: '生日快乐'

MySQL 中使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。通常ngram_token_size设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。因为中文单词最少是两个汉字，推荐使用默认值2。

全局变量ngram_token_size的两种设置方法：
1、修改MySQL配置文件

[mysqld]
ngram_token_size=2

创建全文索引

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;

2、通过 alter table 的方式来添加

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

 3、直接通过create index的方式

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
注意：假如使用navicat创建索引，为了保证中文可用，需要指定解析器！

更新全文索引

为了更新索引，我们需要在INSERT、UPDATE或DELETE操作之后运行如下命令：

OPTIMIZE TABLE `tablename`;

这个命令将重新建立全文索引，以便与表中的最新数据保持同步。

此外，值得注意的是，当表中的数据量增加时，全文索引的更新会变得越来越慢。因此，当你处理大量数据时要特别小心。

最后，如有必要，可以使用MySQL提供的其他全文索引技术，例如Sphinx或Elasticsearch

全文检索模式

常用的全文检索模式有两种：
1、自然语言模式(NATURAL LANGUAGE MODE) ，
   自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
2、BOOLEAN模式(BOOLEAN MODE)
   BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

MATCH()函数：

MATCH()函数是MySQL中专门用于全文搜索的函数。该函数的作用是在文本列上执行全文搜索，并且返回一个匹配度的得分。MATCH()函数可以接收一个或多个搜索词，可以支持Boolean、Natural Language和Query Expansion搜索模式。同时该函数也可以通过关键词或使用IN BOOLEAN MODE指令来指定搜索模式。

该函数的语法：

MATCH (column_list) AGAINST (search_string [search_modifier])

其中，column_list 是一个包含搜索的列的逗号分隔列表。search_string是一个被搜索的字符串。search_modifier是一个可选的搜索修饰符，支持IN BOOLEAN MODE、IN NATURAL LANGUAGE MODE和WITH QUERY EXPANSION。

Match函数执行以下步骤：

1、标记化：将搜索字符串分解成一个词汇表。

2、删除停止词：删除一些常用单词，例如 "the" 和 "and"，这些词对搜索意义不大。

3、词干化：将单词转化为基本形式以便匹配。

4、计算文本与查询之间的相关度：使用内部算法计算文本与查询之间的相关度。

5、返回搜索结果：返回按相关度排序的搜索结果。

示例一

自然语言

SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('一正 一邪' IN NATURAL LANGUAGE MODE);
// 不指定模式，默认使用自然语言模式
SELECT * FROM articles
WHERE MATCH (title,body) AGAINST ('一正 一邪');

上面的示例返回结果会自动按照相关性排序，相关性高的在前面。相关性的值是一个非负浮点数，0表示无相关性。

// 获取相关性的值
SELECT id,title,
MATCH (title,body) AGAINST ('手机' IN NATURAL LANGUAGE MODE) AS score
FROM articles
ORDER BY score DESC;
获取匹配结果记录数
SELECT COUNT(*) FROM articles
WHERE MATCH (title,body)
AGAINST ('一正 一邪' IN NATURAL LANGUAGE MODE);


示例二

布尔搜索

1. + 表示某个关键词必须存在

SELECT id,head,content
FROM  pre_article
WHERE  MATCH ( head, content ) AGAINST ( '+科技'  IN BOOLEAN MODE);

2. - 表示关键词必须不存在

-- 包含科技但不包含文物，注意中间的空格
SELECT id,head,content
FROM  pre_article
WHERE  MATCH ( head, content ) AGAINST ( '+科技 -文物'  IN BOOLEAN MODE);

3. 没有操作符

-- 表示包含“科技”或者“文物”
SELECT id,head,content
FROM  pre_article
WHERE  MATCH ( head, content ) AGAINST ( '科技 文物'  IN BOOLEAN MODE);

4. * 表示已关键词开头

-- 这通常用在英文的查询中SELECT
SELECT id,head,content
FROM  pre_article
WHERE  MATCH ( head, content ) AGAINST ( 'sec*'  IN BOOLEAN MODE);

5. ""表示短语

SELECT id,head,content
FROM  pre_article
WHERE  MATCH ( head, content ) AGAINST ( '"高考数学"'  IN BOOLEAN MODE);

6. 其它

// 必须包含"腾讯"，但是不能包含"通讯工具"
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('+手机 -通讯工具' IN BOOLEAN MODE);

下面的例子演示了BOOLEAN模式下运算符的使用方式：

'apple banana'
无操作符，表示或，要么包含apple，要么包含banana
'+apple +juice'
必须同时包含两个词
'+apple macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性会更高。
'+apple -macintosh'
必须包含apple，同时不能包含macintosh。
'+apple ~macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。
一个商品名称和商品描述的表，叫做products，包含以下两个字段：
  • product_name：表示商品的名称
  • product_description：表示商品的描述
我们希望对这两个字段进行全文搜索，并找出与“iphone”、“apple”或者二者兼备相关的所有记录。可以使用下面的MySQL语句实现：
SELECT * FROM products
WHERE MATCH (product_name, product_description)
AGAINST ('+iphone* +apple*' IN BOOLEAN MODE);
在以上例子中，我们使用了IN BOOLEAN MODE指令，表示我们希望执行布尔搜索。使用+apple +iphone，表示只有这两个字符串同时出现在搜索结果中，才被认为是匹配到了。

实例三

查询扩展搜索

拓展查询模式会进行两次查询，第一次查询命中关键词，第二次查询会根据第一次查询查到的结果作为输入，再进行一次查询。通常用在查询词太短，进行一次查询后，根据第一查询结果产生的分词进行第二次查询，请谨慎使用。

SELECT id,head,content
FROM  pre_article
WHERE  MATCH ( head, content ) AGAINST ( '科技' WITH QUERY expansion);

注意事项：

·       只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。

·       全文索引只支持InnoDB和MyISAM引擎。

·       MATCH (columnName) AGAINST ('keywords')。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。

·       MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。

·       如果要导入大数据集，使用先导入数据再在表上创建全文索引的方式要比先在表上创建全文索引再导入数据的方式快很多，所以全文索引是很影响TPS的