Ciao a tutti! Sono uno sviluppatore backend che scrive microservizi in Java + Spring. Lavoro in uno dei team interni di sviluppo prodotto presso Tinkoff.
Nel nostro team si pone spesso la questione dell'ottimizzazione delle query in un DBMS. Vuoi sempre essere un po' più veloce, ma non puoi sempre cavartela con indici costruiti in modo accurato: devi cercare alcune soluzioni alternative. Durante uno di questi vagabondaggi sul web alla ricerca di ottimizzazioni ragionevoli quando si lavora con i database, ho trovato , autore di Spiegazione delle prestazioni SQL. Questo è quel raro tipo di blog in cui puoi leggere tutti gli articoli di seguito.
Vorrei tradurre per voi un breve articolo di Marcus. In una certa misura può essere definito un manifesto che cerca di attirare l'attenzione sul vecchio, ma ancora rilevante problema dell'esecuzione dell'operazione di offset secondo lo standard SQL.
In alcuni punti integrerò l'autore con spiegazioni e commenti. Mi riferirò a tutti questi luoghi come "circa". per maggiore chiarezza
Piccola introduzione
Penso che molte persone sappiano quanto sia problematico e lento lavorare con la selezione delle pagine tramite offset. Sapevi che può essere facilmente sostituito con un design più efficiente?
Pertanto, la parola chiave offset indica al database di ignorare i primi n record nella richiesta. Tuttavia, il database deve ancora leggere questi primi n record dal disco, nell'ordine indicato (nota: applicare l'ordinamento se specificato), e solo allora sarà possibile restituire i record da n+1 in poi. La cosa più interessante è che il problema non sta nell'implementazione specifica nel DBMS, ma nella definizione originale secondo lo standard:
…le righe vengono prima ordinate in base al e quindi limitato eliminando il numero di righe specificate nel file dall'inizio...
-SQL:2016, Parte 2, 4.15.3 Tabelle derivate (nota: attualmente lo standard più utilizzato)
Il punto chiave qui è che l'offset accetta un singolo parametro: il numero di record da saltare, e il gioco è fatto. Seguendo questa definizione, il DBMS può solo recuperare tutti i record e poi scartare quelli non necessari. Ovviamente, questa definizione di offset ci costringe a fare un lavoro extra. E non importa nemmeno se si tratti di SQL o NoSQL.
Solo un po' più di dolore
I problemi con l'offset non finiscono qui, ed ecco perché. Se, tra la lettura di due pagine di dati dal disco, un'altra operazione inserisce un nuovo record, cosa accadrà in questo caso?
Quando l'offset viene utilizzato per saltare i record dalle pagine precedenti, nella situazione di aggiungere un nuovo record tra letture di pagine diverse, molto probabilmente otterrai duplicati (nota: questo è possibile quando leggiamo pagina per pagina utilizzando l'ordine per costrutto, quindi nel mezzo del nostro output potrebbe essere presente una nuova voce).
La figura illustra chiaramente questa situazione. La base legge i primi 10 record, dopodiché viene inserito un nuovo record, che compensa di 1 tutti i record letti. Quindi la base prende una nuova pagina dai successivi 10 record e inizia non dall'11, come dovrebbe, ma da quella 10°, duplicando questo record. Esistono altre anomalie associate all'uso di questa espressione, ma questa è la più comune.
Come abbiamo già scoperto, questi non sono problemi di uno specifico DBMS o delle sue implementazioni. Il problema sta nel definire l'impaginazione secondo lo standard SQL. Diciamo al DBMS quale pagina recuperare o quanti record saltare. La banca dati semplicemente non è in grado di ottimizzare tale richiesta, poiché le informazioni a riguardo sono troppo poche.
Vale anche la pena chiarire che non si tratta di un problema con una parola chiave specifica, ma piuttosto con la semantica della query. Esistono molte altre sintassi identiche nella loro natura problematica:
- La parola chiave offset è quella menzionata in precedenza.
- Una costruzione di due parole chiave limit [offset] (anche se limit in sé non è poi così male).
- Filtraggio in base ai limiti inferiori, in base alla numerazione delle righe (ad esempio, row_number(), rownum e così via).
Tutte queste espressioni ti dicono semplicemente quante righe saltare, senza informazioni o contesto aggiuntivi.
Più avanti in questo articolo, la parola chiave offset verrà utilizzata come riepilogo di tutte queste opzioni.
La vita senza OFFSET
Ora immaginiamo come sarebbe il nostro mondo senza tutti questi problemi. Si scopre che la vita senza offset non è così difficile: con una selezione puoi selezionare solo quelle righe che non abbiamo ancora visto (nota: cioè quelle che non erano nella pagina precedente), utilizzando una condizione in where.
In questo caso partiamo dal fatto che le select vengono eseguite su un set ordinato (il buon vecchio order by). Dato che abbiamo un insieme ordinato, possiamo utilizzare un filtro abbastanza semplice per ottenere solo i dati che si trovano dietro l'ultimo record della pagina precedente:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYQuesto è il principio centrale di questo approccio. Naturalmente, le cose diventano più divertenti quando si ordina in base a molte colonne, ma l'idea è sempre la stessa. È importante notare che questo design è applicabile a molti -decisioni.
Questo approccio è chiamato metodo di ricerca o impaginazione del keyset. Risolve il problema del risultato mobile (nota: la situazione con la scrittura tra le letture di una pagina descritta in precedenza) e, ovviamente, ciò che tutti amiamo, funziona più velocemente e in modo più stabile rispetto al classico offset. La stabilità sta nel fatto che il tempo di elaborazione della richiesta non aumenta in proporzione al numero della tabella richiesta (nota: se vuoi saperne di più sul lavoro dei diversi approcci all'impaginazione, puoi . Puoi anche trovare benchmark comparativi per diversi metodi lì).
Una delle diapositive quella impaginazione per chiavi, ovviamente, non è onnipotente: ha i suoi limiti. La cosa più significativa è che non ha la capacità di leggere pagine casuali (nota: in modo incoerente). Tuttavia, nell'era dello scorrimento infinito (nota: sul front-end), questo non è un grosso problema. Specificare un numero di pagina su cui fare clic è comunque una decisione sbagliata nella progettazione dell'interfaccia utente (nota: opinione dell'autore dell'articolo).
E gli strumenti?
L'impaginazione su 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 consentono di scegliere esattamente come verrà eseguita l'impaginazione.
La situazione è aggravata dal fatto che il metodo descritto richiede il supporto end-to-end nelle tecnologie utilizzate, dal DBMS all'esecuzione di una richiesta AJAX nel browser con scorrimento infinito. Invece di specificare solo il numero di pagina, ora devi specificare un set di chiavi per tutte le pagine contemporaneamente.
Tuttavia, il numero di framework che supportano l’impaginazione sulle chiavi sta gradualmente crescendo. Ecco cosa abbiamo al momento:
- per Java;
- per Rubino;
- и per Django;
- per Python;
- — criteri API per le implementazioni dell'APP;
- per Perl;
- , mappatore per Node.js .
(Nota: alcuni link sono stati rimossi a causa del fatto che al momento della traduzione alcune librerie non erano state aggiornate dal 2017-2018. Se sei interessato puoi guardare la fonte originale.)
È in questo momento che è necessario il tuo aiuto. Se sviluppi o supporti un framework che fa uso dell'impaginazione, allora ti chiedo, ti esorto, ti imploro di fornire il supporto nativo per l'impaginazione sulle chiavi. Se hai domande o hai bisogno di aiuto, sarò felice di aiutarti (, , ) (nota: dalla mia esperienza con Marcus, posso dire che è davvero entusiasta di diffondere questo argomento).
Se utilizzi soluzioni già pronte che ritieni meritevoli di supporto per l'impaginazione tramite chiavi, crea una richiesta o addirittura offri una soluzione già pronta, se possibile. Puoi anche collegarti a questo articolo.
conclusione
Il motivo per cui un approccio così semplice e utile come l’impaginazione per chiavi non è diffuso non è che sia difficile da implementare tecnicamente o richieda grandi sforzi. Il motivo principale è che molti sono abituati a vedere e lavorare con l'offset: questo approccio è dettato dallo standard stesso.
Di conseguenza, poche persone pensano a cambiare l'approccio all'impaginazione e, per questo motivo, il supporto strumentale da parte di framework e librerie si sta sviluppando poco. Pertanto, se l’idea e l’obiettivo dell’impaginazione senza offset ti è vicino, aiutaci a diffonderlo!
Fonte:
Autore: Markus Winand
Fonte: habr.com