Evitare di utilizzare OFFSET e LIMIT nelle query impaginate

Sono finiti i giorni in cui non dovevi preoccuparti di ottimizzare le prestazioni del database. Il tempo non si ferma. Ogni nuovo imprenditore tecnologico vuole creare il prossimo Facebook, cercando allo stesso tempo di raccogliere tutti i dati su cui riesce a mettere le mani. Le aziende hanno bisogno di questi dati per addestrare meglio i modelli che le aiutano a guadagnare. In tali condizioni, i programmatori devono creare API che consentano loro di lavorare in modo rapido e affidabile con enormi quantità di informazioni.

Se progetti backend di applicazioni o database per un certo periodo di tempo, probabilmente hai scritto codice per eseguire query impaginate. Ad esempio, in questo modo:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Com'è?

Ma se è così che hai fatto l'impaginazione, mi dispiace dire che non l'hai fatto nel modo più efficiente.

Vuoi opporti a me? Puoi no da spendere tempo. Slack, Shopify: и MIXMAX Stanno già utilizzando le tecniche di cui voglio parlare oggi.

Nomina almeno uno sviluppatore backend che non ha mai utilizzato OFFSET и LIMIT per eseguire query impaginate. In MVP (Minimum Viable Product) e nei progetti in cui vengono utilizzate piccole quantità di dati, questo approccio è abbastanza applicabile. “Funziona e basta”, per così dire.

Ma se è necessario creare da zero sistemi affidabili ed efficienti, è necessario prestare attenzione in anticipo all'efficienza delle interrogazioni sui database utilizzati in tali sistemi.

Oggi parleremo dei problemi con le implementazioni comunemente usate (peccato) dei motori di query impaginate e di come ottenere prestazioni elevate durante l'esecuzione di tali query.

Cosa c'è di sbagliato in OFFSET e LIMIT?

Come già accennato, OFFSET и LIMIT Funzionano bene nei progetti che non necessitano di lavorare con grandi quantità di dati.

Il problema sorge quando il database raggiunge dimensioni tali da non poter più essere contenuto nella memoria del server. Tuttavia, quando si lavora con questo database, è necessario utilizzare query impaginate.

Perché questo problema si manifesti, deve esserci una situazione in cui il DBMS ricorre a un'operazione inefficiente di Full Table Scan su ogni query impaginata (mentre possono verificarsi operazioni di inserimento e cancellazione, e non abbiamo bisogno di dati obsoleti!).

Che cos'è una "scansione completa della tabella" (o "scansione sequenziale della tabella", scansione sequenziale)? Si tratta di un'operazione durante la quale il DBMS legge in sequenza ogni riga della tabella, cioè i dati in essa contenuti, e ne verifica il rispetto di una determinata condizione. Questo tipo di scansione della tabella è noto per essere il più lento. Il fatto è che quando viene eseguito, vengono eseguite molte operazioni di input/output che coinvolgono il sottosistema del disco del server. La situazione è aggravata dalla latenza associata all'utilizzo dei dati archiviati sui dischi e dal fatto che il trasferimento dei dati dal disco alla memoria è un'operazione ad alta intensità di risorse.

Ad esempio, disponi di record di 100000000 di utenti ed esegui una query con il costrutto OFFSET 50000000. Ciò significa che il DBMS dovrà caricare tutti questi record (e non ci servono nemmeno!), metterli in memoria, e poi prendere, diciamo, 20 risultati riportati in LIMIT.

Diciamo che potrebbe assomigliare a questo: "seleziona le righe da 50000 a 50020 da 100000". Cioè, il sistema dovrà prima caricare 50000 righe per completare la query. Vedi quanto lavoro inutile dovrà fare?

Se non mi credi, dai un'occhiata all'esempio che ho creato utilizzando le funzionalità db-fiddle.com. 

Esempio su db-fiddle.com

Là, a sinistra, nel campo Schema SQL, c'è un codice che inserisce 100000 righe nel database e, a destra, nel campo Query SQL, vengono visualizzate due query. Il primo, lento, assomiglia a questo:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

E il secondo, che è una soluzione efficace allo stesso problema, è così:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Per soddisfare queste richieste è sufficiente cliccare sul pulsante Run in cima alla pagina. Fatto ciò, confrontiamo le informazioni sul tempo di esecuzione della query. Si scopre che l'esecuzione di una query inefficace richiede almeno 30 volte più tempo dell'esecuzione della seconda (questo tempo varia da esecuzione a esecuzione; ad esempio, il sistema potrebbe segnalare che la prima query ha impiegato 37 ms per essere completata, ma l'esecuzione della secondo - 1 ms).

E se ci sono più dati, allora tutto sembrerà ancora peggio (per esserne convinto, dai un'occhiata al mio esempio con 10 milioni di righe).

Ciò di cui abbiamo appena discusso dovrebbe darti un'idea di come vengono effettivamente elaborate le query del database.

Tieni presente che maggiore è il valore OFFSET — maggiore sarà il tempo necessario per completare la richiesta.

Cosa dovrei usare al posto della combinazione di OFFSET e LIMIT?

Invece di una combinazione OFFSET и LIMIT Vale la pena utilizzare una struttura costruita secondo il seguente schema:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Questa è l'esecuzione della query con l'impaginazione basata sul cursore.

Invece di archiviare quelli correnti localmente OFFSET и LIMIT e trasmetterli ad ogni richiesta, è necessario memorizzare l'ultima chiave primaria ricevuta (di solito è ID) E LIMIT, di conseguenza, si otterranno query simili a quelle precedenti.

Perché? Il punto è che specificando esplicitamente l'identificatore dell'ultima riga letta, dici al tuo DBMS dove deve iniziare a cercare i dati necessari. Inoltre la ricerca, grazie all'utilizzo della chiave, verrà effettuata in modo efficiente; il sistema non dovrà farsi distrarre da linee fuori dal range specificato.

Diamo un'occhiata al seguente confronto delle prestazioni di varie query. Ecco una query inefficace.

Richiesta lenta

Ed ecco una versione ottimizzata di questa richiesta.

Richiesta veloce

Entrambe le query restituiscono esattamente la stessa quantità di dati. Ma il completamento del primo richiede 12,80 secondi e il secondo richiede 0,01 secondi. Senti la differenza?

Possibili problemi

Affinché il metodo di query proposto funzioni in modo efficace, la tabella deve avere una colonna (o colonne) contenente indici sequenziali univoci, come un identificatore intero. In alcuni casi specifici, ciò potrebbe determinare il successo dell'utilizzo di tali query per aumentare la velocità di lavoro con il database.

Naturalmente, quando si costruiscono le query, è necessario tenere conto dell'architettura specifica delle tabelle e scegliere i meccanismi che funzioneranno meglio sulle tabelle esistenti. Ad esempio, se devi lavorare su query con grandi volumi di dati correlati, potresti trovarlo interessante questo articolo.

Se ci troviamo di fronte al problema della mancanza di una chiave primaria, ad esempio, se abbiamo una tabella con una relazione molti-a-molti, allora l'approccio tradizionale di utilizzo OFFSET и LIMIT, è garantito per noi. Ma il suo utilizzo potrebbe comportare query potenzialmente lente. In questi casi, consiglierei di utilizzare una chiave primaria con incremento automatico, anche se è necessaria solo per gestire query impaginate.

Se sei interessato a questo argomento - qui, qui и qui - diversi materiali utili.

Risultati di

La conclusione principale che possiamo trarre è che, indipendentemente dalla dimensione dei database di cui stiamo parlando, è sempre necessario analizzare la velocità di esecuzione delle query. Al giorno d'oggi, la scalabilità delle soluzioni è estremamente importante e se tutto è progettato correttamente fin dall'inizio del lavoro su un determinato sistema, ciò in futuro può salvare lo sviluppatore da molti problemi.

Come analizzi e ottimizzi le query del database?

Fonte: habr.com