やってみる

アウトプットすべく己を導くためのブログ。その試行錯誤すらたれ流す。

SQLite3構文 explain

 クエリプラン文。

成果物

explain

 ステートメントの先頭にexplain query planを付与すると、そのステートメントのクエリプランを表示する。

 以下ドットコマンドを使えばそれを省略できる。

.eqp on

一覧

 結論:改善方法

全表スキャン

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木を用いるしかない。別解の参考

 ってことで合ってるかな?

対象環境

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで