本章节以简化的业务场景来介绍物流快递的最佳实践。

  • 本例中简化的业务场景：仅包括邮件状态表用于实时监控和更新邮件包裹状态，包裹从订单、收单、装车、投递等分为不同的状态。
  • 业务要求：每个机构实时统计当前不同状态的邮件数量。
  • 业务性能要求：
    • 实时统计新增数据，且要求一秒以内返回结果（即RT<1s）。
    • 查询并发量：QPS>300 。

表的逻辑设计—邮件状态表（t_fact_mail_status）

要求：

  • 单日数据量约一亿记录。
  • 有15万机构或快递员，每个机构的邮件量为100到3000不等。
  • 存储30天的数据。

为满足高QPS，从设计上采用大宽表、冗余字段，并且避免表关联。

字段名 字段类型 说明
mail_id varchar 邮件订单唯一码
scan_timestamp timestamp 收件时间
biz_date bigint 日期（二级分区）
dlv_person_name varchar 投递员名称
rg_code varchar 机构编码
rg_name varchar 机构名称
receiver_name varchar 收件人名
receiver_phone varchar 收件人电话
receiver_addr 收件人地址 varchar 收件人地址
product_no 产品编码 varchar 产品编码
mag_no varchar 包裹编号
op_1_timestamp bigint 状态1操作时间
op_2_timestamp bigint 状态2操作时间
op_3_timestamp bigint 状态3操作时间
op_4_timestamp bigint 状态4操作时间
op_5_timestamp bigint 状态5操作时间

表的物理设计

创建邮件状态表 t_fact_mail_status ，需要规划表的一级分区、二级分区。业务查询主要按机构进行查询，QPS要求高。综上所述， 我们选择按rg_code进行一级分区。

CREATE TABLE t_fact_mail_status (
mail_id varchar COMMENT '',
scan_timestamp timestamp COMMENT '',
biz_date bigint COMMENT '',
rg_code varchar COMMENT '',
rg_name varchar COMMENT '',
dlv_person_name varchar COMMENT '',
receiver_name varchar COMMENT '',
receiver_phone varchar COMMENT '',
receiver_addr varchar COMMENT '',
product_no varchar COMMENT '',
mag_no varchar COMMENT '',
op_1_timestamp bigint COMMENT '',
op_2_timestamp bigint COMMENT '',
op_3_timestamp bigint COMMENT '',
op_4_timestamp bigint COMMENT '',
op_5_timestamp bigint COMMENT '',
PRIMARY KEY (mail_id,rg_code,biz_date)
)
PARTITION BY HASH KEY (rg_code) PARTITION NUM 128
SUBPARTITION BY LIST KEY (biz_date)
SUBPARTITION OPTIONS (available_partition_num = 30)
TABLEGROUP ads_demo
OPTIONS (UPDATETYPE='realtime')
COMMENT '';

查询SQL

要求：

  • 查询当天某机构号
  • 不同状态的邮件数据量
  • 核查需要操作的邮件量
  • RT<1s 并且高QPS

综上所述，须利用分区裁剪，使单个查询在不同的节点上运行。SQL语句示例如下：

select sum(case when t.op_1_timestamp >= 20171128000000 and t.
op_1_timestamp <= 20171128235900
and(( t.op_2_timestamp is null or t.op_2_timestamp >
20171128235900 or t.op_2_timestamp < 20171128000000)
and( t.op_3_timestamp is null or t.op_3_timestamp >
20171128235900 or t.op_3_timestamp < 20171128000000)
and( t.op_4_timestamp is null or t.op_4_timestamp >
20171128235900 or t.op_4_timestamp < 20171128000000))
then 1 else 0 end ) as cn
from t_fact_mail_status t
where t.rg_code = '21111101' and t.biz_date = 20171128;