Üks tüüpilisi stsenaariume kõigis meile tuttavates rakendustes on andmete otsimine teatud kriteeriumide järgi ja nende kuvamine lihtsalt loetaval kujul. Sorteerimiseks, rühmitamiseks ja lehitsemiseks võib olla ka lisavõimalusi. Ülesanne on teoreetiliselt triviaalne, kuid selle lahendamisel teevad paljud arendajad hulga vigu, mis hiljem tootlikkust kannatavad. Proovime kaaluda selle probleemi lahendamise erinevaid võimalusi ja sõnastada soovitused kõige tõhusama teostuse valimiseks.
Lehitsemisvalik nr 1
Lihtsaim võimalus, mis meelde tuleb, on otsingutulemuste lehekülgede kaupa kuvamine selle kõige klassikalisemal kujul.
Oletame, et teie rakendus kasutab relatsiooniandmebaasi. Sellisel juhul peate sellel kujul teabe kuvamiseks käivitama kaks SQL-päringut:
- Hangi praeguse lehe read.
- Arvutage otsingukriteeriumitele vastavate ridade koguarv - see on vajalik lehtede kuvamiseks.
Vaatame esimest päringut, kasutades näitena test-MS SQL andmebaasi 2016. aasta 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 tagastab loendist esimesed 50 tellimust, mis on sorteeritud kahaneva lisamiskuupäeva järgi ehk teisisõnu 50 viimast tellimust.
See töötab testbaasis kiiresti, kuid vaatame täitmisplaani ja I/O statistikat:
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.Iga päringu I/O statistika saate hankida, kui käivitate päringu käitusajal käsu SET STATISTICS IO ON.
Nagu teostusplaanist näha, on kõige ressursimahukam variant sortida kõik lähtetabeli read lisamiskuupäeva järgi. Ja probleem on selles, et mida rohkem ridu tabelisse ilmub, seda raskem on sorteerimine. Praktikas tuleks selliseid olukordi vältida, seega lisame lisamise kuupäevale indeksi ja vaatame, kas ressursitarbimine on muutunud:
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.
Ilmselgelt on asi palju paremaks läinud. Kuid kas kõik probleemid on lahendatud? Muudame päringut, et otsida tellimusi, mille kauba kogumaksumus ületab 100 dollarit:
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.Meil on naljakas olukord: päringuplaan pole eelmisest palju halvem, kuid tegelik loogiliste lugemiste arv on peaaegu kaks korda suurem kui täistabeli skaneerimisel. Väljapääs on olemas - kui teeme juba olemasolevast indeksist liitindeksi ja lisame teise väljana kauba koguhinna, saame jällegi 165 loogilist lugemist:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Seda näidete seeriat võib veel kaua jätkata, kuid kaks peamist mõtet, mida ma siin väljendada tahan, on järgmised:
- Mis tahes uue kriteeriumi või sortimisjärjestuse lisamine otsingupäringule võib oluliselt mõjutada otsingupäringu kiirust.
- Kuid kui peame lahutama ainult osa andmetest, mitte kõiki otsinguterminitele vastavaid tulemusi, on sellise päringu optimeerimiseks palju võimalusi.
Liigume nüüd kohe alguses mainitud teise päringu juurde – selle juurde, mis loeb üles otsingukriteeriumile vastavate kirjete arvu. Võtame sama näite – rohkem kui 100 dollari suuruste tellimuste otsimine:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Arvestades ülaltoodud liitindeksit, saame:
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.Asjaolu, et päring läbib kogu indeksi, ei ole üllatav, kuna väli SubTotal ei ole esimesel positsioonil, seega ei saa päring seda kasutada. Probleem lahendatakse, lisades väljale SubTotal veel ühe indeksi ja selle tulemusena annab see ainult 48 loogilist lugemist.
Võite tuua veel mõned näited koguste loendamise taotlustest, kuid olemus jääb samaks: andmete saamine ja kogusumma kokkulugemine on kaks põhimõtteliselt erinevat taotlust, ja igaüks nõuab optimeerimiseks oma meetmeid. Üldiselt ei leia te indeksite kombinatsiooni, mis töötaks mõlema päringu puhul võrdselt hästi.
Sellest tulenevalt on üks oluline nõue, mida sellise otsingulahenduse väljatöötamisel selgitada, kas ettevõtte jaoks on tõesti oluline näha leitud objektide koguarvu. Tihti juhtub, et ei. Ja konkreetsete leheküljenumbrite järgi navigeerimine on minu arvates väga kitsa ulatusega lahendus, kuna enamik lehekülgede otsimise stsenaariume näeb välja nagu "mine järgmisele lehele".
Lehitsemisvalik nr 2
Oletame, et kasutajaid ei huvita leitud objektide koguarvu teadmine. Proovime otsingulehte lihtsustada:
Tegelikult on muutunud ainult see, et konkreetsete lehekülgede numbrite juurde pole võimalik navigeerida ja nüüd ei pea see tabel selle kuvamiseks teadma, kui palju neid võib olla. Kuid tekib küsimus - kuidas tabel teab, kas järgmise lehe jaoks on andmeid (et linki "Järgmine" õigesti kuvada)?
Vastus on väga lihtne: saate andmebaasist lugeda ühe kirje rohkem, kui kuvamiseks vaja on, ja selle "lisakirje" olemasolu näitab, kas on järgmine osa. Nii tuleb ühe lehekülje andmete saamiseks käivitada vaid üks päring, mis parandab oluliselt jõudlust ja muudab sellise funktsionaalsuse toetamise lihtsamaks. Minu praktikas oli juhtum, kus rekordite koguarvu kokkulugemisest keeldumine kiirendas tulemuste edastamist 4-5 korda.
Selle lähenemisviisi jaoks on mitu kasutajaliidese valikut: "tagasi" ja "edasi" käsud, nagu ülaltoodud näites, nupp "laadi rohkem", mis lihtsalt lisab kuvatavatele tulemustele uue osa, "lõpmatu kerimine", mis töötab põhimõttel "laadige rohkem", kuid signaal järgmise osa saamiseks on see, et kasutaja kerib kõik kuvatavad tulemused lõpuni. Olgu visuaalne lahendus milline tahes, andmete valimi võtmise põhimõte jääb samaks.
Saatelehe rakendamise nüansid
Kõik ülaltoodud päringunäited kasutavad "nihe + loendus" lähenemist, kui päring ise määrab, millises järjekorras on tulemuse read ja mitu rida tuleb tagastada. Kõigepealt vaatame, kuidas sel juhul parameetrite edastamist kõige paremini korraldada. Praktikas olen kohanud mitmeid meetodeid:
- Taotletud lehe seerianumber (pageIndex), lehe suurus (pageSize).
- Esimese tagastatava kirje seerianumber (startIndex), maksimaalne kirjete arv tulemuses (count).
- Esimese tagastatava kirje järjekorranumber (startIndex), viimase tagastatava kirje järjenumber (endIndex).
Esmapilgul võib tunduda, et see on nii elementaarne, et vahet pole. Kuid see pole nii - kõige mugavam ja universaalsem variant on teine (startIndex, loendamine). Sellel on mitu põhjust:
- Ülaltoodud +1 kirje korrektuuri puhul on esimene valik pageIndexi ja pageSize'iga äärmiselt ebamugav. Näiteks tahame ühel lehel kuvada 50 postitust. Ülaltoodud algoritmi järgi peate lugema ühe kirje rohkem kui vaja. Kui seda "+1" serveris ei rakendata, selgub, et esimese lehe jaoks peame taotlema kirjeid vahemikus 1 kuni 51, teise jaoks - 51 kuni 101 jne. Kui määrate lehe suuruseks 51 ja suurendate leheindeksit, naaseb teine leht 52-lt 102-le jne. Seega on esimeses valikus ainus viis järgmisele lehele mineku nupu õigesti rakendamiseks lasta serveril "lisa" rida korrektuuri lugeda, mis on väga kaudne nüanss.
- Kolmas valik pole üldse mõttekas, kuna enamikus andmebaasides päringute käitamiseks peate siiski edastama loenduse, mitte viimase kirje indeksi. StartIndexi lahutamine endIndexist võib olla lihtne aritmeetiline tehe, kuid see on siin üleliigne.
Nüüd peaksime kirjeldama „nihe + kogus” kaudu lehitsemise rakendamise puudusi:
- Iga järgmise lehe allalaadimine on eelmisest kulukam ja aeglasem, kuna andmebaas peab ikkagi otsingu- ja sortimiskriteeriumide järgi läbima kõik kirjed "algusest peale" ja seejärel peatuma soovitud fragmendil.
- Mitte kõik DBMS-id ei saa seda lähenemisviisi toetada.
Alternatiive on, aga need on ka ebatäiuslikud. Esimest neist lähenemisviisidest nimetatakse "võtmekomplekti otsimiseks" või "otsingumeetodiks" ja see on järgmine: pärast osa vastuvõtmist saate meelde jätta lehe viimase kirje välja väärtused ja kasutada neid seejärel hankimiseks. järgmine osa. Näiteks käivitasime järgmise päringu:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ja viimasel kirjel saime tellimuse kuupäeva väärtuseks ‘2014-06-29’. Järgmise lehe saamiseks võite proovida teha 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 Tellimuse kuupäev ei ole unikaalne väli ja ülaltoodud tingimus jätab tõenäoliselt palju nõutavaid ridu vahele. Sellele päringule ühemõttelisuse lisamiseks peate tingimusele lisama ainulaadse välja (oletame, et 75074 on primaarvõtme viimane väärtus esimesest osast):
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 valik töötab õigesti, kuid üldiselt on seda raske optimeerida, kuna tingimus sisaldab operaatorit VÕI. Kui Tellimuse kuupäeva kasvades primaarvõtme väärtus suureneb, saab tingimust lihtsustada, jättes ainult SalesOrderID filtri. Kuid kui primaarvõtme väärtuste ja tulemuse sortimise välja vahel pole ranget korrelatsiooni, ei saa seda VÕI enamikus DBMS-ides vältida. Erandiks, millest olen teadlik, on PostgreSQL, mis toetab täielikult korteeži võrdlemist ja ülaltoodud tingimuse saab kirjutada kujul "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Arvestades nende kahe väljaga liitvõtit, peaks selline päring olema üsna lihtne.
Teise alternatiivse lähenemisviisi võib leida näiteks või — kui päring tagastab lisaks andmetele ka spetsiaalse identifikaatori, millega saate hankida järgmise osa andmeid. Kui sellel identifikaatoril on piiramatu kasutusiga (nagu Comsose DB-s), on see suurepärane viis lehekülgede vahel järjestikuse üleminekuga lehitsemiseks (ülalpool mainitud valik nr 2). Selle võimalikud puudused: seda ei toetata kõigis DBMS-ides; saadud järgmise tüki identifikaatori eluiga võib olla piiratud, mis üldiselt ei sobi kasutaja interaktsiooni (nt ElasticSearchi kerimis-API) rakendamiseks.
Kompleksne filtreerimine
Teeme ülesande veelgi keerulisemaks. Oletame, et on nõue rakendada nn tahutud otsing, mis on kõigile veebipoodidest väga tuttav. Ülaltoodud näited tellimuste tabeli põhjal ei ole antud juhul kuigi illustreerivad, seega lülitume AdventureWorksi andmebaasist tootetabelile:
Mis on tahutud otsingu idee? Fakt on see, et iga filtrielemendi jaoks kuvatakse sellele kriteeriumile vastavate kirjete arv võttes arvesse kõigis teistes kategooriates valitud filtreid.
Näiteks kui valime selles näites kategooria Jalgrattad ja värvi Must, kuvatakse tabelis ainult mustad jalgrattad, kuid:
- Iga kriteeriumi puhul kategoorias Kategooriad kuvatakse selle kategooria toodete arv mustana.
- Grupi “Värvid” iga kriteeriumi puhul näidatakse seda värvi jalgrataste arvu.
Siin on näide selliste tingimuste tulemuste väljundist:
Kui märgite ka kategooria “Rõivad”, on tabelis näha ka laos olevaid musta värvi riideid. Mustade toodete arv jaotises “Värv” arvutatakse samuti ümber vastavalt uutele tingimustele, ainult “Kategooriad” rubriigis ei muutu midagi... Loodan, et nendest näidetest piisab, et mõista tavalist tahutud otsingu algoritmi.
Nüüd kujutame ette, kuidas seda saab relatsioonipõhiselt rakendada. Iga kriteeriumirühm, nagu kategooria ja värv, nõuab 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 --Bikes
GROUP BY Color
ORDER BY COUNT(1) DESC
Mis sellel lahendusel viga on? See on väga lihtne – see ei skaleeru hästi. Iga filtri osa nõuab koguste arvutamiseks eraldi päringut ja need päringud pole just kõige lihtsamad. Veebipoodides võib mõnel kategoorial olla mitukümmend filtrisektsiooni, mis võib olla tõsine jõudlusprobleem.
Tavaliselt pakutakse pärast neid avaldusi mulle mõningaid lahendusi, nimelt:
- Ühendage kõik koguste loendid üheks päringuks. Tehniliselt on see võimalik märksõna UNION abil, kuid see ei aita jõudlust kuigi palju – andmebaas peab ikkagi käivitama kõik fragmendid nullist.
- Vahemälu kogused. Seda soovitatakse mulle peaaegu iga kord, kui probleemi kirjeldan. Hoiatus on see, et see on üldiselt võimatu. Oletame, et meil on 10 tahku, millest igaühel on 5 väärtust. See on väga “tagasihoidlik” olukord võrreldes samades veebipoodides nähtuga. Ühe tahu elemendi valik mõjutab 9 muu elemendi suurusi, teisisõnu, iga kriteeriumikombinatsiooni puhul võivad kogused olla erinevad. Meie näites on kokku 50 kriteeriumi, mida kasutaja saab valida, seega on võimalikke kombinatsioone 250. Sellise andmemassiivi täitmiseks pole piisavalt mälu ega aega. Siin saate vastu vaielda ja öelda, et kõik kombinatsioonid pole reaalsed ja kasutaja valib harva rohkem kui 5-10 kriteeriumi. Jah, on võimalik teha laiska laadimist ja vahemällu salvestada ainult seda kogust, mis on kunagi valitud, kuid mida rohkem valikuid on, seda vähem tõhus on selline vahemälu ja seda märgatavamad on reageerimisaja probleemid (eriti kui andmekogum muutub regulaarselt).
Õnneks on sellisel probleemil pikka aega olnud üsna tõhusaid lahendusi, mis töötavad prognoositavalt suurte andmemahtude puhul. Kõigi nende valikute puhul on mõttekas jagada tahkude ümberarvutamine ja tulemuste lehe vastuvõtmine kaheks paralleelseks serverikõneks ning korraldada kasutajaliides nii, et andmete laadimine tahkude kaupa "ei sega" kuvamist. Otsingu tulemused.
- Helistage "tahkude" täielikuks ümberarvutamiseks nii harva kui võimalik. Näiteks ärge arvutage kõike uuesti iga kord, kui otsingukriteeriumid muutuvad, vaid leidke praegustele tingimustele vastavate tulemuste koguarv ja paluge kasutajal neid kuvada – "Leiti 1425 kirjet, näita?" Kasutaja saab jätkata otsinguterminite muutmist või klõpsata nupul „Näita”. Alles teisel juhul täidetakse kõik tulemuste saamise ja koguste ümberarvutamise taotlused kõigil "tahtadel". Sel juhul, nagu näete hõlpsasti, peate tegelema tulemuste koguarvu ja selle optimeerimise taotlusega. Seda meetodit võib leida paljudest väikestest veebipoodidest. Ilmselgelt pole see selle probleemi imerohi, kuid lihtsatel juhtudel võib see olla hea kompromiss.
- Kasutage tulemuste leidmiseks ja tahkude loendamiseks otsingumootoreid, nagu Solr, ElasticSearch, Sphinx ja teised. Kõik need on loodud "tahkude" ehitamiseks ja teevad seda ümberpööratud indeksi tõttu üsna tõhusalt. Kuidas otsingumootorid töötavad, miks on need sellistel juhtudel tõhusamad kui üldotstarbelised andmebaasid, millised tavad ja lõksud seal on - see on eraldi artikli teema. Siinkohal juhin tähelepanu asjaolule, et otsingumootor ei saa asendada põhiandmesalvestust, seda kasutatakse täiendusena: kõik otsingu jaoks olulised muudatused põhiandmebaasis sünkroniseeritakse otsinguindeksisse; Otsingumootor suhtleb tavaliselt ainult otsingumootoriga ega pääse ligi põhiandmebaasi. Siin on üks olulisemaid punkte, kuidas seda sünkroonimist usaldusväärselt korraldada. Kõik sõltub "reaktsiooniaja" nõuetest. Kui põhiandmebaasi muudatuse ja selle otsingus ilmumise vaheline aeg ei ole kriitiline, saate luua teenuse, mis otsib iga paari minuti järel hiljuti muudetud kirjeid ja indekseerib need. Kui soovite võimalikult lühikest reageerimisaega, võite rakendada midagi sarnast otsinguteenusele uuenduste saatmiseks.
Järeldused
- Serveripoolse lehekülge otsimise rakendamine on märkimisväärne komplikatsioon ja see on mõttekas ainult kiiresti kasvavate või lihtsalt suurte andmehulkade puhul. Absoluutselt täpset retsepti, kuidas hinnata “suurt” või “kiirekasvulist”, pole olemas, kuid järgiksin seda lähenemist:
- Kui täieliku andmekogu vastuvõtmine, võttes arvesse serveri aega ja võrguedastust, vastab normaalselt jõudlusnõuetele, pole serveri poolel otsingut mõtet rakendada.
- Võib tekkida olukord, kus lähitulevikus pole jõudlusprobleeme oodata, kuna andmeid on vähe, kuid andmete kogumine kasvab pidevalt. Kui mõni andmekogum tulevikus ei pruugi enam eelmist punkti rahuldada, on parem alustada lehitsemist kohe.
- Kui ettevõttel ei ole ranget nõuet kuvada tulemuste koguarv või kuvada leheküljenumbreid ja teie süsteemil pole otsingumootorit, on parem neid punkte mitte rakendada ja kaaluda võimalust nr 2.
- Kui lihvitud otsingu jaoks on selge nõue, on teil toimivust ohverdamata kaks võimalust.
- Ärge arvutage kõiki koguseid iga kord, kui otsingukriteeriumid muutuvad.
- Kasutage otsingumootoreid nagu Solr, ElasticSearch, Sphinx jt. Kuid tuleb mõista, et see ei saa asendada põhiandmebaasi ja seda tuleks kasutada otsinguprobleemide lahendamiseks põhimälu lisana.
- Samuti on tahutud otsingu puhul mõttekas jagada otsingutulemuste lehe otsimine ja loendamine kaheks paralleelseks päringuks. Koguste loendamine võib võtta kauem aega kui tulemuste saamine, samas kui tulemused on kasutajale olulisemad.
- Kui kasutate otsimiseks SQL-andmebaasi, tuleks iga selle osaga seotud koodimuudatuse toimivust sobival hulgal andmemahul (üle reaalajas andmebaasi mahtu) hästi testida. Samuti on soovitatav kasutada päringu täitmisaja jälgimist andmebaasi kõigil eksemplaridel ja eriti reaalajas. Isegi kui arendusetapis oli päringuplaanidega kõik korras, võib andmemahu kasvades olukord märgatavalt muutuda.
Allikas: www.habr.com