データベース sqlite3 その１４

viewは使い勝手のよい表示をするために生成されます。indexは多量なレコードを素早く見つけるために使われるものです。

データを表示するのにselect ステートメントを使ってターゲットとなるテーブルを指定して行います。しかし、複数のテーブルを結合させて表示するような場合は、その為に、長々とsql 文を書かなければいけないため、大変です。ビューとして予め生成しておけば、このような手間を省けます。

売り上げ管理システムデータベースを使って、このビューの使い方を見ていきたいと思います。

テーブルは以下のように生成されています。

takai@takai-pc ~/db/sales_manage
$ sqlite3 sales_manage.sq3
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
sqlite> .tables
branch               buyer                chglog_details       
details              chglog_price         goods                
orders

view の利用 [CREATE VIEW]

ビューは仮想テーブルなので、データの追加、更新、削除は出来ません。（但し、INSTEAD OF トリガーの場合は可能）利用目的は以下のようです。

• 正規化されたテーブル表示ではなく、直感的に感じる方法でデータを構造化し、表示できる。
• 完全なテーブルではなく限られたデータのみを表示できる。
• レポートの生成に使用できる。

CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column_name FROM table_name WHERE conditions;
table_nameテーブルから条件式にしたがって析出されたcolumn_name列を列とするビューを作成する。
SELECT column_name FROM view_name;
view_nameビューを表示する。
  元のテーブルの値が書き換えられると、select の実行結果が異なることがあります。
  insert文・update文・delete文は使用できない。
DROP INDEX database_name.view_name;
 ビューの削除

「CREATE」と「VIEW」の間に「TEMP」または「TEMPORARY」キーワードがある場合、作成されたビ-ーは、データベース接続が閉じられると自動的に削除されます。この場合は、viewにスキーマ名をつけてはいけない。

注文テーブルの外部キーに対応する名前データをviewに加える。

---- 注文テーブル orders のテーブル構成をみてみます。
sqlite> .schema orders
CREATE TABLE orders (id integer primary key not null, buyer_id integer not null,
publish text,
branch_id integer not null,
amount integer default 0,
iud_mark text check(iud_mark is null or iud_mark='i' or iud_mark='u' or iud_mark='d'),
foreign key(buyer_id) references buyer(id),
foreign key(branch_id) references branch(id));
  .
  .
  .

-- buyer_id と branch_id が外部キーになっていますので、buyer テーブルと branch テーブルをorders テーブルに結合させることにより、これらが、文字列として取り出すことができます。

---- view に使用するテーブルは buyer branch orders テーブルになります。
sqlite> select * from buyer;
id  name      flag
--  --------  ----
1   takai     0
2   arai      0
3   sato      0
4   kato      0
5   yamamoto  0
sqlite> select * from branch;
id   name  flag
---  ----  ----
101  ニコニコ  0
102  ホッコリ  0
103  アッタカ  0
104  トモダチ  0
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2023-08-21  101        3350    
2101002  2         2023-08-25  102        2000   
2102001  3         2023-08-22  103        850    
2102002  4         2023-08-23  101        310    
2102003  1         2023-08-01  103        6600
2102004  3         2023-08-20  102        1710    d

---- buyer_id branch_id の name をviewに盛り込んでみる。
sqlite> create view view_orders
   ...> as
   ...>  select orders.id, orders.publish, orders.amount, buyer.name as buyer_name, branch.name as branch_name
   ...> from orders
   ...> inner join buyer, branch on  -- 結合条件式を on で定義
   ...> orders.buyer_id = buyer.id
   ...> and orders.branch_id = branch.id
   ...> and buyer.del_flag=0
   ...> and branch.del_flag=0;
sqlite> select * from view_orders;
id       publish     amount  buyer_name  branch_name
-------  ----------  ------  ----------  -----------
2101001  2023-08-21  3350    takai       ニコニコ
2101002  2023-08-25  2000    arai        ホッコリ
2102001  2023-08-22  850     sato        アッタカ
2102002  2023-08-23  310    kato        ニコニコ
2102003  2023-08-01  6600    takai       アッタカ
2102004  2023-08-20  1710    sato        ホッコリ

sqlite> select * from view_orders where buyer_name='takai';
id       publish     amount  buyer_name  branch_name
-------  ----------  ------  ----------  -----------
2101001  2023-08-21  3350    takai       ニコニコ
2102003  2023-08-01  6600    takai       アッタカ

データ検索を高速化する index [CREATE INDEX]

index 作成は、本の索引を作るのと同じです。指定のデータにすばやくたどりつける目的で作られます。但し、SELECT クエリと WHERE 句を高速化するのに役立ちますが、UPDATE および INSERT ステートメントでのデータ入力を遅くします。index はテーブル内のデータへのポインター（実データへのアドレス）なので、データそのものには、削除・作成に影響を与えません。但し、以下の場合は作成するメ リットがありません。

• 小さなテーブル。
• 大規模なデータの更新・挿入が行われるテーブル
• 多数の NULL 値を含む列。
• 頻繁に操作される列。

CREATE [UNIQUE] INDEX インデックス名 ON テーブル名（カラム名）[WHERE 条件式];
UNIQUE 指定により、重複が生じた場合のエラー表示が出来ます。
WHERE句があると部分インデックスが作られます。
.indexes ?TABLE?         Show names of indexes
DROP INDEX インデックス名　インデックスを削除する。

index 利用するデータ量が無い、小さなテーブルですが、参考として、購入者 buyer テーブルの name 列にインデックスの仕組みを説明してみたいと思います。

sqlite> select * from buyer;
id  name      flag
--  --------  ----
1   takai     0
2   arai      0
3   sato      0
4   kato      0
5   yamamoto  0

---- name列に対してインデックスをつける
sqlite> create index buyer_index on buyer(name);

---- 作られてインデックス名を表示する

sqlite> .indexes
buyer_index
sqlite> select * from buyer;
id  name      flag
--  --------  ----
1   takai     0
2   arai      0
3   sato      0
4   kato      0
5   yamamoto  0
sqlite> select * from buyer where name='takai';
id  name   flag
--  -----  ----
1   takai  0   -- 表面的にはなんら変らないが、データ量が多いと表示速度が速くなるとおもわれます。

インデックスをつけるということとは

データベースのレコードはＢ木などの多分木というデータ構造として読み込まれます。rowidを持ったテーブルのレコードも、rowid列（あるいはprimary制約を持った列）をキーとした形で格納されます。

ある列にインデックスをつけるということは、rowid列の場合と同様に、その列をキーとして、多分木のデータ構造で仮想にデータ構造が作られると いうことになっております。多分木データの読み出しコストは、データ数をNとした場合に、logN に比例します。このことが、レコードを高速に読み出すことにつながっております。

インデックスを使わないbuyerテーブルの「name=’kato’ 」条件における「del_flag」の値を検索する

1. id列をキーとした多分木のデータ構造一つ一つのキーに対して、「name=’kato’」の条件を満たすレコードを検索する。—> オーダーNの線形探査になる。
2. 該当レコードからdel_flagの値を得る

インデックスを使ったbuyerテーブルの「name=’kato’ 」条件における「del_flag」の値を検索する

1. name列をキーとした多分木のデータ構造をたどる。—> オーダーlogNの探査になる。
2. ターゲットとなる「name=’kato’」行にたどりつく。
3. 「name=’kato’」行のid属性の値からbuyerテーブルの多分木データ構造であるidのレコードを得る。—> オーダーlogNの探査になる。
4. そのdel_flagの値を得る。