Una query che l’anno scorso richiedeva millisecondi può richiedere minuti oggi, semplicemente perché la tabella è cresciuta. L’ottimizzazione delle query SQL riguarda meno trucchi intelligenti e più un processo ripetibile: scoprire cosa sta facendo il database, rimuovere il lavoro sprecato e verificare che sia stato d’aiuto.

Abbiamo testato ciascuna tecnica di seguito su un milione di righe generate in SQLite 3.50. I tempi sono i migliori tra diverse esecuzioni da un laptop, utilizzando un database in memoria. Non corrisponderanno al tuo sistema e PostgreSQL, MySQL e SQL Server hanno ciascuno i propri ottimizzatori e formati di piano. Concentrarsi sulla portata di ogni miglioramento e sui principi che ne stanno alla base.

Risultati in breve

L’impostazione della prova

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

Un semplice processo di ottimizzazione

1. Trova le query che contano. Utilizza registri o monitoraggio delle query lenti. Una query di 200 ms eseguita mille volte al minuto può avere più importanza di un report mensile di 10 secondi.
2. Leggi il piano. Scopri cosa sta realmente facendo il database.
3. Cambia una cosa. Aggiungi un indice o riscrivi parte della query.
4. Misurare nuovamente, su dati realistici. Un piano su 100 righe di prova dice molto poco su un milione.
5. Guardalo nel tempo. I piani possono cambiare man mano che i dati crescono e le statistiche cambiano.

Leggere un piano di esecuzione

Un piano di esecuzione è la strategia passo passo del database per l’esecuzione di una query, inclusi gli indici utilizzati e l’ordine in cui unisce le tabelle. Prefisso una query per vederla:

Tieni presente che EXPLAIN ANALYZE in PostgreSQL esegue effettivamente la query, quindi fai attenzione quando lo usi con UPDATE o DELETE. Ecco cosa cercare per primo:

Una scansione completa non è automaticamente negativa. Se una query richiede gran parte di una tabella piccola, la scansione può essere la scelta più rapida. In PostgreSQL EXPLAIN ANALYZE, confronta anche le righe stimate con le righe effettive. Una grande differenza spesso significa che le statistiche della tabella non sono aggiornate.

Aggiungi l’indice giusto

Questa query trova gli ordini di un cliente:

SELECT id, total FROM orders WHERE customer_id = 123;

Prima di aggiungere un indice, il piano prevedeva una scansione completa:

SCAN orders
-- 20 rows in 73 ms

Quindi abbiamo aggiunto un indice nella colonna del filtro:

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

Le stesse 20 righe tornavano migliaia di volte più velocemente, perché il database non leggeva più un milione di righe per trovarle. Nessun’altra tecnica in questo articolo ci ha dato una vittoria più grande. Buoni primi candidati sono le colonne utilizzate in WHERE, JOIN e ORDER BY, soprattutto quando restringono i risultati a poche righe. La nostra guida agli indici SQL spiega come funzionano.

Non racchiudere le colonne indicizzate nelle funzioni

Ecco due modi per trovare gli ordini di marzo, con un indice su created:

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

Entrambi restituiscono righe identiche, ma la prima ha impiegato circa tre volte il tempo. Il piano mostra il motivo: con una funzione sulla colonna, il database non può saltare a una posizione nell’indice ordinato, quindi deve SCAN attraverso ogni voce. La versione della gamma può SEARCH direttamente al posto giusto. Una condizione che può utilizzare un indice in questo modo è chiamata sargable.

Lo stesso problema si presenta con questi modelli comuni:

Abbiamo controllato anche quest’ultimo. Viene prodotto un carattere jolly iniziale SCAN customers, poiché un indice ordinato a partire dalla prima lettera non può aiutare con una corrispondenza alla fine.

Indici compositi

Quando una query viene filtrata su due colonne, un indice a colonna singola svolge solo parte del lavoro:

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

L’ordine delle colonne è molto importante. Un indice su (status, created) funziona come un elenco telefonico ordinato per cognome e poi per nome. Puoi cercare solo il cognome, ma non solo il nome. La nostra query su status da sola potrebbe utilizzare questo indice. Il filtraggio solo su created in genere non è in grado di utilizzarlo in modo efficiente. Come regola generale, metti prima le colonne testate con l’uguaglianza e dopo di esse le colonne con intervallo.

Quel piano dice anche indice di copertura, il che significa che l’indice conteneva tutto ciò di cui la query aveva bisogno, quindi non era necessario leggere la tabella stessa. La selezione solo delle colonne necessarie rende ciò più probabile, il che è un altro motivo per evitarlo SELECT *.

Impaginazione più veloce

L’utilizzo di OFFSET per le pagine profonde è un costo nascosto, poiché il database deve ancora superare tutte le righe saltate:

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

L’impaginazione dei keyset rimane veloce, non importa quanto vai in profondità. Inoltre, evita che le righe si spostino tra le pagine quando vengono aggiunti nuovi dati. Il compromesso è che gli utenti non possono passare direttamente alla pagina 5.000, il che raramente è una reale necessità. Nelle tabelle con righe larghe o con più righe da saltare, il divario può essere molto maggiore rispetto al nostro test.

Scrivi query più snelle

• Seleziona solo le colonne che ti servono. SELECT * invia più dati e può impedire a un indice di coprire la query.
• Filtra in anticipo. Utilizza WHERE per rimuovere le righe prima del raggruppamento e HAVING solo per le condizioni sugli aggregati. Vedere SQL GROUP BY e HAVING.
• Utilizza LIMIT quando ti servono solo poche righe.
• Preferisci UNION ALL quando non è necessario rimuovere i duplicati. Il nostro test UNION vs UNION ALL mostra cosa si risparmia.
• Evita DISTINCT non necessari. Spesso nasconde un join che produce duplicati.
• Utilizza EXISTS per i controlli di esistenza. Può arrestarsi alla prima corrispondenza, come spiega la nostra guida alle sottoquery SQL.
• Cerca N+1 query. Una query per riga in un ciclo, solitamente causata dal codice dell’applicazione, è spesso il vero problema. Una singola query di unione o batch può sostituire centinaia di viaggi di andata e ritorno.

Si unisce

Indicizza le colonne a cui ti iscrivi. Abbiamo eseguito un report sulla spesa totale per i clienti in una città e il piano era esattamente quello che speravi:

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

Esegue la scansione dei clienti, quindi utilizza un indice per trovare gli ordini di ciascuno. Senza un indice su orders.customer_id, il database dovrebbe svolgere molto più lavoro per ciascun cliente. Un indice sulla chiave esterna è una delle aggiunte più comuni e preziose che puoi apportare. Per rinfrescare la tua comprensione delle nozioni di base, consulta Spiegazione dei join SQL.

Quando gli indici ti costano

Gli indici non sono gratuiti, quindi non aggiungerne uno per ogni colonna:

• Scritture più lente. Ogni inserimento, aggiornamento ed eliminazione deve aggiornare anche ogni indice.
• Più spazio di archiviazione e memoria. Gli indici possono diventare grandi su tabelle di grandi dimensioni.
• Poco vantaggio sulle colonne non selettive. Un indice su una colonna true o false potrebbe non essere d’aiuto se la maggior parte delle righe corrisponde.
• Ridondanza. Un indice su (a) spesso non è necessario se ne hai già uno su (a, b).

Controlla periodicamente le statistiche di utilizzo e rimuovi gli indici che non vengono utilizzati. E mantieni aggiornate le statistiche con ANALYZE o l’equivalente del tuo database, perché l’ottimizzatore fa affidamento su di loro per scegliere un buon piano. A volte la parte lenta è la contesa piuttosto che la domanda. Le transazioni di lunga durata che mantengono i blocchi possono far sembrare lenta una query veloce.

Errori comuni

• Ottimizzare senza misurare. Molte ipotesi attendibili si rivelano errate.
• Test con dati di piccole dimensioni. I piani e le tempistiche cambiano drasticamente man mano che le tabelle crescono.
• Affidarsi a un suggerimento per un altro database. I consigli per un motore potrebbero non essere applicabili a un altro.
• Aggiunta di indici alla cieca. Ognuno di essi rallenta le scritture.
• Ignorando l’effetto della prima esecuzione. Le cache rendono la seconda esecuzione più veloce, quindi esegui più volte.

Considerazioni finali

La maggior parte delle query lente ha cause ordinarie: un indice mancante, una funzione su una colonna o troppi dati recuperati. Prendi la tua query reale più lenta questa settimana, esegui EXPLAIN su di essa e cerca una scansione completa su una tabella di grandi dimensioni. Correggi una cosa, misura di nuovo e prendi nota di ciò che hai modificato.

Domande frequenti

Continua a imparare