快递随着电商的崛起逐渐火爆, 特别是电商搞大促销的阶段, 快递更是达到爆仓的状态.
如果全国电商1天生成100亿比交易的话, 快递公司的IT是怎么完成这些无纸化操作的呢, 比如数据录入, 快件的状态跟踪, 用户通过网站查询快递等等.
我没在快递公司的IT部门干过, 也没和快递公司的DBA或者架构师交流过, 以下纯属个人扯淡.
最近买了点东西, 早上查了一下快递单, 甚至一天查好几次, 我估计大多数人和我一样, 没电脑的人也可能通过打电话去查询.
突发奇想, 快递的数据库怎么支撑用户的查询和快递公司内部业务系统的更新和查询呢?
我假象的快递数据库的特点.
1. 一堆的字典表, 比如快递公司的组织结构字典表. 这种查询多, 更新少.
要体现层次结构的话, 可以参考pg的异构数据查询举例
http://blog.163.com/digoal@126/blog/static/163877040201132843255911/
还有一个PG的插件类型, ltree, 也可以体现组织结构.
http://www.postgresql.org/docs/9.3/static/ltree.html
2. 应该会有类似的GIS系统, 计算最优路径.
3. 快递单的属性表, 包括重量, 起点终点, 保价否, 寄件人属性, 收件人属性, 价格等.
4. 快件跟踪表应该是最大的, 可以按时间分区, 老的快件跟踪记录基本上不会被查询到了. 只用作留档.
快件跟踪表记录快递的实时状态, 并且这些状态要串起来.
例如我查询的一个快递状态如下
快 件 跟 踪
2013/12/13 7:50:38
快件状态 >>
| 快递单号 | 日期时间 | 快件状态 |
|---|---|---|
| 3393108415 | 2013-12-12 17:37:27 | 安徽省合肥市蜀山区包河三部公司 已收件 |
| 3393108415 | 2013-12-12 19:30:59 | 安徽省合肥市蜀山区包河三部公司 已发出 |
| 3393108415 | 2013-12-12 20:09:50 | 安徽省合肥市公司 已收入 |
| 3393108415 | 2013-12-12 21:47:46 | 安徽省合肥市公司 已打包 |
| 3393108415 | 2013-12-12 22:01:08 | 合肥转运中心公司 已收入 |
| 3393108415 | 2013-12-12 22:05:09 | 合肥转运中心公司 已发出 |
| 3393108415 | 2013-12-13 05:11:55 | 杭州转运中心公司 已收入 |
| 3393108415 | 2013-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倍.
这种设计方式适合写少读多的场景, 并且写的场景不会产生锁冲突. 否则不要使用这种设计.