AI 助理
备案控制台
开发者社区 PolarDB开源 PolarDB PostgreSQL 版 文章 正文

PolarDB 开源版通过 parray_gin 实现高效率 数组、JSON 内元素的模糊搜索

2022-12-29 283
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版，标准版 2核4GB 50GB
简介： PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的 价值产出, 将数据变成生产力. 本文将介绍PolarDB 开源版通过 parray_gin 实现高效率 数组、JSON 内元素的模糊搜索

背景

PolarDB 的云原生存算分离架构, 具备低廉的数据存储、高效扩展弹性、高速多机并行计算能力、高速数据搜索和处理; PolarDB与计算算法结合, 将实现双剑合璧, 推动业务数据的
价值产出, 将数据变成生产力.

本文将介绍PolarDB 开源版通过 parray_gin 实现高效率 数组、JSON 内元素的模糊搜索

测试环境为macos+docker, polardb部署请参考:

测试

PG 模糊搜索采用GIN倒排索引, 使用pg_trgm插件将字符串前1后2加上空格后, 按连续3个字符切分, 并对切分后的token建立token,ctid的倒排索引.

在模糊搜索时, 可以将搜索字符串按同样方式切分, 根据倒排搜索快速的定位到对应的ctid.

即使没有parray_gin, 我们也可以将数组或JSON格式化处理后, 用大字符串和pg_trgm来实现元素模糊搜索. 例如

array['abc','aaa','hello']  
  
把元素内容的sep char和quote char转义, 然后直接把 'abc','aaa','hello'当成字符串处理. 建立pg_trgm gin索引.    
  
搜索元素时如果需要指定元素前缀或后缀搜索, 那么带上sep char和quote char即可.

使用parray_gin就简单多了, 不需要处理那么多.

下面测试PolarDB+parray_gin 实现数组内元素的模糊搜索.

git clone --depth 1 http://github.com/theirix/parray_gin/  
  
cd parray_gin/  
  
USE_PGXS=1 make  
  
USE_PGXS=1 make install  
  
export PGHOST=localhost  
  
[postgres@1bbb8082aa60 parray_gin]$ psql  
psql (11.9)  
Type "help" for help.  
  
postgres=# \q  
[postgres@1bbb8082aa60 parray_gin]$ USE_PGXS=1 make installcheck  
/home/postgres/tmp_basedir_polardb_pg_1100_bld/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/home/postgres/tmp_basedir_polardb_pg_1100_bld/bin'      --inputdir=test --dbname=contrib_regression op index  
(using postmaster on localhost, default port)  
============== dropping database "contrib_regression" ==============  
NOTICE:  database "contrib_regression" does not exist, skipping  
DROP DATABASE  
============== creating database "contrib_regression" ==============  
CREATE DATABASE  
ALTER DATABASE  
============== running regression test queries        ==============  
test op                           ... ok  
test index                        ... ok  
  
  
==========================================================  
 All 2 tests passed.   
  
 POLARDB:  
 All 2 tests, 0 tests in ignore, 0 tests in polar ignore.   
==========================================================  
create table t (id int, info text[]);  
  
create or replace function gen_text_arr(int) returns text[] as $$  
  select array(select md5(random()::text) from generate_series(1,$1));  
$$ language sql strict;  
  
postgres=# select gen_text_arr(10);  
-[ RECORD 1 ]+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
gen_text_arr | {4134ee81fcdc29da486df37a1725e1cc,d0bb424307f93a6374d1af5a4b1c0451,def4b4bc24bc6aefb084df8a1571d773,aff17d39b2c3e8ccebf1059c2cd466dc,3988cb3f89372081c6444b7f8a825cf6,77d3a12d9a5159bd2e11fac1782eaf90,0ecac2cd508f60221b31934ea1128223,622819cfa7c3e3e600f70ed90265edaa,e9311e8d6f23be74b2e73eae4408aaa8,207eb23a50212cb101f83a6041211d90}  
  
postgres=# insert into t select id , gen_text_arr(10) from generate_series(1,1000) id;  
INSERT 0 1000  
  
postgres=# select * from t where info @@> array['%4b1%'];  
 id  |                                                                                                                                                            
          info                                                                                                                                                    
                     
-----+----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
-------------------  
  14 | {745b761d7145edb79904c5217c0ec0b4,eab9d9d4de9afc8c7a2bc4cdcd3bcb2a,3116cd48046936709c56e952f5d50380,642eec5d3c17721dadb89759ac116821,49ba14c3c71b73c0a3b8  
6aa6f20a4f9c,01632c5889d4ae642422fea8620187e1,078ea7bf29a6f8bf53c6abcec98df5ad,2548e08ad3cb87dfcfe55a86e47cc60f,0c7002203e72d854f9c0643bec6c59b7,cfdd57d32f4bcee  
8b4b1adfe11a08a81}  
  33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c9  
2240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0d  
e41b70e804dfcc41d}  
...  
  
postgres=# select * from t where info @@> array['%4b1ac%'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
postgres=# select * from t where info @@> array['%4b1acd%'];  
 id | info   
----+------  
(0 rows)  
  
postgres=# explain select * from t where info @@> array['%4b1ac%'];  
                                QUERY PLAN                                  
--------------------------------------------------------------------------  
 Bitmap Heap Scan on t  (cost=28.01..32.02 rows=1 width=36)  
   Recheck Cond: (info @@> '{%4b1ac%}'::text[])  
   ->  Bitmap Index Scan on t_info_idx  (cost=0.00..28.01 rows=1 width=0)  
         Index Cond: (info @@> '{%4b1ac%}'::text[])  
(4 rows)  
  
postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t where info @@> array['%4b1ac%', '%8fc89'];  
                                                     QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=60.01..64.02 rows=1 width=36) (actual time=0.121..0.122 rows=1 loops=1)  
   Output: id, info  
   Recheck Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
   Heap Blocks: exact=1  
   Buffers: shared hit=16  
   ->  Bitmap Index Scan on t_info_idx  (cost=0.00..60.01 rows=1 width=0) (actual time=0.109..0.109 rows=1 loops=1)  
         Index Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
         Buffers: shared hit=15  
 Planning Time: 0.075 ms  
 Execution Time: 0.144 ms  
(10 rows)  
  
Time: 0.699 ms  
postgres=# select * from t where info @@> array['%4b1ac%', '%8fc89'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
Time: 0.733 ms  
  
postgres=# insert into t select id , gen_text_arr(10) from generate_series(1,120000) id;  
INSERT 0 100000  
Time: 9242.877 ms (00:09.243)  
postgres=# \dt+  
                   List of relations  
 Schema | Name | Type  |  Owner   | Size  | Description   
--------+------+-------+----------+-------+-------------  
 public | t    | table | postgres | 50 MB |   
(1 row)  
  
  
  
postgres=# select * from t where info @@> array['%4b1ac%', '%8fc89'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
Time: 4.783 ms  
  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from t where info @@> array['%4b1ac%', '%8fc89'];  
                                                      QUERY PLAN                                                        
----------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=96.94..529.04 rows=121 width=36) (actual time=4.114..4.115 rows=1 loops=1)  
   Output: id, info  
   Recheck Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
   Heap Blocks: exact=1  
   Buffers: shared hit=48  
   ->  Bitmap Index Scan on t_info_idx  (cost=0.00..96.91 rows=121 width=0) (actual time=4.103..4.103 rows=1 loops=1)  
         Index Cond: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
         Buffers: shared hit=47  
 Planning Time: 0.090 ms  
 Execution Time: 4.170 ms  
(10 rows)

全表扫描性能差了几十倍

postgres=# set enable_bitmapscan =off;  
SET  
Time: 0.473 ms  
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from t where info @@> array['%4b1ac%', '%8fc89'];  
                                                QUERY PLAN                                                  
----------------------------------------------------------------------------------------------------------  
 Seq Scan on public.t  (cost=0.00..7881.50 rows=121 width=36) (actual time=0.632..193.929 rows=1 loops=1)  
   Output: id, info  
   Filter: (t.info @@> '{%4b1ac%,%8fc89}'::text[])  
   Rows Removed by Filter: 120999  
   Buffers: shared hit=6229 read=140  
 Planning Time: 0.081 ms  
 Execution Time: 193.947 ms  
(7 rows)  
  
Time: 194.697 ms  
postgres=# select * from t where info @@> array['%4b1ac%', '%8fc89'];  
 id |                                                                                                                                                             
         info                                                                                                                                                     
                    
----+-----------------------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------------------------  
------------------  
 33 | {639e7f990ef271b24b1ac1a1f154476b,5c0dd44f87821cf555fb579f2dd9871d,b3118d34a6f788ad9c9d3343743900bc,798abd4aece1cbe604e608294227dde6,f08757d02fd0db9d08c92  
240c55ec14b,54f206220cf2097f0e2a6f630a7871be,585d04664a022ab49607d0d6ff18fc89,f5681d20b2b923973652f9952df6b71d,1d204241c105c78ba0514bdf1dba6bbb,5f427b5c2b65e0de  
41b70e804dfcc41d}  
(1 row)  
  
Time: 199.342 ms

有了parray_gin, 在设计数据结构时, 可以更加灵活, 例如将“一个时间段、一个组、一个对象”的“多个标签、多个信息”打包成1行数组存储, 对数组进行元素搜索, 则可以快速匹配到符合条件的“一个时间段、一个组、一个对象”.

参考

http://github.com/theirix/parray_gin/

《PostgreSQL 数组或JSON内容的模糊匹配索引插件: parray_gin》

文章标签：
云原生数据库 PolarDB
自然语言处理
SQL
关系型数据库
索引
PostgreSQL
PolarDB
数据格式
存储
JSON
分布式数据库
关键词：
云原生数据库 PolarDB搜索
云原生数据库 PolarDB开源
JSON数组
数组JSON
polardb JSON
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
德哥
目录
相关文章
lsug6eziqmdfk1111
|
11天前
|
存储 关系型数据库 分布式数据库
使用开源PolarDB和imgsmlr进行高效的图片存储和相似度搜索
使用开源PolarDB和imgsmlr进行高效的图片存储和相似度搜索
lsug6eziqmdfk1111
19 5
sunrr
|
14天前
|
SQL JSON 关系型数据库
MySQL是一个广泛使用的开源关系型数据库管理系统，它有许多不同的版本
【10月更文挑战第3天】MySQL是一个广泛使用的开源关系型数据库管理系统，它有许多不同的版本
sunrr
64 5
#FFF
|
14天前
|
关系型数据库 分布式数据库 数据库
PolarDB 开源：推动数据库技术新变革
在数字化时代，数据成为核心资产，数据库的性能和可靠性至关重要。阿里云的PolarDB作为新一代云原生数据库，凭借卓越性能和创新技术脱颖而出。其开源不仅让开发者深入了解内部架构，还促进了数据库生态共建，提升了稳定性与可靠性。PolarDB采用云原生架构，支持快速弹性扩展和高并发访问，具备强大的事务处理能力及数据一致性保证，并且与多种应用无缝兼容。开源PolarDB为国内数据库产业注入新活力，打破国外垄断，推动国产数据库崛起，降低企业成本与风险。未来，PolarDB将在生态建设中持续壮大，助力企业数字化转型。
#FFF
56 2
体验瑶池数据库
|
1月前
|
关系型数据库 分布式数据库 数据库
开源云原生数据库PolarDB PostgreSQL 15兼容版本正式发布
PolarDB进行了深度的内核优化，从而实现以更低的成本提供商业数据库的性能。
体验瑶池数据库
168 13
龙大吉
|
1月前
惊世骇俗！开源 PolarDB-X 部署安装大冒险，全程心跳与惊喜不断！
【9月更文挑战第8天】作为技术爱好者的我，近期成功完成了开源 PolarDB-X 的部署安装。尽管过程中遇到不少挑战，但通过精心准备环境、下载安装包、配置参数及启动服务等步骤，最终顺利实现部署。本文将详细介绍部署全过程及可能遇到的问题，为您的 PolarDB-X 探索之旅提供参考与启发，希望能让大家在技术海洋里畅游得更加顺利！
龙大吉
93 2
众所周知
|
1月前
|
Cloud Native 关系型数据库 分布式数据库
PolarDB开源项目未来展望：技术趋势与社区发展方向
【9月更文挑战第5天】随着云计算技术的发展，阿里云推出的云原生分布式数据库PolarDB受到广泛关注。本文探讨PolarDB的未来展望，包括云原生与容器化集成、HTAP及实时分析能力提升、智能化运维与自动化管理等技术趋势；并通过加强全球开源社区合作、拓展行业解决方案及完善开发者生态等措施推动社区发展，目标成为全球领先的云原生数据库之一，为企业提供高效、可靠的服务。
众所周知
74 5
众所周知
|
1月前
|
关系型数据库 MySQL 分布式数据库
PolarDB开源社区动态：最新版本功能亮点与更新解读
【9月更文挑战第6天】随着云计算技术的发展，分布式数据库系统成为企业数据处理的核心。阿里云的云原生数据库PolarDB自开源以来备受关注，近日发布的最新版本在内核稳定性、性能、分布式CDC架构及基于时间点的恢复等方面均有显著提升，并新增了MySQL一键导入功能。本文将解读这些新特性并提供示例代码，帮助企业更好地利用PolarDB处理实时数据同步和离线分析任务，提升数据安全性。未来，PolarDB将继续创新，为企业提供更高效的数据处理服务。
众所周知
125 3
六卿
|
1月前
|
XML 存储 JSON
Twaver-HTML5基础学习(19)数据容器(2)_数据序列化_XML、Json
本文介绍了Twaver HTML5中的数据序列化，包括XML和JSON格式的序列化与反序列化方法。文章通过示例代码展示了如何将DataBox中的数据序列化为XML和JSON字符串，以及如何从这些字符串中反序列化数据，重建DataBox中的对象。此外，还提到了用户自定义属性的序列化注册方法。
六卿
39 1
运营研究坊
|
4天前
|
JSON JavaScript Java
在Java中处理JSON数据：Jackson与Gson库比较
本文介绍了JSON数据交换格式及其在Java中的应用，重点探讨了两个强大的JSON处理库——Jackson和Gson。文章详细讲解了Jackson库的核心功能，包括数据绑定、流式API和树模型，并通过示例演示了如何使用Jackson进行JSON解析和生成。最后，作者分享了一些实用的代码片段和使用技巧，帮助读者更好地理解和应用这些工具。
运营研究坊
16 0
在Java中处理JSON数据：Jackson与Gson库比较
技术交流18179014480
|
14天前
|
JSON JavaScript API
（API接口系列）商品详情数据封装接口json数据格式分析
在成长的路上，我们都是同行者。这篇关于商品详情API接口的文章，希望能帮助到您。期待与您继续分享更多API接口的知识，请记得关注Anzexi58哦！
技术交流18179014480
41 1
PolarDB开源

PolarDB PostgreSQL 版

热门文章

最新文章

  • 1
    如何一键本地部署PolarDB for PostgreSQL
  • 2
    分布式1024节点！1天玩转PolarDB-X超大规模集群
  • 3
    PolarDB-X 动手实践系列第一讲：如何一键部署开源 PolarDB-X
  • 4
    PolarDB 开源版 使用TimescaleDB 实现时序数据高速写入、压缩、实时聚合计算、自动老化等
  • 5
    PG内核解读-第1节PostgreSQL系统概述
  • 6
    【Paper Reading】DEPART：分布式KV存储系统的副本解耦方案
  • 7
    PolarDB-PG | PostgreSQL + 阿里云OSS 实现高效低价的海量数据冷热存储分离
  • 8
    如何快速安装部署PolarDB-X
  • 9
    PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP
  • 10
    2024年全国大学生计算机系统能力大赛PolarDB数据库创新设计赛（天池杯）等你来战！
  • 1
    2024年全国大学生计算机系统能力大赛PolarDB数据库创新设计赛（天池杯）等你来战！
    178
  • 2
    PolarDB 体验报告
    371
  • 3
    开源之夏2024学生报名启动！阿里云PolarDB社区项目期待你的参与！
    2338
  • 4
    智能辅助驾驶业务遭遇大表瓶颈，小鹏汽车如何破局？
    80457
  • 5
    5分钟免费体验PolarDB PG版Serverless的极致弹性！
    622
  • 6
    【公测】PolarDB PostgreSQL版Serverless功能免费使用​！
    877
  • 7
    一文熟悉PolarDB-PG 分区表核心特性
    82778
  • 8
    成都晨云信息技术完成阿里云PolarDB数据库产品生态集成认证
    876
  • 9
    PolarDB PostgreSQL版：Oracle兼容的高性能数据库
    486
  • 10
    若依框架数据源切换为pg库
    520

    • 相关产品

  • 云原生数据库 PolarDB
    文档详情 产品详情

    • 相关课程

    更多
  • PolarDB for PostgreSQL 内核解读系列课程
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门
  • JSON 自学手册图文教程

    • 相关电子书

    更多
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设
  • 2023云栖大会：和客户一起玩转PolarDB新特性
  • 2023云栖大会：PolarDB for AI

    • 相关实验场景

    更多
  • 使用PolarDB和ECS搭建门户网站
  • PolarDB MySQL全球数据库（GDN）异地多活
  • PolarDB MySQL自动SQL限流
  • PolarDB MySQL从表回收站中恢复误删的表
  • PolarDB MySQL通过DMS恢复误操作数据
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
    • 下一篇
    AI助理直击要害，从繁复中提炼精华——使用CDN加速访问OSS存储的图片