はじめに
MySQL8.0のGIS機能を検証していて、空間検索が遅いのが気になったのでメモ。
おそらく何か原因があるのだろうと推測されますが、原因は特定できておらず解決策も見いだせていません。
測定環境
ハードウェアにはさくらのVPS 1Gプランを使用した。
- メモリ: 1GB
- CPU: 2コア
- HDD: 100GB
また、DBにはそれぞれ下記バージョンを使用した。
- MySQL 8.0.16
- PostgreSQL 10.4 PostGIS 2.4.4
測定結果
MySQLを使い、約23万件のポリゴンデータ(後述)に対して検索を実行した場合に２分弱かかる。
対して、同じデータ、環境、検索条件を使ったPostGISの測定結果は20msであるにも関わらず。
測定方法
MySQLの場合
約23万件のポリゴンデータをシェイプファイルからインポートしたテーブル「e_stat.utf8」に対して下記のSQLを実行した。
SELECT
ST_AsGeoJson((SHAPE))
FROM
`e_stat.utf8`
WHERE
ST_Intersects(
SHAPE,
ST_GeomFromText(
'POLYGON((43.08256990265745 141.3162472988521,
43.056831004971265 141.3162472988521,
43.056831004971265 141.35259659542191,
43.08256990265745 141.35259659542191,
43.08256990265745 141.3162472988521))',
4326
)
);
上記を実行した結果の件数と秒数が下記であった。何度か実行したが概ね1分以上2分以内であった。
391 rows in set (1 min 43.87 sec)
SPATIAL INDEXが使用されていない可能性があるが、EXPLAINの結果を見る限りSHAPEキーが使用されているようである。
mysql> EXPLAIN SELECT
-> ST_AsGeoJson((SHAPE))
-> FROM
-> `e_stat.utf8`
-> WHERE
-> ST_Intersects(
-> SHAPE,
-> ST_GeomFromText(
-> 'POLYGON((43.08256990265745 141.3162472988521,
'> 43.056831004971265 141.3162472988521,
'> 43.056831004971265 141.35259659542191,
'> 43.08256990265745 141.35259659542191,
'> 43.08256990265745 141.3162472988521))',
-> 4326
-> )
-> );
+----+-------------+-------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| 1 | SIMPLE | e_stat.utf8 | NULL | range | SHAPE | SHAPE | 34 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.04 sec)
ちなみに、e_stat.utf8テーブルの定義(SHOW CREATE TABLE `e_stat.utf8` の結果)は下記である。
SHAPE列がGEOMETRY型で定義されており、SRIDは4326であり、SHAPE列に対してインデックスが張られている。
CREATE TABLE `e_stat.utf8` (
`OGR_FID` int(11) NOT NULL AUTO_INCREMENT,
`SHAPE` geometry NOT NULL /*!80003 SRID 4326 */,
`area` double(32,10) DEFAULT NULL,
`perimeter` double(32,10) DEFAULT NULL,
`h22ka01_` decimal(11,0) DEFAULT NULL,
`h22ka01_id` decimal(11,0) DEFAULT NULL,
`ken` varchar(2) DEFAULT NULL,
`city` varchar(3) DEFAULT NULL,
`ken_name` varchar(15) DEFAULT NULL,
`sityo_name` varchar(36) DEFAULT NULL,
`gst_name` varchar(27) DEFAULT NULL,
`css_name` varchar(24) DEFAULT NULL,
`hcode` decimal(6,0) DEFAULT NULL,
`kihon1` varchar(4) DEFAULT NULL,
`dummy1` varchar(1) DEFAULT NULL,
`kihon2` varchar(2) DEFAULT NULL,
`keycode1` varchar(9) DEFAULT NULL,
`keycode2` varchar(9) DEFAULT NULL,
`area_max_f` varchar(1) DEFAULT NULL,
`kigo_d` varchar(2) DEFAULT NULL,
`n_ken` varchar(2) DEFAULT NULL,
`n_city` varchar(3) DEFAULT NULL,
`n_c1` decimal(6,0) DEFAULT NULL,
`kigo_e` varchar(3) DEFAULT NULL,
`kigo_i` varchar(1) DEFAULT NULL,
`tate` decimal(6,0) DEFAULT NULL,
`dir` decimal(6,0) DEFAULT NULL,
`hight` decimal(6,0) DEFAULT NULL,
`jikaku` decimal(6,0) DEFAULT NULL,
`nmoji` decimal(6,0) DEFAULT NULL,
`moji` varchar(72) DEFAULT NULL,
`seq_no2` decimal(11,0) DEFAULT NULL,
`ksum` decimal(6,0) DEFAULT NULL,
`csum` decimal(6,0) DEFAULT NULL,
`jinko` decimal(11,0) DEFAULT NULL,
`setai` decimal(6,0) DEFAULT NULL,
`x_code` double(32,10) DEFAULT NULL,
`y_code` double(32,10) DEFAULT NULL,
`kcode1` varchar(7) DEFAULT NULL,
`key_code` varchar(11) DEFAULT NULL,
UNIQUE KEY `OGR_FID` (`OGR_FID`),
SPATIAL KEY `SHAPE` (`SHAPE`)
) ENGINE=InnoDB AUTO_INCREMENT=228287 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PostGISの場合
MySQLと若干の違いはあるものの条件的には等価の下記SQLを実行した。
テーブル名、列名が異なる以外に、PostGISとMySQLのWKTは緯度と経度の指定順(axis-order)が逆である点が異なっている。
SELECT
ST_AsGeoJson((geom))
FROM
"e_stat"
WHERE
ST_Intersects(
geom,
ST_GeomFromText(
'POLYGON((141.3162472988521 43.08256990265745,
141.3162472988521 43.056831004971265,
141.35259659542191 43.056831004971265,
141.35259659542191 43.08256990265745,
141.3162472988521 43.08256990265745))',
4326
)
);
上記の実行時間は下記。何度か実行しているからキャッシュが効いているとしてもMySQLと比較して、かなり早い。
Time: 20.411 ms
ちなみに、検索結果の件数はMySQLと同じ391件であった。
opendata=# SELECT
opendata-# COUNT(*)
opendata-# FROM
opendata-# "e_stat"
opendata-# WHERE
opendata-# ST_Intersects(
opendata(# geom,
opendata(# ST_GeomFromText(
opendata(# 'POLYGON((141.3162472988521 43.08256990265745,
opendata'# 141.3162472988521 43.056831004971265,
opendata'# 141.35259659542191 43.056831004971265,
opendata'# 141.35259659542191 43.08256990265745,
opendata'# 141.3162472988521 43.08256990265745))',
opendata(# 4326
opendata(# )
opendata(# );
count
-------
391
(1 row)
Time: 13.571 ms
opendata=#
使用したデータ
e-Statから「平成22年国勢調査境界データ」を全国分インポートして使用しています。
e-Stat
https://www.e-stat.go.jp/
より厳密には、shp2pgsqlを用いてPostGISにインポートしたのち、pgsql2shpを使って出力したshpデータをMySQLにGDAL3.01のogr2ogrを使ってインポートしています。
また、ogr2ogrでインポートしたテーブルにはSRIDが指定されていなかったためそのままではSPATIAL INDEXが使用されませんでした。
よって、下記記事で書いた方法を使ってSRIDを指定しています。
具体的には下記のコマンドを使用しました。
mysql> ALTER TABLE `e_stat.utf8` DROP INDEX SHAPE;
mysql> ALTER TABLE `e_stat.utf8` MODIFY `SHAPE` GEOMETRY NOT NULL SRID 4326;
mysql> ALTER TABLE `e_stat.utf8` ADD SPATIAL INDEX (`SHAPE`);
最後に
ここまで実行時間に差が出るということは何か根本的に間違えてる可能性があるので、継続して調査したいと思います。
原因をご存知の方はぜひコメントください。