Lassen Sie uns ausführlich über die LIMIT-Anweisung in MySQL sprechen

Vor kurzem haben viele Freunde Kindern in der Q&A-Gruppe eine Frage zu LIMIT gestellt. Lassen Sie mich diese Frage im Folgenden kurz beschreiben.

Frage

Damit sich die Geschichte reibungslos entwickeln kann, müssen wir zunächst eine Tabelle haben:

TABELLE ERSTELLEN t (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    Schlüssel1 VARCHAR(100),
    gemeinsames_Feld VARCHAR(100),
    Primärschlüssel (ID),
    SCHLÜSSEL idx_key1 (Schlüssel1)
) Engine=InnoDB CHARSET=utf8;

Tabelle t enthält 3 Spalten, die Spalte „ID“ ist der Primärschlüssel und die Spalte „Key1“ ist die sekundäre Indexspalte. Die Tabelle enthält 10.000 Datensätze.

Wenn wir die folgende Anweisung ausführen, wird der sekundäre Index idx_key1 verwendet:

mysql> EXPLAIN SELECT * FROM t ORDER BY Schlüssel1 LIMIT 1;
+----+----------+----------+---------+-------+---------------+-----------+----------+----------+---------+------+------+------+------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+-------+---------------+-----------+----------+----------+---------+------+------+------+------+
| 1 | EINFACH | t | NULL | Index | NULL | idx_key1 | 303 | NULL | 1 | 100,00 | NULL |
+----+----------+----------+---------+-------+---------------+-----------+----------+----------+---------+------+------+------+------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Dies ist leicht zu verstehen, da im sekundären Index idx_key1 die Spalte key1 geordnet ist. Wenn die Abfrage den ersten nach der Spalte „key1“ sortierten Datensatz abrufen soll, muss MySQL nur den ersten sekundären Indexdatensatz von idx_key1 abrufen und dann direkt zur Tabelle zurückkehren, um den vollständigen Datensatz abzurufen.

Wenn wir jedoch LIMIT 1 in der obigen Anweisung in LIMIT 5000, 1 ändern, müssen wir die gesamte Tabelle scannen und eine Dateisortierung durchführen. Der Ausführungsplan sieht wie folgt aus:

mysql> EXPLAIN SELECT * FROM t ORDER BY Schlüssel1 LIMIT 5000, 1;
+----+----------+----------+---------+------+---------------+-----------+---------+---------+------+---------+------+----------+----------------+
| ID | Auswahltyp | Tabelle | Partitionen | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | gefiltert | Extra |
+----+----------+----------+---------+------+---------------+-----------+---------+---------+------+---------+------+----------+----------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 9966 | 100,00 | Dateisortierung wird verwendet |
+----+----------+----------+---------+------+---------------+-----------+---------+---------+------+---------+------+----------+----------------+
1 Zeile im Satz, 1 Warnung (0,00 Sek.)

Einige Schüler verstehen nicht: LIMIT 5000, 1 kann auch den Sekundärindex idx_key1 verwenden. Wir können zuerst den 5001. Sekundärindexdatensatz scannen und dann eine Tabellenrückgabeoperation für den 5001. Sekundärindexdatensatz durchführen. Dieser Aufwand ist definitiv besser als ein vollständiger Tabellenscan + Dateisortierung.

Ich muss Ihnen leider mitteilen, dass die oben beschriebene Idealsituation aufgrund von Mängeln in der MySQL-Implementierung nicht eintreten wird. Es wird dummerweise nur ein vollständiger Tabellenscan + Dateisortierung durchgeführt. Lassen Sie uns darüber sprechen, was los ist.

Serverebene und Speichermodulebene

Wie wir alle wissen, ist MySQL tatsächlich in die Serverschicht und die Speicher-Engine-Schicht unterteilt:

• Die Serverebene ist für die Handhabung einiger allgemeiner Aufgaben verantwortlich, beispielsweise für die Verbindungsverwaltung, die Analyse der SQL-Syntax und die Analyse von Ausführungsplänen.
• Die Speicher-Engine-Schicht ist für die spezifische Datenspeicherung verantwortlich, z. B. ob die Daten in Dateien oder im Speicher gespeichert werden und welches das spezifische Speicherformat ist. Wir verwenden jetzt grundsätzlich die InnoDB-Speicher-Engine und andere Speicher-Engines werden selten verwendet, daher werden wir keine anderen Speicher-Engines einbeziehen.

Die Ausführung einer SQL-Anweisung in MySQL erfordert mehrere Interaktionen zwischen der Serverebene und der Speicher-Engine-Ebene, um das Endergebnis zu erhalten. Betrachten Sie beispielsweise die folgende Abfrage:

SELECT * FROM t WHERE key1 > ‚a‘ AND key1 < ‚b‘ AND gemeinsames_Feld != ‚a‘;

Die Serverebene analysiert, dass die obige Anweisung mit den folgenden beiden Lösungen ausgeführt werden kann:

• Lösung 1: Vollständigen Tabellenscan verwenden
• Lösung 2: Verwenden Sie den Sekundärindex idx_key1. In diesem Fall müssen Sie alle Sekundärindexdatensätze scannen, deren Werte in der Spalte key1 zwischen ('a', 'b') liegen, und jeder Sekundärindexdatensatz muss zurückgelistet werden.

Die Serverebene analysiert, welche der beiden oben genannten Lösungen die geringeren Kosten verursacht, und wählt dann die kostengünstigere Lösung als Ausführungsplan aus. Anschließend wird die von der Speicher-Engine bereitgestellte Schnittstelle aufgerufen, um die Abfrage tatsächlich auszuführen.

Hier gehen wir davon aus, dass Lösung 2 übernommen wird, d. h., dass der sekundäre Index idx_key1 zum Ausführen der obigen Abfrage verwendet wird. Dann kann die Kommunikation zwischen der Serverebene und der Speichermodulebene wie folgt ablaufen:

Serverebene: „Hey, bitte überprüfen Sie den ersten Datensatz im Intervall (‚a‘, ‚b‘) des sekundären Index idx_key1 und geben Sie mir den vollständigen Datensatz zurück, nachdem Sie die Tabelle zurückgegeben haben.“

InnoDB antwortet: „Verstanden. Ich werde es sofort prüfen.“ Dann lokalisiert InnoDB schnell den ersten sekundären Indexdatensatz im Scanintervall (,a‘, ,b‘) durch den B+-Baum, der dem sekundären Index idx_key1 entspricht, und gibt dann den vollständigen gruppierten Indexdatensatz an die Serverebene zurück.

Nach dem Empfang des vollständigen Clustered-Index-Datensatzes ermittelt die Serverebene weiterhin, ob die Bedingung common_field!='a' erfüllt ist. Wenn nicht, wird der Datensatz verworfen, andernfalls wird der Datensatz an den Client gesendet. Sagen Sie dann zur Speicher-Engine: „Bitte gib mir den nächsten Datensatz“

Tipps:

Hier wird der Datensatz beim Senden an den Client tatsächlich an den lokalen Netzwerkpuffer gesendet. Die Puffergröße wird durch net_buffer_length gesteuert und die Standardgröße beträgt 16 KB. Nur wenn der Puffer voll ist, wird das Netzwerkpaket tatsächlich an den Client gesendet.

InnoDB: „Verstanden, ich werde es sofort überprüfen.“ InnoDB findet den nächsten sekundären Indexdatensatz im Intervall ('a', 'b') von idx_key1 basierend auf dem next_record-Attribut des Datensatzes, führt dann eine Tabellenrückgabeoperation aus und gibt den erhaltenen vollständigen gruppierten Indexdatensatz an die Serverebene zurück.

Tipps:
Sowohl gruppierte Indexdatensätze als auch sekundäre Indexdatensätze enthalten ein Attribut namens next_record. Jeder Datensatz ist basierend auf next_record mit einer verknüpften Liste verbunden, und die Datensätze in der verknüpften Liste sind nach Schlüsselwert sortiert (bei gruppierten Indizes bezieht sich der Schlüsselwert auf den Wert des Primärschlüssels, bei sekundären Indexdatensätzen bezieht sich der Schlüsselwert auf den Wert der sekundären Indexspalte).

Nach dem Empfang des vollständigen Clustered-Index-Datensatzes ermittelt die Serverebene weiterhin, ob die Bedingung common_field!='a' erfüllt ist. Wenn nicht, wird der Datensatz verworfen, andernfalls wird der Datensatz an den Client gesendet. Sagen Sie dann zur Speicher-Engine: „Bitte gib mir den nächsten Datensatz“

… und wiederholen Sie dann den obigen Vorgang immer wieder.

bis:

Das heißt, bis InnoDB feststellt, dass der nächste sekundäre Indexdatensatz, der gemäß dem next_record des sekundären Indexdatensatzes erhalten wurde, nicht im Intervall ('a', 'b') liegt, teilt es der Serverebene mit: „Okay, es gibt keinen nächsten Datensatz im Intervall ('a', 'b')“.

Wenn die Serverschicht von InnoDB die Meldung erhält, dass kein nächster Datensatz vorhanden ist, beendet sie die Abfrage.

Mittlerweile kennt jeder den grundlegenden Interaktionsprozess zwischen der Serverebene und der Speicher-Engine-Ebene.

Was zum Teufel ist LIMIT?

Es mag für jeden etwas überraschend sein, dass MySQL den Inhalt der LIMIT-Klausel nur dann verarbeitet, wenn die Serverebene bereit ist, Datensätze an den Client zu senden. Nehmen Sie die folgende Anweisung als Beispiel:

Wählen Sie * aus t Bestellen nach Schlüssel1 LIMIT 5000, 1;

Wenn Sie die obige Abfrage mit idx_key1 ausführen, verarbeitet MySQL sie folgendermaßen:

• Die Serverebene fordert InnoDB nach dem ersten Datensatz an. InnoDB erhält den ersten sekundären Indexdatensatz von idx_key1, führt eine Tabellenrückgabeoperation aus, um den vollständigen Clusterindexdatensatz zu erhalten, und gibt ihn dann an die Serverebene zurück. Die Serverebene ist bereit, es an den Client zu senden, und stellt fest, dass eine LIMIT-Anforderung von 5000, 1 vorliegt, was bedeutet, dass nur der 5001. Datensatz, der die Bedingungen erfüllt, tatsächlich an den Client gesendet werden kann. Lassen Sie uns hier also eine Statistik erstellen. Wir gehen davon aus, dass die Serverebene eine Variable namens limit_count verwaltet, um zu zählen, wie viele Datensätze übersprungen wurden. Zu diesem Zeitpunkt sollte limit_count auf 1 gesetzt sein.
• Anschließend fordert die Serverebene InnoDB nach dem nächsten Datensatz an. InnoDB findet dann den nächsten sekundären Indexdatensatz basierend auf dem Attribut next_record des sekundären Indexdatensatzes und gibt anschließend den vollständigen gruppierten Indexdatensatz an die Serverebene zurück. Wenn die Serverebene es an den Client sendet, stellt sie fest, dass limit_count nur 1 ist. Daher gibt sie den Vorgang des Sendens an den Client auf und erhöht limit_count um 1. Zu diesem Zeitpunkt wird limit_count 2.
• ... Wiederholen Sie die obigen Schritte
• Wenn limit_count gleich 5000 ist, sendet die Serverebene tatsächlich den vollständigen, von InnoDB zurückgegebenen Clusterindexdatensatz an den Client.

Aus dem obigen Prozess können wir ersehen, dass MySQL erst dann feststellt, ob die LIMIT-Klausel die Anforderungen erfüllt, wenn der Datensatz tatsächlich an den Client gesendet wird. Wenn daher der sekundäre Index zur Ausführung der obigen Abfrage verwendet wird, sind 5001 Tabellenrückgabevorgänge erforderlich. Bei der Analyse des Ausführungsplans wird die Serverebene den Eindruck haben, dass die Kosten für die Ausführung so vieler Tabellenrückgaben zu hoch sind und dass dies nicht so schnell ist wie ein direkter vollständiger Tabellenscan + Dateisortierung. Daher wählt sie Letzteres zur Ausführung der Abfrage aus.

was zu tun?

Ist es aufgrund der Einschränkungen der MySQL-Implementierung der LIMIT-Klausel nicht möglich, Abfragen durch die Verwendung sekundärer Indizes bei der Verarbeitung von Anweisungen wie LIMIT 5000, 1 zu beschleunigen? Tatsächlich ist es das nicht. Schreiben Sie die obige Anweisung einfach um in:

Wählen Sie * aus t, (Wählen Sie ID aus t Bestellen nach Schlüssel1 Limit 5000, 1) als d
    WO t.id = d.id;

Auf diese Weise existiert SELECT id FROM t ORDER BY key1 LIMIT 5000, 1 als separate Unterabfrage. Da die Abfrageliste der Unterabfrage nur eine ID-Spalte hat, kann MySQL die Unterabfrage ausführen, indem nur der sekundäre Index idx_key1 gescannt wird, und dann die Tabelle t basierend auf dem in der Unterabfrage erhaltenen Primärschlüsselwert durchsuchen.

Dadurch müssen Sie für die ersten 5.000 Datensätze nicht mehr zur Tabelle zurückkehren, was die Abfrageeffizienz erheblich verbessert!

Tucao

Wann werden die Leute, die MySQL entwickelt haben, diese superdumme Implementierung der LIMIT-Klausel reparieren? Benutzer müssen manuell versuchen, den Optimierer zu täuschen, um die Abfrageeffizienz zu verbessern ~

Dies ist das Ende dieses Artikels über die LIMIT-Anweisung in MySQL. Weitere Informationen zur LIMIT-Anweisung in MySQL finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!