Montag, 18. März 2013

Clustered Primary Key – Surrogate Key vs. Natural Key

Auf die nachfolgende Problematik wurde ich auf Grund eines Threads in den Microsoft Foren zu SQL Server aufmerksam. In dem Thread ging es darum, ob es sinnvoller ist, einen Surrogatschlüssel als “clustered primary key” zu verwenden oder besser einen – aus mehreren Attributen bestehenden – Naturalschlüssel. Die Vor- und Nachteile der verschiedenen Ausprägungen sollen im nachfolgenden Artikel etwas genauer betrachtet werden.

Was ist ein Primärschlüssel?

Untter einem Primärschlüssel versteht man eine Einschränkung einer Relation, die zum Erzwingen von Datenintegritäten in den Relationen von SQL Server verwendet werden kann. Eine Relation verfügt normalerweise über ein Attribut oder eine Kombination aus Attributen, die Werte enthalten, die jede Zeile in der Relation eindeutig identifizieren.  Dieses Attribut oder die Kombination aus Attributen wird als Primärschlüssel (PK, Primary Key) der Relation bezeichnet und erzwingt die Entitätsintegrität der Relation.

Für einen Primärschlüssel gelten die folgenden Einschränkungen:

  • Eine Relation kann nur eine PRIMARY KEY-Einschränkung enthalten. 
  • Ein Primärschlüssel darf 16 Attribute und eine Schlüssellänge von 900 Bytes nicht überschreiten.
  • Wenn CLUSTERED oder NONCLUSTERED für eine PRIMARY KEY-Einschränkung nicht angegeben ist, wird CLUSTERED verwendet, sofern keine gruppierten Indizes für die Relation vorliegen.
  • Alle Attribute, für die eine PRIMARY KEY-Einschränkung definiert wurde, müssen ungleich NULL definiert sein.  Falls keine NULL-Zulässigkeit angegeben ist, wird für alle Spalten, auf die eine PRIMARY KEY-Einschränkung angewendet wird, die NULL-Zulässigkeit auf NOT NULL festgelegt.

Ausgangsstellung der Untersuchung

Für die nachfolgende Untersuchung stand die Frage im Raum, ob ein sich über ein Attribut erstreckender Surrogatschlüssel für einen geclusterten Primärschlüssel besser sei als ein Naturalschlüssel, der sich über mehrere Attribute (cola – cold) erstreckt. Für die Untersuchung wurden zwei Relationen mit identischer Struktur erstellt, die folgenden Aufbau haben:

--  Relation mit Naturalschlüssel auf cola - cold
CREATE TABLE [dbo].[tbl_PK_AD]
(
   [Id]
   int      IDENTITY(1,1) NOT NULL,
   [cola]
char(20) NOT NULL,
   [colb]
char(20) NOT NULL,
   [colc]
char(20) NOT NULL,
   [cold]
char(20) NOT NULL,
   [cole]
char(80) NULL,
  
   CONSTRAINT pk_tbl_pk_ad PRIMARY KEY CLUSTERED
   ([cola], [colb], [colc], [cold])
)
GO

-- Relation mit Surrogatschlüssel auf Id
CREATE TABLE [dbo].[tbl_PK_ID]
(
   [Id]  
int      IDENTITY(1,1) NOT NULL,
   [cola]
char(20) NOT NULL,
   [colb]
char(20) NOT NULL,
   [colc]
char(20) NOT NULL,
   [cold]
char(20) NOT NULL,
   [cole]
char(80) NULL,
  
   CONSTRAINT pk_tbl_PK_Id PRIMARY KEY CLUSTERED
   ([Id])
)

GO

Während in der Relation dbo.tbl_PK_Id das Attribut des Primärschlüssels auf ID liegt, wird in der Relation dbo.tbl_PK_AD ein zusammengesetzter Primärschlüssel implementiert, der sich über die Attribute [cola] – [cold] erstreckt. In beiden Relation befinden sich ~350.000 Datensätze aus simplen Textkombinationen für die nachfolgenden Tests. Die Datenbank kann für Testzwecke mit allen Testscripts herunter geladen werden. Der Link zum Download befindet sich am Ende des Artikels.

ORDER BY

Eine besondere Eigenschaft des clustered index ist die Tatsache, dass die Daten physikalisch nach den Schlüsselattributen des Clustered Keys gespeichert werden. Zu diesem komplexen Thema können im Artikel “Idealer Datentyp für Clustered Index – GUID vs. INT” weitere Informationen bezogen werden. Die nachfolgende Abfrage auf beide Relationen verdeutlicht einen entscheidenden Vorteil, wenn das Sortierkriterium das/die Primärattribut(e) sind.

SELECT * FROM dbo.tbl_pk_id ORDER BY cola, colb, colc, cold;
SELECT * FROM dbo.tbl_pk_ad ORDER BY cola, colb, colc, cold;

ExecutionPlan - 01 - no indexes

Der Ausführungplan zeigt, dass die Sortierung über Attribute, die nicht indiziert sind, sehr teuer ist. Während für die zweite Abfrage der Clustered Index vollständig verwendet werden kann, muss für die Relation dbo.tbl_PK_Id ein expliziter – nicht durch einen Index abgedeckter – Sortierprozess gestartet werden. Dieser Sortierprozess verursacht fast 50% der Kosten für die Ausführung der Abfrage. Ist nicht ausreichend Arbeitsspeicher vorhanden, muss der Sortiervorgang auf die TEMPDB ausgelagert werden. Interessant ist aber im obigen Zusammenhang die Auswertung des IO für beide Abfragen.

Table 'tbl_PK_ID'. Scan count 9, logical reads 8222, physical reads 0,…
Table 'tbl_PK_AD'. Scan count 1, logical reads 7860, physical reads 0,…

Das IO für die Relation dbo.tbl_PK_ID ist ca. 5% höher als für die Relation dbo.tbl_PK_AD. Dieser höhere Wert ist der Sortierung geschuldet. Der Scan-Count definiert die Anzahl der Durchläufe für die Relation. Während also die Relation dbo.tbl_pk_ad sequentiell gelesen werden konnte, musste die Relation dbo.tbl_pk_id insgesamt 9 Mal durchlaufen werden. Die nächste Auswertung soll zeigen, wie sich das Verhalten ändert, wenn man die zu sortierenden Attribute indiziert.

Verwendung eines Index auf zu sortierende Attribute

Wie bereits oben gesehen, kann ein effizienter Sortiervorgang durchgeführt werden, wenn die Daten sequentiell gelesen werden können und wenn keine wiederholten Scans durchgeführt werden müssen. Wendet man dieses Verhalten auf die Relation dbo.tbl_PK_ID an, so sollte ein Index auf die Attribute [cola], [colb], [colc] und [cold] das Ausführungsverhalten signifikant verändern. Der zu erstellende Index ist ein UNIQUE index, da ja die Attribute in Relation dbo.tbl_PK_AD technisch bedingt (Primary Key) ebenfalls UNIQUE sind.

CREATE UNIQUE INDEX ix_tbl_pk_Id ON dbo.tbl_PK_ID ([cola], [colb], [colc], [cold]);

Nachdem der obige Index erstellt wurde, werden die zuvor ausgeführten Abfragen erneut unter Beachtung der Ausführungspläne ausgeführt.

SELECT * FROM dbo.tbl_pk_id ORDER BY [cola], [colb], [colc], [cold];
SELECT * FROM dbo.tbl_pk_ad ORDER BY [cola], [colb], [colc], [cold];

Das Ergebnis ist ernüchternd – es ändert sich nichts! Der Abfrageplan und die IO-Messungen sind absolut identisch. Das ist der Tatsache geschuldet, dass der zuvor erstellte Index  auf der Relation dbo.tbl_PK_ID nicht alle Attribute einschließt, die ausgegeben werden sollen. Die Abfrage soll – auf Grund des “*” alle Attribute der Relation ausgeben. Tatsächlich kann der Index aber nur die Attribute [cola], [colb], [colc] und [cold] abdecken. Um die anderen Informationen ([cole]) abzurufen, müßte ein teurer “Nested Loop” in Verbindung mit einem Key Lookup ausgeführt werden.

SELECT * FROM dbo.tbl_pk_id WITH (INDEX(ix_tbl_pk_id)) ORDER BY cola, colb, colc, cold

ExecutionPlan - 02 - no use of indexes

Die obige Abbildung zeigt den Ausführungsplan bei der forcierten Verwendung des Indexes. Insgesamt hat SQL Server also tatsächlich alles richtig gemacht und auf teuere Key Lookups verzichtet und statt dessen einen Clustered Index Scan durchgeführt.

Um den Index optimal zu nutzen, müsste also das Attribut [cole] ebenfalls in den Index aufgenommen werden. Da jedoch dieses Attribut nicht der Einschränkung der Eindeutigkeit unterliegt, darf es nicht Bestandteil des Index selbst sein sondern muss mittels INCLUDE lediglich in den Indexseiten gespeichert werden.

CREATE UNIQUE INDEX ix_tbl_pk_Id ON dbo.tbl_PK_ID ([cola], [colb], [colc], [cold]) INCLUDE ([cole]);

Die Besonderheit bei dieser Indexkonfiguration liegt in der Tatsache, dass nun die Relation vollständig abgedeckt ist. Der nachfolgende Screenshot zeigt eine typische Datenseite des Index. Informationen über die Ausgabe von Indexinformationen finden Sie z. B. in dem Blogbeitrag “Neue DMV für Struktur der Datenseiten (Pages)

Indexstructure - INCLUDE and PK

Die Attribute [cola] bis [cold] sind Indexattribute (gekennzeichnet durch “(key)”) während das Attribut [code] zusätzlich auf der Datenseite gespeichert wird. Das Schlüsselattribut eines clustered index wird IMMER zu jedem Indexeintrag mitgespeichert. Wie man sehr gut erkennen kann, wird nun die vollständige Relation durch den Index abgedeckt und das Ergebnis des Auführungsplans und des IO ändert sich deutlich.

SELECT * FROM dbo.tbl_pk_id ORDER BY [cola], [colb], [colc], [cold];
SELECT * FROM dbo.tbl_pk_ad ORDER BY [cola], [colb], [colc], [cold];

ExecutionPlan - 03 - use of indexes

Nicht nur, dass nun der Index verwendet werden kann – auch die Performance ist deutlich besser als bei der Verwendung des “natural keys”. Schaut man sich die IO-Statistiken an, wird erkennbar, warum dieser Unterschied existiert:

Table 'tbl_PK_ID'. Scan count 1, logical reads 7232, physical reads 0,…
Table 'tbl_PK_AD'. Scan count 1, logical reads 7860, physical reads 0,…

Der IO Unterschied kann zwei Ursachen haben. Zum einen besitzt ein Clustered Index immer mehr Pages, da der Clustered Index die Relation selbst in nach dem Schlüsselattribut physikalisch sortierten repräsentiert. Somit muss also der Clustered Index deutlich mehr Daten speichern als ein non clustered Index.

Eine andere Erklärung für den Unterschied kann der Grad der Fragmentierung des Index sein. Der “clustered key” des Index für die Relation dbo.tbl_PK_AD muss einen Bereich von 4 Attributen abdecken. Sofern die Datensätze nicht mehr auf eine Indexseite abgelegt werden können, wird bei einer vollen Datenseite ein Page Split ausgeführt und die Daten einer betroffenen Seite werden zu 50% in eine neue Datenseite verschoben. Der so gewonnene Platz wird dann so lange aufgefüllt, bis erneut kein Platz mehr vorhanden ist. Durch diesen Vorgang wird der Index stark fragmentiert. Genauere Informationen dazu finden Sie im Artikel “Idealer Datentyp für Clustered Index – GUID vs INT”.

Das nachfolgende Ergebnis zeigt einen direkten Vergleich zwischen dem clustered Index der Relation dbo.tbl_PK_AD und dem zuvor angelegten Index ix_tbl_PK_ID für die Relation dbo.tbl_PK_ID. Die Abfrage für das Ergebnis sieht wie folgt aus:

SELECT OBJECT_NAME(object_id), index_type_desc, index_level, avg_fragmentation_in_percent,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.tbl_PK_AD'), 1, 1, 'DETAILED');

SELECT OBJECT_NAME(object_id), index_type_desc, index_level, avg_fragmentation_in_percent,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(), object_id('dbo.tbl_PK_ID'), 2, 1, 'DETAILED');

Indexstructure - Indexfragmentation 01

In der Abbildung ist sehr deutlich erkennbar, dass der clustered index eine sehr hohe Fragmentierung auf Datenebene (index_level = 0) besitzt. Daraus resultieren natürlich eine erhöhte Anzahl von Pages. Desweiteren besteht der clustered index aus insgesamt 3 B-Tree-Levels während der non clustered index trotz gleichem Datenvolumen mit 2 B-Tree-Levels auskommt. Nach dem Rebuild des clustered index sieht das Ergebnis wie folgt aus:

Indexstructure - Indexfragmentation 02

SELECT * FROM dbo.tbl_pk_id ORDER BY [cola], [colb], [colc], [cold];
SELECT * FROM dbo.tbl_pk_ad ORDER BY [cola], [colb], [colc], [cold];

ExecutionPlan - 04 - use of rebuild indexes

Abfragen auf indizierte Attribute

Wie im Vorfeld gesehen, hat ein “natural key” im Verhältnis zu einem Surrogatschlüssel zunächst keine signifikanten Vorteile wenn es darum geht, den gesamten Inhalt der Relation auszugeben. Ob eventuell ein Vorteil bei Selektionskriterien vorliegt, soll das nachfolgende Beispiel demonstrieren.

Die nachfolgende Abfrage wird sowohl auf die Relation dbo.tbl_PK_ID als auch dbo.tbl_PK_AD ausgeführt. Hierbei werden zwei Attribute des Index mittels WHERE-Klausel eingeschränkt.

SELECT * FROM dbo.tbl_pk_id
WHERE  cola = 'AAAAAAAAAAAAAAAAAAAA' AND
       colb =
'AAAAAAAAAAAAAAAAAAAA'
ORDER BY
      
cola, colb, colc, cold;

SELECT * FROM dbo.tbl_pk_ad
WHERE  cola = 'AAAAAAAAAAAAAAAAAAAA' AND
       colb =
'AAAAAAAAAAAAAAAAAAAA'
ORDER BY
      
cola, colb, colc, cold;

ExecutionPlan - 05 - use of selectivity in indexes

Das Ergebnis des Auführungsplans zeigt deutlich, dass es KEINEN Vorteil für eine der beiden Varianten gibt. Beide Abfragen vewenden einen optimalen Index für die Ausführung. Während für die Relation dbo.tbl_PK_ID der Index über die Attribute [cola] bis [cold] verwendet werden kann, wird für die Relation dbo.tbl_PK_AD der clustered index verwendet. Der Index für dbo.tbl_PK_ID kann nur deswegen optimal verwendet werden, da er alle Attribute der Relation (siehe Abbildung weiter oben) abdeckt und keine teure Key Lookups nach sich zieht.

Wichtig ist bei den Abfragen jedoch, dass Attribute der Links-Rechts-Regel folgen. Diese Regel besagt, dass bei Abfragen immer das äusserst linke Attribut mit verwendet werden muss, um einen Index Seek durchführen zu können. Die nachfolgenden Beispiele sollen das verdeutlichen.

SELECT  *
FROM    dbo.tbl_PK_AD
WHERE   cola = REPLICATE('A', 20) AND
        colc =
REPLICATE('A', 20)

SELECT  *
FROM    dbo.tbl_PK_AD
WHERE   colb = REPLICATE('A', 20) AND
        colc =
REPLICATE('A', 20)

Vergleicht man die Ausführungspläne, erkennt man sofort, dass die erste Variante deutlich performanter arbeitet, da sie einen Index Seek statt eines Index Scan verwenden kann.

ExecutionPlan - 06 - left to right

Die Erklärung für dieses Verhalten kann man sehr einfach durch Vewendung eines Beispiels aus dem täglichen Leben ableiten – dem Telefonbuch! Ein Telefonbuch ist immer nach Nachnamen, Mittelnamen und Vornamen sortiert. Sofern der Nachnamen (das erste linke Attribut im Index des Telefonbuchs) nicht bekannt ist sondern nur den Vornamen, dann müsste das komplette Telefonbuch durchsucht werden, um alle Einträge mit dem gesuchten Vornamen ausfindig zu machen. Diese Funktionsweise habe ich im Artikel “Multi-Column-Indexe – Vor- und Nachteile” ausführlich beschrieben.

Datenvolumen

Das Datenvolumen beider Relationen unterscheidet sich deutlich. Das Ergebnis kann auch nicht verwundern, da ja – wie bereits weiter oben erwähnt wurde – der Clustered Index immer die Relation selbst ist, bedeutet dies für die Relation dbo.tbl_PK_AD die Speicherung eines Index. Für die Relation dbo.tbl_PK_ID jedoch sieht dieses Verhältnis wieder anders aus. Hier wird zum einen der clustered index als auch der nachträglich für die Optimierung der Abfragen angelegte Index gespeichert. Diese Manko schlägt sich auch deutlich im benötigten Speichervolumen nieder:

Datenvolumen

Fazit

Die Frage, die sich auf Basis der obigen Ergebnisse stellt, ist natürlich, warum man einen Surrogatschlüssel vewenden soll, wenn doch ein Natural Key ebenfalls zum gewünschten Ziel führt. Die Antwort ist recht einfach: “It depends”.

Es gibt keine feste Richtlinie, mit der klar definiert werden kann, wann welche Variante verwendet werden kann/soll. Beide Varianten haben ihre Vor- und ihre Nachteile. Auf Ebene der Performance gibt es – bei ausreichender Kenntnis über das Verhalten von Indexen – keine großen Unterschiede. Jedoch sollte immer das gesamte Datenmodell im Fokus bleiben.

In einem OLTP-System ist ein kleiner Index in Form eines Surrogatschlüssel sicherlich performanter. Dieser Vorteil kann jedoch im Rahmen von optimierten Indexen zu deutlich mehr Volumen für zusätzliche Indexe wieder negiert werden. In OLTP-Systemen ist es  wichtig, dass Daten möglichst schnell gespeichert werden. Je mehr Indexe jedoch in der Relation vorhanden sind, um so mehr Aktualisierungen müssen durchgeführt werden.

Ein – für mich wesentlicher – weiterer Vorteil von Surrogatschlüsseln ist die Möglichkeit der Anonymisierung von Daten. Wenn z. B. der Primärschlüssel auf einer [Id] statt auf [Vorname], [Nachname], [Ort] liegt, wird in einer abhängigen Relation nur die Id gespeichert. während bei der zweiten Konstruktion immer alle drei Attribute in der Relation gespeichert werden müssen.

In einem DWH (Data Warehouse) findet man sehr häufig statische Daten mit wenig Änderungspotential. Hier bietet sich unter Umständen ein aus mehreren Attributen zusammengesetzter Naturalschlüssel an, um zum Beispiel teure Sortieruoperationen zu vermeiden. Jedoch sind solche Schlüssel kein Garant für schnelle Systeme, da bei Beziehungen zwischen mehreren Relationen das Primärattribut in jeder abhängigen Relation mitgeführt werden muss.

Herzlichen Dank für’s Lesen

Keine Kommentare :

Kommentar veröffentlichen