Was sind die besten Verfahren für die Verwendung einer GUID als Primärschlüssel, insbesondere im Hinblick auf die Leistung?

Question

Ich habe eine Anwendung, die GUID als Primärschlüssel in fast allen Tabellen verwendet, und ich habe gelesen, dass es Probleme mit der Leistung gibt, wenn GUID als Primärschlüssel verwendet wird. Ehrlich gesagt, habe ich keine Probleme gesehen, aber ich bin dabei, eine neue Anwendung zu starten, und ich möchte immer noch die GUIDs als Primärschlüssel verwenden, aber ich dachte daran, einen zusammengesetzten Primärschlüssel zu verwenden (die GUID und vielleicht ein anderes Feld).

Ich verwende eine GUID, weil sie gut und einfach zu verwalten sind, wenn man verschiedene Umgebungen hat, z. B. "production", "test" und "dev" Datenbanken, und auch für die Migration von Daten zwischen Datenbanken.

Ich werde Entity Framework 4.3 verwenden, und ich möchte die Guid im Anwendungscode zuweisen, bevor ich sie in die Datenbank einfüge. (d.h. ich möchte nicht, dass SQL die Guid generiert).

Was ist die beste Praxis für die Erstellung von GUID-basierten Primärschlüsseln, um die mit diesem Ansatz verbundenen vermeintlichen Leistungseinbußen zu vermeiden?

Answer 1

GUIDs scheinen eine natürliche Wahl für Ihren Primärschlüssel zu sein - und wenn Sie wirklich müssen, könnten Sie wahrscheinlich argumentieren, sie für den PRIMARY KEY der Tabelle zu verwenden. Was ich dringend empfehlen würde nicht zu tun ist die Verwendung der GUID-Spalte als Bündelungsschlüssel was SQL Server standardmäßig tut, es sei denn, Sie weisen ihn ausdrücklich an, dies nicht zu tun.

Sie müssen zwei Themen wirklich auseinander halten:

1. die Primärschlüssel ist ein logisches Konstrukt - einer der Kandidatenschlüssel, der jede Zeile in Ihrer Tabelle eindeutig und zuverlässig identifiziert. Dies kann wirklich alles sein - ein INT , a GUID Wählen Sie aus, was für Ihr Szenario am sinnvollsten ist.

2. die Bündelungsschlüssel (die Spalte(n), die den "geclusterten Index" der Tabelle definieren) - dies ist ein physisch und hier ist ein kleiner, stabiler, ständig wachsender Datentyp die beste Wahl - INT o BIGINT als Ihre Standardoption.

Standardmäßig wird der Primärschlüssel einer SQL Server-Tabelle auch als Clustering-Schlüssel verwendet - das muss aber nicht so sein! Ich persönlich habe massive Leistungssteigerungen festgestellt, als ich den bisherigen GUID-basierten Primär-/Clustering-Schlüssel in zwei separate Schlüssel auflöste - den primären (logischen) Schlüssel auf der GUID und den Clustering-Schlüssel (Ordnungsschlüssel) auf einem separaten INT IDENTITY(1,1) Spalte.

Als Kimberly Tripp - die Königin der Indizierung - und andere haben es schon oft gesagt - eine GUID als Clustering-Schlüssel ist nicht optimal, da er aufgrund seiner Zufälligkeit zu einer massiven Seiten- und Indexfragmentierung und zu einer allgemein schlechten Leistung führt.

Ja, ich weiß - es gibt newsequentialid() in SQL Server 2005 und höher - aber auch das ist nicht wirklich und vollständig sequentiell und leidet daher unter denselben Problemen wie die GUID - nur etwas weniger auffällig.

Dann gibt es noch einen weiteren Punkt zu bedenken: Der Clustering-Schlüssel einer Tabelle wird zu jedem einzelnen Eintrag in jedem nicht geclusterten Index Ihrer Tabelle hinzugefügt - Sie sollten also sicherstellen, dass er so klein wie möglich ist. Typischerweise wird ein INT mit 2+ Milliarden Zeilen sollte für die große Mehrheit der Tabellen ausreichen - und im Vergleich zu einer GUID als Clustering-Schlüssel verwenden, können Sie Hunderte von Megabyte an Speicherplatz auf der Festplatte und im Serverspeicher sparen.

Schnelle Berechnung - mit INT vs. GUID als Primär- und Clustering-Schlüssel:

• Basistabelle mit 1'000'000 Zeilen (3.8 MB vs. 15.26 MB)
• 6 nicht geclusterte Indizes (22,89 MB vs. 91,55 MB)

TOTAL: 25 MB vs. 106 MB - und das bei nur einem einzigen Tisch!

Ein paar weitere Denkanstöße - exzellentes Material von Kimberly Tripp - lesen Sie es, lesen Sie es noch einmal, verdauen Sie es! Es ist das Evangelium der SQL Server-Indizierung, wirklich.

• GUIDs als PRIMARY KEY und/oder geclusterter Schlüssel
• Die Debatte um geclusterte Indizes geht weiter
• Immer größer werdender Clustering-Schlüssel - die Clustered Index Debate..........wieder!
• Festplattenplatz ist billig - das ist no der Punkt!

PS: Wenn Sie nur mit ein paar hundert oder tausend Zeilen zu tun haben, haben die meisten dieser Argumente natürlich keine großen Auswirkungen auf Sie. Wenn Sie jedoch mit zehn- oder hunderttausenden von Zeilen zu tun haben, oder wenn Sie anfangen, in Millionen zu zählen dann Diese Punkte sind von entscheidender Bedeutung und sehr wichtig zu verstehen.

Update: wenn Sie Ihre PKGUID Spalte als Primärschlüssel (aber nicht als Clustering-Schlüssel), und eine weitere Spalte MYINT ( INT IDENTITY ) als Ihren Clustering-Schlüssel - verwenden Sie diesen:

CREATE TABLE dbo.MyTable
(PKGUID UNIQUEIDENTIFIER NOT NULL,
 MyINT INT IDENTITY(1,1) NOT NULL,
 .... add more columns as needed ...... )

ALTER TABLE dbo.MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY NONCLUSTERED (PKGUID)

CREATE UNIQUE CLUSTERED INDEX CIX_MyTable ON dbo.MyTable(MyINT)

Grundsätzlich gilt: Sie müssen nur ausdrücklich sagen die PRIMARY KEY Einschränkung, dass es sich um NONCLUSTERED (andernfalls wird er standardmäßig als Ihr geclusterter Index erstellt) - und dann erstellen Sie einen zweiten Index, der definiert ist als CLUSTERED

Das funktioniert - und ist eine gute Option, wenn Sie ein bestehendes System haben, das für mehr Leistung "überarbeitet" werden muss. Bei einem neuen System, wenn Sie von Grund auf neu anfangen und sich nicht in einem Replikationsszenario befinden, würde ich mich immer für ID INT IDENTITY(1,1) als geclusterter Primärschlüssel - viel effizienter als alles andere!

Answer 2

Ich verwende GUIDs als PKs seit 2005. In der Welt der verteilten Datenbanken ist dies der beste Weg, um verteilte Daten zusammenzuführen. Man kann Tabellen einfach zusammenführen, ohne sich Gedanken über den Abgleich von Ints in verknüpften Tabellen machen zu müssen. GUIDs-Joins können ohne Probleme kopiert werden.

Dies ist mein Setup für die Verwendung von GUIDs:

1. PK = GUID. GUIDs werden ähnlich wie Strings indiziert, so dass Tabellen mit vielen Zeilen (über 50 Millionen Datensätze) möglicherweise eine Tabellenpartitionierung oder andere Leistungstechniken erfordern. SQL Server wird immer effizienter, so dass Leistungsaspekte immer seltener eine Rolle spielen.

2. PK Guid ist ein NON-Clustered-Index. Clustern Sie niemals einen GUID-Index, es sei denn, es handelt sich um NewSequentialID. Aber selbst dann führt ein Neustart des Servers zu größeren Unterbrechungen in der Reihenfolge.

3. ClusterID Int zu jeder Tabelle hinzufügen. Dies ist Ihr CLUSTERED Index... der Ihre Tabelle ordnet.

4. Das Joinen auf ClusterIDs (int) ist effizienter, aber ich arbeite mit Tabellen mit 20-30 Millionen Datensätzen, so dass das Joinen auf GUIDs die Leistung nicht merklich beeinträchtigt. Wenn Sie maximale Leistung wünschen, verwenden Sie das ClusterID-Konzept als Primärschlüssel und verknüpfen Sie auf ClusterID.

Hier ist meine E-Mail-Tabelle...

CREATE TABLE [Core].[Email] (
    [EmailID]      UNIQUEIDENTIFIER CONSTRAINT [DF_Email_EmailID] DEFAULT (newsequentialid()) NOT NULL,        
    [EmailAddress] NVARCHAR (50)    CONSTRAINT [DF_Email_EmailAddress] DEFAULT ('') NOT NULL,        
    [CreatedDate]  DATETIME         CONSTRAINT [DF_Email_CreatedDate] DEFAULT (getutcdate()) NOT NULL,      
    [ClusterID] INT NOT NULL IDENTITY,
    CONSTRAINT [PK_Email] PRIMARY KEY NonCLUSTERED ([EmailID] ASC)
);
GO

CREATE UNIQUE CLUSTERED INDEX [IX_Email_ClusterID] ON [Core].[Email] ([ClusterID])
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Email_EmailAddress] ON [Core].[Email] ([EmailAddress] Asc)

Answer 3

Ich entwickle derzeit eine Webanwendung mit EF Core und hier ist das Muster, das ich verwende:

Alle meine Klassen (Tabellen) haben eine int PK und FK. Ich habe dann eine zusätzliche Spalte vom Typ Guid (erzeugt durch den C#-Konstruktor) mit einem nicht geclusterten Index darauf.

Alle Verknüpfungen von Tabellen innerhalb von EF werden über die int Tasten, während der gesamte Zugriff von außen (Controller) mit den Guid s.

Diese Lösung ermöglicht es, die int Schlüssel auf URLs, sondern halten das Modell ordentlich und schnell.

Answer 4

Dieser Link drückt es besser aus, als ich es könnte, und half mir bei meiner Entscheidungsfindung. Ich entscheide mich in der Regel für einen int als Primärschlüssel, es sei denn, ich habe einen besonderen Grund, dies nicht zu tun, und ich lasse auch SQL Server dieses Feld automatisch generieren/pflegen, es sei denn, ich habe einen besonderen Grund, dies nicht zu tun. In Wirklichkeit müssen die Leistungsaspekte auf der Grundlage Ihrer spezifischen Anwendung bestimmt werden. Hier spielen viele Faktoren eine Rolle, einschließlich, aber nicht beschränkt auf die erwartete Datenbankgröße, die richtige Indizierung, effiziente Abfragen und vieles mehr. Auch wenn man anderer Meinung sein mag, denke ich, dass man in vielen Szenarien bei beiden Optionen keinen Unterschied feststellen wird, und man sollte sich für die Option entscheiden, die für die eigene Anwendung am besten geeignet ist und mit der man einfacher, schneller und effektiver entwickeln kann (wenn man die Anwendung nie fertigstellt, was macht dann der Rest für einen Unterschied :).

https://web.archive.org/web/20120812080710/http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

P.S. Ich bin mir nicht sicher, warum Sie eine zusammengesetzte PK verwenden sollten oder welchen Vorteil Sie darin sehen.

Answer 5

Nun, wenn Ihre Daten nie Millionen von Zeilen erreichen, sind Sie gut dran. Wenn Sie mich fragen, verwende ich niemals GUID als Datenbank-Identitätsspalte irgendeines Typs, einschließlich PK, selbst wenn Sie mich zwingen, mit einer Schrotflinte am Kopf zu entwerfen.

Die Verwendung von GUID als Primärschlüssel ist ein definitiver Skalierungsstopper, und ein kritischer dazu. Ich empfehle Ihnen, die Datenbankidentität und die Sequenzoption zu prüfen. Sequence ist tabellenunabhängig und kann eine Lösung für Ihre Bedürfnisse bieten (MS SQL hat Sequenzen).

Wenn Ihre Tabellen mehrere Dutzend Millionen Zeilen umfassen, z. B. 50 Millionen, können Sie die Informationen nicht mehr in akzeptablen Zeiten lesen und schreiben, und selbst die Standardwartung von Datenbankindizes wäre nicht mehr möglich.

Dann müssen Sie eine Partitionierung verwenden und bis zu einer halben Milliarde oder sogar 1-2 Milliarden Zeilen skalierbar sein. Das Hinzufügen von Partitionierung auf dem Weg ist nicht die einfachste Sache, alle Lese-/Schreibanweisungen müssen Partitionsspalten enthalten (komplette App-Änderungen!).

Diese Zahlen (50 Mio. und 500 Mio.) sind natürlich für eine leicht selektive Nutzung gedacht. Wenn Sie Informationen auf komplexe Weise auswählen und/oder viele Einfügungen/Aktualisierungen/Löschungen vornehmen müssen, könnten diese Zahlen für ein sehr anspruchsvolles System stattdessen auch 1-2 Millionen und 50 Millionen betragen. Wenn man dann noch Faktoren wie ein vollständiges Wiederherstellungsmodell, hohe Verfügbarkeit und keine Wartungsfenster hinzunimmt, wie sie bei modernen Systemen üblich sind, werden die Dinge extrem unschön.

Beachten Sie an dieser Stelle, dass 2 Milliarden die int-Grenze ist, die schlecht aussieht, aber int ist 4-mal kleiner und ist ein sequentieller Datentyp, kleine Größe und sequentieller Typ sind der Faktor Nr. 1 für die Skalierbarkeit von Datenbanken. Und Sie können big int verwenden, das nur zweimal kleiner ist, aber immer noch sequentiell ist. Sequentiell ist das, was wirklich tödlich wichtig ist - sogar wichtiger als die Größe - wenn es um viele Millionen oder einige Milliarden von Zeilen geht.

Wenn die GUID auch geclustert ist, sieht es noch viel schlimmer aus. Wenn Sie eine neue Zeile einfügen, wird diese zufällig überall in der physischen Position gespeichert.

Selbst wenn es sich nur um eine Spalte handelt, nicht um einen PK- oder PK-Teil, ist die bloße Indizierung problematisch. Von Fragmentierung Perspektive.

Eine guid-Spalte ist wie jede varchar-Spalte völlig in Ordnung, solange Sie sie nicht als PK-Teil und generell als Schlüsselspalte für die Verbindung von Tabellen verwenden. Ihre Datenbank muss über eigene PK-Elemente verfügen, die zum Filtern und Verbinden von Daten verwendet werden - das anschließende Filtern nach einer GUID ist völlig in Ordnung.