In manchen Fällen kann MySQL mit einem Index eine
ORDER BY
-Klausel erfüllen, ohne dass eine
zusätzliche Sortierung erforderlich wäre.
Der Index kann auch verwendet werden, wenn die ORDER
BY
-Klausel dem Index nicht exakt entspricht, solange
alle nicht verwendeten Bestandteile des Indexes und alle
zusätzlichen ORDER BY
-Spalten Konstanten in
der WHERE
-Klausel sind. Die folgenden
Abfragen lösen den ORDER BY
-Teil mithilfe
des Indexes auf:
SELECT * FROM t1 ORDER BYkey_part1
,key_part2
,... ; SELECT * FROM t1 WHEREkey_part1
=constant
ORDER BYkey_part2
; SELECT * FROM t1 ORDER BYkey_part1
DESC,key_part2
DESC; SELECT * FROM t1 WHEREkey_part1
=1 ORDER BYkey_part1
DESC,key_part2
DESC;
In manchen Fällen kann MySQL Indizes nicht
zur Auflösung von ORDER BY
verwenden, obwohl
es mithilfe der Indizes Datensätze findet, die der
WHERE
-Klausel entsprechen. Dies betrifft
u. a. die folgenden Fälle:
Sie verwenden ORDER BY
für verschiedene
Schlüssel:
SELECT * FROM t1 ORDER BYkey1
,key2
;
Sie verwenden ORDER BY
für Teile eines
Schlüssels, die nicht aufeinander folgen:
SELECT * FROM t1 WHEREkey2
=constant
ORDER BYkey_part2
;
Sie verwenden ASC
und
DESC
gemischt:
SELECT * FROM t1 ORDER BYkey_part1
DESC,key_part2
ASC;
Der Schlüssel, der zum Holen der Datensätze verwendet
wird, ist nicht derselbe wie derjenige, der in der
ORDER BY
-Klausel verwendet wird:
SELECT * FROM t1 WHEREkey2
=constant
ORDER BYkey1
;
Sie verknüpfen zahlreiche Tabellen, und die Spalten in der
ORDER BY
-Klausel stammen nicht alle aus
der ersten nichtkonstanten Tabelle, die zum Abrufen von
Datensätzen verwendet wird. (Dies ist die erste Tabelle in
der Ausgabe von EXPLAIN
, die nicht den
Join-Typ const
aufweist.)
Sie haben verschiedene ORDER BY
- und
GROUP BY
-Ausdrücke.
Der Typ des verwendeten Tabellenindexes speichert die
Datensätze nicht in ihrer Reihenfolge. Dies gilt
beispielsweise für einen HASH
-Index in
einer MEMORY
-Tabelle.
Mit EXPLAIN SELECT … ORDER BY
können
Sie überprüfen, ob MySQL Indizes zur Auflösung der Abfrage
verwenden kann. Wenn Sie Using filesort
in
der Spalte Extra
sehen, ist dies nicht
möglich. Siehe auch Abschnitt 7.2.1, „EXPLAIN
-Syntax (Informationen über ein
SELECT
erhalten)“.
Die verwendete filesort
-Optimierung vermerkt
nicht nur den Sortierschlüsselwert und die Datensatzposition,
sondern auch die Spalten, die für die Abfrage erforderlich
sind. Hiermit wird verhindert, dass Datensätze zweimal
ausgelesen werden. Der filesort
-Algorithmus
funktioniert wie folgt:
Die Datensätze, die der WHERE
-Klausel
entsprechen, werden gelesen.
Für jeden Datensatz wird ein Tupel mit Werten aufgezeichnet, die den Sortierschlüsselwert und die Datensatzposition sowie die Spalten umfassen, die für die Abfrage erforderlich sind.
Die Tupel werden nach dem Sortierschlüsselwert sortiert.
Die Datensätze werden in der sortierten Reihenfolge abgerufen, aber die erforderlichen Spalten werden direkt aus den sortierten Tupeln ausgelesen, statt ein zweites Mal auf die Tabelle zuzugreifen.
Dieser Algorithmus stellt gegenüber dem in älteren MySQL-Versionen verwendeten eine erhebliche Verbesserung dar.
Um eine Verlangsamung zu vermeiden, wird diese Optimierung nur
verwendet, wenn die Gesamtgröße der zusätzlichen Spalten im
Sortiertupel den Wert der Systemvariablen
max_length_for_sort_data
nicht
überschreitet. (Wenn Sie dieser Variablen einen zu hohen Wert
zuweisen, treten typische Symptome wie eine hohe Festplatten-
und eine niedrige Prozessoraktivität auf.)
Wenn Sie die Geschwindigkeit von ORDER BY
erhöhen wollen, überprüfen Sie, ob Sie MySQL dazu bewegen
können, Indizes statt einer zusätzlichen Sortierphase zu
verwenden. Ist dies nicht möglich, dann können Sie die
folgenden Strategien ausprobieren:
Sie erhöhen den Wert der Variablen
sort_buffer_size
.
Sie erhöhen den Wert der Variablen
read_rnd_buffer_size
.
Sie ändern tmpdir
so, dass die Variable
auf ein dediziertes Dateisystem mit viel freiem Speicher
weist. Die Option akzeptiert mehrere Pfade, die zyklisch
abwechselnd verwendet werden. Die Pfade sollten unter Unix
mit Doppelpunkten (‘:
’) und
unter Windows, NetWare und OS/2 mit Semikola
(‘;
’) voneinander getrennt
werden. Mithilfe dieser Funktion können Sie die Last auf
mehrere Verzeichnisse verteilen.
Hinweis: Die Pfade sollten auf
Verzeichnisse in Dateisystemen verweisen, die sich auf
verschiedenen physischen Festplatten
befinden – nicht auf verschiedenen Partitionen derselben
Festplatte.
Standardmäßig sortiert MySQL alle GROUP BY
-Abfragen so,
als ob Sie in der Abfrage auch col1
,
col2
, …ORDER BY
angegeben
hätten. Wenn Sie ausdrücklich eine col1
,
col2
, …ORDER
BY
-Klausel angeben, die dieselbe Spaltenliste
enthält, optimiert MySQL diese ohne Geschwindigkeitseinbuße
weg, auch wenn die Sortierung tatsächlich stattfindet. Enthält
eine Abfrage eine GROUP BY
-Klausel, während
Sie die Mehrbelastung durch die Sortierung des Ergebnisses
vermeiden wollen, dann können Sie diese Sortierung durch Angabe
von ORDER BY NULL
unterdrücken. Zum
Beispiel:
INSERT INTO foo SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
Dies ist eine Übersetzung des MySQL-Referenzhandbuchs, das sich auf dev.mysql.com befindet. Das ursprüngliche Referenzhandbuch ist auf Englisch, und diese Übersetzung ist nicht notwendigerweise so aktuell wie die englische Ausgabe. Das vorliegende deutschsprachige Handbuch behandelt MySQL bis zur Version 5.1.