MySQL 8.0 で SPATIAL INDEX を使うとき、特定の条件で検索が非常に遅くなる不具合がありました。
- MySQL8.0の空間検索が遅い？（miyauchi さん）
- MySQL8.0の空間検索が遅い？の続き1（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 を試す
- 国土数値情報 ダウンロードサービス から「行政区域」で「全国」を選び、平成 31 年のデータをダウンロード
-
Alibaba Cloud の POLARDB を試してみる（６）PostgreSQL 11 互換版で Ganos を試す
先ほどの miyauchi さん作「shp2mysql」でシェープファイルを SQL に変換してインポートします。
- MySQLにシェープファイルをインポートするツール(shp2mysql)を作った（miyauchi さん）
DB作成
mysql> CREATE DATABASE gistest;
シェープファイルを変換
> shp2mysql.exe -s 4612 -W CP932 N03-19_190101.shp > N03-19_190101.sql
※このN03-19_190101.sqlファイルを読み込んでgistest内にテーブルとデータを生成。
カラムに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 度がどの市区町村に含まれるのかを検索します。
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 にバージョンアップして試してみます。データはそのままです（バージョンアップ後のプロセスでアップグレードが掛かります）。
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)
高速になったのですが、なんと結果が返ってきません！
試しに、検索対象を「点」ではなく少し広げて「面」（ポリゴン）にしてみます。
ポリゴンを検索
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 を再作成してみました。
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を実行すると何も行われないので、分けて実行します。
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 のリリースノートに注意書きが追記されました。
Note
For upgrades from earlier versions of MySQL, you should recreate spatial indexes in tables that have them.
- Qiitaに投稿したMySQL 8.0関連記事
- MySQL 8.0 の薄い本（無料で配布中！）