Hei alle sammen! Jeg er en backend-utvikler som skriver mikrotjenester i Java + Spring. Jeg jobber i et av de interne produktutviklingsteamene hos Tinkoff.
I vårt team dukker ofte spørsmålet om å optimalisere spørringer i et DBMS opp. Du vil alltid være litt raskere, men du kan ikke alltid klare deg med gjennomtenkt konstruerte indekser – du må se etter noen løsninger. Under en av disse vandringene rundt på nettet på jakt etter fornuftige optimaliseringer når jeg jobber med databaser, fant jeg , forfatter av SQL Performance Explained. Dette er den sjeldne typen blogg der du kan lese alle artiklene på rad.
Jeg vil gjerne oversette en kort artikkel av Marcus for deg. Det kan til en viss grad kalles et manifest som søker å rette oppmerksomheten mot det gamle, men fortsatt relevante problemet med ytelsen til offset-operasjonen i henhold til SQL-standarden.
Noen steder vil jeg supplere forfatteren med forklaringer og kommentarer. Jeg vil referere til alle slike steder som "ca." for mer klarhet
Liten introduksjon
Jeg tror mange vet hvor problematisk og sakte arbeid med sidevalg via offset er. Visste du at den ganske enkelt kan erstattes med en mer effektiv design?
Så, offset-nøkkelordet forteller databasen å hoppe over de første n postene i forespørselen. Databasen trenger imidlertid fortsatt å lese disse første n postene fra disk, i gitt rekkefølge (merk: bruk sortering hvis det er spesifisert), og først da vil det være mulig å returnere poster fra n+1 og utover. Det mest interessante er at problemet ikke ligger i den spesifikke implementeringen i DBMS, men i den opprinnelige definisjonen i henhold til standarden:
… radene sorteres først i henhold til og deretter begrenset ved å slippe antall rader spesifisert i fra begynnelsen...
-SQL:2016, del 2, 4.15.3 Avledede tabeller (merk: for tiden den mest brukte standarden)
Nøkkelpunktet her er at offset tar en enkelt parameter - antall poster som skal hoppes over, og det er det. Etter denne definisjonen kan DBMS bare hente alle postene og deretter forkaste de unødvendige. Å definere offset på denne måten forårsaker åpenbart unødvendig arbeid. Og det spiller ingen rolle om det er SQL eller NoSQL.
Bare litt mer smerte
Problemene med offset slutter ikke der, og her er hvorfor. Hvis, mellom å lese to sider med data fra disken, en annen operasjon setter inn en ny post, hva vil skje i dette tilfellet?
Når offset brukes til å hoppe over poster fra tidligere sider, i situasjonen med å legge til en ny post mellom lesninger av forskjellige sider, vil du mest sannsynlig få duplikater (merk: dette er mulig når vi leser side for side ved å bruke rekkefølgen etter konstruksjon, da midt i produksjonen vår kan den få en ny oppføring).
Figuren viser tydelig denne situasjonen. Basen leser de første 10 postene, hvoretter en ny post settes inn, som forskyver alle leste poster med 1. Deretter tar basen en ny side fra de neste 10 postene og starter ikke fra den 11., som den skal, men fra 10., dupliserer denne posten. Det er andre anomalier knyttet til bruken av dette uttrykket, men dette er det vanligste.
Som vi allerede har funnet ut, er dette ikke problemer med en spesifikk DBMS eller deres implementeringer. Problemet er å definere paginering i henhold til SQL-standarden. Vi forteller DBMS hvilken side som skal hentes eller hvor mange poster som skal hoppes over. Databasen er rett og slett ikke i stand til å optimalisere en slik forespørsel, siden det er for lite informasjon til dette.
Det er også verdt å presisere at dette ikke er et problem med et spesifikt søkeord, men snarere med semantikken til spørringen. Det er flere syntakser som er identiske i sin problematiske natur:
- Offset-nøkkelordet er som nevnt tidligere.
- En konstruksjon av to søkeord limit [offset] (selv om limit i seg selv ikke er så ille).
- Filtrering etter nedre grenser, basert på radnummerering (for eksempel radnummer(), radnummer osv.).
Alle disse uttrykkene forteller deg ganske enkelt hvor mange linjer du skal hoppe over, ingen tilleggsinformasjon eller kontekst.
Senere i denne artikkelen brukes offset-nøkkelordet som en oppsummering av alle disse alternativene.
Livet uten OFFSET
La oss nå forestille oss hvordan vår verden ville vært uten alle disse problemene. Det viser seg at livet uten forskyvning ikke er så vanskelig: med et utvalg kan du bare velge de radene vi ennå ikke har sett (merk: det vil si de som ikke var på forrige side), ved å bruke en betingelse i hvor.
I dette tilfellet tar vi utgangspunkt i at valg utføres på et bestilt sett (god gammel rekkefølge av). Siden vi har et bestilt sett, kan vi bruke et ganske enkelt filter for å få kun dataene som ligger bak den siste posten på forrige side:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYDet er hele prinsippet for denne tilnærmingen. Selvfølgelig blir ting morsommere når man sorterer etter mange kolonner, men ideen er fortsatt den samme. Det er viktig å merke seg at denne designen kan brukes for mange -vedtak.
Denne tilnærmingen kalles søkemetode eller nøkkelsettpaginering. Det løser problemet med flytende resultat (merk: situasjonen med skriving mellom sidelesninger beskrevet tidligere) og, selvfølgelig, det vi alle elsker, fungerer raskere og mer stabilt enn den klassiske offseten. Stabiliteten ligger i det faktum at forespørselsbehandlingstiden ikke øker proporsjonalt med antallet på den forespurte tabellen (merk: hvis du vil lære mer om arbeidet med ulike tilnærminger til paginering, kan du . Du kan også finne komparative benchmarks for ulike metoder der).
En av lysbildene at paginering med nøkler, selvfølgelig, ikke er allmektig – den har sine begrensninger. Det viktigste er at hun ikke har evnen til å lese tilfeldige sider (merk: inkonsekvent). Men i en tid med endeløs rulling (merk: på forsiden), er ikke dette et slikt problem. Å spesifisere et sidetall for å klikke er uansett en dårlig avgjørelse i UI-design (merk: mening fra forfatteren av artikkelen).
Hva med verktøyene?
Paginering på nøkler er ofte ikke egnet på grunn av mangel på instrumentell støtte for denne metoden. De fleste utviklingsverktøy, inkludert ulike rammeverk, lar deg ikke velge nøyaktig hvordan paginering skal utføres.
Situasjonen forverres av det faktum at den beskrevne metoden krever ende-til-ende-støtte i teknologiene som brukes - fra DBMS til utførelse av en AJAX-forespørsel i nettleseren med endeløs rulling. I stedet for å spesifisere bare sidetallet, må du nå spesifisere et sett med nøkler for alle sidene samtidig.
Imidlertid øker antallet rammeverk som støtter paginering på nøkler gradvis. Her er hva vi har for øyeblikket:
- for Java;
- for Ruby;
- и for Django;
- for Python;
- — kriterier API for JPA-implementeringer;
- for Perl;
- , kartlegger for Node.js .
(Merk: noen lenker ble fjernet på grunn av at noen biblioteker på tidspunktet for oversettelsen ikke var oppdatert siden 2017-2018. Hvis du er interessert, kan du se på originalkilden.)
Det er i dette øyeblikket du trenger hjelp. Hvis du utvikler eller støtter et rammeverk som gjør bruk av paginering, spør jeg, jeg ber deg om å gi innfødt støtte for paginering på nøkler. Hvis du har spørsmål eller trenger hjelp, hjelper jeg gjerne (, , ) (merk: fra min erfaring med Marcus kan jeg si at han er veldig entusiastisk over å spre dette emnet).
Hvis du bruker ferdige løsninger som du mener er verdig å ha støtte for paginering med nøkler, opprett en forespørsel eller til og med tilby en ferdig løsning, hvis mulig. Du kan også lenke til denne artikkelen.
Konklusjon
Grunnen til at en så enkel og nyttig tilnærming som paginering etter nøkler ikke er utbredt, er ikke at den er vanskelig å implementere teknisk eller krever stor innsats. Hovedårsaken er at mange er vant til å se og jobbe med offset – denne tilnærmingen er diktert av selve standarden.
Som et resultat er det få som tenker på å endre tilnærmingen til paginering, og på grunn av dette utvikler instrumentell støtte fra rammeverk og biblioteker seg dårlig. Derfor, hvis ideen og målet med offset-fri paginering er nær deg, hjelp til å spre det!
Kilde:
Forfatter: Markus Winand
Kilde: www.habr.com