Ti è mai capitato di aspettare secondi interi per un report che dovrebbe caricare in millisecondi? O di perdere dati perché un backup non era configurato? Se lavori con database relazionali, questi problemi li conosci bene. Noi di Meteora Web li affrontiamo ogni giorno: gestiamo piattaforme che processano migliaia di transazioni, e senza un SQL solido non si va da nessuna parte.
Questa pagina è il punto di riferimento su SQL e database relazionali. Non troverai solo sintassi: analizziamo query, ottimizzazione, confronto tra MySQL e PostgreSQL, normalizzazione, transazioni, stored procedure e strategie di backup. Tutto con esempi reali e il nostro approccio concreto: costi, performance, margini.
Come funziona una query SQL di base e come si compone?
Partiamo dalle fondamenta. SQL (Structured Query Language) è il linguaggio universale per interrogare e manipolare dati in database relazionali. Una query standard si basa su cinque clausole principali:
SELECT colonna1, colonna2
FROM tabella
WHERE condizione
GROUP BY colonna
ORDER BY colonna ASC/DESC;SELECT specifica le colonne da restituire. FROM indica la tabella. WHERE filtra le righe. GROUP BY raggruppa per aggregazioni (SUM, COUNT, AVG). ORDER BY ordina il risultato. Sembra semplice, ma la maggior parte degli errori nasce qui: dimenticare un indice su WHERE, usare SELECT * in produzione, o fare GROUP BY senza capire l’effetto sulle performance.
Esempio concreto: un e-commerce moda che seguiamo aveva una query per il catalogo che impiegava 4 secondi. Il problema? WHERE su una colonna senza indice. Aggiunto un indice composito su (categoria, prezzo), la query è scesa a 30 ms. Se vendi 100 prodotti al giorno, 4 secondi di attesa per ogni visita sono clienti persi.
Sponsored Protocol
Join: INNER, LEFT, RIGHT, FULL, CROSS e self join
I join uniscono dati da più tabelle. Ecco i tipi principali:
- INNER JOIN: solo righe corrispondenti in entrambe le tabelle.
- LEFT JOIN: tutte le righe della tabella sinistra, anche senza match nella destra.
- RIGHT JOIN: tutte le righe della tabella destra.
- FULL OUTER JOIN: tutte le righe di entrambe, con NULL dove manca.
- CROSS JOIN: prodotto cartesiano (ogni riga di A con ogni riga di B).
- Self join: join di una tabella con sé stessa (es. impiegato e manager).
SELECT o.id, c.nome
FROM ordini o
INNER JOIN clienti c ON o.cliente_id = c.id;Un errore comune: usare LEFT JOIN quando serve INNER JOIN, caricando righe inutili. Verifica sempre la logica di business.
GROUP BY e funzioni aggregate
Raggruppare i dati è essenziale per report e statistiche. Le funzioni aggregate (SUM, COUNT, AVG, MAX, MIN) operano su gruppi. Attenzione: ogni colonna nel SELECT che non sia aggregata deve apparire nel GROUP BY.
SELECT categoria, SUM(prezzo * quantita) AS totale_vendite
FROM ordini_dettaglio
GROUP BY categoria
HAVING SUM(prezzo * quantita) > 1000;HAVING filtra i gruppi (mentre WHERE filtra le righe prima del raggruppamento).
Come scrivere query complesse con subquery e CTE (WITH clause)?
Quando una singola query non basta, entrano in gioco subquery e Common Table Expressions (CTE). Le subquery sono query annidate; le CTE sono query temporanee definite con WITH che migliorano la leggibilità.
Subquery
SELECT nome, prezzo
FROM prodotti
WHERE prezzo > (SELECT AVG(prezzo) FROM prodotti);Le subquery possono essere correlate (dipendono dalla query esterna) o non correlate. Attenzione alla performance: una subquery eseguita per ogni riga può essere lenta.
Sponsored Protocol
CTE con WITH
WITH vendite_per_cliente AS (
SELECT cliente_id, SUM(totale) AS totale
FROM ordini
GROUP BY cliente_id
)
SELECT clienti.nome, vendite.totale
FROM clienti
JOIN vendite_per_cliente vendite ON clienti.id = vendite.cliente_id
WHERE vendite.totale > 5000;Le CTE sono ricorsive? Sì, per alberi (es. categorie). Ma per la maggior parte dei casi, preferiamo CTE per chiarezza e manutenibilità.
Come ottimizzare le query SQL e risolvere i colli di bottiglia?
L’ottimizzazione non è un optional: è il cuore di un’applicazione performante. Noi partiamo sempre da EXPLAIN ANALYZE per vedere il piano di esecuzione.
Indici: B‑tree, hash, compositi
Gli indici accelerano la ricerca, ma hanno un costo in scrittura. Tipi comuni:
- B‑tree: di default, per confronti e ordinamenti.
- Hash: per uguaglianze esatte (solo in PostgreSQL).
- Compositi: su più colonne, ordine importante (prima la colonna con maggiore selettività).
CREATE INDEX idx_ordini_data_cliente ON ordini (data_ordine, cliente_id);Errore comune: creare troppi indici. Ogni indice rallenta INSERT/UPDATE. Analizza le query più lente e crea indici mirati.
Usare EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM ordini WHERE cliente_id = 42;Il risultato mostra il costo, il numero di righe stimate e reali, e l’utilizzo degli indici. Se vedi “Seq Scan” su tabelle grandi, probabilmente manca un indice.
Altre tecniche
- Evitare
SELECT *: specifica solo le colonne necessarie. - Usare
LIMITper paginazione. - Evitare funzioni su colonne indicizzate (es.
WHERE YEAR(data) = 2024→ meglioWHERE data BETWEEN '2024-01-01' AND '2024-12-31'). - Partizionare tabelle grandi per data o categoria.
Come garantire integrità dei dati con transazioni ACID?
In un database relazionale, le transazioni assicurano che una sequenza di operazioni sia atomica, consistente, isolata e durevole (ACID).
Sponsored Protocol
START TRANSACTION;
UPDATE conto SET saldo = saldo - 100 WHERE id = 1;
UPDATE conto SET saldo = saldo + 100 WHERE id = 2;
COMMIT;
-- Se qualcosa va male: ROLLBACK;Le transazioni sono cruciali in operazioni finanziarie, ordini ecommerce, aggiornamenti concorrenti. Attenzione al livello di isolamento: READ COMMITTED (default in PostgreSQL) vs REPEATABLE READ vs SERIALIZABLE. Un livello troppo alto blocca le righe e riduce la concorrenza.
MySQL vs PostgreSQL: quale database relazionale scegliere per il tuo progetto?
Questo confronto è tra i più dibattuti. Noi li usiamo entrambi e scegliamo in base al contesto.
Performance e storage engine
MySQL con InnoDB è eccellente per letture semplici e replica master‑slave. PostgreSQL è superiore in query complesse, aggregazioni e concorrenza grazie al suo motore MVCC più maturo.
Tipi di dati e funzioni
PostgreSQL supporta array, JSONB, range, enum e indici di vario tipo (GiST, GIN). MySQL ha JSON ma meno funzioni. Se il progetto richiede geospaziale o dati semi‑strutturati, PostgreSQL vince.
Replica e backup
Entrambi offrono replica streaming. PostgreSQL ha strumenti nativi più flessibili (pg_basebackup, pgBackRest). MySQL con Percona XtraBackup è solido. Per ambienti con alta disponibilità, PostgreSQL è spesso preferito.
Regola pratica: per applicazioni web standard (LAMP, WordPress), MySQL va benissimo. Per data warehousing, analisi, o sistemi che richiedono integrità rigorosa, scegli PostgreSQL.
Sponsored Protocol
Come normalizzare un database in 1NF, 2NF, 3NF e BCNF?
La normalizzazione riduce ridondanza e anomalie. Vediamo ogni forma normale con esempi.
Prima forma normale (1NF)
Ogni cella contiene un solo valore atomico. Niente array o valori multipli in una colonna.
Seconda forma normale (2NF)
Essere in 1NF e ogni attributo non chiave dipende dall’intera chiave primaria (non da una sua parte). Utile per chiavi composite.
Terza forma normale (3NF)
Essere in 2NF e nessuna dipendenza transitiva (attributo non chiave che dipende da un altro attributo non chiave).
Boyce‑Codd (BCNF)
Più restrittiva: ogni determinante deve essere una chiave candidata.
Esempio pratico: una tabella Ordini con (cliente_nome, cliente_indirizzo) viola la 3NF perché indirizzo dipende da cliente. Si separa in tabella Clienti.
Non esagerare: a volte una denormalizzazione controllata migliora le performance (es. report pre‑aggregati).
Stored procedure e trigger: quando usarli e quando evitarli?
Stored procedure e trigger spostano logica di business nel database. Possono essere utili ma vanno usati con criterio.
Quando usarli
- Operazioni complesse che richiedono più query atomiche (es. trasferimento fondi).
- Regole di integrità che devono essere applicate a prescindere dall’applicazione (es. log di audit).
- Performance critiche dove il round‑trip client‑server è il collo di bottiglia.
Quando evitarli
- Logica di presentazione o business che cambia spesso (meglio nell’applicazione).
- Trigger che eseguono query pesanti su ogni INSERT – possono bloccare il database.
- Debug difficile e versionamento complesso. Preferisci codice applicativo (PHP, Python, Node) con librerie ORM.
Noi di Meteora Web prediligiamo logica nell’applicazione e usiamo stored procedure solo per processi batch o migrazioni controllate.
Sponsored Protocol
Backup e replica: strategie per non perdere dati in produzione?
Non esiste database senza backup. Ma non tutti i backup sono uguali.
Tipi di backup
- Backup logico (es. mysqldump, pg_dump): portabile, ma lento per grandi volumi.
- Backup fisico (copia dei file di dati): più veloce, migliore per restore point‑in‑time.
- Backup incrementale: solo le modifiche dall’ultimo backup.
Replica
Replica master‑slave per letture e failover. Replica sincrona vs asincrona. Per alta disponibilità, usa cluster (Galera per MySQL, Patroni per PostgreSQL).
Regola pratica: RPO (Recovery Point Objective) e RTO (Recovery Time Objective) vanno definiti con il cliente. Un backup giornaliero con RPO di 24h può non bastare per un e‑commerce; serve binary log e replica.
Cosa fare adesso — Azioni concrete per dominare SQL e database relazionali
- Analizza le tue query lente: attiva slow query log e usa EXPLAIN ANALYZE sulla peggiore.
- Crea indici mirati: non mettere indici ovunque, ma concentrati su WHERE e JOIN frequenti.
- Normalizza fino a 3NF, poi denormalizza solo se le performance lo richiedono (misura sempre).
- Scegli il database in base ai dati: MySQL per web tradizionale, PostgreSQL per analisi e dati complessi.
- Imposta backup automatici e replica: testa il restore almeno una volta al mese.
- Leggi documentazione ufficiale: MySQL Documentation e PostgreSQL Documentation.
Noi di Meteora Web ti aiutiamo a progettare e ottimizzare il tuo database. Se hai un collo di bottiglia o vuoi migrare da MySQL a PostgreSQL, contattaci – ragioniamo sui numeri, non solo sul codice.
