Heim >Datenbank >MySQL-Tutorial >Detaillierte Einführung in das MySQL-Innodb-Indexprinzip (Codebeispiel)
Dieser Artikel bietet Ihnen eine detaillierte Einführung (Codebeispiel) zum MySQL-Indizierungsprinzip. Ich hoffe, dass er für Freunde hilfreich ist.
Clustered Index
Die Innodb-Speicher-Engine-Tabelle ist eine indexorganisierte Tabelle, und die Daten in der Tabelle werden in der Primärschlüsselreihenfolge gespeichert. Der Clustered-Index erstellt einen B+-Baum in der Reihenfolge der Primärschlüssel jeder Tabelle, und seine Blattknoten speichern die Zeilendatensatzdaten der gesamten Tabelle, und diese Blattknoten werden zu Datenseiten. (Verwandte Empfehlungen: MySQL-Tutorial)
Die Speicherung des Clustered-Index ist nicht physikalisch kontinuierlich, sondern logisch kontinuierlich. Die Blattknoten werden in der Reihenfolge des Primärschlüssels sortiert und durch a verbunden Zweifach verknüpfte Liste. In den meisten Fällen verwendet der Abfrageoptimierer tendenziell Clustered-Indizes, da Clustered-Indizes Daten direkt an Blattknoten finden können und da die logische Reihenfolge der Daten definiert ist, kann sehr schnell auf Abfragen nach Bereichswerten zugegriffen werden.
Diese Funktion des Clustered-Index bestimmt, dass die Daten in der indexorganisierten Tabelle auch Teil des Index sind. Da die Daten in der Tabelle nur nach einem B+-Baum sortiert werden können, kann eine Tabelle nur einen Clustered-Index haben.
In Innodb ist der Clustered-Index standardmäßig der Primärschlüsselindex. Wenn kein Primärschlüssel vorhanden ist, wird ein Clustered-Index gemäß den folgenden Regeln erstellt:
Da der Primärschlüssel einen Clustered-Index verwendet und der Primärschlüssel eine automatisch inkrementierende ID ist, werden die entsprechenden Daten nebeneinander auf der Festplatte gespeichert und die Schreibleistung ist hoch. Wenn es sich um eine Zeichenfolge wie uuid handelt, führt häufiges Einfügen dazu, dass innodb häufig Festplattenblöcke verschiebt und die Schreibleistung relativ gering ist.
Wir wissen, dass der Innodb-Engine-Index die B+-Baumstruktur verwendet. Warum also nicht andere Arten von Baumstrukturen, wie z. B. Binärbäume?
Wenn ein Computer Daten speichert, verfügt er über eine Mindestspeichereinheit, genau wie die Mindesteinheit des RMB-Umlaufs Cent ist. Die kleinste Einheit des Dateisystems ist ein Block. Die Größe eines Blocks variiert je nach System und kann auch über eine eigene Mindestspeichereinheit (Seite) festgelegt werden Die Größe einer Seite beträgt 16 KB (dieser Wert ist ebenfalls konfigurierbar).
Die Größe einer Datei im Dateisystem beträgt nur 1 Byte, sie muss jedoch 4 KB Speicherplatz auf der Festplatte belegen. Ebenso ist die Größe aller Datendateien in innodb immer ein ganzzahliges Vielfaches von 16384 (16 KB).
In MySQL belegt also ein Blockknoten, der den Index speichert, 16 KB, und jeder E/A-Vorgang von MySQL nutzt die Lesefähigkeit des Systems, um jeweils 16 KB zu laden. Auf diese Weise ist es sehr verschwenderisch, nur einen Indexwert in diesen Knoten einzufügen, da jeweils nur ein Indexwert IO abgerufen werden kann und daher kein Binärbaum verwendet werden kann.
B+-Baum ist ein Mehrweg-Suchbaum. Ein Knoten kann n Werte enthalten, n = 16 KB / die Größe jedes Indexwerts.
Wenn die Indexfeldgröße beispielsweise 1 KB beträgt, kann jeder Knoten theoretisch 16 Indexwerte speichern. In diesem Fall kann der Binärbaum nur einen Indexwert pro IO laden, während der B+-Baum 16 laden kann.
Die Anzahl der Wege des B+-Baums beträgt n+1, wobei n die Anzahl der Werte ist, die in jedem Knoten vorhanden sind. Wenn beispielsweise jeder Knoten 16 Werte speichert, hat dieser Baum 17 Wege.
Hier ist auch ersichtlich, dass B+-Baumknoten mehrere Werte speichern können, sodass der B+-Baumindex eine bestimmte Zeile mit einem bestimmten Schlüsselwert nicht finden kann. Der B+-Baum kann nur die spezifische Seite finden, auf der die Datenzeile gespeichert ist, dann die Seite in den Speicher einlesen und dann im Speicher nach den angegebenen Daten suchen.
Anhang: Der Unterschied zwischen B-Baum und B+-Baum besteht darin, dass die Nicht-Blattknoten des B+-Baums nur Navigationsinformationen und keine tatsächlichen Werte enthalten. Alle Blattknoten und verbundenen Knoten sind über verknüpfte Listen miteinander verbunden erleichtern das Finden und Durchqueren von Intervallen.
wird auch als nicht gruppierter Index bezeichnet. Seine Blattknoten enthalten nicht alle in der Zeile aufgezeichneten Daten. Zusätzlich zum Schlüsselwert enthält der Blattknoten den Index Die Zeile in jedem Blattknoten enthält außerdem ein Lesezeichen, das den Clustered-Index-Schlüssel für die entsprechende Zeile darstellt.
Die folgende Abbildung zeigt die Beziehung zwischen dem Hilfsindex und dem Clustered-Index (das Bild stammt aus dem Internet, schauen Sie sich nur die allgemeine Bedeutung an):
Bei Verwendung des Hilfsindex Bei der Suche nach Daten erhält die Innodb-Speicher-Engine den Primärschlüssel, der nur den Primärschlüsselindex benötigt, über den Hilfsindex-Blattknoten und findet dann den vollständigen Zeilendatensatz über den Primärschlüsselindex.
Wenn Sie beispielsweise Daten in einem Hilfsindexbaum mit einer Höhe von 3 suchen möchten, müssen Sie 3 E/A für den Hilfsindexbaum ausführen, um den angegebenen Primärschlüssel zu finden Ist der Indexbaum ebenfalls 3, müssen Sie den Clustered-Indexbaum dreimal durchsuchen, um schließlich eine Seite zu finden, auf der sich die vollständigen Zeilendaten befinden. Daher sind insgesamt sechs E/A-Zugriffe erforderlich, um die endgültige Datenseite zu erhalten. Von
erstellte Indizes, wie gemeinsame Indizes, eindeutige Indizes usw., sind nicht gruppierte Indizes.
Der gemeinsame Index bezieht sich auf die Indizierung mehrerer Spalten in der Tabelle. Der gemeinsame Index ist ebenfalls ein B+-Baum. Der Unterschied besteht darin, dass die Anzahl der Schlüsselwerte im gemeinsamen Index nicht 1, sondern größer oder gleich 2 ist.
Zum Beispiel gibt es eine Benutzertabelle mit den Feldern ID, Alter und Name. Es wurde festgestellt, dass die folgenden zwei SQLs am häufigsten verwendet werden:
Select * from user where age = ? ; Select * from user where age = ? and name = ?;
Derzeit gibt es keine Sie müssen nur den folgenden gemeinsamen Index erstellen:
create index idx_age_name on user(age, name)
Ein weiterer Vorteil des gemeinsamen Index besteht darin, dass der zweite Schlüsselwert sortiert wurde, wodurch manchmal ein zusätzlicher Index vermieden werden kann Sortiervorgang.
Abdeckender Index, dh Sie können alle für die Abfrage erforderlichen Feldwerte aus dem Hilfsindex abrufen, ohne die Datensätze im Clustered-Index abzufragen. Der Vorteil des Deckungsindex besteht darin, dass der Hilfsindex nicht alle Informationen des gesamten Zeilendatensatzes enthält und daher viel kleiner als der Clustered-Index ist, sodass eine große Anzahl von E/A-Vorgängen reduziert werden kann.
Wenn oben beispielsweise ein gemeinsamer Index (Alter, Name) vorhanden ist und dieser wie folgt lautet:
select age,name from user where age=?
, können Sie den Deckungsindex verwenden.
Ein weiterer Vorteil der Abdeckung von Indizes besteht bei statistischen Problemen, wie zum Beispiel:
select count(*) from user
Die innodb-Speicher-Engine fragt den Clustered-Index nicht nach Statistiken ab. Da in der Benutzertabelle ein Hilfsindex vorhanden ist und dieser viel kleiner ist als der Clustered-Index, kann die Auswahl des Hilfsindex die E/A-Vorgänge reduzieren.
Denn jedes Mal, wenn Daten hinzugefügt oder gelöscht werden, muss der B+-Baum angepasst werden Wenn mehrere Indizes erstellt werden, müssen mehrere B+-Bäume angepasst werden. Je mehr Bäume und je größer die Struktur, desto zeitaufwändiger und ressourcenintensiver ist diese Anpassung. Wenn diese unnötigen Indizes reduziert werden, kann die Festplattennutzung erheblich reduziert werden.
Je kleiner die Indexdatenlänge ist, desto mehr Indizes werden in jedem Block gespeichert und desto mehr Werte können in einem IO abgerufen werden.
Wenn es sich nicht in oder gegenüber dem B+-Baum befindet, weiß die Engine nicht, welcher Knoten, von dem aus gestartet werden soll.
Es besteht keine Notwendigkeit, nutzlose Felder abzufragen, und wenn Sie nicht * verwenden, können Sie auf den abdeckenden Index klicken
Im Gelenk Index, Index kann verwendet werden, um die Spalte ganz links und den Bereich mit einer anderen Spalte genau abzugleichen.
Das obige ist der detaillierte Inhalt vonDetaillierte Einführung in das MySQL-Innodb-Indexprinzip (Codebeispiel). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!