where句を使った、シンプルだけれど最も一般的な
文である、プライマリキーでの検索から見ていきましょう。これ以降この章での
例として、以下のようなEMPLOYEES表を使います。
CREATE TABLE employees (
employee_id NUMBER NOT NULL,
first_name VARCHAR(1000) NOT NULL,
last_name VARCHAR(1000) NOT NULL,
date_of_birth DATE NOT NULL,
phone_number VARCHAR(1000) NOT NULL,
CONSTRAINT employees_pk PRIMARY KEY (employee_id)
)データベースは、プライマリキーに対してはインデックスを自動で作成します。つまり、create index文を実行しなくても、
EMPLOYEE_ID列にはインデックスが既にあることになります。
ヒント
付録C, 「Example Schema」
には、サンプルデータを含んだEMPLOYEES表をデータベースに
投入するためのスクリプトがあります。手元の環境でテストしたい時に使用できます。
このテキストと同等のテストを行うには、データベースには1000行程度があれば十分です。
以下のクエリは、従業員の名前を取り出すのに、プライマリキーを使います。
SELECT first_name, last_name
FROM employees
WHERE employee_id = 123プライマリキーの制約により、EMPLOYEE_IDの値は
一意であることが保証されているため、where句は
複数の行に一致することはありません。データベースはインデックスリーフノードをたどる必要はなく、インデックスのツリーをたどるだけで
よいのです。いわゆる実行計画を使って、これを確認してみましょう。
- Db2 (LUW)
以下の実行計画は、付録に登場する
last_explainedビューを使って収集したものです。Explain Plan ------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 13 2 | FETCH EMPLOYEES | 1 of 1 (100.00%) | 13 3 | IXSCAN EMPLOYEES_PK | 1 of 1000 ( .10%) | 6 Predicate Information 3 - START (Q1.EMPLOYEE_ID = +00123.) STOP (Q1.EMPLOYEE_ID = +00123.)IXSCANの処理は、OracleでいうINDEX [RANGE|UNIQUE] SCANのことです。この出力からは、 ユニークスキャンか範囲スキャンかは判断できません。FETCHの処理は、 OracleのTABLE ACCESS BY INDEX ROWIDに対応しています。- MySQL
+----+-----------+-------+---------+---------+------+-------+ | id | table | type | key | key_len | rows | Extra | +----+-----------+-------+---------+---------+------+-------+ | 1 | employees | const | PRIMARY | 5 | 1 | | +----+-----------+-------+---------+---------+------+-------+constタイプは、OracleでいうINDEX UNIQUE SCANのことです。- Oracle
--------------------------------------------------------------- |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)- PostgreSQL
QUERY PLAN ------------------------------------------- Index Scan using employees_pk on employees (cost=0.00..8.27 rows=1 width=14) Index Cond: (employee_id = 123::numeric)PostgreSQLの
Index Scanという操作は、OracleでいうINDEX [UNIQUE/RANGE] SCANとTABLE ACCES BY INDEX ROWIDを組み合わせたものです。インデックスのアクセスで、1行以上を返す可能性があるかどうかは、実行計画からは 分かりません。- SQL Server
|--Nested Loops(Inner Join) |--Index Seek(OBJECT:employees_pk, | SEEK:employees.employee_id=@1 | ORDERED FORWARD) |--RID Lookup(OBJECT:employees, SEEK:Bmk1000=Bmk1000 LOOKUP ORDERED FORWARD)SQL Serverの
INDEX SEEKとRIDLookupは、 OracleでいうINDEX RANGE SCANとTABLE ACCESS BY ROWIDのそれぞれに対応します。Oracleと違って、SQL Serverでは明示的にNested Loops結合で インデックスとテーブルデータを結合したことを表示します。
Oracleの実行計画では、インデックスツリーのみを走査するオペレーションである、INDEX UNIQUE
SCANが実行されると表示されています。これにより、テーブルのサイズとほとんど関係なく、
インデックスの対数的スケーラビリティを活かして、エントリを非常に高速に検索できます。
ヒント
実行計画は、 データベースがSQL文を実行する際のステップを表示します。付録Aで、他のデータベースにおける実行計画の表示方法とその読み方を 説明しています。
インデックスにアクセスした後に、データベースは要求されたデータ(FIRST_NAMELAST_NAMETABLE ACCESS BY INDEX ROWIDの処理に
よって読み込むというステップを実行する必要があります。この処理は、「遅いインデックス パートI」で説明したように、パフォーマンスのボトルネックになる可能性があります。
しかし、INDEX UNIQUE SCANであればこの恐れはありません。
この処理では、2行以上を取得することはないため、1回のテーブルアクセスしか発生しないのです。つまり、INDEX UNIQUE
SCANではクエリが遅くなる要因が無いことになります。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
