Eden tipičnih scenarijev v vseh aplikacijah, ki jih poznamo, je iskanje podatkov po določenih kriterijih in njihov prikaz v lahko berljivi obliki. Obstajajo lahko tudi dodatne možnosti za razvrščanje, združevanje in stranjenje. Naloga je v teoriji nepomembna, vendar pri njenem reševanju mnogi razvijalci naredijo številne napake, zaradi katerih se produktivnost kasneje zmanjša. Poskusimo razmisliti o različnih možnostih za rešitev te težave in oblikovati priporočila za izbiro najučinkovitejše izvedbe.
Možnost strani #1
Najenostavnejša možnost, ki pride na misel, je prikaz iskalnih rezultatov stran za stranjo v najbolj klasični obliki.
Recimo, da vaša aplikacija uporablja relacijsko bazo podatkov. V tem primeru boste morali za prikaz informacij v tem obrazcu izvesti dve poizvedbi SQL:
- Pridobite vrstice za trenutno stran.
- Izračunajte skupno število vrstic, ki ustrezajo iskalnim kriterijem - to je potrebno za prikaz strani.
Oglejmo si prvo poizvedbo na primeru testne baze podatkov MS SQL za strežnik 2016. V ta namen bomo uporabili tabelo Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Zgornja poizvedba bo vrnila prvih 50 naročil s seznama, razvrščenih po padajočem datumu dodajanja, z drugimi besedami, 50 najnovejših naročil.
Na testni bazi deluje hitro, a poglejmo načrt izvajanja in statistiko V/I:
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.Statistiko V/I za vsako poizvedbo lahko pridobite tako, da v izvajalnem okolju poizvedbe zaženete ukaz SET STATISTICS IO ON.
Kot lahko vidite iz izvedbenega načrta, je najbolj intenzivna možnost razvrščanje vseh vrstic izvorne tabele po datumu dodajanja. In težava je v tem, da več kot je vrstic v tabeli, "težje" bo razvrščanje. V praksi se je takšnim situacijam treba izogibati, zato dodamo indeks datumu dodajanja in preverimo, ali se je poraba virov spremenila:
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čitno je postalo veliko bolje. Toda ali so vsi problemi rešeni? Spremenimo poizvedbo za iskanje naročil, kjer skupna cena blaga presega 100 $:
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 smešno situacijo: načrt poizvedbe ni veliko slabši od prejšnjega, vendar je dejansko število logičnih branj skoraj dvakrat večje kot pri pregledu celotne tabele. Obstaja izhod - če iz že obstoječega indeksa naredimo sestavljeni indeks in kot drugo polje dodamo skupno ceno blaga, bomo spet dobili 165 logičnih branj:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
To vrsto primerov je mogoče nadaljevati še dolgo, vendar sta dve glavni misli, ki ju želim izraziti tukaj:
- Dodajanje katerega koli novega kriterija ali vrstnega reda iskalni poizvedbi lahko pomembno vpliva na hitrost iskalne poizvedbe.
- Če pa moramo odšteti le del podatkov in ne vseh rezultatov, ki se ujemajo z iskalnimi izrazi, obstaja veliko načinov za optimizacijo takšne poizvedbe.
Zdaj pa preidimo na drugo poizvedbo, omenjeno na samem začetku – tisto, ki šteje število zapisov, ki ustrezajo iskalnemu kriteriju. Vzemimo isti primer – iskanje naročil, ki so višja od 100 USD:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Glede na zgoraj navedeni sestavljeni indeks dobimo:
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.Dejstvo, da gre poizvedba skozi celoten indeks, ni presenetljivo, saj polje SubTotal ni na prvem mestu, zato ga poizvedba ne more uporabiti. Težavo rešimo tako, da polju SubTotal dodamo še en indeks in posledično dobimo le 48 logičnih branj.
Lahko navedete še nekaj primerov zahtevkov za štetje količin, vendar bistvo ostaja enako: prejemanje podatka in štetje skupnega zneska sta dve bistveno različni zahtevi, in vsaka zahteva svoje ukrepe za optimizacijo. Na splošno ne boste mogli najti kombinacije indeksov, ki bi delovala enako dobro za obe poizvedbi.
Skladno s tem je ena od pomembnih zahtev, ki jih je treba razjasniti pri razvoju takšne iskalne rešitve, ali je za podjetje res pomembno videti skupno število najdenih predmetov. Pogosto se zgodi, da št. In navigacija po določenih številkah strani je po mojem mnenju rešitev z zelo ozkim obsegom, saj je večina scenarijev ostranjenja videti kot »pojdi na naslednjo stran«.
Možnost strani #2
Predpostavimo, da uporabnikom ni mar za poznavanje skupnega števila najdenih predmetov. Poskusimo poenostaviti iskalno stran:
Pravzaprav je edina stvar, ki se je spremenila, ta, da ni možnosti za navigacijo do določenih številk strani, in zdaj tej tabeli ni treba vedeti, koliko jih je lahko, da bi jo lahko prikazali. Toda postavlja se vprašanje - kako tabela ve, ali obstajajo podatki za naslednjo stran (da bi pravilno prikazala povezavo »Naprej«)?
Odgovor je zelo preprost: iz baze podatkov lahko preberete en zapis več, kot je potrebno za prikaz, in prisotnost tega "dodatnega" zapisa bo pokazala, ali obstaja naslednji del. Na ta način morate zagnati samo eno zahtevo, da dobite eno stran podatkov, kar bistveno izboljša zmogljivost in olajša podporo takšni funkcionalnosti. V moji praksi je bil primer, ko je zavrnitev štetja skupnega števila zapisov pospešila dostavo rezultatov za 4-5 krat.
Za ta pristop obstaja več možnosti uporabniškega vmesnika: ukaza "nazaj" in "naprej", kot v zgornjem primeru, gumb "naloži več", ki preprosto doda nov del prikazanim rezultatom, "neskončno drsenje", ki deluje po principu »naloži več«, vendar je znak za pridobitev naslednje porcije ta, da uporabnik pomakne vse prikazane rezultate do konca. Ne glede na vizualno rešitev ostaja princip vzorčenja podatkov enak.
Nianse izvajanja straničenja
Vsi zgoraj navedeni primeri poizvedb uporabljajo pristop »odmik + štetje«, ko poizvedba sama določa, v katerem vrstnem redu so vrstice rezultatov in koliko vrstic je treba vrniti. Najprej poglejmo, kako najbolje organizirati posredovanje parametrov v tem primeru. V praksi sem se srečal z več metodami:
- Serijska številka zahtevane strani (pageIndex), velikost strani (pageSize).
- Serijska številka prvega vrnjenega zapisa (startIndex), največje število zapisov v rezultatu (štetje).
- Zaporedna številka prvega zapisa, ki bo vrnjen (startIndex), zaporedna številka zadnjega zapisa, ki bo vrnjen (endIndex).
Na prvi pogled se morda zdi, da je to tako elementarno, da ni nobene razlike. Vendar to ni tako - najbolj priročna in univerzalna možnost je druga (startIndex, count). Razlogov za to je več:
- Za zgoraj navedeni pristop lektoriranja vnosa +1 je prva možnost s pageIndex in pageSize izjemno neprijetna. Na primer, želimo prikazati 50 objav na stran. V skladu z zgornjim algoritmom morate prebrati en zapis več, kot je potrebno. Če ta "+1" ni implementiran na strežniku, se izkaže, da moramo za prvo stran zahtevati zapise od 1 do 51, za drugo - od 51 do 101 itd. Če določite velikost strani 51 in povečate pageIndex, se bo druga stran vrnila s 52 na 102 itd. Skladno s tem je v prvi možnosti edini način za pravilno implementacijo gumba za prehod na naslednjo stran ta, da strežnik lektorira "dodatno" vrstico, kar bo zelo implicitna niansa.
- Tretja možnost sploh ni smiselna, saj boste za zagon poizvedb v večini baz podatkov še vedno morali posredovati štetje in ne indeksa zadnjega zapisa. Odštevanje startIndex od endIndex je lahko preprosta aritmetična operacija, vendar je tukaj odveč.
Zdaj bi morali opisati slabosti izvajanja ostranjenja prek »odmika + količine«:
- Pridobivanje vsake naslednje strani bo dražje in počasnejše od prejšnje, saj bo baza še vedno morala pregledati vse zapise »od začetka« po kriterijih iskanja in razvrščanja, nato pa se ustaviti na želenem fragmentu.
- Vsi DBMS-ji ne podpirajo tega pristopa.
Obstajajo alternative, a so tudi nepopolne. Prvi od teh pristopov se imenuje "stranjenje nabora ključev" ali "metoda iskanja" in je naslednji: po prejemu dela si lahko zapomnite vrednosti polj v zadnjem zapisu na strani in jih nato uporabite za pridobitev naslednji del. Izvedli smo na primer naslednjo poizvedbo:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
In v zadnjem zapisu smo dobili vrednost datuma naročila '2014-06-29'. Če želite dobiti naslednjo stran, lahko poskusite narediti to:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Težava je v tem, da je polje OrderDate neenolično in da bo zgoraj navedeni pogoj verjetno zgrešil veliko zahtevanih vrstic. Če želite tej poizvedbi dodati nedvoumnost, morate pogoju dodati edinstveno polje (predpostavimo, da je 75074 zadnja vrednost primarnega ključa iz prvega dela):
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
Ta možnost bo delovala pravilno, vendar jo bo na splošno težko optimizirati, saj pogoj vsebuje operator ALI. Če se vrednost primarnega ključa povečuje, ko se povečuje OrderDate, lahko pogoj poenostavite tako, da pustite samo filter po SalesOrderID. Toda če ni stroge korelacije med vrednostmi primarnega ključa in poljem, po katerem je rezultat razvrščen, se temu ALI ni mogoče izogniti v večini DBMS. Izjema, za katero vem, je PostgreSQL, ki v celoti podpira primerjave tulp, zgornji pogoj pa je mogoče zapisati kot "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Glede na sestavljeni ključ s tema dvema poljema bi morala biti poizvedba, kot je ta, dokaj enostavna.
Drugi alternativni pristop lahko najdemo na primer v ali — ko zahteva poleg podatkov vrne še poseben identifikator, s katerim lahko pridobite naslednji del podatkov. Če ima ta identifikator neomejeno življenjsko dobo (kot v Comsos DB), potem je to odličen način za implementacijo ostranjenja z zaporednim prehodom med stranmi (zgoraj omenjena možnost št. 2). Njegove možne slabosti: ni podprt v vseh DBMS; nastali identifikator naslednjega dela ima lahko omejeno življenjsko dobo, ki na splošno ni primerna za izvajanje uporabniške interakcije (kot je API za drsenje ElasticSearch).
Kompleksno filtriranje
Zakomplicirajmo nalogo še naprej. Recimo, da obstaja zahteva po implementaciji tako imenovanega fasetnega iskanja, ki ga vsi dobro poznajo iz spletnih trgovin. Zgornji primeri, ki temeljijo na tabeli naročil, v tem primeru niso preveč ilustrativni, zato preklopimo na tabelo izdelkov iz baze podatkov AdventureWorks:
Kakšna je ideja fasetnega iskanja? Dejstvo je, da je za vsak element filtra prikazano število zapisov, ki izpolnjujejo ta kriterij ob upoštevanju filtrov, izbranih v vseh drugih kategorijah.
Če na primer v tem primeru izberemo kategorijo Kolesa in črno barvo, bodo v tabeli prikazana samo črna kolesa, vendar:
- Za vsak kriterij v skupini Kategorije bo število izdelkov iz te kategorije prikazano v črni barvi.
- Za vsak kriterij skupine »Barve« bo prikazano število koles te barve.
Tukaj je primer izhodnega rezultata za takšne pogoje:
Če obkljukate še kategorijo “Oblačila”, bodo v tabeli prikazana tudi črna oblačila, ki so na zalogi. Tudi število črnih izdelkov v razdelku »Barva« bo preračunano glede na nove pogoje, samo v razdelku »Kategorije« se ne bo nič spremenilo ... Upam, da so ti primeri dovolj za razumevanje običajnega algoritma fasetnega iskanja.
Zdaj pa si predstavljajmo, kako je to mogoče implementirati na relacijski osnovi. Vsaka skupina meril, kot sta kategorija in barva, bo zahtevala ločeno poizvedbo:
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
Kaj je narobe s to rešitvijo? Zelo preprosto je - ni dobro v merilu. Vsak razdelek filtra zahteva ločeno poizvedbo za izračun količin, te poizvedbe pa niso najlažje. V spletnih trgovinah imajo lahko nekatere kategorije več deset razdelkov filtrov, kar je lahko resna težava pri delovanju.
Običajno se mi po teh izjavah ponudi nekaj rešitev, in sicer:
- Združite vse količine v eno poizvedbo. Tehnično je to mogoče z uporabo ključne besede UNION, vendar ne bo veliko pripomoglo k zmogljivosti - baza podatkov bo morala še vedno izvesti vsak fragment od začetka.
- Količine predpomnilnika. To se mi predlaga skoraj vsakič, ko opišem težavo. Opozorilo je, da je to na splošno nemogoče. Recimo, da imamo 10 "faset", od katerih ima vsaka 5 vrednosti. To je zelo "skromna" situacija v primerjavi s tistim, kar je mogoče videti v istih spletnih trgovinah. Izbira enega fasetnega elementa vpliva na količine v 9 drugih, z drugimi besedami, za vsako kombinacijo kriterijev so lahko količine drugačne. V našem primeru je skupno 50 kriterijev, ki jih lahko uporabnik izbere, torej bo možnih kombinacij 250. Za zapolnitev takega niza podatkov ni dovolj pomnilnika ali časa. Tukaj lahko ugovarjate in rečete, da niso vse kombinacije resnične in uporabnik redko izbere več kot 5-10 kriterijev. Da, mogoče je izvajati leno nalaganje in predpomniti količino le tistega, kar je bilo kdaj izbrano, vendar več ko je izbir, manj učinkovit bo takšen predpomnilnik in bolj opazne bodo težave z odzivnim časom (še posebej, če nabor podatkov se redno spreminja).
Na srečo ima taka težava že dolgo precej učinkovite rešitve, ki predvidljivo delujejo na velikih količinah podatkov. Pri kateri koli od teh možnosti je smiselno preračunavanje faset in prejemanje strani z rezultati razdeliti na dva vzporedna klica strežniku in uporabniški vmesnik organizirati tako, da nalaganje podatkov po fasetah »ne moti« prikaza Rezultati iskanja.
- Pokličite popoln preračun "faset" čim redkeje. Na primer, ne preračunajte vsega znova vsakič, ko se spremenijo kriteriji iskanja, temveč namesto tega poiščite skupno število rezultatov, ki ustrezajo trenutnim pogojem, in pozovite uporabnika, da jih prikaže - "1425 zapisov najdenih, prikaži?" Uporabnik lahko nadaljuje s spreminjanjem iskalnih izrazov ali pa klikne gumb »pokaži«. Šele v drugem primeru bodo izvedene vse zahteve za pridobitev rezultatov in preračunavanje količin na vseh “vidikih”. V tem primeru, kot zlahka vidite, se boste morali ukvarjati z zahtevo za pridobitev skupnega števila rezultatov in njegovo optimizacijo. To metodo lahko najdete v številnih majhnih spletnih trgovinah. Očitno to ni zdravilo za to težavo, vendar je v preprostih primerih lahko dober kompromis.
- Za iskanje rezultatov in štetje faset uporabite iskalnike, kot so Solr, ElasticSearch, Sphinx in drugi. Vsi so zasnovani tako, da gradijo "fasete" in to počnejo precej učinkovito zaradi obrnjenega indeksa. Kako delujejo iskalniki, zakaj so v takih primerih učinkovitejši od splošnih zbirk podatkov, kakšne prakse in pasti obstajajo - to je tema za ločen članek. Tukaj vas želim opozoriti na dejstvo, da iskalnik ne more nadomestiti glavnega pomnilnika podatkov, ampak se uporablja kot dodatek: vse spremembe v glavni bazi podatkov, ki so pomembne za iskanje, se sinhronizirajo v iskalni indeks; Iskalnik običajno komunicira samo z iskalnikom in ne dostopa do glavne baze podatkov. Ena najpomembnejših točk tukaj je, kako zanesljivo organizirati to sinhronizacijo. Vse je odvisno od zahtev glede "reakcijskega časa". Če čas med spremembo v glavni bazi podatkov in njeno "manifestacijo" v iskanju ni kritičen, lahko ustvarite storitev, ki vsakih nekaj minut išče nedavno spremenjene zapise in jih indeksira. Če želite čim krajši odzivni čas, lahko implementirate nekaj podobnega za pošiljanje posodobitev iskalni storitvi.
Ugotovitve
- Implementacija ostranjenja na strani strežnika je pomemben zaplet in je smiselna samo za hitro rastoče ali preprosto velike nize podatkov. Absolutno natančnega recepta, kako oceniti »veliko« ali »hitro rastoče«, ni, vendar bi se držal tega pristopa:
- Če sprejem celotne zbirke podatkov, ob upoštevanju strežniškega časa in omrežnega prenosa, običajno ustreza zahtevam glede zmogljivosti, nima smisla izvajati ostranjevanje na strani strežnika.
- Lahko pride do situacije, ko v bližnji prihodnosti ni pričakovati težav z delovanjem, saj je podatkov malo, vendar zbiranje podatkov nenehno narašča. Če kakšen nabor podatkov v prihodnosti morda ne bo več zadovoljeval prejšnje točke, je bolje, da začnete ostranjevanje takoj.
- Če s strani podjetja ni stroge zahteve glede prikaza skupnega števila rezultatov ali prikaza številk strani in vaš sistem nima iskalnika, je bolje, da teh točk ne uvedete in razmislite o možnosti št. 2.
- Če obstaja jasna zahteva za fasetno iskanje, imate dve možnosti, ne da bi žrtvovali zmogljivost:
- Ne preračunajte vseh količin vsakič, ko se spremenijo iskalni kriteriji.
- Uporabite iskalnike, kot so Solr, ElasticSearch, Sphinx in drugi. Vendar je treba razumeti, da ne more biti zamenjava za glavno bazo podatkov in jo je treba uporabiti kot dodatek glavnemu pomnilniku za reševanje težav pri iskanju.
- Poleg tega je v primeru fasetnega iskanja smiselno razdeliti iskanje strani z rezultati iskanja in štetje na dve vzporedni zahtevi. Štetje količin lahko traja dlje kot pridobivanje rezultatov, rezultati pa so za uporabnika bolj pomembni.
- Če za iskanje uporabljate zbirko podatkov SQL, je treba vsako spremembo kode, povezano s tem delom, dobro preizkusiti glede delovanja na ustrezni količini podatkov (ki presega obseg v zbirki podatkov v živo). Prav tako je priporočljivo uporabljati spremljanje časa izvajanja poizvedb na vseh instancah baze podatkov, še posebej pa na tisti »v živo«. Tudi če je bilo z načrti poizvedb v fazi razvoja vse v redu, se lahko z naraščanjem količine podatkov situacija opazno spremeni.
Vir: www.habr.com