経験豊富なデータベース管理者（DBA）に、クエリのパフォーマンスを最も確実に改善する方法を一つだけ教えてほしいと言ったら、答えはほぼ間違いなく「インデックスの最適化」でしょう。ところがインデックスは、データベース開発者が持つツールの中でも、最も誤解されがちなもののひとつでもあります。多くの開発者は、その場しのぎでインデックスを追加し、削除することには二の足を踏み、単に代替案が分からないという理由だけで間違ったタイプのインデックスを組み込んでしまいます。本ガイドでは、最も重要なインデックスタイプである「B-tree」「ハッシュ」「部分」「複合」について解説し、それぞれがどのような場面で有効かを説明するとともに、Navicatの表デザイナーがインデックスの作成と管理をいかにシンプルにまとめるかをご紹介します。
インデックスが重要な理由
インデックスとは、データベースが表と並行して維持する独立したデータ構造であり、すべての行を検索せずとも指定された値を検索できるように設計されています。頻繁にクエリされる列にインデックスがない場合、データベースは毎回フルテーブルスキャンを実行することになります。数千行程度であれば問題なく読み込めますが、数百万行になると動作は著しく遅くなります。その代償として、挿入、更新、削除の操作では表だけでなくインデックスも更新する必要があるため、インデックスを追加するたびに更新処理（書き込み作業）に余分な負荷がかかります。インデックスをむやみに作成するのではなく、慎重に選択することが、読み取りは高速でも書き込みが遅いスキーマと、適切にチューニングされたスキーマとの違いです。
B-Treeインデックス：理にかなった基本設定
B-Tree（バランスドツリー）インデックスは、特に指定がない場合にほとんどのデータベースが作成するものであり、それには十分な理由があります。B-Treeインデックスは、値をソートされた階層構造で格納するため、完全一致、範囲クエリ、並び替え操作のいずれにおいても効率的です。クエリに、列を「=」、「<」、「>」、「BETWEEN」、または「LIKE ‘prefix%’」で比較するWHERE句が含まれる場合、その列に対するB-Treeインデックスが最も適切であると言えるでしょう。
初心者が見落としがちな点として、B-treeインデックスはソート処理も行えることが挙げられます。クエリでインデックス付きの列を基に結果を並べ替える場合、データベースは結果データ全体をソートする代わりに順番にインデックスを読み取るだけで済みます。結果として、大規模な表では大幅なパフォーマンス向上が期待できるのです。
Hashインデックス：高速な反面、用途が限定的
Hashインデックスは、各列の値そのものではなく、計算されたハッシュ値だけを記録します。これは完全一致の検索において極めて高速な処理を可能にします。データベースは検索語句のハッシュ値を生成し、一致する記録へダイレクトに辿り着くからです。一方で、ハッシュ値には意味のある順序がないため、範囲検索やソートには全く役に立たないというのが弱点です。また、列の組み合わせによる検索にも対応していません。
ハッシュインデックスは、UUIDやセッショントークンなど、クエリで常に「=」を使用し、かつ値の重複がほとんどない列に最適です。MySQLでは、ハッシュインデックスはMEMORYテーブルでのみ利用可能です。一方、PostgreSQLでは通常の表でも完全に対応しており、最近のバージョンではますます安定性が向上しています。
部分インデックス(Partial Indexes): クエリ対象のみをインデックス化
部分インデックス（SQL Serverでは絞り込みインデックス/filtered indexとも呼ばれる）は、条件によって定義された行のサブセットに対して構築されます。例えば、注文表に数百万行あったとして、ほぼ毎回「保留中」の状態の注文のみを検索する場合、status = ‘pending’という条件で‘pending’列に部分インデックスを作成すれば、その列に対するフルインデックスよりもはるかに小さく、高速になります。
部分インデックスは、行の大部分が削除済みとして扱われ、検索されることがほとんどない「ソフト削除（soft-delete）」パターンにおいて特に有用です。インデックスからそれらの行を除外することで、インデックスをスリムに保ち、クエリプランナーの効率を向上させることができます。PostgreSQLは部分インデックスを標準サポートしており、SQL Serverではほぼ同等の実装を実現するフィルタ付きインデックス（filtered indexes）を介して同様の結果を得ることができます。
複合インデックス：重要なのは列の順序
複合インデックス（Composite Indexes）は複数の列にまたがり、適切に設計されていれば、データベースが基となる表に物理的にアクセスすることなく、複数の列にわたる高度な検索クエリを処理することができます。このインデックスの重要原則として、複合インデックスは左から右への順序でしか機能しないことを理解しておく必要があります。たとえば、(姓, 名) に対するインデックスは、姓でフィルタリングする時、または姓と名の両方でフィルタリングする時には機能しますが、名だけでフィルタリングすることはできません。
複合インデックスで列の順序を決定する際の一般的なルールは、最も選択性の高い列、つまり最も多くの行を絞り込める列を最初に配置することです。等値条件（=）で使用される列は、範囲条件(>, <, BETWEEN)で使用される列よりも先に配置する必要があります。なぜなら、インデックス内のある列で範囲条件が適用されると、その後に続く列を最適化エンジンが適切に利用できなくなるからです。
Navicatの「テーブルデザイナー」によるインデックスの管理
Navicat's Table Designerは、DDL（データ定義言語）を手動で記述することなく、前述のすべてのインデックスタイプを作成・管理するための実践的で分かりやすいビジュアル環境を提供します。インデックスは、テーブルデザイナー内の専用の「インデックス」タブで管理され、列を定義する「フィールド」タブとは分離されています。この分離により、一目で表のインデックス構築ストラテジーを把握することができます。
インデックスを追加するには、名前を付け、対象とする列を選択し、プルダウンメニューからインデックスの種類と作成方法を選択します。重要な点として、これらのドロップダウンで利用可能なオプションは、接続している特定のデータベースに合わせて調整されています。MySQLで作業している場合は、MySQLがサポートするインデックス種別が表示されます。PostgreSQL接続に切り替えると、オプションはB-tree、ハッシュ、GIN、GiSTなどを含むPostgreSQLの機能に合わせて反映されます。このように使用環境に合わせ自動的に表示が切り替わるため、対象のデータベースでどのオプションが有効か悩む必要がありません。
複合インデックスの場合、一つのインデックス項目に複数を指定し、優先順位に合わせて順序を並べ替えることができます。前項で説明した通り、列の並び順はインデックスを利用できるかどうかに直接影響します。プライマリキー列は「フィールド」タブに表示され、明確に区別されています。一方、すべての補助インデックスは「インデックス」タブに表示されます。
結論
質の高いインデックス設計は、ただインデックスを増やすことではなく、最適なインデックスを適切に配置することにあります。B-treeインデックスは日常的な検索パターンのおよそ大部分をカバーします。Hashインデックスは、特定の条件に完全に一致する検索といった限定的なシナリオでその価値を発揮します。部分インデックスは、データの限られた範囲のみを検索する場合に負荷を軽減します。また、複合インデックスは、順序を正しく設定すれば、いくつもの単一の列インデックスに相当する処理を一度にこなすことができます。これらの違いを理解し、Navicatの「テーブルデザイナー」のようなツールを使って効率的に実装・検証することで、データが増加してもクエリの高速性を維持できる強固な基盤を築くことができます。
