I giorni in cui non era necessario preoccuparsi dell'ottimizzazione delle prestazioni dei database sono finiti. Il tempo non si ferma. Ogni nuovo imprenditore nel settore tecnologico desidera creare un nuovo Facebook, cercando di raccogliere tutti i dati a cui riesce ad accedere. Questi dati sono necessari alle imprese per un'inferenza più accurata dei modelli, che aiutano a generare profitto. In queste condizioni, i programmatori devono creare API che consentano di lavorare rapidamente e in modo affidabile con enormi volumi di informazioni.
Se da un po' di tempo ti occupi della progettazione della parte server delle applicazioni o dei database, probabilmente hai scritto codice per l'esecuzione di query con paginazione. Ad esempio, qualcosa del genere:
SELECT * FROM table_name LIMIT 10 OFFSET 40
È così?
Ma se hai eseguito la paginazione in questo modo, posso dire con rammarico che non l'hai fatto nel modo più efficiente.
Vuoi contestarmi? . , e sono già in uso tecniche che voglio discutere oggi.
Nomina almeno un backend developer che non ha mai usato OFFSET e LIMIT per eseguire query con paginazione. In MVP (Minimum Viable Product, prodotto minimo funzionante) e in progetti dove vengono utilizzati piccoli volumi di dati, questo approccio è del tutto applicabile. In un certo senso, 'funziona e basta'.
Ma se è necessario costruire da zero sistemi affidabili ed efficienti, è importante occuparsi in anticipo dell'efficienza delle query eseguite sui database utilizzati in tali sistemi.
Oggi parleremo dei problemi associati alle implementazioni ampiamente diffuse (purtroppo) dei meccanismi per eseguire query con paginazione, e come ottenere alte prestazioni nell'esecuzione di tali query.
Cosa c'è di sbagliato con OFFSET e LIMIT?
Come già detto, OFFSET e LIMIT funzionano perfettamente in progetti in cui non è necessario lavorare con grandi volumi di dati.
Il problema sorge quando il database cresce a tal punto che smette di entrare nella memoria del server. Ma, nel frattempo, è necessario eseguire query con paginazione su questo database.
Affinché questo problema si manifesti, deve verificarsi una situazione in cui il DBMS ricorre a un'operazione inefficiente di scansione completa della tabella (Full Table Scan) per ogni query con paginazione (nel frattempo, possono avvenire operazioni di inserimento e cancellazione di dati, e i dati obsoleti non ci servono!).
Cosa si intende per 'scansione completa della tabella' (o 'scansione sequenziale della tabella', Sequential Scan)? È un'operazione in cui il DBMS legge sequenzialmente ogni riga della tabella, ossia i dati in essa contenuti, e verifica la loro corrispondenza con le condizioni date. È noto che questo tipo di scansione è il più lento. Il motivo è che viene eseguita molte operazioni di input/output che coinvolgono il sistema di archiviazione del server. La situazione è aggravata dai ritardi associati al lavoro con i dati memorizzati su disco, e il fatto che il trasferimento dei dati dal disco alla memoria è un'operazione dispendiosa in termini di risorse.
Ad esempio, hai registrazioni di 100000000 utenti e stai eseguendo una query con la struttura OFFSET 50000000. Questo significa che il DBMS dovrà caricare tutte queste registrazioni (e noi non ne abbiamo nemmeno bisogno!), metterle in memoria e solo allora prendere, per esempio, 20 risultati, come indicato in LIMIT.
Diciamo che potrebbe apparire così: 'seleziona righe da 50000 a 50020 da 100000'. Cioè, per eseguire questa query, il sistema dovrà prima caricare 50000 righe. Vedi quante operazioni inutili dovrà eseguire?
Se non ci credi, dai un'occhiata all'esempio che ho creato usando le funzionalità di .
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, ci sono due query. La prima, lenta, è così:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
E la seconda, che è una soluzione efficiente dello stesso problema, è così:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Per eseguire queste query, basta premere il pulsante Esegui nella parte superiore della pagina. Facendo ciò, confronteremo i dati sui tempi di esecuzione delle query. Si scopre che l'esecuzione della query inefficiente richiede, almeno, 30 volte più tempo rispetto all'esecuzione della seconda (da un'esecuzione all'altra, questo tempo varia; ad esempio, il sistema potrebbe comunicare che la prima query richiede 37 ms e la seconda 1 ms).
E se i dati aumenteranno, tutto apparirà ancora peggio (per verificarlo, dai un'occhiata al mio con 10 milioni di righe).
Ciò che abbiamo appena discusso dovrebbe darti una certa comprensione di come vengono realmente elaborati le richieste ai database.
Tieni presente che maggiore è il valore OFFSET — più a lungo ci vorrà per eseguire la richiesta.
Cosa dovresti usare invece della combinazione OFFSET e LIMIT?
Invece della combinazione OFFSET e LIMIT dovresti usare una struttura basata su questo schema:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
Questo è l'esecuzione di una richiesta con paginazione basata su cursore.
Invece di memorizzare localmente gli attuali OFFSET e LIMIT e passarli con ogni richiesta, dovresti memorizzare l'ultima chiave primaria ricevuta (di solito è ID) e LIMIT, e così si otterranno richieste simili a quella sopra citata.
Perché? Il fatto è che, specificando esplicitamente l'identificatore dell'ultima riga letta, informi il tuo DBMS su dove iniziare a cercare i dati richiesti. Inoltre, grazie all'uso della chiave, la ricerca sarà efficiente, senza che il sistema debba distrarsi su righe al di fuori dell'intervallo specificato.
Diamo un'occhiata al seguente confronto delle prestazioni di diverse richieste. Ecco una richiesta inefficiente.
Richiesta lenta
Ecco una versione ottimizzata di questa richiesta.
Richiesta veloce
Entrambe le richieste restituiscono esattamente lo stesso volume di dati. Ma il primo impiega 12,80 secondi per completarsi, mentre il secondo solo 0,01 secondo. Senti la differenza?
Problemi potenziali
Per garantire il funzionamento efficace del metodo proposto per l'esecuzione delle richieste, è necessario che nella tabella ci sia una colonna (o colonne) contenente indici univoci e disposti in modo continuo, come un identificatore intero. In alcuni casi specifici, questo può determinare il successo dell'uso di queste richieste per migliorare la velocità con cui si interagisce con il database.
Naturalmente, quando si costruiscono le richieste, è importante considerare le caratteristiche dell'architettura delle tabelle e scegliere i meccanismi che funzionano meglio sulle tabelle esistenti. Ad esempio, se è necessario lavorare con grandi volumi di dati correlati, potrebbe risultarti interessante articolo.
Se ci troviamo di fronte al problema dell'assenza di una chiave primaria, ad esempio se c'è una tabella con una relazione "molti-a-molti", allora l'approccio tradizionale che prevede l'uso di OFFSET e LIMIT, sarà sicuramente adatto. Ma il suo utilizzo può portare all'esecuzione di richieste potenzialmente lente. In tali casi, consiglierei di utilizzare una chiave primaria con auto-incremento, anche se necessaria solo per organizzare l'esecuzione delle richieste con paginazione.
Se sei interessato a questo argomento — , e — alcuni materiali utili.
Risultati
La principale conclusione che possiamo trarre è che, indipendentemente dalle dimensioni dei database di cui si tratta, è sempre necessario analizzare la velocità di esecuzione delle richieste. Oggi la scalabilità delle soluzioni è estremamente importante, e se dall'inizio del lavoro su un sistema si progetta tutto correttamente, ciò può evitare al programmatore molti problemi in futuro.
Come analizzi e ottimizzi le richieste ai database?
Fonte: habr.com
