De dage er forbi, hvor du ikke behøvede at bekymre dig om at optimere databasens ydeevne. Tiden står ikke stille. Enhver ny tech-iværksætter ønsker at skabe den næste Facebook, mens de prøver at indsamle alle de data, de kan få fingrene i. Virksomheder har brug for disse data for bedre at træne modeller, der hjælper dem med at tjene penge. Under sådanne forhold skal programmører skabe API'er, der giver dem mulighed for hurtigt og pålideligt at arbejde med enorme mængder information.
Hvis du har designet applikations- eller database-backends i længere tid, har du sandsynligvis skrevet kode til at køre paginerede forespørgsler. For eksempel sådan her:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Sådan er det?
Men hvis det er sådan du har lavet din paginering, er jeg ked af at sige, at du ikke gjorde det på den mest effektive måde.
Vil du gøre indsigelse mod mig? . , и De bruger allerede de teknikker, som jeg vil tale om i dag.
Nævn mindst én backend-udvikler, der aldrig har brugt OFFSET и LIMIT at udføre paginerede forespørgsler. I MVP (Minimum Viable Product) og i projekter, hvor der bruges små mængder data, er denne tilgang ganske anvendelig. Det "virker bare", så at sige.
Men hvis du har brug for at skabe pålidelige og effektive systemer fra bunden, bør du på forhånd være opmærksom på effektiviteten af forespørgsler i de databaser, der bruges i sådanne systemer.
I dag vil vi tale om problemerne med almindeligt anvendte (alt for dårlige) implementeringer af paginerede forespørgselsmotorer, og hvordan man opnår høj ydeevne, når man udfører sådanne forespørgsler.
Hvad er der galt med OFFSET og LIMIT?
Som allerede sagt, OFFSET и LIMIT De klarer sig godt i projekter, der ikke behøver at arbejde med store mængder data.
Problemet opstår, når databasen vokser til en sådan størrelse, at den ikke længere passer i serverens hukommelse. Men når du arbejder med denne database, skal du bruge paginerede forespørgsler.
For at dette problem kan manifestere sig, skal der være en situation, hvor DBMS'et tyr til en ineffektiv fuld tabelscanningsoperation på hver pagineret forespørgsel (mens indsættelses- og sletningsoperationer kan forekomme, og vi har ikke brug for forældede data!).
Hvad er en "fuld tabelscanning" (eller "sekventiel tabelscanning", sekventiel scanning)? Dette er en operation, hvor DBMS sekventielt læser hver række i tabellen, det vil sige de data, der er indeholdt i den, og kontrollerer dem for overholdelse af en given betingelse. Denne type bordscanning er kendt for at være den langsomste. Faktum er, at når det udføres, udføres der mange input/output-operationer, der involverer serverens diskundersystem. Situationen forværres af den latens, der er forbundet med at arbejde med data, der er lagret på diske, og det faktum, at overførsel af data fra disk til hukommelse er en ressourcekrævende operation.
For eksempel har du registreringer af 100000000 brugere, og du kører en forespørgsel med konstruktionen OFFSET 50000000. Dette betyder, at DBMS'et bliver nødt til at indlæse alle disse poster (og vi har ikke engang brug for dem!), lægge dem i hukommelsen og derefter tage f.eks. 20 resultater rapporteret i LIMIT.
Lad os sige, at det kan se sådan ud: "vælg rækker fra 50000 til 50020 fra 100000". Det vil sige, at systemet først skal indlæse 50000 rækker for at fuldføre forespørgslen. Kan du se, hvor meget unødvendigt arbejde hun skal udføre?
Hvis du ikke tror mig, så tag et kig på det eksempel, jeg lavede ved hjælp af funktionerne .
Eksempel på db-fiddle.com
Der, til venstre, i marken Schema SQL, der er kode, der indsætter 100000 rækker i databasen, og til højre i feltet Query SQL, vises to forespørgsler. Den første, langsomme, ser sådan ud:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
Og den anden, som er en effektiv løsning på det samme problem, er sådan her:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
For at opfylde disse anmodninger skal du blot klikke på knappen Run øverst på siden. Efter at have gjort dette, sammenligner vi oplysninger om forespørgselsudførelsestiden. Det viser sig, at det tager mindst 30 gange længere tid at udføre en ineffektiv forespørgsel end at udføre den anden (denne tid varierer fra kørsel til kørsel; systemet kan f.eks. rapportere, at den første forespørgsel tog 37 ms at fuldføre, men udførelsen af sekund - 1 ms).
Og hvis der er flere data, så vil alt se endnu værre ud (for at være overbevist om dette, tag et kig på min med 10 millioner rækker).
Det, vi lige har diskuteret, burde give dig et indblik i, hvordan databaseforespørgsler faktisk behandles.
Bemærk venligst, at jo højere værdi OFFSET — jo længere tid vil anmodningen tage at fuldføre.
Hvad skal jeg bruge i stedet for kombinationen af OFFSET og LIMIT?
I stedet for en kombination OFFSET и LIMIT Det er værd at bruge en struktur bygget i henhold til følgende skema:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Dette er udførelse af forespørgsler med markørbaseret paginering.
I stedet for at gemme nuværende lokalt OFFSET и LIMIT og overføre dem med hver anmodning, skal du gemme den sidst modtagne primærnøgle (normalt er dette ID) Og LIMIT, som et resultat, vil der blive indhentet forespørgsler svarende til ovenstående.
Hvorfor? Pointen er, at ved eksplicit at angive identifikatoren for den sidste læste række, fortæller du dit DBMS, hvor det skal begynde at søge efter de nødvendige data. Desuden vil søgningen, takket være brugen af nøglen, blive udført effektivt; systemet behøver ikke at blive distraheret af linjer uden for det specificerede område.
Lad os tage et kig på følgende præstationssammenligning af forskellige forespørgsler. Her er en ineffektiv forespørgsel.
Langsom anmodning
Og her er en optimeret version af denne anmodning.
Hurtig anmodning
Begge forespørgsler returnerer nøjagtig den samme mængde data. Men den første tager 12,80 sekunder at gennemføre, og den anden tager 0,01 sekunder. Mærker du forskellen?
Mulige problemer
For at den foreslåede forespørgselsmetode skal fungere effektivt, skal tabellen have en kolonne (eller kolonner), der indeholder unikke, sekventielle indekser, såsom et heltals-id. I nogle specifikke tilfælde kan dette afgøre succesen med at bruge sådanne forespørgsler til at øge hastigheden af arbejdet med databasen.
Når du konstruerer forespørgsler, skal du naturligvis tage hensyn til tabellernes specifikke arkitektur og vælge de mekanismer, der vil fungere bedst på de eksisterende tabeller. For eksempel, hvis du har brug for at arbejde i forespørgsler med store mængder af relaterede data, kan du finde det interessant artikel.
Hvis vi står over for problemet med at mangle en primær nøgle, for eksempel hvis vi har en tabel med en mange-til-mange relation, så er den traditionelle tilgang med at bruge OFFSET и LIMIT, passer med garanti til os. Men brugen kan resultere i potentielt langsomme forespørgsler. I sådanne tilfælde vil jeg anbefale at bruge en auto-incrementing primær nøgle, selvom den kun er nødvendig for at håndtere paginerede forespørgsler.
Hvis du er interesseret i dette emne - , и - flere nyttige materialer.
Resultaterne af
Hovedkonklusionen, som vi kan drage, er, at uanset hvilken størrelse databaser vi taler om, er det altid nødvendigt at analysere hastigheden af forespørgselsudførelsen. I dag er skalerbarheden af løsninger ekstremt vigtig, og hvis alt er designet korrekt fra begyndelsen af arbejdet på et bestemt system, kan dette i fremtiden redde udvikleren fra mange problemer.
Hvordan analyserer og optimerer du databaseforespørgsler?
Kilde: www.habr.com