空間索引 - 各資料庫空間索引使用報告

枕邊書發表於2017-05-07
索引資料庫

 

空間索引

索引我們都用過，它是一種特殊的儲存結構，就像圖書館裡書的分類存放策略或是現代化圖書館裡的圖書查詢系統，能幫助我們快速找到自己需要的書。 資料庫中，索引的儲存一般使用 B樹 或 B+樹 來實現，通過二分法來查詢法來快速定位到資料位置。

普通索引對於一維資料(key->data)是無往不利，可是面對空間資料(lon,lat -> data)就有些無能為力了，如果查詢(116.27636, 40.041285)附近的點：

  • 我們在 lon 或 lat 列上建立普通索引，假設是 lon 列，那麼通過 lon 列查詢到同一經度的資料後，還要在此基礎上過濾掉緯度差異過大的資料。

  • 如果在 lon,lat 上建立多列索引，查詢到同一經度、緯度相近的資料固然快，但附近的點並不只是經度相同。

如此下來，就要用到空間索引了。空間索引通過 四叉樹、R 樹等資料結構，還有 GeoHash 演算法將二維資料轉化為一維使用普通B樹索引 來實現，它們都能實現對空間範圍內的快速搜尋。

可是，今天的主題不在這裡，我們的首要目標是要解決問題，這些空間索引的實現改日專門寫文章來實現。本文來說一說現有的資料庫中對空間索引的支援情況，希望能幫助跟我一樣的 GIS 小白進行技術選型。

組內準備切換 poi 資料的儲存資料庫，花了一週時間安裝配置各種資料庫來測試空間索引的效率，測試了 Redis, Mongo, PostgreSQL, Mysql 這幾個知名的支援空間索引的資料庫，技術選型基本完畢，可是中間踩過的坑和配置經驗不能丟，詳情如下：

Redis

介紹

redis，一個功能強大、效率極高的快取資料庫（或許已經不僅僅是快取資料庫了），已經成為類似於關係儲存型資料庫在各個專案中不可或缺的元件了。首先考慮它是因為它的效率有保障，而且專案中幾乎必備，運維代價很低。Redis 的 空間索引採用 GeoHash 原理，配合集合儲存，查詢效率接近 log(N)。

Redis 3.0 以上版本支援空間索引，新專案不必考慮這些，而一般的老專案可能就需要升級 Redis 了，另外 PHP 中可能還要升級 Redis 的擴充套件，以支援 Redis 的空間索引函式。

使用

Redis 的安裝配置這裡就不再多提了，這裡簡單地介紹一下 Redis 的 GEO 系列函式。

  • GEOADD key longitude latitude member [longitude latitude member ...]

    GEOADD 將元素新增到集合中，可一次新增多個元素，其對應的 php 函式原型為： geoadd($key, $lon, $lat, $member)；

  • GEORADIUS key longitude latitude radius m|km|ft|mi [WITHCOORD] [WITHDIST] [WITHHASH] [ASC|DESC] [COUNT count]

    GEORADIUS 查詢集合內 以目標點為圓心，半徑為radius的圓內 的元素。其 php 函式原型為 georadius($key, $lon, $lat, $radius, $unit, $options); 其 $options 類似於 array('count' => $count, 'WITHDIST' ...);

結論

Redis 確實效率高，使用方便，但有一個無法克服的問題，即無法實現多條件查詢。僅僅查詢附近的點，Redis 是無懈可擊，但是如果需求是查詢附近的飯店呢？或是需求查詢附近的 '萬達' 呢？

不是不可以實現：

  • 在關係型資料庫記憶體儲每個地點的詳細資訊，Redis 內的 member 儲存每個地點在關係型資料庫中的主鍵 ID，查詢到地點的 ID 後，再去取地點的詳細資訊來過濾。

    多餘的庫訪問，會造成額外的網路開銷和 IO 開銷。

  • 在以一定規則拼接 member 的值，如 $memeber = $name.','.$category;，在查詢到地點後解析 member 後進行過濾。

    較上面方法，省了網路開銷，但不夠靈活，如果再加上'城市'的限制，那麼整個庫的資料都要被清洗。

參考： Redis 命令參考 » GEO（地理位置）

MongoDB

介紹

MongoDB 是老牌的支援空間索引的資料庫，作為一個文件型資料庫，它在儲存日誌或靜態資料時效果不錯。 它提供兩種型別的空間索引：

  • 2d 索引支援平臺普通座標的索引，適用於 2.4 版本之前；我們就不再考慮了，在大範圍上儲存和計算時，效率會有較大誤差。
  • 2dsphere 索引支援查詢在一個類地球的球面上進行幾何計算，以GeoJSON物件或者普通座標對的方式儲存資料。

2d 索引 和2dsphere 索引都是使用 GeoHash 演算法用 B+ 樹來實現。

使用

Mongo 建立空間索引的方法很簡單：db.collection.createIndex( { field : "2dsphere" } );。

查詢語句類似（下面是查詢距目標點 3000米 內的地點）：

db.poi.find( { loc :
                { $near :
                    { $geometry :
                        { type : "Point" ,
                           coordinates : [ 113.965355, 23.782865] 
                         } ,
                           $maxDistance : 3000
                      }
                  } 
                } )

Mongo 的使用需要注意如下：

  • Mongo 的 PHP 擴充套件已經更新了，舊的擴充套件已被廢棄，操作要使用 MongoDB\Driver\XXX 等類來進行，具體方法還是官方文件比較清晰。

  • Mongo 的 2dsphere 索引需要建立索引的欄位儲存的資料為 geoJSON 物件，在 PHP 中的構造形式類似：

    $document = [
        'loc' => [
            'type' => 'Point',
            'coordinates' => [$lon, $lat],
        ],
        'name' => $name
    ];

  • Mongo在查詢返回距離時需要使用 runCommand 命令，其語法類似於 db.runCommand({"geoNear":"collection", "near":[lon, lat], "num":count, query:{other condition}});

結論

mongo 的空間索引還是比較靈活的，GeoJSON 物件有點、線、多邊形、多條線段、多點、多個多邊形。支援 包含、相交、臨近的查詢，同時它也解決了 Redis 的多條件查詢問題。

但是測試發現，mongo 有以下問題：

  • 在進行大量資料時，效能會急劇下降，特別在符合條件的結果很多時，查詢時間簡直沒法看。
  • Mongo 對分詞模糊查詢的支援不太好，要進行按地點名字模糊查詢還需要想辦法。
  • Mongo 的安全性配置是個問題。

參考：Mongodb地理空間索引和查詢(Geospatial Indexes)

MongoDB » GeoJSON

PostgreSQL

介紹

postgreSQL 是一個知名的關係型資料庫，構建在其上的空間物件擴充套件模組 PostGIS 使得其成為一個真正的大型空間資料庫。它通過 R樹 或 GIST 樹索引來實現共空間索引，查詢效率極高。同時它對分詞模糊查詢支援很好，也能解決以地點名查詢的需求。

PostGIS 是一個開源程式，它為物件－關係型資料庫PostgreSQL提供了儲存空間地理資料的支援，使 PostgreSQL 成為了一個空間資料庫，能夠進行空間資料管理、數量測量與幾何拓撲分析。PostGIS 實現了 Open Geospatial Consortium 所提出的基本要素類（點、線、面、多點、多線、多面等）的 SQL 實現參考。

使用

postgreSQL 的使用，對比其他資料庫來說，較繁瑣。

  1. 要使用 postgreSQL 的空間索引，需要安裝 postgis，由於它依賴多而複雜，能使用 yum，apt-get，homebrew 等工具的優先使用；
  2. 資料庫完畢後使用 initdb 命令初始化一個資料庫；
  3. 使用非root使用者 postgres -D datadir 開啟服務；
  4. 使用 CREATE EXTENSION postgis; 安裝擴充套件；
  5. 使用 CREATE INDEX idx_name ON table USING gist(field); ；

然後就可以建表建索引導資料了。

以下是一個典型的查詢語句（查詢跟目標點 3000米 內的地點名稱和距離）：

SELECT id, name, st_astext(loc), 
    ST_Distance(loc, ST_GeographyFromText('SRID=4326;POINT(118.08688 33.64843)')) as dist
 FROM test WHERE 
    ST_DWithin(loc, ST_GeographyFromText('SRID=4326;POINT(118.08688 33.64843)'), 3000) 
order by dist ASC limit 200;

使用時還需要注意：

  • 如果需要進行中文分詞查詢的話，初始化資料庫時要新增 -E UTF8 選項來指定字符集;
  • postgreSQL 不能使用 root 使用者登陸，對於許可權的控制也比較嚴，動轍需要賦予許可權；
  • 注意將資料儲存為 geography 物件，預設使用 m 為單位。在使用 geometry 物件時，預設使用 笛卡爾度 為單位，雖然可以互相轉換，但麻煩是少不了的。
  • 建表時要指定其 SRID （空間參考識別符號， 是與特定座標系、容差和解析度關聯的唯一識別符號） 值，以經緯度儲存用 4326; 例如 loc geography(point, 4326)，另外將資料轉為 geography 點時也要注意使用 SRID:4326；
  • 使用ST_ASText(field) 和 ST_GeographyFromText('SRID=4326;POINT(lon lat)')) 進行geography 和 字串點進行轉換；
  • 主鍵索引資料型別可指定為 serial，型別於mysql的 int auto increment;
  • 使用 \timing on 和 \timing off來切換是否顯示命令執行時間；

結論

postgreSQL 對空間查詢的支援非常靈活，足以支援多種複雜的空間查詢，PostGIS 能計算不同投影座標系下的真實空間距離，且查詢效率極高，在大量資料時也不會像 mongo 一樣效能急劇下降。

同時它關係型資料庫的特性支援我們進行多條件查詢，最後它也可以使用 zhparser 擴充套件來進行中文分詞，以支援對地點名模糊查詢。

雖然它在存在著複雜索引時寫入較慢的問題，但對於儲存不常變動的地點資訊來說，是無關大礙的。

參考： PgSQL · 功能分析 · PostGIS 在 O2O應用中的優勢

PostgreSQL 全表 全欄位 模糊查詢的毫秒級高效實現

MySQL

介紹

Mysql 的重要性和強大不必多言，它的儲存引擎 MyISAM 很早就支援空間索引。而 InnoDB 則在5.7.4 labs版本中才新增對空間索引的支援。

它們都是通過 R 樹來實現空間索引。

使用

Mysql 中空間索引使用時要注意：

  • 對空間索引的欄位首先要設定為field geometry NOT NULL；
  • 使用建立空間索引 SPATIAL KEY `idx_fld` (`geom`)來建立一列空間索引；
  • SQL語句中字串與geometry的轉換函式 POINTFROMTEXT('POINT(lon lat)')；
  • 進行範圍查詢時要先構造空間區域：GEOMFROMTEXT('Polygon((lon1 lat1,lon2 lat2,lon3 lat3,lon4 lat4 ...))'

以下是一個典型的空間查詢語句(查詢距目標點3km以內的點)：

SELECT id, ST_Distance_Sphere(Point(-73.951368, 40.716743), geom) as dist, tags, ST_AsText(loc)
FROM nodes
WHERE ST_Contains( ST_MakeEnvelope(
                    Point((-73.951368+(3/111)), (40.716743+(3/111))),
                    Point((-73.951368-(3/111)), (40.716743-(3/111)))
                 ), loc )
      
ORDER BY dist LIMIT 10

結論

由於 Innodb 的功能比 MyISAM 強大太多，且事務、行鎖、B+樹索引等功能的不可替代性，這裡不再討論 MyISAM。

Mysql 的空間索引查詢效率不低。作為傳統的關係型資料庫，其多條件支援、分詞也都被很好地支援。

雖然對 InnoDB 的空間索引有信心，也略期待，可是對一個長時間存在的系統來說，資料庫版本的升級真正不是一個簡單的事。

參考：MySQL Blog - mysql對GIS空間資料的支援

總結

我以 126萬 poi 資料進行了測試，查詢範圍 3km 內的點（最多取200條）。 系統資訊： macos10.12 (x86_64)； 核心： 2 GHz Intel Core i5； 記憶體： 8 GB 1867 MHz LPDDR3；

以下是各資料庫的對比情況：

資料庫耗時區域查詢多條件支援分詞支援運維複雜度備註
redis(3.2.8) 1-10ms 不支援 不支援 不支援 低 簡單但功能單一
mongo(3.4.4) 10-50ms 支援 支援 不支援 中 結果資料量大時效能下降明顯
postgreSQL(9.6.2) 3-8ms 支援 支援 支援 中 資料寫入較慢
mysql（5.7.18 Innodb） 8-15ms 支援 支援 支援 低 版本升級太困難

可能測試操作時有些誤差，有熟練使用這些資料庫的可以評論交流一下。

資料庫沒有哪個一定好，只要適合場景即可。

如果您覺得本文對您有幫助，可以點選下面的 推薦 支援一下我。部落格一直在更新，歡迎 關注 。

相關文章