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
22
ゲームで体感!Aurora DSQL の OCC(楽観的同時実行制御)
hmatsu47
PRO
0
11
PostgreSQL+pgvector で GraphRAG に挑戦 & pgvectorscale 0.7.x アップデート
hmatsu47
PRO
0
28
LlamaIndex の Property Graph Index を PostgreSQL 上に構築してデータ構造を見てみる
hmatsu47
PRO
0
17
PostgreSQL+pgvector で LlamaIndex の Property Graph Index を試す(序章)
hmatsu47
PRO
0
16
HeatWave on AWS という選択肢を検討してみる
hmatsu47
PRO
0
13
HeatWave on AWS のインバウンドレプリケーションで HeatWave エンジン有効時のレプリケーションラグを確認してみた!
hmatsu47
PRO
0
22
CloudWatch Database Insights 関連アップデート
hmatsu47
PRO
0
56
Other Decks in Technology
See All in Technology
DuckDB-Wasmを使って ブラウザ上でRDBMSを動かす
hacusk
1
140
TypeScript入門
recruitengineers
PRO
35
11k
ソフトウェア エンジニアとしての 姿勢と心構え
recruitengineers
PRO
27
13k
事業価値と Engineering
recruitengineers
PRO
8
5.4k
Kubernetes における cgroup driver のしくみ: runwasi の bugfix より
z63d
2
120
【 LLMエンジニアがヒューマノイド開発に挑んでみた 】 - 第104回 Machine Learning 15minutes! Hybrid
soneo1127
0
250
allow_retry と Arel.sql / allow_retry and Arel.sql
euglena1215
1
150
ヘブンバーンズレッドのレンダリングパイプライン刷新
gree_tech
PRO
0
440
AI時代にPdMとPMMはどう連携すべきか / PdM–PMM-collaboration-in-AI-era
rakus_dev
0
260
カミナシ社の『ID管理基盤』製品内製 - その意思決定背景と2年間の進化 #AWSUnicornDay / Kaminashi ID - The Big Whys
kaminashi
3
720
7月のガバクラ利用料が高かったので調べてみた
techniczna
3
820
ガチな登山用デバイスからこんにちは
halka
1
200
Featured
See All Featured
Why You Should Never Use an ORM
jnunemaker
PRO
59
9.5k
XXLCSS - How to scale CSS and keep your sanity
sugarenia
248
1.3M
Put a Button on it: Removing Barriers to Going Fast.
kastner
60
4k
Exploring the Power of Turbo Streams & Action Cable | RailsConf2023
kevinliebholz
34
6k
Making Projects Easy
brettharned
117
6.4k
The Web Performance Landscape in 2024 [PerfNow 2024]
tammyeverts
9
800
Reflections from 52 weeks, 52 projects
jeffersonlam
351
21k
Building a Modern Day E-commerce SEO Strategy
aleyda
43
7.5k
Keith and Marios Guide to Fast Websites
keithpitt
411
22k
Rails Girls Zürich Keynote
gr2m
95
14k
Done Done
chrislema
185
16k
How To Stay Up To Date on Web Technology
chriscoyier
790
250k
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