インデックス入門 — 複合・部分・式・UNIQUE

本記事で使うデータ — perf_sales テーブル

インデックス（索引）は本の索引のようなもので、特定の列の値を素早く見つけるために用います。

本記事ではCREATE INDEXでのインデックス作成、複合・部分・式・UNIQUE の各インデックス、そしてEXPLAIN QUERY PLANで検索方法がどう変わるかの確認を順に扱います。

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

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

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

③ SELECT * FROM perf_sales LIMIT 5;で先頭 5 行をプレビューしてください。perf_salesは 5 万行あるので必ずLIMITを付けてください。

CREATE INDEX と EXPLAIN QUERY PLAN — 全表スキャンを索引検索に変える

インデックスはCREATE INDEX インデックス名 ON 表(列);で作ります。

インデックスのある列をWHEREの等値条件や範囲条件で使うと、全表スキャンの代わりにインデックス検索が選ばれ、目的の行に直接たどり着けます。

検索方法はEXPLAIN QUERY PLANで確認します。

クエリの前にEXPLAIN QUERY PLANを付けて実行すると、実際の結果ではなくどう探すかの計画が表示されます。

インデックスが無いときはSCAN perf_sales（全表を 1 行ずつ調べる）、インデックスが効くとSEARCH perf_sales USING INDEX ...（索引で絞って探す）のように表示が変わります。

計画の文字列は環境によって細部が変わるため、本記事では実行できれば正解とし、計画の意味は本文と解説で読み解きます。

-- 索引が無いときの計画を見る（読むだけの別例）
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SCAN perf_sales （全表を 1 行ずつ調べる）

-- 索引を作ってから同じ計画を見る
DROP INDEX IF EXISTS ix_region;
CREATE INDEX ix_region ON perf_sales(region);
EXPLAIN QUERY PLAN
SELECT sale_id, amount FROM perf_sales WHERE region = 'East';
--> SEARCH perf_sales USING INDEX ix_region (region=?)
-- 注: 同じ実行内で DROP -> CREATE -> EXPLAIN まで完結させる

「emp_id = 7の売上を頻繁に検索するので、全表スキャンからインデックス検索に変えたい」という要件を想定します。

この実践は 3 段階に分かれていて、①索引なしでの計測 → ②索引の作成 → ③索引ありでの再計測の順に進めます。コンソールごとに合計実行時間が表示されるので、最終的には①（索引なし）と③（索引あり）の実行時間を見比べるのが目的です。②の索引作成は 1 度きりのコストなので、純粋な検索速度の比較には含めません。

まずこのコンソールで、SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7;と、同じ条件のEXPLAIN QUERY PLANを実行してください。実行計画がSCAN perf_salesになることと、合計実行時間 t1 を確認します。

次に、emp_id列にインデックスを作ります。このコンソールでDROP INDEX IF EXISTS ix;で前回分を消してから、CREATE INDEX ix ON perf_sales(emp_id);を実行してください。

ここで作る索引は、次の③のコンソールで使い続けます。なお、このコンソールの実行時間は索引を構築する 1 度きりのコストなので、①と③の純粋な検索速度の比較には含めません。

最後に、①と同じクエリを索引ありの状態で実行し、計画と実行時間の変化を確認します。

このコンソールで、SELECT sale_id, amount FROM perf_sales WHERE emp_id = 7;と、同じ条件のEXPLAIN QUERY PLANを実行してください。実行計画がSEARCH perf_sales USING INDEX ixに変わっていること、そして合計実行時間 t3 が①の t1 より短くなっていることを確認します。

複合インデックス — 複数列をまとめて索引する

CREATE INDEX 名前 ON 表(列1, 列2);のように複数列を並べると複合インデックス（コンポジットインデックス）になります。

WHERE 列1 = ... AND 列2 >= ...のように先頭の列から順に条件で使うクエリで処理を高速化します。

ただし列の順序が重要で、先頭の列をWHEREで使わないクエリには複合インデックスは効きません。

「emp_idで等値、sale_dateで範囲」のように検索パターンが決まっているなら、その順で複合インデックスを作ると 1 つの索引で両方の条件を活かせます。

-- 複合インデックス（読むだけの別例）
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(region, sale_date);

EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE region = 'East' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING INDEX ix_demo (region=? AND sale_date>?)
-- region を等値で絞り、sale_date を範囲でさらに絞る

「emp_idで担当者を指定しつつ、sale_dateで期間を絞る検索が多いので、1 つの索引で両方の条件を活かしたい」という要件を想定します。

① DROP INDEX IF EXISTS ix;を書いてから始めてください。

② perf_salesに、emp_idとsale_dateをこの順で並べた複合インデックスをixという名前で作成してください。

③ 続けて、emp_id = 7かつsale_date >= '2024-01-01'で絞ってsale_idとamountを取り出すクエリにEXPLAIN QUERY PLANを付けて実行し、複合インデックスが効くことを確認してください。

部分インデックスと式インデックス

部分インデックス（partial index）はCREATE INDEX 名前 ON 表(列) WHERE 条件;のように、テーブルの一部の行だけを対象にした索引です。

status = 'pending'のように特定の値の行だけをよく検索する場合、その行だけを索引に含めると索引が小さく済み、対象の検索に絞ることができます。

perf_salesは pending が 5,000 行（全体の 1 割）なので、WHERE status = 'pending'を付けた部分インデックスが向きます。

式インデックス（expression index）はCREATE INDEX 名前 ON 表(式);のように、列そのものではなく計算式の結果に索引を張ります。

WHERE amount / 100 = ...のように検索条件側で同じ式を使うクエリにインデックスを作成します。

列に関数や計算を適用すると通常のインデックスは効きませんが、その式を索引にしておけば、同じ式での検索を索引検索にできます。

-- 部分インデックス（読むだけの別例）
DROP INDEX IF EXISTS ix_part;
CREATE INDEX ix_part ON perf_sales(sale_date) WHERE status = 'refunded';
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales
WHERE status = 'refunded' AND sale_date >= '2024-01-01';
--> SEARCH perf_sales USING ... INDEX ix_part (sale_date>?)

-- 式インデックス
DROP INDEX IF EXISTS ix_expr;
CREATE INDEX ix_expr ON perf_sales(qty * amount);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE qty * amount = 100000;
--> SEARCH perf_sales USING ... INDEX ix_expr (<expr>=?)

「未確定（status = 'pending'）の売上を期間で検索することが多いので、その行だけを対象にした小さな索引を作りたい」という要件を想定します。

① DROP INDEX IF EXISTS ix;を書いてから始めてください。

② perf_salesのsale_date列に、status = 'pending'の行だけを対象にした部分インデックスをixという名前で作成してください。

③ status = 'pending' AND sale_date >= '2024-01-01'で絞るクエリにEXPLAIN QUERY PLANを付けて実行し、部分インデックスが効く（SEARCH ... USING INDEX ix）ことを確認してください。

④ さらに、同じ列を使うがstatus = 'paid'の場合（例: status = 'paid' AND sale_date >= '2024-01-01'）にもEXPLAIN QUERY PLANを実行し、部分インデックスの条件に合わないため使われない（SCAN perf_sales）ことを確認してください。

「金額を 100 で割った区分値（amount / 100）で検索することがあるので、その計算結果に索引を張りたい」という要件を想定します。列に計算を適用した検索は通常の列インデックスでは効かないため、式インデックスを使います。

① DROP INDEX IF EXISTS ix;を書いてから始めてください。

② perf_salesに、amount / 100という式に対するインデックスをixという名前で作成してください。

③ amount / 100 = 5000で絞るクエリにEXPLAIN QUERY PLANを付けて実行し、式インデックスが効く（SEARCH ... USING INDEX ix）ことを確認してください。

④ さらに、式を介さずamount = 500000で絞った場合（同じamount列を使うが索引と式が一致しない例）にもEXPLAIN QUERY PLANを実行し、式インデックスが使われない（SCAN perf_sales）ことを確認してください。

UNIQUE インデックス — 重複を許さない索引

CREATE UNIQUE INDEX 名前 ON 表(列);で作るUNIQUE インデックスを使えば、検索を速くして、なおかつその列に重複した値を入れさせないことができます。

すでに重複がある列に UNIQUE インデックスを作ろうとすると、UNIQUE constraint failedのエラーになり作成自体が拒否されます。

逆に値が一意の列（主キーや一意な業務キー）に作れば、重複を防ぎつつ等値検索も速くなります。

perf_salesではproduct（200 種）は同じ値が多数あるため UNIQUE インデックスは作れませんが、sale_idは主キーで一意なので作成できます。

次の演習ではsale_idに UNIQUE インデックスを作り、一意な列なら作成でき、その列の等値検索が索引で行われることを確認します。

-- UNIQUE インデックス（読むだけの別例）
DROP INDEX IF EXISTS ix_u_demo;
CREATE UNIQUE INDEX ix_u_demo ON perf_sales(sale_id);
EXPLAIN QUERY PLAN
SELECT sale_id FROM perf_sales WHERE sale_id = 12345;

-- 重複のある列に UNIQUE を作ろうとすると拒否される
-- CREATE UNIQUE INDEX ix_bad ON perf_sales(product);
--> UNIQUE constraint failed: perf_sales.product
-- product は 200 種が 5 万行に重複して入っているため作れない

「sale_idは売上ごとに一意なので、重複を防ぎつつ 1 件取得を速くしたい」という要件を想定します。本記事の最後の演習です。

① DROP INDEX IF EXISTS ix;を書いてから始めてください。

② perf_salesのsale_id列にUNIQUE インデックスをixという名前で作成してください（sale_idは一意なので作成できます）。

③ 続けて、sale_id = 12345で絞ってsale_idとamountを取り出すクエリにEXPLAIN QUERY PLANを付けて実行し、1 件取得が索引で行われることを確認してください。

Tips — インデックスを付けるかの判断基準

目安は 該当行が全体の 15〜20% 以下に絞り込めるときにインデックスが SCAN より速くなります。それより多く該当するとオプティマイザは自動で SCAN を選びます。

- 付ける: 大きいテーブルで等値・範囲検索する列、主キー / 外部キー / UNIQUE、JOIN キー、ORDER BY / GROUP BY の対象列

- 付けない: 数千行以下の小さいテーブル、gender のような選択率の低い列、書き込みが多いテーブル、LIKE '%foo%'（中間一致は効かない）

基本はEXPLAIN QUERY PLANで SCAN / SEARCH を確認し、遅いクエリだけにインデックスを足します。