Montag, 20. Mai 2013

Unterschied zwischen Primary Key und Clustered Index

Letzte Woche wurde ich beauftragt, die Ursachen für eine schlechte Performance innerhalb einer Datenbank zu analysieren und gegebenenfalls Hinweise zu geben, wie man die Wartezeiten im Frontend verkürzen kann. Bei der Prüfung der Ausführungspläne und Indexstrukturen ist aufgefallen, dass fast 10% der Relationen HEAPS sind und in vielen Abfragen mit anderen Relationen über JOINS verwendet wurden. Meine erste Empfehlung war demzufolge – basierend auf den Ausführungsplänen – die Implementierung und Verwendung von Clustered Indexes. Da die Anwendung nicht “in House” entwickelt worden ist, wurde sich mit der Bitte um Prüfung und Zusendung von Skripten an den Hersteller gewandt. Das nachfolgende Script soll stellvertretend für den Lösungsansatz des Herstellers dienen. Bei der im Beispiel benannten Relation handelt es sich um eine simple Relation für die Speicherung historischer Daten ohne Fremdschlüssel-Referenzen.

  1. CREATE CLUSTERED INDEX ix_relation_id ON dbo.tbl_relation(id);
  2. DELETE dbo.relation WHERE id IN (SELECT id FROM dbo.relation GROUP BY id HAVING COUNT(id) > 1);
  3. DROP INDEX ix_relation ON dbo.tbl_relation;
  4. ALTER TABLE dbo.tbl_relation ADD CONSTRAINT p_relation_id PRIMARY KEY (id);

Als ich das Script von meinem Auftraggeber erhalten habe, musste ich erst mal tief Luft holen und mich fragen, welcher “Experte” solche Skripte an seine Kunden verschickt. Zunächst wird ein Clustered Index auf dem Attribut [Id] der Relation [dbo].[tbl_relation] erstellt (Zeile 1). Anschließend werden ALLE Datensätze, die eine redundante [Id] besitzen gelöscht (Zeile 2). Nachdem die Daten gelöscht wurden, wird der Clustered Index wieder entfernt und durch einen PRIMARY KEY ersetzt.

Der brisanteste Teil in diesem Script ist sicherlich die Zeile 2; sollen doch tatsächlich Daten aus einem Produktionssystem gelöscht werden ohne diese im Vorfeld in einer Staging-Tabelle zu analysieren. Des Weiteren ist aufgefallen, dass dem Programmierer scheinbar der Unterschied zwischen einem Clustered Index und der Einschränkung eines PRIMARY KEY nicht bekannt ist. Die Intention des Programmierers wird beim Lesen der obigen Skriptzeilen relativ schnell klar; zunächst wird ein “Index” erzeugt, um schnell die redundanten Datensätze zu löschen. Anschließend soll mittels PRIMARY KEY erneut ein Clustered Index erzeugt werden, um unsere Anforderungen zu erfüllen.

Was ist ein Primary Key

Um mitzuteilen, welchen der Schlüsselkandidaten man zur Identifikation eines Datensatzes (Tupel) in einer Relation bevorzugt, wird aus allen Schlüsselkandidaten der Primärschlüssel ausgewählt. Der Primärschlüssel wird üblicherweise so ausgewählt, dass er möglichst klein ist, das heißt möglichst wenige Attribute umfasst bzw. einen möglichst simplen Datentyp hat. Ein Primärschlüssel ist KEIN Index ist sondern eine Einschränkung (CONSTRAINT). Ein Primärschlüssel besitzt die nachfolgenden Besonderheiten:

  • Eine Relation kann nur einen PRIMARY KEY besitzen
  • In den Attributen des PRIMARY KEY sind keine NULL-Werte zugelassen
  • Die Attribute des PRIMARY KEY dürfen nicht redundant sein (doppelte Schlüsselwerte!)
  • Die Einschränkung PRIMARY KEY erzwingt immer die Definition eines EINDEUTIGEN Index und nicht zwingend einen CLUSTERED INDEX!
  • Ein PRIMARY KEY ist NICHT zwingend für DRI (deklarative referenzielle Integrität)

Die nachfolgenden Beispiele soll die Besonderheiten eines PRIMARY KEY verdeutlichen:

CREATE TABLE dbo.master_table
(
    Id
    int       NULL PRIMARY KEY,
    col1  char(20)  NULL
);
GO

Der Aufruf schlägt fehl, da das Attribut [Id] NULL-Werte zulässt während der nächste Aufruf im nächsten Beispiel funktioniert, da die Einschränkung NOT NULL für das Attribut [Id] verwendet wird.

Meldung 8111, Ebene 16, Status 1, Zeile 2
Eine PRIMARY KEY-Einschränkung kann für eine Spalte in der master_table-Tabelle, die NULL zulässt, nicht definiert werden.

CREATE TABLE dbo.master_table
(
    Id
    int       NOT NULL PRIMARY KEY,
    col1  char(20)  NULL
);
GO

Sofern für die Relation nicht explizit ein Clustered Index definiert wird, erzeugt SQL Server automatisch für den PRIMARY KEY einen clustered Index. Beweis liefert die Untersuchung der Indexe und Einschränkungen für die zuvor erstellte Relation

SELECT OBJECT_NAME(parent_object_id)  AS table_name,
       k.name
                         AS constraint_name,
       k.type
                         AS constraint_type,
       k.type_desc
                    AS constraint_type_desc,
       unique_index_id,
       is_system_named,
       i.name
                         AS index_name,
       i.type_desc                   
AS index_type
FROM   sys.key_constraints k LEFT JOIN sys.indexes i
       ON (
            k.parent_object_id =
i.object_id AND
            k.unique_index_id = i.index_id
          )
WHERE  parent_object_id = OBJECT_ID('dbo.master_table', 'U');

Primary Key 01

Das DMO sys.key_constraints enthält jeweils eine Zeile für jedes Objekt, dass entweder eine PRIMARY KEY oder eine UNIQUE Einschränkung besitzt. Die obige Abbildung zeigt, dass der PRIMARY KEY eindeutig ist und der Name der Einschränkung automatisch durch SQL Server erstellt wurde. Handelt es sich um eine eindeutige Einschränkung (UNIQUE), kann der entsprechende UNIQUE Index über die [index_id] der zugehörigen Relation ermittelt werden.

Da eine Einschränkung auch durch individuelle Namensgebung erstellt werden kann, führt das nachfolgende Beispiel ebenfalls zur Erstellung des PRIMARY KEY mit dem Unterschied, dass der Name durch den Ersteller vordefiniert wird.

CREATE TABLE dbo.master_table
(
    Id
    int       NOT NULL,
    col1  char(20)  NULL

    CONSTRAINT pk_master_table_id PRIMARY KEY (Id)
);
GO

Primary Key 02

Da nun der Name des PRIMARY KEY selbst festgelegt worden ist, ist das Attribut [is_system_named] nun 0. Weitere Einstellungen haben sich nicht geändert. Das ein PRIMARY KEY nicht zwingend ein Clustered Index ist, zeigt das nächste Beispiel sehr deutlich:

CREATE TABLE dbo.master_table
(
    Id
    int       NOT NULL,
    col1 
char(20)  NULL
   
    ,
CONSTRAINT pk_master_table_id PRIMARY KEY NONCLUSTERED (id)
);
GO

CREATE CLUSTERED INDEX ix_master_table_col1 ON dbo.master_table (col1);
GO

Unabhängig von der Existenz eines Clustered Index kann der PRIMARY KEY definiert werden. Da ein PRIMARY KEY nur dann als Clustered Index angelegt wird, wenn noch kein Clustered Index existiert, musste in dem obigen Beispiel der PRIMARY KEY explizit mit der Option NONCLUSTERED definiert werden um eine automatische Erstellung des Clustered Index zu verhindern. Ein Blick auf die Einschränkungen und die vorhandenen Indexe zeigt das Ergebnis.

Primary Key 03

Wozu wird ein Primary Key verwendet?

Der Sinn und Zweck eines PRIMARY KEY ergibt sich aus den Eigenschaften der Einschränkung selbst. Da ein PRIMARY KEY keine NULL-Werte enthalten darf und eindeutig sein muss, eignet er sich für die Durchsetzung von DRI (Deklarative Referenzielle Integrität).  Unter Referentieller Integrität versteht man Bedingungen, die zur Sicherung der Datenintegrität bei Nutzung relationaler Datenbanken beitragen können. Nach der RI-Regel dürfen Datensätze (über ihre Fremdschlüssel) nur auf existierende Datensätze verweisen.

Danach besteht die Referenzielle Integrität grundsätzlich aus zwei Teilen:

  1. Ein neuer Datensatz mit einem Fremdschlüssel kann nur dann in einer Tabelle eingefügt werden, wenn in der referenzierten Tabelle ein Datensatz mit entsprechendem Wert im Primärschlüssel oder einem eindeutigen Alternativschlüssel existiert.
  2. Eine Datensatzlöschung oder Änderung des Schlüssels in einem Primär-Datensatz ist nur möglich, wenn zu diesem Datensatz keine abhängigen Datensätze in Beziehung stehen.

SQL Server benötigt für Referenzielle Integrität nicht zwingend einen PRIMARY KEY; vielmehr sind nur die Eigenschaften eines PRIMARY KEY für SQL Server von Relevanz für die Durchsetzung von Referenzieller Integrität. Die beiden nachfolgenden Skriptbeispiele sollen diesen Zusammenhang verdeutlichen. Für die Definition eines Fremdschlüssels ist zwingend erforderlich, dass das Primärattribut der Masterrelation weder NULL-Werte besitzt noch mehrfach vorkommen darf. Das nachfolgende Script wird fehlschlagen, da SQL Server nicht in der Lage ist, eindeutige Schlüsselwerte in [master_table] sicher zu stellen:

CREATE TABLE dbo.master_table
(
    Id
   int       NOT NULL,
    col1
char(20)  NULL   
);
GO

CREATE TABLE dbo.detail_table
(
    Master_id
   int       NOT NULL,
    detail_id  
int       NOT NULL,
    col1       
char(20)  NOT NULL,

    CONSTRAINT fk_master_table_id FOREIGN KEY (master_id)
    REFERENCES dbo.master_table(Id)
    ON DELETE CASCADE
);

Wird das obige Script ausgeführt, wird der nachfolgende Fehler ausgelöst. Die Fehlerbeschreibung ist selbsterklärend:

Meldung 1776, Ebene 16, Status 0, Zeile 2
In der dbo.master_table-Tabelle, auf die verwiesen wird, befinden sich keine primären oder Kandidatenschlüssel, die mit der verweisenden Spaltenliste im fk_master_table_id-Fremdschlüssel übereinstimmen.

Für eine Fremdschlüsselbeziehung wird ENTWEDER ein PRIMARY KEY oder ein UNIQUE NOT NULL Index benötigt. Statt also eines PRIMARY KEY kann auch eine UNIQUE-Einschränkung verwendet werden. Ändert man das Script zur Erstellung der Relationen wie folgt um, kann die Einschränkung problemlos erstellt werden:

CREATE TABLE dbo.master_table
(
    Id
   int       NOT NULL,
    col1
char(20)  NULL,

    CONSTRAINT ux_master_table_id UNIQUE (Id)   
);
GO

CREATE TABLE dbo.detail_table
(
    Master_id
   int       NOT NULL,
    detail_id  
int       NOT NULL,
    col1       
char(20)  NOT NULL,

    CONSTRAINT fk_master_table_id FOREIGN KEY (master_id)
    REFERENCES dbo.master_table(Id)
    ON DELETE CASCADE
);

Das Script läuft fehlerfrei durch und die Fremdschlüssel-Einschränkung wird erstellt, da für das Fremdschlüssel-Attribut [Id] die Einschränkungen “NOT NULL” und “UNIQUE” vorhanden sind. Es bedarf keiner weiteren Einschränkungen mehr. Schaut man sich anschließend die in der Relation [dbo].[master_table] befindlichen Indexe an, stellt man fest, dass die Relation selbst immer noch ein HEAP ist und keinen clustered Index besitzt.

SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.master_table', 'U');

Primary Key 04

Fazit

Die Definition eines PRIMARY KEY für eine Relation hat NICHTS mit der Definition und der Funktionalität eines Clustered Index zu tun. Beide Verfahren sind unabhängig voneinander zu betrachten und verfolgen ganz verschiedene Ansätze. Während ein PRIMARY KEY eine Eigenschaft einer Relation ist, ist der Clustered Index die Relation selbst. Während ein Clustered Index ausschließlich für die physikalische Ordnung der Datensätze verantwortlich ist, dient der PRIMARY KEY der Durchsetzung von klar vordefinierten Vorgaben für das Schlüsselattribut eines Datensatzes (Eindeutigkeit und NOT NULLABLE).

Herzlichen Dank für’s Lesen

PRIMARY KEY http://msdn.microsoft.com/de-de/library/ms191236.aspx
CLUSTERED INDEX http://msdn.microsoft.com/de-de/library/ms177443.aspx
Referentielle Integrität http://de.wikipedia.org/wiki/Referentielle_Integrit%C3%A4t
sys.key_constraints http://msdn.microsoft.com/de-de/library/ms174321.aspx
sys.indexes http://msdn.microsoft.com/de-de/library/ms173760.aspx

Kommentare :

  1. Danke für den super Artikel.
    Diesen Artikel habe ich eute einem Entwickler unter die Nase gehalten. Der mir was anderes Erzählen wollte.
    MFG
    Alex

    AntwortenLöschen
  2. Gibt es - abgesehen davon, dass man es immer so macht - einen technischen / performancerelevanten Grund dafür, einen PK anzulegen, statt einfach nur einen CLUSTERED UNIQUE INDEX (insbesondere, wenn keine FKs für die Tabelle vorgesehen sind)?

    AntwortenLöschen
  3. Servus Thomas,

    nein - den gibt es in dem von Dir beschriebenen Fall - nicht. Letztendlich ist ein PK zunächst ein CONSTRAINT, der sich unter anderem eines UNIQUE Index bedient, um die Integritätsregeln durchzusetzen.

    Aus meiner Sicht spricht nichts dagegen (eher dafür), einen UNIQUE CLUSTERED INDEX zu setzen als einen CONSTRAINT. Er ist leichter zu entfernen als ein CONSTRAINT :)

    ALTER TABLE dbo.foo DROP CONSTRAINT...
    oder
    DROP INDEX pk_foo ON dbo.foo;

    Die zweite Variante liegt mir deutlich mehr - ist aber eindeutig Geschmackssache.
    Ich werde das Thema noch einmal überarbeitet in meinem neuen Blog veröffentlichen:

    http://www.sqlmaster.de

    Herzliche Grüße aus Erzhausen, Uwe

    AntwortenLöschen
  4. Es gibt meines Wissens noch einen Grund für einen PK. Wenn man eine Replikation aufbaut ist meines Wissens bei einer Transaktionbasierenden Replikation ein PK in jeder Tabelle zwingend.

    Eine Frage die ich noch nicht gelesen habe ist, ob es performancetechnisch ein Problem ist wenn ich einen PK anlege und diesen geclustert habe. Damit müsste ich doch die gleichen Performancewerte wie mit einem Clustered Index bekommen

    Grüsse
    Sven Günter

    AntwortenLöschen
    Antworten
    1. Ein ungefilterter UNIQUE INDEX sollte für Replikation auch reichen, egal ob geclustert oder nicht.

      Und es kann performancetechnisch durchaus einen Unterschied machen, ob der PK (wenn es z.B. eine IDENTITY-Spalte ist) geclustert wird oder nicht.

      Einfachstes Beispiel: eine Tabelle mit Auftragspositionen (oder Rechnungspositionen). Diese enthält als PK eine hochgezählte auftragspositionsid und darüber hinaus die Spalte AuftragsId (und ggf. noch eine PosNummer, wobei die nicht zwingend ist).

      Da wahrscheinlich 95 % der Anfragen über auftragsid = xxx eingeschränkt werden (im WHERE oder JOIN) würde - wenn der PK geclustert ist - sehr häufig ein Keylookup nötig sein (oder man müsste sehr viele Spalten in den Index auf der AuftragsId includen).

      Löschen
    2. Lieber Sven,

      vielen Dank für den Besuch meines - veralteten - Blogs. Du hast vollkommen Recht; für eine Transaktionale Replikation ist ein PRIMARY KEY zwingend vorgeschrieben.

      Gleiches gilt für eine Peer To Peer Replikation. Ich habe mich in diesem Artikel jedoch nicht so sehr auf die "applikationsseitigen" Erfordernisse konzentriert sondern vielmehr das Relationale Model damals vor Augen gehabt.

      Vielen Dank für Deinen - wertvollen - Kommentar zu diesem Thema.

      Löschen