SQL 問い合わせ計画 (SQL query plan)
大学で使用した自作の資料等を,手直しの上公開している. クリエイティブ・コモンズ BY NC SA.
リレーショナルデータベースの基礎であるテーブル定義,一貫性制約,SQL,結合と分解,トランザクション,埋め込みSQL,実行計画,二次索引を学ぶ.SQLite 3 を用いて,SQL についての演習も行う.
【サイト内の関連ページ】
演習で行うこと
- SQL 問い合わせ計画 (SQL Query Plan)
SQLite 3 の SQL 演習に関連する部分
- explain <SQL 問い合わせ>
SQL 文の前に「explain」を付けると,SQL は実行されずに,SQL 問い合わせ計画が表示される. 問い合わせ計画とは,SQLite 3の場合には,仮想のデータベースマシンが実行する命令列である.
【SQLite 3 の主要なオペコード (Opcode) の要点】
- OpenRead: ルート・ページが P2 であるようなテーブルのカーソルを作る. P1 にはカーソル番号を設定する. P4 には、テーブルの列数を設定するか, KeyInfo 構造体 (KeyInfo 構造体)と呼ばれる構造体へのポインタを設定する.
- Rewind: 将来の Column, Rowid, Next 命令の実行に備えて,カーソル P1 をテーブルの先頭を指し示すようにする.テーブルが空の場合には,アドレス P2 にジャンプする.o
- Column: カーソル P1 (cursor P1) が指し示すレコードの P2 番目の桁 (P2-th column) からデータを取り出して,レジスタ P3 に格納する.(列番号は 0 から始まる)
- Rowid: カーソル P1 (cursor P1) が指し示すレコードの主キーの値を,レジスタ P2 に格納する.
- ResultRow: レジスタP1 から レジスタ(P1+P2-1) までの値を1行として出力する
- Ne: レジスタP1 の値とレジスタ P3 の値が等しくないときに限り,アドレス P2 (address P2) にジャンプする.
- Next: もし,カーソル P1 (cursor P1) が指し示すレコードが末端レコードならば、次の命令に進む. もし,カーソル P1 (cursor P1) が指し示すレコードが末端レコードでなければ、カーソル P1 (cursor P1) を1つ進めて、アドレス P2 (address P2) にジャンプする.
- Goto: アドレス P2 (address P2) にジャンプする.
* SQLite 3 のオペコードの説明は http://www.hwaci.com/sw/sqlite/opcode.html にある.
* SQLite 3の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある.
Sqliteman で既存のデータベースを開く (Open an existing database using Sqliteman)
すでに作成済みのデータベースを,下記の手順で開くことができる.
以下の手順で,既存のデータベースファイルを開く. (Open an existing database file)
- 「File」→
「Open」
- データベースファイルを開く (Open Database File)
* Ubuntu での実行例(「/home/ubuntuuser/mydb」を開く場合)
データベースファイル /home/ubuntuuser/mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "/home/ubuntuuser/mydb2")
* Windows での実行例(「C:\SQLite\mydb」を開く場合)
データベースファイル C:\SQLite\mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")
要するに,/home/<ユーザ名>/SQLite 3の下の mydb を選ぶ.
SQL を用いたテーブル定義と一貫性制約の記述 (Table definition and integrity constraint specification using SQL)
以前の授業で定義した scores テーブルを使う。 scores テーブルのテーブル定義が残っている場合には、ここの操作は必要ない。 scores テーブルのテーブル定義が残っていない場合には、次の手順で定義する。
SQL を用いて,scores テーブルを定義し,一貫性制約を記述する. (Define 'scores' table and specify integrity constrants of the table using SQL)
リレーショナル・スキーマ (relational schema): scores( name, teacher_name, student_name, score )
- scores テーブルの定義 (Define a table)
次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).
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 Editor」のウインドウには,SQL プログラムを書くことができる. In the 'SQL string' window, you can write down SQL program(s).
- コンソールの確認 (Inspect console)
エラーメッセージが出ていないことを確認
SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)
以前の授業で定義した scores テーブルを使う。 scores テーブルが残っている場合には、ここの操作は必要ない。 scores テーブルが残っていない場合には、次の手順で作成する。
次のような scores テーブルを作る. (Construct table 'scores')
以下の手順で,SQL を用いて scores テーブルへの行の挿入を行う (Insert rows into table 'scores' using SQL)
- SQL プログラムの記述
「insert into ...」は行の挿入.ここには 5つの SQL 文を書き, 「begin transaction」と「commit」で囲む. ("insert into ..." means inserting a row into a table. Four SQL statements are wrote).
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;
- 複数の SQL 文の一括実行 (Run multiple SQL statements)
複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に,「Run multiple SQL statements ...」のボタンをクリックする. 「Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)
- 「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
テーブル一覧の確認、データベーススキーマの確認
- 「Tables」を展開すると,テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')
- 「System Catalogue」を展開し,「sqlite_master」をクリックすると,データベース・スキーマ (database schema) が表示されるので,scores の行を確認する (Database schema appears by clicking 'sqlite_master')
* 「2」 の部分は別の数値になっているはずである。 これらは、scores テーブルのデータが置かれるページ番号に関する情報 (rootpage) であり、 SQLite 3 システムが自動で決める値である。
Sqliteman を用いたデータのブラウズ (Browse Data using Sqliteman)
- まず,オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')
- 次に,scores テーブルをダブルクリックする (Select table 'scores')
- テーブル scoresが表示される (table 'scores' appears)
* もし,データに間違いがあれば,このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).
Sqliteman を用いた SQL 問い合わせ計画の表示
ここでは,SQL 問い合わせ計画の表示例を示す.
データベース管理システムは, SQL 文をコンパイルし,SQL 問い合わせ計画を作る. SQL 問い合わせ計画とは,データベースに関する基本的なオペレータの並びである. (Database management system compliles a SQL statement into a SQL query plan. SQL query plan is a sequence or a tree of database operations ).
SQLite 3の問い合わせ計画については,SQLite Virtual Machine Opcodes の Web ページなどを見てください.(please refer to https://www.sqlite.org/opcode.html for SQLite opcodes)
* テーブルの全ての行の表示 (List all rows of a table)
- SQL の問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the scores)
まずは scores テーブルを使った簡単な SQL を試す
SELECT * FROM scores;
- 問い合わせ計画の表示 (query plan)
今度は、SQL 文の前に「EXPLAIN」を付ける.(Add 'EXPLAIN' before a SQL statement)
EXPLAIN SELECT * FROM scores;
【表示された問い合わせ計画の要点】
アドレス (addr) | オペコード | 主なオペランド | |
2 | OpenRead | P2 = 2 | ルート・ページが 2 であるようなテーブル (この場合は,テーブル scores) のカーソルを作る (Open table 'scores' for read, and make a cursor) |
3 | Rewind | P2 = 13 | カーソルを,テーブルの先頭を指し示すようにする.テーブルが空の場合には,アドレス 13 (Close)にジャンプする (Use the first row. If the first row is empty then jump to '13') |
5, 6, 7, 8, 9, 10 | Column | P3 = 1,2,3,4,5,6, P4 = 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行として出力する (Generate output using registers) |
9 | Next | P2 = 4 | もし,カーソルが指し示すレコードが末端レコードならば、次の命令に進む. もし,カーソルが指し示すレコードが末端レコードでなければ、カーソルを1つ進めて、アドレス4 にジャンプする. (Advance cursor to the next tow. If there are more rows, then jump to the address '4') |
二次索引については、今後の授業で学ぶ。そのときは、 二次索引がないとき,テーブルの本体が1行ずつ処理される.テーブルの全ての行について処理が繰り返される. (このことを「tuple at a time」ともいう)ということを再度確認する。
* 条件を満足する行のみの表示 (List the rows which satisfy a given condition)
- SQL の問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the scores)
SELECT * FROM scores WHERE student_name='KK';
- 問い合わせ計画の表示 (query plan)
SQL 文の前に「EXPLAIN」を付ける.(Add 'EXPLAIN' before a SQL statement)
EXPLAIN SELECT * FROM scores WHERE student_name='KK';
アドレス 6 の「Ne 1 15 2 collseq(BINARY) 69」は「条件付きジャンプ」である. 条件を満たさないような行の場合には、結果を得るプログラムの部分をジャンプ (ここでは、7行目から11行めまで)するようになっている。これで、条件を満たす行だけが得られるようになる。
最初,カーソルは先頭行にセットされる. アドレス 6 の「Ne 1 15 2 collseq(BINARY) 69」は「条件付きジャンプ」であり、 SQL で指定されていたstudent_name = 'KK'」の処理を行うためのものである. (true の時に限り,新しい行が,評価結果に追加される) その後,カーソルは次の行へ動く. このようにして,scores テーブルの全ての行が精査される.