SQLインデックス — クエリが遅い理由と、実際にどうすべきか

あなたのクエリはローカルホストで2msで実行され、500行を処理します。しかし、生産環境で300万行の場合、9秒かかり、ユーザーはスピンを眺めています。クエリ自体は問題ないかもしれませんが、スキーマが問題です。インデックスが必要であり、理解が必要です。 なぜ 次回このようなことが起こったときに、予測をやめましょう。

データベースがインデックスがない場合にしていること

Bツリーインデックスは、列（または列の組み合わせ）の並べ替えされたコピーであり、データベースが二分検索でO(log n)時間で検索できるようにバランスの取れた木として保存されています。インデックスがない場合、データベースは 全テーブルスキャン — すべての行を読み込んで一致を探す必要があります。300万行の場合、300万行の読み込みが必要です。適切な列にインデックスがある場合、20～30の木ノードの探索で一致行を見つけることができます。

電話帳をイメージしてみてください。アルファベット順に「スミス、ジョン」を探すのは数秒ですが、すべての項目を1ページから読み込んで「ジョン」という名前のすべての人を探すのは永遠にかかります。インデックスはアルファベット順の並びです。データベースはそれを使って、関連する行に直接ジャンプするようにします。

重要な点は、インデックスはテーブルの隣にディスク上に存在しています。データベースは自動的に書き込み時にインデックスを維持します。読み取りには無料ですが、INSERT、UPDATE、DELETEのたびにコストがかかります。そのため、すべての列にインデックスを付けることはできません。

EXPLAINで問題を特定する

何かを変更する前に、遅いクエリを実行してください。 EXPLAIN MySQLで見たいのは次の2つのフィールドです：

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 2847391 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+

タイプ：ALL

• — 全テーブルスキャン。10万行を超えるとほぼ常に悪いです。 行数：2847391
• — オプティマイザが284万行を調べて結果を返すと予測しています。 インデックスを追加して再実行します：

タイプ：ref

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys          | key                    | key_len | ref   | rows | Extra |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_id | idx_orders_customer_id | 4       | const |   12 |       |
+----+-------------+--------+------+------------------------+------------------------+---------+-------+------+-------+

行数：12 と 。これが解決策です。284万行を調べる → 12行。PostgreSQLでは、

（実際のクエリを実行）を使って正確な時間の数字を取得します： EXPLAIN ANALYZE 8934ms → 0.3ms。これは単なる例ではなく、数百万行のテーブルで欠落したインデックスがもたらす現実的な影響です。

-- Before index
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Seq Scan on orders  (cost=0.00..89432.00 rows=14 width=156)
                    (actual time=142.341..8934.221 rows=12 loops=1)
  Filter: (customer_id = 12345)
  Rows Removed by Filter: 2847379
Planning Time: 0.4 ms
Execution Time: 8934.8 ms

-- After: CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Index Scan using idx_orders_customer_id on orders
                    (cost=0.43..92.15 rows=12 width=156)
                    (actual time=0.042..0.198 rows=12 loops=1)
  Index Cond: (customer_id = 12345)
Planning Time: 0.5 ms
Execution Time: 0.3 ms

複合インデックスと左端前件ルール

WHERE句に複数の列がある場合、複合インデックスが必要になり、列の順番が重要になります。

左端前件ルール

SELECT * FROM orders
WHERE customer_id = 12345
  AND status = 'pending'
  AND created_at > '2024-01-01';

の ：データベースは左端の列から始まり、右へ進みますが、ギャップに到達すると無効になります。インデックスが— インデックスを使用 (a, b, c):

• WHERE a = 1 — インデックスの2つの列を使用
• WHERE a = 1 AND b = 2 — 全インデックスを使用
• WHERE a = 1 AND b = 2 AND c = 3 — インデックスを使用せず（スキップされた列a）
• WHERE b = 2 — aの部分のみを使用し、cを無視
• WHERE a = 1 AND c = 3 実用的な順番ルール：

等値条件を最初に、範囲条件を最後に。 範囲条件（）は、次の列の使える前件を破壊します。したがって：>, <, BETWEEN, LIKE 'prefix%'また、選択性についても考えましょう：選択性の高い列（重複値が少ない列）をインデックスの先頭に置くべきです。もし

-- Good: equality columns first, range column last
CREATE INDEX idx_orders_compound ON orders (customer_id, status, created_at);

-- This query uses all three columns of the index:
SELECT * FROM orders
WHERE customer_id = 12345   -- equality, goes first
  AND status = 'pending'    -- equality, goes second
  AND created_at > '2024-01-01'; -- range, goes last

-- Bad ordering for this query: range column in the middle
-- CREATE INDEX idx_bad ON orders (customer_id, created_at, status);
-- The status filter won't use the index because created_at is a range predicate

が50,000の異なる値を持ち、 customer_id が3つなら、 status を最初に置くべきです — これにより結果セットをより早く絞り込みます。 customer_id インデックスが悪化する場合

インデックスは無料ではありません。すべてのインデックスは書き込みパフォーマンスとディスクスペースを消費します。データベースはそのテーブルのすべてのINSERT、UPDATE、DELETEに対してすべてのインデックスを更新します。書き込みが重いテーブルでは、過剰なインデックスが実際にパフォーマンスを損ないます。以下のような場面でインデックスは失敗または逆効果になります：

低選択性の列。

• ブール値または 3つの異なる値を持つ列にインデックスを付けることは、オプティマイザが無視する傾向があります。もしクエリがインデックスを通じて10～15%以上の行を返す場合、データベースは通常、テーブルスキャンを好む — インデックスの検索コストがそれほど価値がないからです。EXPLAINでインデックスが実際に使用されているかを確認してください。 status 書き込みが重いテーブル。
• ログテーブル、イベントテーブル、監視テーブル — ここでは慎重に扱うべきです。10個のインデックスを持つテーブルで1時間に数百万回の書き込みを行う場合、そのインデックスに10倍の書き込みオーバーヘッドを支払っています。 使用されていないインデックス。
• PostgreSQLでは、 — 最後に統計リセット後の各インデックスが使用された回数を示します。スキャン回数が0のインデックスは純粋な過剰負荷です。MySQLではパフォーマンススケミア（ pg_stat_user_indexes ）で追跡されます（MySQL 5.7以降）。 idx_scan オプティマイザが間違っている。sys.schema_unused_indexes クエリプランナーは常に正しいとは限りません。場合によっては、インデックスを選び、全スキャンよりも遅い場合があります。
• がこの問題を検出します — 予測行数と実際の行数を比較します。MySQLでは でヒントを提供できます；PostgreSQLでは EXPLAIN ANALYZE (生産環境では残さない) をテストできます。 USE INDEX (idx_name)インデックスかクエリのリライトか？実際のトレードオフ SET enable_seqscan = off 場合によってはインデックスが解決策です。場合によってはクエリ自体が問題であり、インデックスはそれを隠しているだけです。次のパターンでは、クエリをリライトすべきです（インデックスを追加する代わりにまたは追加とともに）：

インデックスされた列に関数を適用する

列に関数を包むと、標準的なインデックス使用が妨げられます。インデックスは

の値を保存しており、

-- Will NOT use an index on created_at — the function result isn't indexed:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Rewrite to use the index:
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

の値は異なるものです。PostgreSQLでは表現インデックス（ created_at）をサポートしていますが、クエリをリライトするのはよりクリーンで、すべての環境で動作します。 YEAR(created_at) 熱い読み取りパス用のカバーインデックスCREATE INDEX ON orders (YEAR(created_at))カバーインデックス

クエリがすべて必要な列を含んでいるため、データベースは実際にテーブル行を読み込む必要がなく、すべてインデックスから答えを出します。これは、読み取りが非常に多いテーブルでは、通常のインデックスよりも大幅に速い場合があります：

あ パターンマッチ 先頭のワイルドカードはBツリーインデックスで修正できません。サフィックスやサブ文字列検索が必要な場合は、フルテキストインデックス（

-- Query that only needs id, status, total:
SELECT id, status, total FROM orders WHERE customer_id = 12345;

-- Regular index: database finds matching rows via index, then reads each row from table
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Covering index: database reads everything it needs from the index alone
CREATE INDEX idx_orders_cover ON orders (customer_id, id, status, total);
-- Note: customer_id first (the WHERE column), then the SELECT columns

MySQLで、

-- Uses the index (prefix match — database knows where to start):
SELECT * FROM users WHERE email LIKE 'john%';

-- Does NOT use the index (leading wildcard — database doesn't know where to start):
SELECT * FROM users WHERE email LIKE '%@gmail.com';

PostgreSQLで）またはElasticsearchなどの外部検索システムが必要です。FULLTEXT N+1クエリ GIN は迅速なパスだが、属性の扱いが不一致であり、エッジケースでデータを失う可能性がある。SOAPレスポンスのプロダクション用途では、 pg_trgm インデックスはN+1クエリを解決しません。もしORMが200個の注文を1つのクエリで取得し、それぞれの注文に対して個別のクエリで顧客名を取得する場合、201個のクエリが発生します。それぞれが速い場合でも、問題は量です。JOINまたはバッチクエリで解決すべきです。インデックスの数がクエリ数を変えることはできません。

もう一つ：遅いクエリを分析する前にクエリを整理してください

遅いクエリに対してEXPLAINを実行する前に、SQLを明確にフォーマットしておくと良いです。200文字の1行でインデントなしのクエリは、構造を理解するのが難しいです。構造を一目で見られるようにするための

SQLフォーマッター（IO Tools）

はMySQL、PostgreSQL、SQL Server、その他の言語をサポートしており、ログやORMのデバッグ出力からクエリを貼り付けて、構造を一瞬で確認できるようにします。 実用的なチェックリスト インデックスを追加する前に確認すべき項目です。問題をまず確認してください。

The practical checklist

• でWindows上で EXPLAIN (MySQL) または
• (PostgreSQL) — 通常、問題の場所です。 type: ALL EXPLAINでの見積もり。数百万行を調べて数十行を返す = 欠落したインデックス。 Seq Scan 複合インデックスでは：等値条件を最初に、範囲条件を最後に。
• 確認 rows 選択性の高い列は複合インデックスの先頭に置くべきです。
• 低選択性の列にインデックスを付ける前に、オプティマイザが実際にインデックスを使用しているかを確認してください。
• 定期的に使用されていないインデックスを監視してください：
• (PostgreSQL)、
• (MySQL)。 pg_stat_user_indexes WHEREに関数が列を包んでいる場合、クエリをリライトすべきです。 sys.schema_unused_indexes 読み取りが重い熱いパスで、小さな列セットがある場合、カバーインデックスを検討してください。
• N+1はクエリアーキテクチャの問題であり、インデックスの問題ではありません。
• SQLインデックス — なぜクエリが遅いのか、そして実際にどうすべきか 2
• SQLインデックス — なぜクエリが遅いのか、そして実際にどうすべきか 1

あなたも好きかもしれません