Slide 1
Slide 1 text
MySQL 8.0の空間検索、
確かに速くなった…けど
MySQL Release note でわいわい言う勉強会 8.0.20 2020/06/10
まつひさ（hmatsu47）
Slide 2
Slide 2 text
MySQL 8.0の空間検索、
確かに速く遅くならなくなった…けど
MySQL Release note でわいわい言う勉強会 8.0.20 2020/06/10
まつひさ（hmatsu47）
Slide 3
Slide 3 text
自己紹介…は省略
MySQL 8.0の薄い本、8.0.20対応版（印刷版）の不良在庫が…
○ 6冊余ってます
○ GitHub（印刷版無料配布のお知らせを追記しました）
https://github.com/hmatsu47/mysql80_no_usui_hon
○ BOOTH（本体100円＋送料370円です）
https://hmatsu47.booth.pm/
GitHubリポジトリ
3
Slide 4
Slide 4 text
本題
MySQL 8.0で ST_Intersects() などを実行すると遅い！
○ インデックスはあるのに…
https://qiita.com/miyauchi/items/893f12679cb21c12c454
4
Slide 5
Slide 5 text
そして、ついに
8.0.20で直ったらしい。
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html#mysqld-8-0-20-bug
5
Slide 6
Slide 6 text
試してみました
まずは手もとのMySQL 8.0.17 on Windowsで…
○ 国土数値情報ダウンロードサービスから落としてきたシェープファイル
をインポート
https://nlftp.mlit.go.jp/ksj/index.html
○ インポートには、さきほどの宮内さんのこれを使わせていただきました
■ shp2mysql
https://qiita.com/miyauchi/items/b4e810b3becf2cf07e2f
https://github.com/hajime-miyauchi/shp2mysql
6
Slide 7
Slide 7 text
準備完了
7
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)
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
Slide 8
Slide 8 text
8.0.17では
こんな感じで遅いです
8
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)
Slide 9
Slide 9 text
8.0.20にバージョンアップして挑戦！
速くなっ…あれ？
9
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)
Slide 10
Slide 10 text
8.0.20にバージョンアップして挑戦！
速くなっ…あれ？
結果がない…だと？
10
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)
Slide 11
Slide 11 text
ちなみに、探してみたのはここです（札幌市中央区）
11
Slide 12
Slide 12 text
ちょっと範囲を広げてみると…
北区は来たけど中央区が来ない
さっきの場所の ST_Within() もダメでした
12
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)
mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE
ST_Within(ST_GeomFromText('POINT(43.06 141.34)', 4612), `geom`);
Empty set (0.00 sec)
Slide 13
Slide 13 text
ポリゴンの範囲に北区が入ったのですが中央区は…？
13
Slide 14
Slide 14 text
なお、先ほどの地図は
福野泰介さんの「緯度経度地図」のお世話になりました
○ 地理院地図を使用して作られたサイトのようです
○ CC BY 4.0
https://fukuno.jig.jp/app/printmap/latlngmap.html
https://fukuno.jig.jp/1330
14
Slide 15
Slide 15 text
気を取り直して…インデックスを再作成してみます
15
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
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)
Slide 16
Slide 16 text
来た！
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
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)
気を取り直して…インデックスを再作成してみます
16
Slide 17
Slide 17 text
来ました！
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)
mysql> SELECT CONCAT(`n03_001`, `n03_003`, `n03_004`) AS `city` FROM `n03-19_190101` WHERE ST_Within(ST_GeomFromText('POINT(43.06
141.34)', 4612), `geom`);
+-----------------------------+
| city |
+-----------------------------+
| 北海道札幌市中央区 |
+-----------------------------+
1 row in set (0.01 sec)
こちらも…
17
Slide 18
Slide 18 text
インデックスの再作成が必要なようです
結局、
18