Come ottimizzare le query SQL per un recupero dati più veloce

Optimizing SQL queries for faster data retrieval.

Oggi parleremo di perché l’ottimizzazione delle query SQL è importante e quali tecniche possono essere utilizzate per ottimizzarla.

SQL (Structured Query Language), come probabilmente sapete, aiuta a raccogliere dati dai database.

È appositamente progettato per questo. In altre parole, funziona con righe e colonne, consentendoti di manipolare i dati dai database usando le query SQL.

Cosa è una query SQL?

Una query SQL è un insieme di istruzioni che si danno al database per raccogliere informazioni da esso.

Puoi raccogliere e manipolare i dati dal database utilizzando queste query.

Usandole, puoi creare report, eseguire analisi dei dati e altro ancora.

A causa della forma e della lunghezza di queste query, i tempi di esecuzione possono essere significativi, soprattutto se si lavora con tabelle di dati più grandi.

Perché abbiamo bisogno dell’ottimizzazione delle query SQL?

Lo scopo dell’ottimizzazione delle query SQL è di assicurarsi che si utilizzino efficientemente le risorse. In parole povere, riduce il tempo di esecuzione, risparmia costi e migliora le prestazioni. È una competenza importante per sviluppatori e analisti di dati. Non è solo importante restituire i dati corretti dal database. È anche importante sapere quanto efficientemente si fa questo.

Dovresti sempre chiederti: “C’è un modo migliore per scrivere la mia query?”

Parliamo più a fondo dei motivi per questo.

Efficienza delle risorse: le query SQL scarsamente ottimizzate consumano risorse di sistema eccessive, come CPU e memoria. Ciò potrebbe portare a una riduzione delle prestazioni complessive del sistema. L’ottimizzazione delle query SQL garantisce che queste risorse vengano utilizzate in modo efficiente. Ciò porta a prestazioni e scalabilità migliori.

Riduzione del tempo di esecuzione: se le query vengono eseguite lentamente, ciò avrà un impatto negativo sull’esperienza dell’utente. O sulla prestazione di un’applicazione se si dispone di un’applicazione in esecuzione. L’ottimizzazione delle query può aiutare a ridurre il tempo di esecuzione, fornendo tempi di risposta più rapidi e un’esperienza utente migliore.

Risparmi sui costi: le query ottimizzate possono ridurre l’hardware e l’infrastruttura necessari per supportare il sistema di database. Ciò può portare a risparmi sui costi di hardware, energia e manutenzione.

Controlla “Best Practices per scrivere query SQL” che possono aiutarti a scoprire come migliorare la struttura del tuo codice, anche se è corretto.

Tecniche di ottimizzazione delle query SQL

Ecco una panoramica delle tecniche di ottimizzazione delle query SQL che copriremo in questo articolo.

Ecco il diagramma di flusso che mostra i passaggi suggeriti da seguire durante l’ottimizzazione della query SQL. Seguiremo lo stesso approccio nei nostri esempi. Gli strumenti di ottimizzazione possono anche aiutare a migliorare le prestazioni delle query. Quindi, esploriamo queste tecniche iniziando con il comando SQL ben noto, SELECT.

Usa SELECT con campi specificati invece di SELECT *

Quando si utilizza SELECT *, verranno restituite tutte le righe e tutte le colonne dalla/e tabella/e. Devi chiederti se ne hai davvero bisogno.

Invece di esaminare l’intero database, utilizza i campi specifici dopo SELECT.

Nell’esempio, sostituiremo SELECT * con nomi di colonne specifici. Come vedrete, ciò ridurrà la quantità di dati recuperati.

Di conseguenza, le query vengono eseguite più rapidamente poiché il database deve ottenere e fornire le colonne richieste, non tutte le colonne della tabella.

Ciò riduce il carico di I/O sul database, il che è particolarmente utile quando una tabella include molte colonne o molte righe di dati.

Ecco il codice prima dell’ottimizzazione.

SELECT * FROM customer;

Ecco l’output.

Il tempo di esecuzione totale della query è di 260 msec. Ciò può essere migliorato.

Per dimostrartelo, selezionerò solo 3 colonne diverse invece di selezionarle tutte.

Puoi selezionare la colonna di cui hai bisogno in base alle esigenze del tuo progetto.

Ecco il codice.

SELECT customer_id, 
       age, 
       country 
FROM customer;

E questo è l’output.

Come si può vedere, definendo i campi che vogliamo selezionare, non costringiamo il database a scansionare tutti i dati che ha, quindi il tempo di esecuzione viene ridotto da 260 a 79 millisecondi.

Immagina quale sarebbe la differenza con milioni o miliardi di righe. O centinaia di colonne.

Evita di usare SELECT DISTINCT

SELECT DISTINCT viene utilizzato per restituire i valori unici in una determinata colonna. Per fare ciò, il motore del database deve scansionare l’intera tabella e rimuovere i valori duplicati. In molti casi, l’utilizzo di un approccio alternativo come GROUP BY può portare a una migliore performance riducendo il numero di dati elaborati.

Ecco il codice.

SELECT DISTINCT segment 
FROM customer;

Ecco l’output.

Il nostro codice recupera i valori unici nella colonna segment dalla tabella customer. Il motore del database deve elaborare tutti i record nella tabella, identificare i valori duplicati e restituire solo i valori unici. Questo può essere costoso in termini di tempo e risorse, soprattutto per grandi tabelle.

Nella versione alternativa, la seguente query recupera i valori unici nella colonna segment utilizzando una clausola GROUP BY. La clausola GROUP BY raggruppa i record in base alle colonne specificate e restituisce un record per ogni gruppo.

Ecco il codice.

SELECT segment
FROM customer
GROUP BY segment;

Ecco l’output.

In questo caso, la clausola GROUP BY raggruppa efficacemente i record in base alla colonna segment, risultando nello stesso output della query SELECT DISTINCT.

Evitando SELECT DISTINCT e usando invece GROUP BY, è possibile ottimizzare le query SQL e ridurre il tempo totale della query da 198 a 62 millisecondi, che è più di 3 volte più veloce.

Evita di usare i cicli

I cicli potrebbero rendere la tua query più lenta poiché costringono il database a passare attraverso i record uno per uno.

Quando possibile, utilizza le operazioni integrate e le funzioni SQL, che possono sfruttare le ottimizzazioni del motore del database e elaborare i dati in modo più efficiente.

Definiamo una funzione personalizzata con un ciclo.

CREATE OR REPLACE FUNCTION sum_ages_with_loop() RETURNS TABLE (country_name TEXT, sum_age INTEGER) AS $$
DECLARE
    country_record RECORD;
    age_sum INTEGER;
BEGIN
    FOR country_record IN SELECT DISTINCT country FROM customer WHERE segment = 'Corporate'
    LOOP
        SELECT SUM(age) INTO age_sum FROM customer WHERE country = country_record.country AND segment = 'Corporate';
        country_name := country_record.country;
        sum_age := age_sum;
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Il codice sopra utilizza un approccio basato su ciclo per calcolare la somma delle età per ogni paese in cui il segmento del cliente è ‘Corporate’.

Recupera prima un elenco di paesi distinti e poi itera attraverso ciascun paese utilizzando un ciclo, calcolando la somma delle età dei clienti in quel paese. Questo approccio può essere lento e inefficiente, poiché elabora i dati riga per riga.

Adesso eseguiamo questa funzione.

SELECT * 
FROM sum_ages_with_loop()

Ecco l’output.

Il tempo di esecuzione con questo approccio è di 198 millisecondi.

Vediamo adesso il nostro codice SQL ottimizzato.

SELECT country, 
       SUM(age) AS sum_age
FROM customer
WHERE segment = 'Corporate'
GROUP BY country;

Ecco il suo output.

In generale, la versione ottimizzata utilizzando una singola query SQL funzionerà meglio, poiché sfrutta le capacità di ottimizzazione del motore del database.

Per ottenere lo stesso risultato del nostro primo codice, utilizziamo un loop in una funzione PL/pgSQL, che è spesso più lenta e meno efficace rispetto a farlo con una singola query SQL. E ci costringe a scrivere molte più righe di codice!

Usare i Wildcard Correttamente

L’uso corretto dei wildcard è vitale per ottimizzare le query SQL, soprattutto quando si tratta di corrispondenze di stringhe e modelli.

I wildcard sono caratteri speciali utilizzati nelle query SQL per trovare modelli specifici.

I wildcard più comuni in SQL sono “%” e “_”, dove “%” rappresenta qualsiasi sequenza di caratteri e “_” rappresenta un singolo carattere.

Utilizzare i wildcard in modo saggio è importante perché un uso improprio può portare a problemi di prestazioni, soprattutto in grandi database.

Tuttavia, utilizzarli in modo efficiente può migliorare notevolmente le prestazioni delle query di corrispondenza di stringhe e modelli.

Ora vediamo il nostro esempio.

Questa query utilizza la funzione RIGHT() per estrarre gli ultimi tre caratteri della colonna customer_name e quindi verifica se sono uguali a ‘son’.

SELECT customer_name
FROM customer
WHERE RIGHT(customer_name, 3) = 'son';

Ecco l’output.

Anche se questa query raggiunge il risultato desiderato, non è così efficiente perché la funzione RIGHT() deve essere applicata a ogni riga della tabella.

Optimizziamo il nostro codice utilizzando i wildcard.

SELECT customer_name
FROM customer
WHERE customer_name LIKE '%son';

Ecco l’output.

Questa query SQL ottimizzata utilizza l’operatore LIKE e il wildcard “%” per cercare i record in cui la colonna customer_name termina con ‘son’.

Questo approccio è più efficiente perché sfrutta le capacità di corrispondenza dei modelli del motore di database e può fare un uso migliore degli indici se disponibili.

E come possiamo vedere, il tempo totale della query è stato ridotto da 436 msec a 62 msec, quasi 7 volte più veloce.

Usare Top o LIMIT per Limitare il Numero di Risultati di Esempio

Usare TOP o LIMIT per limitare i risultati di esempio è fondamentale per ottimizzare le query SQL, in particolare quando si lavora con grandi tabelle.

Queste clausole consentono di recuperare solo un numero specificato di record da una tabella anziché tutti i record, il che può essere vantaggioso per le prestazioni.

Ora, recuperiamo tutte le informazioni dalla tabella customer.

SELECT *
FROM customer

Ecco l’output.

Quando si lavora con tabelle più grandi, questa operazione può aumentare l’I/O e la latenza di rete, il che potrebbe ridurre le prestazioni della query SQL.

Optimizziamo il nostro codice limitando l’output a 10.

SELECT *
FROM customer
LIMIT 10;

Ecco l’output.

Limitando l’output, si ridurrà la latenza di rete e l’utilizzo della memoria e si migliorerà il tempo di risposta, soprattutto con tabelle più grandi. Nel nostro esempio, dopo l’ottimizzazione della query SQL, il tempo totale di esecuzione della query è stato ridotto da 260 msec a 89 msec.

Quindi la nostra query diventa quasi 3 volte più veloce.

Usare gli Indici

Questa volta, identifichiamo e creiamo gli indici appropriati per le colonne utilizzate nelle clausole WHERE, JOIN e ORDER BY per migliorare le prestazioni della query.

Indicizzando le colonne frequentemente utilizzate, il database può recuperare i dati più velocemente.

Ora, eseguiamo prima la seguente query.

SELECT customer_id, 
       customer_name
FROM customer
WHERE segment = 'Corporate';

Ecco l’output.

Il tempo di esecuzione della nostra query è di 259 msec.

Cerchiamo di migliorarlo creando l’indice.

CREATE INDEX idx_segment ON customer (segment);

Ottimo, ora eseguiamo di nuovo il codice.

SELECT customer_id, 
       customer_name
FROM customer WITH (INDEX(idx_segment))
WHERE segment = 'Corporate';

Ecco l’output.

Utilizzando idx_segment in INDEX(), il motore di database è stato in grado di cercare efficientemente nella tabella dei clienti in base alla colonna segment, rendendo più veloce la query – ha ridotto il tempo totale della query da 259 msec a 75 msec.

Sezione Bonus: Utilizzare gli strumenti di ottimizzazione delle query SQL

A causa della complessità di codici lunghi e query altamente complesse, potresti considerare l’uso di strumenti di ottimizzazione delle query.

Questi strumenti possono analizzare i piani di esecuzione delle tue query, identificare gli indici mancanti e suggerire strutture di query alternative per ottimizzare le tue query. Alcuni strumenti di ottimizzazione delle query popolari includono:

  1. Analizzatore delle prestazioni del database SolarWinds: Questo strumento ti aiuta a tenere d’occhio e migliorare le prestazioni del database. Ti mostra i problemi con le query e come vengono eseguite. Funziona con diversi sistemi di database come SQL Server, Oracle e MySQL.

Puoi trovarlo qui.

  1. SQL Query Tuner per SQL Diagnostic Manager: Questo strumento ha funzionalità avanzate per migliorare le query, come suggerimenti sulle prestazioni, verifica degli indici e visualizzazione di come vengono eseguite le query. Ti aiuta a migliorare le query SQL trovando e risolvendo problemi.
  2. SQL Server Management Studio (SSMS): SSMS ha strumenti integrati per verificare le prestazioni e migliorare le query, come Activity Monitor, Execution Plan Analysis e Index Tuning Wizard.
  3. EverSQL: EverSQL è uno strumento online che migliora automaticamente le tue query guardando la struttura del database e come vengono eseguite le query. Ti fornisce consigli e riscrive le tue query SQL per renderle più veloci.

L’utilizzo di strumenti e risorse di ottimizzazione delle query SQL è vitale per migliorare le tue query. Con questi strumenti, puoi imparare come funzionano le tue query, trovare problemi e utilizzare le migliori pratiche per ottenere dati più velocemente e migliorare le tue applicazioni.

Se vuoi semplificare le tue query SQL complesse, guarda questa “Come semplificare le query SQL complesse”.

Note Finali

Le modifiche che abbiamo apportato ottimizzando le query SQL sopra potrebbero sembrare insignificanti a causa della loro scala (ms). Ma man mano che la quantità di dati con cui lavori aumenta, questi millisecondi aumenteranno a secondi, minuti e possibilmente persino ore. Ti renderai conto quindi che queste tecniche di ottimizzazione delle query SQL sono altamente importanti.

Se vuoi saperne di più, ecco le 30 domande di intervista sulle query SQL principali, che aiuteranno anche chi vuole prepararsi per un’intervista durante l’apprendimento.

Grazie per la lettura! Nate Rosidi è un data scientist e una strategia di prodotto. È anche un professore a contratto che insegna analisi e fondatore di StrataScratch, una piattaforma che aiuta i data scientist a prepararsi per le loro interviste con domande di intervista reali dalle principali aziende. Connettiti con lui su Twitter: StrataScratch o LinkedIn.