Üks tüüpiline stsenaarium kõigis tuttavates rakendustes on andmete otsimine teatud kriteeriumide alusel ja nende esitamine mugaval lugemisviisil. Siin võivad olla ka lisavõimalused sorteerimiseks, rühmitamiseks ja lehemitteväljundiks. Ülesanne on iseenesest triviaalne, kuid selle lahendamisel teevad paljud arendajad mitmeid vigu, mis halvavad seejärel jõudlust. Proovime vaadata erinevaid lahendusi sellele ülesandele ja formuleerime soovitusi, et valida kõige tõhusam teostus.
Lehemittevälja variant #1
Kõige lihtsam variant, mis pähe tuleb, on otsingutulemuste lehemittevälja esitamine kõige klassikalisemas vormis.
Oletame, et rakenduses kasutatakse relatsioonilist andmebaasi. Sel juhul tuleb selle kujul teabe kuvamiseks täita kaks SQL päringut:
- Saada praeguse lehe jaoks read.
- Loendada otsingu kriteeriumidele vastavad read – see on vajalik lehtede näitamiseks.
Vaatame esimest päringut näite põhjal testimis MS SQL andmebaasist 2016 serveri jaoks. Selleks kasutame tabelit Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ülaltoodud päring kuvab esimesed 50 tellimust nimekirjast, mis on sorteeritud lisamise kuupäeva järgi kahanevas järjekorras, teisisõnu – 50 viimast tellimust.
See täidetakse kiiresti testimisandmebaasis, aga vaatame täitmisplaani ja sisendi-väljundi statistikat:
Tabel 'SalesOrderHeader'. Skaneerimise arv 1, loogilised lugemised 698, füüsilised lugemised 0, eelnevad lugemised 0, lob loogilised lugemised 0, lob füüsilised lugemised 0, lob eelnevad lugemised 0.Iga päringu sisendi/väljundi statistika saamiseks saab päringute täitmise keskkonnas käivitada käsu SET STATISTICS IO ON.
Nagu täitmisplaanist näha, on kõige ressursimahukam kõigi ridade sorteerimine algsest tabelist lisamise kuupäeva järgi. Probleem on see, et mida rohkem ridu tabelisse lisandub, seda 'raske'maks muutub sorteerimine. Selliste olukordade vältimiseks tuleks praktikas indeks lisada lisamise kuupäeva järgi ja vaadata, kas ressursikasutus on muutunud:
Tabel 'SalesOrderHeader'. Skaneerimise arv 1, loogilised lugemised 165, füüsilised lugemised 0, eelnevad lugemised 5, lob loogilised lugemised 0, lob füüsilised lugemised 0, lob eelnevad lugemised 0.
Ilmselgelt on olukord muutunud palju paremaks. Aga kas kõik probleemid on lahendatud? Muudame päringut, et otsida tellimusi, mille kaupade koguväärtus ületab 100 dollarit:
SELECT * FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Tabel 'SalesOrderHeader'. Skaneerimise arv 1, loogilised lugemised 1081, füüsilised lugemised 0, eelnevad lugemised 0, lob loogilised lugemised 0, lob füüsilised lugemised 0, lob eelnevad lugemised 0.Meil on naljakas olukord: päringu plaan ei ole oluliselt halvem kui eelmine, kuid tegelik loogiliste lugemiste arv on peaaegu kaks korda suurem kui täis skaneerimise korral. Lahendus on – kui olemasolevast indeksist teha koosindeks ja teiseks väljaks lisada kaupade koguväärtus, saame jälle 165 loogilist lugemist:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal ON Sales.SalesOrderHeader(OrderDate, SubTotal);
Seda näidisesarja võiks jätkata veel kaua, kuid kaks peamist mõtet, mida soovin siin edastada, on:
- Iga uue kriteeriumi või sorteerimisjärjekorra lisamine otsingupäringusse võib oluliselt mõjutada selle täitmise kiirus.
- Kuid kui me peame lugema vaid osa andmeid, mitte kõiki otsingutingimustele vastavaid tulemusi, on palju viise sellise päringu optimeerimiseks.
Nüüd liigume teise päringu juurde, millest rääkisime alguses – see, mis arvutab kriteeriumidele vastavate kirjete arvu. Võtame sama näite – otsides tellimusi, mis maksavad üle 100 dollari:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Olemasoleva koosindeksi korral saame:
Tabel 'SalesOrderHeader'. Skaneerimise arv 1, loogilised lugemised 698, füüsilised lugemised 0, eelnevad lugemised 0, lob loogilised lugemised 0, lob füüsilised lugemised 0, lob eelnevad lugemised 0.Kuna päring läbib kogu indeksi, pole see üllatav, kuna väli SubTotal ei ole esimesel kohal, seega ei saa päring seda kasutada. Probleem lahendatakse, lisades veel ühe indeksi väljale SubTotal, mis annab lõppkokkuvõttes juba vaid 48 loogilist lugemist.
Saame tuua veel mõned näited kandahtud arvude arvutamisest, kuid põhimõte jääb samaks: andmete osade saamine ja koguarvu arvutamine on kaks põhimõtteliselt erinevat päringut, ja igaühel neist on oma meetmed optimeerimiseks. Üldiselt ei saa leida indeksite kombinatsiooni, mis töötaks mõlema päringu jaoks ühtemoodi hästi.
Seega on üks olulisemaid nõudeid, mida tuleks sellise otsingulahenduse väljatöötamisel täpsustada, tõesti see, kas ettevõttele on oluline näha leitud objektide koguarvu. Tihti ei ole see oluline. Ja navigeerimine konkreetsete leheküljenumbrite vahel on minu arvates väga kitsas lahendusala, kuna enamik lehekülgede vahetusega seotud stsenaariume näeb välja nagu "mine järgmisele lehele".
Päevastamise variant #2
Oletame, et kasutajad ei pea teadma leitud objektide koguarvu. Proovime otsingulehte lihtsustada:
Sisuliselt muutus ainult see, et ei ole võimalik liikuda konkreetsete leheküljenumbrite vahel ning nüüd ei pea selle tabeli jaoks teadma, kui palju lehti kokku võiks olla. Kuid tekib küsimus - kuidas tabel saab teada, kas järgmine leht on andmeid (et õigesti kuvada linki „Järgmine“)?
Vastus on väga lihtne: andmebaasist saab lugeda ühe salvestuse rohkem, kui on vaja kuvamiseks, ja see "ülemäärane" salvestus näitab, kas järgmine hulk andmeid on olemas. Seega, et saada üks andmeleht, tuleb teha vaid üks päring, mis oluliselt parandab jõudlust ja lihtsustab sellise funktsiooni toetamist. Mul oli praktikas juhtum, kus koguarvust loobumine kiirendas tulemuste väljundit 4-5 korda.
Selle lähenemise jaoks on mitu liidese varianti: nupud „tagasi“ ja „edasi“, nagu ülaltoodud näites, nupp „lae rohkem“, mis lihtsalt lisab uusi andmeid kuvatud tulemustele, „lõputu kerimine“, mis töötab nagu „lae rohkem“, kuid signaal järgmise hulga saamiseks on kasutaja kerimine kõigi kuvatud tulemusteni lõpuni. Milline visualiseerimislahendus iganes, andmete valimise põhimõte jääb samaks.
Päevastamise rakendamise nüansid
Kõik ülaltoodud päringute näited kasutavad lähenemist „offset + count“, kus päringus on näidatud, milliselt realt alustada ja kui palju ridu tagasi saada. Esiteks vaatame, kuidas oleks parem edastada parameetreid sel juhul. Praktikas olen kohtunud mitme meetodiga:
- Tellige lehe järjekorranumber (pageIndex), lehe suurus (pageSize).
- Tellige esimese salvestuse järjekorranumber, mida tuleb tagasi anda (startIndex), maksimaalne salvestuste arv tulemuses (count).
- Tellige esimese salvestuse järjekorranumber, mida tuleb tagasi anda (startIndex), viimase salvestuse järjekorranumber, mida tuleb tagasi anda (endIndex).
Esmapilgul võib tunduda, et see on nii elementaarne, et vahet ei ole. Kuid see ei ole tõsi - kõige mugavam ja universaalsem variant on teine (startIndex, count). Sellel on mitu põhjust:
- Eelneva '+1' salvestuse lugemise lähenemise puhul on esimene variant (pageIndex ja pageSize) äärmiselt ebamugav. Näiteks soovime kuvada lehe kaupa 50 salvestust. Vastavalt ülaltoodud algoritmile tuleb lugeda ühe salvestuse rohkem, kui vajalik. Kui see „+1“ ei ole serverisse sisse ehitatud, peame esimeselt lehelt küsima salvestusi 1 kuni 51, teiselt - 51 kuni 101 jne. Kui lehe suurus on 51 ja suurendame pageIndex, naaseb teine leht 52 kuni 102 jne. Seega on esimese variandi ainus viis järgmise lehe nuppude korrektseks rakendamiseks, et serverisse sisse ehitada „üleliigsete“ ridade lugemine, mis oleks väga ebaselge nüanss.
- Kolmas variant on täielikult mõtetu, kuna enamikus andmebaasides tuleb päringute täitmiseks ikkagi edastada salvestuste arv, mitte viimase salvestuse indeks. Olgu startIndex'i lahutamine endIndex'ist elementaarne aritmeetiline toiming, kuid see on siin üleliigne.
Nüüd tuleks kirjeldada päevastamise rakendamise puudusi lähenemisega "offset + count":
- Iga järgmise lehe saamine on kulukam ja aeglasem kui eelmine, kuna andmebaas peab ikkagi läbima kõik salvestused "algusest" vastavalt otsingukriteeriumidele ja sortimisele ning seejärel peatuma õigel fraktsioonil.
- Kaugel kõik andmebaasid võivad seda lähenemist toetada.
Alternatiivid on olemas, kuid need pole ka ideaalsed. Esimese sellise lähenemise nimi on "keyset paging" või "seek method", mis tähendab järgmist: pärast osade saamist saab salvestada väärtused väljadest viimases salvestuses lehel ja seejärel kasutada neid järgmise osa saamiseks. Näiteks sooritasime sellise päringu:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ja viimasest kirjest saime tellimuse kuupäeva '2014-06-29'. Seega, et saada järgmine leht, võime proovida järgmist:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Probleem on selles, et OrderDate ei ole unikaalne väli ja eespool toodud tingimus jätab suure tõenäosusega paljusid vajalikke ridu vahele. Selle päringu selgitamiseks tuleb tingimusse lisada unikaalne väli (eeldame, et 75074 on esimese osa viimane primaarvõtme väärtus):
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
See variant töötab korralikult, kuid üldiselt on seda keeruline optimeerida, kuna tingimus sisaldab OR operaatorit. Kui OrderDate väärtuse tõustes primaarvõtme väärtus kasvab, siis saab tingimust lihtsustada, jättes alles ainult SalesOrderID filtri. Kuid kui primaarvõtme ja tulemuste sortimise järgi pole ranget korrelatsiooni, siis enamikes andmebaasides ei õnnestu sellest OR-ist vältida. Ainus tuttav erand, millest ma tean, on PostgreSQL, kus tupla võrdlemist toetatakse täielikult ja eespool antud tingimus võib olla kirjutatud kujul "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Kui on olemas koosnevaid võtmeid nende kahe välja põhjal, peaks sarnane päring olema piisavalt lihtne.
Teine alternatiivne lähenemine võib olla näiteks või — kui päring tagastab andmete kõrval erilise identifikaatori, millega saab järgmise andmepartii. Kui see identifikaator on piiramatu elueaga (nagu Comsos DB-s), on see suurepärane viise lehtede vaheliste järjestikuste üleminekute rakendamiseks (eelnevalt mainitud variant #2). Selle võimalikud puudused: seda ei toetata kaugeltki kõigis andmebaasides; järgmise partii saamiseks saadud identifikaatoril võib olla piiratud eluaeg, mis üldiselt ei sobi kasutajaga suhtlemiseks (nagu näiteks ElasticSearch scroll API).
Keeruline filtreerimine
Teeme ülesande veelgi keerulisemaks. Oletame, et on tekkinud nõudmine rakendada nn faceted search-i, mis on internetipoodidest hästi tuntud. Eelnevad näited, mis põhinevad tellimustabelil, ei ole sel juhul eriti näitlikud, seega liigume AdvantureWorks andmebaasi toote tabelisse:
Mis on faceted search'i idee? Selles seisneb, et iga filtri elemendi jaoks kuvatakse arvu, mis vastab sellele kriteeriumile. võttes arvesse kõiki teisi kategooriaid valitud filtreid..
Näiteks, kui valime antud näites kategooria Jalgrattad ja värv Must, kuvab tabel ainult musta värvi jalgrattaid, kuid samas:
- Iga 'Kategooriad' grupi kriteeriumi jaoks kuvatakse arvu musta värvi tooteid, mis kuuluvad sellesse kategooriasse.
- Iga 'Värvid' grupi kriteeriumi jaoks kuvatakse musta värvi jalgrataste arvu.
Siin on näide selliste tingimuste jaoks tulemuste kuvamisest:
Kui lisaks valida kategooria 'Riided', kuvab tabel ka musta värvi riideid, mis on laos. Mustade toodete arvu sektsioonis 'Värv' arvutatakse vastavalt uutele tingimustele ümber, kuid 'Kategooriad' sektsioonis ei muutu enam midagi... Loodan, et neid näiteid piisab, et mõista faceted search'i tuttavat tööprotsessi.
Nüüd kujutame ette, kuidas seda saab rakendada relatsioonilises andmebaasis. Iga kriteeriumigrupp, nagu Kategooria ja Värv, vajab eraldi päringut:
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 --Jalgrattad
GROUP BY Color
ORDER BY COUNT(1) DESC
Mis on valesti selle lahendusega? Väga lihtsalt — see ei skaala hästi. Iga filtrisegment vajab eraldi päringut koguste arvestamiseks ja need päringud ei ole eriti kerged. Internetipoodides võib mõnes kategoorias olla ka mitu tosinat filtrisegmenti, mis võib osutuda tõsiseks probleemiks jõudlusele.
Tavaliselt pakuvad mulle pärast neid väiteid mõned lahendused, nimelt:
- Kombineerida kõik koguste arvutused üheks päringuks. Tehniliselt on see võimalik sõna UNION abil, kuid see ei aita eriti jõudluse osas — andmebaasil tuleb ikkagi "nullist" iga fragment käivitada.
- Külastusmahtude vahemälestamine. Seda pakutakse mulle praktiliselt igal korral, kui kirjeldan probleemi. Probleemi keerukus on see, et see on tavaliselt sisuliselt ebareaalne. Oletame, et meil on 10 „fassaadi“, milles on 5 väärtust. See on väga „modest“ olukord võrreldes sellega, mida võib näha samades veebipoodides. Ühe fassaadi elemendi valik mõjutab 9 teist, teisisõnu, iga kriteeriumi kombinatsiooni jaoks võivad kogused olla erinevad. Kokku on meie näites 50 kriteeriast, mida kasutaja saab valida, seega on võimalikke kombinatsioone 250. Sellise andmemahtu täitmiseks ei piisa ei mälu ega ajast. Siinkohal võib vastu väita, et mitte kõik kombinatsioonid pole reaalsed ja kasutaja valib harva rohkem kui 5-10 kriteeriat. Jah, saab teha laiskade laadimist ja koguste vahemälestamist ainult neile, mis kunagi on valitud, kuid mida rohkem valikuvõimalusi on, seda vähem efektiivne on selline vahemälu ning seda silmatorkavamad on probleemid vastamisaegadega (eriti kui andmestik muutub regulaarselt).
Õnneks on sellisel ülesandel ammu olemas piisavalt tõhusad lahendused, mis töötavad ennustatavasti suurte andmemahtudega. Igaühe jaoks on mõistlik eraldada fassaadide ümberarvutamine ja tulemuste lehe saamine kahe paralleelse päringu kaudu serverisse ning korraldada kasutajaliides selliselt, et fassaadide andmete laadimine „ei sega” otsingutulemuste kuvamist.
- Kutsuda fassaadide täielikku ümberarvutamist nii harva kui võimalik. Näiteks mitte ümber arvutada kõike igal otsingukriteeriumide muutmisel, vaid leida üldine tulemuste arv, mis vastab praegustele tingimustele ning pakkuda kasutajale nende kuvamist: „leiti 1425 kirjet, kas näidata?“ Kasutaja võib kas jätkata otsingutingimuste muutmist või vajutada nuppu „näita“. Ainult teisel juhul teostatakse kõik päringud tulemuste saamiseks ja fassaadide koguste ümberarvutamiseks. Sellega seoses, nagu on lihtne märgata, tuleb tegeleda päringuga üldarvu saamiseks ja selle optimeerimiseks. Seda lähenemist võib leida paljusid väikeseid veebipoodidest. Ilmselgelt ei ole see lahendus selle probleemi jaoks, kuid lihtsates olukordades võib see olla hea kompromiss.
- Kasutada otsingumootorit tulemuste otsimiseks ja fassaadide arvu lugemiseks, nagu Solr, ElasticSearch, Sphinx ja teised. Kõik need on loodud „fassaadide“ genereerimiseks ja teevad seda piisavalt efektiivselt, kasutades pöördindeksi. Kuidas otsingusüsteemid töötavad, miks need on sellistes olukordades efektiivsemad kui üldotstarbelised andmebaasid, millised on parimad praktikad ja varjatud probleemid — see on eraldi artikli teema. Siin tahan rõhutada, et otsingumootor ei saa olla peamise andmehoidla asendaja, seda kasutatakse täiendava vahendina: kõik peamises andmebaasis toimuvad muutused, mis mõjutavad otsingut, sünkroniseeritakse otsinguindeksisse; otsingumehhanism suhtleb tavaliselt ainult otsingumootoriga ega pöördu peamise andmebaasi poole. Üks kõige olulisemaid aspekte siin on, kuidas korraldada see sünkroniseerimine usaldusväärselt. Kõik sõltub nõuetest „reaktsiooniaja” osas. Kui aeg peamise andmebaasi muudatuse ja selle „peegeldumise“ vahel otsingus ei ole kriitiline, saab teha teenuse, mis korra paari minuti jooksul otsib hiljuti muudetud kirjeid ja indekseerib need. Kui soovitakse minimaalset võimalikku reaktsiooniaega, saab rakendada midagi sellist nagu uuenduste saatmiseks otsinguteenusesse.
Järeldused
- Serveripoolse leheküljenduse teostamine on tõsine keerukus, ja selle rakendamine on mõistlik ainult kiiresti kasvavate või lihtsalt suurte andmehulkade puhul. Kuidas hinnata, mis on „suur” või „kiiresti kasvav” — absoluutset täpset retsept ei ole, kuid ma järgiksin järgmist lähenemist:
- Kui andmekogu täielik saamine, võttes arvesse serveri aega ja andmete edastamist võrgus, mahub normaalselt jõudluse nõuetesse — serveripoolse leheküljenduse rakendamiseks pole mõtet.
- Võib juhtuda, et lähitulevikus pole jõudlusprobleeme, kuna andmeid on vähe, kuid andmekogu kasvab pidevalt. Kui mõni andmehulk ei pruugi tulevikus enam rahuldada eelmist punkti — võiks serveripoolset leheküljendust kohe kavandada.
- Kui äri poolelt pole ranged nõuded üldise tulemuste arvu kuvamiseks või leheküljenumbrite kuvamiseks, ja teie süsteemis ei ole otsingumootorit — on parem neid punkte mitte rakendada ja kaaluda varianti #2.
- Kui on selged nõuded faceted search'ile, siis on teil kaks võimalust, et mitte ohverdada tootlikkust:
- Ärge arvutage kõiki arve iga otsingukriteeriumi muutmise korral.
- Kasutage otsingumootoreid nagu Solr, ElasticSearch, Sphinx ja teised. Kuid on oluline mõista, et see ei saa asendada põhitooriku andmebaasi ning seda tuleks kasutada lisandina peamise salvestuse jaoks otsingute ülesannete lahendamiseks.
- Samuti on mõistlik jagada otsingutulemuste lehe saamine ja arvude arvestamine kaheks paralleelseks päringuks. Arvude arvestamine võib võtta rohkem aega kui tulemuste saamine, samas kui tulemused on kasutaja jaoks olulisemad.
- Kui kasutate SQL-andmebaasi otsimiseks, siis peab kõik koodimuudatused, mis puudutavad seda osa, olema hoolikalt testitud tootlikkuse osas vastava andmemahtu (mis ületab ''elava'' andmebaasi mahu) suhtes. Samuti on soovitatav jälgida päringute täitmise aega kõigis andmebaasi instantsides, eriti - ''elavas'' andmebaasis. Isegi kui arendusetapis kõik päringute plaanid olid head, võib andmemahtu kasvades olukord oluliselt muutuda.
Allikas: habr.com
