一覧DB-07-2 · インデックスと実行計画

複合インデックスと検索条件

顧客・金額・日付を持つ100件の注文を用意し、customer_id・amount・dayの索引を使います。条件を一つずつ変えて探索に使われる列を確認し、並べ替え用の一時構造、式索引とLIKEの接頭辞条件まで調べます。

説明

複合索引は複数列を順に並べた検索構造です。先頭の列を等号で固定した後の範囲検索や並べ替えには使いやすい一方、先頭を飛ばした条件や列へ関数を掛けた条件では、別の探索になることがあります。

1検索条件の順序を調べる注文表

索引の列順は顧客ID、金額、日付です。

CREATE TABLE orders(id INTEGER PRIMARY KEY,customer_id INTEGER,amount INTEGER,day TEXT) STRICT;
WITH RECURSIVE n(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM n WHERE i<100) INSERT INTO orders SELECT i,(i%5)+1,(i%10)*100,printf('2026-11-%02d',(i%20)+1) FROM n;
CREATE INDEX orders_customer_amount_day

ON orders(customer_id,amount,day)索引の並び順

;
SELECT COUNT(*) AS rows,

COUNT(DISTINCT customer_id)顧客IDの種類数 AS customers FROM orders;

100行、顧客IDは1～5です。索引では先にcustomer_idで並び、同じ顧客内でamount、さらに同じ金額内でdayが並びます。SQLのWHEREへ条件を書く順序ではなく、CREATE INDEXの列順が索引の構造を決めます。

2先頭を等号、次を範囲にする

顧客3の200円以上700円未満を探します。

EXPLAIN QUERY PLAN SELECT id,amount FROM orders WHERE customer_id=3先頭列を等号で固定 AND amount>=200 AND amount<700次の列の範囲を指定

;
SELECT id,amount FROM orders WHERE customer_id=3 AND amount>=200 AND amount<700 ORDER BY id LIMIT 3;

計画の検索条件はcustomer_id=? AND amount>? AND amount<?です。最初の三件はID 2・12・22で金額200円です。顧客を固定した範囲内で金額の区間へ進むため、二列目まで探索範囲に使えます。

3範囲の後ろの条件

dayも条件へ加えます。

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE customer_id=3 AND amount>=200二列目が範囲 AND day='2026-11-03'範囲の後ろの列の条件

;
SELECT id,amount,day FROM orders WHERE customer_id=3 AND amount>=200 AND day='2026-11-03' ORDER BY id;

計画はcustomer_idとamountの範囲を示し、dayは探索範囲の項目に入りません。amountが複数値にまたがると、それぞれの金額内にdayの並びがあるため、三列目の一致を一つの連続区間として絞れないからです。dayの条件は候補へ追加で判定されます。

4前の二列を等号で固定する

amountを200に固定します。

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE customer_id=3 AND amount=200二列目も等号にする AND day='2026-11-03'三列目の一致も探索に使う;

計画の検索条件にcustomer_id・amount・dayの三列が並びます。前の列を等号で固定すると、その中にある次の列の連続した範囲を探索できます。

5先頭列を指定しない条件

金額だけを指定します。

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE amount=200先頭customer_idを指定しない;

この統計未採取の状態ではSCAN ordersです。顧客ごとに金額が並ぶ索引なので、amount=200の位置は顧客の区画ごとに分かれます。SQLiteには統計に応じて区画をまたぐskip-scanもありますが、先頭列なしなら必ず効率よく絞れるとは限りません。

6索引順に沿う並べ替え

顧客3に固定して金額順・日付順で取得します。

EXPLAIN QUERY PLAN SELECT id,amount,day FROM orders WHERE customer_id=3顧客の区画を固定 ORDER BY amount,dayその中の索引順に合わせる;

探索に使う索引の順がORDER BYとも一致し、一時的な並べ替えの行は出ません。顧客を固定した内部では、金額と日付がこの順に並んでいるためです。

7日付だけで並べる場合

同じ顧客の結果を日付だけの順にします。

EXPLAIN QUERY PLAN SELECT id,amount,day FROM orders WHERE customer_id=3 ORDER BY day索引の二列目を飛ばした並び順;

USE TEMP B-TREE FOR ORDER BYが加わります。索引ではamountがdayより先なので、金額を固定せずに日付だけで並べるには、別の並べ替えが必要です。索引で抽出できることと、並べ替えを省けることは別です。

8列へ関数を掛けた検索

名前に索引を付け、lower(name)で検索します。

CREATE TABLE names(id INTEGER PRIMARY KEY,name TEXT) STRICT;
INSERT INTO names VALUES(1,'ALICE'),(2,'Bob'),(3,'alicia');

CREATE INDEX names_plain ON names(name)元の文字列の索引

;
EXPLAIN QUERY PLAN SELECT id FROM names WHERE

lower(name)='alice'式の結果への条件

;
SELECT id,name FROM names WHERE lower(name)='alice';

通常のname索引ではこのlower(name)の一致を探索できず、SCANになります。結果はALICEの一行です。保存値そのものの順序と、式を計算した結果の順序を同一とは扱えません。

9検索式と一致する式索引

lower(name)自体を索引へ保存します。

CREATE INDEX names_lower ON names(lower(name))検索に使う式の索引

;
EXPLAIN QUERY PLAN SELECT id FROM names

WHERE lower(name)='alice'索引と同じ式;

names_lowerを使ったSEARCHへ変わります。SQLiteで式索引を使うには、問い合わせの式を索引の式に合わせます。数学的に同じ意味でも、別の書き方では一致と判定されない場合があります。

10LIKEの前方一致と部分一致

既定のLIKEのASCII比較に合わせ、NOCASEの索引を作ります。

CREATE INDEX names_nocase ON names(name COLLATE NOCASELIKEの比較に対応する索引

);
EXPLAIN QUERY PLAN SELECT id FROM names WHERE name

LIKE 'Al%'先頭の固定文字がある

;
EXPLAIN QUERY PLAN SELECT id FROM names WHERE name

LIKE '%li%'先頭が任意の文字列

;
SELECT id,name FROM names WHERE name LIKE 'Al%' ORDER BY id;

Al%はnames_nocaseの範囲SEARCH、%li%はSCANです。先頭の固定文字列がある場合は索引の連続範囲を使えますが、先頭が任意文字列では同じ方法で範囲を定められません。検索結果はALICEとaliciaです。

まとめ

複合索引は先頭からの等号と、その後の範囲条件の位置を確認します。

抽出と並べ替えの両方について計画を確認し、一時的な並べ替えの有無を調べます。

関数・照合順序・LIKEの固定接頭辞によって、索引を使える条件が変わります。

操作履歴と解説を振り返れます。

SQLite 3.54.0

CREATE TABLE orders(id INTEGER PRIMARY KEY,customer_id INTEGER,amount INTEGER,day TEXT) STRICT;
WITH RECURSIVE n(i) AS (VALUES(1) UNION ALL SELECT i+1 FROM n WHERE i<100) INSERT INTO orders SELECT i,(i%5)+1,(i%10)*100,printf('2026-11-%02d',(i%20)+1) FROM n;
CREATE INDEX orders_customer_amount_day ON orders(customer_id,amount,day);
SELECT COUNT(*) AS rows,COUNT(DISTINCT customer_id) AS customers FROM orders;

╭──────┬───────────╮
│ rows │ customers │
╞══════╪═══════════╡
│  100 │         5 │
╰──────┴───────────╯

EXPLAIN QUERY PLAN SELECT id,amount FROM orders WHERE customer_id=3 AND amount>=200 AND amount<700;
SELECT id,amount FROM orders WHERE customer_id=3 AND amount>=200 AND amount<700 ORDER BY id LIMIT 3;

QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_customer_amount_day (customer_id=? AND amount>? AND amount<?)
╭────┬────────╮
│ id │ amount │
╞════╪════════╡
│  2 │    200 │
│ 12 │    200 │
│ 22 │    200 │
╰────┴────────╯

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE customer_id=3 AND amount>=200 AND day='2026-11-03';
SELECT id,amount,day FROM orders WHERE customer_id=3 AND amount>=200 AND day='2026-11-03' ORDER BY id;

QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_customer_amount_day (customer_id=? AND amount>?)
╭────┬────────┬────────────╮
│ id │ amount │    day     │
╞════╪════════╪════════════╡
│  2 │    200 │ 2026-11-03 │
│ 22 │    200 │ 2026-11-03 │
│ 42 │    200 │ 2026-11-03 │
│ 62 │    200 │ 2026-11-03 │
│ 82 │    200 │ 2026-11-03 │
╰────┴────────┴────────────╯

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE customer_id=3 AND amount=200 AND day='2026-11-03';

QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_customer_amount_day (customer_id=? AND amount=? AND day=?)

EXPLAIN QUERY PLAN SELECT id FROM orders WHERE amount=200;

QUERY PLAN
`--SCAN orders

EXPLAIN QUERY PLAN SELECT id,amount,day FROM orders WHERE customer_id=3 ORDER BY amount,day;

QUERY PLAN
`--SEARCH orders USING COVERING INDEX orders_customer_amount_day (customer_id=?)

EXPLAIN QUERY PLAN SELECT id,amount,day FROM orders WHERE customer_id=3 ORDER BY day;

QUERY PLAN
|--SEARCH orders USING COVERING INDEX orders_customer_amount_day (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY

CREATE TABLE names(id INTEGER PRIMARY KEY,name TEXT) STRICT;
INSERT INTO names VALUES(1,'ALICE'),(2,'Bob'),(3,'alicia');
CREATE INDEX names_plain ON names(name);
EXPLAIN QUERY PLAN SELECT id FROM names WHERE lower(name)='alice';
SELECT id,name FROM names WHERE lower(name)='alice';

QUERY PLAN
`--SCAN names
╭────┬───────╮
│ id │ name  │
╞════╪═══════╡
│  1 │ ALICE │
╰────┴───────╯

CREATE INDEX names_lower ON names(lower(name));
EXPLAIN QUERY PLAN SELECT id FROM names WHERE lower(name)='alice';

QUERY PLAN
`--SEARCH names USING COVERING INDEX names_lower (<expr>=?)

CREATE INDEX names_nocase ON names(name COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT id FROM names WHERE name LIKE 'Al%';
EXPLAIN QUERY PLAN SELECT id FROM names WHERE name LIKE '%li%';
SELECT id,name FROM names WHERE name LIKE 'Al%' ORDER BY id;

QUERY PLAN
`--SEARCH names USING COVERING INDEX names_nocase (name>? AND name<?)
QUERY PLAN
`--SCAN names
╭────┬────────╮
│ id │  name  │
╞════╪════════╡
│  1 │ ALICE  │
│  3 │ alicia │
╰────┴────────╯

演習完了