Dag Allemaal! Ik ben een backend-ontwikkelaar die microservices schrijft in Java + Spring. Ik werk in een van de interne productontwikkelingsteams bij Tinkoff.
In ons team komt vaak de vraag naar het optimaliseren van queries in een DBMS naar voren. Je wilt altijd een beetje sneller zijn, maar je kunt niet altijd rondkomen met zorgvuldig opgebouwde indexen. Je moet op zoek gaan naar een aantal oplossingen. Tijdens een van deze omzwervingen op internet, op zoek naar redelijke optimalisaties bij het werken met databases, ontdekte ik , auteur van SQL Performance uitgelegd. Dit is dat zeldzame type blog waarin je alle artikelen achter elkaar kunt lezen.
Ik wil graag een kort artikel van Marcus voor je vertalen. Het kan tot op zekere hoogte een manifest worden genoemd dat de aandacht wil vestigen op het oude, maar nog steeds relevante probleem van de uitvoering van de offsetbewerking volgens de SQL-standaard.
Op sommige plaatsen zal ik de auteur aanvullen met uitleg en commentaar. Ik zal naar al dergelijke plaatsen verwijzen als "ongeveer." voor meer duidelijkheid
Een kleine introductie
Ik denk dat veel mensen weten hoe problematisch en langzaam het werken met paginaselectie via offset is. Wist u dat het vrij eenvoudig kan worden vervangen door een efficiënter ontwerp?
Het offset-trefwoord vertelt de database dus dat de eerste n records in het verzoek moeten worden overgeslagen. De database moet echter nog steeds deze eerste n records van schijf lezen, in de gegeven volgorde (let op: pas sortering toe als dit is gespecificeerd), en alleen dan zal het mogelijk zijn om records vanaf n+1 terug te sturen. Het meest interessante is dat het probleem niet zit in de specifieke implementatie in het DBMS, maar in de oorspronkelijke definitie volgens de standaard:
…de rijen worden eerst gesorteerd op basis van de en vervolgens beperkt door het aantal rijen dat is opgegeven in de vanaf het begin…
-SQL:2016, deel 2, 4.15.3 Afgeleide tabellen (let op: momenteel de meest gebruikte standaard)
Het belangrijkste punt hier is dat voor offset één enkele parameter nodig is: het aantal records dat moet worden overgeslagen, en dat is alles. Volgens deze definitie kan het DBMS alleen alle records ophalen en vervolgens de onnodige records verwijderen. Het is duidelijk dat deze definitie van offset ons dwingt om extra werk te doen. En het maakt niet eens uit of het SQL of NoSQL is.
Nog een beetje pijn
De problemen met offset houden daar niet op, en dit is waarom. Als tussen het lezen van twee pagina's met gegevens van de schijf een andere bewerking een nieuw record invoegt, wat zal er dan in dit geval gebeuren?
Wanneer offset wordt gebruikt om records van eerdere pagina's over te slaan, in de situatie waarin u een nieuw record toevoegt tussen het lezen van verschillende pagina's, krijgt u hoogstwaarschijnlijk duplicaten (opmerking: dit is mogelijk wanneer we pagina voor pagina lezen met behulp van de volgorde per constructie, en vervolgens in het midden van onze uitvoer kan er een nieuw item zijn).
De figuur geeft deze situatie duidelijk weer. De basis leest de eerste 10 records, waarna een nieuw record wordt ingevoegd, waardoor alle gelezen records met 1 worden verschoven. Vervolgens neemt de basis een nieuwe pagina van de volgende 10 records en begint niet vanaf de 11e, zoals het hoort, maar vanaf de 10e, het dupliceren van dit record. Er zijn nog andere afwijkingen verbonden aan het gebruik van deze uitdrukking, maar dit is de meest voorkomende.
Zoals we al hebben ontdekt, zijn dit geen problemen van een specifiek DBMS of hun implementaties. Het probleem zit hem in het definiëren van paginering volgens de SQL-standaard. We vertellen het DBMS welke pagina moet worden opgehaald of hoeveel records moeten worden overgeslagen. De database is simpelweg niet in staat een dergelijk verzoek te optimaliseren, omdat hiervoor te weinig informatie beschikbaar is.
Het is ook de moeite waard om te verduidelijken dat dit geen probleem is met een specifiek zoekwoord, maar eerder met de semantiek van de zoekopdracht. Er zijn nog een aantal syntaxis die identiek zijn in hun problematische aard:
- Het offset-trefwoord is zoals eerder vermeld.
- Een constructie van twee trefwoorden limit [offset] (hoewel limit zelf niet zo slecht is).
- Filteren op ondergrenzen, gebaseerd op rijnummering (bijvoorbeeld row_number(), rownum, etc.).
Al deze uitdrukkingen vertellen u eenvoudigweg hoeveel regels u moet overslaan, zonder aanvullende informatie of context.
Verderop in dit artikel wordt het trefwoord offset gebruikt als samenvatting van al deze opties.
Leven zonder OFFSET
Laten we ons nu eens voorstellen hoe onze wereld eruit zou zien zonder al deze problemen. Het blijkt dat het leven zonder offset niet zo moeilijk is: met een selectie kun je alleen die rijen selecteren die we nog niet hebben gezien (let op: dat wil zeggen, die niet op de vorige pagina stonden), met behulp van een voorwaarde in waar.
In dit geval gaan we ervan uit dat selecties worden uitgevoerd op een geordende set (de goede oude order door). Omdat we een geordende set hebben, kunnen we een vrij eenvoudig filter gebruiken om alleen de gegevens op te halen die achter het laatste record van de vorige pagina staan:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYDat is het hele principe van deze aanpak. Natuurlijk wordt het leuker als je op veel kolommen sorteert, maar het idee is nog steeds hetzelfde. Het is belangrijk op te merken dat dit ontwerp op velen van toepassing is -beslissingen.
Deze aanpak wordt de zoekmethode of keyset-paginering genoemd. Het lost het zwevende resultaatprobleem op (let op: de situatie met schrijven tussen paginalezingen, eerder beschreven) en, natuurlijk, waar we allemaal van houden, het werkt sneller en stabieler dan de klassieke offset. De stabiliteit ligt in het feit dat de verwerkingstijd van de aanvraag niet toeneemt in verhouding tot het aantal opgevraagde tabellen (let op: als u meer wilt weten over het werk van verschillende benaderingen van paginering, kunt u . Daar kunt u ook vergelijkende benchmarks voor verschillende methoden vinden).
Eén van de glijbanen die paginering op basis van sleutels is natuurlijk niet almachtig - het heeft zijn beperkingen. Het belangrijkste is dat ze niet de mogelijkheid heeft om willekeurige pagina's te lezen (let op: inconsistent). In het tijdperk van eindeloos scrollen (let op: aan de voorkant) is dit echter niet zo'n probleem. Het opgeven van een paginanummer om op te klikken is sowieso een slechte beslissing in het UI-ontwerp (let op: mening van de auteur van het artikel).
Hoe zit het met de hulpmiddelen?
Paginering op sleutels is vaak niet geschikt vanwege het gebrek aan instrumentele ondersteuning voor deze methode. Bij de meeste ontwikkeltools, waaronder verschillende frameworks, kunt u niet precies kiezen hoe de paginering wordt uitgevoerd.
De situatie wordt verergerd door het feit dat de beschreven methode end-to-end ondersteuning vereist in de gebruikte technologieën - van het DBMS tot de uitvoering van een AJAX-verzoek in de browser met eindeloos scrollen. In plaats van alleen het paginanummer op te geven, moet u nu een set sleutels voor alle pagina's tegelijk opgeven.
Het aantal raamwerken dat paginering op toetsen ondersteunt, groeit echter geleidelijk. Dit is wat we op dit moment hebben:
- voor Java;
- voor Ruby;
- и voor Django;
- voor Python;
- — criteria-API voor PPV-implementaties;
- voor Perl;
- , mapper voor Node.js .
(Opmerking: sommige links zijn verwijderd vanwege het feit dat op het moment van vertaling sommige bibliotheken sinds 2017-2018 niet meer waren bijgewerkt. Als u geïnteresseerd bent, kunt u de originele bron bekijken.)
Het is op dit moment dat uw hulp nodig is. Als u een raamwerk ontwikkelt of ondersteunt dat enig gebruik maakt van paginering, dan vraag ik u dringend om native ondersteuning te bieden voor paginering op sleutels. Als je vragen hebt of hulp nodig hebt, help ik je graag verder (, , ) (let op: uit mijn ervaring met Marcus kan ik zeggen dat hij erg enthousiast is over het verspreiden van dit onderwerp).
Als u kant-en-klare oplossingen gebruikt waarvan u denkt dat ze ondersteuning voor paginering via sleutels waard zijn, dien dan een verzoek in of bied indien mogelijk zelfs een kant-en-klare oplossing aan. U kunt ook naar dit artikel linken.
Conclusie
De reden waarom zo'n eenvoudige en nuttige aanpak als paginering op basis van sleutels niet wijdverspreid is, is niet dat het technisch moeilijk te implementeren is of grote inspanningen vergt. De belangrijkste reden is dat velen gewend zijn aan het zien en werken met offset - deze aanpak wordt bepaald door de standaard zelf.
Als gevolg hiervan denken maar weinig mensen na over het veranderen van de aanpak van paginering, en hierdoor ontwikkelt de instrumentele ondersteuning van frameworks en bibliotheken zich slecht. Daarom, als het idee en het doel van offsetvrije paginering dichtbij u ligt, help dan mee deze te verspreiden!
Bron:
Auteur: Markus Winand
Bron: www.habr.com