von Markus Winand.

Primärschlüssel


Zunächst betrachten wir die einfachste, aber dennoch häufigste where-Klausel: die Suche mit dem Primärschlüssel. Dafür legen wir zuerst die Tabelle EMPLOYEES an, die für sämtliche Beispiele dieses Kapitels verwendet wird:

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)
)

Mit der Definition des Primärschlüssels wird automatisch ein Index auf EMPLOYEE_ID angelegt. Daher ist in diesem Fall keine create index-An­wei­sung notwendig.

Tipp

Anhang C„Beispiel Schema, enthält alle Skripte um die EMPLOYEES Tabelle mit Testdaten zu befüllen. Damit kann man die Beispiele selbst testen.

Um dem Text zu folgen, genügt es, zu wissen, dass die Tabelle EMPLOYEES 1000 Einträge hat.

Die folgende Abfrage sucht einen Angestellten mit dem Primärschlüssel und zeigt seinen Vor- und Nachnamen an:

SELECT first_name, last_name
  FROM employees
 WHERE employee_id = 123

Der Primärschlüssel garantiert, dass die Abfrage maximal einen Eintrag liefert. Die Datenbank muss also nur den Indexbaum durchwandern, nicht aber der Blattknoten-Kette folgen. Zur Kontrolle kann man den sogenannten Ausführungsplan heranziehen.

DB2

Der folgende Ausführungsplan wurde mit dem last_explained-View erstellt, den wir im Anhang vorstellen.

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.)

Die Operation IXSCAN entspricht einem Oracle INDEX RANGE SCAN oder INDEX UNIQUE SCAN. Aus dieser Anzeige können wir nicht schließen, ob es ein Range- oder Unique-Scan ist. Die FETCH-Operation stellt den Tabellenzugriff dar (Oracle TABLE ACCESS BY INDEX ROWID).

MySQL

+----+-----------+-------+---------+---------+------+-------+
| id | table     | type  | key     | key_len | rows | Extra |
+----+-----------+-------+---------+---------+------+-------+
|  1 | employees | const | PRIMARY | 5       |    1 |       |
+----+-----------+-------+---------+---------+------+-------+

Der Zugriffstyp const ist MySQL's äquivalent zum INDEX UNIQUE SCAN der Oracle Datenbank.

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)

Die PostgreSQL Operation Index Scan ist eine Kombination der Oracle-Operationen INDEX [UNIQUE/RANGE] SCAN und TABLE ACCESS BY INDEX ROWID. An einem PostgreSQL Ausführungsplan kann man nicht erkennen, ob der Indexzugriff potenziell mehr als eine Zeile liefern könnte.

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)

Die SQL Server Operationen INDEX SEEK und RID Lookup entsprechen den Oracle-Operationen INDEX RANGE SCAN und TABLE ACCESS BY ROWID. Im Gegensatz zu Oracle zeigt der SQL Server Ausführungsplan das Zusammenführen der Index und Tabellendaten mit dem Nested Loops Join explizit an.

Der Ausführungsplan der Oracle Datenbank zeigt einen INDEX UNIQUE SCAN – jene Operation, die nur den Indexbaum durchwandert. Dadurch wird die logarithmische Skalierung des Indexbaumes voll ausgenutzt, sodass der Eintrag nahezu unabhängig von der Tabellengröße sehr schnell gefunden wird.

Tipp

Der Ausführungsplan (auch Explain-Plan oder Query-Plan genannt) zeigt die Ausführungsschritte der Datenbank an. Anhang A zeigt, wie man Ausführungspläne in ver­schie­denen Datenbanken abfragt und interpretiert.

Nach der Indexsuche muss die Datenbank noch einen weiteren Schritt durchführen, um den Vor- und Nachnamen aus der Tabelle zu laden. Die TABLE ACCESS BY INDEX ROWID Operation stellt diesen Tabellenzugriff dar. Obwohl dieser Zugriff – wie in Langsame Indizes, Teil I beschrieben – zum Performanceproblem werden kann, ist das bei einem INDEX UNIQUE SCAN nicht der Fall. Diese Operation kann nämlich nur einen Eintrag liefern und daher auch nur einen Tabellenzugriff auslösen. Bei einem INDEX UNIQUE SCAN können die Zutaten zu einer langsamen Abfrage also nicht auftreten.

Wenn dir gefällt, wie ich die Dinge erkläre, wirst du meine Kurse lieben.

Primärschlüssel ohne Unique Index

Ein Primärschlüssel erfordert nicht zwangsläufig einen Unique Index – man kann dafür auch einen Non-Unique Index verwenden. Das führt dazu, dass die Oracle Datenbank einen INDEX RANGE SCAN verwendet. Dennoch ist die Eindeutigkeit des Primärschlüssels gewährleistet, sodass die Indexsuche maximal einen Eintrag liefert.

Einer der Gründe einen Non-Unique Index für einen Primärschlüssel zu verwenden, sind verzögerte Constraints (deferrable constraints). Im Gegensatz zu normalen Constraints werden verzögerte erst geprüft, wenn die Transaktion mittels commit abgeschlossen wird. Verzögerte Constraints werden benötigt, um Tabellen mit zirkulären Abhängigkeiten zu befüllen.

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz