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:

• Wenn kein Primärschlüssel vorhanden ist, wird eine eindeutige Indexspalte ungleich Null als Primärschlüssel verwendet und zum Clustered-Index dieser Tabelle werden;
• Wenn kein solcher Index vorhanden ist, definiert InnoDB implizit einen Primärschlüssel als Clustered-Index.

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.

B+-Baum (mehrfach ausgeglichener Suchbaum)

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.

Hilfsindex

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.

Gemeinsamer Index

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

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.

Hinweise

• Erstellen Sie nur geeignete Indizes, keine redundanten.

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.

• Die Datenlänge der Indexspalte kann so klein wie möglich sein.

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.

• Das passende Spaltenpräfix kann im Index wie 9999% verwendet werden, wie %9999%, wie %9999 kann der Index nicht verwendet werden
• In und oder im Wobei Bedingung den Index nicht verwenden kann und -Operationen keine Indizes verwenden können

Wenn es sich nicht in oder gegenüber dem B+-Baum befindet, weiß die Engine nicht, welcher Knoten, von dem aus gestartet werden soll.

• Übereinstimmung mit Bereichswerten, Sortieren nach kann auch zum Indexieren verwendet werden
• Verwenden Sie Abfragen nach bestimmten Spalten häufiger, geben Sie nur die Datenspalten zurück, die Ihnen einfallen, verwenden Sie „select“; weniger;

Es besteht keine Notwendigkeit, nutzlose Felder abzufragen, und wenn Sie nicht * verwenden, können Sie auf den abdeckenden Index klicken

• Wenn Sie Beginnen Sie nicht mit der Suche nach der Spalte ganz links im gemeinsamen Index. Sie können den Index nicht verwenden

Im Gelenk Index, Index kann verwendet werden, um die Spalte ganz links und den Bereich mit einer anderen Spalte genau abzugleichen.

Wenn in der Abfrage im gemeinsamen Index eine Bereichsabfrage vorhanden ist, ist dies für alle Spalten rechts davon nicht möglich indiziert werden

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!