🚀 データベースパフォーマンス最適化：1秒でわかるIndex索引

プロジェクトがリリースされたばかりの頃、データベースには100人のユーザーと1000件の注文しかないかもしれません。この時、SELECT * FROM orders WHERE user_id = 5のようなSQLを書いても、処理速度は非常に速いです（通常 < 1ミリ秒）。

しかし、SaaS製品が大ヒットしてデータベースに100万件の注文が流れ込むと、ある日突然気づきます：ユーザーのダッシュボードの読み込みに5秒もかかる！ 慌ててサーバーのCPUを確認しますが、実は全く飽和していません。一体何が問題なのでしょうか？

答えは：あなたのデータベースが「全表スキャン（Sequential Scan）」を行っているからです！ この章では、データベースの最重要武器である**Index（索引）**を紹介します。適切な索引を追加すれば、100万件のデータ検索でも1ミリ秒以内で完了できます！

1. 全表スキャンとは？なぜ遅いのか？

1000ページもある英和辞典を想像してください。ただし、中の単語は「完全にランダムに並んでいる」とします。この状態でAppleという単語を探すとしたら、どうしますか？ 1ページ目から順にページをめくっていき、おそらく680ページにあるAppleを見つけるまで探し続けるしかありません。これが**Sequential Scan（全表スキャン）**です。

PostgreSQLでテーブルに索引を作成していない場合、次のクエリを実行すると：

SELECT * FROM users WHERE email = 'test@example.com';

データベースはハードディスクにある数百万件のユーザーデータを1件ずつ読み込んで比較し、該当するユーザーを見つけるまで処理を続けます。これはハードディスクI/Oを極度に消費し、ウェブサイトが遅くなる主な原因です。

2. Index（索引）の仕組み

もし辞書の単語が「A～Zのアルファベット順に並んでいる」場合、Appleを探すのは非常に簡単です。Aのセクションを開けば、すぐに見つかります。

データベースにおいて、Indexはこの辞書の目次に相当します。 emailフィールドに索引を作成すると、PostgreSQLは裏で**B-Tree（平衡木）**と呼ばれるデータ構造を構築します。この木構造は全てのemailをソートし、各emailがハードディスク上のどこにあるか（ポインタ）を記憶しています。

次にemail = 'test@example.com'を検索する時、B-Treeを辿って（Binary Searchと呼ばれる）検索を行い、わずか数ステップ（時間計算量O(log N)）でポインタを見つけ、直接ハードディスクから該当データを取得します。

速度がO(N)からO(log N)に改善されるため、100万件のデータでは数千倍のパフォーマンス差が生まれます！

3. 索引の作成方法（Create Index）

PostgreSQLで索引を作成するのは非常に簡単です。 ordersテーブルがあり、頻繁にuser_idで注文を検索する場合を考えます。

-- シンプルな注文テーブルを作成
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID,
  amount DECIMAL(10,2),
  created_at TIMESTAMP
);

-- ❌ 索引がない場合、データが増えると次のクエリが非常に遅くなる
-- SELECT * FROM orders WHERE user_id = 'xxx-xxx-xxx';

-- ✅ user_idに索引を作成
CREATE INDEX idx_orders_user_id ON orders(user_id);

たったこれだけ！CREATE INDEXを1回実行するだけで、ウェブサイトが復活します！

よくある索引の使用例：

1. ユニーク索引（Unique Index） あるフィールド（例えばユーザーのemail）の重複を防ぎたい場合、UNIQUEを追加します：

CREATE UNIQUE INDEX idx_users_email ON users(email);

これにより検索が高速化されるだけでなく、同時登録時に同じemailアカウントが作成されるのを防ぎます。

2. 複合索引（Composite Index） 2つの条件で同時にフィルタリングする場合（例：特定ユーザーの「特定期間内」の注文を検索）：

SELECT * FROM orders WHERE user_id = 'xxx' AND created_at > '2023-01-01';

この場合、複合索引が最も効果的です：

CREATE INDEX idx_orders_user_time ON orders(user_id, created_at);

⚠️ 複合索引の落とし穴：複合索引には「最左接頭辞の原則」があります。上記の索引はuser_id単独での検索には有効ですが、created_at単独での検索には全く効果がありません！最も頻繁にフィルタリングに使用されるフィールドを括弧内の最初に配置する必要があります。

4. 索引が機能しているか確認する方法（EXPLAIN）

索引を追加しても、PostgreSQLが実際にそれを使用しているかどうかはどう確認すればよいでしょうか？SQLの書き方が悪いと、データベースは全表スキャンを選び、索引を使わないことがあります！

ここで、DBA（データベース管理者）の究極の技であるEXPLAINを学びましょう。

SQLクエリの先頭にEXPLAIN ANALYZEを追加して実行すると、PostgreSQLはデータを返さず、「どのようにこのSQLを実行したか」のレポートを返します。

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = '123e4567-e89b-12d3-a456-426614174000';

レポートの解釈：

❌ Seq Scan（全表スキャン）が表示された場合：

Seq Scan on orders  (cost=0.00..15243.00 rows=12 width=144) (actual time=1.234..45.678)
  Filter: (user_id = '123e4567...'::uuid)

これは索引が適切に作成されていないか、データベースが全表スキャンの方が速いと判断したことを意味します。

✅ Index ScanまたはBitmap Index Scanが表示された場合：

Index Scan using idx_orders_user_id on orders  (cost=0.29..8.30 rows=12 width=144) (actual time=0.012..0.025)
  Index Cond: (user_id = '123e4567...'::uuid)

おめでとうございます！索引が完璧に機能しています！actual timeが0.025ミリ秒しかかかっていないことに注目してください！

5. 索引は万能薬か？（The Cost of Indexes）

索引がこんなに素晴らしいなら、全てのフィールドに索引を追加すればよいのでしょうか？ 絶対にダメです！大惨事になります！

索引の代償（Trade-offs）：

ディスク容量の消費：B-Tree索引自体もデータです。テーブルが大きい場合、索引がテーブル本体よりも大きくなる可能性があります！
書き込み速度の低下（INSERT / UPDATE）：これが最も深刻です。新しい注文を追加するたびに、データベースはデータをハードディスクに書き込むだけでなく、「全てのB-Tree索引ディレクトリ」を更新しなければなりません。1つのテーブルに10個の索引がある場合、1回の書き込み操作で実際には11回の操作が実行されます。書き込みパフォーマンスが崩壊します！

索引作成のベストプラクティス（Best Practices）：

変更されない履歴データ：大胆に索引を追加できます。
書き込み頻度の高いテーブル（例：ログ、追跡データ）：索引を最小限に抑えないと、書き込みが詰まります。
Primary Key（主キー）とForeign Key（外部キー）：これらにはほぼ必ず索引を追加すべきです！特にJOINを行う場合、外部キーに索引がないとデータベース全体が遅くなります。
使用されていない索引を定期的に削除：恐れずに削除しましょう。データは消えません。

IndexとEXPLAINをマスターすれば、「CRUDしか書けない初心者」というレッテルを剥がし、上級バックエンドエンジニアの仲間入りを果たせます！