用 Mysql 实现一个全文检索系统

vlambda
2021-01-18

用 Mysql 实现一个全文检索系统

说到全文检索，大家接触最多的便是以 Solr、ElasticSearch 为首的分布式搜索引擎。一个常规技术方案是 OLTP 数据库用来满足常规的业务系统查询，而文本数据用 ES 进行存储，两个系统混合来满足业务需求。那么我们将应用场景简化：

总数据量百万级别；
需要建立文本索引字段平均长度在1000；
全文检索满足基本诉求即可：与或非逻辑支持、短语查询等；
语言以中文为主；
性能要求在 1s以内(没有其他额外查询开销)；

Mysql FullText 简介

Mysql 对全文检索支持的版本迭代历程：

5.6 版本以下，只有MyISAM引擎支持全文检索；
5.6 版本以上，支持Innodb引擎；
5.7.6 版本以下只支持英文全文检索；
5.7.6 版本以上扩展出 Ngram-Parser，支持以 CJK 语言的全文检索；

我们以 Mysql8.0为例，简要说明功能：

Natural Language Full-Text Searches

IN NATURAL LANGUAGE MODE 是默认修饰符，MATCH() 函数对文本集合执行字符串的自然语言搜索。集合是一组包含在 FULLTEXT 索引中的一个或多个列。搜索字符串作为AGAINST() 的参数提供。对于表中的每一行，MATCH() 返回一个相关值;

SELECT title, MATCH ( title ) AGAINST ( '大数据' IN NATURAL LANGUAGE MODE ) AS score FROM mf_docs WHERE MATCH ( title ) AGAINST ( '大数据' IN NATURAL LANGUAGE MODE )   LIMIT 10;

默认按照相关的得分倒序排列：

title	score(相关度得分)
【德州4项目入选！省级大数据“三优两重”项目公布】日前，省工信厅公布2020年度省级大数据“三优两重”项目名单，我市 4项目入选。据悉，“三优”指山东省优秀大数据产品、优秀大数据解决方案、优秀大数据应用案例，“两重”指山东省重点大数据企业、重点大数据资源。	65.07472229003906
【重庆市大数据标准化建设实施方案启动】#大数据# 《重庆市大数据标准化建设实施方案(2020-2022年)》日前启动，《方案》提出到2022年，重庆市将参与国家大数据共享开放标准、数据安全标准研制3项以上，参与示范应用5项以上，推动研制大数据标准规范20项以上。via重庆日报	50.245452880859375
（原）大数据的4V特征：一，Volume 大数据量大，单位为T 或P级。二，Variety 大数据包括日志、视频、图片等多种类型。三，Value 大数据的商业和社会管理价值高，比如：监控视频。四，Velocity 处理速度快。大数据技术可在媒体机构发布的年度关键词中“小试牛刀”，也可在“	46.48194122314453
福泉市大数据发展服务中心揭牌成立 1月11日，福泉市大数据发展服务中心揭牌成立。大数据发展服务中心将发挥汇聚、整合、清洗全市数据、挖掘和应用数据资源潜在价值的基础作用，为提升城市规划和城市基础设施管理数字化、精准化水平提供有利平台，实现“用数据说话、用数据决	42.943199157714844
......	......

Boolean Full-Text Searches

MySQL可以使用`IN BOOLEAN MODE`修饰符执行布尔型全文检索。也就是我常说的 AND 、OR 逻辑。这里我们举例说明：文本包含“大数据“，排除”北京“

SELECT titleFROM mf_docs WHERE MATCH ( title ) AGAINST ( '+大数据 -北京' IN BOOLEAN MODE )  LIMIT 10;

Full-Text Searches with Query Expansion
这类场景适合查询短语较为短小，而通常的做法是我们自己维护一批扩

展词或者同义词，进行联合查询。而 Mysql 中的查询扩展是基于第一次相关度查询的结果，进行二次查询。这里以官方举例：

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);+----+-------------------+------------------------------------------+| id | title | body |+----+-------------------+------------------------------------------+| 1 | MySQL Tutorial | DBMS stands for DataBase ... || 5 | MySQL vs. YourSQL | In the following database comparison ... |+----+-------------------+------------------------------------------+2 rows in set (0.00 sec)# 基于第一次的相关度查询的结果，会二次匹配那些记录与上述的两篇文档相似的结果返回mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION);+----+-----------------------+------------------------------------------+| id | title | body |+----+-----------------------+------------------------------------------+| 5 | MySQL vs. YourSQL | In the following database comparison ... || 1 | MySQL Tutorial | DBMS stands for DataBase ... || 3 | Optimizing MySQL | In this tutorial we show ... || 6 | MySQL Security | When configured properly, MySQL ... || 2 | How To Use MySQL Well | After you went through a ... || 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |+----+-----------------------+------------------------------------------+6 rows in set (0.00 sec)

环境准备

这里以 Mysql8.0 为软件环境，硬件为真实的物理机(64GB内存，48 Core)。未做任何调优。

表结构: 其中 title 平均长度在1000以内，content 平均长度在 10000以上：

CREATE TABLE mf_docs ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, titleSimHash VARCHAR(64), pubTime BIGINT, title TEXT, content TEXT, FULLTEXT (title) WITH PARSER ngram, FULLTEXT (content) WITH PARSER ngram ) ENGINE=InnoDB CHARACTER SET utf8mb4;

数据量：总数据量在80万，标准的互联网文本数据：

MySQL [fulltext_db]> select count(1) from mf_docs;+----------+| count(1) |+----------+| 828414 |+----------+1 row in set (2.49 sec)

性能对比分析

# 短语查询 title 匹配 ”大数据“MySQL [fulltext_db]> SELECT count(1) AS num FROM mf_docs WHERE MATCH (title) AGAINST ('"大数据"' IN BOOLEAN MODE);+------+| num |+------+| 3270 |+------+1 row in set (0.19 sec)# Like 查询 title 匹配 ”大数据“MySQL [fulltext_db]> SELECT count(1) AS num FROM mf_docs WHERE title like ('%大数据%');+------+| num |+------+| 3270 |+------+1 row in set (3.43 sec)# 短语查询 content 匹配 ”大数据“MySQL [fulltext_db]> SELECT count(1) AS num FROM mf_docs WHERE MATCH (content) AGAINST ('"大数据"' IN BOOLEAN MODE);+-------+| num |+-------+| 30192 |+-------+1 row in set (4.29 sec)# Like 查询 content 匹配 ”大数据“MySQL [fulltext_db]> SELECT count(1) AS num FROM mf_docs WHERE content like ('%大数据%');+-------+| num |+-------+| 30190 |+-------+1 row in set (17.56 sec)

从以上结果可以看出：

全文检索的性能的远高于 Like查询。性能提高在 4 ~ 30 倍不等。
文本索引长度不同，全文检索的性能差异在几十倍左右。

Phrase 短语查询性能要低于一般形式的查询（会引起搜索结果不精准等问题）。

MySQL [fulltext_db]> SELECT count(1) as num FROM mf_docs WHERE MATCH (content) AGAINST ('大数据' IN NATURAL LANGUAGE MODE);+--------+| num |+--------+| 126903 |+--------+1 row in set (0.27 sec)# 可以看到 NATURAL LANGUAGE MODE 要比 短语查询(4.29 sec)高出百倍性能。

问题分析：在对content字段进行全文检索时，发现有(30192 - 30190) 2 条的数据误差：进行校验，的确存在 ”大数据“,但是无法进行匹配，应该是存在一些特殊字符吧，这里不再进行展开分析。

总结

如果你的场景是在百万级别的数据规模下，同时全文检索字段长度不大的情况下，可以考虑使用 Mysql FullText Searches 机制。同时避免了引入 ES 等额外组件带来的性能开销。

vlambda博客
学习文章列表