Ciao a tutti! Sono uno sviluppatore backend, scrivo microservizi in Java + Spring. Lavoro in uno dei team di sviluppo dei prodotti interni di Tinkoff.
Nel nostro team si pone spesso la questione dell'ottimizzazione delle query nel DBMS. Si desidera sempre un po' più velocità, ma non sempre si possono usare indici ben strutturati: bisogna cercare delle soluzioni alternative. Durante uno di questi vagabondaggi in rete alla ricerca di ottimizzazioni sensate nella gestione dei database, ho trovato , autore del libro SQL Performance Explained. Questo è quel raro tipo di blog dove si possono leggere tutti gli articoli in sequenza.
Voglio tradurre per voi un breve articolo di Markus. Può essere definito in una certa misura un manifesto, che mira a richiamare l'attenzione su un problema vecchio, ma ancora attuale, relativo alle prestazioni dell'operazione offset secondo lo standard SQL.
In alcuni punti aggiungerò spiegazioni e osservazioni dell'autore. Tutti questi punti saranno contrassegnati come «note» per maggiore chiarezza.
Piccola introduzione
Penso che molti sappiano quanto sia problematica e lenta la gestione delle selezioni paginabili tramite offset. E sapete che può essere sostituita con una costruzione molto più performante?
Dunque, la parola chiave offset indica al database di saltare i primi n record nella query. Tuttavia, il database deve ancora leggere questi primi n record dal disco, e in un ordine specificato (nota: applicare ordinamento se è stato specificato), e solo dopo sarà possibile restituire i record a partire da n+1 e oltre. La cosa più interessante è che il problema non è nella specifica implementazione nel DBMS, ma nella definizione iniziale secondo lo standard:
…the rows are first sorted according to the and then limited by dropping the number of rows specified in the from the beginning…
-SQL:2016, Part 2, 4.15.3 Derived tables (nota: attualmente lo standard più utilizzato)
Il punto chiave qui è che l'offset accetta un solo parametro: il numero di record da saltare, e basta. Seguendo tale definizione, il DBMS può solo recuperare tutti i record e poi scartare quelli non necessari. È evidente che tale definizione di offset obbliga a svolgere lavoro superfluo. E non importa se si tratta di SQL o NoSQL.
Un po' di dolore in più
I problemi di offset non finiscono qui, ecco perché. Se tra la lettura di due pagine di dati dal disco un'altra operazione inserisce una nuova registrazione, cosa succede in questo caso?
Quando si utilizza l'offset per saltare le registrazioni delle pagine precedenti, in una situazione in cui viene aggiunta una nuova registrazione tra le operazioni di lettura di pagine diverse, è probabile che si ottengano duplicati (nota: ciò è possibile quando leggiamo pagina per pagina utilizzando la clausola order by, quindi una nuova registrazione può finire nel mezzo della nostra estrazione).
L'immagine illustra chiaramente questa situazione. Il database legge le prime 10 registrazioni, poi viene inserita una nuova registrazione che sposta tutte le registrazioni lette di 1. Quindi il database prende una nuova pagina delle successive 10 registrazioni e non inizia dall'11ª, come dovrebbe, ma dalla 10ª, duplicando questa registrazione. Ci sono altre anomalie legate all'uso di questa espressione, ma questa è la più comune.
Come abbiamo già stabilito, non si tratta di problemi specifici di un singolo DBMS o delle loro implementazioni. Il problema risiede nella definizione della paginazione secondo lo standard SQL. Indichiamo al DBMS quale pagina dobbiamo estrarre o quante registrazioni saltare. Il database semplicemente non può ottimizzare una tale query, poiché ha troppe poche informazioni.
Vale la pena precisare che questo non è un problema legato a una specifica parola chiave, ma piuttosto alla semantica della query. Ci sono anche altri syntaxes identici per problematicità:
- La parola chiave offset, come già detto in precedenza.
- La costruzione di due parole chiave limit [offset] (anche se limit di per sé non è così male).
- Filtraggio basato sui limiti inferiori, costruito sulla numerazione delle righe (ad esempio, row_number(), rownum, ecc.).
Tutte queste espressioni semplicemente indicano quante righe devono essere saltate, senza fornire ulteriori informazioni o contesto.
Nell'articolo successivo, la parola chiave offset viene utilizzata come generalizzazione di tutte queste varianti.
Vita senza OFFSET
Ora immaginiamo come sarebbe il nostro mondo senza tutti questi problemi. A quanto pare, la vita senza offset non è così complicata: con una select si possono scegliere solo le righe che non abbiamo ancora visto (nota: cioè quelle che non erano presenti nella pagina precedente), attraverso una condizione in where.
In questo caso ci basiamo sul fatto che le selezioni vengono eseguite su un insieme ordinato (il buon vecchio order by). Poiché abbiamo un insieme ordinato, possiamo utilizzare un filtro abbastanza semplice per estrarre solo i dati che si trovano dopo l'ultima registrazione della pagina precedente:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYQuesto è il principio di questo approccio. Certo, quando si ordina su molte colonne le cose si fanno più interessanti, ma l'idea rimane la stessa. È importante notare che questa costruzione è applicabile a molti -soluzioni.
Questo approccio si chiama seek method o keyset pagination. Risolve il problema del risultato fluttuante (nota: la situazione di una registrazione tra letture delle pagine, descritta in precedenza) e, naturalmente, come tutti noi amiamo, funziona più velocemente e in modo più stabile rispetto all'offset classico. La stabilità consiste nel fatto che il tempo di elaborazione della richiesta non aumenta proporzionalmente al numero della tabella richiesta (nota: se desideri saperne di più sui vari approcci alla paginazione, puoi . Lì puoi anche trovare benchmark comparativi sui diversi metodi).
Una delle diapositive E per quanto riguardo gli strumenti?
La paginazione per chiavi spesso non è adatta a causa della mancanza di supporto strumentale per questo metodo. La maggior parte degli strumenti di sviluppo, inclusi vari framework, non permette di scegliere quale metodo verrà utilizzato per eseguire la paginazione.
La situazione è aggravata dal fatto che il metodo descritto richiede un supporto trasversale nelle tecnologie utilizzate — a partire dal DBMS e arrivando all'esecuzione delle richieste AJAX nel browser durante lo scrolling infinito. Invece di indicare solo il numero della pagina, ora sarà necessario specificare un insieme di chiavi per tutte le pagine contemporaneamente.
La situazione è aggravata dal fatto che il metodo descritto richiede un supporto continuo nelle tecnologie utilizzate, a partire dal database fino all'esecuzione di richieste AJAX nel browser durante lo scrolling infinito. Invece di indicare solo il numero della pagina, ora dovremo indicare un insieme di chiavi per tutte le pagine contemporaneamente.
Tuttavia, il numero di framework che supportano la paginazione basata su chiavi sta crescendo gradualmente. Ecco cosa c'è al momento:
- per Java;
- per Ruby;
- e per Django;
- per Python;
- — API dei criteri per implementazioni JPA;
- per Perl;
- , мапер для Node.js .
(N.d.R.: alcuni link sono stati rimossi poiché al momento della traduzione alcune librerie non erano aggiornate dal 2017-2018. Se siete interessati, potete consultare la fonte originale.)
Proprio in questo momento è necessaria la vostra assistenza. Se state sviluppando o mantenendo un framework che in qualche modo utilizza la paginazione, vi prego di fare, vi esorto, vi supplico di implementare un supporto nativo per la paginazione basata su chiavi. Se ci sono domande o avete bisogno di aiuto, sarò felice di assistervi (, , ) (N.d.R.: dalla mia esperienza con Markus posso dire che è veramente entusiasta nel promuovere questo argomento).
Se state utilizzando soluzioni già pronte che ritenete meritevoli di avere il supporto per la paginazione basata su chiavi, create una richiesta o proponete anche una soluzione pronta, se possibile. È anche possibile menzionare questo articolo nel link.
Conclusione
Il motivo per cui un approccio così semplice e utile come la paginazione basata su chiavi è poco diffuso non è dovuto alla sua complessità tecnica o alla necessità di sforzi significativi. La principale ragione è che molti sono abituati a vedere e lavorare con l'offset — questo approccio è dettato dallo standard stesso.
Di conseguenza, in pochi riflettono sulla possibilità di cambiare approccio nella paginazione, e per questo motivo il supporto strumentale da parte dei framework e delle librerie si sviluppa lentamente. Quindi, se vi piace l'idea e l'obiettivo della paginazione senza offset, aiutate a diffonderla!
Fonte:
Autore: Markus Winand
Fonte: habr.com
