Et af de typiske scenarier i alle de applikationer, vi kender, er at søge efter data efter bestemte kriterier og vise dem i en letlæselig form. Der kan også være yderligere muligheder for sortering, gruppering og sidesøgning. Opgaven er i teorien triviel, men når de løser den, laver mange udviklere en række fejl, som senere får produktiviteten til at lide. Lad os prøve at overveje forskellige muligheder for at løse dette problem og formulere anbefalinger til at vælge den mest effektive implementering.
Personsøgningsmulighed #1
Den enkleste mulighed, der kommer til at tænke på, er en side-for-side-visning af søgeresultater i sin mest klassiske form.
Lad os sige, at din applikation bruger en relationsdatabase. I dette tilfælde skal du køre to SQL-forespørgsler for at vise oplysninger i denne formular:
- Hent rækker til den aktuelle side.
- Beregn det samlede antal linjer svarende til søgekriterierne - dette er nødvendigt for at vise sider.
Lad os se på den første forespørgsel ved at bruge en test MS SQL-database som eksempel til 2016 server. Til dette formål vil vi bruge tabellen Sales.SalesOrderHeader:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Ovenstående forespørgsel returnerer de første 50 ordrer fra listen, sorteret efter faldende tilføjelsesdato, med andre ord de 50 seneste ordrer.
Det kører hurtigt på testbasen, men lad os se på udfø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/O-statistik for hver forespørgsel ved at køre kommandoen SET STATISTICS IO ON i forespørgslens runtime.
Som du kan se fra udførelsesplanen, er den mest ressourcekrævende mulighed at sortere alle rækker i kildetabellen efter tilføjet dato. Og problemet er, at jo flere rækker der vises i tabellen, jo "sværere" bliver sorteringen. I praksis bør sådanne situationer undgås, så lad os tilføje et indeks til datoen for tilføjelse og se, om ressourceforbruget har ændret sig:
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 tydeligvis blevet meget bedre. Men er alle problemer løst? Lad os ændre forespørgslen til at søge efter ordrer, hvor den samlede varepris overstiger 100 USD:
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 sjov situation: forespørgselsplanen er ikke meget værre end den forrige, men det faktiske antal logiske læsninger er næsten dobbelt så stort som ved en fuld tabelscanning. Der er en vej ud - hvis vi laver et sammensat indeks fra et allerede eksisterende indeks og tilføjer den samlede pris på varer som det andet felt, får vi igen 165 logiske læsninger:
CREATE INDEX IX_SalesOrderHeader_OrderDate_SubTotal on Sales.SalesOrderHeader(OrderDate, SubTotal);
Denne række eksempler kan fortsættes i lang tid, men de to hovedtanker, som jeg vil udtrykke her, er:
- Tilføjelse af ethvert nyt kriterium eller sorteringsrækkefølge til en søgeforespørgsel kan have en betydelig indvirkning på søgeforespørgslens hastighed.
- Men hvis vi kun skal trække en del af dataene fra, og ikke alle de resultater, der matcher søgetermerne, er der mange måder at optimere en sådan forespørgsel på.
Lad os nu gå videre til den anden forespørgsel, der blev nævnt i begyndelsen - den, der tæller antallet af poster, der opfylder søgekriteriet. Lad os tage det samme eksempel - søgning efter ordrer, der er mere end $100:
SELECT COUNT(1) FROM Sales.SalesOrderHeader
WHERE SubTotal > 100
Givet det sammensatte indeks angivet ovenfor, opnå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.At forespørgslen går gennem hele indekset er ikke overraskende, da SubTotal-feltet ikke er i første position, så forespørgslen kan ikke bruge det. Problemet løses ved at tilføje endnu et indeks på SubTotal-feltet, og som følge heraf giver det kun 48 logiske læsninger.
Du kan give et par flere eksempler på anmodninger om optælling af mængder, men essensen forbliver den samme: at modtage et stykke data og tælle det samlede beløb er to grundlæggende forskellige anmodninger, og hver kræver sine egne tiltag for optimering. Generelt vil du ikke være i stand til at finde en kombination af indekser, der fungerer lige godt for begge forespørgsler.
Derfor er et af de vigtige krav, der bør afklares, når man udvikler en sådan søgeløsning, om det virkelig er vigtigt for en virksomhed at se det samlede antal fundne objekter. Det sker ofte, at nej. Og navigation efter specifikke sidetal er efter min mening en løsning med et meget snævert omfang, da de fleste personsøgningsscenarier ser ud som "gå til næste side."
Personsøgningsmulighed #2
Lad os antage, at brugerne er ligeglade med at kende det samlede antal fundne objekter. Lad os prøve at forenkle søgesiden:
Faktisk er det eneste, der har ændret sig, at der ikke er nogen måde at navigere til specifikke sidetal på, og nu behøver denne tabel ikke at vide, hvor mange der kan være for at vise den. Men spørgsmålet opstår - hvordan ved tabellen, om der er data til den næste side (for korrekt at vise "Næste" linket)?
Svaret er meget enkelt: du kan læse en post mere fra databasen, end der er behov for til visning, og tilstedeværelsen af denne "ekstra" post vil vise, om der er en næste del. På denne måde behøver du kun at køre én anmodning for at få én side med data, hvilket forbedrer ydeevnen markant og gør det nemmere at understøtte en sådan funktionalitet. I min praksis var der et tilfælde, hvor afvisningen af at tælle det samlede antal poster fremskyndede leveringen af resultater med 4-5 gange.
Der er flere brugergrænseflade muligheder for denne tilgang: "tilbage" og "frem" kommandoer, som i eksemplet ovenfor, en "indlæs mere" knap, som blot tilføjer en ny del til de viste resultater, "uendelig rul", som virker på princippet om "indlæs mere" ", men signalet for at få den næste del er for brugeren at rulle alle de viste resultater til slutningen. Uanset den visuelle løsning, forbliver princippet om datasampling det samme.
Nuancer af personsøgningsimplementering
Alle forespørgselseksemplerne ovenfor bruger "offset + count" tilgangen, når forespørgslen selv specificerer i hvilken rækkefølge resultatrækkerne og hvor mange rækker der skal returneres. Lad os først se på, hvordan man bedst organiserer parameteroverførsel i dette tilfælde. I praksis er jeg stødt på flere metoder:
- Serienummeret på den anmodede side (pageIndex), sidestørrelse (pageSize).
- Serienummeret på den første post, der skal returneres (startIndex), det maksimale antal poster i resultatet (tæller).
- Sekvensnummeret på den første post, der skal returneres (startIndex), sekvensnummeret på den sidste post, der skal returneres (endIndex).
Ved første øjekast kan det se ud til, at dette er så elementært, at der ikke er nogen forskel. Men det er ikke tilfældet - den mest bekvemme og universelle mulighed er den anden (startindeks, tæller). Det er der flere grunde til:
- For den ovennævnte metode til korrekturlæsning af +1-indgange er den første mulighed med pageIndex og pageSize ekstremt ubelejlig. For eksempel ønsker vi at vise 50 indlæg pr. side. Ifølge ovenstående algoritme skal du læse en post mere end nødvendigt. Hvis denne "+1" ikke er implementeret på serveren, viser det sig, at vi for den første side skal anmode om poster fra 1 til 51, for den anden - fra 51 til 101 osv. Hvis du angiver en sidestørrelse på 51 og øger sideindekset, vil den anden side vende tilbage fra 52 til 102 osv. Derfor, i den første mulighed, er den eneste måde at implementere en knap for at gå til næste side korrekt ved at få serveren korrekturlæst den "ekstra" linje, hvilket vil være en meget implicit nuance.
- Den tredje mulighed giver slet ikke mening, da for at køre forespørgsler i de fleste databaser skal du stadig bestå optællingen i stedet for indekset for den sidste post. At trække startIndex fra endIndex kan være en simpel aritmetisk operation, men det er overflødigt her.
Nu skal vi beskrive ulemperne ved at implementere sidesøgning gennem "offset + kvantitet":
- Hentning af hver efterfølgende side vil være dyrere og langsommere end den forrige, fordi databasen stadig skal gennemgå alle posterne "fra begyndelsen" i henhold til søge- og sorteringskriterierne og derefter stoppe ved det ønskede fragment.
- Ikke alle DBMS'er kan understøtte denne tilgang.
Der er alternativer, men de er også ufuldkomne. Den første af disse tilgange kaldes "keyset paging" eller "seek method" og er som følger: efter at have modtaget en del, kan du huske feltværdierne i den sidste post på siden og derefter bruge dem til at få næste portion. For eksempel kørte vi følgende forespørgsel:
SELECT * FROM Sales.SalesOrderHeader
ORDER BY OrderDate DESC
OFFSET 0 ROWS
FETCH NEXT 50 ROWS ONLY
Og i den sidste post fik vi værdien af ordredatoen ‘2014-06-29’. Så for at få den næste side kan du prøve at gø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 angivet ovenfor vil sandsynligvis gå glip af en masse påkrævede rækker. For at tilføje utvetydighed til denne forespørgsel skal du tilføje et unikt felt til betingelsen (antag, at 75074 er den sidste værdi af den primære nøgle fra den første del):
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
Denne mulighed vil fungere korrekt, men generelt vil det være svært at optimere, da betingelsen indeholder en OR-operator. Hvis værdien af den primære nøgle stiger, når OrderDate stiger, kan betingelsen forenkles ved kun at efterlade et filter efter SalesOrderID. Men hvis der ikke er nogen streng sammenhæng mellem værdierne af den primære nøgle og det felt, som resultatet er sorteret efter, kan denne OR ikke undgås i de fleste DBMS'er. En undtagelse, jeg er klar over, er PostgreSQL, som fuldt ud understøtter tuple-sammenligning, og ovenstående betingelse kan skrives som "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Givet en sammensat nøgle med disse to felter, burde en forespørgsel som denne være ret nem.
En anden alternativ tilgang kan f.eks. findes i eller — når en anmodning ud over data returnerer en speciel identifikator, som du kan få den næste del af data med. Hvis denne identifikator har en ubegrænset levetid (som i Comsos DB), så er dette en fantastisk måde at implementere sidesøgning med sekventiel overgang mellem sider (mulighed #2 nævnt ovenfor). Dets mulige ulemper: det understøttes ikke i alle DBMS'er; den resulterende næste-chunk identifikator kan have en begrænset levetid, hvilket generelt ikke er egnet til at implementere brugerinteraktion (såsom ElasticSearch scroll API).
Kompleks filtrering
Lad os komplicere opgaven yderligere. Antag, at der er et krav om at implementere den såkaldte facetterede søgning, som er meget velkendt for alle fra netbutikker. Ovenstående eksempler baseret på ordretabellen er ikke særlig illustrative i dette tilfælde, så lad os skifte til produkttabellen fra AdventureWorks-databasen:
Hvad er ideen bag facetteret søgning? Faktum er, at for hvert filterelement vises antallet af poster, der opfylder dette kriterium under hensyntagen til filtre valgt i alle andre kategorier.
Hvis vi for eksempel vælger kategorien Cykler og farven Sort i dette eksempel, vil tabellen kun vise sorte cykler, men:
- For hvert kriterium i kategorigruppen vil antallet af produkter fra den kategori blive vist med sort.
- For hvert kriterium i gruppen "Farver" vil antallet af cykler i denne farve blive vist.
Her er et eksempel på resultatoutput for sådanne forhold:
Hvis du også tjekker kategorien "Tøj", vil tabellen også vise sort tøj, der er på lager. Antallet af sorte produkter i "Farve"-sektionen vil også blive genberegnet i henhold til de nye betingelser, kun i "Kategorier"-sektionen vil intet ændre sig... Jeg håber, at disse eksempler er nok til at forstå den sædvanlige facetterede søgealgoritme.
Lad os nu forestille os, hvordan dette kan implementeres på et relationelt grundlag. Hver gruppe af kriterier, såsom kategori og farve, vil kræve en separat forespørgsel:
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
Hvad er der galt med denne løsning? Det er meget enkelt – det skalerer ikke godt. Hver filtersektion kræver en separat forespørgsel for at beregne mængder, og disse forespørgsler er ikke de nemmeste. I onlinebutikker kan nogle kategorier have flere dusin filtersektioner, hvilket kan være et alvorligt præstationsproblem.
Normalt efter disse udsagn bliver jeg tilbudt nogle løsninger, nemlig:
- Kombiner alle mængder i én forespørgsel. Teknisk er dette muligt ved at bruge UNION nøgleordet, men det hjælper ikke meget på ydeevnen - databasen skal stadig udføre hvert af fragmenterne fra bunden.
- Cache-mængder. Dette foreslås mig næsten hver gang, jeg beskriver et problem. Forbeholdet er, at dette generelt er umuligt. Lad os sige, at vi har 10 "facetter", som hver har 5 værdier. Dette er en meget "beskeden" situation i forhold til, hvad man kan se i de samme netbutikker. Valget af et facetelement påvirker mængderne i 9 andre, med andre ord, for hver kombination af kriterier kan mængderne være forskellige. I vores eksempel er der i alt 50 kriterier, som brugeren kan vælge, så der vil være 250 mulige kombinationer.Der er ikke nok hukommelse eller tid til at udfylde en sådan række af data. Her kan du indvende og sige, at ikke alle kombinationer er rigtige, og brugeren vælger sjældent mere end 5-10 kriterier. Ja, det er muligt at foretage doven indlæsning og cache en mængde på kun det, der nogensinde er blevet valgt, men jo flere valg der er, jo mindre effektiv vil en sådan cache være, og jo mere mærkbare vil problemerne med responstid være (især hvis datasæt ændres regelmæssigt).
Heldigvis har et sådant problem længe haft ret effektive løsninger, der virker forudsigeligt på store datamængder. For enhver af disse muligheder giver det mening at opdele genberegningen af facetter og modtage resultatsiden i to parallelle opkald til serveren og organisere brugergrænsefladen på en sådan måde, at indlæsning af data efter facetter "ikke forstyrrer" visningen af Søgeresultater.
- Kald en fuldstændig genberegning af "facetter" så sjældent som muligt. For eksempel skal du ikke genberegne alt, hver gang søgekriterierne ændres, men i stedet finde det samlede antal resultater, der matcher de aktuelle betingelser, og bede brugeren om at vise dem - "1425 poster fundet, vis?" Brugeren kan enten fortsætte med at ændre søgetermerne eller klikke på knappen "Vis". Kun i det andet tilfælde vil alle anmodninger om opnåelse af resultater og genberegning af mængder på alle "facetter" blive udført. I dette tilfælde, som du nemt kan se, bliver du nødt til at håndtere en anmodning for at opnå det samlede antal resultater og dets optimering. Denne metode kan findes i mange små netbutikker. Dette er naturligvis ikke et vidundermiddel for dette problem, men i simple tilfælde kan det være et godt kompromis.
- Brug søgemaskiner til at finde resultater og tælle facetter, såsom Solr, ElasticSearch, Sphinx og andre. Alle af dem er designet til at bygge "facetter" og gør dette ret effektivt på grund af det omvendte indeks. Hvordan søgemaskiner fungerer, hvorfor de i sådanne tilfælde er mere effektive end databaser til generelle formål, hvilke praksisser og faldgruber der er - dette er et emne for en separat artikel. Her vil jeg gerne henlede din opmærksomhed på, at søgemaskinen ikke kan erstatte hoveddatalageret, den bruges som en tilføjelse: eventuelle ændringer i hoveddatabasen, der er relevante for søgning, synkroniseres i søgeindekset; Søgemaskinen interagerer normalt kun med søgemaskinen og får ikke adgang til hoveddatabasen. Et af de vigtigste punkter her er, hvordan man organiserer denne synkronisering pålideligt. Det hele afhænger af kravene til "reaktionstid". Hvis tiden mellem en ændring i hoveddatabasen og dens "manifestation" i søgningen ikke er kritisk, kan du oprette en tjeneste, der søger efter nyligt ændrede poster med få minutters mellemrum og indekserer dem. Hvis du vil have den kortest mulige svartid, kan du implementere noget som f.eks for at sende opdateringer til søgetjenesten.
Fund
- Implementering af serversidesøgning er en betydelig komplikation og giver kun mening for hurtigt voksende eller blot store datasæt. Der er ingen helt nøjagtig opskrift på, hvordan man vurderer "stor" eller "hurtigt voksende", men jeg ville følge denne tilgang:
- Hvis modtagelse af en komplet samling af data, under hensyntagen til servertid og netværkstransmission, normalt passer til ydeevnekravene, er der ingen mening i at implementere personsøgning på serversiden.
- Der kan være en situation, hvor der ikke forventes nogen præstationsproblemer i den nærmeste fremtid, da der er lidt data, men dataindsamlingen vokser konstant. Hvis et sæt data i fremtiden måske ikke længere opfylder det foregående punkt, er det bedre at begynde at søge med det samme.
- Hvis der ikke er et strengt krav fra virksomhedens side om at vise det samlede antal resultater eller at vise sidetal, og dit system ikke har en søgemaskine, er det bedre ikke at implementere disse punkter og overveje mulighed #2.
- Hvis der er et klart krav til facetteret søgning, har du to muligheder uden at ofre ydeevnen:
- Genberegn ikke alle mængder, hver gang søgekriterierne ændres.
- Brug søgemaskiner som Solr, ElasticSearch, Sphinx og andre. Men det skal forstås, at det ikke kan være en erstatning for hoveddatabasen, og skal bruges som en tilføjelse til hovedlageret til at løse søgeproblemer.
- I tilfælde af facetteret søgning giver det også mening at opdele hentning af søgeresultatsiden og optællingen i to parallelle anmodninger. At tælle mængder kan tage længere tid end at opnå resultater, mens resultaterne er vigtigere for brugeren.
- Hvis du bruger en SQL-database til søgning, bør enhver kodeændring relateret til denne del være veltestet for ydeevne på den passende mængde data (overskrider volumen i live-databasen). Det er også tilrådeligt at bruge overvågning af forespørgselsudførelsestid på alle forekomster af databasen, og især på den "live". Selvom alt var fint med forespørgselsplaner på udviklingsstadiet, kan situationen ændre sig mærkbart efterhånden som mængden af data vokser.
Kilde: www.habr.com