チューニング

SQLite の実行計画

SQLite の EXPLAIN QUERY PLAN と SCAN/SEARCH、カバリングの見分け、VDBE への降り方。

SQLite には 2 種類の EXPLAIN がある

SQLite の EXPLAIN は2 段階あり、役割が明確に分かれています:

EXPLAIN QUERY PLAN: 高レベル。各テーブルへのアクセス方法（SCAN / SEARCH / USING INDEX）を階層的に表示。チューニングで普段使うのはこちら
EXPLAIN: 低レベル。SQLite 仮想マシン（VDBE）の opcode 列を出力。SQLite 自体のデバッグや、拡張機能を書くときに見る

まずは EXPLAIN QUERY PLAN を覚えれば十分です。クエリを実際には実行せず、計画だけを返すので副作用もありません。

sql

-- 高レベル: ふだんはこちら
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE user_id = 42;

-- 低レベル: VDBE opcode
EXPLAIN
SELECT * FROM orders WHERE user_id = 42;

SCAN と SEARCH の違い

EXPLAIN QUERY PLAN の行は、先頭の動詞がアクセス方式を表します:

SCAN TABLE t: テーブル全走査（フルスキャン）。小テーブル / 全件取得なら OK、大テーブルで見えたら要注意
SEARCH TABLE t USING INDEX idx (col=?): インデックスで絞り込んで取得。一番見たい形
SEARCH TABLE t USING COVERING INDEX idx: カバリング成功。テーブル本体を読まずインデックスだけで完結
SEARCH TABLE t USING INTEGER PRIMARY KEY (rowid=?): rowid（内部 ROWID）直接。最速
SEARCH TABLE t USING INDEX idx (col>?): 範囲検索でインデックス利用

「SCAN を SEARCH に変える」が SQLite チューニングの合言葉。ない列にインデックスを貼る、関数適用で SARG でない書き方を直す、といった定石は他 RDBMS と同じです。

階層出力の読み方

SQLite 3.8.1 以降、EXPLAIN QUERY PLAN は階層構造で出力されます。各行に id（ノード ID）と parent（親 ID）があり、インデントで JOIN やサブクエリの入れ子が表現されます。

読むときのポイント:

最も深い SEARCH / SCAN から実行される（他 RDBMS と同様、葉から）
JOIN は上から順に駆動表 → 内側。駆動表（外側）に SCAN が出ているなら、そこが件数支配的
USE TEMP B-TREE FOR ORDER BY や USE TEMP B-TREE FOR GROUP BY は一時 B-Tree 作成のサイン（MySQL の filesort / temporary 相当）。ソート / グループ列に複合インデックスを追加で消えることが多い

sql

EXPLAIN QUERY PLAN
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.plan = 'pro'
GROUP BY u.name;

-- 出力例
-- |--SEARCH TABLE users AS u USING INDEX idx_users_plan (plan=?)
-- |--SEARCH TABLE orders AS o USING INDEX idx_orders_user (user_id=?)
-- `--USE TEMP B-TREE FOR GROUP BY

カバリングインデックスと COVERING 表示

SQLite はクエリで参照する全列がインデックスに含まれると、自動的にUSING COVERING INDEX で動きます。テーブル本体へのアクセスが消えるので、同じクエリでも数倍〜10 倍速くなることも。

SQLite には PostgreSQL の INCLUDE に相当する構文はないので、キー列として含めるか、式インデックスや部分インデックスで工夫します。

sql

-- カバリングを狙ったインデックス
CREATE INDEX idx_orders_user_total
  ON orders (user_id, created_at, total);

-- このクエリは USING COVERING INDEX になる
EXPLAIN QUERY PLAN
SELECT user_id, created_at, total
FROM orders WHERE user_id = 42;

-- 出力
-- |--SEARCH TABLE orders USING COVERING INDEX idx_orders_user_total (user_id=?)

統計情報と ANALYZE

SQLite のオプティマイザは、統計情報が無くてもそこそこ動きますが、大きめのテーブル・複雑な JOIN では ANALYZE を実行して sqlite_stat1（および sqlite_stat4）を作るのが推奨です。

ANALYZE; で全テーブル、ANALYZE table_name; で単体のみ
データを大量更新した後は再実行すると計画が改善することがある
WAL モードの長期運用 DB では定期実行が無難

ビルドオプションで SQLITE_ENABLE_STAT4 が有効（公式配布は有効）なら、値分布のヒストグラムまで取れ、選択率推定の精度が上がります。

sql

-- 全テーブル
ANALYZE;

-- 1 テーブルだけ更新
ANALYZE orders;

-- 収集された統計を見る
SELECT * FROM sqlite_stat1;

低レベル EXPLAIN を使うとき

EXPLAIN QUERY PLAN で欲しい情報が得られないケースはほぼありません。低レベルの EXPLAIN（VDBE opcode）が役に立つのは:

SQLite 本体 / 拡張機能（virtual table, FTS, R-Tree）を作っていて、内部挙動を確認したい
バグ報告で SQLite 開発者向けに opcode 列が必要
極限までクエリを詰めたい（稀）

通常のアプリ開発では、EXPLAIN QUERY PLAN + ANALYZE + 適切なインデックス設計で十分です。SCAN を減らし、COVERING を増やすという基本を外さないこと。