0Pricing
SQL Interview Prep · レッスン

カバリングインデックスとIndex-Only Scan

クエリがテーブルヒープに一度もアクセスせずに済むよう、列を含めます。

「カバリングインデックスとIndex-Only Scan」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン3/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

ヒープフェッチを振り返る

以前、通常のB-Treeにはインデックス対象の列と行ポインターしか格納されないため、インデックスが一致する行を見つけた後も、エンジンは他の列を読むためにテーブルへ移動する必要があると学びました。この移動がヒープフェッチであり、カバリングインデックスはこのコストをなくすよう設計されています。

面接官がカバリングインデックスについて尋ねるのは、テーブルにアクセスせずにインデックスだけでクエリに完全に回答できる理由を理解しているか確認するためです。

「カバーする」とは

クエリが必要とする、SELECT、WHERE、ORDER BY、GROUP BY内のすべての列がインデックス自体に含まれているとき、そのインデックスはクエリをカバーするといいます。

この条件を満たすと、エンジンはインデックスだけを読み取り、テーブルには一切アクセスしません。PostgreSQLではこれをIndex-Only Scanと呼び、SQL Serverなどではカバリングインデックスと呼びます。利点は、ページ読み取りが減り、クエリが高速になることです。

実例：カバーされるクエリ

クエリがcustomer_idとorder_dateだけを必要とするとします。この2列だけで構成された複合インデックスには、クエリが要求するすべての情報が含まれるため、インデックスだけで回答できます。

CREATE INDEX idx_orders_cust_date
  ON orders (customer_id, order_date);

-- Covered: both selected columns are in the index
SELECT customer_id, order_date
FROM orders
WHERE customer_id = 42;

列を1つ追加するとカバーできなくなる

インデックスに含まれていない列を1つ追加するとカバレッジが失われ、エンジンはその列を取得するためにヒープをフェッチする必要があります。

ここではtotalがインデックスに含まれていないため、customer_idが検索の起点になっていても、一致するすべての行でtotalを読み取るためのヒープフェッチが発生します。

-- NOT covered: total is not in the index, forces heap fetches
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

INCLUDE句

totalを4番目のキー列として追加することもできますが、そこでは絞り込みもソートもしないので、ツリーのソート順に含めるのは領域の無駄です。より適切な手段がINCLUDEです（PostgreSQLとSQL Serverがサポートしています）。INCLUDEは追加の列をインデックスのリーフにのみペイロードとして格納し、ソートキーの一部にはしません。

これで、インデックスの検索対象部分を肥大化させずにクエリをカバーできます。

CREATE INDEX idx_orders_cust_date_inc
  ON orders (customer_id, order_date)
  INCLUDE (total);

-- Now covered: total is carried in the leaf
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;

キー列とINCLUDE列

面接官に好印象を与える、正確な区別です。

  • キー列はソート順を定義し、シークや範囲スキャンに使用できます。左端プレフィックスのルールに従います。
  • INCLUDE列は追加データとしてリーフにのみ格納されます。検索には使用できませんが、より多くのクエリをインデックスでカバーできるようにします。

経験則として、絞り込みやソートに使う列はキーに、返すだけの列はINCLUDEに配置します。

MySQL/InnoDB：クラスタ化による違い

データベース方言間の違いへの理解を示しましょう。InnoDB（MySQL）のテーブルは主キーによってクラスタ化されています。セカンダリインデックスには主キー列が暗黙的に含まれるため、インデックス対象の列と主キー列だけを選択するクエリであれば、セカンダリインデックスが自動的にカバーします。MySQLにはINCLUDE句はありません。

カバリングという考え方は普遍的ですが、構文や追加コストなしで含まれる列はデータベースエンジンによって異なります。

Index-Only Scanを検証する

EXPLAINでカバレッジを証明します。PostgreSQLでは、実行計画のノードがIndex ScanではなくIndex Only Scanと表示されます。EXPLAIN (ANALYZE)でHeap Fetches: 0となっていることを確認してください。これが、テーブルにアクセスしていないことを示す決定的なサインです。

Index-Only Scanを期待していたのに、ヒープフェッチを伴うIndex Scanが表示される場合は、選択した列のいずれかがインデックスに含まれていません。

EXPLAIN (ANALYZE)
SELECT customer_id, order_date, total
FROM orders
WHERE customer_id = 42;
-- Look for: Index Only Scan ... Heap Fetches: 0

Postgresの可視性マップに関する注意点

加点につながる、Postgresについての細かなポイントです。ページがvisibility mapで全可視としてマークされていない場合、Index-Only Scanでもヒープにアクセスすることがあります。大量の更新の後にはVACUUMを実行して、visibility mapを最新の状態にしてください。そうしないとHeap Fetchesが増え、「index-only」のメリットが小さくなります。

-- Keeps the visibility map fresh so index-only scans stay heap-free
VACUUM ANALYZE orders;

幅広いカバリングインデックスを作るべきでない場合

カバリングインデックスは無料ではありません。多数の列をINCLUDEに詰め込むとインデックスが大きくなり、キャッシュを消費するとともに書き込みを遅くします（関連する書き込みのたびにインデックスも更新されるためです）。トレードオフとして、次の点を説明できるようにしましょう。

  • 頻繁に実行される、対象の狭い読み取りクエリには有効です。
  • 「念のため」とすべての列を入れる用途には不向きです。

行全体ではなく、重要なクエリをカバーしてください。

面接での答え方

簡潔にまとめると、次のようになります。

「カバリングインデックスには、クエリが参照するすべての列が含まれるため、エンジンはヒープフェッチを省略し、インデックスだけでクエリに回答できます。つまりIndex-Only Scanです。検索する列はキーに、返すだけの列はINCLUDEに配置し、EXPLAIN ANALYZEでHeap Fetchesがゼロであることを検証します。また、書き込み速度を守るため、インデックスは狭く保ちます。」

クイックチェック

カバレッジと、それぞれの列を配置すべき場所について考えてください。

振り返り：カバリングインデックス

重要なポイント：

  • クエリが必要とするすべての列をインデックスが保持しているとき、そのインデックスはクエリをカバーし、ヒープフェッチなしのIndex-Only Scanを可能にします。
  • キー列はシークを実行し、左端プレフィックスのルールに従います。INCLUDE列は、カバレッジのためにリーフにのみ格納されるペイロードです。
  • InnoDBのセカンダリインデックスには、主キーが暗黙的に含まれます。
  • EXPLAIN (ANALYZE)で検証し、Heap Fetchesを確認します。PostgresではVACUUMを最新の状態に保ってください。
  • 書き込み性能を守るため、カバリングインデックスは狭く保ちます。

次は反対の話、インデックスが実際に害になる場合です。

よくある質問

「カバリングインデックスとIndex-Only Scan」レッスンは無料ですか？

はい。「カバリングインデックスとIndex-Only Scan」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「カバリングインデックスとIndex-Only Scan」で何を学びますか？

クエリがテーブルヒープに一度もアクセスせずに済むよう、列を含めます。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン3/4です。

「カバリングインデックスとIndex-Only Scan」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. B-Treeインデックスとその効果
  2. 複合インデックスの列順
  3. カバリングインデックスとIndex-Only Scan
  4. インデックスが逆効果になる場合：書き込みと選択性
← SQL Interview Prepに戻る