Wenn Sie eine Anfrage zur Auswahl von Zeilen absetzen, analysiert MySQL diese, um festzustellen, ob Optimierungen genutzt werden können, damit die Anfrage schneller zu verarbeiten ist. In diesem Artikel beschreiben wir die Arbeitsweise der Anfrageoptimierung. Mit diesem Wissen können Sie die Queries manuell weiter optimieren und die Geschwinigkeit der Datenbankabfragen erhöhen. Noch mehr Detailwissen finden Sie im Kapitel »MySQL-Optimierung« des MySQL-Handbuchs; dort werden verschiedene Optimierungsmethoden beschrieben, die MySQL verwendet.
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 |
Nutzung von Indizes
Die Anfrageoptimierung von MySQL nutzt natürlich die Indizes, zieht aber auch andere Informationen in Betracht. Die folgende Anfrage beispielsweise kann von MySQL sehr schnell ausgeführt werden, egal wie groß die Tabelle ist:
SELECT * FROM tbl_name WHERE 1 = 0;
In diesem Fall wertet MySQL die WHERE-Klausel aus, erkennt, dass keine Zeile diese Anfrage erfüllen kann, und macht sich gar nicht erst die Mühe, die Tabelle zu durchsuchen. Sie können dies mithilfe der EXPLAIN-Anweisung überprüfen, die MySQL anweist, Informationen darüber anzuzeigen, wie es eine SELECT-Anfrage ausführen würde, ohne diese tatsächlich auszuführen. Dazu schreiben Sie einfach nur das Wort EXPLAIN vor die SELECT-Anweisung:
mysql> EXPLAIN SELECT * FROM tbl_name WHERE 1 = 0;
+------------------+
| Comment |
+------------------+
| Impossible WHERE |
+------------------+
Normalerweise gibt EXPLAIN mehr Informationen zurück, unter anderem, welche Indizes verwendet werden, um Tabellen zu durchsuchen, die Typen der verwendeten Joins sowie einen Schätzwert, wie viele Zeilen in jeder Tabelle durchsucht werden müssten.
Wie die Optimierung funktioniert
Die Anfrageoptimierung von MySQL verfolgt mehrere Ziele. Am wichtigsten dabei ist, Indizes zu verwenden, wo immer das möglich ist, und dabei den restriktivsten Index zu wählen, um möglichst viele Zeilen möglichst schnell ausschließen zu können. Das hört sich kontraproduktiv an, weil Ihr Ziel bei der Ausführung einer SELECT-Anweisung darin besteht, Zeilen zu finden, nicht jedoch, sie auszuschließen. Der Grund für diese Arbeitsweise der Optimierung ist die Tatsache, dass die Ihrem Suchkriterium entsprechenden Zeilen umso schneller gefunden werden, je schneller die anderen Zeilen ausgeschlossen werden können. Anfragen werden schneller, wenn die restriktivsten Überprüfungen ganz am Anfang ausgeführt werden. Angenommen, Sie führen eine Anfrage aus, die zwei Spalten auswertet, die beide einen Index haben:
SELECT col3 FROM mytable
WHERE col1 = ‘value’ AND col2 = ‘noch ein value’;
Nehmen wir ferner an, dass bei der Auswertung von col1 900 Übereinstimmungen gefunden werden, bei der Auswertung von col2 300 Übereinstimmungen und dass die Kombination beider Auswertungen schließlich 30 Zeilen ergibt. Wenn Sie zuerst col1 auswerten, müssen Sie 900 Zeilen untersuchen, um die 30 zu finden, die auch mit dem Wert in col2 übereinstimmen. Das bedeutet 870 Überprüfungen, die keine Übereinstimmung bringen. Wenn Sie als Erstes col2 auswerten, müssen Sie nur 300 Zeilen auswerten, um die 30 Übereinstimmungen mit col1 zu finden, es gibt also 270 Überprüfungen ohne Übereinstimmung, was weniger Rechenzeit und weniger Festplattenzugriffe verursacht. Aufgrund dessen wird die Optimierung zunächst versuchen, col2 zu testen.
Sie unterstützen die Optimierung, indem Sie die folgenden Regeln beachten:
Vergleichen Sie Spalten desselben Typs
Verwenden Sie beim Vergleich indizierter Spalten nur Spalten desselben Typs. Beispielsweise wird CHAR(10) als identisch mit CHAR(10) und VARCHAR(10), nicht jedoch als identisch mit CHAR(12) oder VARCHAR(12) betrachtet. INT unterscheidet sich von BIGINT. Bei MySQL-Versionen vor 3.23 war es sogar notwendig, Spalten desselben Typs zu verwenden, weil die Indizes für diese Spalten andernfalls nicht genutzt wurden. Seit Version 3.23 ist dies nicht mehr unbedingt erforderlich, aber durch die Verwendung gleicher Spaltentypen erzielen Sie eine bessere Performance als bei unterschiedlichen Typen. Wenn die verglichenen Spalten unterschiedliche Typen haben, können Sie mit ALTER TABLE eine von ihnen so ändern, dass die Typen übereinstimmen.
Versuchen Sie, innerhalb von Vergleichen die indizierten Spalten unabhängig zu verwenden
Wenn Sie eine Spalte in einem Funktionsaufruf oder als Komponente eines komplexeren Terms in einem arithmetischen Ausdruck verwenden, kann MySQL den Index nicht nutzen, weil es den Wert des Ausdrucks für jede Zeile berechnen muss. Manchmal ist dies unvermeidbar, aber häufig kann man eine Anfrage auch so umformulieren, dass die Spalte unabhängig von anderen Komponenten verwendet wird.
Die folgenden WHERE-Klauseln zeigen, wie es geht; sie sind arithmetisch äquivalent, aus Sicht der Anfrageoptimierung jedoch völlig unterschiedlich. In der ersten Zeile vereinfacht die Anfrageoptimierung den Ausdruck 4/2 zum Wert 2 und wendet dann einen Index auf mycol an, um schnell alle Werte kleiner 2 zu finden. Im zweiten Ausdruck muss MySQL den Wert von mycol für jede Zeile ermitteln, ihn mit 2 multiplizieren und das Ergebnis dann mit 4 vergleichen. Es kann kein Index verwendet werden, weil jeder Wert in der Spalte ermittelt werden muss, damit der Ausdruck auf der linken Seite des Vergleichs ausgewertet werden kann:
WHERE mycol < 4 / 2
WHERE mycol * 2 < 4
Betrachten wir noch ein Beispiel. Angenommen, Sie haben die indizierte Spalte date_col. Bei einer Anfrage wie der folgenden wird der Index nicht genutzt:
SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;
Der Ausdruck führt keinen Vergleich einer indizierten Spalte mit dem Wert 1990 durch; er vergleicht einen aus dem Spaltenwert berechneten Wert, und dieser Wert muss für jede einzelne Zeile berechnet werden. Der Index für date_col kann also nicht genutzt werden. Gibt es eine Lösung? Man verwendet einfach ein konkretes Datum, dann kann der Index für date_col genutzt werden:
WHERE date_col < ‘1990-01-01’
Aber was tun Sie, wenn Sie kein bestimmtes Datum haben? Vielleicht wollen Sie stattdessen alle Datensätze finden, deren Datum innerhalb einer bestimmten Anzahl von Tagen ab dem heutigen Tag liegt. Es gibt mehrere Möglichkeiten, eine solche Anfrage zu formulieren, die aber nicht alle gleich gut sind. Hier sehen Sie drei der Möglichkeiten:
WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)
Für die erste Zeile wird kein Index verwendet, weil die Spalte für jede Zeile ermittelt werden muss, damit der Wert von TO_DAYS(date_col) berechnet werden kann. Die zweite Zeile ist besser. Sowohl cutoff als auch TO_DAYS(CURDATE) sind Konstanten, die rechte Seite des Vergleichs kann also von der Optimierung vor der Verarbeitung der Anfrage einmalig (statt einmal pro Zeile) berechnet werden. Allerdings kommt die Spalte date_col auch hier im Funktionsaufruf vor, weshalb der Index nicht genutzt wird. Die dritte Zeile stellt die Lösung der Wahl dar. Auch hier kann die rechte Seite des Vergleichs als Konstante einmal berechnet werden, bevor die Anfrage ausgeführt wird, aber jetzt ist der Wert ein Datum. Dieser Wert kann direkt mit den Werten von date_col verglichen werden, die jetzt nicht mehr in Tage umgewandelt werden müssen. In diesem Fall kann der Index genutzt werden.
Verwenden Sie am Anfang eines LIKE-Musters keine Platzhalter
Es kommt vor, dass mithilfe einer WHERE-Klausel in der folgenden Form nach Zeichen gesucht wird:
WHERE col_name LIKE ‘%string%’
Das ist richtig, wenn Sie string finden wollen, egal wo es in der Spalte auftritt. Aber schreiben Sie nicht einfach aus Gewohnheit ein % an den Anfang und das Ende der Zeichenkette. Wenn Sie die Zeichenkette nur dort finden wollen, wo sie am Anfang der Spalte auftritt, lassen Sie das erste % weg. Wenn Sie beispielsweise in einer Spalte mit Nachnamen solche Namen suchen, die mit »Mac« beginnen, schreiben Sie die WHERE-Klausel wie folgt:
WHERE last_name LIKE ‘Mac%’
Die Anfrageoptimierung sucht nach dem konkreten Anfangsteil des Musters und nutzt den Index, um übereinstimmende Zeilen so zu finden, als ob Sie den folgenden Ausdruck geschrieben hätten – eine Formulierung, die erlaubt, dass ein Index für last_name genutzt wird:
WHERE last_name >= ‘Mac’ AND last_name < ‘Mad’
Diese Optimierung erfolgt nicht für Mustervergleiche, die den REGEXP-Operator verwenden.
Helfen Sie der Optimierung, die Indexeffizienz besser einschätzen zu können
Wenn Sie Werte in indizierten Spalten mit einer Konstanten vergleichen, setzt die Optimierung standardmäßig voraus, dass die Schlüsselwerte gleichmäßig im Index verteilt sind. Außerdem wertet sie den Index schnell aus, um abzuschätzen, wie viele Einträge verwendet werden, und so festzustellen, ob der Index für Konstantenvergleiche genutzt werden sollte. Bei BDB- und MyISAM-Tabellen können Sie den Server anweisen, eine Analyse der Schlüsselwerte durchzuführen, indem Sie die Anweisung ANALYZE TABLE absetzen. Auf diese Weise versorgen Sie die Anfrageoptimierung mit besseren Informationen. Bei MyISAM-Tabellen können Sie auch die Dienstprogramme myisamchk oder isamchk mit der Option --analyze ausführen. Diese Dienstprogramme wirken direkt auf die Tabellendateien, weswegen zwei Bedingungen erfüllt sein müssen, um sie nutzen zu können:
-
Sie müssen über ein Konto auf dem MySQL-Serverhost verfügen, das Ihnen Schreibzugriff auf die Tabellendateien gewährt.
-
Sie müssen mit dem Server hinsichtlich des Zugriffs auf die Tabellendateien kooperieren, damit er nicht auf die Tabelle zugreift, während Sie mit den entsprechenden Dateien hantieren. Verwenden Sie für den Schreibzugriff ein geeignetes Protokoll.
Verwenden Sie EXPLAIN, um den Optimierungseinsatz zu verifizieren
Überprüfen Sie, ob die Indizes in Ihrer Anfrage genutzt werden, um bestimmte Zeilen schnell auszuschließen. Falls das nicht der Fall ist, könnten Sie es mit einem STRAIGHT_JOIN versuchen, um einen Join auszuführen, bei dem die Tabellen in einer bestimmten Reihenfolge verwendet werden (führen Sie die Anfrage sowohl mit als auch ohne STRAIGHT_JOIN durch; möglicherweise hat MySQL einen guten Grund, die Indizes nicht in der Art zu verwenden, wie Sie es für das Beste halten). Seit MySQL 3.23.12 können Sie auch die Anweisungen USE INDEX bzw. IGNORE INDEX verwenden, um dem Server zu signalisieren, welche Indizes vorzuziehen sind.
Probieren Sie unterschiedliche Anfrageformen aus, die Sie aber mehrfach testen
Wenn Sie alternative Formen einer Anfrage testen, sollten Sie diese jeweils mehrmals ausführen. Wenn Sie eine Anfrage nur jeweils ein einziges Mal in unterschiedlichen Formulierungen ausführen, werden Sie häufig feststellen, dass die zweite Anfrage schneller ist – aber nur, weil die Information aus der ersten Anfrage noch im Festplatten-Cache liegt und nicht erneut gelesen werden muss. Sie sollten auch versuchen, die Anfragen dann auszuführen, wenn die Systemauslastung relativ stabil ist, sodass sich andere Aktivitäten auf Ihrem System nicht negativ auswirken.
Vermeiden Sie die Überbeanspruchung der automatischen Typkonvertierung
MySQL führt die Typkonvertierung automatisch durch; wenn Sie diese Konvertierungen aber verhindern können, kann die Performance im Endeffekt besser sein. Wenn etwa num_col im folgenden Beispiel eine Integerspalte ist, dann geben die beiden folgenden Anfragen das gleiche Ergebnis zurück:
SELECT * FROM mytbl WHERE num_col = 4;
SELECT * FROM mytbl WHERE num_col = ‘4’;
Die zweite Anfrage bedingt jedoch eine Typumwandlung. Der Konvertierungsvorgang selbst wirkt sich in geringem Maße nachteilig auf die Leistung aus, weil zur Durchführung des Vergleichs die Ganzzahl und der String konvertiert werden müssen. Ein schwerwiegenderes Problem ist allerdings die Tatsache, dass – sofern num_col indiziert ist – ein Vergleich, der eine Typumwandlung bedingt, die Verwendung des Index verhindern kann.
Optimierung außer Kraft setzen
Es hört sich vielleicht seltsam an, aber es gibt Konstellationen, in dessen Sie die Optimierungsmethoden von MySQL besser außer Kraft setzen sollten. Einige mögliche Gründe sind die folgenden:
Tabelle mit möglichst wenig Nebeneffekten löschen
Wenn Sie den Inhalt einer Tabelle vollständig löschen wollen, besteht die schnellste Methode darin, die Tabelle selbst zu löschen und sie basierend auf der Beschreibung in der .frm-Datei neu zu erstellen. Dies tun Sie mit der Anweisung TRUNCATE TABLE:
TRUNCATE TABLE tbl_name;
Bei MySQL vor Version 4 erzielen Sie denselben Effekt, indem Sie eine DELETE-Anweisung ohne WHERE-Klausel ausführen:
DELETE FROM tbl_name;
Die serverseitige Optimierung des Leerens einer Tabelle durch vollständige Neuerstellung ist extrem schnell, weil nicht jede Zeile einzeln gelöscht werden muss. Es gibt allerdings Nebeneffekte, die unter bestimmten Umständen unerwünscht sein können:
-
Bei MySQL vor Version 4 kann eine
DELETE-Anweisung ohneWHERE-Klausel melden, dass null Zeilen gelöscht wurden, auch wenn die Tabelle nicht leer war; beiTRUNCATE TABLEkann dies – je nach Tabellentyp – sogar bei jeder MySQL-Version vorkommen. Meistens spielt dieses Verhalten keine Rolle, auch wenn es verwirrend sein kann, weil Sie es nicht erwarten. Für Programme, die die konkrete Anzahl der gelöschten Zeilen kennen müssen, ist null kein akzeptabler Rückgabewert. -
Bei MyISAM-Tabellen werden Werte in einer
AUTO_INCREMENT-Spalte nach dem Löschen von Zeilen nicht wiederverwendet; das Neuerstellen einer Tabelle kann jedoch dazu führen, dass die Folgenummerierung für diese Spalte auf 1 zurückgesetzt wird.
Um diese Nebeneffekte zu vermeiden, verwenden Sie eine DELETE-Anweisung mit einer einfachen, immer wahren WHERE-Klausel:
DELETE FROM tbl_name WHERE 1;
Auf diese Weise wird MySQL gezwungen, zeilenweise zu löschen, weil die Bedingung für jede Zeile neu ausgewertet werden muss, um zu bestimmen, ob die Zeile gelöscht werden muss oder nicht. Die Anfrage wird viel langsamer ausgeführt, gibt aber zurück, wie viele Zeilen tatsächlich gelöscht wurden. Außerdem wird die aktuelle AUTO_INCREMENT-Nummerierung bei MyISAMTabellen beibehalten.
Reihenfolge der Anfrageoptimierung für Joins außer Kraft setzen
Verwenden Sie STRAIGHT_JOIN, um die Optimierung zur Verwendung der Tabellen in einer bestimmten Reihenfolge zu zwingen. Wenn Sie dies tun, sollten Sie die Tabellen so arrangieren, dass die erste Tabelle diejenige ist, aus der die wenigsten Zeilen ausgewählt werden (wenn Sie nicht sicher sind, welche Tabelle das ist, setzen Sie die Tabelle mit den meisten Zeilen an den Anfang). Mit anderen Worten: Versuchen Sie, die Tabellen so zu ordnen, dass die restriktivste Auswahl zuerst erfolgt.
Anfragen bieten eine umso bessere Leistung, je schneller Sie die Anzahl möglicher Ausgabezeilen beschränken können. Sie sollten die Anfrage in jedem Fall in beide Richtungen ausführen; es kommt vor, dass die Optimierung die Tabellen nicht so verknüpft, wie Sie annehmen, und dann hilft auch STRAIGHT_JOIN nicht mehr.
Eine weitere Möglichkeit ist die Verwendung der Modifikatoren USE INDEX und IGNORE INDEX nach einem Tabellennamen aus der Liste oder einem Join, um MySQL anzuweisen, Indizes zu verwenden bzw. zu ignorieren. Dies kann in Fällen hilfreich sein, in denen die Anfrageoptimierung falsche Entscheidungen trifft.
Ergebnisse in einer beliebigen Reihenfolge erhalten
Seit MySQL 3.23.3 können Sie die Ergebnisse mit ORDER BY RAND() nach dem Zufallsprinzip sortieren. Eine weitere Technik, die auch für ältere Versionen von MySQL geeignet ist, ist die Auswahl einer Spalte mit Zufallszahlen und die Sortierung nach dieser Spalte. Wenn Sie die Anfrage jedoch wie folgt formulieren, macht die Optimierung Ihnen einen Strich durch die Rechnung:
SELECT ..., RAND() as rand_col FROM ... ORDER BY rand_col;
Das Problem dabei ist, dass MySQL erkennt, wenn die Spalte ein Funktionsaufruf ist, annimmt, dass der Wert der Spalte eine Konstante sei, und die ORDER BY-Klausel aus der Anfrage heraus optimiert. Sie können die Anfrageoptimierung aber täuschen, indem Sie in dem Ausdruck auf eine Tabellenspalte verweisen. Wenn Ihre Tabelle beispielsweise die Spalte age enthält, könnten Sie die Anfrage wie folgt schreiben:
SELECT ..., age*0+RAND() as rand_col FROM ... ORDER BY rand_col;
In diesem Fall entspricht der Wert des Ausdrucks immer RAND(). Die Anfrageoptimierung weiß dies nicht, d.h. sie geht nicht mehr davon aus, dass die Spalte in jeder Zeile eine Konstante enthält.
Eine endlose Aktualisierungsschleife vermeiden
Wenn Sie bei MySQL-Versionen vor 3.23.2 eine indizierte Spalte aktualisieren, kann diese Aktualisierung der betreffenden Zeilen endlos dauern, wenn die Spalte in der WHERE-Klausel angegeben wird und die Aktualisierung den Indexwert in den Teil des Wertebereichs verschiebt, der noch nicht verarbeitet wurde. Angenommen, die Tabelle mytbl enthält die ganzzahlige Spalte key_col, für die ein Index angelegt wurde. Anfragen wie die folgende können Probleme verursachen:
UPDATE mytbl SET key_col = key_col+1 WHERE key_col > 0;
Die Lösung besteht darin, key_col in einem Ausdruck in der WHERE-Klausel zu verwenden, sodass MySQL den Index nicht nutzen kann:
UPDATE mytbl SET key_col = key_col+1 WHERE key_col+0 > 0;
Ausblick
Dieser Teil der Serie hat sich mit der Arbeitsweise der Anfrageoptimierung beschäftigt. Im nächsten Artikel wird die Anfrageeffizienz behandelt. (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 |