Ottimizzazione del Database: Prestazioni SQL e Indicizzazione

Perché le prestazioni del database contano

Le query lente del database aumentano il tempo di risposta dell'applicazione. Le query di oltre 100ms influenzano negativamente l'esperienza utente.

Il collo di bottiglia del database può influenzare l'80% delle prestazioni dell'applicazione. Il database è solitamente il collo di bottiglia più grande piuttosto che CPU o memoria.

L'ottimizzazione riduce i costi hardware. Query più efficienti richiedono meno risorse del server.

Strategie di Indicizzazione

Indice B-Tree: Il tipo di indice più comune. Efficace per query di uguaglianza (=) e intervallo (<, >, BETWEEN). Le chiavi primarie ed esterne sono indicizzate automaticamente.

Indice composito: Indice contenente più colonne. Determinate l'ordine delle colonne in base all'ordine della query. La prima colonna nella clausola WHERE deve essere la più selettiva.

Indice covering: Indice contenente tutte le colonne usate nella query. Non è necessario accedere alla tabella, viene letto solo l'indice.

Indice parziale: Indice per le righe che soddisfano una condizione specifica. Come WHERE is_active = true. Riduce la dimensione dell'indice.

Manutenzione degli indici: Rimuovete gli indici inutilizzati. Ogni indice rallenta le operazioni INSERT/UPDATE/DELETE. Verificate l'utilizzo dell'indice con EXPLAIN.

Ottimizzazione delle Query

EXPLAIN / EXPLAIN ANALYZE: Analizzate il piano di esecuzione della query. Mira a Index Scan invece di Seq Scan (scansione completa della tabella).

Problema N+1: Recuperate i dati correlati in una singola query (JOIN) invece di fare una query separata per ogni record. Usate eager loading negli ORMs.

Non usate SELECT *: Selezionate solo le colonne necessarie. Questo riduce il traffico di rete e l'uso della memoria.

Ottimizzazione JOIN: Evitate JOIN non necessari. Fate JOIN delle tabelle più piccole prima. Usate INNER JOIN invece di LEFT JOIN (se possibile).

Subquery vs JOIN: Le subquery correlate sono lente. Usate JOIN o CTE (Common Table Expression) quando possibile.

Cache e Connection Pooling

Query cache: Mettete in cache i risultati delle query usate frequentemente. Fate caching a livello applicativo con Redis o Memcached.

Connection pooling: Riutilizzate le connessioni al database. Aprire una nuova connessione per ogni richiesta è costoso. Usate pgBouncer (PostgreSQL) o HikariCP (Java).

Read replicas: Instradate le operazioni di lettura ai server replica. Mantenete le operazioni di scrittura sul server primario. Questo distribuisce il carico di lettura.

Materialized views: Create materialized view per query di aggregazione complesse. Aggiornate periodicamente.

Design del Database

Normalizzazione: Riducete la ripetizione dei dati (1NF, 2NF, 3NF). Tuttavia, la sovra-normalizzazione aumenta il numero di JOIN e riduce le prestazioni.

Denormalizzazione: La denormalizzazione migliora le prestazioni in sistemi con molte letture (analytics, reporting). Trade-off: coerenza dei dati vs prestazioni.

Partitioning: Partizionate tabelle grandi (milioni di righe). Range partitioning (basato su data) è il metodo più comune.

Tipi di dati: Usate i tipi di dati corretti. INT invece di VARCHAR, VARCHAR (dimensione appropriata) invece di TEXT. I tipi di dati più piccoli occupano meno spazio e vengono elaborati più velocemente.

Monitoraggio e Profiling

Slow query log: Loggate le query lente. PostgreSQL: log_min_duration_statement = 100. MySQL: slow_query_log = 1.

Performance Schema (MySQL) / pg_stat_statements (PostgreSQL): Raccogliete le statistiche delle query. Identificate le query che richiedono più tempo.

Strumenti APM: Monitorate le prestazioni del database con Datadog, New Relic o Dynatrace. Tracciate i tempi di query e i tassi di errore.

Uso degli indici: Verificate l'utilizzo degli indici con pg_stat_user_indexes (PostgreSQL). Rimuovete gli indici inutilizzati.