Jedan od tipičnih scenarija u svim aplikacijama s kojima smo upoznati je traženje podataka prema određenim kriterijima i njihovo prikazivanje u lako čitljivom obliku. Mogu postojati i dodatne opcije za sortiranje, grupiranje i stranice. Zadatak je, u teoriji, trivijalan, ali pri njegovom rješavanju mnogi programeri čine niz pogrešaka, što kasnije uzrokuje štetu produktivnosti. Pokušajmo razmotriti različite opcije za rješavanje ovog problema i formulirati preporuke za odabir najučinkovitije implementacije.
Opcija straničenja #1
Najjednostavnija opcija koja pada na pamet je prikaz rezultata pretraživanja stranicu po stranicu u svom najklasičnijem obliku.
Recimo da vaša aplikacija koristi relacijsku bazu podataka. U ovom slučaju, za prikaz informacija u ovom obrascu morat ćete pokrenuti dva SQL upita:
- Dohvati retke za trenutnu stranicu.
- Izračunajte ukupan broj redaka koji odgovaraju kriterijima pretraživanja - to je potrebno za prikaz stranica.
Pogledajmo prvi upit koristeći testnu MS SQL bazu podataka kao primjer za server 2016. U tu svrhu koristit ćemo tablicu Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Gornji upit vratit će prvih 50 narudžbi s popisa, poredanih prema silaznom datumu dodavanja, drugim riječima, 50 najnovijih narudžbi.
Radi brzo na testnoj bazi, ali pogledajmo plan izvršenja i I/O statistiku:
Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Možete dobiti I/O statistiku za svaki upit izvođenjem naredbe SET STATISTICS IO ON u vremenu izvođenja upita.
Kao što možete vidjeti iz plana izvršenja, opcija koja zahtijeva najviše resursa je sortiranje svih redaka izvorne tablice prema datumu dodavanja. A problem je u tome što što se više redaka pojavi u tablici, sortiranje će biti "teže". U praksi takve situacije treba izbjegavati, pa dodajmo indeks na datum dodavanja i vidimo je li se potrošnja resursa promijenila:
Table 'SalesOrderHeader'. Scan count 1, logical reads 165, physical reads 0, read-ahead reads 5, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Očito je postalo puno bolje. Ali jesu li svi problemi riješeni? Promijenimo upit za traženje narudžbi gdje ukupna cijena robe prelazi 100 USD:
SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Table 'SalesOrderHeader'. Scan count 1, logical reads 1081, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Imamo smiješnu situaciju: plan upita nije puno lošiji od prethodnog, ali je stvarni broj logičkih čitanja gotovo dvostruko veći nego kod skeniranja pune tablice. Izlaz postoji - ako od već postojećeg indeksa napravimo kompozitni indeks i kao drugo polje dodamo ukupnu cijenu robe, opet ćemo dobiti 165 logičnih čitanja:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Ovaj niz primjera može se nastaviti još dugo, ali dvije glavne misli koje želim izraziti ovdje su:
- Dodavanje bilo kojeg novog kriterija ili redoslijeda sortiranja upitu za pretraživanje može imati značajan utjecaj na brzinu upita za pretraživanje.
- Ali ako trebamo oduzeti samo dio podataka, a ne sve rezultate koji odgovaraju traženim pojmovima, postoji mnogo načina za optimizaciju takvog upita.
Sada prijeđimo na drugi upit spomenut na samom početku – onaj koji broji zapise koji zadovoljavaju kriterij pretraživanja. Uzmimo isti primjer - traženje narudžbi koje su veće od 100 USD:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
S obzirom na gore navedeni kompozitni indeks, dobivamo:
Table 'SalesOrderHeader'. Scan count 1, logical reads 698, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Činjenica da upit prolazi kroz cijeli indeks nije iznenađujuća, budući da polje SubTotal nije na prvoj poziciji, pa ga upit ne može koristiti. Problem se rješava dodavanjem još jednog indeksa na polje SubTotal, i kao rezultat daje samo 48 logičkih čitanja.
Možete dati još nekoliko primjera zahtjeva za brojanje količina, ali suština ostaje ista: primanje podatka i brojanje ukupnog iznosa su dva bitno različita zahtjeva, a svaki zahtijeva svoje mjere za optimizaciju. Općenito, nećete moći pronaći kombinaciju indeksa koja jednako dobro funkcionira za oba upita.
Sukladno tome, jedan od važnih zahtjeva koji bi trebalo razjasniti pri razvoju ovakvog rješenja za pretraživanje je je li poduzeću doista važno vidjeti ukupan broj pronađenih predmeta. Često se događa da br. A navigacija prema određenim brojevima stranica, po mom mišljenju, rješenje je s vrlo uskim opsegom, jer većina scenarija straničenja izgleda kao "idi na sljedeću stranicu".
Opcija straničenja #2
Pretpostavimo da korisnicima nije stalo do znanja ukupnog broja pronađenih objekata. Pokušajmo pojednostaviti stranicu pretraživanja:
Zapravo, jedina stvar koja se promijenila je da ne postoji način navigacije do određenih brojeva stranica, a sada ova tablica ne mora znati koliko ih može biti da bi se prikazala. Ali postavlja se pitanje - kako tablica zna ima li podataka za sljedeću stranicu (kako bi se ispravno prikazala poveznica "Dalje")?
Odgovor je vrlo jednostavan: iz baze podataka možete pročitati još jedan zapis nego što je potrebno za prikaz, a prisutnost tog "dodatnog" zapisa pokazat će postoji li sljedeći dio. Na ovaj način trebate pokrenuti samo jedan zahtjev da biste dobili jednu stranicu podataka, što značajno poboljšava performanse i olakšava podršku takve funkcionalnosti. U mojoj praksi postojao je slučaj kada je odbijanje prebrojavanja ukupnog broja zapisa ubrzalo isporuku rezultata za 4-5 puta.
Postoji nekoliko opcija korisničkog sučelja za ovaj pristup: naredbe "natrag" i "naprijed", kao u gornjem primjeru, gumb "učitaj više", koji jednostavno dodaje novi dio prikazanim rezultatima, "beskonačno pomicanje", koji radi po principu "učitaj više", ali signal za dobivanje sljedeće porcije je da korisnik skrola sve prikazane rezultate do kraja. Bez obzira na vizualno rješenje, princip uzorkovanja podataka ostaje isti.
Nijanse implementacije straničenja
Svi gore navedeni primjeri upita koriste pristup "pomak + brojanje", kada sam upit navodi kojim redoslijedom su retci rezultata i koliko redaka treba vratiti. Prvo, pogledajmo kako najbolje organizirati prosljeđivanje parametara u ovom slučaju. U praksi sam naišao na nekoliko metoda:
- Serijski broj tražene stranice (pageIndex), veličina stranice (pageSize).
- Serijski broj prvog zapisa koji se vraća (startIndex), najveći broj zapisa u rezultatu (count).
- Redni broj prvog zapisa koji se vraća (startIndex), redni broj posljednjeg zapisa koji se vraća (endIndex).
Na prvi pogled može se činiti da je to toliko elementarno da nema razlike. Ali to nije tako - najprikladnija i univerzalna opcija je druga (startIndex, count). Nekoliko je razloga za to:
- Za gore navedeni pristup lekturi +1 unosa, prva opcija s pageIndexom i pageSizeom izuzetno je nezgodna. Na primjer, želimo prikazati 50 postova po stranici. Prema gornjem algoritmu, morate pročitati jedan zapis više nego što je potrebno. Ako ovaj "+1" nije implementiran na poslužitelju, ispada da za prvu stranicu moramo zahtijevati zapise od 1 do 51, za drugu - od 51 do 101, itd. Ako navedete veličinu stranice 51 i povećate pageIndex, tada će se druga stranica vratiti s 52 na 102, itd. U skladu s tim, u prvoj opciji, jedini način da se ispravno implementira gumb za odlazak na sljedeću stranicu je da poslužitelj lektorira "ekstra" redak, što će biti vrlo implicitna nijansa.
- Treća opcija uopće nema smisla, budući da ćete za pokretanje upita u većini baza podataka i dalje morati proslijediti brojač, a ne indeks posljednjeg zapisa. Oduzimanje startIndexa od endIndexa može biti jednostavna aritmetička operacija, ali je ovdje suvišna.
Sada bismo trebali opisati nedostatke implementacije straničenja kroz "pomak + količina":
- Dohvaćanje svake sljedeće stranice bit će skuplje i sporije od prethodne, jer će baza ipak morati proći kroz sve zapise “ispočetka” prema kriterijima pretraživanja i sortiranja, a zatim se zaustaviti na željenom fragmentu.
- Ne mogu svi DBMS-ovi podržati ovaj pristup.
Postoje alternative, ali su i nesavršene. Prvi od ovih pristupa naziva se "straničenje skupa ključeva" ili "metoda traženja" i sljedeći je: nakon što primite dio, možete se sjetiti vrijednosti polja u posljednjem zapisu na stranici, a zatim ih koristiti za dobivanje sljedeći dio. Na primjer, pokrenuli smo sljedeći upit:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
I u zadnjem zapisu dobili smo vrijednost datuma naloga ‘2014-06-29’. Zatim da biste dobili sljedeću stranicu možete pokušati učiniti ovo:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Problem je u tome što je OrderDate nejedinstveno polje i gore navedeni uvjet vjerojatno će propustiti mnogo potrebnih redaka. Da biste ovom upitu dodali jednoznačnost, trebate dodati jedinstveno polje uvjetu (pretpostavite da je 75074 zadnja vrijednost primarnog ključa iz prvog dijela):
SELECT * FROM Sales.SalesOrderHeader
WHERE (OrderDate = '2014-06-29' AND SalesOrderID < 75074)
OR (OrderDate < '2014-06-29')
ORDER BY OrderDate DESC, SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ova će opcija raditi ispravno, ali će je općenito biti teško optimizirati jer uvjet sadrži OR operator. Ako se vrijednost primarnog ključa povećava kako se povećava OrderDate, tada se uvjet može pojednostaviti ostavljanjem samo filtra prema SalesOrderID-u. Ali ako ne postoji stroga korelacija između vrijednosti primarnog ključa i polja prema kojem je rezultat sortiran, ovaj OR se ne može izbjeći u većini DBMS-ova. Poznata mi je iznimka PostgreSQL, koji u potpunosti podržava usporedbu tuplea, a gornji uvjet može se napisati kao "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". S obzirom na kompozitni ključ s ova dva polja, upit poput ovog trebao bi biti prilično jednostavan.
Drugi alternativni pristup može se pronaći, na primjer, u ili — kada zahtjev, osim podataka, vraća i poseban identifikator pomoću kojeg možete dobiti sljedeći dio podataka. Ako ovaj identifikator ima neograničen životni vijek (kao u Comsos DB), onda je ovo sjajan način za implementaciju straničenja sa sekvencijalnim prijelazom između stranica (gore navedena opcija #2). Njegovi mogući nedostaci: nije podržan u svim DBMS-ovima; rezultirajući identifikator sljedećeg dijela može imati ograničen životni vijek, što općenito nije prikladno za implementaciju korisničke interakcije (kao što je ElasticSearch scroll API).
Složeno filtriranje
Idemo dodatno zakomplicirati zadatak. Pretpostavimo da postoji zahtjev za implementacijom takozvanog fasetnog pretraživanja, koje je vrlo poznato svima iz internetskih trgovina. Gore navedeni primjeri koji se temelje na tablici narudžbi nisu baš ilustrativni u ovom slučaju, pa prijeđimo na tablicu proizvoda iz baze podataka AdventureWorks:
Koja je ideja iza fasetnog pretraživanja? Činjenica je da je za svaki element filtera prikazan broj zapisa koji zadovoljavaju ovaj kriterij uzimajući u obzir filtre odabrane u svim ostalim kategorijama.
Na primjer, ako u ovom primjeru odaberemo kategoriju Bicikli i crnu boju, tablica će prikazivati samo crne bicikle, ali:
- Za svaki kriterij u grupi Kategorije crnom bojom bit će prikazan broj proizvoda iz te kategorije.
- Za svaki kriterij grupe “Boje” bit će prikazan broj bicikala te boje.
Evo primjera rezultata za takve uvjete:
Ako označite i kategoriju “Odjeća”, u tablici će biti prikazana i crna odjeća koja je na zalihi. Broj crnih proizvoda u odjeljku “Boja” također će se ponovno izračunati prema novim uvjetima, samo se u odjeljku “Kategorije” ništa neće promijeniti... Nadam se da su ovi primjeri dovoljni za razumijevanje uobičajenog algoritma fasetirane pretrage.
Sada zamislimo kako se to može implementirati na relacijskoj osnovi. Svaka grupa kriterija, kao što su Kategorija i Boja, zahtijevat će zaseban upit:
SELECT pc.ProductCategoryID, pc.Name, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc ON ps.ProductCategoryID = pc.ProductCategoryID
WHERE p.Color = 'Black'
GROUP BY pc.ProductCategoryID, pc.Name
ORDER BY COUNT(1) DESC
SELECT Color, COUNT(1) FROM Production.Product p
INNER JOIN Production.ProductSubcategory ps ON p.ProductSubcategoryID = ps.ProductSubcategoryID
WHERE ps.ProductCategoryID = 1 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC
Što nije u redu s ovim rješenjem? Vrlo je jednostavno - ne mjeri se dobro. Svaki odjeljak filtra zahtijeva poseban upit za izračun količina, a ti upiti nisu najlakši. U internetskim trgovinama neke kategorije mogu imati nekoliko desetaka odjeljaka filtera, što može biti ozbiljan problem s performansama.
Obično mi se nakon ovih izjava ponude neka rješenja, naime:
- Kombinirajte sve količine u jedan upit. Tehnički je to moguće korištenjem ključne riječi UNION, ali to neće puno pomoći performansama - baza podataka će i dalje morati izvršiti svaki od fragmenata ispočetka.
- Količine keša. To mi se sugerira gotovo svaki put kad opišem problem. Upozorenje je da je to općenito nemoguće. Recimo da imamo 10 "faseta", od kojih svaka ima 5 vrijednosti. Ovo je vrlo "skromna" situacija u usporedbi s onim što se može vidjeti u istim internetskim trgovinama. Odabir jednog aspektnog elementa utječe na količine u ostalih 9, drugim riječima, za svaku kombinaciju kriterija količine mogu biti različite. U našem primjeru postoji ukupno 50 kriterija koje korisnik može odabrati, dakle mogućih kombinacija će biti 250. Nema dovoljno memorije ni vremena za popunjavanje takvog niza podataka. Ovdje možete prigovoriti i reći da nisu sve kombinacije stvarne i da korisnik rijetko odabire više od 5-10 kriterija. Da, moguće je raditi odgođeno učitavanje i predmemorirati količinu samo onoga što je ikada odabrano, ali što je više odabira, to će takva predmemorija biti manje učinkovita i problemi s vremenom odgovora bit će vidljiviji (posebno ako skup podataka se redovito mijenja) .
Srećom, takav problem već dugo ima prilično učinkovita rješenja koja predvidljivo rade na velikim količinama podataka. Za bilo koju od ovih opcija ima smisla podijeliti ponovni izračun faseta i primanje stranice s rezultatima u dva paralelna poziva poslužitelju i organizirati korisničko sučelje na takav način da učitavanje podataka po fasetama "ne ometa" prikaz Rezultati pretraživanja.
- Pozovite potpuno ponovno izračunavanje "faseta" što je rjeđe moguće. Na primjer, nemojte ponovno izračunavati sve svaki put kada se promijene kriteriji pretraživanja, već umjesto toga pronađite ukupan broj rezultata koji odgovaraju trenutnim uvjetima i zatražite od korisnika da ih prikaže - "1425 zapisa pronađeno, prikazati?" Korisnik može nastaviti mijenjati pojmove za pretraživanje ili kliknuti gumb "prikaži". Tek u drugom slučaju bit će izvršeni svi zahtjevi za dobivanje rezultata i preračunavanje količina na svim “aspekatima”. U ovom slučaju, kao što lako vidite, morat ćete se pozabaviti zahtjevom za dobivanje ukupnog broja rezultata i njegovu optimizaciju. Ova se metoda može pronaći u mnogim malim internetskim trgovinama. Očito, ovo nije lijek za ovaj problem, ali u jednostavnim slučajevima može biti dobar kompromis.
- Koristite tražilice za pronalaženje rezultata i brojanje aspekata, kao što su Solr, ElasticSearch, Sphinx i drugi. Svi su oni dizajnirani za izgradnju "faseta" i to rade prilično učinkovito zahvaljujući obrnutom indeksu. Kako rade tražilice, zašto su u takvim slučajevima učinkovitije od baza podataka opće namjene, koje prakse i zamke postoje - to je tema za poseban članak. Ovdje želim skrenuti pozornost na činjenicu da tražilica ne može biti zamjena za glavnu pohranu podataka, već se koristi kao dodatak: sve promjene u glavnoj bazi podataka koje su relevantne za pretraživanje sinkroniziraju se u indeks pretraživanja; Tražilica obično komunicira samo s tražilicom i ne pristupa glavnoj bazi podataka. Jedna od najvažnijih točaka ovdje je kako pouzdano organizirati ovu sinkronizaciju. Sve ovisi o zahtjevima "vremena reakcije". Ako vrijeme između promjene u glavnoj bazi podataka i njezine "manifestacije" u pretrazi nije kritično, možete stvoriti uslugu koja traži nedavno promijenjene zapise svakih nekoliko minuta i indeksira ih. Ako želite najkraće moguće vrijeme odgovora, možete implementirati nešto poput za slanje ažuriranja usluzi pretraživanja.
Zaključci
- Implementacija straničenja na strani poslužitelja je značajna komplikacija i ima smisla samo za brzo rastuće ili jednostavno velike skupove podataka. Ne postoji apsolutno točan recept kako ocijeniti "velike" ili "brzorastuće", ali ja bih slijedio ovaj pristup:
- Ako primanje kompletne zbirke podataka, uzimajući u obzir vrijeme poslužitelja i mrežni prijenos, normalno odgovara zahtjevima performansi, nema smisla implementirati straničenje na strani poslužitelja.
- Može doći do situacije u kojoj se u bliskoj budućnosti ne očekuju problemi s performansama, budući da ima malo podataka, ali prikupljanje podataka stalno raste. Ako neki skup podataka u budućnosti možda više ne zadovoljava prethodnu točku, bolje je odmah započeti s straničenjem.
- Ako ne postoji striktan zahtjev od strane tvrtke za prikaz ukupnog broja rezultata ili za prikaz brojeva stranica, a vaš sustav nema tražilicu, bolje je ne implementirati ove točke i razmotriti opciju #2.
- Ako postoji jasan zahtjev za fasetirano pretraživanje, imate dvije mogućnosti bez žrtvovanja izvedbe:
- Ne preračunavajte sve količine svaki put kada se promijene kriteriji pretraživanja.
- Koristite tražilice kao što su Solr, ElasticSearch, Sphinx i druge. Ali treba imati na umu da ne može biti zamjena za glavnu bazu podataka, već bi se trebala koristiti kao dodatak glavnoj pohrani za rješavanje problema pretraživanja.
- Također, u slučaju fasetnog pretraživanja, ima smisla podijeliti dohvaćanje stranice rezultata pretraživanja i brojanje u dva paralelna zahtjeva. Brojanje količina može trajati dulje od dobivanja rezultata, a korisniku su rezultati važniji.
- Ako koristite SQL bazu podataka za pretraživanje, svaka promjena koda povezana s ovim dijelom trebala bi biti dobro testirana za izvedbu na odgovarajućoj količini podataka (prekoračenje količine u živoj bazi podataka). Također je preporučljivo koristiti praćenje vremena izvršavanja upita na svim instancama baze podataka, a posebno na onoj „živoj“. Čak i ako je sve bilo u redu s planovima upita u fazi razvoja, kako količina podataka raste, situacija se može značajno promijeniti.
Izvor: www.habr.com