EXPLAINとクエリプラン：データベースが実際に何をしているかを読む

EXPLAIN QUERY PLANを使って、SQLiteのプランナーがあなたのクエリのために本当に選ぶプランを読み解きましょう。

10 分 · 3 問

SQLは宣言的です。結果を記述するのであって、それをどう得るかは記述しません。データをどう読むかを決めるのはクエリプランナーです。その働きを観察するために、SQLiteは二つのコマンドを用意しています。EXPLAIN QUERY PLANは読みやすい高レベルのプランを示し、一方EXPLAIN単体はVDBE仮想マシンのバイトコードを吐き出します。sqlite3コマンドラインツールをインストールするか、ブラウザでsqliteonline.comを開いて、これから続くすべての例を再現してみましょう。

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000);

任意のSELECTクエリの前にEXPLAIN QUERY PLANを置きます。結果の各行は、テーブルがどう読まれるかを説明します。二つのキーワードが中心になります。SCANはテーブルが丸ごと、一行ずつスキャンされることを意味し、一方SEARCHはインデックスに導かれた狙いを定めたアクセスを示します。適切なインデックスがなければ、単純な等値比較でさえ、すでに全体スキャンを引き起こします。

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Result: SCAN employees
-- (no index: the whole table is read)

フィルターされる列にインデックスを作成し、まったく同じクエリを実行し直します。プランはSCANからSEARCH ... USING INDEXへと切り替わります。ほかの表示も役立ちます。USE TEMP B-TREEはメモリ上で実体化されたソートやグルーピングを明かし（多くの場合、並べ替え用のインデックスが欠けているしるしです）、USING COVERING INDEXはテーブルが一度も開かれなかったことを知らせます。

CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)

理解度チェック

このレッスンの要点をしっかり覚えているか確認しましょう。

1. SQLiteで、EXPLAIN QUERY PLANはEXPLAIN単体と比べて何を返しますか？
   • VDBEバイトコードを、命令ごとに
   • 高レベルのプラン（SCAN、SEARCH、一時的なソート）
   • クエリの実際に測定された実行時間
   • テーブルに存在するインデックスの一覧
2. SQLiteのプランで、SCAN employeesという行は何を意味しますか？
   • テーブルが丸ごと、一行ずつスキャンされる
   • カバリングインデックスが使われる
   • インデックスを介して狙いを定めた検索が行われる
   • テーブルがメモリキャッシュに読み込まれる
3. ANALYZEコマンドは何のために使われますか？
   • データベースのすべてのインデックスを再構築する
   • プランナーを導くためにsqlite_stat1へ統計を集める
   • クエリを実行し、その実行時間を測る
   • 分析のあいだテーブルをロックする