サイトマップ サイト内検索 まとめ

二次索引 (secondary index)

URL: https://www.kkaneko.jp/data/dataset/7.html





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

* SQLite 3 のオペコードの説明は http://www.hwaci.com/sw/sqlite/opcode.html にある．

* SQLite 3の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある．

演習で行うこと

SQLite 3 の SQL 演習に関連する部分

SQLite 3の SQL の説明は http://www.hwaci.com/sw/sqlite/lang.html (English Web Page) にある．

郵便番号データベース (Japanese ZIP code database)

郵便番号データベースは zips, kens, shichosons の 3 つのテーブルから構成される．

郵便番号データベースの作成手順については別の Web ページで説明している．

Sqliteman で既存のデータベースを開く

すでに作成済みのデータベースを，下記の手順で開くことができる．

以下の手順で，既存のデータベースファイルを開く． (Open an existing database file)

  1. Sqliteman を起動する
  2. 「File」→ 「Open」
  3. データベースファイルを開く

    * Ubuntu での実行例（「SQLite/mydb」を開く場合）

    データベースファイル SQLite/mydb を選び， 「開く」をクリック (Click '開く' after choosing the database file "SQLite/mydb")

    * Windows での実行例（「C:\SQLite\mydb」を開く場合）

    データベースファイル C:\SQLite\mydb を選び， 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")

    要するに，/home/＜ユーザ名＞/SQLite 3の下の mydb を選ぶ．　

  4. データベースの中身が表示されるので確認する (Database appears)
  5. 「Tables」を展開すると，テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')

Sqliteman を用いたデータのブラウズ

zips, kens, shichosons テーブルの中身を表示してみる． 表示ができないというときは， zips, kens, shichosons テーブルを作る作業（前回の資料）を行っていないのかも知れない

データベースの構造の確認 (Database Structure)

  1. sqlite_master をクリック (Click 'sqlite_master)
  2. テーブルと二次索引のルート・ページ番号が分かる (Root page number of each table and secondary index)

zips, kens, shichosons の 3 つのテーブルのルートページ (root page) は，この資料では，次の値になっているものとして説明を続ける．

* ルート・ページ番号は，SQLite 3 システムが決める値なので， 実際には，上とは違う値になっていることが多い，

(The number is automatically decided by the database management system)

Sqliteman を用いた SQL 問い合わせ計画の表示

単一テーブルに対する問い合わせの SQL 問い合わせ計画の表示例 (SQL query plan)

条件を満足する行のみの表示 (List the rows which satisfy a given condition)

結合問い合わせの SQL 問い合わせ計画の表示例 (SQL query plan)

今度は結合問い合わせである (join query)

Sqliteman を用いた二次索引の追加 (generate a secondary index using Sqliteman)

ここでは，テーブル zips の属性 choiki_kanjiの二次索引を作る． (Generate a secondary index of the table 'zips')
  1. CREATE INDEX を用いた二次索引の生成 (Generate a secondary index using 'CREATE INDEX') 

    CREATE INDEX idx1 ON zips( choiki_kanji );

    「idx1は索引名である．索引の管理（索引の削除など）に使用される． ('idx1' is index name).

    索引は数秒以内で生成される．(The secondary index will be generated in a several seconds)

  2. Sqliteman での二次索引の確認

    idx1 が出来ている

二次索引による問い合わせ計画の変化 (secondary index and query plan)

* データベースの構造の確認 (Database Structure)

sqlite_master をクリック (Click 'sqlite_master)

テーブルと二次索引のルート・ページ番号が分かる (Root page number of each table and secondary index)

二次索引 idx1 のルート・ページ番号（ここでは「10357」）を確認しておく．ルート・ページ番号は，データベース管理システムが決める値なので，違う値になっているはずである．

(Inspect the root page number of the secondary index 'idx1'. The number is automatically decided by the database management system)

問い合わせ計画の表示 (query plan)

今度は，先ほどと同じ SQL 問い合わせを評価させる． 評価結果は同じになる． 評価にかかる時間は速くなる． 

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

SQL 文の前に「EXPLAIN」を付ける．(Add 'EXPLAIN' before a SQL statement) 

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

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

二次索引が働くとき，処理は二次索引上で行われる．

最初，カーソルは二次索引の先頭ページにセットされる． 二次索引の中から，「jiscode = 40135」という条件式を満たす索引エントリを見つけ， そのエントリを使って，データが取り出される． カーソルは，二次索引の中だけを動く．

演習問題

次の問いに答えよ． Answer the following questions.

問い (Questions)

  1. 次の PTABLE テーブルに関する問題 (About the following 'PTABLE' table) 
    name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  yellow

    このテーブルの行数は 1,000,000 行以上に増える予定である．(The number of row of the table will be more than 1,000,000).

    次の SQL を高速に処理するための二次索引を生成しなさい．二次索引の索引名は「idx3」にしなさい． (Write a SQL to generate a secondary index named 'idx3' that is used for the following SQL) 

    SELECT *
FROM PTABLE
WHERE name = 'apple'
  2. 次の PLACE テーブルに関する問題 (About the following 'PLACE' table) 
    name    |  x      |  y
------------------------------
tenji   |  101    |  104
hakata  |  180    |  125
nishijin|  45     |  108

    このテーブルの行数は 1,000,000 行以上に増える予定である．(The number of row of the table will be more than 1,000,000).

    次の SQL を高速に処理するための二次索引を下記の中から選びなさい．二次索引の索引名は「idx4」にしなさい． (Write a SQL to generate a secondary index named 'idx4' that is used for the following SQL) 

    SELECT *
FROM PLACE
WHERE x > 80 AND x < 120 AND y > 90 AND y < 110
    • CREATE INDEX idx4 ON PLACE( x, y );
    • CREATE INDEX idx4 ON PLACE( name, x );
    • CREATE INDEX idx4 ON PLACE( name, y );
  3. 次のテーブルに関する問題 (About the following table) 
    create table R (
  id  integer primary key,
  val integer,
  note text );

    テーブル R の属性 val に対する二次索引を生成した場合，どのような処理が遅くなるか？ (What kinds of database processing become slower when a secondary index on 'val' of the table 'R').

  4. 下記を行いなさい (Do the followings)
    1. SQLite を使い，下記のテーブルを定義しなさい (Define tables below using SQL) 
          FF (id, name, price)
    2. FF の属性 name に二次索引を作りなさい (Generate a secondary index on 'name' of 'FF')

解答例 (Answers)

  1. CREATE INDEX idx3 ON PTABLE( name );
  2. CREATE INDEX idx4 ON PLACE( x, y );
  3. テーブル R への行の挿入や削除が遅くなる (Insertion of rows into R. Deletion of rows from R). 
  4. create table FF (
  id  integer primary key,
  name text,  
  price integer );
CREATE INDEX idx5 ON FF( name );