Utdata för sökresultat och prestandaproblem

Ett av de typiska scenarierna i alla applikationer vi är bekanta med är att söka efter data enligt vissa kriterier och visa den i en lättläst form. Det kan också finnas ytterligare alternativ för sortering, gruppering och personsökning. Uppgiften är i teorin trivial, men när de löser den gör många utvecklare ett antal misstag, som senare gör att produktiviteten blir lidande. Låt oss försöka överväga olika alternativ för att lösa detta problem och formulera rekommendationer för att välja den mest effektiva implementeringen.

Personsökningsalternativ #1

Det enklaste alternativet som kommer att tänka på är en sida-för-sida-visning av sökresultat i dess mest klassiska form.

Låt oss säga att din applikation använder en relationsdatabas. I det här fallet, för att visa information i det här formuläret, måste du köra två SQL-frågor:

• Hämta rader för den aktuella sidan.
• Beräkna det totala antalet rader som motsvarar sökkriterierna - detta är nödvändigt för att visa sidor.

Låt oss titta på den första frågan med en test MS SQL-databas som exempel AdventureWorks för 2016 års server. För detta ändamål kommer vi att använda tabellen Sales.SalesOrderHeader:

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

Ovanstående fråga kommer att returnera de första 50 beställningarna från listan, sorterade efter fallande datum för tillägg, med andra ord, de 50 senaste beställningarna.

Det körs snabbt på testbasen, men låt oss titta på exekveringsplanen och I/O-statistik:

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 för varje fråga genom att köra kommandot SET STATISTICS IO ON i frågekörningstiden.

Som du kan se från exekveringsplanen är det mest resurskrävande alternativet att sortera alla rader i källtabellen efter datum som lagts till. Och problemet är att ju fler rader som visas i tabellen, desto "svårare" blir sorteringen. I praktiken bör sådana situationer undvikas, så låt oss lägga till ett index till datumet för tillägget och se om resursförbrukningen har förändrats:

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 är klart att det har blivit mycket bättre. Men är alla problem lösta? Låt oss ändra frågan för att söka efter beställningar där den totala kostnaden för varor överstiger 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 rolig situation: frågeplanen är inte mycket sämre än den föregående, men det faktiska antalet logiska läsningar är nästan dubbelt så stort som med en heltabellsskanning. Det finns en väg ut - om vi gör ett sammansatt index från ett redan existerande index och lägger till det totala priset på varor som det andra fältet, kommer vi återigen att få 165 logiska läsningar:

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

Denna serie av exempel kan fortsätta under lång tid, men de två huvudtankarna som jag vill uttrycka här är:

• Att lägga till ett nytt kriterium eller sorteringsordning i en sökfråga kan ha en betydande inverkan på sökfrågans hastighet.
• Men om vi bara behöver subtrahera en del av datan, och inte alla resultat som matchar söktermerna, finns det många sätt att optimera en sådan fråga.

Låt oss nu gå vidare till den andra frågan som nämndes i början - den som räknar antalet poster som uppfyller sökkriteriet. Låt oss ta samma exempel - söka efter beställningar som är mer än 100 USD:

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

Med tanke på det sammansatta index som anges ovan 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 att frågan går igenom hela indexet är inte förvånande, eftersom SubTotal-fältet inte är i första position, så frågan kan inte använda det. Problemet löses genom att lägga till ytterligare ett index i fältet SubTotal, och som ett resultat ger det endast 48 logiska läsningar.

Du kan ge några fler exempel på förfrågningar om att räkna kvantiteter, men kärnan förblir densamma: att ta emot en bit data och räkna det totala beloppet är två fundamentalt olika förfrågningar, och var och en kräver sina egna åtgärder för optimering. I allmänhet kommer du inte att kunna hitta en kombination av index som fungerar lika bra för båda frågorna.

Ett av de viktiga kraven som bör förtydligas när man utvecklar en sådan söklösning är därför om det verkligen är viktigt för en verksamhet att se det totala antalet hittade objekt. Det händer ofta att nej. Och navigering efter specifika sidnummer, enligt min mening, är en lösning med en mycket snäv omfattning, eftersom de flesta personsökningsscenarier ser ut som "gå till nästa sida."

Personsökningsalternativ #2

Låt oss anta att användare inte bryr sig om att veta det totala antalet objekt som hittades. Låt oss försöka förenkla söksidan:

Faktum är att det enda som har förändrats är att det inte finns något sätt att navigera till specifika sidnummer, och nu behöver den här tabellen inte veta hur många det kan vara för att visa den. Men frågan uppstår - hur vet tabellen om det finns data för nästa sida (för att korrekt visa länken "Nästa")?

Svaret är mycket enkelt: du kan läsa från databasen ytterligare en post än vad som behövs för visning, och närvaron av denna "ytterligare" post kommer att visa om det finns en nästa del. På så sätt behöver du bara köra en begäran för att få en sida med data, vilket avsevärt förbättrar prestandan och gör det lättare att stödja sådan funktionalitet. I min praktik var det ett fall då jag vägrade att räkna det totala antalet poster påskyndade leveransen av resultat med 4-5 gånger.

Det finns flera användargränssnittsalternativ för detta tillvägagångssätt: kommandon "bakåt" och "framåt", som i exemplet ovan, en "ladda mer"-knapp, som helt enkelt lägger till en ny del till de visade resultaten, "oändlig rullning", som fungerar på principen om "ladda mer" ", men signalen för att få nästa del är att användaren ska scrolla alla visade resultat till slutet. Oavsett den visuella lösningen förblir principen för datasampling densamma.

Nyanser av personsökningsimplementering

Alla frågeexempel som ges ovan använder metoden "offset + count", när själva frågan anger i vilken ordning resultatraderna och hur många rader som måste returneras. Låt oss först titta på hur man bäst organiserar parameteröverföring i det här fallet. I praktiken har jag stött på flera metoder:

• Serienumret för den begärda sidan (pageIndex), sidstorlek (pageSize).
• Serienumret för den första posten som ska returneras (startIndex), det maximala antalet poster i resultatet (antal).
• Sekvensnumret för den första posten som ska returneras (startIndex), sekvensnumret för den sista posten som ska returneras (endIndex).

Vid första anblicken kan det tyckas att detta är så elementärt att det inte är någon skillnad. Men det är inte så - det mest bekväma och universella alternativet är det andra (startindex, count). Det finns flera anledningar till detta:

• För korrekturläsningsmetoden för +1-inlägg som ges ovan är det första alternativet med pageIndex och pageSize extremt obekvämt. Vi vill till exempel visa 50 inlägg per sida. Enligt ovanstående algoritm måste du läsa ytterligare en post än nödvändigt. Om denna "+1" inte är implementerad på servern, visar det sig att för den första sidan måste vi begära poster från 1 till 51, för den andra - från 51 till 101, etc. Om du anger en sidstorlek på 51 och ökar pageIndex, kommer den andra sidan att återgå från 52 till 102, etc. Följaktligen, i det första alternativet, är det enda sättet att korrekt implementera en knapp för att gå till nästa sida att låta servern korrekturläsa den "extra" raden, vilket kommer att vara en mycket implicit nyans.
• Det tredje alternativet är inte vettigt alls, eftersom för att köra frågor i de flesta databaser måste du fortfarande klara räkningen snarare än indexet för den senaste posten. Att subtrahera startIndex från endIndex kan vara en enkel aritmetisk operation, men det är överflödigt här.

Nu ska vi beskriva nackdelarna med att implementera sökning genom "offset + kvantitet":

• Att hämta varje efterföljande sida kommer att bli dyrare och långsammare än den föregående, eftersom databasen fortfarande behöver gå igenom alla poster "från början" enligt sök- och sorteringskriterierna och sedan stanna vid det önskade fragmentet.
• Inte alla DBMS kan stödja detta tillvägagångssätt.

Det finns alternativ, men de är också ofullkomliga. Den första av dessa tillvägagångssätt kallas "nyckeluppsättningssökning" eller "sökmetod" och är följande: efter att ha mottagit en del kan du komma ihåg fältvärdena i den sista posten på sidan och sedan använda dem för att få nästa del. Till exempel körde vi följande fråga:

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

Och i förra posten fick vi orderdatumvärdet '2014-06-29'. För att komma till nästa sida kan du försöka göra så här:

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

Problemet är att OrderDate är ett icke-unikt fält och villkoret som anges ovan kommer sannolikt att missa många obligatoriska rader. För att lägga till entydighet till den här frågan måste du lägga till ett unikt fält till villkoret (anta att 75074 är det sista värdet på primärnyckeln från den första 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

Detta alternativ kommer att fungera korrekt, men i allmänhet är det svårt att optimera eftersom villkoret innehåller en ELLER-operator. Om värdet på primärnyckeln ökar när OrderDate ökar, kan villkoret förenklas genom att endast lämna ett filter efter SalesOrderID. Men om det inte finns någon strikt korrelation mellan värdena för primärnyckeln och fältet som resultatet sorteras efter, kan detta ELLER inte undvikas i de flesta DBMS. Ett undantag jag känner till är PostgreSQL, som fullt ut stöder tupeljämförelser, och ovanstående villkor kan skrivas som "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Med en sammansatt nyckel med dessa två fält borde en fråga som denna vara ganska enkel.

Ett andra alternativt tillvägagångssätt kan hittas till exempel i ElasticSearch scroll API eller Cosmos DB — när en begäran, förutom data, returnerar en speciell identifierare med vilken du kan få nästa del av data. Om denna identifierare har en obegränsad livslängd (som i Comsos DB), så är detta ett utmärkt sätt att implementera personsökning med sekventiell övergång mellan sidor (alternativ #2 nämnt ovan). Dess möjliga nackdelar: det stöds inte i alla DBMS; den resulterande nästa-chunk-identifieraren kan ha en begränsad livslängd, vilket i allmänhet inte är lämpligt för att implementera användarinteraktion (som ElasticSearch scroll API).

Komplex filtrering

Låt oss komplicera uppgiften ytterligare. Anta att det finns ett krav på att implementera den så kallade facetterade sökningen, som är mycket bekant för alla från nätbutiker. Ovanstående exempel baserade på ordertabellen är inte särskilt illustrativa i det här fallet, så låt oss byta till produkttabellen från AdventureWorks-databasen:

Vad är tanken bakom fasetterad sökning? Faktum är att för varje filterelement visas antalet poster som uppfyller detta kriterium med hänsyn till filter valda i alla andra kategorier.

Till exempel, om vi väljer kategorin Cyklar och färgen Svart i det här exemplet, kommer tabellen bara att visa svarta cyklar, men:

• För varje kriterium i kategorigruppen kommer antalet produkter från den kategorin att visas i svart.
• För varje kriterium i gruppen "Färger" kommer antalet cyklar i denna färg att visas.

Här är ett exempel på resultatet för sådana villkor:

Om du även kollar i kategorin “Kläder” kommer tabellen även att visa svarta kläder som finns i lager. Antalet svarta produkter i avsnittet "Färg" kommer också att räknas om enligt de nya villkoren, bara i avsnittet "Kategorier" kommer ingenting att förändras... Jag hoppas att dessa exempel är tillräckligt för att förstå den vanliga facetterade sökalgoritmen.

Låt oss nu föreställa oss hur detta kan implementeras på en relationell basis. Varje grupp av kriterier, som Kategori och Färg, kommer att kräva en separat fråga:

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

Vad är det för fel på den här lösningen? Det är väldigt enkelt - det skalar inte bra. Varje filtersektion kräver en separat fråga för att beräkna kvantiteter, och dessa frågor är inte de lättaste. I nätbutiker kan vissa kategorier ha flera dussin filtersektioner, vilket kan vara ett allvarligt prestandaproblem.

Vanligtvis efter dessa uttalanden erbjuds jag några lösningar, nämligen:

• Kombinera alla kvantitetsräkningar till en fråga. Tekniskt sett är detta möjligt med hjälp av nyckelordet UNION, men det kommer inte att hjälpa prestanda mycket - databasen måste fortfarande köra vart och ett av fragmenten från början.
• Cachemängder. Detta föreslås för mig nästan varje gång jag beskriver ett problem. Förbehållet är att detta i allmänhet är omöjligt. Låt oss säga att vi har 10 "facetter", som var och en har 5 värden. Detta är en mycket "blygsam" situation jämfört med vad som kan ses i samma nätbutiker. Valet av ett aspektelement påverkar kvantiteterna i 9 andra, med andra ord, för varje kombination av kriterier kan kvantiteterna vara olika. I vårt exempel finns det totalt 50 kriterier som användaren kan välja, därför kommer det att finnas 250 möjliga kombinationer.Det finns inte tillräckligt med minne eller tid för att fylla en sådan uppsättning data. Här kan man invända och säga att inte alla kombinationer är verkliga och användaren väljer sällan mer än 5-10 kriterier. Ja, det är möjligt att göra lat laddning och cache en mängd av bara det som någonsin har valts, men ju fler val det finns, desto mindre effektiv blir en sådan cache och desto mer märkbara blir svarstidsproblemen (särskilt om datauppsättningen ändras regelbundet).

Lyckligtvis har ett sådant problem länge haft ganska effektiva lösningar som fungerar förutsägbart på stora datamängder. För något av dessa alternativ är det vettigt att dela upp omräkningen av fasetter och ta emot resultatsidan i två parallella anrop till servern och organisera användargränssnittet på ett sådant sätt att laddning av data per fasetter "inte stör" visningen av sökresultat.

• Kalla en fullständig omräkning av "facetter" så sällan som möjligt. Beräkna till exempel inte om allt varje gång sökkriterierna ändras, utan hitta istället det totala antalet resultat som matchar de aktuella villkoren och uppmana användaren att visa dem - "1425 poster hittades, visa?" Användaren kan antingen fortsätta att ändra söktermerna eller klicka på knappen "Visa". Endast i det andra fallet kommer alla förfrågningar om att erhålla resultat och omräkning av kvantiteter på alla "facetter" att utföras. I det här fallet, som du lätt kan se, måste du ta itu med en begäran för att få det totala antalet resultat och dess optimering. Denna metod kan hittas i många små nätbutiker. Uppenbarligen är detta inte ett universalmedel för detta problem, men i enkla fall kan det vara en bra kompromiss.
• Använd sökmotorer för att hitta resultat och räkna aspekter, som Solr, ElasticSearch, Sphinx och andra. Alla är designade för att bygga "facetter" och gör detta ganska effektivt på grund av det inverterade indexet. Hur sökmotorer fungerar, varför de i sådana fall är mer effektiva än allmänna databaser, vilka metoder och fallgropar det finns - det här är ett ämne för en separat artikel. Här vill jag uppmärksamma er på att sökmotorn inte kan ersätta huvuddatalagringen, den används som ett tillägg: alla ändringar i huvuddatabasen som är relevanta för sökning synkroniseras i sökindexet; Sökmotorn interagerar vanligtvis endast med sökmotorn och kommer inte åt huvuddatabasen. En av de viktigaste punkterna här är hur man organiserar denna synkronisering på ett tillförlitligt sätt. Allt beror på kraven på "reaktionstid". Om tiden mellan en ändring i huvuddatabasen och dess "manifestation" i sökningen inte är kritisk, kan du skapa en tjänst som söker efter nyligen ändrade poster med några minuters mellanrum och indexerar dem. Vill du ha kortast möjliga svarstid kan du implementera något liknande transaktionsutkorg för att skicka uppdateringar till söktjänsten.

Resultat

1. Implementering av sökning på serversidan är en betydande komplikation och är bara vettigt för snabbväxande eller helt enkelt stora datamängder. Det finns inget absolut exakt recept för hur man utvärderar "stor" eller "snabbväxande", men jag skulle följa detta tillvägagångssätt:
   • Om mottagningen av en komplett datasamling, med hänsyn till servertid och nätverksöverföring, normalt passar prestandakraven, är det ingen idé att implementera personsökning på serversidan.
   • Det kan finnas en situation där inga prestandaproblem förväntas inom en snar framtid, eftersom det finns lite data, men datainsamlingen växer hela tiden. Om någon uppsättning data i framtiden kanske inte längre uppfyller föregående punkt, är det bättre att börja söka direkt.
2. Om det inte finns några strikta krav från företagets sida att visa det totala antalet resultat eller att visa sidnummer, och ditt system inte har en sökmotor, är det bättre att inte implementera dessa punkter och överväga alternativ #2.
3. Om det finns ett tydligt krav på fasetterad sökning har du två alternativ utan att offra prestanda:
   • Beräkna inte om alla kvantiteter varje gång sökkriterierna ändras.
   • Använd sökmotorer som Solr, ElasticSearch, Sphinx och andra. Men det bör förstås att det inte kan ersätta huvuddatabasen, och bör användas som ett tillägg till huvudlagringen för att lösa sökproblem.
4. Vid fasetterad sökning är det också vettigt att dela upp hämtningen av sökresultatsidan och räkningen i två parallella förfrågningar. Att räkna kvantiteter kan ta längre tid än att få resultat, medan resultaten är viktigare för användaren.
5. Om du använder en SQL-databas för sökning, bör alla kodändringar som är relaterade till denna del testas väl för prestanda på lämplig mängd data (överstigande volymen i livedatabasen). Det är också tillrådligt att använda övervakning av frågekörningstid på alla instanser av databasen, och speciellt på den "live". Även om allt var bra med frågeplaner på utvecklingsstadiet, när mängden data växer, kan situationen förändras märkbart.

Källa: will.com