0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@kodamaya(Koji Yamada)

空間インデックスを張っているのにフルスキャン — SRID未設定が原因だった話

0
Last updated at Posted at 2026-03-31

はじめに

位置情報を扱うシステムで、MySQLの空間インデックス（Spatial Index）を使っているのになぜかフルテーブルスキャンになる、という問題に遭遇しました。

原因は、SRID（空間参照系ID）を明示的に設定していないと、MySQL 8.0のオプティマイザが空間インデックスを無視するという仕様でした。EXPLAIN ANALYZEを使った原因特定から解決までの流れを書き残しておきます。

環境

  • MySQL 8.0.36
  • テーブル: 約1,000件の位置情報データ（ローカル開発環境）
  • 空間インデックス: 作成済み

本記事のSQL・テーブル名は外部公開用に抽象化しています。

問題の発生

症状

バックグラウンド処理のパフォーマンスを検証していたところ、特定の条件でCPU使用率が高くなるケースがあり、位置情報検索クエリを中心に原因を調査しました。

SELECT * FROM 位置情報テーブル
WHERE ST_Within(
  coordinates,
  ST_Buffer(
    ST_GeomFromText('POINT(【経度】 【緯度】)'),
    【検索半径メートル】 * 180.0 / PI() / 6378137.0
  )
);

まずローカル環境で同じクエリを再現し、EXPLAIN で実行計画を確認してみると、空間インデックスがあるのに使われていませんでした。

テーブル定義

※以下のテーブル定義は説明用に再構成したサンプルです

CREATE TABLE `位置情報テーブル` (
  `id` int NOT NULL AUTO_INCREMENT,
  `resource_id` int unsigned NOT NULL,
  `coordinates` point NOT NULL COMMENT '緯度経度',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_location_resource_id` (`resource_id`),
  SPATIAL KEY `sp_location_coordinates` (`coordinates`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

空間インデックス SPATIAL KEY はちゃんと存在しています。

原因調査

EXPLAIN で実行計画を確認

実行計画（Execution Plan）とは、SQLクエリをDBがどう処理するかの計画です。どのインデックスを使うか、テーブルをどの順序で結合するか、フィルタリングをどこで行うか、といった情報が含まれます。

EXPLAIN をクエリの先頭に付けると、クエリを実際に実行せずに実行計画を確認できます。

EXPLAIN SELECT * FROM 位置情報テーブル
WHERE ST_Within(coordinates, ST_Buffer(ST_GeomFromText('POINT(【経度】 【緯度】)'), ...));

| type | possible_keys | key  | rows  | Extra       |
|------|---------------|------|-------|-------------|
| ALL  | NULL          | NULL | 1,027 | Using where |
  • type: ALL → フルテーブルスキャン
  • possible_keys: NULL → 使用可能なインデックスがない
  • key: NULL → インデックス未使用

空間インデックスがあるのに、完全に無視されています。

EXPLAIN ANALYZE で詳細を確認

EXPLAIN と EXPLAIN ANALYZE の違いはこうです。

コマンド クエリ実行 取得できる情報
EXPLAIN しない 推定値（推定行数、使用インデックスなど）
EXPLAIN ANALYZE する 実測値（実際の実行時間、実際の行数、使用インデックスなど）

EXPLAIN ANALYZE は実際にクエリを実行するので本番環境での使用には注意が必要ですが、より正確にパフォーマンスを分析できます。

EXPLAIN ANALYZE SELECT * FROM 位置情報テーブル
WHERE ST_Within(
  coordinates,
  ST_Buffer(
    ST_GeomFromText('POINT(【経度】 【緯度】)'),
    【検索半径メートル】 * 180.0 / PI() / 6378137.0
  )
);

結果はこうなりました。

-> Filter: st_within(...)  (cost=109 rows=1027) (actual time=8.16..10.4 rows=143 loops=1)
    -> Table scan on 位置情報テーブル  (cost=109 rows=1027) (actual time=1.2..2.43 rows=1027 loops=1)
項目 値 意味
実行時間 10.4ms -
スキャン行数 1,027行 -
結果件数 143件 -
実行計画 Table scan 全件スキャン

1,027行すべてをスキャンして、1行ずつ ST_Within の計算をしています。

SRIDの確認

SELECT ST_SRID(coordinates) FROM 位置情報テーブル LIMIT 1;

0

SRIDの値は 0 でした。

さらにテーブル定義を確認します。

SHOW CREATE TABLE 位置情報テーブル;

`coordinates` point NOT NULL COMMENT '緯度経度'

SRID制約が含まれていません。

SRID制約の有無の見分け方

SHOW CREATE TABLEの結果を比べると違いがわかります。

-- SRID制約なし（問題のある状態）
`coordinates` point NOT NULL COMMENT '緯度経度'

-- SRID制約あり（オプティマイザが空間インデックスを使用する状態）
`coordinates` point NOT NULL /*!80003 SRID 0 */ COMMENT '緯度経度'

/*!80003 SRID 0 */ という記述があれば、SRID制約が設定されています。この記述がない場合、MySQL 8.0のオプティマイザは空間インデックスを無視します。

「データのSRID値」と「カラムのSRID制約」は別物

ここが混乱しやすいところです。

概念 意味 確認方法
データのSRID値 各レコードに格納されているSRID SELECT ST_SRID(coordinates)
カラムのSRID制約 「このカラムにはSRID Xしか入れられない」というスキーマレベルの制約 SHOW CREATE TABLE

上の調査で、ST_SRID() によりデータのSRID値は 0 だと確認できました。しかし SHOW CREATE TABLE ではカラムのSRID制約がありません。

たとえ実際のデータが全部 SRID 0 でも、スキーマに制約がなければオプティマイザは空間インデックスを無視します。

SRID制約なし
  ↓
「このカラムには異なるSRIDのデータが混在する可能性がある」
  ↓
「空間インデックスを使った最適化は安全にできない」
  ↓
フルテーブルスキャン

※ 重要なのは「SRIDの値が何か」ではなく「SRID属性があるか」です。値が0でも、カラムにSRID制約が付いていれば最適化対象になります。

原因

MySQL 8.0 の仕様

MySQL 8.0のドキュメントにはこう書かれています。

オプティマイザは、SRID 属性を持たない（したがって SRID 制限のない）カラムの SPATIAL インデックスを無視します。

— MySQL 8.0 リファレンスマニュアル - SPATIAL インデックス最適化

テーブル定義でSRIDを明示的に指定していないと、空間インデックスは存在していても使われません。

解決策

SRID 0 を明示的に付与する

手順は3ステップです。

  1. 既存の空間インデックスを削除
  2. カラムにSRID 0を明示的に付与
  3. 空間インデックスを再作成
-- 1. 空間インデックスを削除
DROP INDEX sp_location_coordinates ON 位置情報テーブル;

-- 2. SRID の制約をカラムに付与
ALTER TABLE 位置情報テーブル
MODIFY coordinates POINT NOT NULL SRID 0 COMMENT '緯度経度';

-- 3. 空間インデックスを再作成
CREATE SPATIAL INDEX sp_location_coordinates
ON 位置情報テーブル(coordinates);

注意: 空間インデックスがあるカラムのSRIDを変更するには、先にインデックスを削除する必要があります。

SRIDについて
緯度経度を扱う場合、SRID 4326（WGS 84）が推奨されます。本記事では説明の簡略化のため SRID 0 を使用していますが、新規にテーブルを設計する場合は SRID 4326 の使用を検討してください。

テーブル定義の確認

SHOW CREATE TABLE 位置情報テーブル;

`coordinates` point NOT NULL /*!80003 SRID 0 */ COMMENT '緯度経度'

SRID 0 が付与されていることを確認できます。

効果の検証

修正後、同じクエリで EXPLAIN ANALYZE を実行します。

EXPLAIN ANALYZE SELECT * FROM 位置情報テーブル
WHERE ST_Within(
  coordinates,
  ST_Buffer(
    ST_GeomFromText('POINT(【経度】 【緯度】)'),
    【検索半径メートル】 * 180.0 / PI() / 6378137.0
  )
);

-> Filter: st_within(...)  (cost=93.9 rows=208) (actual time=1.87..2.87 rows=143 loops=1)
    -> Index range scan on 位置情報テーブル using sp_location_coordinates
       over (coordinates unprintable_geometry_value)  (cost=93.9 rows=208) (actual time=1.7..2.3 rows=147 loops=1)
項目 修正前 修正後
実行時間 10.4ms 2.87ms
スキャン行数 1,027行（全件） 147行
実行計画 Table scan Index range scan
インデックス 未使用 使用

スキャン行数が約1/7に、実行時間が約1/4に削減されました。

まとめ

問題

  • 空間インデックスが存在するのに、フルテーブルスキャンになる

原因

  • テーブル定義にSRID制約がない
  • MySQL 8.0はSRID制約がないカラムの空間インデックスを無視する

解決策

カラムにSRID 0を明示的に付与

-- SRID の制約をカラムに付与
ALTER TABLE 位置情報テーブル
MODIFY coordinates POINT NOT NULL SRID 0 COMMENT '緯度経度';
-- ※空間インデックスの削除・再作成が必要（手順は「解決策」セクション参照）

効果

  • 10.4ms → 2.87ms（約3.6倍高速化、テストデータ約1,000件）
  • 実行計画が Table scan → Index range scan に変化
  • データ量が多いほど効果は顕著

教訓

  1. EXPLAIN / EXPLAIN ANALYZE はインデックス未使用やフルスキャンの原因特定に手軽に使える
  2. 空間インデックスを使うなら、SRID制約を明示する
  3. インデックスが「ある」ことと「使われる」ことは別問題

参考文献

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?