Detaillierte Erklärung des Unterschieds zwischen MySQL-Normalindex und eindeutigem Index

1 Begriffliche Abgrenzung

• Normaler Index und eindeutiger Index
  

Gewöhnliche Indizes können wiederholt werden, eindeutige Indizes können jedoch, wie beispielsweise Primärschlüssel, nicht wiederholt werden. Ein eindeutiger Index kann als Mittel zur Validierung von Daten verwendet werden. Wenn wir beispielsweise im Feld „Ausweisnummer“ einer Studententabelle künstlich festlegen, dass dieses Feld nicht wiederholt werden kann, wird ein eindeutiger Index verwendet. (Normalerweise wird das Feld „Studenten-ID“ als Primärschlüssel festgelegt.)

• Primärschlüssel und eindeutiger Index
  

Der Primärschlüssel stellt sicher, dass jede Zeile in der Datenbank eindeutig ist, wie z. B. Personalausweis, Studentenausweis usw., die in der Tabelle eindeutig und nicht doppelt vorhanden sein müssen. Die Rolle eines eindeutigen Indexes ist die gleiche wie die eines Primärschlüssels. Der Unterschied besteht darin, dass es in einer Tabelle nur einen Primärschlüssel geben kann und dieser nicht leer sein darf. Es kann mehrere eindeutige Indizes geben und ein eindeutiger Index kann einen leeren Datensatz haben, was bedeutet, dass er sich lediglich von den anderen unterscheiden muss. Beispielsweise verwendet die Schule in der Schülertabelle normalerweise die Schülerausweisnummer als Primärschlüssel und den Personalausweis als eindeutigen Index. Beim Bildungsamt hingegen ist die Personalausweisnummer der Primärschlüssel und die Schülerausweisnummer der eindeutige Index. Die Wahl des Primärschlüssels der Tabelle hängt von der tatsächlichen Anwendung ab und der Primärschlüssel darf nicht leer sein.

2 Fallvorstellung

In einem bestimmten Einwohnersystem hat jede Person eine eindeutige ID-Nummer. Wenn das System einen Namen anhand der ID-Nummer nachschlagen muss, führt es SQL ähnlich dem folgenden aus:

Wählen Sie den Namen von CUser, wobei id_card = „ooxx“ ist.

Dann erstellen Sie auf jeden Fall einen Index für das ID-Card-Feld. Das ID-Card-Feld ist jedoch groß und es wird nicht empfohlen, es als Primärschlüssel zu verwenden. Es gibt jetzt also zwei Möglichkeiten:

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

Vorausgesetzt, dass im Geschäftscode sichergestellt ist, dass keine doppelten ID-Nummern geschrieben werden, sind beide Optionen logisch korrekt. Aber aus Leistungssicht: eindeutiger Index oder normaler Index?

Betrachten wir folgenden Fall: Angenommen, die Werte auf Feld k wiederholen sich nicht.

• InnoDB-Indexorganisationsstruktur:

Als nächstes analysieren wir die Leistung.

3 Abfrageleistung

wähle die ID aus T, wobei k=4

Durch das Durchlaufen des B+-Baums der Reihe nach von der Wurzel bis zum Blattknoten kann davon ausgegangen werden, dass die Datenseite durch eine binäre Suche durchsucht wird.

• Normaler Index: Nachdem Sie den ersten Datensatz gefunden haben, der die Bedingung (4.400) erfüllt, müssen Sie nach dem nächsten Datensatz suchen, bis Sie auf den ersten Datensatz stoßen, der k=4 nicht erfüllt.
• Eindeutiger Index. Da der Index eindeutig ist, wird die Suche beendet, nachdem der erste Datensatz gefunden wurde, der die Bedingungen erfüllt.
  

Es scheint, als wäre die Leistungslücke sehr gering.

InnoDB-Daten werden in Datenseiteneinheiten gelesen und geschrieben. Das heißt, beim Lesen eines Datensatzes wird dieser nicht von der Platte gelesen, sondern als Ganzes seitenweise in den Speicher eingelesen.

Daher ist für normale Indizes eine weitere Operation zum „Suchen und Bestimmen des nächsten Datensatzes“ erforderlich, nämlich eine Zeigersuche und eine Berechnung. Wenn der Datensatz k=4 der letzte Datensatz der Datenseite ist, müssen Sie die nächste Datenseite lesen, um den nächsten Datensatz zu erhalten, was eine etwas komplizierte Operation ist. Bei ganzzahligen Feldern kann eine Datenseite fast tausend Schlüssel speichern, daher ist die Wahrscheinlichkeit dieser Situation tatsächlich sehr gering. Daher können die Kosten dieses Vorgangs bei der Berechnung des durchschnittlichen Leistungsunterschieds im Vergleich zum aktuellen CPU-Overhead als vernachlässigbar angesehen werden.

Wir wissen, dass MySQL über einen Änderungspuffer verfügt.

4 Leistung aktualisieren

Fügen wir nun einen neuen Datensatz (4.400) in die Tabelle ein. Was macht InnoDB?

Dabei muss unterschieden werden, ob sich die durch den Datensatz zu aktualisierende Zielseite im Speicher befindet:

4.1 Im Speicher

• Eindeutiger Index
  

Suchen Sie die Position zwischen 3 und 5, stellen Sie sicher, dass kein Konflikt vorliegt, fügen Sie den Wert ein und die Anweisungsausführung wird beendet.

• Normaler Index
  

Suchen Sie die Position zwischen 3 und 5, fügen Sie den Wert ein und die Anweisungsausführung wird beendet.

Der Unterschied zwischen der Auswirkung eines normalen Indexes und eines eindeutigen Indexes auf die Leistung einer Aktualisierungsanweisung ist lediglich eine Ermessensentscheidung, die nur eine geringe Menge an CPU-Zeit in Anspruch nimmt.

4.2 Nicht im Speicher

• Eindeutiger Index
  

Die Datenseite muss in den Speicher gelesen werden. Wenn festgestellt wird, dass kein Konflikt vorliegt, wird der Wert eingefügt und die Anweisungsausführung beendet.

• Normaler Index
  

Die Aktualisierung wird im Änderungspuffer aufgezeichnet und die Anweisungsausführung wird beendet.

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. Der Änderungspuffer reduziert zufällige Festplattenzugriffe, sodass die Aktualisierungsleistung erheblich verbessert wird.

5 Indexauswahl in der Praxis

Wie wählt man zwischen einem normalen Index und einem eindeutigen Index? Hinsichtlich der Abfrageleistung gibt es zwischen diesen beiden Indextypen keinen Unterschied. Die wichtigste Überlegung besteht in den Auswirkungen 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 anderen Fällen kann der Änderungspuffer die Aktualisierungsleistung verbessern. Die Kombination aus normalen Indizes und Änderungspuffern ist sehr effektiv bei der Optimierung von Aktualisierungen von Tabellen mit großen Datenmengen.

Beim Einsatz mechanischer Festplatten ist der Change-Buffer-Mechanismus sehr effektiv. Wenn Sie über eine Bibliothek wie „Historische Daten“ verfügen und aus Kostengründen mechanische Festplatten verwenden, sollten Sie daher auf die Indizes in diesen Tabellen achten, versuchen, normale Indizes zu verwenden, den Änderungspuffer zu erhöhen und die Datenschreibgeschwindigkeit der Tabelle „Historische Daten“ sicherzustellen.

6 Änderungspuffer und Redo-Log

Der Kernmechanismus von WAL zur Leistungssteigerung besteht in der Minimierung von zufälligen Lese- und Schreibvorgängen. Diese beiden Konzepte können leicht verwechselt werden. Um den Unterschied zu verdeutlichen, habe ich sie hier demselben Prozess unterzogen.

6.1 Einfügevorgang

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

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 k2 befindet sich nicht im Speicher.

• Update-Flussdiagramm mit Änderungspuffer. Die beiden Pfeile in der Abbildung sind Hintergrundoperationen und haben keinen Einfluss auf die Update-Antwort.

Dieses Update bewirkt Folgendes:

1. Seite1 ist im Speicher, Speicher direkt aktualisieren
2. Seite2 befindet sich nicht im Speicher, sondern im Änderungspuffer und speichert die Informationen zum „Einfügen einer Datensatzzeile in Seite2“ zwischen.
3. Notieren Sie die ersten beiden Aktionen im Redo-Log
   

Danach ist die Transaktion abgeschlossen. Der Aufwand für die Ausführung dieser Aktualisierungsanweisung ist sehr gering, da lediglich zwei Speicher und dann eine Festplatte geschrieben werden (die ersten beiden Vorgänge zusammen schreiben eine Festplatte) und die Anweisungen sequenziell geschrieben werden.

6.2 Wie werden nachfolgende Leseanforderungen behandelt?

wähle * aus t, wobei k in (k1, k2);

Auf die Aktualisierungsanweisung folgt die Leseanweisung, und die Daten im Speicher sind noch vorhanden. Zu diesem Zeitpunkt haben diese beiden Lesevorgänge nichts mit dem Systemtabellenbereich und dem Redo-Protokoll zu tun. Deshalb habe ich diese beiden nicht in das Bild gezeichnet.

• Lesevorgang mit Änderungspufferung

Beim Lesen von Seite1 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 nicht nötig. Betrachtet man den Status in der obigen Abbildung, so ist auf der Festplatte zwar immer noch die vorherigen Daten vorhanden, das Ergebnis wird jedoch direkt aus dem Speicher zurückgegeben und ist korrekt.

Um Seite2 zu lesen, müssen Sie Seite2 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. Es ist ersichtlich, dass die Datenseite erst dann in den Speicher gelesen wird, wenn Seite2 gelesen werden muss.

Daher müssen wir einfach die Auswirkungen dieser beiden Mechanismen auf die Aktualisierungsleistung vergleichen.

• Redo-Log spart hauptsächlich den IO-Verbrauch beim zufälligen Schreiben auf die Festplatte (Konvertierung in sequentielles Schreiben).
• Der Änderungspuffer spart hauptsächlich den IO-Verbrauch beim zufälligen Lesen der Festplatte.
  

7 Fazit

Da eindeutige Indizes den Änderungspufferoptimierungsmechanismus nicht verwenden können, wird aus Leistungsgründen empfohlen, nicht eindeutigen Indizes Vorrang zu geben, sofern dies für das Unternehmen akzeptabel ist.

7.1 Ob ein eindeutiger Index verwendet werden soll

Die Hauptsorge bestehe darin, dass „das Geschäft möglicherweise nicht sichergestellt ist“. In diesem Artikel werden Leistungsprobleme unter der Prämisse erörtert, dass „garantiert wurde, dass der Geschäftscode keine doppelten Daten schreibt“.

Wenn das Unternehmen dies nicht garantieren kann oder erfordert, dass die Datenbank Einschränkungen vornimmt, bleibt nichts anderes übrig, als einen eindeutigen Index zu erstellen. In diesem Fall besteht die Bedeutung dieses Artikels darin, eine zusätzliche Idee zur Fehlerbehebung bereitzustellen, wenn Sie auf eine Situation stoßen, in der eine große Datenmenge langsam eingefügt wird und die Speichertrefferrate niedrig ist.
Dann können Sie in einigen „Archivbibliothek“-Szenarien die Verwendung eines eindeutigen Indexes in Betracht ziehen. Beispielsweise müssen Online-Daten nur ein halbes Jahr lang aufbewahrt werden, und historische Daten werden in einer Archivbibliothek gespeichert. Zu diesem Zeitpunkt ist garantiert, dass die archivierten Daten keine eindeutigen Schlüsselkonflikte aufweisen. Um die Archivierungseffizienz zu verbessern, können Sie den eindeutigen Index der Tabelle in einen gemeinsamen Index ändern.

7.2 Wenn ein Änderungspuffer für einen Schreibvorgang verwendet wird und der Host anschließend abnormal neu gestartet wird, gehen die Änderungspufferdaten dann verloren?

Geht nicht verloren. Obwohl nur der Speicher aktualisiert wird, zeichnen wir auch die Änderungspuffervorgänge im Redo-Protokoll auf, wenn die Transaktion festgeschrieben wird, sodass der Änderungspuffer auch während der Wiederherstellung nach einem Absturz abgerufen werden kann.

7.3 Werden die Daten beim Zusammenführen direkt wieder auf die Festplatte geschrieben?

Zusammenführungsausführungsprozess

1. Datenseiten von der Festplatte in den Speicher lesen (Datenseiten der alten Version)
2. Suchen Sie im Änderungspuffer nach den Änderungspufferdatensätzen der Datenseite (es können mehrere vorhanden sein), wenden Sie sie nacheinander an und erhalten Sie die neue Version der Datenseite
3. Redo-Log schreiben
   

Das Redo-Log enthält Datenänderungen und Änderungen am Änderungspuffer.

Der Zusammenführungsprozess endet hier. Zu diesem Zeitpunkt wurden die Datenseite und der entsprechende Datenträgerspeicherort des Änderungspuffers im Speicher nicht geändert und sind schmutzige Seiten. Danach löschen sie jeweils ihre eigenen physischen Daten zurück, was ein weiterer Prozess ist.

Fragen zum Nachdenken

Beim Erstellen des ersten Beispiels wird Sitzung B in Zusammenarbeit mit Sitzung A aufgefordert, die Daten zu löschen und anschließend erneut einzufügen. Anschließend wird festgestellt, dass sich im Erläuterungsergebnis das Zeilenfeld von 10001 auf über 37000 ändert. Wenn Sitzung A nicht verwendet wird und nur „Löschen“ aus t, „idata()“ aufrufen und „Erläutern“ separat ausgeführt werden, sehen Sie, dass das Zeilenfeld immer noch etwa 10.000 beträgt. Was ist der Grund dafür?

Wenn es nicht wieder auftritt, überprüfen Sie

• Ist die Isolationsebene RR (Repeatable Read)?
• Handelt es sich bei der erstellten Tabelle um eine InnoDB-Engine?
  

Warum ist das Explain-Ergebnis nach dieser Abfolge von Operationen falsch? Die Löschanweisung löschte alle Daten und fügte dann durch den Aufruf von idata() 100.000 Datenzeilen ein, wodurch die ursprünglichen 100.000 Zeilen scheinbar überschrieben wurden. Allerdings hat Sitzung A eine Transaktion gestartet, diese aber nicht festgeschrieben. Daher können die zuvor eingefügten 100.000 Datenzeilen nicht gelöscht werden. Auf diese Weise hat jede Zeile vorheriger Daten zwei Versionen: Die alte Version sind die Daten vor dem Löschen und die neue Version sind die als gelöscht markierten Daten. Auf diese Weise gibt es tatsächlich zwei Kopien der Daten im Index a.

Dann sagen Sie vielleicht, das stimmt nicht, die Daten im Primärschlüssel können nicht gelöscht werden. Warum liegt die Anzahl der vom EXPLAIN-Befehl angezeigten gescannten Zeilen dann immer noch bei etwa 100.000, wenn keine Force-Index-Anweisung vorhanden ist? (Die Folge ist, dass, wenn dieser Wert ebenfalls verdoppelt wird, der Optimierer möglicherweise denkt, dass es angemessener ist, Feld a als Index auszuwählen.) Ja, aber dies ist der Primärschlüssel, und der Primärschlüssel wird direkt anhand der Anzahl der Zeilen in der Tabelle geschätzt. Was die Anzahl der Zeilen in einer Tabelle betrifft, verwendet der Optimierer direkt den Wert von show table status . Wenn Ihr Computer über unzureichende E/A-Funktionen verfügt, können Sie bei dieser Überprüfung innodb_flush_log_at_trx_commit und sync_binlog auf 0 setzen.

Oben finden Sie eine ausführliche Erklärung zum Unterschied zwischen dem gemeinsamen MySQL-Index und dem eindeutigen MySQL-Index. Weitere Informationen zum gemeinsamen MySQL-Index und zum eindeutigen MySQL-Index finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!