Detaillierte Erklärung zum MySQL-Index

1. Index-Grundlagen

1.1 Einleitung

In MySQL wird ein Index auch als „Schlüssel“ bezeichnet. Es handelt sich dabei um eine Datenstruktur, die von der Speicher-Engine zum schnellen Auffinden von Datensätzen verwendet wird.

Indizes sind für eine gute Leistung von entscheidender Bedeutung. Insbesondere wenn die Datenmenge in einer Tabelle zunimmt, wird der Einfluss der Indizes auf die Leistung immer wichtiger.

Indexoptimierung sollte das effektivste Mittel zur Optimierung der Abfrageleistung sein. Um einen wirklich optimalen Index zu erstellen, müssen SQL-Abfrageanweisungen häufig neu geschrieben werden.

1.2 So funktioniert die Indizierung

Um zu verstehen, wie Indizes in MySQL funktionieren, ist es am einfachsten, einen Blick auf den Index eines Buches zu werfen. Wenn Sie beispielsweise ein Thema in einem Buch finden möchten, schauen Sie sich normalerweise zuerst den Index an. Sobald Sie das entsprechende Kapitel und die Seitenzahl gefunden haben, können Sie schnell finden, was Sie suchen.

In MySQL verwendet die Speicher-Engine Indizes auf ähnliche Weise: Sie sucht zunächst nach dem entsprechenden Wert im Index, findet dann die entsprechende Datenzeile basierend auf dem übereinstimmenden Indexdatensatz und gibt schließlich den Datenergebnissatz an den Client zurück.

1.3 Indextypen

In MySQL beziehen wir uns normalerweise auf die folgenden Indextypen:

• Allgemeiner Index

  Ein regulärer Index, auch gemeinsamer Index (Index oder Schlüssel) genannt, kann die Abfrageeffizienz im Allgemeinen verbessern. Eine Datentabelle kann mehrere reguläre Indizes haben. Der am häufigsten verwendete Indextyp ist der reguläre Index. Wenn der Indextyp nicht explizit angegeben ist, handelt es sich bei dem Index, von dem wir sprechen, um einen regulären Index.

• Primärschlüsselindex

  Primärschlüsselindex (Primärschlüssel), auch als Primärschlüssel bezeichnet. Es kann die Abfrageeffizienz verbessern und einzigartige Einschränkungen bereitstellen. Eine Tabelle kann nur einen Primärschlüssel haben. Ein als Auto-Inkrement markiertes Feld muss ein Primärschlüssel sein, aber ein Primärschlüssel führt nicht unbedingt eine Auto-Inkrementierung durch. Im Allgemeinen wird der Primärschlüssel für ein bedeutungsloses Feld (z. B. eine Zahl) definiert und der Datentyp des Primärschlüssels sollte vorzugsweise ein numerischer Wert sein.

• Eindeutiger Index

  Eindeutige Indizes können die Abfrageeffizienz verbessern und eindeutige Einschränkungen bereitstellen. Eine Tabelle kann mehrere eindeutige Indizes haben.

• Volltextindex

  Die Volltextindizierung (Volltext) kann die Abfrageeffizienz der Volltextsuche verbessern und wird im Allgemeinen durch Sphinx ersetzt. Sphinx unterstützt jedoch keine chinesische Suche. Coreseek ist eine Volltextsuchmaschine, die Chinesisch unterstützt, auch bekannt als Sphinx mit chinesischer Wortsegmentierungsfunktion. Im eigentlichen Projekt haben wir Coreseek verwendet.

• Fremdschlüsselindex

  Fremdschlüsselindizes (Foreign Key), auch als Fremdschlüssel bezeichnet, können die Abfrageeffizienz verbessern. Fremdschlüssel werden automatisch mit den Primärschlüsseln anderer entsprechender Tabellen verknüpft. Die Hauptfunktion von Fremdschlüsseln besteht darin, die Konsistenz und Integrität von Datensätzen sicherzustellen.

  Hinweis: Nur Tabellen, die die InnoDB-Speicher-Engine verwenden, unterstützen Fremdschlüssel. Wenn für das Fremdschlüsselfeld kein Indexname angegeben ist, wird dieser automatisch generiert. Wenn Sie Datensätze in einer übergeordneten Tabelle (z. B. einer Kategorietabelle) löschen möchten, müssen Sie zuerst die entsprechenden Datensätze in der untergeordneten Tabelle (einer Tabelle mit einem Fremdschlüssel, z. B. einer Artikeltabelle) löschen, da sonst ein Fehler auftritt. Beim Erstellen einer Tabelle können Sie einen Fremdschlüssel für das Feld festlegen, z. B. verweist der Fremdschlüssel (cate_id) auf cms_cate (id). Da die Effizienz von Fremdschlüsseln nicht sehr gut ist, wird die Verwendung von Fremdschlüsseln nicht empfohlen. Wir müssen jedoch die Idee von Fremdschlüsseln verwenden, um die Konsistenz und Integrität der Daten sicherzustellen.

1.4 Indizierungsmethoden

In MySQL werden Indizes auf der Ebene der Speicher-Engine und nicht auf der Serverebene implementiert. Die von MySQL unterstützten Indizierungsmethoden können auch als Indextypen (im weiteren Sinne) bezeichnet werden. Im Wesentlichen handelt es sich dabei um die folgenden:

B-Baum-Index

Wenn der Typ nicht angegeben ist, bezieht er sich wahrscheinlich auf einen B-Tree-Index. Verschiedene Speicher-Engines verwenden B-Tree-Indizes auf unterschiedliche Weise und die Leistung variiert. Beispiel: MyISAM verwendet die Präfixkomprimierungstechnologie, um den Index zu verkleinern, aber InnoDB speichert den Index im ursprünglichen Datenformat. Beispielsweise referenziert MyISAM indizierte Zeilen anhand des physischen Speicherorts der Daten, während InnoDB indizierte Zeilen basierend auf dem Primärschlüssel referenziert.

B-Tree speichert Indexspalten sequenziell und eignet sich daher sehr gut für die Suche nach Bereichsdaten. Dadurch kann der Datenzugriff beschleunigt werden, da die Speicher-Engine keinen vollständigen Tabellenscan mehr durchführen muss, um die erforderlichen Daten abzurufen.

Wenn ein Index Werte aus mehreren Feldern (Spalten) enthält, handelt es sich um einen zusammengesetzten Index. Ein zusammengesetzter Index ordnet mehrere Feldwerte basierend auf der Reihenfolge, in der die Spalten erstellt werden. wie folgt:

Tabelle Personen erstellen (
 id int unsigned not null auto_increment Primärschlüssel Kommentar 'Primärschlüssel-ID',
 Nachname varchar (20) nicht null Standard '' Kommentar 'Nachname',
 Vorname varchar(20) nicht null Standard '' Kommentar 'Name',
 Geburtstagsdatum nicht null Standard '1970-01-01' Kommentar 'Geburtsdatum',
 gender tinyint unsigned not null default 3 comment 'Geschlecht: 1 männlich, 2 weiblich, 3 unbekannt',
 Schlüssel (Nachname, Vorname, Geburtstag)
) Engine=InnoDB Standardzeichensatz=UTF8;

Die folgenden Daten wurden außerdem in die Personentabelle eingefügt:

Wir haben einen zusammengesetzten Indexschlüssel (Nachname, Vorname, Geburtstag) erstellt, der die Werte der Spalten Nachname, Vorname und Geburtstag für jede Zeile in der Tabelle enthält. Auch der Index wird nach dieser Reihenfolge sortiert und gespeichert. Wenn zwei Personen den gleichen Vor- und Nachnamen haben, wird der Index nach ihrem Geburtsdatum sortiert und gespeichert.

B-Tree-Indizes sind auf die Suche nach vollständigen Schlüsselwerten, Schlüsselwertbereichen oder Schlüsselpräfixen anwendbar, wobei die Schlüsselpräfixsuche nur auf die Suche basierend auf dem äußersten linken Präfix anwendbar ist.

Zusammengesetzte Indizes sind für die folgenden Abfragetypen effektiv:

Vollständige Werteübereinstimmung

Vollständiger Wertabgleich bezieht sich auf den Abgleich aller Spalten im Index. Beispiel: Suchen Sie nach Personen, deren Nachname Allen, deren Vorname Cuba und deren Geburtsdatum der 01.01.1960 ist.

Die SQL-Anweisung lautet:

Wählen Sie ID, Nachname, Vorname, Geburtstag aus Personen aus, bei denen Nachname = „Allen“ und Vorname = „Kuba“ und Geburtstag = „01.01.1960“ ist.

.

Stimmt mit dem Präfix ganz links überein

Beispielsweise können wir nur die erste Spalte des Index verwenden, um alle Personen mit dem Nachnamen Allen zu finden. Die SQL-Anweisung lautet:

Wählen Sie ID, Nachname, Vorname, Geburtstag aus Personen aus, bei denen Nachname = „Allen“ ist.

Passender Spaltenpräfix

Sie können beispielsweise nur den Anfang des Werts in der ersten Spalte des Index abgleichen, um alle Personen zu finden, deren Nachname mit A beginnt. Die SQL-Anweisung lautet:

Wählen Sie ID, Nachname, Vorname, Geburtstag aus Personen aus, deren Nachname wie „A%“ ist.

Passender Bereichswert

Der Bereich entspricht beispielsweise Personen mit Nachnamen zwischen Allen und Clinton. Die SQL-Anweisung lautet:

Wählen Sie ID, Nachname, Vorname, Geburtstag aus Personen aus, bei denen der Nachname ZWISCHEN ,Allen‘ und ,Clinton‘ liegt;

Auch hier wird nur die erste Spalte des Index verwendet.

Genaue Übereinstimmung mit der ersten Spalte und Bereichsübereinstimmung mit den folgenden Spalten

Suchen Sie beispielsweise nach Personen, deren Nachname Allen ist und deren Vorname mit dem Buchstaben C beginnt. Das heißt, die erste Spalte des zusammengesetzten Index weist eine vollständige Übereinstimmung auf, und die zweite Spalte weist eine Bereichsübereinstimmung auf. Die SQL-Anweisung lautet:

Wählen Sie ID, Nachname, Vorname, Geburtstag aus Personen aus, bei denen Nachname = „Allen“ und Vorname wie „C%“ ist.

Abfragen, die nur auf den Index zugreifen

B-Tree kann normalerweise „Nur-Index-Abfragen“ unterstützen, d. h. Abfragen müssen nur auf den Index zugreifen, ohne auf die Datenzeilen zuzugreifen. Dies hängt mit der Optimierung des „Abdeckungsindex“ zusammen, die später erläutert wird.

Hier sind einige Situationen, in denen zusammengesetzte Indizes fehlschlagen:

(1) Wenn die Suche nicht in der äußersten linken Spalte des zusammengesetzten Index beginnt, kann der Index nicht verwendet werden. Im obigen Beispiel kann der Index beispielsweise weder verwendet werden, um eine Person namens Cuba zu finden, noch kann er verwendet werden, um eine Person mit einem bestimmten Geburtsdatum zu finden, da keine dieser beiden Spalten die äußerste linke Spalte des zusammengesetzten Indexschlüssels (Nachname, Vorname, Geburtstag) ist. Ebenso ist die Suche nach Personen, deren Nachnamen mit einem bestimmten Buchstaben enden, nicht möglich. Das bedeutet, dass der Fuzzy-Match-Operator % in einer Abfrage nach ähnlichen Bereichen den Index ungültig macht, wenn er an erster Stelle steht.

(2) Wird bei einer Suche eine Spalte im Index übersprungen, wird nur die erste Indexspalte verwendet und die folgenden Indexspalten werden ungültig. Suchen Sie beispielsweise nach Personen mit dem Nachnamen Allen, die an einem bestimmten Datum geboren wurden. Da bei der Suche hier der Suchname (Vorname) nicht angegeben ist, kann MySQL nur die erste Spalte des zusammengesetzten Index (d. h. Nachname) verwenden.

(3) Wenn eine Abfrage eine Bereichsabfrage für eine bestimmte Spalte enthält, können alle Spalten rechts von dieser Spalte nicht mittels Indexoptimierung gefunden werden. Wenn die Abfragebedingung beispielsweise lautet: „wo Nachname='Allen' und Vorname wie 'C%' und Geburtstag='1992-10-25'“, kann diese Abfrage nur die ersten beiden Spalten des Index verwenden, da „wie hier“ eine Bereichsbedingung ist. Wenn die Anzahl der Werte in der Spalte der Bereichsabfrage begrenzt ist, können Sie diese optimieren, indem Sie anstelle von Bereichsbedingungen mehrere gleiche Bedingungen verwenden, sodass auch die rechte Spalte den Index verwenden kann.

Nachdem wir nun wissen, wie wichtig die Reihenfolge der Spalten in einem zusammengesetzten Index ist, beziehen sich diese Einschränkungen alle auf die Reihenfolge der Indexspalten. Bei der Leistungsoptimierung müssen Sie möglicherweise Indizes mit denselben Spalten, aber in unterschiedlicher Reihenfolge verwenden, um unterschiedliche Abfrageanforderungen zu erfüllen. In einer Tabelle benötigen Sie beispielsweise möglicherweise zwei zusammengesetzte Indizes: Schlüssel (Nachname, Vorname, Geburtstag) und Schlüssel (Vorname, Nachname, Geburtstag).

Der B-Tree-Index ist der am häufigsten verwendete Indextyp. Im Folgenden wird, sofern nicht anders angegeben, vom B-Tree-Index gesprochen.

1. Hash-Index

Ein Hash-Index wird basierend auf einer Hash-Tabelle implementiert. Nur Abfragen, die mit allen Spalten des Indexes exakt übereinstimmen, sind gültig. In MySQL unterstützt nur die Speicher-Engine explizit Hash-Indizes.

2. Räumlicher Datenindex (R-Baum)

Die MyISAM-Engine unterstützt räumliche Indizes und kann als geografischer Datenspeicher verwendet werden. Im Gegensatz zu B-Tree-Indizes erfordert dieser Index keine Präfixabfragen.

3. Volltextindizierung

Ein Volltextindex ist ein spezieller Indextyp, der nach Schlüsselwörtern im Text sucht, anstatt Werte im Index direkt zu vergleichen. Die Matching-Methode des Volltextindex unterscheidet sich völlig von der anderer Indizes. Sie ähnelt eher dem, was eine Suchmaschine macht, als einer einfachen Where-Bedingungsübereinstimmung. Sie können gleichzeitig einen Volltextindex und einen B-Tree-Index für dieselbe Spalte erstellen. Der Volltextindex eignet sich eher für Match Against-Operationen als für gewöhnliche Where-Condition-Operationen.

Ein Index kann die Werte einer Spalte (also Feld) oder mehrerer Spalten enthalten. Wenn der Index mehrere Spalten enthält, wird er im Allgemeinen als zusammengesetzter Index bezeichnet. Zu diesem Zeitpunkt ist die Reihenfolge der Spalten sehr wichtig, da MySQL nur die am weitesten links stehenden Präfixspalten des Index effizient nutzen kann. Das Erstellen eines Indexes mit zwei Spalten unterscheidet sich erheblich vom Erstellen zweier Indizes mit nur einer Spalte.

1.5 Vorteile von Indizes

Mithilfe von Indizes kann MySQL die benötigten Daten schnell finden. Dies ist jedoch nicht die einzige Funktion von Indizes.

Der am häufigsten verwendete B-Tree-Index speichert Daten in der richtigen Reihenfolge, sodass MySQL für Order By- und Group By-Operationen verwendet werden kann. Da die Daten der Reihe nach gespeichert werden, speichert B-Tree verwandte Spaltenwerte zusammen. Da die tatsächlichen Spaltenwerte ebenfalls im Index gespeichert werden, können einige Abfragen alle Daten nur mithilfe des Index abrufen, ohne für die Abfrage zur Tabelle zurückkehren zu müssen. Aufgrund dieser Funktion können wir den Schluss ziehen, dass Indizes die folgenden drei Vorteile haben:

• Durch Indizes wird die Datenmenge, die der MySQL-Server scannen muss, erheblich reduziert.
• Indizes können dem Server helfen, Sortierungen und temporäre Tabellen zu vermeiden.
• Indizes können zufällige E/A in sequentielle E/A umwandeln.

Darüber hinaus verwenden manche Leute das „Drei-Sterne-System“, um zu bewerten, ob ein Index für eine Abfrageanweisung geeignet ist. Das Drei-Sterne-System bedeutet im Wesentlichen: Wenn der Index zusammengehörende Datensätze zusammenfügen kann, erhält er einen Stern; wenn die Reihenfolge der Daten im Index mit der Anordnungsreihenfolge bei der Suche übereinstimmt, erhält er zwei Sterne; wenn die Spalten im Index alle für die Abfrage erforderlichen Spalten enthalten, erhält er drei Sterne.

Indizes sind nicht immer das beste Werkzeug und je mehr Indizes, desto besser. Im Allgemeinen ist ein Index nur dann sinnvoll, wenn der Nutzen, der dadurch der Speicher-Engine beim schnellen Auffinden von Datensätzen hilft, den dadurch verursachten Mehraufwand überwiegt.

Bei sehr kleinen Tabellen ist in den meisten Fällen ein einfacher vollständiger Tabellenscan effizienter und es ist nicht erforderlich, einen Index zu erstellen. Bei mittelgroßen bis großen Tabellen liegen die Vorteile von Indizes auf der Hand.

2. Leistungsstarke Indexierungsstrategie

Das korrekte Erstellen und Verwenden von Indizes ist die Grundlage für performante Abfragen. Zuvor haben wir verschiedene Indextypen sowie deren Vor- und Nachteile vorgestellt. Schauen wir uns nun an, wie Sie die Vorteile dieser Indizes wirklich nutzen können. Die folgenden Abschnitte helfen Ihnen dabei, die effiziente Verwendung von Indizes zu verstehen.

2.1 Unabhängige Spalten

Wir sehen häufig Abfragen, die Indizes unangemessen verwenden oder MySQL daran hindern, vorhandene Indizes zu verwenden. Wenn die Spalten in der SQL-Abfrage nicht unabhängig sind, verwendet MySQL den Index nicht. „Unabhängige Spalten“ bedeutet, dass die Indexspalte nicht Teil eines Ausdrucks oder eines Funktionsparameters sein kann.

Beispielsweise kann die folgende SQL-Abfrageanweisung die Index-ID des Primärschlüssels nicht verwenden:

Wählen Sie ID, Nachname, Vorname, Geburtstag aus den Personen aus, bei denen ID+1=3 ist;

Es ist leicht zu erkennen, dass der obige Where-Ausdruck tatsächlich zu „Where id=2“ gekürzt werden kann, MySQL diesen Ausdruck jedoch nicht automatisch analysieren kann. Wir sollten uns angewöhnen, die Where-Bedingungen zu vereinfachen und die Indexspalte immer allein auf eine Seite des Vergleichsoperators zu setzen. Wenn Sie also den Primärschlüsselindex verwenden möchten, lautet die korrekte Schreibweise:

Wählen Sie ID, Nachname, Vorname, Geburtstag aus Personen mit ID=2 aus.

Hier ist ein weiterer häufiger Fehler:

wählen Sie ... von ..., wobei bis_Tage(aktuelles_Datum()) – bis_Tage(Datumsspalte) <= 10;

2.2 Präfixindex und Indexselektivität

Manchmal müssen wir sehr lange Zeichenspalten indizieren, was den Index groß und langsam macht. Die übliche Lösung besteht darin, nur die ersten paar Zeichen der Spalte zu indizieren. Dadurch lässt sich viel Indexplatz sparen und die Indexeffizienz verbessern. Allerdings verringert sich dadurch auch die Selektivität des Index. Unter der Selektivität eines Index versteht man das Verhältnis der Anzahl eindeutiger Indexwerte (auch Kardinalität genannt) zur Gesamtzahl der Datensätze in der Datentabelle, das zwischen 0 und 1 liegt.

Die Selektivität eines eindeutigen Indexes beträgt 1. Dies ist die beste Indexselektivität und weist die beste Leistung auf.

Im Allgemeinen ist die Selektivität eines Spaltenpräfixes hoch genug, um die Anforderungen an die Abfrageleistung zu erfüllen. Für Spalten vom Typ Blob, Text oder sehr lange Varchar müssen Sie einen Präfixindex verwenden, das heißt, nur die ersten paar Zeichen der Spalte indizieren, da MySQL nicht die Indizierung der vollständigen Länge dieser Spalten zulässt.

So fügen Sie einen Präfixindex hinzu:

alter table user add key(address(8)); // Nur die ersten 8 Zeichen des Adressfeldes indizieren

Ein Präfixindex ist eine effektive Möglichkeit, den Index kleiner und schneller zu machen. Der Nachteil besteht jedoch darin, dass MySQL weder Präfixindizes für Order By- und Group By-Operationen noch für Covering-Scans verwenden kann.

Manchmal ist ein Suffixindex nützlich, beispielsweise um alle E-Mail-Adressen für eine Domäne zu finden. MySQL unterstützt jedoch keine Suffixindizes von Haus aus. Wir können die Zeichenfolge in umgekehrter Reihenfolge speichern und darauf basierend einen Präfixindex erstellen und diesen Index dann über Trigger verwalten.

2.3 Mehrspaltiger Index

Ein mehrspaltiger Index ist ein Index, der mehrere Spalten enthält. Dabei ist auf die Reihenfolge der Spalten zu achten. Ein mehrspaltiger Index wird auch als zusammengesetzter Index bezeichnet. Beispielsweise ist der vorherige Schlüssel (Nachname, Vorname, Geburtstag) ein zusammengesetzter Index.

Ein häufiger Fehler besteht darin, für jede Spalte einen separaten Index zu erstellen oder Indizes für mehrere Spalten in der falschen Reihenfolge zu erstellen.

Schauen wir uns zuerst das erste Problem an. Erstellen Sie für jede Spalte einen separaten Index. Diese Situation lässt sich leicht anhand von „show create table“ erkennen:

Tabelle erstellen t (
 c1 int,
 c2 int,
 c3 int,
 Taste(c1),
 Taste(c2),
 Taste (c3)
);

Diese falsche Indizierungsstrategie ist normalerweise darauf zurückzuführen, dass Benutzer vage Ratschläge von Experten hören, wie etwa „Indizieren Sie alle Spalten in der Where-Bedingung“.

Das Erstellen unabhängiger Einzelspaltenindizes für mehrere Spalten verbessert in den meisten Fällen nicht die Leistung von MySQL-Abfragen. In MySQL 5.0 und späteren Versionen wird eine Strategie namens Index Merge eingeführt, die mehrere einspaltige Indizes für die Tabelle verwenden kann, um die angegebene Zeile bis zu einem gewissen Grad zu lokalisieren. Die Effizienz ist jedoch immer noch viel schlechter als der zusammengesetzte Index.

Beispielsweise hat die Tabelle film_actor einen einspaltigen Index für die Felder film_id und actor_id. Die SQL-Abfrageanweisung lautet wie folgt:

Wählen Sie film_id, actor_id aus film_actor, wobei actor_id=1 oder film_id=1 ist;

In MySQL 5.0 und späteren Versionen können Abfragen diese beiden einspaltigen Indizes verwenden, um gleichzeitig zu scannen und die Ergebnisse zusammenzuführen. Dieser Algorithmus hat drei Varianten: Vereinigung mit oder Bedingungen, Schnittmenge mit und Bedingungen sowie Vereinigung und Schnittmenge, die die ersten beiden Bedingungen kombiniert.

Die obige Abfrage verwendet eine Vereinigungsstelle zweier Index-Scans, was aus der Spalte „Extra“ in „Explain“ ersichtlich ist (das Vereinigungszeichen erscheint im Wert „Extra“):

erklären select film_id,actor_id from film_actor where actor_id=1 or film_id=1\G

Die Indexzusammenführungsstrategie ist manchmal das Ergebnis einer Optimierung, weist aber häufiger darauf hin, dass der Index für die Tabelle schlecht aufgebaut ist:

• Wenn eine Schnittmenge mehrerer Indizes (normalerweise mit mehreren „und“-Bedingungen) verwendet wird, ist normalerweise ein zusammengesetzter Index mit allen zugehörigen Spalten erforderlich und nicht mehrere unabhängige Indizes mit jeweils einer Spalte.

• Wenn mehrere Indizes kombiniert werden (normalerweise mit mehreren oder Bedingungen), werden für die Caching-, Sortier- und Zusammenführungsvorgänge des Algorithmus normalerweise große Mengen an CPU- und Speicherressourcen verbraucht. An diesem Punkt kann die Abfrage als zwei Abfragevereinigungen neu geschrieben werden:

wähle film_id,actor_id aus film_actor, wobei actor_id=1
Vereinigung alle
Wählen Sie film_id,actor_id aus film_actor, wobei film_id=1 und actor_id<>1;

Wenn Sie im Erläuterungsergebnis eine Indexvereinigung finden, sollten Sie die SQL-Abfrageanweisung und die Tabellenstruktur sorgfältig überprüfen, um festzustellen, ob sie optimal ist, ob sie in mehrere Abfragevereinigungsmethoden aufgeteilt werden kann usw.

2.4 Wählen Sie die entsprechende Indexspaltenreihenfolge

Am verwirrendsten ist die Reihenfolge der Spalten in einem zusammengesetzten Index. In einem zusammengesetzten Index hängt die richtige Spaltenreihenfolge von den Abfragen ab, die den Index verwenden, und muss auch berücksichtigt werden, um die Sortier- und Gruppierungsanforderungen optimal zu erfüllen.

Die Reihenfolge der Indexspalten bedeutet, dass der Index zuerst nach der äußersten linken Spalte, dann nach der zweiten Spalte, der dritten Spalte usw. sortiert wird. Daher kann der Index in aufsteigender oder absteigender Reihenfolge durchsucht werden, um die Abfrageanforderungen von Klauseln wie „order by“, „group by“ und „distinct“ zu erfüllen, die genau der Spaltenreihenfolge entsprechen.

Wenn Sortieren und Gruppieren keine Rolle spielen, empfiehlt es sich häufig, die selektivsten Spalten ganz links (an erster Stelle) des zusammengesetzten Indexes zu platzieren. Derzeit wird der Index nur verwendet, um die Suche der Where-Bedingung zu optimieren. Möglicherweise müssen wir jedoch auch die Reihenfolge der Indexspalten basierend auf den am häufigsten ausgeführten Abfragen anpassen, um den Index in diesem Fall möglichst selektiv zu gestalten.

Nehmen Sie als Beispiel die folgende Abfrage:

Wählen Sie * aus der Zahlung, wobei staff_id=2 und customer_id=500 ist;

Soll ich einen Index mit Schlüssel (Staff-ID, Customer-ID) oder Schlüssel (Customer-ID, Staff-ID) erstellen? Sie können einige Abfragen ausführen, um die Verteilung der Werte in der Tabelle zu ermitteln und festzustellen, welche Spalte selektiver ist. Sie können beispielsweise die folgende Abfrage zur Vorhersage verwenden:

Wählen Sie Summe (Personal-ID = 2), Summe (Kunden-ID = 500) aus Zahlung\G

Angenommen, das Ergebnis zeigt, dass der Wert von sum(staff_id=2) 7000 und der Wert von sum(customer_id=500) 60 ist. Daraus können wir erkennen, dass in der obigen Abfrage die Kunden-ID selektiver ist und an den Anfang des Index gestellt werden sollte, also mit dem Schlüssel „key(customer_id, staff_id)“.

Hierbei gilt es jedoch eines zu beachten: Die Ergebnisse der Abfrage sind sehr stark von den konkret ausgewählten Werten abhängig. Wenn Sie mit der oben beschriebenen Methode optimieren, ist dies möglicherweise unfair gegenüber Abfragen mit unterschiedlichen Bedingungswerten und kann auch dazu führen, dass sich die Gesamtleistung des Servers verschlechtert.

Wenn die „schlechtesten Abfragen“ aus dem Bericht eines Tools wie pt-query-digest extrahiert werden, ist die nach der obigen Methode ausgewählte Indexreihenfolge oft sehr effizient. Wenn keine ähnliche spezifische Abfrage ausgeführt werden kann, ist es am besten, den Faustregeln zu folgen, da die Faustregeln eher die globale Kardinalität und Selektivität berücksichtigen als die Abfrage eines spezifischen Bedingungswerts. Als Faustregel kann die Selektivität wie folgt bestimmt werden:

wähle count(eindeutige Mitarbeiter-ID)/count(*) als Mitarbeiter-ID-Selektivität,
Anzahl(eindeutige Kunden-ID)/Anzahl(*) als Kunden-ID-Selektivität,
ab Zahlung\G

Angenommen, das Ergebnis zeigt, dass der Wert von staff_id_selectivity 0,001 beträgt, während der Wert von customer_id_selectivity 0,086 beträgt. Wir wissen, dass die Selektivität umso höher ist, je größer der Wert ist. Daher weist die Kunden-ID eine höhere Selektivität auf. Daher setzen wir es als erste Spalte des Index:

Tabelle ändern, Zahlung, Schlüssel hinzufügen (Kunden-ID, Mitarbeiter-ID);

Obwohl es sich lohnt, die Faustregeln zu Selektivität und globaler Kardinalität zu studieren und zu analysieren, sollten Sie unbedingt die Auswirkungen von Faktoren wie „Order by“ und „Group by“ bedenken, die einen erheblichen Einfluss auf die Abfrageleistung haben können.

2.5 Clustered-Index

Ein gruppierter Index ist kein eigener Indextyp, sondern eine Möglichkeit zur Datenspeicherung. Die genauen Details hängen von der Implementierung ab, aber der gruppierte Index von InnoDB speichert den B-Tree-Index und die Datenzeilen tatsächlich in derselben Struktur.

Wenn eine Tabelle einen gruppierten Index enthält, werden dessen Datenzeilen tatsächlich auf den Blattseiten des Index gespeichert. Das heißt, die Blattseiten enthalten alle Daten der Zeilen, während die Knotenseiten nur die Daten der Indexspalten enthalten.

Da die Speicher-Engine für die Implementierung des Index verantwortlich ist, unterstützen nicht alle Speicher-Engines Cluster-Indizes. In diesem Abschnitt konzentrieren wir uns auf InnoDB, aber die hier besprochenen Inhalte sind auf jede Speicher-Engine anwendbar, die Cluster-Indizes unterstützt.

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, der als gruppierter Index dient.

Vorteile von Clustered-Indizes:

• Zusammengehörende Daten können gemeinsam gespeichert werden.
• Schnellerer Datenzugriff. Ein gruppierter Index speichert den Index und die Daten im selben B-Baum. Daher ist das Abrufen von Daten aus einem gruppierten Index normalerweise schneller als aus einem nicht gruppierten Index.
• Abfragen, die abdeckende Index-Scans verwenden, können die Primärschlüsselwerte direkt von der Knotenseite verwenden.

Wenn Sie beim Entwerfen von Tabellen und Abfragen die oben genannten Vorteile voll ausnutzen, können Sie die Leistung erheblich steigern.

Nachteile des Clustered-Index:

• Clustered-Indizes maximieren die Leistung von E/A-intensiven Anwendungen. Wenn jedoch alle Daten im Speicher abgelegt sind, ist die Zugriffsreihenfolge nicht so wichtig und Clustered-Indizes bieten kaum Vorteile.
• Die Einfügegeschwindigkeit hängt stark von der Einfügereihenfolge ab. Das Einfügen in der Primärschlüsselreihenfolge ist der schnellste Weg, Daten in eine InnoDB-Tabelle einzufügen. Wenn die Daten jedoch nicht in der Reihenfolge des Primärschlüssels eingefügt werden, verwenden Sie am besten den Befehl OPTIMIZE TABLE, um die Tabelle nach Abschluss des Vorgangs neu zu organisieren.
• Das Aktualisieren von Clustered-Indexspalten ist aufwändig, da InnoDB dadurch gezwungen wird, jede aktualisierte Zeile an einen neuen Speicherort zu verschieben.
• Bei einer Tabelle, die auf einem gruppierten Index basiert, kann es beim Einfügen einer neuen Zeile oder beim Aktualisieren des Primärschlüssels zu einem Problem der „Seitenaufteilung“ kommen, wodurch Zeilen verschoben werden müssen. Seitenaufteilungen führen dazu, dass die Tabelle mehr Speicherplatz belegt.

In InnoDB „ist“ der gruppierte Index die Tabelle, sodass kein separater Zeilenspeicher wie bei MyISAM erforderlich ist. Jeder Blattknoten des gruppierten Indexes enthält den Primärschlüsselwert, die Transaktions-ID, den Rollback-Zeiger für Transaktionen und MVCC (Multiversion Control) sowie alle verbleibenden Spalten.

Der Sekundärindex (nicht gruppierter Index) von InnoDB unterscheidet sich stark vom gruppierten Index. Die Blattknoten des Sekundärindex speichern keine „Zeilenzeiger“, sondern Primärschlüsselwerte. Daher werden bei der Datensuche über einen Sekundärindex zwei Indexsuchvorgänge durchgeführt. Die Speicher-Engine muss zuerst den Blattknoten des Sekundärindex durchsuchen, um den entsprechenden Primärschlüsselwert zu erhalten, und dann basierend auf diesem Primärschlüsselwert nach der entsprechenden Datenzeile im Clusterindex suchen.

Um sicherzustellen, dass die Datenzeilen in der richtigen Reihenfolge eingefügt werden, ist es am einfachsten, den Primärschlüssel als auto_increment zu definieren. Wenn Sie InnoDB verwenden, sollten Sie versuchen, Daten nach Möglichkeit in der Reihenfolge des Primärschlüssels einzufügen und neue Zeilen nach Möglichkeit mit monoton zunehmenden Primärschlüsselwerten einzufügen.

Bei stark gleichzeitig ausgeführten Arbeitslasten kann das Einfügen in der Primärschlüsselreihenfolge in InnoDB zu erheblichen Konflikten mit den Primärschlüsselwerten führen. Dieses Problem ist sehr schwerwiegend und Sie können es selbst lösen, indem Sie auf Baidu suchen.

2.6 Deckungsindex

Normalerweise werden entsprechende Indizes auf Grundlage der Where-Bedingungen der Abfrage erstellt, dies ist jedoch nur ein Aspekt der Indexoptimierung. Um einen hervorragenden Index zu entwerfen, sollten Sie die gesamte Abfrage berücksichtigen, nicht nur die Where-Bedingung.

Indizes sind zwar eine effiziente Möglichkeit, Daten zu finden, MySQL kann jedoch auch Indizes verwenden, um Spaltendaten direkt abzurufen, ohne die Datenzeile lesen zu müssen. Wenn die Blattknoten des Index bereits alle abzufragenden Daten enthalten, warum ist es dann notwendig, für die Abfrage zur Tabelle zurückzukehren?

Wenn ein Index die Werte aller abzufragenden Felder (Spalten) enthält (oder abdeckt), nennen wir ihn einen „abdeckenden Index“.

Das Abdecken von Indizes ist sehr nützlich und kann die Leistung erheblich verbessern. Überlegen Sie, welcher Nutzen erzielt würde, wenn die Abfrage nur den Index scannen müsste, anstatt zur Tabelle zurückzukehren, um die Zeilen abzurufen:

• Indexeinträge sind normalerweise viel kleiner als die Größe einer Datenzeile. Wenn also nur der Index gelesen werden muss, kann MySQL die Menge der Datenzugriffe erheblich reduzieren. Das Abdecken von Indizes ist auch bei E/A-intensiven Anwendungen hilfreich, da der Index kleiner als die Daten ist und leichter im Speicher abgelegt werden kann.
• Da Indizes sequenziell nach Spaltenwerten gespeichert werden (zumindest innerhalb einer einzelnen Seite), erfordern E/A-intensive Bereichsabfragen wesentlich weniger Daten-E/A als das zufällige Lesen jeder Zeile von der Festplatte.
• Aufgrund des gruppierten Indexes von InnoDB sind abdeckende Indizes besonders für InnoDB-Tabellen nützlich. Der sekundäre Index (nicht gruppierter Index) von InnoDB speichert den Primärschlüsselwert der Zeile im Blattknoten. Wenn also der sekundäre Primärschlüssel die Abfrage abdecken kann, kann die sekundäre Abfrage des Primärschlüsselindex vermieden werden.

In allen diesen Szenarien ist der Aufwand, die gesamte Abfrage im Index auszuführen, im Allgemeinen wesentlich geringer, als zur Tabelle zurückzukehren.

B-Tree-Indizes können überdeckende Indizes sein, aber Hash-Indizes, räumliche Indizes und Volltext-Indizes unterstützen keine überdeckenden Indizes.

Wenn Sie eine Abfrage initiieren, die von einem Index abgedeckt wird (auch als Indexabdeckungsabfrage bezeichnet), können Sie die Informationen zur Verwendung des Index in der Spalte „Extra“ der Erläuterung sehen. wie:

Erklären Sie die Auswahl der ID unter den Personen.
Erklären Sie, wählen Sie den Nachnamen aus den Personen aus.
Erklären Sie „Select ID, Vorname aus Personen“.
Erklären Sie, wählen Sie Nachname, Vorname, Geburtstag aus den Personen aus.
Erklären Sie „select last_name,first_name,birthday from people where last_name='Allen';“

Die Personentabelle ist das, was wir im obigen Abschnitt erstellt haben. Sie enthält einen Primärschlüsselindex (ID) und einen mehrspaltigen zusammengesetzten Indexschlüssel (Nachname, Vorname, Geburtstag). Diese beiden Indizes decken die Werte von vier Feldern ab. Wenn eine SQL-Abfrageanweisung alle abzufragenden Felder innerhalb dieser vier Felder enthält, kann diese Abfrage als Indexabdeckungsabfrage bezeichnet werden. Wenn ein Index die Werte aller in einer SQL-Abfrageanweisung abzufragenden Felder enthält, ist dieser Index ein überlagernder Index für die Abfrageanweisung. Beispielsweise ist „key(last_name, first_name, birthday)“ ein abdeckender Index für die Auswahl von last_name, first_name aus Personen.

2.7 Index-Scan zum Sortieren verwenden

MySQL bietet zwei Möglichkeiten, geordnete Ergebnismengen zu erzeugen: durch eine Sortieroperation (Order By) und durch automatisches Sortieren per Index Order Scan (also Sortieren nach Index). Tatsächlich kommt es zwischen diesen beiden Sortiervorgängen nicht zu Konflikten, sodass „order by“ den Index zum Sortieren verwenden kann.

Genauer gesagt sortiert MySQL die Ergebnismengen auf zwei Arten:

1. Indexsortierung

Unter Indexsortierung versteht man das Sortieren der Ergebnismenge anhand der Feldwerte im Index. Wenn der Wert des Typparameters in „Explain“ „Index“ ist, bedeutet dies, dass MySQL die Indexsortierung verwenden muss. wie:

Erklären Sie die Auswahl der ID unter den Personen.
Erläutern Sie „Select ID, Nachname“ aus der Personensortierung nach ID absteigend.
Erklären Sie, wählen Sie den Nachnamen aus den Personen aus.
Erklären Sie, dass Sie aus der Personensortierung nach Nachnamen den Nachnamen auswählen.
Erklären Sie, dass Sie aus den Personen, die nach Nachname absteigend sortiert sind, den Nachnamen auswählen.

Hinweis: Auch wenn der Wert des Typs in „Explain“ nicht „Index“ ist, kann nach Index sortiert werden. wie:

Erläutern Sie die Auswahl der ID bei Personen, bei denen die ID > 3 ist.
Erläutern Sie „Select ID, Nachname“ aus Personen, bei denen die ID >3 ist. Sortieren nach ID absteigend.

2. Dateisortierung

Unter Dateisortierung versteht man das Sortieren des Abfrageergebnissatzes durch zusätzliche Operationen und die anschließende Rückgabe an den Client. Diese Sortiermethode verwendet keine Indexsortierung und ist weniger effizient. Allerdings werden bei der Dateisortierung, die MySQL „Filesort“ nennt, nicht unbedingt Datenträgerdateien verwendet.

Wenn der Wert des Extra-Parameters in „Explain“ die Zeichenfolge „Using filesort“ enthält, bedeutet dies, dass die Dateisortierung ausgeführt wird. An dieser Stelle müssen Sie den Index oder die SQL-Abfrageanweisung optimieren. wie:

Erläutern Sie die Auswahl von ID, Nachname und Vorname aus Personen, bei denen die ID > 3 ist. Sortieren Sie nach Nachname.

MySQL kann denselben Index sowohl für Suchvorgänge als auch für Abfragen verwenden. Wenn möglich, sollten Sie beim Entwerfen des Index versuchen, beide Operationen so weit wie möglich zu erfüllen.

Die Indexsortierung kann nur verwendet werden, wenn die Indexspalten die Felder in der Where-Bedingung und die Felder in Order By enthalten und die Reihenfolge der Spalten im Index mit der Reihenfolge aller in Where + Order By enthaltenen Felder übereinstimmt (Hinweis: Order By kommt nach Where).

Optimieren wir nun die obige SQL-Anweisung, um die Indexsortierung zu nutzen.

Fügen Sie zunächst einen mehrspaltigen Index hinzu.

Tabelle ändern, Personen hinzufügen, Schlüssel (ID, Nachname);

Sie werden feststellen, dass Sie die Indexsortierung immer noch nicht verwenden können, wenn Sie nur den Schlüssel (ID, Nachname) hinzufügen. Dies liegt daran, dass die Anweisung „where + order by“ auch die Anforderung des am weitesten links stehenden Präfixes des Index erfüllen muss und „where id > 3“ eine Bereichsbedingung ist, was dazu führt, dass die nachfolgende Anweisung „order by last_name“ den Indexschlüssel (ID, Nachname) nicht verwenden kann.

Zweitens ändern Sie die Reihenfolge nach Nachname in der SQL-Anweisung in die Reihenfolge nach ID, Nachname.

Hinweis: Wenn es sich bei der SQL-Abfrageanweisung um eine Join-Abfrage handelt, die mehrere Tabellen verbindet, kann die Indexsortierung nur verwendet werden, wenn alle Felder in der Reihenfolge aus der ersten Tabelle stammen.

In den folgenden Situationen kann die Indexsortierung nicht verwendet werden:

1. Wenn „order by“ nach mehreren Feldern sortiert, die Sortierrichtung der mehreren Felder jedoch inkonsistent ist, d. h. einige Felder sind asc (aufsteigend, standardmäßig aufsteigend) und einige Felder sind desc (absteigend). wie:

Erklären Sie „Select * from people where last_name='Allen'“, sortieren Sie nach Vorname aufsteigend, Geburtstag absteigend;

2. Wenn die Sortierung ein Feld enthält, das nicht in der Indexspalte enthalten ist. wie:

Erklären Sie „Select * from people where last_name='Allen'“, sortieren Sie nach Vorname, Geschlecht;

3. Wenn die erste Spalte der Indexspalte eine Bereichssuchbedingung ist. wie:

Erklären Sie „Select * from people where last_name like 'A%'“ (Auswahl * von Personen, bei denen Nachname wie „A%“ ist), sortiert nach Vorname;

4. Für diesen Fall kann die SQL-Anweisung wie folgt optimiert werden:

Erklären Sie „Select * from people where last_name like 'A%'“ (Auswahl * von Personen, bei denen Nachname wie ‚A%‘ ist), sortieren Sie nach Nachname, Vorname;

2.8 Redundante und doppelte Indizes

MySQL ermöglicht das Erstellen mehrerer Indizes für dieselbe Spalte (die Namen der Indizes sind jedoch unterschiedlich). Da MySQL doppelte Indizes separat verwalten muss und der Optimierer sie bei der Optimierung von Abfragen auch einzeln analysieren und berücksichtigen muss, wirken sich doppelte Indizes auf die Leistung aus.

Doppelte Indizes sind Indizes desselben Typs, die für dieselben Spalten in derselben Reihenfolge erstellt werden. Die Erstellung doppelter Indizes sollte vermieden und diese sofort nach Entdeckung gelöscht werden.

Redundante Indizes unterscheiden sich von doppelten Indizes. Wenn Sie einen Indexschlüssel (a, b) erstellen und dann einen Indexschlüssel (a) erstellen, ist dies ein redundanter Index. Weil Index (a) nur ein Präfixindex des vorherigen Index ist. Index (a, b) kann auch als Index (a) verwendet werden. Wenn Sie jedoch einen anderen Index (b, a) erstellen, ist dies kein redundanter Index mehr.

Redundante Indizes treten normalerweise auf, wenn einer Tabelle neue Indizes hinzugefügt werden. Zum Beispiel könnte man einen neuen Index (a, b) hinzufügen, anstatt den vorhandenen Index (a) zu erweitern. Ein weiterer Fall besteht darin, einen Sekundärindex (a) auf (a, ID) zu erweitern, wobei ID der Primärschlüssel ist.

In den meisten Fällen sind keine redundanten Indizes erforderlich. Sie sollten versuchen, vorhandene Indizes zu erweitern, anstatt neue zu erstellen. Manchmal werden redundante Indizes aus Leistungsgründen benötigt, da die Erweiterung eines vorhandenen Index ihn größer macht, was die Leistung anderer Abfrageanweisungen beeinflusst, die den Index verwenden.

Seien Sie vorsichtig, wenn Sie die Indizes erweitern. Da die Blattknoten des Sekundärindex die Primärschlüsselwerte enthalten, entspricht ein Index in Spalte (a) einem Index auf (a, id). Wenn jemand eine Abfrage verwendete, wie bei einer ID a = 5 -Reihenfolge, wäre der Index (a) sehr nützlich. Wenn Sie jedoch den Index (a) in den Index (a, b) ändern, wird der Index (a, b, id).

Es wird empfohlen, das PT-Upgrade-Tool aus der Percona Toolbox zu verwenden, um geplante Indexänderungen zu überprüfen.

Erweitern Sie daher nur einen vorhandenen Index, wenn Sie über alle Abfragen im Zusammenhang mit einem Index klar sind. Andernfalls ist das Erstellen eines neuen Index (der den ursprünglichen Index mit dem neuen Index redundant macht) die sicherste Methode.

2.9 Unbenutzte Indizes

Es kann einige Indizes auf dem MySQL -Server geben, die niemals verwendet werden. Es ist jedoch zu beachten, dass die eindeutige Einschränkung des eindeutigen Index darin besteht, dass ein eindeutiger Index in einer Abfrage noch nie verwendet wurde, aber es kann verwendet werden, um doppelte Daten zu vermeiden.