数据库开发者社区 > 正文

多流聚合与JSON全文检索的功能应用

简介:
+关注继续查看

标签

PostgreSQL , 写时合并 , 记录级快照 , json , json索引 , json聚合 , 圈选 , 并行计算


背景

这个需求是这样的,数据在写入时,以上一条记录作为基础,将当前写入的记录与上一条记录合并,然后作为新的记录写进去。

从而每一条记录都携带了之前所有记录的内容。

当然这里指的是每个维度各自的快照,并不是一张表所有记录的快照。

例如,一笔电商订单,可能经过若干个系统(每个系统产生的属性可能都不一样,多个系统合起来就是个大宽表,应用为了设计简单,往往可能选择JSON存储,而不是大宽表),产生若干笔记录,每次写入时期望将之前与之相关的记录内容都合并起来,产生新的值写入。

但是不要忘记,同一笔订单的数据,可能存在并行写入(除非业务上能将订单编号按哈希让一个线程来处理它,而且不能多机)。当存在并发写同一笔订单时,写时合并就违反自然规律。

例子:

tbl已有记录 (0, 1, 'test0', now())  
  
session A:  
  
insert into tbl (pk, caseid, info, crt_time) values (1, 1, 'test1', now());  
  
session B:  
  
insert into tbl (pk, caseid, info, crt_time) values (2, 1, 'test2', now());  
  
如果SESSION A,B同时发起,那么写入的记录可能变成:  
  
(1, 1, 'test0_test1', now());  
  
(2, 1, 'test0_test2', now());  
  
然而实际上要的可能是这两条  
  
(1, 1, 'test0_test1', now());  
  
(2, 1, 'test0_test1_test2', now());  

所以,我们使用另一种方法来获取快照,写入时,不改变原始的写入方法,即各个业务线产生的订单记录,分别写入到一个单表,使用JSON来表示各个业务线对这个订单的描述。

JSON写入性能

create table tbl_ord (  
  ordid int8,   -- 订单号  
  appid  int,   -- 应用ID  
  info jsonb,   -- 内容  
  crt_time timestamp  -- 写入时间  
);  
  
create index idx_tbl_ord on tbl_ord(ordid, crt_time);  

单条写入压测

vi test.sql  
  
\set ordid random(1,10000000)  
\set appid random(1,10)  
insert into tbl_ord (ordid,appid,info,crt_time) values (:ordid,:appid,jsonb '{"a" : 1, "b" : 2}',now());  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 40 -j 40 -t 2500000  

单条写入压测,23.4万行/s。

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 40  
number of threads: 40  
number of transactions per client: 2500000  
number of transactions actually processed: 100000000/100000000  
latency average = 0.170 ms  
latency stddev = 0.498 ms  
tps = 234047.009786 (including connections establishing)  
tps = 234060.902533 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.001  \set ordid random(1,10000000)  
         0.001  \set appid random(1,10)  
         0.168  insert into tbl_ord (ordid,appid,info,crt_time) values (:ordid,:appid,jsonb '{"a" : 1, "b" : 2}',now());  

如果使用批量写入,可以达到100万+行/s。

JSON全字段索引

PostgreSQL 支持JSON类型的全字段索引,支持两种operator class,支持的检索如下。

GIN indexes can be used to efficiently search for keys or key/value pairs occurring within   
a large number of jsonb documents (datums).   
  
Two GIN “operator classes” are provided, offering different performance and flexibility trade-offs.  
  
The default GIN operator class for jsonb supports queries with top-level key-exists operators   
?, ?& and ?| operators and path/value-exists operator @>.   
  
(For details of the semantics that these operators implement,   
  
see Table 9.44.) An example of creating an index with this operator class is:  
  
CREATE INDEX idxgin ON api USING GIN (jdoc);  
  
The non-default GIN operator class jsonb_path_ops supports indexing the @> operator only.   
  
An example of creating an index with this operator class is:  
  
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);  
create index idx_tbl_ord_2 on tbl_ord using gin (info);  

使用举例

-- Find documents in which the key "company" has value "Magnafone"  
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';  
  
-- Find documents in which the key "tags" contains key or array element "qui"  
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';  
  
-- Find documents in which the key "tags" contains array element "qui"  
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';  

点查,json聚合,得任意时间快照

取某个时间点,某个caseid的快照,使用JSONB聚合,性能贼好。

将所有记录聚合成一条

select caseid, jsonb_agg((pk,info,crt_time) order by crt_time) from tbl where caseid=? and crt_time<=? group by caseid;  

jsonb_agg用法举例

postgres=# create type typ1 as (c1 int, c2 int);  
CREATE TYPE  
  
postgres=# select jsonb_agg((c1,c2)::typ1 order by c1 desc) from (values (1,2),(2,3)) t(c1,c2);  
                jsonb_agg                   
------------------------------------------  
 [{"c1": 2, "c2": 3}, {"c1": 1, "c2": 2}]  
(1 row)  

按订单的聚合查询性能:

0.7毫秒

create type typ2 as (appid int, info jsonb, crt_time timestamp);  
  
postgres=# select ordid, jsonb_agg((appid,info,crt_time)::typ2 order by crt_time) from tbl_ord where ordid=1 and crt_time<=now() group by ordid;  
  
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
ordid     | 1  
jsonb_agg | [{"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:24:56.659672"}, {"info": {"a": 1, "b": 2}, "appid": 5, "crt_time": "2017-12-09T23:25:13.073163"}, {"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:25:49.94649"}, {"info": {"a": 1, "b": 2}, "appid": 10, "crt_time": "2017-12-09T23:26:23.523946"}, {"info": {"a": 1, "b": 2}, "appid": 2, "crt_time": "2017-12-09T23:26:49.900199"}, {"info": {"a": 1, "b": 2}, "appid": 7, "crt_time": "2017-12-09T23:27:10.643058"}, {"info": {"a": 1, "b": 2}, "appid": 8, "crt_time": "2017-12-09T23:27:20.937021"}, {"info": {"a": 1, "b": 2}, "appid": 8, "crt_time": "2017-12-09T23:27:21.446752"}, {"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:29:19.10536"}, {"info": {"a": 1, "b": 2}, "appid": 7, "crt_time": "2017-12-09T23:29:56.192353"}, {"info": {"a": 1, "b": 2}, "appid": 1, "crt_time": "2017-12-09T23:30:07.879201"}, {"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:30:31.487457"}]  
  
Time: 0.696 ms  

压测

vi test.sql  
  
\set ordid random(1,10000000)  
select ordid, jsonb_agg((appid,info,crt_time)::typ2 order by crt_time) from tbl_ord where ordid=:ordid and crt_time<=now() group by ordid;  

结果

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 28 -j 28 -T 120  
  
transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 28  
number of threads: 28  
duration: 120 s  
number of transactions actually processed: 4677282  
latency average = 0.718 ms  
latency stddev = 0.463 ms  
tps = 38977.016281 (including connections establishing)  
tps = 38982.209839 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.001  \set ordid random(1,10000000)  
         0.717  select ordid, jsonb_agg((appid,info,crt_time)::typ2 order by crt_time) from tbl_ord where ordid=:ordid and crt_time<=now() group by ordid;  

1亿记录,任意订单任意时间点快照聚合,性能:

TPS:38982

平均响应时间:0.718 毫秒

批量获取,使用JSON索引 + PostgreSQL 10的并行计算,飞快

1亿记录,全表扫一遍只需要1秒。

postgres=# select count(*) from tbl_ord;  
   count     
-----------  
 100000000  
(1 row)  
  
Time: 1014.201 ms (00:01.014)  

根据索引搜索,极快(毫秒级),支持并行扫描,也支持单进程扫描:

postgres=# explain (analyze,verbose,timing,costs,buffers) SELECT * from tbl_ord WHERE info @> '{"a": 5}';  
                                                             QUERY PLAN                                                                
-------------------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=32241.40..142872.70 rows=100000 width=61) (actual time=3.878..3.878 rows=0 loops=1)  
   Output: ordid, appid, info, crt_time  
   Workers Planned: 1  
   Workers Launched: 1  
   Single Copy: true  
   ->  Bitmap Heap Scan on public.tbl_ord  (cost=32241.40..142872.70 rows=100000 width=61) (actual time=0.158..0.158 rows=0 loops=1)  
         Output: ordid, appid, info, crt_time  
         Recheck Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)  
         Buffers: shared hit=6  
         Worker 0: actual time=0.158..0.158 rows=0 loops=1  
           Buffers: shared hit=6  
         ->  Bitmap Index Scan on idx_tbl_ord_2  (cost=0.00..32216.40 rows=100000 width=0) (actual time=0.153..0.153 rows=0 loops=1)  
               Index Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)  
               Buffers: shared hit=6  
               Worker 0: actual time=0.153..0.153 rows=0 loops=1  
                 Buffers: shared hit=6  
 Planning time: 0.092 ms  
 Execution time: 4.836 ms  
(18 rows)  
  
Time: 5.416 ms  
postgres=# set max_parallel_workers_per_gather =0;  
SET  
Time: 0.202 ms  
postgres=# explain (analyze,verbose,timing,costs,buffers) SELECT * from tbl_ord WHERE info @> '{"a": 5}';  
                                                          QUERY PLAN                                                             
-------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.tbl_ord  (cost=32241.40..142872.70 rows=100000 width=61) (actual time=0.062..0.062 rows=0 loops=1)  
   Output: ordid, appid, info, crt_time  
   Recheck Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)  
   Buffers: shared hit=6  
   ->  Bitmap Index Scan on idx_tbl_ord_2  (cost=0.00..32216.40 rows=100000 width=0) (actual time=0.060..0.060 rows=0 loops=1)  
         Index Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)  
         Buffers: shared hit=6  
 Planning time: 0.091 ms  
 Execution time: 0.098 ms  
(9 rows)  
  
Time: 0.539 ms  

批量数据结果通过写OSS,与其他业务平台打通

RDS PG OSS 外部表文档:

https://help.aliyun.com/knowledge_detail/43352.html

HDB PG OSS 外部表文档:

https://help.aliyun.com/document_detail/35457.html

小结

1、一笔电商订单,可能经过若干个系统(每个系统产生的属性可能都不一样,多个系统合起来就是个大宽表,应用为了设计简单,往往可能选择JSON存储,而不是大宽表),业务上需要任意时间点,一笔订单的所有也许数据合并。

2、使用JSON聚合,可以很好的解决订单按时间顺序,合并所有业务线数据的需求。

3、PostgreSQL JSON类型,支持GIN索引,可以实现高效率的JSON内容检索。

4、通过RDS PG对接OSS对象存储,用户在对订单数据进行筛选后,如果要输送给其他平台,通过OSS外部表,可以轻松的对接其他业务。

5、PostgreSQL 支持全表、索引、排序、聚合等操作的并行计算,使得亿级的表,查询飞快。

参考

https://www.postgresql.org/docs/10/static/functions-json.html

https://www.postgresql.org/docs/10/static/datatype-json.html

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
JSON、JSONObject 与 JSONArray 详细介绍及其应用方式
JSON、JSONObject 与 JSONArray 详细介绍及其应用方式
287 0
json介绍与在php中的应用
json介绍与在php中的应用
99 0
json基础知识与Qt中的应用
json基础知识与Qt中的应用
117 0
JSON Web Token - 在Web应用间安全地传递信息
JSON Web Token - 在Web应用间安全地传递信息
111 0
推荐一个非常好用的Chrome扩展应用,用于美化Json字符串
推荐一个非常好用的Chrome扩展应用,用于美化Json字符串
78 0
SQLServer · 特性分析 · XML与JSON应用比较
背景 SQL Server是一种强大的数据库引擎,不仅性能卓越,稳定,功能还很强大,SQL Server 2016中已经支持JSON。这让我想到以前工作中经常使用的SQL XML,也对比一下他们几个关键领域的应用方法。这两种SQL特性,在实际的工作中也是常用的功能,特别是JSON在最近发展非常火爆,SQL Server也不落后,很快就在SQL Server2016支持。 广义的XML与JSON
1500 0
数据库领域前沿技术分享与交流
热门文章
热门讨论
+关注
德哥
公益是一辈子的事, I am digoal, just do it.
文章
问答
视频
相关电子书
更多
基于HBase的海量数据查询与检索解析_游骐_202105_v3
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载