実行できるSQLiteドキュメント: EXPLAIN QUERY PLAN

Q: 出力の SCAN と SEARCH は何が違う?

SCANはテーブルやインデックスを全行なめている状態。データが小さいうちは問題ありませんが、件数が増えるとどんどん重くなります。一方SEARCHはインデックスや主キーを使って該当行に直接ジャンプしている状態です。大きなテーブルでは、絞り込みに使うカラムは基本的にSEARCHになっていてほしいところです。

Q: クエリがインデックスを使っているか確認するには?

EXPLAIN QUERY PLANをクエリの先頭に付けて実行し、出力にUSING INDEX やUSING COVERING INDEX が含まれているかをチェックします。SCAN しか出ておらずインデックス名が見当たらない場合は、フルテーブルスキャンになっているサインで、インデックスを張れば改善する可能性が高いです。

Q: EXPLAIN と EXPLAIN QUERY PLAN はどう使い分ける?

EXPLAINはSQLite内部の仮想マシンが実行するバイトコードを表示するもので、エンジンの内部挙動を追いたいときに使います。普段のクエリチューニングではあまり出番がありません。テーブルアクセスやインデックス利用状況を人間に分かりやすくまとめてくれるのはEXPLAIN QUERY PLANの方なので、パフォーマンス調査ではこちらを使うのが基本です。

EXPLAIN QUERY PLAN でクエリの実行計画を確認する

遅いクエリをチューニングする前に、まずは SQLite が実際にどう動こうとしているのかを把握する必要があります。EXPLAIN QUERY PLAN を使うと、プランナーが選んだ戦略 ── どのテーブルを、どの順番で読み、どのインデックスを使うのか(あるいは使わないのか) ── を短いサマリで表示してくれます。クエリ自体は実行されず、実行計画だけが返ってくるのがポイントです。

任意のステートメントの先頭に、次のキーワードを付けるだけで使えます。

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

出力はだいたい次のようになります。

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

この一行から、いろんなことが読み取れます。SQLite は users テーブルに対して SCAN ではなく SEARCH を実行していて、email 用に自動生成されたユニークインデックスを使い、検索キーは email。まさに理想的な動きです。

SCAN と SEARCH の違い:まず最初に読むべきポイント

クエリプランの各行は、必ず SCAN か SEARCH のどちらかで始まります。この違いは、EXPLAIN QUERY PLAN の出力の中でも一番大事なシグナルです。

SCAN <table> — テーブル全体(またはインデックス全体)を読み込みます。いわゆるフルテーブルスキャンで、コストはテーブルのサイズに比例して増えます。
SEARCH <table> USING ... — インデックスや主キーを使って、該当する行に直接ジャンプします。コストはテーブルサイズではなく、結果セットのサイズに比例します。

実際に並べて見てみましょう。片方の列にはインデックスがあり、もう片方にはありません:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

1つ目のプランには SEARCH orders USING INDEX idx_orders_customer と表示されていますね。一方、2つ目は SCAN orders です。status 列にインデックスが張られていないので、SQLite は全行を読みにいくしかありません。テーブルが小さければ気づきもしませんが、100万行クラスになると、ミリ秒で終わる処理が数秒かかる、なんてことになります。

とはいえ、SCAN が常に悪いわけではありません。小さなマスタテーブルや、実際に大半の行を返すクエリなら、スキャンこそが正解です。ただし、大きなテーブルに対して絞り込みの効くフィルタを書いているのに SCAN が出ているなら、それはインデックスを追加すべきというサインです。

インデックスが使われているかを確認する

注目すべきキーワードは USING INDEX <name> です(あるいは USING COVERING INDEX <name>。これについては後ほど詳しく説明します)。「このインデックスをプランナーが拾ってくれるはず」と思ってインデックスを作ったときの確認方法はこうです。

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

SEARCH events USING INDEX idx_events_user (user_id=?) と表示されればOKです。もし SCAN events になっている場合は、何らかの理由でプランナーがインデックスを使えていません。よくある原因は、WHERE lower(user_id) = ... のようにカラムを関数で包んでしまっているケース、型が一致しない比較をしているケース、あるいは LIKE '%foo%' のように先頭にワイルドカードが来ているケースです。

実際に確認してみましょう。

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

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

+ 0 を付けただけでインデックスは効かなくなり、プランは SCAN events に戻ってしまいます。インデックス列に何らかの式を被せると、同じことが起きます。

カバリングインデックスは表示が違う

クエリで必要な列がすべてインデックスに含まれている場合、SQLite はテーブル本体に触れずインデックスだけで結果を返せます。このときプランには USING COVERING INDEX と表示されます。

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

プラン例：SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?)。クエリで欲しいのは price ですが、インデックス側に sku と price の両方が入っているので、SQLite は元のテーブルを一切読みに行きません。これがいわゆる カバリングインデックスで、ルックアップ系のクエリではこれ以上速いプランはありません。複合インデックスにどのカラムを含めるか決めるとき、ぜひ覚えておきたいテクニックです。

JOIN の実行計画を読む

JOIN が絡んでくると、クエリプランは一気に面白くなります。プランの各行が JOIN 対象のテーブル 1 つに対応していて、行の順序がそのまま SQLite がテーブルを処理していく順番です。最初に出てくるテーブルが外側（outer）で、それ以降のテーブルは外側の 1 行ごとに引かれていきます。

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

よくある実行計画はこんな感じです。

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

上から順に読んでいきます。SQLite はまず主キーで該当の顧客を1件特定し、その顧客に紐づく注文を customer_id のインデックスを使って引いてきます。どちらの行も SEARCH になっていて、フルスキャンは発生していません。これが理想的な状態です。

もし2行目が SCAN o になっていたら、顧客を1件引くたびに orders テーブル全体を舐めることになります。大きなテーブルでは致命的です。対処法はほぼ決まっていて、JOIN に使うカラムにインデックスを張ることです。

複合クエリとサブクエリの実行計画

UNION や EXCEPT、サブクエリを含むクエリでは、実行計画がネストして表示されます。各ブランチが親の下にインデントされて並ぶ形です。

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

COMPOUND QUERY という見出しの下に、ブランチごとに 1 つずつ、子の行が 2 つ並んで表示されます。サブクエリや CTE についても同じ仕組みで、それぞれが独立したインデント付きのプランノードとして出てきます。読み方も同じで、SCAN か SEARCH かという観点でチェックしていけば OK です。

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

サブクエリは別のプランノード(「LIST SUBQUERY」など)として表示され、それぞれが独自のアクセス戦略を持ちます。各レベルで同じ観点からチェックしていきましょう。

EXPLAIN と EXPLAIN QUERY PLAN の違い

この2つはまったく別物なのですが、混同されがちです。

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN(QUERY PLANなし)を実行すると、SQLiteの仮想マシンが実行するバイトコードがそのまま出てきます。OpenRead、SeekRowid、Column、ResultRow といった低レベルのオペコードが何十行も並ぶアレです。エンジン自体をデバッグしたいなら役に立ちますが、クエリのチューニング目的ではほぼ使いません。

一方で EXPLAIN QUERY PLAN は、人間が読める形に要約してくれる、実際に欲しいやつです。迷ったらまず EXPLAIN QUERY PLAN、これを覚えておけばOKです。

SQLiteで遅いクエリを調べるときの流れ

クエリが遅いと感じたら、だいたい以下のループで原因を追いかけます。

そのクエリに EXPLAIN QUERY PLAN をかける。
テーブルごとの行を見て、「これは SCAN か? SEARCH か?」を確認する。大きなテーブルで SCAN が出ていたら要注意。
SCAN が特定のカラムで絞り込みをしているなら、そのカラムにインデックスを張ることを検討する。
JOINの場合は、内側のループのテーブルが結合キーで SEARCH USING INDEX になっているかを確認する。
インデックスを追加したら、もう一度 EXPLAIN QUERY PLAN を実行する。プランが変わっていればOK。変わっていなければ、プランナが「このインデックスは使う価値なし」と判断したということ。テーブルが小さすぎるか、絞り込みの選択性が低いのが典型的な原因です。

ステップ5の具体例を見てみましょう。

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- logs をスキャン

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- インデックス idx_logs_level (level=?) を使って logs を検索

プランが SCAN から SEARCH に変わりました。これがインデックスがちゃんと効いている合図です。(ただし、データがほとんど入っていない新しいテーブルだと、インデックスを使うほどの価値がないと判断されてスキャンのままになることもあります。データを入れるか ANALYZE を走らせれば、判断が切り替わるケースが多いです。)

クエリプランだけでは分からないこと

EXPLAIN QUERY PLAN が教えてくれるのはあくまで戦略であって、 コスト ではありません。クエリに 800ms かかったとか、5万行返ってきたといった情報は出てきません。そこを知りたいときは、実行時間 (CLI なら .timer on) と行数を見ましょう。プランとタイミングは補い合う関係で、プランは「なぜ遅いのか」、タイマーは「そもそも遅いのか」を教えてくれます。

もう2つ、押さえておきたい注意点があります。

プランはデータ量とともに変わります。100 行のテーブルなら気持ちよくスキャンしていたクエリも、100 万行になればインデックスが必要になります。開発用のサンプルデータではなく、本番相当のデータ量でプランを確認し直しましょう。
プランナーは ANALYZE で集めた統計情報を使って判断しています。統計がないとデフォルト値にフォールバックしますが、これは必ずしも良い結果になりません。統計が古かったり無かったりすることが、想定外のプランが出る原因としてよくあります。

次は ANALYZE と VACUUM

クエリプランナーは、テーブルやインデックスの統計情報をもとに判断を下しています。その統計が無かったり古かったりすると、いくらインデックスを完璧に張っていてもおかしなプランが出てしまいます。ANALYZE はその統計を最新に保つためのコマンドで、VACUUM はその相棒として、空き領域の回収とデータベースファイルのデフラグを担当します。次回はこの2つを見ていきましょう。

よくある質問

SQLiteのEXPLAIN QUERY PLANは何をしてくれるの?

クエリを実際に実行せず、「もし実行したらこう動く」という計画をSQLiteに教えてもらうための仕組みです。どのテーブルをスキャンするか、どのインデックスを使うか、JOINの順序などが分かります。SELECT・INSERT・UPDATE・DELETEの前にEXPLAIN QUERY PLANを付けるだけで使えます。

出力の SCAN と SEARCH は何が違う?

SCANはテーブルやインデックスを全行なめている状態。データが小さいうちは問題ありませんが、件数が増えるとどんどん重くなります。一方SEARCHはインデックスや主キーを使って該当行に直接ジャンプしている状態です。大きなテーブルでは、絞り込みに使うカラムは基本的にSEARCHになっていてほしいところです。

クエリがインデックスを使っているか確認するには?

EXPLAIN QUERY PLANをクエリの先頭に付けて実行し、出力にUSING INDEX <name>やUSING COVERING INDEX <name>が含まれているかをチェックします。SCAN <table>しか出ておらずインデックス名が見当たらない場合は、フルテーブルスキャンになっているサインで、インデックスを張れば改善する可能性が高いです。

EXPLAIN と EXPLAIN QUERY PLAN はどう使い分ける?

EXPLAINはSQLite内部の仮想マシンが実行するバイトコードを表示するもので、エンジンの内部挙動を追いたいときに使います。普段のクエリチューニングではあまり出番がありません。テーブルアクセスやインデックス利用状況を人間に分かりやすくまとめてくれるのはEXPLAIN QUERY PLANの方なので、パフォーマンス調査ではこちらを使うのが基本です。

SQLite EXPLAIN QUERY PLANで遅いクエリを見抜く方法