Wann sollte ein Primärschlüssel als nicht geclustert deklariert werden?
Beim Erstellen einer Testdatenbank für eine andere Frage, die ich zuvor gestellt habe, fiel mir ein, dass ein Primärschlüssel deklariert werden kann NONCLUSTERED
Wann würden Sie einen NONCLUSTERED Primärschlüssel im Gegensatz zu einem CLUSTERED Primärschlüssel verwenden?
Danke im Voraus
Die Frage lautet nicht "Wann sollte die PK NC sein", sondern "Was ist der richtige Schlüssel für den Clustered-Index?".
Und die Antwort hängt wirklich davon ab , wie Sie die Daten abfragen . Der Clustered-Index hat einen Vorteil gegenüber allen anderen Indizes: Da er immer alle Spalten enthält, deckt er immer ab. Daher müssen Abfragen, die den Clustered-Index nutzen können, sicherlich keine Suchvorgänge verwenden, um einige der projizierten Spalten und/oder Prädikate zu erfüllen.
Ein weiteres Puzzleteil ist , wie ein Index verwendet werden kann . Es gibt drei typische Muster:
- sonden, wenn ein einzelner Schlüsselwert im Index gesucht wird
- bereichsscans, wenn ein Bereich von Schlüsselwerten abgerufen wird
- reihenfolge nach Anforderungen, wenn ein Index eine Reihenfolge erfüllen kann, ohne dass eine Stop-and-Go-Sortierung erforderlich ist
Wenn Sie also Ihre erwartete Auslastung (die Abfragen) analysieren und feststellen, dass eine große Anzahl von Abfragen einen bestimmten Index verwenden würde, weil sie ein bestimmtes Zugriffsmuster verwenden, das von einem Index profitiert, ist es sinnvoll, diesen Index als Clustered-Index vorzuschlagen.
Ein weiterer Faktor ist, dass der gruppierte Indexschlüssel der Suchschlüssel ist, der von allen nicht gruppierten Indizes verwendet wird. Daher erzeugt ein breiter gruppierter Indexschlüssel einen Welligkeitseffekt und Erweitert alle nicht gruppierten Indizes und breite Indizes bedeuten mehr Seiten, mehr E/A, mehr Speicher, weniger Güte.
Ein guter Clustered-Index ist stabil und ändert sich während der Lebensdauer der Entität nicht, da eine Änderung der Clustered-Index-Schlüsselwerte bedeutet, dass die Zeile dies tun muss gelöscht und wieder eingefügt werden.
Und ein guter Clustered-Index wächst nicht zufällig (jeder neu eingefügte Schlüsselwert ist größer als der vorhergehende Wert), um Seitenteilung und Fragmentierung zu vermeiden (ohne mit FILLFACTORs herumzuspielen).
Entspricht der Primärschlüssel (eine logische Eigenschaft für die Datenmodellierung) den Anforderungen, nachdem wir nun wissen, was ein guter Clustered-Indexschlüssel ist? Wenn ja, sollte die PK geclustert werden. Wenn nein, sollte die PK nicht geclustert sein.
Betrachten Sie als Beispiel eine Tabelle mit Verkaufsdaten. Jeder Eintrag hat eine ID, die der Primärschlüssel ist. Die überwiegende Mehrheit der Abfragen fragt jedoch nach Daten zwischen einem Datum und einem anderen Datum. Daher ist der beste Cluster-Indexschlüssel das Verkaufsdatum und nicht das [~ # ~] id [~ # ~] . Ein weiteres Beispiel für einen anderen Clustered-Index als der Primärschlüssel ist ein Schlüssel mit sehr geringer Selektivität, z. B. eine 'Kategorie' oder ein 'Status', ein Schlüssel mit nur sehr wenigen unterschiedlichen Werten. Ein Clustered-Indexschlüssel mit diesem Schlüssel mit niedriger Selektivität als Schlüssel ganz links, z. (state, id), macht oft Sinn, weil Bereiche nach allen Einträgen in einem bestimmten 'Zustand' suchen.
Ein letzter Hinweis zur Möglichkeit eines nicht gruppierten Primärschlüssels über einem Heap (d. H. Es gibt überhaupt keinen gruppierten Index). Dies kann ein gültiges Szenario sein. Der typische Grund dafür ist, dass die Leistung von Masseneinfügungen kritisch ist, da Heaps im Vergleich zu Clustered-Indizes einen deutlich besseren Durchsatz von Masseneinfügungen aufweisen.
Der Hauptgrund für die Verwendung von Clustered-Indizes ist auf Wikipedia angegeben:
Durch Clustering wird der Datenblock in eine bestimmte Reihenfolge geändert, um dem Index zu entsprechen, sodass die Zeilendaten in der angegebenen Reihenfolge gespeichert werden. Daher kann für eine bestimmte Datenbanktabelle nur ein Clustered-Index erstellt werden. Clustered-Indizes können die Gesamtabrufgeschwindigkeit erheblich erhöhen, normalerweise jedoch nur , wenn auf die Daten nacheinander im selben oder nacheinander zugegriffen wird umgekehrte Reihenfolge des Clustered-Index oder wenn ein Bereich von Elementen ausgewählt ist.
Angenommen, ich habe eine Personentabelle, und diese Personen haben eine Länderspalte und einen eindeutigen Primärschlüssel. Es ist eine demografische Tabelle, daher sind dies die einzigen Dinge, die mir wichtig sind. Welches Land und wie viele einzigartige Menschen sind an dieses Land gebunden?.
Es ist daher immer wahrscheinlich, dass ich in der Spalte "Land" AUSWÄHLEN, WO oder BESTELLEN. Ein Clustered-Index für den Primärschlüssel nützt mir nichts. Ich greife nicht über PK auf diese Daten zu, sondern über diese andere Spalte. Da ich nur einen Clustered-Index für eine Tabelle haben kann, würde die Deklaration meiner PK als Clustered die Verwendung eines Clustered-Index für das Land verhindern.
Darüber hinaus ist hier ein guter Artikel über Clustered vs Nonclustered Indexes , der sich herausstellt, dass Clustered-Indizes Probleme mit der Einfügungsleistung in SQL Server 6.5 verursachen (was zumindest hoffentlich für die meisten von uns hier nicht relevant ist).
Wenn Sie eine IDENTITY-Spalte mit einem Clustered-Index versehen, werden alle Einfügungen auf der letzten Seite der Tabelle ausgeführt - und diese Seite ist für die Dauer jeder IDENTITY gesperrt. Keine große Sache ... es sei denn, Sie haben 5000 Leute, die alle die letzte Seite wollen. Dann haben Sie viel Streit um diese Seite
Beachten Sie, dass dies in späteren Versionen nicht der Fall ist.
Wenn Ihr Primärschlüssel UNIQUEIDENTIFIER ist, stellen Sie sicher, dass es NONCLUSTERED ist. Wenn Sie es gruppieren, muss jede Einfügung eine Reihe von Datensätzen mischen, um die neue Zeile an der richtigen Position einzufügen. Dies wird die Tankleistung verbessern.
Ein sehr häufiges Beispiel:
CustomerTabelle mitCustomerIDalsCLUSTERED PRIMARY KEY- Bestelltabelle mit
OrderID (PK), CustomerID, OrderDateund einigen anderen Spalten OrderPositionsmitOrderPositionID (PK), OrderId, ProductID, Amount, Price ...- sie müssen die Auftragstabellen indizieren
Natürlich ist "es kommt darauf an" - wie fast immer - die richtige Antwort, aber die meisten Anwendungen (nicht BI-Berichte) funktionieren kundenbasiert (z. B. melden Sie sich als Kunde 278 auf der Website an und klicken auf "Meine Bestellungen" oder Der Sachbearbeiter listet alle Bestellungen für den Kunden 4569 auf, oder Ihre Rechnungsroutine summiert alle Bestellungen für den Kunden 137).
In diesem Fall wäre es nicht sehr sinnvoll, die Tabelle nach OrderID zu gruppieren. Ja, Sie haben Abfragen als SELECT ... WHERE OrderId = ?, Um die Bestelldetails aufzulisten. Dies ist jedoch normalerweise eine kurze und kostengünstige Indexsuche (3 Lesevorgänge).
Wenn Sie andererseits Ihre Order -Tabelle nach CustomerID gruppieren würden, müssten nicht jedes Mal, wenn Sie die Tabelle nach CustomerId = ? Abfragen, mehrere Schlüsselsuchen durchgeführt werden.
Der CLUSTERED INDEX Sollte immer UNIQUE sein, andernfalls würde SQL Server eine unsichtbare (= unbrauchbare) INT-Spalte UNIQUIFIER hinzufügen, um die Eindeutigkeit sicherzustellen - und es wäre viel sinnvoller, real ( verwendbare) Daten dann einige zufällige (abhängig von der Einfügereihenfolge) Sachen.
Da ein Kunde (hoffentlich) mehr als eine Bestellung aufgeben wird, müssten wir entweder die OrderID oder (wenn Sie normalerweise danach sortieren) die OrderDate (wenn es sich um eine Datums-/Uhrzeitangabe handelt - ansonsten den Kunden hinzufügen wäre auf eine Bestellung pro Tag beschränkt) auf den CLUSTERED INDEX und am Ende mit:
CREATE UNIQUE CLUSTERED INDEX IX_Orders_UQ on Orders (CustomerID, OrderID)
Die gleichen Regeln gelten für die Tabelle OrderPositions. Normalerweise listen die meisten Abfragen alle Positionen für eine bestimmte Reihenfolge auf. Daher sollten Sie die PK mit OrderPositionID als NONCLUSTERED und einem UNIQUE CLUSTERED INDEX Auf OrderId, OrderPositionID Erstellen.
Übrigens: Es ist richtig, dass die Tabelle Customer von ihrer PK (der Tabelle CustomerID) geclustert wird, da es sich um eine "Top-Level-Tabelle" handelt und in einer typischen Anwendung hauptsächlich von ihrer Tabelle abgefragt wird Kundennummer.
Reine Nachschlagetabellen wie z. Genders oder InvoiceTypes oder PaymentType sind ein weiteres Beispiel für Tabellen, die von ihrer PK geclustert werden sollten (da Sie sie normalerweise auf GenderId, InvoiceTypeId oder verbinden PaymentTypeId).
Wenn ein Clustered-Index für das Gesamtsystem vorteilhafter ist als ein Clustered-PK, indem ein Leistungsmaß verwendet wird. Es kann nur einen Clustered-Index für eine Tabelle geben.
Beispielhafte Leistungsmessungen sind die Einzelabfragezeit (Geschwindigkeit), die Integration der gesamten Abfragezeiten in die Tabelle (Effizienz) und das Hinzufügen vieler Include-Spalten zu einem sehr großen nicht gruppierten Index, um eine ähnliche Leistung wie bei Clustered (Größe) zu erzielen ).
Dies kann passieren, wenn Daten im Allgemeinen mit einem Index abgerufen werden, der nicht eindeutig ist, Nullen enthält (in einer PK nicht zulässig) oder die PK aus einem sekundären Grund hinzugefügt wurde (z. B. Replikation oder Identifizierung von Audit-Trail-Datensätzen).