二次索引 (secondary index)





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

* SQLite 3 のオペコードの説明は https://www.sqlite.org/opcode.html にある．

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

SQL による二次索引の生成と削除

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

郵便番号データベースは zips, kens, shichosons の 3 つのテーブルから構成される． 郵便番号データベースの作成手順は 別の Web ページで説明している．

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

作成済みのデータベースファイルは，次の手順で開く． (Open an existing database file)

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

    * Ubuntu での実行例

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

    * Windows での実行例

    データベースファイル C:\sqlite3\mydb を選び， 「開く」をクリック (Click '開く' after choosing the database file "C:\sqlite3\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) が次の値であるとして説明する．

* ルート・ページ番号はデータベース管理システムが決める値なので， 上とは違う値になることが多い．

(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;

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

二次索引が使われるとき，結合の処理は二次索引の上で行われる． 一時的な索引の生成は行われない．

カーソルは，最初，二次索引 idx1 にセットされる． choiki_kanji の値を検索キーとして索引エントリを探し， 見つかった索引エントリを使って，テーブル本体のデータが取り出される．

演習問題

次の問いに答えよ． 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 rows of the table will be more than 1,000,000).

    次の SQL を高速に処理するための二次索引を生成する 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 rows of the table will be more than 1,000,000).

    次の SQL を高速に処理するための二次索引を，下記の中から選びなさい． (Choose a SQL that generates a secondary index 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' is generated).

  4. 下記を行いなさい (Do the followings)
    1. SQLite を使い，下記のテーブルを定義しなさい (Define the table 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 );