Eine kurze Diskussion über die Indexseitenstruktur, den Einfügepuffer und den adaptiven Hash-Index von InnoDB

Physische Struktur eines InnoDB-Index

Alle InnoDB-Indizes sind Btree-Indizes, Indexdatensätze werden auf Blättern gespeichert und die Standardindexseitengröße beträgt 16 KB. Wenn ein neuer Datensatz eingefügt wird, versucht InnoDB, 1/16 der Seitengröße für zukünftige Einfüge- und Aktualisierungsvorgänge frei zu lassen.

Wenn die Indexsätze genau in der Reihenfolge der Indexsatzgröße eingefügt werden, dann füllt der Index auch 15/16 der gesamten Seitengröße. Wenn die Einfügereihenfolge völlig zufällig ist, dann füllt sich die Indexseite grundsätzlich zu 1/2 bis 15/16 selbst aufgebaut. Wenn der Füllfaktor kleiner als 1/2 ist, versucht InnoDB, den B-Baum neu aufzubauen.

Ab MySQL 5.6 können Sie den Parameter innodb_page_size verwenden, um die Größe jeder Indexseite in der aktuellen Instanz festzulegen. Einmal festgelegt, kann der Parameter nicht mehr zurückgeändert werden. Die empfohlene Konfiguration ist im Allgemeinen 16K, 8K oder 4K. Wenn eine MySQL-Instanz außerdem auf einen innodb_page_size-Wert von A eingestellt ist, der vom Standardwert abweicht, können Sie keine Dateien auf anderen Instanzen mit einem anderen Wert als A verwenden (beispielsweise, um eine physische Sicherung und Wiederherstellung durchzuführen).

Pufferung einfügen

Datenbankanwendungen fügen Daten normalerweise in der Reihenfolge des Primärschlüssels ein. In diesem Fall wird durch die Einfügeoperation eine Menge zufälliger IOs reduziert, da die Reihenfolge des Clusterindex genau der Reihenfolge der Primärschlüsselwerte entspricht.

Sekundärindizes hingegen sind in der Regel nicht eindeutig, sodass die Daten in relativ zufälliger Reihenfolge in den Sekundärindex eingefügt werden. Ähnlich verhält es sich mit Lösch- und Aktualisierungsvorgängen, die Datenseiten betreffen. Sie beinhalten Änderungen am Index, die im sekundären Index nicht nebeneinander liegen. Dies führt zu zahlreichen zufälligen E/A-Vorgängen.

Beim Einfügen oder Löschen eines Datensatzes aus einem nicht eindeutigen sekundären Index prüft InnoDB zunächst, ob sich die sekundäre Indexseite im Pufferpool befindet. Wenn sie sich im Pufferpool befindet, ändert InnoDB die Indexseite direkt im Speicher. Wenn sich der Index auch nicht im Pufferpool befindet, zeichnet InnoDB die Änderung im Einfügepuffer auf. Der Einfügepuffer ist normalerweise klein, sodass er vollständig im Pufferpool gespeichert und sehr häufig aktualisiert werden kann. Dieser Änderungsprozess wird als Änderungspufferung bezeichnet (normalerweise betrifft er nur Einfügevorgänge, daher wird er auch als Einfügepufferung bezeichnet, und die Datenstruktur ist der Einfügepuffer).

Disk-E/A zum Leeren des Einfügepuffers

Wie also reduziert die Einfügepufferung zufällige IOs? Von Zeit zu Zeit führt der Einfügepuffer die sekundären nicht eindeutigen Indizes im Einfügepuffer zusammen. Normalerweise führt es N Änderungen in die Indexseite desselben B-Baum-Index ein und spart so viele IO-Vorgänge. Nach dem Testen kann Insertbuffer die Einfügegeschwindigkeit um das 15-fache erhöhen.

Nachdem die Transaktion bestätigt wurde, kann es sein, dass der Einfügepuffer weiterhin Schreibvorgänge zusammenführt. Wenn die Datenbank während der Wiederherstellungsphase abnormal neu gestartet wird und eine große Anzahl sekundärer Indizes aktualisiert oder eingefügt werden muss, kann das Laden des Einfügepuffers daher sehr lange dauern, sogar mehrere Stunden. Während dieser Phase steigt die Festplatten-E/A, was zu einer erheblichen Leistungsverschlechterung bei festplattengebundenen Abfragen führt.

Adaptive Hash-Indizes

Durch den Adaptive Hash Index (AHI) ähnelt InnoDB eher einer In-Memory-Datenbank, wenn der Pufferpool über genügend Speicher und bestimmte Arbeitslasten verfügt, ohne dass dabei Transaktionsfunktionen und Stabilität beeinträchtigt werden. Diese Funktion wird durch den Parameter innodb_adaptive_hash_index gesteuert, einen dynamischen Parameter. Der Standardwert ist „on“, was bedeutet, dass der adaptive Hash-Index aktiviert ist. Nach dem Ausschalten von AHI wird die integrierte Hash-Tabelle sofort gelöscht und der normale Betrieb kann weiterhin fortgesetzt werden, allerdings mit direktem Zugriff auf den B-TREE-Index. Die Hash-Tabelle wird nach der erneuten Aktivierung von AHI neu erstellt.

Durch Beobachten des Suchmusters verwendet MySQL das Präfix des Indexschlüssels, um einen Hash-Index zu erstellen. Dieses Präfix kann beliebig lang sein und kann nur einige Werte im B-Baum sein, nicht den gesamten B-Baum. Der Hash-Index wird erkannt und auf den häufig aufgerufenen Indexseiten wird ein Hash-Index erstellt.

Wenn sich eine Tabelle größtenteils im Pufferpool befindet, kann das Erstellen eines Hash-Index Gleichheitsabfragen beschleunigen, indem der B-Baum-Indexwert in einen sortierten Zeiger konvertiert wird. Innodb verfügt über diesen Mechanismus, der den Suchstatus des Index überwachen kann. Wenn es feststellt, dass einige Abfragen durch die Erstellung von Hash-Indizes optimiert werden können, erstellt es diese automatisch und ist daher „adaptiv“.

Bei manchen Workloads überwiegt die durch Hash-Index-Lookups erzielte Leistungssteigerung den zusätzlichen Aufwand für die Überwachung der Indexsuchen und die Pflege der Hash-Tabellenstruktur. Unter Bedingungen hoher Belastung können die dem adaptiven Hash-Index hinzugefügten Lese-/Schreibsperren jedoch manchmal auch zu Konkurrenz führen, wie etwa bei Join-Operationen mit hoher Parallelität. Der Like-Operator und das %-Platzhalterzeichen gelten für AHI ebenfalls nicht. Wenn Ihre Arbeitslast nicht für AHI geeignet ist, wird empfohlen, es auszuschalten, um unnötigen Leistungsmehraufwand zu vermeiden. Da es schwierig ist, vorherzusagen, ob AHI in einer bestimmten Situation innerhalb von MySQL angemessen ist, wird empfohlen, einen Stresstest mit einer tatsächlichen Arbeitslast (mit und ohne AHI) durchzuführen. In 5.6 und späteren Versionen wird berücksichtigt, dass es für immer mehr Workloads am besten ist, die adaptive Hash-Indizierung zu deaktivieren, obwohl sie derzeit standardmäßig aktiviert ist.

Die Erstellung eines Hash-Index basiert häufig auf einem vorhandenen B-Baum. InnoDB kann einen Hash-Index erstellen, indem es die Suchsituation des B-Baums beobachtet und ein B-Baum-Indexpräfix beliebiger Länge festlegt. Ein Hash-Index kann partiell sein und nur die am häufigsten aufgerufenen Seiten des B-Baum-Index enthalten.

Sie können entscheiden, ob adaptive Hash-Indizes verwendet werden sollen, indem Sie den Abschnitt „SEMAPHORES“ in den InnoDB-Statusergebnissen der Show Engine beobachten. Wenn Sie sehen, dass viele Threads auf den in der Datei btr0sea.c erstellten RW-Latch warten, wird empfohlen, den adaptiven Hash-Index zu deaktivieren. Nachfolgend sehen Sie einen Screenshot eines Falls, den ich erlebt habe. Es handelt sich um einen typischen Fall von AHI-Konflikten im Modus mit hoher Parallelität. AHI muss ausgeschaltet werden.

Die obige kurze Erläuterung der Indexseitenstruktur, des Einfügepuffers und des adaptiven Hash-Index von InnoDB ist der gesamte Inhalt, den der Editor mit Ihnen teilt. Ich hoffe, er kann Ihnen als Referenz dienen. Ich hoffe auch, dass Sie 123WORDPRESS.COM unterstützen werden.