インデックスショットガン: 3つのアンチパターンと回避策

『SQLアンチパターン』12章「インデックスショットガン」では、推測に基づいてインデックスを使うこと で発生する典型的なミスを 3 つに分類しています。

「インデックスを多めに張っておけば安心」「とりあえず気になる列に全部インデックス」という設計判断は、書き込み性能の劣化やストレージ消費・メンテナンスコスト増を招きます。本記事では 3 つのアンチパターンと具体的な回避策を整理します。

インデックスショットガンとは

推測に基づいてインデックスを使うこと

によって、以下 3 つのミスのどれかが起こる、という指摘です（『SQLアンチパターン』12章）。

インデックスをまったく定義しないか、少ししか定義しない
インデックスを多く定義し過ぎるか、役立たないインデックスを定義する
インデックスを活用しないクエリを実行する

「どれか1つ」ではなく、無計画に作ると複数同時に陥ることが多いのが厄介です。

アンチパターン 1: 全く張らない／少なすぎる

「インデックスはオーバーヘッド」と過剰反応して避けてしまうパターンです。

よくある誤解

「INSERT が遅くなるからインデックスは最小限に」
「ストレージを食うから無駄なインデックスは作らない」
「主キーがあれば十分」

なぜアンチパターンか

実際には クエリ発行回数の方がテーブル更新回数より何百倍も多い のが普通で、書き込みオーバーヘッドは正当化されます。Web サービスで考えれば、1 件のレコード作成に対して数百〜数千の参照が走るのが一般的です。

書き込みコストを恐れて検索を遅いまま放置するのは、優先順位を間違えた最適化と言えます。

回避策

「WHERE / ORDER BY / GROUP BY / JOIN で頻繁に使われる列」にはインデックスを付けることを基本姿勢にする
インデックスを増やすことを恐れるより、未使用インデックスを定期検出して削除 するワークフローに切り替える
インデックスの判断基準は基礎から学ぶインデックス作成時のチェックポイントを参照

アンチパターン 2: 張りすぎ・役立たないインデックスを定義する

逆に、推測で 全カラムにインデックスを張る などの過剰インデックスです。

よくあるパターン

「念のため全カラムにインデックス」
長い VARCHAR にインデックス（インデックスサイズが膨らむ）
主キー以外の 冗長なインデックス（(a) と (a, b) の両方を作るなど）
UPPER(last_name) 用と LOWER(last_name) 用を 両方作る
未使用のまま放置されたインデックス

なぜアンチパターンか

INSERT/UPDATE/DELETE の遅延（書き込みコスト参照）
ストレージ消費の増加
メンテナンスコストの増加
バックアップ・リストア時間の増加
オプティマイザが「どのインデックスを使うか」の判断に迷い、誤った実行計画を選ぶこともある

特に未使用インデックスの放置は、書き込み性能の慢性的な劣化要因になります。

同じ列の関数インデックス重複は統一する

UPPER(last_name) 用と LOWER(last_name) 用を両方作るのは典型的なミスです。アプリケーション全体で同じ関数を使うように統一 すれば 1 つで済みます。

未使用インデックスの検出

MySQL で未使用インデックスを検出:

SELECT object_name, index_name
  FROM sys.schema_unused_indexes
 WHERE object_schema = 'your_schema'
 ORDER BY object_name, index_name;

注意点:

performance_schema の統計情報は DB 起動時にリセット されるので、有効化してすぐに削除せず、ある程度ためてから判断する
月次バッチでしか使わないインデックス もあり得る
FOREIGN KEY 制約に必要なインデックス は出てくることがあるので削除前に要確認

PostgreSQL で未使用インデックスを検出:

SELECT schemaname, relname, indexrelname, idx_scan
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
   AND indexrelname NOT LIKE '%_pkey';

idx_scan = 0 のものが未使用候補。プライマリキーは除外。pg_stat_reset() で統計をリセットしてから一定期間モニタリングするのが安全です。

安全な削除手順

未使用と判断したインデックスをいきなり DROP するのは怖いので、MySQL 8.0+ では 不可視インデックス（Invisible Index） で「使わない状態」を擬似的に作ってから削除するのが定石です。

詳細は MySQL 8.0 不可視インデックスで削除を安全に検証するを参照してください。

アンチパターン 3: インデックスを活用しないクエリ

せっかくインデックスを作っても、SQL の書き方によってインデックスが使えないケースがあります。

よくあるパターン

関数適用: WHERE UPPER(last_name) = 'WINAND' → 関数で列を包むとインデックスは効かない
演算: WHERE age / 2 = 15 → 列に演算するとインデックスは効かない
中間/後方一致 LIKE: WHERE name LIKE '%aa%'、WHERE name LIKE '%aa'
暗黙の型変換: 文字列列に数値を渡すなど
否定条件: WHERE flag <> 1、WHERE id NOT IN (...)
OR: ケースによってはインデックスが使えない

なぜアンチパターンか

「インデックスを作った＝速い」という錯覚が起きやすく、本番でフルスキャンが走っていることに気付かないまま運用されることがあります。EXPLAIN で type=ALL が出ているクエリは要注意です（EXPLAIN の読み方参照）。

回避策

インデックスを利用するときは、条件式の左辺は裸

これがインデックスまわりのチューニングの基本の「き」です（達人に学ぶSQL徹底指南書 1-11）。式や関数の適用は 右辺で行い、左辺のテーブル列はそのままにする。

-- 悪い例: 列に関数を適用
WHERE UPPER(last_name) = 'WINAND'

-- 良い例: リテラル側を変換
WHERE last_name = 'WINAND'

-- 悪い例: 列に演算
WHERE age / 2 = 15

-- 良い例: 右辺で演算
WHERE age = 30
WHERE age = 60 / 2

-- 悪い例: 列を変換
WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '2024-01-01'

-- 良い例: 範囲条件に書き換え
WHERE sale_date >= DATE '2024-01-01'
  AND sale_date <  DATE '2024-01-02'

詳細な検証パターンは【SQL】インデックスが利用されない検索条件についてEXPLAINで検証してみたを参照してください。

補足: ORM の「全列 UPDATE」問題

ORM が「変更のなかった列も含めて全列 UPDATE」を発行する場合、対象列を含む全インデックスが無駄に書き換えられます。アンチパターン2（過剰なインデックス書き換え）の連鎖反応として、運用後に書き込み性能が劣化する原因になります。

SQL ロギングを開発時に有効化して 生成 SQL を確認する のが推奨運用です。詳細はインデックスは書き込みにいくら払っているかを参照してください。

補足: インデックスは規格ではない

ANSI SQL 規格は インデックスについて何も規定していません。CREATE INDEX 構文も標準ではなく、各 DBMS ベンダーの独自実装です。インデックスのメンテナンス・自動最適化・実行計画レポート（EXPLAIN 等）にも標準は存在しません。

使用している DBMS のドキュメントをよく読むこと

これが最終的な拠り所になります（『SQLアンチパターン』12章）。

都市伝説に振り回されない

インデックスまわりには根強い都市伝説があり、これらに振り回されると不要な作業や誤った判断を招きます。

「インデックスは劣化する／再構築すれば速くなる」

B-tree はバランスを常に保つので、深さがどんどん増えていくことはない。再構築の効果は限定的（INDEX UNIQUE SCAN レベルで 0〜2% 改善程度、Use The Index, Luke! の著者 Markus Winand の主張）。

「劣化しているように見える」のは、リーフノードチェーンの走査範囲やテーブルアクセス数が大きいだけで、インデックス構造の問題ではありません（B-tree内部構造参照）。

「最も選択性の高い列を最初に」

一般則として誤り。正しくは「アプリケーションの WHERE で多く出る列の組み合わせを最大限カバーする列順」です。詳細は複合インデックスの順番を正しく作る。

「Oracle は NULL にインデックスを作れない」

正しくは「インデックス対象の全列が NULL の行をインデックスに含めない」です。NULL になり得ない列または定数式を 2 列目に追加すれば NULL 行もインデックスに入ります。

CREATE INDEX with_null ON table_name (nullable_column, 'X');

「動的 SQL は遅い」

動的 SQL と静的 SQL は排他ではありません。本当の悪手は「バインドパラメータを使わずに値を直接埋め込むこと」です。プリペアドステートメント＋バインドパラメータで動的 SQL を組み立てれば、SQL インジェクションを防ぎつつ実行計画キャッシュも活用できます。

まとめ

インデックスショットガン = 推測でインデックスを作る／作らないことで起きる3つのミス
1. 全く張らない／少なすぎる
2. 張りすぎ・役立たないインデックスを作る
3. インデックスを活用しないクエリを書く
「インデックスを増やすのを恐れる」より「未使用を定期検出して削除する運用」に切り替える
同じ列の関数インデックスはアプリケーションで関数を統一して 1 つに集約
削除前は MySQL 8.0+ の不可視インデックスで観察してから DROP
「左辺は裸」「アクセス述語と整合した列順」を守る
都市伝説（劣化／選択性の迷信／NULL／動的 SQL）に振り回されない
ANSI SQL にインデックス規定はない。各 DBMS のドキュメントを読む