🚀

MySQL 8.0でspatialインデックスが利かない問題を解決した

2022/12/30に公開

MySQL

geo

spatial

tech

はじめに

先に結論から書いておくと、データ投入前にインデックスを作成することで効く様になりました。
原因は分かっていないですが、データ投入後にインデックスを作成する方法だと効いてくれませんでした。

使用したMySQLバージョンはこちらです。

mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)

やりたいこと

国土交通省の行政区域データを使って、ある地点から[n]km圏内にかかる市区町村を抽出したい。

シェープファイルをSQLに変換してインポート

shp2mysqlというライブラリを使用して、SQLファイルを生成してインポートしました。

データは下記の令和3年（N03-20210101_GML.zip）を使っています。

（最新の令和4年版を使いたかったのですが、データサイズがかなり増えていて、インデックスを使ってもやたら遅い現象があります）

インデックスを追加

インポートしたテーブル定義を見てみると、spatialインデックスが無かったので、追加してみます。
geomカラムがNULL許容になっていたので、geomカラムをNOT NULLに変更。
そしてインデックス追加。

ALTER TABLE `n03-21_210101` ADD SPATIAL INDEX(geom);

sqlを実行してみます。
しかし、EXPLAINしてみても、インデックスは効きません。ヒント句を指定してみても変わらずでした。

sqlはこちらです。

EXPLAIN
SELECT
    *
FROM `n03-21_210101` use index (geom)
WHERE ST_Intersects(geom,
        ST_SRID(
          ST_Buffer(
            ST_GeomFromText('POINT(139.74521832327363 35.65943478441101)', 4612, 'axis-order=long-lat'),
            3000 * 100000 * 180.0 / PI() / 6378137.0
        ), 4612)
      )

東京タワーを中心に3km圏内の市区町村を取得しています。
（3km圏内の円を正しく出せてるかは分からないですが、だいたい合ってそうでした）

SRIDが揃っていないとインデックスが利かない？

色々調べていると、SRIDが揃っていないとインデックスが利かないという記事が見つかりました。

ただ、SRIDを揃えてみてもインデックスが効いてくれませんでした。

試しにこの記事どおりにtestテーブルを作って再現してみると、インデックスは確かに効ききました。（記事に載っていない点として、データが1件でも入ってる必要がありました）

もしかして先にindexを追加する必要がある？

これはもしや、、と思って、先にindexを追加してからデータを追加するようにしてみました。

生成されたテーブル定義に、

CREATE TABLE `n03-21_210101` (gid serial,
`n03_001` varchar(10),
`n03_002` varchar(20),
`n03_003` varchar(20),
`n03_004` varchar(20),
`n03_007` varchar(5));
ALTER TABLE `n03-21_210101` ADD PRIMARY KEY (gid);
ALTER TABLE `n03-21_210101` ADD COLUMN geom MULTIPOLYGON SRID 4612 ;
INSERT INTO ...

↓インデックスを追加しました。

CREATE TABLE `n03-21_210101` (gid serial,
`n03_001` varchar(10),
`n03_002` varchar(20),
`n03_003` varchar(20),
`n03_004` varchar(20),
`n03_007` varchar(5));
ALTER TABLE `n03-21_210101` ADD PRIMARY KEY (gid);
ALTER TABLE `n03-21_210101` ADD COLUMN geom MULTIPOLYGON SRID 4612 NOT NULL;
ALTER TABLE `n03-21_210101` ADD SPATIAL INDEX(geom);
INSERT INTO ...

これでデータを取り込んでEXPLAINしてみると、インデックスが効く様になりました！

おわりに

結局のところなぜ利かなかったのかの原因まではわからなかったのですが、インデックスは効く様になりました。gis周りはまだ始めたばかりで、色々とわからないことが多いですが、先人たちの記事に助けられました。
この記事も誰かの役に立てば幸いです。