电子商务

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

业务场景和要求

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

业务场景要求如下：

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

• RT<60秒。

• 每秒查询率QPS（ Query Per Second ）：偶尔查询。

表的逻辑设计

客户信息表（t_fact_customers）

数据量：约有五亿客户

订单表（t_fact_orders）

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

商品类型表（t_dim_goods）

商品总数约100万

表的物理设计

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

创建表组ads_demo

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

create tablegroup ads_demo;

创建表

创建t_fact_customers表

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

CREATE TABLE t_fact_customers (
customer_id varchar COMMENT '',
customer_name varchar COMMENT '',
phone_number varchar COMMENT '',
address varchar COMMENT '',
last_login_time timestamp COMMENT '',
age int COMMENT '',
PRIMARY KEY (customer_id)
)
PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE='realtime')
COMMENT '';

创建t_fact_orders表

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

CREATE TABLE t_fact_orders (
order_id varchar COMMENT '',
customer_id varchar COMMENT '',
goods_id bigint COMMENT '',
numbers bigint COMMENT '',
total_price double COMMENT '',
order_time timestamp COMMENT '',
order_date bigint COMMENT '',
PRIMARY KEY (order_id,customer_id,order_date)
)
PARTITION BY HASH KEY (customer_id) PARTITION NUM 128
SUBPARTITION BY LIST KEY (order_date)
SUBPARTITION OPTIONS (available_partition_num = 90)
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE='realtime')
COMMENT '';

创建t_dim_goods商品信息表（维度表）

CREATE DIMENSION TABLE t_dim_goods (
goods_id bigint comment '',
price double comment '',
class bigint comment '',
name VARCHAR comment '',
update_time TIMESTAMP comment '',
primary key (goods_id)
)
OPTIONS (UPDATETYPE='realtime');

查询SQL

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

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

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