Hallo zusammen! Ich bin Backend-Entwickler und schreibe Mikrodienste in Java + Spring. Ich arbeite in einem der Entwicklungsteams für interne Produkte bei Tinkoff.
In unserem Team stellt sich oft die Frage nach der Optimierung von Datenbankabfragen. Man möchte immer etwas schneller sein, aber nicht immer kann man mit gut durchdachten Indizes auskommen — manchmal muss man alternative Wege suchen. Während einer dieser Suchaktionen im Netz nach sinnvollen Optimierungen für die Arbeit mit Datenbanken habe ich gefunden , dem Autor des Buches SQL Performance Explained. Das ist eine der seltenen Arten von Blogs, in denen man alle Artikel hintereinander lesen kann.
Ich möchte für euch einen kleinen Artikel von Markus übersetzen. Man könnte ihn gewissermaßen als Manifest bezeichnen, das darauf abzielt, die Aufmerksamkeit auf ein altes, aber nach wie vor aktuelles Problem der Performance von Offset-Operationen nach dem SQL-Standard zu lenken.
An einigen Stellen werde ich die Erklärungen und Anmerkungen des Autors ergänzen. Alle diese Stellen werde ich zur besseren Klarheit als „Anm.“ kennzeichnen.
Eine kleine Einführung
Ich denke, viele wissen, wie problematisch und langsam die Arbeit mit paginierten SELECTs durch Offset sein kann. Aber wusstet ihr, dass man es ziemlich einfach durch eine leistungsfähigere Konstruktion ersetzen kann?
Das Schlüsselwort Offset weist der Datenbank an, die ersten n Datensätze in der Abfrage zu überspringen. Die Datenbank muss jedoch diese ersten n Datensätze trotzdem von der Festplatte lesen, und zwar in der vorgegebenen Reihenfolge (Anm.: Sortierung anwenden, wenn angegeben), und erst danach kann sie die Datensätze ab n+1 und weiter zurückgeben. Am interessantesten ist, dass das Problem nicht in der spezifischen Implementierung in der Datenbank liegt, sondern in der ursprünglichen Definition nach dem Standard:
…the rows are first sorted according to the and then limited by dropping the number of rows specified in the from the beginning…
-SQL:2016, Teil 2, 4.15.3 Abgeleitete Tabellen (Anm.: Derzeit der am häufigsten verwendete Standard)
Der entscheidende Punkt hier ist, dass Offset einen einzigen Parameter annimmt — die Anzahl der Datensätze, die übersprungen werden sollen, und das war's. Nach dieser Definition kann die Datenbank nur alle Datensätze abrufen und dann die unnötigen verwwerfen. Offensichtlich zwingt eine solche Definition von Offset dazu, unnötige Arbeit zu leisten. Dabei ist es egal, ob es sich um SQL oder NoSQL handelt.
Noch ein bisschen Schmerz
Die Probleme mit dem Offset enden hier nicht, und das ist der Grund. Wenn zwischen dem Lesen von zwei Seiten Daten von der Festplatte eine andere Operation einen neuen Eintrag einfügt, was passiert in diesem Fall?
Wenn Offset verwendet wird, um Einträge von vorherigen Seiten zu überspringen, besteht in einer Situation, in der ein neuer Eintrag zwischen den Leseoperationen verschiedener Seiten hinzugefügt wird, die höchste Wahrscheinlichkeit, dass Sie Duplikate erhalten (Anm.: dies kann geschehen, wenn wir seitenweise mit der Konstruktion ‚ORDER BY‘ lesen, dann kann ein neuer Eintrag mitten in unsere Ausgabe gelangen).
Die Abbildung veranschaulicht eine solche Situation. Die Datenbank liest die ersten 10 Einträge, danach wird ein neuer Eintrag eingefügt, der alle bereits gelesenen Einträge um 1 verschiebt. Die Datenbank nimmt dann die neue Seite mit den nächsten 10 Einträgen und beginnt nicht mit dem 11., wie sie sollte, sondern mit dem 10., wodurch dieser Eintrag dupliziert wird. Es gibt auch andere Anomalien, die mit dieser Ausdrucksweise verbunden sind, aber diese ist die häufigste.
Wie wir bereits festgestellt haben, sind dies keine Probleme einer bestimmten DBMS oder deren Implementierungen. Das Problem liegt in der Definition der Paginierung nach dem SQL-Standard. Wir sagen der DBMS, welche Seite sie abrufen oder wie viele Einträge sie überspringen soll. Die Datenbank ist einfach nicht in der Lage, eine solche Anfrage zu optimieren, da dafür viel zu wenig Informationen vorliegen.
Es sei auch darauf hingewiesen, dass dies kein Problem eines bestimmten Schlüsselworts ist, sondern eher der Semantik der Anfrage. Es gibt noch einige andere identische Syntaxe mit ähnlichen Problemen:
- Das Schlüsselwort Offset, wie bereits erwähnt.
- Die Konstruktion aus zwei Schlüsselwörtern Limit [Offset] (obwohl Limit für sich genommen nicht allzu schlecht ist).
- Filtern nach Untergrenzen, die auf Zeilennummerierungen basieren (z. B. row_number(), rownum usw.).
All diese Ausdrücke sagen einfach, wie viele Zeilen übersprungen werden sollen, ohne zusätzliche Informationen oder Kontext.
Im Folgenden in diesem Artikel wird das Schlüsselwort Offset als Verallgemeinerung aller dieser Optionen verwendet.
Das Leben ohne OFFSET
Und jetzt stellen wir uns vor, wie unsere Welt ohne all diese Probleme wäre. Es stellt sich heraus, dass das Leben ohne Offset nicht so kompliziert ist: Mit einem SELECT können wir nur die Zeilen auswählen, die wir noch nicht gesehen haben (Anm.: das sind die, die nicht auf der vorherigen Seite waren), mit einer Bedingung in der WHERE-Klausel.
In diesem Fall gehen wir von der Tatsache aus, dass die Selects auf einer sortierten Menge (der gute alte Order By) durchgeführt werden. Da wir eine sortierte Menge haben, können wir einen ziemlich einfachen Filter verwenden, um nur die Daten abzurufen, die nach dem letzten Eintrag der vorherigen Seite liegen:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYDas ist das ganze Prinzip dieses Ansatzes. Natürlich wird es bei der Sortierung nach mehreren Spalten interessanter, aber die Idee bleibt die gleiche. Es ist wichtig zu beachten, dass diese Konstruktion in vielen -Lösungen anwendbar ist.
Dieser Ansatz wird als Seek-Methode oder Keyset-Pagination bezeichnet. Sie löst das Problem der schwankenden Ergebnisse (Anm.: die Situation mit Einträgen zwischen dem Lesen von Seiten, die zuvor beschrieben wurde) und, wie wir alle es lieben, funktioniert schneller und stabiler als die klassische Offset-Methode. Die Stabilität besteht darin, dass die Verarbeitungszeit der Anfrage nicht proportional zur Nummer der abgerufenen Seite ansteigt (Anm.: wenn Sie mehr über die Funktionsweise verschiedener Ansätze zur Pagination erfahren möchten, können Sie . Dort finden Sie auch vergleichende Benchmarks verschiedener Methoden.
Eine der Folien Und wie sieht es mit den Werkzeugen aus?
Die Pagination nach Schlüsseln eignet sich oft nicht aufgrund der fehlenden unterstützenden Werkzeuge für diese Methode. Die meisten Entwicklungstools, einschließlich verschiedener Frameworks, bieten keine Wahl, wie die Pagination durchgeführt wird.
Die Situation wird dadurch verschärft, dass die beschriebene Methode durchgängige Unterstützung in den verwendeten Technologien erfordert – von der Datenbank bis hin zur Ausführung von AJAX-Anfragen im Browser beim unendlichen Scrollen. Statt nur die Seitenzahl anzugeben, müssen nun die Schlüssel für alle Seiten gleichzeitig angegeben werden.
Die Situation wird verschärft durch die Tatsache, dass die beschriebene Methode durchgängige Unterstützung in den verwendeten Technologien erfordert – von der Datenbankverwaltung bis zur Ausführung von AJAX-Anfragen im Browser beim unendlichen Scrollen. Anstatt nur die Seitenzahl anzugeben, muss nun eine Reihe von Schlüsseln für alle Seiten gleichzeitig angegeben werden.
Die Anzahl der Frameworks, die die Paginierung über Schlüssel unterstützen, wächst allmählich. Hier ist, was es derzeit gibt:
- für Java;
- für Ruby;
- und für Django;
- für Python;
- — Criteria API für JPA-Implementierungen;
- für Perl;
- , мапер для Node.js .
(Hinweis: Einige Links wurden entfernt, da bestimmte Bibliotheken zum Zeitpunkt der Übersetzung seit 2017–2018 nicht aktualisiert wurden. Bei Interesse können Sie die ursprüngliche Quelle besuchen.)
Gerade an diesem Punkt benötigen wir Ihre Hilfe. Wenn Sie ein Framework entwickeln oder unterstützen, das irgendwie Paginierung verwendet, bitte ich Sie, ich fordere Sie auf, ich flehe Sie an, native Unterstützung für die Paginierung über Schlüssel zu implementieren. Wenn Sie Fragen haben oder Hilfe brauchen, helfe ich Ihnen gerne (, , ) (Hinweis: Nach meiner Erfahrung mit Markus kann ich sagen, dass er wirklich enthusiastisch ist, wenn es darum geht, dieses Thema zu verbreiten).
Wenn Sie bereits Lösungen verwenden, die Ihrer Meinung nach Unterstützung für die Paginierung über Schlüssel verdienen, erstellen Sie bitte einen Request oder schlagen Sie sogar eine fertige Lösung vor, wenn möglich. Sie können auch diesen Artikel im Link angeben.
Fazit
Der Grund, warum ein so einfacher und nützlicher Ansatz wie die Paginierung über Schlüssel wenig verbreitet ist, liegt nicht darin, dass es technisch schwierig umzusetzen ist oder große Anstrengungen erfordert. Der Hauptgrund ist, dass viele es gewohnt sind, mit Offset zu arbeiten – dieser Ansatz wird durch den Standard selbst diktiert.
Infolgedessen denken nur wenige über einen Wechsel des Ansatzes zur Paginierung nach, und deshalb entwickelt sich die instrumentelle Unterstützung seitens der Frameworks und Bibliotheken nur langsam. Wenn Sie also die Idee und das Ziel der offsetlosen Paginierung unterstützen, helfen Sie, sie zu verbreiten!
Quelle:
Autor: Markus Winand
Quelle: habr.com
