Unu el la tipaj scenaroj en ĉiuj aplikaĵoj, kiujn ni konas, estas serĉi datumojn laŭ certaj kriterioj kaj montri ĝin en facile legebla formo. Eble ankaŭ ekzistas pliaj elektoj por ordigo, grupigo kaj paĝigo. La tasko estas, en teorio, bagatela, sed solvante ĝin, multaj programistoj faras kelkajn erarojn, kiuj poste suferas produktivecon. Ni provu konsideri diversajn eblojn por solvi ĉi tiun problemon kaj formulu rekomendojn por elekti la plej efikan efektivigon.
Paĝiga opcio #1
La plej simpla opcio, kiu venas en la menso, estas paĝo-post-paĝa montrado de serĉrezultoj en sia plej klasika formo.
Ni diru, ke via aplikaĵo uzas rilatan datumbazon. En ĉi tiu kazo, por montri informojn en ĉi tiu formo, vi devos ruli du SQL-demandojn:
- Akiru vicojn por la nuna paĝo.
- Kalkulu la totalan nombron da linioj respondaj al la serĉkriterioj - tio estas necesa por montri paĝojn.
Ni rigardu la unuan demandon uzante provan MS SQL-datumbazon kiel ekzemplon por 2016 servilo. Tiucele ni uzos la tabelon Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
La ĉi-supra demando resendos la unuajn 50 ordojn de la listo, ordigitaj laŭ descenda dato de aldono, alivorte, la 50 plej freŝaj ordoj.
Ĝi funkcias rapide sur la testa bazo, sed ni rigardu la ekzekutplanon kaj I/O-statistikojn:
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.Vi povas akiri I/O-statistikojn por ĉiu demando rulante la komandon SET STATISTICS IO ON en la demanda rultempo.
Kiel vi povas vidi el la ekzekutplano, la plej rimeda opcio estas ordigi ĉiujn vicojn de la fonta tabelo laŭ dato aldonita. Kaj la problemo estas, ke ju pli da vicoj aperas en la tabelo, des pli "malmola" estos la ordigo. En la praktiko, tiaj situacioj devus esti evititaj, do ni aldonu indekson al la dato de aldono kaj vidu ĉu la konsumo de rimedoj ŝanĝiĝis:
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.
Evidente ĝi multe pliboniĝis. Sed ĉu ĉiuj problemoj estas solvitaj? Ni ŝanĝu la demandon por serĉi mendojn kie la totalkosto de varoj superas $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.Ni havas amuzan situacion: la demandplano ne estas multe pli malbona ol la antaŭa, sed la reala nombro da logikaj legoj estas preskaŭ duoble pli granda ol kun plena tabelskanado. Estas elirejo - se ni faras kunmetitan indekson el jam ekzistanta indekso kaj aldonas la totalan prezon de varoj kiel la duan kampon, ni denove ricevos 165 logikajn legaĵojn:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Ĉi tiu serio de ekzemploj povas esti daŭrigita dum longa tempo, sed la du ĉefaj pensoj, kiujn mi volas esprimi ĉi tie, estas:
- Aldoni ajnan novan kriterion aŭ ordigon al serĉdemando povas havi signifan efikon al la rapideco de la serĉdemando.
- Sed se ni bezonas subtrahi nur parton de la datumoj, kaj ne ĉiujn rezultojn, kiuj kongruas kun la serĉaj terminoj, ekzistas multaj manieroj optimumigi tian demandon.
Nun ni transiru al la dua konsulto menciita komence - tiu, kiu kalkulas la nombron da registroj, kiuj kontentigas la serĉkriterion. Ni prenu la saman ekzemplon - serĉante mendojn kiuj estas pli ol $100:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Surbaze de la kunmetita indekso indikita supre, ni ricevas:
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.La fakto, ke la demando trairas la tutan indekson, ne estas surpriza, ĉar la kampo SubTotal ne estas en la unua pozicio, do la demando ne povas uzi ĝin. La problemo estas solvita aldonante alian indekson sur la kampo SubTotal, kaj kiel rezulto ĝi donas nur 48 logikajn legadojn.
Vi povas doni kelkajn pliajn ekzemplojn de petoj por kalkuli kvantojn, sed la esenco restas la sama: ricevi pecon da datumoj kaj kalkuli la totalan kvanton estas du fundamente malsamaj petoj, kaj ĉiu postulas siajn proprajn mezurojn por optimumigo. Ĝenerale, vi ne povos trovi kombinaĵon de indeksoj, kiu funkcias same bone por ambaŭ demandoj.
Sekve, unu el la gravaj postuloj, kiuj devus esti klarigitaj kiam disvolvas tian serĉsolvon, estas ĉu vere gravas por komerco vidi la tutan nombron da trovitaj objektoj. Ofte okazas, ke ne. Kaj navigado per specifaj paĝnumeroj, laŭ mi, estas solvo kun tre malvasta amplekso, ĉar la plej multaj paĝscenaroj aspektas kiel "iru al la sekva paĝo".
Paĝiga opcio #2
Ni supozu, ke uzantoj ne zorgas pri sciado de la tuta nombro de trovitaj objektoj. Ni provu simpligi la serĉpaĝon:
Fakte, la sola afero, kiu ŝanĝiĝis, estas, ke ne estas maniero navigi al specifaj paĝnumeroj, kaj nun ĉi tiu tabelo ne bezonas scii kiom povas esti por montri ĝin. Sed staras la demando - kiel la tabelo scias ĉu estas datumoj por la sekva paĝo (por ĝuste montri la ligilon "Sekva")?
La respondo estas tre simpla: vi povas legi el la datumbazo unu plian registron ol necesas por montri, kaj la ĉeesto de ĉi tiu "aldona" registro montros ĉu estas sekva parto. Tiel vi nur bezonas fari unu peton por ricevi unu paĝon da datumoj, kio signife plibonigas rendimenton kaj faciligas subteni tian funkcion. En mia praktiko, estis kazo kiam rifuzi kalkuli la totalan nombron da rekordoj rapidigis la liveron de rezultoj je 4-5 fojojn.
Estas pluraj opcioj de uzantinterfaco por ĉi tiu aliro: komandoj "malantaŭen" kaj "antaŭen", kiel en la supra ekzemplo, butonon "ŝarĝi pli", kiu simple aldonas novan parton al la montrataj rezultoj, "senfina movo", kiu funkcias. laŭ la principo "ŝarĝi pli" ", sed la signalo por ricevi la sekvan parton estas por la uzanto rulumi ĉiujn montritajn rezultojn ĝis la fino. Kia ajn estas la vida solvo, la principo de datuma specimenigo restas la sama.
Nuancoj de paĝiga efektivigo
Ĉiuj demandekzemploj donitaj supre uzas la "offset + kalkulo" aliron, kiam la demando mem specifas en kiu ordo la rezultvicoj kaj kiom da vicoj devas esti resenditaj. Unue, ni rigardu kiel plej bone organizi parametron en ĉi tiu kazo. En la praktiko, mi trovis plurajn metodojn:
- La seria numero de la petita paĝo (pageIndex), paĝa grandeco (pageSize).
- La seria numero de la unua rekordo redonota (startIndex), la maksimuma nombro da rekordoj en la rezulto (kalkulo).
- La sinsekvo de la unua rekordo redonota (startIndex), la sinsekvo de la lasta rekordo redonota (endIndex).
Unuavide povas ŝajni, ke tio estas tiel elementa, ke ne ekzistas diferenco. Sed ĉi tio ne estas tiel - la plej oportuna kaj universala opcio estas la dua (startIndex, kalkuli). Estas pluraj kialoj por ĉi tio:
- Por la +1-enira provlegado-aliro donita supre, la unua opcio kun pageIndex kaj pageSize estas ege maloportuna. Ekzemple, ni volas montri 50 afiŝojn po paĝo. Laŭ la ĉi-supra algoritmo, vi devas legi unu plian rekordon ol necese. Se ĉi tiu "+1" ne estas efektivigita sur la servilo, rezultas, ke por la unua paĝo ni devas peti registrojn de 1 ĝis 51, por la dua - de 51 ĝis 101, ktp. Se vi specifas paĝan grandecon de 51 kaj pliigas pageIndex, tiam la dua paĝo revenos de 52 ĝis 102, ktp. Sekve, en la unua opcio, la sola maniero ĝuste efektivigi butonon por iri al la sekva paĝo estas ke la servilo provlegu la "kroman" linion, kiu estos tre implica nuanco.
- La tria opcio tute ne havas sencon, ĉar por fari demandojn en plej multaj datumbazoj, vi ankoraŭ devos pasigi la kalkulon anstataŭ la indekson de la lasta rekordo. Subtrahi startIndex de endIndex povas esti simpla aritmetika operacio, sed ĝi estas superflua ĉi tie.
Nun ni devus priskribi la malavantaĝojn de efektivigado de paĝigo per "offset + kvanto":
- Repreni ĉiun postan paĝon estos pli multekosta kaj pli malrapida ol la antaŭa, ĉar la datumbazo ankoraŭ bezonos trairi ĉiujn registrojn "de la komenco" laŭ la serĉo kaj ordigo kriterioj, kaj poste halti ĉe la dezirata fragmento.
- Ne ĉiuj DBMS-oj povas subteni ĉi tiun aliron.
Estas alternativoj, sed ili ankaŭ estas neperfektaj. La unua el ĉi tiuj aliroj nomiĝas "keyset paging" aŭ "serĉmetodo" kaj estas jena: post ricevi parton, vi povas memori la kampovalorojn en la lasta rekordo sur la paĝo, kaj poste uzi ilin por akiri. la sekva parto. Ekzemple, ni rulis la sekvan demandon:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Kaj en la lasta rekordo ni ricevis la valoron de la menda dato '2014-06-29'. Tiam por ricevi la sekvan paĝon vi povas provi fari ĉi tion:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
La problemo estas, ke OrderDate estas ne-unika kampo kaj la kondiĉo specifita supre verŝajne maltrafos multajn postulatajn vicojn. Por aldoni malambiguecon al ĉi tiu demando, vi devas aldoni unikan kampon al la kondiĉo (supoze ke 75074 estas la lasta valoro de la ĉefa ŝlosilo de la unua parto):
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
Ĉi tiu opcio funkcios ĝuste, sed ĝenerale estos malfacile optimumebla ĉar la kondiĉo enhavas OR-funkciigiston. Se la valoro de la primara ŝlosilo pliiĝas kiel OrderDate pliiĝas, tiam la kondiĉo povas esti simpligita lasante nur filtrilon de SalesOrderID. Sed se ne ekzistas strikta korelacio inter la valoroj de la ĉefa ŝlosilo kaj la kampo per kiu la rezulto estas ordigita, ĉi tiu AŬ ne povas esti evitita en la plej multaj DBMSoj. Escepto pri kiu mi konscias estas PostgreSQL, kiu plene subtenas opon-komparon, kaj la supra kondiĉo povas esti skribita kiel "KIE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Konsiderante kunmetitan ŝlosilon kun ĉi tiuj du kampoj, demando kiel ĉi tiu devus esti sufiĉe facila.
Dua alternativa aliro troveblas, ekzemple, en aŭ — kiam peto, krom datumoj, resendas specialan identigilon per kiu vi povas akiri la sekvan parton de datumoj. Se ĉi tiu identigilo havas senliman vivdaŭron (kiel en Comsos DB), tiam ĉi tio estas bonega maniero efektivigi paĝigon kun sinsekva transiro inter paĝoj (opcio #2 menciita supre). Ĝiaj eblaj malavantaĝoj: ĝi ne estas subtenata en ĉiuj DBMS-oj; la rezulta sekva-peco-identigilo povas havi limigitan vivdaŭron, kiu ĝenerale ne taŭgas por efektivigado de uzantinterago (kiel ekzemple la ElasticSearch-volvlibro-API).
Kompleksa filtrado
Ni pli kompliku la taskon. Supozu, ke ekzistas postulo efektivigi la tiel nomatan facetan serĉon, kiu estas tre konata al ĉiuj el interretaj vendejoj. La supraj ekzemploj bazitaj sur la mendoj-tabelo ne estas tre ilustraj ĉi-kaze, do ni ŝanĝu al la Produkta tabelo de la AdventureWorks-datumbazo:
Kio estas la ideo malantaŭ faceta serĉo? La fakto estas, ke por ĉiu filtrila elemento montriĝas la nombro da registroj, kiuj plenumas ĉi tiun kriterion konsiderante filtrilojn elektitajn en ĉiuj aliaj kategorioj.
Ekzemple, se ni elektas la kategorion Bicikloj kaj la koloron Nigra en ĉi tiu ekzemplo, la tabelo nur montros nigrajn biciklojn, sed:
- Por ĉiu kriterio en la grupo Kategorioj, la nombro da produktoj de tiu kategorio montriĝos nigre.
- Por ĉiu kriterio de la grupo "Koloroj", la nombro da bicikloj de ĉi tiu koloro estos montrita.
Jen ekzemplo de la rezulta eligo por tiaj kondiĉoj:
Se vi ankaŭ kontrolas la kategorion "Vestaĵoj", la tabelo ankaŭ montros nigrajn vestaĵojn, kiuj estas en stoko. La nombro de nigraj produktoj en la sekcio "Koloro" ankaŭ estos rekalkulita laŭ la novaj kondiĉoj, nur en la sekcio "Kategorioj" nenio ŝanĝiĝos... Mi esperas, ke ĉi tiuj ekzemploj sufiĉas por kompreni la kutiman facetan serĉalgoritmon.
Nun ni imagu kiel ĉi tio povas esti efektivigita sur interrilata bazo. Ĉiu grupo de kriterioj, kiel Kategorio kaj Koloro, postulos apartan demandon:
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
Kio estas malbona kun ĉi tiu solvo? Ĝi estas tre simpla - ĝi ne skalas bone. Ĉiu filtrila sekcio postulas apartan demandon por kalkuli kvantojn, kaj ĉi tiuj demandoj ne estas la plej facilaj. En interretaj vendejoj, iuj kategorioj povas havi plurajn dekduojn da filtrilaj sekcioj, kio povas esti serioza agado.
Kutime post ĉi tiuj deklaroj oni proponas al mi iujn solvojn, nome:
- Kombinu ĉiujn kvantojn en unu demandon. Teknike tio eblas uzante la ŝlosilvorton UNION, sed ĝi ne multe helpos agadon - la datumbazo ankoraŭ devos ekzekuti ĉiun el la fragmentoj de nulo.
- Cache kvantoj. Ĉi tio estas sugestita al mi preskaŭ ĉiufoje kiam mi priskribas problemon. La averto estas, ke ĉi tio estas ĝenerale neebla. Ni diru, ke ni havas 10 "facetojn", ĉiu el kiuj havas 5 valorojn. Ĉi tio estas tre "modesta" situacio kompare kun tio, kion oni povas vidi en la samaj interretaj vendejoj. La elekto de unu faceta elemento influas la kvantojn en 9 aliaj, alivorte, por ĉiu kombinaĵo de kriterioj la kvantoj povas esti malsamaj. En nia ekzemplo, estas entute 50 kriterioj, kiujn la uzanto povas elekti, tial estos eblaj kombinaĵoj 250. Ne estas sufiĉe da memoro aŭ tempo por plenigi tian aron da datumoj. Ĉi tie vi povas kontraŭi kaj diri, ke ne ĉiuj kombinaĵoj estas realaj kaj la uzanto malofte elektas pli ol 5-10 kriteriojn. Jes, eblas fari maldiligentan ŝarĝon kaj kaŝmemori kvanton nur de tio, kio iam estis elektita, sed ju pli da elektoj estas, des malpli efika estos tia kaŝmemoro kaj des pli rimarkindaj estos la respondtempaj problemoj (precipe se la datumserio ŝanĝiĝas regule).
Feliĉe, tia problemo delonge havas sufiĉe efikajn solvojn, kiuj funkcias antaŭvideble sur grandaj volumoj da datumoj. Por iu ajn el ĉi tiuj opcioj, havas sencon dividi la rekalkulon de facetoj kaj ricevi la rezultojn-paĝon en du paralelajn alvokojn al la servilo kaj organizi la uzantinterfacon tiel, ke ŝarĝi datumojn per facetoj "ne malhelpas" la montradon de. Serĉrezultoj.
- Voku kompletan rekalkulon de "facetoj" kiel eble plej malofte. Ekzemple, ne rekalkulu ĉion ĉiufoje kiam la serĉkriterioj ŝanĝiĝas, sed anstataŭe trovu la totalan nombron da rezultoj, kiuj kongruas kun la nunaj kondiĉoj kaj instigu la uzanton montri ilin - "1425 rekordoj trovitaj, montri?" La uzanto povas aŭ daŭrigi ŝanĝi la serĉajn terminojn aŭ alklaki la butonon "montri". Nur en la dua kazo ĉiuj petoj por akiri rezultojn kaj rekalkuli kvantojn sur ĉiuj "facetoj" estos plenumitaj. En ĉi tiu kazo, kiel vi povas facile vidi, vi devos trakti peton por akiri la totalan nombron da rezultoj kaj ĝian optimumigon. Ĉi tiu metodo troveblas en multaj malgrandaj interretaj vendejoj. Evidente, ĉi tio ne estas panaceo por ĉi tiu problemo, sed en simplaj kazoj ĝi povas esti bona kompromiso.
- Uzu serĉilojn por trovi rezultojn kaj kalkuli aspektojn, kiel Solr, ElasticSearch, Sphinx kaj aliaj. Ĉiuj ili estas dizajnitaj por konstrui "facetojn" kaj fari tion sufiĉe efike pro la inversa indekso. Kiel funkcias serĉiloj, kial en tiaj kazoj ili estas pli efikaj ol ĝeneraluzeblaj datumbazoj, kiaj praktikoj kaj malfacilaĵoj ekzistas - ĉi tio estas temo por aparta artikolo. Ĉi tie mi ŝatus atentigi vin pri tio, ke la serĉilo ne povas anstataŭi la ĉefan datumstokadon, ĝi estas uzata kiel aldono: ĉiuj ŝanĝoj en la ĉefa datumbazo, kiuj rilatas por serĉo, estas sinkronigitaj en la serĉindekson; La serĉilo kutime interagas nur kun la serĉilo kaj ne aliras la ĉefan datumbazon. Unu el la plej gravaj punktoj ĉi tie estas kiel organizi ĉi tiun sinkronigon fidinde. Ĉio dependas de la postuloj de "reaga tempo". Se la tempo inter ŝanĝo en la ĉefa datumbazo kaj ĝia "manifestiĝo" en la serĉo ne estas kritika, vi povas krei servon, kiu serĉas lastatempe ŝanĝitajn rekordojn ĉiujn kelkajn minutojn kaj indeksas ilin. Se vi volas la plej mallongan eblan respondtempon, vi povas efektivigi ion similan por sendi ĝisdatigojn al la serĉservo.
trovoj
- Efektivigo de servilflanka paĝigo estas signifa komplikaĵo kaj nur havas sencon por rapide kreskantaj aŭ simple grandaj datumserioj. Ne ekzistas absolute preciza recepto pri kiel taksi "grandan" aŭ "rapide kreskantan", sed mi sekvus ĉi tiun aliron:
- Se ricevi kompletan kolekton de datumoj, konsiderante servilan tempon kaj rettranssendon, konvenas la agadon-postulojn normale, estas nenia signifo efektivigi paĝigon ĉe la servila flanko.
- Povas esti situacio, kie neniuj agado-problemoj estas atendataj en proksima estonteco, ĉar ekzistas malmulte da datumoj, sed la datumkolekto konstante kreskas. Se iu aro da datumoj estonte eble ne plu kontentigas la antaŭan punkton, estas pli bone komenci paĝigi tuj.
- Se ne estas strikta postulo flanke de la komerco montri la totalan nombron de rezultoj aŭ montri paĝnumerojn, kaj via sistemo ne havas serĉilon, estas pli bone ne efektivigi ĉi tiujn punktojn kaj konsideri opcion #2.
- Se estas klara postulo por faceta serĉo, vi havas du eblojn sen oferi rendimenton:
- Ne rekalkulu ĉiujn kvantojn ĉiufoje kiam la serĉkriterioj ŝanĝiĝas.
- Uzu serĉilojn kiel Solr, ElasticSearch, Sphinx kaj aliajn. Sed oni devas kompreni, ke ĝi ne povas esti anstataŭaĵo de la ĉefa datumbazo, kaj devas esti uzata kiel aldono al la ĉefa stokado por solvi serĉajn problemojn.
- Ankaŭ, en la kazo de faceta serĉo, estas senco dividi la retrovon de la serĉrezulta paĝo kaj la kalkuladon en du paralelajn petojn. Nombri kvantojn povas daŭri pli longe ol akiri rezultojn, dum la rezultoj estas pli gravaj por la uzanto.
- Se vi uzas SQL-datumbazon por serĉi, ajna kodŝanĝo rilata al ĉi tiu parto devus esti bone provita por agado sur la taŭga kvanto da datumoj (superante la volumon en la viva datumbazo). Estas ankaŭ konsilinde uzi monitoradon de demanda ekzekuttempo ĉe ĉiuj kazoj de la datumbazo, kaj precipe ĉe la "viva". Eĉ se ĉio estis bone kun demandaj planoj en la disvolva stadio, ĉar la volumo de datumoj kreskas, la situacio povas ŝanĝiĝi rimarkeble.
fonto: www.habr.com