MySql全文索引+Ngram

一、关于Ngram

1.1 什么是ngram 

 MySQL 内置的全文解析器使用单词之间的空格作为分隔符，这对于不使用空格做分隔符的语言是一种限制。为了解决这一限制，MySQL提供了一个支持中文、日文和韩文（CJK）的ngram全文解析器。ngram 全文解析器支持InnoDB和MyISAM的全文索引 。

ngram解析器将文本序列分词为连续的n个字符。例如，你可以使用ngram全文解析器为“早上好呀”进行不同值的n分词。

n=1: '早', '上', '好', '呀'
n=2: '早上', '上好', '好呀'
n=3: '早上好', '上好呀'
n=4: '早上好呀' 

1.2 查看ngram配置 

分词的实现是通过mysql的ngram_token_size这项配置实现的，可以执行如下语句查看ngram_token_size的配置

SHOW VARIABLES LIKE '%token%';

 

• innodb_ft_min_token_size：默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小
• innodb_ft_max_token_size：默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小
• ngram_token_size：默认2，表示2个字符作为内置分词解析器的一个关键词，如对“abcd”建立全文索引，关键词为’ab’，‘bc’，‘cd’

当使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size 无效 

1.3 ngram检索模式

查询模式分为自然语言模式和布尔模式

自然语言模式（NATURAL LANGUAGE MODE）

• 自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符，不能指定关键词必须出现或者必须不能出现等复杂查询。
• 在自然语言模式（NATURAL LANGUAGE MODE)下，文本的查询被转换为n-gram分词查询的并集，比如查询地址中包含“太湖”的数据，那么返回的数据是地址字段中有“太”或者“湖”的数据集

布尔模式（BOOLEAN MODE）

• BOOLEAN模式可以使用操作符，可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询

1. 为检索的字符串增加操作符，如“+”表示必须包含，"-“不包含，”*" 表示通配符，字符串较小或出现在停词中，也不会被过滤掉
2.  +word必须存在
3. -word必须不存在
4. (no operator)该word可选，如果出现relevance value更高
5. distance 仅用于InnoDB表。查询多个单词之间的距离是否在distance（字节）内
6. > < 分别表示出现该word时增加和降低relevance value
7. ~ 出现该word时relevance value变负值，用于制造噪音词（“noise” word）
8. 表示以该字符串开头的word，写在word前不生效，word*才有效
9. ‘’ ‘’ 中的内容视作一个短语（整体）

• 布尔模式（BOOLEAN MODE）文本查询被转化为n-gram分词的短语查询，比如查询地址中包含“太湖”的数据，那么返回的数据是地址字段中有“太湖”的数据集

1.4 ngram查询关键词

ngram查询用MATCH() … AGAINST 方式来进行搜索，并且查询的字段一定要设置ngram的全文索引，否则查询报错

MATCH()：表示搜索的是那个列
AGAINST：表示要搜索的是那个字符串

查询订单表中收货地址中包含“太湖”的查询示例：select * from orders where MATCH(`UserAddress`) AGAINST('太湖' IN BOOLEAN MODE)

二、全文索引+Ngram配置

2.1 修改Ngram配置

修改ngram_token_size的方式有两种

1）使用命令：mysqld --ngram_token_size=1

2）在数据库配置文件 my.ini，在mysqld 下添加对ngram_token_size的配置：

# ngram 配置

ngram_token_size=1

修改后，重启Mysql服务，使得配置生效 

2.2 添加全文索引

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

比如对b_patient表中的CurrenAddress字段添加索引为例：

1、创建表的同时创建全文索引

CREATE TABLE b_patient(
    Id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    PatientName VARCHAR (200),
    CurrentAddress VARCHAR (500),
    # 建立全文索引，同时使用ngram全文分析器
    FULLTEXT (CurrentAddress) WITH PARSER ngram
) ENGINE = INNODB;

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

ALTER TABLE b_patient ADD fulltext index full_index_address(CurrentAddress) WITH PARSER ngram;

3、直接通过create index的方式 

CREATE FULLTEXT INDEX full_index_address ON b_patient(CurrentAddress) WITH PARSER `ngram`;

三、检索测试

自然语言模式

select PatientName,CurrentAddress  from b_patient 
where MATCH(`CurrentAddress`) AGAINST('五星' IN BOOLEAN MODE);

 

布尔模式

 select PatientName,CurrentAddress  from b_patient 
where MATCH(`CurrentAddress`) AGAINST('太湖' IN BOOLEAN MODE);