by Hayato Matsuura.

プライマリキー


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列にはインデックスが既にあることになります。

ヒント

付録, 「Example Schema には、サンプルデータを含んだ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文を実行する際のステップを表示します。付録で、他のデータベースにおける実行計画の表示方法とその読み方を 説明しています。

インデックスにアクセスした後に、データベースは要求されたデータ(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つのエントリだけがヒットするように、制約によって インデックスの一意性が保たれます。

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

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | 接触 | 無保証 | 商標 | Privacy | CC-BY-NC-ND 3.0 license