Ottimizzazione del database SQL: indici, partizionamento e ottimizzazione

I database relazionali sono il cuore di molte applicazioni. Quando le query iniziano a rallentare, l’esperienza dell’utente ne risente e i costi dell’infrastruttura aumentano. Questo articolo presenta tecniche pratiche per ottimizzare le query e la struttura dei dati.

1. Comprendere il piano di esecuzione

La prima cosa da fare è analizzare EXPLAIN (PostgreSQL) o EXPLAIN ANALYZE (MySQL). Mostra come l'ottimizzatore prevede di accedere ai dati.

Seq Scan indica la lettura completa della tabella, generalmente segno di un indice mancante.
Scansione indice mostra che è in uso un indice.
Scansione indice bitmap combina più indici.
Nested Loop, Hash Join, Merge Join, la scelta dell'algoritmo di join influisce sulle prestazioni.

Lista di controllo dell'analisi

Il piano utilizza indici adeguati?
Quante linee sono stimate rispetto a quelle reali?
L'ordinamento o l'hash aggregato sono costosi?
Il costo totale è quello previsto?

2. Strategie di indicizzazione

Indici B-Tree (predefinito)

Ideale per ricerche di uguaglianza e di intervallo.
Crea indici sulle colonne utilizzate in WHERE, JOIN, ORDER BY.

Indici parziali

CREATE INDEX idx_orders_status_pending ON orders (status) WHERE status = 'pending';

Riduce la dimensione dell'indice concentrandosi solo sulle righe pertinenti.

Indici compositi

Ordina le colonne nell'indice nello stesso ordine in cui appaiono nelle clausole WHERE e ORDER BY.

CREATE INDEX idx_sales_date_customer ON sales (sale_date, customer_id);

Indici GIN/GIST (PostgreSQL)

Utile per colonne JSONB, matrici, ricerca full-text.
Esempio: CREATE INDEX idx_data_json ON events USING GIN (data);

3. Partizionamento della tabella

La suddivisione di tabelle di grandi dimensioni in partizioni più piccole migliora la leggibilità e la manutenzione.

Partizionamento per intervallo, per data (es.: orders_2025_q1).
Partizionamento delle liste, per enumerazione (es.: status = 'completed').
Partizionamento hash, distribuzione uniforme.

Esempio di partizione di intervalli (PostgreSQL)

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  order_date DATE NOT NULL,
  status TEXT NOT NULL,
  total NUMERIC
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2025_q1 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

4. Normalizzazione e denormalizzazione

La standardizzazione riduce la ridondanza e facilita la manutenzione.
La Denormalizzazione può migliorare la lettura evitando unioni complesse.
Valuta i compromessi: se la maggior parte delle query sono lettura pesante, considera le tabelle ottimizzate per la lettura.

5. Impostazioni del server

shared_buffers (PostgreSQL), 25% della RAM.
work_mem, memoria per operazione di ordinamento/unione.
innodb_buffer_pool_size (MySQL), 70-80% della RAM.
max_connections, regolare in base al carico.

6. Monitoraggio continuo

Utilizza pg_stat_statements (PostgreSQL) o performance_schema (MySQL) per identificare le query lente.
Configura gli avvisi di registro delle query lente.
Strumenti come pgBadger, Percona Toolkit aiutano ad analizzare i log.

7. Lista di controllo per l'ottimizzazione

Analizzare i piani di esecuzione per le query critiche.
Creare indici adatti (B-Tree, parziale, composito).
Valutare la necessità di partizionamento.
Esaminare la configurazione della memoria del server.
Monitora e registra le query lente.
[] Revisione del modello dei dati (normalizzazione vs. denormalizzazione).

Conclusione

L'ottimizzazione del database non è un evento una tantum, è un processo iterativo. Inizia analizzando i colli di bottiglia, applica l'indicizzazione intelligente, regola la configurazione del server e monitora continuamente. Con queste pratiche riduci la latenza, risparmi risorse e offri un'esperienza più fluida agli utenti.

Quali problemi di prestazioni hai riscontrato nei tuoi database? Condividi nei commenti!