PostgreSQL 百亿数据 秒级响应 正则及模糊查询

最新推荐文章于 2023-07-16 13:05:58 发布
最新推荐文章于 2023-07-16 13:05:58 发布
阅读量2.5k 收藏 21
点赞数 2
文章标签： 数据库 大数据 人工智能
原文链接：https://yq.aliyun.com/articles/7444
版权
本文介绍了在PostgreSQL中如何对亿级数据进行正则表达式和模糊查询的优化，包括使用btree索引、reverse函数、pg_trgm插件的gin索引等方法。通过案例分析，展示了不同类型的模糊查询优化策略，如带前缀、后缀和不带前缀后缀的查询，以及正则表达式查询。同时，文章讨论了优化技巧，如调整索引参数和使用分区表以实现秒级响应。
摘要由CSDN通过智能技术生成

正则匹配和模糊匹配通常是搜索引擎的特长，但是如果你使用的是 PostgreSQL 数据库照样能实现，并且性能不赖，加上分布式方案 (譬如 plproxy, pg_shard, fdw shard, pg-xc, pg-xl, greenplum)，处理百亿以上数据量的正则匹配和模糊匹配效果杠杠的，同时还不失数据库固有的功能，一举多得。

物联网中有大量的数据，除了数字数据，还有字符串类的数据，例如条形码，车牌，手机号，邮箱，姓名等等。
假设用户需要在大量的传感数据中进行模糊检索，甚至规则表达式匹配，有什么高效的方法呢？
这种场景还挺多，例如市面上发现了一批药品可能有问题，需要对药品条码进行规则表达式查找，找出复合条件的药品流向。
又比如在侦查行动时，线索的检索，如用户提供的残缺的电话号码，邮箱，车牌，IP地址，QQ号码，微信号码等等。
根据这些信息加上时间的叠加，模糊匹配和关联，最终找出罪犯。
可以看出，模糊匹配，正则表达式匹配，和人脸拼图有点类似，需求非常的迫切。

首先对应用场景进行一下分类，以及现有技术下能使用的优化手段。
.1. 带前缀的模糊查询，例如 like 'ABC%'，在PG中也可以写成 ~ '^ABC'
可以使用btree索引优化，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

.2. 带后缀的模糊查询，例如 like '%ABC'，在PG中也可以写成 ~ 'ABC$'
可以使用reverse函数btree索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

.3. 不带前缀和后缀的模糊查询，例如 like '%AB_C%'，在PG中也可以写成 ~ 'AB.C'
可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

.4. 正则表达式查询，例如 ~ '[\d]+def1.?[a|b|0|8]{1,3}'
可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

PostgreSQL pg_trgm插件自从9.1开始支持模糊查询使用索引，从9.3开始支持规则表达式查询使用索引，大大提高了PostgreSQL在刑侦方面的能力。
代码见
https://github.com/postgrespro/pg_trgm_pro

pg_trgm插件的原理，将字符串前加2个空格，后加1个空格，组成一个新的字符串，并将这个新的字符串按照每3个相邻的字符拆分成多个token。
当使用规则表达式或者模糊查询进行匹配时，会检索出他们的近似度，再进行filter。
GIN索引的图例：
26721394885162976
从btree检索到匹配的token时，指向对应的list, 从list中存储的ctid找到对应的记录。
因为一个字符串会拆成很多个token，所以没插入一条记录，会更新多条索引，这也是GIN索引需要fastupdate的原因。
正则匹配是怎么做到的呢？
详见 https://raw.githubusercontent.com/postgrespro/pg_trgm_pro/master/trgm_regexp.c
实际上它是将正则表达式转换成了NFA格式，然后扫描多个TOKEN，进行bit and|or匹配。
正则组合如果转换出来的的bit and|or很多的话，就需要大量的recheck，性能也不能好到哪里去。

下面针对以上四种场景，实例讲解如何优化。

.1. 带前缀的模糊查询，例如 like 'ABC%'，在PG中也可以写成 ~ '^ABC'
可以使用btree索引优化，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。
例子，1000万随机产生的MD5数据的前8个字符。

postgres=# create table tb(info text);  
CREATE TABLE  
postgres=# insert into tb select substring(md5(random()::text),1,8) from generate_series(1,10000000);  
INSERT 0 10000000  
postgres=# create index idx_tb on tb(info);  
CREATE INDEX  
postgres=# select * from tb limit 1;  
   info     
----------  
 376821ab  
(1 row)  
postgres=# explain select * from tb where info ~ '^376821' limit 10;  
                                  QUERY PLAN                                     
-------------------------------------------------------------------------------  
 Limit  (cost=0.43..0.52 rows=10 width=9)  
   ->  Index Only Scan using idx_tb on tb  (cost=0.43..8.46 rows=1000 width=9)  
         Index Cond: ((info >= '376821'::text) AND (info < '376822'::text))  
         Filter: (info ~ '^376821'::text)  
(4 rows)  
postgres=# select * from tb where info ~ '^376821' limit 10;  
   info     
----------  
 376821ab  
(1 row)  
Time: 0.536 ms  
postgres=# set enable_indexscan=off;  
SET  
Time: 1.344 ms  
postgres=# set enable_bitmapscan=off;  
SET  
Time: 0.158 ms  
postgres=# explain select * from tb where info ~ '^376821' limit 10;  
                           QUERY PLAN                             
----------------------------------------------------------------  
 Limit  (cost=0.00..1790.55 rows=10 width=9)  
   ->  Seq Scan on tb  (cost=0.00..179055.00 rows=1000 width=9)  
         Filter: (info ~ '^376821'::text)  
(3 rows)  
Time: 0.505 ms

带前缀的模糊查询，不使用索引需要5483毫秒。
带前缀的模糊查询，使用索引只需要0.5毫秒。

postgres=# select * from tb where info ~ '^376821' limit 10;  
   info     
----------  
 376821ab  
(1 row)  
Time: 5483.655 ms

.2. 带后缀的模糊查询，例如 like '%ABC'，在PG中也可以写成 ~ 'ABC$'
可以使用reverse函数btree索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

postgres=# create index idx_tb1 on tb(reverse(info));  
CREATE INDEX  
postgres=# explain select * from tb where reverse(info) ~ '^ba128' limit 10;  
                                         QUERY PLAN                                           
--------------------------------------------------------------------------------------------  
 Limit  (cost=0.43..28.19 rows=10 width=9)  
   ->  Index Scan using idx_tb1 on tb  (cost=0.43..138778.43 rows=50000 width=9)  
         Index Cond: ((reverse(info) >= 'ba128'::text) AND (reverse(info) < 'ba129'::text))  
         Filter: (reverse(info) ~ '^ba128'::text)  
(4 rows)  

postgres=# select * from tb where reverse(info) ~ '^ba128' limit 10;  
   info     
----------  
 220821ab  
 671821ab  
 305821ab  
 e65821ab  
 536821ab  
 376821ab  
 668821ab  
 4d8821ab  
 26c821ab  
(9 rows)  
Time: 0.506 ms

带后缀的模糊查询，使用索引只需要0.5毫秒。

.3. 不带前缀和后缀的模糊查询，例如 like '%AB_C%'，在PG中也可以写成 ~ 'AB.C'
可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

postgres=# create extension pg_trgm;  
postgres=# explain select * from tb where info ~ '5821a';  
                                 QUERY PLAN                                   
----------------------------------------------------------------------------  
 Bitmap Heap Scan on tb  (cost=103.75..3677.71 rows=1000 width=9)  
   Recheck Cond: (info ~ '5821a'::text)  
   ->  Bitmap Index Scan on idx_tb_2  (cost=0.00..103.50 rows=1000 width=0)  
         Index Cond: (info ~ '5821a'::text)  
(4 rows)  
Time: 0.647 ms  

postgres=# select * from tb where info ~ '5821a';  
   info     
----------  
 5821a8a3  
 945821af  
 45821a74  
 9fe5821a  
 5821a7e0  
 5821af2a  
 1075821a  
 e5821ac9  
 d265821a  
 45f5821a  
 df5821a4  
 de5821af  
 71c5821a  
 375821a3  
 fc5821af  
 5c5821ad  
 e65821ab  
 5821adde  
 c35821a6  
 5821a642  
 305821ab  
 5821a1c8  
 75821a5c  
 ce95821a  
 a65821ad  
(25 rows)  
Time: 3.808 ms

前后模糊查询，使用索引只需要3.8毫秒。

.4. 正则表达式查询，例如 ~ '[\d]+def1.?[a|b|0|8]{1,3}'
可以使用pg_trgm的gin索引，或者拆列用多列索引叠加bit and或bit or进行优化（只适合固定长度的端字符串，例如char(8)）。

前后模糊查询，使用索引只需要108毫秒。

postgres=# select * from tb where info ~ 'e65[\d]{2}a[b]{1,2}8' limit 10;  
   info     
----------  
 4e6567ab  
 1e6530ab  
 e6500ab8  
 ae6583ab  
 e6564ab7  
 5e6532ab  
 e6526abf  
 e6560ab6  
(8 rows)  
Time: 108.577 ms

时间主要花费在排他上面。
检索了14794行，remove了14793行。大量的时间花费在无用功上，但是比全表扫还是好很多。

postgres=# explain (verbose,analyze,buffers,costs,timing) select * from tb where info ~ 'e65[\d]{
   2}a[b]{
   1,2}8' limit 10;  
                                                            QUERY PLAN                                                              
----------------------------------------------------------------------------------------------------------------------------------  
 Limit  (cost=511.75..547.49 rows=10 width=9) (actual time=89.934..120.567 rows=1 loops=1)  
   Output: info  
   Buffers: shared hit=13054  
   ->  Bitmap Heap Scan on public.tb  (cost=511.75..4085.71 rows=1000 width=9) (actual time=89.930..120.562 rows=1 loops=1)  
         Output: info  
         Recheck Cond: (tb.info ~ 'e65[\d]{
   2}a[b]{
   1,2}8'::text)  
         Rows Removed by Index Recheck: 14793  
         Heap Blocks: exact=12929  
         Buffers: shared hit=13054  
         ->  Bitmap Index Scan on idx_tb_2  (cost=0.00..511.50 rows=1000 width=0) (actual time=67.589..67.589 rows=14794 loops=1)  
               Index Cond: (tb.info ~ 'e65[\d]{
   2}a[b]{
   1,2}8'::text)  
               Buffers: shared hit=125  
 Planning time: 0.493 ms  
 Execution time: 120.618 ms  
(14 rows)  
Time: 124.693 ms

优化：
使用gin索引后，需要考虑性能问题，因为info字段被打散成了多个char(3)的token，从而涉及到非常多的索引条目，如果有非常高并发的插入，最好把gin_pending_list_limit设大，来提高插入效率，降低实时合并索引带来的RT升高。
使用了fastupdate后，会在每次vacuum表时，自动将pengding的信息合并到GIN索引中。
还有一点，查询不会有合并的动作，对于没有合并的GIN信息是使用遍历的方式搜索的。

压测高并发的性能：

create table tbl(id serial8, crt_time timestamp, sensorid int, sensorloc point, info text) with (autovacuum_enabled=on, autovacuum_vacuum_threshold=0.000001,autovacuum_vacuum_cost_delay=0);  
CREATE INDEX trgm_idx ON tbl USING GIN (info gin_trgm_ops) with (fastupdate='on', gin_pending_list_limit='6553600');  
alter sequence tbl_id_seq cache 10000;

修改配置，让数据库的autovacuum快速迭代合并gin。

vi $PGDATA/postgresql.conf  
autovacuum_naptime=1s  
maintenance_work_mem=1GB  
autovacuum_work_mem=1GB  
autovacuum = on  
autovacuum_max_workers = 3  
log_autovacuum_min_duration = 0  
autovacuum_vacuum_cost_delay=0  

$ pg_ctl reload

创建一个测试函数，用来产生随机的测试数据。

postgres=# create or replace function f() returns void as $$  
  insert into tbl (crt_time,sensorid,info) values ( clock_timestamp(),trunc(random()*500000),substring(md5(random()::text),1,8) );  
$$ language sql strict;  

vi test.sql  
select f();  

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 48 -j 48 -T 10000  

progress: 50.0 s, 52800.9 tps, lat 0.453 ms stddev 0.390  
progress: 51.0 s, 52775.8 tps, lat 0.453 ms stddev 0.398  
progress: 52.0 s, 53173.2 tps, lat 0.449 ms stddev 0.371  
progress: 53.0 s, 53010.0 tps, lat 0.451 ms stddev 0.390  
progress: 54.0 s, 53360.9 tps, lat 0.448 ms stddev 0.365  
progress: 55.0 s, 53285.0 tps, lat 0.449 ms stddev 0.362  
progress: 56.0 s, 53662.1 tps, lat 0.445 ms stddev 0.368  
progress: 57.0 s, 53283.8 tps, lat 0.448 ms stddev 0.385  
progress: 58.0 s, 53703.4 tps, lat 0.445 ms stddev 0.355  
progress: 59.0 s, 53818.7 tps, lat 0.444 ms stddev 0.344  
progress: 60.0 s, 53889.2 tps, lat 0.443 ms stddev 0.361  
progress: 61.0 s, 53613.8 tps, lat 0.446 ms stddev 0.355  
progress: 62.0 s, 53339.9 tps, lat 0.448 ms stddev 0.392  
progress: 63.0 s, 54014.9 tps, lat 0.442 ms stddev 0.346  
progress: 64.0 s, 53112.1 tps, lat 0.450 ms stddev 0.374  
progress: 65.0 s, 53706.1 tps, lat 0.445 ms stddev 0.367  
progress: 66.0 s, 53720.9 tps, lat 0.445 ms stddev 0.353  
progress: 67.0 s, 52858.1 tps, lat 0.452 ms stddev 0.415  
progress: 68.0 s, 53218.9 tps, lat 0.449 ms stddev 0.387  
progress: 69.0 s, 53403.0 tps, lat 0.447 ms stddev 0.377  
progress: 70.0 s, 53179.9 tps, lat 0.449 ms stddev 0.377  
progress: 71.0 s, 53232.4 tps, lat 0.449 ms stddev 0.373  
progress: 72.0 s, 53011.7 tps, lat 0.451 ms stddev 0.386  
progress: 73.0 s, 52685.1 tps, lat
最低0.47元/天 解锁文章
weixin_33893473
关注
  • 2
    点赞
  • 踩
  • 21
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL 百亿级数据范围查询, 分组排序窗口取值 极致优化 case
weixin_34224941的博客
05-06 1991
本文将对一个任意范围按ID分组查出每个ID对应的最新记录的CASE做一个极致的优化体验。 优化后性能维持在可控范围内，任意数据量，毫秒级返回，性能平稳可控。 比优化前性能提升1万倍。 CASE 有一张数据表，结构： CREATE TABLE target_position ( target_id varchar(80), time big...
postgreSQL如何快速查询大表数据量
JavaPub
11-22 2265
如果你需要更快地获取表中的行数，你可以考虑使用特定的技术或工具来进行优化。一种常见的方法是使用表的统计信息，比如PostgreSQL中的。但需要注意的是，这种方法可能不会总是返回最新的准确值，因为统计信息可能会有一定的延迟。表，它包含了关于表的行数等统计信息。这个查询会直接从系统表中获取表的统计信息，因此可能比。我有一个非常大的表，估计几百万或者几千万。速度非常快，有图有真相。
PostgreSQL正则表达式查询
weixin_34097242的博客
08-14 182
参考http://www.php100.com/manual/PostgreSQL8/functions-matching.html LIKE string LIKE pattern [ ESCAPE escape-character ] string NOT LIKE pattern [ ESCAPE escape-character ] 每个pattern定义一个字串的集合。 如果...
PostgreSQL 1000亿数据量 正则匹配 速度与激情
weixin_34378045的博客
03-07 1181
承接上一篇 https://yq.aliyun.com/articles/7444 测试环境为 8台主机(16c/host)的 PostgreSQL集群，一共240个数据节点，测试数据量1008亿。 性能图表 : 如果要获得更快的响应速度，可以通过增加主机和节点数(或者通过增加CPU和节点数)，缩短recheck的处理时间。 数据生成方法： #...
PostgreSQL 11 100亿 tpcb 性能测试 on ECS
weixin_34203426的博客
04-14 596
标签 PostgreSQL , tpcb , pgbench , 100亿 背景 PostgreSQL 11 发布在即，以下是tpcc与tpch测试的结果： 《PostgreSQL 11 tpcc 测试(103万tpmC on ECS) - use sysbench-tpcc by Percona-Lab》 《(TPC-H测试 SF=10,SF=20...
Postgresql 正则表达式
dengwan3818的博客
12-19 734
在postgresql中使用正则表达式时需要使用关键字“~”，以表示该关键字之前的内容需匹配之后的正则表达式，若匹配规则不需要区分大小写，可以使用组合关键字“~*”； 相反，若需要查询不匹配这则表达式的记录，只需在该关键字前加否定关键字“！”即可。若正则表达式包含转义字符，则需在表达式前加关键字“E”。 例如： [sql]view plaincopy ...
PostgreSQL Like模糊查询优化方案
最新发布
04-02
虽然标准的B树索引不适用于LIKE '%xxx%',但PostgreSQL 9.6及更高版本引入了布隆过滤器（Bloom Filters）和位图索引（Bitmap Indexes），在某些情况下可以辅助模糊查询。位图索引尤其适合在低基数（low-cardinality）...
postgresql 正则代替in查询
02-24
postgresql 正则代替in查询 当需要参数查询时非常有用
PostgreSQL 正则表达式 常用函数的总结
10-19
主要介绍了PostgreSQL 正则表达式 常用函数的总结的相关资料,对那些需要进行复杂数据处理的程序来说，正则表达式无疑是一个非常有用的工具，这里就介绍下如何使用，需要的朋友可以参考下
postgresql分页数据重复问题的深入理解
09-09
在PostgreSQL中，分页查询是一项常见的操作，但有时可能会遇到分页数据重复的问题。这种情况通常是由于在排序字段中存在重复值导致的。为了解决这个问题，我们需要理解PostgreSQL的排序机制以及如何选择合适的排序...
PostgreSQL：使用 JSON 函数和正则表达式，带你轻松高效处理半结构化数据
明而决之
07-16 1743
本文围绕PostgreSQL数据库中的JSON函数和正则表达式的运用，由浅入深介绍了几种常用数据处理方法，分别是JSON 取值、拆分键值对、拆分字符串和批量匹配字符串。 PostgreSQL的JSON数据类型表现出极大的灵活性，支持键值对和嵌套数组结构，使得我们能够方便地存储和检索非结构化数据。而且，再结合具有优秀处理字符串能力的正则表达式，处理非结构化数据就变得轻而易举。 由于文章篇幅的限制，仅介绍了JSON 函数和正则表达式很小的一部分内容。如果你想获取更多信息，了解更全面的知识
查询亿级数据毫秒级返回！牛逼哄哄的ElasticSearch是如何做到的？
石杉的架构笔记
06-09 3706
点击上方石杉的架构笔记，右上角选择“设为星标”每日早8点半，技术文章准时送上公众号后台回复“学习”，获取作者独家秘制精品资料往期文章BAT 面试官是如何360°无死角考察...
2亿数据量PostgreSQL 10.4查询调优思路分享
热门推荐
jui121314的博客
12-07 1万+
目录   ●背景 ●使用物理服务器 ●增加内存大小 ●使用NVMe协议的固态硬盘 ●将数据库安装在内存 ●业务调整 ●修改默认配置项 ●启用Gin (Generalized Inverted Index)索引（仅适用于非前缀模糊查询） ●启用Gist(Generalized Search Tree)索引（仅适用于非前缀模糊查询） ●小结 ●背景 最近工作中遇到了数据库的查...
模糊匹配、相似度查询怎么破？看PG亿级检索毫秒响应
PostgreSQL China
12-23 2671
阅读使人充实，讨论使人敏捷，写作使人精确。 需求场景假设 Aken某天在咖啡店听到一首歌，觉得很好听，但不知道具体的歌名，只知道歌曲是“民谣”，歌词包含“一把破吉他”、“旅人”，然后是男歌手。 如果我想收藏该歌曲，请问小编如何为我找到目标歌曲、对应的歌手、专辑？ 为什么要讨论这个问题 首先，搜索需求的实际场景实在太多了，可以说处处可见，比如： 百度、Google输入关键字搜索信息； 泛娱乐行业搜索对应的目标音视频文件； 人脸识别、指纹验证、特定动作捕捉认证等。 其次，物联网时代网络产生的数据信息浩如烟海
【记录】postgresql like 模糊查询优化
u010328311的博客
02-11 5427
【记录】postgresql like 模糊查询优化
StarRocks在千亿级日增数据场景下替换Greenplum的实践
StarRocks的博客
04-23 2617
原Greenplum集群在进行10亿*1亿的关联查询时达到极限，无法支撑更大数据量级的关联查询。使用StarRocks替换Greenplum构建新的集群，在进行736亿*15亿的超大量级数据关联查询时，可以顺利完成，并且耗时很短，对业务的整体提升巨大。 一、使用背景 1.1 选用原因 我司原有业务查询使用的数据库为Greenplum，在数据源变更后，数据量从原来的日增千万级别（近百G）暴增至日增千亿（10T）级别，原有的12台GP集群在数据量增长后存在以下痛点： 1、数据导入 原有的数据导入借.
postgresql模糊查询
11-08
PostgreSQL是一种广泛使用的关系数据库管理系统。在进行模糊查询时，我们可以使用LIKE操作符和通配符来实现模糊匹配。...通过使用LIKE操作符和通配符，我们可以在PostgreSQL中实现各种模糊查询，以满足我们的需求。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值