全部产品
云市场

电子商务

更新时间:2018-12-02 20:40:07

本章节以简化的业务场景来介绍电子商务的最佳实践。

业务场景和要求

本例中简化的业务场景包含三个数据表:客户信息表、订单表、商品类型表(维度表)。

业务场景要求如下:

  • 统计不同客户群体在不同时间段的数据。

  • RT<60秒。

  • 每秒查询率QPS( Query Per Second ):偶尔查询。

表的逻辑设计

客户信息表(t_fact_customers)

数据量:约有五亿客户

字段名 数据类型 说明
customer_id varchar 客户编号
customer_name varchar 客户姓名
phone_number varchar 电话
address varchar 地址
last_login_time timestamp 最近登录时间
age int 年龄

订单表(t_fact_orders)

数据量:每日订单量5000万,需要存储三年的数据,总数据量约550亿。

字段名 数据类型 说明
order_id varchar 订单编号
customer_id varchar 客户编号
goods_id bigint 商品编号
numbers bigint 数量
total_price double 总价
order_time timestamp 订单时间
order_date bigint 订单日期(二级分区)

商品类型表(t_dim_goods)

商品总数约100万

字段名 数据类型 说明
goods_id bigint 商品id
price double 单价
class bigint 类型
name varchar 名称
update_time timestamp 更新时间

表的物理设计

物理设计的目的是获得最佳性能,在进行表的物理设计前,必须了解表的查询SQL,才能决策表的最佳分片策略。假设本例中大部分查询都需要关联查询客户表+订单表,那么我们优先考虑以customer_id进行一级分区,每个节点的计算都是本地数据。

创建表组ads_demo

表组为ads_demo,两个副本,SQL查询超时为30秒。创建表组的SQL语句如下:

  1. create tablegroup ads_demo;

创建表

创建t_fact_customers表

每个分区数据量390万=5亿/128个一级分区。创建t_fact_customers表的SQL语句如下。

  1. CREATE TABLE t_fact_customers (
  2. customer_id varchar COMMENT '',
  3. customer_name varchar COMMENT '',
  4. phone_number varchar COMMENT '',
  5. address varchar COMMENT '',
  6. last_login_time timestamp COMMENT '',
  7. age int COMMENT '',
  8. PRIMARY KEY (customer_id)
  9. )
  10. PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
  11. TABLEGROUP ads_demo
  12. OPTIONS (UPDATETYPE='realtime')
  13. COMMENT '';

创建t_fact_orders表

二级分区键order_date为bigint数据类型。根据数据量和存储总时间,按月(201712)间隔,每月一个二级分区,每个二级分区数据量为:1193万 = 550亿/(128个一级分区)/(3年*12个月)。创建表的SQL语句如下:

  1. CREATE TABLE t_fact_orders (
  2. order_id varchar COMMENT '',
  3. customer_id varchar COMMENT '',
  4. goods_id bigint COMMENT '',
  5. numbers bigint COMMENT '',
  6. total_price double COMMENT '',
  7. order_time timestamp COMMENT '',
  8. order_date bigint COMMENT '',
  9. PRIMARY KEY (order_id,customer_id,order_date)
  10. )
  11. PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
  12. SUBPARTITION BY LIST KEY (order_date)
  13. SUBPARTITION OPTIONS (available_partition_num = 90)
  14. TABLEGROUP ads_demo
  15. OPTIONS (UPDATETYPE='realtime')
  16. COMMENT '';

创建t_dim_goods商品信息表(维度表)

  1. CREATE DIMENSION TABLE t_dim_goods (
  2. goods_id bigint comment '',
  3. price double comment '',
  4. class bigint comment '',
  5. name VARCHAR comment '',
  6. update_time TIMESTAMP comment '',
  7. primary key (goods_id)
  8. )
  9. OPTIONS (UPDATETYPE='realtime');

查询SQL

按年龄段统计在一段时间内订单的客户数量、订单销售总额。

  1. SELECT
  2. case when cus.age <= 30 then '<20' when cus.age>20
  3. and cus.age <= 30 then '20-30' when cus.age>30
  4. and cus.age <= 40 then '30-40' else '>40' end as age_range,
  5. COUNT(distinct cus.customer_id),
  6. SUM(total_price)
  7. FROM
  8. t_fact_customers cus LEFT JOIN t_fact_orders ord ON cus.customer_id =ord.customer_id
  9. WHERE ord.order_time >= '2017-10-01 00:00:00' AND ord.order_time < '
  10. 2017-11-01 00:00:00'
  11. AND ord.order_date = 201710
  12. GROUP BY age_range;

可以增加二级分区条件,进行二级分区裁剪,提高查询效率。