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
860
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
5
PostgreSQL+pgvector で GraphRAG に挑戦 & pgvectorscale 0.7.x アップデート
hmatsu47
PRO
0
17
LlamaIndex の Property Graph Index を PostgreSQL 上に構築してデータ構造を見てみる
hmatsu47
PRO
0
14
PostgreSQL+pgvector で LlamaIndex の Property Graph Index を試す(序章)
hmatsu47
PRO
0
11
HeatWave on AWS という選択肢を検討してみる
hmatsu47
PRO
0
7
HeatWave on AWS のインバウンドレプリケーションで HeatWave エンジン有効時のレプリケーションラグを確認してみた!
hmatsu47
PRO
0
17
CloudWatch Database Insights 関連アップデート
hmatsu47
PRO
0
32
さいきんの MySQL との付き合い方 〜 MySQL 8.0 より後の世界へようこそ 〜
hmatsu47
PRO
0
31
ベクトルストア入門
hmatsu47
PRO
0
25
Other Decks in Technology
See All in Technology
20250705 Headlamp: 專注可擴展性的 Kubernetes 用戶界面
pichuang
0
270
American airlines ®️ USA Contact Numbers: Complete 2025 Support Guide
airhelpsupport
0
390
Model Mondays S2E04: AI Developer Experiences
nitya
0
140
関数型プログラミングで 「脳がバグる」を乗り越える
manabeai
1
190
Yahoo!しごとカタログ 新しい境地を創るエンジニア募集!
lycorptech_jp
PRO
0
110
タイミーのデータモデリング事例と今後のチャレンジ
ttccddtoki
6
2.4k
freeeのアクセシビリティの現在地 / freee's Current Position on Accessibility
ymrl
2
200
第4回Snowflake 金融ユーザー会 Snowflake summit recap
tamaoki
1
290
Delta airlines®️ USA Contact Numbers: Complete 2025 Support Guide
airtravelguide
0
340
LangChain Interrupt & LangChain Ambassadors meetingレポート
os1ma
2
320
使いたいMCPサーバーはWeb APIをラップして自分で作る #QiitaBash
bengo4com
0
1.9k
Backlog ユーザー棚卸しRTA、多分これが一番早いと思います
__allllllllez__
1
150
Featured
See All Featured
Building a Modern Day E-commerce SEO Strategy
aleyda
42
7.4k
The Art of Delivering Value - GDevCon NA Keynote
reverentgeek
15
1.5k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
35
2.4k
Measuring & Analyzing Core Web Vitals
bluesmoon
7
510
Reflections from 52 weeks, 52 projects
jeffersonlam
351
20k
Docker and Python
trallard
44
3.5k
Scaling GitHub
holman
460
140k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_iinuma
126
53k
GitHub's CSS Performance
jonrohan
1031
460k
A designer walks into a library…
pauljervisheath
207
24k
JavaScript: Past, Present, and Future - NDC Porto 2020
reverentgeek
50
5.5k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
667
120k
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