MySQL 8.0.20 で SPATIAL INDEX 検索が遅い不具合が Fix された（らしい）ので試してみた

MySQL 8.0 で SPATIAL INDEX を使うとき、特定の条件で検索が非常に遅くなる不具合がありました。

 - **[MySQL8.0の空間検索が遅い？](https://qiita.com/miyauchi/items/f6621cc79aff98dd46e0)（miyauchi さん）**
 - **[MySQL8.0の空間検索が遅い？の続き1](https://qiita.com/miyauchi/items/893f12679cb21c12c454)（miyauchi さん）**

MySQL 8.0.20 でこの不具合が直ったと聞いたので、実際に試してみました。

# テスト環境

 - 手元の Windows 10 PC
 - Windows 版 MySQL Server 8.0.17（修正前）と MySQL Server 8.0.20（修正後）で比較
 - 検索対象のデータは以前 POLARDB PostgreSQL 11 互換版で Ganos を試すときに使ったものと同じ
   - **[Alibaba Cloud の POLARDB を試してみる（６）PostgreSQL 11 互換版で Ganos を試す](https://qiita.com/hmatsu47/items/7af490cf526d7b19a6ec#%E3%83%87%E3%83%BC%E3%82%BF%E3%83%80%E3%82%A6%E3%83%B3%E3%83%AD%E3%83%BC%E3%83%89%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%E6%BA%96%E5%82%99)**
     - **[国土数値情報 ダウンロードサービス](http://nlftp.mlit.go.jp/ksj/index.html)** から「行政区域」で「全国」を選び、平成 31 年のデータをダウンロード

---

先ほどの miyauchi さん作**「shp2mysql」**でシェープファイルを SQL に変換してインポートします。

 - **[MySQLにシェープファイルをインポートするツール(shp2mysql)を作った](https://qiita.com/miyauchi/items/b4e810b3becf2cf07e2f)（miyauchi さん）**

```sql:DB作成
mysql> CREATE DATABASE gistest;
```

```text:シェープファイルを変換
> shp2mysql.exe -s 4612 -W CP932 N03-19_190101.shp > N03-19_190101.sql
```

※この`N03-19_190101.sql`ファイルを読み込んで`gistest`内にテーブルとデータを生成。

```sql:カラムにSRID指定・INDEX作成
※テーブル定義を確認

mysql> SHOW CREATE TABLE `n03-19_190101`\G
*************************** 1. row ***************************
       Table: n03-19_190101
Create Table: CREATE TABLE `n03-19_190101` (
  `gid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `n03_001` varchar(10) DEFAULT NULL,
  `n03_002` varchar(20) DEFAULT NULL,
  `n03_003` varchar(20) DEFAULT NULL,
  `n03_004` varchar(20) DEFAULT NULL,
  `n03_007` varchar(5) DEFAULT NULL,
  `geom` multipolygon /*!80003 SRID 4612 */ DEFAULT NULL,
  PRIMARY KEY (`gid`),
  UNIQUE KEY `gid` (`gid`)
) ENGINE=InnoDB AUTO_INCREMENT=117581 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

※カラムの SRID を指定して INDEX を作成

mysql> ALTER TABLE `n03-19_190101` MODIFY COLUMN `geom` multipolygon NOT NULL SRID 4612, ADD SPATIAL INDEX `geom_index` (`geom`);
Query OK, 117580 rows affected (2 min 13.78 sec)
Records: 117580  Duplicates: 0  Warnings: 0
```

# MySQL Server 8.0.17 でテスト

北緯 43.06 度・東経 141.34 度がどの市区町村に含まれるのかを検索します。

```sql:8.0.17でテスト
※EXPLAIN で INDEX が効いていることを確認

mysql> EXPLAIN SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612));
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | n03-19_190101 | NULL       | range | geom_index    | geom_index | 34      | NULL |    1 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

※実行

mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612));
+-----------------------------+
| city                        |
+-----------------------------+
| 北海道札幌市中央区          |
+-----------------------------+
1 row in set (29.60 sec)
```

結果は出ましたが、非常に遅いです。

# MySQL Server 8.0.20 でテスト

同じ環境を 8.0.20 にバージョンアップして試してみます。データはそのままです（バージョンアップ後のプロセスでアップグレードが掛かります）。

```sql:8.0.20でテスト
※EXPLAIN で INDEX が効いていることを確認

mysql> EXPLAIN SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612));
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | n03-19_190101 | NULL       | range | geom_index    | geom_index | 34      | NULL |    1 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

※実行

mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612));
Empty set (0.38 sec)
```

高速になったのですが、なんと結果が返ってきません！

試しに、検索対象を「点」ではなく少し広げて「面」（ポリゴン）にしてみます。

```sql:ポリゴンを検索
mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POLYGON((43.05 141.33, 43.07 141.33, 43.07 141.35, 43.05 141.35, 43.05 141.33))', 4612));
+--------------------------+
| city                     |
+--------------------------+
| 北海道札幌市北区         |
+--------------------------+
1 row in set (0.50 sec)
```

範囲を広げたので「札幌市北区」がヒットしましたが、本来なら重なっているはずの「札幌市中央区」が出てきません。

---

しばらく悩みましたが、INDEX を再作成してみました。

```sql:INDEX再作成
mysql> ALTER TABLE `n03-19_190101` DROP INDEX `geom_index`;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `n03-19_190101` ADD SPATIAL INDEX `geom_index` (`geom`);
Query OK, 0 rows affected (2 min 27.82 sec)
Records: 0  Duplicates: 0  Warnings: 0
```

なお、1 行で`DROP`と`ADD`を実行すると何も行われないので、分けて実行します。

```sql:INDEX再作成後のテスト
mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POINT(43.06 141.34)', 4612));
+-----------------------------+
| city                        |
+-----------------------------+
| 北海道札幌市中央区          |
+-----------------------------+
1 row in set (0.02 sec)

mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Intersects(`geom`, ST_GeomFromText('POLYGON((43.05 141.33, 43.07 141.33, 43.07 141.35, 43.05 141.35, 43.05 141.33))', 4612));
+-----------------------------+
| city                        |
+-----------------------------+
| 北海道札幌市北区            |
| 北海道札幌市中央区          |
+-----------------------------+
2 rows in set (0.02 sec)
```

点・面（ポリゴン）とも正しく高速に検索できるようになりました。

マニュアルには書かれていなかったのですが、旧バージョンで作成したデータをそのまま移行した場合は、INDEX の再作成が必要なようです。

---

**2020/07/21 追記：**
Oracle 山﨑さんにバグレポートをしていただいた結果、**[MySQL 8.0.20 のリリースノート](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html#mysqld-8-0-20-bug)**に注意書きが追記されました。

> **Note**
>
> For upgrades from earlier versions of MySQL, you should recreate spatial indexes in tables that have them.

---

 - **[Qiitaに投稿したMySQL 8.0関連記事](https://hmatsu47.hatenablog.com/qiita_mysql80)**
 - **[MySQL 8.0 の薄い本](https://github.com/hmatsu47/mysql80_no_usui_hon)（無料で配布中！）**