SQL 問い合わせ計画 (SQL query plan)

【概要】

SQL問い合わせ計画は，SQL問い合わせ（クエリ）がどのように実行されるかを示す計画で，SQL文の前に「EXPLAIN」を付けると表示される．SQLite 3では，オペコードと呼ばれるSQLite 3の仮想マシンが実行する基本命令の列である．主要なオペコードには，OpenRead（テーブル読み取り用のカーソル作成），Rewind（カーソルをテーブル先頭に位置づけ），Column（レコードの列データ取得），ResultRow（結果行の出力）などがある．カーソルはテーブル内の特定の位置を指し示すポインタで，オペコードによって操作され，テーブル内の行を順に処理するために使用される．条件付きジャンプはWHERE句の条件処理などに使用されるオペコードの機能で，レジスタの値を比較し，条件が満たされると指定されたアドレスにジャンプする．例えば「Ne 1 15 2」はレジスタ1と2の値が等しくないときにアドレス15へジャンプする．

【目次】

【関連する外部ページ】

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

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

1. 演習で行うこと

2. SQLite 3 の主要なオペコード

SQL 文の前に「explain」を付けると，SQL は実行されずに，SQL 問い合わせ計画が表示される．問い合わせ計画とは，SQLite 3 の場合，仮想のデータベースマシンが実行する命令列である．

【SQLite 3 の主要なオペコード (Opcode) の要点】

3. 既存のデータベースを開く

作成済みのデータベースを，下記の手順で開く．

  1. ツールバーの「DBオープン (Ctrl+O)」ボタンをクリックする．
  2. データベースファイルを開く

    * Ubuntu での実行例（「/home/ubuntuuser/mydb」を開く場合）．データベースファイル /home/ubuntuuser/mydb を選ぶ．

    * Windows での実行例（「C:\SQLite\mydb」を開く場合）．データベースファイル C:\SQLite\mydb を選び，「開く」をクリックする．

4. テーブル定義と一貫性制約の記述

以前の授業で定義した scores テーブルを使う．scores テーブルのテーブル定義が残っている場合には，この操作は不要である．残っていない場合には，次の手順で定義する．

SQL を用いて，scores テーブルを定義し，一貫性制約を記述する． 

リレーショナル・スキーマ (relational schema)：scores( name, teacher_name, student_name, score )
  1. scores テーブルの定義

    次の SQL を入力し，「▶ SQL実行 (F5)」を押下する． 

    create table scores (
    id            integer  primary key autoincrement not null,
    name          text     not null,
    teacher_name  text     not null,
    student_name  text     not null,
    score         integer  not null check ( score >= 0 and score <= 100 ),
    created_at    datetime not null,
    updated_at    datetime,
    unique (name, student_name) );

    * 「SQLエディタ」欄には，SQL プログラムを書くことができる．

  2. 出力欄の確認

    「出力」欄にエラーメッセージが表示されていないことを確認する．

5. テーブルへの行の挿入

以前の授業で定義した scores テーブルを使う．scores テーブルが残っている場合には，この操作は不要である．残っていない場合には，次の手順で作成する．

次のような scores テーブルを作る．

以下の手順で，SQL を用いて scores テーブルへの行の挿入を行う．

  1. SQL プログラムの記述

    「insert into ...」は行の挿入である．ここには 5つの SQL 文を書き，「begin transaction」と「commit」で囲む． 

    begin transaction;
insert into scores values( 1, 'Database',    'K', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 2, 'Database',    'K', 'AA', 75, datetime('now', 'localtime'), NULL );
insert into scores values( 3, 'Database',    'K', 'LL', 90, datetime('now', 'localtime'), NULL );
insert into scores values( 4, 'Programming', 'A', 'KK', 85, datetime('now', 'localtime'), NULL );
insert into scores values( 5, 'Programming', 'A', 'LL', 75, datetime('now', 'localtime'), NULL );
commit;
  2. 複数の SQL 文の一括実行

    「SQLエディタ」に記述した複数の SQL 文をまとめて実行するには，「▶ SQL実行 (F5)」を押下する．

  3. 「出力」欄の確認

    エラーメッセージが出ていないことを確認する．

6. テーブル一覧とデータベーススキーマの確認

  1. 「スキーマナビゲータ」にテーブルの一覧が表示されるので確認する．
  2. 「SQLエディタ」に「select * from sqlite_master;」と入力し，「▶ SQL実行 (F5)」を押下すると，「出力」欄にデータベーススキーマが表示されるので，scores の行を確認する．

    * 「2」の部分は別の数値になっている場合がある．これは，scores テーブルのデータが置かれるページ番号に関する情報 (rootpage) であり，SQLite 3 システムが自動で決める値である．

7. データのブラウズ

8. SQL 問い合わせ計画の表示

データベース管理システムは，SQL 文をコンパイルし，SQL 問い合わせ計画を作る．SQL 問い合わせ計画とは，データベースに関する基本的なオペレータの並びである．

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

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

アドレス (addr) オペコード 主なオペランド  
2 OpenRead P2 = 2 ルート・ページが 2 であるようなテーブル（この場合は，テーブル scores）のカーソルを作る．
3 Rewind P2 = 13 カーソルを，テーブルの先頭を指し示すようにする．テーブルが空の場合には，アドレス 13（Close）にジャンプする．
5, 6, 7, 8, 9, 10 Column P2 = 1,2,3,4,5,6, P3 = 2,3,4,5,6,7 列番号 1,2,3,4,5,6 の値を，それぞれレジスタ 2,3,4,5,6,7 に格納する．
11 ResultRow P1 = 1, P2 = 7 レジスタ 1 からレジスタ 7 までの値を1行として出力する．
12 Next P2 = 4 カーソルが指し示すレコードが末端レコードならば，次の命令に進む．末端レコードでなければ，カーソルを1つ進めて，アドレス 4 にジャンプする．

二次索引については，今後の授業で学ぶ．二次索引がないとき，テーブルの本体が1行ずつ処理される．テーブルの全ての行について処理が繰り返される（このことを「tuple at a time」ともいう）．

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