Die Welt der relationalen Datenbanktheorie wird von Tabellen und Mengen sowie von Operationen auf diesen Tabellen und Mengen beherrscht. Eine Datenbank ist eine Menge von Tabellen, und eine Tabelle ist eine Menge aus Zeilen und Spalten. Wenn Sie mithilfe einer SELECT-Anfrage Zeilen aus einer Tabelle abrufen, erhalten Sie ebenfalls eine Menge aus Zeilen und Spalten. Dabei handelt es sich um abstrakte Konzepte, die nichts mit der einem Datenbanksystem zugrunde liegenden Darstellung der Daten für die Verarbeitung Ihrer Tabellen zu tun haben. Eine weitere Abstraktion ist, dass die Operationen auf Tabellen alle gleichzeitig erfolgen; Anfragen kann man sich als eine Menge von Operationen vorstellen, und in der Mengenlehre gibt es kein Zeitkonzept.
Im wirklichen Leben sieht es natürlich ganz anders aus. Datenbankmanagement-Systeme implementieren abstrakte Konzepte, jedoch auf realer Hardware und unter Berücksichtigung der realen Beschränkungen. Anfragen brauchen also Zeit – manchmal sehr viel Zeit. Und weil wir ungeduldige Wesen sind, die auf nichts warten können, verlassen wir die abstrakte Welt der unmittelbaren mathematischen Operationen in großer Zahl und suchen nach Verfahren, um unsere Anfragen zu beschleunigen.
Glücklicherweise gibt es entsprechende Möglichkeiten. Wir legen einen Index auf Tabellen, sodass der Datenbankserver die Zeilen schneller findet. Wir überlegen, wie wir Anfragen so formulieren können, dass sie diese Indizes optimal nutzen können. Wir schreiben Anfragen so, dass der Einplanungsmechanismus des Servers genutzt wird, damit die Anfragen verschiedener Clients besser kooperieren können. Wir denken darüber nach, was auf der benutzten Hardware passiert und wie wir ihre physischen Beschränkungen umgehen und damit die Performance verbessern können.
Um genau diese Probleme dreht sich diese Artikelserie. Sie soll Ihnen helfen, die Performance Ihres Datenbanksystems zu optimieren, sodass Ihre Anfragen so schnell wie möglich verarbeitet werden. MySQL ist schon relativ schnell, aber selbst die schnellste Datenbank kann Anfragen schneller ausführen, wenn Sie ihr dabei helfen.
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |
Richtige Nutzung von Indizes
Als Erstes werden wir uns mit den Indizes beschäftigen, weil sie das wichtigste Werkzeug zur Beschleunigung Ihrer Anfragen darstellen. Es gibt auch noch andere Techniken, aber deutlich bessere Ergebnisse erzielen Sie durch die richtige Nutzung von Indizes. Abonnenten der MySQL-Mailingliste bitten oft um Hilfe, um ihre Anfragen schneller zu machen. Bei überraschend vielen der betroffenen Tabellen sind keine Indizes vorhanden, und die Erstellung des Index löst das Problem häufig sofort.
Es funktioniert nicht immer, weil die Optimierung nicht immer einfach ist. Wenn Sie jedoch keine Indizes verwenden, vergeuden Sie, wenn Sie versuchen, mit anderen Mitteln eine bessere Performance zu erzielen, vorwiegend Ihre Zeit. Erstellen Sie als Erstes einen Index, um wirklich große Performance-Steigerungen zu erhalten, und versuchen Sie dann, das Ganze mithilfe anderer Techniken weiter zu verfeinern.
Dieser Artikel beschreibt, was ein Index ist, wie er die Anfrageleistung verbessert, unter welchen Umständen Indizes die Performance verschlechtern können und wie Sie den richtigen Index für Ihre Tabellen auswählen. Im nächsten Artikel geht es um die Anfrageoptimierung von MySQL. Sie sollten die Arbeitsweise der Anfrageoptimierung verstehen und auch wissen, wie man Indizes anlegt, weil Sie den Indexmechanismus dann besser nutzen können. Bei bestimmten Anfrageformulierungen sind Indizes nicht sinnvoll, was Sie natürlich größtenteils vermeiden sollten. (Dies ist aber nicht immer der Fall: Manchmal sollten Sie das Verhalten der Anfrageoptimierung auch überschreiben. Auch solche Fälle werden hier vorgestellt.)
Vorteile der Indizierung
Um die Arbeitsweise der Indizes zu verstehen, beginnen wir zunächst mit einer Tabelle ohne Index. Eine nicht indizierte Tabelle ist nichts anderes als eine ungeordnete Ansammlung von Zeilen. Abbildung 1 beispielsweise zeigt die Tabelle ad. Diese Tabelle hat keinen Index. Wenn wir nach den Zeilen für eine bestimmte Firma suchen wollen, müssen wir jede Zeile in der Tabelle durchsuchen, um zu überprüfen, ob es eine Übereinstimmung mit dem gewünschten Wert gibt. Dabei muss die gesamte Tabelle durchsucht werden, was langsam und außerdem hoffnungslos ineffizient ist, wenn die Tabelle umfangreich ist, aber nur wenige Datensätze enthält, die den Suchkriterien entsprechen.
Abbildung 2 zeigt dieselbe Tabelle, jetzt jedoch mit einem Index für die Spalte company_num. Der Index enthält für jede Zeile in der Tabelle ad einen Eintrag, aber die Indexeinträge sind nach dem Wert von company_num sortiert. Anstatt jetzt die ganze Tabelle zeilenweise durchsuchen zu müssen, wenn nach einer Übereinstimmung gesucht wird, verwenden wir einfach den Index.
Angenommen, wir suchen alle Zeilen für die Firma 13. Wir durchsuchen den Index und finden drei Zeilen für diese Firma. Danach gelangen wir zu der Zeile für die Firma 14, einem höheren Wert als dem gesuchten. Die Indexwerte liegen also sortiert vor, wenn wir den Datensatz mit dem Wert 14 erreicht haben. Dann wissen wir, dass es keine weiteren Übereinstimmungen mehr geben wird, und können die Suche abbrechen. Dies ist einer der wesentlichen Vorteile eines Index.
Schnellere Suche
Wenn wir nach einem Wert suchen, der erst irgendwo in der Mitte der Indextabelle liegt, gibt es Positionierungsalgorithmen, um den ersten übereinstimmenden Indexeintrag zu finden, ohne die Tabelle linear durchsuchen zu müssen (beispielsweise eine binäre Suche). Auf diese Weise können wir eine schnelle Positionierung auf den ersten übereinstimmenden Wert vornehmen und sparen uns viel Zeit bei der Suche.
Datenbanken verwenden unterschiedliche Techniken für die schnelle Positionierung auf einen Indexwert, aber welche das genau sind, ist hier nicht so wichtig. Wichtig ist, dass sie funktionieren und dass die Indizierung etwas Gutes ist.
Vielleicht fragen Sie sich, warum wir nicht einfach die Datendatei sortieren und auf die Indexdatei verzichten. Hätte dies nicht die gleiche Steigerung der Suchgeschwindigkeit zur Folge? Dies wäre tatsächlich der Fall, wenn die Tabelle einen einzelnen Index hätte. Aber vielleicht wollen Sie einen zweiten Index hinzufügen – z.B. einen für die Kundennamen und einen zweiten für die Kunden- oder Telefonnummern –, und Sie können die Datendatei sicher nicht auf zwei verschiedene Arten gleichzeitig sortieren.
Die Verwendung von Indizes als von der Datendatei getrennte Einheiten löst dieses Problem und ermöglicht die Erstellung mehrerer Indizes. Außerdem sind Zeilen im Index in der Regel kürzer als Datenzeilen. Wenn Sie neue Werte hinzufügen oder löschen, dann ist es einfacher, die kürzeren Indexwerte zu verschieben als die langen Datenzeilen.
Indizierte Tabellen
Das beschriebene Beispiel entspricht weitgehend der Art und Weise, wie MySQL Tabellen indiziert, auch wenn die einzelnen Details je nach Tabellentyp ein wenig voneinander abweichen. Bei MyISAM- und ISAM-Tabellen werden die Datenzeilen der Tabelle in einer Datendatei abgelegt, die Indexwerte jedoch in einer Indexdatei. Eine Tabelle kann mehrere Indizes haben; sie alle werden innerhalb ein und derselben Indexdatei abgelegt. Jeder Index in der Indexdatei besteht aus einem sortierten Array aus Datensätzen mit Schlüsseln, die für den schnellen Zugriff auf die Datendatei genutzt werden.
Im Gegensatz dazu werden Datenzeilen und Indexwerte bei BDB- und InnoDB-Tabellen nicht auf diese Weise getrennt, auch wenn beide Tabellentypen Indizes als Mengen mit sortierten Werten behandeln. Der BDB-Handler verwendet pro Tabelle eine einzelne Datei, in der sowohl Daten- als auch Indexwerte abgelegt werden; der InnoDBHandler benutzt einen einzelnen Tablespace, innerhalb dessen er den Speicherplatz für Daten und Indizes für alle InnoDB-Tabellen verwaltet.
Die obige Darstellung beschreibt den Vorteil eines Index bei Anfragen, die sich nur auf eine einzige Tabelle beziehen, wobei durch die Nutzung des Index die Suche wesentlich beschleunigt wird, weil die Tabelle nicht mehr vollständig durchsucht werden muss. Noch wertvoller sind die Indizes aber, wenn Sie Anfragen mit Joins über mehrere Tabellen ausführen. Bei Anfragen innerhalb einer einzigen Tabelle ist die Anzahl der Werte, die Sie pro Spalte auswerten müssen, gleich der Anzahl der Zeilen in der Tabelle. Bei einer Anfrage über mehrere Spalten steigt die Anzahl möglicher Kombinationen rapide, weil sie das Produkt der Zeilen in den Tabellen darstellen.
Anfragen über mehrere Tabellen
Angenommen, Sie haben drei Tabellen t1, t2 und t3, die jeweils eine Spalte c1, c2 und c3 mit je 1.000 Zeilen enthalten, in denen wiederum die Zahlen 1 bis 1000 abgelegt sind. Eine Anfrage, mit der Sie alle Kombinationen der Tabellenzeilen ermitteln, in denen gleiche Werte abgelegt sind, würde wie folgt aussehen:
SELECT t1.c1, t2.c2, t3.c3
FROM t1, t2, t3
WHERE t1.c1 = t2.c2 AND t1.c1 = t3.c3;
Das Ergebnis dieser Anfrage sollte 1.000 Zeilen umfassen, die jeweils drei gleiche Werte enthalten. Wenn wir die Anfrage ohne die Verwendung von Indizes ausführen, wissen wir nicht, welche Zeilen welche Werte enthalten. Wir müssen also alle möglichen Kombinationen ausprobieren, um diejenigen zu finden, die mit der WHERE-Klausel übereinstimmen. Die Anzahl möglicher Kombinationen ist 1000 × 1000 × 1000 (1 Milliarde!), also eine Million mal mehr als die Anzahl der Übereinstimmungen.
Das ist viel unnützer Aufwand, und diese Anfrage ist wahrscheinlich auch sehr langsam, sogar für eine schnelle Datenbank wie MySQL. Und das bei nur 1000 Zeilen pro Tabelle! Wie wird das erst aussehen, wenn Sie Tabellen mit Millionen von Zeilen haben? Sie sehen, dass die Dauer der Verarbeitung von Joins bei zunehmender Tabellengröße extrem hoch werden kann, wenn keine Indizes verwendet werden, was wiederum zu einer sehr schlechten Performance führt. Wenn wir hingegen für die Tabellen Indizes einführen, beschleunigen wir das Ganze wesentlich, weil die Anfrage nach der Indizierung wie folgt ausgeführt werden kann:
-
Die erste Zeile von Tabelle t1 wird ausgewählt, und ihr Wert wird ermittelt.
-
Anhand des Index von Tabelle t2 geht man direkt zu der Zeile, die mit dem Wert von t1 übereinstimmt. Auf dieselbe Weise wird der Index für Tabelle t3 verwendet, um dort direkt zu der Zeile zu gelangen, die mit dem Wert aus t1 übereinstimmt.
-
Die Verarbeitung wird mit der nächsten Zeile von t1 fortgesetzt. Die oben beschriebene Prozedur wird wiederholt, bis alle Zeilen in t1 ausgewertet wurden.
Weitere Operationen
In diesem Fall durchsuchen wir zwar Tabelle t1 immer noch vollständig, aber wir können für t2 und t3 indizierte Suchvorgänge ausführen, sodass die Zeilen aus diesen Tabellen direkt angesprochen werden. Die Anfrage läuft etwa eine Million mal schneller – kein Witz! (Dieses Beispiel ist natürlich trivial. Es soll nur der Veranschaulichung dienen; die dabei aufgezeigten Probleme sind jedoch echt. Wenn Sie eine Tabelle mit Indizes versehen, können Sie die Leistungsfähigkeit wirklich drastisch verbessern.)
MySQL verwendet die Indizes in der beschriebenen Weise, um das Suchen nach Zeilen zu beschleunigen, die mit dem Inhalt einer WHERE-Klausel übereinstimmen oder die bei Joins mit Zeilen in anderen Tabellen übereinstimmen. Außerdem werden Indizes verwendet, um die Performance anderer Operationen zu verbessern:
-
Der kleinste oder größte Wert einer indizierten Spalte wird schnell mithilfe der Funktionen
MIN()oderMAX()gefunden, ohne jede Zeile auswerten zu müssen. -
MySQL kann die Indizes häufig nutzen, um Sortieroperationen für
ORDER BY- undGROUP BY-Klauseln schneller auszuführen. -
Manchmal braucht MySQL die Datendatei überhaupt nicht zu lesen. Angenommen, Sie wählen Werte aus einer indizierten numerischen Spalte einer MyISAM-Tabelle, aber keine anderen Spalten dieser Tabelle aus. In diesem Fall haben Sie mit dem Lesen eines Indexwerts bereits den Wert, den Sie auch erhalten würden, wenn Sie die Datendatei lesen würden. Es gibt keinen Grund, Werte zweimal zu lesen, deshalb muss die Datendatei für diese Operation nicht herangezogen werden.
Nachteile der Indizierung
Im Allgemeinen wird eine Anfrage schneller ausgeführt, wenn MySQL erkennt, wie es einen Index dafür nutzen kann. Wenn Sie Ihre Tabelle nicht indizieren, schaden Sie sich also damit nur selbst. Wie Sie gesehen haben, habe ich die Nutzung der Indizes in den schönsten Farben geschildert. Gibt es Nachteile? Ja. In der Praxis werden diese Nachteile durch die Vorteile aufgewogen, aber Sie sollten wissen, worum es sich dabei handelt.
Erstens belegen Indexdateien Speicherplatz, und mehrere Indizes entsprechend mehr Speicherplatz. Das kann dann dazu führen, dass Sie die zulässige Tabellengröße schneller erreichen, als wenn keine Indizes vorhanden wären:
-
Bei ISAM- und MyISAM-Tabellen kann eine umfassende Indizierung der Tabelle dazu führen, dass die Indexdatei die maximale Dateigröße schneller erreicht als die Datendatei.
-
Bei BDB-Tabellen, die Daten- und Indexwerte in ein und derselben Datei speichern, führt das Hinzufügen eines Index ganz sicher dazu, dass die maximale Dateigröße schneller erreicht wird.
-
InnoDB-Tabellen nutzen den im Tablespace vorhandenen Speicherplatz gemeinsam. Das Hinzufügen von Indizes führt also zu einer schnelleren Erschöpfung des Tablespace. Allerdings können Sie, wenn Sie auf der Festplatte noch über Speicherkapazität verfügen, den Tablespace durch das Hinzufügen neuer Komponenten erweitern (anders als bei Dateien für ISAM-, MyISAM- und BDB-Tabellen ist der InnoDB-Tablespace nicht an die Dateigrößenbegrenzung Ihres Betriebssystems gebunden, da er aus mehreren Dateien bestehen kann).
Zweitens beschleunigen Indizes zwar die Suche, verlangsamen aber Einfüge- und Löschoperationen ebenso wie die Aktualisierung von Werten in indizierten Spalten (d.h. die meisten Operationen, bei denen etwas geschrieben wird), weil beim Schreiben jetzt nicht nur die Datenzeile berücksichtigt werden muss, sondern häufig auch der Index. Je mehr Indizes eine Tabelle hat, desto größer ist die durchschnittliche Performance-Verschlechterung für Schreiboperationen. Im Teil 4 der Artikelserie finden Sie detaillierte Informationen zu diesen Performance-Problemen und Hinweise dazu, was Sie dagegen tun können.
Indizes auswählen
Ich setze voraus, dass Ihnen die Syntax zum Erstellen von idizes bekannt ist. Aber allein die Syntax hilft Ihnen nicht bei der Entscheidung, wie Ihre Tabellen indiziert werden sollen. Dazu müssen Sie überlegen, wie Ihre Tabellen genutzt werden. Dieser Abschnitt gibt Ihnen Anhaltspunkte dafür, wie Sie Spalten erkennen, die für die Indizierung geeignet sind, und wie Sie Ihre Indizes am besten erstellen:
Indizieren Sie Spalten zur Suche, Sortierung und Gruppierung von Daten, nicht jedoch Spalten zur Anzeige von Ausgaben
Mit anderen Worten: Die am besten für die Indizierung geeigneten Spalten sind diejenigen, die in Ihrer WHERE-Klausel erscheinen, oder Spalten, die in JOIN-, ORDER BY- und GROUP BY-Klauseln angegeben werden. Ungeeignet sind Spalten, die in der Auswahlliste nach dem Schlüsselwort SELECT aufgeführt werden:
SELECT
col_a ungeeignet
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c geeignet
WHERE
col_d = expr; geeignet
Die Spalten, die Sie zur Anzeige auswählen, und diejenigen, die Sie in der WHERE-Klausel angeben, können natürlich die gleichen sein. Wichtig ist, dass das Vorhandensein einer Spalte in der Ausgabespaltenliste kein relevanter Hinweis darauf ist, dass ein Index dafür angelegt werden sollte.
Spalten, die in Join-Klauseln oder Ausdrücken der Form col1 = col2 in WHERE-Klauseln auftreten, sind außerordentlich gut für die Indizierung geeignet. col_b und col_c in der obigen Anfrage sind Beispiele dafür. Wenn MySQL eine Anfrage unter Verwendung von mit JOIN verknüpften Spalten optimieren kann, reduziert es damit die möglichen Tabellen-/Zeilen-Kombinationen erheblich, weil Tabellen nicht mehr vollständig durchsucht werden.
Verwenden Sie eindeutige Indizes
Betrachten Sie die Verteilung von Werten innerhalb einer Spalte. Indizes funktionieren am besten für Spalten mit eindeutigen Werten und am schlechtesten für Spalten mit vielen mehrfach auftretenden Werten. Enthält eine Spalte beispielsweise Altersangaben und treten sehr viele verschiedene Werte auf, dann kann ein Index die Zeilen sinnvoll differenzieren.
Ein Index ist nicht sinnvoll, wenn eine Spalte das Geschlecht aufzeichnet und nur die beiden Werte M und W aufnehmen kann (egal nach welchem Wert Sie suchen, Sie werden immer noch etwa die Hälfte der Zeilen erhalten). Unter diesen Umständen wird der Index niemals benutzt werden, da die Anfrageoptimierung in der Regel das vollständige Durchsuchen der Tabelle vorzieht, wenn sie feststellt, dass ein Wert in mehr als etwa 30 Prozent der Tabellenzeilen auftaucht.
Indizieren Sie kurze Werte
Falls Sie eine Stringspalte indizieren, geben Sie eine Präfixlänge an, bei der das in Betracht kommt. Angenommen, Sie haben eine CHAR(200)-Spalte, dann sollten Sie keinen Index über die ganze Spalte legen, wenn sich die meisten Werte innerhalb der ersten zehn oder zwanzig Bytes unterscheiden. Durch die Indizierung der ersten zehn oder zwanzig Bytes sparen Sie im Index viel Platz und machen wahrscheinlich auch Ihre Anfragen schneller. Ein kleinerer Index verursacht weniger Festplattenaktivität, und kürzere Werte können schneller verglichen werden.
Außerdem können, was noch wichtiger ist, bei der Verwendung kürzerer Schüsselwerte die Blöcke im Indexcache mehr Schlüsselwerte aufnehmen, sodass MySQL mehr Schlüssel gleichzeitig im Speicher vorhalten kann. Damit ist es wahrscheinlicher, Zeilen zu finden, ohne zusätzliche Blöcke aus dem Index lesen zu müssen. (Hier sollten Sie natürlich auch Ihren gesunden Menschenverstand einsetzen. Durch die Indizierung des ersten Zeichens einer Spalte werden Sie nicht viel gewinnen, weil es dann nicht sehr viele unterschiedliche Werte im Index gibt.)
Nutzen Sie linksseitige Präfixe
Wenn Sie einen n-spaltigen Index anlegen, erzeugen Sie eigentlich n Indizes, die MySQL nutzen kann. Ein mehrspaltiger Index stellt deshalb mehrere Indizes dar, weil alle Spaltenmengen von links im Index genutzt werden können, um übereinstimmende Spalten zu finden. Diese Menge wird auch als linksseitiges Präfix bezeichnet (hier liegt ein Unterschied zur Indizierung eines Spaltenpräfix vor, denn dabei werden die ersten n Bytes der Spalte als Indexwerte verwendet).
Angenommen, Sie haben eine Tabelle mit einem Index für die Spalten land, stadt und plz. Die Zeilen im Index werden in der Reihenfolge land, stadt, plz sortiert, sodass sie automatisch auch nach land/stadt oder land sortiert sind. Das bedeutet: MySQL kann den Index nutzen, auch wenn Sie in einer Anfrage insbesondere nach land-Werten suchen oder nur nach land- und stadt-Werten. Damit kann der Index genutzt werden, um die folgenden Spaltenkombinationen zu suchen:
land, stadt, plz
land, stadt
land
MySQL kann den Index jedoch nicht für Suchaktionen verwenden, die kein linksseitiges Präfix verwenden. Wenn Sie beispielsweise nach stadt oder nach plz suchen, wird der Index nicht benutzt. Wenn Sie nach einem bestimmten Land suchen und eine Postleitzahl eingeben (Spalten 1 und 3 des Index), kann der Index nicht für die Wertekombinationen genutzt werden. MySQL kann die Suche jedoch mithilfe des Index eingrenzen, um Zeilen zu finden, die mit dem Land übereinstimmen.
Übertreiben Sie es nicht mit den Indizes
Legen Sie nicht einfach nach dem Motto »je mehr, desto besser« für alles Mögliche einen Index an. Jeder zusätzliche Index belegt zusätzlichen Speicherplatz und wirkt sich, wie bereits erwähnt, negativ auf die Performance der Schreiboperationen aus. Indizes müssen bei Änderungen der Tabelleninhalte Ihrer Datenbank aktualisiert und möglicherweise reorganisiert werden, und je mehr Indizes Sie haben, desto länger dauert das.
Wenn Sie einen Index angelegt haben, den Sie nur selten oder nie benutzen, dann verlangsamt dieser Index die Tabellenaktualisierung nur unnötig. Darüber hinaus berücksichtigt MySQL Indizes beim Anlegen eines Ausführungsplans für Suchoperationen. Durch das Anlegen eines zusätzlichen Index entsteht mehr Arbeit für die Anfrageoptimierung. Außerdem ist es möglich (wenn auch unwahrscheinlich), dass MySQL beim Vorhandensein zu vieler Indizes nicht in der Lage ist, den besten davon auszuwählen. Behalten Sie nur die Indizes bei, die Sie wirklich brauchen, um auf diese Weise Fehler bei der Anfrageoptimierung zu vermeiden.
Wenn Sie einen Index für eine Tabelle anlegen möchten, für die es bereits einen Index gibt, sollten Sie sich fragen, ob der neue Index nicht nur ein linksseitiges Präfix eines bereits existierenden mehrspaltigen Index ist. Ist dies der Fall, dann sollten Sie den neuen Index nicht erstellen, denn Sie haben ihn ja bereits (wenn Sie also einen Index für land, stadt, plz haben, dann ist es sinnlos, noch einen zusätzlichen Index für das Land zu erstellen).
Berücksichtigen Sie, welche Vergleiche für die Spalten vorgenommen werden
Indizes werden für die Operationen <, <=, =, >=, > und BETWEEN verwendet. Außerdem werden sie in LIKE-Operationen eingesetzt, wenn das Muster ein literales Präfix aufweist. Wenn Sie eine Spalte nur für andere Operationen verwenden (z.B. STRCMP()), dann ist es nicht sinnvoll, einen Index dafür anzulegen. Bei HEAP-Tabellen werden die Indizes in Hashs unterteilt und nur für Gleichheitsüberprüfungen benutzt; wenn Sie bei einer HEAPTabelle eine Bereichssuche durchführen (beispielsweise a < b), dann hilft Ihnen ein Index dabei nicht weiter.
Verwenden Sie das Slow-Query-Log, um Leistungseinbußen durch langsame Anfragen zu ermitteln
Mithilfe dieser Logdatei können Sie Anfragen ermitteln, die von einer Indizierung profitieren könnten. Sie können sich die Logdatei mit dem Dienstprogramm mysqldumpslow anzeigen lassen. Erscheint eine bestimmte Anfrage immer wieder in der Logdatei für langsame Anfragen, dann ist die Wahrscheinlichkeit hoch, dass diese Anfrage nicht optimal formuliert ist; vielleicht können Sie sie so optimieren, dass die Ausführung schneller erfolgt. Beachten Sie bei der Einschätzung Ihrer Logdatei für langsame Anfragen die folgenden Aspekte:
-
»Langsam« wird in Echtzeit gemessen. Wenn Ihr Server also stark belastet wird, erscheinen mehr Anfragen in dieser Logdatei, als wenn er nicht viel zu tun hat. Dies ist unbedingt zu berücksichtigen.
-
Wenn Sie zusätzlich zur Aktivierung der Protokollierung langsamer Anfragen die Option
--log-long-formatverwenden, wird die Logdatei auch solche Anfragen enthalten, die ohne Verwendung eines Index ausgeführt werden. Diese Anfragen müssen aber nicht unbedingt langsam sein (beispielsweise wird für kleine Tabellen kein Index benötigt).
Ausblick
Dieser erste Teil der Serie hat sich damit beschäftig, wie Indizes sinnvoll eingesetzt werden. Im nächsten Teil behandeln wir die Arbeitsweise der Anfrageoptimierung. (mzu)
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |