MySQL-Sortierung mittels Index-Scan

Installieren Sie Sakila

Wir werden die MySQL-Beispieldatenbank sakila verwenden, um SQL dev.mysql.com/doc/sakila/… zu demonstrieren und zu erklären.

Index-Scan-Sortierung

MySQL bietet zwei Möglichkeiten, geordnete Ergebnisse zu erzeugen: durch Sortiervorgänge oder durch Scannen in Indexreihenfolge. Wenn der Wert der von EXPLAIN ausgegebenen Typspalte „index“ ist, bedeutet dies, dass MySQL zum Sortieren Indexscannen verwendet.
Das Scannen des Indexes selbst geht schnell, weil man nur von einem Indexdatensatz zum unmittelbar nächsten Datensatz wechseln muss. Wenn der Index jedoch nicht alle für die Abfrage erforderlichen Spalten abdeckt, müssen Sie bei jedem Scannen eines Indexdatensatzes zur Tabelle zurückkehren, um die entsprechende Zeile abzufragen. Da es sich im Wesentlichen um zufällige E/A-Vorgänge handelt, ist das Lesen der Daten in Indexreihenfolge normalerweise langsamer als ein sequentieller vollständiger Tabellenscan, insbesondere bei E/A-intensiven Arbeitslasten. Derzeit kann anstelle einer Indexsuche ein vollständiger Tabellenscan verwendet werden.
Wenn möglich, sollten Indizes so gestaltet sein, dass sie sowohl das Sortieren als auch das Suchen von Zeilen ermöglichen.
MySQL kann den Index nur dann zum Sortieren der Ergebnisse verwenden, wenn die Reihenfolge der Indexspalten genau mit der Reihenfolge der ORDER BY-Klausel übereinstimmt und die Sortierrichtung (rückwärts oder vorwärts) aller Spalten gleich ist. Wenn die Abfrage mehrere Tabellen verbinden muss, kann der Index nur dann zum Sortieren verwendet werden, wenn alle von der ORDER BY-Klausel referenzierten Felder aus der ersten Tabelle stammen. Die Einschränkungen der ORDER BY-Klausel sind die gleichen wie bei einer Suchanfrage: Das äußerste linke Präfix des Index muss eingehalten werden, sonst muss MySQL eine Sortieroperation (Filesort) durchführen und kann die Indexsortierung nicht verwenden.

Tabellenstruktur

Anhand der Mietpreistabelle erklären wir

CREATE TABLE `Miete` (
  
  EINDEUTIGER SCHLÜSSEL `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  SCHLÜSSEL `idx_fk_inventory_id` (`Inventar-ID`),
  SCHLÜSSEL `idx_fk_customer_id` (`customer_id`),
  SCHLÜSSEL `idx_fk_staff_id` (`staff_id`),
  
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4;

Überprüfen Sie, ob „Dateisortierung verwenden“ in „Extra“ angezeigt wird (der Sortiervorgang, der in MySQL nicht mithilfe des Index abgeschlossen werden kann, wird als „Dateisortierung“ bezeichnet). Wenn wir versuchen, ein Feld ohne Index zu sortieren, handelt es sich um „Dateisortierung“. Obwohl sich darin eine Datei befindet, hat es nichts mit der Datei zu tun. Es handelt sich tatsächlich um eine interne Schnellsortierung.

Situationen, in denen Index-Scans zum Sortieren verwendet werden können

Füllen Sie die führende Spalte aus

Es gibt einen Fall, in dem die ORDER BY-Klausel die Anforderung des äußersten linken Präfixes des Index nicht erfüllen muss, nämlich wenn die führende Spalte eine Konstante ist. Wenn in der WHERE-Klausel oder JOIN-Klausel Konstanten für diese Spalten angegeben werden, können die fehlenden Indizes „ausgeglichen“ werden. Wir verwenden die Sakila-Datenbank zum Testen

Sie können sehen

Im Extra im Buch steht „Using where“, aber als ich es ausführte, verwendete ich „Using index condition“. Der Grund dafür ist, dass die in MySQL mit hoher Leistung verwendete Version 5.5 ist und der Indexbedingungs-Pushdown in Version 5.6 noch nicht offiziell freigegeben wurde. Der Grund, warum hier keine Dateisortierung erfolgt, liegt darin, dass die konstante Bedingung „rental_date = ‚2005-05-25‘“ vorliegt, die dem Ausfüllen der ersten Spalte des Index entspricht und somit die Anforderung des Indexes nach dem ganz linken Präfix erfüllt.

Die Sortierung enthält nur eine Sortierung

SELECT Miet-ID, Mitarbeiter-ID FROM sakila.rental WHERE Mietdatum = '2005-05-25' ORDER BY Inventar-ID desc

Sie können sehen

Beachten Sie, dass im Buch die Bedingung „Mietdatum>25.05.2005“ verwendet wird.

WO Mietdatum > '2005-05-25' ORDER BY Mietdatum, Inventar-ID

Derzeit können wir die Indexsortierung nicht verwenden, sondern müssen die gesamte Tabelle direkt scannen, um sie zu sortieren. Der Grund dafür ist, dass zu viele Datenelemente zurückgegeben werden und die Verwendung der Indexabfrage derzeit nicht kosteneffizient ist.

Es ist zu beachten, dass die Anzahl der Zeilen in der Erklärung hier nicht genau ist, sondern nur eine Schätzung darstellt. Tatsächlich enthält die Abfrage gemäß dieser Bedingung 16036 Datenelemente. Um dieses Problem zu lösen, müssen Sie ein Limit hinzufügen

SELECT Miet-ID, Mitarbeiter-ID FROM sakila.rental WHERE Mietdatum > '2005-05-25' ORDER BY Mietdatum, Inventar-ID Limit 0,10

Der entsprechende Ausführungsplan

Sie können sehen, dass der Index verwendet wird

Situationen, in denen Index-Scans nicht verwendet werden können

Die Abfragebedingungen enthalten unterschiedliche Sortierrichtungen

SELECT Miet-ID, Mitarbeiter-ID FROM sakila.rental WHERE Mietdatum = '2005-05-25' ORDER BY Inventar-ID absteigend, Kunden-ID aufsteigend

Beide Spalten im Index sind aufsteigend sortiert. Jetzt ist in der Sortierreihenfolge eine Spalte aufsteigend und die andere absteigend sortiert, sodass eine sekundäre Sortierung erforderlich ist.

Die Abfragebedingung bezieht sich auf eine Spalte, die nicht im Index vorhanden ist

Wählen Sie Miet-ID, Mitarbeiter-ID aus sakila.rental, wobei Mietdatum = '2005-08-23 21:01:09' ist. Bestellen Sie nach Inventar-ID, Mitarbeiter-ID.

Wenn das Präfix ganz links nicht kombiniert werden kann

Wählen Sie Miet-ID, Mitarbeiter-ID aus sakila.rental, wobei Mietdatum = '2005-08-23 21:01:09' ist. Bestellen Sie nach Kunden-ID.

Wenn die erste Spalte der Abfragebereich ist

Wählen Sie Miet-ID, Mitarbeiter-ID aus sakila.rental, wobei Mietdatum > '2005-08-22' ist. Bestellen Sie nach Inventar-ID, Kunden-ID.

bei mehreren gleichen Bedingungen

SELECT Vermietungs-ID, Mitarbeiter-ID FROM sakila.rental WHERE Vermietungsdatum ='2005-08-23 21:01:09' und Inventar-ID in (1,2) ORDER BY Kunden-ID

Einfach ausgedrückt werden diejenigen sortiert, die nicht dem äußersten linken Präfix des Index entsprechen.

Zusammenfassen

Heute haben wir die Index-Scan-Sortierung in MySQL erklärt. Morgen werden wir weitere Methoden zum Erstellen von Hochleistungsindizes vorstellen. Bleiben Sie dran und wir sehen uns im nächsten Artikel!

Oben finden Sie ausführliche Informationen zur einfachen Verwendung des MySQL-Index-Scans. Weitere Informationen zur Sortierung per MySQL-Index-Scan finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!