クエリプラン文。
成果物
explain
ステートメントの先頭にexplain query plan
を付与すると、そのステートメントのクエリプランを表示する。
以下ドットコマンドを使えばそれを省略できる。
.eqp on
一覧
- 全表スキャン
- 固定行
- 主キー
- インデックス
- 複数列インデックス
- カバリング・インデックス
order by
,group by
,distinct
- サブクエリ(副問合せ)
- 複合クエリ(
union
,union all
,except
,intersect
)
結論:改善方法
全表スキャン
select 列名 from 表名;
`--SCAN TABLE 表名
固定行
select 1;
`--SCAN CONSTANT ROW
主キー
SELECT 列名 FROM 表名 WHERE rowid=値;
`--SEARCH TABLE 表名 USING INTEGER PRIMARY KEY (rowid=?)
インデックス
CREATE INDEX Idx1 ON FruitsForSale(Fruit); SELECT Price FROM FruitsForSale WHERE Fruit='Orange';
`--SEARCH TABLE 表名 USING INDEX インデックス名 (列名=?)
複数列インデックス
CREATE INDEX Idx3 ON FruitsForSale(Fruit, State); SELECT price FROM FruitsForSale WHERE Fruit='Orange' AND state='CA';
QUERY PLAN
`--SEARCH TABLE FruitsForSale USING INDEX Idx3 (Fruit=? AND State=?)
カバリング・インデックス
CREATE INDEX Idx4 ON FruitsForSale(Fruit, State, Price); SELECT Price FROM FruitsForSale WHERE Fruit='Orange' AND State='CA';
`--SEARCH TABLE FruitsForSale USING COVERING INDEX Idx4 (Fruit=? AND State=?)
order by
, group by
, distinct
一時B木
order by
, group by
, distinct
句が含まれるとき、一時的なBツリー構造構造を使ってソートすることがある。ほとんどの場合はインデックスを用いたほうが高速。
SELECT * FROM FruitsForSale ORDER BY Fruit;
|--SCAN TABLE FruitsForSale `--USE TEMP B-TREE FOR ORDER BY
サブクエリ(副問合せ)
select * from FruitsForSale where rowid=(select 1);
QUERY PLAN |--SEARCH TABLE FruitsForSale USING INTEGER PRIMARY KEY (rowid=?) `--SCALAR SUBQUERY 1 `--SCAN CONSTANT ROW
フラット化
before
SELECT t1.a, t2.b FROM t2, (SELECT x+y AS a FROM t1 WHERE z<100) WHERE="1" a="1">5
after
SELECT t1.x+t1.y AS a, t2.b FROM t2, t1 WHERE z<100 AND="1" a="1">5
before
のままではサブクエリ箇所を親クエリの行数回だけ繰り返すことになる。これを回避するためにafter
のようにテーブル結合する。これを「サブクエリのフラット化」と呼ぶ。
コルーチン
サブクエリがフラット化されなければ、その結果は一時テーブルに保存されるかコルーチンとして実行される。以下は後者。
sqlite> EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; QUERY PLAN |--CO-ROUTINE 0x20FC3E0 | `--SCAN TABLE t1 USING COVERING INDEX i2 |--SCAN SUBQUERY 0x20FC3E0 `--USE TEMP B-TREE FOR GROUP BY
フラット化されたら以下。
sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1; QUERY PLAN |--SEARCH TABLE t2 USING INDEX i4 (c=?) `--SCAN TABLE t1
サブクエリのコンテンツに複数回アクセスする必要がある場合、コルーチンはデータを複数回計算する必要があるため、コルーチンの使用は望ましくない。
明示一時テーブル
サブクエリをフラット化できない場合、サブクエリを一時テーブルに明示することでコルーチンを回避できる。
sqlite> SELECT * FROM > (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x, > (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y; QUERY PLAN |--MATERIALIZE 0x18F06F0 | `--SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) |--MATERIALIZE 0x18F80D0 | |--SEARCH TABLE t2 USING INDEX i4 (c=?) | `--USE TEMP B-TREE FOR ORDER BY |--SCAN SUBQUERY 0x18F06F0 AS x `--SCAN SUBQUERY 0x18F80D0 AS y
複合クエリ(union
,union all
,except
,intersect
)
一時B木
union
,union all
,except
,intersect
は個別に計算されて割り当てられ、EXPLAIN QUERY PLAN
出力で独自の行が与えられる。
sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2; QUERY PLAN `--COMPOUND QUERY |--LEFT-MOST SUBQUERY | `--SCAN TABLE t1 USING COVERING INDEX i1 `--UNION USING TEMP B-TREE `--SCAN TABLE t2 USING COVERING INDEX i4
上記の出力の USING TEMP B-TREE
句は、2つのサブセレクトの結果のUNIONを実装するために一時的なBツリー構造が使用されることを示している。化合物を計算する別の方法は、各サブクエリをコルーチンとして実行し、出力がソートされた順序で表示されるように調整し、結果をマージすることである。クエリプランナーが後者のアプローチを選択すると、EXPLAIN QUERY PLAN
出力は次のようになります。
sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1; QUERY PLAN `--MERGE (EXCEPT) |--LEFT | `--SCAN TABLE t1 USING COVERING INDEX i1 `--RIGHT |--SCAN TABLE t2 `--USE TEMP B-TREE FOR ORDER BY
改善方法
問題状態 | 改善方法 |
---|---|
全表スキャン 一時B木( order by , group by , distinct ) |
インデックス 複数列インデックス カバリング・インデックス 主キー |
サブクエリ・コルーチン | フラット化 明示一時テーブル |
複合クエリの一時B木は改善不可。複数の異なるテーブルにまたがってインデックスを作ることはできないはず。よって複合クエリのときはインデックス使用を諦めて一時B木を用いるしかない。別解の参考。
ってことで合ってるかな?
対象環境
- Raspbierry pi 3 Model B+
- Raspbian stretch 9.0 2018-11-13
- bash 4.4.12(1)-release
- SQLite 3.29.0
- MeCab 0.996ユーザ辞書
$ uname -a Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux
前回まで
- SQLite3学習 俯瞰まとめ
- SQLite3学習 環境構築まとめ
- SQLite3学習 インタフェースまとめ(C言語、CLI、対話モード、Tcl...)
- SQLite3学習 ドットコマンドまとめ
- SQLite3学習 JSON拡張まとめ
- SQLite3学習 FTSまとめ(ICU, MeCab)
- SQLite3学習 再帰クエリ(WITH RECURSIVE)
- SQLite3学習 R-Treeモジュール
- SQLite3学習 Geopoly(2次元ベクタ画像の生成)
- SQLite3学習 拡張関数(generate_series)
- SQLite3学習 拡張ライブラリ数学関数(extension-functions.c)
- SQLite3学習 謎と名前
- SQL文の分類(DDL,DML,TCL,DCL)
- SQL構文 alter(rename)
- SQL構文 alter(add column)概要
- SQL構文 alter(add column)制約
- SQL構文 alter(add column)sqlite_master変更しても反映されない
- SQL構文 alter(add column)スキーマ再定義(テーブル再作成による定義変更)
- SQL構文 analyze
- SQL構文 attach/detach
- SQLite3構文 begin,end,commit,rollback,savepoint(deferred,immediate,exclusive)
- SQLite3構文 コメント
- SQLite3構文 create/drop
- SQLite3構文 index(create/drop)
- SQLite3構文 table(create/drop)
- SQLite3構文 列制約(default)
- SQLite3構文 列制約(collate)
- SQLite3構文 列制約(primary key)
- SQLite3構文 列制約(primary key)ベストプラクティス
- SQLite3構文 列制約(unique)
- SQLite3構文 列制約(not null)
- SQLite3構文 列制約(check)
- SQLite3構文 列制約(foreign key references)
- SQLite3構文 表制約(primary key, unique, check, foreign key)
- SQLite3でメタデータを取得する方法(DB名(スキーマ名)、テーブル名、列名、制約)
- SQLite3でTEMPの保存先を指定する
- SQLite3構文 delete
- SQLite3ビルド失敗(SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
- SQLite3をソースからビルドする(SQLITE_ENABLE_UPDATE_DELETE_LIMIT)
- SQLite3構文 delete(limit offset, order by)
- SQLite3クエリプランニング(インデックスの働き)