Anweisungen zur Verwendung von MySQL-Indizes (einspaltiger Index und mehrspaltiger Index)

1. Einspaltiger Index

Die Auswahl der Spalten, für die Indizes erstellt werden sollen, ist einer der wichtigsten Schritte im Leistungsoptimierungsprozess. Es gibt zwei Haupttypen von Spalten, die für die Indizierung berücksichtigt werden können: Spalten, die in der WHERE-Klausel vorkommen, und Spalten, die in der JOIN-Klausel vorkommen. Betrachten Sie die folgende Abfrage:

Wählen Sie ein Alter. ## Verwenden Sie keinen Index von Personen mit Vorname='Mike'. ## Erwägen Sie die Verwendung eines Index UND Nachname='Sullivan'. ## Erwägen Sie die Verwendung eines Index

Diese Abfrage unterscheidet sich geringfügig von der vorherigen, ist aber immer noch einfach. Da im Select-Teil auf das Alter verwiesen wird, verwendet MySQL es nicht, um die Spaltenauswahl einzuschränken. Daher ist es für diese Abfrage nicht erforderlich, einen Index für die Altersspalte zu erstellen.

Hier ist ein komplexeres Beispiel:

Wählen Sie people.age, ##Verwenden Sie keinen Index town.name ##Verwenden Sie keinen Index FROM people LEFT JOIN town ON people.townid=town.townid ##Verwenden Sie ggf. einen Index Where firstname='Mike' ##Verwenden Sie ggf. einen Index AND lastname='Sullivan' ##Verwenden Sie ggf. einen Index

Da wie im vorherigen Beispiel „firstname“ und „lastname“ in der Where-Klausel vorkommen, ist es immer noch notwendig, Indizes für diese beiden Spalten zu erstellen. Da außerdem die Spalte „TownID“ der Tabelle „Town“ in der Join-Klausel erscheint, müssen wir die Erstellung eines Indexes für diese Spalte in Betracht ziehen.

Können wir also einfach davon ausgehen, dass jede Spalte, die in der Where-Klausel und der Join-Klausel erscheint, indiziert werden sollte? Fast, aber nicht ganz. Wir müssen auch den Typ des Operators berücksichtigen, der zum Vergleichen der Spalten verwendet wird. MySQL verwendet Indizes nur für die folgenden Operatoren: <, <=, =, >, >=, BETWEEN, IN und manchmal LIKE.

Ein Index kann in einer LIKE-Operation verwendet werden, wenn der andere Operand nicht mit einem Platzhalterzeichen (% oder _) beginnt.

Zum Beispiel:

Wählen Sie die Personen-ID AUS Personen mit Vornamen LIKE 'Mich%'

Diese Abfrage verwendet den Index. Die folgende Abfrage tut dies jedoch nicht.

Wählen Sie die Personen-ID AUS Personen aus, wobei der Vorname LIKE '%ike' ist;

2. Mehrspaltiger Index

Der Index kann ein einspaltiger oder ein mehrspaltiger Index sein. Nachfolgend verdeutlichen wir anhand konkreter Beispiele den Unterschied zwischen diesen beiden Indizes. Angenommen, es gibt eine Personentabelle wie diese:

Erstellen Sie TABLE-Personen (  
peopleid SMALLINT NICHT NULL AUTO_INCREMENT,  
Vorname CHAR(50) NOT NULL,  
Nachname CHAR(50) NOT NULL,  
Alter SMALLINT NICHT NULL,  
Stadt-ID SMALLINT NICHT NULL,  
PRIMÄRSCHLÜSSEL (Personen-ID));

Hier sind die Daten, die wir in die Personentabelle eingefügt haben:

In diesem Datenausschnitt gibt es vier Personen mit dem Namen „Mikes“ (zwei mit dem Nachnamen Sullivans und zwei mit dem Nachnamen McConnells), zwei Personen im Alter von 17 Jahren und eine Person mit dem ungewöhnlichen Vornamen Joe Smith.

Der Hauptzweck dieser Tabelle besteht darin, die entsprechende Personen-ID basierend auf dem Vornamen, Nachnamen und Alter des angegebenen Benutzers zurückzugeben. Beispielsweise möchten wir möglicherweise die Personen-ID eines Benutzers namens Mike Sullivan herausfinden, der 17 Jahre alt ist:

Personen-ID auswählen 
VON Menschen  
Wobei Vorname='Mike'  
   UND Nachname='Sullivan' UND Alter=17;

Da MySQL bei jeder Ausführung einer Abfrage nicht die gesamte Tabelle scannen soll, müssen wir die Verwendung eines Indexes in Betracht ziehen.

Zunächst können wir die Erstellung eines Index für eine einzelne Spalte in Betracht ziehen, beispielsweise für Vorname, Nachname oder Alter. Wenn wir einen Index für die Spalte „Vorname“ erstellen (Alter TABLE people ADD INDEX firstname (Vorname);), verwendet MySQL diesen Index schnell, um den Suchbereich auf die Datensätze zu beschränken, bei denen Vorname=„Mike“ ist, und führt dann andere Suchbedingungen für diesen „Zwischenergebnissatz“ aus: Zuerst schließt es die Datensätze aus, bei denen Nachname ungleich „Sullivan“ ist, und schließt dann die Datensätze aus, bei denen das Alter ungleich 17 ist. Wenn die Datensätze alle Suchbedingungen erfüllen, gibt MySQL die endgültigen Suchergebnisse zurück.

Da die Spalte „Vorname“ indiziert ist, ist MySQL viel effizienter als ein vollständiger Scan der Tabelle. Dennoch muss MySQL weitaus mehr Datensätze scannen, als eigentlich nötig sind. Obwohl wir den Index der Spalte „Vorname“ löschen und dann einen Index für die Spalte „Nachname“ oder „Alter“ erstellen können, ist die Suchleistung insgesamt ähnlich, unabhängig davon, für welche Spalte der Index erstellt wird.

Um die Sucheffizienz zu verbessern, müssen wir die Verwendung mehrspaltiger Indizes in Betracht ziehen. Wenn Sie einen mehrspaltigen Index für Vorname, Nachname und Alter erstellen, kann MySQL mit nur einer Suche das richtige Ergebnis finden! Hier ist der SQL-Befehl zum Erstellen dieses mehrspaltigen Indexes:

Altartisch Leute  
ADD INDEX fname_lname_age (Vorname, Nachname, Alter);

Da die Indexdatei im B-Baum-Format gespeichert ist, kann MySQL sofort zum entsprechenden Vornamen, dann zum entsprechenden Nachnamen und schließlich zum entsprechenden Alter gehen. Ohne irgendwelche Datensätze in der Datendatei zu scannen, hat MySQL den Zieldatensatz korrekt gefunden!

Wenn wir also einspaltige Indizes für die drei Spalten Vorname, Nachname und Alter erstellen, ist der Effekt dann derselbe, als wenn wir einen mehrspaltigen Index für Vorname, Nachname und Alter erstellen?

Die Antwort ist nein, die beiden sind völlig unterschiedlich. Wenn wir eine Abfrage ausführen, kann MySQL nur einen Index verwenden. Wenn Sie drei einspaltige Indizes haben, versucht MySQL, den restriktivsten Index auszuwählen. Allerdings ist selbst der restriktivste einspaltige Index sicherlich viel weniger restriktiv als ein mehrspaltiger Index für die Spalten „Vorname“, „Nachname“ und „Alter“.

3. Ganz links stehendes Präfix in mehrspaltigen Indizes

Mehrspaltige Indizes bieten noch einen weiteren Vorteil, der sich im Konzept „Leftmost Prefixing“ widerspiegelt. Wenn wir das vorherige Beispiel weiter betrachten, haben wir jetzt einen mehrspaltigen Index für die Spalten „Vorname“, „Nachname“ und „Alter“, und wir nennen diesen Index „fname_lname_age“. MySQL verwendet den Index fname_lname_age, wenn die Suchbedingung eine Kombination der folgenden Spalten ist:

Vorname, Nachname, Alter
Vorname, Nachname
Vorname

Aus einer anderen Perspektive ist es gleichbedeutend mit der Erstellung von Indizes für die Kombination aus (Vorname, Nachname, Alter), (Vorname, Nachname) und (Vorname). Die folgenden Abfragen können alle den Index fname_lname_age verwenden:

Wählen Sie Personen-ID aus Personen  
Wobei Vorname='Mike' UND Nachname='Sullivan' UND Alter='17';

Wählen Sie Personen-ID aus Personen  
Wobei Vorname='Mike' UND Nachname='Sullivan';

Wählen Sie Personen-ID aus Personen  
Wobei Vorname='Mike';

Die folgenden Abfragen können den Index fname_lname_age nicht verwenden:

Wählen Sie Personen-ID aus Personen  
Wobei Nachname='Sullivan';

Wählen Sie Personen-ID aus Personen  
Wobei Alter = ,17‘;

Wählen Sie Personen-ID aus Personen  
Wobei Nachname='Sullivan' UND Alter='17';

Dieser Artikel endet hier. Später wird Ihnen der Herausgeber von 123WORDPRESS.COM weitere Artikel zum MySQL-Index vorstellen.