Meklēšanas rezultātu izvades un veiktspējas problēmas

Viens no tipiskiem scenārijiem visās mums pazīstamajās lietojumprogrammās ir datu meklēšana pēc noteiktiem kritērijiem un to attēlošana viegli lasāmā formā. Var būt arī papildu kārtošanas, grupēšanas un lapošanas opcijas. Uzdevums teorētiski ir triviāls, taču, to risinot, daudzi izstrādātāji pieļauj vairākas kļūdas, kas vēlāk liek ciest produktivitātei. Mēģināsim apsvērt dažādas iespējas šīs problēmas risināšanai un formulēsim ieteikumus efektīvākās ieviešanas izvēlei.

Peidžeru opcija Nr. 1

Vienkāršākā iespēja, kas nāk prātā, ir meklēšanas rezultātu rādīšana pa lappusēm klasiskākajā formā.

Pieņemsim, ka jūsu lietojumprogramma izmanto relāciju datu bāzi. Šajā gadījumā, lai parādītu informāciju šajā veidlapā, jums būs jāpalaiž divi SQL vaicājumi:

• Iegūt rindas pašreizējai lapai.
• Aprēķiniet kopējo rindu skaitu, kas atbilst meklēšanas kritērijiem - tas ir nepieciešams, lai parādītu lapas.

Apskatīsim pirmo vaicājumu, kā piemēru izmantojot testa MS SQL datu bāzi AdventureWorks 2016. gada serverim. Šim nolūkam izmantosim tabulu Sales.SalesOrderHeader:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Iepriekš minētais vaicājums atgriezīs pirmos 50 pasūtījumus no saraksta, kas sakārtoti pēc pievienošanas datuma dilstošā secībā, citiem vārdiem sakot, 50 jaunākie pasūtījumi.

Testēšanas bāzē tas darbojas ātri, taču apskatīsim izpildes plānu un I/O statistiku:

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.

Katra vaicājuma I/O statistiku var iegūt, vaicājuma izpildlaikā palaižot komandu SET STATISTICS IO ON.

Kā redzams izpildes plānā, resursietilpīgākā iespēja ir kārtot visas avota tabulas rindas pēc pievienošanas datuma. Un problēma ir tā, ka, jo vairāk rindu parādīsies tabulā, jo “grūtāka” būs šķirošana. Praksē no šādām situācijām vajadzētu izvairīties, tāpēc pievienosim indeksu pievienošanas datumam un paskatīsimies, vai resursu patēriņš ir mainījies:

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.

Acīmredzot tas ir kļuvis daudz labāks. Bet vai visas problēmas ir atrisinātas? Mainīsim vaicājumu, lai meklētu pasūtījumus, kuru kopējās preču izmaksas pārsniedz 100 ASV dolārus:

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.

Mums ir smieklīga situācija: vaicājumu plāns nav daudz sliktāks par iepriekšējo, bet faktiskais loģisko nolasījumu skaits ir gandrīz divreiz lielāks nekā ar pilnu tabulu skenēšanu. Ir izeja - ja izveidosim saliktu indeksu no jau esoša indeksa un kā otro lauku pievienosim kopējo preču cenu, atkal iegūsim 165 loģiskus nolasījumus:

CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);

Šo piemēru sēriju var turpināt vēl ilgi, bet divas galvenās domas, kuras es vēlos izteikt šeit, ir:

• Jebkura jauna kritērija vai kārtošanas secības pievienošana meklēšanas vaicājumam var būtiski ietekmēt meklēšanas vaicājuma ātrumu.
• Bet, ja mums ir jāatņem tikai daļa datu, nevis visi rezultāti, kas atbilst meklēšanas vienumiem, ir daudz veidu, kā optimizēt šādu vaicājumu.

Tagad pāriesim pie otrā vaicājuma, kas minēts pašā sākumā – tā, kas uzskaita meklēšanas kritērijam atbilstošo ierakstu skaitu. Ņemsim to pašu piemēru — meklējot pasūtījumus, kuru vērtība pārsniedz 100 ASV dolārus:

SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100

Ņemot vērā iepriekš norādīto salikto indeksu, mēs iegūstam:

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.

Tas, ka vaicājums iet cauri visam indeksam, nav pārsteidzošs, jo lauks SubTotal nav pirmajā pozīcijā, tāpēc vaicājums to nevar izmantot. Problēma tiek atrisināta, pievienojot vēl vienu indeksu laukā SubTotal, un rezultātā tas dod tikai 48 loģiskus nolasījumus.

Varat sniegt vēl dažus daudzumu skaitīšanas pieprasījumu piemērus, taču būtība paliek nemainīga: Datu daļas saņemšana un kopējās summas saskaitīšana ir divi principiāli atšķirīgi pieprasījumi, un katram ir nepieciešami savi optimizācijas pasākumi. Kopumā jūs nevarēsit atrast indeksu kombināciju, kas vienlīdz labi darbojas abiem vaicājumiem.

Attiecīgi viena no būtiskām prasībām, kas būtu jāprecizē, izstrādājot šādu meklēšanas risinājumu, ir, vai uzņēmumam tiešām ir svarīgi redzēt kopējo atrasto objektu skaitu. Bieži gadās, ka nē. Un navigācija pēc konkrētiem lappušu numuriem, manuprāt, ir risinājums ar ļoti šauru darbības jomu, jo vairums peidžeru scenāriju izskatās kā “pāriet uz nākamo lapu”.

Peidžeru opcija Nr. 2

Pieņemsim, ka lietotājiem nav svarīgi zināt kopējo atrasto objektu skaitu. Mēģināsim vienkāršot meklēšanas lapu:

Faktiski ir mainījies tikai tas, ka nav iespējams pārvietoties uz konkrētiem lapu numuriem, un tagad šai tabulai nav jāzina, cik to var būt, lai to parādītu. Bet rodas jautājums - kā tabula zina, vai ir dati par nākamo lapu (lai pareizi parādītu saiti "Nākamais")?

Atbilde ir ļoti vienkārša: jūs varat nolasīt no datu bāzes par vienu ierakstu vairāk, nekā nepieciešams attēlošanai, un šī “papildu” ieraksta klātbūtne parādīs, vai ir nākamā daļa. Tādā veidā jums ir nepieciešams izpildīt tikai vienu pieprasījumu, lai iegūtu vienu datu lapu, kas ievērojami uzlabo veiktspēju un atvieglo šādas funkcionalitātes atbalstu. Manā praksē bija gadījums, kad atteikšanās skaitīt kopējo ierakstu skaitu paātrina rezultātu piegādi 4-5 reizes.

Šai pieejai ir vairākas lietotāja saskarnes opcijas: komandas "atpakaļ" un "uz priekšu", tāpat kā iepriekš minētajā piemērā, poga "ielādēt vairāk", kas vienkārši pievieno parādītajiem rezultātiem jaunu daļu, "bezgalīga ritināšana", kas darbojas. pēc principa "ielādēt vairāk", bet signāls, lai iegūtu nākamo daļu, ir lietotājam, lai ritinātu visus parādītos rezultātus līdz beigām. Lai kāds būtu vizuālais risinājums, datu izlases princips paliek nemainīgs.

Peidžeru ieviešanas nianses

Visos iepriekš sniegtajos vaicājumu piemēros tiek izmantota pieeja “nobīde + skaitīšana”, kad pats vaicājums norāda, kādā secībā ir jāatgriež rezultātu rindas un cik rindu ir jāatgriež. Vispirms apskatīsim, kā šajā gadījumā vislabāk organizēt parametru nodošanu. Praksē esmu sastapies ar vairākām metodēm:

• Pieprasītās lapas sērijas numurs (pageIndex), lapas izmērs (pageSize).
• Pirmā atgriežamā ieraksta sērijas numurs (startIndex), maksimālais ierakstu skaits rezultātā (skaits).
• Pirmā atgriežamā ieraksta kārtas numurs (startIndex), pēdējā atgriežamā ieraksta kārtas numurs (endIndex).

No pirmā acu uzmetiena var šķist, ka tas ir tik elementāri, ka nav nekādas atšķirības. Bet tas tā nav - ērtākā un universālākā iespēja ir otrā (startIndex, count). Tam ir vairāki iemesli:

• Iepriekš aprakstītajai +1 ieraksta korektūras pieejai pirmā opcija ar pageIndex un pageSize ir ārkārtīgi neērta. Piemēram, mēs vēlamies parādīt 50 ziņas vienā lapā. Saskaņā ar iepriekš minēto algoritmu jums ir jāizlasa par vienu ierakstu vairāk nekā nepieciešams. Ja šis “+1” nav ieviests serverī, izrādās, ka pirmajai lapai mums ir jāpieprasa ieraksti no 1 līdz 51, otrajai - no 51 līdz 101 utt. Ja norādāt lapas izmēru 51 un palielināsit pageIndex, tad otrā lapa atgriezīsies no 52 uz 102 utt. Attiecīgi pirmajā variantā vienīgais veids, kā pareizi ieviest pogu, lai pārietu uz nākamo lapu, ir likt serverim pārbaudīt “papildu” rindiņu, kas būs ļoti netieša nianse.
• Trešajai opcijai vispār nav jēgas, jo, lai izpildītu vaicājumus lielākajā daļā datu bāzu, jums joprojām ir jānokārto skaitīšana, nevis pēdējā ieraksta indekss. StartIndex atņemšana no endIndex var būt vienkārša aritmētiska darbība, taču šeit tā ir lieka.

Tagad mums jāapraksta peidžeru ieviešanas trūkumi, izmantojot “nobīde + daudzums”:

• Katras nākamās lapas izgūšana būs dārgāka un lēnāka nekā iepriekšējā, jo datu bāzei vēl būs jāiziet cauri visi ieraksti “no sākuma” pēc meklēšanas un kārtošanas kritērijiem un tad jāapstājas pie vēlamā fragmenta.
• Ne visas DBVS var atbalstīt šo pieeju.

Ir alternatīvas, taču tās ir arī nepilnīgas. Pirmo no šīm pieejām sauc par “atslēgkopu peidžeru” vai “meklēšanas metodi”, un tā ir šāda: pēc daļas saņemšanas varat atcerēties lauku vērtības lapas pēdējā ierakstā un pēc tam izmantot tās, lai iegūtu nākamā porcija. Piemēram, mēs izpildījām šādu vaicājumu:

SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Un pēdējā ierakstā mēs saņēmām pasūtījuma datuma vērtību ‘2014-06-29’. Pēc tam, lai iegūtu nākamo lapu, varat mēģināt rīkoties šādi:

SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY

Problēma ir tāda, ka OrderDate nav unikāls lauks, un iepriekš norādītajā nosacījumā, iespējams, netiks rādīts daudz obligāto rindu. Lai šim vaicājumam pievienotu nepārprotamību, nosacījumam jāpievieno unikāls lauks (pieņemsim, ka 75074 ir primārās atslēgas pēdējā vērtība no pirmās daļas):

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

Šī opcija darbosies pareizi, taču kopumā to būs grūti optimizēt, jo nosacījums satur operatoru VAI. Ja primārās atslēgas vērtība palielinās, palielinoties OrderDate, nosacījumu var vienkāršot, atstājot tikai filtru pēc SalesOrderID. Bet, ja nav stingras korelācijas starp primārās atslēgas vērtībām un lauku, pēc kura tiek kārtots rezultāts, lielākajā daļā DBVS nevar izvairīties no šī VAI. Izņēmums, par kuru es zinu, ir PostgreSQL, kas pilnībā atbalsta korešu salīdzināšanu, un iepriekš minēto nosacījumu var rakstīt kā "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Ņemot vērā salikto atslēgu ar šiem diviem laukiem, šādam vaicājumam vajadzētu būt diezgan vienkāršam.

Otru alternatīvu pieeju var atrast, piemēram, ElasticSearch ritināšanas API vai Cosmos DB — ja pieprasījums papildus datiem atgriež īpašu identifikatoru, ar kuru jūs varat iegūt nākamo datu daļu. Ja šim identifikatoram ir neierobežots kalpošanas laiks (kā Comsos DB), tad tas ir lielisks veids, kā ieviest peidžeru ar secīgu pāreju starp lapām (iepriekš minētā opcija Nr. 2). Tās iespējamie trūkumi: tas netiek atbalstīts visās DBVS; iegūtajam nākamā gabala identifikatoram var būt ierobežots kalpošanas laiks, kas parasti nav piemērots lietotāja mijiedarbības ieviešanai (piemēram, ElasticSearch ritināšanas API).

Sarežģīta filtrēšana

Sarežģīsim uzdevumu vēl vairāk. Pieņemsim, ka ir prasība ieviest tā saukto fasetēto meklēšanu, kas ikvienam ir ļoti pazīstama no interneta veikaliem. Iepriekš minētie piemēri, kuru pamatā ir pasūtījumu tabula, šajā gadījumā nav īpaši ilustratīvi, tāpēc pāriesim uz tabulu Produkts no AdventureWorks datu bāzes:

Kāda ir šķautņu meklēšanas ideja? Fakts ir tāds, ka katram filtra elementam tiek parādīts šim kritērijam atbilstošo ierakstu skaits ņemot vērā visās pārējās kategorijās atlasītos filtrus.

Piemēram, ja šajā piemērā atlasām kategoriju Velosipēdi un krāsu Melnu, tabulā tiks rādīti tikai melni velosipēdi, bet:

• Katram kritērijam grupā Kategorijas preču skaits no šīs kategorijas tiks parādīts melnā krāsā.
• Katram grupas “Krāsas” kritērijam tiks parādīts šīs krāsas velosipēdu skaits.

Šeit ir šādu apstākļu rezultātu izvades piemērs:

Ja atzīmēsiet arī kategoriju “Apģērbs”, tabulā būs redzamas arī melnas drēbes, kas ir noliktavā. Arī melno preču skaits sadaļā “Krāsa” tiks pārrēķināts pēc jaunajiem nosacījumiem, tikai sadaļā “Kategorijas” nekas nemainīsies... Ceru, ka ar šiem piemēriem pietiks, lai saprastu ierasto faceted meklēšanas algoritmu.

Tagad iedomāsimies, kā to var īstenot uz attiecību pamata. Katrai kritēriju grupai, piemēram, kategorijai un krāsai, būs nepieciešams atsevišķs vaicājums:

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 ir nepareizi ar šo risinājumu? Tas ir ļoti vienkārši — tas nav labi mērogots. Katrai filtra sadaļai ir nepieciešams atsevišķs vaicājums, lai aprēķinātu daudzumus, un šie vaicājumi nav no tiem vienkāršākajiem. Tiešsaistes veikalos dažās kategorijās var būt vairāki desmiti filtru sadaļu, kas var būt nopietna veiktspējas problēma.

Parasti pēc šiem apgalvojumiem man tiek piedāvāti daži risinājumi, proti:

• Apvienojiet visus daudzumu uzskaites vienā vaicājumā. Tehniski tas ir iespējams, izmantojot atslēgvārdu UNION, taču tas daudz nepalīdzēs veiktspējai - datu bāzei joprojām būs jāizpilda katrs fragments no nulles.
• Kešatmiņas daudzumi. Tas man tiek ieteikts gandrīz katru reizi, kad aprakstu problēmu. Brīdinājums ir tāds, ka tas parasti nav iespējams. Pieņemsim, ka mums ir 10 "šķautnes", no kurām katrai ir 5 vērtības. Šī ir ļoti “pieticīga” situācija, salīdzinot ar to, ko var redzēt tajos pašos interneta veikalos. Viena aspekta elementa izvēle ietekmē daudzumus 9 citos, citiem vārdiem sakot, katrai kritēriju kombinācijai daudzumi var būt atšķirīgi. Mūsu piemērā kopā ir 50 kritēriji, kurus lietotājs var atlasīt, tātad iespējamās kombinācijas būs 250. Nav pietiekami daudz atmiņas vai laika, lai aizpildītu šādu datu masīvu. Šeit var iebilst un teikt, ka ne visas kombinācijas ir īstas un lietotājs reti izvēlas vairāk par 5-10 kritērijiem. Jā, ir iespējams veikt slinku ielādi un kešatmiņā saglabāt tikai kādreiz atlasīto daudzumu, taču, jo vairāk atlases būs, jo mazāk efektīva būs šāda kešatmiņa un pamanāmākas būs reakcijas laika problēmas (īpaši, ja datu kopa regulāri mainās).

Par laimi, šādai problēmai jau sen ir bijuši diezgan efektīvi risinājumi, kas paredzami darbojas ar lielu datu apjomu. Jebkurai no šīm opcijām ir jēga sadalīt aspektu pārrēķinu un rezultātu lapas saņemšanu divos paralēlos izsaukumos uz serveri un sakārtot lietotāja interfeisu tā, lai datu ielāde pa aspektiem “netraucē” Meklēšanas rezultāti.

• Pēc iespējas retāk izsauciet pilnīgu “šķautņu” pārrēķinu. Piemēram, nepārrēķiniet visu katru reizi, kad mainās meklēšanas kritēriji, bet gan atrodiet kopējo rezultātu skaitu, kas atbilst pašreizējiem nosacījumiem, un aiciniet lietotāju tos parādīt - "Atrasti 1425 ieraksti, rādīt?" Lietotājs var vai nu turpināt mainīt meklēšanas vienumus, vai arī noklikšķināt uz pogas “rādīt”. Tikai otrajā gadījumā tiks izpildīti visi pieprasījumi iegūt rezultātus un pārrēķināt daudzumus visās “šķautnēs”. Šajā gadījumā, kā jūs viegli varat redzēt, jums būs jārisina pieprasījums iegūt kopējo rezultātu skaitu un tā optimizāciju. Šo metodi var atrast daudzos mazos tiešsaistes veikalos. Acīmredzot šī nav panaceja šai problēmai, taču vienkāršos gadījumos tas var būt labs kompromiss.
• Izmantojiet meklētājprogrammas, lai atrastu rezultātus un saskaitītu aspektus, piemēram, Solr, ElasticSearch, Sphinx un citus. Visi no tiem ir paredzēti, lai izveidotu "šķautnes", un to dara diezgan efektīvi, pateicoties apgrieztajam indeksam. Kā darbojas meklētājprogrammas, kāpēc šādos gadījumos tās ir efektīvākas par vispārējas nozīmes datubāzēm, kāda ir prakse un kļūmes – tā ir atsevišķa raksta tēma. Šeit vēlos vērst jūsu uzmanību uz to, ka meklētājprogramma nevar aizstāt galveno datu krātuvi, tā tiek izmantota kā papildinājums: visas izmaiņas galvenajā datubāzē, kas ir būtiskas meklēšanai, tiek sinhronizētas meklēšanas rādītājā; Meklētājprogramma parasti mijiedarbojas tikai ar meklētājprogrammu un nepiekļūst galvenajai datubāzei. Viens no svarīgākajiem jautājumiem šeit ir tas, kā uzticami organizēt šo sinhronizāciju. Tas viss ir atkarīgs no “reakcijas laika” prasībām. Ja laiks starp izmaiņām galvenajā datubāzē un to “izpaušanos” meklēšanā nav kritisks, varat izveidot pakalpojumu, kas ik pēc dažām minūtēm meklē nesen mainītos ierakstus un indeksē tos. Ja vēlaties pēc iespējas īsāku reakcijas laiku, varat ieviest kaut ko līdzīgu darījumu izsūtne lai nosūtītu atjauninājumus meklēšanas pakalpojumam.

Atzinumi

1. Servera puses peidžeru ieviešana ir ievērojama komplikācija, un tā ir jēga tikai strauji augošām vai vienkārši lielām datu kopām. Nav absolūti precīzas receptes, kā novērtēt “lielo” vai “ātri augošu”, taču es izvēlētos šādu pieeju:
   • Ja pilnīgas datu kolekcijas saņemšana, ņemot vērā servera laiku un tīkla pārraidi, parasti atbilst veiktspējas prasībām, nav jēgas ieviest peidžeru servera pusē.
   • Var rasties situācija, ka tuvākajā nākotnē nav gaidāmas veiktspējas problēmas, jo datu ir maz, bet datu apkopojums nepārtraukti pieaug. Ja kāda datu kopa nākotnē vairs neatbilst iepriekšējam punktam, labāk ir nekavējoties sākt lapošanu.
2. Ja uzņēmumam nav stingras prasības parādīt kopējo rezultātu skaitu vai parādīt lappušu numurus, un jūsu sistēmā nav meklētājprogrammas, labāk neieviest šos punktus un apsvērt iespēju #2.
3. Ja ir skaidra prasība pēc slīpētas meklēšanas, jums ir divas iespējas, nezaudējot veiktspēju.
   • Nepārrēķiniet visus daudzumus katru reizi, kad mainās meklēšanas kritēriji.
   • Izmantojiet meklētājprogrammas, piemēram, Solr, ElasticSearch, Sphinx un citas. Bet jāsaprot, ka tas nevar aizstāt galveno datubāzi, un tas ir jāizmanto kā papildinājums galvenajai krātuvei meklēšanas problēmu risināšanai.
4. Turklāt slīpētas meklēšanas gadījumā ir lietderīgi sadalīt meklēšanas rezultātu lapas izguvi un skaitīšanu divos paralēlos pieprasījumos. Daudzumu skaitīšana var aizņemt ilgāku laiku nekā rezultātu iegūšana, savukārt rezultāti lietotājam ir svarīgāki.
5. Ja meklēšanai izmantojat SQL datu bāzi, visas ar šo daļu saistītās koda izmaiņas ir rūpīgi jāpārbauda, ​​vai tās darbojas atbilstošā datu daudzumā (pārsniedzot reāllaika datubāzes apjomu). Ieteicams arī izmantot vaicājuma izpildes laika uzraudzību visos datu bāzes gadījumos, un jo īpaši “dzīvajā”. Pat ja izstrādes stadijā ar vaicājumu plāniem viss bija kārtībā, pieaugot datu apjomam, situācija var manāmi mainīties.

Avots: www.habr.com