Wusstest du, dass Blättern mit offset sehr problematisch, aber einfach zu vermeiden ist?
Offset sagt dem DBMS die ersten N Ergebnisse eine Abfrage zu überspringen. Die Datenbank muss diese Ergebnisse aber trotzdem laden und in die gewünschte Reihenfolge bringen, bevor sie die darauffolgenden Ergebnisse senden kann.
Das ist kein Problem der Implementierung, sondern ein Design-Problem von offset:
…the rows in the derived table are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…
SQL:2023, Part 2, §4.17.3 Derived tables
Der Knackpunkt ist, dass offset nur einen Parameter kennt: die Zahl der Zeilen die verworfen wrden sollen. Kein weitere Kontext. Das einzige, dass die Datenbank mit diesem Parameter machen kann, ist die Zeilen zu laden, zählen und zu verwerfen. In anderen Worten: Große offsets verursachen einen großen Aufwand für die Datenbank – unabhängig ob SQL oder NoSQL.
Es gibt aber noch andere Probleme, wenn man mit offset blättert: Was passiert wenn, zwischen dem Laden zweier Seiten ein neuer Eintrag eingefügt wird?
Wenn man offset nutzt➌, um die zuvor geladenen Einträge❶ zu überspringen, erhält man Duplikate, wenn in der Zwischenzeit neue Einträge gespeichert wurden➋. Natürlich gibt es durch offset noch andere Anormalitäten, dies ist aber die Häufigste.
Warnung
Die Idee, die Anzahl der gesehene Zeilen später zum überspringen dieser zu verwenden ist einfach falsch.0
Das ist auch kein Datenbank-spezifisches Problem – es wird vielmehr dadurch verursacht, dass beim Blättern Seitennummern verwendet werden. Nur mit dieser Information kann es keine Datenbank besser machen.
Beachte auch, dass dieses Problem in verschiedenen Formen kommen kann:
Mit dem Schlüsselwort
offsetMit der 2-Parameter Form von
limit [offset], limit(limitmit einem Parameter ist kein Problem!)Durch eine Filter auf die Untergrenze einer Zeilennummber (z. B. mittels
row_number(),rownum, und der gleichen).
Das gemeinsame Problem all dieser Methoden ist der einzelne Parameter der nur eine Zeilnzahl anggibt – kein weiterer Kontext. In diesem Artikel verwende ich offset als stellvertreter für all diese Methoden.
Das Leben ohne OFFSET
Und nun stell dir eine Welt vor, in der es diese Probleme nicht gibt. Es stellt sich sogar heraus, dass das Leben ohne offset sehr einfach ist: Dazu muss man nur eine where-Klausel verwenden, die nur die Daten selektiert, die man noch nicht gesehen hat.
Dafür nutzt man einfach aus, dass man mit sortieren Daten arbeitet – du hast doch eine order by-Klausel, oder? Sobald man eine eindeutige Sortierreihenfolge hat, kann man mit einem einfachen Filter nur das auswählen, was nach dem letzten Eintrag kommt, den wir gesehen haben:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYDas ist das Grundrezept. Es wird etwas interessanter, wenn man über mehrere Spalten sortiert, aber die Idee bleibt dieselbe. Das Rezept lässt sich natürlich auch auf NoSQL-Systeme anwenden.
Dieser Ansatz – die Seek-Methode, auch keyset pagination genannt – löst das Problem von doppelten Ergebnissen und ist obendrein noch schneller als offset. Wenn du sehen willst, was sich bei offset und keyset pagination in der Datenbank abspielt, sieh dir doch diese Slides an:
In den Slides wird auch gezeigt, dass SQL eine praktische Funktion hat um alles, das nach etwas bestimmten einsortiert wird, zu selektieren: die row-value Vergleiche. Leider werden sie kaum unterstützt. Das Problem lässt sich aber leicht umgehen – man muss die where-Klausel nur sehr sorgfältig verfassen. Die Details findest du in diesem Artikel. Wenn du Blättern ohne offset umsetzen möchtest, musst du diesen Artikel vorher unbedingt lesen.
Aber die Frameworks…
Der Hauptgrund, dennoch offset zu verwenden, ist, dass die Seek-Methode (keyset pagination) von keinem gängigen Framework unterstützt wird. Die meisten Tools unterstützten offset, aber keinen einfachen Weg auch keyset pagination umzusetzen.
Bitte beachte, dass keyset pagination den ganzen Technologie-Stack bis zum JavaScript im Browser, das mittels AJAX die nächsten Ergebnisse beim „unendlichen Scrollen“ holt: Anstatt einer Seitennummer muss man dem Backend nämlich einen Schlüssel des zuletzt geladenen Eintrages übergeben.
Nur wenige Werkzeuge können sich derzeit damit rühmen, keyset pagination angemessen zu unterstützen:
- JavaScript
- Python
- Java / JVM
- PHP
- .NET
- Ruby
- Perl
Hier brauche ich deine Hilfe. Wenn du ein Framework wartest, das etwas mit Blättern zu tun hat, dann bitte ich dich, ich dränge dich, ich flehe dich an, ebenfalls Unterstützung für keyset pagination einzubauen. Wenn du irgendwelche Fragen zu den Details hast, helfe ich gerne (Forum, Kontakt-Formular, Twitter)!
Selbst wenn du nur Nutzer eines Frameworks bist, das keyset pagination umsetzen sollte, mach die Entwickler bitte darauf aufmerksam. Du könntest es einfach als neue Funktion vorschlagen, oder, falls möglich, einen Patch dafür liefern. Noch mal: Bei den Details helfe ich gerne.
