Frage Was bedeuten Clustered und Non Clustered Index eigentlich?


Ich habe eine begrenzte Exposition gegenüber DB und habe nur DB als Anwendungsprogrammierer verwendet. Ich möchte es wissen Clustered und Non clustered indexes. Ich googelte und was ich fand war:

Ein gruppierter Index ist ein spezieller Indextyp, der den Weg neu definiert   Datensätze in der Tabelle sind physisch   gelagert. Daher kann Tabelle nur haben   ein gruppierter Index. Die Blattknoten   eines gruppierten Index enthalten die Daten   Seiten. Ein Nonclustered-Index ist ein   spezielle Art von Index, in dem die   logische Reihenfolge des Indexes nicht   Übereinstimmung mit der physikalisch gespeicherten Reihenfolge von   die Zeilen auf der Festplatte. Der Blattknoten von a   Nonclustered 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 in einfachem Englisch erklären?


805
2017-08-09 15:59


Ursprung


Antworten:


Bei einem gruppierten Index werden die Zeilen physisch auf der Festplatte in derselben Reihenfolge wie der Index gespeichert. Daher kann nur ein gruppierter Index vorhanden sein.

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 erhöht, die zum Schreiben neuer Datensätze benötigt wird.

Es ist im Allgemeinen schneller, aus einem Clustered-Index zu lesen, wenn Sie alle Spalten abrufen möchten. Sie müssen nicht zuerst zum Index und dann zum Tisch gehen.

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


804
2017-08-09 16:05



Ein gruppierter Index bedeutet, dass Sie der Datenbank mitteilen, dass nahe beieinander liegende Werte auf dem Datenträger gespeichert werden sollen. Dies hat den Vorteil des schnellen Scannens / Wiederauffindens von Datensätzen, die in einen Bereich von Clustered-Index-Werten fallen.

Zum Beispiel haben Sie zwei Tabellen, Kunde und Bestellung:

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

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

Wenn Sie schnell alle Aufträge eines bestimmten Kunden abrufen möchten, möchten Sie möglicherweise einen gruppierten Index in der Spalte "CustomerID" der Auftragstabelle erstellen. Auf diese Weise werden die Datensätze mit derselben CustomerID physisch nahe beieinander auf der Festplatte gespeichert (gruppiert), was deren Abruf beschleunigt.

P.S. Der Index für CustomerID wird natürlich nicht eindeutig sein, also müssen Sie entweder ein zweites Feld hinzufügen, um den Index zu "uniquifizieren", oder die Datenbank kann das für Sie übernehmen, aber das ist eine andere Geschichte.

In Bezug auf mehrere Indizes. Sie können nur einen Clustered-Index pro Tabelle haben, da dieser 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 zusammenfassen oder sie alle zu einem großen Konferenztisch zusammenziehen, aber nicht auf beide Arten gleichzeitig. Eine Tabelle kann andere Indizes haben, sie zeigen dann auf die Einträge im Clustered-Index, der seinerseits sagt, wo die tatsächlichen Daten zu finden sind.


528
2017-08-09 16:01



Im reihenorientierten SQL Server-Speicher sind Clustered- und Nonclustered-Indizes als B-Bäume organisiert.

enter image description here

(Bildquelle)

Der Hauptunterschied zwischen gruppierten Indizes und nicht gruppierten Indizes besteht in der Blattebene des gruppierten Index ist Der Tisch. Dies hat zwei Auswirkungen.

  1. Die Zeilen auf den Blattseiten des gruppierten Index enthalten immer etwas für jede der (nicht spärlichen) Spalten in der Tabelle (entweder der Wert oder ein 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 mithilfe von INCLUDE Klausel (seit SQL Server 2005), um explizit alle Nicht-Schlüsselspalten einzuschließen, aber sie sind sekundäre Repräsentationen und es gibt immer eine andere 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 werden nahezu identisch sein. Mit den Indexseiten der oberen Ebene, die Werte für die Schlüsselspalten enthalten A,B und die Blattebenen-Seiten enthalten A,B,C,D

Es kann nur einen gruppierten Index pro Tabelle geben, weil die Datenzeilen   selbst können in nur einer Reihenfolge sortiert werden.

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

Meiner Meinung nach wäre es viel besser formuliert als.

Es kann nur einen Clustered-Index pro Tabelle geben, da es sich um die Blattebenenzeilen des Clustered-Index handelt sind die Tabellenzeilen.

Das Online-Angebot für Bücher ist nicht inkorrekt, aber Sie sollten sich darüber im Klaren sein, dass das "Sortieren" von nicht gruppierten und 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, lesen Sie die Indexzeilen in sortierter Reihenfolge, aber die Seiten sind möglicherweise nicht sortiert. Die gängige Annahme, dass bei einem gruppierten Index die Zeilen immer physisch auf der Festplatte in der gleichen Reihenfolge wie der Index gespeichert werden Schlüssel ist falsch.

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

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

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

Wenn eine Seitenaufteilung auftritt, wird eine neue Seite von einer beliebigen Stelle in der Dateigruppe zugewiesen (entweder in einem gemischten Ausmaß für kleine Tabellen oder eine nicht leere einheitliche Ausdehnung, die zu diesem Objekt gehört, oder eine neu zugeteilte einheitliche Ausdehnung). Dies ist möglicherweise nicht in der gleichen Datei enthalten, wenn die Dateigruppe mehrere enthält.

Der Grad, um den die logische Reihenfolge und Kontiguität von der idealisierten physikalischen Version abweicht, 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 überprüft das Seitenlayout mit

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 Schlüsselreihenfolge (mit Wert 1 - markiert mit Pfeil unten) war fast auf der letzten physischen Seite.

enter image description here

Die Fragmentierung kann reduziert oder entfernt werden, indem ein Index neu aufgebaut oder reorganisiert wird, um die Korrelation zwischen logischer Reihenfolge und physikalischer 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 sie als Heap bezeichnet.

Nicht gruppierte Indizes können entweder auf einem Heap oder einem gruppierten Index erstellt werden. Sie enthalten immer einen Zeilen-Locator zurück zur Basistabelle. Bei einem Heap ist dies eine physikalische Zeilenkennung (Rid) und besteht aus drei Komponenten (File: Page: Slot). Bei einem Clustered-Index ist der Zeilenlokalisierer logisch (der Clustered-Index-Schlüssel).

Für den letzteren Fall, wenn der Nicht-Cluster-Index die CI-Schlüsselspalte (n) bereits entweder als NCI-Schlüsselspalten oder enthält INCLUDE-d Spalten wird dann 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 nicht als eindeutig deklarierte Indizes erzwungen wird, unterscheidet sich jedoch zwischen den beiden Indextypen.

Clustered-Indizes erhalten einen uniquifier für alle Zeilen mit Schlüsselwerten hinzugefügt, die eine vorhandene Zeile duplizieren. Dies ist nur eine aufsteigende Ganzzahl.

Für nicht gruppierte Indizes, die nicht als eindeutiger SQL Server deklariert sind, wird der Zeilenlokalisierer automatisch zum nicht gruppierten Indexschlüssel hinzugefügt. Dies gilt für alle Zeilen, nicht nur für die, die tatsächlich Duplikate sind.

Die clustered vs non clustered Nomenklatur wird auch für Spaltenspeicherindizes verwendet. Das Papier Erweiterungen für SQL Server-Spaltenspeicher Zustände

Obwohl Spaltenspeicherdaten nicht wirklich auf irgendeinem Schlüssel "gruppiert" sind, wir   beschlossen, die traditionelle SQL Server-Konvention der Verweisung zu behalten   zum primären Index als gruppierter Index.


206
2018-06-28 19:16



Ich weiß, dass dies eine sehr alte Frage ist, aber ich dachte, ich würde eine Analogie anbieten, um die obigen feinen Antworten zu illustrieren.

CLUSTERED INDEX

Wenn Sie in eine öffentliche Bibliothek gehen, werden Sie feststellen, dass die Bücher alle in einer bestimmten Reihenfolge angeordnet sind (höchstwahrscheinlich das Dewey Decimal System oder DDS). Dies entspricht dem "gruppierter Index" der Bücher. Wenn die DDS # für das Buch, das Sie möchten, war 005.7565 F736s, würden Sie beginnen, indem Sie die Reihe der Bücherregale finden, die markiert ist 001-099 oder etwas ähnliches. (Dieses Endcap-Zeichen am Ende des Stapels entspricht einem "intermediate node" im Index.) Schließlich würden Sie einen Drilldown zu dem spezifischen Regal mit der Bezeichnung durchführen 005.7450 - 005.7600, dann würden Sie scannen, bis Sie das Buch mit der angegebenen DDS # gefunden haben, und an diesem Punkt Du hast dein Buch gefunden.

Nicht geclusterter Index

Aber wenn Sie nicht mit der DDS # Ihres Buches in die Bibliothek kommen, dann brauchen Sie einen zweiten Index, um Ihnen zu helfen. Früher gab es an der Vorderseite der Bibliothek ein wunderbares Kommode-Kommode, das "Kartenkatalog". Darin waren Tausende von 3x5 Karten - eine für jedes Buch, sortiert in alphabetischer Reihenfolge (nach Titel, vielleicht). Dies entspricht dem "nicht gruppierter Index". Diese Kartenkataloge waren in einer hierarchischen Struktur organisiert, so dass jede Schublade mit dem Kartenumfang beschriftet war (Ka - Kl, beispielsweise; d.h. der "Zwischenknoten"). Noch einmal bohrten Sie ein, bis Sie Ihr Buch gefunden hatten, aber in Dies Fall, wenn Sie es gefunden haben (d. h. der "Blattknoten"), haben Sie nicht das Buch selbst, sondern nur eine Karte mit einem Index Nummer (die DDS #), mit der Sie das tatsächliche Buch im gruppierten Index finden konnten.

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. (Normalerweise gab es mindestens zwei solcher Kataloge: einer nach Autor und einer nach Titel.) Im Prinzip könnten Sie so viele dieser "nicht gruppierten" Indizes haben, wie Sie möchten.


67
2017-10-26 21:06



Unten finden Sie einige Merkmale von gruppierten und nicht gruppierten Indizes:

Clustered-Indizes

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

Nicht geclusterte Indizes

  1. Nicht gruppierte Indizes sind wie einfache Indizes. Sie dienen nur zum schnellen Abruf von Daten. Nicht sicher, einzigartige Daten zu haben.

60
2018-01-21 14:21



Eine sehr einfache, nicht-technische Faustregel wäre, dass Clustered-Indizes normalerweise für Ihren Primärschlüssel (oder zumindest eine eindeutige Spalte) verwendet werden und dass Nicht-Clustered für andere Situationen verwendet werden (z. B. ein Fremdschlüssel). . Tatsächlich erstellt SQL Server standardmäßig einen Clustered-Index für Ihre Primärschlüsselspalte (n). Wie Sie wissen werden, bezieht sich der gruppierte Index auf die Art und Weise, wie Daten physisch auf der Festplatte sortiert werden, was bedeutet, dass sie für die meisten Situationen eine gute Allround-Wahl ist.


41
2017-08-09 16:17



Clustered Index

Ein gruppierter Index bestimmt die physische Reihenfolge von DATEN in einer Tabelle. Aus diesem Grund hat eine Tabelle nur 1 gruppierten Index.

wie "Dictionary" Keine Notwendigkeit für einen anderen Index, seine bereits Index nach Worten

Nicht gruppierter Index

Ein nicht gruppierter Index entspricht einem Index in einem Buch. Die Daten werden an einer Stelle gespeichert. das Der Index wird an einer anderen Stelle gespeichert und der Index weist Zeiger auf den Speicherort der Daten auf. Aus diesem Grund hat eine Tabelle mehr als 1 Nonclustered-Index.

Wie bei "Chemiebuch" beim Anschauen gibt es einen separaten Index, um auf den Kapitelstandort zu zeigen. Am "END" befindet sich ein weiterer Index, der auf den gemeinsamen WORDS-Ort zeigt


4
2018-01-21 18:47