Skip navigation links
7.2.13 GROUP BY-Optimierung »
Section Navigation      [Toggle]

7.2.12. ORDER BY-Optimierung

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 BY key_part1,key_part2,... ;
    
SELECT * FROM t1 
  WHERE key_part1=constant 
  ORDER BY key_part2;
    
SELECT * FROM t1 
  ORDER BY key_part1 DESC, key_part2 DESC;
    
SELECT * FROM t1
  WHERE key_part1=1 
  ORDER BY key_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 BY key1, key2;

  • Sie verwenden ORDER BY für Teile eines Schlüssels, die nicht aufeinander folgen: 

    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

  • Sie verwenden ASC und DESC gemischt: 

    SELECT * FROM t1 ORDER BY key_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 WHERE key2=constant ORDER BY key1;

  • 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:

  1. Die Datensätze, die der WHERE-Klausel entsprechen, werden gelesen.

  2. 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.

  3. Die Tupel werden nach dem Sortierschlüsselwert sortiert.

  4. 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 col1, col2, …-Abfragen so, als ob Sie in der Abfrage auch ORDER BY col1, col2, … angegeben hätten. Wenn Sie ausdrücklich eine 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.