SQLite 3 の SQL 問い合わせ計画（Windows 上）

【概要】

データベース管理システムは SQL 文をコンパイルし、問い合わせ計画を作る。問い合わせ計画とは、データベースに関する基本的なオペレータ（オペコード）の並びである。SQLite 3 では、SQL 文の前に EXPLAIN を付けることで問い合わせ計画を表示できる。ここでは、単一テーブルに対する問い合わせ、結合問い合わせ、二次索引がある場合のそれぞれについて、問い合わせ計画を確認する。

【目次】

前準備
SQL 問い合わせ計画の表示
結合問い合わせの SQL 問い合わせ計画の表示
二次索引がある場合の問い合わせ計画の例

【関連する外部ページ】

SQLite 3 のオペコードの説明：https://www.sqlite.org/opcode.html
SQLite 3 の SQL の説明：https://www.sqlite.org/lang.html

【サイト内の関連ページ】

リレーショナルデータベースの種々の概念と実装技術の探究のページ

公開資料はクリエイティブコモンズ表示-非営利-継承 4.0 国際ライセンス（CC BY-NC-SA 4.0）で提供しており，事前の許可なく自由に利用できます．条件は著作者表示（BY），非営利目的のみ（NC），同一ライセンスでの再配布（SA）です．

前準備

Windows で SQLite 3 のインストール: 別ページ »で説明

SQL 問い合わせ計画の表示

テーブルの全ての行の表示

次の SQL で行う。

SELECT * FROM results;

このときの SQL 問い合わせ計画を表示させる。そのために、SQL 文の前に「EXPLAIN」を付ける。

EXPLAIN SELECT * FROM results;

【表示された問い合わせ計画の要点】

アドレス (addr)	オペコード	主なオペランド
2	OpenRead	P2 = 14	ルート・ページが 14 であるようなテーブル（この場合は、テーブル results）のカーソルを作る (Open table 'results' for read, and make a cursor)
3	Rewind	P2 = 10	カーソルを、テーブルの先頭行を指し示すようにする。テーブルが空の場合には、アドレス 10（Close）にジャンプする (Move the cursor to the first row. If the table is empty then jump to '10')
4,5,6,7	Column	P2 = 0,1,2,3, P3 = 1,2,3,4	列番号 0, 1, 2, 3 の値を、それぞれレジスタ 1, 2, 3, 4 に格納する (Column values #0, #1, #2, #3 are stored into registers #1, #2, #3, #4)
8	ResultRow	P1 = 1, P2 = 4	レジスタ 1 からレジスタ 4 までの値を1行として出力する (Generate output using registers)
9	Next	P2 = 4	もし、カーソルが指し示すレコードが末端レコードならば、次の命令に進む。もし、カーソルが指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス 4 にジャンプする (Advance cursor to the next row. If there are more rows, then jump to the address '4')

条件を満足する行のみの表示

次の SQL で行う。

SELECT * FROM results WHERE student_name='KK';

このときの SQL 問い合わせ計画を表示させる。そのために、SQL 文の前に「EXPLAIN」を付ける。

EXPLAIN SELECT * FROM results WHERE student_name='KK';

【表示された問い合わせ計画の要点】

最初、カーソルは先頭行にセットされる。アドレス 6 の「Ne 1 12 2 collseq(BINARY)」は「条件付きジャンプ」であり、 SQL で指定されていた「student_name = 'KK'」の条件処理を行うためのものである。 条件を満たさない行の場合には、結果を出力するプログラムの部分（ここでは、アドレス 7 からアドレス 11 まで）を飛び越えてジャンプするようになっている。これで、条件を満たす行だけが評価結果に追加される。その後、カーソルは次の行へ動く。このようにして、results テーブルの全ての行が精査される。

結合問い合わせの SQL 問い合わせ計画の表示

次のような結合問い合わせを考える。

SELECT zips.zipcode, zips.choiki_kanji, shichosons.shichoson_kanji, kens.ken_kanji
FROM zips, kens, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji=kens.ken_kanji
      AND kens.id = 43;

まず 2 つのテーブルの結合、次に 3 つのテーブルの結合について、SQL 文の前に「EXPLAIN」を付けて実行し、問い合わせ計画を表示する。

EXPLAIN SELECT zips.zipcode, zips.choiki_kanji, shichosons.shichoson_kanji
FROM zips, shichosons
WHERE zips.jiscode = shichosons.jiscode;

EXPLAIN SELECT zips.zipcode, zips.choiki_kanji, shichosons.shichoson_kanji, kens.ken_kanji
FROM zips, kens, shichosons
WHERE zips.jiscode = shichosons.jiscode
      AND shichosons.ken_kanji=kens.ken_kanji
      AND kens.id = 43;

今度は、次のような結合問い合わせ（同一テーブル TT の自己結合）を考える。

select distinct R.choiki_kanji
FROM TT as R, TT as S
WHERE R.choiki_kanji = S.choiki_kanji
      AND R.jiscode <> S.jiscode;

SQL 文の前に「EXPLAIN」を付けて実行する。

EXPLAIN select distinct R.choiki_kanji
FROM TT as R, TT as S
WHERE R.choiki_kanji = S.choiki_kanji
      AND R.jiscode <> S.jiscode;

【表示された問い合わせ計画の要点】

アドレス (addr)	オペコード	主なオペランド
6	OpenRead	P1 = 0, P2 = 7	ルート・ページが 7 であるようなテーブル（この場合は、テーブル TT）のカーソルを作る。カーソル番号は 0 (Open table 'TT' for read, and make a cursor)
7	OpenRead	P1 = 1, P2 = 7	ルート・ページが 7 であるようなテーブル（この場合は、テーブル TT）のカーソルを作る。カーソル番号は 1 (Open table 'TT' for read, and make a cursor)
11	Rewind	P1 = 1, P2 = 18	カーソル 1 を、テーブルの先頭行を指し示すようにする。テーブルが空の場合には、アドレス 18 にジャンプする (Move the cursor to the first row. If the table is empty then jump to '18')
12	Rowid	P1 = 1, P2 = 12	カーソル 1 が指し示すレコードの主キーの値を、レジスタ 12 に格納する
13	Column	P1 = 1, P2 = 4, P3 = 10	カーソル 1 が指し示すレコードの列番号 4 の値を、レジスタ 10 に格納する (Save #4 column value into the register #10)
14	Column	P1 = 1, P2 = 3, P3 = 11	カーソル 1 が指し示すレコードの列番号 3 の値を、レジスタ 11 に格納する (Save #3 column value into the register #11)
15	MakeRecord	P1 = 10, P2 = 3	レジスタ 10 からレジスタ 12 までの3個の値からレコードをつくる（次の IdxInsert で使う）
16	IdxInsert	P1 = 3, P2 = 9	索引 3 に、前の MakeRecord 命令で作ったレコードを挿入する。 * 「索引 3」といっているのは、アドレス 10 の「OpenAutoindex」で生成された索引のことである。この索引は、今回の SQL 問い合わせの評価のために一時的に生成された索引である。MakeRecord 命令で作ったレコードの挿入が繰り返され、この「索引 3」の中で整列（ソート）された状態で保持される。
17	Next	P1 = 1, P2 = 12	もし、カーソル 1 が指し示すレコードが末端レコードならば、次の命令に進む。もし、カーソル 1 が指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス 12 にジャンプする (Advance cursor to the next row. If there are more rows, then jump to the address '12')
18	Rewind	P1 = 0, P2 = 32	カーソル 0 を、テーブルの先頭行を指し示すようにする。テーブルが空の場合には、アドレス 32 にジャンプする (Move the cursor to the first row. If the table is empty then jump to '32')
19	Column	P1 = 0, P2 = 4, P3 = 13	カーソル 0 が指し示すレコードの列番号 4 の値を、レジスタ 13 に格納する (Save #4 column value into the register #13)
21	SeekGe	P1 = 3, P2 = 31, P3 = 13, P4 = 1	いま、カーソル 3 は「索引 3」を指し示している。カーソル 3 の位置を、レジスタ 13 に入っている検索キーに等しいか検索キーよりも大きいという条件を満足するなかで最小の索引エントリを指し示すように動かす。そのようなエントリがない場合にはアドレス 31 にジャンプする
22	IdxGe	P1 = 3, P2 = 31, P3 = 13, P4 = 1	条件付きジャンプ。レジスタ 13 を検索キーとして使い、現在カーソル 3 が指し示している索引エントリと比較する。索引エントリが検索キーの値を超えている（一致する範囲を過ぎている）場合にはアドレス 31 にジャンプする。さもなければ次に進む。これにより、検索キーに一致する索引エントリだけが処理される
23	Column	P1 = 0, P2 = 3, P3 = 9	カーソル 0 が指し示すレコードの列番号 3 の値を、レジスタ 9 に格納する (Save #3 column value into the register #9)
24	Column	P1 = 3, P2 = 1, P3 = 14	カーソル 3 が指し示す索引エントリの列番号 1 の値を、レジスタ 14 に格納する (Save #1 column value into the register #14)
25	Eq	P1 = 14, P2 = 30, P3 = 9	条件付きジャンプ。レジスタ 14 の値とレジスタ 9 の値が等しいときに限り、アドレス 30 にジャンプする。これは、SQL で指定されていた「R.jiscode <> S.jiscode」の条件処理を行うためのものである（jiscode が等しい行の組は結果から除外される）
26	Column	P1 = 0, P2 = 4, P3 = 10	カーソル 0 が指し示すレコードの列番号 4 の値を、レジスタ 10 に格納する
28	MakeRecord	P1 = 10, P2 = 2	レジスタ 10 からレジスタ 11 までの2個の値からレコードをつくる（次の IdxInsert で使う）
29	IdxInsert	P1 = 2, P2 = 14	カーソル 2 が指し示す一時テーブルに、前の MakeRecord 命令で作ったレコードを挿入する。この挿入により、「DISTINCT」指定による重複除去が行われる。 * 「カーソル 2」といっているのは、最終結果として出力されるテーブルのために作られたカーソルのことである。このカーソルは、アドレス 1 の「OpenEphemeral」命令で生成されている
30	Next	P1 = 3, P2 = 22	もし、カーソル 3 が指し示すレコードが末端レコードならば、次の命令に進む。もし、カーソル 3 が指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス 22 にジャンプする
31	Next	P1 = 0, P2 = 19	もし、カーソル 0 が指し示すレコードが末端レコードならば、次の命令に進む。もし、カーソル 0 が指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス 19 にジャンプする

二次索引がある場合の問い合わせ計画の例

今度は、テーブル TT の属性 choiki_kanji についての二次索引を作り、先ほどと同じ SQL 問い合わせを評価させる。

create index idx1 on TT(choiki_kanji);
select distinct R.choiki_kanji
FROM TT as R, TT as S
WHERE R.choiki_kanji = S.choiki_kanji
      AND R.jiscode <> S.jiscode;

SQL 文の前に「EXPLAIN」を付ける。

EXPLAIN select distinct R.choiki_kanji
FROM TT as R, TT as S
WHERE R.choiki_kanji = S.choiki_kanji
      AND R.jiscode <> S.jiscode;

【問い合わせ計画の要点】

二次索引が働くとき、処理は二次索引上で行われる。先ほどのような一時的な索引（OpenAutoindex による索引）の生成は不要になる。

最初、カーソルは二次索引の先頭にセットされる。二次索引の中から、「R.choiki_kanji = S.choiki_kanji」という条件を満たす索引エントリを見つけ、そのエントリを使って、データが取り出される。このとき、S 側の探索では、カーソルは二次索引の中だけを動く。

二次索引のサイズは、テーブル本体のサイズよりずっと小さい (The size of the secondary index is much smaller than the table)
二次索引は、高速処理に向いたデータ構造になっている (The secondary index is a fast access path)