Undvik att använda OFFSET och LIMIT i sidnumrerade frågor

De dagar då du inte behövde oroa dig för att optimera databasprestanda är förbi. Tiden står inte stilla. Varje ny teknikentreprenör vill skapa nästa Facebook, samtidigt som de försöker samla in all data de kan få tag på. Företag behöver dessa uppgifter för att bättre utbilda modeller som hjälper dem att tjäna pengar. Under sådana förhållanden måste programmerare skapa API:er som gör att de snabbt och tillförlitligt kan arbeta med enorma mängder information.

Om du har designat program- eller databasbackends under en längre tid, har du förmodligen skrivit kod för att köra sidnumrerade frågor. Till exempel, så här:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Så är det?

Men om det var så här du gjorde din paginering så är jag ledsen att säga att du inte gjorde det på det mest effektiva sättet.

Vill du invända mot mig? Du kan ingen spendera tid. Slak, Shopify и MIXMAX De använder redan de tekniker som jag vill prata om idag.

Nämn minst en backend-utvecklare som aldrig har använt OFFSET и LIMIT för att utföra sidnumrerade frågor. I MVP (Minimum Viable Product) och i projekt som använder små mängder data är detta tillvägagångssätt ganska tillämpligt. Det "bara funkar" så att säga.

Men om du behöver skapa tillförlitliga och effektiva system från grunden, bör du i förväg ta hand om effektiviteten i att söka efter databaserna som används i sådana system.

Idag ska vi prata om problemen med vanliga (för dåliga) implementeringar av paginerade frågemotorer, och hur man uppnår hög prestanda när man kör sådana frågor.

Vad är det för fel på OFFSET och LIMIT?

Som redan sagt, OFFSET и LIMIT De presterar bra i projekt som inte behöver arbeta med stora datamängder.

Problemet uppstår när databasen växer till en sådan storlek att den inte längre får plats i serverns minne. Men när du arbetar med den här databasen måste du använda sidnumrerade frågor.

För att detta problem ska visa sig måste det finnas en situation där DBMS tar till en ineffektiv Full Table Scan-operation för varje sidnumrerad fråga (medan insättnings- och raderingsoperationer kan förekomma och vi behöver inte föråldrade data!).

Vad är en "full table scan" (eller "sequential table scan", Sequential Scan)? Detta är en operation under vilken DBMS sekventiellt läser varje rad i tabellen, det vill säga data som finns i den, och kontrollerar att de överensstämmer med ett givet villkor. Denna typ av tabellskanning är känd för att vara den långsammaste. Faktum är att när det exekveras utförs många in-/utdataoperationer som involverar serverns diskundersystem. Situationen förvärras av latensen som är förknippad med att arbeta med data lagrad på diskar, och det faktum att överföring av data från disk till minne är en resurskrävande operation.

Till exempel har du register över 100000000 XNUMX XNUMX användare och du kör en fråga med konstruktionen OFFSET 50000000. Detta innebär att DBMS kommer att behöva ladda alla dessa poster (och vi behöver inte ens dem!), sätta dem i minnet och sedan ta, säg, 20 resultat rapporterade i LIMIT.

Låt oss säga att det kan se ut så här: "välj rader från 50000 till 50020 från 100000". Det vill säga, systemet måste först ladda 50000 XNUMX rader för att slutföra frågan. Ser du hur mycket onödigt arbete hon kommer att behöva göra?

Om du inte tror mig, ta en titt på exemplet jag skapade med hjälp av funktionerna db-fiddle.com. 

Exempel på db-fiddle.com

Där, till vänster, i fältet Schema SQL, det finns kod som infogar 100000 XNUMX rader i databasen och till höger i fältet Query SQLvisas två frågor. Den första, långsamma, ser ut så här:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

Och den andra, som är en effektiv lösning på samma problem, är så här:

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

För att uppfylla dessa önskemål klickar du bara på knappen Run längst upp på sidan. Efter att ha gjort detta jämför vi information om exekveringstiden för frågan. Det visar sig att det tar minst 30 gånger längre tid att köra en ineffektiv fråga än att köra den andra (denna tid varierar från körning till körning; systemet kan till exempel rapportera att den första frågan tog 37 ms att slutföra, men exekveringen av sekund - 1 ms).

Och om det finns mer data kommer allt att se ännu värre ut (för att vara övertygad om detta, ta en titt på min exempel med 10 miljoner rader).

Det vi just har diskuterat bör ge dig en inblick i hur databasfrågor faktiskt behandlas.

Observera att ju högre värde OFFSET — ju längre tid det tar att slutföra begäran.

Vad ska jag använda istället för kombinationen av OFFSET och LIMIT?

Istället för en kombination OFFSET и LIMIT Det är värt att använda en struktur byggd enligt följande schema:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Detta är frågekörning med markörbaserad paginering.

Istället för att lagra aktuella lokalt OFFSET и LIMIT och överför dem med varje begäran, måste du lagra den senast mottagna primärnyckeln (vanligtvis är detta ID) Och LIMIT, som ett resultat kommer frågor som liknar ovanstående att erhållas.

Varför? Poängen är att genom att uttryckligen ange identifieraren för den sista lästa raden, talar du om för din DBMS var den behöver börja söka efter nödvändig data. Dessutom kommer sökningen, tack vare användningen av nyckeln, att utföras effektivt, systemet kommer inte att behöva distraheras av linjer utanför det angivna intervallet.

Låt oss ta en titt på följande prestandajämförelse av olika frågor. Här är en ineffektiv fråga.

Långsam begäran

Och här är en optimerad version av denna begäran.

Snabb förfrågan

Båda frågorna returnerar exakt samma mängd data. Men den första tar 12,80 sekunder att slutföra och den andra tar 0,01 sekunder. Känner du skillnaden?

Möjliga problem

För att den föreslagna frågemetoden ska fungera effektivt måste tabellen ha en kolumn (eller kolumner) som innehåller unika, sekventiella index, till exempel en heltalsidentifierare. I vissa specifika fall kan detta avgöra framgången med att använda sådana frågor för att öka hastigheten på arbetet med databasen.

När du konstruerar frågor måste du naturligtvis ta hänsyn till tabellernas specifika arkitektur och välja de mekanismer som fungerar bäst på de befintliga tabellerna. Om du till exempel behöver arbeta i frågor med stora mängder relaterad data kan du tycka att det är intressant detta artikel.

Om vi ​​ställs inför problemet med att missa en primärnyckel, till exempel om vi har en tabell med en många-till-många-relation, då är det traditionella tillvägagångssättet att använda OFFSET и LIMIT, kommer garanterat att passa oss. Men dess användning kan resultera i potentiellt långsamma frågor. I sådana fall skulle jag rekommendera att använda en auto-inkrementerande primärnyckel, även om den bara behövs för att hantera sidnumrerade frågor.

Om du är intresserad av detta ämne - här, här и här - flera användbara material.

Resultat av

Den huvudsakliga slutsatsen som vi kan dra är att det alltid är nödvändigt att analysera hastigheten på exekveringen av frågor, oavsett vilken storlek databaser vi talar om. Nuförtiden är skalbarheten av lösningar extremt viktig, och om allt är korrekt designat från början av arbetet med ett visst system, kan detta i framtiden rädda utvecklaren från många problem.

Hur analyserar och optimerar du databasfrågor?

Källa: will.com