Vollständige Schritte zum Erstellen eines Hochleistungsindex in MySQL

1. Index-Grundlagen

1. Arten von Indizes

1.1 B-Baum-Index

Die meisten MySQL-Speicher-Engines verwenden standardmäßig B+-Baumindizes. Verschiedene Speicher-Engines verwenden B+-Baumindizes auf unterschiedliche Weise. MyISAM verwendet Präfixkomprimierungstechnologie, um den Index zu verkleinern, aber InnoDB speichert ihn im Metadatenformat. MyISAM-Indizes verweisen auf die indizierten Zeilen anhand des physischen Speicherorts der Daten, während InnoDB die indizierten Zeilen basierend auf dem Primärschlüssel referenziert.

B-Baum und B+Baum

B-Baum:

B+ Baum:

Der Unterschied:

• Die Schlüsselwörter und Datensätze des B-Baums werden zusammen platziert. Die Blattknoten können als externe Knoten betrachtet werden und enthalten keine Informationen. Die Nicht-Blattknoten des B+-Baums haben nur Schlüsselwörter und Indizes, die auf den nächsten Knoten verweisen. Datensätze werden nur in Blattknoten platziert.
• In einem B-Baum ist es umso schneller, einen Datensatz zu finden, je näher er am Stammknoten liegt. Solange das Schlüsselwort gefunden wird, kann die Existenz des Datensatzes festgestellt werden. In einem B+-Baum ist die Suchzeit für jeden Datensatz jedoch grundsätzlich gleich. Es ist notwendig, vom Stammknoten zum Blattknoten zu gelangen, und das Schlüsselwort muss im Blattknoten erneut verglichen werden. Aus dieser Perspektive scheint die Leistung des B-Baums besser zu sein als die des B+-Baums, in tatsächlichen Anwendungen ist jedoch die Leistung des B+-Baums besser. Da die Nicht-Blattknoten des B+-Baums keine tatsächlichen Daten speichern, kann jeder Knoten mehr Elemente aufnehmen als der B-Baum, und die Baumhöhe ist kleiner als beim B-Baum. Der Vorteil hiervon besteht darin, dass die Anzahl der Festplattenzugriffe reduziert wird. Obwohl der B+-Baum mehr Vergleiche benötigt, um einen Datensatz zu finden als der B-Baum, entspricht die Zeit für einen Festplattenzugriff Hunderten oder Tausenden von Speichervergleichen, sodass die Leistung des B+-Baums in der Praxis besser sein kann. Darüber hinaus sind die Blattknoten des B+-Baums mithilfe von Zeigern miteinander verbunden, um eine sequentielle Durchquerung zu erleichtern (z. B. das Anzeigen aller Dateien in einem Verzeichnis, aller Datensätze in einer Tabelle usw.), weshalb viele Datenbanken und Dateisysteme B+-Bäume verwenden.
  

Warum ist der B+-Baum in praktischen Anwendungen für die Datei- und Datenbankindizierung in Betriebssystemen besser geeignet als der B-Baum?

• Der B+-Baum weist geringere Lese- und Schreibkosten auf der Festplatte auf.
  • Die internen Knoten des B+-Baums haben keine Zeiger auf die spezifischen Informationen des Schlüsselworts. Daher sind seine internen Knoten kleiner als die des B-Baums. Wenn alle Schlüsselwörter desselben internen Knotens im selben Datenträgerblock gespeichert sind, kann der Datenträgerblock mehr Schlüsselwörter aufnehmen. Je mehr Schlüsselwörter gesucht werden müssen, desto mehr werden auf einmal in den Speicher eingelesen. Relativ gesehen wird die Anzahl der IO-Lese- und Schreibvorgänge reduziert.
• Die Abfrageeffizienz des B+-Baums ist stabiler
  • Da der Nicht-Endpunkt nicht der Knoten ist, der letztendlich auf den Dateiinhalt verweist, handelt es sich lediglich um einen Index des Schlüsselworts im Blattknoten. Daher muss jede Schlüsselwortsuche einen Pfad vom Stammknoten zum Blattknoten nehmen. Die Pfadlängen aller Keyword-Abfragen sind gleich, was zu einer gleichen Abfrageeffizienz für jeden Datenpunkt führt.
    

Warum keine rot-schwarzen Bäume verwenden?

• Der B+-Baum hat kürzere Suchzeiten
  • Die zeitliche Komplexität einer Suchoperation in einem ausgeglichenen Baum hängt mit der Baumhöhe h zusammen, O(h)=O(logdN), wobei d der Ausgangsgrad jedes Knotens ist.
  • Der Ausgangsgrad eines Rot-Schwarz-Baums beträgt 2, während der Ausgangsgrad eines B+-Baums im Allgemeinen sehr groß ist. Daher ist die Baumhöhe h eines Rot-Schwarz-Baums offensichtlich viel größer als die eines B+-Baums und auch die Anzahl der Suchvorgänge ist größer.
• Der B+-Baum verwendet die Funktion zum Vorlesen der Festplatte
  • Um die Anzahl der Festplatten-E/A-Vorgänge zu reduzieren, wird die Festplatte häufig nicht streng nach Bedarf gelesen, sondern jedes Mal im Voraus. Während des Vorlesevorgangs führt die Festplatte ein sequentielles Lesen durch. Beim sequentiellen Lesen ist kein Suchen der Festplatte erforderlich und die Festplatte benötigt nur eine kurze Rotationszeit, sodass die Geschwindigkeit sehr hoch ist.
  • Das Betriebssystem unterteilt Speicher und Festplatte im Allgemeinen in Blöcke fester Größe, von denen jeder als Seite bezeichnet wird, und Speicher und Festplatte tauschen Daten in Seiteneinheiten aus. Das Datenbanksystem stellt die Größe eines Indexknotens auf die Größe einer Seite ein, sodass ein Knoten in einem I/O vollständig geladen werden kann. Und die Vorlesefunktion kann verwendet werden, und benachbarte Knoten können auch vorgeladen werden
    

1.2 Hash-Index

Hash-Indizes werden auf Basis von Hash-Tabellen implementiert. Für jede Datenzeile berechnet die Speicher-Engine einen Hash-Code für alle Indexspalten. Der Hash-Code kann für die Suche in O(1)-Zeit verwendet werden, kann jedoch nicht zum Sortieren und Gruppieren verwendet werden. Er unterstützt nur die exakte Suche und kann nicht für die partielle Suche oder Bereichssuche verwendet werden.

In MySQL unterstützt nur die Speicher-Engine explizit Hash-Indizes.

Die InnoDB-Speicher-Engine verfügt über eine spezielle Funktion namens „adaptiver Hash-Index“. Wenn ein Indexwert sehr häufig verwendet wird, wird über dem B+Tree-Index ein Hash-Index erstellt. Dadurch erhält der B+Tree-Index einige der Vorteile des Hash-Index, z. B. eine schnelle Hash-Suche.

1.3 Geodatenindex (R-Baum)

Die MyISAM-Speicher-Engine unterstützt räumliche Datenindizes (R-Tree) und kann zur Speicherung geografischer Daten verwendet werden. Räumliche Datenindizes indizieren Daten aus allen Dimensionen und können jede Dimension effektiv für kombinierte Abfragen verwenden.

Zur Datenpflege müssen GIS-bezogene Funktionen verwendet werden.

1.4 Volltextindizierung

Die MyISAM-Speicher-Engine unterstützt die Volltextindizierung, die zum Suchen von Schlüsselwörtern im Text verwendet wird, anstatt sie direkt auf Gleichheit zu vergleichen.

Die Suchbedingung verwendet MATCH AGAINST anstelle des normalen WHERE. Der Volltextindex wird mithilfe eines invertierten Index implementiert, der die Zuordnung von Schlüsselwörtern zu den Dokumenten aufzeichnet, in denen sie sich befinden.

Die Speicher-Engine InnoDB unterstützt seit MySQL Version 5.6.4 auch die Volltextindizierung.

2. Vor- und Nachteile von Indizes

Vorteil

• Indizes reduzieren die Datenmenge, die der Server scannen muss, erheblich
• Indizes können dem Server helfen, Sortierungen und temporäre Tabellen zu vermeiden und so die CPU-Auslastung zu reduzieren.
• Zufällige IO können in sequentielle IO umgewandelt werden, um die IO zu beschleunigen
  

Mangel

• Obwohl Indizes die Abfragegeschwindigkeit erheblich erhöhen, verringern sie auch die Geschwindigkeit von Tabellenaktualisierungen wie INSERT, UPDATE und DELETE. Denn wenn Sie eine Tabelle aktualisieren, speichert MySQL nicht nur die Daten, sondern speichert auch die Indexdatei. Jedes Mal, wenn Sie ein Feld mit einer Indexspalte aktualisieren, werden die Indexinformationen nach den durch das Update verursachten Schlüsselwertänderungen angepasst.
• Tatsächlich ist der Index auch eine Tabelle, die den Primärschlüssel und die Indexfelder speichert und auf die Datensätze der Entitätstabelle verweist, sodass die Indexspalte auch Speicherplatz beansprucht.
  

3. Leistungsstarke Indexierungsstrategie

1. Unabhängige Spalten

Wenn die Spalten in der MySQL-Abfrage nicht unabhängig sind, wird der Index nicht verwendet. „Unabhängige Spalten“ bedeutet, dass die Indexspalte nicht Teil eines Ausdrucks oder eines Funktionsparameters sein kann.

Zum Beispiel

mysql> SELECT-ID, Name FROM t_user WHERE-ID + 1 = 5;

MySQL kann diese Gleichung „ID + 1“ nicht analysieren. Wir sollten uns angewöhnen, die WHERE-Bedingungen zu vereinfachen.

2. Präfixindex

Manchmal müssen Sie sehr lange Zeichenspalten indizieren, wodurch der Index groß und langsam wird.

Beispielsweise müssen Sie für Spalten der Typen BLOB, TEXT und VARCHAR einen Präfixindex verwenden, um nur die Anfangszeichen zu indizieren.

Die Auswahl der Präfixlänge muss basierend auf der Indexselektivität bestimmt werden

3. Mehrspaltiger Index

Viele Leute verstehen mehrspaltige Indizes nicht vollständig. Ein häufiger Fehler besteht darin, für jede Spalte einen separaten Index zu erstellen oder mehrspaltige Indizes in der falschen Reihenfolge zu erstellen.

In den meisten Fällen verbessert das Erstellen unabhängiger einspaltiger Indizes für mehrere Spalten die Abfrageleistung von MySQL nicht. Daher wird die Strategie „Indexzusammenführung“ eingeführt, mit der mehrere einspaltige Indizes für die Tabelle verwendet werden können, um die angegebene Zeile bis zu einem gewissen Grad zu lokalisieren.

Beispielsweise ist es in der folgenden Anweisung am besten, Benutzername und Passwort als mehrspaltige Indizes festzulegen.

SELECT Benutzername, Passwort FROM t_user WHERE Benutzername = ‚Aiguodala‘ AND Passwort = ‚Aiguodala‘;

4. Entsprechende Indexspaltenreihenfolge

Setzen Sie die selektivsten Indexspalten an den Anfang.

Unter der Selektivität eines Index versteht man das Verhältnis der eindeutigen Indexwerte zur Gesamtzahl der Datensätze. Der Maximalwert ist 1. In diesem Fall verfügt jeder Datensatz über einen entsprechenden eindeutigen Index. Je höher die Selektivität, desto besser sind die einzelnen Datensätze unterscheidbar und desto höher ist die Abfrageeffizienz.

5. Clustered-Index

Ein Clustered-Index ist kein eigener Indextyp, sondern eine Möglichkeit zur Datenspeicherung. Der Begriff „clustered“ bedeutet, dass Datenzeilen und benachbarte Schlüsselwerte kompakt zusammen gespeichert werden.

InnoDB clustert Daten nach Primärschlüssel. 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 als Clusterindex.

Vor- und Nachteile aggregierter Daten

Vorteil:

• Sie können zusammengehörige Daten zusammen speichern
  • Wenn Sie beispielsweise ein E-Mail-Postfach implementieren, werden die Daten entsprechend der Benutzer-ID gruppiert, sodass nur eine kleine Datenmenge von der Festplatte gelesen werden muss, um alle E-Mails eines bestimmten Benutzers abzurufen. Wenn kein gruppierter Index vorhanden ist, führt das Abrufen jeder E-Mail zu einem Festplatten-E/A.
• Schnellerer Datenzugriff. Clustered-Indizes speichern Indizes und Daten im selben B+-Baum, wodurch Daten leichter gefunden werden können.
• Abfragen mit abdeckenden Indexscans können die Primärschlüsselwerte in den Seitenknoten direkt verwenden
  

Mangel:

• Durch das Clustering von Daten lässt sich die Leistung von E/A-intensiven Anwendungen maximieren. Wenn jedoch alle Daten im Speicher abgelegt werden, ist die Zugriffsreihenfolge nicht wichtig und gruppierte Indizes bieten keinen Vorteil.
• Die Einfügegeschwindigkeit hängt stark von der Einfügereihenfolge ab. Wenn die Daten nicht in der Reihenfolge des Primärschlüssels geladen werden, verwenden Sie am besten den Befehl OPTIMIZE TABLE, um die Tabelle nach dem Laden neu zu organisieren. Daher wird empfohlen, einen automatisch inkrementierenden Primärschlüssel zu wählen.
• Das Aktualisieren von Clustered-Indexspalten ist aufwändig, da InnoDB dadurch gezwungen wird, jede aktualisierte Zeile an einen neuen Speicherort zu verschieben.
• Bei Tabellen, die auf gruppierten Indizes basieren, kann es zu einer Seitenaufteilung kommen, wenn neue Zeilen eingefügt werden oder wenn der Primärschlüssel aktualisiert wird und Zeilen verschoben werden müssen. Wenn der Primärschlüsselwert einer Zeile erfordert, dass die Zeile in eine ganze Seite eingefügt wird, teilt die Speicher-Engine die Seite in zwei Seiten auf, um die Zeile unterzubringen. Dies ist eine Teilungsoperation. Seitenaufteilungen führen dazu, dass die Tabelle mehr Speicherplatz belegt.
• Clustered-Indizes können die Ausführung vollständiger Tabellenscans verlangsamen, insbesondere bei spärlicher Zeilenanzahl oder wenn die Daten aufgrund von Seitenaufteilungen nicht zusammenhängend gespeichert sind.
  

Nicht gruppierter Index

Die Daten werden in einer indexgetrennten Struktur gespeichert. Die Blattknoten der Indexstruktur zeigen auf die entsprechenden Zeilen der Daten. MyISAM speichert den Index über key_buffer im Speicher zwischen. Wenn auf die Daten zugegriffen werden muss (Zugriff über den Index), wird der Index direkt im Speicher gesucht und dann werden die entsprechenden Daten auf der Festplatte über den Index gefunden. Aus diesem Grund ist die Geschwindigkeit langsam, wenn der Index nicht im Schlüsselpuffer gefunden wird.

6. Abdeckungsindex

Der Index umfasst die Werte aller abzufragenden Felder

Nutzen:

• Indexeinträge sind viel kleiner als Datenzeilen, sodass sie die Datenzugriffe erheblich reduzieren und das Ablegen aller Daten im Speicher vereinfachen können.
• Da die Indizes in der Reihenfolge der Spaltenwerte gespeichert werden, erfordern Bereichsabfragen mit E/A-intensiven Typen wesentlich weniger E/A als das zufällige Lesen jeder einzelnen Datenzeile von der Festplatte.
• Einige Speicher-Engines (wie etwa MyISAM) speichern nur Indizes im Speicher zwischen und verlassen sich beim Zwischenspeichern der Daten auf das Betriebssystem. Daher ist der Zugriff auf den Index allein ohne die Verwendung von Systemaufrufen (die normalerweise zeitaufwändig sind) möglich.
• Der sekundäre Index (nicht gruppierter Index) von InnoDB speichert den Primärschlüsselwert der Zeile im Blattknoten. Wenn der sekundäre Primärschlüssel die Abfrage abdecken kann, kann die sekundäre Abfrage des Primärschlüsselindex vermieden werden.

3. Optimierung der Abfrageleistung

1. Erklären Sie die Leistungsanalyse

Verwenden Sie das Schlüsselwort EXPLAIN, um den Optimierer zur Ausführung von SQL-Abfrageanweisungen zu simulieren, damit Sie erfahren, wie MySQL Ihre SQL-Anweisungen verarbeitet. Analysieren Sie den Leistungsengpass Ihrer Abfrageanweisung oder Tabellenstruktur

Beispiel:

1.1 id: Tabellenlesereihenfolge

„id“ ist die Sequenznummer der Auswahlabfrage, die eine Reihe von Zahlen enthält, die die Reihenfolge angeben, in der die Auswahlklauseln oder Operationstabellen in der Abfrage ausgeführt werden.

Die ID ist dieselbe: Die Ausführungsreihenfolge ist von oben nach unten

ERKLÄREN SIE: SELECT * FROM t1, t2, t3, WO t1.id = t2.id UND t2.id = t3.id;

Unterschiedliche IDs: Die Ausführungsreihenfolge ist so, dass die mit der größeren ID zuerst ausgeführt wird

ERKLÄREN SIE: SELECT t2.id FROM t2 WHERE t2.id = 
(WÄHLEN SIE t1.id AUS t1, WO t1.id = 
(WÄHLEN SIE t3.id AUS t3)
);

1.2 select_type: Abfragevorgangstyp

select_type stellt den Abfragetyp dar, der hauptsächlich zur Unterscheidung zwischen allgemeinen Abfragen, gemeinsamen Abfragen, Unterabfragen und anderen komplexen Abfragen verwendet wird

1.3 Tabelle: Die Quelle der Tabelle

Tabelle gibt an, auf welcher Tabelle diese Daten basieren

1.4 Typ: Zugriffstyp

Typ ist der Zugriffstyp der Abfrage. Es ist ein wichtigerer Indikator. Die Ergebnisse sind vom Besten zum Schlechtesten:

System > const > eq_ref > ref > Volltext > ref_or_null > Index_Merge > eindeutige Unterabfrage > Index_Unterabfrage > Bereich > Index > alle

--Die übliche Reihenfolge ist system > const > eq_ref > ref > range > index > all

Generell muss darauf geachtet werden, dass die Abfrage mindestens die Bereichsebene, besser noch die Referenzebene erreicht.

1.5 possible_key: mögliche Indizes

Zeigt einen oder mehrere Indizes an, die auf diese Tabelle zutreffen können. Wenn für das in die Abfrage einbezogenen Feld ein Index vorhanden ist, wird dieser zwar aufgelistet, aber möglicherweise nicht tatsächlich von der Abfrage verwendet.

1.6 Schlüssel: der tatsächlich verwendete Index

Der tatsächlich verwendete Index. Wenn NULL, wird kein Index verwendet.

1.7 key_len: Anzahl der vom Index verwendeten Bytes

Gibt die Anzahl der im Index verwendeten Bytes an. Mit dieser Spalte kann die Länge des in der Abfrage verwendeten Indexes berechnet werden. Mithilfe des Felds key_len können Sie überprüfen, ob der Index vollständig genutzt wird.

Je länger ken_len ist, desto vollständiger wird der Index genutzt.

1.8 ref: Zeigt detaillierte Informationen über den verwendeten Index an

ref zeigt an, welche Spalte des Index verwendet wird und kann nach Möglichkeit eine Konstante sein. Welche Spalten oder Konstanten werden zum Nachschlagen von Werten in der Indexspalte verwendet?

1,9 Zeilen: die Anzahl der abgefragten Zeilen

In der Spalte „Zeilen“ wird die Anzahl der Zeilen angezeigt, die MySQL seiner Meinung nach untersuchen muss, um die Abfrage auszuführen. Je weniger, desto besser!

1.10 Extra: Weitere wichtige Informationen

Weitere wichtige Zusatzinformationen

• Filesort verwenden: Externe Indexsortierung verwenden (es wird kein benutzerdefinierter Index verwendet)
  • Dies bedeutet, dass MySQL zum Sortieren der Daten einen externen Index verwendet, anstatt sie in der Reihenfolge des Indexes in der Tabelle zu lesen. Sortiervorgänge in MySQL, die nicht mithilfe von Indizes abgeschlossen werden können, werden als „Dateisortierungen“ bezeichnet.
  • Die Meldung „Filesort wird verwendet“ weist darauf hin, dass die SQL-Anweisung nicht gut konzipiert ist und nicht entsprechend dem erstellten Index oder in der vom Index angegebenen Reihenfolge sortiert ist.
• Mit temporären
  • Temporäre Tabellen werden zum Speichern von Zwischenergebnissen verwendet. MySQL verwendet temporäre Tabellen beim Sortieren von Abfrageergebnissen. Häufig verwendet in Sortierreihenfolge und Gruppierungsabfrage group by
  • Das Vorkommen von „Using temporary“ weist darauf hin, dass die SQL-Anweisung schlecht konzipiert ist, möglicherweise weil der zusammengesetzte Index nicht in der richtigen Reihenfolge verwendet wird.
• Verwenden des Index
  • Die Verwendung eines Index bedeutet, dass bei der entsprechenden Auswahloperation ein überdeckender Index verwendet wird, wodurch der Zugriff auf die Datenzeilen der Tabelle vermieden wird und eine gute Effizienz erzielt wird!
  • Wenn gleichzeitig „using where“ auftritt, bedeutet dies, dass der Index für die Suche nach dem Indexschlüsselwert verwendet wird.
  • Wenn „using where“ nicht vorhanden ist, wird der Index nur zum Lesen von Daten verwendet, anstatt eine Suche durchzuführen.
• Verwenden von „where“
  • Gibt an, dass bei Verwendung von Filtern
• Verwenden des Join-Puffers
  • Verbindungs-Caching wird verwendet
• unmöglich, wo
  • Der Wert der Where-Klausel ist immer „false“ und kann nicht zum Abrufen von Tupeln verwendet werden.
• Tabellen auswählen, die optimiert wurden
  • Wenn keine GROUP BY-Klausel vorhanden ist, muss die Optimierung von MIN/MAX-Operationen basierend auf Indizes oder COUNT(*)-Operationen für die MyISAM-Speicher-Engine nicht bis zur Ausführungsphase warten, um die Berechnungen durchzuführen. Die Optimierung wird während der Phase der Generierung des Abfrageausführungsplans abgeschlossen.

Zusammenfassen

Dies ist das Ende dieses Artikels zum Erstellen von Hochleistungsindizes in MySQL. Weitere relevante Inhalte zu MySQL-Hochleistungsindizes finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!