PostgreSQL FOR 快递公司快件跟踪表设计思考

最新推荐文章于 2020-07-13 19:23:24 发布
最新推荐文章于 2020-07-13 19:23:24 发布
阅读量233 收藏 1
点赞数
文章标签： 数据库
原文链接：https://yq.aliyun.com/articles/14597
版权
快递随着电商的崛起逐渐火爆, 特别是电商搞大促销的阶段, 快递更是达到爆仓的状态.
如果全国电商1天生成100亿比交易的话, 快递公司的IT是怎么完成这些无纸化操作的呢, 比如数据录入, 快件的状态跟踪, 用户通过网站查询快递等等.
我没在快递公司的IT部门干过, 也没和快递公司的DBA或者架构师交流过, 以下纯属个人扯淡.
最近买了点东西, 早上查了一下快递单, 甚至一天查好几次, 我估计大多数人和我一样, 没电脑的人也可能通过打电话去查询.
突发奇想, 快递的数据库怎么支撑用户的查询和快递公司内部业务系统的更新和查询呢?
我假象的快递数据库的特点.
1. 一堆的字典表, 比如快递公司的组织结构字典表. 这种查询多, 更新少. 
要体现层次结构的话, 可以参考pg的异构数据查询举例
还有一个PG的插件类型, ltree, 也可以体现组织结构.
2. 应该会有类似的GIS系统, 计算最优路径.
3. 快递单的属性表, 包括重量, 起点终点, 保价否, 寄件人属性, 收件人属性, 价格等.
4. 快件跟踪表应该是最大的, 可以按时间分区, 老的快件跟踪记录基本上不会被查询到了. 只用作留档.
快件跟踪表记录快递的实时状态, 并且这些状态要串起来.
例如我查询的一个快递状态如下

快 件 跟 踪
2013/12/13 7:50:38

快件状态 >>

快递单号日期时间快件状态
33931084152013-12-12 17:37:27安徽省合肥市蜀山区包河三部公司 已收件
33931084152013-12-12 19:30:59安徽省合肥市蜀山区包河三部公司 已发出
33931084152013-12-12 20:09:50安徽省合肥市公司 已收入
33931084152013-12-12 21:47:46安徽省合肥市公司 已打包
33931084152013-12-12 22:01:08合肥转运中心公司 已收入
33931084152013-12-12 22:05:09合肥转运中心公司 已发出
33931084152013-12-13 05:11:55杭州转运中心公司 已收入
33931084152013-12-13 05:43:59杭州转运中心公司 已发出

有些快递公司提供一次查询多个快递单号.
快递的状态表的特点, 我假象它是更新少, 查询多的一个特点.
并且更新不应该出现冲突, 即多个线程同时更新同一条记录. 
那么查询多, 更新少的话, 我的建议是在跟踪表中, 一个快递单子一条记录. 
这样做的好处是, 查询时很快, 只需要扫描2个块左右 , 1个索引块, 1个HEAP块.
但是如果一个快递单子多条记录, 即每个状态插入一条, 这样的带来的是大量的数据块的扫描, 基本上每条记录都会分布在不同的数据块上. 假设平均一个快递单的跟踪记录是15条, 数据块的扫描要多14个.
那么1条记录怎么存储呢?
快递单+复合类型数组(节点时间+节点ID+状态)+rec插入时间+rec更新时间
当然, 这里不用数组也行, 只要程序能根据存储的信息分隔每个节点状态即可, 这样做的好处是结构更加松散, 适合非结构话数据, 比方说在快递结束的地方会出现派件人, 派件人的电话等, 结构和前面不一样.
对应我这个例子是: 
3393108415, [(2013-12-12 17:37:27,安徽合肥市蜀山区包河三部公司字典表里的ID,收件状态ID), (下一个状态的复合类型...), ()], 记录插入时间, 记录的更新时间
新的状态录入时, 用array_append往数组后面追加数据即可.

我们举一个实际的例子(本例省去字典表, 只为体现单行和多行的查询性能) : 
为了使生成速度快点, 我们使用NOLOGGING表, 同时关闭 autovacuum. 
digoal=# create unlogged table kuaidi_log1(id int8, crt_time timestamp, nodeid text, stat text);
CREATE TABLE
digoal=# create unlogged table kuaidi_log2(id int8, comp text);
CREATE TABLE

假设每天有500W个订单, 平均每个订单20条数据的话, 需要1亿条测试数据.
 
pg93@db-172-16-3-150-> vi test.sql
\setrandom id 1 5000000
insert into kuaidi_log1 values (:id, now(), '浙江省圆通快递杭州分公司', '已收件');

开启16个连接使用pgbench进行插入.
 
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -t 6250000
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
number of transactions per client: 6250000
number of transactions actually processed: 100000000/100000000
tps = 79215.655001 (including connections establishing)
tps = 79216.552965 (excluding connections establishing)
statement latencies in milliseconds:
        0.001938        \setrandom id 1 5000000
        0.194045        insert into kuaidi_log1 values (:id, now(), '浙江省圆通快递杭州分公司', '已收件');
刚好1亿记录.

插入完后创建快递单的索引.
 
digoal=# create index idx_kuaidi_log1_1 on kuaidi_log1 (id);

kuaidi_log1表和索引的大小
 
digoal=# \dt+ kuaidi_log1
                         List of relations
  Schema  |    Name     | Type  |  Owner   |  Size   | Description 
----------+-------------+-------+----------+---------+-------------
 postgres | kuaidi_log1 | table | postgres | 8880 MB | 
(1 row)

digoal=# \di+ idx_kuaidi_log1_1 
                                   List of relations
  Schema  |       Name        | Type  |  Owner   |    Table    |  Size   | Description 
----------+-------------------+-------+----------+-------------+---------+-------------
 postgres | idx_kuaidi_log1_1 | index | postgres | kuaidi_log1 | 2142 MB | 
(1 row)

把这些记录合并到kuaidi_log2表, 每个快递单1条.
 
insert into kuaidi_log2 select id, string_agg(crt_time::text||','||nodeid||','||stat, ';' order by crt_time) from kuaidi_log1 group by id;
INSERT 0 5000000

创建 kuaidi_log2表的 快递单号索引.
 
digoal=# create index idx_kuaidi_log2_1 on kuaidi_log2 (id);

kuaidi_log2表和索引大小, 索引变小了20倍, 因为只有500万条记录.
 
digoal=# \dt+ kuaidi_log2
                         List of relations
  Schema  |    Name     | Type  |  Owner   |  Size   | Description 
----------+-------------+-------+----------+---------+-------------
 postgres | kuaidi_log2 | table | postgres | 7172 MB | 
(1 row)

digoal=# \di+ idx_kuaidi_log2_1 
                                  List of relations
  Schema  |       Name        | Type  |  Owner   |    Table    |  Size  | Description 
----------+-------------------+-------+----------+-------------+--------+-------------
 postgres | idx_kuaidi_log2_1 | index | postgres | kuaidi_log2 | 107 MB | 
(1 row)

数据对比 : 
 
digoal=# select * from kuaidi_log1 where id=1;
 id |          crt_time          |          nodeid          |  stat  
----+----------------------------+--------------------------+--------
  1 | 2013-12-13 08:40:21.116411 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:41:22.298613 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:42:38.141089 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:43:10.396533 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:43:14.961726 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:44:22.036227 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:46:24.767686 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:46:41.418713 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:46:48.03819  | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:47:32.8119   | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:47:48.996097 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:50:54.001399 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:52:00.678307 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:52:14.56005  | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:56:45.806082 | 浙江省圆通快递杭州分公司 | 已收件
(15 rows)

digoal=# select * from kuaidi_log2 where id=1;
 id |                                                                                                                               
                                                                                                                                    
                                                                                                                                    
                                                comp                                                                                
                                                                                                                                    
                                                                                                                                    
                                                                                               
----+-------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
  1 | 2013-12-13 08:40:21.116411,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:41:22.298613,浙江省圆通快递杭州分公司,已收件;2013-12-
13 08:42:38.141089,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:43:10.396533,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:43:14.96
1726,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:44:22.036227,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:46:24.767686,浙江省圆
快递杭州分公司,已收件;2013-12-13 08:46:41.418713,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:46:48.03819,浙江省圆通快递杭州分公司,
已收件;2013-12-13 08:47:32.8119,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:47:48.996097,浙江省圆通快递杭州分公司,已收件;2013-12-1
3 08:50:54.001399,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:52:00.678307,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:52:14.560
05,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:56:45.806082,浙江省圆通快递杭州分公司,已收件
(1 row)


测试 kuaidi_log1的查询性能
 
vi test.sql
\setrandom id 1 5000000
select * from kuaidi_log1 where id=:id order by crt_time;
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 30 s
number of transactions actually processed: 349163
tps = 11635.992258 (including connections establishing)
tps = 11641.693582 (excluding connections establishing)
statement latencies in milliseconds:
        0.003351        \setrandom id 1 5000000
        1.368531        select * from kuaidi_log1 where id=:id order by crt_time;


测试 kuaidi_log2的查询性能, 流水已按时间排序, 所以不需要再次排序.
 
vi test.sql
\setrandom id 1 5000000
select * from kuaidi_log2 where id=:id;
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 30 s
number of transactions actually processed: 1880690
tps = 62681.334228 (including connections establishing)
tps = 62711.904831 (excluding connections establishing)
statement latencies in milliseconds:
        0.002509        \setrandom id 1 5000000
        0.251047        select * from kuaidi_log2 where id=:id;

查询性能相差5倍.
这种设计方式适合写少读多的场景, 并且写的场景不会产生锁冲突. 否则不要使用这种设计.
weixin_34248705
关注
【项目实战】电商项目中的物流模块设计（数据库表设计、业务功能讲解、配送订单功能开发与讲解）
本本本添哥
05-18 383
物流数据库设计需覆盖物流业务的各个方面，确保数据的一致性、完整性和高效访问。 这些表之间通过外键关联，形成完整的物流数据库结构。
顺丰快递数据库设计
12-01
顺丰快递数据库设计
快递查询的设计实现
qq_36663951的博客
12-29 3679
背景：不久前，设计实现了京东api的功能，发现如果换了其它快递再重新设计，岂不是会浪费太多的时间，所以选个第三方提供的快递API是最为合理的,下面给出快递鸟和快递100的设计实现。     一.快递鸟     I.快递鸟api实现物流即时查询     1.首先要有一个快递鸟账号，根据对方的要求，完善用户申请。     2.根据自己的需求，开通服务          3.进入“我的API
物流表结构
布里渊区
10-17 1824
–20、产品单据1 CREATE TABLE MRTCPDJ0 ( DJBM00 NUMBER(10) CONSTRAINT PK_MRTCPDJ0_DJBM00 PRIMARY KEY USING INDEX TABLESPACE INDX, DJDH00 VARCHAR2(30) , –单号 DJSJ00 DATE
PostgreSQL分区表实践与思考.pptx
10-14
PostgreSQL 分区表实践与思考 PostgreSQL 分区表是指将一个大型物理表分解成多个小型物理表，以便于管理和优化数据库性能。分区表的出现是为了解决大型表带来的性能问题，例如历史数据归档、空间问题和查询性能...
PostgreSQL for Data Architects epub
09-29
PostgreSQL for Data Architects 英文epub 本资源转载自网络，如有侵权，请联系上传者或csdn删除 本资源转载自网络，如有侵权，请联系上传者或csdn删除
PostgreSQL表分区和子表及删除所有的数据库表.zip
02-19
PostgreSQL表分区和子表及删除所有的数据库表 最近需求要求统计DNS近7天每天的解析情况。数据量相对大，所以我这边对表进行分区。 对每天的数据进行分区存储。主表只存储近7天的数据，7天之前的数据删掉。所以我...
PostgreSQL教程(三)：表的继承和分区表详解
12-16
 这个概念对于很多已经熟悉其他数据库编程的开发人员而言会多少有些陌生，然而它的实现方式和设计原理却是简单易懂，现在就让我们从一个简单的例子开始吧。  1. 第一个继承表：  代码如下:  CREATE TABLE cities...
Navicat For Postgresql 10 绿色
04-20
《Navicat for PostgreSQL 10 绿色版：高效管理PostgreSQL数据库的得力工具》 Navicat是一款强大的数据库管理和开发工具，其PostgreSQL版本专为PostgreSQL数据库服务器设计，提供了直观且用户友好的界面，使得...
完整项目开发文档--物流配送最优路径规划模拟系统（需求、设计、实现、测试）
10-16
完整项目开发文档--物流配送最优路径规划模拟系统（需求、设计、实现、测试）
快递100对外开放的部份公司的列表sql数据
08-31
这是我辛辛苦苦敲进数据库的一张关于快递物流信息查询的数据表， 快递100的api接口参数
java面试宝典 之 物流配送路径优化问题分析与算法解读
张晨光老师的播客
07-21 8074
物流配送路径优化问题分析与算法解读（一）       去年五一跳蚤以后，一直在一家公司参与物流配送软件开发的相关工作，负责的工作内容包括物流配送路径优化这一块。关于物流配送这一专业领域，自己以前也是门外汉，对这一领域也没有接触过，更谈不上理解。所以，一直在学习，一直在探索。在这个过程中因为工作需要学习了很多大牛的博客（太多了，记不住，所以大家别指望了，还是踏实看我的帖子吧），也研究了在开发过程中客
柔性自动化在物流的应用及探索
cpongo011702
05-05 170
采访嘉宾简介朱礼君（元享），菜鸟网络，资深算法专家。于美国马里兰大学获得物理学博士学位。先后在Goldman Sachs、Amazon和Facebook从事数学建模和算法方面的研究工作。2014年回国后加入阿里巴巴，先在搜索事业部从事推荐算法的应用研究。于2015年加入菜鸟，现在的主要研究方向是人工智能和运筹优化算法在物流中的应用。InfoQ：智慧物流和智能物流有区别么？怎么理解智慧物流？朱礼君：...
多点最优路径规划 - (商旅问题,拼车,餐饮配送,包裹配送,包裹取件,回程单)
weixin_34290000的博客
04-10 5357
标签 PostgreSQL , PostGIS , pgrouting , 商旅问题 , 拼车 , 餐饮配送 , 包裹配送 , 包裹取件 , 回程单 背景 小长假，带着一家人出去旅行，计划好了去几个地方，如何设计旅行线路是最优的？(里面还涉及到路费，路途时间等因素)。 又比如 拼车，餐饮配送，包裹取件、配送，都包含最佳路径计算的共性在里面。 Pos...
PostgreSQL 物流轨迹系统数据库需求分析与设计 - 包裹侠实时跟踪与召回
秋̶᭄ꦿ攻城狮࿆ྂ
07-13 1837
PostgreSQL 物流轨迹系统数据库需求分析与设计 - 包裹侠实时跟踪与召回 作者 digoal 日期 2017-04-10 标签 PostgreSQL , PostGIS , 快递 , 包裹侠 , 地理位置 , 距离排序 , KNN 背景 物流行业对地理位置信息数据的处理有非常强烈的需求，例如 1. 实时跟踪快递员、货车的位置信息。对数据库的写入性能要求较高。 2. 对于当日件，需要按发货位置，实时召回附近的快递员。 3. 实时的位置信息非常庞大，为了数据分析的需求，需要保留数据，所以需要廉价的存储
GeoServer+PostgreSQL+PostGIS+pgRouting实现最短路径查询
weixin_34372728的博客
10-19 480
一、软件安装 GeoServer下载地址： http://geoserver.org/download/ PostgreSQL下载地址： https://www.postgresql.org/download/ paAdmin3下载地址： https://www.pgadmin.org/download/pgadmin-3-windows/ PostGIS下载地址： http://...
关于订单追踪的设计思路思考
qq_37375667的博客
04-16 923
这个功能就是做类似展示一个订单到哪的功能，如：京东快递。 现将自己的思路整理如下： 1.标记法 可以分段标记：比如是否有分配中和运输中数据的标识，这样在后台处理好数据之后，前台就可以直接展示和判断是否要展示。 2.封装到实体类 因为我需要的数据并不是在一张表中，也不是通过一个sql就可以查询出来的，所以我们需要将数据分次查询出来，然后封装到一个统一的实体中或者map中。 -------------...
德歌：PostgreSQL独孤九式搞定物联网
weixin_34261415的博客
05-24 249
【直播预告】5月27日，阿里云高级技术专家德歌将在云栖社区直播《云数据库RDS for PostgreSQL最佳实践》：https://yq.aliyun.com/webinar/join/15，为你分享阿里云云数据库PostgreSQL的最佳技术实战，包括PostgreSQL使用技巧、上云实战、数据迁移与同步、插件使用等内容，纯干货。 物...
PostgreSQL教程：深入解析表继承与分区表
总结，PostgreSQL的表继承和分区表功能为数据库设计提供了强大的工具。通过继承，可以构建灵活的数据层次结构，而分区表则有助于优化大数据环境下的查询性能。理解并合理运用这些特性，对于构建高效、可扩展的数据库...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值