Izbjegavajte korištenje OFFSET i LIMIT u paginiranim upitima

Prošli su dani kada se niste morali brinuti o optimizaciji performansi baze podataka. Vrijeme ne stoji. Svaki novi tehnološki poduzetnik želi stvoriti sljedeći Facebook, dok pokušava prikupiti sve podatke do kojih može doći. Tvrtke trebaju ove podatke kako bi bolje obučavale modele koji im pomažu zaraditi novac. U takvim uvjetima programeri moraju stvoriti API-je koji im omogućuju brz i pouzdan rad s ogromnim količinama informacija.

Ako već duže vrijeme dizajnirate pozadinu aplikacije ili baze podataka, vjerojatno ste napisali kod za pokretanje paginiranih upita. Na primjer, ovako:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Način na koji je?

Ali ako ste ovako napravili paginaciju, žao mi je što moram reći da to niste učinili na najučinkovitiji način.

Hoćeš li mi prigovoriti? Možeš ne potrošiti vrijeme. Zatišje, Shopify и mixmax Oni već koriste tehnike o kojima danas želim govoriti.

Imenujte barem jednog pozadinskog programera koji nikada nije koristio OFFSET и LIMIT za izvođenje paginiranih upita. U MVP (Minimum Viable Product) iu projektima gdje se koriste male količine podataka ovaj je pristup sasvim primjenjiv. To "jednostavno radi", da tako kažem.

Ali ako trebate stvoriti pouzdane i učinkovite sustave od nule, trebali biste unaprijed voditi računa o učinkovitosti upita baza podataka koje se koriste u takvim sustavima.

Danas ćemo govoriti o problemima s često korištenim (šteta) implementacijama paginiranih motora za upite i kako postići visoku izvedbu prilikom izvršavanja takvih upita.

Što nije u redu s OFFSET i LIMIT?

Kao što je već rečeno, OFFSET и LIMIT Dobro se ponašaju u projektima koji ne moraju raditi s velikim količinama podataka.

Problem nastaje kada baza podataka naraste do te veličine da više ne stane u memoriju poslužitelja. Međutim, kada radite s ovom bazom podataka, morate koristiti paginirane upite.

Da bi se ovaj problem manifestirao, mora postojati situacija u kojoj DBMS pribjegava neučinkovitoj operaciji skeniranja pune tablice na svakom upitu s paginacijom (iako se mogu pojaviti operacije umetanja i brisanja, a ne trebaju nam zastarjeli podaci!).

Što je "potpuno skeniranje tablice" (ili "sekvencijalno skeniranje tablice", Sequential Scan)? Ovo je operacija tijekom koje DBMS sekvencijalno čita svaki redak tablice, odnosno podatke sadržane u njoj, i provjerava njihovu usklađenost sa zadanim uvjetom. Poznato je da je ova vrsta skeniranja tablice najsporija. Činjenica je da se prilikom njegovog izvršavanja izvode mnoge ulazno/izlazne operacije koje uključuju diskovni podsustav poslužitelja. Situaciju pogoršava latencija povezana s radom s podacima pohranjenim na diskovima i činjenica da je prijenos podataka s diska u memoriju radnja koja zahtijeva velike resurse.

Na primjer, imate zapise o 100000000 korisnika i pokrećete upit s konstrukcijom OFFSET 50000000. To znači da će DBMS morati učitati sve te zapise (a oni nam čak i ne trebaju!), staviti ih u memoriju i nakon toga uzeti, recimo, 20 rezultata prijavljenih u LIMIT.

Recimo da bi to moglo izgledati ovako: "odaberite retke od 50000 do 50020 od 100000". To jest, sustav će prvo trebati učitati 50000 redaka kako bi dovršio upit. Vidite li koliko će nepotrebnog posla morati obaviti?

Ako mi ne vjerujete, pogledajte primjer koji sam napravio pomoću značajki db-fiddle.com. 

Primjer na db-fiddle.com

Tamo, lijevo, u polju Schema SQL, postoji kod koji ubacuje 100000 redaka u bazu podataka, a desno, u polju Query SQL, prikazana su dva upita. Prvi, spori, izgleda ovako:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

A drugi, koji je učinkovito rješenje za isti problem, je ovakav:

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

Kako biste ispunili ove zahtjeve, samo kliknite na gumb Run na vrhu stranice. Nakon što to učinimo, uspoređujemo informacije o vremenu izvršenja upita. Ispostavilo se da izvršavanje neučinkovitog upita traje najmanje 30 puta dulje nego izvršavanje drugog (ovo vrijeme varira od pokretanja do izvršavanja; na primjer, sustav može prijaviti da je za dovršetak prvog upita trebalo 37 ms, ali izvršenje sekunda - 1 ms).

A ako bude više podataka, onda će sve izgledati još gore (da biste se u to uvjerili, pogledajte moj primjer s 10 milijuna redaka).

Ono o čemu smo upravo raspravljali trebalo bi vam dati neki uvid u to kako se zapravo obrađuju upiti baze podataka.

Imajte na umu da što je veća vrijednost OFFSET — dulje će trebati da se ispuni zahtjev.

Što trebam koristiti umjesto kombinacije OFFSET i LIMIT?

Umjesto kombinacije OFFSET и LIMIT Vrijedno je koristiti strukturu izgrađenu prema sljedećoj shemi:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Ovo je izvođenje upita s paginacijom na temelju kursora.

Umjesto lokalnog pohranjivanja trenutnih OFFSET и LIMIT i prenositi ih sa svakim zahtjevom, trebate pohraniti posljednji primljeni primarni ključ (obično je to ID) I LIMIT, kao rezultat će se dobiti upiti slični gore navedenima.

Zašto? Poanta je da eksplicitnim navođenjem identifikatora posljednjeg pročitanog retka kažete svom DBMS-u gdje treba započeti traženje potrebnih podataka. Štoviše, pretraga će se, zahvaljujući korištenju ključa, provoditi učinkovito; sustav neće morati ometati linije izvan navedenog raspona.

Pogledajmo sljedeću usporedbu performansi različitih upita. Evo neučinkovitog upita.

Spor zahtjev

A ovdje je optimizirana verzija ovog zahtjeva.

Brzi zahtjev

Oba upita vraćaju potpuno istu količinu podataka. Ali za prvi je potrebno 12,80 sekundi, a za drugi 0,01 sekundu. Osjećate li razliku?

Mogući problemi

Da bi predložena metoda upita radila učinkovito, tablica mora imati stupac (ili stupce) koji sadrže jedinstvene, sekvencijalne indekse, kao što je identifikator cijelog broja. U nekim specifičnim slučajevima to može odrediti uspjeh korištenja takvih upita za povećanje brzine rada s bazom podataka.

Naravno, prilikom konstruiranja upita morate uzeti u obzir specifičnu arhitekturu tablica i odabrati one mehanizme koji će najbolje raditi na postojećim tablicama. Na primjer, ako trebate raditi u upitima s velikim količinama povezanih podataka, to bi vam moglo biti zanimljivo ovo članak.

Ako se suočimo s problemom nedostatka primarnog ključa, na primjer, ako imamo tablicu s odnosom više-prema-više, tada tradicionalni pristup korištenja OFFSET и LIMIT, garantirano će nam odgovarati. Ali njegova upotreba može rezultirati potencijalno sporim upitima. U takvim slučajevima, preporučio bih korištenje primarnog ključa s automatskim povećanjem, čak i ako je potreban samo za obradu paginiranih upita.

Ako vas zanima ova tema - ovdje, ovdje и ovdje - nekoliko korisnih materijala.

Rezultati

Glavni zaključak koji možemo izvući je da je, bez obzira o kojoj veličini baza podataka je riječ, uvijek potrebno analizirati brzinu izvršavanja upita. U današnje vrijeme skalabilnost rješenja je izuzetno važna, a ako je sve ispravno projektirano od samog početka rada na određenom sustavu, to u budućnosti može spasiti developera od mnogih problema.

Kako analizirate i optimizirate upite baze podataka?

Izvor: www.habr.com