Leitfaden zur effizienten Nutzung von MySQL-Indizes

Vorwort

Ich glaube, die meisten Leute haben MySQL und Indizes verwendet, aber wissen Sie, wie man geeignete Indizes erstellt? Bei geringen Datenmengen wirken sich unangemessene Indizes nicht stark auf die Leistung aus. Bei einer allmählichen Zunahme der Datenmenge nimmt die Leistung jedoch stark ab.

Dieser Artikel ist eine Zusammenfassung der MySQL-Indizes. Wenn Fehler vorhanden sind, kommentieren Sie diese bitte und weisen Sie darauf hin.

Index-Grundlagen

Wir alle kennen die Schritte zum Nachschlagen eines Wortes in einem Wörterbuch. Suchen Sie zuerst die Seitenzahl des Wortes auf der Indexseite und gehen Sie dann zur entsprechenden Seitenzahl, um die Informationen zum Wort zu überprüfen. Die MySQL-Indizierungsmethode ist ähnlich: Suchen Sie zuerst den entsprechenden Wert im Index und dann die entsprechende Datenzeile basierend auf dem übereinstimmenden Indexdatensatz. Wenn die folgende SQL-Anweisung vorhanden ist:

Wählen Sie * vom Studenten, wobei Code = "2333" ist.

Wenn für die Codespalte ein Index erstellt wird, verwendet MySQL den Index, um die Zeile mit dem Wert „2333“ zu finden, liest dann alle Daten in der Zeile und gibt sie zurück.

Indextyp

B-Baum-Index

(entweder B-Baum oder B-Baum), die überwiegende Mehrheit der Indextypen sind B-Baum (oder B-Baum-Varianten), und wir verwenden normalerweise diesen Indextyp. Die MyISAM-Speicher-Engine in MySQL verwendet B-Tree und InnoDB verwendet B+Tree. Sie können bei Baidu nach dem Unterschied zwischen B-Tree und B+Tree suchen.

Der baumstrukturierte Index kann den Datenzugriff beschleunigen. Die Speicher-Engine muss nicht mehr die gesamte Tabelle durchsuchen, um die erforderlichen Daten zu erhalten. Stattdessen führt sie eine binäre Suche vom Stammknoten des Baums aus durch. Wie wir alle wissen, ist die Geschwindigkeit der binären Suche recht hoch, sodass wir den Index verwenden können, um die Abfragegeschwindigkeit erheblich zu verbessern. B-Tree unterstützt die folgenden Abfragetypen:

Angenommen, es gibt nur einen mehrspaltigen Index für die Tabelle „Student“: Name, Alter, Gewicht. Die folgenden Abfragen können alle diesen Index verwenden:

• Vollständige Werteübereinstimmung

Und alle Spalten in der Indexspalte stimmen überein. Um beispielsweise Name='abc' und Alter=12 abzufragen, werden hier die erste und die zweite Spalte verwendet.

• Übereinstimmung mit der Spalte ganz links

Es wird nur der Anfangsteil des Indexes verwendet. Beispielsweise verwendet die Abfrage für name='ggg' nur die erste Spalte des Indexes, und die Abfrage für name='ggg' und age=12 verwendet die erste und zweite Spalte des Indexes.

• Passender Spaltenpräfix

Sie können auch nur den Anfangsteil einer Spalte abgleichen, zum Beispiel den Abfragenamen „g%“, um Datensätze abzufragen, deren Name mit „g“ beginnt. Die erste Spalte wird hier verwendet

• Passender Bereichswert

Kann verwendet werden, um Bereichswerte abzugleichen, z. B. die Abfrage „Name > ‚abc‘“ und „Name < ‚bcd‘“.

• Genaue Übereinstimmung mit einer Spalte und Bereichsübereinstimmung mit einer anderen Spalte

Wird zum Abgleichen mehrerer Spalten verwendet, z. B. bei der Abfrage „name='abc'“ und „age > 12“.

Im Allgemeinen können wir feststellen, dass der B-Tree-Index für die Suche basierend auf dem äußersten linken Präfix geeignet ist, d. h. die Reihenfolge der Abfragefelder muss mit der Reihenfolge der Indexfelder übereinstimmen und mit dem ersten Indexfeld beginnen. Beispielsweise können über den Index die Angaben „Name“, „Name und Alter“, „Name und Alter und Gewicht“ abgefragt werden, nicht jedoch „Alter“, „Alter und Name“.

Hash-Index

Der Hash-Index wird basierend auf der Hash-Tabelle implementiert und wird nur wirksam, wenn alle Spalten des Index exakt übereinstimmen. In MySQL unterstützt nur die Speicher-Engine explizit Hash-Indizes, was auch ihr Standardindex ist.

InnoDB kann keine Hash-Indizes erstellen, verfügt jedoch über eine Funktion namens adaptiver Hash-Index. Wenn bestimmte Indexwerte sehr häufig verwendet werden, erstellt die Engine basierend auf dem B-Tree-Index einen weiteren Hash-Index im Speicher, sodass der B-Tree-Index auch einige Vorteile des Hash-Index bietet. Bei dieser Funktion handelt es sich um ein vollständig automatisches, internes Verhalten, d. h. sie kann nicht manuell gesteuert oder konfiguriert werden.

Leistungsstarke Indizierungsstrategie

Nachfolgend sind einige gängige Indizierungsstrategien aufgeführt.

Unabhängige Spalten

Das ist ganz einfach. Wenn die Spalten in der Abfrage nicht unabhängig sind, kann der Index nicht verwendet werden, zum Beispiel:

Wählen Sie * vom Schüler, wobei Alter+1=12

Auch wenn die Spalte „Alter“ einen Index hat, kann die obige Abfrageanweisung den Index nicht verwenden.

Präfixindizes und Indexselektivität

Wenn Sie eine sehr lange Zeichenfolgenspalte indizieren müssen, führt das direkte Erstellen eines Indexes dazu, dass der Index mehr Speicherplatz beansprucht und langsamer ist. Eine Optimierungsstrategie besteht darin, einen Hash-Index zu simulieren: Berechnen Sie einen Hash-Wert für die Spalte und erstellen Sie einen Index für die Hash-Wert-Spalte.

Eine andere Möglichkeit besteht darin, einen Präfixindex zu erstellen. Es werden nur die Zeichen am Anfang dieses Felds indexiert. Dadurch kann der belegte Speicherplatz erheblich reduziert werden und die Indexerstellung wird wesentlich schneller. Dies hat aber auch folgende Nachteile:

• Die Indexselektivität wird reduziert. Wenn mehrere Zeichenfolgen das gleiche Präfix haben, können sie nicht unterschieden werden und es ist ein Zeichenfolgenvergleich erforderlich.
• Order by und Group by werden nicht unterstützt. Der Grund liegt auf der Hand. Es werden nur einige Zeichen indexiert und können nicht vollständig unterschieden werden.

Der Schlüssel liegt hier darin, zu bestimmen, wie viele Zeichen angemessen indiziert werden sollen. Dabei gilt es, eine übermäßige Länge zu vermeiden und auf eine ausreichende Indexselektivität zu achten. Zum Ermitteln der Anzahl der Indexzeichen gibt es zwei Möglichkeiten:

Die Indexfeldpräfixdaten sind gleichmäßig verteilt. Das heißt, die Anzahl der Zeichenfolgen, die mit dem Indexzeichen beginnen, ist gleichmäßig verteilt. Wenn wir beispielsweise die ersten 3 Zeichen des Namensfelds indizieren, ist das folgende Ergebnis sinnvoll (nur die ersten 8 werden verwendet):

Wenn die Daten in jeder Spalte relativ groß sind, bedeutet dies, dass die Unterscheidungsfähigkeit nicht hoch genug ist und die Anzahl der Indexzeichen erhöht werden muss, bis die Selektivität des Präfixes nahe an der Indexierbarkeit der gesamten Spalte liegt, d. h. die vorhergehenden Daten sollten so klein wie möglich sein.

Berechnen Sie die Selektivität der gesamten Spalte und bringen Sie die Selektivität des Präfixes in die Nähe der Selektivität der gesamten Spalte. Die folgende Anweisung berechnet die vollständige Spaltenselektivität:

-- Die Anzahl der unterschiedlichen Zeichenfolgen/Gesamtzahl ist die komplette Spalte „Selective select count(distinct name)/count(*) from person“.

Die folgende Anweisung berechnet die Selektivität der ersten drei Felder des Index:

-- String-Daten mit unterschiedlichen ersten 3 Zeichen/Gesamtdaten select count(distincy left(city,3))/count(*) from person

Erhöhen Sie die Anzahl der Indexzeichen weiter, bis die Selektivität die Vollspaltenselektivität erreicht. Eine weitere Erhöhung der Anzahl der Indexzeichen verbessert die Selektivität der Daten nicht wesentlich.

Erstellungsmethode

-- Nehmen wir an, die optimale Länge ist 4
Tabelle ändern, Person hinzufügen, Schlüssel (Name (4));

Mehrspaltige Indizes

Viele Leute haben folgendes Missverständnis: Wenn eine Abfrage mehrere Felder und eine „und“-Abfrage verwendet, wäre es dann nicht möglich, die Effizienz durch die Indizierung jedes einzelnen Felds zu maximieren? Dies ist nicht der Fall. MySQL wählt nur eines der Felder für die Indexsuche aus. In diesem Fall sollten Sie einen mehrspaltigen Index (auch gemeinsamer Index genannt) erstellen, damit Sie mehrere Indexfelder verwenden können. Beachten Sie, dass die Reihenfolge der Indexspalten mit der Abfragereihenfolge übereinstimmen muss.

Die Strategie „Index Merge“ wurde in den Versionen 5.0 und höher eingeführt. Teilweise können auch mehrere einspaltige Indizes verwendet werden, wie etwa die folgende Abfrage:

-- MySQL verwendet die Namens- und Altersindizes, um die Daten zu finden und sie dann zusammenzuführen. -- Wenn Sie „und“ verwenden, werden die Daten gefunden, verglichen und die Schnittmenge ermittelt. select * from person where name = "bob" or age=12

Dies wird jedoch nicht empfohlen. Zu viele „and“ oder „or“-Bedingungen verbrauchen bei den Caching-, Sortier- und Zusammenführungsvorgängen des Algorithmus viel CPU und Speicher.

Wählen Sie die entsprechende Indexspaltenreihenfolge

In einem mehrspaltigen B-Tree-Index bedeutet die Reihenfolge der Indexspalten, dass der Index zuerst nach der äußersten linken Spalte und dann nach der zweiten Spalte sortiert wird. Beim Indizieren eines guten mehrspaltigen Index sollte der selektivste Index zuerst und dann nach unten gesetzt werden, damit die Indizierung besser erleichtert wird. Selektive Erkennung von Berechnungsmethoden: Abschnitt mit Präfixindex.

Gruppierter Index

Ein Clustered-Index ist kein separater Indextyp, sondern eine Methode zur Datenspeicherung. Die spezifischen Details hängen von der Implementierung ab.

Der Clusterindex von InnoDB speichert Indexwerte und Datenzeilen tatsächlich in derselben Struktur. Da Sie eine Datenzeile nicht gleichzeitig an zwei verschiedenen Stellen platzieren können, kann eine Tabelle nur einen gruppierten Index haben. Die gruppierten Indexspalten von InnoDB werden „Primärschlüsselspalten“ genannt.

Wenn kein Primärschlüssel definiert ist, wählt InnoDB stattdessen einen eindeutigen, nicht leeren Index. Wenn kein solcher Index vorhanden ist, definiert InnoDB implizit einen Primärschlüssel, der als gruppierter Index dient.

Der Hauptvorteil von Clusterindizes besteht darin, dass sie zusammengehörige Daten zusammen speichern, die Festplatten-E/A reduzieren und die Abfrageeffizienz verbessern können. Es gibt aber auch Nachteile:

• Die Einfügereihenfolge ist stark von der Einfügereihenfolge abhängig. Das Einfügen in der Reihenfolge der Primärschlüssel ist der schnellste Weg. Andernfalls kann es zu Problemen beim Aufteilen der Seiten kommen, mehr Speicherplatz beanspruchen und die Scangeschwindigkeit verlangsamen. Die Tabelle kann mit OPTIMIZE TABLE neu organisiert werden.
• Das Aktualisieren von gruppierten Indexspalten ist aufwändig, da bei einer Änderung des Indexwerts die Zeilendaten zusammen mit dem Index an die neue Position verschoben werden.
• Der Zugriff auf Zeilendaten mithilfe eines sekundären Index (nicht gruppierter Index) erfordert zwei Indexsuchen, da die Blattknoten des sekundären Index nicht den physischen Speicherort der Zeilendaten speichern, sondern den Primärschlüsselwert der Zeile, und dann die Zeilendaten mithilfe des Primärschlüsselwerts aus dem gruppierten Index abgerufen werden.

Abdeckungsindex

Einfach ausgedrückt deckt ein Index die abzufragenden Spaltenfelder ab, so dass für eine sekundäre Suche im gruppierten Index keine Verwendung des Primärschlüssels erforderlich ist und die erforderlichen Daten in einem sekundären Index abgerufen werden können.

InnoDB-Indizes speichern Indexwerte in Blattknoten. Wenn also alle abzufragenden Felder in einem Index enthalten sind und dieser Index verwendet wird, kann die Abfragegeschwindigkeit erheblich verbessert werden. Beispielsweise die folgende Abfrage:

-- Wenn der Name indiziert ist, rufen Sie den Namenswert direkt vom Blattknoten des Index ab, ohne eine sekundäre Suche durchzuführen. Wählen Sie den Namen aus der Person aus, wobei Name = „abc“ ist.
-- Wenn ein aggregierter Index „Name, Alter“ vorhanden ist, werden die Daten ohne eine sekundäre Suche direkt zurückgegeben. Wählen Sie „Name, Alter“ aus der Person aus, wobei „Name“ = „abc“ und „Alter“ = 12 ist.

Sortieren anhand eines Indexes

Die Sortieroperation von MySQL kann auch Indizes verwenden. Der Index kann nur dann zum Sortieren verwendet werden, wenn die Spaltenreihenfolge des Indexes genau der Reihenfolge von ORDER BY entspricht und die Sortiermethode aller Spalten (aufsteigend oder absteigend) ebenfalls gleich ist. Hinweis: Die Anzahl der sortierten Felder kann kleiner sein als die der entsprechenden Indexfelder, die Reihenfolge muss jedoch konsistent sein. wie folgt:

-- Angenommen, es gibt einen gemeinsamen Index für (Name, Alter, Geschlecht). -- Sie können den Index verwenden, um auszuwählen ... Sortieren nach Name absteigend, Alter absteigend
auswählen ... sortieren nach Name absteigend, Alter absteigend, Geschlecht absteigend
-- Sortieren ist nicht erlaubt. Auswählen ... Sortieren nach Name desc,Geschlecht desc
Auswählen ... Sortieren nach Name absteigend, Alter aufsteigend

Beenden

Dieser Artikel basiert auf MySQL 5.5. Neuere Versionen können andere Strategien haben.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.