左端プレフィックスの規則、カバリングインデックス、そしてインデックスをプランナーから見えなくする落とし穴を使いこなしましょう。
このレッスンを Kodokon で開く複合インデックスは、宣言された順序に従って、複数の列にまたがって行を並べます。決定的な規則は左端プレフィックスです。(a, b)のインデックスは、aのフィルター、あるいはaかつbのフィルターには役立ちますが、b単体には決して役立ちません。したがって列の順序は些細なことではなく、設計上の選択です。まずは下のデータセットを作成しましょう。
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
department_id INTEGER,
salary INTEGER NOT NULL
);
INSERT INTO employees
(id, name, department_id, salary)
VALUES
(1, 'Ada', 1, 65000),
(2, 'Grace', 1, 72000),
(3, 'Alan', 2, 58000),
(4, 'Edsger', 1, 90000);(department_id, salary)にインデックスを作成しましょう。department_idから始まるフィルターはSEARCHを引き起こします。しかしsalaryだけのフィルターは左端プレフィックスを崩します。プランナーは正しい行へ直接飛べず、SCANに戻ります。二つのプランを比べてみましょう。
CREATE INDEX idx_dept_salary
ON employees (department_id, salary);
-- Leftmost prefix present: index used
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE department_id = 1
AND salary > 60000;
-- SEARCH ... USING INDEX idx_dept_salary
-- Leading column missing: index ignored
EXPLAIN QUERY PLAN
SELECT id FROM employees
WHERE salary > 60000;
-- SCAN employeesカバリングインデックスは、クエリが読むすべての列を含みます。するとプランナーはテーブルを開かずに、インデックスから直接答えを返します。それがUSING COVERING INDEXの意味です。ここでは、インデックス(department_id, salary)が、その二つの列だけを読むクエリをカバーします。id（rowid）はすべてのインデックスに暗黙的に存在するので、これも無償でカバーされることを覚えておきましょう。
-- Every column read is in the index
EXPLAIN QUERY PLAN
SELECT department_id, salary
FROM employees
WHERE department_id = 1;
-- SEARCH employees USING COVERING INDEX
-- idx_dept_salary (department_id=?)(department_id, salary)の複合インデックスがあるとき、それを使えないクエリはどれですか？emailに対する単純なインデックスの使用を妨げるのはどれですか？