Bilaketa-emaitzen irteera eta errendimendu arazoak

Ezagutzen ditugun aplikazio guztietan ohiko agertokietako bat datuak irizpide batzuen arabera bilatzea eta irakurtzeko modu errazean bistaratzea da. Ordenatzeko, taldekatzeko eta orrietarako aukera gehigarriak ere egon daitezke. Zeregina, teorian, hutsala da, baina konpontzerakoan garatzaile askok hainbat akats egiten dituzte, eta gero produktibitatea pairatzen dute. Saia gaitezen arazo hau konpontzeko hainbat aukera aztertzen eta inplementazio eraginkorrena aukeratzeko gomendioak formulatzen.

Orrialdearen aukera #1

Burura etortzen den aukerarik errazena bilaketa-emaitzen orrialdez orrialde bistaratzea da forma klasikoenean.

Demagun zure aplikazioak datu-base erlazional bat erabiltzen duela. Kasu honetan, inprimaki honetan informazioa bistaratzeko, bi SQL kontsulta exekutatu beharko dituzu:

• Lortu uneko orriaren errenkadak.
• Kalkulatu bilaketa-irizpideei dagozkien lerro kopurua guztira - beharrezkoa da orriak bistaratzeko.

Ikus dezagun lehenengo kontsulta probako MS SQL datu-base bat adibide gisa erabiliz AdventureWorks 2016 zerbitzarirako. Horretarako Sales.SalesOrderHeader taula erabiliko dugu:

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

Goiko kontsultak zerrendako lehen 50 eskaerak itzuliko ditu, gehitze-dataren arabera ordenatuta, hau da, azken 50 eskaerak.

Azkar exekutatzen da probaren oinarrian, baina ikus ditzagun exekuzio-plana eta I/O estatistikak:

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.

Kontsulta bakoitzerako I/O estatistikak lor ditzakezu SET STATISTICS IO ON komandoa exekutatuz kontsultaren exekuzioan.

Exekuzio-planean ikus dezakezun bezala, baliabide gehien erabiltzen dituen aukera da iturburu-taularen errenkada guztiak gehitutako dataren arabera ordenatzea. Eta arazoa da taulan zenbat eta errenkada gehiago agertu, orduan eta "zailagoa" izango dela ordenatzea. Praktikan, horrelako egoerak saihestu behar dira, beraz, gehi diezaiogun indize bat gehitze-datari eta ikus dezagun baliabideen kontsumoa aldatu den:

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.

Argi dago asko hobetu dela. Baina arazo guztiak konponduta al daude? Alda dezagun kontsulta salgaien kostu osoa 100 $ gainditzen duten eskaerak bilatzeko:

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.

Egoera xelebre bat dugu: kontsulta-plana ez da aurrekoa baino askoz okerragoa, baina irakurketa logikoen benetako kopurua taula osoko eskaneatzearekin baino bi aldiz handiagoa da. Irteera bat dago: lehendik dagoen indize batetik indize konposatua egiten badugu eta bigarren eremu gisa ondasunen prezio osoa gehitzen badugu, berriro 165 irakurketa logiko lortuko ditugu:

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

Adibide sorta hau luzaroan jarrai daiteke, baina hemen adierazi nahi ditudan bi gogoeta nagusiak hauek dira:

• Bilaketa-kontsultari edozein irizpide edo ordena berri gehitzeak eragin handia izan dezake bilaketa-kontsultaren abiaduran.
• Baina datuen zati bat bakarrik kendu behar badugu, eta ez bilaketa-terminoekin bat datozen emaitza guztiak, hainbat modu daude kontsulta hori optimizatzeko.

Orain, hasiera-hasieran aipatutako bigarren kontsultara pasa gaitezen: bilaketa-irizpidea betetzen duten erregistro-kopurua zenbatzen duena. Har dezagun adibide bera - 100 $ baino gehiagoko eskaerak bilatzen:

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

Goian adierazitako indize konposatua kontuan hartuta, honako hau lortzen dugu:

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.

Kontsultak indize osoan zehar igarotzea ez da harritzekoa, SubTotal eremua ez baitago lehen posizioan, beraz, kontsultak ezin du erabili. SubTotal eremuan beste indize bat gehituz konpontzen da arazoa, eta, ondorioz, 48 irakurketa logiko baino ez ditu ematen.

Kantitateak zenbatzeko eskaeren adibide gehiago eman ditzakezu, baina funtsak berdin jarraitzen du: datu bat jasotzea eta zenbateko osoa kontatzea funtsean bi eskaera desberdinak dira, eta bakoitzak bere neurriak eskatzen ditu optimizatzeko. Oro har, ezin izango duzu bi kontsultetarako berdin funtzionatzen duen indizeen konbinaziorik aurkitu.

Horren arabera, horrelako bilaketa-soluzio bat garatzean argitu beharko litzatekeen baldintza garrantzitsuenetako bat da ea benetan garrantzitsua den negozio batek aurkitutako objektuen kopuru osoa ikustea. Askotan gertatzen da ezetz. Eta orrialde-zenbaki zehatzen bidezko nabigazioa, nire ustez, esparru oso estua duen irtenbidea da, orri-eszenatoki gehienek "joan hurrengo orrialdera" bezalakoa baita.

Orrialdearen aukera #2

Demagun erabiltzaileei ez zaiela axola aurkitutako objektuen kopuru osoa jakiteak. Saia gaitezen bilaketa orria sinplifikatzen:

Izan ere, aldatu den gauza bakarra da ez dagoela orri-zenbaki zehatzetara nabigatzeko modurik, eta orain taula honek ez du jakin behar zenbat izan daitezkeen hura bistaratzeko. Baina galdera sortzen da: nola daki taulak hurrengo orrialderako datuak dauden ala ez («Hurrengoa» esteka behar bezala bistaratzeko)?

Erantzuna oso erraza da: datu-basetik bistaratzeko behar dena baino erregistro bat gehiago irakur dezakezu, eta erregistro "gehigarri" hori egoteak hurrengo zati bat dagoen ala ez erakutsiko du. Horrela, eskaera bakarra exekutatu beharko duzu datu orri bat lortzeko, eta horrek errendimendua nabarmen hobetzen du eta funtzionaltasun hori errazten du. Nire praktikan, kasu bat egon zen erregistro kopurua zenbatzeari uko egiteak emaitzak 4-5 aldiz bizkortu zituenean.

Ikuspegi honetarako hainbat erabiltzailearen interfaze aukera daude: "atzera" eta "aurrera" komandoak, goiko adibidean bezala, "kargatu gehiago" botoia, bistaratzen diren emaitzei zati berri bat gehitzen diena, "infinite scroll", funtzionatzen duena. "Gehiago kargatu" printzipioaren arabera, baina hurrengo zatia lortzeko seinalea erabiltzaileak bistaratutako emaitza guztiak amaieraraino mugitzea da. Irtenbide bisuala edozein dela ere, datuen laginketaren printzipioak berdin jarraitzen du.

Orrialdearen ezarpenaren ñabardurak

Goian emandako kontsulta-adibide guztiek "desplazamendua + zenbaketa" ikuspegia erabiltzen dute, kontsultak berak zehazten duenean emaitza errenkadak zein ordenatan eta zenbat errenkada itzuli behar diren. Lehenik eta behin, ikus dezagun kasu honetan parametroak pasatzea nola antolatzeko onena. Praktikan, hainbat metodo topatu ditut:

• Eskatutako orriaren serie-zenbakia (pageIndex), orriaren tamaina (pageSize).
• Itzuli beharreko lehen erregistroaren serie-zenbakia (startIndex), emaitzan gehienezko erregistro-kopurua (zenbaketa).
• Itzuli beharreko lehen erregistroaren sekuentzia-zenbakia (startIndex), itzuli beharreko azken erregistroaren sekuentzia-zenbakia (endIndex).

Lehen begiratuan badirudi hori hain oinarrizkoa dela, non ez dagoela desberdintasunik. Baina hau ez da horrela - aukerarik erosoena eta unibertsalena bigarrena da (hasiIndex, zenbatu). Hainbat arrazoi daude horretarako:

• Goian emandako +1 sarrera zuzentzeko ikuspegirako, pageIndex eta pageSize duten lehen aukera oso deserosoa da. Adibidez, orrialde bakoitzeko 50 mezu bistaratu nahi ditugu. Goiko algoritmoaren arabera, beharrezkoa baino erregistro bat gehiago irakurri behar duzu. "+1" hau zerbitzarian ezartzen ez bada, lehen orrialderako 1etik 51era bitarteko erregistroak eskatu behar ditugula, bigarrenerako - 51etik 101era, etab. 51 orrialdearen tamaina zehazten baduzu eta pageIndex handitzen baduzu, bigarren orrialdea 52tik 102ra itzuliko da, etab. Horren arabera, lehenengo aukeran, hurrengo orrialdera joateko botoi bat behar bezala inplementatzeko modu bakarra zerbitzariak "gehigarria" lerroa zuzentzea da, oso ñabardura inplizitua izango dena.
• Hirugarren aukerak ez du batere zentzurik, izan ere, datu-base gehienetan kontsultak egiteko, azken erregistroaren indizea baino gehiago zenbaketa gainditu beharko duzu. startIndex endIndex kentzea eragiketa aritmetiko sinple bat izan daiteke, baina hemen soberan dago.

Orain orria ezartzearen desabantailak deskribatu beharko genituzke "offset + kantitatea" bidez:

• Ondorengo orrialde bakoitza berreskuratzea aurrekoa baino garestiagoa eta motelagoa izango da, datu-baseak oraindik ere erregistro guztiak "hasieratik" igaro beharko dituelako bilaketa eta ordenatzeko irizpideen arabera, eta gero nahi den zatian gelditu.
• DBMS guztiek ezin dute ikuspegi hau onartzen.

Alternatibak badaude, baina inperfektuak ere badira. Planteamendu hauetako lehenengoari "keyset paging" edo "seek method" deitzen zaio eta honako hau da: zati bat jaso ondoren, eremuaren balioak gogoratu ditzakezu orrialdeko azken erregistroan, eta gero erabil ditzakezu lortzeko. hurrengo zatia. Adibidez, honako kontsulta hau egin dugu:

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

Eta azken erregistroan ‘2014-06-29’ eskaera-dataren balioa lortu dugu. Ondoren hurrengo orria lortzeko hau egiten saia zaitezke:

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

Arazoa da OrderDate eremu ez bakarra dela eta goian zehaztutako baldintza beharrezkoak diren errenkada asko galtzea litekeena da. Kontsulta honi anbiguotasuna gehitzeko, eremu esklusibo bat gehitu behar diozu baldintzari (demagun 75074 lehen zatiko gako nagusiaren azken balioa dela):

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

Aukera honek behar bezala funtzionatuko du, baina orokorrean zaila izango da optimizatzea baldintza horrek OR operadorea duelako. Lehen mailako gakoaren balioa handitzen bada OrderDate handitzen den heinean, baldintza sinplifikatu daiteke SalesOrderID-ren iragazkia soilik utziz. Baina lehen gakoaren balioen eta emaitza ordenatzen duen eremuaren arteko korrelazio zorrotzik ez badago, OR hau ezin da saihestu DBMS gehienetan. Ezagutzen dudan salbuespen bat PostgreSQL da, tuple konparaketa guztiz onartzen duena, eta goiko baldintza "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)" gisa idatz daiteke. Bi eremu hauek dituen gako konposatua emanda, honelako kontsultak nahiko erraza izan beharko luke.

Bigarren planteamendu alternatibo bat aurki daiteke, adibidez, urtean ElasticSearch scroll APIa edo Cosmos DB — Eskaera batek, datuez gain, identifikatzaile berezi bat itzultzen duenean, eta horrekin hurrengo datuen zatia lor dezakezu. Identifikatzaile honek bizitza mugagabea badu (Comsos DB-n bezala), orrien arteko trantsizio sekuentzialarekin orrialdeak ezartzeko modu bikaina da (goian aipatutako 2. aukera). Bere desabantaila posibleak: ez da onartzen DBMS guztietan; sortzen den hurrengo zatiaren identifikatzaileak bizitza mugatua izan dezake, eta, oro har, ez da egokia erabiltzailearen interakzioa ezartzeko (adibidez, ElasticSearch scroll APIa).

Iragazte konplexua

Zaildu dezagun zeregina. Demagun bilaketa fazetatua deritzona ezartzeko eskakizuna dagoela, eta hori oso ezaguna da lineako dendetako guztiontzat. Eskaeren taulan oinarritutako goiko adibideak ez dira oso ilustragarriak kasu honetan, beraz, joan gaitezen AdventureWorks datu-baseko Produktuen taulara:

Zein da bilaketa fazetatuaren atzean dagoen ideia? Kontua da iragazki-elementu bakoitzeko irizpide hori betetzen duten erregistro kopurua erakusten dela gainerako kategoria guztietan aukeratutako iragazkiak kontuan hartuta.

Adibidez, Adibide honetan Bizikletak kategoria eta Beltza kolorea hautatzen baditugu, taulak bizikleta beltzak bakarrik erakutsiko ditu, baina:

• Kategoriak taldeko irizpide bakoitzeko, kategoria horretako produktu kopurua beltzez agertuko da.
• “Koloreak” taldeko irizpide bakoitzeko, kolore horretako bizikleta kopurua erakutsiko da.

Hona hemen horrelako baldintzetarako emaitzen irteeraren adibide bat:

"Arropa" kategoria ere egiaztatzen baduzu, taulak stockean dauden arropa beltzak ere erakutsiko ditu. “Kolorea” ataleko produktu beltzen kopurua ere baldintza berrien arabera kalkulatuko da, soilik “Kategoriak” atalean ez da ezer aldatuko... Espero dut adibide hauek nahikoa izatea ohiko bilaketa-algoritmoa ulertzeko.

Orain imajina dezagun nola inplementa daitekeen hori harreman-oinarri batean. Irizpide talde bakoitzak, hala nola Kategoria eta Kolorea, kontsulta bereizi bat beharko du:

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

Zer gertatzen da irtenbide honek? Oso erraza da - ez da ondo eskalatzen. Iragazkien atal bakoitzak kontsulta bereizi bat behar du kantitateak kalkulatzeko, eta kontsulta hauek ez dira errazenak. Lineako dendetan, kategoria batzuek zenbait iragazki-atal izan ditzakete, eta horrek errendimendu-arazo larria izan daiteke.

Normalean adierazpen horien ondoren irtenbide batzuk eskaintzen zaizkit, hots:

• Konbinatu kantitate kopuru guztiak kontsulta batean. Teknikoki hori UNION gako-hitza erabiliz posible da, baina ez du errendimenduan asko lagunduko - datu-baseak zati bakoitza hutsetik exekutatu beharko du.
• Cache-kopuruak. Hau iradokitzen zait arazoren bat deskribatzen dudan bakoitzean. Oharra da hori orokorrean ezinezkoa dela. Demagun 10 "fazeta" ditugula, bakoitzak 5 balio dituela. Oso egoera "apala" da hau online denda berdinetan ikus daitekeenarekin alderatuta. Fazeta-elementu bat aukeratzeak beste 9 kantitateei eragiten die, hau da, irizpide-konbinazio bakoitzeko kantitateak desberdinak izan daitezke. Gure adibidean, guztira 50 irizpide daude erabiltzaileak hauta ditzakeena, beraz, 250 konbinazio posible izango dira. Ez dago nahikoa memoria edo denbora datu multzo hori betetzeko. Hemen aurka egin dezakezu eta esan dezakezu konbinazio guztiak ez direla benetakoak eta erabiltzaileak oso gutxitan hautatzen dituela 5-10 irizpide baino gehiago. Bai, posible da karga alferra egin eta inoiz hautatu denaren kopuru bat bakarrik gordetzea, baina zenbat eta hautapen gehiago egon, orduan eta eraginkortasun txikiagoa izango da horrelako cachea eta orduan eta nabarmenagoak izango dira erantzun denbora arazoak (batez ere datu multzoa aldizka aldatzen da).

Zorionez, horrelako arazo batek aspaldidanik nahiko konponbide eraginkorrak izan ditu, aurreikusten duten datu-bolumen handietan funtzionatzen dutenak. Aukera hauetako edozeinetarako, zentzuzkoa da alderdien birkalkulua eta emaitzen orria jasotzea zerbitzarirako bi dei paralelotan banatzea eta erabiltzailearen interfazea antolatzea horrela, datuen arabera kargatzeak bistaratzea "oztopatzen ez duen". bilaketa-emaitzak.

• Deitu "fazeten" birkalkulu osoa ahalik eta gutxien. Adibidez, ez kalkulatu dena berriro bilaketa-irizpideak aldatzen diren bakoitzean, baizik eta aurkitu uneko baldintzekin bat datozen emaitzen kopurua guztira eta eskatu erabiltzaileari erakusteko: "1425 erregistro aurkitu dira, erakutsi?" Erabiltzaileak bilaketa-terminoak aldatzen jarraitu edo "erakutsi" botoian klik egin dezake. Bigarren kasuan bakarrik exekutatuko dira emaitzak lortzeko eta kantitateak berriro kalkulatzeko "alderdi" guztietan. Kasu honetan, erraz ikus dezakezun bezala, eskaera bati aurre egin beharko diozu emaitza kopuru osoa eta haren optimizazioa lortzeko. Metodo hau lineako denda txiki askotan aurki daiteke. Jakina, hau ez da arazo honen panazea, baina kasu sinpleetan konpromiso ona izan daiteke.
• Erabili bilatzaileak emaitzak aurkitzeko eta alderdiak zenbatzeko, hala nola, Solr, ElasticSearch, Sphinx eta beste. Horiek guztiak "fazetak" eraikitzeko diseinatuta daude eta alderantzizko indizea dela eta nahiko eraginkortasunez egiteko. Bilatzaileek nola funtzionatzen duten, zergatik diren horrelako kasuetan helburu orokorreko datu-baseak baino eraginkorragoak, zer praktika eta hutsune dauden - hau aparteko artikulu baterako gaia da. Hemen zure arreta deitu nahi dut bilatzailea ezin dela datu biltegiratze nagusiaren ordezkoa izan; gehigarri gisa erabiltzen da: bilaketarako garrantzitsuak diren datu-base nagusiko aldaketak bilaketa-indizean sinkronizatzen dira; Bilatzaileak normalean bilatzailearekin soilik elkarreragiten du eta ez da datu-base nagusira sartzen. Hemen puntu garrantzitsuenetako bat sinkronizazio hori modu fidagarrian antolatzea da. Guztia "erreakzio denbora" eskakizunen araberakoa da. Datu-base nagusian aldaketa baten eta bilaketan "agertzearen" arteko denbora kritikoa ez bada, berriki aldatutako erregistroak minutu gutxitan bilatu eta indexatzen dituen zerbitzu bat sor dezakezu. Erantzun denbora ahalik eta laburrena nahi baduzu, antzeko zerbait ezar dezakezu transakzio-irteera-ontzia bilaketa-zerbitzuari eguneraketak bidaltzeko.

Findings

1. Zerbitzariaren alboko orrialdea ezartzea konplikazio nabarmena da eta hazten ari diren datu-multzo edo, besterik gabe, handientzat bakarrik du zentzua. Ez dago "handia" edo "azkar hazten" nola ebaluatzeko errezeta zehatzik, baina ikuspegi hau jarraituko nuke:
   • Datu-bilduma osoa jasotzeak, zerbitzariaren denbora eta sarearen transmisioa kontuan hartuta, errendimendu-baldintzak normalean betetzen baditu, ez dago zerbitzariaren aldean orria ezartzeak.
   • Etorkizun hurbilean errendimendu arazorik espero ez den egoera egon daiteke, datu gutxi baitago, baina datu bilketa etengabe hazten ari da. Etorkizunean datu-multzoren batek aurreko puntua betetzen ez badu, hobe da berehala orria egiten hastea.
2. Negozioaren aldetik emaitza kopuru osoa bistaratzeko edo orri-zenbakiak bistaratzeko baldintza zorrotzik ez badago eta zure sistemak ez badu bilatzailerik, hobe da puntu hauek ez ezartzea eta #2 aukera kontuan hartzea.
3. Bilaketa aldeetan egiteko baldintza argia badago, bi aukera dituzu errendimenduari uko egin gabe:
   • Ez kalkulatu kantitate guztiak bilaketa-irizpideak aldatzen diren bakoitzean.
   • Erabili bilatzaileak, hala nola, Solr, ElasticSearch, Sphinx eta beste. Baina ulertu behar da ezin dela izan datu-base nagusiaren ordezkoa, eta biltegiratze nagusiaren gehigarri gisa erabili behar dela bilaketa-arazoak konpontzeko.
4. Era berean, bilaketa alderdien kasuan, zentzuzkoa da bilaketa-emaitzen orria berreskuratzea eta zenbaketa bi eskaera paralelotan banatzea. Kantitateak zenbatzeak emaitzak lortzea baino denbora gehiago behar izan dezake, eta emaitzak erabiltzailearentzat garrantzitsuagoak dira.
5. Bilaketarako SQL datu-base bat erabiltzen ari bazara, zati honekin erlazionatutako edozein kode-aldaketa ondo probatu behar da datu-kopuru egokian (zuzeneko datu-basearen bolumena gainditzen duen). Era berean, komeni da kontsultaren exekuzio denboraren jarraipena erabiltzea datu-baseko instantzia guztietan, eta bereziki "zuzeneko" batean. Kontsulten planekin dena ondo egon bazen ere garapen fasean, datuen bolumena hazi ahala, egoera nabarmen alda daiteke.

Iturria: www.habr.com