概要 †

  • RDB 周りの基本知識
↑

参考 †

↑

インデックス †

  • レコード = SSD 内に格納された行
  • インデックスの内部実装
    • インデックスは、SSD 上に埋め込まれた (column -> record pointer の) 順序付き辞書
    • 複合インデックスは column -> record pointer の辞書ではなく、tuple(column, column) -> record pointer になっているだけ。
    • 関数インデックスは、column -> record pointer の辞書ではなく、f(column) -> record pointer になっているだけ。
    • B+木という平衡多分木が SSD に埋め込まれている。これは毎回の SSD ランダムアクセスが遅いので、多分木のノードリストを一括で取りたいから。
  • インデックスも SSD に埋め込まれているのは、インメモリで全レコードのカラムを展開することが不可能だと推定すべきため。
  • それ以上でもそれ以下でもない。これ以上、変な議論をしている複合インデックスの説明は、読み飛ばすこと。有害。
↑

実行計画 †

  • 実行計画の表示は、DB によって全然変わってくるので、それぞれについて勉強しなければならない。
    • だが、Oracle の実行計画が読みやすく理解しやすいので、その用語で一旦説明して、最後に他の DB との関わりについて議論する
  • 実行計画の流れ
    • 1 テーブルに対する取得: SCAN (= レコードを読み込む範囲を指定すること) -> TABLE ACCESS (= 指定された範囲を読み込むこと。途中で WHERE 句に指定された条件で Filter などを行うことも) -> SORT / LIMIT
    • 2 テーブルの JOIN: 特定の条件のもとでテーブル 2 つを結合
      • JOIN は同時に 3 つを JOIN することはできないことに注意
      • JOIN には nested-loop, hash, sort-merge の三つの種類がある。
  • 実行計画中の用語
    • ACCESS = 平衡二分木の Search によって、操作範囲の初めと終わりを確定させること。
    • FILTER = TABLE ACCESS で範囲のデータを辞書の NEXT 操作で全列挙する際、特定の条件で無視すること。
  • 実行計画の読み方
    • Operation が木構造で依存関係を表している。インデントが深いところを解決しないと、インデントが浅いところが解決できない。
    • 今回であれば、Id 2 -> Id 1 -> Id 0 という順番で解決されていく。
    • Predicate Information には、Id で行われている ACCESS, FILTER が何かという参考情報が記載されている。
---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    2 |
|*2 |  INDEX UNIQUE SCAN         | EMPLOYEES_PK |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPLOYEE_ID"=123)
↑

単一インデックス †

  • 事前知識
    • UNIQUE 制約がついているカラムでは、その値はテーブルワイドで一意であることが保証される。
    • PRIMARY キーにはインデックスがデフォルトで張られている。
    • PRIMARY キーには UNIQUE 制約が自動で付いているとは限らない
  • SCAN の種類
    • INDEX UNIQUE SCAN: 辞書によりノードが一意に確定する時の SCAN。インデックスが UNIQUE 制約がついたカラムに付いている場合、そのカラムに対する = 演算子での WHERE 句で使われる（超早い）
    • INDEX RANGE SCAN: 辞書により選択されるノードの範囲が確定し、1 個以上のノードが選択される場合に使われる。
  • TABLE ACCESS の種類
    • TABLE ACCESS BY INDEX ROWID: SCAN で得られたノード範囲から、実際にレコードポインタを辿って SSD アクセスして読み込むこと。この時に、いくつかの WHERE で指定された条件で間引かれることがあり、これが FILTER と呼ばれる。
↑

INDEX UNIQUE SCAN - PRIMARY Key †

  • 以下では、PRIMARY キーにはデフォルトでインデックスが張られている。PRIMARY インデックスが UNIQUE である保証はないが、今回は UNIQUE とする。
    • (2) で INDEX UNIQUE SCAN している。
    • (1) で、ただし、辞書に乗っている情報だけでは first_name, last_name を取得できないので TABLE ACCESS が必要になっている。
  • スキーマ、クエリ、実行計画
CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
)
SELECT first_name, last_name
  FROM employees
 WHERE employee_id = 123
---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    2 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    2 |
|*2 |  INDEX UNIQUE SCAN         | EMPLOYEES_PK |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPLOYEE_ID"=123)
↑

INDEX RANGE SCAN - 複合インデックス †

  • 以下の例では、(subsidiary_id, employee_id) = (子会社の ID, 子会社での社員 ID) で社員を一意に定めている。
    • WHERE で subsidiary_id を指定しても、その子会社の全ての社員が返ってくることになる。この場合、SUBSIDIARY_ID が先頭のインデックスが定義されているので、問題なく INDEX が使われる。アクセスでは、(SUBSIDIARY_ID, -inf) ~ (SUBSIDIARY_ID, +inf) の範囲が指定される。
  • スキーマ、インデックス、クエリ、実行計画
CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   subsidiary_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
)
CREATE UNIQUE INDEX EMPLOYEES_PK 
    ON EMPLOYEES (SUBSIDIARY_ID, EMPLOYEE_ID)
SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = 20
---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |  106 |   75 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |  106 |   75 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK |  106 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=20)
↑

TABLE ACCESS FULL - インデックスを指定すると探索範囲が限定される †

  • 上のクエリでは、subsidiary_id がインデックスに含まれるが、last_name はインデックスが張られていないので subsidiary_id = 30 となるようなレコードを全て調べて last_name が特定の値になっているもの以外を無視しなければならない。
    • 「subsidiary_id = 30 となるようなレコードを全て調べて」が 2 - access("SUBSIDIARY_ID"=30)
    • 「last_name が特定の値になっているもの以外を無視」が 1 - filter("LAST_NAME"='WINAND')
  • 下のクエリでは、Oracle では、NO_INDEX でクエリオプティマイザにインデックスの不使用を指示している。
    • すると、SUBSIDIARY_ID=30 となる最初のレコードと最後のレコードがわからなくなるので、テーブル全てをスキャンする必要がある
    • 上のクエリでは ACCESS で探索範囲を制限する段階で SUBSIDIARY_ID=30 を担保できていた。
    • 下のクエリではそれができないので、TABLE ACCESS の Filter で 1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30) のように無視すべきレコードを判断しなければいけない。
  • スキーマ、インデックス、クエリ、実行計画、クエリ、実行計画
CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   subsidiary_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
)
SELECT first_name, last_name, subsidiary_id
  FROM employees
 WHERE last_name  = 'WINAND'
   AND subsidiary_id = 30
---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |   30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |   30 |
|*2 |  INDEX RANGE SCAN          | EMPLOYEES_PK |   40 |    2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  1 - filter("LAST_NAME"='WINAND')
  2 - access("SUBSIDIARY_ID"=30)
SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */ 
      first_name, last_name, subsidiary_id
 FROM employees
WHERE last_name  = 'WINAND'
  AND subsidiary_id = 30
----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 |  TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)
↑

他の DB との関係 †

↑

MySQL †

  • INDEX UNIQUE SCAN = type const = UNIQUE 制約のついた行に対するアクセス
+----+-----------+-------+---------+---------+------+-------+
| id | table     | type  | key     | key_len | rows | Extra |
+----+-----------+-------+---------+---------+------+-------+
|  1 | employees | const | PRIMARY | 5       |    1 |       |
+----+-----------+-------+---------+---------+------+-------+
トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS