Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
MySQL 8.0の空間検索、確かに速くなった…けど
Search
hmatsu47
PRO
June 10, 2020
Technology
0
870
MySQL 8.0の空間検索、確かに速くなった…けど
MySQL Release note でわいわい言う勉強会 8.0.20で話すかもしれないネタ
hmatsu47
PRO
June 10, 2020
Tweet
Share
More Decks by hmatsu47
See All by hmatsu47
今年の FESTA で初当日スタッフ+登壇してきました
hmatsu47
PRO
0
6
攻略!Aurora DSQL の OCC(楽観的同時実行制御)
hmatsu47
PRO
0
3
PostgreSQL でもできる!GraphRAG
hmatsu47
PRO
0
2
Aurora DSQL のトランザクション(スナップショット分離と OCC)
hmatsu47
PRO
0
8
いろんなところに居る Amazon Q(Developer)を使い分けてみた
hmatsu47
PRO
0
25
「ゲームで体感!Aurora DSQL の OCC(楽観的同時実行制御)」の結果ログから Aurora DSQL の動作を考察する
hmatsu47
PRO
0
2
ゲームで体感!Aurora DSQL の OCC(楽観的同時実行制御)
hmatsu47
PRO
0
27
PostgreSQL+pgvector で GraphRAG に挑戦 & pgvectorscale 0.7.x アップデート
hmatsu47
PRO
0
47
LlamaIndex の Property Graph Index を PostgreSQL 上に構築してデータ構造を見てみる
hmatsu47
PRO
0
17
Other Decks in Technology
See All in Technology
"プロポーザルってなんか怖そう"という境界を超えてみた@TSUDOI by giftee Tech #1
shilo113
0
190
オープンソースでどこまでできる?フォーマル検証チャレンジ
msyksphinz
0
140
能登半島災害現場エンジニアクロストーク 【JAWS FESTA 2025 in 金沢】
ditccsugii
0
830
アイテムレビュー機能導入からの学びと改善
zozotech
PRO
0
160
ユーザーの声とAI検証で進める、プロダクトディスカバリー
sansantech
PRO
1
140
ソースを読むプロセスの例
sat
PRO
6
2.2k
セキュアな認可付きリモートMCPサーバーをAWSマネージドサービスでつくろう! / Let's build an OAuth protected remote MCP server based on AWS managed services
kaminashi
3
330
Geospatialの世界最前線を探る [2025年版]
dayjournal
1
220
Introduction to Sansan, inc / Sansan Global Development Center, Inc.
sansan33
PRO
0
2.8k
Contract One Engineering Unit 紹介資料
sansan33
PRO
0
8.8k
ガバメントクラウドの概要と自治体事例(名古屋市)
techniczna
3
240
『OCI で学ぶクラウドネイティブ 実践 × 理論ガイド』 書籍概要
oracle4engineer
PRO
3
220
Featured
See All Featured
XXLCSS - How to scale CSS and keep your sanity
sugarenia
248
1.3M
Side Projects
sachag
455
43k
Product Roadmaps are Hard
iamctodd
PRO
54
11k
The Success of Rails: Ensuring Growth for the Next 100 Years
eileencodes
46
7.7k
jQuery: Nuts, Bolts and Bling
dougneiner
65
7.9k
Being A Developer After 40
akosma
91
590k
Java REST API Framework Comparison - PWX 2021
mraible
34
8.9k
Building Applications with DynamoDB
mza
96
6.7k
The Cult of Friendly URLs
andyhume
79
6.6k
Scaling GitHub
holman
463
140k
Speed Design
sergeychernyshev
32
1.2k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
194
16k
Transcript
MySQL 8.0の空間検索、 確かに速くなった…けど MySQL Release note でわいわい言う勉強会 8.0.20 2020/06/10 まつひさ(hmatsu47)
MySQL 8.0の空間検索、 確かに速く遅くならなくなった…けど MySQL Release note でわいわい言う勉強会 8.0.20 2020/06/10 まつひさ(hmatsu47)
自己紹介…は省略 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
本題 MySQL 8.0で ST_Intersects() などを実行すると遅い! ◦ インデックスはあるのに… https://qiita.com/miyauchi/items/893f12679cb21c12c454 4
そして、ついに 8.0.20で直ったらしい。 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-20.html#mysqld-8-0-20-bug 5
試してみました まずは手もとの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
準備完了 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
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)
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)
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)
ちなみに、探してみたのはここです(札幌市中央区) 11
ちょっと範囲を広げてみると… 北区は来たけど中央区が来ない さっきの場所の 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)
ポリゴンの範囲に北区が入ったのですが中央区は…? 13
なお、先ほどの地図は 福野泰介さんの「緯度経度地図」のお世話になりました ◦ 地理院地図を使用して作られたサイトのようです ◦ CC BY 4.0 https://fukuno.jig.jp/app/printmap/latlngmap.html https://fukuno.jig.jp/1330
14
気を取り直して…インデックスを再作成してみます 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)
来た! 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
来ました! 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
インデックスの再作成が必要なようです 結局、 18