Was sind die Nachteile des MySQL-Clustered-Index?

Der Clustered-Index ist kein separater Indextyp, sondern eine Datenspeichermethode (keine Datenstruktur, sondern eine Speicherstruktur). Die spezifischen Details hängen von der Implementierung ab, aber der Clustered-Index von innodb ist tatsächlich der Btree-Index und die Datenzeilen in derselben Struktur gespeichert.

Wenn eine Tabelle einen Index hat, werden ihre Datenzeilen tatsächlich in den Blattseiten des Index gespeichert. Clustering bedeutet, dass die Datenzeilen und benachbarten Schlüsselwerte kompakt zusammen gespeichert werden, da die Datenzeilen nicht gespeichert werden können Gleichzeitig gespeichert an zwei verschiedenen Orten, sodass eine Tabelle nur einen Clustered-Index haben kann. Da die Speicher-Engine für die Implementierung des Index verantwortlich ist, unterstützen nicht alle Speicher-Engines Clustered-Indizes. Im Folgenden wird hauptsächlich Innodb vorgestellt, aber die unten diskutierten Prinzipien gelten für jede Engine, die Clustered-Indizes unterstützt:

Die Blattseite enthält alle Daten der Zeile, aber die Knotenseite enthält nur die Indexspalte (oder diese). Man kann sagen, dass es sich nicht um ein Blatt handelt. Die Knotenseiten des Knotens enthalten den Index des Indexwerts, da die in diesen Knotenseiten enthaltenen Werte aus der Indexspalte extrahiert werden.

Innodb aggregiert Daten nach Primärschlüssel. Wenn kein Primärschlüssel definiert ist, wählt Innodb stattdessen den ersten nicht leeren eindeutigen Index. Wenn kein nicht leerer eindeutiger Index vorhanden ist, definiert Innodb implizit eine 6 -Byte-Zeilen-ID-Primärschlüssel als Clustered-Index. InnoDB aggregiert nur Datensätze auf derselben Seite. Seiten mit benachbarten Schlüsselwerten können weit voneinander entfernt sein.

Hinweis: Geclusterte Primärschlüssel können die Leistung verbessern, sie können jedoch auch schwerwiegende Leistungsprobleme verursachen, insbesondere wenn die Speicher-Engine der Tabelle von innodb in eine andere Engine konvertiert wird.

Aggregierte Daten haben einige wichtige Vorteile:

A: Zusammengehörige Daten können zusammen gespeichert werden. Wenn Sie beispielsweise E-Mails implementieren, können Sie Daten basierend auf der Benutzer-ID aggregieren, sodass Sie sie nur sammeln müssen Daten von Alle E-Mails eines Benutzers können durch Lesen einer kleinen Anzahl von Datenseiten von der Festplatte abgerufen werden. Wenn der Clustered-Index nicht verwendet wird, kann jede E-Mail einen Festplatten-IO verursachen

B: Der Datenzugriff ist schneller. und der Clustered-Index indiziert und Die Daten werden im selben Btree gespeichert, sodass das Abrufen von Daten aus einem Clustered-Index normalerweise schneller ist als das Nachschlagen in einem Nicht-Clustered-Index

C: Abfragen mit einem Covering-Index-Scan können Verwenden Sie den Primärschlüsselwert direkt im Seitenknoten

Nachteile des Clustered-Index:

A: Clustered-Daten maximieren die Leistung IO-intensiver Anwendungen, aber wenn alle Daten im Speicher abgelegt werden, Die Zugriffsreihenfolge ist nicht so wichtig. Nein, der Clustered-Index hat keinen Vorteil.

B: Die Einfügegeschwindigkeit hängt stark von der Einfügereihenfolge ab. Das Einfügen in der Reihenfolge des Primärschlüssels ist der schnellste Weg, Daten zu laden die innodb-Tabelle, aber wenn sie nicht in der Reihenfolge der Primärschlüsseldaten geladen wird, ist es am besten, den Befehl „Tabelle optimieren“ zu verwenden, um die Tabelle nach Abschluss des Ladevorgangs neu zu organisieren

C: Clustered-Index-Spalten aktualisieren ist sehr teuer, da es innodb dazu zwingt, jede aktualisierte Zeile an einen neuen Speicherort zu verschieben

D: Wenn eine auf einem Clustered-Index basierende Tabelle eine neue Zeile einfügt oder wenn der Primärschlüssel aktualisiert wird und die Zeile aktualisiert werden muss Wenn der Primärschlüsselwert der Zeile erfordert, dass die Zeile in eine bestimmte Zeile eingefügt werden muss, kann es beim Verschieben zu Problemen kommen. Wenn die Seite voll ist, teilt die Speicher-Engine die Seite in zwei Seiten auf, um die Zeile aufzunehmen. Dies ist ein Seitenaufteilungsvorgang, der dazu führt, dass die Tabelle mehr Speicherplatz belegt

E: Aggregationsindizes können dazu führen, dass vollständige Tabellenscans langsamer werden, insbesondere wenn die Zeilen spärlich sind oder die Datenspeicherung unterbrochen ist Seitenteilungen

F: Der Sekundärindex ist möglicherweise größer als erwartet, da die Blattknoten des Sekundärindex die Primärschlüsselspalten der Referenzzeilen enthalten.

G: Der sekundäre Indexzugriff erfordert zwei Indexsuchen statt einer

Denn was im sekundären Indexblattknoten gespeichert ist, ist nicht der Zeiger auf den physischen Speicherort der Zeile, sondern der Primärschlüsselwert der Zeile. Dies bedeutet, dass die Speicher-Engine bei der Suche nach Zeilen über den Sekundärindex den Blattknoten des Sekundärindex finden muss, um den entsprechenden Primärschlüsselwert zu erhalten, und dann diesen Primärschlüsselwert verwenden muss, um die entsprechende Zeile im Clustered-Index zu finden. Hier werden wiederholte Arbeiten statt einmal durchgeführt. Bei innodb können adaptive Hash-Indizes solche wiederholten Arbeiten reduzieren.

Vergleich der Datenverteilung zwischen innodb und myisam physischem Speicher:

Myisam:

Es wird in der Reihenfolge der Dateneinfügung im Primärschlüsselindex und im Sekundärschlüssel gespeichert Ebene in Myisam Es gibt keinen Unterschied in der Struktur des Index. Der Primärschlüsselindex ist ein eindeutiger, nicht leerer Index mit dem Namen „Primär“.

innodb:

Da innodb Clustered-Indizes unterstützt, verwendet es eine ganz andere Methode zum Speichern derselben Daten. Der innodb-Clustered-Index enthält die Daten der gesamten Tabelle, nicht nur des Index In Innodb ist der Clustered-Index eine Tabelle und erfordert daher keinen unabhängigen Zeilenspeicher wie Myisam. Jeder Blattknoten des Clustered-Index enthält den Primärschlüsselwert, die Transaktions-ID, den Rollback-Zeiger für Transaktion und MVCC sowie die Werte aller verbleibenden Spalten. Wenn der Primärschlüssel ein Spaltenpräfixindex ist, enthält InnoDB auch den vollständigen Primärschlüssel Spalte und Die verbleibenden Spaltenwerte.

Ein weiterer Unterschied zu Myisam besteht darin, dass sich der Sekundärindex von Innodb stark vom Clustered-Index unterscheidet. Die Blattknoten des Sekundärindex von Innodb speichern nicht den Zeilenzeiger, sondern den Primärschlüsselwert Verwenden Sie diese Strategie als Zeiger auf Zeilen, wenn Zeilen verschoben oder Datenseiten geteilt werden. Der Vorteil besteht darin, dass InnoDB mehr Platz einnimmt Dieser Zeiger im Sekundärindex muss beim Verschieben von Zeilen nicht aktualisiert werden.

Fügen Sie Zeilen in der Reihenfolge des Primärschlüssels in die Innodb-Tabelle ein. Wenn Sie die Innodb-Tabelle verwenden und keine Daten aggregiert werden müssen, können Sie einen Ersatzschlüssel als Primärschlüsseldaten definieren sollte nichts mit der Anwendung zu tun haben. Die einfachste Methode besteht darin, auto_increment zum automatischen Inkrementieren der Spalte zu verwenden, wodurch sichergestellt werden kann, dass die Datenzeilen der Reihe nach eingefügt werden und die Leistung von Zuordnungsvorgängen basierend auf dem Primärschlüssel verbessert wird.

Verwenden Sie UUID nicht als Clustered-Index, da sonst die Leistung schrecklich wird, da das Einfügen des Clustered-Index völlig zufällig erfolgt und die Daten keine Clustering-Eigenschaften aufweisen. Denn das Einfügen von Zeilen mit UUID als Primärschlüssel dauert nicht nur länger, sondern auch der Index ist größer. Dies liegt zum Teil daran, dass das Primärschlüsselfeld länger wird, und zum anderen liegt es zweifellos an der längeren Zeit, die durch die Seitenaufteilung verursacht wird Die durch Fragmentierung verursachte Indexänderung ist groß. Da die Primärschlüsselwerte sequentiell sind, speichert Innodb jeden Datensatz nach dem vorherigen Datensatz. Wenn der maximale Füllfaktor der Seite erreicht ist (der standardmäßige maximale Füllfaktor von InnoDB beträgt 15/16 der Seitengröße, bleibt (um etwas freizugeben). Platz für spätere Änderungen) wird der nächste Datensatz auf eine neue Seite geschrieben. Sobald die Daten in dieser Reihenfolge geladen sind, wird die Primärschlüsselseite ungefähr mit sequenziellen Datensätzen gefüllt, was genau das ist, was erwartet wird (jedoch). (sekundäre Indexseiten können unterschiedlich sein).

Da der Primärschlüsselwert der neu eingefügten Zeile unter dem UUID-Primärschlüssel nicht unbedingt größer als der vorherige ist, kann innodb die neue Zeile nicht einfach immer am Ende des Index einfügen, sondern muss sie finden Der geeignete Speicherort ist normalerweise der mittlere Speicherort der vorhandenen Daten, und die Zuweisung von neuem Speicherplatz führt zu einer nicht optimalen Datenverteilung. Hier sind einige Nachteile der Verwendung von UUID als Primärschlüssel 🎜>

A: Die geschriebene Zielseite wurde möglicherweise auf die Festplatte geleert und aus dem Cache entfernt, oder sie wurde nicht in den Cache geladen. InnoDB muss die Zielseite zuvor von der Festplatte finden und in den Speicher lesen Das Einfügen führt zu vielen zufälligen IO

B: Da Schreibvorgänge nicht in der richtigen Reihenfolge sind, muss innodb häufig Seitenaufteilungsvorgänge durchführen, um Platz für neue Zeilen zuzuweisen Daten müssen gleichzeitig verschoben und eingefügt werden. Anstelle einer Seite müssen mindestens drei Seiten geändert werden.

C: Aufgrund häufiger Seitenaufteilungen werden die Seiten spärlich und unregelmäßig gefüllt, sodass die endgültigen Daten fragmentiert werden

Nachdem Sie diese Zufallswerte in den Clustered-Index geladen haben, müssen Sie möglicherweise eine Optimierungstabelle durchführen, um die Tabelle neu zu erstellen und die Seitenfüllung zu optimieren. Wenn Sie InnoDB verwenden, sollten Sie Daten so weit wie möglich in der Reihenfolge der Primärschlüssel einfügen und wann immer möglich eine einfache Erhöhung des Werts des Clustering-Schlüssels verwenden, um neue Zeilen einzufügen.

Hinweis: Wann führen sequentielle Primärschlüssel zu schlechteren Ergebnissen?

Bei Arbeitslasten mit hoher Parallelität kann das Einfügen in der Reihenfolge der Primärschlüssel in Innodb zu offensichtlichen Konflikten führen. Die Obergrenze des Primärschlüssels wird als Hotspot bezeichnet, da hier alle Einfügungen erfolgen, sodass gleichzeitige Einfügungen auftreten können Ein weiterer Hotspot kann der Sperrmechanismus auto_increment sein. Wenn dieses Problem auftritt, müssen Sie möglicherweise die Tabelle oder Anwendung neu entwerfen oder die Konfiguration von innodb_autoinc_lock_mode ändern.

Das obige ist der detaillierte Inhalt vonWas sind die Nachteile des MySQL-Clustered-Index?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!