Gründe und Optimierungslösungen für langsames MySQL-Limit-Paging mit großen Offsets

In MySQL verwenden wir normalerweise ein Limit, um die Paging-Funktion auf der Seite abzuschließen. Wenn die Datenmenge jedoch einen großen Wert erreicht, wird die Reaktionsgeschwindigkeit der Schnittstelle umso langsamer, je weiter Sie auf der Seite blättern.

In diesem Artikel werden hauptsächlich die Gründe erläutert, warum Limit Paging bei großen Offsets langsam ist, sowie Optimierungslösungen. Um diese Situation zu simulieren, werden im Folgenden zunächst die Tabellenstruktur und das ausgeführte SQL vorgestellt.

Szenariosimulation

Anweisung „Tabelle erstellen“

Die Struktur der Benutzertabelle ist relativ einfach und umfasst ID, Geschlecht und Name. Um die Änderung der SQL-Ausführungszeit deutlicher zu machen, gibt es 9 Namensspalten.

CREATE TABLE `Benutzer` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel',
 `sex` tinyint(4) NULL DEFAULT NULL COMMENT 'Geschlecht 0-Männlich 1-Weiblich',
 `name1` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name2` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name3` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name4` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name5` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name6` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name7` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name8` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 `name9` varchar(255) ZEICHENSATZ utf8 SORTIMENT utf8_general_ci NULL STANDARD NULL KOMMENTAR 'Name',
 PRIMÄRSCHLÜSSEL (`id`) MIT BTREE,
 INDEX `Geschlecht`(`Geschlecht`) MIT BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamisch;

Datenfüllung

Hier wird eine gespeicherte Prozedur erstellt, um die Daten mit insgesamt 9.000.000 Datensätzen auszufüllen. Nachdem die Funktion ausgeführt wurde, wird eine weitere SQL-Anweisung ausgeführt, um das Feld „Geschlecht“ zu ändern.

PS: Die Ausführung dieser Funktion dauert lange. Bei mir dauerte die Ausführung 617,284 Sekunden.

CREATE DEFINER=`root`@`localhost` VERFAHREN `data`()
beginnen 
 deklariere i int; 
 setze i=1; 
 während(i<=9000000)mache 
  in Benutzerwerte einfügen (i,0,i,i,i,i,i,i,i,i,i);
  setze i=i+1; 
 Ende während;
Ende

-- Legen Sie das Geschlecht von Benutzern mit geraden IDs auf 1-weiblich fest. Update User Set Sex=1, wobei ID%2=0 ist.

SQL und Ausführungszeit

Wie Sie sehen, ist die Ausführungszeit umso länger, je größer der Grenzversatz ist.

Ursachenanalyse

Lassen Sie uns zunächst den Ausführungsprozess dieser SQL-Anweisung analysieren und dabei die erste Zeile der obigen Tabelle als Beispiel nehmen.

Da es sich bei der Spalte „Geschlecht“ um eine Indexspalte handelt, durchläuft MySQL den Geschlechtsindexbaum und findet die Daten mit Geschlecht=1.

Da der nicht gruppierte Index den Wert der Primärschlüssel-ID speichert und die Abfrageanweisung eine Abfrage aller Spalten erfordert, erfolgt hier eine Tabellenrückgabe. Nachdem die Daten mit dem Wert 1 im Geschlechtsindexbaum gefunden wurden, wird der Wert seines Blattknotens, d. h. der Wert der Primärschlüssel-ID, verwendet, um die Werte anderer Spalten (Name, Geschlecht) dieser Zeile im Primärschlüsselindexbaum abzufragen und schließlich zum Ergebnissatz zurückzukehren, sodass die erste Datenzeile erfolgreich abgefragt wurde.

Die letzte SQL-Anweisung erfordert das Limit 100, 10, was bedeutet, dass Daten von 101 bis 110 abgefragt werden. MySQL fragt jedoch die ersten 110 Zeilen ab und verwirft dann die ersten 100 Zeilen. Schließlich verbleiben nur die Zeilen 101 bis 110 im Ergebnissatz und die Ausführung wird beendet.

Zusammenfassend lässt sich sagen, dass im obigen Ausführungsprozess die Ausführungszeit eines Grenzwerts mit großem Offset aus folgenden Gründen länger wird:

• Die Abfrage aller Spalten führt zu einer Tabellenrückgabe
• Begrenzen Sie a, b, um die ersten a + b Daten abzufragen und dann die ersten a Daten zu verwerfen
  

Durch die Kombination der beiden oben genannten Gründe verbringt MySQL viel Zeit mit der Tabellenrückgabe, und das Ergebnis einer Tabellenrückgabe wird nicht im Ergebnissatz angezeigt, was dazu führt, dass die Abfragezeit immer länger wird.

Optimierungsplan

Abdeckungsindex

Da ungültige Tabellenrückgaben der Hauptgrund für langsame Abfragen sind, besteht der Optimierungsplan darin, die Anzahl der Tabellenrückgaben zu reduzieren. Angenommen, wir erhalten in den Grenzen a, b zuerst die IDs der Daten a+1 bis a+b und kehren dann zur Tabelle zurück, um Daten in anderen Spalten zu erhalten. Auf diese Weise wird die Anzahl der Tabellenrückgaben reduziert und die Geschwindigkeit wird definitiv viel schneller.

Dabei handelt es sich um sogenannte Covering-Indizes. Die sogenannten Covering-Indizes ermöglichen es, aus nicht-primären Clustered-Indizes die gewünschten Daten abzurufen, ohne über die Tabellenrückgabe weitere Spalten aus dem Primärschlüsselindex abfragen zu müssen, was die Performance deutlich steigern kann.

Basierend auf dieser Idee besteht die Optimierungslösung darin, zuerst die Primärschlüssel-ID abzufragen und dann andere Spaltendaten basierend auf der Primärschlüssel-ID abzufragen. Das optimierte SQL und die Ausführungszeit werden in der folgenden Tabelle angezeigt.

Tatsächlich wurde die Ausführungseffizienz erheblich verbessert.

Bedingte Filterung

Natürlich gibt es auch einen fehlerhaften Ansatz, nämlich die bedingte Filterung auf Grundlage der Sortierung.

Beispielsweise möchte ich in der obigen Benutzertabelle die Seitenbegrenzung verwenden, um Daten von 1000001 bis 1000010 abzurufen. Ich kann SQL wie folgt schreiben:

Wählen Sie * vom Benutzer, wobei Geschlecht = 1 und ID > (Wählen Sie ID vom Benutzer, wobei Geschlecht = 1, Limit 1000000, 1) Limit 10;

Diese Optimierungsmethode ist jedoch an Bedingungen geknüpft: Die Primärschlüssel-ID muss in der richtigen Reihenfolge sein. Unter geordneten Voraussetzungen können statt des Primärschlüssels id auch andere Felder wie z.B. der Erstellungszeitpunkt verwendet werden, Voraussetzung ist allerdings, dass dieses Feld indiziert ist.

Kurz gesagt, es gibt viele Einschränkungen bei der Verwendung bedingter Filterung zur Optimierung des Grenzwerts. Es wird im Allgemeinen empfohlen, zur Optimierung abdeckende Indizes zu verwenden.

Zusammenfassung

In diesem Artikel werden hauptsächlich die Gründe für die langsame Ausführung von Limit Paging mit großen Offsets analysiert und entsprechende Optimierungslösungen vorgeschlagen. Es wird empfohlen, abdeckende Indizes zu verwenden, um das Problem der langen Ausführungszeit von Limit Paging mit großen Offsets zu optimieren.

Ich hoffe, das hilft allen.

Oben finden Sie ausführliche Informationen zu den Gründen, warum MySQL-Limit-Paging bei großen Offsets langsam ist, sowie zu Optimierungslösungen. Weitere Informationen zu MySQL-Limit-Paging finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!