文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

Mysql全文索引

2023-08-18 22:45

关注

1.背景简介

项目开发过程有时候会遇到全文检索的需求,但是数据量有时候比较小,不属于高并发高吞吐场景,这种场景搭建ES服务有点浪费资源,也把工程设计复杂了,所以需要采用更简单更廉价的方案。一般业务系统都会用到Mysql或者PostgreSQL服务,无论是Mysql还是PostgreSQL都对full-text做了兼容,下面以最常见的mysql数据库服务为例,讲述mysql服务接入full-text索引的过程。

2.Mysql全文索引简介

mysql的全文索引主要用于全文字段的检索场景,支持char、varchar、text几个字段加全文索引,仅支持InNoDB与MyISAM引擎。
mysql内置了ngram解析器来支持中文、日文、韩文等语言的文本,全文索引支持通过建表语句来创建或者建表后新增。
mysql全文索引支持三种模式:
● 布尔模式(IN BOOLEAN MODE)
● 自然语言模式(NATURAL LANGUAGE MODE)
● 查询拓展(QUERY EXPANSION)
参考:
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html《Full-Text Search Functions》

3.ngram简介

ngram一种基于统计语言模型的算法,简单来说,就是通过一个大小为n的滑动窗口,将一段文本分成多个由n个连续单元组成的term。例:

n=2text=湖北省武汉市

经过ngram解析器解析后,得到如下分词:

湖北 北省 省武 武汉 汉市

ngram全文解析器是mysql服务内置的插件,与其他插件一样,在mysql服务启动的时候自动加载。

参考:
https://zhuanlan.zhihu.com/p/32829048《自然语言处理中N-Gram模型介绍》
https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html《ngram Full-Text Parser》

4.数据库配置

先看mysql版本

ngram解析器是mysql5.7版本后,内置的全文索引解析器,所以要求mysql版本要5.7及以上。
查看mysql版本:

select version()

配置ngram

ngram可以作为启动字符串的一部分或者在配置文件中设置
启动字符串:

mysqld --ngram_token_size=2

配置文件(my.ini):
以windos系统为例,首先找到my.ini文件(默认安装路径:C:\ProgramData\MySQL\MySQL Server 5.7\my.ini),编辑该文件,在文件后加上如下配置:

ngram_token_size=2

配置完成后重启服务

5.创建全文索引

通过建表语句创建

CREATE TABLE `news`  (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',  `content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '内容',  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '',  PRIMARY KEY (`id`) USING BTREE,  FULLTEXT INDEX `idx_full_text`(`content`) WITH PARSER `ngram`) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

通过CREATE FULLTEXT INDEX语句创建

CREATE FULLTEXT INDEX idx_full_text ON news (content) WITH PARSER ngram;

6.使用全文索引

首先写入部分测试全文数据:

INSERT INTO `news` VALUES (1, '武汉市今天有小雨,大家出门记得带雨伞', '武汉天气');INSERT INTO `news` VALUES (2, '湖北省武汉市是中部最大的城市,华中地区第一大都市', '湖北省武汉市');INSERT INTO `news` VALUES (3, '武汉大学今年不开放樱花观赏,请各位游客不必前往', '武大樱花');INSERT INTO `news` VALUES (4, '湖北省是千湖之省,风景秀丽,欢迎全国各地游客', '湖北省旅游');INSERT INTO `news` VALUES (5, '武汉光谷是武汉市高新技术公司聚集地,大量来自全球各地的优秀人才聚集在此', '武汉光谷');

布尔模式

布尔模式的全文检索支持下面几种操作符:
● +(必须出现)
● -(必须不出现)
● 无操作符(出现了,相关性会更高)
● @distance(需要满足一定的编辑距离 仅仅支持InnoDB存储引擎)
● <>(增加或者减少相关性)
● ~(负相关性)
● *(通配符)
● “”(短语)

下面分别介绍这几种操作符在布尔模式下的用法:

select * from news where MATCH (content) against ('+武汉' in Boolean MODE);

查询结果:
在这里插入图片描述

+武汉表示必须出现’武汉’这个单词数据才能被检索到,从结果可以看出,'武汉’这个单词出现次数最多的排在最前面(参考第7章节)。

select * from news where MATCH (content) against ('+武汉 -雨伞' in Boolean MODE); 

在这里插入图片描述

+武汉表示被检索到的数据必须包含’武汉’这个分词,-雨伞表示被检索到的数据必须不能包含’雨伞这个分词’

select * from news where MATCH (content) against ('武汉 雨伞' in Boolean MODE);

在这里插入图片描述

无操作符则表示出现’武汉’或者’雨伞’的数据会有更高的相关性

select * from news where MATCH (content) against ('武汉  <雨伞' in Boolean MODE);

在这里插入图片描述

'武汉 <雨伞’表示需要检索到包含武汉的数据,但是当出现’雨伞’这个分词时,需要降低相关性,所以带有’雨伞’的数据会排在最后

select * from news where MATCH (content) against ('"全球 优秀"@5' in Boolean MODE);

这个@distance语法不知道为啥一直没有查出结果(待查明原因)

select * from news where MATCH (content) against ('武汉  ~雨伞' in Boolean MODE);

在这里插入图片描述

'武汉 ~雨伞’表示需要检索到包含武汉的数据,但是当出现’雨伞’这个分词时,相关性为负,所以带有’雨伞’的数据会排在最后(这个效果与<操作符有类似效果)

select * from news where MATCH (content) against ('全球*' in Boolean MODE);

在这里插入图片描述

*操作符的作用其实与like的通配符类似

select * from news where MATCH (content) against ('"武汉光谷"' in Boolean MODE);

在这里插入图片描述

双引号表示’武汉光谷’以短语的方式被检索到

校验ngram

第三节讲到了ngram分词,下面校验一下ngram分词:
在这里插入图片描述

以数据表的第一条文本为例:

select * from news where MATCH (content) against ('天有' in Boolean MODE);

在这里插入图片描述

检索’天有’这个分词能够检索出’武汉市今天有小雨,大家出门记得带雨伞’这条文本,说明这条文本的分词情况如下:

武汉 汉市 市今 今天 天有 有小 小雨 。。。

说明ngram(n=2)解析器将文本分成了以2为滑动窗口,将一段文本分成多个由2个连续单元组成的term

整个用下来,熟悉ES服务的同学可能已经发现了,这个跟ES操作还是很类似的。
参考:
https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html《Boolean Full-Text Searches》

自然语言模式

自然语言模式是默认全文检索模式,简单地说就是把检索关键词当做自然语言来处理,自然语言模式也等价于布尔模式中的无操作符模式,下面三种查询,结果是一样的:

-- 自然语言模式select * from news where MATCH (content) against ('技术 武汉 光谷' IN NATURAL LANGUAGE MODE);-- 布尔模式 无操作符select * from news where MATCH (content) against ('技术 武汉 光谷' in Boolean MODE);-- 默认模式select * from news where MATCH (content) against ('技术 武汉 光谷');

在这里插入图片描述

这三种查询结果和排序都是一样的。

拓展查询

拓展查询模式会进行两次查询,第一次查询命中关键词,第二次查询会根据第一次查询到的结果作为输入,再进行一次查询,下面举例说明:
首先,我们的doc列表如下:
在这里插入图片描述

先查询关键词’西湖’:

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

在这里插入图片描述

上面通过WITH QUERY EXPANSION查询’西湖’就返回一条与’西湖’相关的数据。

下面再插入一条数据:
在这里插入图片描述

再来拓展查询’西湖’:

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

再看结果:
在这里插入图片描述

发现多了一条与’西湖’无关的记录(就是刚刚新增的数据)。

那么为什么会出现这样的结果呢?
原因是拓展查询会根据第一次查询到的结果再进行一次查询,第一次查询到的结果是关于’西湖’的结果,结果里面包含’杭州’、‘美丽’、‘地方’等关键词,第二次查询会检索’杭州’、‘美丽’、'地方’等关键词,所以自然会查询到上面两条数据,下面在新增几条数据来验证:
在这里插入图片描述

上面新增数据11和12。
再次拓展查询’西湖’:

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

查询结果:
在这里插入图片描述

查询结果包括刚刚新增的11、12两条数据(因为这两条数据包括’西湖’关键字的第一次查询结果里面的关键词)。
上面的拓展查询结果相当于下面这两次查询:

-- 首先根据'西湖'关键词 查询出 '杭州'、'美丽'、'地方' 等结果select content from news where MATCH (content) against ('西湖' IN NATURAL LANGUAGE MODE)-- 再根据 '杭州'、'美丽'、'地方' 等结果二次查询select * from news where MATCH (content) against ('杭州 美丽 地方' IN NATURAL LANGUAGE MODE)

拓展查询很有可能查询出很多意想不到的结果,有点盲查的意思。

参考:
https://dev.mysql.com/doc/refman/5.7/en/fulltext-query-expansion.html《Full-Text Searches with Query Expansion》
https://www.begtut.com/mysql/using-mysql-query-expansion.html《MySQL查询扩展》

7.相关性排序

InnoDB的全文搜索基于 Sphinx 搜索引擎,相关性排序算法采用的是 BM25 and TF-IDF ,熟悉ES的同学应该对TF-IDT不陌生,TF-IDT简单的来说就是:被检索关键词出现越多的doc相关性越高,包含高辨识度(整体上在doc里面出现的比较少)关键词的doc相关性越高,相关性越高,会被排在结果的前面。

SELECT*,MATCH ( content ) against ( '武汉 杭州' IN Boolean MODE ) AS score FROMnews ORDER BYscore DESC;

在这里插入图片描述

上面检索了’武汉’、‘杭州’两个关键词,从结果中可以看出:
● 出现’杭州’(因为’杭州’整体上出现的较少,所以辨识度较高 IDF)的记录(doc)得分较高(相关性交大),排在前面
● 武汉出现较多的记录得分也比较高(TF)

参考:
http://t.zoukankan.com/hoohack-p-5408933.html《Sphinx的介绍和原理探索》
https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html《Boolean Full-Text Searches》
https://blog.csdn.net/zc1995730/article/details/122498012《TF-IDF算法原理和公式》

来源地址:https://blog.csdn.net/Princeliu999/article/details/128539216

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯