wake-up-neo.com

Was bedeutet Clustered und Non Clustered Index eigentlich?

Ich habe eine begrenzte DB-Belastung und habe nur DB als Anwendungsprogrammierer verwendet. Ich möchte etwas über Clustered und Non clustered indexes..__ wissen. Ich habe gegoogelt und was ich gefunden habe war:

Ein Clustered-Index ist ein spezieller Indextyp, der die Art und Weise neu sortiert Datensätze in der Tabelle sind physikalisch gelagert. Deshalb kann die Tabelle nur .__ haben. ein gruppierter Index. Die Blattknoten eines gruppierten Index enthalten die Daten Seiten. Ein nicht gruppierter Index ist ein spezieller Indextyp, in dem der logische Reihenfolge des Indexes nicht stimmt mit der physisch gespeicherten Reihenfolge von .__ überein. die Zeilen auf der Festplatte. Der Blattknoten eines Nicht gruppierter Index besteht nicht aus die Datenseiten. Stattdessen das Blatt Knoten enthalten Indexzeilen.

Was ich in SO gefunden habe war Was sind die Unterschiede zwischen einem gruppierten und einem nicht gruppierten Index? .

Kann jemand das auf Englisch erklären?

897
P.K

Bei einem Clustered-Index werden die Zeilen in derselben Reihenfolge wie der Index physisch auf der Festplatte gespeichert. Daher kann es nur einen Clusterindex geben.

Bei einem nicht gruppierten Index gibt es eine zweite Liste mit Zeigern auf die physischen Zeilen. Sie können viele nicht gruppierte Indizes haben, obwohl jeder neue Index die Zeit zum Schreiben neuer Datensätze verlängert. 

Es ist im Allgemeinen schneller, einen Clustered-Index zu lesen, wenn Sie alle Spalten zurückbekommen möchten. Sie müssen nicht zuerst zum Index und dann zur Tabelle gehen.

Das Schreiben in eine Tabelle mit einem Clustered-Index kann langsamer sein, wenn die Daten neu angeordnet werden müssen.

902
Shiraz Bhaiji

Ein gruppierter Index bedeutet, dass Sie der Datenbank mitteilen, dass sie nahe Werte nahe beieinander auf der Festplatte speichern soll. Dies hat den Vorteil eines schnellen Scans/Abrufs von Datensätzen, die in einen bestimmten Bereich gruppierter Indexwerte fallen.

Sie haben beispielsweise zwei Tabellen, Kunden und Auftrag:

Customer
----------
ID
Name
Address

Order
----------
ID
CustomerID
Price

Wenn Sie alle Bestellungen eines bestimmten Kunden schnell abrufen möchten, können Sie in der Spalte "CustomerID" der Tabelle "Auftrag" einen gruppierten Index erstellen. Auf diese Weise werden die Datensätze mit derselben CustomerID physisch nahe beieinander auf der Festplatte gespeichert (gruppiert), was das Abrufen beschleunigt.

P.S. Der Index für CustomerID ist offensichtlich nicht eindeutig. Daher müssen Sie entweder ein zweites Feld hinzufügen, um den Index zu "eindeutig" machen, oder die Datenbank das für Sie erledigen lassen. Dies ist jedoch eine andere Geschichte.

In Bezug auf mehrere Indizes. Sie können nur einen Clustered-Index pro Tabelle erstellen, da dies definiert, wie die Daten physisch angeordnet sind. Wenn Sie eine Analogie wünschen, stellen Sie sich einen großen Raum mit vielen Tischen vor. Sie können diese Tabellen entweder zu mehreren Zeilen zusammenstellen oder sie alle zu einem großen Konferenztisch zusammenfassen, jedoch nicht auf beide Arten gleichzeitig. Eine Tabelle kann andere Indizes haben, sie zeigt dann auf die Einträge im Clustered-Index, die wiederum angeben, wo die tatsächlichen Daten zu finden sind.

553
user151323

Im zeilenorientierten Speicher von SQL Server sind sowohl gruppierte als auch nicht gruppierte Indizes als B-Bäume organisiert.

enter image description here

( Bildquelle )

Der Hauptunterschied zwischen Clustered-Indizes und nicht Clustered-Indizes besteht darin, dass die Blattebene des Clustered-Index ist der Tabelle liegt. Dies hat zwei Auswirkungen.

  1. Die Zeilen auf den gruppierten Indexblattseiten enthalten immer etwas für jede der (nicht spärlichen) Spalten der Tabelle (entweder den Wert oder einen Zeiger auf den tatsächlichen Wert). 
  2. Der gruppierte Index ist die primäre Kopie einer Tabelle.

Nicht gruppierte Indizes können auch Punkt 1 ausführen, indem sie die Klausel INCLUDE (Since SQL Server 2005) verwenden, um alle Nicht-Schlüsselspalten explizit einzuschließen. Diese sind jedoch sekundäre Repräsentationen. Es gibt immer eine weitere Kopie der Daten (die Tabelle selbst).

CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)

CREATE UNIQUE CLUSTERED INDEX ci ON T(A,B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A,B) INCLUDE (C,D)

Die beiden obigen Indizes sind nahezu identisch. Mit den Indexseiten der oberen Ebene, die Werte für die Schlüsselspalten enthalten, A,B und die Blattseiten, die A,B,C,D enthalten.

Pro Tabelle kann nur ein Clustered-Index vorhanden sein, da die Datenzeilen selbst kann nur in einer Reihenfolge sortiert werden.

Das obige Zitat aus SQL Server-Online-Büchern verursacht viel Verwirrung

Meiner Meinung nach wäre es viel besser als ausgedrückt.

Es kann nur einen Clustered-Index pro Tabelle geben, da die Blattebenen des Clustered-Index sind die Tabellenzeilen sind. 

Das Online-Angebot für Bücher ist nicht falsch, aber Sie sollten sich darüber im Klaren sein, dass die "Sortierung" sowohl der nicht gruppierten als auch der gruppierten Indizes logisch und nicht physisch ist. Wenn Sie die Seiten auf Blattebene lesen, indem Sie der verknüpften Liste folgen und die Zeilen auf der Seite in der Reihenfolge der Slot-Arrays lesen, werden Sie die Indexzeilen in sortierter Reihenfolge lesen, aber physisch werden die Seiten möglicherweise nicht sortiert. Die weit verbreitete Annahme, dass bei einem gruppierten Index die Zeilen immer physisch auf der Festplatte in der gleichen Reihenfolge wie der Index Schlüssel gespeichert werden, ist falsch. 

Dies wäre eine absurde Implementierung. Wenn beispielsweise eine Zeile in die Mitte einer 4-GB-Tabelle eingefügt wird, muss der SQL Server nicht 2 GB Daten in die Datei kopieren, um Platz für die neu eingefügte Zeile zu schaffen.

Stattdessen tritt eine Seitenteilung auf. Jede Seite auf Blattebene sowohl von gruppierten als auch nicht gruppierten Indizes hat die Adresse (File:Page) der nächsten und vorherigen Seite in logischer Schlüsselreihenfolge. Diese Seiten müssen weder zusammenhängend noch in Schlüsselreihenfolge sein.

z.B. Die verknüpfte Seitenkette könnte 1:2000 <-> 1:157 <-> 1:7053 sein.

Wenn eine Seitenteilung auftritt, wird eine neue Seite von einer beliebigen Stelle in der Dateigruppe zugewiesen (entweder aus einem gemischten Bereich, für kleine Tabellen oder aus einem nicht leeren einheitlichen Bereich, der zu diesem Objekt gehört, oder einem neu zugewiesenen einheitlichen Bereich). Dies ist möglicherweise nicht einmal in derselben Datei, wenn die Dateigruppe mehr als eine enthält.

Der Grad, in dem sich die logische Reihenfolge und die zusammenhängende Verbindung von der idealisierten physikalischen Version unterscheiden, ist der Grad der logischen Fragmentierung. 

In einer neu erstellten Datenbank mit einer einzigen Datei habe ich Folgendes ausgeführt.

CREATE TABLE T
  (
     X TINYINT NOT NULL,
     Y CHAR(3000) NULL
  );

CREATE CLUSTERED INDEX ix
  ON T(X);

GO

--Insert 100 rows with values 1 - 100 in random order
DECLARE @C1 AS CURSOR,
        @X  AS INT

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT number
    FROM   master..spt_values
    WHERE  type = 'P'
           AND number BETWEEN 1 AND 100
    ORDER  BY CRYPT_GEN_RANDOM(4)

OPEN @C1;

FETCH NEXT FROM @C1 INTO @X;

WHILE @@FETCH_STATUS = 0
  BEGIN
      INSERT INTO T (X)
      VALUES        (@X);

      FETCH NEXT FROM @C1 INTO @X;
  END

Dann das Seitenlayout mit überprüft

SELECT page_id,
       X,
       geometry::Point(page_id, X, 0).STBuffer(1)
FROM   T
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
ORDER  BY page_id

Die Ergebnisse waren überall. Die erste Reihe in Tastenreihenfolge (mit Wert 1 - hervorgehoben mit Pfeil unten) befand sich fast auf der letzten physischen Seite.

enter image description here

Fragmentierung kann reduziert oder entfernt werden, indem ein Index neu erstellt oder reorganisiert wird, um die Korrelation zwischen logischer Reihenfolge und physischer Reihenfolge zu erhöhen.

Nach dem Rennen

ALTER INDEX ix ON T REBUILD;

Ich habe folgendes bekommen

enter image description here

Wenn die Tabelle keinen gruppierten Index hat, wird dies als Heap bezeichnet.

Nicht gruppierte Indizes können entweder auf einem Heap- oder einem gruppierten Index erstellt werden. Sie enthalten immer einen Zeilenlokator zurück zur Basistabelle. Bei einem Heap handelt es sich hierbei um einen physischen Zeilenbezeichner (rid), der sich aus drei Komponenten zusammensetzt (File: Page: Slot). Im Falle eines Clustered-Index ist der Zeilenlokator logisch (der Clustered-Indexschlüssel). 

Wenn der nicht gruppierte Index die CI-Schlüsselspalte (n) entweder als NCI-Schlüsselspalten oder INCLUDE- d-Spalten enthält, wird im letzteren Fall nichts hinzugefügt. Andernfalls werden die fehlenden CI-Schlüsselspalten automatisch dem NCI hinzugefügt.

SQL Server stellt immer sicher, dass die Schlüsselspalten für beide Indextypen eindeutig sind. Der Mechanismus, in dem dies für Indizes erzwungen wird, die nicht als eindeutig deklariert sind, unterscheidet sich jedoch zwischen den beiden Indextypen.

Clustered-Indizes erhalten eine uniquifier für Zeilen mit Schlüsselwerten, die eine vorhandene Zeile duplizieren. Dies ist nur eine aufsteigende ganze Zahl. 

Für nicht gruppierte Indizes, die nicht als eindeutig deklariert sind, fügt SQL Server den Zeilenlokator automatisch dem nicht gruppierten Indexschlüssel hinzu. Dies gilt für alle Zeilen, nicht nur für die tatsächlich vorhandenen Duplikate.

Die gruppierte vs. nicht gruppierte Nomenklatur wird auch für Spaltenspeicherindizes verwendet. Das Papier Erweiterungen der SQL Server-Spaltenspeicher Status

Obwohl Spaltenspeicherdaten nicht wirklich auf einem Schlüssel "gruppiert" sind, werden wir beschloss, die traditionelle SQL Server-Konvention des Verweises beizubehalten. auf den Primärindex als Clustered-Index.

253
Martin Smith

Mir ist klar, dass dies eine sehr alte Frage ist, aber ich dachte, ich würde eine Analogie anbieten, um die feinen Antworten zu veranschaulichen.

CLUSTERED INDEX

Wenn Sie in eine öffentliche Bibliothek gehen, werden Sie feststellen, dass alle Bücher in einer bestimmten Reihenfolge angeordnet sind (höchstwahrscheinlich das Dewey Decimal System oder DDS). Dies entspricht dem "Clustered Index" der Bücher. Wenn die DDS-Nummer für das gewünschte Buch 005.7565 F736s ist, suchen Sie zunächst die Zeile mit den Bücherregalen, die mit 001-099 oder ähnlich bezeichnet ist. (Dieses Endkappenzeichen am Ende des Stapels entspricht einem "Zwischenknoten" im Index.) Möglicherweise werden Sie bis zu einem bestimmten Regal mit der Bezeichnung 005.7450 - 005.7600 eingrillt und dann gescannt, bis Sie das Buch mit der angegebenen DDS # und gefunden haben an diesem Punkt Sie haben Ihr Buch gefunden.

NICHT GESTRICHTER INDEX

Aber wenn Sie nicht mit der DDS-Nummer Ihres Buchs in die Bibliothek aufgenommen wurden, benötigen Sie einen zweiten Index, um Sie zu unterstützen. Früher fand man an der Vorderseite der Bibliothek ein wunderbares Schubladenbüro, das als "Kartenkatalog" bekannt ist. Darin befanden sich Tausende von 3x5 Karten - eine für jedes Buch, alphabetisch sortiert (vielleicht nach Titel). Dies entspricht dem "nicht gruppierter Index". Diese Kartenkataloge waren in einer hierarchischen Struktur organisiert, so dass jede Schublade mit dem darin enthaltenen Kartenbereich gekennzeichnet wurde (beispielsweise Ka - Kl; d. H. Der "Zwischenknoten"). Noch einmal würden Sie so lange bohren, bis Sie Ihr Buch gefunden haben, aber wenn Sie es gefunden haben (dh den "Blattknoten"), haben Sie im Fall this nicht das Buch selbst, sondern nur eine Karte mit einer Index-Nummer (der DDS #), mit der Sie das tatsächliche Buch im gruppierten Index finden können.

Natürlich würde nichts den Bibliothekar davon abhalten, alle Karten zu kopieren und sie in einer anderen Reihenfolge in einem separaten Kartenkatalog zu sortieren. (In der Regel gab es mindestens zwei solcher Kataloge: einen nach Autorennamen und einen nach Titel.) Grundsätzlich könnten Sie so viele dieser "nicht gruppierten" Indizes haben, wie Sie möchten. 

103
kmote

Nachfolgend finden Sie einige Merkmale gruppierter und nicht gruppierter Indizes:

Clustered-Indizes

  1. Clustered-Indizes sind Indizes, die die Zeilen in einer SQL-Tabelle eindeutig identifizieren.
  2. Jede Tabelle kann genau einen Clusterindex haben.
  3. Sie können einen Clusterindex erstellen, der mehr als eine Spalte abdeckt. Zum Beispiel: create Index index_name(col1, col2, col.....).
  4. Standardmäßig verfügt eine Spalte mit einem Primärschlüssel bereits über einen Clusterindex.

Nicht gruppierte Indizes

  1. Nicht gruppierte Indizes sind wie einfache Indizes. Sie dienen lediglich zum schnellen Abrufen von Daten. Nicht sicher, eindeutige Daten zu haben.
66
Anirudh Sood

Eine sehr einfache, nicht technische Daumenregel wäre, dass in der Regel gruppierte Indizes für Ihren Primärschlüssel (oder zumindest eine eindeutige Spalte) verwendet werden und dass nicht gruppierte Indizes für andere Situationen verwendet werden (möglicherweise einen Fremdschlüssel) . Tatsächlich erstellt SQL Server standardmäßig einen Clusterindex für Ihre Primärschlüsselspalten. Wie Sie gelernt haben, bezieht sich der Clustered-Index darauf, wie Daten physisch auf der Festplatte sortiert werden, was bedeutet, dass er in den meisten Situationen eine gute Wahl ist.

45
Dan Diplo

Clustered Index

Ein Clustered-Index bestimmt die physikalische Reihenfolge von DATA in einer Tabelle. Aus diesem Grund hat eine Tabelle nur einen Clustered-Index.

wie "dictionary" Kein weiterer Index erforderlich, Index bereits nach Wörtern 

Nonclustered Index

Ein nicht gruppierter Index entspricht einem Index in einem Buch. Die Daten werden an einem Ort gespeichert. Der Index wird an einem anderen Ort gespeichert, und der Index enthält Zeiger auf den Speicherort der Daten. Aus diesem Grund enthält eine Tabelle mehr als einen Nonclustered-Index.

wie "Chemiebuch" beim Anstarren gibt es einen separaten Index, der auf die Position des Kapitels verweist, und am "ENDE" gibt es einen weiteren Index, der auf den gemeinsamen WORDS-Standort verweist 

13
abdul rehman kk

Clustered Index

Clustered-Indizes sortieren und speichern die Datenzeilen in der Tabelle oder Ansicht basierend auf ihren Schlüsselwerten. Dies sind die in der Indexdefinition enthaltenen Spalten. Pro Tabelle kann nur ein Clustered-Index vorhanden sein, da die Datenzeilen selbst nur in einer Reihenfolge sortiert werden können.

Die Datenzeilen in einer Tabelle werden nur in sortierter Reihenfolge gespeichert, wenn die Tabelle einen gruppierten Index enthält. Wenn eine Tabelle einen gruppierten Index hat, wird sie als gruppierte Tabelle bezeichnet. Wenn eine Tabelle keinen gruppierten Index hat, werden ihre Datenzeilen in einer ungeordneten Struktur gespeichert, die als Heap bezeichnet wird.

Nonclustered

Nicht gruppierte Indizes haben eine Struktur, die von den Datenzeilen getrennt ist. Ein nicht gruppierter Index enthält die nicht gruppierten Indexschlüsselwerte, und jeder Schlüsselwerteintrag enthält einen Zeiger auf die Datenzeile, die den Schlüsselwert enthält .. Der Zeiger von einer Indexzeile in einem nicht gruppierten Index auf eine Datenzeile wird Zeilenlokator genannt. Die Struktur des Zeilenlokators hängt davon ab, ob die Datenseiten in einem Heap oder in einer Clustertabelle gespeichert sind. Bei einem Heap ist ein Zeilenlokator ein Zeiger auf die Zeile. Bei einer gruppierten Tabelle ist der Zeilenlokator der gruppierte Indexschlüssel.

Sie können der Blattebene des nicht gruppierten Index Nonkey-Spalten hinzufügen, um vorhandene Indexschlüsselgrenzwerte zu umgehen und vollständig abgedeckte, indizierte Abfragen auszuführen. Weitere Informationen finden Sie unter Erstellen von Indizes mit eingeschlossenen Spalten. Einzelheiten zu den Grenzwerten für Indexschlüssel finden Sie unter Maximale Kapazitätsspezifikation für SQL Server.

Referenz: https://docs.Microsoft.com/de-de/sql/relational-databases/indexes/clustered-and-onclustered-indexes-beschrieben

3
marvelTracker

Clustered Index: Die Primärschlüsseleinschränkung erstellt automatisch einen Clustered-Index, wenn in der Tabelle noch kein Clustered-Index vorhanden ist. Die tatsächlichen Daten des gruppierten Index können auf der Blattebene des Index gespeichert werden. 

Nicht gruppierter Index: Die tatsächlichen Daten des nicht gruppierten Index werden nicht direkt am Blattknoten gefunden, sondern müssen einen zusätzlichen Schritt zum Suchen durchführen, da nur Werte von Zeilenlokatoren vorhanden sind, die auf tatsächliche Daten zeigen. .Nicht Clustered-Index kann nicht als Clustered-Index sortiert werden. Es können mehrere nicht gruppierte Indizes pro Tabelle vorhanden sein. Tatsächlich hängt es von der verwendeten SQL Server-Version ab. Grundsätzlich erlaubt SQL Server 2005 249 nicht gruppierte Indizes und für die obigen Versionen wie 2008, 2016 999 nicht gruppierte Indizes pro Tabelle.

0

Lassen Sie mich eine Lehrbuchdefinition für "Clustering Index" anbieten, die aus 15.6.1 aus Database Systems: The Complete Book entnommen wird.

Wir können auch von Clustering-Indizes sprechen, bei denen es sich um Indizes für ein Attribut oder um Attribute handelt, sodass alle Tupel mit einem festen Wert für den Suchschlüssel dieses Index auf ungefähr so ​​wenigen Blöcken angezeigt werden, wie sie enthalten sind.

Um die Definition zu verstehen, werfen wir einen Blick auf Beispiel 15.10 des Lehrbuchs:

Eine Beziehung R(a,b), die nach dem Attribut a sortiert und darin gespeichert wird Ordnung, verpackt in Blöcken, ist sicherlich Clusterd. Ein Index für a ist ein Clustering-Index, da für einen gegebenen a- Wert a1 alle Tupel mit dieser Wert für a ist aufeinanderfolgend. Sie erscheinen also verpackt in Blöcke, möglicherweise mit Ausnahme des ersten und letzten Blocks, der .__ enthält. a- Wert a1, wie in Abb. 15.14 vorgeschlagen. Ein Index für b ist jedoch unwahrscheinlich Clustering, da die Tupel mit einem festen b- Wert wird über die gesamte Datei verteilt, es sei denn, die Werte von a und b sind sehr eng korreliert.

 Fig 15.14

Beachten Sie, dass die Definition nicht erzwingt, dass die Datenblöcke auf dem Datenträger zusammenhängend sein müssen. es heißt nur, Tupel mit dem Suchschlüssel werden in so wenige Datenblöcke wie möglich gepackt.

Ein verwandtes Konzept ist Cluster-Beziehung. Eine Relation wird "gruppiert", wenn ihre Tupel in ungefähr so ​​wenige Blöcke gepackt sind, wie sie möglicherweise enthalten sind. Mit anderen Worten, aus der Sicht eines Plattenblocks können diese Beziehungen nicht gebündelt werden, wenn sie Tupel aus verschiedenen Beziehungen enthält (dh, es gibt einen gepackteren Weg, um diese Beziehung zu speichern, indem die Tupel dieser Beziehung von anderen Plattenblöcken mit der Datenbank ausgetauscht werden Tupel, die nicht zu der Relation im aktuellen Plattenblock gehören). Natürlich ist R(a,b) im obigen Beispiel gruppiert. 

Um zwei Konzepte miteinander zu verbinden, kann eine Cluster-Beziehung einen Clustering-Index und einen Non-Clustering-Index aufweisen. Für eine nicht gruppierte Beziehung ist ein Clustering-Index jedoch nicht möglich, es sei denn, der Index wird über dem Primärschlüssel der Beziehung erstellt.

"Cluster" als Word wird über alle Abstraktionsebenen der Datenbankspeicherseite (drei Abstraktionsebenen: Tupel, Blöcke, Datei) gespammt. Ein Konzept namens " clustered file ", das beschreibt, ob eine Datei (eine Abstraktion für eine Gruppe von Blöcken (ein oder mehrere Plattenblöcke)) Tupel aus einer Beziehung oder verschiedenen Beziehungen enthält. Es bezieht sich nicht auf das Clustering-Index-Konzept, da es sich auf Dateiebene befindet.

Einige Unterrichtsmaterialien möchten jedoch den Clustering-Index basierend auf der Definition der Cluster-Datei definieren. Diese beiden Definitionstypen sind auf der Ebene der Cluster-Beziehung gleich, unabhängig davon, ob sie die Cluster-Beziehung in Bezug auf den Block oder die Datei einer Datenplatte definieren. Über den Link in diesem Absatz 

Ein Index für Attribut (e) A für eine Datei ist ein Clustering-Index, wenn: Alle Tupel mit dem Attributwert A = a werden sequentiell (= fortlaufend) in der Datendatei gespeichert

Das aufeinanderfolgende Speichern von Tupeln ist dasselbe, als würde man sagen: "Tupel werden in ungefähr so ​​wenig Blöcke wie möglich gepackt" (mit geringfügigen Unterschieden in einer Datei, der andere in einem Datenträger). Es ist deshalb so, weil das aufeinanderfolgende Speichern von Tuple der Weg ist, "in ungefähr so ​​wenige Blöcke gepackt zu werden, wie es diese Tupel möglicherweise halten kann".

0
xxks-kkk