*概要 [#h1dadad4]
-RDB 周りの基本知識
*参考 [#f2a34be5]
-https://use-the-index-luke.com/ja
--未定義の用語を前提としながら説明をしたり、日本語が不自由な部分以外は良い資料
*インデックス [#y3553137]
-レコード = SSD 内に格納された行
-インデックスの内部実装
--インデックスは、SSD 上に埋め込まれた (column -> record pointer の) 順序付き辞書
--複合インデックスは column -> record pointer の辞書ではなく、tuple(column, column) -> record pointer になっているだけ。
--関数インデックスは、column -> record pointer の辞書ではなく、f(column) -> record pointer になっているだけ。
--B+木という平衡多分木が SSD に埋め込まれている。これは毎回の SSD ランダムアクセスが遅いので、多分木のノードリストを一括で取りたいから。
-インデックスも SSD に埋め込まれているのは、インメモリで全レコードのカラムを展開することが不可能だと推定すべきため。
-それ以上でもそれ以下でもない。これ以上、変な議論をしている複合インデックスの説明は、読み飛ばすこと。有害。
*実行計画 [#kfb2466c]
-実行計画の表示は、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)
**単一インデックス [#k1b715d0]
-事前知識
--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 [#dc8831e4]
-以下では、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 - 複合インデックス [#ue73db8a]
-以下の例では、(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 - インデックスを指定すると探索範囲が限定される [#x3b81ff1]
-上のクエリでは、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 との関係 [#a6428970]
**MySQL [#id93285f]
-INDEX UNIQUE SCAN = type const = UNIQUE 制約のついた行に対するアクセス
+----+-----------+-------+---------+---------+------+-------+
| id | table | type | key | key_len | rows | Extra |
+----+-----------+-------+---------+---------+------+-------+
| 1 | employees | const | PRIMARY | 5 | 1 | |
+----+-----------+-------+---------+---------+------+-------+
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
