インデックスオンリースキャン — テーブル参照を省くインデックス

テーブルルックアップを省く — インデックスオンリースキャン

通常のインデックス探索は、インデックスで対象の行を見つけたあと、その行の他の列を読むためにテーブル本体に戻って 1 行ずつ読み直します。

この戻りをテーブルルックアップ（インデックスからテーブル本体の行を引きに行く処理）と呼びます。

返す行が多いとこの戻りが積み重なり、コストになります。

クエリが参照する列をすべて含むインデックスを使うと、必要な値がインデックスの中だけで揃うため、テーブル本体に戻る必要がなくなります。

インデックスだけで結果を返すこの動きをインデックスオンリースキャンと呼びます（このような全列を含むインデックスを、英語ではカバリングインデックスとも呼びます）。

-- region だけを含むインデックスで region を集計する例
-- 参照列(region)が全部インデックスに入る → テーブル本体へ戻らない
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region);

EXPLAIN QUERY PLAN
SELECT region, COUNT(*)
FROM perf_sales
GROUP BY region;

「担当者ごとの売上合計を出したい」という要件を考えます。このクエリが参照する列はemp_idとamountの 2 つだけです。この 2 列を含むインデックスを作り、テーブル本体へ戻らずインデックスだけで結果が出る計画になることを確かめます。インデックスはこのコンソール 1 回の実行で作り直し、計画を表示するところまでを自己完結させます。（正しく実行できれば解説が表示されます）

① DROP INDEX IF EXISTSでインデックスを消してください。

② クエリが参照するemp_idとamountを両方含むインデックスを作成してください。

③ emp_idごとにamountの合計を出す集計クエリの実行計画をEXPLAIN QUERY PLANで表示し、テーブル本体を読まない計画になっているか確認してください。

1 列でも欠けるとテーブル本体へ戻る

インデックスオンリースキャンが成立するのは、SELECT・WHERE・GROUP BY などクエリが触る列が 1 つ残らずインデックスに含まれているときだけです。

1 列でも足りないと、その列の値を読むためにテーブル本体へ戻る必要があり、計画からUSING COVERING INDEXが消えます。

たとえば(emp_id, amount)のインデックスに対してSELECT emp_id, SUM(amount), regionのようにregionを追加すると、regionはインデックスに無いためテーブル本体へ戻ります。

-- (region, amount) のインデックスに対し、
-- 参照列に product を足すとインデックスオンリースキャンが崩れる例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

-- region, SUM(amount) だけ → インデックスのみで完結
EXPLAIN QUERY PLAN
SELECT region, SUM(amount) FROM perf_sales GROUP BY region;

-- product を足す → インデックスに無いので本体へ戻る
EXPLAIN QUERY PLAN
SELECT region, SUM(amount), MAX(product) FROM perf_sales GROUP BY region;

同じインデックスに対し、参照する列がインデックスに収まる場合と、収まらない列を 1 つ足した場合とで実行計画がどう変わるかを観察します。emp_idとamountの複合インデックスを作り、2 種類の集計クエリで計画を見比べてください。

① DROP INDEX IF EXISTSでインデックスを消してください。

② emp_idとamountを含む複合インデックスを作成してください。

③ emp_idごとにamountの合計を出す集計クエリの実行計画を表示してください（インデックスに収まる）。

④ 続けて、同じ集計にregionの最大値も加えたクエリの実行計画を表示し、③ と表示がどう変わるか見比べてください（regionはインデックスに無い）。

WHERE の絞り込み列も含めてインデックスオンリースキャンを保つ

インデックスに含めるべき列は SELECT に出てくるものだけではありません。

WHERE で絞り込む列もクエリが触る列なので、これもインデックスに含めて初めてテーブル本体に戻らずに済みます。

「絞り込み列 → 出力・集計列」の順で 1 本のインデックスにまとめると、絞り込みと値の取り出しが同じインデックス内で完結します。

たとえばWHERE region = 'East'で絞ってSUM(amount)を出すなら、(region, amount)のように絞り込み列regionを先頭、集計列amountを後ろにします。

regionで対象を絞り込みつつ、amountの値もインデックスから読めるため、本体への戻りが発生しません。

-- 絞り込み列(status) と集計列(amount) を 1 本にまとめる例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status, amount);

EXPLAIN QUERY PLAN
SELECT SUM(amount)
FROM perf_sales
WHERE status = 'pending';

「特定の地域に絞った売上合計を出したい」という要件を考えます。WHEREでregionを絞り、amountの合計を出すクエリです。絞り込み列と集計列を 1 本のインデックスにまとめ、テーブル本体へ戻らない計画になることを確かめます。

① DROP INDEX IF EXISTSでインデックスを消してください。

② 絞り込み列regionを先頭、集計列amountを後ろにした複合インデックスを作成してください。

③ regionで絞り込んでamountの合計を出す集計クエリの実行計画をEXPLAIN QUERY PLANで表示し、テーブル本体を読まない計画になっているか確認してください。