Prošli su dani kada niste morali da brinete o optimizaciji performansi baze podataka. Vrijeme ne miruje. Svaki novi tehnološki poduzetnik želi stvoriti sljedeći Facebook, pokušavajući pritom prikupiti sve podatke do kojih može doći. Poduzećima su potrebni ovi podaci kako bi bolje obučili modele koji im pomažu da zarade novac. U takvim uslovima, programeri treba da kreiraju API-je koji im omogućavaju brz i pouzdan rad sa ogromnim količinama informacija.
Ako ste dizajnirali pozadinu aplikacije ili baze podataka već duže vrijeme, vjerovatno ste napisali kod za pokretanje paginiranih upita. Na primjer, ovako:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Kako je?
Ali ako ste ovako izvršili svoju paginaciju, žao mi je što moram reći da to niste uradili na najefikasniji način.
Hoćeš li mi prigovoriti? . , и Oni već koriste tehnike o kojima želim da pričam danas.
Navedite barem jednog backend programera koji nikada nije koristio OFFSET и LIMIT za izvođenje paginiranih upita. U MVP-u (Minimum Viable Product) i projektima gdje se koriste male količine podataka, ovaj pristup je prilično primjenjiv. To "samo radi", da tako kažem.
Ali ako treba da kreirate pouzdane i efikasne sisteme od nule, trebalo bi unapred da vodite računa o efikasnosti upita baza podataka koje se koriste u takvim sistemima.
Danas ćemo razgovarati o problemima sa često korištenim (šteta) implementacijama paginiranih mehanizama upita i kako postići visoke performanse prilikom izvršavanja takvih upita.
Šta nije u redu sa OFFSET-om i LIMIT-om?
Kao što je već rečeno, OFFSET и LIMIT Oni dobro rade u projektima koji ne moraju raditi s velikim količinama podataka.
Problem nastaje kada baza podataka naraste do takve veličine da više ne stane u memoriju servera. 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 neefikasnoj operaciji skeniranja pune tablice na svakom paginiranom upitu (dok se mogu dogoditi operacije umetanja i brisanja, a ne trebaju nam zastarjeli podaci!).
Šta je „skeniranje cele tabele“ (ili „sekvencijalno skeniranje tabele“, sekvencijalno skeniranje)? Ovo je operacija tokom koje DBMS sekvencijalno čita svaki red tabele, odnosno podatke sadržane u njoj, i provjerava njihovu usklađenost sa datim uvjetom. Poznato je da je ova vrsta skeniranja tablice najsporija. Činjenica je da kada se izvrši, izvode se mnoge ulazno/izlazne operacije koje uključuju diskovni podsistem servera. Situaciju pogoršava kašnjenje povezano s radom s podacima pohranjenim na diskovima, te činjenica da je prijenos podataka s diska na memoriju operacija koja zahtijeva velike resurse.
Na primjer, imate zapise od 100000000 korisnika i pokrećete upit s konstrukcijom OFFSET 50000000. To znači da će DBMS morati učitati sve te zapise (a nama ni ne trebaju!), staviti ih u memoriju i nakon toga uzeti, recimo, 20 rezultata prijavljenih u LIMIT.
Recimo da bi to moglo izgledati ovako: "odaberite redove od 50000 do 50020 od 100000". To jest, sistem će prvo morati da učita 50000 redova da dovrši upit. Vidite li koliko će ona morati da obavi nepotrebni posao?
Ako mi ne vjerujete, pogledajte primjer koji sam napravio koristeći funkcije .
Primjer na db-fiddle.com
Tamo, lijevo, u polju Schema SQL, postoji kod koji ubacuje 100000 redova u bazu podataka, a desno, u polje Query SQL, prikazana su dva upita. Prvi, spori, izgleda ovako:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
A drugo, koje je efikasno rješenje za isti problem, je ovako:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Da biste ispunili ove zahtjeve, samo kliknite na dugme Run na vrhu stranice. Nakon što smo to uradili, upoređujemo informacije o vremenu izvršenja upita. Ispostavilo se da izvršavanje neefikasnog upita traje najmanje 30 puta duže od izvršavanja drugog (ovo vrijeme varira od pokretanja do pokretanja; na primjer, sistem može prijaviti da je prvom upitu bilo potrebno 37 ms da se završi, ali izvršenje sekunda - 1 ms).
A ako bude više podataka, onda će sve izgledati još gore (da biste se uvjerili u ovo, pogledajte moj sa 10 miliona redova).
Ono o čemu smo upravo govorili trebalo bi vam dati uvid u to kako se upiti baze podataka zapravo obrađuju.
Imajte na umu da je vrijednost veća OFFSET — duže će biti potrebno da se zahtjev završi.
Šta da koristim umjesto kombinacije OFFSET i LIMIT?
Umjesto kombinacije OFFSET и LIMIT Vrijedi koristiti strukturu izgrađenu prema sljedećoj shemi:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Ovo je izvršenje upita sa paginacijom baziranom na kursoru.
Umjesto pohranjivanja trenutnih lokalno OFFSET и LIMIT i prenosite ih sa svakim zahtjevom, morate pohraniti posljednji primljeni primarni ključ (obično je to ID) i LIMIT, kao rezultat će se dobiti upiti slični gore navedenim.
Zašto? Poenta je u tome da eksplicitnim navođenjem identifikatora posljednjeg pročitanog reda kažete svom DBMS-u gdje treba da počne tražiti potrebne podatke. Štaviše, zahvaljujući upotrebi ključa, sistem neće morati da bude ometan linijama izvan navedenog opsega.
Pogledajmo sljedeće poređenje performansi različitih upita. Evo neefikasnog upita.
Spor zahtjev
A evo optimizirane verzije ovog zahtjeva.
Brzi zahtjev
Oba upita vraćaju potpuno istu količinu podataka. Ali za prvu je potrebno 12,80 sekundi, a za drugu 0,01 sekundu. Osjećate li razliku?
Mogući problemi
Da bi predloženi metod upita radio efikasno, tabela mora imati kolonu (ili kolone) koja sadrži jedinstvene, sekvencijalne indekse, kao što je celobrojni identifikator. U nekim specifičnim slučajevima, ovo može odrediti uspjeh korištenja takvih upita za povećanje brzine rada s bazom podataka.
Naravno, kada konstruišete upite, morate uzeti u obzir specifičnu arhitekturu tabela i odabrati one mehanizme koji će najbolje raditi na postojećim tabelama. Na primjer, ako trebate raditi na upitima s velikim količinama povezanih podataka, možda će vam biti zanimljivo članak.
Ako smo suočeni s problemom nedostatka primarnog ključa, na primjer, ako imamo tabelu sa relacijom mnogo-prema-mnogo, onda tradicionalni pristup korištenja OFFSET и LIMIT, garantovano nam odgovara. Ali njegova upotreba može dovesti do potencijalno sporih upita. U takvim slučajevima, preporučio bih korištenje primarnog ključa koji se automatski povećava, čak i ako je potreban samo za rukovanje paginiranim upitima.
Ako ste zainteresovani za ovu temu - , и - nekoliko korisnih materijala.
Ishodi
Glavni zaključak koji možemo izvući je da, bez obzira o kojoj veličini baza podataka govorimo, uvijek je potrebno analizirati brzinu izvršavanja upita. Danas je skalabilnost rješenja izuzetno važna, a ako je sve ispravno dizajnirano od samog početka rada na određenom sistemu, to u budućnosti može spasiti programera od mnogih problema.
Kako analizirate i optimizirate upite u bazi podataka?
izvor: www.habr.com