PostgreSQL 电商业务(任意维度商品圈选应用) - json包range数组的命中优化 - 展开+索引优化-阿里云开发者社区

开发者社区> 德哥> 正文

PostgreSQL 电商业务(任意维度商品圈选应用) - json包range数组的命中优化 - 展开+索引优化

简介:
+关注继续查看

标签

PostgreSQL , range , jsonb , gist , btree_gist , 展开 , array


背景

电商,任意维度商品圈选应用,其中一个查询请求是这样的:

求 "某个国家、某个时间点、调价+折扣后的价格" 落在某个价格范围的商品。

首先需要有的要素包括:

1、商品ID

2、不同国家的商品价格

3、商品原价

4、商品日常价

5、不同时间段的价格折扣

6、调价系数

例子

1、表结构设计

create table t_item (  
  id int8 primary key,   -- 商品ID  
  country jsonb,         -- 每个国家的价格取值范围  
  price jsonb,           -- 每个时间段的折扣,(时间可能重叠,根据优先级LIMIT 1个折扣)  
  ratio float4           -- 调价比例  
  -- 其他属性scalar类型, 使用rum或gin索引,本文末尾有案例  
);  

2、数据样本

insert into t_item values (  
  1,  
  jsonb '{"global":{"min": 100, "max":200}, "china":{"min": 120, "max":260}, "us":{"min": 170, "max":300}}',  
  jsonb '{"100|[1514764800,1515542400)":0.4, "200|[1514764800,1515542400)":0.9, "0|[-62135596800,253402214400)":1}',  
  0.1  
);  

其中时间区间可以使用epoch表示

postgres=# select extract(epoch from date '2018-01-01');  
 date_part    
------------  
 1514764800  
(1 row)  
  
postgres=# select extract(epoch from date '2018-01-10');  
 date_part    
------------  
 1515542400  
(1 row)  
  
postgres=# select extract(epoch from date '0001-01-01');  
  date_part     
--------------  
 -62135596800  
(1 row)  
  
postgres=# select extract(epoch from date '9999-12-31');  
  date_part     
--------------  
 253402214400  
(1 row)  

3、由于不同时间段的折扣不一样,并且优先级也不一样,所以,使用一个函数来获取某个时间点的这块。

当输入的时间点有多个时间区间包括它时,取优先级最高的那个折扣,并返回,如果没有任何匹配的时间区间,则返回1。

create or replace function get_discount(  
  jsonb,  -- 每个时间段的折扣字段  
  int8    -- epoch 时间值  
) returns float4 as $$  
declare  
  res float4;   
begin  
  -- select split_part(key,'|',1) as priority, split_part(key,'|',2) as ts, value from jsonb_each_text($1);  
  select value into res from jsonb_each_text($1) where split_part(key,'|',2)::int8range @> $2 order by split_part(key,'|',1)::numeric desc limit 1;  
  if found then  
    return res;  
  end if;  
  return 1;  
end;  
$$ language plpgsql strict parallel safe;  

例子

postgres=# select get_discount(jsonb '{"100|[1514764800,1515542400)":0.4, "200|[1514764800,1515542400)":0.9, "0|[-62135596800,253402214400)":1}', 100000);  
 get_discount   
--------------  
            1  
(1 row)  
  
postgres=# select get_discount(jsonb '{"100|[1514764800,1515542400)":0.4, "200|[1514764800,1515542400)":0.9, "0|[-62135596800,253402214400)":1}', 1515542200);  
 get_discount   
--------------  
          0.9  
(1 row)  

4、不同的国家,价格不一样,输入国家编码,返回对应国家的价格,如果输入的编码在JSONB中没有,则返回global的价格。

create or replace function get_price(  
  jsonb,  -- 国家价格区间  
  text    -- 国家编码  
) returns float8 as $$  
  select case when ($1->$2->>'max')::float8 is not null then ($1->$2->>'max')::float8 else ($1->'global'->>'max')::float8 end;  
$$ language sql strict parallel safe;  

例子

postgres=# select get_price(jsonb '{"global":{"min": 100, "max":200}, "china":{"min": 120, "max":260}, "us":{"min": 170, "max":300}}', 'hello');  
 get_price   
-----------  
       200  
(1 row)  
  
  
  
postgres=# select get_price(jsonb '{"global":{"min": 100, "max":200}, "china":{"min": 120, "max":260}, "us":{"min": 170, "max":300}}', 'china');  
 get_price   
-----------  
       260  
(1 row)  

5、求 "某个国家、某个时间点、调价+折扣后的价格" 落在某个价格范围的商品。

SQL

postgres=# select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 100;    
 id | country | price | ratio   
----+---------+-------+-------  
(0 rows)  
  
  
postgres=# select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 1000;    
 id |                                                 country                                                 |                                                    price                                                     | ratio   
----+---------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------+-------  
  1 | {"us": {"max": 300, "min": 170}, "china": {"max": 260, "min": 120}, "global": {"max": 200, "min": 100}} | {"100|[1514764800,1515542400)": 0.4, "200|[1514764800,1515542400)": 0.9, "0|[-62135596800,253402214400)": 1} |   0.1  
(1 row)  

6、压测

写入5.3亿数据

insert into t_item select * from t_item ;  
.....  
insert into t_item select * from t_item ;  

单表约 186 GB

postgres=# \dt+ t_item  
                     List of relations  
 Schema |  Name  | Type  |  Owner   |  Size  | Description   
--------+--------+-------+----------+--------+-------------  
 public | t_item | table | postgres | 186 GB |   
(1 row)  

7、使用并行计算

postgres=# alter function get_price  ;  
ALTER FUNCTION  
postgres=# alter function get_discount  parallel safe;  
ALTER FUNCTION  
  
postgres=# set max_parallel_workers_per_gather =56;  
SET  
postgres=# alter table t_item set (parallel_workers =56);  
ALTER TABLE  
  
postgres=# set min_parallel_table_scan_size =0;  
SET  
postgres=# set min_parallel_index_scan_size =0;  
SET  
postgres=# set parallel_setup_cost =0;  
SET  
postgres=# set parallel_tuple_cost =0;  
SET  

8、最差的情况,没有一条命中的数据,耗时为处理完5.3亿条记录的耗时

postgres=# explain select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 100 ;  
                                                                           QUERY PLAN                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=1000.00..51024073.42 rows=178956971 width=332)  
   Workers Planned: 32  
   ->  Parallel Seq Scan on t_item  (cost=0.00..33127376.32 rows=5592405 width=332)  
         Filter: (((get_price(country, 'china'::text) * get_discount(price, '1515542200'::bigint)) * ('1'::double precision + ratio)) < '100'::double precision)  
(4 rows)  
  
  
  
postgres=# explain analyze select * from t_item where get_price(country, 'china') * get_discount(price, 1515542200) * (1+ratio) < 100 ;  
                                                                           QUERY PLAN                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------  
 Gather  (cost=1000.00..47285151.00 rows=178956971 width=332) (actual time=444448.106..444448.106 rows=0 loops=1)  
   Workers Planned: 56  
   Workers Launched: 56  
   ->  Parallel Seq Scan on t_item  (cost=0.00..29388453.90 rows=3195660 width=332) (actual time=444292.055..444292.055 rows=0 loops=57)  
         Filter: (((get_price(country, 'china'::text) * get_discount(price, '1515542200'::bigint)) * ('1'::double precision + ratio)) < '100'::double precision)  
         Rows Removed by Filter: 9418788  
 Planning Time: 0.072 ms  
 Execution Time: 462253.627 ms  
(8 rows)  

56 core 虚拟机,耗时462秒。

索引优化

将数据展开为两张表(其中一张可以使用原始表,不需要新建)

其中表1的数据,需要业务方维护,当原价、折扣、调价系数发生变化时,需要实时的更新这里的记录。

表1

折扣区间展开表:

商品ID  
国家  
时间区间  
折后价  
create table t_item1 (  
  id int8,  
  country text,  
  ts int8range,  
  price float8,  
  exclude using gist (id with =, country with =, ts with &&)  -- 排他约束,同一个商品ID同一个国家不允许有TS相交的折扣数据
);  
  
create extension IF NOT EXISTS btree_gist;  
  
create index idx_t_item1_1 on t_item1 using gist (country,price,ts);  

表2

常规价格查原始表:

商品ID  
国家  
日常价  
原价  
调价比例  
create table t_item2 (  
  id int8,  
  country text,  
  price1 float8,  
  price2 float8,  
  ratio float4,  
  primary key (country, id)  
);  
  
create index idx_t_item2_1 on t_item2 (country, (least(price1*ratio,price2*ratio)));  

SQL

求 "某个国家、某个时间点、调价+折扣后的价格" 落在某个价格范围的商品。

select id from t_item1 where country ='china' and price < 50::float8 and ts @> 10000000::int8  
union  
select id from t_item2  where country='china' and (least(price1*ratio,price2*ratio)) < 50;  

  
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=5.91..5.94 rows=3 width=8)
   Group Key: t_item1.id
   ->  Append  (cost=0.14..5.90 rows=3 width=8)
         ->  Index Scan using idx_t_item1_1 on t_item1  (cost=0.14..2.37 rows=1 width=8)
               Index Cond: ((country = 'china'::text) AND (price < '50'::double precision) AND (ts @> '10000000'::bigint))
         ->  Index Scan using idx_t_item2_1 on t_item2  (cost=0.15..3.49 rows=2 width=8)
               Index Cond: ((country = 'china'::text) AND (LEAST((price1 * ratio), (price2 * ratio)) < '50'::double precision))
(7 rows)

小结

第一种设计,简化了程序开发,但是无法使用索引扫描,性能会比较差。

第二种设计,当调价比例、原价、折扣数据发生变化时,程序需要维护价格的变更到t_item1表,程序开发上会增加一定的负担,(当然也可以使用数据库触发器来更新,程序偷一下懒,但是不推荐这么做)。

参考

《PostgreSQL 函数式索引使用注意 - 暨非immutable函数不适合索引的原因》

《PostgreSQL ADHoc(任意字段组合)查询(rums索引加速) - 非字典化,普通、数组等组合字段生成新数组》

《PostgreSQL ADHoc(任意字段组合)查询 与 字典化 (rum索引加速) - 实践与方案1》

《PostgreSQL 店铺运营实践 - JSON[]数组 内部标签数据等值、范围检索100倍+加速示例 (含,单值+多值列合成)》

《PostgreSQL UDF实现tsvector(全文检索), array(数组)多值字段与scalar(单值字段)类型的整合索引(类分区索引) - 单值与多值类型复合查询性能提速100倍+ 案例 (含,单值+多值列合成)》

《会议室预定系统实践(解放开发) - PostgreSQL tsrange(时间范围类型) + 排他约束》

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

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

相关文章
Soundslice – 美妙乐谱!Web 技术高大上的应用
  Web 技术的不断发展让我们能够开发各种好玩的功能。这里给大家分享一个使用 HTML5 技术实现的在线乐谱,可以播放的哦,也可以选择一个片段进行循环播放。赶紧来体验一下:) 您可能感兴趣的相关文章 10大流行的 Metro UI 风格 Bootstrap 主题 推荐35款精致的 CS...
778 0
以“用户播放行为与体验”为核心的视频服务质量优化
如何应对视频直播中复杂多样的用户网络环境,提高视频服务质量是各直播服务平台面临的一大难题。Twitch提出了一种无监督学习的方法,全面评估用户观看时的行为与体验,预测用户的网络状况,通过码率自适应的方法实现快速的迭代升级从而提高服务质量。本文来自Twitch Principal Research Engineer沈悦时在LiveVideoStackCon 2018中的分享,并由LiveVideoStack整理而成。
118 0
MySQL的索引是什么?怎么优化?
MySQL的索引是什么?怎么优化? 索引对大数据的查询速度的提升是非常大的,Explain可以帮你分析SQL语句是否用到相关索引。 索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。
1330 0
Microsoft Access应用可以这么玩
Access作为一个轻量级的个人数据库,很久以前是数据库设计的流行工具,整理资料的过程中看到一个基于access的accdb格式的应用,功能也很强大。在目前网络应用主流的今天这样的应用比较少,不过在特定的场景下采用这个方案仍是一个高效快速的解决方案。
882 0
CDN高级技术专家周哲:深度剖析短视频分发过程中的用户体验优化技术点
深圳云栖大会已经圆满落幕,在3月29日飞天技术汇-弹性计算、网络和CDN专场中,阿里云CDN高级技术专家周哲为我们带来了《海量短视频极速分发》的主题分享,带领我们从视频内容采集、上传、存储和分发的角度介绍整体方案,并且重点讲解短视频加速的注意事项和用户体验优化要点。
5109 0
JSON在PHP中的应用
互联网的今天,AJAX已经不是什么陌生的词汇了。说起AJAX,可能会立即想起因RSS而兴起的XML。XML的解析,恐怕已经不是什么难题了,特别是 PHP5,大量的XML解析器的涌现,如最轻量级的SimpleXML。
769 0
轻松搭建基于 Serverless 的 ThinkPHP 应用
首先介绍下在本文出现的几个比较重要的概念: 函数计算(Function Compute): 函数计算是一个事件驱动的服务,通过函数计算,用户无需管理服务器等运行情况,只需编写代码并上传。函数计算准备计算资源,并以弹性伸缩的方式运行用户代码,而用户只需根据实际代码运行所消耗的资源进行付费。
2564 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载