軌迹系統 需求分析與DB設計

postgresql , postgis , 快遞 , 包裹俠 , 地理位置 , 距離排序 , knn

物流行業對地理位置資訊資料的處理有非常強烈的需求，例如

1. 實時跟蹤快遞員、貨車的位置資訊。對資料庫的寫入性能要求較高。

2. 對于當日件，需要按發貨位置，實時召回附近的快遞員。

3. 實時的位置資訊非常龐大，為了資料分析的需求，需要保留資料，是以需要廉價的存儲。例如對象存儲。同時還需要和資料庫或分析型的資料庫産品實作關聯。

阿裡雲的 postgresql + hybriddb for postgresql + oss 對象存儲可以很好的滿足這個需求，詳細的方案如下。

以物流配送場景為例，介紹阿裡雲的解決方案。

快遞員：百萬級。

快遞員的軌迹定位資料間隔：5秒。

一個快遞員每天工作時間 7 ~ 19點 (12個小時)。

一個快遞員一天産生8640條記錄。

所有的快遞員，全網一天産生86.4億條記錄。

1. 繪制快遞員軌迹（實時）

2. 召回快遞員（實時）

當天件的需求。

按快遞員id哈希，128張表。

(如果不分區，單表存儲86.4億記錄，問題也不大，隻是導出到oss對象存儲的過程可能比較長，如果oss出現故障，再次導出又要很久)

另一方面的好處是便于擴容。

每天1張子表，輪詢使用，覆寫到周(便于維護, 導出到oss後直接truncate)。一共7張子表。

oss對象存儲。

阿裡雲postgresql有oss_ext插件，可以将資料寫入oss對象存儲。同時也支援從oss對象存儲讀取資料（外部表的方式），對使用者透明。

詳見

<a href="https://help.aliyun.com/document_detail/44461.html">https://help.aliyun.com/document_detail/44461.html</a>

postgresql 10.0 内置了分區表，是以以上分區，可以直接讀寫主表。

<a href="https://github.com/digoal/blog/blob/master/201612/20161215_01.md">《postgresql 10.0 preview 功能增強 - 内置分區表》</a>

9.5以及以上版本，建議使用pg_pathman插件，一樣可以達到分區表的目的。

<a href="https://github.com/digoal/blog/blob/master/201610/20161024_01.md">《postgresql 9.5+ 高效分區表實作 - pg_pathman》</a>

分區表例子

實時位置表，記錄快遞員的實時位置（最後一條記錄的位置）。

由于快遞員的位置資料會不停的彙報，是以實時位置表的資料不需要持久化，可以使用unlogged table。

注意

（假如快遞員的位置不能實時上報，那麼請使用非unlogged table。）

位置字段，建立gist空間索引。

為了實時更新快遞員的位置，可以設定一個觸發器，在快遞員上傳實時位置時，自動更新最後的位置。

（如果實時位置表cainiao_trace_realtime使用了非unlogged table，那麼考慮到(寫入+update)的rt會升高一些，建議不要使用觸發器來更新位置。建議程式将 插入和update 作為異步調用進行處理。例如在收到快遞員上報的批量位置軌迹後，拆分為batch insert以及update 一次。）

（batch insert: insert into cainiao values (),(),(),....; update 最終狀态: update cainiao_trace_realtime set xx=xx where uid=xx;）(好處：1. 插入和更新異步, 2. 插入批量執行, 3. 整體rt更低)

對基表添加觸發器

觸發器示例如下

說明

1. 本文假設應用程式會根據 快遞員uid ，時間字段 拼接出基表的表名。

否則就需要使用postgresql的分區表功能（分區表的性能比直接操作基表差一些）。

2. 本文使用point代替經緯度，因為point比較好造資料，友善測試。

實際上point和經緯度都是地理位置類型，可以實作的場景類似。性能名額也可以用于參考。

模拟快遞員實時的上傳軌迹，實時的更新快遞員的最新位置。

pgbench的測試腳本如下

開始測試，持續300秒。

每秒寫入17.4萬，單次請求延遲0.18毫秒。

比如當日件達到一定數量、或者到達一定時間點時，需要召回附近的快遞員取件。

或者當使用者寄當日件時，需要召回附近的快遞員取件。

壓測用例

随機選擇一個點，召回半徑為20000範圍内，距離最近的100名快遞員。

sql樣例

每秒處理召回請求 6萬，單次請求延遲0.53毫秒。

備注，如果隻召回一名快遞員，可以達到28萬 tps.

同時壓測快遞員軌迹插入、随機召回快遞員。

插入tps: 12.5萬，響應時間0.25毫秒

查詢tps: 2.17萬，響應時間1.47毫秒

如果要盡量的降低rt，快遞員實時位置表可以與軌迹明細表剝離，由應用程式來更新快遞員的實時位置。

至于這個實時位置表，你要把它放在明細表的資料庫，還是另外一個資料庫？

我的建議是放在另外一個資料庫，因為這種表的應用非常的獨立（更新，查詢），都是小事務。

而明細軌迹，可能涉及到比較大的查詢，以插入，範圍分析，資料合并，日軌迹查詢為主。

将明細和實時軌迹獨立開來，也是有原因的。

剝離後，明細位置你可以繼續使用unlogged table，也可以使用普通表。

下面測試一下剝離後的性能。

pgbench腳本，更新快遞員位置，查詢某個随機點的最近100個快遞員。

前面對實時軌迹資料使用一周的分表，目的就是有時間可以将其寫入到oss，友善維護。

每天可以将6天前的資料，寫入oss對象存儲。

單個快遞員，一天産生的軌迹是8640條。

postgresql支援json、hstore(kv)、數組、複合數組 類型。每天将單個快遞員的軌迹聚合為一條記錄，可以大幅度提升按快遞員查詢軌迹的速度。

同樣的場景可以參考：

<a href="https://github.com/digoal/blog/blob/master/201212/20121217_01.md">《performance tuning about multi-rows query aggregated to single-row query》</a>

聚合例子

查詢某個快遞員1天的軌迹，性能提升對比

聚合前(b-tree索引)，耗時8毫秒

聚合後，耗時0.033毫秒

1. 本文以物流軌迹系統為背景，對兩個常見需求進行資料庫的設計以及模型的壓測：實時跟蹤快遞員軌迹，實時召回附近的快遞員。

2. postgresql 結合 oss，實作了資料的冷熱分離，曆史軌迹寫入oss儲存，再通過oss可以共享給hybriddb for postgresql，進行實時的資料挖掘分析。

3. 單機滿足了每秒18萬的軌迹寫入，按最近距離召回快遞員(100名快遞員)可以達到6萬/s的速度，按最近距離召回快遞員(1名快遞員)可以達到28萬/s的速度。

4. 使用postgresql的繼承特性，可以更好的管理分區表，例如要查詢禮拜一的所有分區，查詢這些分區的主表。 如果要查某個模值的所有時間段資料，查詢對應的主表即可。

<a href="https://github.com/digoal/blog/blob/master/201308/20130806_01.md">《postgis long lat geometry distance search tuning using gist knn function》</a>

<a href="https://github.com/digoal/blog/blob/master/201601/20160119_01.md">《postgresql 百億地理位置資料 近鄰查詢性能》</a>

<a href="https://github.com/digoal/blog/blob/master/201701/20170101_02.md">《apsaradb的左右互搏(pgsql+hybriddb+oss) - 解決oltp+olap混合需求》</a>