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
Glacierだからってコストあきらめてない? / JAWS Meet Glacier Cost
taishin
1
160
マーケットプレイス版Oracle WebCenter Content For OCI
oracle4engineer
PRO
3
960
OSSのSNSツール「Misskey」をさわってみよう(右下ワイプで私のOSCの20年を振り返ります) / 20250705-osc2025-do
akkiesoft
0
170
第4回Snowflake 金融ユーザー会 Snowflake summit recap
tamaoki
1
290
Lufthansa ®️ USA Contact Numbers: Complete 2025 Support Guide
lufthanahelpsupport
0
200
Delta airlines Customer®️ USA Contact Numbers: Complete 2025 Support Guide
deltahelp
0
710
ビズリーチが挑む メトリクスを活用した技術的負債の解消 / dev-productivity-con2025
visional_engineering_and_design
3
7.7k
AIの全社活用を推進するための安全なレールを敷いた話
shoheimitani
2
530
無意味な開発生産性の議論から抜け出すための予兆検知とお金とAI
i35_267
4
13k
Claude Code に プロジェクト管理やらせたみた
unson
6
4.2k
【5分でわかる】セーフィー エンジニア向け会社紹介
safie_recruit
0
27k
スタートアップに選択肢を 〜生成AIを活用したセカンダリー事業への挑戦〜
nstock
0
210
Featured
See All Featured
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
31
2.4k
Balancing Empowerment & Direction
lara
1
430
Writing Fast Ruby
sferik
628
62k
Become a Pro
speakerdeck
PRO
29
5.4k
Bash Introduction
62gerente
613
210k
Rebuilding a faster, lazier Slack
samanthasiow
82
9.1k
The Pragmatic Product Professional
lauravandoore
35
6.7k
Building a Scalable Design System with Sketch
lauravandoore
462
33k
Cheating the UX When There Is Nothing More to Optimize - PixelPioneers
stephaniewalter
281
13k
RailsConf 2023
tenderlove
30
1.1k
Java REST API Framework Comparison - PWX 2021
mraible
31
8.7k
Speed Design
sergeychernyshev
32
1k
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