Detaillierte Analyse der Auswahl des MySQL-Gemeinschaftsindex und des eindeutigen Index

Nehmen wir ein Benutzerverwaltungssystem an, bei dem sich jede Person mit einer eindeutigen Mobiltelefonnummer registriert und der Geschäftscode sicherstellt, dass keine doppelten Mobiltelefonnummern eingegeben werden. Wenn das Benutzerverwaltungssystem einen Namen per Mobiltelefonnummer nachschlagen muss, führt es eine SQL-Anweisung wie diese aus:

Wählen Sie den Namen aus den Benutzern aus, bei denen die Mobilnummer = „15202124529“ ist.

Normalerweise würden Sie erwägen, einen Index für das mobile Feld zu erstellen. Da das Feld für die Mobiltelefonnummer relativ groß ist, wird es normalerweise nicht als Primärschlüssel verwendet. Daher gibt es jetzt zwei Möglichkeiten:

1. Erstellen Sie einen eindeutigen Index für das Feld id_card
2. Erstellen Sie einen normalen Index

Wenn im Geschäftscode sichergestellt ist, dass keine doppelten ID-Nummern geschrieben werden, sind beide Optionen logisch korrekt.

Sollten wir aus Leistungsgründen einen eindeutigen oder einen normalen Index wählen?

Wie in der Abbildung gezeigt: Angenommen, die Werte im Feld k werden nicht wiederholt

Als Nächstes analysieren wir die Leistungsauswirkungen dieser beiden (ID, k)-Indizes auf Abfrage- und Aktualisierungsanweisungen.

Abfrageprozess

Nehmen Sie an, dass die Abfrageanweisung lautet: „Select id from T where k=5“. Die Abfrageanweisung durchsucht den Indexbaum, indem sie an der Wurzel des B+-Baums beginnt und Schicht für Schicht bis zum Blattknoten sucht, der die Datenseite in der unteren rechten Ecke der Abbildung ist. Dann können wir davon ausgehen, dass die Datenseite eine binäre Suche verwendet, um den Datensatz zu finden (die Datenseite verwendet ein geordnetes Array, um die Knoten zu speichern. Die Datenseiten sind durch eine bidirektionale verknüpfte Liste in Reihe verbunden).

• Bei einem normalen Index müssen Sie, nachdem Sie den ersten Datensatz gefunden haben, der die Bedingung (5.500) erfüllt, den nächsten Datensatz suchen, bis Sie auf den ersten Datensatz stoßen, der die Bedingung k=5 nicht erfüllt.
• Bei einem eindeutigen Index wird die Suche beendet, nachdem der erste Datensatz gefunden wurde, der die Bedingungen erfüllt, da der Index die Eindeutigkeit definiert.
  

Wie groß wird also die Leistungslücke aufgrund dieses Unterschieds sein? Die Antwort ist: sehr wenig.

Grund: Sofern die Schlüsselspalte nicht sehr groß ist und mehrere aufeinanderfolgende Schlüssel eine ganze Seite belegen, führt dies zu einem Seiten-IO, was zu einem deutlicheren Leistungsunterschied führt. Aus Sicht der durchschnittlichen Amortisierung ist der Unterschied nahezu vernachlässigbar.

InnoDB-Daten werden in Datenseiteneinheiten gelesen und geschrieben. Das heißt, wenn ein Datensatz gelesen werden muss, wird nicht der Datensatz selbst von der Festplatte gelesen, sondern als Ganzes in Seiten in den Speicher gelesen. In InnoDB beträgt die Standardgröße jeder Datenseite 16 KB.

Aktualisierungsprozess

Um die Auswirkungen gemeinsamer und eindeutiger Indizes auf die Leistung von Aktualisierungsanweisungen zu erklären, müssen wir zunächst den Änderungspuffer einführen.

• Wenn eine Datenseite aktualisiert werden muss und sich die Datenseite im Speicher befindet, wird sie direkt aktualisiert.
• Wenn die Datenseite noch nicht im Speicher ist, ohne die Datenkonsistenz zu beeinträchtigen:
  

1. InnoDB speichert diese Aktualisierungsvorgänge im Änderungspuffer zwischen, sodass die Datenseite nicht von der Festplatte gelesen werden muss.
2. Wenn eine Abfrage das nächste Mal auf diese Datenseite zugreifen muss, wird die Datenseite in den Speicher gelesen.
3. Führen Sie dann die mit dieser Seite verbundenen Vorgänge im Änderungspuffer aus.
   

Auf diese Weise kann die Richtigkeit der Datenlogik gewährleistet werden

Es ist zu beachten, dass es sich, obwohl der Name Änderungspuffer lautet, tatsächlich um persistente Daten handelt. Das heißt, es gibt eine Kopie des Änderungspuffers im Speicher und er wird auch auf die Festplatte geschrieben.

Der Vorgang des Anwendens der Operationen im Änderungspuffer auf die alte Datenseite zum Erhalten der neuen Datenseite sollte als Zusammenführen bezeichnet werden.

P.S. Zusätzlich zum Zugriff auf diese Datenseite, der eine Zusammenführung auslöst, verfügt das System über einen Hintergrundthread, der in regelmäßigen Abständen Zusammenführungen durchführt. Der Zusammenführungsvorgang wird auch während des normalen Herunterfahrens der Datenbank ausgeführt.

(Der Zusammenführungsvorgang des Änderungspuffers aktualisiert zuerst den Änderungspuffervorgang auf die Datenseite im Speicher. Dieser Vorgang wird in das Redo-Protokoll geschrieben. Wenn MySQL nicht ausgefallen ist und das Redo-Protokoll voll ist und der Prüfpunkt verschoben werden muss, werden die Daten im Speicher auf die Festplatte aktualisiert, indem beurteilt wird, ob die Daten auf der Festplatte mit den Daten auf der Festplatte übereinstimmen, d. h. ob es sich um eine schmutzige Seite handelt. Wenn MySQL ausgefallen ist und kein Speicher vorhanden ist, d. h. keine schmutzige Seite, wird es über das Redo-Protokoll wiederhergestellt.)

Wenn der Aktualisierungsvorgang zunächst im Änderungspuffer aufgezeichnet werden kann, um das Lesen auf der Festplatte zu reduzieren, wird die Ausführungsgeschwindigkeit der Anweisung natürlich erheblich verbessert.

Darüber hinaus erfordert das Lesen von Daten in den Speicher die Belegung des Pufferpools, sodass mit dieser Methode auch die Speicherbelegung vermieden und die Speicherauslastung verbessert werden kann.

Unter welchen Voraussetzungen kann der Wechselgeldpuffer genutzt werden?

Bei einem eindeutigen Index muss bei allen Aktualisierungsvorgängen zunächst ermittelt werden, ob der Vorgang die Eindeutigkeitsbeschränkung verletzt.

Um beispielsweise den Datensatz (4.400) einzufügen, müssen wir zunächst ermitteln, ob in der Tabelle bereits ein Datensatz mit k=4 vorhanden ist. Um dies festzustellen, müssen wir die Datenseite in den Speicher lesen.

Wenn bereits alles in den Speicher eingelesen wurde, wäre es schneller, den Speicher direkt zu aktualisieren, und es wäre nicht erforderlich, einen Änderungspuffer zu verwenden.

Daher kann der Änderungspuffer nicht zum Aktualisieren des eindeutigen Index verwendet werden. Tatsächlich können nur normale Indizes verwendet werden.

Der Änderungspuffer verwendet den Speicher im Pufferpool und kann daher nicht unbegrenzt vergrößert werden. Die Größe des Änderungspuffers kann dynamisch über den Parameter innodb_change_buffer_max_size eingestellt werden. Wenn dieser Parameter auf 50 gesetzt ist, bedeutet dies, dass die Größe des Änderungspuffers maximal 50 % des Pufferpools einnehmen kann.

Ps. Datenbank-Pufferpool (Pufferpool) https://www.jianshu.com/p/f9ab1cb24230

Analyse: Einfügen eines neuen Datensatzes InnoDB-Verarbeitungsablauf

Nachdem wir nun den Änderungspuffermechanismus verstanden haben, wie sieht der InnoDB-Prozess zum Einfügen eines neuen Datensatzes (4.400) in diese Tabelle aus?

1. Der erste Fall ist: Die durch diesen Datensatz zu aktualisierende Zielseite befindet sich im Speicher.

• Zu diesem Zeitpunkt ist der Verarbeitungsablauf von InnoDB wie folgt: Suchen Sie für den eindeutigen Index die Position zwischen 3 und 5, stellen Sie fest, dass kein Konflikt vorliegt, fügen Sie diesen Wert ein und die Anweisungsausführung wird beendet.
• Suchen Sie für einen normalen Index die Position zwischen 3 und 5, fügen Sie den Wert ein und die Anweisungsausführung wird beendet.
  

Auf diese Weise ist der Unterschied zwischen der Auswirkung gewöhnlicher und eindeutiger Indizes auf die Leistung von Aktualisierungsanweisungen lediglich eine Ermessensfrage und beansprucht nur eine winzige Menge an CPU-Zeit. Aber das ist nicht der Schwerpunkt

2. Die zweite Situation ist, dass die durch diesen Datensatz zu aktualisierende Zielseite nicht im Speicher vorhanden ist. Derzeit ist der Verarbeitungsablauf von InnoDB wie folgt:

• Für einen eindeutigen Index muss die Datenseite in den Speicher gelesen werden. Wenn kein Konflikt vorliegt, wird der Wert eingefügt und die Anweisungsausführung beendet.
• Bei allgemeinen Indizes wird die Aktualisierung im Änderungspuffer aufgezeichnet und die Anweisungsausführung abgeschlossen.
  

Das Lesen von Daten von der Festplatte in den Speicher erfordert wahlfreien E/A-Zugriff und ist einer der aufwändigsten Vorgänge in einer Datenbank. Da der Änderungspuffer den wahlfreien Festplattenzugriff reduziert, ist die Verbesserung der Aktualisierungsleistung deutlich.

Der Änderungspuffer wird hauptsächlich verwendet, um Aktualisierungsvorgänge zwischenzuspeichern und asynchron zu verarbeiten. Auf diese Weise kann jede Aktualisierung direkt im Änderungspuffer aufgezeichnet werden, was sehr schnell ist und mehrere Schreibvorgänge auf die Festplatte in einen einzigen Schreibvorgang auf die Festplatte umwandeln kann.

Szenarien zur Puffernutzung ändern

Aus der obigen Analyse geht klar hervor, dass die Verwendung des Änderungspuffers den Aktualisierungsprozess beschleunigt. Es ist auch klar, dass der Änderungspuffer auf die Verwendung in allgemeinen Indexszenarien beschränkt ist und nicht für eindeutige Indizes geeignet ist.

Kann die Verwendung eines Änderungspuffers alle Szenarien gängiger Indizes beschleunigen?

Denn während der Zusammenführung werden die Daten tatsächlich aktualisiert, und der Änderungspuffer dient vor allem dazu, die aufgezeichneten Änderungen zwischenzuspeichern. Daher gilt: Je mehr Änderungen der Änderungspuffer vor der Zusammenführung einer Datenseite aufzeichnet (d. h. je öfter diese Seite aktualisiert werden muss), desto größer ist der Nutzen.

Daher ist für Unternehmen, die viel schreiben und weniger lesen, die Wahrscheinlichkeit, dass auf eine Seite unmittelbar nach dem Schreiben zugegriffen wird, relativ gering und der Änderungspuffer ist zu diesem Zeitpunkt am effektivsten. Diese Art von Geschäftsmodell ist bei Abrechnungs- und Protokollierungssystemen üblich. (Geeignet für Szenarien mit mehr Schreibvorgängen und weniger Lesevorgängen. Mehr Lesevorgänge und weniger Schreibvorgänge erhöhen die Wartungskosten des Änderungspuffers.)

Wenn umgekehrt der Aktualisierungsmodus eines Unternehmens darin besteht, unmittelbar nach dem Schreiben abzufragen, wird der Zusammenführungsprozess sofort ausgelöst, da bald auf die Datenseite zugegriffen wird, auch wenn die Bedingungen erfüllt sind und die Aktualisierung zuerst im Änderungspuffer aufgezeichnet wird. Dadurch wird die Anzahl der wahlfreien E/A-Vorgänge nicht verringert, aber die Wartungskosten des Änderungspuffers steigen. Daher hat der Änderungspuffer für dieses Geschäftsmodell tatsächlich einen Nebeneffekt. (Wenn Sie die Ergebnisse des Aktualisierungsvorgangs des normalen Index sofort abfragen, wird ein Zusammenführungsvorgang ausgelöst und die Daten auf der Festplatte werden mit den Vorgangsdatensätzen im Änderungspuffer zusammengeführt, wodurch eine große Menge an E/A erzeugt wird.)

Indexauswahl und Praxis

Wie sollten wir auf der Grundlage der obigen Analyse zwischen gemeinsamen und eindeutigen Indizes wählen?

Tatsächlich gibt es hinsichtlich der Abfragefunktionen keinen Unterschied zwischen diesen beiden Indextypen. Die wichtigste Überlegung ist die Auswirkung auf die Aktualisierungsleistung. Daher wird empfohlen, möglichst gemeinsame Indizes zu wählen.

Wenn auf alle Aktualisierungen Abfragen für den gleichen Datensatz folgen, sollte der Änderungspuffer geschlossen werden.

In allen anderen Fällen kann der Änderungspuffer die Aktualisierungsleistung verbessern. In der Praxis ist die Kombination aus normalen Indizes und Änderungspuffern sehr effektiv, um Aktualisierungen von Tabellen mit großen Datenmengen zu optimieren.

P.S.: Gerade beim Einsatz mechanischer Festplatten ist der Effekt des Change-Buffer-Mechanismus sehr groß. Wenn es eine Bibliothek wie „Historische Daten“ gibt, sollten Sie daher den Indizes in diesen Tabellen besondere Aufmerksamkeit schenken, versuchen, gemeinsame Indizes zu verwenden und dann den Änderungspuffer so weit wie möglich erhöhen, um die Datenschreibgeschwindigkeit dieser „Historischen Daten“-Tabelle sicherzustellen.

Änderungspuffer und Redo-Log

Wenn Sie das Prinzip des Änderungspuffers verstehen, erinnern Sie sich möglicherweise an Redo-Log und WAL (Write-Ahead Logging, bei dem es entscheidend darum geht, zuerst Protokolle zu schreiben und diese dann auf die Festplatte zu schreiben).

Der Kernmechanismus von WAL zur Verbesserung der Leistung besteht in der Minimierung zufälliger Lese- und Schreibvorgänge

Führen Sie diese Einfügeanweisung für die Tabelle aus:

mysql> einfügen in t(id,k) Werte(id1,k1),(id2,k2);

Unter der Annahme des aktuellen Status des k-Indexbaums befindet sich nach dem Auffinden des Speicherorts die Datenseite, auf der sich k1 befindet, im Speicher (InnoDB-Pufferpool), und die Datenseite, auf der sich k2 befindet, befindet sich nicht im Speicher. Die folgende Abbildung zeigt das Update-Statusdiagramm mit Änderungspuffer.

Abbildung 3: Update-Prozess mit Änderungspuffer

Bei der Analyse dieser Aktualisierungsanweisung werden Sie feststellen, dass sie aus vier Teilen besteht:

Speicher, Redo-Log (ib_log_fileX), Datentabellenbereich (t.ibd), Systemtabellenbereich (ibdata1).

Datentabellenbereich: Dies ist eine Tabellendatendatei. Die entsprechende Datenträgerdatei ist „Tabellenname.ibd“; Systemtabellenbereich: Wird zum Speichern von Systeminformationen wie Datenwörterbuch usw. verwendet. Die entsprechende Datenträgerdatei ist „ibdata1“

Der Datentabellenbereich und der Systemtabellenbereich scheinen die komplexe Struktur darzustellen, die dem B+-Baum entspricht.

Diese Update-Anweisung führt die folgenden Operationen aus (in der Abbildung in numerischer Reihenfolge):

1. Seite 1 Im Speicher, den Speicher direkt aktualisieren;
2. Seite 2 befindet sich nicht im Speicher, daher wird im Änderungspufferbereich im Speicher aufgezeichnet: „Ich möchte eine Zeile in Seite 2 einfügen“
3. Diese Information zeichnet die beiden oben genannten Aktionen im Redo-Log auf (3 und 4 in der Abbildung).
   

Nachdem Sie die oben genannten Schritte ausgeführt haben, kann die Transaktion abgeschlossen werden. Sie sehen also, dass der Aufwand für die Ausführung dieser Aktualisierungsanweisung sehr gering ist. Sie besteht darin, zwei Speicher und dann eine Festplatte zu schreiben (die beiden Vorgänge zusammen schreiben eine Festplatte), und sie wird sequenziell geschrieben.

Die Granularität von Änderungspuffer und Redo-Log ist unterschiedlich, da die Änderungsdateien nur dann temporär im Änderungspuffer gespeichert werden, wenn sich die Seite, auf der sich die geänderten Daten befinden, nicht im Speicher befindet. Das Redo-Log zeichnet alle Vorgänge auf, die Daten innerhalb einer Transaktion ändern, auch wenn sich die geänderten Daten bereits im Speicher befinden.

Gleichzeitig handelt es sich bei den beiden gepunkteten Pfeilen in der Abbildung um Hintergrundvorgänge, die keinen Einfluss auf die Antwortzeit des Updates haben.

Wie soll also anschließend mit der Lese-Anforderung umgegangen werden?

Beispielsweise möchten wir jetzt select * from t where k in (k1, k2) ausführen.

Wenn die Leseanweisung kurz nach der Aktualisierungsanweisung erfolgt und die Daten im Speicher noch vorhanden sind, haben die beiden Lesevorgänge zu diesem Zeitpunkt nichts mit dem Systemtabellenbereich (ibdata1) und dem Redo-Protokoll (ib_log_fileX) zu tun.

Abbildung 4 Lesevorgang mit Änderungspuffer

Wie aus der Abbildung ersichtlich: Beim Lesen von Seite 1 wird diese direkt aus dem Speicher zurückgegeben.

Wenn wir Daten nach WAL lesen, müssen wir dann die Festplatte lesen? Müssen wir die Daten im Redo-Protokoll aktualisieren, bevor wir zurückkehren können?

Eigentlich ist das nicht nötig. Obwohl die Platte noch die vorherigen Daten enthält, wird das Ergebnis direkt aus dem Speicher zurückgegeben und ist korrekt. Um Seite 2 zu lesen, müssen Sie Seite 2 von der Festplatte in den Speicher lesen und dann das Vorgangsprotokoll im Änderungspuffer anwenden, um eine korrekte Version zu generieren und das Ergebnis zurückzugeben. Wie Sie sehen, wird diese Datenseite erst in den Speicher gelesen, wenn Seite 2 gelesen werden muss.

Wenn wir einfach die Vorteile dieser beiden Mechanismen hinsichtlich der Verbesserung der Aktualisierungsleistung vergleichen, wird deutlich, dass das Redo-Protokoll hauptsächlich den IO-Verbrauch von zufälligen Festplattenschreibvorgängen einspart (indem es diese in sequenzielle Schreibvorgänge umwandelt), während der Änderungspuffer hauptsächlich den IO-Verbrauch von zufälligen Festplattenlesevorgängen einspart.

Fragen zur Überlegung:

1. Wie aus Abbildung 3 ersichtlich, wird der Änderungspuffer zunächst in den Speicher geschrieben. Wenn die Maschine zu diesem Zeitpunkt die Stromversorgung verliert und neu gestartet wird, geht der Änderungspuffer dann verloren? Der Verlust des Änderungspuffers ist keine Kleinigkeit. Beim erneuten Lesen der Daten von der Festplatte findet kein Zusammenführungsprozess statt, was bedeutet, dass Daten verloren gehen. Wird diese Situation eintreten?

Antwort:

1. Der Änderungspuffer befindet sich teilweise im Speicher und teilweise in ibdata.

Der Bereinigungsvorgang sollte die entsprechenden Daten im Änderungspuffer in ibdata speichern.

2. Das Redo-Log zeichnet die Änderungen an der Datenseite und die neuen Informationen auf, die in den Änderungspuffer geschrieben werden

Bei einem Stromausfall werden die persistenten Änderungspufferdaten gelöscht und müssen nicht wiederhergestellt werden. Analysieren Sie hauptsächlich Daten ohne Persistenz

Die Situationen werden in folgende Kategorien eingeteilt:

(1) Wenn der Änderungspuffer geschrieben wird, wird das Redo-Protokoll zwar synchronisiert, aber nicht festgeschrieben, und das Binärprotokoll wird nicht mit der Festplatte synchronisiert, sodass dieser Teil der Daten verloren geht
(2) Der Änderungspuffer wird geschrieben, das Redo-Log wird geschrieben, aber nicht festgeschrieben, binär oder fsynced. Stellen Sie zuerst das Redo-Log aus dem binär wieder her, dann den Änderungspuffer aus dem Redo-Log

(3) Nachdem der Änderungspuffer geschrieben wurde, wurden sowohl das Redo-Log als auch das Binlog fsynced. Die Daten können also direkt aus dem Redo-Log wiederhergestellt werden.

Zusammenfassen

Dies ist das Ende dieses Artikels über MySQL Common Index und Unique Index Selection. Weitere relevante Inhalte zu MySQL Common Index und Unique Index Selection finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!