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

ヒント

付録C, 「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文を実行する際のステップを表示します。付録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つのエントリだけがヒットするように、制約によって インデックスの一意性が保たれます。

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

協力してください

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

著者について

Markus Winandの写真

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

彼の本

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

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

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

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license