用 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() 返回一个相关值;
SELECTtitle,MATCH ( title ) AGAINST ( '大数据' IN NATURAL LANGUAGE MODE ) AS scoreFROMmf_docsWHEREMATCH ( 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 逻辑。这里我们举例说明：文本包含“大数据“，排除”北京“
SELECTtitleFROMmf_docsWHEREMATCH ( title ) AGAINST ( '+大数据 -北京' IN BOOLEAN MODE )LIMIT 10;
Full-Text Searches with Query Expansion
这类场景适合查询短语较为短小，而通常的做法是我们自己维护一批扩
展词或者同义词，进行联合查询。而 Mysql 中的查询扩展是基于第一次相关度查询的结果，进行二次查询。这里以官方举例：
mysql> SELECT * FROM articlesWHERE 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 articlesWHERE 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 等额外组件带来的性能开销。