Qual è la differenza tra WHERE e HAVING?
WHERE filtra le singole righe prima che avvenga qualsiasi raggruppamento, quindi non può riferirsi a funzioni di aggregazione. HAVING filtra i gruppi dopo l'esecuzione di GROUP BY, quindi può riferirsi ad aggregati come COUNT(*) o SUM(amount). Come regola, metti una condizione in WHERE ogni volta che si applica alle righe grezze, perché filtrare presto riduce il numero di righe che il database deve raggruppare; riserva HAVING alle condizioni che dipendono dal risultato aggregato.
Quando conviene aggiungere un indice e quando danneggia?
Aggiungi un indice quando una colonna è usata di frequente nei predicati WHERE, JOIN o ORDER BY ed è abbastanza selettiva da restringere la ricerca in modo significativo. Gli indici danneggiano le prestazioni di scrittura perché ogni INSERT, UPDATE e DELETE deve mantenerli, e consumano spazio, quindi gli indici inutilizzati o ridondanti sono puro sovraccarico. Conferma che un indice sia davvero usato leggendo il piano di EXPLAIN; un indice su una colonna a bassa cardinalità o mai referenziato dal pianificatore è un costo sprecato.
Come evito il problema delle query N+1 con un ORM?
Il problema N+1 si verifica quando il caricamento di N righe padre innesca una query aggiuntiva per ciascun padre per recuperare una collezione correlata, per un totale di N+1 round trip. Si risolve recuperando l'associazione nella stessa query: usa un JOIN FETCH (JPA/Hibernate), un caricamento eager include/select (Prisma) o selectinload/joinedload (SQLAlchemy). Verifica sempre l'SQL generato con il logging delle query, perché la soluzione è caricare esattamente i dati che il caso d'uso richiede nel minor numero di round trip possibile.
Qual è la differenza tra un indice clustered e uno non-clustered?
Un indice clustered definisce l'ordine fisico delle righe della tabella, quindi i dati delle righe risiedono nelle foglie dell'indice e una tabella può averne uno solo. Un indice non-clustered (secondario) è una struttura separata che contiene le colonne indicizzate più un puntatore alla riga, quindi una tabella può averne molti. In MySQL/InnoDB e SQL Server la chiave primaria è per default clustered, il che rende molto veloci le ricerche per chiave primaria e fa pagare alle ricerche su indice secondario un passaggio extra per recuperare l'intera riga.
Devo usare una chiave naturale o una chiave surrogata come chiave primaria?
Nella maggior parte degli schemi operativi preferisci una chiave surrogata (intero auto-incrementale o UUID) perché è stabile, compatta e non deve mai cambiare quando cambiano gli attributi di business, il che mantiene semplici e immutabili le chiavi esterne. Una chiave naturale può essere appropriata quando esiste un identificatore di business davvero stabile e univoco e si vuole che il database lo imponga direttamente. Puoi avere entrambe: una chiave primaria surrogata più un vincolo UNIQUE sulla chiave di business naturale per imporne l'unicità.
Come prevengono le anomalie di concorrenza i livelli di isolamento?
I livelli di isolamento scambiano concorrenza con consistenza controllando quali effetti delle altre transazioni una transazione può vedere. Read Committed previene le letture sporche, Repeatable Read previene in più le letture non ripetibili e Serializable previene anche le letture fantasma, comportandosi come se le transazioni venissero eseguite una alla volta. La maggior parte dei database usa per default Read Committed; alza il livello solo per le transazioni specifiche che ne hanno bisogno, perché un isolamento più forte aumenta il blocco o i retry per fallimento di serializzazione.
Cosa causa un deadlock e come lo prevengo?
Un deadlock si verifica quando due transazioni detengono ciascuna un lock di cui l'altra ha bisogno, così nessuna può avanzare; il database rileva il ciclo e annulla una transazione come vittima. Previeni i deadlock acquisendo i lock in un ordine coerente in tutti i percorsi di codice, mantenendo le transazioni brevi e toccando le righe in una sequenza prevedibile. Poiché i deadlock non possono essere eliminati del tutto in concorrenza, le applicazioni dovrebbero anche avvolgere le transazioni in una logica di retry che riesegue la transazione annullata.
Quando dovrei denormalizzare uno schema?
Normalizza prima: uno schema normalizzato memorizza ogni fatto una sola volta, il che rende le scritture corrette e semplici. Denormalizza solo quando il profiling mostra che uno specifico percorso di lettura è troppo lento a causa di join o aggregazioni ripetute, e il rapporto lettura/scrittura è alto. Il costo della denormalizzazione è mantenere coerenti i dati ridondanti, quindi preferisci meccanismi che il database può mantenere per te, come viste materializzate o trigger, piuttosto che disperdere la logica di aggiornamento nell'applicazione.
Come pagino in modo efficiente grandi insiemi di risultati?
La paginazione con OFFSET/LIMIT è semplice ma degrada sulle pagine profonde perché il database deve scansionare e scartare tutte le righe saltate. La paginazione keyset (a cursore) è molto più efficiente su larga scala: ordina per una colonna univoca e indicizzata e recupera la pagina successiva con WHERE (sort_col, id) > (:last_sort, :last_id) ORDER BY sort_col, id LIMIT n. La paginazione keyset mantiene veloce ogni pagina perché si posiziona direttamente nell'indice invece di contare dall'inizio.
Qual è la differenza tra DELETE, TRUNCATE e DROP?
DELETE rimuove le righe che corrispondono a una clausola WHERE, è pienamente transazionale e loggata e attiva i trigger di riga, quindi può essere annullata. TRUNCATE rimuove tutte le righe rapidamente deallocando le pagine dati, di solito azzera i contatori di identità/sequenza e in molti motori non può essere filtrata o è non transazionale. DROP rimuove l'oggetto tabella stesso, inclusi dati, indici e vincoli. Scegli DELETE per una rimozione selettiva e recuperabile; TRUNCATE per svuotamenti rapidi dell'intera tabella; DROP per scartare la struttura.
Come prevengono la SQL injection le prepared statement?
Una prepared statement invia prima al database il testo SQL con segnaposto per i parametri, dove viene analizzato e pianificato, e poi invia i valori dei parametri separatamente. Poiché i valori arrivano dopo che la struttura dell'istruzione è fissata, non possono mai cambiare il significato della query, quindi l'input dell'attaccante viene trattato rigorosamente come dato. Questa separazione è la difesa definitiva contro la SQL injection, molto più affidabile del tentativo di fare escape o sanitizzare stringhe concatenate.
Cos'è una funzione finestra e in cosa differisce da GROUP BY?
Una funzione finestra calcola un valore su un insieme di righe correlate (la finestra definita da OVER (...)) restituendo comunque ogni riga di input, a differenza di GROUP BY che riduce ogni gruppo a una singola riga. Questo permette di produrre totali progressivi, classifiche (ROW_NUMBER, RANK) e accesso alle righe vicine (LAG, LEAD) accanto ai dati di dettaglio. Usa una funzione finestra ogni volta che ti serve un calcolo in stile aggregato ma devi mantenere visibili le singole righe.
Come dovrei memorizzare date e ore per evitare bug di fuso orario?
Memorizza gli istanti in un tipo timezone-aware che normalizza a UTC, come il timestamptz di PostgreSQL, e converti al fuso locale dell'utente solo al livello di presentazione. Memorizzare orari locali ingenui introduce ambiguità intorno ai cambi di ora legale e rende soggetto a errori il confronto tra regioni. Mantieni il database come unica fonte di verità in UTC e non memorizzare mai un orario locale senza registrarne anche il fuso.
Qual è la differenza tra una vista e una vista materializzata?
Una vista semplice è una query memorizzata che viene ricalcolata ogni volta che è referenziata, quindi riflette sempre le tabelle di base correnti e non memorizza dati. Una vista materializzata memorizza fisicamente il risultato precalcolato, rendendo veloci le letture ma richiedendo un refresh esplicito per recepire le modifiche, quindi i suoi dati possono essere obsoleti. Usa una vista per semplificare e mettere in sicurezza l'accesso ai dati live; usa una vista materializzata per fare cache di aggregazioni costose che tollerano una certa obsolescenza.
Come diagnostico una query lenta?
Inizia con EXPLAIN (e EXPLAIN ANALYZE per ottenere tempi e conteggi di righe reali), e cerca scansioni sequenziali su tabelle grandi, grandi errori di stima delle righe, ordinamenti costosi e join a nested-loop su molte righe. Confronta righe stimate e reali: una grande discrepanza di solito significa statistiche obsolete, quindi esegui ANALYZE. Poi affronta la causa radice, tipicamente aggiungendo o correggendo un indice, riscrivendo la query per renderla sargable o aggiornando le statistiche, e ricontrolla il piano.
Cosa significa che una query è sargable?
Una query è sargable (Search ARGument ABLE) quando i suoi predicati possono usare un indice, cosa che in genere significa che la colonna indicizzata compare da sola su un lato del confronto. Avvolgere la colonna in una funzione, come WHERE YEAR(created_at) = 2026, o applicarvi aritmetica, di solito vanifica l'indice e forza una scansione. Riscrivi tali predicati lasciando nuda la colonna, per esempio WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01', così l'ottimizzatore può posizionarsi nell'indice.
Quando dovrei scegliere un database relazionale invece di uno store NoSQL?
Scegli un database relazionale quando i dati sono fortemente interrelati, hai bisogno di transazioni ACID multi-riga, di query ad hoc con join e di una forte integrità imposta dai vincoli. Scegli uno store NoSQL quando hai bisogno di uno specifico pattern di accesso a scala estrema, di uno schema flessibile o in rapida evoluzione, o di una forma di dato (documento, chiave-valore, grafo, wide-column) che si mappa male sulle tabelle. Molti sistemi usano entrambi: un database relazionale come system of record e uno store specializzato per un particolare carico di lavoro.