Rezultatele căutării și probleme de performanță

Unul dintre scenariile tipice din toate aplicațiile cu care suntem familiarizați este căutarea datelor după anumite criterii și afișarea lor într-o formă ușor de citit. Pot exista și opțiuni suplimentare pentru sortare, grupare și paginare. Sarcina este, teoretic, banală, dar atunci când o rezolvă, mulți dezvoltatori fac o serie de greșeli, care mai târziu fac ca productivitatea să aibă de suferit. Să încercăm să luăm în considerare diferite opțiuni pentru rezolvarea acestei probleme și să formulăm recomandări pentru alegerea celei mai eficiente implementări.

Opțiunea de paginare #1

Cea mai simplă opțiune care îmi vine în minte este afișarea, pagină cu pagină, a rezultatelor căutării în forma sa cea mai clasică.

Să presupunem că aplicația dvs. utilizează o bază de date relațională. În acest caz, pentru a afișa informații în acest formular, va trebui să rulați două interogări SQL:

• Obțineți rânduri pentru pagina curentă.
• Calculați numărul total de linii care corespund criteriilor de căutare - acest lucru este necesar pentru afișarea paginilor.

Să ne uităm la prima interogare folosind o bază de date de testare MS SQL ca exemplu AdventureWorks pentru serverul 2016. În acest scop vom folosi tabelul Sales.SalesOrderHeader:

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

Interogarea de mai sus va returna primele 50 de comenzi din listă, sortate după data descrescătoare a adăugării, cu alte cuvinte, cele mai recente 50 de comenzi.

Se rulează rapid pe baza de testare, dar să ne uităm la planul de execuție și la statisticile I/O:

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.

Puteți obține statistici I/O pentru fiecare interogare rulând comanda SET STATISTICS IO ON în timpul de execuție al interogării.

După cum puteți vedea din planul de execuție, opțiunea cea mai consumatoare de resurse este să sortați toate rândurile tabelului sursă după data adăugată. Și problema este că cu cât apar mai multe rânduri în tabel, cu atât sortarea va fi „mai grea”. În practică, astfel de situații ar trebui evitate, așa că haideți să adăugăm un index la data adăugării și să vedem dacă consumul de resurse s-a modificat:

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.

Evident că a devenit mult mai bine. Dar toate problemele sunt rezolvate? Să modificăm interogarea pentru a căuta comenzi în care costul total al mărfurilor depășește 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.

Avem o situație amuzantă: planul de interogări nu este cu mult mai rău decât cel anterior, dar numărul real de citiri logice este aproape de două ori mai mare decât în ​​cazul unei scanări de tabel complet. Există o cale de ieșire - dacă facem un index compus dintr-un index deja existent și adăugăm prețul total al mărfurilor ca al doilea câmp, vom obține din nou 165 de citiri logice:

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

Această serie de exemple poate fi continuată mult timp, dar cele două gânduri principale pe care vreau să le exprim aici sunt:

• Adăugarea oricărui criteriu nou sau ordine de sortare la o interogare de căutare poate avea un impact semnificativ asupra vitezei interogării de căutare.
• Dar dacă trebuie să scădem doar o parte din date și nu toate rezultatele care se potrivesc cu termenii de căutare, există multe modalități de a optimiza o astfel de interogare.

Acum să trecem la a doua interogare menționată chiar la început - cea care numără numărul de înregistrări care îndeplinesc criteriul de căutare. Să luăm același exemplu - căutând comenzi care depășesc 100 USD:

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

Având în vedere indicele compus indicat mai sus, obținem:

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.

Faptul că interogarea parcurge întregul index nu este surprinzător, deoarece câmpul SubTotal nu se află în prima poziție, deci interogarea nu îl poate folosi. Problema este rezolvată prin adăugarea unui alt index pe câmpul SubTotal și, ca rezultat, dă doar 48 de citiri logice.

Mai puteți da câteva exemple de solicitări de numărare a cantităților, dar esența rămâne aceeași: primirea unei date și numărarea sumei totale sunt două solicitări fundamental diferite, și fiecare necesită propriile măsuri pentru optimizare. În general, nu veți putea găsi o combinație de indici care să funcționeze la fel de bine pentru ambele interogări.

În consecință, una dintre cerințele importante care ar trebui clarificate atunci când se dezvoltă o astfel de soluție de căutare este dacă este cu adevărat important pentru o afacere să vadă numărul total de obiecte găsite. Se întâmplă adesea ca nu. Și navigarea după anumite numere de pagină, în opinia mea, este o soluție cu un domeniu de aplicare foarte restrâns, deoarece majoritatea scenariilor de paginare arată ca „mergi la pagina următoare”.

Opțiunea de paginare #2

Să presupunem că utilizatorilor nu le pasă să cunoască numărul total de obiecte găsite. Să încercăm să simplificăm pagina de căutare:

De fapt, singurul lucru care s-a schimbat este că nu există nicio modalitate de a naviga la anumite numere de pagină, iar acum acest tabel nu trebuie să știe câte pot fi pentru a-l afișa. Dar apare întrebarea - de unde știe tabelul dacă există date pentru pagina următoare (pentru a afișa corect linkul „Următorul”)?

Răspunsul este foarte simplu: puteți citi din baza de date o înregistrare mai mult decât este necesar pentru afișare, iar prezența acestei înregistrări „suplimentare” va arăta dacă există o porțiune următoare. În acest fel, trebuie să rulați o singură solicitare pentru a obține o pagină de date, ceea ce îmbunătățește semnificativ performanța și facilitează suportarea unei astfel de funcționalități. În practica mea, a existat un caz în care refuzul de a număra numărul total de înregistrări a accelerat livrarea rezultatelor de 4-5 ori.

Există mai multe opțiuni de interfață cu utilizatorul pentru această abordare: comenzi „înapoi” și „înainte”, ca în exemplul de mai sus, un buton „încărcare mai mult”, care adaugă pur și simplu o nouă porțiune la rezultatele afișate, „defilare infinită”, care funcționează pe principiul „încărcați mai mult”, dar semnalul pentru a obține următoarea porțiune este ca utilizatorul să deruleze toate rezultatele afișate până la sfârșit. Oricare ar fi soluția vizuală, principiul eșantionării datelor rămâne același.

Nuanțe ale implementării paginii

Toate exemplele de interogare prezentate mai sus folosesc abordarea „offset + count”, atunci când interogarea în sine specifică în ce ordine rândurile rezultate și câte rânduri trebuie returnate. În primul rând, să ne uităm la cum să organizați cel mai bine trecerea parametrilor în acest caz. În practică, am întâlnit mai multe metode:

• Numărul de serie al paginii solicitate (pageIndex), dimensiunea paginii (pageSize).
• Numărul de serie al primei înregistrări care va fi returnată (startIndex), numărul maxim de înregistrări din rezultat (count).
• Numărul de secvență al primei înregistrări care trebuie returnată (startIndex), numărul de secvență al ultimei înregistrări care trebuie returnată (endIndex).

La prima vedere poate părea că acest lucru este atât de elementar încât nu există nicio diferență. Dar nu este așa - cea mai convenabilă și universală opțiune este a doua (startIndex, count). Există mai multe motive pentru aceasta:

• Pentru abordarea de corectare a intrării +1 prezentată mai sus, prima opțiune cu pageIndex și pageSize este extrem de incomodă. De exemplu, vrem să afișăm 50 de postări pe pagină. Conform algoritmului de mai sus, trebuie să citiți încă o înregistrare decât este necesar. Dacă acest „+1” nu este implementat pe server, se dovedește că pentru prima pagină trebuie să solicităm înregistrări de la 1 la 51, pentru a doua - de la 51 la 101 etc. Dacă specificați o dimensiune a paginii de 51 și creșteți pageIndex, atunci a doua pagină va reveni de la 52 la 102 etc. În consecință, în prima opțiune, singura modalitate de a implementa corect un buton pentru a merge la pagina următoare este ca serverul să corecteze linia „extra”, care va fi o nuanță foarte implicită.
• A treia opțiune nu are deloc sens, deoarece pentru a rula interogări în majoritatea bazelor de date, va trebui să treceți mai degrabă numărul decât indexul ultimei înregistrări. Scăderea startIndex din endIndex poate fi o operație aritmetică simplă, dar este de prisos aici.

Acum ar trebui să descriem dezavantajele implementării paginii prin „offset + cantitate”:

• Preluarea fiecărei pagini ulterioare va fi mai costisitoare și mai lentă decât cea anterioară, deoarece baza de date va trebui totuși să parcurgă toate înregistrările „de la început” conform criteriilor de căutare și sortare, apoi să se oprească la fragmentul dorit.
• Nu toate SGBD-urile pot suporta această abordare.

Există alternative, dar sunt și imperfecte. Prima dintre aceste abordări se numește „keyset paging” sau „seek method” și este următoarea: după ce primiți o porțiune, vă puteți aminti valorile câmpului din ultima înregistrare de pe pagină și apoi le puteți utiliza pentru a obține următoarea porțiune. De exemplu, am rulat următoarea interogare:

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

Și în ultima înregistrare am primit valoarea datei comenzii „2014-06-29”. Apoi, pentru a obține următoarea pagină, puteți încerca să faceți acest lucru:

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

Problema este că OrderDate este un câmp neunic și condiția specificată mai sus este probabil să lipsească o mulțime de rânduri obligatorii. Pentru a adăuga claritate acestei interogări, trebuie să adăugați un câmp unic la condiție (presupuneți că 75074 este ultima valoare a cheii primare din prima porțiune):

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

Această opțiune va funcționa corect, dar în general va fi dificil de optimizat, deoarece condiția conține un operator OR. Dacă valoarea cheii primare crește odată cu creșterea OrderDate, atunci condiția poate fi simplificată lăsând doar un filtru după SalesOrderID. Dar dacă nu există o corelație strictă între valorile cheii primare și câmpul după care este sortat rezultatul, acest OR nu poate fi evitat în majoritatea SGBD-urilor. O excepție pe care o cunosc este PostgreSQL, care acceptă pe deplin comparațiile de tuplu, iar condiția de mai sus poate fi scrisă ca „WHERE (OrderDate, SalesOrderID) <('2014-06-29', 75074)". Având în vedere o cheie compusă cu aceste două câmpuri, o interogare ca aceasta ar trebui să fie destul de ușoară.

O a doua abordare alternativă poate fi găsită, de exemplu, în API-ul de defilare ElasticSearch sau Cosmos DB — când o solicitare, pe lângă date, returnează un identificator special cu care puteți obține următoarea porțiune de date. Dacă acest identificator are o durată de viață nelimitată (ca în Comsos DB), atunci aceasta este o modalitate excelentă de a implementa paginarea cu tranziție secvențială între pagini (opțiunea #2 menționată mai sus). Posibilele sale dezavantaje: nu este suportat în toate SGBD-urile; identificatorul de fragment următor rezultat poate avea o durată de viață limitată, ceea ce, în general, nu este potrivit pentru implementarea interacțiunii utilizatorului (cum ar fi API-ul de defilare ElasticSearch).

Filtrare complexă

Să complicăm sarcina și mai mult. Să presupunem că există o cerință de a implementa așa-numita căutare fațetă, care este foarte familiară pentru toată lumea din magazinele online. Exemplele de mai sus bazate pe tabelul de comenzi nu sunt foarte ilustrative în acest caz, așa că să trecem la tabelul Produse din baza de date AdventureWorks:

Care este ideea din spatele căutării fațete? Faptul este că pentru fiecare element de filtru este afișat numărul de înregistrări care îndeplinesc acest criteriu luând în considerare filtrele selectate în toate celelalte categorii.

De exemplu, dacă selectăm categoria Biciclete și culoarea Negru în acest exemplu, tabelul va afișa doar bicicletele negre, dar:

• Pentru fiecare criteriu din grupul Categorii, numărul de produse din categoria respectivă va fi afișat cu negru.
• Pentru fiecare criteriu al grupului „Culori”, va fi afișat numărul de biciclete de această culoare.

Iată un exemplu de rezultat pentru astfel de condiții:

Dacă bifați și categoria „Îmbrăcăminte”, tabelul va afișa și hainele negre care sunt în stoc. Numărul de produse negre din secțiunea „Culoare” va fi și el recalculat conform noilor condiții, doar că la secțiunea „Categorii” nu se va schimba nimic... Sper că aceste exemple sunt suficiente pentru a înțelege algoritmul obișnuit de căutare fațetată.

Acum să ne imaginăm cum poate fi implementat acest lucru pe o bază relațională. Fiecare grup de criterii, cum ar fi Categorie și Culoare, va necesita o interogare separată:

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

Ce este în neregulă cu această soluție? Este foarte simplu - nu se scalează bine. Fiecare secțiune de filtrare necesită o interogare separată pentru a calcula cantitățile, iar aceste interogări nu sunt cele mai ușoare. În magazinele online, unele categorii pot avea câteva zeci de secțiuni de filtrare, ceea ce poate fi o problemă serioasă pentru performanță.

De obicei dupa aceste afirmatii mi se ofera cateva solutii si anume:

• Combinați toate cantitățile într-o singură interogare. Din punct de vedere tehnic, acest lucru este posibil folosind cuvântul cheie UNION, dar nu va ajuta prea mult la performanță - baza de date va trebui totuși să execute fiecare dintre fragmente de la zero.
• Cantitățile din cache. Acest lucru mi se sugerează aproape de fiecare dată când descriu o problemă. Avertismentul este că acest lucru este în general imposibil. Să presupunem că avem 10 „fațete”, fiecare dintre ele având 5 valori. Aceasta este o situație foarte „modesta” în comparație cu ceea ce se poate vedea în aceleași magazine online. Alegerea unui element de fațetă afectează cantitățile din alte 9, cu alte cuvinte, pentru fiecare combinație de criterii cantitățile pot fi diferite. În exemplul nostru, există un total de 50 de criterii pe care utilizatorul le poate selecta, prin urmare, vor exista combinații posibile 250. Nu există suficientă memorie sau timp pentru a umple o astfel de matrice de date. Aici puteți obiecta și spune că nu toate combinațiile sunt reale, iar utilizatorul rareori selectează mai mult de 5-10 criterii. Da, este posibil să faceți încărcare leneșă și să stocați în cache doar o cantitate din ceea ce a fost selectat vreodată, dar cu cât sunt mai multe selecții, cu atât va fi mai puțin eficientă o astfel de cache și cu atât vor fi mai vizibile problemele legate de timpul de răspuns (mai ales dacă setul de date se modifică în mod regulat).

Din fericire, o astfel de problemă are de mult timp soluții destul de eficiente care funcționează previzibil pe volume mari de date. Pentru oricare dintre aceste opțiuni, este logic să împărțiți recalcularea fațetelor și primirea paginii de rezultate în două apeluri paralele către server și să organizați interfața cu utilizatorul în așa fel încât încărcarea datelor după fațete „nu interferează” cu afișarea rezultatele cautarii.

• Apelați la o recalculare completă a „fațetelor” cât mai rar posibil. De exemplu, nu recalculați totul de fiecare dată când criteriile de căutare se schimbă, ci găsiți numărul total de rezultate care se potrivesc condițiilor actuale și solicitați utilizatorului să le arate - „1425 de înregistrări găsite, afișate?” Utilizatorul poate continua modificarea termenilor de căutare sau poate face clic pe butonul „afișează”. Numai în al doilea caz vor fi executate toate cererile de obținere a rezultatelor și recalculare a cantităților pe toate „fațetele”. În acest caz, după cum puteți vedea cu ușurință, va trebui să faceți față unei cereri pentru a obține numărul total de rezultate și optimizarea acestuia. Această metodă poate fi găsită în multe magazine online mici. Evident, acesta nu este un panaceu pentru această problemă, dar în cazuri simple poate fi un bun compromis.
• Utilizați motoarele de căutare pentru a găsi rezultate și a număra fațete, cum ar fi Solr, ElasticSearch, Sphinx și altele. Toate sunt concepute pentru a construi „fațete” și pentru a face acest lucru destul de eficient datorită indexului inversat. Cum funcționează motoarele de căutare, de ce în astfel de cazuri sunt mai eficiente decât bazele de date cu scop general, ce practici și capcane există - acesta este un subiect pentru un articol separat. Aici aș dori să vă atrag atenția asupra faptului că motorul de căutare nu poate fi un înlocuitor pentru stocarea principală a datelor, este folosit ca o completare: orice modificări în baza de date principală care sunt relevante pentru căutare sunt sincronizate în indexul de căutare; Motorul de căutare interacționează de obicei doar cu motorul de căutare și nu accesează baza de date principală. Unul dintre cele mai importante puncte aici este cum să organizați această sincronizare în mod fiabil. Totul depinde de cerințele „timp de reacție”. Dacă timpul dintre o modificare în baza de date principală și „manifestarea” acesteia în căutare nu este critic, puteți crea un serviciu care caută înregistrările modificate recent la fiecare câteva minute și le indexează. Dacă doriți cel mai scurt timp de răspuns posibil, puteți implementa ceva de genul căsuță de ieșire tranzacțională pentru a trimite actualizări serviciului de căutare.

Constatări

1. Implementarea paginii pe server este o complicație semnificativă și are sens doar pentru seturi de date cu creștere rapidă sau pur și simplu mari. Nu există o rețetă absolut exactă pentru a evalua „mare” sau „cu creștere rapidă”, dar aș urma această abordare:
   • Dacă primirea unei colecții complete de date, luând în considerare timpul serverului și transmisia prin rețea, se potrivește în mod normal cerințelor de performanță, nu are rost să implementezi paginarea pe partea serverului.
   • Poate exista o situație în care nu sunt de așteptat probleme de performanță în viitorul apropiat, deoarece există puține date, dar colectarea de date este în continuă creștere. Dacă un set de date în viitor ar putea să nu mai satisfacă punctul anterior, este mai bine să începeți paginarea imediat.
2. Dacă nu există o cerință strictă din partea companiei de a afișa numărul total de rezultate sau de a afișa numere de pagini, iar sistemul dvs. nu are un motor de căutare, este mai bine să nu implementați aceste puncte și să luați în considerare opțiunea #2.
3. Dacă există o cerință clară pentru căutarea fațetă, aveți două opțiuni fără a sacrifica performanța:
   • Nu recalculați toate cantitățile de fiecare dată când criteriile de căutare se schimbă.
   • Utilizați motoare de căutare precum Solr, ElasticSearch, Sphinx și altele. Dar ar trebui să se înțeleagă că nu poate fi un înlocuitor pentru baza de date principală și ar trebui folosit ca o completare la stocarea principală pentru rezolvarea problemelor de căutare.
4. De asemenea, în cazul căutării cu fațete, este logic să împărțiți regăsirea paginii cu rezultatele căutării și numărarea în două solicitări paralele. Numărarea cantităților poate dura mai mult decât obținerea rezultatelor, în timp ce rezultatele sunt mai importante pentru utilizator.
5. Dacă utilizați o bază de date SQL pentru căutare, orice modificare de cod legată de această parte ar trebui să fie bine testată pentru performanță pe cantitatea adecvată de date (depășind volumul din baza de date live). De asemenea, este recomandabil să folosiți monitorizarea timpului de execuție a interogărilor pe toate instanțele bazei de date și în special pe cea „în direct”. Chiar dacă totul a fost în regulă cu planurile de interogare în etapa de dezvoltare, pe măsură ce volumul de date crește, situația se poate schimba semnificativ.

Sursa: www.habr.com