【每日一记11】单表千万行数据库 LIKE 搜索优化手记
  

sailyang 4853

{{ttag.title}}
本帖最后由 sailyang 于 2020-9-2 20:30 编辑

    单表千万行数据库 LIKE 搜索优化手记,由于出于业务数据考虑,本次在模拟库上把优化流程思路给展示出来我们经常在数据库中使用 LIKE 操作符来完成对数据的模糊搜索,LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。如果需要查找客户表中所有姓氏是“张”的数据,可以使用下面的 SQL 语句:
SELECT * FROM Customer WHERE Name LIKE '张%'
   如果需要查找客户表中所有手机尾号是“1234”的数据,可以使用下面的 SQL 语句:
SELECT * FROM Customer WHERE Phone LIKE '%123456'
   如果需要查找客户表中所有名字中包含“秀”的数据,可以使用下面的 SQL 语句:
SELECT * FROM Customer WHERE Name LIKE '%秀%'
  以上三种分别对应了:左前缀匹配、右后缀匹配和模糊查询,并且对应了不同的查询优化方式。

数据概览
   现在有一张名为 tbl_like 的数据表,表中包含了四大名著中的全部语句,数据条数上千万:
左前缀匹配查询优化如果要查询所有以“孙悟空”开头的句子,可以使用下面的 SQL 语句:
SELECT * FROM tbl_like WHERE txt LIKE '孙悟空%'
SQL Server 数据库比较强大,耗时八百多毫秒,并不算快:
我们可以在 txt 列上建立索引,用于优化该查询:
CREATE INDEX tbl_like_txt_idx ON [tbl_like] ( [txt] )
应用索引后,查询速度大大加快,仅需 5 毫秒:

此可知:对于左前缀匹配,我们可以通过增加索引的方式来加快查询速度。

右后缀匹配查询优化
    在右后缀匹配查询中,上述索引对右后缀匹配并不生效。使用以下 SQL 语句查询所有以“孙悟空”结尾的数据:SELECT * FROM tbl_like WHERE txt LIKE '%孙悟空'

效率十分低下,耗时达到了 2.5秒:

我们可以采用“以空间换时间”的方式来解决右后缀匹配查询时效率低下的问题。

    简单来说,我们可以将字符串倒过来,让右后缀匹配变成左前缀匹配。以“防着古海回来再抓孙悟空”为例,将其倒置之后的字符串是“空悟孙抓再来回海古着防”。当需要查找结尾为“孙悟空”的数据时,去查找以“空悟孙”开头的数据即可。

    具体做法是:在该表中增加“txt_back”列,将“txt”列的值倒置后,填入“txt_back”列中,最后为 “txt_back”列增加索引。
ALTER TABLE tbl_like ADD txt_back nvarchar(1000);-- 增加数据列
UPDATE tbl_like SET txt_back = reverse(txt); -- 填充 txt_back 的值
CREATE INDEX tbl_like_txt_back_idx ON [tbl_like] ( [txt_back] );-- 为 txt_back 列增加索引
数据表调整之后,我们的 SQL 语句也需要调整:

SELECT * FROM tbl_like WHERE txt_back LIKE '空悟孙%'
此番操作下来,执行速度就非常迅速了:

由此可知:对于右后缀匹配,我们可以建立倒序字段将右后缀匹配变成左前缀匹配来加快查询速度。

模糊查询优化
在查询所有包含“悟空”的语句时,我们使用以下的 SQL 语句:

SELECT * FROM tbl_like WHERE txt LIKE '%悟空%'
该语句无法利用到索引,所以查询非常慢,需要 2.7 秒:

遗憾的是,我们并没有一个简单的办法可以优化这个查询。但没有简单的办法,并不代表没有办法。解决办法之一就是:分词+倒排索引。+
我们需要一张数据表,把分词后的词条和原始数据对应起来,为了获得更好的效率,我们还用到了覆盖索引:

CREATE TABLE tbl_like_word (
  [id] int identity,
  [rid] int NOT NULL,
  [word] nchar(2) NOT NULL,
  PRIMARY KEY CLUSTERED ([id])
);
CREATE INDEX tbl_like_word_word_idx ON tbl_like_word(word,rid);-- 覆盖索引(Covering index)
以上 SQL 语句创建了一张名为 ”tbl_like_word“的数据表,并为其 ”word“和“rid”列增加了联合索引。这就是我们的倒排表,接下来就是为其填充数据。

在上述 SQL 语句中,我们对 rid 进行了分组,并筛选出了不重复的词组数量是三个(即我们的查询词数量)的。于是,我们可以得到正确的结果:

    在实际工作中使用 PostgreSQL 数据库,那么在做倒排索引时可以直接使用数组类型并配置 GiN 索引,以获得更好的开发和使用体验。需要注意的是,虽然 PostgreSQL 支持函数索引,但是如果对函数结果进行 LIKE 筛选时,索引并不会命中。

   对于 SQLite 这种小型数据库,模糊搜索并不能使用到索引,所以左前缀搜索和右后缀搜索的优化方式对其不生效。不过,一般我们不会使用 SQLite 去存储大量的数据,尽管分词+倒排索引的优化方式也可以在 SQLite 中实现。

打赏鼓励作者,期待更多好文!

打赏
暂无人打赏

小霞米 发表于 2023-9-8 13:29
  
每天学习一点点,每天进步一点点。
小西北 发表于 2023-9-8 13:29
  
每天学习一点点,每天进步一点点。
焱燚 发表于 2023-9-8 13:33
  
每天学习一点点,每天进步一点点。
日出 发表于 2023-9-8 13:34
  
每天学习一点点,每天进步一点点。
德德 发表于 2023-9-8 13:38
  
每天学习一点点,每天进步一点点。
飞飞侠 发表于 2023-9-8 13:39
  
每天学习一点点,每天进步一点点。
小德 发表于 2023-9-8 13:43
  
每天学习一点点,每天进步一点点。
小小胖 发表于 2023-9-8 13:43
  
每天学习一点点,每天进步一点点。
鲤鲤 发表于 2023-9-8 13:47
  
每天学习一点点,每天进步一点点。
发表新帖
热门标签
全部标签>
西北区每日一问
技术盲盒
安全效果
【 社区to talk】
技术笔记
干货满满
每日一问
信服课堂视频
新版本体验
GIF动图学习
技术咨询
功能体验
2023技术争霸赛专题
产品连连看
安装部署配置
通用技术
秒懂零信任
技术晨报
自助服务平台操作指引
原创分享
标准化排查
排障笔记本
玩转零信任
排障那些事
SDP百科
深信服技术支持平台
POC测试案例
畅聊IT
答题自测
专家问答
技术圆桌
在线直播
MVP
网络基础知识
升级
安全攻防
上网策略
测试报告
日志审计
问题分析处理
流量管理
每日一记
运维工具
云计算知识
用户认证
解决方案
sangfor周刊
VPN 对接
项目案例
SANGFOR资讯
专家分享
技术顾问
信服故事
功能咨询
终端接入
授权
设备维护
资源访问
地址转换
虚拟机
存储
迁移
加速技术
产品预警公告
信服圈儿
S豆商城资讯
技术争霸赛
「智能机器人」
追光者计划
社区帮助指南
答题榜单公布
纪元平台
卧龙计划
华北区拉练
天逸直播
以战代练
山东区技术晨报
文档捉虫活动
齐鲁TV
华北区交付直播
每周精选
2024年技术争霸赛
北京区每日一练
场景专题
故障笔记
高手请过招
升级&主动服务
高频问题集锦
社区新周刊
全能先锋系列
云化安全能力

本版达人

新手89785...

本周建议达人

新手78183...

本周分享达人