データベースインデックスを理解してパフォーマンスを向上させる

データベースクエリがミリ秒で完了すべきところ、数秒もかかっている。原因は?インデックスの欠如または不適切な設計です。データベースインデックスは、数百万行をスキャンするのか、必要なデータに直接ジャンプするのかの違いを生み出します。しかし、間違った設計は書き込みを遅くし、ストレージを肥大化させる可能性があります。

本記事では、B-treeインデックスと複合インデックスから、カバリングインデックスとクエリプランなどの高度な技術まで、データベースインデックスの基礎を解説します。JSONやテキストフィールドを含む最新のデータ型のインデックス方法を学び、最も重要なこととして、最新データベースにおける過剰インデックス化というパフォーマンスの罠を回避する方法を習得できます。

データベースインデックスが実際に行うこと

1000万行を持つusersテーブルへのクエリを考えてみましょう:

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

インデックスがない場合、PostgreSQLやMySQLはすべての行をチェックする必要があります—これがフルテーブルスキャンです。emailにインデックスがあれば、データベースはB-tree構造を使用して一致する行に直接ジャンプし、通常、検索時間をO(n)からO(log n)に短縮します。

インデックスは本質的に、特定の列のソート済みコピーと完全な行へのポインタです。トレードオフは次の通りです:読み取りの高速化と引き換えに、書き込みの低速化(インデックスを更新する必要がある)と追加のストレージ(通常、インデックスごとにテーブルサイズの10〜20%)が発生します。

B-treeインデックス:データベースの主力

B-treeインデックスは、ほとんどのデータベースクエリを支えています。データをバランスの取れたツリー構造で維持し、各ノードには複数のソート済みキーが含まれます。この設計はディスクI/Oを最小化します—ディスクアクセスはメモリアクセスよりも桁違いに遅いため、これは非常に重要です。

CREATE INDEX idx_users_created_at ON users(created_at);

B-treeが得意とするもの:

• 等価チェック(WHERE status = 'active')
• 範囲クエリ(WHERE created_at > '2024-01-01')
• ソート(ORDER BY created_at)
• プレフィックス検索(WHERE email LIKE 'john%')

PostgreSQLとMySQL InnoDBは、すべてのデータがリーフノードに存在するB+tree変種を使用し、範囲スキャンをより効率的にします。MongoDBもインデックスに同様のB-tree構造を使用していますが、ドキュメントデータベースはネストされたフィールドのインデックス化により複雑さが増します。

複合インデックス:列の順序が重要

複合インデックスは複数の列をカバーしますが、列の順序が重要です:

CREATE INDEX idx_events_user_date ON events(user_id, created_at);

このインデックスがサポートするもの:

• WHERE user_id = 123
• WHERE user_id = 123 AND created_at > '2024-01-01'

しかし、サポートしないもの:

• WHERE created_at > '2024-01-01'(user_idなし)

インデックスは左から右に機能します。等価条件に使用される列を最初に配置し、次に範囲条件を配置します。ユーザーと日付範囲でイベントをフィルタリングするAPIエンドポイントの場合、この複合インデックスにより各列に個別のインデックスを作成する必要がなくなります。

カバリングインデックスとクエリプラン

カバリングインデックスは、クエリに必要なすべての列を含み、インデックスオンリースキャンを可能にします:

-- PostgreSQLの例
CREATE INDEX idx_orders_covering 
ON orders(user_id, status, created_at) 
INCLUDE (total_amount);

次のクエリの場合:

SELECT total_amount FROM orders 
WHERE user_id = 123 AND status = 'completed';

データベースはテーブルに一切アクセスしません—すべてがインデックスから取得されます。EXPLAINを使用して確認します:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

PostgreSQLでは「Index Only Scan」、MySQLでは「Using index」を探します。クエリプランは、インデックスが実際に使用されているかを明らかにします。高コストのシーケンシャルスキャンはインデックスの欠如を示し、未使用のインデックスはリソースを浪費します。

JSONとテキストフィールドのインデックス化

最新のアプリケーションは、特殊なインデックスを必要とする非構造化データを保存します:

JSONインデックス化(PostgreSQL)

CREATE INDEX idx_metadata_gin ON products 
USING GIN (metadata);

-- 次のようなクエリをサポート:
SELECT * FROM products 
WHERE metadata @> '{"category": "electronics"}';

GINインデックスはJSON包含クエリを効率的に処理しますが、B-treeよりも大きく、更新も遅くなります。書き込みが多いテーブルの場合、ドキュメント全体をインデックス化するよりも、特定のJSONパスのみをインデックス化する方が効率的です。

全文検索

CREATE INDEX idx_posts_text ON posts 
USING GIN (to_tsvector('english', content));

-- 次のクエリを実現:
SELECT * FROM posts 
WHERE to_tsvector('english', content) @@ 'database & indexing';

テキストインデックスはGoogleのような検索機能を実現しますが、テキストが多いテーブルではストレージ要件が2倍になる可能性があります。

最新データベースにおける過剰インデックス化の回避

インデックスが多ければ良いというわけではありません。各インデックスは:

• INSERT/UPDATE/DELETE操作を遅くする(インデックスを更新する必要がある)
• ストレージを増加させる(インデックスごとに10〜20%)
• メンテナンスのオーバーヘッドを追加する(断片化、再構築)

過剰インデックス化の兆候:

• 書き込みが多いテーブルに5つ以上のインデックス
• 重複インデックス(例:(a, b)と(a))
• 未使用インデックス(pg_stat_user_indexesまたはsys.dm_db_index_usage_statsで確認)
• インデックスサイズがテーブルサイズを超える

ベストプラクティス:

1. 主キーと外部キーのインデックスから始める
2. 推測ではなく、スロークエリログに基づいてインデックスを追加する
3. 可能な限り複合インデックスで統合する
4. 30日以上監視した後、未使用インデックスを削除する
5. フィルタリングされたクエリを持つ大きなテーブルには部分インデックスを検討する:

CREATE INDEX idx_orders_recent 
ON orders(created_at) 
WHERE created_at > '2024-01-01';

インデックス選択性と実際のパフォーマンス

インデックス選択性—ユニーク値と総行数の比率—が有効性を決定します。ブール列(2つの値)のインデックスは選択性が低く、ほとんど役に立ちません。emailのインデックス(すべてユニーク)は完全な選択性を持ちます。

実際の本番環境のメトリクスを監視します:

• インデックス化前後のクエリ実行時間
• インデックスヒット率
• 書き込みパフォーマンスへの影響
• ストレージの増加

pgAdmin、MySQL Workbench、MongoDB Compassなどのツールは、視覚的なクエリ分析を提供します。本番環境の監視には、DataDogやNew Relicを検討してください。

まとめ

効果的なデータベースインデックス化には、厳格なルールに従うのではなく、クエリパターンを理解することが必要です。WHERE、ORDER BY、JOIN句の列にB-treeインデックスから始めましょう。複合インデックスを戦略的に使用し、列の順序を考慮します。読み取りが多いクエリにはカバリングインデックスを活用し、JSONやテキストデータには特殊なインデックスを使用します。

最も重要なのは、すべてを測定することです。EXPLAINを使用してクエリプランを理解し、インデックス使用統計を監視し、読み取りと書き込みの両方のパフォーマンスへの実際の影響を追跡します。目標はすべてをインデックス化することではなく、アプリケーションが必要とするものを正確にインデックス化することです。

よくある質問

Understand every bug

Uncover frustrations, understand bugs and fix slowdowns like never before with OpenReplay — the open-source session replay tool for developers. Self-host it in minutes, and have complete control over your customer data. Check our GitHub repo and join the thousands of developers in our community.