Mysql全文索引使用教程

简介
一、配置
- 注意事项
二、创建全文索引
三、查询语句
四、检索模式
- 1、自然语言检索
- 2、布尔检索
  - 2.1、布尔检索IN BOOLEAN MODE的特色：
  - 2.2、布尔检索IN BOOLEAN MODE搜索语法规则：
五、举个栗子

简介

Mysql 5.6之后的版本innodb存储引擎才支持全文索引，在5.6之前，只有myisam存储引擎支持全文索引；

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

全文索引的底层原理是倒排索引，可以参考下es的倒排索引；

全文索引比 like + % 快 N 倍，但是可能存在精度问题，
如果需要全文索引的是大量数据，建议先添加数据，再创建索引，
MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致，且只能是同一个表的字段不能跨表。

一、配置

找到配置文件my.ini，在mysqld加入以下配置，注意看自己的存储引擎是innodb还是myisam

[mysqld]
ngram_token_size=2  # 搜索(中文)关键词最小长度  该参数不区分存储引擎
ft_min_word_len=4 # 搜索(英文)关键词最小长度  默认为4，MyISAM引擎参数
ft_max_word_len=84 # 搜索(英文)关键词最大长度  默认为 84，MyISAM引擎参数
innodb_ft_min_token_size=3 # 搜索(英文)关键词最小长度 默认为3，InnoDB引擎参数
innodb_ft_max_token_size=84 #  搜索(英文)关键词最大长度 默认为 84，InnoDB引擎参数

ngram_token_size ：表示中文最少按2个文字切词，进行全文索引，设置的值越小，索引越大，取值范围：1~10，默认值：2。通常将ngram_token_size的值设置为要查询的单词的最小字数。如果需要搜索单字，就要把ngram_token_size设置为1。在默认值是2的情况下，搜索单字是得不到任何结果的。该参数不区分搜索引擎

注意事项

myisam引擎下对应的变量名为ft_min_word_len和ft_max_word_len

innodb引擎下对应的变量名为innodb_ft_min_token_size和innodb_ft_max_token_size

二、创建全文索引

-- 创建全文索引，并指定分词器为ngram,建议使用
CREATE FULLTEXT INDEX preview_text_ft ON t_artile(preview_text) WITH PARSER ngram;
-- 创建全文索引的第二种方式
alter table t_artile add FULLTEXT INDEX preview_text_ft(preview_text);
-- 创建全文索引的第三种方式-建表指定

创建多列全文索引

CREATE FULLTEXT INDEX preview_text_ft ON t_artile(title,content_html,preview_text) WITH PARSER ngram;

当前的分词配置可通过以下指令查看

SHOW VARIABLES LIKE 'ngram_token_size'
SHOW VARIABLES LIKE 'ft_min_word_len'

三、查询语句

查询时会自动分词

SELECT title,preview_text FROM t_artile WHERE MATCH(content_html) AGAINST("git status");
SELECT title,preview_text FROM t_artile WHERE MATCH(content_html) AGAINST("我爱你")

若是多列索引，在查询时也要指定多列,MATCH中的字段需要建索引时的列数一样

SELECT title,preview_text FROM t_artile WHERE MATCH(title,content_html,preview_text) AGAINST("我爱你")

四、检索模式

1、自然语言检索

（IN NATURAL LANGUAGE MODE）自然语言模式是 MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。

2、布尔检索

（IN BOOLEAN MODE）剔除一半匹配行以上都有的词，例如，每行都有this这个词的话，那用this去查时，会找不到任何结果，这在记录条数特别多时很有用，原因是数据库认为把所有行都找出来是没有意义的，这时，this几乎被当作是stopword(中断词)；布尔检索模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

2.1、布尔检索IN BOOLEAN MODE的特色：

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

2.2、布尔检索IN BOOLEAN MODE搜索语法规则：

前缀语法	说明
+	一定要有(不含有该关键词的数据条均被忽略)。
-	不可以有(排除指定关键词，含有该关键词的均被忽略)。
>	提高该条匹配数据的权重值。
<	降低该条匹配数据的权重值。
~	将其相关性由正转负，表示拥有该字会降低相关性(但不像-将之排除)，只是排在较后面权重值降低。
*	万用字，不像其他语法放在前面，这个要接在字符串后面。用法：`MATCH ( title, body ) AGAINST ( 'My*' IN BOOLEAN MODE );` 类似模糊查询`like 'My%'`
“ “	用双引号将一段句子包起来表示要完全相符，不可拆字。如 ‘“hello world”‘
@distance	distance是个数字，表示查询的多个单词之间的距离是否在 distance 之内，distance 的单位是字节，这种全文检索的查询也称为 Proximity Search，如 `MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)`语句表示字符串 Pease 和 hot 之间的距离需在30字节内
no operator	无语法 (no operator)表示该 word 是可选的，但是如果出现，其相关性会更高

五、举个栗子

1、查询包含hello且不包含hi的行 + -

SELECT
    * 
FROM
    `t_home_article` 
WHERE
    MATCH ( title, preview_text ) AGAINST ( '+hello -hi' IN BOOLEAN MODE );

2、no operator

SELECT
    * 
FROM
    `t_home_article` 
WHERE
    MATCH ( title, content_html ) AGAINST ( 'hello hi' IN BOOLEAN MODE );

3、短语搜索`""`

SELECT
    * 
FROM
    `t_home_article` 
WHERE
    MATCH ( title, content_html ) AGAINST ( '"hello yexindong"' IN BOOLEAN MODE );

简介