Ein af dæmigerðum atburðarásum í öllum forritum sem við þekkjum er að leita að gögnum samkvæmt ákveðnum forsendum og birta þau á auðlestri formi. Það geta líka verið fleiri valkostir fyrir flokkun, flokkun og síðuskipun. Verkefnið er fræðilega léttvægt, en þegar þeir leysa það gera margir forritarar fjölda mistaka, sem síðar valda því að framleiðni verður fyrir skaða. Við skulum reyna að íhuga ýmsa möguleika til að leysa þetta vandamál og móta tillögur til að velja árangursríkustu framkvæmdina.
Símboðsvalkostur #1
Einfaldasti kosturinn sem mér dettur í hug er að birta leitarniðurstöður síðu fyrir síðu á sínu klassískasta formi.
Segjum að forritið þitt noti venslagagnagrunn. Í þessu tilviki, til að birta upplýsingar á þessu formi, þarftu að keyra tvær SQL fyrirspurnir:
- Fáðu línur fyrir núverandi síðu.
- Reiknaðu heildarfjölda lína sem samsvara leitarskilyrðunum - þetta er nauðsynlegt til að birta síður.
Við skulum skoða fyrstu fyrirspurnina með því að nota MS SQL prófunargagnagrunn sem dæmi fyrir 2016 netþjón. Í þessu skyni munum við nota Sales.SalesOrderHeader töfluna:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Fyrirspurnin hér að ofan mun skila fyrstu 50 pöntunum af listanum, raðað eftir lækkandi dagsetningu samlagningar, með öðrum orðum, 50 nýjustu pöntunum.
Það keyrir hratt á prófunargrunninum, en við skulum skoða framkvæmdaáætlunina og I/O tölfræð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.Þú getur fengið I/O tölfræði fyrir hverja fyrirspurn með því að keyra SET STATISTICS IO ON skipunina í fyrirspurnartímanum.
Eins og þú sérð af framkvæmdaáætluninni er auðlindafrekasti kosturinn að raða öllum línum upprunatöflunnar eftir dagsetningu sem bætt er við. Og vandamálið er að því fleiri línur sem birtast í töflunni, því „erfiðari“ verður flokkunin. Í reynd ætti að forðast slíkar aðstæður, svo við skulum bæta vísitölu við dagsetningu viðbótarinnar og sjá hvort auðlindanotkun hefur breyst:
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.
Það hefur greinilega batnað miklu. En eru öll vandamál leyst? Við skulum breyta fyrirspurninni til að leita að pöntunum þar sem heildarkostnaður vöru fer yfir $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.Við erum með fyndnar aðstæður: fyrirspurnaáætlunin er ekki mikið verri en sú fyrri, en raunverulegur fjöldi rökréttra lestra er næstum tvöfalt meiri en með fullri töfluskönnun. Það er leið út - ef við gerum samsetta vísitölu úr vísitölu sem þegar er til og bætum við heildarverði vöru sem annað reitinn, þá fáum við aftur 165 rökrétt lesefni:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Þessi röð dæma má halda áfram í langan tíma, en tvær meginhugsanir sem ég vil koma á framfæri hér eru:
- Að bæta hvaða nýrri viðmiðun sem er eða flokkunarröð við leitarfyrirspurn getur haft veruleg áhrif á hraða leitarfyrirspurnar.
- En ef við þurfum að draga aðeins hluta af gögnunum frá, en ekki allar niðurstöðurnar sem passa við leitarskilyrðin, þá eru margar leiðir til að fínstilla slíka fyrirspurn.
Nú skulum við halda áfram að annarri fyrirspurninni sem nefnd var í upphafi - sú sem telur fjölda færslur sem uppfylla leitarviðmiðið. Tökum sama dæmi - að leita að pöntunum sem eru meira en $100:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Miðað við samsetta vísitöluna sem tilgreind er hér að ofan, fáum við:
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.Sú staðreynd að fyrirspurnin fer í gegnum alla vísitöluna kemur ekki á óvart, þar sem SubTotal reiturinn er ekki í fyrsta sæti, þannig að fyrirspurnin getur ekki notað hann. Vandamálið er leyst með því að bæta við annarri vísitölu á SubTotal reitinn og þar af leiðandi gefur hún aðeins 48 rökréttar lestur.
Þú getur nefnt nokkur fleiri dæmi um beiðnir um talningu magns, en kjarninn er sá sami: að taka á móti gögnum og telja heildarupphæðina eru tvær í grundvallaratriðum ólíkar beiðnir, og hver krefst eigin ráðstafana til hagræðingar. Almennt séð muntu ekki geta fundið samsetningu af vísitölum sem virkar jafn vel fyrir báðar fyrirspurnirnar.
Í samræmi við það er ein af mikilvægu kröfunum sem ætti að skýra við þróun slíkrar leitarlausnar hvort það sé virkilega mikilvægt fyrir fyrirtæki að sjá heildarfjölda hluta sem finnast. Það kemur oft fyrir að nr. Og flakk eftir sérstökum blaðsíðunúmerum, að mínu mati, er lausn með mjög þröngt umfang, þar sem flestar síðuaðstæður líta út eins og „farðu á næstu síðu“.
Símboðsvalkostur #2
Gerum ráð fyrir að notendum sé sama um að vita heildarfjölda hluta sem fundust. Við skulum reyna að einfalda leitarsíðuna:
Reyndar er það eina sem hefur breyst að það er engin leið að fletta í ákveðin blaðsíðunúmer og nú þarf þessi tafla ekki að vita hversu margar þær geta verið til að birta hana. En spurningin vaknar - hvernig veit taflan hvort það eru gögn fyrir næstu síðu (til að birta „Næsta“ hlekkinn rétt)?
Svarið er mjög einfalt: þú getur lesið úr gagnagrunninum eina skrá í viðbót en þarf til að birta, og tilvist þessarar „viðbótar“ færslu mun sýna hvort það er næsti hluti. Þannig þarftu aðeins að keyra eina beiðni til að fá eina síðu af gögnum, sem bætir verulega afköst og gerir það auðveldara að styðja slíka virkni. Í starfi mínu kom upp tilvik þar sem neitað var að telja heildarfjölda skráa flýtti fyrir afhendingu niðurstaðna um 4-5 sinnum.
Það eru nokkrir notendaviðmótsvalkostir fyrir þessa nálgun: „til baka“ og „áfram“ skipanir, eins og í dæminu hér að ofan, „hlaða meira“ hnappur, sem einfaldlega bætir nýjum hluta við birtar niðurstöður, „óendanlega fletta“, sem virkar á meginreglunni um "hlaða meira" ", en merkið til að fá næsta hluta er fyrir notandann að fletta öllum birtum niðurstöðum til enda. Hver sem sjónræn lausnin er, er meginreglan um sýnatöku gagna áfram sú sama.
Litbrigði útfærslu síðuskipta
Öll fyrirspurnadæmin sem gefin eru hér að ofan nota „offset + count“ nálgunina, þegar fyrirspurnin sjálf tilgreinir í hvaða röð niðurstöðulínurnar og hversu mörgum línum þarf að skila. Í fyrsta lagi skulum við skoða hvernig best er að skipuleggja breytuflutning í þessu tilfelli. Í reynd hef ég rekist á nokkrar aðferðir:
- Raðnúmer umbeðinnar síðu (pageIndex), síðustærð (pageSize).
- Raðnúmer fyrstu færslu sem á að skila (startIndex), hámarksfjöldi skráa í niðurstöðunni (talning).
- Raðarnúmer fyrstu færslu sem á að skila (startIndex), raðnúmer síðustu færslu sem á að skila (endIndex).
Við fyrstu sýn kann að virðast að þetta sé svo grunnatriði að það sé enginn munur. En þetta er ekki svo - þægilegasti og alhliða valkosturinn er annar (startIndex, telja). Það eru nokkrar ástæður fyrir þessu:
- Fyrir prófarkalestursaðferðina fyrir +1 færslu sem gefin er upp hér að ofan er fyrsti kosturinn með pageIndex og pageSize afar óþægilegur. Til dæmis viljum við birta 50 færslur á síðu. Samkvæmt ofangreindu reikniritinu þarftu að lesa eina skrá í viðbót en nauðsynlegt er. Ef þetta „+1“ er ekki útfært á þjóninum, kemur í ljós að fyrir fyrstu síðu verðum við að biðja um skrár frá 1 til 51, fyrir þá seinni - frá 51 til 101 osfrv. Ef þú tilgreinir síðustærð 51 og eykur pageIndex, þá mun önnur síða fara aftur úr 52 í 102, o.s.frv. Samkvæmt því, í fyrsta valkostinum, er eina leiðin til að útfæra hnapp á réttan hátt til að fara á næstu síðu að láta þjóninn prófarkalesa „auka“ línuna, sem mun vera mjög óbeinn blæbrigði.
- Þriðji valkosturinn er alls ekki skynsamlegur, þar sem til að keyra fyrirspurnir í flestum gagnagrunnum þarftu samt að standast talningu frekar en vísitölu síðustu skráningar. Að draga startIndex frá endIndex getur verið einföld reikningsaðgerð, en það er óþarfi hér.
Nú ættum við að lýsa göllunum við að innleiða síðuskipti í gegnum „jöfnun + magn“:
- Að sækja hverja síðari síðu verður dýrari og hægari en sú fyrri, vegna þess að gagnagrunnurinn þarf samt að fara í gegnum allar færslur „frá upphafi“ samkvæmt leitar- og flokkunarskilyrðum og stoppa síðan við viðkomandi brot.
- Ekki geta allir DBMS-kerfi stutt þessa nálgun.
Það eru valkostir, en þeir eru líka ófullkomnir. Fyrsta af þessum aðferðum er kölluð „keyset paging“ eða „leitaraðferð“ og er sem hér segir: eftir að hafa fengið hluta geturðu munað svæðisgildin í síðustu færslunni á síðunni og síðan notað þau til að fá næsta hluta. Til dæmis keyrðum við eftirfarandi fyrirspurn:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Og í síðustu færslu fengum við gildi pöntunardagsins „2014-06-29“. Síðan til að fá næstu síðu geturðu reynt að gera þetta:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Vandamálið er að OrderDate er ekki einstakur reitur og ástandið sem tilgreint er hér að ofan mun líklega missa af mörgum nauðsynlegum línum. Til að bæta ótvíræðni við þessa fyrirspurn þarftu að bæta einstökum reit við skilyrðið (gerum ráð fyrir að 75074 sé síðasta gildi frumlykils frá fyrsta hluta):
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
Þessi valkostur mun virka rétt, en almennt verður erfitt að fínstilla þar sem ástandið inniheldur OR rekstraraðila. Ef gildi aðallykilsins eykst þegar OrderDate hækkar, þá er hægt að einfalda ástandið með því að skilja aðeins eftir síu eftir SalesOrderID. En ef það er engin ströng fylgni á milli gilda aðallykilsins og reitsins sem niðurstaðan er flokkuð eftir er ekki hægt að forðast þessa OR í flestum DBMS. Undantekning sem mér er kunnugt um er PostgreSQL, sem styður fyllilega samanburð á túllum og hægt er að skrifa ofangreint skilyrði sem "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Miðað við samsettan lykil með þessum tveimur sviðum ætti fyrirspurn eins og þessi að vera frekar auðveld.
Önnur valaðferð er til dæmis að finna í eða — þegar beiðni, auk gagna, skilar sérstöku auðkenni sem þú getur fengið næsta hluta gagna með. Ef þetta auðkenni hefur ótakmarkaðan líftíma (eins og í Comsos DB), þá er þetta frábær leið til að útfæra síðuskipti með raðskiptum milli síðna (valkostur #2 nefndur hér að ofan). Mögulegir ókostir þess: það er ekki stutt í öllum DBMS; næstkomandi auðkenni sem myndast getur haft takmarkaðan líftíma, sem er almennt ekki hentugur til að útfæra notendasamskipti (eins og ElasticSearch scroll API).
Flókin síun
Við skulum flækja verkefnið enn frekar. Segjum sem svo að það sé krafa um að innleiða svokallaða hliðarleit, sem allir þekkja mjög vel frá netverslunum. Dæmin hér að ofan, byggð á pöntunartöflunni, eru ekki mjög lýsandi í þessu tilfelli, svo við skulum skipta yfir í vörutöfluna úr AdventureWorks gagnagrunninum:
Hver er hugmyndin á bak við hliðarleit? Staðreyndin er sú að fyrir hvern síuþátt er sýndur fjöldi skráa sem uppfylla þessa viðmiðun að teknu tilliti til sía sem valdar eru í öllum öðrum flokkum.
Til dæmis, ef við veljum Hjólaflokkinn og litinn Svartur í þessu dæmi, mun taflan aðeins sýna svört hjól, en:
- Fyrir hverja viðmiðun í flokknum Flokkar verður fjöldi vara úr þeim flokki sýndur svartur.
- Fyrir hverja viðmiðun í hópnum „Litir“ verður sýndur fjöldi reiðhjóla í þessum lit.
Hér er dæmi um útkomuna fyrir slík skilyrði:
Ef þú hakar líka við flokkinn „Föt“ mun taflan einnig sýna svört föt sem eru til á lager. Fjöldi svartra vara í „Litur“ hlutanum verður einnig endurreiknaður í samræmi við nýju skilyrðin, aðeins í „Flokkum“ hlutanum mun ekkert breytast... Ég vona að þessi dæmi séu nóg til að skilja venjulega fletileitaralgrímið.
Nú skulum við ímynda okkur hvernig hægt er að útfæra þetta á tengslagrundvelli. Hver hópur viðmiða, eins og Flokkur og Litur, mun krefjast sérstakrar fyrirspurnar:
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
Hvað er að þessari lausn? Það er mjög einfalt - það mælist ekki vel. Hver síuhluti krefst sérstakrar fyrirspurnar til að reikna út magn og þessar fyrirspurnir eru ekki þær auðveldustu. Í netverslunum geta sumir flokkar verið með nokkra tugi síuhluta, sem getur verið alvarlegt frammistöðuvandamál.
Venjulega eftir þessar yfirlýsingar býðst mér nokkrar lausnir, þ.e.
- Sameina allar magntölur í eina fyrirspurn. Tæknilega séð er þetta mögulegt með því að nota UNION lykilorðið, en það mun ekki hjálpa mikið af frammistöðu - gagnagrunnurinn verður samt að keyra hvert brot frá grunni.
- Skyndiminni magn. Þetta er bent mér á næstum í hvert skipti sem ég lýsi vandamáli. Fyrirvarinn er sá að þetta er almennt ómögulegt. Segjum að við höfum 10 "hliðar", sem hver um sig hefur 5 gildi. Þetta er mjög „hógvær“ staða miðað við það sem sést í sömu netverslunum. Val á einum þætti hefur áhrif á magn í 9 öðrum, með öðrum orðum, fyrir hverja samsetningu af viðmiðum getur magnið verið mismunandi. Í dæminu okkar eru samtals 50 viðmið sem notandinn getur valið, þess vegna verða mögulegar samsetningar 250. Það er ekki nóg minni eða tími til að fylla slíka gagnafjölda. Hér er hægt að mótmæla og segja að ekki séu allar samsetningar raunverulegar og notandinn velur sjaldan meira en 5-10 viðmið. Já, það er hægt að gera letihleðslu og skyndiminni aðeins það sem hefur verið valið, en því meira sem valið er, því minna skilvirkt verður slíkt skyndiminni og því meira áberandi verða viðbragðstímavandamálin (sérstaklega ef gagnasett breytast reglulega).
Sem betur fer hefur slíkt vandamál lengi haft mjög árangursríkar lausnir sem virka fyrirsjáanlega á miklu magni gagna. Fyrir einhvern af þessum valkostum er skynsamlegt að skipta endurútreikningi hliða og móttöku niðurstöðusíðunnar í tvö samhliða símtöl á netþjóninn og skipuleggja notendaviðmótið á þann hátt að hleðsla gagna eftir hliðum „truflar ekki“ birtingu leitarniðurstöður.
- Kallaðu algjöran endurútreikning á „hliðum“ eins sjaldan og mögulegt er. Til dæmis, ekki endurreikna allt í hvert skipti sem leitarskilyrðin breytast, heldur finndu í staðinn heildarfjölda niðurstaðna sem passa við núverandi skilyrði og biðja notandann um að sýna þær - "1425 færslur fundnar, sýna?" Notandinn getur annað hvort haldið áfram að breyta leitarskilmálum eða smellt á „sýna“ hnappinn. Aðeins í öðru tilvikinu verða allar beiðnir um að fá niðurstöður og endurreikna magn á öllum „hliðum“ framkvæmdar. Í þessu tilfelli, eins og þú getur auðveldlega séð, verður þú að takast á við beiðni til að fá heildarfjölda niðurstaðna og hagræðingu þess. Þessa aðferð er að finna í mörgum litlum netverslunum. Augljóslega er þetta ekki lækning fyrir þetta vandamál, en í einföldum tilfellum getur það verið góð málamiðlun.
- Notaðu leitarvélar til að finna niðurstöður og telja hliðar eins og Solr, ElasticSearch, Sphinx og fleiri. Öll þau eru hönnuð til að byggja upp „hliðar“ og gera þetta á nokkuð skilvirkan hátt vegna öfugs vísitölunnar. Hvernig leitarvélar virka, hvers vegna þær eru í slíkum tilfellum skilvirkari en almennar gagnagrunnar, hvaða starfshættir og gildrur eru til staðar - þetta er efni fyrir sérstaka grein. Hér vil ég vekja athygli á því að leitarvélin getur ekki komið í staðinn fyrir aðalgagnageymsluna, hún er notuð sem viðbót: allar breytingar á aðalgagnagrunninum sem skipta máli fyrir leit eru samstilltar inn í leitarskrána; Leitarvélin hefur yfirleitt aðeins samskipti við leitarvélina og hefur ekki aðgang að aðalgagnagrunninum. Eitt mikilvægasta atriðið hér er hvernig á að skipuleggja þessa samstillingu á áreiðanlegan hátt. Það veltur allt á kröfum um „viðbragðstíma“. Ef tíminn milli breytinga á aðalgagnagrunni og „birting“ hans í leitinni er ekki mikilvægur geturðu búið til þjónustu sem leitar að nýlega breyttum gögnum á nokkurra mínútna fresti og skráir þær. Ef þú vilt sem stystan viðbragðstíma geturðu útfært eitthvað eins og til að senda uppfærslur til leitarþjónustunnar.
Niðurstöður
- Innleiðing á síðuboði á netþjóni er verulegur fylgikvilli og er aðeins skynsamlegt fyrir ört vaxandi eða einfaldlega stór gagnasöfn. Það er engin nákvæmlega nákvæm uppskrift að því hvernig á að meta "stórt" eða "hraðvaxandi", en ég myndi fylgja þessari aðferð:
- Ef móttaka heildarsöfnunar gagna, að teknu tilliti til netþjónstíma og netsendingar, uppfyllir venjulega kröfur um frammistöðu, þá er ekkert vit í að innleiða síðuskipti á netþjóninum.
- Það getur komið upp sú staða að ekki sé búist við afkomuvandamálum á næstunni, þar sem lítið er um gögn, en gagnasöfnunin er stöðugt að aukast. Ef eitthvert safn af gögnum í framtíðinni gæti ekki lengur uppfyllt fyrra atriðið, þá er betra að byrja að hringja strax.
- Ef það er engin ströng krafa af hálfu fyrirtækisins um að birta heildarfjölda niðurstaðna eða birta blaðsíðunúmer, og kerfið þitt er ekki með leitarvél, er betra að útfæra þessi atriði ekki og íhuga valkost #2.
- Ef það er skýr krafa um flókaleit, hefur þú tvo valkosti án þess að fórna frammistöðu:
- Ekki endurreikna allt magn í hvert sinn sem leitarskilyrðin breytast.
- Notaðu leitarvélar eins og Solr, ElasticSearch, Sphinx og fleiri. En það ætti að skilja að það getur ekki komið í stað aðalgagnagrunnsins og ætti að nota það sem viðbót við aðalgeymsluna til að leysa leitarvandamál.
- Einnig, þegar um er að ræða flettarleit, er skynsamlegt að skipta sókn leitarniðurstöðusíðunnar og talningu í tvær samhliða beiðnir. Það getur tekið lengri tíma að telja magn en að fá niðurstöður á meðan niðurstöðurnar eru mikilvægari fyrir notandann.
- Ef þú ert að nota SQL gagnagrunn til að leita, ætti allar kóðabreytingar sem tengjast þessum hluta að vera vel prófaðar með tilliti til frammistöðu á viðeigandi magni af gögnum (meira magni í lifandi gagnagrunni). Einnig er ráðlegt að nota eftirlit með framkvæmdartíma fyrirspurna á öllum tilfellum gagnagrunnsins, og sérstaklega á „lifandi“. Jafnvel þótt allt væri í lagi með fyrirspurnaáætlanir á þróunarstigi, eftir því sem gagnamagn eykst, getur ástandið breyst verulega.
Heimild: www.habr.com