Spatial Index vs Geohash vs 緯度経度

MyNA(日本MySQLユーザ会) 望年LT大会2025＠新宿の発表資料です。

Transcript

1. Spatial Index vs Geohash vs 緯度経度 MyNA(日本MySQLユーザ会) 望年LT大会2025＠新宿 Suzuki Shunya
2. こんにちは • Suzuki Shunya（shunyasu）です ◦ とある企業のDBA ◦ Indexが好き • X：@ssuzuki67
3. 今回の話｜いろんな手法での空間検索の性能測定 • 空間データをいじっていた際、 Spatial Indexでの空間範囲検索が直感より遅く感じた → いろんな空間検索手法で性能測定してみた！ ❖ この前書いたBlogの使い回し ◦ MySQLのSpatial Index, Geohash, 座標値による空間検索の性能評価（あるいは SRID 4326の Spatial Indexが遅い件について） ▪ https://shunyas.hatenablog.com/entry/2025/12/02/184554 ◦ LT用に圧縮してるので、詳細はこちら
4. 評価対象 • Spatial Index ◦ そのまま ◦ 空間用Indexなので、最も効率がいいはず • Geohash ◦ Geohash値+普通のIndex（知る人ぞ知るUB-treeと構造的には同じ） ◦ これも実質空間用Indexなので、Spatial Indexの次に効率がいいはず • 緯度経度 ◦ 緯度, 経度 + 普通のIndexの複合キー ◦ ただの複合キーなので、一番効率が悪いはず
5. 設定 • データセット：OpenStreetMapの日本全体のデータ（Pointレイヤ 3,445,131行）
 • ベンチマーク
 ◦ データセットからランダムに取り出した1点を中心とする矩形（ほぼ正方形）での範囲検索 
 • テーブル定義（一部抜粋）
 > show create table osm_points\G *************************** 1. row *************************** Table: osm_points Create Table: CREATE TABLE `osm_points` ( `id` bigint NOT NULL AUTO_INCREMENT, `p4326` point NOT NULL /*!80003 SRID 4326 */, `lat` double GENERATED ALWAYS AS (st_latitude(`p4326`)) STORED, `lon` double GENERATED ALWAYS AS (st_longitude(`p4326`)) STORED, `geohash` varchar(24) GENERATED ALWAYS AS (st_geohash(`lon`,`lat`,24)) STORED, `p0` point GENERATED ALWAYS AS (st_pointfromgeohash(`geohash`,0)) STORED NOT NULL /*!80003 SRID 0 */, PRIMARY KEY (`id`), SPATIAL KEY `r4326` (`p4326`), ← SRID 4326 のSpatial Index SPATIAL KEY `r0` (`p0`) ← SRID 0 のSpatial Index KEY `lon_lat` (`lon`,`lat`), ← 緯度経度のIndex KEY `geohash` (`geohash`,`lon`,`lat`) ← GeohashのIndex（ICPを効かせるためにlon, latも含めた複合キー） ) ENGINE=InnoDB AUTO_INCREMENT=3473356 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 1 row in set (0.00 sec)
6. 検索方法 • Spatial Index ◦ SELECT * FROM osm_points force index(r0) WHERE ST_WITHIN(p0, 検索範囲 ); • Geohash ◦ SELECT * FROM osm_points force index(geohash) WHERE geohash between 最小のGeohash AND 最大のGeohash AND lon between 最小の経度 AND 最大の経度 AND lat between 最小の緯度 AND 最大の緯度; • 緯度経度 ◦ SELECT * FROM osm_points force index(lon_lat) WHERE lon between 最小の経度 AND 最大の経度 AND lat between 最小の緯度 AND 最大の緯度; 検索範囲 最小の経度 最大の経度 最小の緯度 最大の緯度 最小の Geohash 最大の Geohash
7. 結果 • ほぼ全ての場合（1辺36.5kmの時以外）で緯度経度が最高 に！ • SRID 0のSpatial IndexがSRID 4326のSpatial Indexの1.46 ~ 1.72倍の性能！ • 縦軸：スループット[TPS] • 横軸：検索範囲の大きさ • 凡例 ◦ ◼：SRID 4326 ◦ ◼：SRID 0 ◦ ◼：Geohash ◦ ◼：緯度経度
8. まとめ（お気持ち） • なぜか緯度経度のIndexが一番性能が良かった ◦ Spatial Indexとは... ◦ （Spatial Indexは点以外のGeometryも入れれるから汎用性は高いと思う） • Spatial IndexはSRID 0がSRID 4326より1.5倍くらい性能が良かった ◦ SRID 4326での厳密な範囲判定が必要なければ、 検索はSRID 0のSpatial Indexで行えばいいのでは？ ◦ 日本のデータしか扱わない場合、 SRID 4326が必要な場面ってどのくらいあるのだろう 🤔 • Geohashは、検索範囲が大きくなるほど相対的な性能が良くなった ◦ もっと膨大なデータセットで、 結果セットが大きくなる場面では Geohashが一番いいのかも？
9. 来年も良いMySQLライフを！ 🐬