Index in MySQL

Vorwort

Kommen wir gleich zur Sache. Die folgende Mindmap ist das, worüber ich jetzt sprechen werde. Du kannst dir zunächst einen Eindruck verschaffen.

• Gemeinsame Indextypen (Implementierungsebene)
• Indextyp (Anwendungsebene)
• Gruppierte und nicht gruppierte Indizes
• Abdeckungsindex
• Beste Indexnutzungsstrategie

1. Gemeinsame Indextypen (Implementierungsebene)

Lassen Sie uns zunächst nicht darüber sprechen, wie MySQL die Indizierung implementiert. Lassen Sie uns später darüber sprechen. Wenn wir aufgefordert werden, den Index der Datenbank zu entwerfen, wie sollten wir ihn entwerfen?

Überlegen wir zunächst, welche Wirkung der Index erzielen soll. Tatsächlich möchten wir nur eine Strategie zum schnellen Auffinden von Daten implementieren, daher ist die Implementierung des Index im Wesentlichen ein Suchalgorithmus .

Der Unterschied zur normalen Suche besteht jedoch darin, dass unsere Daten die folgenden Merkmale aufweisen:

1. Die gespeicherte Datenmenge ist sehr, sehr groß

2. Und es verändert sich ständig dynamisch

Daher müssen diese beiden Merkmale bei der Implementierung des Index berücksichtigt werden. Wir müssen den am besten geeigneten Datenstrukturalgorithmus finden, um die Suchfunktion zu implementieren.

Werfen wir einen Blick auf die gängigen Suchstrategien, wie unten dargestellt:

Aufgrund der beiden oben genannten Eigenschaften schließen wir zunächst statische Suchalgorithmen aus.

Für den Suchbaum haben wir zwei Möglichkeiten: Binärbaum und Mehrfachbaum:

Binärbaum: Wenn wir einen Binärbaum wählen, wird die Tiefe des Binärbaums aufgrund der riesigen Datenmenge sehr groß, unser Indexbaum wird zu einem riesigen Baum und jede Abfrage verursacht eine Menge Festplatten-E/A.

Mehrzweigiger Baum: Ein mehrzweigiger Baum löst das Problem einer großen Baumtiefe. Sollten wir also einen B-Baum oder einen B+-Baum wählen?

B-Baum von Wikipediazh.wikipedia.org/wiki/B%2Btree

B+-Baum von Wikipediazh.wikipedia.org/wiki/B%2Btree

Aus der obigen Abbildung können wir ersehen, dass die Blattknoten des B+-Baums alle Indexwerte speichern und die Blattknoten in Form einer verknüpften Liste miteinander verbunden sind, sodass wir nur von der am weitesten links stehenden verknüpften Liste aus durchlaufen müssen, um alle Werte zu finden. Die häufigste Verwendung ist die Bereichssuche, aber der B-Baum erfüllt diese Bereichssuche nicht oder die Implementierung ist besonders komplex, sodass MySQL sich schließlich für die Verwendung des B+-Baums zur Implementierung dieser Funktion entschieden hat.

1.1 B-Baum-Index (B+-Baum)

Zunächst einmal: Obwohl es in MySQL offiziell B-Tree-Index heißt, verwendet es die B+-Baum-Datenstruktur.

Der B-Tree-Index kann den Datenzugriff beschleunigen. Er muss nicht die gesamte Tabelle durchsuchen. Stattdessen durchsucht er den Indexbaum Schicht für Schicht vom Stammknoten aus nach unten. Der Stammknoten speichert den Indexwert und den Zeiger zum nächsten Knoten.

Sehen wir uns an, wie die Daten eines einspaltigen Index organisiert sind.

Tabelle erstellen Benutzer(
`name` varchar(50) nicht null,
`uid` int(4) ungleich null,
`gender` int(2) ungleich null,
 Schlüssel(`uid`)
);

Die obige Benutzertabelle erstellt einen Index für die UID-Spalte. Wie verwaltet die Speicher-Engine den Index, wenn die UID (96-102) in die Tabelle eingefügt wird? Schauen Sie sich den Indexbaum unten an

1. Speichern Sie alle Indexwerte in Blattknoten. Nicht-Blattknotenwerte werden verwendet, um Blattknoten mit Zielwerten schneller zu finden.

2. Die Werte der Blattknoten sind geordnet

3. Blattknoten werden in Form verknüpfter Listen verknüpft

Als Nächstes schauen wir uns an, wie die Daten eines mehrspaltigen (gemeinsamen) Indexes organisiert sind.

Tabelle erstellen Benutzer(
`name` varchar(50) nicht null,
`uid` int(4) ungleich null,
`gender` int(2) ungleich null,
 Schlüssel(`uid`,`name`)
);

Für die Tabelle „Benutzer“ wird ein gemeinsamer Indexschlüssel (UID, Name) erstellt. In diesem Fall sieht der Indexbaum wie in der folgenden Abbildung aus.

Die Eigenschaften sind die gleichen wie bei einspaltigen Indizes, der Unterschied liegt in der Sortierung. Wenn das erste Feld gleich ist, wird nach dem zweiten Indexfeld sortiert.

Wie findet man mithilfe eines B-Baums schnell Daten?

Für den B-Baum-Index der InnoDb-Speicher-Engine werden die folgenden Schritte ausgeführt, um die Zeilendaten über den Index zu finden:

• Wenn ein gruppierter Index (Primärschlüssel) verwendet wird, enthält der Blattknoten Zeilendaten und kann direkt zurückgegeben werden.
• Wenn ein nicht gruppierter Index (normaler Index) verwendet wird, wird der Primärschlüssel im Blattknoten gespeichert und der darüber liegende gruppierte Index wird basierend auf dem Primärschlüssel abgefragt und die Daten werden schließlich zurückgegeben.

Für den B-Baum-Index der MyISAM-Speicher-Engine werden die folgenden Schritte ausgeführt, um die Zeilendaten über den Index zu finden:

• In den Blattknoten des MyISAM-Indexbaums werden außer dem Indexwert weder der Primärschlüssel noch die Zeilendaten gespeichert. Stattdessen wird ein Zeiger auf die Zeilendaten gespeichert, und basierend auf diesem Zeiger werden Daten aus der Tabellendatei abgefragt.

1.2 Hash-Index (Hash-Tabelle)

Der Hash-Index wird basierend auf der Hash-Tabelle implementiert und wird nur wirksam, wenn alle Spalten exakt übereinstimmen.

Das heißt, wenn ein Hash-Indexschlüssel (col1,col2) vorhanden ist, kann er nur wirksam werden, wenn jedes Mal sowohl col1 als auch col2 verwendet werden. Denn wenn ein Hash-Index generiert wird, wird dies dadurch implementiert, dass der Hash-Wert aller Indexspalten auf Basis einer Hash-Funktion ermittelt wird.

Wie in der folgenden Abbildung gezeigt, gibt es einen Hash-Indexschlüssel (Name).

Wenn wir mysql> select * from User where name='張三'； wie können wir den Hash-Index für eine schnelle Suche verwenden?

1. Der erste Schritt besteht darin, den Hashwert zu berechnen, hash(Zhang San) = 1287
2. Der zweite Schritt besteht darin, die Zeilennummer zu lokalisieren. Beispielsweise entspricht key=1287 der Zeilennummer 3.
3. Der dritte Schritt besteht darin, die angegebene Zeile zu finden und den Wert der Namensspalte zu vergleichen, um festzustellen, ob es sich um Zhang San handelt.

2. Gemeinsame Indextypen (Anwendungsebene)

Primärschlüsselindex

Tabelle erstellen Benutzer(
`name` varchar(50) nicht null,
`uid` int(4) ungleich null,
`gender` int(2) ungleich null,
 Primärschlüssel („uid“)
);

Der Primärschlüsselindex ist eindeutig und wird normalerweise mit der Tabellen-ID festgelegt. Eine Tabelle kann nur einen Primärschlüsselindex haben, was den Unterschied zum eindeutigen Index darstellt.

Eindeutiger Index

Tabelle erstellen Benutzer(
`name` varchar(50) nicht null,
`uid` int(4) ungleich null,
`gender` int(2) ungleich null,
 eindeutiger Schlüssel („Name“)
);

Der eindeutige Index wird hauptsächlich für eindeutige Einschränkungen in Unternehmen verwendet. Der Unterschied zwischen ihm und dem Primärschlüsselindex besteht darin, dass eine Tabelle mehrere eindeutige Indizes haben kann.

Einspaltiger Index

Tabelle erstellen Benutzer(
`name` varchar(50) nicht null,
`uid` int(4) ungleich null,
`gender` int(2) ungleich null,
 Schlüssel(`Name`)
);

Indizieren eines Felds

Gemeinsamer Index

Tabelle erstellen Benutzer(
`name` varchar(50) nicht null,
`uid` int(4) ungleich null,
`gender` int(2) ungleich null,
 Schlüssel(`Name`,`UID`)
);

Zwei oder mehr Felder werden zu einem Index zusammengefasst. Bei der Verwendung müssen Sie auf das Prinzip der Übereinstimmung ganz links achten!

Es gibt noch andere, weniger gebräuchliche, die ich hier nicht vorstellen werde~

3. Clustered-Index und Nicht-Clustered-Index

Was ist ein gruppierter Index?

In einem gruppierten Index werden Index- und Zeilendaten zusammen gespeichert. Das heißt, dass auf dem Blattknoten eines B+-Baums nicht nur sein Indexwert gespeichert ist, sondern auch die Daten einer entsprechenden Zeile. Sie werden es wissen, wenn Sie sich das Bild später ansehen.

Ein gruppierter Index ist kein Index, sondern eine Möglichkeit, die Datenspeicherung zu organisieren! ! !

Tabellentest erstellen(
 col1 int nicht null,
 col2 int nicht null,
 PRIMÄRSCHLÜSSEL(Spalte1),
 SCHLÜSSEL(Spalte2)
);

Wie oben gezeigt, verfügt die Tabelle test über zwei Indizes, nämlich den Primärschlüssel col1 und den gemeinsamen Index col2. Welche Beziehung besteht also zwischen diesen beiden Indizes und gruppiert und nicht gruppiert?

Es wird ein gruppierter Index und ein nicht gruppierter Index (Sekundärindex) generiert, d. h. es werden zwei Indexbäume organisiert. Der Primärschlüsselindex generiert einen Baum für den gruppierten Index und einen Baum für den nicht gruppierten Index mit col2 als Index.

InnoDb implementiert den Clusterindex über den Primärschlüssel. Wenn kein Primärschlüssel vorhanden ist, wird zur Implementierung ein eindeutiger, nicht leerer Index ausgewählt. Wenn kein eindeutiger nicht leerer Index vorhanden ist, wird implizit ein Primärschlüssel generiert.

Schauen wir uns an, wie Clustered- und Nicht-Clustered-Indizes Daten im Indexbaum verteilen. Das Bild stammt aus „High Performance Nysql“

Die folgende Abbildung zeigt, wie die Daten eines gruppierten Indexes organisiert sind. Col1 ist der gruppierte Indexbaum des Primärschlüsselindex

Die Indexspalte ist der Primärschlüssel col1

Es ist ersichtlich, dass der Blattknoten neben der Speicherung des Indexwerts der Spalte col1 (3 ~ 99 ~ 4700) auch die Werte anderer Spalten speichert, beispielsweise der Spalte col2 (92 ~ 8 ~ 13). Wenn andere Spalten vorhanden sind, werden diese ebenfalls gespeichert. Mit anderen Worten speichert der gruppierte Indexbaum eine Datenzeile, die einem bestimmten Indexwert auf dem Blattknoten entspricht.

Die folgende Abbildung zeigt die Datenorganisation eines nicht gruppierten Indexes (sekundärer Index).

Die Indexspalte ist col2

Im Gegensatz zu gruppierten Indizes speichern nicht gruppierte Indizes neben Indexwerten nur Primärschlüsselwerte auf den Blattknoten des Baums. Der gruppierte Index speichert eine Datenzeile.

Wenn eine SQL-Anweisung vorhanden ist select * from test where col2=93;

Die obige Anweisung durchläuft den Indexbaum-Suchvorgang zweimal

1. Der erste Schritt besteht darin, den Blattknoten mit col2 = 93 aus dem Indexbaum des nicht gruppierten Index zu finden und den Primärschlüssel 3 der Zeile zu lokalisieren

2. Der zweite Schritt besteht darin, den Blattknoten mit dem Primärschlüssel = 3 im gruppierten Index basierend auf dem Primärschlüssel 3 zu lokalisieren und alle Zeilendaten zurückzugeben.

Das Obige basiert auf der InnoDb-Speicher-Engine. MyISAM unterstützt keine Cluster-Indizes, da seine Datendateien und Indexdateien unabhängig voneinander gespeichert werden. Die Blattknoten des Indexbaums der MyISAM-Speicher-Engine speichern nicht den Primärschlüsselwert, sondern eine Adresse oder einen Zeiger auf die entsprechende Zeile und suchen dann in der Tabellendatendatei, wie in der folgenden Abbildung dargestellt.

abschließend:

• Gruppierter Index:

Normalerweise wird der Blattknoten durch einen Primärschlüssel oder einen nicht leeren eindeutigen Index implementiert. Er speichert eine ganze Datenzeile.

• Nicht gruppierter Index:

Wird auch als Sekundärindex bezeichnet. Dies ist der allgemeine Index, den wir häufig verwenden. Der Blattknoten speichert den Indexwert und den Primärschlüsselwert.

4. Abdeckungsindex

Ein überdeckender Index bedeutet, dass der Index alle Felder enthält, die abgefragt werden müssen.

Tabelle erstellen Benutzer(
`name` varchar(50) nicht null,
`uid` int(4) ungleich null,
`gender` int(2) ungleich null,
 Schlüssel(`uid`,`name`)
);

Wenn die Tabelle „User“ drei Felder „User“ (Name, UID, Geschlecht) und einen gemeinsamen Indexschlüssel (Name, UID) hat, dann

Der überdeckende Index wird beim Ausführen der folgenden SQL-Abfrage verwendet.

Wählen Sie Name, UID vom Benutzer, wobei Name in ('a', 'b') und UID >= 98 und UID <= 100 ist;

Die obige SQL-Anweisung verwendet den gemeinsamen Indexschlüssel (Name, UID) und muss nur die Felder „Name“ und „UID“ durchsuchen, sodass ein abdeckender Index verwendet wird. Welche Vorteile bietet die Index-Abdeckung? Schauen Sie sich zunächst das folgende Bild an

Die obige Abbildung zeigt den Indexbaum, der dem gemeinsamen Indexschlüssel (Name, UID) entspricht. Aus der Abbildung können wir ersehen, dass wir die Daten, die wir abfragen müssen, aus dem Indexbaum abrufen können, wenn wir nur die beiden Felder (Name, UID) abfragen müssen. Es ist nicht erforderlich, den Indexwert zu finden und dann die entsprechenden Zeilendaten aus der Tabellendatendatei zu lokalisieren.

Vorteile der Indexabdeckung

1. Vermeiden Sie sekundäre Abfragen des Primärschlüsselindex (Clustering).

2. Da keine Abfrage der Tabelle (aus der Tabellendatendatei) erforderlich ist, wird die Auslastung des MySQL-Cache erheblich verbessert

Kurz gesagt, die Leistung beim Lesen von Daten wird erheblich verbessert

5. Beste Indexnutzungsstrategie

Lassen Sie uns abschließend über die Anleitung zur Vermeidung von Fallstricken bei der Verwendung des Index sprechen.

Unabhängige Spalten

Unabhängige Spalten beziehen sich nicht auf einspaltige Indizes, sondern darauf, dass die Indexspalte nicht Teil eines Ausdrucks oder einer Funktion sein kann.

select * FROM test where col1 + 1 = 100; // kann nicht Teil eines Ausdrucks sein select * FROM test where ABS(col1) = 100; // kann nicht Teil einer Funktion sein

Prinzip der Übereinstimmung ganz links

Angenommen, es gibt einen gemeinsamen Indexschlüssel (col1,col2). Dann ist die folgende Abfrage ein ungültiger Index

Wählen Sie * aus dem Test, wobei Spalte2 = 3 ist;
Wählen Sie * aus Test, wobei Spalte1 wie „%3“ ist;

Was das Prinzip der ganz linken Übereinstimmung betrifft: Wenn Sie über die Verbindung zwischen den Blattknoten des B+-Baums nachdenken, werden Sie fast verstehen, warum das Prinzip der ganz linken Übereinstimmung erforderlich ist, da die Blattknoten von B+ in Form einer verknüpften Liste von links nach rechts verbunden sind. Wenn wir den Index abfragen, müssen wir entweder eine Bereichsabfrage verwenden oder einen eindeutigen Startindexwert auf der linken Seite haben. Wir können keine mehrdeutigen Abfragen wie „%XYZ“ überspringen oder verwenden.

Der Indexwert darf nicht null sein.

Ein einspaltiger Index mit einem Nullwert macht den Index ungültig

Ein mehrspaltiger Index ist ungültig, wenn eine Spalte einen Nullwert hat

Verwenden Sie Clustered-Indizes und Covering-Indizes, um die Leseleistung deutlich zu verbessern

Da die erforderlichen Felder bereits im Indexbaum des gruppierten Index und des überlagernden Index verfügbar sind, ist es nicht erforderlich, für die Abfrage zur Tabellendatei zurückzukehren, wodurch die Abfragegeschwindigkeit verbessert wird.

Verwenden kurzer Indizes

Wenn Sie nach einer langen Zeichenfolge suchen, müssen Sie nur eine Präfixlänge abgleichen, was viel Indexplatz sparen kann.

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. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.