Rezilta rechèch pwodiksyon ak pwoblèm pèfòmans

Youn nan senaryo tipik nan tout aplikasyon nou abitye ak se chèche done dapre sèten kritè epi montre li nan yon fòm fasil pou li. Kapab genyen opsyon adisyonèl tou pou klasman, gwoupman, ak paging. Travay la se, nan teyori, trivial, men lè rezoud li, anpil devlopè fè yon kantite erè, ki pita lakòz pwodiktivite soufri. Ann eseye konsidere divès opsyon pou rezoud pwoblèm sa a ak fòmile rekòmandasyon pou chwazi aplikasyon ki pi efikas.

Paging opsyon #1

Opsyon ki pi senp ki vin nan tèt ou se yon ekspozisyon paj pa paj nan rezilta rechèch nan fòm ki pi klasik li yo.

Ann di aplikasyon w lan sèvi ak yon baz done relasyon. Nan ka sa a, pou montre enfòmasyon nan fòm sa a, w ap bezwen kouri de demann SQL:

• Jwenn ranje pou paj aktyèl la.
• Kalkile kantite total liy ki koresponn ak kritè rechèch yo - sa nesesè pou montre paj yo.

Ann gade nan premye rechèch la lè l sèvi avèk yon baz done tès MS SQL kòm yon egzanp AdventureWorks pou sèvè 2016. Pou rezon sa a nou pral itilize tab Sales.SalesOrderHeader:

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

Rekèt ki pi wo a pral retounen 50 premye lòd ki soti nan lis la, klase pa dat desant de adisyon, sa vle di, 50 lòd ki pi resan yo.

Li kouri byen vit sou baz tès la, men ann gade plan ekzekisyon an ak estatistik I/O:

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.

Ou ka jwenn estatistik I/O pou chak demann lè w ap kouri kòmandman SET STATISTICS IO ON la nan tan an ekzekite rechèch la.

Kòm ou ka wè nan plan ekzekisyon an, opsyon ki pi entansif resous se klase tout ranje tab sous la pa dat ajoute. Ak pwoblèm nan se ke plis ranje parèt nan tablo a, "pi difisil" klasman an pral. Nan pratik, sitiyasyon sa yo ta dwe evite, kidonk ann ajoute yon endèks nan dat adisyon a epi wè si konsomasyon resous te chanje:

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.

Li evidan li vinn anpil pi bon. Men, èske tout pwoblèm yo rezoud? Ann chanje rechèch la pou chèche lòd kote pri total machandiz depase $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.

Nou gen yon sitiyasyon komik: plan rechèch la pa pi mal pase anvan an, men kantite aktyèl la nan lekti lojik se prèske de fwa pi gwo pase ak yon eskanè tab konplè. Gen yon fason soti - si nou fè yon endèks konpoze soti nan yon endèks ki deja egziste epi ajoute pri total machandiz la kòm dezyèm jaden an, nou pral ankò jwenn 165 lekti lojik:

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

Seri egzanp sa yo ka kontinye pou yon tan long, men de panse prensipal yo ke mwen vle eksprime isit la yo se:

• Ajoute nenpòt nouvo kritè oswa lòd sòt nan yon rechèch rechèch ka gen yon enpak siyifikatif sou vitès la nan rechèch rechèch la.
• Men, si nou bezwen soustraksyon sèlman yon pati nan done yo, epi yo pa tout rezilta yo ki matche ak tèm rechèch yo, gen plizyè fason yo optimize yon rechèch konsa.

Koulye a, ann ale nan dezyèm rechèch la mansyone nan kòmansman an anpil - youn nan ki konte kantite dosye ki satisfè kritè rechèch la. Ann pran menm egzanp - chèche lòd ki gen plis pase $ 100:

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

Bay endèks konpoze ki endike anwo a, nou jwenn:

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.

Lefèt ke rechèch la ale nan endèks la tout antye se pa etone, depi jaden an SubTotal se pa nan premye pozisyon an, kidonk rechèch la pa ka sèvi ak li. Pwoblèm nan rezoud lè w ajoute yon lòt endèks sou jaden SubTotal la, epi kòm yon rezilta li bay sèlman 48 lekti lojik.

Ou ka bay kèk egzanp plis nan demann pou konte kantite, men sans lan rete menm jan an: resevwa yon moso nan done ak konte kantite total la se de demann fondamantalman diferan, epi chak mande pwòp mezi pa yo pou optimize. An jeneral, ou p ap ka jwenn yon konbinezon de endèks ki travay egalman byen pou tou de requêtes.

An konsekans, youn nan kondisyon ki enpòtan yo ta dwe klarifye lè w ap devlope yon solisyon rechèch se si li vrèman enpòtan pou yon biznis wè kantite total objè yo jwenn. Li souvan rive ke non. Ak navigasyon pa nimewo paj espesifik, nan opinyon mwen, se yon solisyon ak yon sijè ki abòde trè etwat, paske pifò senaryo paging sanble "ale nan pwochen paj la."

Paging opsyon #2

Ann sipoze ke itilizatè yo pa pran swen sou konnen kantite total objè yo jwenn. Ann eseye senplifye paj rechèch la:

An reyalite, bagay la sèlman ki te chanje se ke pa gen okenn fason yo navige nan nimewo paj espesifik, e kounye a, tablo sa a pa bezwen konnen konbyen ka genyen yo nan lòd yo montre li. Men, kesyon an rive - ki jan tab la konnen si gen done pou pwochen paj la (yo nan lòd yo montre kòrèkteman lyen "Next")?

Repons lan se trè senp: ou ka li nan baz done a yon lòt dosye plis pase sa nesesè pou ekspozisyon, ak prezans nan dosye "anplis" sa a pral montre si gen yon pòsyon pwochen. Nan fason sa a, ou sèlman bezwen kouri yon sèl demann pou jwenn yon paj nan done, ki siyifikativman amelyore pèfòmans ak fè li pi fasil sipòte fonksyonalite sa yo. Nan pratik mwen an, te gen yon ka lè refize konte kantite total dosye akselere livrezon rezilta yo pa 4-5 fwa.

Gen plizyè opsyon koòdone itilizatè pou apwòch sa a: "retounen" ak "devan" kòmandman, tankou nan egzanp ki anwo a, yon bouton "chaje plis", ki tou senpleman ajoute yon nouvo pòsyon nan rezilta yo parèt, "enfini woulo liv", ki travay. sou prensip la nan "chaje plis" ", men siyal la jwenn pòsyon nan pwochen se pou itilizatè a woulo liv tout rezilta yo parèt nan fen an. Kèlkeswa solisyon vizyèl la, prensip echantiyon done rete menm jan an.

Nuans nan aplikasyon paging

Tout egzanp rechèch yo bay pi wo a itilize apwòch "decalaj + konte", lè rechèch la li menm espesifye nan ki lòd ranje rezilta yo ak konbyen ranje yo bezwen retounen. Premyèman, kite a gade nan ki jan pi bon yo òganize paramèt pase nan ka sa a. Nan pratik, mwen te rankontre plizyè metòd:

• Nimewo seri paj yo mande a (pageIndex), gwosè paj (pageSize).
• Nimewo seri premye dosye yo dwe retounen (startIndex), kantite maksimòm dosye nan rezilta a (kont).
• Nimewo sekans premye dosye yo dwe retounen (startIndex), nimewo sekans dènye dosye yo dwe retounen (endIndex).

Nan premye gade li ka sanble ke sa a se tèlman elemantè ke pa gen okenn diferans. Men, sa a se pa konsa - opsyon ki pi pratik ak inivèsèl se dezyèm lan (startIndex, konte). Gen plizyè rezon pou sa:

• Pou apwòch koreksyon +1 antre yo bay pi wo a, premye opsyon ki gen pageIndex ak pageSize trè enkonvenyan. Pou egzanp, nou vle montre 50 posts pou chak paj. Dapre algorithm ki pi wo a, ou bezwen li yon lòt dosye pase sa nesesè. Si "+1" sa a pa aplike sou sèvè a, li sanble ke pou premye paj la nou dwe mande dosye soti nan 1 a 51, pou dezyèm lan - soti nan 51 a 101, elatriye. Si ou presize yon gwosè paj nan 51 epi ogmante pageIndex, Lè sa a, dezyèm paj la ap retounen soti nan 52 a 102, elatriye. An konsekans, nan premye opsyon an, sèl fason pou byen aplike yon bouton pou ale nan pwochen paj la se fè sèvè a koreksyon liy "siplemantè", ki pral yon nuans trè implicite.
• Twazyèm opsyon a pa fè sans ditou, paske pou w ka kouri demann nan pifò baz done w ap toujou bezwen pase konte a olye ke endèks dènye dosye a. Soustraksyon startIndex soti nan endIndex ka yon operasyon aritmetik senp, men li initil isit la.

Koulye a, nou ta dwe dekri dezavantaj yo nan aplikasyon paging atravè "konpanse + kantite":

• Rekipere chak paj ki vin apre yo pral pi chè ak pi dousman pase youn anvan an, paske baz done a ap toujou bezwen ale nan tout dosye yo "depi nan kòmansman an" dapre rechèch la ak kritè klasman, ak Lè sa a, sispann nan fragman an vle.
• Se pa tout DBMS yo ki ka sipòte apwòch sa a.

Gen altènativ, men yo tou enpafè. Premye apwòch sa yo rele "keyset paging" oswa "seek method" e li se jan sa a: apre ou fin resevwa yon pòsyon, ou ka sonje valè jaden yo nan dènye dosye ki sou paj la, epi sèvi ak yo pou jwenn pwochen pòsyon an. Pou egzanp, nou te kouri rechèch sa a:

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

Ak nan dènye dosye a nou te resevwa valè dat lòd '2014-06-29'. Lè sa a, pou jwenn pwochen paj la ou ka eseye fè sa:

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

Pwoblèm lan se ke OrderDate se yon jaden ki pa inik ak kondisyon ki espesifye pi wo a gen chans rive nan manke yon anpil nan ranje obligatwa. Pou ajoute anbigwite nan rechèch sa a, ou bezwen ajoute yon jaden inik nan kondisyon an (sipoze ke 75074 se dènye valè kle prensipal la soti nan premye pòsyon an):

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

Opsyon sa a pral travay kòrèkteman, men an jeneral li pral difisil pou optimize paske kondisyon an gen yon operatè OSWA. Si valè kle prensipal la ogmante kòm OrderDate ogmante, Lè sa a, kondisyon an ka senplifye lè yo kite sèlman yon filtè pa SalesOrderID. Men, si pa gen okenn korelasyon strik ant valè kle prensipal la ak jaden kote rezilta a klase, OSWA sa a pa ka evite nan pifò DBMS yo. Yon eksepsyon mwen konnen se PostgreSQL, ki konplètman sipòte konparezon tuple, ak kondisyon ki anwo a ka ekri kòm "KOTE (OrderDate, SalesOrderID) <('2014-06-29', 75074)". Bay yon kle konpoze ak de jaden sa yo, yon rechèch tankou sa a ta dwe jistis fasil.

Yon dezyèm apwòch altènatif ka jwenn, pou egzanp, nan ElasticSearch woulo API oswa Cosmos DB — lè yon demann, anplis done, retounen yon idantifyan espesyal ak ki ou ka jwenn pòsyon nan pwochen nan done. Si idantifyan sa a gen yon lavi san limit (tankou nan Comsos DB), Lè sa a, sa a se yon bon fason pou aplike paging ak tranzisyon sekans ant paj (opsyon #2 mansyone pi wo a). Dezavantaj posib li yo: li pa sipòte nan tout DBMS yo; idantifyan kap vini an kap vini an ka gen yon lavi limite, ki jeneralman pa apwopriye pou mete ann aplikasyon entèraksyon itilizatè (tankou API nan woulo ElasticSearch).

Filtrage konplèks

Ann konplike travay la pi lwen. Sipoze gen yon egzijans pou aplike sa yo rele rechèch fasèt, ki trè abitye ak tout moun ki soti nan magazen sou entènèt. Egzanp ki anwo yo ki baze sou tablo lòd yo pa trè ilistrasyon nan ka sa a, kidonk ann chanje nan tablo Product ki soti nan baz done AdventureWorks la:

Ki lide dèyè rechèch fasèt? Reyalite a se ke pou chak eleman filtre yo montre kantite dosye ki satisfè kritè sa a pran an kont filtè yo chwazi nan tout lòt kategori.

Pa egzanp, si nou chwazi kategori Bisiklèt ak koulè Nwa nan egzanp sa a, tablo a ap montre sèlman bisiklèt nwa, men:

• Pou chak kritè nan gwoup Kategori yo, yo pral montre kantite pwodwi ki nan kategori sa a an nwa.
• Pou chak kritè gwoup "Koulè", yo pral montre kantite bisiklèt koulè sa a.

Men yon egzanp rezilta rezilta pou kondisyon sa yo:

Si w tcheke tou kategori "Rad", tablo a pral montre tou rad nwa ki nan stock. Nimewo a nan pwodwi nwa nan seksyon "Koulè" la pral rekalkile tou dapre nouvo kondisyon yo, sèlman nan seksyon "Kategori" pa gen anyen ki pral chanje... Mwen espere egzanp sa yo ase yo konprann algorithm rechèch la abityèl fasèt.

Koulye a, kite a imajine ki jan sa a ka aplike sou yon baz relasyon. Chak gwoup kritè, tankou Kategori ak Koulè, pral mande pou yon rechèch separe:

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

Ki sa ki mal ak solisyon sa a? Li trè senp - li pa echèl byen. Chak seksyon filtre mande pou yon rechèch separe pou kalkile kantite, epi demann sa yo pa pi fasil. Nan magazen sou entènèt, kèk kategori ka gen plizyè douzèn seksyon filtre, ki ka yon pwoblèm pèfòmans grav.

Anjeneral apre deklarasyon sa yo mwen ofri kèk solisyon, sètadi:

• Konbine tout kantite kantite nan yon sèl demann. Teknikman sa posib lè l sèvi avèk mo kle UNION la, men li pa pral ede pèfòmans anpil - baz done a ap toujou gen pou egzekite chak nan fragman yo nan grate.
• Kantite kachèt. Sa a se sijere m 'prèske chak fwa mwen dekri yon pwoblèm. Opozisyon an se ke sa a se jeneralman enposib. Ann di nou gen 10 "fas", chak nan yo ki gen 5 valè. Sa a se yon sitiyasyon trè "modès" konpare ak sa ki ka wè nan menm magazen yo sou entènèt. Chwa a nan yon sèl eleman fasèt afekte kantite yo nan 9 lòt, nan lòt mo, pou chak konbinezon de kritè kantite yo ka diferan. Nan egzanp nou an, gen yon total de 50 kritè ke itilizatè a ka chwazi, kidonk, pral gen konbinezon posib 250. Pa gen ase memwa oswa tan pou ranpli yon seri done konsa. Isit la ou ka fè objeksyon epi di ke se pa tout konbinezon reyèl epi itilizatè a raman chwazi plis pase 5-10 kritè. Wi, li posib pou fè loading parese ak kachèt yon kantite sèlman sa ki te janm chwazi, men plis seleksyon gen, se mwens efikas tankou yon kachèt ak pwoblèm nan tan repons yo pral pi aparan (espesyalman si la. seri done chanje regilyèman).

Erezman, pwoblèm sa a depi lontan te gen solisyon byen efikas ki travay previzib sou gwo kantite done. Pou nenpòt nan opsyon sa yo, li fè sans pou divize rekalkile fasèt yo epi resevwa paj rezilta a an de apèl paralèl ak sèvè a epi òganize koòdone itilizatè a yon fason pou chaje done pa fasèt yo "pa entèfere" ak ekspozisyon an. rezilta rechèch la.

• Rele yon rekalkil konplè nan "aspè" pi raman ke posib. Pa egzanp, pa rekalkile tout bagay chak fwa kritè rechèch la chanje, men pito jwenn kantite total rezilta ki koresponn ak kondisyon aktyèl yo epi mande itilizatè a montre yo - "1425 dosye yo te jwenn, montre?" Itilizatè a ka swa kontinye chanje tèm rechèch yo oswa klike sou bouton "montre". Se sèlman nan dezyèm ka a tout demann pou jwenn rezilta ak rekalkile kantite sou tout "aspè" yo pral egzekite. Nan ka sa a, jan ou ka fasilman wè, ou pral gen fè fas ak yon demann yo jwenn kantite total rezilta ak optimize li yo. Metòd sa a ka jwenn nan anpil ti boutik sou entènèt. Li evidan, sa a se pa yon mirak pou pwoblèm sa a, men nan ka senp li ka yon bon konpwomi.
• Sèvi ak motè rechèch pou jwenn rezilta ak konte aspè, tankou Solr, ElasticSearch, Sphinx ak lòt moun. Yo tout yo fèt pou konstwi "aspè" epi fè sa byen avèk efikasite akòz endèks la Envèse. Ki jan motè rechèch travay, poukisa nan ka sa yo yo pi efikas pase baz done jeneral, ki pratik ak enkonvenyans ki genyen - sa a se yon sijè pou yon atik separe. Isit la mwen ta renmen atire atansyon ou sou lefèt ke motè rechèch la pa ka yon ranplasman pou depo done prensipal la; li itilize kòm yon adisyon: nenpòt chanjman ki fèt nan baz done prensipal la ki enpòtan pou rechèch yo senkronize nan endèks rechèch la; Motè rechèch la anjeneral reyaji sèlman ak motè rechèch la epi li pa jwenn aksè nan baz done prensipal la. Youn nan pwen ki pi enpòtan isit la se ki jan yo òganize senkronizasyon sa a seryezman. Tout depann sou kondisyon "tan reyaksyon". Si tan ki genyen ant yon chanjman nan baz done prensipal la ak "manifestasyon" li nan rechèch la pa kritik, ou ka kreye yon sèvis ki fè rechèch pou dosye ki fèk chanje chak kèk minit epi endis yo. Si ou vle tan repons ki pi kout posib, ou ka aplike yon bagay tankou bwat tranzaksyon yo pou voye mizajou nan sèvis rechèch la.

Jwenn

1. Aplike paging bò sèvè se yon konplikasyon enpòtan epi sèlman fè sans pou seri done k ap grandi rapid oswa tou senpleman gwo. Pa gen okenn resèt absoliman egzak pou kijan pou evalye "gwo" oswa "ap grandi rapid", men mwen ta swiv apwòch sa a:
   • Si w ap resevwa yon koleksyon done konplè, pran an kont tan sèvè ak transmisyon rezo, adapte kondisyon pèfòmans yo nòmalman, pa gen okenn pwen nan mete ann aplikasyon paging sou bò sèvè.
   • Ka gen yon sitiyasyon kote pa gen okenn pwoblèm pèfòmans espere nan fiti prè, depi gen ti done, men koleksyon done a toujou ap grandi. Si kèk seri done nan tan kap vini an ka pa satisfè pwen anvan an ankò, li pi bon pou kòmanse pageging touswit.
2. Si pa gen okenn egzijans strik sou pati nan biznis la pou montre kantite total rezilta oswa pou montre nimewo paj, epi sistèm ou a pa gen yon motè rechèch, li pi bon pou pa aplike pwen sa yo epi konsidere opsyon #2.
3. Si gen yon kondisyon klè pou rechèch fasèt, ou gen de opsyon san sakrifye pèfòmans:
   • Pa rekalkile tout kantite chak fwa kritè rechèch la chanje.
   • Sèvi ak motè rechèch tankou Solr, ElasticSearch, Sphinx ak lòt moun. Men, li ta dwe konprann ke li pa ka yon ranplasman pou baz done prensipal la, epi yo ta dwe itilize kòm yon adisyon nan depo prensipal la pou rezoud pwoblèm rechèch.
4. Epitou, nan ka rechèch fasèt, li fè sans pou divize rekipere paj rezilta rechèch la ak konte a an de demann paralèl. Konte kantite ka pran plis tan pase jwenn rezilta, pandan y ap rezilta yo pi enpòtan pou itilizatè a.
5. Si w ap itilize yon baz done SQL pou chèche, nenpòt chanjman kòd ki gen rapò ak pati sa a ta dwe byen teste pou pèfòmans sou kantite done ki apwopriye (depase volim nan baz done a ap viv). Li rekòmande tou pou itilize siveyans tan ekzekisyon demann sou tout sikonstans nan baz done a, epi espesyalman sou "viv" yon sèl la. Menm si tout bagay te byen ak plan rechèch nan etap devlopman, kòm volim nan done ap grandi, sitiyasyon an ka chanje notables.

Sous: www.habr.com