空間複合索引加速空間搜尋

postgresql , postgis , 複合索引 , btree_gist , 共享單車

随着移動網際網路的普及，空間資料已經成為大多數企業資料的标配，例如出行、快遞、等。

通常資料的查詢會帶位置距離搜尋，同時還會伴随其他屬性的過濾，其他屬性的過濾：例如時間範圍，區域id的過濾，物流公司id的過濾。

空間索引和btree索引在postgresql中屬于兩種索引(postgresql支援btree,hash,gin,gist,sp-gist,brin,rum,bloom,zoomdb等多種索引方法)。

怎麼使得查詢效率達到最優呢？

例子

資料庫中存儲了3個關鍵字段，一個表示共享單車的公司（mobike, ofo, ...），一個表示共享單車是否在使用中，還有一個字段表示共享單車目前的位置。

建構測試表，三個字段，兩個int類型，一個point類型，使用者可能需要根據point查詢近鄰資料，同時過濾掉c1,c2的某一些值。

測試表以及測試資料如下

搜尋某個點附近1000距離内，屬于某個公司的，沒有使用的共享單車。

查詢語句如下

建立空間複合索引

性能如下

對于一個這樣的postgis相關的query，優化如下

極限優化

如下，同樣的資料：

查詢性能如下

性能差的原因是rows remove by filter，因為僅僅通過空間掃描的過濾，大量的行是不滿足條件的，是以導緻了大量的無用功。

如果你使用的是geohash，而不是geometry類型，當你的地理位置并非邊界位址時，相鄰的資料的geohash的某些prefix可能是相同的，是以geohash可以使用btree索引。

再次優化，cluster，減少索引掃描的離散度。

還是前面的例子，當驅動列的過濾條件不是等于，而是範圍時，效果為什麼不好呢？

因為需要掃描整個範圍以及下級分支，而索引的塊是離散塊，是以掃描效率并不高。

而前面的例子對應的是驅動列的點掃描，是以效率很好。

對于有範圍掃描的場景，應該如何應對呢？

1、使用分區表，例如使用c3字段作為分區列，按時間進行分區。建立索引時将c3列摘除。

或者使用核心優化，讓核心支援分區索引。

分區索引，按時間進行分區，建立分區索引。

在掃描時，自動檢索對應的索引分區。達到 分區表+獨立索引 同樣的效果。

1、如果要建構複合索引，那麼為了達到最好的效果，所有的驅動列使用等值查詢是最好的，使用範圍查詢會導緻大範圍的搜尋。

2、如果需要使用複合索引進行排序，那麼要麼按所有字段排序，要麼按驅動列等值條件+suffix列排序。

3、為了減少索引掃描的離散度，建議使用cluster對資料按索引進行重排。

<a href="https://github.com/digoal/blog/blob/master/201404/20140426_01.md">《索引順序掃描引發的堆掃描io放大背後的統計學原理與解決辦法 - postgresql index scan enlarge heap page scans when index and column correlation small.》</a>