Hej alle! Jeg er en backend-udvikler og skriver mikrotjenester i Java + Spring. Jeg arbejder i et af de interne produktudviklingsteams hos Tinkoff.
I vores team opstår ofte spørgsmålet om optimering af forespørgsler i et DBMS. Du vil altid gerne være lidt hurtigere, men du kan ikke altid klare dig med gennemtænkte konstruerede indekser - du skal lede efter nogle løsninger. Under en af disse vandringer rundt på nettet på jagt efter fornuftige optimeringer, når man arbejder med databaser, fandt jeg , forfatter til SQL Performance Explained. Dette er den sjældne type blog, hvor du kan læse alle artiklerne i træk.
Jeg vil gerne oversætte en kort artikel af Marcus for dig. Det kan til en vis grad kaldes et manifest, der søger at gøre opmærksom på det gamle, men stadig relevante problem med udførelsen af offset-operationen i henhold til SQL-standarden.
Nogle steder vil jeg supplere forfatteren med forklaringer og kommentarer. Jeg vil referere til alle sådanne steder som "ca." for mere klarhed
Lille introduktion
Jeg tror, mange mennesker ved, hvor problematisk og langsomt arbejde med sidevalg via offset er. Vidste du, at det ganske nemt kan udskiftes med et mere effektivt design?
Så offset nøgleordet fortæller databasen at springe de første n poster over i anmodningen. Databasen mangler dog stadig at læse disse første n poster fra disk, i den givne rækkefølge (bemærk: anvend sortering, hvis det er angivet), og først derefter vil det være muligt at returnere poster fra n+1 og fremefter. Det mest interessante er, at problemet ikke ligger i den specifikke implementering i DBMS, men i den oprindelige definition i henhold til standarden:
…rækkerne sorteres først efter og derefter begrænset ved at droppe antallet af rækker angivet i fra begyndelsen…
-SQL:2016, del 2, 4.15.3 Afledte tabeller (bemærk: i øjeblikket den mest brugte standard)
Nøglepunktet her er, at offset tager en enkelt parameter - antallet af poster, der skal springes over, og det er det. Efter denne definition kan DBMS kun hente alle posterne og derefter kassere de unødvendige. Denne definition af offset tvinger os naturligvis til at udføre ekstra arbejde. Og det er lige meget, om det er SQL eller NoSQL.
Bare lidt mere smerte
Problemerne med offset slutter ikke der, og her er hvorfor. Hvis, mellem læsning af to sider med data fra disken, en anden operation indsætter en ny post, hvad vil der så ske i dette tilfælde?
Når offset bruges til at springe poster fra tidligere sider over, i situationen med at tilføje en ny post mellem læsninger af forskellige sider, vil du højst sandsynligt få dubletter (bemærk: dette er muligt, når vi læser side for side ved at bruge rækkefølgen efter konstruktion, så midt i vores output kan den få en ny post).
Figuren viser tydeligt denne situation. Basen læser de første 10 poster, hvorefter der indsættes en ny post, som forskyder alle læste poster med 1. Så tager basen en ny side fra de næste 10 poster og starter ikke fra den 11., som den skal, men fra 10., duplikere denne post. Der er andre anomalier forbundet med brugen af dette udtryk, men dette er det mest almindelige.
Som vi allerede har fundet ud af, er disse ikke problemer med et specifikt DBMS eller deres implementeringer. Problemet er at definere paginering i henhold til SQL-standarden. Vi fortæller DBMS, hvilken side der skal hentes, eller hvor mange poster der skal springes over. Databasen er simpelthen ikke i stand til at optimere en sådan anmodning, da der er for lidt information til dette.
Det er også værd at præcisere, at dette ikke er et problem med et specifikt søgeord, men snarere med forespørgslens semantik. Der er flere flere syntakser, der er identiske i deres problematiske natur:
- Offset nøgleordet er som nævnt tidligere.
- En konstruktion af to søgeord limit [offset] (selvom limit i sig selv ikke er så slemt).
- Filtrering efter nedre grænser baseret på rækkenummerering (f.eks. rækkenummer(), rækkenummer osv.).
Alle disse udtryk fortæller dig blot, hvor mange linjer du skal springe over, ingen yderligere information eller kontekst.
Senere i denne artikel bruges offset nøgleordet som en oversigt over alle disse muligheder.
Liv uden OFFSET
Lad os nu forestille os, hvordan vores verden ville være uden alle disse problemer. Det viser sig, at livet uden offset ikke er så svært: med et valg kan du kun vælge de rækker, som vi endnu ikke har set (bemærk: det vil sige dem, der ikke var på den forrige side), ved at bruge en betingelse i hvor.
I dette tilfælde tager vi udgangspunkt i, at udvælgelser udføres på et bestilt sæt (god gammel ordre af). Da vi har et bestilt sæt, kan vi bruge et ret simpelt filter til kun at få de data, der ligger bag den sidste post på den forrige side:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYDet er hele princippet i denne tilgang. Selvfølgelig bliver tingene sjovere, når man sorterer efter mange kolonner, men ideen er stadig den samme. Det er vigtigt at bemærke, at dette design er anvendeligt for mange -beslutninger.
Denne tilgang kaldes søgemetode eller nøglesætpaginering. Det løser problemet med flydende resultat (bemærk: situationen med at skrive mellem sidelæsninger, beskrevet tidligere) og selvfølgelig, hvad vi alle elsker, det fungerer hurtigere og mere stabilt end den klassiske offset. Stabiliteten ligger i, at anmodningsbehandlingstiden ikke stiger i forhold til antallet af den ønskede tabel (bemærk: hvis du vil lære mere om arbejdet med forskellige tilgange til paginering, kan du . Du kan også finde sammenlignende benchmarks for forskellige metoder der).
En af diasene at paginering med nøgler selvfølgelig ikke er almægtig - den har sine begrænsninger. Det mest betydningsfulde er, at hun ikke har evnen til at læse tilfældige sider (bemærk: inkonsekvent). Men i en tid med endeløs scrolling (bemærk: på forsiden), er dette ikke et sådant problem. Angivelse af et sidetal for at klikke er alligevel en dårlig beslutning i UI-design (bemærk: mening fra forfatteren af artiklen).
Hvad med værktøjerne?
Paginering på taster er ofte ikke egnet på grund af manglen på instrumentel støtte til denne metode. De fleste udviklingsværktøjer, herunder forskellige frameworks, tillader dig ikke at vælge præcis, hvordan paginering skal udføres.
Situationen forværres af, at den beskrevne metode kræver end-to-end support i de anvendte teknologier - fra DBMS til eksekvering af en AJAX-anmodning i browseren med endeløs scrolling. I stedet for kun at angive sidetallet, skal du nu angive et sæt nøgler for alle sider på én gang.
Antallet af rammer, der understøtter paginering på nøgler, vokser dog gradvist. Her er hvad vi har i øjeblikket:
- til Java;
- for Ruby;
- и for Django;
- for Python;
- — kriterier API for JPA-implementeringer;
- for Perl;
- , kortlægger for Node.js .
(Bemærk: nogle links blev fjernet på grund af det faktum, at nogle biblioteker på tidspunktet for oversættelsen ikke var blevet opdateret siden 2017-2018. Hvis du er interesseret, kan du se på den originale kilde.)
Det er i dette øjeblik, at din hjælp er nødvendig. Hvis du udvikler eller understøtter en ramme, der gør brug af paginering, så beder jeg, jeg opfordrer dig til at give indbygget support til paginering på nøgler. Hvis du har spørgsmål eller brug for hjælp, hjælper jeg gerne (, , ) (bemærk: ud fra min erfaring med Marcus kan jeg sige, at han er virkelig begejstret for at udbrede dette emne).
Bruger du færdige løsninger, som du mener er værdige til at have understøttelse af paginering ved hjælp af nøgler, så lav en forespørgsel eller byd sågar en færdig løsning, hvis det er muligt. Du kan også linke til denne artikel.
Konklusion
Grunden til, at en så enkel og brugbar tilgang som paginering efter nøgler ikke er udbredt, er ikke, at den er svær at implementere teknisk eller kræver nogen stor indsats. Hovedårsagen er, at mange er vant til at se og arbejde med offset – denne tilgang er dikteret af selve standarden.
Som følge heraf tænker få mennesker på at ændre tilgangen til paginering, og på grund af dette udvikler instrumentel støtte fra rammer og biblioteker sig dårligt. Derfor, hvis ideen og målet med offset-fri paginering er tæt på dig, så hjælp med at sprede det!
Kilde:
Forfatter: Markus Winand
Kilde: www.habr.com