実行できるSQLiteドキュメント: 複合インデックス

Q: SQLite の複合インデックスとは？

複合インデックスとは、2つ以上の列をまとめて1つにしたインデックスのことです。CREATE INDEX idxname ON table(cola, colb) のように作成します。エントリは最初に cola で並び、同じ cola の中で colb 順に並びます。電話帳で「姓 → 名」の順にソートされているのと同じイメージですね。

Q: 複合インデックスでは列の順序は重要ですか？

かなり重要です。SQLite は WHERE 句が最左プレフィックスにマッチしているときだけ複合インデックスを使えます。例えば (a, b, c) のインデックスは、a だけ、a と b、3つ全部、で絞り込むクエリには効きますが、b だけ・c だけで絞り込むクエリには使えません。

1つのインデックスに複数のカラムをまとめる

複合インデックス(マルチカラムインデックス)は、2つ以上のカラムをまとめて1つのインデックスにしたものです。SQLite で複合インデックスを作るときは、対象のカラムを順番に並べて指定します。

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    created_at TEXT NOT NULL,
    total REAL NOT NULL
);

CREATE INDEX idx_orders_customer_status
    ON orders(customer_id, status);

INSERT INTO orders(customer_id, status, created_at, total) VALUES
    (1, 'paid',    '2026-01-10', 49.00),
    (1, 'paid',    '2026-02-03', 12.50),
    (1, 'pending', '2026-02-14',  9.99),
    (2, 'paid',    '2026-01-22', 80.00),
    (2, 'refunded','2026-02-01', 25.00);

SELECT * FROM orders WHERE customer_id = 1 AND status = 'paid';

インデックス idx_orders_customer_status は、まず customer_id でソートされ、各カスタマーの中で status でソートされた順にエントリを保持しています。SQLite の複合インデックスを理解するうえで、この「並び順」がすべての出発点です。あとの話はここから自然に導かれます。

イメージで掴む:ソート済みの電話帳

昔ながらの電話帳を思い浮かべてください。エントリは姓でソートされ、同じ姓の中ではさらに名でソートされています。(last_name, first_name) に張ったインデックスは、まさにこれと同じ構造です。

検索によって速いものと遅いものがあります:

「Patel という姓の人を全員探す」— 簡単。Patel さんが固まって並んでいます。
「Priya Patel を探す」— 簡単。Patel まで飛んで、そこから Priya を探すだけです。
「Priya という名の人を全員探す」— 遅い。Priya はあらゆる姓の中に散らばっているので、全ページを舐めるしかありません。

SQLite のマルチカラムインデックスもまったく同じ仕組みです。最初のカラムが主のソートキーになり、2 番目のカラムは「最初のカラムの値が同じエントリ」の中でしかソートに効きません。

最左プレフィックスのルール

SQLite が複合インデックスをクエリに利用できるのは、WHERE 句がそのインデックスの最左プレフィックスを絞り込んでいるときだけです。(a, b, c) のインデックスを例に取ると:

a で絞る — インデックスが使われる。
a と b で絞る — インデックスが使われる。
a、b、c で絞る — インデックスが使われる。
b だけ、c だけ、あるいは b と c だけで絞る — インデックスは使われない。

これは EXPLAIN QUERY PLAN を使えば実際にその目で確認できます:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    kind TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);

CREATE INDEX idx_events_user_kind_time
    ON events(user_id, kind, occurred_at);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42 AND kind = 'login';

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE kind = 'login';

最初のプランは SEARCH events USING INDEX idx_events_user_kind_time と表示されています。一方、2 つ目は SCAN events に落ちています。kind だけで絞り込むと先頭の user_id 列が飛ばされてしまうので、このクエリではインデックスが使い物になりません。

列の順序は設計判断そのもの

最左プレフィックスが効くかどうかで結果が変わる以上、CREATE INDEX で列をどの順番に並べるかは、見た目の好みではなく立派な設計判断です。指針は次の 2 つ。

一番よく絞り込みに使う列を先頭に置く。 その列を先頭にしておけば、より多くのクエリでインデックスが効くようになります。
等価条件の列を範囲条件の列より前に置く。 SQLite は = でインデックスをピンポイントに辿り、そのうえで <、>、BETWEEN などを使って連続した範囲をスキャンできます。ただし、範囲スキャンが効くのは 最後に使われた 列だけです。

CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    region TEXT NOT NULL,
    sold_at TEXT NOT NULL,
    amount REAL NOT NULL
);

-- 良い例: regionで等価検索を先に、sold_atで範囲検索を後に。
CREATE INDEX idx_sales_region_time ON sales(region, sold_at);

INSERT INTO sales(region, sold_at, amount) VALUES
    ('EU', '2026-01-05', 120),
    ('EU', '2026-02-11', 80),
    ('US', '2026-01-09', 200),
    ('US', '2026-02-20', 150);

EXPLAIN QUERY PLAN
SELECT * FROM sales
WHERE region = 'EU' AND sold_at >= '2026-02-01';

実行計画には SEARCH sales USING INDEX idx_sales_region_time (region=? AND sold_at>?) と表示されます。SQLite はまず region = 'EU' の位置にジャンプし、そこから日付の範囲を順にたどっていきます。ところが列の順序を (sold_at, region) に入れ替えると、同じクエリでも日付範囲のすべての行をスキャンしながら、1行ごとに region を再チェックする羽目になります。

複合インデックス vs. 単一列インデックスを複数

よくある疑問がこれです。(a, b) に複合インデックスを1つ作るべきか、それとも a と b にそれぞれ単一列のインデックスを2つ作るべきか?

CREATE TABLE tickets (
    id INTEGER PRIMARY KEY,
    project_id INTEGER NOT NULL,
    assignee_id INTEGER NOT NULL,
    state TEXT NOT NULL
);

-- 選択肢A: 1つの複合インデックス。
CREATE INDEX idx_tickets_proj_state ON tickets(project_id, state);

-- 選択肢B(代替案): 2つの単一カラムインデックス。
-- CREATE INDEX idx_tickets_project ON tickets(project_id);
-- CREATE INDEX idx_tickets_state   ON tickets(state);

EXPLAIN QUERY PLAN
SELECT * FROM tickets
WHERE project_id = 7 AND state = 'open';

複合フィルタの場合、複合インデックスのほうが高速です。SQLite は (project_id, state) に一致するエントリへ一直線に到達できるからです。一方、単一カラムのインデックスを2つ用意した場合は、SQLite はたいていどちらか一方を使って行を絞り込み、ヒットした行ごとにもう一方のカラムを再チェックする動きになります。両方のインデックスを交差させるケースもありますが、2つのカラムを まとめて 検索するなら、複合インデックスのほうが素直で速い答えになります。

project_id と state をそれぞれ単独でも検索する場合は、両方持っておくのが無難です。複合フィルタ用の複合インデックスに加えて、state 単独で絞り込むクエリ向けに state の単一カラムインデックスを用意しておきましょう。

SQLite カバリングインデックスとは

クエリが必要とするすべてのカラム ― 絞り込みに使うカラムも、SELECT で取得するカラムも ― がインデックスに含まれていれば、SQLite はテーブル本体に一切アクセスせずにクエリへ応答できます。これがカバリングインデックス(covering index)で、クエリが出せる最速の形です。

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    issued_at TEXT NOT NULL,
    total REAL NOT NULL,
    notes TEXT
);

CREATE INDEX idx_invoices_cover
    ON invoices(customer_id, issued_at, total);

INSERT INTO invoices(customer_id, issued_at, total, notes) VALUES
    (1, '2026-01-12', 100, '初回'),
    (1, '2026-02-02', 250, NULL),
    (2, '2026-01-30',  75, '至急');

EXPLAIN QUERY PLAN
SELECT issued_at, total FROM invoices WHERE customer_id = 1;

プランには USING COVERING INDEX idx_invoices_cover と表示されています。このクエリは issued_at と total をインデックスから直接読み取っており、notes と id は不要なのでテーブル本体には一切アクセスしません。頻繁に走るホットなクエリをカバーするためだけに複合インデックスへ列を追加するのは、そのクエリが常時実行されているなら十分割に合うトレードオフです。

SQLite の複合ユニークインデックス

複合インデックスは、列の組み合わせに対するユニーク制約の役割も果たします。単独の列だけでは一意にならないけれど、組み合わせとしては必ず一意であってほしい、というケースで便利です。

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id  INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL
);

CREATE UNIQUE INDEX idx_enrollments_pair
    ON enrollments(student_id, course_id);

INSERT INTO enrollments VALUES (1, 100, '2026-01-05');
INSERT INTO enrollments VALUES (1, 101, '2026-01-05');  -- OK、別のコース
INSERT INTO enrollments VALUES (1, 100, '2026-02-01');  -- 失敗：ペアが重複

3 件目の INSERT は UNIQUE constraint failed: enrollments.student_id, enrollments.course_id を返します。同じペアがすでにインデックス内に存在するため、SQLite は重複を弾いてくれます。

ハマりやすいポイント

先頭以外のカラムを OR でつなぐとインデックスが効かない。 インデックス (a, b) に対して WHERE a = 1 OR b = 2 と書いても、SQLite は両側を別々に評価する必要があるため、たいていインデックスをまったく使えません。
インデックス列に関数をかけると無効化される。 WHERE lower(email) = 'x' は email のインデックスを使ってくれません。式インデックスを張るか、INSERT 時にデータ側を正規化しておきましょう。
インデックスはタダではない。 INSERT、(インデックス対象列の) UPDATE、DELETE のたびに、すべてのインデックスが更新されます。書き込みの多いテーブルに複合インデックスを 3 つも貼ると、書き込みコストの大半をそれらが占めることになりかねません。
インデックスを作ったら ANALYZE を実行する。 SQLite のプランナは ANALYZE で集めた統計情報をもとに、複数のインデックス候補から最適なものを選びます。統計がないとヒューリスティックに頼ることになり、必ずしも最適とは限りません。

実践的なチューニングの流れ

遅いクエリをチューニングするときは、たいてい次のループを回すことになります。

クエリに対して EXPLAIN QUERY PLAN を実行し、いま SQLite が何をしているかを確認する。
スキャンしているなら WHERE 句を眺める — 等価比較しているのはどの列か、範囲検索はどの列か、SELECT しているのは何か。
等価比較を先頭、範囲検索を次に置いた順序で複合インデックスを作る。カバリングが効きそうなら SELECT する列も末尾に足す。
ANALYZE を実行する。
もう一度 EXPLAIN QUERY PLAN を実行する。プランが変わって、作ったインデックスが実際に使われていることを確認する。
本番に近いデータで、変更前と変更後のクエリ時間を計測する。

ステップ 6 を飛ばすと痛い目を見ます。プラン上は 正しく見える インデックスでも、テーブルが小さかったりプランナが別の経路を選んだりすると、実際にはむしろ遅くなることもあるからです。

次は: 部分インデックス

複合インデックスはテーブルのすべての行を対象にします。とはいえ実務では、本当に重要なのはごく一部の行だけ — 未対応のチケット、未処理のジョブ、論理削除されていないレコード — というケースがよくあります。部分インデックス を使えば、WHERE 句をインデックス自体に焼き付けて、対象行だけをインデックス化できます。次のページではこれを扱います。

よくある質問

SQLite の複合インデックスとは？

複合インデックスとは、2つ以上の列をまとめて1つにしたインデックスのことです。CREATE INDEX idx_name ON table(col_a, col_b) のように作成します。エントリは最初に col_a で並び、同じ col_a の中で col_b 順に並びます。電話帳で「姓 → 名」の順にソートされているのと同じイメージですね。

複合インデックスでは列の順序は重要ですか？

かなり重要です。SQLite は WHERE 句が最左プレフィックスにマッチしているときだけ複合インデックスを使えます。例えば (a, b, c) のインデックスは、a だけ、a と b、3つ全部、で絞り込むクエリには効きますが、b だけ・c だけで絞り込むクエリには使えません。

複合インデックスと単一列インデックスを複数作るのは、どちらが良い？

同じ列の組み合わせで日常的に絞り込みやソートをかけるなら複合インデックスが有利です。逆に、各列を独立に検索することが多いなら単一列インデックスを並べたほうが柔軟です。最終的には EXPLAIN QUERY PLAN を実行して、SQLite が実際にどのインデックスを選んだかを見るのが一番確実です。

SQLite のカバリングインデックスとは？

クエリが必要とする列をすべて含んでいるインデックスのことです。テーブル本体にアクセスせず、インデックスだけで結果を返せます。EXPLAIN QUERY PLAN で USING COVERING INDEX と表示されたらこの状態です。よく叩かれるクエリのために、複合インデックスにあえて参照列を追加してカバリング化するのは定番のチューニング手法です。

SQLite 複合インデックス：列の順序と最左プレフィックス