Warum wird die MySQL-Paging-Funktion bei Verwendung von Limits immer langsamer?

Aniu ist gerade in eine neue Firma eingetreten. Seine erste Aufgabe war es, die Daten in der Bestelltabelle basierend auf Bedingungen in eine Datei zu exportieren. Aniu dachte: Das ist zu einfach, also schrieb er schnell die folgende Anweisung und teilte dem Tester mit, dass sein Code ein ausgenommenes Produkt sei.

Die Anweisung lautet wie folgt:

Wählen Sie * aus Bestellungen, bei denen Name = "lilei" und Erstellungszeit > "2020-01-01 00:00:00" Limit Start, Ende

Nachdem die Funktion eine Zeit lang online war, begann die Produktion unerwarteterweise eine Frühwarnung auszugeben, die anzeigte, dass es sich bei diesem SQL um ein langsames SQL mit einer Ausführungszeit von mehr als 50 Sekunden handelte, was das Geschäft ernsthaft beeinträchtigte.
Aniu bat schnell den großen Bruder Yuanyuan, ihm bei der Suche nach der Ursache zu helfen. Yuanyuan löste das Problem schnell und führte für Aniu das folgende Experiment durch:

1. Testexperiment

MySQL-Paging verwendet direkt die Paging-Anweisungen „Limit Start“ und „Count“:

select * ab Produktlimit Start, Anzahl

Wenn die Startseite klein ist, treten bei der Abfrage keine Leistungsprobleme auf. Betrachten wir die Ausführungszeit der Seitenumbrüche von 10, 100, 1000 und 10000 (20 Datensätze pro Seite) wie folgt:

Wählen Sie * aus Produktlimit 10, 20 0,016 Sekunden. Wählen Sie * aus Produktlimit 100, 20 0,016 Sekunden. Wählen Sie * aus Produktlimit 1000, 20 0,047 Sekunden. Wählen Sie * aus Produktlimit 10000, 20 0,094 Sekunden.

Wir haben gesehen, dass mit zunehmender Anzahl der Startdatensätze auch die Zeit zunimmt. Dies zeigt, dass die Begrenzung der Paging-Anweisung eng mit der Anzahl der Startseiten zusammenhängt.
Dann ändern wir den Startrekord auf 40 W (etwa die Hälfte des Rekords).

select * from product limit 400000, 20 3.229 Sekunden

Schauen wir uns den Zeitpunkt an, zu dem wir die letzte Seite der Datensätze erhalten

select * from product limit 866613, 20 37.44 Sekunden

Für eine Seite mit der höchsten Seitenzahl wie dieser ist diese Zeit offensichtlich unerträglich.
Daraus können wir auch zwei Dinge schließen:
Die Abfragezeit der Limit-Anweisung ist proportional zur Position des Startdatensatzes.
Die MySQL-Limit-Anweisung ist sehr praktisch, aber nicht für die direkte Verwendung bei Tabellen mit vielen Datensätzen geeignet.

2. Leistungsoptimierungsmethode für das Problem des begrenzten Paging

2.1 Verwenden des überdeckenden Index der Tabelle zur Beschleunigung von Paging-Abfragen

Wir alle wissen, dass die Abfrage sehr schnell ist, wenn die Anweisung, die eine Indexabfrage verwendet, nur diese Indexspalte (den abdeckenden Index) enthält.
Da die Indexsuche über einen optimierten Algorithmus verfügt und sich die Daten im Abfrageindex befinden, muss nicht nach der relevanten Datenadresse gesucht werden, was viel Zeit spart.
Darüber hinaus verfügt MySQL auch über einen zugehörigen Index-Cache. Die Wirkung ist besser, wenn der Cache bei hoher Parallelität verwendet wird.
In unserem Beispiel wissen wir, dass das ID-Feld der Primärschlüssel ist, daher ist der Standard-Primärschlüsselindex eingeschlossen. Sehen wir uns nun an, wie die Abfrage mit dem überdeckenden Index funktioniert:
Diesmal fragen wir die Daten der letzten Seite ab (mithilfe eines überdeckenden Index, der nur die Spalte „id“ enthält) und zwar wie folgt:

Wählen Sie die ID aus dem Produktlimit 866613, 20

Die Abfragezeit beträgt 0,2 Sekunden und ist damit etwa 100-mal schneller als die 37,44 Sekunden, die zum Abfragen aller Spalten erforderlich sind.
Wenn wir auch alle Spalten abfragen möchten, gibt es zwei Möglichkeiten.

2.2 Verwenden des id>=-Formats:

Wählen Sie * aus Produkt 
WHERE ID > =(Wählen Sie die ID aus dem Produktlimit 866613, 1) Limit 20

Die Abfragezeit beträgt 0,2 Sekunden, was einen qualitativen Sprung darstellt.

2.3 Verwenden von join

SELECT * FROM Produkt a 
JOIN (wähle ID aus Produktlimit 866613, 20) b ON a.ID = b.id

Zusammenfassen:

Glauben Sie, ich habe den Grund nicht genannt? Der Grund ist, dass bei Verwendung von select * das Limit 600000 direkt verwendet wird, 10 Scans etwa 600.000 Daten sind und 600.000 Mal zur Tabelle zurückgekehrt werden muss, was bedeutet, dass der Großteil der Leistung beim Direktzugriff verbraucht wird und am Ende nur 10 Daten verwendet werden. Wenn Sie zuerst die ID herausfinden und dann die Datensätze durch Zuordnung abfragen, geht dies viel schneller, da der Index die ID, die die Bedingungen erfüllt, schnell finden und dann 10 Mal zur Tabelle zurückkehren kann. Wir können die gewünschten Daten abrufen.

Damit ist der Artikel darüber, warum MySQL-Paging mit Limit immer langsamer wird, abgeschlossen. Weitere Informationen zur Langsamkeit von MySQL-Paging-Limits finden Sie in den vorherigen Artikeln von 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!