MySQL全文检索临时代替ES实现快速搜索_不使用es如何进行全文检索

引入

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

全文索引只支持InnoDB和MyISAM引擎，支持的类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。

了解：什么是n-gram模型

N-Gram是一种基于统计语言模型的算法。它的基本思想是将文本里面的内容按照字节进行大小为N的滑动窗口操作，形成了长度是N的字节片段序列。
每一个字节片段称为gram，对所有gram的出现频度进行统计，并且按照事先设定好的阈值进行过滤，形成关键gram列表，也就是这个文本的向量特征空间，列表中的每一种gram就是一个特征向量维度。
该模型基于这样一种假设，第N个词的出现只与前面N-1个词相关，而与其它任何词都不相关，整句的概率就是各个词出现概率的乘积。这些概率可以通过直接从语料中统计N个词同时出现的次数得到。常用的是二元的Bi-Gram和三元的Tri-Gram。

MySQL分词配置

ngram就是一段文字里面连续的n个字的序列。ngram全文解析器能够对文本进行分词，每个单词是连续的n个字的序列。例如，用ngram全文解析器对“我是中国人”进行分词:

n=1: '我', '是', '中', '国','人' 
n=2: '我是', '中国', '人' 
n=3: '我是', '中国人' 
n=4: '我是中国人'
1
2
3
4

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

全局变量ngram_token_size的两种设置方法：

1. 启动mysqld命令时

mysqld --ngram_token_size=2
1

2. 修改MySQL配置文件

[mysqld]

ngram_token_size=2
1
2
3

创建全文检索并分词

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

CREATE TABLE `essay` (
  `essay_id` varchar(20) NOT NULL,
  `autoid` tinyint(20) NOT NULL COMMENT '序号',
  `essay_title` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '文章标题',
  `essay_details` varchar(255) NOT NULL COMMENT '文章内容',
  `essay_images` varchar(255) DEFAULT NULL COMMENT '文章图片地址',
  `user_id` varchar(20) NOT NULL COMMENT '用户id',
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` timestamp(6) NULL DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (`essay_id`),
  UNIQUE KEY `idx_cs_id` (`essay_id`),
  FULLTEXT KEY `idx_cs_index` (`essay_title`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
1
2
3
4
5
6
7
8
9
10
11
12
13

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

ALTER TABLE essay ADD FULLTEXT INDEX idx_cs_index(essay_title) WITH PARSER ngram;
1

3. 直接通过create index的方式

CREATE FULLTEXT INDEX idx_cs_index ON essay(essay_title) WITH PARSER ngram;
1

使用Navicat创建全文检索

SQL中使用全文检索

常用的全文检索模式有两种：

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

-- 简单使用
select id,essay_title,essay_details from essay
where match(essay_title) against('全文检索' IN NATURAL LANGUAGE MODE)
order by create_time desc
limit 200,20

-- 不指定模式，默认使用自然语言模式
select id,essay_title,essay_details from essay
where match(essay_title) against('全文检索')
order by create_time desc
limit 200,20

-- 搜索框提示查询
select id,match(essay_title) against('全文检索' IN NATURAL LANGUAGE MODE) as title from essay
order by create_time desc
limit 200,20

-- 使用BOOLEAN模式执行高级查询 必须包含"Java"
SELECT * FROM essay WHERE MATCH (essay_title) against('+Java' IN BOOLEAN MODE);

-- 使用BOOLEAN模式执行高级查询 必须包含"Java"，但是不能包含"汇编"
SELECT * FROM essay WHERE MATCH (essay_title) against('+Java -汇编' IN BOOLEAN MODE);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

BOOLEAN模式下运算符的使用方式：

'apple banana' 
无操作符，表示或，要么包含apple，要么包含banana
 
'+apple +juice'
必须同时包含两个词
 
'+apple macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性会更高。
 
'+apple -macintosh'
必须包含apple，同时不能包含macintosh。
 
'+apple ~macintosh'
必须包含apple，但是如果也包含macintosh的话，相关性要比不包含macintosh的记录低。
 
'+apple +(>juice <pie)'
查询必须包含apple和juice或者apple和pie的记录，但是apple juice的相关性要比apple pie高。
 
'apple*'
查询包含以apple开头的单词的记录，如apple、apples、applet。
 
'"some words"'
使用双引号把要搜素的词括起来，效果类似于like '%some words%'，

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引。
全文索引只支持InnoDB和MyISAM引擎。
MATCH (columnName) AGAINST (‘keywords’)。MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名与全文索引ft_articles(title,body)定义的字段名一致。如果要对title或者body字段分别进行查询，就需要在title和body字段上分别创建新的全文索引。
MATCH()函数使用的字段名只能是同一个表的字段，因为全文索引不能够跨多个表进行检索。
建议：如果要导入大数据集，数据初始化同步等需求时；建议先同步数据之后然后再创建全文索引，创建全文检索后导入很影响TPS的

全文检索与模糊查询效率对比

第一张图是模糊查询的结果，第二张图是全文检索的结果

执行器解释查看

可以看到最明显的区别，第一个没全文索引的语句最后的rows是2120，有全文检索的是1

explain解读

1. type：ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）
2. possible_keys：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
3. key：表示MySQL实际决定使用的键（索引），如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
4. ref：表示选择 key 列上的索引，哪些列或常量被用于查找索引列上的值
5. rows：根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
6. Extra
   a.Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
   b.Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”