SQLite のインデックス：最左前置は制約ではなく整列の帰結

複合インデックス (a, b) が WHERE a = ? と WHERE a = ? AND b = ? に使え、WHERE b = ? には使えない、という口诀は誰もが覚えています。理由は単純です。インデックスとは整列された行の並びであり、その並びが何を引けるかを決めます。

インデックスは集合ではなく整列

CREATE INDEX idx ON t (a, b) が作る順序は、まず a の昇順、a が同じなら b の昇順です。ディスク上は概ね次のようになります。

a=1, b=1
a=1, b=5
a=1, b=9
a=2, b=3
a=2, b=7
a=3, b=2

a = 2 を引く：連続した一区間なので二分探索。 a = 2 AND b = 7 を引く：a = 2 の区間を特定し、その中は b で整列しているのでさらに二分探索。

b = 7 を引く：7 は各区間に散っています。すべての答えを含む連続区間は存在しません。全表走査しかありません。

これはデータベースの約束事ではなく、整列の必然です。

等値と範囲の境界

複合インデックスでは、最初の範囲条件が後続の列の使用を打ち切ります。

-- (a, b) を使える
WHERE a = 1 AND b = 2

-- a は使えるが b は整列のみで位置特定には使えない
WHERE a = 1 AND b > 2

-- b は絞り込みに使えない
WHERE a > 1 AND b = 2

三番目が最も間違えやすい形です。a > 1 は複数の区間に当たり、区間ごとに b が独立して整列しています。

列の順序の決め方

規則	理由
等値列を先、範囲列を後	範囲は後続列を打ち切る
選択性の高い列を先	より早く行を絞れる
被覆列は最後に足す	テーブル参照を避ける

三番目が最も実用的です。

CREATE INDEX idx_user_time ON posts (user_id, created_at, title);

SELECT created_at, title FROM posts WHERE user_id = ? ORDER BY created_at DESC はインデックスだけで答えられます。列が増えるほどインデックスは太り、行全体を抱えるならテーブルを読む方がましです。

推測せず EXPLAIN で確かめる

EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 7 ORDER BY created_at DESC;

SEARCH posts USING INDEX idx_user_time なら実際に使われています。SCAN は全表走査です。SQLite では ANALYZE が統計を更新し、最適化器が適切なインデックスを選びやすくなります。

インデックスの順序は整列の順序です。どの順で取りたいかを先に決めれば、列順は自然に決まります。