ジュニア開発者にデータベースのインデックスを説明する

もしこれまでに基本的なCRUDアプリしか書いたことがないなら、データベースインデックスは少し神秘的に感じられるかもしれません。良いニュースは、基本的な考え方はシンプルだということです。インデックスとは、データベースがテーブル全体をスキャンせずにレコードをより速く見つけられるように維持するデータ構造のことです。

良い例えは、教科書の巻末にある索引です。「トランザクション」という言葉が出てくるすべてのページを見つけたいと想像してみてください。ページを1ページずつ本全体を読むこともできますが、それは遅いです。本の索引はショートカットを提供します。単語を検索して、関連するページに直接ジャンプできます。データベースインデックスも同じような役割を果たします。テーブルのすべてのレコードを読む代わりに、データベースはインデックスを使用して必要なレコードに近づくことができます。

なぜこれが重要なのでしょうか？テーブルが大きくなるにつれて、フルスキャンは高コストになります。100件のレコードを読むのは安価です。ユーザーがページを読み込むたびに1000万件のレコードを読むのはそうではありません。インデックスは、データベースが一般的なクエリのために調べる必要があるデータの量を減らすために存在します。

概念レベルでは、リレーショナルデータベースで最も一般的なインデックスタイプはBツリーインデックスです。それをうまく使うために、低レベルの実装の詳細を知る必要はありません。重要な考え方は、インデックスが実際のレコードへのポインタとともに、列の値をソートされた順序で格納することです。

Bツリーは、標識の階層のようなものだと想像できます。最上位レベルでは、データベースがどの大きな値の範囲をたどるかを決定するのに役立ちます。次に、次のレベルでさらに絞り込みます。数回のステップの後、リーフレベルに到達し、そこで正確な値または小さな値の範囲を見つけて、一致するレコードにジャンプできます。

ユーザーテーブルにemail列があり、このクエリを実行すると仮定します。

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

emailにインデックスがない場合、データベースは一致するものを見つけるまでusersテーブルのすべてのレコードを検査する必要があるかもしれません。emailにBツリーインデックスがある場合、値の比較によってツリーをナビゲートし、正しいセクションにすばやく到達できます。テーブル全体をチェックする代わりに、はるかに短いパスをたどります。

その速度向上は、特に以下の場合に役立ちます。

• emailやorder_idでレコードを検索するなど、正確な検索
• created_at >= 特定の日付などの範囲クエリ
• ORDER BY last_nameなどの並べ替え
• 特定のプレフィックスで始まる名前など、場合によってはプレフィックスマッチング

Bツリーがそれほど汎用性が高い理由は、ソートされたデータが多くの操作に役立つからです。値が順序付けられている場合、データベースは1つの値、近くの値のセット、または並べ替えのためにすでに配置されているレコードを効率的に見つけることができます。

さて、重要な部分です。インデックスは無料ではありません。多くのジュニア開発者は、「インデックスはクエリを高速化する」と聞き、「それならすべてをインデックス付けすべきだ」と考えます。それは通常問題を引き起こします。

主なトレードオフは次のとおりです。

ストレージコスト
インデックスはディスクスペースを消費します。大きなテーブルのいくつかの列をインデックス付けすると、テーブルサイズのかなりの割合を占めるインデックス、またはテーブル自体よりも合計サイズが大きいインデックスになる可能性があります。

書き込みコスト
レコードを挿入、更新、または削除するたびに、データベースは関連するすべてのインデックスも更新する必要があります。テーブルに多くのインデックスがある場合、データベースは維持する構造が増えるため、書き込みは遅くなります。

メンテナンスコスト
データベースとワークロードによっては、インデックスは時間の経過とともに断片化したり、効率が低下したりする可能性があります。データベースは、クエリプランナーがインデックスを使用する価値があるかどうかを判断できるように、統計情報を収集するためにも時間を費やします。

プランナーのオーバーヘッドと誤った選択
インデックスが存在しても、データベースがそれを使用することを保証するものではありません。一部のクエリでは、特にテーブルが小さい場合やクエリがテーブルの大部分を返す場合、テーブル全体をスキャンする方が実際には高速です。

インデックスが役立つ場合
インデックスは、クエリが選択的である場合に最も役立ちます。つまり、少数のレコードに絞り込むことを意味します。たとえば、500万件のレコードがあるテーブルで1人のユーザーをemailで検索するのは、優れたユースケースです。

インデックスがあまり役立たない、または害になる場合
インデックスがあまり役立たないのは、次のような場合です。

• テーブルが非常に小さい
• クエリがテーブルのほとんどを返す
• インデックス付けされた列のバリエーションが非常に少ない（たとえば、trueとfalseしかないブール値など）、特別な方法で使用されない限り
• テーブルが書き込み中心で、読み取りのメリットが書き込みの遅延に見合わない場合

たとえば、このクエリを想像してみてください。

SELECT * FROM users WHERE is_active = true;

ユーザーの95％がアクティブな場合、is_activeのインデックスはあまり役立たないかもしれません。データベースはまだほとんどのテーブルを取得する必要があるかもしれないので、インデックスはあまり作業を節約しません。場合によっては、プランナーはインデックスを完全に無視します。

では、実際には何をインデックス付けするかをどのように決定しますか？

良いルールは次のとおりです。WHERE、JOIN、ORDER BY、および場合によってはGROUP BY句で頻繁に使用される列をインデックス付けします。特に、これらのクエリがテーブルのごく一部にしかアクセスしない場合です。

実践的な例をいくつか示します。

例1：一意の値での正確な検索
クエリ：
SELECT * FROM users WHERE email = 'sam@example.com';

インデックスは役立ちますか？
はい、非常に可能性が高いです。

理由：
メールはしばしば一意またはほぼ一意であるため、クエリは非常に選択的です。users(email)のインデックスは強力な選択肢です。多くのシステムでは、emailが一意である必要がある場合、重複を強制する一意のインデックスまたは一意の制約を作成することがよくあります。

例2：日付範囲でのフィルタリング
クエリ：
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';

インデックスは役立ちますか？
通常ははい。特にテーブルが大きく、日付範囲が比較的少数のレコードを選択する場合です。

理由：
Bツリーインデックスは、値がソートされているため、範囲スキャンに適しています。データベースは最初の一致する日付にジャンプし、範囲が終わるまで読み進めることができます。

例3：低カーディナリティ列でのフィルタリング
クエリ：
SELECT * FROM orders WHERE status = 'completed';

インデックスは役立ちますか？
場合によっては、そうでない場合もあります。

理由：
データの分布によります。ほとんどすべての注文が完了している場合、インデックスはあまり役立たないかもしれません。完了している注文がごく一部であり、このクエリが一般的である場合、役立つ可能性があります。だからこそ、データの形状を知ることが重要です。

例4：テーブルの結合
クエリ：
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'sam@example.com';

インデックスは役立ちますか？
はい。

理由：
通常、ユーザーをすばやく見つけるためにusers(email)にインデックスを付け、そのユーザーの注文を効率的に見つけるためにorders(user_id)にもインデックスを付けることがよくあります。結合列は非常に一般的なインデックス候補です。

例5：結果の並べ替え
クエリ：
SELECT * FROM products ORDER BY price LIMIT 20;

インデックスは役立ちますか？
しばしばはい。

理由：
priceのインデックスにより、データベースはテーブル全体を並べ替えるのではなく、最も安いレコードを直接読み取ることができるかもしれません。これは、LIMITと特に役立つ場合があります。

複合インデックスは、もう1つの重要な実践的なトピックです。複合インデックスは、複数の列をカバーします。たとえば、次のようになります。

INDEX ON orders (customer_id, created_at)

これは、次のようなクエリに役立ちます。
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at DESC;

データベースは、インデックスを使用してまず1人の顧客のレコードに絞り込み、次にcreated_atの順序で読み取ることができます。これは、customer_idとcreated_atに個別のインデックスを付けるよりもはるかに優れている可能性があります。

ただし、複合インデックスでは列の順序が重要です。 (customer_id, created_at)のインデックスは、クエリが最初にcustomer_idでフィルタリングする場合に最も役立ちます。(created_at, customer_id)のインデックスと同じではありません。選択する前に、最も一般的なクエリパターンについて考えてください。

役立つメンタルモデルは次のとおりです。列を孤立してインデックス付けしないでください。クエリのためにインデックス付けしてください。自問自答してください。

• 実際に遅いクエリは何ですか？
• どの列がフィルター、結合、および並べ替えに出現しますか？
• クエリはごく一部のレコードを返すか、それとも大部分を返しますか？
• このテーブルは主に読み取り中心ですか、それとも書き込み中心ですか？

また、データベースツールを使用してください。たとえば、PostgreSQLでは、EXPLAINまたはEXPLAIN ANALYZEは、プランナーがインデックスを使用しているか、シーケンシャルスキャンを実行しているか、並べ替えを行っているかなどを示します。これは学習する最良の方法の1つです。推測する代わりに、実行計画を調べて、データベースが実際に行っていることを確認できます。

もう1つの便利なポイントは、主キーは通常自動的にインデックス付けされるということです。したがって、テーブルにidが主キーとしてある場合、次のようなクエリは次のようになります。
SELECT * FROM users WHERE id = 123;
データベースが通常そのインデックスを作成してくれるため、すでに高速です。

Bツリー以外にも、特別なケースのための他のインデックスタイプがあります。

1つの例はハッシュインデックスです。ハッシュインデックスは、column = valueのような等価チェックを高速化するように設計されています。順序を維持しないため、通常は範囲クエリや並べ替えには役立ちません。多くの実際のアプリケーションでは、Bツリーは等価および範囲検索の両方をうまく処理できるため、依然として好まれています。

もう1つの重要な例、特にPostgreSQLではGINです。GINインデックスは、配列、JSONB、または全文検索のようなデータ型に役立つことがよくあります。JSONドキュメント内を検索したり、配列に値が含まれているかを確認したりする必要がある場合は、GINインデックスがBツリーよりもはるかに適切である可能性があります。

したがって、他のインデックスタイプについて1つだけ覚えておく必要があるとすれば、それは次のとおりです。Bツリーは汎用のデフォルトですが、特殊なデータとクエリパターンには特殊なインデックスが必要になる場合があります。

ここにあなたが使用できる実践的な意思決定プロセスがあります。

1. テーブルではなく、遅いクエリから始めます。
2. WHERE、JOIN、およびORDER BYで使用されている列を確認します。
3. クエリが選択的かどうかを推定します。
4. テーブルに頻繁な書き込みがあるかどうかを検討します。
5. 重要なクエリパターンをサポートする最小の有用なインデックスを追加します。
6. EXPLAIN ANALYZEと実際の測定値で検証します。

いくつかの最終的な経験則：

• 良い候補：主キー、結合で使用される外部キー、emailのような一意の検索フィールド、範囲に使用されるタイムスタンプ、LIMITでの並べ替えに使用される列
• 弱い候補：値が数個しかない列、小さなテーブルの列、クエリでめったに使用されない列
• 大量の更新があるテーブルでの過剰なインデックスに注意してください
• 直感だけでなく、実際の遅いクエリからの証拠を優先してください

要するに、インデックスは、本の索引が本全体を読まずにページを見つけるのに役立つのと同様に、データベースがデータをすばやく見つけるのに役立つショートカット構造です。Bツリーインデックスは、値をツリー構造でソートして、データベースが検索をすばやく絞り込めるようにすることで機能します。これらは強力で、多くの場合適切なデフォルトですが、ストレージ、書き込み速度、およびメンテナンスにコストがかかります。最良のインデックス決定は、実際のクエリ、データの分布、および読み取りと書き込みのワークロードを理解することから生まれます。

データベースが行をどのように見つけるかという観点から考え始めると、インデックス付けはそれほど魔法ではなくなります。すべてのインデックスタイプをすぐに覚える必要はありません。クエリを見て、「データベースがこれらのレコードへのショートカットを持っていたら役立つだろうか？」と自問自答できれば、あなたはすでにインデックスについて正しい方法で考えています。

こんにちは！クエリのパフォーマンスについて考えているのは素晴らしいことです。インデックスはデータベースの基本的な概念であり、それを理解することはデータベーススキルを確実に向上させるでしょう。インデックスとは何か、そしてどのように機能するのかを解説しましょう。

1. データベースインデックスとは何か、そしてなぜ存在するのか？

数千ページもある巨大な物理的な辞書を想像してみてください。特定の単語の定義を見つけたい場合、例えば「ubiquitous」という単語を探すとしたら、どうしますか？最初からすべての単語を読んでいくわけではないですよね？それは永遠にかかってしまいます！

代わりに、単語のアルファベット順を利用するでしょう。「U」のセクションに飛び、それから素早く「ubiquitous」を探します。これはまさにデータベースインデックスがやっていることです：テーブル全体をスキャンすることなく、データベースシステムがテーブル内の行を素早く見つけるのを助けるデータ構造です。

辞書のアルファベット順が単語探しを速めるのと同じように、データベースインデックスは特定のデータ行を見つけるのを速めます。インデックスがないと、データベースは要求されたデータを見つけるために「フルテーブルスキャン」を実行しなければならないかもしれません。これは、文字通りすべての行を確認することになり、大きなテーブルでは信じられないほど遅くなります。

2. Bツリーインデックスの仕組み（概念的）

最も一般的なインデックスの種類はBツリーインデックスです。これを非常に整理された多層ディレクトリのようなものと考えてください。基本的な考え方は次のとおりです。

• ツリー構造: Bツリーはツリーのようなデータ構造です。一番上に「ルート」ノードがあり、そこから「内部」ノードに分岐し、最終的に一番下に「リーフ」ノードで終わります。
• ソートされたデータ: 重要なのは、インデックス内のデータがソートされていることです。ツリーの各ノードには値の範囲とポインタが含まれています。例えば、ルートノードは「A～Mの値はこの子ブランチに、N～Zの値はあの子供ブランチに」と言うかもしれません。
• 高速なトラバース: 特定の値（例：WHERE user_id = 123）を検索すると、データベースはルートから開始します。値が含まれる可能性のある子ノードを素早く特定し、そのノードに移動し、これを繰り返します。検索パスを素早く絞り込み、リーフノードに到達します。
• データへのポインタ: Bツリーのリーフノードには実際のテーブルデータは含まれていません。代わりに、それらはメインテーブル内の対応する行の正確な物理的な場所へのポインタ（辞書の例でのページ番号のようなもの）を含んでいます。これにより、データベースはテーブル全体をスキャンすることなく、関連する行に直接ジャンプできます。

このツリー構造とソートされた性質のため、Bツリーでデータを見つけるのは非常に効率的です。数百万行を確認する可能性がある代わりに、数回のステップでデータを検索できます。

3. インデックス追加のトレードオフ

インデックスは強力ですが、万能薬ではありません。考慮すべき重要なトレードオフがあります。

インデックスが役立つ場合（メリット）：

• 高速なSELECTクエリ: これが主なメリットです。インデックス付き列に対するWHERE句（特に等価検索、範囲検索（<、>、BETWEENなど））、JOIN条件、ORDER BY、GROUP BY句を持つクエリは、劇的な速度向上が見られます。
• 高速なJOIN操作: インデックス付き列で2つのテーブルを結合する場合、データベースは一致する行を素早く見つけることができます。
• ソートの回避: ORDER BYまたはGROUP BY句がインデックス付き列を使用している場合、データベースは既にソートされたインデックスを使用できる可能性があり、コストのかかるソート操作を回避できます。

インデックスが不利になる場合（コスト）：

• ストレージ容量: インデックスはディスク容量を消費します。非常に大きなテーブルの場合、インデックスはかなりのストレージを消費する可能性があり、時にはテーブルデータ自体よりも多く消費することもあります。
• 書き込みパフォーマンスの低下: メインテーブルに行をINSERT、UPDATE、またはDELETEするたびに、データベースは関連するすべてのインデックスも更新する必要があります。これにより、書き込み操作にオーバーヘッドが追加され、遅くなります。テーブルに多くのインデックスがある場合、書き込みパフォーマンスは著しく低下する可能性があります。
• メンテナンスのオーバーヘッド: データベースシステムはインデックス構造を維持する必要があります。これには計算コストがかかり、特にデータが変更されてツリーの再編成が必要になる場合に顕著です。
• 常に使用されるわけではない: データベースのクエリオプティマイザがインデックスを使用するかどうかを決定します。非常に小さなテーブルの場合、またはクエリが複雑でインデックスが十分に選択的でない場合（例：値の99％がtrueであるboolean列のインデックス）、オプティマイザはフルテーブルスキャンの方が実際には速いと判断する場合があります。

4. 実践的なガイダンス：どの列をインデックスするかを決定する

目標は、検索を絞り込むために頻繁に使用される列をインデックスすることですが、過剰なインデックスを避け、コストをかけすぎないことです。

一般的な経験則：

• WHERE句: WHERE句に頻繁に現れる列をインデックスします。
• JOIN条件: JOINのON句で使用される列をインデックスします。
• ORDER BYおよびGROUP BY: ソートを回避するために、ORDER BYまたはGROUP BYで使用される列をインデックスします。
• 高カーディナリティ: 多くのユニークな値を持つ列（例：email_address、user_id、product_sku）は一般的に良い候補です。is_activeのような列（trueまたはfalseしかない）のインデックスは、結果をあまり絞り込まないため、通常はあまり効果的ではありません。
• 複合インデックス: 場合によっては、複数の列を一緒にインデックス付けすること（例：(last_name, first_name)）は、それらの列の組み合わせで頻繁にクエリを実行する場合に役立ちます。
• 過剰なインデックスを避ける: すべてをインデックス化しないでください！最も遅いクエリとその使用列に焦点を当ててください。

具体的な例：

id、email、registration_date、country、is_activeという列を持つusersテーブルがあるとします。

1. クエリ: SELECT * FROM users WHERE email = 'alice@example.com';

   • インデックスは役立ちますか？ 絶対に役立ちます！email列のインデックスにより、データベースはusersテーブル全体をスキャンすることなく、アリスの行を素早く見つけることができます。これはBツリーインデックスの完璧なユースケースです。

2. クエリ: SELECT id, email, registration_date FROM users WHERE country = 'USA' AND registration_date > '2023-01-01' ORDER BY registration_date DESC;

   • インデックスは役立ちますか？ はい、大幅に役立ちます。(country, registration_date)の複合インデックスは非常に有益でしょう。データベースはまず'USA'のユーザーを素早くフィルタリングし、次に'2023-01-01'以降に登録されたユーザーを効率的に見つけ、さらにregistration_dateの降順で既にソートされた状態で返すことができるため、別のソート操作を回避できます。

5. Bツリー以外のインデックス：その他のインデックスタイプ

Bツリーは主力ですが、特殊なユースケースのために他のインデックスタイプも存在します。一般的な例はハッシュインデックスです。

• ハッシュインデックス: ソートされたツリーの代わりに、ハッシュインデックスはハッシュ関数を使用して列値を直接行の場所にマッピングします。これらは等価ルックアップ（WHERE column = 'value'）に非常に高速です。なぜなら、データに直接ジャンプできるからです。しかし、データがソートされた順序で格納されていないため、範囲クエリ（<、>、BETWEEN）やORDER BY句には使用できません。これらのシナリオでは通常Bツリーを使用します。

PostgreSQLでは、**GIN（Generalized Inverted Index）やGiST（Generalized Search Tree）**インデックスにも遭遇するかもしれません。これらは、単純なBツリーでは効果的でない、配列、JSONBドキュメント、または全文検索のような複雑なデータ型をインデックス化するための強力なツールです。例えば、タグの配列を格納するtags列がある場合、GINインデックスは特定のタグを含むすべての行を素早く見つけることができます。

結論

インデックスは、特に読み取り負荷の高いアプリケーションや大規模なデータセットにおいて、データベースパフォーマンスを最適化するための重要なツールです。最も遅いSELECTクエリを特定し、WHERE、JOIN、ORDER BY、GROUP BY句を分析し、関連する列にBツリーインデックスを追加することを検討してください。インデックスにはストレージと書き込みパフォーマンスのトレードオフが伴うため、新しいインデックスのパフォーマンスへの影響を常にテストしてください。頑張ってください！