Vermeiden Sie die Verwendung von OFFSET und LIMIT in paginierten Abfragen.

Die Zeiten, in denen man sich keine Sorgen um die Optimierung der Datenbankleistung machen musste, sind vorbei. Die Zeit steht nicht still. Jeder neue Unternehmer im Bereich der Hochtechnologie will ein weiteres Facebook schaffen und dabei alle Daten sammeln, die er erreichen kann. Diese Daten sind für das Geschäft notwendig, um qualitativ hochwertigere Modelle zu trainieren, die beim Geldverdienen helfen. Unter diesen Umständen müssen Programmierer APIs erstellen, die eine schnelle und zuverlässige Verarbeitung von riesigen Datenmengen ermöglichen.

Wenn Sie sich bereits eine Weile mit dem Entwurf von Serverteilen von Anwendungen oder Datenbanken beschäftigen, haben Sie wahrscheinlich Code geschrieben, um Abfragen mit Paging durchzuführen. Zum Beispiel — so:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Stimmt das?

Wenn Sie das Paging genau so gemacht haben, muss ich bedauernd feststellen, dass Sie dies keineswegs auf die effektivste Weise getan haben.

Wollen Sie mir widersprechen? Sie können nicht Zeit Zeit. Slack, Shopify und Mixmax nutzt bereits die Techniken, von denen ich heute erzählen möchte.

Nennen Sie mir bitte einen Backend-Entwickler, der noch nie genutzt hat OFFSET und LIMIT um Abfragen mit Paging durchzuführen. In MVP (Minimum Viable Product, minimal funktionsfähiges Produkt) und in Projekten, in denen kleine Datenmengen verwendet werden, ist dieser Ansatz durchaus anwendbar. Er funktioniert sozusagen "einfach".

Aber wenn man von Grund auf zuverlässige und effiziente Systeme erstellen möchte, sollte man im Voraus dafür sorgen, dass die Abfragen an die Datenbanken, die in solchen Systemen verwendet werden, effizient ausgeführt werden.

Heute werden wir über die Probleme sprechen, die mit den weit verbreiteten (leider ist es so) Implementierungen von Mechanismen zur Ausführung von Abfragen mit Paging verbunden sind, und darüber, wie man eine hohe Leistung bei solchen Abfragen erzielen kann.

Was ist falsch mit OFFSET und LIMIT?

Wie bereits erwähnt, OFFSET und LIMIT schneiden sich gut in Projekten, in denen nicht mit großen Datenmengen gearbeitet werden muss.

Das Problem tritt auf, wenn die Datenbank so groß wird, dass sie nicht mehr in den Speicher des Servers passt. Aber dennoch müssen bei der Arbeit mit dieser Datenbank Abfragen mit Paging verwendet werden.

Um dieses Problem zu erkennen, muss eine Situation entstehen, in der das DBMS auf eine ineffiziente Operation des vollständigen Tabellenscans (Full Table Scan) zurückgreift, wenn es jede Anfrage mit Paging ausführt (währenddessen können auch Einfüge- und Löschoperationen stattfinden, und veraltete Daten sind hierbei nicht erforderlich!).

Was ist ein „vollständiger Tabellenscan“ (oder „sequentieller Tabellenscan“, Sequential Scan)? Dies ist eine Operation, bei der das DBMS jede Zeile der Tabelle nacheinander lesen, das heißt, die enthaltenen Daten, und diese auf Übereinstimmung mit einer bestimmten Bedingung überprüfen. Es ist bekannt, dass dieser Typ des Tabellenscans der langsamste ist. Das liegt daran, dass bei seiner Ausführung viele E/A-Operationen durchgeführt werden, die das Speichersystem des Servers einbeziehen. Die Situation wird durch Verzögerungen, die mit der Arbeit mit Daten, die auf Festplatten gespeichert sind, verbunden sind, und durch die Tatsache, dass der Transfer von Daten von der Festplatte in den Speicher eine ressourcenintensive Operation ist, verschärft.

Zum Beispiel haben Sie Aufzeichnungen über 100000000 Benutzer und führen eine Anfrage mit der Konstruktion aus OFFSET 50000000. Das bedeutet, dass das DBMS alle diese Aufzeichnungen laden muss (obwohl wir diese nicht einmal brauchen!), sie in den Speicher bringen und erst dann beispielsweise 20 Ergebnisse abrufen kann, die in LIMIT.

Nehmen wir an, das könnte so aussehen: „Wählen Sie Zeilen von 50000 bis 50020 aus 100000“. Das heißt, um diese Anfrage auszuführen, muss das System zuerst 50000 Zeilen laden. Sehen Sie, wie viel unnötige Arbeit es verrichten muss?

Wenn Sie es nicht glauben, schauen Sie sich das Beispiel an, das ich mit den Möglichkeiten von db-fiddle.com. 

Beispiel auf db-fiddle.com

Dort gibt es links im Feld Schema SQL, einen Code, der 100000 Zeilen in die Datenbank einfügt, und rechts im Feld Query SQL, sind zwei Abfragen zu sehen. Die erste, langsame, sieht so aus:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Und die zweite, die eine effiziente Lösung für dieselbe Aufgabe darstellt, so:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Um diese Abfragen auszuführen, genügt es, auf die Schaltfläche zu klicken. Ausführen Im oberen Teil der Seite. Wenn Sie dies tun, vergleichen wir die Daten über die Ausführungszeiten der Anfragen. Es stellt sich heraus, dass die Ausführung einer ineffizienten Anfrage mindestens 30-mal länger dauert als die Ausführung der zweiten (von Ausführung zu Ausführung variiert diese Zeit beispielsweise, das System kann berichten, dass die Ausführung der ersten Anfrage 37 ms in Anspruch genommen hat, während die Ausführung der zweiten 1 ms dauert).

Und wenn es mehr Daten gibt, wird alles noch schlimmer aussehen (um dies festzustellen, werfen Sie einen Blick auf mein Beispiel mit 10 Millionen Zeilen).

Das, was wir gerade besprochen haben, sollte Ihnen ein gewisses Verständnis dafür geben, wie Anfragen an Datenbanken tatsächlich verarbeitet werden.

Bedenken Sie, dass je größer der Wert ist, OFFSET desto länger dauert die Anfrage.

Was sollte man anstelle der Kombination OFFSET und LIMIT verwenden?

Statt einer Kombination OFFSET und LIMIT sollte eine Konstruktion verwendet werden, die nach folgendem Schema aufgebaut ist:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Dies ist die Ausführung einer Anfrage mit Pagination basierend auf einem Cursor (Cursor basierte Pagination).

Anstatt die aktuellen OFFSET und LIMIT lokal zu speichern und sie mit jeder Anfrage zu übermitteln, sollten Sie den letzten erhaltenen Primärschlüssel speichern (in der Regel ist dies ID) und LIMITund in der Folge ergeben sich Anfragen, die der oben genannten ähneln.

Warum? Der Grund ist, dass Sie durch die explizite Angabe der Identifikation der zuletzt gelesenen Zeile Ihrer DBMS mitteilen, wo sie die Suche nach den erforderlichen Daten beginnen soll. Darüber hinaus wird die Suche durch die Verwendung des Schlüssels effizienter, das System muss sich nicht mit Zeilen befassen, die außerhalb des angegebenen Bereichs liegen.

Lassen Sie uns den folgenden Vergleich der Leistung verschiedener Anfragen betrachten. Hier ist eine ineffiziente Anfrage.

Langsame Abfrage

Und hier ist die optimierte Version dieser Anfrage.

Schnelle Anfrage

Beide Anfragen geben exakt dasselbe Datenvolumen zurück. Aber die erste benötigt 12,80 Sekunden zur Ausführung, während die zweite 0,01 Sekunden in Anspruch nimmt. Fühlen Sie den Unterschied?

Mögliche Probleme

Um die effektive Nutzung der vorgeschlagenen Methode zur Ausführung von Anfragen sicherzustellen, muss die Tabelle eine oder mehrere Spalten aufweisen, die eindeutige, fortlaufend angeordnete Indizes enthalten, wie zum Beispiel eine ganzzahlige Identifikationsnummer. In bestimmten speziellen Fällen kann dies den Erfolg der Anwendung solcher Anfragen zur Steigerung der Geschwindigkeit beim Arbeiten mit der Datenbank bestimmen.

Natürlich ist es wichtig, beim Konstruieren von Anfragen die Besonderheiten der Tabellarchitektur zu berücksichtigen und die Mechanismen auszuwählen, die sich am besten bei den vorhandenen Tabellen bewähren. Wenn Sie beispielsweise mit großen Mengen verknüpfter Daten in Anfragen arbeiten müssen, könnte Ihnen das interessieren dies Artikel.

Wenn wir mit dem Problem eines fehlenden Primärschlüssels konfrontiert sind, zum Beispiel in einer Tabelle mit einer "Viele-zu-viele"-Beziehung, wird der traditionelle Ansatz, der die Anwendung von OFFSET und LIMIT, auf jeden Fall geeignet sein. Aber seine Anwendung kann zu potenziell langsamen Anfragen führen. In solchen Fällen würde ich empfehlen, einen Primärschlüssel mit automatischer Inkrementierung zu verwenden, selbst wenn er nur zur Organisation der Ausführung von Anfragen mit Paginierung benötigt wird.

Wenn Sie sich für dieses Thema interessieren — hier, hier und hier — einige nützliche Materialien.

Ergebnisse

Die wichtigste Erkenntnis, die wir gewinnen können, besteht darin, dass es unabhängig von der Größe der Datenbanken notwendig ist, die Geschwindigkeit der Anfragen zu analysieren. In unserer Zeit ist die Skalierbarkeit von Lösungen extrem wichtig, und wenn man zu Beginn der Arbeit an einem System alles richtig plant, kann man den Entwickler in der Zukunft von vielen Problemen befreien.

Wie analysieren und optimieren Sie Anfragen an Datenbanken?

Quelle: habr.com