Et av de typiske scenariene i alle applikasjonene vi er kjent med er å søke etter data i henhold til bestemte kriterier og vise dem i en lettlest form. Det kan også være flere alternativer for sortering, gruppering og personsøking. Oppgaven er i teorien triviell, men når de løser den, gjør mange utviklere en rekke feil, som senere får produktiviteten til å lide. La oss prøve å vurdere ulike alternativer for å løse dette problemet og formulere anbefalinger for å velge den mest effektive implementeringen.
Personsøkeralternativ #1
Det enkleste alternativet du tenker på, er en side-for-side-visning av søkeresultater i sin mest klassiske form.
La oss si at applikasjonen din bruker en relasjonsdatabase. I dette tilfellet, for å vise informasjon i dette skjemaet, må du kjøre to SQL-spørringer:
- Få rader for gjeldende side.
- Beregn det totale antallet linjer som tilsvarer søkekriteriene - dette er nødvendig for å vise sider.
La oss se på den første spørringen ved å bruke en test MS SQL-database som et eksempel for 2016 server. Til dette formålet vil vi bruke tabellen Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Spørsmålet ovenfor vil returnere de første 50 bestillingene fra listen, sortert etter synkende dato for tillegg, med andre ord, de 50 siste bestillingene.
Den kjører raskt på testbasen, men la oss se på utførelsesplanen og I/O-statistikken:
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.Du kan få I/U-statistikk for hver spørring ved å kjøre kommandoen SET STATISTICS IO ON i spørringens kjøretid.
Som du kan se fra utførelsesplanen, er det mest ressurskrevende alternativet å sortere alle rader i kildetabellen etter dato lagt til. Og problemet er at jo flere rader som vises i tabellen, jo "vanskeligere" blir sorteringen. I praksis bør slike situasjoner unngås, så la oss legge til en indeks til datoen for tillegg og se om ressursforbruket har endret seg:
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.
Det er klart at det har blitt mye bedre. Men er alle problemer løst? La oss endre søket for å søke etter bestillinger der den totale varekostnaden overstiger $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.Vi har en morsom situasjon: spørringsplanen er ikke mye dårligere enn den forrige, men det faktiske antallet logiske avlesninger er nesten dobbelt så stort som med en full tabellskanning. Det er en vei ut - hvis vi lager en sammensatt indeks fra en allerede eksisterende indeks og legger til totalprisen på varer som det andre feltet, vil vi igjen få 165 logiske lesninger:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Denne serien med eksempler kan fortsette i lang tid, men de to hovedtankene jeg vil uttrykke her er:
- Å legge til et nytt kriterium eller sorteringsrekkefølge i et søk kan ha en betydelig innvirkning på søkets hastighet.
- Men hvis vi bare trenger å trekke fra deler av dataene, og ikke alle resultatene som samsvarer med søkeordene, er det mange måter å optimalisere et slikt søk på.
La oss nå gå videre til den andre spørringen som ble nevnt helt i begynnelsen - den som teller antall poster som tilfredsstiller søkekriteriet. La oss ta det samme eksempelet – søker etter bestillinger som er mer enn $100:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Gitt den sammensatte indeksen som er angitt ovenfor, får vi:
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.Det faktum at spørringen går gjennom hele indeksen er ikke overraskende, siden SubTotal-feltet ikke er i første posisjon, så spørringen kan ikke bruke den. Problemet løses ved å legge til en annen indeks på SubTotal-feltet, og som et resultat gir det bare 48 logiske lesninger.
Du kan gi noen flere eksempler på forespørsler om å telle mengder, men essensen forblir den samme: å motta et stykke data og telle det totale beløpet er to fundamentalt forskjellige forespørsler, og hver krever sine egne tiltak for optimalisering. Generelt vil du ikke kunne finne en kombinasjon av indekser som fungerer like godt for begge søkene.
Et av de viktige kravene som bør avklares ved utvikling av en slik søkeløsning er derfor om det virkelig er viktig for en virksomhet å se det totale antallet gjenstander som er funnet. Det hender ofte at nei. Og navigering etter spesifikke sidetall, etter min mening, er en løsning med et veldig smalt omfang, siden de fleste personsøkingsscenarier ser ut som "gå til neste side."
Personsøkeralternativ #2
La oss anta at brukere ikke bryr seg om å vite det totale antallet gjenstander som er funnet. La oss prøve å forenkle søkesiden:
Faktisk er det eneste som har endret seg at det ikke er mulig å navigere til spesifikke sidetall, og nå trenger ikke denne tabellen å vite hvor mange det kan være for å vise den. Men spørsmålet oppstår - hvordan vet tabellen om det er data for neste side (for å vise koblingen "Neste" riktig)?
Svaret er veldig enkelt: du kan lese en post til fra databasen enn det som er nødvendig for visning, og tilstedeværelsen av denne "ekstra" posten vil vise om det er en neste del. På denne måten trenger du bare å kjøre én forespørsel for å få én side med data, noe som forbedrer ytelsen betydelig og gjør det enklere å støtte slik funksjonalitet. I min praksis var det et tilfelle da nektet å telle det totale antallet poster fremskyndet leveringen av resultater med 4-5 ganger.
Det er flere brukergrensesnittalternativer for denne tilnærmingen: "tilbake" og "frem"-kommandoer, som i eksemplet ovenfor, en "last mer"-knapp, som ganske enkelt legger til en ny del til de viste resultatene, "uendelig rull", som fungerer på prinsippet om "last mer" ", men signalet for å få neste del er for brukeren å rulle alle de viste resultatene til slutten. Uansett hvilken visuell løsning, forblir prinsippet for datasampling det samme.
Nyanser av personsøkingsimplementering
Alle spørringseksemplene gitt ovenfor bruker "offset + count"-tilnærmingen, når spørringen selv spesifiserer i hvilken rekkefølge resultatradene og hvor mange rader som må returneres. La oss først se på hvordan du best kan organisere parameteroverføring i dette tilfellet. I praksis har jeg kommet over flere metoder:
- Serienummeret til den forespurte siden (pageIndex), sidestørrelse (pageSize).
- Serienummeret til den første posten som skal returneres (startIndex), det maksimale antallet poster i resultatet (telling).
- Sekvensnummeret til den første posten som skal returneres (startIndex), sekvensnummeret til den siste posten som skal returneres (endIndex).
Ved første øyekast kan det virke som om dette er så elementært at det ikke er noen forskjell. Men dette er ikke slik - det mest praktiske og universelle alternativet er det andre (startindeks, antall). Det er flere grunner til dette:
- For korrekturlesingsmetoden for +1-oppføringer gitt ovenfor, er det første alternativet med sideindeks og sidestørrelse ekstremt upraktisk. For eksempel ønsker vi å vise 50 innlegg per side. I henhold til algoritmen ovenfor, må du lese én post til enn nødvendig. Hvis denne "+1" ikke er implementert på serveren, viser det seg at for den første siden må vi be om poster fra 1 til 51, for den andre - fra 51 til 101, etc. Hvis du angir en sidestørrelse på 51 og øker sideindeksen, vil den andre siden gå tilbake fra 52 til 102 osv. Følgelig, i det første alternativet, er den eneste måten å implementere en knapp for å gå til neste side på å få serveren til å korrekturlese den "ekstra" linjen, som vil være en veldig implisitt nyanse.
- Det tredje alternativet gir ikke mening i det hele tatt, siden for å kjøre spørringer i de fleste databaser, må du fortsatt passere tellingen i stedet for indeksen til den siste posten. Selv om å trekke startIndex fra endIndex er en enkel aritmetisk operasjon, er det overflødig her.
Nå skal vi beskrive ulempene ved å implementere personsøking gjennom "offset + mengde":
- Å hente hver påfølgende side vil være dyrere og tregere enn den forrige, fordi databasen fortsatt må gå gjennom alle postene "fra begynnelsen" i henhold til søke- og sorteringskriteriene, og deretter stoppe ved ønsket fragment.
- Ikke alle DBMS-er kan støtte denne tilnærmingen.
Det finnes alternativer, men de er også ufullkomne. Den første av disse tilnærmingene kalles "keyset paging" eller "seek method" og er som følger: etter å ha mottatt en del, kan du huske feltverdiene i den siste posten på siden, og deretter bruke dem til å få neste porsjon. For eksempel kjørte vi følgende spørring:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Og i den siste posten fikk vi ordredatoverdien '2014-06-29'. Så for å få neste side kan du prøve å gjøre dette:
SELECT * FROM Sales.SalesOrderHeader
WHERE OrderDate < '2014-06-29'
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Problemet er at OrderDate er et ikke-unikt felt, og betingelsen som er spesifisert ovenfor vil sannsynligvis gå glipp av mange nødvendige rader. For å legge til entydighet til denne spørringen, må du legge til et unikt felt i betingelsen (anta at 75074 er den siste verdien av primærnøkkelen fra den første delen):
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
Dette alternativet vil fungere riktig, men generelt vil det være vanskelig å optimalisere siden tilstanden inneholder en OR-operator. Hvis verdien av primærnøkkelen øker når OrderDate øker, kan betingelsen forenkles ved å la bare et filter ligge etter SalesOrderID. Men hvis det ikke er noen streng korrelasjon mellom verdiene til primærnøkkelen og feltet som resultatet er sortert etter, kan ikke denne ELLER unngås i de fleste DBMS-er. Et unntak jeg kjenner til er PostgreSQL, som fullt ut støtter tuppelsammenligninger, og betingelsen ovenfor kan skrives som "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Gitt en sammensatt nøkkel med disse to feltene, bør et søk som dette være ganske enkelt.
En annen alternativ tilnærming finnes for eksempel i eller — når en forespørsel, i tillegg til data, returnerer en spesiell identifikator som du kan få neste del av data med. Hvis denne identifikatoren har en ubegrenset levetid (som i Comsos DB), så er dette en fin måte å implementere personsøking med sekvensiell overgang mellom sider (alternativ #2 nevnt ovenfor). Dens mulige ulemper: den støttes ikke i alle DBMS-er; den resulterende identifikatoren for neste del kan ha en begrenset levetid, som vanligvis ikke er egnet for å implementere brukerinteraksjon (for eksempel ElasticSearch scroll API).
Kompleks filtrering
La oss komplisere oppgaven ytterligere. Anta at det er et krav om å implementere det såkalte fasetterte søket, som er veldig kjent for alle fra nettbutikker. Eksemplene ovenfor basert på ordretabellen er ikke særlig illustrerende i dette tilfellet, så la oss bytte til produkttabellen fra AdventureWorks-databasen:
Hva er ideen bak fasettert søk? Faktum er at for hvert filterelement vises antallet poster som oppfyller dette kriteriet tar hensyn til filtre valgt i alle andre kategorier.
Hvis vi for eksempel velger kategorien Sykler og fargen Svart i dette eksemplet, vil tabellen bare vise svarte sykler, men:
- For hvert kriterium i kategorier-gruppen vil antall produkter fra den kategorien vises i svart.
- For hvert kriterium i "Farger"-gruppen vil antall sykler i denne fargen vises.
Her er et eksempel på resultatet for slike forhold:
Hvis du i tillegg sjekker kategorien "Klær", vil tabellen også vise svarte klær som er på lager. Antallet svarte produkter i "Farge"-delen vil også bli beregnet på nytt i henhold til de nye betingelsene, bare i "Kategorier"-delen vil ingenting endre seg... Jeg håper disse eksemplene er nok til å forstå den vanlige fasetterte søkealgoritmen.
La oss nå forestille oss hvordan dette kan implementeres på et relasjonelt grunnlag. Hver gruppe med kriterier, for eksempel kategori og farge, vil kreve en separat spørring:
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
Hva er galt med denne løsningen? Det er veldig enkelt - det skalerer ikke godt. Hver filterdel krever en separat spørring for å beregne mengder, og disse spørringene er ikke de enkleste. I nettbutikker kan noen kategorier ha flere dusin filterseksjoner, noe som kan være et alvorlig problem for ytelsen.
Vanligvis etter disse uttalelsene blir jeg tilbudt noen løsninger, nemlig:
- Kombiner alle mengdetellinger til ett søk. Teknisk sett er dette mulig ved å bruke UNION-nøkkelordet, men det hjelper ikke mye på ytelsen - databasen vil fortsatt måtte kjøre hvert av fragmentene fra bunnen av.
- Cache-mengder. Dette blir foreslått for meg nesten hver gang jeg beskriver et problem. Forbeholdet er at dette generelt er umulig. La oss si at vi har 10 "fasetter", som hver har 5 verdier. Dette er en veldig «beskjeden» situasjon sammenlignet med det man kan se i de samme nettbutikkene. Valget av ett fasettelement påvirker mengdene i 9 andre, med andre ord, for hver kombinasjon av kriterier kan mengdene være forskjellige. I vårt eksempel er det totalt 50 kriterier som brukeren kan velge, så det vil være 250 mulige kombinasjoner Det er ikke nok minne eller tid til å fylle en slik rekke data. Her kan du protestere og si at ikke alle kombinasjoner er ekte og brukeren velger sjelden mer enn 5-10 kriterier. Ja, det er mulig å gjøre lat lasting og cache en mengde av bare det som noen gang har blitt valgt, men jo flere valg det er, jo mindre effektiv vil en slik cache være, og jo mer merkbare vil responstidsproblemene være (spesielt hvis datasett endres regelmessig).
Heldigvis har et slikt problem lenge hatt ganske effektive løsninger som fungerer forutsigbart på store datamengder. For alle disse alternativene er det fornuftig å dele omberegningen av fasetter og mottak av resultatsiden i to parallelle anrop til serveren og organisere brukergrensesnittet på en slik måte at lasting av data etter fasetter "ikke forstyrrer" visningen av Søkeresultater.
- Kall en fullstendig omberegning av "fasetter" så sjelden som mulig. For eksempel, ikke kalkuler alt på nytt hver gang søkekriteriene endres, men finn i stedet det totale antallet resultater som samsvarer med gjeldende betingelser og be brukeren om å vise dem - "1425 poster funnet, vis?" Brukeren kan enten fortsette å endre søkeordene eller klikke på "vis"-knappen. Bare i det andre tilfellet vil alle forespørsler om å oppnå resultater og omberegning av mengder på alle "fasetter" bli utført. I dette tilfellet, som du lett kan se, må du håndtere en forespørsel for å få det totale antallet resultater og optimaliseringen av det. Denne metoden finner du i mange små nettbutikker. Dette er selvsagt ikke et universalmiddel for dette problemet, men i enkle tilfeller kan det være et godt kompromiss.
- Bruk søkemotorer for å finne resultater og telle fasetter, som Solr, ElasticSearch, Sphinx og andre. Alle av dem er designet for å bygge "fasetter" og gjør dette ganske effektivt på grunn av den inverterte indeksen. Hvordan søkemotorer fungerer, hvorfor de i slike tilfeller er mer effektive enn generelle databaser, hvilke praksiser og fallgruver det er - dette er et emne for en egen artikkel. Her vil jeg gjøre oppmerksom på at søkemotoren ikke kan erstatte hoveddatalagringen, den brukes som et tillegg: eventuelle endringer i hoveddatabasen som er relevante for søk synkroniseres inn i søkeindeksen; Søkemotoren samhandler vanligvis bare med søkemotoren og får ikke tilgang til hoveddatabasen. Et av de viktigste punktene her er hvordan du kan organisere denne synkroniseringen pålitelig. Alt avhenger av kravene til "reaksjonstid". Hvis tiden mellom en endring i hoveddatabasen og dens "manifestasjon" i søket ikke er kritisk, kan du opprette en tjeneste som søker etter nylig endrede poster med noen minutters mellomrom og indekserer dem. Hvis du vil ha kortest mulig responstid, kan du implementere noe som for å sende oppdateringer til søketjenesten.
Funn
- Implementering av personsøking på serversiden er en betydelig komplikasjon og gir bare mening for raskt voksende eller ganske enkelt store datasett. Det er ingen absolutt eksakt oppskrift på hvordan man vurderer "stor" eller "rasktvoksende", men jeg vil følge denne tilnærmingen:
- Hvis mottak av en komplett samling av data, tatt i betraktning servertid og nettverksoverføring, passer til ytelseskravene normalt, er det ingen vits i å implementere personsøking på serversiden.
- Det kan oppstå en situasjon der det ikke forventes noen ytelsesproblemer i nær fremtid, siden det er lite data, men datainnsamlingen vokser stadig. Hvis et sett med data i fremtiden kanskje ikke lenger tilfredsstiller det forrige punktet, er det bedre å begynne å søke med en gang.
- Hvis det ikke er noen strenge krav fra virksomhetens side om å vise det totale antallet resultater eller å vise sidetall, og systemet ditt ikke har en søkemotor, er det bedre å ikke implementere disse punktene og vurdere alternativ #2.
- Hvis det er et klart krav til fasettert søk, har du to alternativer uten å ofre ytelsen:
- Ikke rekalkuler alle mengder hver gang søkekriteriene endres.
- Bruk søkemotorer som Solr, ElasticSearch, Sphinx og andre. Men det skal forstås at det ikke kan være en erstatning for hoveddatabasen, og bør brukes som et tillegg til hovedlageret for å løse søkeproblemer.
- I tilfelle fasettert søk er det også fornuftig å dele innhentingen av søkeresultatsiden og tellingen i to parallelle forespørsler. Å telle mengder kan ta lengre tid enn å oppnå resultater, mens resultatene er viktigere for brukeren.
- Hvis du bruker en SQL-database for søk, bør alle kodeendringer relatert til denne delen testes godt for ytelse på riktig mengde data (overskrider volumet i live-databasen). Det er også tilrådelig å bruke overvåking av spørringsutførelsestid på alle forekomster av databasen, og spesielt på den "live". Selv om alt var bra med spørringsplaner på utviklingsstadiet, kan situasjonen endre seg merkbart etter hvert som datavolumet vokser.
Kilde: www.habr.com