プライマリキー


where句を使った、シンプルだけれど最も一般的な 文である、プライマリキーでの検索から見ていきましょう。これ以降この章での 例として、以下のようなEMPLOYEES表を使います。

CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   date_of_birth DATE           NOT NULL,
   phone_number  VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
)

データベースは、プライマリキーに対してはインデックスを自動で 作成します。つまり、create index文を実行しなくても、 EMPLOYEE_ID列にはインデックスが既にあることになります。

ヒント

付録C には、サンプルデータを含んだEMPLOYEES表をデータベースに 投入するためのスクリプトがあります。手元の環境でテストしたい時に 使用できます。

このテキストと同等のテストを行うには、データベースには 1000行程度があれば十分です。

以下のクエリは、従業員の名前を取り出すのに、プライマリキーを 使います。

SELECT first_name, last_name
  FROM employees
 WHERE employee_id = 123

プライマリキーの制約により、EMPLOYEE_IDの値は 一意であることが保証されているため、where句は 複数の行に一致することはありません。データベースはインデックスリーフ ノードをたどる必要はなく、インデックスのツリーをたどるだけで よいのです。いわゆる実行計画を使って、これを確認して みましょう。

DB2

以下の実行計画は、付録に登場する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] SCANTABLE 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 SEEKRIDLookupは、 OracleでいうINDEX RANGE SCANTABLE ACCESS BY ROWIDのそれぞれに対応します。Oracleと違って、 SQL Serverでは明示的にNested Loops結合で インデックスとテーブルデータを結合したことを表示します。

Oracleの実行計画では、インデックスツリーのみを走査する オペレーションである、INDEX UNIQUE SCANが実行されると 表示されています。これにより、テーブルのサイズとほとんど関係なく、 インデックスの対数的スケーラビリティを活かして、エントリを非常に高速に 検索できます。

ヒント

実行計画は、 データベースがSQL文を実行する際のステップを表示します。 付録Aで、 他のデータベースにおける実行計画の表示方法とその読み方を 説明しています。

インデックスにアクセスした後に、データベースは要求されたデータ (FIRST_NAME, LAST_NAME)を、テーブルの ストレージからTABLE ACCESS BY INDEX ROWIDの処理に よって読み込むというステップを実行する必要があります。この処理は、 「遅いインデックス パートI」 で説明したように、パフォーマンスのボトルネックになる可能性があります。 しかし、INDEX UNIQUE SCANであればこの恐れはありません。 この処理では、2行以上を取得することはないため、1回のテーブルアクセスしか 発生しないのです。つまり、INDEX UNIQUE SCANでは クエリが遅くなる要因が無いことになります。

一意なインデックスのないプライマリキー

プライマリキーは、必ずしも一意なインデックスである必要はなく、 一意でないインデックスを持つこともできます。この場合、Oracleデータ ベースはINDEX UNIQUE SCANは使わず、代わりにINDEX RANGE SCANで処理を行います。しかしこの場合でも、インデックスを 使って検索した時に1つのエントリだけがヒットするように、制約によって インデックスの一意性が保たれます。

一意でないインデックスをプライマリキーに使う理由のひとつは、 遅延制約のためです。文の実行時にバリデーションが行われるのが 通常の制約(即時制約)である一方、遅延制約の場合、トランザクションが コミットされるまでバリデーションを遅らせます。遅延制約は、循環する 依存関係があるテーブルにデータを挿入する際に必要になります。

初心者からエキスパートまで役に立つ内容です。
特に駆け出しのエンジニアは持っておくといい
Photo of Markus Winand
Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。 彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。