Techniken zur Optimierung von MySQL-Paging-Abfragen

In Anwendungen mit Paging-Abfragen sind Abfragen, die LIMIT und OFFSET enthalten, sehr häufig und fast jede davon verfügt über eine ORDER BY-Klausel. Wenn Indexsortierung verwendet wird, ist dies für die Leistungsoptimierung sehr hilfreich, da der Server andernfalls viele Dateien sortieren muss.

Ein häufiges Problem ist, dass der Offsetwert zu groß ist. Wenn die Abfrage beispielsweise LIMIT 10000, 20 lautet, werden 10020 Zeilen generiert und die vorherigen 10000 Zeilen verworfen, was sehr kostspielig ist. Unter der Annahme, dass alle Seiten mit der gleichen Häufigkeit aufgerufen werden, wird bei einer solchen Abfrage im Durchschnitt die Hälfte der Tabelle durchsucht. Um sie zu optimieren, können Sie die maximale Anzahl der Seiten begrenzen, auf die in der paginierten Ansicht zugegriffen werden kann, oder große und kostengünstige Abfragen effizienter gestalten.

Ein einfacher Trick zur Verbesserung der Leistung besteht darin, Abfragen auf einem abdeckenden Index statt auf der gesamten Zeile durchzuführen. Sie können das Ergebnis mit der gesamten Zeile vereinigen und erhalten dann die zusätzlichen Spalten, die Sie benötigen. Dies ist effizienter, beispielsweise die folgende Abfrage:

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

Wenn die Datentabelle groß ist, können Sie sie wie folgt optimieren:

SELECT film.film_id, film.beschreibung
VON sakila.film
	Innerer Join (
    Wählen Sie Film-ID aus Sakila.Film
    ORDER BY Titel LIMIT 50, 5)
  ) als lim USING(film_id);

Diese „abgeleitete Join-Abfrage“ funktioniert effektiv, da sie Indizes verwendet, um die Anzahl der Zeilen zu reduzieren, auf die der Server zum Überprüfen der Daten zugreifen muss. Sobald die zur Überprüfung erforderlichen Zeilen gefunden wurden, werden sie mit den Zeilen der entsprechenden Datentabelle verknüpft, um die anderen Spalten der entsprechenden Zeilen zu erhalten.

Manchmal können Sie das Limit auch in eine Abfrage mit fester Position umwandeln, die durch Bereichsscannen des Indexes vervollständigt werden kann. Wenn Sie beispielsweise eine Spalte mit fester Position namens „Position“ vorab berechnen, können Sie die Abfrage folgendermaßen umschreiben:

Wählen Sie Film-ID und Beschreibung aus sakila.film
WO Position ZWISCHEN 50 UND 54 ORDER BY Position;

Sortierte Daten können auf ähnliche Weise behandelt werden, sind aber normalerweise von der GROUP BY-Operation betroffen. In den meisten Fällen muss der Sortierwert im Voraus berechnet und gespeichert werden.

Das eigentliche Problem mit LIMIT und OFFSET ist OFFSET, was bedeutet, dass der Server viele Zeilen verwirft. Wird die Position der nächsten abzurufenden Zeile durch ein geordnetes Lesezeichen festgehalten, kann ab der letzten Position auf die nächsten Daten zugegriffen werden. Wenn Sie beispielsweise Mietunterlagen paginieren müssen, beginnend mit der aktuellsten Mietunterlage und dann rückwärts, können Sie sich darauf verlassen, dass der Primärschlüssel der Unterlagen immer ansteigt. Sie können also die erste Seite der Daten folgendermaßen abfragen:

Wählen Sie * aus sakila.rental
ORDER BY Miet-ID DESC LIMIT 20;

Diese Abfrage gibt Daten zwischen 16049 und 16030 zurück. Die nächste Abfrage kann dort beginnen, wo sie zuvor endete:

Wählen Sie * aus sakila.rental
WO: Miet-ID < 16030 
ORDER BY Miet-ID DESC LIMIT 20;

Dieser Trick funktioniert unabhängig davon, wie weit vom Offset entfernt Sie Ihre Abfrage starten.

Zu den weiteren Techniken gehören die Verwendung vorkalkulierter Statistiken oder Abfragen durch die Verknüpfung von Tabellen mit redundanten Primärschlüsseln und Sortierspalten. Beide Verfahren verbessern die Abfrageeffizienz, indem sie Speicherplatz gegen Zeit eintauschen.

Oben sind die Details der Optimierungstechniken für MySQL-Paging-Abfragen aufgeführt. Weitere Informationen zur Optimierung von MySQL-Paging-Abfragen finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!