インデックス設計 — 複合の列順とインデックスが効く条件

本記事で使うデータ — perf_sales（5 万行）

ここからのチューニング記事では、行数の多いテーブルでインデックスがどう効くかを観察します。

この記事では複合インデックス（複数の列をまとめて並べたインデックス）の列順、左端プレフィックス則、そしてインデックスが効く条件の書き方について扱います。

演習に入る前に、perf_salesテーブルの列定義とデータの規模・サンプルを確認しておきます。（正しく実行できれば解説が表示されます）

① PRAGMA table_info(perf_sales);で列名・型・主キーを確認してください。

② SELECT COUNT(*) FROM perf_sales;で行数を確認してください。

③ SELECT emp_id, region, product, amount, sale_date, status FROM perf_sales LIMIT 5;で先頭 5 行のサンプルを確認してください（5 万行あるためSELECT *で全件は取り出さず、集計かLIMITで眺めます）。

複合インデックスの列順 — 左端プレフィックス則

複合インデックス（CREATE INDEX ix ON 表(列A, 列B)のように複数列を順番に並べたインデックス）は、その列順がそのまま並び順になります。

インデックスは「列A の昇順、同じ列A の中では列B の昇順」という辞書のような並びで作られます。

このため、条件で先頭の列から連続して使っているかが効くかどうかを左右します。

これを左端プレフィックス則（複合インデックスは先頭列から続けて指定した条件にしか効かない、という性質）と呼びます。

(emp_id, sale_date)のインデックスはemp_idの条件、またはemp_idとsale_dateの両方の条件には効きますが、sale_dateだけの条件には先頭のemp_idが抜けるためインデックスが使用されません。

-- region と product の複合インデックスを作り、
-- 先頭列 region を含む条件と、product だけの条件で計画を見比べる例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, product);

-- 先頭列 region を使う → ix_demo が使われる
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE region = 'East';

-- 先頭 region を飛ばして product だけ → ix_demo は効かない
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product = 'P050';

perf_salesにemp_idとsale_dateの複合インデックスを作り、左端プレフィックス則を実行計画で確かめます。インデックスはこのコンソール 1 回の実行で作り直し、計画を表示するところまでを自己完結させます。（正しく実行できれば解説が表示されます）

① 同名インデックスが残っていても作り直せるよう、まずDROP INDEX IF EXISTSでインデックスを消してください。

② perf_salesにemp_id、sale_dateの順の複合インデックスを作成してください。

③ emp_idだけで絞り込む集計クエリの実行計画をEXPLAIN QUERY PLANで表示してください。

④ 続けて、sale_dateだけで絞り込む集計クエリの実行計画も表示し、③ と表示がどう変わるか見比べてください。

列順は「等値 → 範囲」の順にする

複合インデックスに含める列の順番には指針があります。

等値条件（=やINで 1 つの値に固定する条件）の列を先頭に、範囲条件（> >= < BETWEENのように幅を持つ条件）の列を後ろに置きます。

等値で先頭を固定すると、インデックスの並びの中で対象が連続したかたまりになり、その範囲を一気に絞り込めます。

WHERE emp_id = 7 AND sale_date >= '2024-01-01'のような条件には(emp_id, sale_date)の順が向きます。

-- region(等値) と amount(範囲) の組み合わせを
-- 等値→範囲 の順でインデックス化する例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, amount);

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE region = 'West' AND amount >= 500000;

「特定の担当者の、ある日付以降の売上をまとめたい」という要件を考えます。条件はemp_idの等値とsale_dateの範囲です。この 2 列を等値 → 範囲の順で複合インデックスにし、実行計画でインデックス探索になることを確かめます。

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

② 等値条件の列を先頭、範囲条件の列を後ろにした複合インデックスを作成してください。

③ emp_idの等値とsale_dateの範囲をANDで組み合わせた集計クエリの実行計画をEXPLAIN QUERY PLANで表示し、インデックス探索になっているか確認してください。

インデックスが効く条件 — 列を加工せずそのまま比較する

インデックスを作っても、条件の書き方によっては使われません。

インデックスで絞り込める形の条件を、ここではインデックスが効く条件と呼びます（英語では SARGable とも呼ばれます）。

基本はインデックスを張った列を、加工せずそのまま比較することです。

WHERE emp_id = 7のように列をそのまま等値・範囲で比較するとインデックスが効きます。

一方WHERE emp_id + 0 = 7のように列を計算式の中に入れたり、WHERE substr(sale_date,1,4) = '2024'のように関数で包んだりすると、インデックスの並びと値が対応しなくなり全行走査に戻ります。

条件は列を素のまま左辺に置き、加工が必要なら右辺の値側で調整します。

-- 同じ意味でも書き方で計画が変わる例
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- 列を素のまま比較 → インデックスが効き SEARCH になる
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- 列を式に入れる → インデックスが効かず SCAN に戻る
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount + 0 >= 800000;

同じ意味の条件でも、列をそのまま比較する場合と、列を計算式に入れた場合とで実行計画がどう変わるかを観察します。emp_idにインデックスを張り、2 種類の書き方で計画を見比べてください。

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

② emp_idの単一列インデックスを作成してください。

③ emp_idを素のまま等値比較する集計クエリの実行計画を表示してください。

④ 続けて、emp_idを計算式に入れた同じ意味の集計クエリの実行計画を表示し、③ と表示がどう変わるか見比べてください。