Risultati della ricerca e problemi di prestazioni

Uno degli scenari tipici di tutte le applicazioni che conosciamo è la ricerca di dati secondo determinati criteri e la loro visualizzazione in una forma di facile lettura. Potrebbero essere presenti anche opzioni aggiuntive per l'ordinamento, il raggruppamento e l'impaginazione. Il compito è, in teoria, banale, ma quando lo risolve, molti sviluppatori commettono una serie di errori, che in seguito ne risentono la produttività. Proviamo a considerare varie opzioni per risolvere questo problema e formuliamo raccomandazioni per scegliere l'implementazione più efficace.

Opzione di cercapersone n. 1

L'opzione più semplice che mi viene in mente è la visualizzazione pagina per pagina dei risultati di ricerca nella sua forma più classica.

Supponiamo che la tua applicazione utilizzi un database relazionale. In questo caso, per visualizzare le informazioni in questo modulo, sarà necessario eseguire due query SQL:

• Ottieni righe per la pagina corrente.
• Calcola il numero totale di righe corrispondenti ai criteri di ricerca: è necessario per visualizzare le pagine.

Diamo un'occhiata alla prima query utilizzando come esempio un database MS SQL di prova AdventureWorksWork per il server 2016. A questo scopo utilizzeremo la tabella Sales.SalesOrderHeader:

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

La query precedente restituirà i primi 50 ordini dell'elenco, ordinati per data di aggiunta decrescente, in altre parole, i 50 ordini più recenti.

Funziona rapidamente sulla base del test, ma diamo un'occhiata al piano di esecuzione e alle statistiche di 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.

È possibile ottenere statistiche I/O per ciascuna query eseguendo il comando SET STATISTICS IO ON nel runtime della query.

Come puoi vedere dal piano di esecuzione, l'opzione che richiede più risorse è ordinare tutte le righe della tabella di origine in base alla data di aggiunta. E il problema è che più righe compaiono nella tabella, più “difficile” risulterà l’ordinamento. In pratica, tali situazioni dovrebbero essere evitate, quindi aggiungiamo un indice alla data di aggiunta e vediamo se il consumo di risorse è cambiato:

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.

Ovviamente è migliorato molto. Ma tutti i problemi sono risolti? Modifichiamo la query per cercare ordini in cui il costo totale delle merci supera i 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.

Abbiamo una situazione divertente: il piano di query non è molto peggiore del precedente, ma il numero effettivo di letture logiche è quasi il doppio rispetto a una scansione completa della tabella. C'è una via d'uscita: se creiamo un indice composito da un indice già esistente e aggiungiamo il prezzo totale dei beni come secondo campo, otterremo nuovamente 165 letture logiche:

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

Questa serie di esempi potrebbe continuare a lungo, ma i due pensieri principali che voglio esprimere qui sono:

• L'aggiunta di nuovi criteri o criteri di ordinamento a una query di ricerca può avere un impatto significativo sulla velocità della query di ricerca.
• Ma se dobbiamo sottrarre solo una parte dei dati e non tutti i risultati che corrispondono ai termini di ricerca, esistono molti modi per ottimizzare tale query.

Passiamo ora alla seconda query menzionata all'inizio, quella che conta il numero di record che soddisfano il criterio di ricerca. Prendiamo lo stesso esempio, cercando ordini superiori a $ 100:

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

Dato l’indice composito sopra indicato, otteniamo:

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.

Il fatto che la query attraversi l'intero indice non sorprende, poiché il campo SubTotale non è in prima posizione, quindi la query non può utilizzarlo. Il problema viene risolto aggiungendo un altro indice nel campo Subtotale e di conseguenza fornisce solo 48 letture logiche.

Puoi fornire qualche altro esempio di richieste di conteggio delle quantità, ma l'essenza rimane la stessa: ricevere un dato e contare l'importo totale sono due richieste fondamentalmente diversee ciascuno richiede le proprie misure di ottimizzazione. In generale, non sarai in grado di trovare una combinazione di indici che funzioni altrettanto bene per entrambe le query.

Di conseguenza, uno dei requisiti importanti da chiarire quando si sviluppa una soluzione di ricerca di questo tipo è se sia davvero importante per un'azienda vedere il numero totale di oggetti trovati. Capita spesso che no. E la navigazione tramite numeri di pagina specifici, a mio avviso, è una soluzione con un ambito molto ristretto, poiché la maggior parte degli scenari di paginazione assomigliano a “vai alla pagina successiva”.

Opzione di cercapersone n. 2

Supponiamo che agli utenti non interessi conoscere il numero totale di oggetti trovati. Proviamo a semplificare la pagina di ricerca:

In effetti, l'unica cosa che è cambiata è che non c'è modo di navigare verso numeri di pagina specifici, e ora questa tabella non ha bisogno di sapere quanti possono essercene per visualizzarla. Ma sorge la domanda: come fa la tabella a sapere se sono presenti dati per la pagina successiva (per visualizzare correttamente il collegamento "Avanti")?

La risposta è molto semplice: puoi leggere dal database un record in più di quello necessario per la visualizzazione, e la presenza di questo record “aggiuntivo” mostrerà se esiste una porzione successiva. In questo modo, è sufficiente eseguire una sola richiesta per ottenere una pagina di dati, il che migliora significativamente le prestazioni e semplifica il supporto di tale funzionalità. Nella mia pratica, si è verificato un caso in cui il rifiuto di contare il numero totale di record ha accelerato la consegna dei risultati di 4-5 volte.

Esistono diverse opzioni dell'interfaccia utente per questo approccio: comandi "indietro" e "avanti", come nell'esempio sopra, un pulsante "carica altro", che aggiunge semplicemente una nuova porzione ai risultati visualizzati, "scorrimento infinito", che funziona secondo il principio "carica di più" ", ma il segnale per ottenere la porzione successiva è che l'utente scorra fino alla fine tutti i risultati visualizzati. Qualunque sia la soluzione visiva, il principio del campionamento dei dati rimane lo stesso.

Sfumature dell'implementazione della paginazione

Tutti gli esempi di query sopra riportati utilizzano l'approccio "offset + conteggio", quando la query stessa specifica in quale ordine le righe dei risultati e quante righe devono essere restituite. Per prima cosa, vediamo come organizzare al meglio il passaggio dei parametri in questo caso. In pratica, mi sono imbattuto in diversi metodi:

• Il numero di serie della pagina richiesta (pageIndex), la dimensione della pagina (pageSize).
• Il numero di serie del primo record da restituire (startIndex), il numero massimo di record nel risultato (count).
• Il numero di sequenza del primo record da restituire (startIndex), il numero di sequenza dell'ultimo record da restituire (endIndex).

A prima vista può sembrare così elementare che non vi sia alcuna differenza. Ma non è così: l'opzione più comoda e universale è la seconda (startIndex, count). Ci sono diverse ragioni per questo:

• Per l'approccio di correzione di bozze +1 indicato sopra, la prima opzione con pageIndex e pageSize è estremamente scomoda. Ad esempio, vogliamo visualizzare 50 post per pagina. Secondo l'algoritmo di cui sopra, è necessario leggere un record in più del necessario. Se questo "+1" non è implementato sul server, risulta che per la prima pagina dobbiamo richiedere i record da 1 a 51, per la seconda - da 51 a 101, ecc. Se specifichi una dimensione di pagina pari a 51 e aumenti pageIndex, la seconda pagina tornerà da 52 a 102, ecc. Di conseguenza, nella prima opzione, l'unico modo per implementare correttamente un pulsante per passare alla pagina successiva è far correggere al server la riga "extra", che sarà una sfumatura molto implicita.
• La terza opzione non ha affatto senso, poiché per eseguire query nella maggior parte dei database sarà comunque necessario passare il conteggio anziché l'indice dell'ultimo record. Sottrarre startIndex da endIndex può essere una semplice operazione aritmetica, ma in questo caso è superflua.

Ora dovremmo descrivere gli svantaggi dell’implementazione della paginazione tramite “offset + quantità”:

• Recuperare ogni pagina successiva sarà più costoso e più lento della precedente, perché il database dovrà comunque scorrere tutti i record “dall'inizio” secondo i criteri di ricerca e ordinamento, per poi fermarsi al frammento desiderato.
• Non tutti i DBMS possono supportare questo approccio.

Le alternative ci sono, ma sono anche imperfette. Il primo di questi approcci si chiama “keyset paging” o “metodo di ricerca” ed è il seguente: dopo aver ricevuto una porzione, è possibile ricordare i valori dei campi nell'ultimo record della pagina, e quindi utilizzarli per ottenere la porzione successiva. Ad esempio, abbiamo eseguito la seguente query:

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

E nell'ultimo record abbiamo ottenuto il valore della data dell'ordine "2014-06-29". Quindi per ottenere la pagina successiva puoi provare a fare questo:

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

Il problema è che OrderDate è un campo non univoco ed è probabile che la condizione specificata sopra manchi molte righe obbligatorie. Per aggiungere univocità a questa query, è necessario aggiungere un campo univoco alla condizione (supponiamo che 75074 sia l'ultimo valore della chiave primaria della prima porzione):

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

Questa opzione funzionerà correttamente, ma in generale sarà difficile da ottimizzare poiché la condizione contiene un operatore OR. Se il valore della chiave primaria aumenta all'aumentare di OrderDate, la condizione può essere semplificata lasciando solo un filtro in base a SalesOrderID. Ma se non esiste una stretta correlazione tra i valori della chiave primaria e il campo in base al quale viene ordinato il risultato, nella maggior parte dei DBMS questo OR non può essere evitato. Un'eccezione che conosco è PostgreSQL, che supporta completamente i confronti di tuple e la condizione di cui sopra può essere scritta come "WHERE (OrderDate, SalesOrderID) < ('2014-06-29', 75074)". Data una chiave composta con questi due campi, una query come questa dovrebbe essere abbastanza semplice.

Un secondo approccio alternativo è rinvenibile, ad esempio, in API di scorrimento ElasticSearch o Cosmo DB — quando una richiesta, oltre ai dati, restituisce un identificatore speciale con il quale è possibile ottenere la porzione successiva di dati. Se questo identificatore ha una durata illimitata (come in Comsos DB), allora questo è un ottimo modo per implementare il paging con transizione sequenziale tra le pagine (opzione n. 2 menzionata sopra). I suoi possibili svantaggi: non è supportato in tutti i DBMS; l'identificatore del blocco successivo risultante potrebbe avere una durata limitata, che generalmente non è adatta per implementare l'interazione dell'utente (come l'API di scorrimento ElasticSearch).

Filtraggio complesso

Complichiamo ulteriormente il compito. Supponiamo che sia necessario implementare la cosiddetta ricerca sfaccettata, che è molto familiare a tutti nei negozi online. Gli esempi precedenti basati sulla tabella degli ordini non sono molto illustrativi in ​​questo caso, quindi passiamo alla tabella Product dal database AdventureWorks:

Qual è l'idea alla base della ricerca sfaccettata? Il fatto è che per ciascun elemento filtro viene mostrato il numero di record che soddisfano questo criterio tenendo conto dei filtri selezionati in tutte le altre categorie.

Ad esempio, se in questo esempio selezioniamo la categoria Biciclette e il colore Nero, la tabella mostrerà solo le bici nere, ma:

• Per ciascun criterio nel gruppo Categorie, il numero di prodotti di quella categoria verrà mostrato in nero.
• Per ogni criterio del gruppo “Colori” verrà mostrato il numero di biciclette di quel colore.

Ecco un esempio dell'output del risultato per tali condizioni:

Se controlli anche la categoria "Abbigliamento", la tabella mostrerà anche i vestiti neri disponibili. Anche il numero di prodotti neri nella sezione “Colore” verrà ricalcolato in base alle nuove condizioni, solo nella sezione “Categorie” non cambierà nulla... Spero che questi esempi siano sufficienti per comprendere il consueto algoritmo di ricerca a faccette.

Ora immaginiamo come ciò possa essere implementato su base relazionale. Ogni gruppo di criteri, come Categoria e Colore, richiederà una query separata:

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

Cosa c'è di sbagliato in questa soluzione? È molto semplice: non si adatta bene. Ogni sezione del filtro richiede una query separata per calcolare le quantità e queste query non sono le più semplici. Nei negozi online, alcune categorie possono avere diverse dozzine di sezioni filtro, il che può rappresentare un serio problema di prestazioni.

Solitamente dopo queste affermazioni mi vengono proposte alcune soluzioni e cioè:

• Combina tutti i conteggi delle quantità in un'unica query. Tecnicamente questo è possibile utilizzando la parola chiave UNION, ma non aiuterà molto le prestazioni: il database dovrà comunque eseguire ciascuno dei frammenti da zero.
• Quantità di cache. Questo mi viene suggerito quasi ogni volta che descrivo un problema. L'avvertenza è che questo è generalmente impossibile. Diciamo di avere 10 "sfaccettature", ognuna delle quali ha 5 valori. Si tratta di una situazione molto “modesta” rispetto a quanto si può vedere negli stessi negozi online. La scelta di un elemento della sfaccettatura influenza le quantità in altri 9, in altre parole, per ogni combinazione di criteri le quantità possono essere diverse. Nel nostro esempio ci sono 50 criteri in totale che l'utente può selezionare, quindi le combinazioni possibili saranno 250. Non c'è abbastanza memoria o tempo per riempire un tale array di dati. Qui puoi obiettare e dire che non tutte le combinazioni sono reali e l'utente raramente seleziona più di 5-10 criteri. Sì, è possibile eseguire il caricamento lento e memorizzare nella cache solo una quantità di ciò che è stato selezionato, ma più selezioni ci sono, meno efficiente sarà tale cache e più evidenti saranno i problemi di tempo di risposta (specialmente se il il set di dati cambia regolarmente).

Fortunatamente, per un problema del genere esistono da tempo soluzioni abbastanza efficaci che funzionano in modo prevedibile su grandi volumi di dati. Per ognuna di queste opzioni, ha senso dividere il ricalcolo delle faccette e la ricezione della pagina dei risultati in due chiamate parallele al server e organizzare l'interfaccia utente in modo tale che il caricamento dei dati per faccette “non interferisca” con la visualizzazione delle faccette. risultati di ricerca.

• Chiamare un ricalcolo completo delle "sfaccettature" il più raramente possibile. Ad esempio, non ricalcolare tutto ogni volta che cambiano i criteri di ricerca, ma trovare invece il numero totale di risultati che corrispondono alle condizioni attuali e chiedere all'utente di mostrarli: "1425 record trovati, mostrare?" L'utente può continuare a modificare i termini di ricerca o fare clic sul pulsante "mostra". Solo nel secondo caso verranno eseguite tutte le richieste di ottenimento di risultati e di ricalcolo delle quantità su tutte le “faccette”. In questo caso, come puoi facilmente vedere, dovrai occuparti di una richiesta per ottenere il numero totale di risultati e la relativa ottimizzazione. Questo metodo può essere trovato in molti piccoli negozi online. Ovviamente non si tratta di una panacea per questo problema, ma nei casi più semplici può rivelarsi un buon compromesso.
• Utilizza i motori di ricerca per trovare risultati e contare le sfaccettature, come Solr, ElasticSearch, Sphinx e altri. Tutti sono progettati per costruire “sfaccettature” e farlo in modo abbastanza efficiente grazie all’indice invertito. Come funzionano i motori di ricerca, perché in questi casi sono più efficaci dei database generici, quali pratiche e insidie ​​​​ci sono: questo è un argomento per un articolo a parte. Vorrei qui attirare la vostra attenzione sul fatto che il motore di ricerca non può sostituire la memoria principale dei dati, ma viene utilizzato come un'aggiunta: tutte le modifiche rilevanti per la ricerca nella banca dati principale vengono sincronizzate nell'indice di ricerca; Il motore di ricerca solitamente interagisce solo con il motore di ricerca e non accede al database principale. Uno dei punti più importanti qui è come organizzare questa sincronizzazione in modo affidabile. Tutto dipende dai requisiti del “tempo di reazione”. Se il tempo che intercorre tra una modifica nel database principale e la sua “manifestazione” nella ricerca non è critico, è possibile creare un servizio che cerchi i record modificati di recente ogni pochi minuti e li indicizzi. Se desideri il tempo di risposta più breve possibile, puoi implementare qualcosa di simile casella di posta transazionale per inviare aggiornamenti al servizio di ricerca.

risultati

1. L'implementazione del paging lato server è una complicazione significativa e ha senso solo per set di dati in rapida crescita o semplicemente di grandi dimensioni. Non esiste una ricetta assolutamente esatta su come valutare “grande” o “in rapida crescita”, ma seguirei questo approccio:
   • Se la ricezione di una raccolta completa di dati, tenendo conto dell'ora del server e della trasmissione di rete, soddisfa normalmente i requisiti di prestazione, non ha senso implementare il paging sul lato server.
   • Potrebbe verificarsi una situazione in cui non sono previsti problemi di prestazioni nel prossimo futuro, poiché i dati sono pochi, ma la raccolta dati è in costante crescita. Se qualche insieme di dati in futuro potrebbe non soddisfare più il punto precedente, è meglio iniziare subito la paginazione.
2. Se non esiste un requisito rigoroso da parte dell'azienda di visualizzare il numero totale di risultati o di visualizzare i numeri di pagina e il tuo sistema non dispone di un motore di ricerca, è meglio non implementare questi punti e considerare l'opzione n. 2.
3. Se esiste un requisito chiaro per la ricerca sfaccettata, hai due opzioni senza sacrificare le prestazioni:
   • Non ricalcolare tutte le quantità ogni volta che cambiano i criteri di ricerca.
   • Utilizza motori di ricerca come Solr, ElasticSearch, Sphinx e altri. Ma dovrebbe essere chiaro che non può sostituire il database principale e dovrebbe essere utilizzato come aggiunta alla memoria principale per risolvere i problemi di ricerca.
4. Inoltre, nel caso della ricerca a faccette, ha senso suddividere il recupero della pagina dei risultati di ricerca e il conteggio in due richieste parallele. Il conteggio delle quantità può richiedere più tempo dell'ottenimento dei risultati, mentre i risultati sono più importanti per l'utente.
5. Se si utilizza un database SQL per la ricerca, qualsiasi modifica al codice relativa a questa parte dovrebbe essere ben testata per le prestazioni sulla quantità appropriata di dati (superando il volume nel database attivo). È inoltre consigliabile utilizzare il monitoraggio del tempo di esecuzione delle query su tutte le istanze del database, e in particolare su quella “live”. Anche se tutto andava bene con i piani di query in fase di sviluppo, con l'aumentare del volume dei dati la situazione potrebbe cambiare notevolmente.

Fonte: habr.com