Unngå å bruke OFFSET og LIMIT i paginerte søk

Borte er dagene da du ikke trengte å bekymre deg for å optimalisere databaseytelsen. Tiden står ikke stille. Hver ny teknologigründer ønsker å lage den neste Facebook, mens de prøver å samle all data de kan få tak i. Bedrifter trenger disse dataene for å bedre trene modeller som hjelper dem å tjene penger. Under slike forhold må programmerere lage APIer som lar dem raskt og pålitelig arbeide med enorme mengder informasjon.

Hvis du har utformet applikasjoner eller databaser over lengre tid, har du sannsynligvis skrevet kode for å kjøre paginerte spørringer. For eksempel slik:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Sånn er det?

Men hvis dette er hvordan du gjorde pagineringen din, beklager jeg å si at du ikke gjorde det på den mest effektive måten.

Vil du protestere mot meg? Du kan no bruke tid. Slack, Shopify и MIXMAX De bruker allerede teknikkene som jeg vil snakke om i dag.

Nevn minst én backend-utvikler som aldri har brukt OFFSET и LIMIT for å utføre paginerte søk. I MVP (Minimum Viable Product) og i prosjekter der små mengder data brukes, er denne tilnærmingen ganske anvendelig. Det "bare fungerer", for å si det sånn.

Men hvis du trenger å lage pålitelige og effektive systemer fra bunnen av, bør du på forhånd passe på effektiviteten av å spørre databasene som brukes i slike systemer.

I dag skal vi snakke om problemene med ofte brukte (for dårlige) implementeringer av paginerte søkemotorer, og hvordan man oppnår høy ytelse når man utfører slike spørringer.

Hva er galt med OFFSET og LIMIT?

Som allerede sagt, OFFSET и LIMIT De presterer godt i prosjekter som ikke trenger å jobbe med store datamengder.

Problemet oppstår når databasen vokser til en slik størrelse at den ikke lenger får plass i serverens minne. Når du arbeider med denne databasen, må du imidlertid bruke paginerte spørringer.

For at dette problemet skal manifestere seg, må det være en situasjon der DBMS tyr til en ineffektiv Full Table Scan-operasjon på hver paginerte spørring (mens innsettings- og slettingsoperasjoner kan forekomme, og vi trenger ikke utdaterte data!).

Hva er en "full tabellskanning" (eller "sekvensiell tabellskanning", sekvensiell skanning)? Dette er en operasjon der DBMS sekvensielt leser hver rad i tabellen, det vil si dataene i den, og sjekker dem for samsvar med en gitt betingelse. Denne typen tabellskanning er kjent for å være den tregeste. Faktum er at når det utføres, utføres mange input/output-operasjoner som involverer serverens diskundersystem. Situasjonen forverres av ventetiden knyttet til arbeid med data lagret på disker, og det faktum at overføring av data fra disk til minne er en ressurskrevende operasjon.

Du har for eksempel poster på 100000000 XNUMX XNUMX brukere og du kjører en spørring med konstruksjonen OFFSET 50000000. Dette betyr at DBMS må laste inn alle disse postene (og vi trenger dem ikke engang!), legge dem i minnet, og deretter ta for eksempel 20 resultater rapportert i LIMIT.

La oss si at det kan se slik ut: "velg rader fra 50000 til 50020 fra 100000". Det vil si at systemet først må laste 50000 XNUMX rader for å fullføre spørringen. Ser du hvor mye unødvendig arbeid hun må gjøre?

Hvis du ikke tror meg, ta en titt på eksemplet jeg laget med funksjonene db-fiddle.com. 

Eksempel på db-fiddle.com

Der, til venstre, i feltet Schema SQL, det er kode som setter inn 100000 XNUMX rader i databasen, og til høyre i feltet Query SQL, vises to søk. Den første, sakte, ser slik ut:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Og den andre, som er en effektiv løsning på det samme problemet, er slik:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

For å oppfylle disse forespørslene, klikker du bare på knappen Run øverst på siden. Etter å ha gjort dette, sammenligner vi informasjon om utførelsestiden for spørringen. Det viser seg at å utføre en ineffektiv spørring tar minst 30 ganger lengre tid enn å utføre den andre (denne tiden varierer fra kjøring til kjøring; systemet kan for eksempel rapportere at det tok 37 ms å fullføre den første spørringen, men utførelsen av sekund - 1 ms).

Og hvis det er mer data, vil alt se enda verre ut (for å være overbevist om dette, ta en titt på min eksempel med 10 millioner rader).

Det vi nettopp har diskutert bør gi deg litt innsikt i hvordan databasespørringer faktisk behandles.

Vær oppmerksom på at jo høyere verdi OFFSET – jo lengre tid vil forespørselen ta å fullføre.

Hva bør jeg bruke i stedet for kombinasjonen av OFFSET og LIMIT?

I stedet for en kombinasjon OFFSET и LIMIT Det er verdt å bruke en struktur bygget i henhold til følgende skjema:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Dette er kjøring av spørring med markørbasert paginering.

I stedet for å lagre gjeldende lokalt OFFSET и LIMIT og overføre dem med hver forespørsel, må du lagre den sist mottatte primærnøkkelen (vanligvis er dette ID) Og LIMIT, som et resultat vil spørsmål som ligner på de ovennevnte bli innhentet.

Hvorfor? Poenget er at ved å eksplisitt spesifisere identifikatoren for den siste raden som ble lest, forteller du DBMS hvor den må begynne å søke etter de nødvendige dataene. Dessuten vil søket, takket være bruken av nøkkelen, utføres effektivt; systemet trenger ikke å bli distrahert av linjer utenfor det angitte området.

La oss ta en titt på følgende ytelsessammenligning av ulike søk. Her er et ineffektivt søk.

Sakte forespørsel

Og her er en optimalisert versjon av denne forespørselen.

Rask forespørsel

Begge spørringene returnerer nøyaktig samme mengde data. Men den første tar 12,80 sekunder å fullføre, og den andre tar 0,01 sekunder. Føler du forskjellen?

Mulige problemer

For at den foreslåtte spørringsmetoden skal fungere effektivt, må tabellen ha en kolonne (eller kolonner) som inneholder unike, sekvensielle indekser, for eksempel en heltallsidentifikator. I noen spesifikke tilfeller kan dette avgjøre suksessen til å bruke slike spørringer for å øke hastigheten på arbeidet med databasen.

Naturligvis, når du konstruerer spørringer, må du ta hensyn til den spesifikke arkitekturen til tabellene og velge de mekanismene som vil fungere best på de eksisterende tabellene. Hvis du for eksempel trenger å jobbe med spørringer med store mengder relaterte data, kan det hende du synes det er interessant dette artikkel.

Hvis vi står overfor problemet med å savne en primærnøkkel, for eksempel hvis vi har en tabell med et mange-til-mange forhold, så er den tradisjonelle tilnærmingen med å bruke OFFSET и LIMIT, passer garantert oss. Men bruken kan føre til potensielt trege søk. I slike tilfeller vil jeg anbefale å bruke en auto-inkrementerende primærnøkkel, selv om den kun er nødvendig for å håndtere paginerte søk.

Hvis du er interessert i dette emnet - her, her и her - flere nyttige materialer.

Resultater av

Hovedkonklusjonen vi kan trekke er at uansett hvilken størrelse databaser vi snakker om, er det alltid nødvendig å analysere hastigheten på utførelse av spørringer. I dag er skalerbarheten til løsninger ekstremt viktig, og hvis alt er designet riktig fra begynnelsen av arbeidet med et bestemt system, kan dette i fremtiden redde utvikleren fra mange problemer.

Hvordan analyserer og optimaliserer du databasespørringer?

Kilde: www.habr.com