菜鳥末端軌跡（解密支撐每天251億個包裹的資料庫）-阿里雲RDS PostgreSQL最佳實踐

標籤

PostgreSQL , PostGIS , 多邊形 , 面 , 點 , 面點判斷 , 菜鳥

背景

菜鳥末端軌跡項目中涉及的一個關鍵需求，面面判斷。

在資料庫中存儲了一些多邊形記錄，約幾百萬到千萬條記錄，例如一個小區，在地圖上是一個多邊形。

不同的快遞公司，會有各自不同的多邊形劃分方法（每個網點負責的片區(多邊形)，每個快遞員負責的片區(多邊形)）。

用戶在寄件時，根據用戶的位置，查找對應快遞公司負責這個片區的網點、或者負責該片區的快遞員。

一、需求

1、在資料庫中存儲了一些靜態的面信息，代表小區、園區、寫字樓等等。所有的面不相交。

2、為了支持不同的業務類型，對一個地圖，可能劃分為不同的多邊形組成。

例如不同的快遞公司，會有各自不同的多邊形劃分方法（網點負責的片區(多邊形)，某個快遞員負責的片區(多邊形)）。

因此在一張地圖上，有多個圖層，每個圖層的多邊形劃分方法可能不一樣。

3、快速的根據快遞公司、客戶的位置，求包含這個點的多邊形（即得到對應快遞公司負責這個片區的網點、或者負責該片區的快遞員）。

二、架構設計

用到阿里雲的RDS PostgreSQL，以及PG提供的PostGIS插件。

我們需要用到PostGIS的函數有兩個

http://postgis.net/docs/manual-2.3/ST_Within.html

1、ST_within

ST_Within — Returns true if the geometry A is completely inside geometry B

boolean ST_Within(geometry A, geometry B);

Returns TRUE if geometry A is completely inside geometry B. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID. It is a given that if ST_Within(A,B) is true and ST_Within(B,A) is true, then the two geometries are considered spatially equal.

This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Within.

-- a circle within a circle

2、ST_Contains

ST_Contains — Returns true if and only if no points of B lie in the exterior of A, and at least one point of the interior of B lies in the interior of A.

boolean ST_Contains(geometry geomA, geometry geomB);

Returns TRUE if geometry B is completely inside geometry A. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID. ST_Contains is the inverse of ST_Within. So ST_Contains(A,B) implies ST_Within(B,A) except in the case of invalid geometries where the result is always false regardless or not defined.

This function call will automatically include a bounding box comparison that will make use of any indexes that are available on the geometries. To avoid index use, use the function _ST_Contains.

-- A circle within a circle

三、DEMO與性能

1 PG內置幾何類型 面點搜索 壓測

為了簡化測試，採樣PG內置的幾何類型進行測試，用法與PostGIS是類似的。

1、創建測試表

postgres=# create table po(id int, typid int, po polygon);

2、創建分區表或分區索引

create extension btree_gist;

3、創建空間排他約束，可選

如果要求單個typid內的po不重疊，可以創建空間排他約束

create table tbl_po(id int, typid int, po polygon)

4、寫入1000萬多邊形測試數據

insert into po select id, random()*20, polygon("(("||x1||","||y1||"),("||x2||","||y2||"),("||x3||","||y3||"))") from (select id, 180-random()*180 x1, 180-random()*180 x2, 180-random()*180 x3, 90-random()*90 y1, 90-random()*90 y2, 90-random()*90 y3 from generate_series(1,10000000) t(id)) t;

5、測試面點判斷性能

查詢包含point(1,1)的多邊形，響應時間0.57毫秒。

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from po where typid=1 and po @> polygon("((1,1),(1,1),(1,1))") limit 1;

6、壓測

vi test.sql

驚不驚喜、意不意外

TPS：29萬 ，平均響應時間：0.2毫秒

2 PostGIS空間資料庫 面點搜索 壓測

阿里雲 RDS PostgreSQL，HybridDB for PostgreSQL 已經內置了PostGIS空間資料庫插件，使用前創建插件即可。

create extension postgis;

1、建表

postgres=# create table po(id int, typid int, po geometry);

2、創建空間索引

postgres=# create extension btree_gist;

3、寫入1000萬多邊形測試數據

postgres=# insert into po

4、測試面點判斷性能

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from po where typid=1 and st_within(ST_PointFromText("POINT(1 1)"), po) limit 1;

5、壓測

vi test.sql

驚不驚喜、意不意外

TPS：19.8萬 ，平均響應時間：0.32毫秒

四、技術點

1、空間排他約束

這個約束可以用於強制記錄中的多邊形不相交。例如地圖這類嚴謹數據，絕對不可能出現兩個多邊形相交的，否則就有領土紛爭了。

PostgreSQL就是這麼嚴謹，意不意外。

2、分區表

本例中不同的快遞公司，對應不同的圖層，每個快遞公司根據網點、快遞員負責的片區(多邊形)劃分為多個多邊形。

使用LIST分區，每個分區對應一家快遞公司。

3、空間索引

GiST空間索引，支持KNN、包含、相交、上下左右等空間搜索。

效率極高。

4、空間分區索引

《分區索引的應用和實踐 - 阿里雲RDS PostgreSQL最佳實踐》

5、面面、點判斷

面面判斷或面點判斷是本例的主要需求，用戶在寄包裹時，根據用戶位置在資料庫的一千萬多邊形中找出覆蓋這個點的多邊形。

五、雲端產品

阿里雲 RDS PostgreSQL

六、類似場景、案例

《PostgreSQL 物流軌跡系統資料庫需求分析與設計 - 包裹俠實時跟蹤與召回》

七、小結

菜鳥末端軌跡項目中涉及的一個關鍵需求，面面判斷。

在資料庫中存儲了一些多邊形記錄，約幾百萬到千萬條記錄，例如一個小區，在地圖上是一個多邊形。

不同的快遞公司，會有各自不同的多邊形劃分方法（網點負責的片區(多邊形)，某個快遞員負責的片區(多邊形)）。

用戶在寄件時，根據用戶的位置，查找對應快遞公司負責這個片區的網點、或者負責該片區的快遞員。

使用阿里雲RDS PostgreSQL，用戶存放約1千萬的多邊形數據，單庫實現了每秒29萬的處理請求，單次請求平均響應時間約0.2毫秒。

驚不驚喜、意不意外。

八、參考

http://postgis.net/docs/manual-2.3/ST_Within.html

《分區索引的應用和實踐 - 阿里雲RDS PostgreSQL最佳實踐》

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！