Vienas iš tipiškų scenarijų visose mums žinomose programose yra duomenų paieška pagal tam tikrus kriterijus ir jų rodymas lengvai skaitoma forma. Taip pat gali būti papildomų rūšiavimo, grupavimo ir puslapių puslapių parinkčių. Užduotis teoriškai nereikšminga, tačiau ją spręsdami daugelis kūrėjų daro nemažai klaidų, dėl kurių vėliau nukenčia produktyvumas. Pabandykime apsvarstyti įvairius šios problemos sprendimo variantus ir suformuluoti rekomendacijas, kaip pasirinkti efektyviausią įgyvendinimą.
Puslapio parinktis Nr. 1
Paprasčiausias variantas, kuris ateina į galvą, yra kiekvieno puslapio paieškos rezultatų pateikimas klasikine forma.
Tarkime, jūsų programa naudoja reliacinę duomenų bazę. Tokiu atveju, norėdami rodyti informaciją šioje formoje, turėsite paleisti dvi SQL užklausas:
- Gaukite dabartinio puslapio eilutes.
- Apskaičiuokite bendrą paieškos kriterijus atitinkančių eilučių skaičių – tai būtina puslapiams rodyti.
Pažvelkime į pirmąją užklausą, kaip pavyzdį naudodami bandomąją MS SQL duomenų bazę serveriui 2016 m. Šiuo tikslu naudosime lentelę Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Aukščiau pateikta užklausa pateiks pirmuosius 50 užsakymų iš sąrašo, surūšiuotų pagal įtraukimo datą mažėjimo tvarka, kitaip tariant, 50 naujausių užsakymų.
Jis greitai veikia bandymų bazėje, bet pažvelkime į vykdymo planą ir įvesties / išvesties statistiką:
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.Kiekvienos užklausos įvesties / išvesties statistiką galite gauti paleidę komandą SET STATISTICS IO ON užklausos vykdymo metu.
Kaip matote iš vykdymo plano, daugiausiai išteklių reikalaujanti parinktis yra rūšiuoti visas šaltinio lentelės eilutes pagal pridėjimo datą. O problema ta, kad kuo daugiau lentelėje atsiras eilučių, tuo „sunkesnis“ bus rūšiavimas. Praktiškai tokių situacijų reikėtų vengti, tad pridėkime prie papildymo datos indeksą ir pažiūrėkime, ar nepasikeitė išteklių suvartojimas:
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.
Akivaizdu, kad tapo daug geriau. Bet ar visos problemos išspręstos? Pakeiskime užklausą ir ieškokime užsakymų, kurių bendra prekių kaina viršija 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.Turime juokingą situaciją: užklausos planas yra ne ką prastesnis nei ankstesnis, tačiau tikrasis loginių skaitymų skaičius yra beveik dvigubai didesnis nei nuskaitant visą lentelę. Yra išeitis - jei sudarysime sudėtinį indeksą iš jau esančio indekso ir kaip antrą lauką pridėsime bendrą prekių kainą, vėl gausime 165 loginius rodmenis:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Šią pavyzdžių seriją galima tęsti ilgą laiką, tačiau čia noriu išreikšti dvi pagrindines mintis:
- Naujo kriterijaus ar rūšiavimo tvarkos įtraukimas į paieškos užklausą gali turėti didelės įtakos paieškos užklausos greičiui.
- Bet jei mums reikia atimti tik dalį duomenų, o ne visus paieškos terminus atitinkančius rezultatus, yra daug būdų, kaip optimizuoti tokią užklausą.
Dabar pereikime prie antrosios užklausos, paminėtos pačioje pradžioje – tos, kuri skaičiuoja paieškos kriterijų atitinkančių įrašų skaičių. Paimkime tą patį pavyzdį – daugiau nei 100 USD užsakymų paieška:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Atsižvelgdami į aukščiau nurodytą sudėtinį indeksą, gauname:
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.Tai, kad užklausa eina per visą indeksą, nestebina, nes laukas „SubTotal“ nėra pirmoje pozicijoje, todėl užklausa negali jo naudoti. Problema išspręsta įtraukus kitą indeksą į lauką „SubTotal“, todėl jis pateikia tik 48 loginius skaitymus.
Galite pateikti dar keletą užklausų dėl kiekių skaičiavimo pavyzdžių, tačiau esmė išlieka ta pati: gauti duomenų dalį ir suskaičiuoti bendrą sumą yra du iš esmės skirtingi prašymai, ir kiekvienam reikia savo optimizavimo priemonių. Apskritai negalėsite rasti indeksų derinio, kuris vienodai gerai veiktų abiejose užklausose.
Atitinkamai, vienas iš svarbių reikalavimų, kurį reikėtų išsiaiškinti kuriant tokį paieškos sprendimą – ar verslui tikrai svarbu matyti bendrą rastų objektų skaičių. Dažnai atsitinka, kad ne. O naršymas pagal konkrečius puslapių numerius, mano nuomone, yra labai siauros apimties sprendimas, nes dauguma puslapių paieškos scenarijų atrodo kaip „eiti į kitą puslapį“.
Puslapio parinktis Nr. 2
Tarkime, kad vartotojams nerūpi žinoti bendrą rastų objektų skaičių. Pabandykime supaprastinti paieškos puslapį:
Tiesą sakant, pasikeitė tik tai, kad nėra galimybės pereiti prie konkrečių puslapių numerių, o dabar šiai lentelei nereikia žinoti, kiek jų gali būti, kad ji būtų rodoma. Tačiau kyla klausimas - kaip lentelė žino, ar yra kito puslapio duomenų (kad teisingai būtų rodoma nuoroda „Kitas“)?
Atsakymas labai paprastas: iš duomenų bazės galite nuskaityti dar vieną įrašą, nei reikia rodyti, o šio „papildomo“ įrašo buvimas parodys, ar yra kita dalis. Tokiu būdu jums tereikia vykdyti vieną užklausą, kad gautumėte vieną duomenų puslapį, o tai žymiai pagerina našumą ir palengvina tokių funkcijų palaikymą. Mano praktikoje buvo atvejis, kai atsisakymas skaičiuoti bendrą įrašų skaičių paspartino rezultatų pateikimą 4-5 kartus.
Šiam metodui yra keletas vartotojo sąsajos parinkčių: komandos „atgal“ ir „pirmyn“, kaip ir aukščiau esančiame pavyzdyje, mygtukas „įkelti daugiau“, kuris tiesiog prideda naują rodomų rezultatų dalį, „begalinis slinkimas“, kuris veikia. „Įkelti daugiau“ principu, tačiau signalas gauti kitą dalį yra vartotojui, kad jis slinktų visus rodomus rezultatus iki galo. Kad ir koks būtų vizualinis sprendimas, duomenų atrankos principas išlieka tas pats.
Puslapio diegimo niuansai
Visuose aukščiau pateiktuose užklausų pavyzdžiuose naudojamas „poslinkis + skaičius“, kai pati užklausa nurodo, kokia tvarka rezultatų eilučių ir kiek eilučių reikia grąžinti. Pirmiausia pažiūrėkime, kaip šiuo atveju geriausiai organizuoti parametrų perdavimą. Praktikoje susidūriau su keliais būdais:
- Prašomo puslapio serijos numeris (pageIndex), puslapio dydis (pageSize).
- Pirmo grąžintino įrašo serijos numeris (startIndex), maksimalus įrašų skaičius rezultate (count).
- Pirmo grąžintino įrašo eilės numeris (startIndex), paskutinio grąžintino įrašo eilės numeris (endIndex).
Iš pirmo žvilgsnio gali atrodyti, kad tai taip elementaru, kad nėra jokio skirtumo. Bet taip nėra – patogiausias ir universaliausias variantas yra antrasis (startIndex, count). Tam yra keletas priežasčių:
- Taikant anksčiau pateiktą +1 įrašo korektūros metodą, pirmoji parinktis su pageIndex ir pageSize yra labai nepatogi. Pavyzdžiui, norime, kad puslapyje būtų rodoma 50 pranešimų. Pagal aukščiau pateiktą algoritmą reikia perskaityti vienu įrašu daugiau nei reikia. Jei šis „+1“ neįdiegtas serveryje, paaiškėja, kad pirmame puslapyje turime prašyti įrašų nuo 1 iki 51, antrame - nuo 51 iki 101 ir tt. Jei nurodysite 51 puslapio dydį ir padidinsite puslapio indeksą, antrasis puslapis grįš nuo 52 iki 102 ir pan. Atitinkamai, pirmajame variante vienintelis būdas tinkamai įdiegti mygtuką, kad pereitumėte į kitą puslapį, yra priversti serverį perskaityti „papildomą“ eilutę, o tai bus labai netiesioginis niuansas.
- Trečioji parinktis visiškai neturi prasmės, nes norint vykdyti užklausas daugumoje duomenų bazių, vis tiek reikės perduoti skaičių, o ne paskutinio įrašo indeksą. StartIndex atėmimas iš endIndex gali būti paprastas aritmetinis veiksmas, tačiau čia jis nereikalingas.
Dabar turėtume apibūdinti puslapių kūrimo per „kompensaciją + kiekis“ trūkumus:
- Kiekvieno paskesnio puslapio gavimas bus brangesnis ir lėtesnis nei ankstesnis, nes duomenų bazėje vis tiek reikės „nuo pradžios“ pagal paieškos ir rūšiavimo kriterijus pereiti visus įrašus, o tada sustoti ties norimu fragmentu.
- Ne visos DBVS gali palaikyti šį metodą.
Yra alternatyvų, bet jos taip pat netobulos. Pirmasis iš šių metodų vadinamas „raktų rinkinio puslapiu“ arba „ieškos metodu“ ir yra toks: gavę dalį galite prisiminti paskutinio puslapio įrašo lauko reikšmes ir jas naudoti norėdami gauti kita dalis. Pavyzdžiui, atlikome šią užklausą:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
O paskutiniame įraše gavome užsakymo datos reikšmę ‘2014-06-29’. Tada norėdami gauti kitą puslapį galite pabandyti tai padaryti:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Problema ta, kad užsakymo data yra neunikalus laukas ir dėl anksčiau nurodytos sąlygos greičiausiai bus praleista daug privalomų eilučių. Kad ši užklausa būtų nedviprasmiška, prie sąlygos turite pridėti unikalų lauką (tarkime, kad 75074 yra paskutinė pirminio rakto reikšmė iš pirmosios dalies):
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
Ši parinktis veiks tinkamai, tačiau apskritai ją bus sunku optimizuoti, nes sąlygoje yra operatorius ARBA. Jei pirminio rakto reikšmė didėja didėjant OrderDate, sąlygą galima supaprastinti paliekant tik filtrą pagal SalesOrderID. Bet jei nėra griežtos koreliacijos tarp pirminio rakto ir lauko, pagal kurį rūšiuojamas rezultatas, verčių, šio ARBA negalima išvengti daugumoje DBVS. Išimtis, apie kurią žinau, yra PostgreSQL, kuri visiškai palaiko eilučių palyginimą, o pirmiau nurodyta sąlyga gali būti parašyta kaip "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Turint omenyje sudėtinį raktą su šiais dviem laukais, tokia užklausa turėtų būti gana paprasta.
Antrą alternatyvų metodą galima rasti, pavyzdžiui, arba — kai užklausoje, be duomenų, pateikiamas specialus identifikatorius, su kuriuo galite gauti kitą duomenų dalį. Jei šis identifikatorius galioja neribotą laiką (kaip Comsos DB), tai yra puikus būdas įdiegti puslapius su nuosekliu perėjimu tarp puslapių (anksčiau paminėta 2 parinktis). Galimi jo trūkumai: jis palaikomas ne visose DBVS; gautas kito gabalo identifikatorius gali turėti ribotą galiojimo laiką, kuris paprastai nėra tinkamas vartotojo sąveikai įgyvendinti (pvz., ElasticSearch slinkties API).
Sudėtingas filtravimas
Dar labiau apsunkinkime užduotį. Tarkime, yra reikalavimas įdiegti vadinamąją faceted paiešką, kuri visiems labai pažįstama iš internetinių parduotuvių. Aukščiau pateikti pavyzdžiai, pagrįsti užsakymų lentele, šiuo atveju nėra labai iliustratyvūs, todėl iš AdventureWorks duomenų bazės pereikime prie produktų lentelės:
Kokia yra detalios paieškos idėja? Faktas yra tas, kad kiekvienam filtro elementui rodomas šį kriterijų atitinkančių įrašų skaičius atsižvelgiant į filtrus, pasirinktus visose kitose kategorijose.
Pavyzdžiui, jei šiame pavyzdyje pasirinksime kategoriją Dviračiai ir juodą spalvą, lentelėje bus rodomi tik juodi dviračiai, bet:
- Kiekvieno kriterijaus grupėje Kategorijos produktų skaičius iš tos kategorijos bus rodomas juodai.
- Prie kiekvieno „Spalvų“ grupės kriterijaus bus rodomas šios spalvos dviračių skaičius.
Štai tokių sąlygų rezultatų išvesties pavyzdys:
Jei pažymėsite ir kategoriją „Apranga“, lentelėje taip pat bus rodomi sandėlyje esantys juodi drabužiai. Skiltyje “Spalva” juodų gaminių skaičius taip pat bus perskaičiuotas pagal naujas sąlygas, tik “Kategorijos” skiltyje niekas nepasikeis... Tikiuosi, kad šių pavyzdžių pakaks suprasti įprastą briaunoto paieškos algoritmą.
Dabar įsivaizduokime, kaip tai galima įgyvendinti santykiniu pagrindu. Kiekvienai kriterijų grupei, pvz., kategorijai ir spalvai, reikės atskiros užklausos:
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
Kas negerai su šiuo sprendimu? Tai labai paprasta – jis netinkamai keičiamas. Kiekvienai filtrų sekcijai reikalinga atskira užklausa dydžiams apskaičiuoti, o šios užklausos nėra pačios lengviausios. Internetinėse parduotuvėse kai kurios kategorijos gali turėti kelias dešimtis filtrų skyrių, o tai gali būti rimta našumo problema.
Paprastai po šių teiginių man siūlomi keli sprendimai, būtent:
- Sujunkite visus kiekio skaičiavimus į vieną užklausą. Techniškai tai įmanoma naudojant raktinį žodį UNION, tačiau tai labai nepadės našumui – duomenų bazė vis tiek turės vykdyti kiekvieną fragmentą nuo nulio.
- Talpyklos kiekiai. Tai man siūloma beveik kiekvieną kartą aprašant problemą. Įspėjimas yra tas, kad tai paprastai neįmanoma. Tarkime, kad turime 10 „aspektų“, kurių kiekvienas turi 5 reikšmes. Tai labai „kukli“ situacija, palyginti su tuo, ką galima pamatyti tose pačiose internetinėse parduotuvėse. Vieno aspekto elemento pasirinkimas turi įtakos 9 kitų dydžiams, kitaip tariant, kiekvieno kriterijų derinio kiekiai gali būti skirtingi. Mūsų pavyzdyje iš viso yra 50 kriterijų, kuriuos vartotojas gali pasirinkti, todėl galimų derinių bus 250. Neužtenka atminties ar laiko tokiam duomenų masyvui užpildyti. Čia galima prieštarauti ir sakyti, kad ne visos kombinacijos yra tikros ir vartotojas retai pasirenka daugiau nei 5-10 kriterijų. Taip, galima atlikti atsainį įkėlimą ir talpykloje išsaugoti tik tą kiekį, kuris kada nors buvo pasirinktas, tačiau kuo daugiau pasirinkimų, tuo tokia talpykla bus mažiau efektyvi ir bus pastebimesnės reakcijos laiko problemos (ypač jei duomenų rinkinys reguliariai keičiasi).
Laimei, tokia problema jau seniai turi gana veiksmingų sprendimų, kurie nuspėjamai veikia didelius duomenų kiekius. Bet kuriai iš šių parinkčių prasminga padalinti aspektų perskaičiavimą ir rezultatų puslapio gavimą į du lygiagrečius iškvietimus į serverį ir sutvarkyti vartotojo sąsają taip, kad duomenų įkėlimas pagal aspektus „netrukdytų“ rodyti Paieškos rezultatai.
- Skambinkite visiškai perskaičiuoti "aspektus" kuo rečiau. Pavyzdžiui, neperskaičiuokite visko kaskart pasikeitus paieškos kriterijams, o suraskite bendrą esamas sąlygas atitinkančių rezultatų skaičių ir paraginkite vartotoją juos parodyti – „Rasti 1425 įrašai, rodyti?“ Vartotojas gali tęsti paieškos terminų keitimą arba spustelėti mygtuką „rodyti“. Tik antruoju atveju bus įvykdyti visi prašymai gauti rezultatus ir perskaičiuoti kiekius visuose „pusiuose“. Tokiu atveju, kaip nesunkiai matote, turėsite susidoroti su užklausa gauti bendrą rezultatų skaičių ir jo optimizavimą. Šį metodą galima rasti daugelyje mažų internetinių parduotuvių. Akivaizdu, kad tai nėra panacėja nuo šios problemos, tačiau paprastais atvejais tai gali būti geras kompromisas.
- Naudokite paieškos variklius, kad rastumėte rezultatus ir suskaičiuotumėte aspektus, pvz., Solr, ElasticSearch, Sphinx ir kt. Visi jie skirti kurti "fasetams" ir tai daro gana efektyviai dėl apversto indekso. Kaip veikia paieškos sistemos, kodėl tokiais atvejais jos yra veiksmingesnės už bendros paskirties duomenų bazes, kokios praktikos ir spąstų yra – tai atskiro straipsnio tema. Čia noriu atkreipti dėmesį į tai, kad paieškos sistema negali pakeisti pagrindinės duomenų saugyklos, ji naudojama kaip priedas: bet kokie paieškai aktualūs pagrindinės duomenų bazės pakeitimai sinchronizuojami į paieškos indeksą; Paieškos sistema dažniausiai sąveikauja tik su paieškos sistema ir nepasiekia pagrindinės duomenų bazės. Vienas iš svarbiausių dalykų yra tai, kaip patikimai organizuoti šią sinchronizaciją. Viskas priklauso nuo „reakcijos laiko“ reikalavimų. Jei laikas nuo pagrindinės duomenų bazės pakeitimo iki jo „pasireiškimo“ paieškoje nėra svarbus, galite sukurti paslaugą, kuri kas kelias minutes ieško neseniai pakeistų įrašų ir juos indeksuoja. Jei norite kuo trumpesnio atsako laiko, galite įgyvendinti kažką panašaus norėdami siųsti atnaujinimus į paieškos paslaugą.
išvados
- Serverio puslapių ieškos diegimas yra didelė komplikacija ir prasminga tik greitai augantiems arba tiesiog dideliems duomenų rinkiniams. Nėra absoliučiai tikslaus recepto, kaip vertinti „didelį“ ar „greitai augantį“, tačiau vadovaučiausi tokiu požiūriu:
- Jei viso duomenų rinkinio gavimas, atsižvelgiant į serverio laiką ir tinklo perdavimą, paprastai atitinka našumo reikalavimus, nėra prasmės diegti ieškos serverio pusėje.
- Gali susidaryti situacija, kai artimiausiu metu našumo problemų nesitikima, nes duomenų yra mažai, tačiau duomenų rinkimas nuolat auga. Jei tam tikras duomenų rinkinys ateityje gali nebetenkinti ankstesnio punkto, geriau pradėti ieškoti puslapių iš karto.
- Jei verslui nėra griežto reikalavimo rodyti bendrą rezultatų skaičių ar puslapių numerius, o jūsų sistemoje nėra paieškos sistemos, geriau šių punktų neįgyvendinti ir apsvarstyti 2 variantą.
- Jei yra aiškus briaunuotos paieškos reikalavimas, turite dvi parinktis neprarandant našumo:
- Neperskaičiuokite visų kiekių kiekvieną kartą, kai pasikeičia paieškos kriterijai.
- Naudokite tokias paieškos sistemas kaip Solr, ElasticSearch, Sphinx ir kt. Tačiau reikia suprasti, kad tai negali būti pagrindinės duomenų bazės pakaitalas ir turėtų būti naudojamas kaip pagrindinės saugyklos priedas sprendžiant paieškos problemas.
- Be to, briaunuotos paieškos atveju prasminga paieškos rezultatų puslapio gavimą ir skaičiavimą padalyti į dvi lygiagrečias užklausas. Kiekių skaičiavimas gali užtrukti ilgiau nei gauti rezultatus, o rezultatai yra svarbesni vartotojui.
- Jei paieškai naudojate SQL duomenų bazę, bet koks su šia dalimi susijęs kodo pakeitimas turi būti gerai patikrintas, ar tinkamai veikia atitinkamas duomenų kiekis (viršijant tiesioginės duomenų bazės kiekį). Taip pat patartina naudoti užklausos vykdymo laiko stebėjimą visuose duomenų bazės egzemplioriuose, o ypač „gyvoje“. Net jei su užklausų planais kūrimo etape viskas buvo gerai, augant duomenų kiekiui situacija gali pastebimai pasikeisti.
Šaltinis: www.habr.com