Mysql 实现类似于 ElasticSearch 的全文检索功能

男娘i 2024-03-27 11:34 76阅读 0赞

**一、前言**

今天一个同事问我，如何使用 Mysql 实现类似于 ElasticSearch 的全文检索功能，并且对检索关键词跑分？我当时脑子里立马产生了疑问？为啥不直接用es呢？简单好用还贼快。但是听他说，数据量不多，客户给的时间非常有限，根本没时间去搭建es，所以还是看一下 Mysql 的全文检索功能吧！  
MySQL 从 5.7.6 版本开始，MySQL就内置了ngram全文解析器，用来支持中文、日文、韩文分词。在 MySQL 5.7.6 版本之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。本篇文章测试的时候，采用的 Mysql 5.7.6 ，InnoDB数据库引擎。

[原文解析][Link 1]

前言

*  只有Innodb和myisam存储引擎能用全文索引（innodb支持全文索引是从mysql5.6开始的）
 *  char、varchar、text类型字段能创建全文索引（fulltext index type）
 *  全文索引的基于关键词的，如何区分不同的关键词了，就要用到分词（stopword）
 *  英文单词用空格，逗号进行分词；中文分词不方便（一个句子不知道怎样区分不同的关键词）
 *  内置分词解析器ngram支持中文，日文，韩文（将句子分成固定数字的短语）
 *  当对表写入大量数据时，写入数据后再创建全文索引的速度更快（减少了维护索引的开销）
 *  全文索引的原理的倒排索引（一种数据结构），一般利用关联数组，在辅助表中存储单词与文档中所在位置的映射

**使用**

用MATCH() … AGAINST 方式来进行搜索

match()表示搜索的是那个列，against表示要搜索的是那个字符串

查看默认的分词（以这些词来区分不同的关键词）；也可以自定义分词，以这些词来区分不同的关键词  
SELECT \* FROM information\_schema.INNODB\_FT\_DEFAULT\_STOPWORD;

如

1.  \+-------+
2.  | value |
3.  \+-------+
4.  | a |
5.  | about |
6.  | an |
7.  | are |
8.  | as |
9.  | at |
10. | be |
11. | by |
12. | com |
13. | de |
14. | en |
15. | for |
16. | from |

**三种类型的全文搜索方式**

**natural language search****（自然语言搜索）**

通过MATCH AGAINST 传递某个特定的字符串来进行检，默认方式

**boolean search****（布尔搜索）**

为检索的字符串增加操作符，如“+”表示必须包含，"-"不包含，"\*" 表示通配符，即使传递的字符串较小或出现在停词中，也不会被过滤掉

**query expansion search****（查询扩展搜索）**

搜索字符串用于执行自然语言搜索，然后，搜索返回的最相关行的单词被添加到搜索字符串，并且再次进行搜索，查询将返回来自第二个搜索的行

**相关参数**

配置相关参数

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 无效

注意 这三个参数均不可动态修改，修改了这些参数，需重启MySQL服务，并重新建立全文索引

**二、全文解析器ngram**

ngram就是一段文字里面连续的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。

咱们看一下Mysql默认的ngram\_token\_size大小：

show variables like 'ngram\_token\_size'

ngram\_token\_size 变量的两种设置方式：

1、启动mysqld命令时指定

mysqld --ngram\_token\_size=2

2、修改mysql配置文件

\[mysqld\]

ngram\_token\_size=2

**三、全文索引**

以某文书数据为例，新建数据表 t\_wenshu ，并且针对文书内容字段创建全文索引，导入10w条测试数据。

1、建表时创建全文索引

CREATE TABLE \`t\_wenshu\` (

\`province\` varchar(255) DEFAULT NULL,

\`caseclass\` varchar(255) DEFAULT NULL,

\`casenumber\` varchar(255) DEFAULT NULL,

\`caseid\` varchar(255) DEFAULT NULL,

\`types\` varchar(255) DEFAULT NULL,

\`title\` varchar(255) DEFAULT NULL,

\`content\` longtext,

\`updatetime\` varchar(255) DEFAULT NULL,

FULLTEXT KEY \`content\` (\`content\`) WITH PARSER \`ngram\`

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、通过 alter table 方式

ALTER TABLE t\_wenshu ADD FULLTEXT INDEX content\_index (content) WITH PARSER ngram;

3、通过 create index 方式

CREATE FULLTEXT INDEX content\_index ON t\_wenshu (content) WITH PARSER ngram;

**四、检索模式**

**自然语言检索**

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

**布尔检索**

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

● IN BOOLEAN MODE的特色：

·不剔除50%以上符合的row。

·不自动以相关性反向排序。

·可以对没有FULLTEXT index的字段进行搜寻，但会非常慢。

·限制最长与最短的字符串。

·套用Stopwords。

● 搜索语法规则：

\+ 一定要有(不含有该关键词的数据条均被忽略)。

\- 不可以有(排除指定关键词，含有该关键词的均被忽略)。

> 提高该条匹配数据的权重值。

< 降低该条匹配数据的权重值。

~ 将其相关性由正转负，表示拥有该字会降低相关性(但不像-将之排除)，只是排在较后面权重值降低。

\* 万用字，不像其他语法放在前面，这个要接在字符串后面。

" " 用双引号将一段句子包起来表示要完全相符，不可拆字。

**查询扩展检索**

注释：（WITH QUERY EXPANSION）由于查询扩展可能带来许多非相关性的查询，谨慎使用！

**五、检索查询**

1）查询 content 中包含“盗窃罪”的记录，查询语句如下

select caseid,content, MATCH ( content) AGAINST ('盗窃罪') as score from t\_wenshu where MATCH ( content) AGAINST ('盗窃罪' IN NATURAL LANGUAGE MODE)

2）查询 content 中包含“寻衅滋事”的记录，查询语句如下

select caseid,content, MATCH ( content) AGAINST ('寻衅滋事') as score from t\_wenshu where MATCH ( content) AGAINST ('寻衅滋事' IN NATURAL LANGUAGE MODE) ;

3）单个汉字，查询 content 中包含“我”的记录，查询语句如下

select caseid,content, MATCH ( content) AGAINST ('我') as score from t\_wenshu where MATCH ( content) AGAINST ('我' IN NATURAL LANGUAGE MODE) ;

备注：因为设置的全局变量 ngram\_token\_size 的值为 2。如果想查询单个汉字，需要在配置文件 my.ini 中修改 ngram\_token\_size = 1 ，并重启 mysqld 服务，此处不做尝试了。

4）查询字段 content 中包含 “危险驾驶”和“寻衅滋事”的语句如下：

select caseid,content, MATCH (content) AGAINST ('+危险驾驶 +寻衅滋事') as score from t\_wenshu where MATCH (content) AGAINST ('+危险驾驶 +寻衅滋事' IN BOOLEAN MODE);

5）查询字段 content 中包含 “危险驾驶”，但不包含“寻衅滋事”的语句如下：

select caseid,content, MATCH (content) AGAINST ('+危险驾驶 -寻衅滋事') as score from t\_wenshu where MATCH (content) AGAINST ('+危险驾驶 -寻衅滋事' IN BOOLEAN MODE);

6）查询字段 conent 中包含“危险驾驶”或者“寻衅滋事”的语句如下：

select caseid,content, MATCH (content) AGAINST ('危险驾驶 寻衅滋事') as score from t\_wenshu where MATCH (content) AGAINST ('危险驾驶 寻衅滋事' IN BOOLEAN MODE);

**六、总结**

1）使用 Mysql 全文索引之前，搞清楚各版本支持情况；  
2）全文索引比 like + % 快 N 倍，但是可能存在精度问题；  
3）如果需要全文索引的是大量数据，建议先添加数据，再创建索引；  
4）对于中文，可以使用 MySQL 5.7.6 之后的版本，或者 Sphinx、Lucene 等第三方的插件；  
5）MATCH()函数使用的字段名，必须要与创建全文索引时指定的字段名一致，且只能是同一个表的字段不能跨表；

**测试innodb引擎使用全文索引**

**准备**

**1****、目标**

*  查询文章中是否含有某个关键词；一系列文章出现某个关键词的次数
 *  查询文章的标题是否含有某个关键词

**2****、设置以下参数减少磁盘IO压力**

1.  SET GLOBAL sync\_binlog=100;
2.  SET GLOBAL innodb\_flush\_log\_at\_trx\_commit=2;

**3****、导入1kw 数据进行测试全文索引**

该数据来源网上搜索

https://pan.baidu.com/s/1aaB1R3bkBGZRMEx0o6T61w 提取码：60l7

**4****、某个文章表 的结构**

1.  CREATE TABLE \`article\` (
2.  \`id\` bigint(10) NOT NULL,
3.  \`url\` varchar(1024) CHARACTER SET latin1 NOT NULL DEFAULT '',
4.  \`title\` varchar(256) NOT NULL DEFAULT '',
5.  \`source\` varchar(32) DEFAULT '' COMMENT '真实来源',
6.  \`keywords\` varchar(32) DEFAULT NULL,
7.  \`publish\_time\` timestamp NULL DEFAULT NULL,
8.  PRIMARY KEY (\`id\`),
9.  KEY \`title\_idx\` (\`title\`)
10. ) ENGINE=InnoDB

使用myloader 多线程导入测试数据

1.  \-- 先把测试数据进行解压
2.  tar -zxf mydumper\_dump\_article.tar.gz
3.  time myloader -u $user -p $passwd -S $socket -t 32 -d /datas/dump\_article -v 3

**5****、导入数据后总数据量和数据文件、索引文件大小**

1.  SELECT COUNT(\*) FROM \`article\`;
2.  \+----------+
3.  | COUNT(\*) |
4.  \+----------+
5.  | 10000000 |
6.  \+----------+
7.  1 row in set (7.85 sec)
8.  
9.  SELECT table\_name, CONCAT(FORMAT(SUM(data\_length) / 1024 / 1024,2),'M') AS dbdata\_size, CONCAT(FORMAT(SUM(index\_length) / 1024 / 1024,2),'M') AS dbindex\_size, CONCAT(FORMAT(SUM(data\_length + index\_length) / 1024 / 1024 / 1024,2),'G') AS \`db\_size(G)\`, AVG\_ROW\_LENGTH,table\_rows,update\_time FROM information\_schema.tables WHERE table\_schema = DATABASE() and table\_name='article';
10. \+------------+-------------+--------------+------------+----------------+------------+---------------------+
11. | table\_name | dbdata\_size | dbindex\_size | db\_size(G) | AVG\_ROW\_LENGTH | table\_rows | update\_time |
12. \+------------+-------------+--------------+------------+----------------+------------+---------------------+
13. | article | 3,710.00M | 1,003.00M | 4.60G | 414 | 9388739 | 2019-07-05 15:31:37 |
14. \+------------+-------------+--------------+------------+----------------+------------+---------------------+

**使用默认方式创建全文索引**

**1****、该表已有关键词字段（对文章内容的简述），并以“,”作为分词符**

1.  select keywords from article limit 10;
2.  \+-------------------------------------------------+
3.  | keywords |
4.  \+-------------------------------------------------+
5.  | NULL |
6.  | NULL |
7.  | ,婚姻,爱情 |
8.  | 发型,偏分,化妆,时尚 |
9.  | 小A, |
10. | ,服装搭配,女性,时尚 |
11. | 漂亮,女性 |
12. | 情人节,东莞,女性 |
13. | 皮肤,护肤,护肤,食品营养,美容,养生 |
14. | 三里屯,北京,时尚 |
15. \+-------------------------------------------------+

**2****、不建全文索引时搜索某个关键词**

需要进行全表扫描

1.  select count(\*) from article where keywords like '%时尚%';
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 163 |
6.  \+----------+
7.  1 row in set (7.56 sec)

**3****、对关键词字段创建全文索引（以 , 作为分词）**

my.cnf配置文件中设置innodb\_ft\_min\_token\_size，并重启MySQL服务（最小两个字符作为一个关键词，默认三个字符作为一个关键词）

1.  \[mysqld\]
2.  innodb\_ft\_min\_token\_size=2

**3.1** **设置自定义stopwords（即分词）**

1.  USE mysql;
2.  CREATE TABLE my\_stopwords(VALUE VARCHAR(30)) ENGINE = INNODB;
3.  INSERT INTO my\_stopwords(VALUE) VALUE (',');
4.  SET GLOBAL innodb\_ft\_server\_stopword\_table = 'mysql/my\_stopwords';

1.  SHOW GLOBAL VARIABLES WHERE Variable\_name IN('innodb\_ft\_min\_token\_size','innodb\_ft\_server\_stopword\_table');
2.  \+---------------------------------+--------------------+
3.  | Variable\_name | Value |
4.  \+---------------------------------+--------------------+
5.  | innodb\_ft\_min\_token\_size | 2 |
6.  | innodb\_ft\_server\_stopword\_table | mysql/my\_stopwords |
7.  \+---------------------------------+--------------------+

**3.2** **创建全文索引**

1.  alter table article add fulltext index idx\_full\_keyword(keywords);
2.  \* \[ \] Query OK, 0 rows affected, 1 warning (1 min 27.92 sec)
3.  \* \[ \] Records: 0 Duplicates: 0 Warnings: 1

**3.3** **剩余磁盘空间需足够，原表4.6G，剩余5.7G磁盘，添加全文索引也会失败**

1.  df -h
2.  Filesystem Size Used Avail Use% Mounted on
3.  /dev/vda1 7.8G 6.3G 1.2G 85% /
4.  tmpfs 1.9G 0 1.9G 0% /dev/shm
5.  /dev/mapper/vg\_opt-lvol0
6.  19G 12G 5.7G 68% /datas
7.  
8.  \-- 会创建原表大小的临时文件
9.  8.6K Jul 5 16:19 \#sql-5250\_3533.frm
10. 4.4G Jul 5 16:20 \#sql-ib117-1768830977.ibd
11. 
12. 
13. alter table article add fulltext index idx\_full\_keyword(keywords);
14. ERROR 1114 (HY000): The table 'article' is full

**3.4** **利用创建的全文索引进行查询某个关键词出现的次数**

查询响应时间有了很大的提升，只需0.05s；使用where keywords like '%时尚%' 需要7.56s。推荐阅读：[MySQL性能优化实践（很全面，值得收藏）][MySQL]

1.  select count(\*) from article where match(keywords) against('%时尚%');
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 163 |
6.  \+----------+
7.  1 row in set (0.05 sec)

**3.5** **如需同时完全匹配多个关键词，用布尔全文搜索**

表示完全匹配 "三里屯,北京" 的记录数

1.  select count(\*) from article where match(keywords) against('+三里屯,北京' in boolean mode);
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 1 |
6.  \+----------+
7.  1 row in set (0.06 sec)

表示匹配“三里屯” 或者 “北京”的记录数

1.  select count(\*) from article where match(keywords) against('三里屯,北京');
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 8 |
6.  \+----------+
7.  1 row in set (0.06 sec)

**3.6** **创建全文索引后，会创建一些其它文件**

96K Jul 5 16:30 FTS\_00000000000000a7\_00000000000000c0\_INDEX\_1.ibd  
96K Jul 5 16:30 FTS\_00000000000000a7\_00000000000000c0\_INDEX\_2.ibd  
96K Jul 5 16:30 FTS\_00000000000000a7\_00000000000000c0\_INDEX\_3.ibd  
96K Jul 5 16:30 FTS\_00000000000000a7\_00000000000000c0\_INDEX\_4.ibd  
128K Jul 5 16:30 FTS\_00000000000000a7\_00000000000000c0\_INDEX\_5.ibd  
256K Jul 5 16:30 FTS\_00000000000000a7\_00000000000000c0\_INDEX\_6.ibd  
96K Jul 5 16:29 FTS\_00000000000000a7\_BEING\_DELETED\_CACHE.ibd  
96K Jul 5 16:29 FTS\_00000000000000a7\_BEING\_DELETED.ibd  
96K Jul 5 16:30 FTS\_00000000000000a7\_CONFIG.ibd  
96K Jul 5 16:29 FTS\_00000000000000a7\_DELETED\_CACHE.ibd  
96K Jul 5 16:29 FTS\_00000000000000a7\_DELETED.ibd

*  前6个表示倒排索引（辅助索引表）
 *  第7，8个表示包含已删除文档的文档ID（DOC\_ID），其数据当前正在从全文索引中删除
 *  第9个表示FULLTEXT索引内部状态的信息
 *  第10，11个表示包含已删除但尚未从全文索引中删除其数据的文档

**使用ngram分词解析器创建全文索引**

**1****、对title字段建立全文索引（该字段没有固定的stopwords 分词，使用ngram分词解析器）**

需先在my.cnf 配置文件中设置ngram\_token\_size（默认为2，2个字符作为ngram 的关键词），并重启mysql服务

这里使用默认的 2

1.  select title from article limit 10;
2.  \+------------------------------------------------------------------------------+
3.  | title |
4.  \+------------------------------------------------------------------------------+
5.  | worth IT |
6.  |Launchpad 江南皮革厂小show |
7.  |Raw 幕后罕见一刻 “疯子”被抬回后台 |
8.  |Raw:公子大骂老爸你就是个绿茶 公子以一打四 |
9.  |四组30平米精装小户型，海量图片，附户型图 |
10. |夜店女王性感烟熏猫眼妆 |
11. |大秀哥重摔“巨石”强森 |
12. |少女时代 崔秀英 服饰科普 林允儿 黄美英 金泰妍 郑秀晶 |
13. |德阳户外踏青，花田自助烧烤 |
14. \+------------------------------------------------------------------------------+

**2****、对title字段创建全文索引**

1.  alter table article add fulltext index ft\_index\_title(title) with parser ngram;
2.  Query OK, 0 rows affected (3 min 29.22 sec)
3.  Records: 0 Duplicates: 0 Warnings: 0

**3****、会创建倒排索引（title字段越长长，创建的倒排索引越大）**

112M Jul 5 21:46 FTS\_00000000000000a7\_00000000000000cd\_INDEX\_1.ibd  
28M Jul 5 21:46 FTS\_00000000000000a7\_00000000000000cd\_INDEX\_2.ibd  
20M Jul 5 21:46 FTS\_00000000000000a7\_00000000000000cd\_INDEX\_3.ibd  
140M Jul 5 21:46 FTS\_00000000000000a7\_00000000000000cd\_INDEX\_4.ibd  
128M Jul 5 21:46 FTS\_00000000000000a7\_00000000000000cd\_INDEX\_5.ibd  
668M Jul 5 21:46 FTS\_00000000000000a7\_00000000000000cd\_INDEX\_6.ibd

**4****、不建立全文索引搜索title的某个关键词**

1.  select count(\*) from article where title like '%户外%';
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 22058 |
6.  \+----------+
7.  1 row in set (8.60 sec)
8.  
9.  select count(\*) from article where title like '%后台%';
10. \+----------+
11. | count(\*) |
12. \+----------+
13. | 1142 |
14. \+----------+

**5****、使用全文索引搜索某个关键词**

响应时间有很大的提升

1.  select count(\*) from article where match(title) against('户外');
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 22058 |
6.  \+----------+
7.  1 row in set (0.07 sec)
8.  
9.  select count(\*) from article where title like '%后台%';
10. \+----------+
11. | count(\*) |
12. \+----------+
13. | 1142 |
14. \+----------+
15. 1 row in set (8.31 sec)

**6****、注意当搜索的关键词字符数大于2 （ngram\_token\_size定义大小）会出现不一致问题**

普通搜索，实际中出现该关键词的记录数为6

1.  select count(\*) from article where title like '%公子大%';
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 6 |
6.  \+----------+
7.  1 row in set (8.40 sec)

全文搜索，出现关键字的记录数为9443

1.  select count(\*) from article where match(title) against('公子大');
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 9443 |
6.  \+----------+
7.  1 row in set (0.06 sec)

实际出现该关键字的记录数为1

1.  select count(\*) from article where title like '%花田自助%';
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 1 |
6.  \+----------+
7.  1 row in set (8.33 sec)

全文搜索出现该关键词的记录数为3202

1.  select count(\*) from article where match(title) against('花田自助');
2.  \+----------+
3.  | count(\*) |
4.  \+----------+
5.  | 3202 |
6.  \+----------+
7.  1 row in set (0.06 sec)

**结论**

当mysql 某字段中有固定的stopword 分词（英文的空格符，中文的“,”"-"等），对该字段建立全文索引，能快速搜索出现某个关键词的相关记录信息，实现简单搜索引擎的效果

当mysql 某字段没有固定的stopword 分词，使用内置解析器ngram 可将字段值分成固定数量（ngram\_token\_size定义大小）的关键词快速进行搜索；当搜索的关键词的字符数量不等于ngram\_token\_size定义大小时，会出现与实际情况不一致的问题

全文索引能快速搜索，也存在维护索引的开销；字段长度越大，创建的全文索引也越大，会影响DML语句的吞吐量，可用专门的全文搜索引擎ES来做这件事

**参考**

https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html

[Link 1]: http://www.yund.tech/zdetail.html?type=1&id=27bf77759730689042ff6432df34d711
[MySQL]: https://blog.csdn.net/weixin_38405253/article/details/105002305