Ien fan 'e typyske senario's yn alle applikaasjes dy't wy bekend binne, is sykjen nei gegevens neffens bepaalde kritearia en werjaan yn in maklik te lêzen foarm. D'r kinne ek ekstra opsjes wêze foar sortearjen, groepearjen en paging. De taak is yn teory triviaal, mar by it oplossen meitsje in protte ûntwikkelders in oantal flaters, dy't letter de produktiviteit lijen litte. Lit ús besykje te beskôgje ferskate opsjes foar it oplossen fan dit probleem en formulearje oanbefellings foar it kiezen fan de meast effektive útfiering.
Paging opsje #1
De ienfâldichste opsje dy't yn 't sin komt is in side-by-side werjefte fan sykresultaten yn syn meast klassike foarm.
Litte wy sizze dat jo applikaasje in relaasjedatabase brûkt. Yn dit gefal, om ynformaasje yn dit formulier wer te jaan, moatte jo twa SQL-fragen útfiere:
- Krij rigen foar de aktuele side.
- Berekkenje it totale oantal rigels dy't oerienkomme mei de sykkritearia - dit is nedich om siden te werjaan.
Litte wy nei de earste query sjen mei in test MS SQL-database as foarbyld foar 2016 tsjinner. Foar dit doel sille wy de tabel Sales.SalesOrderHeader brûke:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
De boppesteande query sil de earste 50 oarders fan 'e list werombringe, sortearre op ôfnimmende datum fan tafoeging, mei oare wurden, de 50 meast resinte oarders.
It rint fluch op 'e testbasis, mar litte wy nei it útfieringsplan en I / O-statistiken sjen:
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.Jo kinne I/O-statistiken krije foar elke query troch it kommando SET STATISTICS IO ON út te fieren yn 'e query-runtime.
Lykas jo kinne sjen fan it útfieringsplan, is de meast boarne-yntinsive opsje om alle rigen fan 'e boarnetabel te sortearjen op datum tafoege. En it probleem is dat de mear rigen yn 'e tabel ferskine, de "hurder" sil de sortearring wêze. Yn 'e praktyk moatte sokke situaasjes foarkommen wurde, dus litte wy in yndeks tafoegje oan' e datum fan tafoeging en sjen oft boarneferbrûk feroare 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.
Fansels is it in stik better wurden. Mar binne alle problemen oplost? Litte wy de query feroarje om te sykjen nei oarders wêr't de totale kosten fan guod $100 grutter binne:
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.Wy hawwe in grappige situaasje: it queryplan is net folle slimmer as it foarige, mar it eigentlike oantal logyske lêzings is hast twa kear sa grut as mei in folsleine tabelscan. D'r is in útwei - as wy in gearstalde yndeks meitsje fan in al besteande yndeks en de totale priis fan guod tafoegje as it twadde fjild, sille wy wer 165 logyske lêzingen krije:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Dizze rige foarbylden kin lang trochset wurde, mar de twa haadtinzen dy't ik hjir útdrukke wol binne:
- It tafoegjen fan in nij kritearium of sortearring oan in sykfraach kin in wichtige ynfloed hawwe op de snelheid fan de sykfraach.
- Mar as wy mar in part fan 'e gegevens moatte subtractearje, en net alle resultaten dy't oerienkomme mei de sykbegripen, binne d'r in protte manieren om sa'n query te optimalisearjen.
Litte wy no gean nei de twadde query neamd oan it begjin - dejinge dy't it oantal records telt dy't foldogge oan it sykkritearium. Litte wy itselde foarbyld nimme - sykje nei oarders dy't mear dan $100 binne:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Sjoen de hjirboppe oantsjutte gearstalde yndeks krije wy:
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.It feit dat de query troch de hiele yndeks giet is net ferrassend, om't it SubTotal-fjild net yn 'e earste posysje is, sadat de query it net kin brûke. It probleem wurdt oplost troch it tafoegjen fan in oare yndeks op it SubTotal-fjild, en as gefolch jout it allinich 48 logyske lêzingen.
Jo kinne in pear mear foarbylden jaan fan oanfragen foar it tellen fan hoemannichten, mar de essinsje bliuwt itselde: it ûntfangen fan in stikje gegevens en it tellen fan it totale bedrach binne twa fûneminteel ferskillende fersiken, en elk fereasket syn eigen maatregels foar optimalisaasje. Yn 't algemien kinne jo gjin kombinaasje fan yndeksen fine dy't like goed wurket foar beide fragen.
Dêrom is ien fan 'e wichtige easken dy't dúdlik wurde moatte by it ûntwikkeljen fan sa'n sykoplossing, oft it echt wichtich is foar in bedriuw om it totale oantal fûn objekten te sjen. It komt faak foar dat nee. En navigaasje troch spesifike sidenûmers, nei myn miening, is in oplossing mei in heul smelle omfang, om't de measte pagingscenario's der útsjen as "gean nei de folgjende side."
Paging opsje #2
Lit ús oannimme dat brûkers net skele oer it witten fan it totale oantal fûn objekten. Litte wy besykje de sykside te ferienfâldigjen:
Yn feite is it ienige ding dat feroare is dat d'r gjin manier is om nei spesifike sidenûmers te navigearjen, en no hoecht dizze tabel net te witten hoefolle d'r kin wêze om it wer te jaan. Mar de fraach ûntstiet - hoe wit de tabel as d'r gegevens binne foar de folgjende side (om de keppeling "Folgjende" korrekt wer te jaan)?
It antwurd is heul ienfâldich: jo kinne fan 'e databank ien mear rekord lêze dan nedich is foar werjaan, en de oanwêzigens fan dit "oanfoljende" rekord sil sjen litte oft der in folgjende diel is. Op dizze manier hoege jo mar ien fersyk út te fieren om ien side mei gegevens te krijen, wat de prestaasjes signifikant ferbettert en it makliker makket om sokke funksjonaliteit te stypjen. Yn myn praktyk wie d'r in gefal doe't it wegerjen fan it tellen fan it totale oantal records de levering fan resultaten mei 4-5 kear fersnelle.
D'r binne ferskate opsjes foar brûkersynterface foar dizze oanpak: kommando's "werom" en "foarút", lykas yn it foarbyld hjirboppe, in knop "mear laden", dy't gewoan in nij diel tafoegje oan 'e werjûn resultaten, "ûneinige rôlje", dy't wurket op it prinsipe fan "load more" ", mar it sinjaal om it folgjende diel te krijen is foar de brûker om alle werjûn resultaten nei it ein te rôljen. Wat de fisuele oplossing ek is, bliuwt it prinsipe fan sampling fan gegevens itselde.
Nuânses fan paging ymplemintaasje
Alle boppesteande queryfoarbylden brûke de "offset + count" oanpak, as de query sels spesifisearret yn hokker folchoarder de resultaat rigen en hoefolle rigen moatte wurde weromjûn. Lit ús earst sjen hoe't it bêste yn dit gefal it trochjaan fan parameters kin organisearje. Yn 'e praktyk bin ik ferskate metoaden tsjinkaam:
- It serialnûmer fan de frege side (pageIndex), sidegrutte (pageSize).
- It searjenûmer fan it earste record dat weromjûn wurdt (startIndex), it maksimum oantal records yn it resultaat (count).
- It folchoardernûmer fan it earst werom te jaan record (startIndex), it folchoardernûmer fan it lêste record dat weromjûn wurdt (endIndex).
Op it earste each kin it lykje dat dit sa elemintêr is dat der gjin ferskil is. Mar dit is net sa - de meast handige en universele opsje is de twadde (startIndex, count). D'r binne ferskate redenen foar dit:
- Foar de hjirboppe jûne oanpak foar korrektyflêzen fan +1 yngong is de earste opsje mei pageIndex en pageSize ekstreem ûngemaklik. Wy wolle bygelyks 50 berjochten per side sjen litte. Neffens it boppesteande algoritme moatte jo noch ien rekord lêze dan nedich. As dizze "+1" net wurdt ymplementearre op 'e tsjinner, docht bliken dat foar de earste side wy moatte freegje records fan 1 oant 51, foar de twadde - fan 51 oant 101, ensfh. As jo in sidegrutte fan 51 oantsjutte en pageIndex ferheegje, dan komt de twadde side werom fan 52 nei 102, ensfh. Dêrom, yn 'e earste opsje, is de ienige manier om in knop goed te ymplementearjen om nei de folgjende side te gean, de tsjinner de "ekstra" rigel te hawwen, dy't in heul ymplisite nuânse sil wêze.
- De tredde opsje makket hielendal gjin sin, om't jo fragen yn 'e measte databases wolle útfiere, moatte jo de telling noch trochjaan ynstee fan de yndeks fan it lêste record. It subtrahearjen fan startIndex fan endIndex kin in ienfâldige arithmetyske operaasje wêze, mar it is hjir oerstallich.
No moatte wy de neidielen beskriuwe fan it ymplementearjen fan paging fia "offset + kwantiteit":
- It opheljen fan elke folgjende side sil djoerder en stadiger wêze as de foarige, om't de databank noch troch alle records "fan it begjin" moat gean neffens de syk- en sortearringskritearia, en dan stopje by it winske fragmint.
- Net alle DBMS's kinne dizze oanpak stypje.
Der binne alternativen, mar se binne ek ûnfolslein. De earste fan dizze oanpak wurdt "keyset paging" of "sykje metoade" neamd en is as folget: nei it ûntfangen fan in diel, kinne jo de fjildwearden ûnthâlde yn 'e lêste record op' e side, en brûke se dan om te krijen it folgjende diel. Wy hawwe bygelyks de folgjende query útfierd:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
En yn it lêste record krigen wy de besteldatumwearde '2014-06-29'. Om dan de folgjende side te krijen kinne jo besykje dit te dwaan:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
It probleem is dat OrderDate in net-unyk fjild is en de boppesteande betingst sil wierskynlik in protte fereaske rigen misse. Om unambiguity oan dizze query ta te foegjen, moatte jo in unyk fjild tafoegje oan 'e betingst (oannimme dat 75074 de lêste wearde is fan 'e primêre kaai fan it earste diel):
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
Dizze opsje sil goed wurkje, mar yn 't algemien sil it lestich wêze om te optimalisearjen, om't de betingst in OR-operator befettet. As de wearde fan 'e primêre kaai ferheget as OrderDate ferheget, dan kin de betingst ferienfâldige wurde troch allinich in filter te litten troch SalesOrderID. Mar as d'r gjin strikte korrelaasje is tusken de wearden fan 'e primêre kaai en it fjild wêrmei't it resultaat wurdt sorteare, kin dizze OF yn de measte DBMS's net foarkommen wurde. In útsûndering wêrfan ik wit is PostgreSQL, dy't folslein tuple-fergelikingen stipet, en de boppesteande betingst kin skreaun wurde as "WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074)". Sjoen in gearstalde kaai mei dizze twa fjilden, soe in fraach lykas dizze frij maklik wêze moatte.
In twadde alternative oanpak is te finen, bygelyks yn of - as in fersyk, neist gegevens, in spesjale identifier jout wêrmei jo it folgjende diel fan gegevens kinne krije. As dizze identifier in ûnbeheinde libbensdoer hat (lykas yn Comsos DB), dan is dit in geweldige manier om paging te ymplementearjen mei opfolgjende oergong tusken siden (opsje #2 hjirboppe neamd). Syn mooglike neidielen: it wurdt net stipe yn alle DBMSs; de resultearjende folgjende-chunk-identifikaasje kin in beheind libben hawwe, wat oer it algemien net geskikt is foar it útfieren fan brûkersynteraksje (lykas de ElasticSearch scroll API).
Komplekse filtering
Litte wy de taak fierder komplisearje. Stel dat d'r in eask is om de saneamde faceted sykjen út te fieren, dy't elkenien fan online winkels tige bekend is. De boppesteande foarbylden basearre op 'e oardertabel binne yn dit gefal net heul yllustratyf, dus litte wy oerskeakelje nei de produkttabel fan' e AdventureWorks-database:
Wat is it idee efter faceted sykjen? It feit is dat foar elk filterelemint it oantal records te sjen is dat oan dit kritearium foldocht rekken hâldend mei filters selektearre yn alle oare kategoryen.
As wy bygelyks de kategory Bikes en de kleur Swart yn dit foarbyld selektearje, sil de tabel allinich swarte fytsen sjen litte, mar:
- Foar elk kritearium yn 'e Kategoryen-groep sil it oantal produkten út dy kategory yn swart wurde toand.
- Foar elk kritearium fan 'e groep "Kleuren" sil it oantal fytsen fan dizze kleur wurde toand.
Hjir is in foarbyld fan 'e resultaatútfier foar sokke betingsten:
As jo ek de kategory "Klean" kontrolearje, sil de tabel ek swarte klean sjen dy't op foarried binne. It oantal swarte produkten yn 'e seksje "Kleur" sil ek opnij berekkene wurde neffens de nije betingsten, allinich yn 'e seksje "Kategoryen" sil neat feroarje ... Ik hoopje dat dizze foarbylden genôch binne om it gewoane faceted sykalgoritme te begripen.
Lit ús no yntinke hoe't dit kin wurde útfierd op in relaasje basis. Elke groep kritearia, lykas Kategory en Kleur, sil in aparte query fereaskje:
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
Wat is der mis mei dizze oplossing? It is heul ienfâldich - it skaalt net goed. Elke filterseksje fereasket in aparte query foar it berekkenjen fan hoemannichten, en dizze queries binne net de maklikste. Yn online winkels kinne guon kategoryen ferskate tsientallen filterseksjes hawwe, wat in serieus prestaasjeprobleem kin wêze.
Meastentiids wurde ik nei dizze útspraken wat oplossingen oanbean, nammentlik:
- Kombinearje alle kwantiteiten yn ien query. Technysk is dit mooglik mei it UNION-kaaiwurd, mar it sil de prestaasjes net folle helpe - de databank sil noch elk fan 'e fragminten fanôf it begjin moatte útfiere.
- Cache hoemannichten. Dit wurdt my foarsteld hast elke kear as ik in probleem beskriuw. De caveat is dat dit oer it algemien ûnmooglik is. Litte wy sizze dat wy 10 "facetten" hawwe, dy't elk 5 wearden hawwe. Dit is in heul "beskieden" situaasje yn ferliking mei wat te sjen is yn deselde online winkels. De kar fan ien faset elemint beynfloedet de hoemannichten yn 9 oare, mei oare wurden, foar elke kombinaasje fan kritearia de hoemannichten kin wêze oars. Yn ús foarbyld binne d'r in totaal fan 50 kritearia dy't de brûker kin selektearje, dus d'r sille 250 mooglike kombinaasjes wêze D'r is net genôch ûnthâld of tiid om sa'n array fan gegevens te foljen. Hjir kinne jo beswier meitsje en sizze dat net alle kombinaasjes echt binne en de brûker selden mear as 5-10 kritearia selektearret. Ja, it is mooglik om loai te laden en in hoemannichte te cache fan allinich wat ea is selektearre, mar hoe mear seleksjes der binne, hoe minder effisjint sa'n cache sil wêze en hoe mear opfallend de antwurdtiidproblemen sille wêze (benammen as de gegevensset feroaret regelmjittich).
Gelokkich hat sa'n probleem al lang frij effektive oplossingen hân dy't foarsisber wurkje op grutte folumes fan gegevens. Foar ien fan dizze opsjes is it logysk om de herberekkening fan fasetten en it ûntfangen fan 'e resultatenside te dielen yn twa parallelle oproppen nei de tsjinner en de brûkersynterface op sa'n manier te organisearjen dat it laden fan gegevens troch fasetten "net ynterfereart" mei de werjefte fan sykresultaten.
- Neam in folsleine herberekkening fan "facetten" sa komselden mooglik. Berekkenje bygelyks net alles elke kear as de sykkritearia wizigje, mar fyn it totale oantal resultaten dat oerienkomt mei de hjoeddeistige betingsten en freegje de brûker om se te sjen - "1425 records fûn, sjen litte?" De brûker kin trochgean mei it feroarjen fan de sykbegripen of klikje op de knop "sjen litte". Allinich yn it twadde gefal sille alle oanfragen foar it krijen fan resultaten en it opnij berekkenjen fan hoemannichten op alle "facetten" wurde útfierd. Yn dit gefal, sa't jo maklik kinne sjen, sille jo moatte omgean mei in fersyk om it totale oantal resultaten te krijen en har optimalisaasje. Dizze metoade is te finen yn in protte lytse online winkels. Fansels is dit gjin panacee foar dit probleem, mar yn ienfâldige gefallen kin it in goed kompromis wêze.
- Brûk sykmasines om resultaten te finen en fasetten te tellen, lykas Solr, ElasticSearch, Sphinx en oaren. Allegear binne ûntworpen om "facetten" te bouwen en dit frij effisjint te dwaan troch de omkearde yndeks. Hoe sykmasjines wurkje, wêrom binne se yn sokke gefallen effektiver as databases foar algemiene doelen, hokker praktiken en falkûlen binne der - dit is in ûnderwerp foar in apart artikel. Hjir wol ik jo oandacht op it feit dat de sykmasine gjin ferfanging kin wêze foar de haadgegevensopslach, it wurdt brûkt as tafoeging: alle wizigingen yn 'e haaddatabase dy't relevant binne foar sykjen wurde syngronisearre yn 'e sykindex; De sykmasine ynteraktearret normaal allinich mei de sykmasine en hat gjin tagong ta de haaddatabase. Ien fan 'e wichtichste punten hjir is hoe't jo dizze syngronisaasje betrouber organisearje. It hinget allegear ôf fan 'e easken foar "reaksjetiid". As de tiid tusken in feroaring yn 'e wichtichste databank en syn "manifestaasje" yn it sykjen is net kritysk, kinne jo meitsje in tsjinst dy't siket nei koartlyn feroare records alle pear minuten en yndeksearret se. As jo de koartst mooglike reaksjetiid wolle, kinne jo soksawat implementearje om updates nei de syktsjinst te stjoeren.
befinings
- It ymplementearjen fan paging oan 'e tsjinner is in wichtige komplikaasje en makket allinich sin foar rap groeiende as gewoan grutte datasets. D'r is gjin absolút krekt resept foar hoe't jo "grut" of "rapstgroeiend" kinne evaluearje, mar ik soe dizze oanpak folgje:
- As it ûntfangen fan in folsleine kolleksje fan gegevens, rekken hâldend mei tsjinner tiid en netwurk oerdracht, past de prestaasjes easken normaal, der is gjin punt in útfieren paging op de tsjinner kant.
- D'r kin in situaasje wêze wêr't yn 'e heine takomst gjin prestaasjesproblemen wurde ferwachte, om't d'r net folle gegevens binne, mar de gegevenssammeling groeit konstant. As guon set gegevens yn 'e takomst miskien net mear oan it foarige punt foldogge, is it better om daliks te begjinnen mei paging.
- As d'r gjin strikte eask is fan 'e kant fan it bedriuw om it totale oantal resultaten te werjaan of sidenûmers te werjaan, en jo systeem hat gjin sykmasjine, is it better om dizze punten net te ymplementearjen en opsje #2 te beskôgjen.
- As d'r in dúdlike eask is foar fasetten sykjen, hawwe jo twa opsjes sûnder prestaasjes op te offerjen:
- Berekkenje net alle hoemannichten elke kear as de sykkritearia feroaret.
- Brûk sykmasines lykas Solr, ElasticSearch, Sphinx en oaren. Mar it moat wurde begrepen dat it kin net in ferfanging foar de wichtichste databank, en moat brûkt wurde as in oanfolling op de wichtichste opslach foar it oplossen fan sykproblemen.
- Ek yn it gefal fan faceted sykjen is it sinfol om it opheljen fan 'e sykresultatenside en it tellen te splitsen yn twa parallelle fersiken. It tellen fan hoemannichten kin langer duorje dan it krijen fan resultaten, wylst de resultaten wichtiger binne foar de brûker.
- As jo in SQL-database brûke foar it sykjen, moat elke koadeferoaring yn ferbân mei dit diel goed wurde hifke foar prestaasjes op 'e passende hoemannichte gegevens (mei it folume yn' e live databank te boppe). It is ek oan te rieden om monitoaring fan query-útfiertiid te brûken op alle eksimplaren fan 'e databank, en benammen op' e "live". Sels as alles goed wie mei queryplannen yn 'e ûntwikkelingsstadium, as it folume fan gegevens groeit, kin de situaasje merkber feroarje.
Boarne: www.habr.com
