MySQL使用全文索引+ngram全文解析器进行全文检索

一、前言

最近有项目需要使用mysql进行全文检索，由于之前都是使用的Elasticsearch数据库进行数据检索，因此查询了相关资料后，了解了mysql如何使用全文索引。

二、ngram全文分析器

1.什么是ngram

ngram是全文解析器能够对文本进行分词，中文分词用 ngram_token_size 设定分词的大小,ngram_token_size 的值就是连续n个字的序列
示例：使用ngram对于‘全文索引进行分词’

ngram_token_size =1,分词为 ‘全‘，’文‘，’索‘，’引‘
ngram_token_size =2,分词为 ‘全文‘，’文索‘，’索引‘
ngram_token_size =3,分词为 ‘全文索‘，’文索引‘
ngram_token_size =4,分词为 ‘全文索引‘

2.如何查看配置ngram_token_size

#查看默认分词大小 ngram_token_size=2
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 无效

3.修改配置ngram_token_size

第一种
mysqld --ngram_token_size=1
第二种
在配置文件中[mysqld]ngram_token_size=1
这三个参数均不可动态修改，修改后需重启MySQL服务，并重新建立全文索引
== 建议使用默认分词大小==

三、全文索引

MySQL 5.6版本以前只有MyISAM存储引擎支持全文引擎,在5.6版本中,InnoDB支持对全文索引的.5.7.6版本,MySQL内置了ngram全文解析器,同时支持中文全文的分词。
因此需要支持全文检索需要大于5.76版本

创建全文索引

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

CREATE TABLE full_search_test(
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    author VARCHAR (200),
    content TEXT,
    # 建立全文索引，同时使用ngram全文分析器
    FULLTEXT (title) WITH PARSER ngram
) ENGINE = INNODB;

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

ALTER TABLE full_search_test ADD FULLTEXT INDEX full_index_title(title) WITH PARSER ngram;

3、直接通过create index的方式

#为title创建全文索引并且使用ngram全文解析器进行分词
CREATE FULLTEXT INDEX full_index_title ON full_search_test(title) WITH PARSER `ngram`;

四、检索测试

测试环境搭建

数据库脚本

CREATE TABLE `full_search_test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `author` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `title` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `content` text COLLATE utf8mb4_general_ci,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `full_index_title` (`title`) WITH PARSER `ngram`  
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

测试数据：

INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (1, '小孩子4919', 'MySQL 是怎样运行的 test', '《MySQL是怎样运行的：从根儿上理解 MySQL》采用诙谐幽默的表达方式，对 MySQL 的底层运行原理进行了介绍，内容涵盖了使用MySQL的同学在求职面试和工作中常见的一些核心概...');
INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (2, '施瓦茨 (Baron Schwartz) / 宁海元', '高性能MySQL (第3版)  术内', '《高性能mysql(第3版)》是mysql 领域的经典之作，拥有广泛的影响力。第3 版更新了大量的内容，不但涵盖了最新mysql 5.5版本的新特性，也讲述了关于固态盘、高可扩展性设...');
INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (3, '[日]西泽梦路 / 卢克贵 ', 'MySQL 基础教程 内幕', '1.本书是日本公认的MySQL入门首选教程，原版长销13年，好评如潮，本书是第3次改版 2.本书有丰富的插图和示例程序，即使是初学者，也很容易理解和掌握相关知识点 3.本书...');
INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (4, '小孩子4919 ', '技术MySQL 是怎样使用的 快速入门 MySQL', '《MySQL是怎样使用的：快速入门MySQL》采用通俗易懂的表达方式，对如何使用MySQL进行了详细的介绍。《MySQL是怎样使用的：快速入门MySQL》完全从零基础用户的角度出发，...');
INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (5, '[英] Ben Forta / 刘晓霞', 'MySQL 必知必会 内幕', '《MySQL必知必会》MySQL是世界上最受欢迎的数据库管理系统之一。书中从介绍简单的数据检索开始，逐步深入一些复杂的内容，包括联结的使用、子查询、正则表达式和基于全...');
INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (6, '姜承尧', 'MySQL 技术内幕', '《MySQL技术内幕:InnoDB存储引擎(第2版)》由国内资深MySQL专家亲自执笔，国内外多位数据库专家联袂推荐。作为国内唯一一本关于InnoDB的专著，《MySQL技术内幕:InnoDB存...');
INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (7, '[英] Ben Forta / 刘晓霞', 'MySQL 必知必会 测试', '《MySQL必知必会》MySQL是世界上最受欢迎的数据库管理系统之一。书中从介绍简单的数据检索开始，逐步深入一些复杂的内容，包括联结的使用、子查询、正则表达式和基于全...');
INSERT INTO `platform`.`full_search_test`(`id`, `author`, `title`, `content`) VALUES (8, '刘晓霞', '高性能必知必会', '《MySQL必知必会》MySQL是世界上最受欢迎的数据库管理系统之一。书中从介绍简单的数据检索开始，逐步深入一些复杂的内容，包括联结的使用、子查询、正则表达式和基于全...');

全文检索模式

1. 自然语言模式(NATURAL LANGUAGE MODE)

不指定检索模式

#ngram默认配置为2，因此'技术内幕'会被分词解析器解析为 '技术','术内','内幕'
#score表示相关性评分
select *,(MATCH ( `title`  ) AGAINST ( '技术内幕' )  )AS score 
from full_search_test 
where MATCH(title) AGAINST('技术内幕' ) ;

自然语言模式(NATURAL LANGUAGE MODE)

select *,(MATCH ( `title`  ) AGAINST ( '技术内幕' )  )AS score 
from full_search_test 
where MATCH(title) AGAINST('技术内幕' in NATURAL LANGUAGE MODE );

== 执行结果可以看出 自然语言模式是MySQL 默认的全文检索模式 ==

2. BOOLEAN模式(BOOLEAN MODE)

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

---

# '内幕 技术' 无操作符，表示或
select *  
from full_search_test 
where MATCH(title) AGAINST('内幕 技术' IN BOOLEAN MODE);

可以看到由于同时包含’内幕 技术’，因此第一个记录的相关性评分更高

---

# '+内幕 +技术' 必须同时包含两个词
select *  ,(MATCH ( `title`  ) AGAINST ( '内幕 技术' )  )AS score 
from full_search_test 
where MATCH(title) AGAINST('+内幕 +技术' IN BOOLEAN MODE) order by score Desc;

---

# '+内幕 -技术' 必须包含内幕，但是不包含技术
select *  
from full_search_test 
where MATCH(title) AGAINST('+内幕 -技术' IN BOOLEAN MODE);

---

# '+MySQL ~技术' 必须包含MySQL，但是如果包含技术，相关性比不包含技术的记录高
select * ,(MATCH ( `title`  ) AGAINST ( '+MySQL ~技术' IN BOOLEAN MODE)  )AS score 
from full_search_test 
where MATCH(title) AGAINST('+MySQL ~技术' IN BOOLEAN MODE) order by score Desc;

---

# '+MySQL +(>基础 <必知)' 必须包含MySQL，或者包含基础和必知，'MySQL 基础'相关性评分高于'MySQL 必知'
select * ,(MATCH ( `title`  ) AGAINST ( '+MySQL +(>基础 <必知)' )  )AS score 
from full_search_test 
where MATCH(title) AGAINST('+MySQL +(>基础 <必知)' IN BOOLEAN MODE) order by score Desc;

---

# 查询包含高性能开头的记录
select * ,(MATCH ( `title`  ) AGAINST ( '高性能*' )  )AS score 
from full_search_test 
where MATCH(title) AGAINST('高性能*' IN BOOLEAN MODE) order by score Desc;

---

# 使用双引号把要搜素的词括起来，效果类似于like '%高性能%'
select * ,(MATCH ( `title`  ) AGAINST ( '"高性能"' )  )AS score 
from full_search_test 
where MATCH(title) AGAINST('"高性能"' IN BOOLEAN MODE) order by score Desc;