Detaillierte Erläuterung der MySQL-Methode zur Optimierung der Reihenfolge nach Anweisung

In diesem Artikel erfahren Sie mehr über die Optimierung der ORDER BY-Anweisung. Zuvor müssen Sie über grundlegende Kenntnisse zu Indizes verfügen. Wenn Sie diese nicht verstehen, können Sie zunächst die Index-bezogenen Artikel lesen, die ich zuvor geschrieben habe. Nun legen wir los.

Zwei Sortiermethoden in MySQL

1. Geben Sie geordnete Daten direkt durch geordnetes sequentielles Scannen des Index zurück

Da die Struktur des Index ein B+-Baum ist, sind die Daten im Index in einer bestimmten Reihenfolge angeordnet. Wenn der Index also in Sortierabfragen verwendet werden kann, können zusätzliche Sortiervorgänge vermieden werden. Wenn EXPLAIN die Abfrage analysiert, wird „Extra“ als „Verwendeter Index“ angezeigt.

2. Filesort sortiert die zurückgegebenen Daten

Bei allen Operationen die nicht direkt über den Index sortierte Ergebnisse zurückliefern handelt es sich um Filesort-Sortierungen, d.h. es werden zusätzliche Sortieroperationen durchgeführt. Wenn EXPLAIN die Abfrage analysiert, wird „Extra“ als „Dateisortierung wird verwendet“ angezeigt.

Grundprinzipien der ORDER BY-Optimierung

Minimieren Sie zusätzliche Sortiervorgänge und geben Sie geordnete Daten direkt über den Index zurück.

ORDER BY-Optimierungspraxis

Der Index der für das Experiment verwendeten Kundentabelle lautet wie folgt:

Bitte beachten Sie zunächst:

MySQL kann nur einen Index für eine Abfrage verwenden. Wenn Sie Indizes für mehrere Felder verwenden möchten, erstellen Sie einen zusammengesetzten Index.

ORDER BY-Optimierung

1. Die abgefragten Felder sollten nur die in dieser Abfrage verwendeten Indexfelder und Primärschlüssel enthalten. Die übrigen Nicht-Indexfelder und Indexfelder verwenden keine Indizes als Abfragefelder.

Um nur die Indexfelder abzufragen, die zur Sortierung verwendet werden, können Sie die Indexsortierung verwenden:

explain select store_id,email from customer order by store_id,email;

Beachten Sie jedoch, dass, wenn sich das Sortierfeld in mehreren Indizes befindet, die Indexsortierung nicht verwendet werden kann und jeweils nur ein Index für die Abfrage verwendet werden kann:

explain select store_id,email,last_name from customer order by store_id,email,last_name;

Fragen Sie nur die Indexfelder und Primärschlüssel ab, die zur Sortierung verwendet werden, und Sie können die Indexsortierung verwenden:

Voiceover: Die standardmäßige InnoDB-Engine von MySQL verwendet physisch einen Clustered-Index, um nach Primärschlüsseln zu suchen. Daher erfordert die InnoDB-Engine, dass die Tabelle einen Primärschlüssel haben muss. Auch wenn der Primärschlüssel nicht explizit angegeben ist, generiert die InnoDB-Engine einen eindeutigen impliziten Primärschlüssel. Dies bedeutet, dass im Index ein Primärschlüssel vorhanden sein muss.

explain select customer_id,store_id,email from customer order by store_id,email;

Bei der Abfrage anderer Felder als der Indexfelder und Primärschlüsselfelder, die zur Sortierung verwendet werden, wird die Indexsortierung nicht genutzt:

explain select store_id,email,last_name from customer order by store_id,email;

explain select * from customer order by store_id,email;

WHERE + ORDER BY-Optimierung

1. Das Sortierfeld befindet sich in mehreren Indizes und kann nicht über den Index sortiert werden

Die Sortierfelder befinden sich in mehreren Indizes (nicht im selben Index) und die Indexsortierung kann nicht verwendet werden:

explain select * from customer where last_name='swj' order by last_name,store_id;

Voiceover: Wenn sich die Sortierfelder nicht im selben Index befinden, ist es nicht möglich, die Sortierung in einem B+-Baum abzuschließen und es muss eine zusätzliche Sortierung durchgeführt werden.

Das Sortierfeld befindet sich in einem Index und die WHERE-Bedingung und ORDER BY verwenden denselben Index. Sie können die Indexsortierung verwenden:

explain select * from customer where last_name='swj' order by last_name;

Natürlich können zusammengesetzte Indizes auch die Indexsortierung verwenden:

Beachten Sie, dass sich die Felder „store_id“ und „email“ in einem zusammengesetzten Index befinden.

explain select * from customer where store_id = 5 order by store_id,email;

2. Die Sortierfeldreihenfolge stimmt nicht mit der Indexspaltenreihenfolge überein und die Indexsortierung kann nicht verwendet werden

Voiceover: Dies gilt für zusammengesetzte Indizes. Wir alle wissen, dass bei der Verwendung zusammengesetzter Indizes das Prinzip „ganz links“ befolgt werden muss. Die WHERE-Klausel muss die erste Spalte im Index haben. Obwohl die ORDER BY-Klausel diese Anforderung nicht hat, erfordert sie auch, dass die Sortierfeldreihenfolge mit der Spaltenreihenfolge des zusammengesetzten Index übereinstimmt. Wenn wir normalerweise zusammengesetzte Indizes verwenden, müssen wir uns angewöhnen, in der Reihenfolge der zusammengesetzten Indexspalten zu schreiben.

Die Sortierfeldreihenfolge stimmt nicht mit der Indexspaltenreihenfolge überein und die Indexsortierung kann nicht verwendet werden:

explain select * from customer where store_id > 5 order by email,store_id;

Sie sollten sicherstellen, dass die Sortierfeldreihenfolge mit der Indexspaltenreihenfolge übereinstimmt, damit Sie die Vorteile der Indexsortierung nutzen können:

explain select * from customer where store_id > 5 order by store_id,email;

Für die ORDER BY-Klausel ist die erste Spalte im Index nicht erforderlich, und der Index kann auch ohne sie zum Sortieren verwendet werden. Voraussetzung ist allerdings, dass dies nur bei der Filterung mit gleichen Werten möglich ist, nicht bei Abfragen mit Bereichen:

explain select * from customer where store_id = 5 order by email;

explain select * from customer where store_id > 5 order by email;

Sprechertext:

Der Grund ist eigentlich ganz einfach. Bei einer Bereichsabfrage wird zwar die erste Spalte a auf jeden Fall sortiert (standardmäßig aufsteigend), das zweite Feld b wird jedoch nicht wirklich sortiert. Wenn aber Feld a den gleichen Wert hat, wird Feld b sortiert. Handelt es sich also um eine Bereichsabfrage, kann für b nur eine zusätzliche Sortierung durchgeführt werden.

3. Die aufsteigende und absteigende Reihenfolge ist inkonsistent und der Index kann nicht zum Sortieren verwendet werden

ORDER BY-Sortierfelder müssen entweder aufsteigend oder absteigend sortiert sein, sonst kann die Indexsortierung nicht verwendet werden.

explain select * from customer where store_id > 5 order by store_id,email;

explain select * from customer where store_id > 5 order by store_id desc,email desc;

explain select * from customer where store_id > 5 order by store_id desc,email asc;

Zusammenfassen:

Die obige Optimierung kann eigentlich wie folgt zusammengefasst werden: Die WHERE-Bedingung und ORDER BY verwenden denselben Index, die ORDER BY-Reihenfolge ist dieselbe wie die Indexreihenfolge und die ORDER BY-Felder sind in aufsteigender oder absteigender Reihenfolge. Andernfalls sind auf jeden Fall weitere Sortiervorgänge erforderlich und es erscheint Filesort.

Dateisortierungsoptimierung

Das Auftreten von Filesort kann durch die Erstellung geeigneter Indizes reduziert werden, in manchen Fällen kann Filesort jedoch nicht vollständig eliminiert werden. In diesem Fall besteht die einzige Möglichkeit darin, Möglichkeiten zu finden, den Vorgang von Filesort zu beschleunigen.

Zwei Sortieralgorithmen für Filesort:

1. Zwei-Scan-Algorithmus

Dabei werden zunächst die Sortierfelder und Zeilenzeigerinformationen entsprechend der Bedingungen ausgelesen und anschließend im Sortierpuffer sortiert. Dieser Sortieralgorithmus muss zweimal auf die Daten zugreifen: das erste Mal, um die Sortierfeld- und Zeilenzeigerinformationen abzurufen, und das zweite Mal, um den Datensatz basierend auf dem Zeilenzeiger abzurufen. Der zweite Lesevorgang kann zu einer großen Anzahl zufälliger E/A-Vorgänge führen. Der Vorteil liegt darin, dass der Speicheraufwand beim Sortieren gering ist.

2. One-Scan-Algorithmus

Dabei werden alle Felder der Zeilen, die die Bedingungen erfüllen, auf einmal ausgelesen und anschließend die Ergebnismenge nach der Sortierung direkt im Sortierpuffer ausgegeben. Der Speicheraufwand beim Sortieren ist relativ groß, die Sortiereffizienz ist jedoch höher als beim Two-Scan-Algorithmus.

Gemäß den Eigenschaften der beiden Sortieralgorithmen kann MySQL durch entsprechendes Erhöhen des Werts der Systemvariablen max_length_for_sort_data einen optimierteren Filesort-Sortieralgorithmus auswählen. Und verwenden Sie beim Schreiben von SQL-Anweisungen nur die erforderlichen Felder anstelle von SELECT * all fields. Dies kann die Verwendung des Sortierbereichs reduzieren und die SQL-Leistung verbessern.

Zusammenfassen

Oben finden Sie eine ausführliche Erläuterung der vom Herausgeber eingeführten Methode zur Optimierung der MySQL-Order-by-Anweisung. Ich hoffe, dass sie für alle hilfreich ist. Wenn Sie Fragen haben, hinterlassen Sie mir bitte eine Nachricht und der Herausgeber wird Ihnen rechtzeitig antworten. Ich möchte auch allen für ihre Unterstützung der Website 123WORDPRESS.COM danken!