MySQL8系でGeometry型を使うときはSRIDを指定しないとインデックスが有効にならない話

はじめに

あけましておめでとうございます。
年内に公開するつもりがいつの間にやら年明けておりました。すみません。

MySQLで空間演算を行ったところ大変に遅いので、これは使い方が悪いのではないかと調べた結果、Geometry型にSRIDを指定していないと空間インデックスが無効になってしまうことが判りましたというお話です。

OGR2OGRでシェープファイルの取り込みを行うと、SRIDが定義されていないテーブルが生成されますので注意が必要ですという話でもある。

検証

検証方法

空間演算を使用してポイントに重なるヘックスタイルのIDを付与する時間を計測。
やっていることはこちらと同じです。

by @Yfuruchin CCBY 4.0

こんな感じのSQLを投げます。
（結果が判りやすいように付与されたヘックスタイルのIDをSUMします。）

SELECT sum(gid) AS sum_gid
FROM (
 SELECT point.id AS pid, grid.id AS gid, point.geometry geom
 FROM point 
 INNER JOIN grid 
 ON ST_WithIn(point.geometry, grid.geometry)
) within;

以下の4パターンで比較

• NOT NULL以外の制約なし（OGR2OGRで取り込んだ状態）
• ジオメトリタイプを設定
• SRIDを設定
• ジオメトリタイプとSRIDを設定

テストデータ

縦横120kmのヘックスタイルポリゴンを1000個。

ヘックスタイル内且つ最小DISTANCEが1kmのランダムポイントを10万個。

Maptiles by MIERUNE, under CC BY. Data by OpenStreetMap contributors, under ODbL.

MySQLテーブル情報

OGR2OGRでデータを取り込んだ素の状態が以下

mysql> DESCRIBE point;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| OGR_FID | int(11)       | NO   | UNI | NULL    | auto_increment |
| SHAPE   | geometry      | NO   | MUL | NULL    |                |
| id      | decimal(10,0) | NO   | PRI | NULL    |                |
+---------+---------------+------+-----+---------+----------------+

mysql> DESCRIBE grid;
+---------+---------------+------+-----+---------+----------------+
| Field   | Type          | Null | Key | Default | Extra          |
+---------+---------------+------+-----+---------+----------------+
| OGR_FID | int(11)       | NO   | UNI | NULL    | auto_increment |
| SHAPE   | geometry      | NO   | MUL | NULL    |                |
| id      | decimal(10,0) | NO   | PRI | NULL    |                |
+---------+---------------+------+-----+---------+----------------+

CREATE文はこんな感じ。
pointテーブルだけ載せておきますがgridも同じ構成です。

CREATE TABLE `point` (
  `OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
  `SHAPE` geometry NOT NULL,
  `id` decimal(10,0) DEFAULT NULL,
  UNIQUE KEY `OGR_FID` (`OGR_FID`),
  SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

SHAPEカラムにNOT NULL以外の制約が無いことが確認できます。
それではこれをベースに検証を行っていきましょう。

NOT NULL以外の制約なしの場合

まずはテーブルを作ってデータを移し替えます。

CREATE TABLE point_1
(id INTEGER,
geometry GEOMETRY
);
ALTER TABLE point_1 ADD PRIMARY KEY(id);
ALTER TABLE point_1 MODIFY COLUMN geometry GEOMETRY NOT NULL;
ALTER TABLE point_1 ADD SPATIAL INDEX(geometry);

CREATE TABLE grid_1
(id INTEGER,
geometry GEOMETRY
);
ALTER TABLE grid_1 ADD PRIMARY KEY(id);
ALTER TABLE grid_1 MODIFY COLUMN geometry GEOMETRY NOT NULL;
ALTER TABLE grid_1 ADD SPATIAL INDEX(geometry);

INSERT INTO point_1 SELECT id, SHAPE FROM point;
INSERT INTO grid_1 SELECT id, SHAPE FROM grid;

それでは計測をしてみます。

mysql> SELECT sum(gid) AS sum_gid
    -> FROM (
    ->  SELECT point.id AS pid, grid.id AS gid, point.geometry geom
    ->  FROM point_1 point 
    ->  INNER JOIN grid_1 grid 
    ->  ON ST_WithIn(point.geometry, grid.geometry)
    -> ) within;
+----------+
| sum_gid  |
+----------+
| 50035423 |
+----------+
1 row in set (7 min 48.16 sec)

ジオメトリタイプを設定した場合

次はgridにはポリゴン、pointにはポイントのタイプ指定をしたテーブルを用意します。

CREATE TABLE point_2
(id INTEGER,
geometry POINT
);
ALTER TABLE point_2 ADD PRIMARY KEY(id);
ALTER TABLE point_2 MODIFY COLUMN geometry POINT NOT NULL;
ALTER TABLE point_2 ADD SPATIAL INDEX(geometry);

CREATE TABLE grid_2
(id INTEGER,
geometry POLYGON
);
ALTER TABLE grid_2 ADD PRIMARY KEY(id);
ALTER TABLE grid_2 MODIFY COLUMN geometry POLYGON NOT NULL;
ALTER TABLE grid_2 ADD SPATIAL INDEX(geometry);

INSERT INTO point_2 SELECT id, SHAPE FROM point;
INSERT INTO grid_2 SELECT id, SHAPE FROM grid;

計測します。

mysql> SELECT sum(gid) AS sum_gid
    -> FROM (
    ->  SELECT point.id AS pid, grid.id AS gid, point.geometry geom
    ->  FROM point_2 point 
    ->  INNER JOIN grid_2 grid 
    ->  ON ST_WithIn(point.geometry, grid.geometry)
    -> ) within;
+----------+
| sum_gid  |
+----------+
| 50035423 |
+----------+
1 row in set (7 min 40.01 sec)

SRIDを設定した場合

次はジオメトリカラムにSRIDを指定したテーブルを用意します。

CREATE TABLE point_3
(id INTEGER,
geometry GEOMETRY SRID 3857
);
ALTER TABLE point_3 ADD PRIMARY KEY(id);
ALTER TABLE point_3 MODIFY COLUMN geometry GEOMETRY SRID 3857 NOT NULL;
ALTER TABLE point_3 ADD SPATIAL INDEX(geometry);

CREATE TABLE grid_3
(id INTEGER,
geometry GEOMETRY SRID 3857
);
ALTER TABLE grid_3 ADD PRIMARY KEY(id);
ALTER TABLE grid_3 MODIFY COLUMN geometry GEOMETRY SRID 3857 NOT NULL;
ALTER TABLE grid_3 ADD SPATIAL INDEX(geometry);

INSERT INTO point_3 SELECT id, SHAPE FROM point;
INSERT INTO grid_3 SELECT id, SHAPE FROM grid;

計測

mysql> SELECT sum(gid) AS sum_gid
    -> FROM (
    ->  SELECT point.id AS pid, grid.id AS gid, point.geometry geom
    ->  FROM point_3 point 
    ->  INNER JOIN grid_3 grid 
    ->  ON ST_WithIn(point.geometry, grid.geometry)
    -> ) within;
+----------+
| sum_gid  |
+----------+
| 50035423 |
+----------+
1 row in set (10.79 sec)

ジオメトリタイプとSRIDを設定

最後にジオメトリタイプとSRIDを設定したテーブルを用意しいます。

CREATE TABLE point_4
(id INTEGER,
geometry POINT SRID 3857
);
ALTER TABLE point_4 ADD PRIMARY KEY(id);
ALTER TABLE point_4 MODIFY COLUMN geometry POINT SRID 3857 NOT NULL;
ALTER TABLE point_4 ADD SPATIAL INDEX(geometry);

CREATE TABLE grid_4
(id INTEGER,
geometry POLYGON SRID 3857
);
ALTER TABLE grid_4 ADD PRIMARY KEY(id);
ALTER TABLE grid_4 MODIFY COLUMN geometry POLYGON SRID 3857 NOT NULL;
ALTER TABLE grid_4 ADD SPATIAL INDEX(geometry);

INSERT INTO point_4 SELECT id, SHAPE FROM point;
INSERT INTO grid_4 SELECT id, SHAPE FROM grid;

計測

mysql> SELECT sum(gid) AS sum_gid
    -> FROM (
    ->  SELECT point.id AS pid, grid.id AS gid, point.geometry geom
    ->  FROM point_4 point 
    ->  INNER JOIN grid_4 grid 
    ->  ON ST_WithIn(point.geometry, grid.geometry)
    -> ) within;
+----------+
| sum_gid  |
+----------+
| 50035423 |
+----------+
1 row in set (10.76 sec)

まとめ

実行計画は確認してませんけどSRIDを設定しないと空間インデックスが効いてないっぽいですね。
これでは使い物になりませんのでSRIDの指定は必須だと言えるでしょう。

ジオメトリタイプの指定では速度的な影響は無いようです。
但し、空間関数によっては引数のジオメトリタイプが指定されているものがありますので混在させない方が要らぬトラブルを回避できるでしょう。

制約によるデメリットよりもメリットの方が多いと思いますのでどちらも設定しておくのが良いのではないでしょうか。

本記事のライセンス

この記事は クリエイティブ・コモンズ 表示 4.0 国際 ライセンスの下に提供されています。

追記

@tohka383さんにMySQLのリファレンスを教えてもらいました。
確かにSRIDが指定されていないとオプティマイザはインデックスを無視するんだからねって書いてある。
https://dev.mysql.com/doc/refman/8.0/en/spatial-index-optimization.html