mysql8でspatial indexを効かせるためにはSRIDを統一しておく必要があるかもしれない話

総論

spatial index効かせたかったら列にSRIDをつけろ

GISを使って地図情報を扱ったり近傍検索したり

mysqlは8になってGIS関連が超パワーアップしたと聞いて。

• MySQLに本格GIS機能がやってきた～MySQL8.0最新情報～@OSC2018北海道
• MySQL 8.0で強化されたGIS機能のご紹介：「FOSS4G 2018 Hokkaido」での発表資料

準備

何はともあれ環境作成

[qyen@mullet mullet]$ mysql --version
mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)

テーブルを作る

CREATE TABLE `locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `geohash` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `location` geometry NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `locations_id_unique` (`id`),
  KEY `locations_name_index` (`name`),
  KEY `locations_geohash_index` (`geohash`),
  SPATIAL KEY `location` (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

名前と住所とロケーション。あとgeohashでオシャレ感を演出的な。

データ投入

位置参照情報ダウンロードサービスから大字・町丁目レベルでダウンロードして投入。
googlemapで使いたいのでWGS 94(SRID4326)を使おう。

LOAD DATA INFILE '/path/to/13_2015_utf8.csv'
REPLACE INTO TABLE
geo.spots
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' STARTING BY ''
IGNORE 1 LINES
(@dummy, @prefecture, @dummy, @city, @dummy, @area, @lat, @lon, @dummy, @dummy)
SET
name = CONCAT(@city, @area), 
address = CONCAT(@prefecture, @city, @area), 
location = ST_GeomFromText(CONCAT('POINT(', @lon, ' ', @lat, ')'),4326),
created_at=now(),
updated_at=now();

みたいな。(実際はlaravel使ってコマンド化してインポートした。)

• 空間参照系の概要 - Qiita
• MySQLに街区レベル位置参照情報のCSVデータをインポートする - xykのブログ

空間検索って中2感あふれる言葉だよね

どうやらst_bufferで範囲を作ってst_withinで含まれているか検索するのが良いようだ。

• MySQL (InnoDB) で空間検索 (st_within) - Qiita

select count(*) from locations 
where ST_Within(location,ST_GeomFromText(St_AsText(ST_Buffer(ST_GeomFromText(St_AsText(ST_GeomFromText('POINT(35.668440825448 139.74229644907)' , 4326))),0.0089831528411952)),4326))

...くっそ遅ぇ。全国分のCSV食わせて189539件のデータから検索するのに7秒ぐらいかかってる。

explainしてみる。

explain select count(*) from locations 
where ST_Within(location,ST_GeomFromText(St_AsText(ST_Buffer(ST_GeomFromText(St_AsText(ST_GeomFromText('POINT(35.668440825448 139.74229644907)' , 4326))),0.0089831528411952)),4326))

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	locations	null	ALL	null	null	null	null	187266	100	Using where

おい、type=ALL おい。

こんなにSRIDアンマッチに煩いシステムなのに列定義にSRIDを指定しないでいいのだろうか ¹

In 5.7 and earlier, the only requirements on an indexed geometry column were that the type should be a geometry type and that the column shouldn’t be nullable. Unfortunately, we were allowed to insert geometries in different SRIDs into the same index. That never made sense, and it gets worse when some geometries are in geographic SRSs.
Therefore, MySQL 8.0 adds a way to restrict geometry columns to only one SRID:

(いいかげん翻訳) 5.7以前だとnotnullかつgeometryならindex張れたけどSRID違いだったり一部の空間関数使うとクソ重かったから8.0以降では列をSRIDで絞ってindexにSRIDを強制させる方法ができたよ。
Geographic Indexes in InnoDB | MySQL Server Blog

mysql> CREATE TABLE places (
 -> pk INT PRIMARY KEY,
 -> position POINT NOT NULL SRID 4326,
 -> name VARCHAR(200)
 -> );
Query OK, 0 rows affected (0,00 sec)

We can easily create an index on our geometry column.

mysql> CREATE SPATIAL INDEX position ON places (position);
Query OK, 0 rows affected (0,00 sec) Records: 0 Duplicates: 0 Warnings: 0

Since this column is in SRID 4326, the index will also be in SRID 4326. SRID 4326 is a geographic SRS, so this will be a geographic index. The query optimizer will automatically use this index to optimize execution of queries with spatial relation functions (ST_Contains, ST_Within, etc.), if it finds that that is the cheapest access method. All the spatial relation functions support geographic computations.
(いいかげん翻訳) 列をSRID 4326にしたからindexも4326になってクエリオプティマイザが4326系の検索にインデックスを使うようになるぞ。
Geographic Indexes in InnoDB | MySQL Server Blog

なるほど。

列にSRIDを指定し直して実行

alter table locations drop index location;
alter table locations modify location geometry not null SRID 4326 ;
alter table locations add spatial index(location);

して

explain select count(*) from locations 
where ST_Within(location,ST_GeomFromText(St_AsText(ST_Buffer(ST_GeomFromText(St_AsText(ST_GeomFromText('POINT(35.668440825448 139.74229644907)' , 4326))),0.0089831528411952)),4326))

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	locations	null	range	location	location	34	null	1	100	Using where

おお。早くなった。

つーか公式ブログてめぇ

One perhaps surprising fact is that the server still allows us to crate indexes on columns that are not restricted to a single column, but with a warning that the index will never be used:
(いいかげん翻訳) SRIDを指定してない列にインデックス張っても警告されない上に絶対にそのインデックスを使われないとかマジ驚きだよね
Geographic Indexes in InnoDB | MySQL Server Blog

驚きだよねじゃなくて直せや。

1. なんかなろう系いけ好かないタイプの主人公みたいな見出しだ。 ↩