MySQL versteht kurz, wie "order by" funktioniert

Zum Sortieren ist „order by“ ein Schlüsselwort, das wir sehr häufig verwenden. Wenn wir unser bisheriges Verständnis von Indizes mit diesem Artikel kombinieren, erhalten wir ein tiefes Verständnis dafür, wie wir Indizes verwenden können, um weniger Tabellen zu scannen oder beim Sortieren eine externe Sortierung zu verwenden.

Definieren Sie zunächst eine Tabelle, die uns später beim Verständnis hilft:

TABELLE ERSTELLEN `t` (
 `id` int(11) NICHT NULL,
 `Stadt` varchar(16) NICHT NULL,
 `name` varchar(16) NICHT NULL,
 `Alter` int(11) NICHT NULL,
 `Adresse` varchar(128) DEFAULT NULL,
 Primärschlüssel (`id`),
 SCHLÜSSEL `Stadt` (`Stadt`)
)ENGINE=InnoDB;

Nun schreiben wir eine Abfrageanweisung

select city,name,age from t where city= ' 杭州' order by name limit 1000 ;

Gemäß der obigen Tabellendefinition kann city=xxx einen von uns definierten Index verwenden. Da uns aber offensichtlich kein Index für die Sortierung nach Namen zur Verfügung steht, müssen wir zunächst den Index für die Abfrage „city=xxx“ verwenden, dann die Tabelle zurückabfragen und schließlich sortieren.

Vollständige Feldsortierung

Nachdem wir einen Index für das Stadtfeld erstellt haben, verwenden wir den Ausführungsplan, um diese Anweisung anzuzeigen

Sie können sehen, dass wir auch bei Vorhandensein eines Indexes immer noch „Using filesort“ verwenden, um anzugeben, dass eine Sortierung erforderlich ist. MySQL weist jedem Thread zum Sortieren einen Speicherbereich namens sort_buffer zu.

Wenn wir die obige Select-Anweisung ausführen, durchlaufen wir normalerweise einen solchen Prozess

1. Initialisieren Sie sort_buffer und bestätigen Sie, dass die drei Felder Name, Stadt und Alter hinzugefügt werden.

2. Suchen Sie die erste Primärschlüssel-ID, die die Bedingung „Stadt = ‚Hangzhou‘“ aus dem Index „Stadt“ erfüllt.

3. Geben Sie die Tabelle zurück, um die Werte der drei Felder Name, Stadt und Alter abzurufen und im Sortierpuffer zu speichern.

4. Holen Sie sich einen Primärschlüssel-ID-Datensatz aus der Indexstadt.

5. Wiederholen Sie die Schritte 3–4, bis die Stadt die Bedingungen nicht mehr erfüllt.

6. Sortieren Sie die Daten im Sort_Buffer schnell nach Feldnamen.

7. Die ersten 1000 Zeilen des Sortierergebnisses werden an den Client zurückgegeben.

Dies wird als Vollfeldsortierung bezeichnet.

Die Sortierung nach Namen kann entweder im Speicher oder mithilfe einer externen Datei erfolgen. Dies hängt von der Sort_Buffer_Size ab. Der Standardwert von sort_buffer_size beträgt 1048576 Bytes, also 1 MB. Wenn die zu sortierende Datenmenge weniger als 1 MB beträgt, erfolgt die Sortierung im Speicher. Wenn die zu sortierende Datenmenge groß ist und nicht im Speicher gespeichert werden kann, wird zur Unterstützung der Sortierung eine temporäre Datenträgerdatei verwendet.

Zeilen-ID-Sortierung

Wenn eine einzelne Zeile sehr groß ist, ist das Einfügen aller erforderlichen Felder in den Sortierpuffer nicht sehr effektiv.

In MySQL gibt es einen Parameter max_length_for_sort_data, der speziell zur Steuerung der Länge der Zeilendaten für die Sortierung verwendet wird. Der Standardwert ist 1024. Wenn dieser Wert überschritten wird, wird die Zeilen-ID-Sortierung verwendet. Dann wird der Prozess der Ausführung der obigen Anweisung zu

1. Initialisieren Sie sort_buffer und stellen Sie sicher, dass Sie zwei Felder eingeben: Name und ID.

2. Suchen Sie aus dem Index „Stadt“ die erste Primärschlüssel-ID, die die Bedingung „Stadt = ‚Hangzhou‘“ erfüllt.

3. Geben Sie die Namens- und ID-Felder an die Tabelle zurück und speichern Sie sie im Sortierpuffer.

4. Nehmen Sie den nächsten Datensatz, der die Bedingungen erfüllt, und wiederholen Sie die Schritte 2 und 3.

5. Sortieren Sie die Namen im Sort_Buffer.

6. Durchlaufen Sie die Ergebnisse und nehmen Sie die ersten 1000 Zeilen. Anschließend werden die Ergebnisfelder entsprechend der ID erneut aus der Tabelle ausgelesen und an den Client zurückgegeben.

Tatsächlich erfordern nicht alle Order-By-Anweisungen den oben genannten sekundären Sortiervorgang. Aus dem oben analysierten Ausführungsprozess können wir Folgendes feststellen. Der Grund, warum MySQL eine temporäre Tabelle generieren muss, besteht darin, dass es die temporäre Tabelle sortieren muss, da die zuvor erhaltenen Daten ungeordnet waren.

Wenn wir den vorherigen Index ändern, um ihn zu einem gemeinsamen Index zu machen, werden die Werte, die wir aus dem zweiten Feld erhalten, tatsächlich geordnet.

Der gemeinsame Index erfüllt die Bedingung, dass das zweite Feld geordnet wird, wenn unser erstes Indexfeld gleich ist.

Dadurch wird sichergestellt, dass bei der Erstellung eines Index (Stadt, Name) und der Suche nach „Stadt='Hangzhou'“ der zweite Feldname des Ziels tatsächlich in der richtigen Reihenfolge ist. Der Abfragevorgang lässt sich dadurch vereinfachen.

1. Suchen Sie aus dem Index (Stadt, Name) die erste Primärschlüssel-ID, die die Bedingung „Stadt = ‚Hangzhou‘“ erfüllt.

2. Geben Sie die drei Werte Name, Stadt und Alter aus der Tabelle zurück.

3. Holen Sie sich einen Ausweis.

4. Wiederholen Sie die Schritte 2 und 3, bis 1.000 Datensätze vorhanden sind oder die Bedingung „Stadt = ‚Hangzhou‘“ nicht mehr erfüllt ist.

Da der Abfragevorgang außerdem die Ordnungsmäßigkeit des Indexes nutzen kann, ist eine Sortierung oder Verwendung des Sortierpuffers nicht erforderlich.

Eine weitere Optimierung ist die bereits erwähnte Indexabdeckung. Dabei werden die abzufragenden Felder auch im Index abgedeckt und der Schritt der Rückkehr zur Tabelle entfällt, was die gesamte Abfrage beschleunigen kann.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.