Hallo zusammen! Ich bin ein Backend-Entwickler, der Microservices in Java + Spring schreibt. Ich arbeite in einem der internen Produktentwicklungsteams bei Tinkoff.
In unserem Team stellt sich häufig die Frage der Optimierung von Abfragen in einem DBMS. Sie möchten immer etwas schneller sein, kommen aber nicht immer mit durchdachten Indizes aus – Sie müssen nach Workarounds suchen. Während einer dieser Streifzüge durch das Internet auf der Suche nach sinnvollen Optimierungen für die Arbeit mit Datenbanken bin ich darauf gestoßen , Autor von SQL Performance Explained. Dies ist die seltene Art von Blog, in der Sie alle Artikel hintereinander lesen können.
Ich würde gerne einen kurzen Artikel von Marcus für Sie übersetzen. Es kann gewissermaßen als Manifest bezeichnet werden, das auf das alte, aber immer noch relevante Problem der Leistung der Offset-Operation gemäß dem SQL-Standard aufmerksam machen soll.
An einigen Stellen werde ich den Autor durch Erläuterungen und Kommentare ergänzen. Ich werde alle diese Orte mit „ca.“ bezeichnen. für mehr Klarheit
Eine kurze Einführung
Ich denke, viele Leute wissen, wie problematisch und langsam die Arbeit mit der Seitenauswahl über den Offset ist. Wussten Sie, dass es ganz einfach durch ein effizienteres Design ersetzt werden kann?
Das Schlüsselwort offset weist die Datenbank also an, die ersten n Datensätze in der Anfrage zu überspringen. Die Datenbank muss jedoch weiterhin diese ersten n Datensätze in der angegebenen Reihenfolge von der Festplatte lesen (Hinweis: Sortierung anwenden, falls angegeben), und erst dann ist es möglich, Datensätze ab n+1 zurückzugeben. Das Interessanteste ist, dass das Problem nicht in der konkreten Implementierung im DBMS liegt, sondern in der ursprünglichen Definition gemäß Standard:
…die Zeilen werden zunächst nach sortiert und dann durch Weglassen der in der angegebenen Anzahl von Zeilen begrenzt von Anfang an…
-SQL:2016, Teil 2, 4.15.3 Abgeleitete Tabellen (Hinweis: derzeit der am häufigsten verwendete Standard)
Der entscheidende Punkt hierbei ist, dass Offset einen einzigen Parameter benötigt – die Anzahl der zu überspringenden Datensätze, und das war’s. Nach dieser Definition kann das DBMS nur alle Datensätze abrufen und dann die unnötigen verwerfen. Offensichtlich zwingt uns diese Definition des Offsets zu zusätzlicher Arbeit. Dabei spielt es keine Rolle, ob es sich um SQL oder NoSQL handelt.
Nur ein bisschen mehr Schmerzen
Die Probleme mit dem Offset enden hier nicht, und hier erfahren Sie, warum. Was passiert in diesem Fall, wenn zwischen dem Lesen zweier Datenseiten von der Festplatte ein anderer Vorgang einen neuen Datensatz einfügt?
Wenn Offset verwendet wird, um Datensätze von vorherigen Seiten zu überspringen, werden Sie beim Hinzufügen eines neuen Datensatzes zwischen Lesevorgängen verschiedener Seiten höchstwahrscheinlich Duplikate erhalten (Hinweis: Dies ist möglich, wenn wir Seite für Seite mit dem Order-by-Konstrukt lesen in der Mitte unserer Ausgabe könnte es einen neuen Eintrag geben).
Die Abbildung verdeutlicht diese Situation deutlich. Die Basis liest die ersten 10 Datensätze, danach wird ein neuer Datensatz eingefügt, der alle gelesenen Datensätze um 1 versetzt. Dann nimmt die Basis eine neue Seite aus den nächsten 10 Datensätzen und beginnt nicht wie vorgesehen mit dem 11., sondern mit dem 10., Duplizieren dieses Datensatzes. Es gibt noch andere Anomalien im Zusammenhang mit der Verwendung dieses Ausdrucks, aber dieser kommt am häufigsten vor.
Wie wir bereits herausgefunden haben, handelt es sich hierbei nicht um Probleme eines bestimmten DBMS oder seiner Implementierungen. Das Problem besteht darin, die Paginierung gemäß dem SQL-Standard zu definieren. Wir teilen dem DBMS mit, welche Seite abgerufen oder wie viele Datensätze übersprungen werden sollen. Die Datenbank ist einfach nicht in der Lage, eine solche Anfrage zu optimieren, da hierfür zu wenig Informationen vorhanden sind.
Es sollte auch klargestellt werden, dass es sich hierbei nicht um ein Problem mit einem bestimmten Schlüsselwort handelt, sondern vielmehr um die Semantik der Abfrage. Es gibt mehrere weitere Syntaxen, die in ihrer Problematik identisch sind:
- Das Schlüsselwort offset ist wie bereits erwähnt.
- Eine Konstruktion aus zwei Schlüsselwörtern limit [offset] (obwohl limit selbst nicht so schlecht ist).
- Filtern nach Untergrenzen, basierend auf der Zeilennummerierung (z. B. row_number(), rownum usw.).
Alle diese Ausdrücke sagen Ihnen lediglich, wie viele Zeilen Sie überspringen müssen, ohne zusätzliche Informationen oder Kontext.
Später in diesem Artikel wird das Schlüsselwort offset als Zusammenfassung all dieser Optionen verwendet.
Leben ohne OFFSET
Stellen wir uns nun vor, wie unsere Welt ohne all diese Probleme aussehen würde. Es stellt sich heraus, dass das Leben ohne Offset nicht so schwierig ist: Mit einer Auswahl können Sie nur die Zeilen auswählen, die wir noch nicht gesehen haben (Hinweis: diejenigen, die nicht auf der vorherigen Seite waren), indem Sie eine Bedingung in where verwenden.
In diesem Fall gehen wir davon aus, dass Auswahlen auf einer geordneten Menge (gute alte Reihenfolge nach) ausgeführt werden. Da wir eine geordnete Menge haben, können wir einen ziemlich einfachen Filter verwenden, um nur die Daten zu erhalten, die hinter dem letzten Datensatz 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 macht es mehr Spaß, wenn man nach vielen Spalten sortiert, aber die Idee ist immer noch dieselbe. Es ist wichtig zu beachten, dass dieses Design auf viele anwendbar ist -Entscheidungen.
Dieser Ansatz wird als Suchmethode oder Keyset-Paginierung bezeichnet. Es löst das Problem des schwebenden Ergebnisses (Hinweis: die zuvor beschriebene Situation beim Schreiben zwischen Seitenlesevorgängen) und arbeitet natürlich, was wir alle lieben, schneller und stabiler als der klassische Offset. Stabilität liegt darin, dass die Bearbeitungszeit der Anfrage nicht proportional zur Anzahl der angeforderten Tabellen zunimmt (Hinweis: Wenn Sie mehr über die Arbeit verschiedener Ansätze zur Paginierung erfahren möchten, können Sie dies tun . Dort finden Sie auch vergleichende Benchmarks für verschiedene Methoden.
Eine der Folien dass die Paginierung nach Tasten natürlich nicht allmächtig ist – sie hat ihre Grenzen. Das Wichtigste ist, dass sie nicht in der Lage ist, zufällige Seiten zu lesen (Hinweis: inkonsistent). Im Zeitalter des endlosen Scrollens (Anmerkung: im Frontend) ist dies jedoch kein so großes Problem. Die Angabe einer Seitenzahl zum Anklicken ist im UI-Design ohnehin eine schlechte Entscheidung (Anmerkung: Meinung des Autors des Artikels).
Was ist mit den Werkzeugen?
Die Paginierung auf Tasten ist oft nicht geeignet, da diese Methode nicht instrumental unterstützt wird. Bei den meisten Entwicklungstools, einschließlich verschiedener Frameworks, können Sie nicht genau auswählen, wie die Paginierung durchgeführt werden soll.
Erschwerend kommt hinzu, dass die beschriebene Methode eine durchgängige Unterstützung der eingesetzten Technologien erfordert – vom DBMS bis zur Ausführung einer AJAX-Anfrage im Browser mit endlosem Scrollen. Anstatt nur die Seitenzahl anzugeben, müssen Sie jetzt einen Schlüsselsatz für alle Seiten gleichzeitig angeben.
Allerdings wächst die Zahl der Frameworks, die die Paginierung auf Schlüsseln unterstützen, allmählich. Folgendes haben wir im Moment:
- für Java;
- für Ruby;
- и für Django;
- für Python;
- — Kriterien-API für JPA-Implementierungen;
- für Perl;
- , Mapper für Node.js .
(Hinweis: Einige Links wurden entfernt, da zum Zeitpunkt der Übersetzung einige Bibliotheken seit 2017–2018 nicht mehr aktualisiert waren. Bei Interesse können Sie sich die Originalquelle ansehen.)
In diesem Moment wird Ihre Hilfe benötigt. Wenn Sie ein Framework entwickeln oder unterstützen, das die Paginierung in irgendeiner Weise nutzt, dann bitte ich Sie, ich fordere Sie auf, ich flehe Sie an, native Unterstützung für die Paginierung auf Tasten bereitzustellen. Wenn Sie Fragen haben oder Hilfe benötigen, helfe ich Ihnen gerne weiter (, , ) (Anmerkung: Aus meiner Erfahrung mit Marcus kann ich sagen, dass er wirklich begeistert davon ist, dieses Thema zu verbreiten.)
Wenn Sie vorgefertigte Lösungen verwenden, die Ihrer Meinung nach Unterstützung für die Paginierung nach Schlüsseln wert sind, erstellen Sie eine Anfrage oder bieten Sie nach Möglichkeit sogar eine vorgefertigte Lösung an. Sie können auch auf diesen Artikel verlinken.
Abschluss
Der Grund dafür, dass ein so einfacher und nützlicher Ansatz wie die Paginierung nach Schlüsseln nicht weit verbreitet ist, liegt nicht darin, dass er technisch schwierig umzusetzen ist oder großen Aufwand erfordert. Der Hauptgrund dafür ist, dass viele es gewohnt sind, Offset zu sehen und damit zu arbeiten – dieser Ansatz wird durch die Norm selbst vorgegeben.
Infolgedessen denken nur wenige Menschen darüber nach, den Ansatz zur Paginierung zu ändern, und aus diesem Grund entwickelt sich die instrumentelle Unterstützung durch Frameworks und Bibliotheken nur unzureichend. Wenn Ihnen die Idee und das Ziel der versatzfreien Paginierung am Herzen liegen, helfen Sie mit, sie zu verbreiten!
Source:
Autor: Markus Winand
Source: habr.com