PostgreSQLFOR快遞公司快件跟蹤表設計思考

有些快遞公司提供一次查詢多個快遞單號.

快遞的狀態表的特點, 我假象它是更新少, 查詢多的一個特點.

並且更新不應該出現衝突, 即多個執行緒同時更新同一條記錄. 

那麼查詢多, 更新少的話, 我的建議是在跟蹤表中, 一個快遞單子一條記錄. 

這樣做的好處是, 查詢時很快, 只需要掃描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;