Ausführliche Erläuterung des MySQL-Gemeinschaftsindex und des eindeutigen Index

Szenario

1. Pflegen Sie ein Bürgersystem mit einem Feld für die ID-Nummer

2. Der Geschäftscode kann sicherstellen, dass keine zwei doppelten ID-Nummern geschrieben werden (wenn das Unternehmen dies nicht garantieren kann, können Sie sich auf den eindeutigen Index der Datenbank verlassen, um dies einzuschränken).

3. Allgemeine SQL-Abfrageanweisungen: SELECT name FROM CUser WHERE id_card = 'XXX'

4. Erstellen Sie einen Index

• Die ID-Nummer ist relativ groß, daher wird nicht empfohlen, sie als Primärschlüssel festzulegen
• Sollten Sie aus Leistungsgründen einen normalen oder einen eindeutigen Index wählen?

Angenommen, die Werte im Feld k werden nicht wiederholt

Abfrageprozess

1. Abfrageanweisung: SELECT id FROM T WHERE k=5

2. Abfragevorgang

• Beginnen Sie an der Wurzel des B+-Baums und suchen Sie schichtweise bis zum Blattknoten. Dabei handelt es sich um die Datenseite in der unteren rechten Ecke der obigen Abbildung.
• Verwenden Sie die binäre Suche, um bestimmte Datensätze auf der Datenseite zu finden

3. Für gemeinsame Indizes

• Suchen Sie den ersten Datensatz, der die Bedingung (5.500) erfüllt, und suchen Sie dann den nächsten Datensatz, bis der erste Datensatz gefunden ist, der k=5 nicht erfüllt.

4. Für einen eindeutigen Index

• Da die Eindeutigkeit durch den Index definiert wird, wird die Suche beendet, nachdem der erste Datensatz gefunden wurde, der die Bedingungen erfüllt.

Leistungsunterschiede

1. Leistungsunterschied: sehr gering

2. InnoDB-Daten werden in Datenseiteneinheiten gelesen und geschrieben, der Standardwert beträgt 16 KB

3. Wenn ein Datensatz gelesen werden muss, wird der Datensatz selbst nicht von der Festplatte gelesen, sondern in Einheiten von Datenseiten.

4. Wenn der Datensatz mit k = 5 gefunden wird, befindet sich die Datenseite, auf der er sich befindet, bereits im Speicher

5. Für normale Indizes sind nur eine weitere Zeigersuche und eine weitere Berechnung erforderlich – der CPU-Verbrauch ist sehr gering

• Wenn der Datensatz k=5 zufällig der letzte Datensatz der Datenseite ist, müssen Sie die nächste Datenseite lesen, wenn Sie den nächsten Datensatz erhalten möchten.
• Sehr geringe Wahrscheinlichkeit: Für einen ganzzahligen Feldindex kann eine Datenseite (16 KB, kompaktes Format) ungefähr 745 Werte speichern.

Änderungspuffer

1. Wenn eine Datenseite aktualisiert werden muss und sich die Datenseite im Speicher befindet, wird sie direkt aktualisiert

2. Wenn sich die Datenseite nicht im Speicher befindet, ohne die Datenkonsistenz zu beeinträchtigen

• InnoDB speichert diese Update-Operationen im Änderungspuffer
• Es ist nicht notwendig, diese Datenseite von der Festplatte zu lesen (random read)
• Wenn eine Abfrage das nächste Mal auf diese Datenseite zugreifen muss, wird die Datenseite in den Speicher eingelesen und die mit dieser Datenseite im Änderungspuffer verbundenen Operationen werden ausgeführt (Zusammenführen).

3. Änderungspuffer sind persistente Daten, von denen eine Kopie im Speicher vorhanden ist und die auch auf die Festplatte geschrieben werden

4. Notieren Sie den Aktualisierungsvorgang zuerst im Änderungspuffer, um zufällige Festplattenlesevorgänge zu reduzieren und die Ausführungsgeschwindigkeit der Anweisung zu verbessern

5. Darüber hinaus erfordert das Lesen von Datenseiten in den Speicher die Belegung des Pufferpools. Durch die Verwendung des Changenge-Puffers kann die Belegung des Speichers vermieden und die Speicherauslastung verbessert werden

6. Der Änderungspuffer verwendet den Speicher im Pufferpool und kann nicht unbegrenzt vergrößert werden. Der Steuerparameter innodb_change_buffer_max_size

# Standard ist 25, Maximum ist 50
mysql> VARIABLEN WIE '%innodb_change_buffer_max_size%' ANZEIGEN;
+-------------------------------+----------+
| Variablenname | Wert |
+-------------------------------+----------+
| innodb_change_buffer_max_size | 25 |
+-------------------------------+----------+

verschmelzen

1. Zusammenführen: Wenden Sie die Operationen im Änderungspuffer auf die ursprüngliche Datenseite an

2. Der Ausführungsprozess der Zusammenführung

• Datenseiten von der Festplatte in den Speicher lesen (Datenseiten der alten Version)
• Suche den Änderungspufferdatensatz für diese Datenseite aus dem Änderungspuffer (ggf. mehrere)
  Führen Sie sie dann nacheinander aus, um die neue Version der Datenseite zu erhalten
• Redolog schreiben, darunter: Tabellenaktualisierung der Datenseite + Änderungspufferänderung
  

3. Nachdem die Zusammenführung ausgeführt wurde, wurden die Datenseite im Speicher und die dem Änderungspuffer entsprechende Festplattenseite nicht geändert und sind fehlerhafte Seiten.

• Über andere Mechanismen werden schmutzige Seiten auf die entsprechenden physischen Festplattenseiten übertragen.

4. Zeitpunkt des Auslösens

• Besuchen Sie diese Datenseite
• Der Systemhintergrund-Thread wird regelmäßig zusammengeführt
• Die Datenbank wird normal heruntergefahren

Nutzungsbedingungen

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

2. Die Aktualisierung des eindeutigen Index kann den Änderungspuffer nicht verwenden, nur der allgemeine Index kann den Änderungspuffer verwenden

• Der Primärschlüssel kann den Änderungspuffer ebenfalls nicht verwenden.
• Um beispielsweise (4.400) einzufügen, müssen Sie zunächst feststellen, ob in der Tabelle ein Datensatz mit k=4 vorhanden ist. Voraussetzung für diese Feststellung ist, dass die Datenseite in den Speicher gelesen wird.
• Da die Datenseite in den Speicher eingelesen wurde, reicht es aus, die Datenseite im Speicher direkt zu aktualisieren, ohne den Änderungspuffer zu schreiben.

Anwendungsszenarien

1. Je mehr Änderungen eine Datenseite vor dem Zusammenführen im Änderungspuffer aufgezeichnet hat, desto größer ist der Nutzen.

2. Bei Unternehmen mit mehr Schreib- als Lesevorgängen ist die Wahrscheinlichkeit, dass auf eine Seite unmittelbar nach dem Schreiben zugegriffen wird, äußerst gering. Daher ist die Verwendung eines Änderungspuffers zu diesem Zeitpunkt am effektivsten.

• Beispielsweise Abrechnungs- und Protokollierungssysteme

3. Wenn der Aktualisierungsmodus eines Unternehmens lautet: Die Abfrage wird sofort nach dem Schreiben ausgeführt

• Obwohl der Aktualisierungsvorgang im Änderungspuffer aufgezeichnet wird, wird die Datenseite unmittelbar nach der Abfrage von der Festplatte gelesen, wodurch der Zusammenführungsprozess ausgelöst wird.
• Anstatt die Anzahl der zufälligen Lesevorgänge zu verringern, erhöht sich dadurch der Aufwand für die Pflege des Änderungspuffers.

Aktualisierungsprozess

Einfügen(4,400)

Die Zielseite befindet sich im Speicher

• Suchen Sie für den eindeutigen Index die Position zwischen 3 und 5, stellen Sie fest, ob kein Konflikt vorliegt, und fügen Sie diesen Wert ein
• Für einen normalen Index suchen Sie die Position zwischen 3 und 5 und geben diesen Wert ein
• Leistungsunterschied: Minimal

Die Zielseite befindet sich nicht im Speicher

1. Für einen eindeutigen Index müssen Sie die Datenseite in den Speicher lesen, feststellen, ob ein Konflikt vorliegt, und den Wert einfügen

• Zufällige Festplattenlesevorgänge sind sehr teuer

Zeichnen Sie bei allgemeinen Indizes einfach den Aktualisierungsvorgang im Änderungspuffer auf.

• Reduzierte zufällige Festplattenlesevorgänge, deutlich verbesserte Leistung

Indexauswahl

1. Es gibt keinen großen Unterschied in der Abfrageleistung zwischen einem gemeinsamen Index und einem eindeutigen Index. Die wichtigste Überlegung ist die Aktualisierungsleistung. Es wird empfohlen, einen gemeinsamen Index zu wählen.

2. Szenarien, in denen es empfohlen wird, den Änderungspuffer zu schließen

• Wenn auf alle Aktualisierungen Abfragen für diesen Datensatz folgen
• Steuerparameter innodb_change_buffering

mysql> VARIABLEN WIE '%innodb_change_buffering%' ANZEIGEN;
+-------------------------+----------+
| Variablenname | Wert |
+-------------------------+----------+
| innodb_change_buffering | alle |
+-------------------------+----------+

# Gültige Werte (>= 5.5.4)
keine / Einfügungen / Löschungen / Änderungen / Löschvorgänge / alle

# Gültige Werte (<= 5.5.3)
keine / Einfügungen

# Der Vorgänger des Änderungspuffers ist der Einfügepuffer, der nur Einfügevorgänge optimieren kann

Änderungspuffer + Redolog

Aktualisierungsprozess

Aktueller Status des K-Baums: Nach dem Auffinden des entsprechenden Speicherorts befindet sich die Datenseite Seite 1, auf der sich k1 befindet, im Speicher, und die Datenseite Seite 2, auf der sich k2 befindet, befindet sich nicht im Speicher

EINFÜGEN IN t(id,k) WERTE (id1,k1),(id2,k2); 

# Speicher: Pufferpool
# redolog:ib_logfileX
# Datentabellenbereich: t.ibd
# Systemtabellenbereich: ibdata1

1. Seite 1 ist im Speicher, aktualisiere den Speicher direkt

2. Seite 2 ist nicht im Speicher, Datensatz im Wechslerpuffer: addiere (id2,k2) zu Seite 2

3. Die beiden oben genannten Aktionen sind im Redolog enthalten (sequentielles Schreiben auf die Festplatte).

4. Die Transaktion ist nun abgeschlossen und die Kosten für die Ausführung der Update-Anweisung sind sehr gering

• Zweimal in den Speicher schreiben + einmal auf die Festplatte

5. Wenn eine Transaktion festgeschrieben wird, werden die Datensätze für die Änderungspufferoperation auch im Redolog aufgezeichnet

• Daher kann der Änderungspuffer während der Wiederherstellung nach einem Absturz wiederhergestellt werden.

Die gepunktete Linie ist der Hintergrundvorgang und hat keinen Einfluss auf die Reaktionszeit des Aktualisierungsvorgangs

Lesevorgang

Annahme: Die Lese-Anweisung erfolgt kurz nach der Aktualisierungsanweisung, die Daten im Speicher sind noch vorhanden und sie haben nichts mit dem Systemtabellenbereich (ibdata1) und dem Redolog (ib_logfileX) zu tun.

Wählen Sie * aus t, wobei k in (k1, k2); 

1. Seite 1 lesen und direkt aus dem Speicher zurückgeben (zu diesem Zeitpunkt ist Seite 1 möglicherweise noch eine schmutzige Seite und wurde noch nicht auf die Festplatte geschrieben)

2. Seite 2 lesen, die Datenseite durch zufälliges Lesen der Festplatte in den Speicher lesen und dann das Betriebsprotokoll im Änderungspuffer anwenden (Zusammenführen).

• Korrekte Version erstellen und zurücksenden

Verbessern der Update-Leistung

1. Redolog: spart zufälligen E/A-Verbrauch beim Schreiben auf die Festplatte (sequentielles Schreiben)

2. Puffer ändern: spart IO-Verbrauch bei zufälligen Festplattenlesevorgängen

Verweise

„MySQL Praxis 45 Vorlesungen“

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.