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
Aurora DSQL のトランザクション(スナップショット分離と OCC)
hmatsu47
PRO
0
4
いろんなところに居る Amazon Q(Developer)を使い分けてみた
hmatsu47
PRO
0
24
ゲームで体感!Aurora DSQL の OCC(楽観的同時実行制御)
hmatsu47
PRO
0
13
PostgreSQL+pgvector で GraphRAG に挑戦 & pgvectorscale 0.7.x アップデート
hmatsu47
PRO
0
33
LlamaIndex の Property Graph Index を PostgreSQL 上に構築してデータ構造を見てみる
hmatsu47
PRO
0
17
PostgreSQL+pgvector で LlamaIndex の Property Graph Index を試す(序章)
hmatsu47
PRO
0
17
HeatWave on AWS という選択肢を検討してみる
hmatsu47
PRO
0
14
HeatWave on AWS のインバウンドレプリケーションで HeatWave エンジン有効時のレプリケーションラグを確認してみた!
hmatsu47
PRO
0
22
CloudWatch Database Insights 関連アップデート
hmatsu47
PRO
0
57
Other Decks in Technology
See All in Technology
新アイテムをどう使っていくか?みんなであーだこーだ言ってみよう / 20250911-rpi-jam-tokyo
akkiesoft
0
310
AIのグローバルトレンド2025 #scrummikawa / global ai trend
kyonmm
PRO
1
300
EncryptedSharedPreferences が deprecated になっちゃった!どうしよう! / Oh no! EncryptedSharedPreferences has been deprecated! What should I do?
yanzm
0
470
2025年夏 コーディングエージェントを統べる者
nwiizo
0
180
企業の生成AIガバナンスにおけるエージェントとセキュリティ
lycorptech_jp
PRO
2
190
250905 大吉祥寺.pm 2025 前夜祭 「プログラミングに出会って20年、『今』が1番楽しい」
msykd
PRO
1
980
「何となくテストする」を卒業するためにプロダクトが動く仕組みを理解しよう
kawabeaver
0
420
JTCにおける内製×スクラム開発への挑戦〜内製化率95%達成の舞台裏/JTC's challenge of in-house development with Scrum
aeonpeople
0
250
AIをプライベートや業務で使ってみよう!効果的な認定資格の活かし方
fukazawashun
0
100
未経験者・初心者に贈る!40分でわかるAndroidアプリ開発の今と大事なポイント
operando
5
730
AIエージェントで90秒の広告動画を制作!台本・音声・映像・編集をつなぐAWS最新アーキテクチャの実践
nasuvitz
3
300
AWSを利用する上で知っておきたい名前解決のはなし(10分版)
nagisa53
10
3.2k
Featured
See All Featured
The Art of Programming - Codeland 2020
erikaheidi
56
13k
Reflections from 52 weeks, 52 projects
jeffersonlam
352
21k
The World Runs on Bad Software
bkeepers
PRO
70
11k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
Faster Mobile Websites
deanohume
309
31k
Making Projects Easy
brettharned
117
6.4k
Practical Orchestrator
shlominoach
190
11k
Facilitating Awesome Meetings
lara
55
6.5k
Code Review Best Practice
trishagee
71
19k
What's in a price? How to price your products and services
michaelherold
246
12k
個人開発の失敗を避けるイケてる考え方 / tips for indie hackers
panda_program
113
20k
The Straight Up "How To Draw Better" Workshop
denniskardys
236
140k
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