专栏首页枕边书见招拆招-PostgreSQL中文全文索引效率优化

见招拆招-PostgreSQL中文全文索引效率优化

前言

上文 使用PostgreSQL进行中文全文检索 中我使用 PostgreSQL 搭建完成了一套中文全文检索系统,对数据库配置和分词都进行了优化,基本的查询完全可以支持,但是在使用过程中还是发现了一些很恼人的问题,包括查询效果和查询效率,万幸都一一解决掉了。

其中过程自认为还是很有借鉴意义的,今天来总结分享一下。

博客欢迎转载,请带上来源:http://www.cnblogs.com/zhenbianshu/p/8253131.html 


使用B树索引优化查询效果

分词问题

一开始是分词效果的问题:

  • 中文博大精深,乒乓球拍卖啦、南京市长江大桥 这种歧义句的分词,还没有一个分词插件能够达到 100% 的准确率,当然包括我们正在使用的 scws 分词库;
  • 我们的搜索内容是 Poi 地点名,而很多地点名都缺失语义性,产生歧义词的概率更大;

scws 支持更为灵活的分词等级,为了能分出较多的词来尽量包含目标结果,我们将 scws 的分词等级调为了 7(不了解的可以看上文),但同时也引入了更奇葩的问题: 搜索天安门 查不到 天安门广场。。。

原因也很另人无语:

  • 天安门广场 的分词结果向量 tsv 是 '天安':2 '天安门广场':1 '广场':4 '门广':3;
  • 查询向量 to_tsquery('parser', '天安门') tsq 的结果是 '天安门' & '天安' & '安门';
  • 查询语句是 SELECT * FROM table WHERE tsv @@ tsq, 由于 tsv 里没有 tsq 里的 安门向量,匹配失败。

B树索引

一个常识:大家想搜一个地点时大多会先输入其名称前面的部分,基于此考虑,我向表内引入 B树索引支持前缀查询,配合原来分词的 GIN 索引,解决了此问题。

如Mysql一样,PostgreSQL 也支持通过 like '关键词%' 语句来使用 B树索引。在 name 列上添加了 B树索引,再修改查询语句变为 SELECT * FROM table WHERE tsv @@ tsq OR name LIKE 'keyword%',这样结果就完全 OK 啦。


使用子查询优化查询效率

GIN索引效率问题

紧接着又发现了新的问题:

PostgreSQL 的 GIN 索引(Generalized Inverted Index 通用倒排索引)存储的是 (key, posting list)对, 这里的 posting list 是一组出现键的行ID。如 数据:

行ID

分词向量

1

测试 分词

2

分词 结果

则索引的内容就是 测试=>1 分词=>1,2 结果=>2,在我们要查询分词向量内包含 分词 的数据时就可以快速查找到第1,2列。

但这种设计也带来了另一个问题,当某一个 key 对应的 posting list 过大时,数据操作会很慢,如我们的数据中地点名带有 饭店 的数据就很多,有几十万,而我们的需求有一项就是要对查询结果按照 评分 一列倒序排序,这么几十万数据,数据库响应超时会达到 3000 ms。

我们期望的响应时间是 90% 50ms 以内,虽然统计结果显示,确实 90% 的请求已经符合要求,但另外的 10% 完全不能用也是不可能接受的。

接下来的优化就是针对这些 bad case。

缓存

对于这种响应超时的问题,大家肯定会想到万能的缓存:把响应超时的查询结果放到缓存,查询时先检查缓存。

可是超时的毕竟只有很少一部分,缓存的命中率堪忧。虽然这一小部分查询可用了,但是所有查询语句都会多出一次取缓存的操作。

为了能提高缓存命中率,我还特意统计了关键字各长度的搜索数量占比和超时率占比,发现以下情况:

  • 1字节(1个字母)、3字节(单字)关键词的超时率最高,可是也不超过 30%;
  • 1字节、3字节关键词的搜索量占比有30%左右;
  • 其他长度关键词的超时率10%左右,非常尴尬。

这种情况打消了我只针对某些长度的关键词设置缓存的想法。

不仅是命中率问题,缓存过期时间和缓存更新等更是大坑,基于以上考虑,缓存方案彻底被放弃。

分表

一个方法不行,那就换一个方向,既然某些关键词的结果集太大,那么我们就将它变小一些,我们一开始采用的策略是分表。

由于 Poi 地点都有区域属性,我们以区域 ID 将这些数据分成了多个数据表,原来最大的关键词结果集有几十万,拆分到多个表后,每个表中最大的关键词结果集也就几万,此时的排序性能提高了,基本在 100~200ms 之间。

查询时我们先通过位置将用户定位到区域,根据区域 ID 确定要查询的表,再从对应表内查询结果。

这个方案的缺点也非常多:

  • 对定位很依赖,且定位计算区域也会有耗时;
  • 区域边缘点的搜索很蛋疼,明明离得很近,如果被划分到跟用户不同区域了就搜索不到。
  • 多个表非常不好维护。

子查询

终于灵活考虑了业务需求,引入子查询提出了一种颇为完美的方案:

用户在搜索框键入了 饭店、宾馆 等无意义关键词,不同于搜索 海底捞,此时用户也不知道他自己需要什么,对搜索结果是没有明确期待的。

这时候,我们也并不需要很愣地把全国名字中带有饭店、宾馆的地点都拿出来排序,这样的排序结果用户也不一定满意。 我们可以只取一部分 Poi 地点给用户,如果结果用户不满意,会再完善关键词,而关键词稍有完善,结果集就会极大地减小。

子查询用来实现结果集过滤非常有效,如我们可以在极大页码查询分页时使用子查询先过滤掉一大批无用数据。

本例中,我们在子查询语句中使用 limit 语句限制取的结果集条数,从而大大减小排序压力,查询语句类似 SELECT id FROM (SELECT * FROM table WHERE tsv @@ tsq OR name LIKE 'keyword%' LIMIT 10000) AS tmp ORDER BY score DESC

这样优化过后,查询语句的最差性能也可以稳定在 170ms 以下了。


替换B树索引消灭慢查询

多索引效率问题

本以为优化到此为止了呢,可是有次在试着查询 中关村 两个关键词时,我明确感觉到了响应时间的差异, 100ms 左右的时间差还是很明显的。

子查询语句才是这条 SQL 语句的效率关键,于是我开始分析 这个关键词的 子查询SQL 语句,首先我试着调整语句中 limit 的限制值,发现即使只取 1000条,响应时间也在 100ms 以上。

接着我又尝试改变 SQL 语句的 WHERE 条件,去除 OR name LIKE 'keyword%' 后, 总条数并没有太大的变动,结果集由 13w 减小到了 11w, 但 添加 limit 后的效率却急剧提升:

SQL

结果条数

响应时间

添加 limit 后

SQL

响应时间

WHERE tsv @@ tsq OR name LIKE 'keyword%'

13W

2400ms

WHERE tsv @@ tsq OR name LIKE 'keyword%' LIMIT 10000

170ms

WHERE tsv @@ tsq

11W

1900ms

WHERE tsv @@ tsq limit 10000

25ms

这样对比起来就很明显了, 分词查询的 GIN 索引和前缀词查询的 B树索引之间配合并不完美。

想想也是,如果在一个索引上取 1w 条数据,直接取就行了,而如果在两个索引上取 1w 数据,那么还得考虑每个索引上各取多少,取完后还要排重。

替换B树索引

问题分析完,那么就得根据问题寻找解决方案了,怎么能把两个索引并到同一索引上呢?把分词 GIN 索引并到 B树索引显然是不可能的,只能试着使用分词来替代 B树索引。

当时有三种方案:

  • 修改开源分词库 scws,添加一个分前缀词的功能。不过我担心改出 Bug,而且还要改 PostgreSQL 的分词插件 zhparser 以适应 scws 的参数变动。
  • 使用 PostgreSQL 的数组类型(text[])存储分词结果,后续往此字段内灵活添加前缀词。但填充数组字段需要调用 SELECT to_tsvector('parser', 'nane') 查询后使用脚本处理结果后再写入数组,比较麻烦。
  • 修改 tsvector 分词向量字段,手动向此字段添加前缀词的分词向量。但分词向量不同于文本,不能直接拼接。

最好的方案当然是最后一种,改动最小,于是我就查询了一下 PostgreSQL 向量拼接,还是找到了向量拼接的方法,使用 ::tsvector 将字符串强转成向量,再使用 || 拼接到原来的分词向量上,SQL 语句类似 SELECT to_tsvector('parser', 'keyword') || 'prefix'::tsvector

在查询时,就可以直接使用 WHERE tsv @@ to_tsquery('parser', 'keyword') 查询前缀了。这样,子查询语句的响应时间就可以大大降低了,在 50ms 左右,而且还可以通过减小 LIMIT 值来加快响应。

此后,B树索引就可以退休啦~


小结

以上就是我对 PostgreSQL 关键词查询从效果到效率优化的全过程了,效果和效率已经完全达标了。当然,还可以对用户体验进行再优化,比如添加错别字识别、拼音首字母智能识别等,打磨好一款产品当然是非常不容易的,还需要继续努力。

顺便吐槽几句周边同事对 PostgreSQL 的态度,理由竟然是认为它是一个开源产品,可能会有各种埋得深的坑,所以不信任。

比较想不到比较前沿的互联网公司也会有人对开源抱如此看法,不可否认很多开源产品或工具都有各种各样的坑,但为此因噎废食大可不必,我们一直在用的 Linux/Git 还是开源产品呢,可有多少人离不开它们?而且闭源产品就不会出现问题么?也不可否认 PostgreSQL 小众,但它也有自己的特色,而且近年来它的占有率一率攀升,未来什么样,还未可知。

关于本文有什么问题可以在下面留言交流,如果您觉得本文对您有帮助,可以点击下面的 推荐 支持一下我,博客一直在更新,欢迎 关注

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • PHP用mb_string函数库处理与windows相关中文字符

    昨天想批处理以前下载的一堆文件,把文件里的关键内容用正则匹配出来,集中处理。在操作文件时遇到一个问题,就是windows操作系统中的编码问题。 我们都知道win...

    枕边书
  • PHP中的数据库一、MySQL优化策略综述

    前些天看到一篇文章说到PHP的瓶颈很多情况下不在PHP自身,而在于数据库。我们都知道,PHP开发中,数据的增删改查是核心。为了提升PHP的运行效率,程序员不光需...

    枕边书
  • 空间索引 - 各数据库空间索引使用报告

    空间索引 索引我们都用过,它是一种特殊的存储结构,就像图书馆里书的分类存放策略或是现代化图书馆里的图书查询系统,能帮助我们快速找到自己需要的书。 数据库中,索引...

    枕边书
  • Macaca 极简教程Macaca 介绍快速开始:从无到有搭建 Macaca 环境 (forMac)代码实例awesome-macaca

    macaca提供的元素查找工具,可以将app视图的结构以布局结构树的格式在浏览器上展示出来,用过点击某个元素,就可以方便的查询到该控件的基本信息,以方便查找。具...

    一个会写诗的程序员
  • 用sphinx给PHP加个给力的搜索功能

    最近工作上需要实现搜索功能,尝试了几种方案。虽然最终线上部署的还是最low的方案,但是中间的过程还是比较有意思的。业务上根据关键字查找内容。关键字的出处多来源于...

    写PHP的老王
  • 电气技术中的文字符号和项目代号

    一个电气系统或一种电气设备通常都是由各种基本件、部件、组件等组成,为了在电气图上或其他技术文件中表示这些基本件、部件、组件,除了采用各种图形符号外,还须标注一些...

    机器人网
  • uikiller使用手册(一)

    uikiller是使用名命规则来控制UI节点、组件和触摸事件,减少UI相关的代码与编辑器设置,实现原理是提前对UI树的遍历。

    张晓衡
  • Redis中string、list的底层数据结构原理

    Redis 有一个比较突出的特点就是数据结构更丰富, 「string、hash、list、set、zset、Redis5.0 新数据结构-stream」

    用户2781897
  • 8个SQL错误写法,你中枪了几个

    分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般 DBA 想到的办法是在 type, name, create_time...

    框架师
  • 新google haking

    intitle:搜索网页标题中包含有特定字符的网页。例如输入“intitle: cbi”,这样网页标题中带有cbi的网页都会被搜索出来。

    字节脉搏实验室

扫码关注云+社区

领取腾讯云代金券