Welchen MySQL-Eindeutigen Index oder Normalen Index soll ich wählen?

Stellen Sie sich ein Szenario vor, in dem beim Entwerfen einer Benutzertabelle die ID-Nummer jeder Person eindeutig ist und gesucht werden muss. Da das ID-Nummernfeld jedoch groß ist, ist es nicht zur Verwendung als Primärschlüssel geeignet. Wenn der Geschäftscode garantiert, dass der eingelegte Ausweis eindeutig ist, können Sie einen eindeutigen Index und einen normalen Index erstellen. Wie sollten Sie wählen? Als Nächstes analysieren wir den Abfrage- und Aktualisierungsausführungsprozess.

Abfrageprozess

Angenommen, k ist der Index der Tabelle t. Bei der Suche nach select id from t where k=5 wird an der Wurzel des B+-Baums k begonnen, die Blattknoten werden Schicht für Schicht durchsucht, die Datenseite mit k=5 gefunden und anschließend eine binäre Positionierung für den Inhalt der Datenseite durchgeführt.

Bei einem normalen Index wird nach dem Auffinden des Datensatzes mit k=5 die Suche noch einmal fortgesetzt, bis der erste Datensatz ungleich 5 gefunden wird.

Bei einem eindeutigen Index ist der Wert eindeutig und die Suche wird nach dem Auffinden beendet.

Da InnoDB in Datenseiteneinheiten liest und schreibt (Datenseiten sind standardmäßig 16 KB groß), wird beim Lesen eines Datenelements die gesamte Datenseite als Ganzes in den Speicher gelesen . Wenn auf der in den Speicher gelesenen Datenseite Datensätze mit k = 5 enthalten sind, verfügt der eindeutige Index im Abfragefall über einen Such- und Beurteilungsprozess mehr als der normale Index und kann ignoriert werden.

Wenn k=5 der letzte Eintrag auf der aktuellen Datenseite ist, muss die nächste Datenseite gelesen werden. Die Wahrscheinlichkeit, dass dies passiert, ist jedoch gering und kann ignoriert werden.

Im Allgemeinen gibt es beim Abfragevorgang also keinen großen Unterschied zwischen normalen und eindeutigen Indizes.

Änderungspuffer

Bevor wir die Auswirkungen eindeutiger und gemeinsamer Indizes analysieren, wollen wir zunächst die Änderungspufferstruktur verstehen.

Was ist ein Änderungspuffer?

Wenn beim Ausführen eines Aktualisierungsvorgangs die zu aktualisierende Datenseite im Speicher ist, wird sie direkt aktualisiert. Andernfalls speichert InnoDB den Aktualisierungsvorgang im Änderungspuffer zwischen, ohne die Datenkonsistenz zu beeinträchtigen, wodurch das Lesen der Datenseite von der Festplatte entfällt. Wenn der nächste Abfragevorgang die Datenseite liest, die aktualisiert werden muss, wird die Aktualisierungsanweisung im Änderungspuffer ausgeführt und auf die Datenseite geschrieben. Der Vorgang, bei dem Operationen auf der Festplatte ausgeführt werden, wird als Zusammenführen bezeichnet. Der Hintergrundthread führt regelmäßig Zusammenführungen durch, oder wenn die Datenbank normal geschlossen wird, wird auch eine Zusammenführungsoperation ausgeführt.

Der Ausführungsprozess der Zusammenführung ist wie folgt:

1. Lesen Sie die alte Version der Datenseite von der Festplatte.
2. Suchen Sie im Änderungspuffer nach den mit der Datenseite verknüpften Datensätzen und wenden Sie diese nacheinander an, um die neue Version der Datenseite zu erhalten.
3. Schreiben Sie ein Redo-Protokoll, um Datenänderungen und Änderungen im Änderungspuffer aufzuzeichnen.

Beim Änderungspuffer handelt es sich tatsächlich um Daten, die auf der Festplatte gespeichert werden können. Dies bedeutet, dass der Änderungspuffer sowohl im Speicher als auch auf der Festplatte vorhanden ist. Der Änderungspuffer wurde früher Einfügepuffer genannt. Anfangs wurde nur der Einfügepuffer optimiert, später wurde jedoch die Unterstützung für Löschen und Aktualisieren hinzugefügt und der Name in Änderungspuffer geändert.

Es ist ersichtlich, dass das Aufzeichnen des Aktualisierungsvorgangs im Änderungspuffer zunächst den Prozess des Lesens von Datenseiten auf der Festplatte in den Speicher reduziert und die Ausführungsgeschwindigkeit der Anweisung erheblich verbessert wird. Gleichzeitig wird durch das Lesen von Daten in den Speicher der Pufferpoolspeicher belegt, sodass durch die Reduzierung von Lesevorgängen auch die Speicherauslastung verbessert wird.

Der Pufferpool ist ein Bereich im Speicher, in dem InnoDB Tabellen- und Indexdaten beim Zugriff darauf zwischenspeichert. Ermöglicht die Aktualisierung häufig verwendeter Daten direkt im Speicher und beschleunigt so die Verarbeitung. Auf einigen dedizierten Servern sind 80 % des physischen Speichers in einen Pufferpool aufgeteilt.

Mit innodb_change_buffer_max_size können Sie die Größe des Pufferpools festlegen, der vom Änderungspuffer belegt wird.

Puffer-Anwendungsszenarien ändern?

Wie oben erwähnt, speichert der Änderungspuffer Aktualisierungsdatensätze im Voraus, wodurch der Vorgang des Lesens von Datenseiten verkürzt und somit die Leistung verbessert wird. Mit anderen Worten: Je mehr Aktualisierungsdatensätze für unterschiedliche Datenseiten im Änderungspuffer enthalten sind, desto größer ist der Nutzen.

Daher spielt der Änderungspuffer für Unternehmen mit mehr Schreibvorgängen und weniger Lesevorgängen (sofortige Abfrage nach dem Update) eine größere Rolle . Wie etwa gängige Abrechnungs- und Protokollierungssysteme.

Wenn die Abfrage im Unternehmen unmittelbar nach der Aktualisierung erfolgen soll, wird der Zusammenführungsprozess zwar sofort ausgelöst, da die Datenseite unmittelbar danach abgefragt werden muss, obwohl der Aktualisierungsdatensatz im Änderungspuffer abgelegt werden kann. Dadurch wird die Anzahl der wahlfreien E/A-Vorgänge nicht verringert, sondern der Wartungsaufwand für den Änderungspuffer erhöht, was den gegenteiligen Effekt hat.

Aktualisierungsprozess

Bei eindeutigen Indizes muss bei allen Aktualisierungsvorgängen ermittelt werden, ob sie die Eindeutigkeitsbeschränkung verletzen. Daher müssen die erforderlichen Datenseiten in den Speicher eingelesen und dann direkt aktualisiert werden, ohne den Änderungspuffer zu verwenden. Daher ist der Änderungspuffer nur für normale Indizes nützlich.

Für eine gezielte Analyse fügen Sie einen neuen Datensatz in eine Tabelle ein:

Wenn sich die durch den neuen Datensatz zu aktualisierende Datenseite im Speicher befindet:

Suchen Sie für einen eindeutigen Index die entsprechende Position, ermitteln Sie, ob ein Konflikt vorliegt, fügen Sie den Wert ein und beenden Sie die Anweisung.

Für einen normalen Index: Suchen Sie die Position, fügen Sie den Wert ein und die Anweisung endet.

Wenn sich die Datenseite im Speicher befindet, besteht der einzige Unterschied zwischen einem eindeutigen Index und einem normalen Index daher in einem Beurteilungsprozess. Kann ignoriert werden.

Wenn die durch den neuen Datensatz zu aktualisierende Datenseite nicht im Speicher ist:

Bei einem eindeutigen Index wird die Datenseite in den Speicher gelesen, Konflikte werden ermittelt, die Daten werden eingefügt und die Anweisung wird beendet.

Bei gemeinsamen Indizes wird die Anweisung im Änderungspuffer aufgezeichnet und die Anweisung beendet.

Da es sich um einen wahlfreien E/A-Zugriff von der Festplatte auf den Speicher handelt, handelt es sich hierbei um einen der aufwändigsten Vorgänge in der Datenbank. Gewöhnliche Indizes reduzieren Lesevorgänge im Vergleich zu eindeutigen Indizes, was die Leistung erheblich verbessern kann.

Auswahl zwischen eindeutigem oder normalem Index

Indem wir die beiden hinsichtlich Abfrage und Aktualisierung vergleichen. Wir wissen, dass der Unterschied zwischen beiden während des Abfragevorgangs, außer in äußerst speziellen Fällen, eigentlich nicht so groß ist.

Der wesentliche Unterschied liegt dann vor, wenn bei einer Aktualisierung die zu aktualisierende Datenseite nicht im Inhalt enthalten ist. Zu diesem Zeitpunkt kann der eindeutige Index den Änderungspuffer nicht verwenden, da er eine Eindeutigkeitsprüfung benötigt. Es gibt einen zusätzlichen Prozess zum Lesen von Daten von der Festplatte in den Inhalt, der zufälligen E/A-Zugriff beinhaltet und relativ ineffizient ist.

Wenn das Unternehmen eine gute Leistung aktualisieren muss, können Sie daher einen normalen Index wählen. Natürlich steht alles unter der Prämisse, die Datengenauigkeit sicherzustellen.

Wenn auf ein Update eine Abfrage folgt, können Sie erwägen, den Änderungspuffer auszuschalten. In anderen Fällen kann der Änderungspuffer eine erhebliche Verbesserung bringen.

Insbesondere bei mechanischen Festplatten ist der Änderungspuffereffekt sehr groß.

Vergleich von Redo-Log und Änderungspuffer

Das Aufkommen des Redo-Logs in InnoDB macht es absturzsicher und verbessert die Effizienz, indem zuerst Protokolle geschrieben und dann über WAL auf die Festplatte geschrieben werden.

Der Änderungspuffer speichert den zufälligen IO-Prozess des Lesens von Datenseiten von der Festplatte in den Speicher.

Lassen Sie uns die Beziehung zwischen den beiden anhand einer Insert-Anweisung analysieren:

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

Angenommen, k ist ein normaler Index und die von k1 eingefügte Datenseite befindet sich im Speicher, k2 jedoch nicht.

Bei der Durchführung eines Einfügevorgangs sind hauptsächlich die folgenden vier Teile beteiligt:

InnoDB-Pufferpool: Speicherbereich

Redo-Log: Protokoll

Systemtabellenbereich (ibdata1): Systemtabellenbereich

data(t.idb): Datentabellenbereich

Wenn innodb_file_per_table aktiviert ist, wird die Tabelle in einem separaten Tabellenbereich erstellt, andernfalls wird sie im Systemtabellenbereich erstellt.

Der Ausführungsprozess ist wie folgt:

1. Seite 1, auf der sich k1 befindet, befindet sich im Speicher. Aktualisieren Sie den Speicher direkt
2. Seite2, auf der sich k2 befindet, befindet sich nicht im Speicher, wird aber im Änderungspuffer aufgezeichnet.
3. Notieren Sie die Vorgänge von k1 und k2 im Redo-Protokoll.
4. Führen Sie eine Transaktion durch.
   

Es ist ersichtlich, dass die Ausführungskosten dieser Aktualisierungsanweisung (einschließlich Einfüge-, Lösch- und Aktualisierungsvorgänge) mit zwei Schreibvorgängen in den Speicher und einem sequentiellen Schreibvorgang auf die Festplatte sehr gering sind. Die mit gepunkteten Linien markierten Vorgänge sind Hintergrundvorgänge und haben keinen Einfluss auf die Reaktionszeit.

Schauen wir uns eine andere Abfrageanweisung an:

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

Angenommen, die Lese-Anweisung erfolgt kurz nach der Aktualisierungsanweisung und die Daten im Speicher sind noch vorhanden. Dann hat der Lesevorgang nichts mit dem Systemtabellenbereich und dem Redo-Protokoll zu tun.

Ausführungsprozess:

1. Lesen Sie Seite1, wo sich k1 im Speicher befindet, und kehren Sie direkt zurück. Beachten Sie, dass die Daten auf der Festplatte nicht gelesen werden und dass es sich bei den Daten auf der Festplatte möglicherweise noch um eine frühere Version handelt.
2. Lesen Sie Seite2, auf der sich k2 befindet. Zu diesem Zeitpunkt muss Seite2 von der Festplatte in den Speicher geladen werden, und der Inhalt des Änderungspuffers muss angewendet werden, und dann wird das richtige Ergebnis zurückgegeben. Auch hier ist zu erkennen, dass der Änderungspuffer nicht zum Auslesen unmittelbar nach der Aktualisierung geeignet ist.
   

Um die Beziehung zwischen Redo-Protokoll und Änderungspuffer zusammenzufassen:

Speicherort: Der Änderungspuffer bleibt ebenfalls auf der Festplatte bestehen, wird jedoch im System-Tablespace ibdata1 gespeichert. Das Redo-Log ist eine separate Datei.

Datensatzinhalt: Der Änderungspuffer zeichnet den Inhalt des Aktualisierungsvorgangs auf, während das Redo-Protokoll die Änderungen normaler Datenseiten und die Änderungen im Änderungspuffer aufzeichnet.

Festplattensynchronisierungsprozess: Die Synchronisierung der Änderungen an den Datenseiten im Speicher erfolgt über einen Zusammenführungsvorgang und nicht auf Grundlage des Redo-Protokolls.

Aus Sicht des Aktualisierungsprozesses: Das Redo-Log konvertiert zufällige Festplattenschreib-E/A in sequenzielles Schreiben, während der Änderungspuffer den Verbrauch zufälliger Festplattenlese-E/A einspart.

Geht der Änderungspuffer verloren, wenn der Server unerwartet die Stromversorgung verliert?

Nein, da die Daten im Änderungspuffer im Redo-Log aufgezeichnet wurden und somit nicht verloren gehen.

Weil sich ein Teil der Änderungspufferdaten auf der Festplatte und ein Teil im Speicher befindet. Die Daten auf der Festplatte wurden zusammengeführt, sodass sie nicht verloren gehen.
Für Daten im Speicher:

1. Wenn der Änderungspuffer geschrieben wird, das Redo-Protokoll und das Binärprotokoll jedoch nicht festgeschrieben werden, wird die Transaktion zurückgesetzt und dieser Teil der Daten ist nicht mehr vorhanden.
2. Wenn der Änderungspuffer, das Redo-Protokoll und das Binärprotokoll geschrieben und festgeschrieben werden, gehen sie nicht verloren. Direkte Wiederherstellung aus dem Redo-Log.
3. Wenn der Änderungspuffer geschrieben wird, das Redo-Protokoll geschrieben, aber nicht festgeschrieben wird und das Binärprotokoll geschrieben wird, stellen Sie das Redo-Protokoll aus dem Binärprotokoll wieder her und stellen Sie dann den Änderungspuffer wieder her.

Verweise

Pufferpool

Oben steht die Wahl zwischen einem eindeutigen MySQL-Index und einem normalen Index. Weitere Einzelheiten zum eindeutigen Index und gemeinsamen Index von MySQL finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!