CREATE INDEXの使い方
CREATE INDEXの使い方 とは
CREATE INDEXで検索速度を向上させる方法を、実際に操作しながら学びます。本レッスンでは、CREATE INDEXの使い方 の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。
なぜ重要か
インデックスの仕組みを理解しても、実際に SQL でどう書けばいいのかが分からないと現場では使えません。CREATE INDEX には単一列・複合・一意・関数ベース・全文検索などのバリエーションがあり、用途ごとに最適な書き方が違います。さらに、本番運用では「ロック時間」「オンライン DDL」「降順インデックス」といった運用面の知識も欠かせません。
ここでは CREATE INDEX の文法と、現場で使う代表的なパターンを整理します。
基本構文
もっとも基本的な形は次の通りです。
SQL クエリ
CREATE INDEX idx_users_email ON users (email);複数列を含めるとそれは 複合インデックス になります。
SQL クエリ
CREATE INDEX idx_orders_user_ordered ON orders (user_id, ordered_at);この順序は重要で、WHERE user_id = 42 AND ordered_at >= '2026-01-01' のような検索に効きますが、WHERE ordered_at >= '2026-01-01' 単独では効きません (左端優先の原則)。
クエリと CREATE INDEX の対応
クエリのパターンからインデックスを設計する流れを図で見ます。
基本は WHERE → ORDER BY の順 にカラムを並べることです。等価条件のカラムを先、範囲条件やソートのカラムを後にすると、B+ ツリーをきれいに辿れます。
代表例 インデックスのバリエーション
用途別に代表的な書き方を見てみましょう。
SQL クエリ
-- 1. 単一列の通常インデックス
CREATE INDEX idx_products_category ON products (category_id);
-- 2. 一意インデックス (重複を防ぐ)
CREATE UNIQUE INDEX uq_users_email ON users (email);
-- 3. 複合 + 降順 (新しい順の検索を高速化)
CREATE INDEX idx_orders_user_recent ON orders (user_id, ordered_at DESC);
-- 4. カバリングインデックス (SELECT 列まで含める)
CREATE INDEX idx_users_email_name ON users (email, name);4 番目の カバリングインデックス は、セカンダリ + クラスタードの 2 段探索を 1 段に減らす強力なテクニックです。SELECT name FROM users WHERE email = ? がインデックスだけで完結します。
削除と一覧確認
インデックスは状況によって作り直しが必要になります。
SQL クエリ
-- 既存インデックスを確認
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
-- 不要になったインデックスを削除
DROP INDEX idx_orders_old;本番でインデックスを足すときは、テーブルサイズによっては数分から数時間ロックがかかる場合があります。PostgreSQL では
CREATE INDEX CONCURRENTLYなどロックを抑える選択肢がありますが、必ず事前に検証環境で時間を計測してから本番に当てましょう。
不要なインデックスを見つける
インデックスは無料ではありません。INSERT/UPDATE のたびに更新コストが乗るため、使われていないインデックスは捨てるべきです。information_schema や performance_schema を見れば、各インデックスがいつ最後に使われたかが分かります。
SQL クエリ
-- TiDB/MySQL 8 でインデックス使用統計を見る
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_star ASC;注意 「将来使うかもしれない」という理由でインデックスを残すのは典型的な失敗です。本当に必要になったときに作り直すコストの方が、無駄なインデックスを抱え続けるコストより安いことが多いです。
「インデックスは資産ではなく負債である」と考えると判断が楽になります。プラスの効果は計測しないと分からない一方、書き込み遅延・ストレージ・運用工数というコストは確実に支払い続けるからです。最小のセットで最大の効果を狙うのが物理設計者の腕の見せ所です。
まとめ
CREATE INDEX 名前 ON テーブル (列, ...)が基本構文- 複合インデックスは WHERE → ORDER BY の順で並べる
- UNIQUE / DESC / カバリングなどの派生形を使い分ける
- 本番投入時はオンライン DDL の可否と所要時間を必ず検証
- 使われていないインデックスは早めに削除する
演習
クエリの一覧から、最適なインデックス定義を読み取りましょう。
次のレッスン
次は インデックス設計の注意点 です。SQLのインデックス設計における注意点と、パフォーマンス改善のヒントを解説します。
事前確認 — 進む前に次の 3 つができることを確認しましょう。
- CREATE INDEX の要点を自分の言葉で説明できる
- このレッスンの最小コード (または操作手順) を見ずに書ける
- 練習問題やクイズで間違えた箇所を読み直して理解した
理解度チェック (30 秒)
Q. CREATE INDEX とは何か、1 文で説明してください。
A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。
関連レッスン
テーブル構造
schema.sql
CREATE TABLE query_log (
id INT PRIMARY KEY,
query_pattern VARCHAR(200) NOT NULL,
exec_count INT NOT NULL,
avg_ms INT NOT NULL
);
INSERT INTO query_log VALUES
(1, 'SELECT * FROM orders WHERE user_id = ? ORDER BY ordered_at DESC', 50000, 250),
(2, 'SELECT * FROM products WHERE category_id = ?', 80000, 30),
(3, 'SELECT * FROM users WHERE email = ?', 100000, 5),
(4, 'SELECT COUNT(*) FROM orders WHERE status = ?', 200, 1200),
(5, 'SELECT * FROM reviews WHERE product_id = ? AND rating >= ?', 30000, 180);期待される出力
| id | query_pattern | total_load |
|---|---|---|
| 1 | SELECT * FROM orders WHERE user_id = ? ORDER BY ordered_at DESC | 12500000 |
| 5 | SELECT * FROM reviews WHERE product_id = ? AND rating >= ? | 5400000 |
| 2 | SELECT * FROM products WHERE category_id = ? | 2400000 |