von Markus Winand.

Wir brauchen Tool-Unterstützung zum Blättern mit Keysets


Wusstest du, dass Blättern mit offset sehr problematisch, aber einfach zu vermeiden ist?

offset sagt der Datenbank 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 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:2016, Part 2, §4.15.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?

1FETCH FIRST 10 ROWS ONLYErgebnisrow 1row 2row 3row 4row 5row 6row 7row 8row 9row 10row 11row 12row 13row 142INSERTnew row3Verworfene ZeilenErgebnisOFFSET 10FETCH NEXT 10 ROWS ONLYnew row

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.

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 offset

  • Mit der 2-Parameter Form von limit [offset], limit (limit mit 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 ONLY

Das 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.

Bitte weitersagen

Das Problem mit keyset pagination ist nicht technisch. Das Problem ist, dass es kaum bekannt ist und von den Tools nicht unterstützt wird. Wenn du die Idee magst, ohne offset auszukommen, dann erzähl es bitte weiter. Tweet es, mag es, mail es, du kannst diesen Blog-Eintrag sogar re-bloggen (CC-BY-NC-ND).

Falls du selbst bloggst, könntest du auch einen NoOffset-Banner auf deinen Blog stellen, damit deine Leser auch Bescheid wissen. Ich habe eine ganze Gallerie von NoOffset-Bannern vorbereitet – nimm einfach, was passt.

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Twitter oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Mit Markus Winand verbinden

Markus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf Twitter
„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO | CC-BY-NC-ND 3.0 Lizenz