インデックス設計の注意点
インデックス設計の注意点 とは
SQLのインデックス設計における注意点と、パフォーマンス改善のヒントを解説します。本レッスンでは、インデックス設計の注意点 の基本から実際の使いどころまでを整理し、現場で迷わず使える形に落とし込みます。
なぜ重要か
インデックスを張れば速くなる、という素朴な感覚で乱用すると、書き込みが詰まり、ディスクが膨らみ、最終的にはオプティマイザが誤判定して逆に遅くなることまであります。インデックス 設計は 足し算ではなく引き算 の発想が必要で、「どうやって速くするか」だけでなく「どこまでなら張っていいか」「どう運用するか」を考えることが本当の 物理設計 です。
ここではインデックス設計でよくある落とし穴と、その対処法を整理します。
インデックスの代償
インデックスを 1 つ追加すると、3 つの代償が発生します。
つまりインデックスは「読み取りを書き込みに対して有利にする取引」です。書き込みが大きくに多いログテーブルや、ほぼ全件 SELECT する小テーブルにはインデックスを足してもメリットが少ないことが多いです。
代表例 カーディナリティの低いインデックス
ブール値やステータスのような 値の種類が少ない (カーディナリティ が低い) 列 にインデックスを張っても効きません。
SQL クエリ
-- NG カーディナリティの低い列だけのインデックス
CREATE INDEX idx_users_is_active ON users (is_active);
-- このクエリはインデックスを使わずフルスキャンになりがち
SELECT * FROM users WHERE is_active = TRUE;is_active = TRUE がテーブルの 90% を占めるなら、オプティマイザはインデックスより全件スキャンの方が速いと判断します。こういう場合は カーディナリティの高い列を先頭にした 複合インデックス にするのが正解です。
SQL クエリ
-- OK 高カーディナリティ列を先頭に
CREATE INDEX idx_users_email_active ON users (email, is_active);代表例 重複インデックスの掃除
同じカラムに複数のインデックスが残っていると、書き込みコストが二重・三重にかかります。
SQL クエリ
-- 既に idx_orders_user (user_id) があるのに、追加で同じ意味のインデックスが作られている
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';
-- idx_orders_user (user_id)
-- idx_orders_user_only (user_id)
-- idx_orders_user_status (user_id, status)idx_orders_user と idx_orders_user_only は完全な重複です。さらに、idx_orders_user_status があれば idx_orders_user も左端マッチで代用できるため、シングル版は捨てて構いません。
SQL クエリ
DROP INDEX idx_orders_user_only;
DROP INDEX idx_orders_user;複合インデックス
(A, B, C)があれば、(A)と(A, B)の単体インデックスは不要です。左端優先で代用できるためです。重複インデックスは思っているより多く残っているので、定期的に棚卸ししましょう。
NULL とインデックス
PostgreSQL のB-treeインデックスも NULL を扱えるため、条件次第で IS NULL 検索にインデックスが使われます。ただし設計上、NULL を許す列は意味があるか毎回考えるべきです。
SQL クエリ
-- NULL を多く含む列にインデックスを張っても、NULL の比率次第で効かない
CREATE INDEX idx_orders_canceled_at ON orders (canceled_at);
-- ほとんど NULL 以外だけを探すなら、部分インデックスを検討
CREATE INDEX idx_orders_canceled_at_not_null
ON orders (canceled_at)
WHERE canceled_at IS NOT NULL;注意 インデックスの大きさは元テーブルの 10-30% 程度になります。あるテーブルに 5 個のインデックスを張ると、ストレージは 1.5-2.5 倍になります。ディスク容量とバックアップ時間に直接効くため、本番投入前に必ず見積もりましょう。
「Indexes are like sandwiches. They are not free, and too many of them will make you sick」 — DBA コミュニティのジョークですが本質を突いています。必要な 1 枚があれば十分なのに、何枚も重ねて満腹になってから「動けない」と気づくのが、インデックス乱発の悲劇です。
インデックス棚卸しの実務手順
本番でインデックスを整理するときの手順を一例として挙げます。
SQL クエリ
-- 1. 使われていない可能性があるインデックスを抽出
SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname, indexrelname;
-- 2. 重複の疑いがあるインデックス定義を一覧化
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexdef;
-- 3. 削除候補をリスト化してレビュー → 検証環境で DROP → 本番反映このフローを四半期に一度回せば、インデックスが暴れたままにならずに済みます。
まとめ
- インデックスは読み取りと書き込みのトレードオフ
- カーディナリティが低い列を単体でインデックス化しても効かない
- 重複インデックスは棚卸しで定期削除する
- NULL 比率の高い列は部分インデックスを検討
- インデックスはストレージとバックアップ時間にも効くので見積もりが必須
演習
インデックス棚卸しのため、使われていないインデックスを抽出する SQL を書いてみましょう。
次のレッスン
次は 物理設計クイズ です。物理設計に関する理解度をクイズでチェック！データベース設計の基礎を復習しましょう。
事前確認 — 進む前に次の 3 つができることを確認しましょう。
- インデックス注意点 の要点を自分の言葉で説明できる
- このレッスンの最小コード (または操作手順) を見ずに書ける
- 練習問題やクイズで間違えた箇所を読み直して理解した
理解度チェック (30 秒)
Q. インデックス注意点 とは何か、1 文で説明してください。
A. 本文の「このレッスンで分かること」または冒頭の説明文を見直し、自分の言葉で要約できれば OK。詰まったら本レッスンの最初の H2 セクションを読み返してみましょう。
関連レッスン
テーブル構造
schema.sql
CREATE TABLE index_usage_stats (
schema_name VARCHAR(50) NOT NULL,
table_name VARCHAR(50) NOT NULL,
index_name VARCHAR(50) NOT NULL,
use_count BIGINT NOT NULL,
size_mb DECIMAL(10, 2) NOT NULL
);
INSERT INTO index_usage_stats VALUES
('app', 'orders', 'idx_orders_user_ordered', 1500000, 120.50),
('app', 'orders', 'idx_orders_user_only', 0, 80.20),
('app', 'orders', 'idx_orders_status_only', 5, 60.10),
('app', 'users', 'uq_users_email', 800000, 30.00),
('app', 'users', 'idx_users_is_active', 0, 25.50),
('app', 'products', 'idx_products_category', 200000, 15.00),
('app', 'reviews', 'idx_reviews_old', 0, 45.80);期待される出力
| table_name | index_name | use_count | size_mb |
|---|---|---|---|
| orders | idx_orders_user_only | 0 | 80.20 |
| orders | idx_orders_status_only | 5 | 60.10 |
| reviews | idx_reviews_old | 0 | 45.80 |
| users | idx_users_is_active | 0 | 25.50 |