地図リクエストが積み重なり、コネクションプールが枯れた — lat/lng B-treeからMySQL SPATIALインデックスへ
目次
- 1. 症状 — 遅いクエリがネットワークエラーに化けるまで
- 2. 一次対応 — HikariCP設定バグ(症状の緩和)
- 3. 根本原因 — 2D範囲クエリをB-treeで処理してはいけない
- 4. 解決 — MySQL SPATIALインデックス
- 5. 3度の試行錯誤 — SPATIALインデックスが効かなかった理由
- ① SRID 4326 + 軸(axis)順序の不一致
- ② ダーティデータ
- ③ /!80003 SRID 0 / コメントの欠落 ← 本当の原因
- ALTERは3段階に分ける
- 6. 座標系・座標順序 — 実戦リファレンス
- 軸順序: SRID 0 vs 4326(一番混乱しやすいところ)
- 外部地図APIはほぼWGS84(4326) — ただし順序がバラバラ
- 7. 大容量テーブルのマイグレーションのコツ
- 8. インデックス1つで終わりではなかった — 複数の軸で防ぐ
- 9. 技術サマリー
- 番外. 振り返り — 専門性は「データで先に見せて」こそ採用される
地図サービスのQAで、地図を素早く操作する(ズームやパンを連続して行う)と断続的に「ネットワークエラー」のポップアップが出るというレポートが上がってきた。1〜2回のリクエストでは問題なかった — 問題は短時間にリクエストが重なったときだった。表面上はフロントエンドのネットワークエラーだったが、原因を掘り下げると空間クエリをlat/lng B-treeで処理していたこと + リクエストが同時多発した際のコネクションプール枯渇に行き着いた。最終的な解決策はMySQL POINT + SPATIAL INDEXへの移行だ。その過程で踏んだ落とし穴を整理しておく。
1. 症状 — 遅いクエリがネットワークエラーに化けるまで
ポイントは1〜2回のリクエストでは問題が出なかったという点だ。遅いクエリが1本あるだけではすぐ障害にはならない。地図は表示範囲(bounding box)が変わるたびにAPIを叩くが、ズームやパンを素早く繰り返すとこの呼び出しが短時間に積み重なる。各リクエストが遅いクエリ(最大6秒)でコネクションを長く握り続けるため、処理が追いつかず同時接続数がプールサイズを超えた瞬間に詰まる。その結果がこれだ:
HikariPool-x - Connection is not available, request timed out after 30000ms
(total=4, active=4, idle=0, waiting=N)遅いクエリ(最大6秒)がコネクションを長く占有
+ 素早いズーム・移動で短時間にリクエストが累積
→ 同時占有コネクション数 > プールサイズ(4)
→ プール枯渇、待機リクエストが30秒でタイムアウト
→ フロント「ネットワークエラー」ポップアップ教訓0: プールを枯らすのは単一リクエストの遅延ではなく、**「遅いリクエスト × 同時実行」**の掛け算だ。1〜2本なら耐えても、短時間に積み上がれば一気に崩れる。フロントエンドに出る「ネットワークエラー」はあくまで末端の症状でしかない — 表面のメッセージを鵜呑みにせず、カスケードを逆に辿っていく必要がある。
2. 一次対応 — HikariCP設定バグ(症状の緩和)
まずプール側を確認したところ、設定バグが見つかった。maxLifetime/keepaliveTimeをフィールドで宣言しているだけで、HikariConfigへのset呼び出しが漏れていた。しかもデフォルト値も30秒と短すぎた。
// ❌ 宣言はあるのにHikariConfigへのset呼び出しが抜けていた
private long maxLifeTime = 30000; // しかも30秒は短すぎる
// ✅ 修正
hikariConfig.setMaxLifetime(property.getMaxLifeTime()); // 30分
hikariConfig.setKeepaliveTime(property.getKeepaliveTime()); // 5分これを修正すると症状は収まった。ただし根本原因(遅いクエリがコネクションを長く占有すること)は手つかずのままだった。プール設定はあくまで鎮痛剤にすぎない。
3. 根本原因 — 2D範囲クエリをB-treeで処理してはいけない
問題のクエリはこういう形をしていた。
SELECT ...
FROM places p
LEFT JOIN place_stats s ON p.id = s.place_id
WHERE p.center_lat BETWEEN :latSW AND :latNE -- 緯度範囲
AND p.center_lng BETWEEN :lngSW AND :lngNE -- 経度範囲
AND p.type IN (:types)(center_lat, center_lng)の複合B-treeでは2次元の領域検索に本質的な限界がある。緯度で絞り込んでも、その緯度範囲に含まれる全経度のデータをスキャンしなければならない。範囲によっては数万〜数十万行を走査することになり、クエリは最大6秒まで膨らんだ。データが数百万件(約540万行)規模になると、これは時限爆弾だ。
B-treeは1次元のソート構造なので、「矩形領域内の点を探す」という操作には根本的に向いていない。それは**R-tree(空間インデックス)**の仕事だ。
4. 解決 — MySQL SPATIALインデックス
POINTカラム + SPATIAL INDEX(R-tree) + MBRContains/MBRWithinに切り替えた。
-- 領域内の点をR-treeで検索
WHERE MBRContains(
ST_GeomFromText('POLYGON((...bbox...))', 0),
p.location
)効果は劇的だった。EXPLAINの結果:
type=range key=idx_location rows≈52,000 -- 540万 → 5万 rowに縮小クエリが6秒 → 数十msに短縮。コネクションの占有時間が大幅に短くなり、プール枯渇も根本から解消した。
5. 3度の試行錯誤 — SPATIALインデックスが効かなかった理由
ここからが本題だ。SPATIAL INDEXを作ったにもかかわらず、オプティマイザはフルスキャン(type: ALL)を選び続けた。3回試して、3回外した。
① SRID 4326 + 軸(axis)順序の不一致
最初はSRID 4326(WGS84)で試みた。ところが4326は緯度優先(lat-first)の仕様なので、POINT(lng, lat)で格納していたデータと軸がずれてインデックスが効かなかった(540万行フルスキャン、90秒)。lat-firstで入れ直そうとすると、今度はlatitude 126.97 out of range(経度の値が緯度の位置に入っている)エラー。
補足 — 座標系の選び方。 4326のlat-first仕様がこの罠の温床になる。ただより本質的な問題として、今回の演算は測地距離ではなくbboxの包含判定だ。4326の楕円体精度は結果に影響しない — コストだけかかる。それならより単純で落とし穴の少ない**平面座標の
SRID 0**を選ぶのが合理的だ。(距離・半径が必要になったときに4326/投影CRSへ切り替えればいい — 詳細は§6「座標系の選択」)
② ダーティデータ
latカラムに経度値(126.x)が混入した行があった。SRID 0の平面座標系に切り替えてデータを整理。それでもまだtype: ALLのまま。
③ /*!80003 SRID 0 */ コメントの欠落 ← 本当の原因
これが真の原因だった。カラム定義にSRID属性が明示されていないと、オプティマイザはSPATIALインデックスを使えない。FORCE INDEXすら無力だ。
-- ❌ SRID属性なし → インデックスは役立たず
location POINT GENERATED ALWAYS AS (...) STORED NOT NULL
-- ✅ /*!80003 SRID 0 */ があってこそオプティマイザがインデックスを認識
location POINT GENERATED ALWAYS AS (...) STORED NOT NULL /*!80003 SRID 0 */MySQL 8.0マニュアル: "The optimizer can use SPATIAL indexes on SRID-restricted columns." — カラムにSRID制約が刻まれている必要がある。
実はこの制約自体は知っていた。ただSRIDの設定は年に一度あるかないかの作業で(以前の環境ではすでに設定済みで自分で触ったことがなかった)、いざ自分でやろうとしたときには記憶からすっかり抜け落ちていた。「知っている」と「その瞬間に思い出せる」は別の話だ — 滅多に使わない制約ほどそうなりやすい。頻度の低いセットアップは、チェックリストやスニペットとして外部化しておくに限る。
ただ、これは自分だけのミスとも言い切れない。MySQL 8.0がSRID制約のないカラムのSPATIALインデックスをinvalid扱いしてオプティマイザに使わせない仕様は、WL#10439による意図的な設計だ — インデックスが平面(SRID 0)か地理(4326)かを把握していないと、bounding boxの計算が正しく行えないためだ。だからFORCE INDEXも効かない(インデックスがinvalidなのだから)。とはいえ開発者体験の面では批判されて当然でもある。5.7→8.0のアップグレード時に既存インデックスが何の警告も出さずに黙って無効化され、「正常に動いていたものが突然遅くなる」という事故があまりに多発したため、MySQLは別途アップグレードガイドのブログを公開したほどだ。(正確には、SRID制約のないカラムに新規でインデックスを作る際には「使われない」という警告が表示される。しかしアップグレード時やクエリの実行時には沈黙したまま、FORCE INDEXも効かないという一貫性のなさが人をはまらせる。)
ALTERは3段階に分ける
1つのALTER文にSRID変更とインデックス操作をまとめると「インデックスが残った状態でSRIDは変更できない」というエラーになる。DROP → MODIFY → ADDの3段階に分ける必要がある。
ALTER TABLE places DROP INDEX idx_location;
ALTER TABLE places
MODIFY COLUMN location POINT
GENERATED ALWAYS AS (ST_SRID(POINT(COALESCE(center_lng,0), COALESCE(center_lat,0)), 0))
STORED NOT NULL /*!80003 SRID 0 */;
ALTER TABLE places ADD SPATIAL INDEX idx_location (location);
ANALYZE TABLE places;6. 座標系・座標順序 — 実戦リファレンス
軸順序: SRID 0 vs 4326(一番混乱しやすいところ)
同じPOINT(a b)でも、SRIDによってa・bが経度か緯度かが変わる。空間データで最も踏みやすい地雷の一つだ。
| SRID 0(平面/Cartesian) | SRID 4326(WGS84 地理) | |
|---|---|---|
| 意味 | 単なるX・Y平面(地理的意味なし) | 実際の地球楕円体上の緯度経度 |
| 軸順序 | X, Y — 慣例上 X=経度, Y=緯度 | 緯度が先、経度が後(EPSG 4326公式軸順序 = MySQLデフォルト) |
| 韓国座標の入力 | POINT(経度, 緯度) → POINT(127.0, 37.5) | POINT(緯度, 経度) → POINT(37.5, 127.0) |
| 範囲検証 | なし | 緯度 −90〜90 / 経度 −180〜180、外れるとエラー |
ST_Distance | デカルト距離(単位は無意味) | メートル(実距離) |
最大の落とし穴: SRID 4326は緯度が先だ。韓国の座標を経度先でPOINT(127.0 37.5)と入れると → 最初の値127が緯度として解釈される → Latitude out of rangeエラー。(上記の試行錯誤①がまさにこれだ)
MySQL実戦メモ
POINT(x, y)関数はSRID 0ジオメトリを作る。4326で使うにはST_SRID(POINT(...), 4326)またはST_GeomFromText('POINT(...)', 4326)。- 4326であえて経度先で書きたいなら
ST_GeomFromText(wkt, 4326, 'axis-order=long-lat')オプションで軸順序を反転できる。 - 迷ったらコンポーネントを明示的に: 地理点は
ST_Latitude(g)/ST_Longitude(g)で安全に取り出す。
結局、選択の軸は「データがWGS84かどうか」ではなく「このクエリが測地距離を必要とするか」だ:
- ビューポートのbbox・領域包含のみ → SRID 0 +
POINT(経度, 緯度)(十分・シンプル・高速、軸順序の罠なし)。MBR*関数はbounding-box比較なので平面でも地理でも結果は変わらない。 - 実距離・半径(「500m以内」)・面積の計算 → 4326(メートル単位)または投影平面CRS。
「平面だと距離計算が不正確では?」という疑問はもっともだ。その通りで、生の緯度経度を平面距離として扱うと歪む(韓国の37.5°Nでは経度1°≈88km、緯度1°≈111km)。距離を正確に扱うなら、生のlat/lngではなく投影座標系(EPSG:5179/5186 Korea TM、またはWeb Mercator 3857)を使う。距離計算が不要なら(YAGNI)SRID 0で十分であり、その必要が生じたときに切り替えればいい。(要は「慣れているから」ではなく演算の性質に合わせて選ぶということだ。)
🐘 余談 —「PostGISが空間処理に優れている」のは4326のためではない。 PostGISが強い理由は球面座標系を強制するからではなく、
geometry(平面)・geography(測地)の二型分離 + 任意CRSへの再投影(ST_Transform) + 豊富な関数群 + GiSTインデックスの組み合わせにある。PostGIS自身もbboxのクエリは平面geometryで処理する — つまり上記のSRID 0選択と同じ哲学だ。測地(geography)は「本物の距離」が必要なときだけ引き出す。MySQLは型が1つなのでSRIDから動作を推論する設計であり、測地演算・再投影のサポートが限定的なのが大きな違いだ。
外部地図APIはほぼWGS84(4326) — ただし順序がバラバラ
ジオコーディング・POI・ルート検索のレスポンスから取得した座標をPOINTカラムに格納するとき、この罠が実際の現場で問題になる。レスポンスのほとんどはWGS84だが、lat/lngの順序はサービスによってバラバラだ:
| サービス | 座標系 | 順序(代表API) |
|---|---|---|
| Google(Geocoding) | WGS84 | 緯度,経度(latlng=lat,lng) |
| Naver(NCP Geocoding) | WGS84 | 経度,緯度(lng,lat) |
| Kakao(Local REST) | WGS84デフォルト(+TM/WTM等) | 経度,緯度(x=経度, y=緯度) |
| Tmap(SK Open API) | WGS84GEO | 経度,緯度(startX=経度, startY=緯度) |
**Googleだけが緯度先で、韓国の3サービス(Naver・Kakao・Tmap)はいずれも経度先(x/startX=経度)**という構図だ。「WGS84なら全部同じ」と思い込まず、APIごとに軸順序を確認し、自分たちのPOINT(経度, 緯度)格納規約に揃える必要がある。揃えないと、SRID 0の場合は緯度経度が反転して全く別の場所にプロットされ、4326の場合はLatitude out of rangeでエラーになる。
7. 大容量テーブルのマイグレーションのコツ
数百万行のテーブルにPOINTカラム + SPATIAL INDEXを安全に乗せる戦略は2つある:
- 通常カラム方式(アプリから直接更新/INSERTが必要なとき):
Nullableでカラム追加 → PK範囲でのチャンク分割UPDATE(540万行を一括UPDATEするとbinlog/undoが膨らみ長時間ロックになるリスクがある) →NOT NULL+ インデックス。POINT(x, y)は**POINT(経度, 緯度)の順**であることを忘れずに。 - STORED生成カラム方式(lat/lngにNULLがなく、導出カラムとして読み取り専用で使うなら): 単一のALTERで完了する。ただし
STOREDが必須(VIRTUALには空間インデックスを作れない)で、カラムのSRIDと式のSRIDが一致している必要がある。
8. インデックス1つで終わりではなかった — 複数の軸で防ぐ
そもそもこの問題は複数の要因が重なって起きたものなので、それぞれの軸で個別に対処した。
-
クエリ効率 — SPATIALインデックス(上述): geometryフィルタをR-treeで安く処理する。
-
リクエスト頻度 — debounce(実装はあったがprodで外れていた): フロントにはズーム/パンの連続リクエストをまとめるdebounceがすでに実装されていた。しかし配線を忘れたのか、どこかのバグ修正でロールバックされたのか — ある時点でそのロジックが抜け落ち、prodはdebounceなしで動き続けていた。リクエストが連続してそのまま流れ込む状態で、§1の「累積+同時実行」を増幅させた一因だ。対応にはこのdebounceを正しく再接続することも含まれた。
教訓: コードが存在する ≠ 動いている。「実装済み」ではなく「今prodで有効になっているか」を確認すべきだ。
-
データ量 — 広いズームはフロントがAPIを切り替える: ズームアウトするほどbbox内の件数が急増する — ズームレベル14・大都市(ソウル)基準で1画面に約1万件。そこに統計テーブルのLEFT JOINが重なれば重くなる。そこでフロントがズームに応じてAPIの呼び出しを切り替える: ズーム14では個別物件を一定数にcap(中心からの半径順に優先表示)し、さらにズームアウトしたら個別取得の代わりに集計マーカー(count)APIを呼ぶ。(なお、バックエンドの取得API自体にズーム分岐ロジックはない — 分岐はフロントのAPIルーティングで行う。)
まとめると**高速なクエリ(SPATIALインデックス) + リクエスト削減(debounce) + ズーム別のAPI切り替え(フロント)**という多層防御だ。ただし正直に言えば、SPATIALインデックスが解決したのはgeometryフィルタのコストだけで、LEFT JOINのボリューム問題(統計テーブルの統合など)は後続課題として残っている。「インデックス1本で完結」ではないのがこの問題の肝だ。
9. 技術サマリー
- 遅いクエリ → コネクションプール枯渇 → ネットワークエラーというカスケードが起きる。プール設定は鎮痛剤にすぎず、クエリ最適化こそが根本治療だ。
- 2D領域検索はB-treeではなく**SPATIALインデックス(R-tree)**の仕事。
/*!80003 SRID 0 */がなければFORCE INDEXも効かない — カラムへのSRID属性の明示は必須。- 4326はlat-firstの軸順序が罠で、bbox照会に測地精度はそもそも不要 —
SRID 0で十分。実距離/半径/面積が必要なら4326または投影CRS。 - ALTERはDROP → MODIFY → ADDの3段階に分ける。
- 解決策は1つの軸に留まらない — クエリ効率(SPATIAL) + リクエスト頻度(debounce) + ズームアウト時のボリューム(フロントが集計マーカーAPIに切り替え)を合わせて対処した。ただしLEFT JOINのボリューム構造の整理は後続課題(インデックスはgeometryフィルタのみ解決)。
番外. 振り返り — 専門性は「データで先に見せて」こそ採用される
この記事の技術的な結論はすっきりしているが、個人的により強く残ったのは別のことだ。
地図の機能はもともとlat/lng B-treeで動いていたレガシーだった — 自分が設計した構造ではない。位置データは自分の専門領域だったので(前職で4年間、位置情報系の技術を扱ってきた)、この方式がデータ規模が大きくなると限界を迎えることは分かっていた。しかし「今は問題ない」という優先順位に押され、手をつけられないまま運用が続いていた。そしてトラフィックが増えるにつれ、まさにその弱点でインシデントが起きた。
そこで学んだのは「やはり自分が正しかった」ではない。専門知識があっても、それだけではレガシーの優先順位は変えられないということだ。優先順位を動かすのは言葉ではなく数値だ。「今は問題ないが、データがN倍になればスキャンがこれだけ増えてプールがこう枯渇する」を、インシデントが起きる前に小さなベンチマークで示していれば、コストが現実化する前に手を打てた。次からは自分の専門領域であるほど、言葉での主張より先にベンチマーク1本を持っていくと決めた。
これは誰かの判断を批判する記事ではない。自分がもっとうまくできた部分についての記録だ。今回最も強く学んだのは、「正しいこと」より「数値で先に示すこと」のほうが決定を動かすという事実だった。