Database Indexing: La Guida Completa agli Indici per Sviluppatori

Introduzione: Come Funzionano gli Indici

Immagina un libro di 500 pagine senza indice. Per trovare il capitolo su "Database Indexing", leggi tutte le 500 pagine. Con un indice? 2 secondi.

Gli indici database funzionano così: mantengono una struttura ordinata dei dati che permette lookup veloce senza leggere ogni riga.

In questo articolo esploro i 4 tipi di indici più comuni (B-Tree, Hash, GIN, BRIN), quando usarli, e come evitare indici che rallentano invece di velocizzare.

1. B-Tree Index: Il Più Versatile

B-Tree (Balanced Tree) è l'indice di default in 99% dei database. Funziona così:

• Mantiene i dati ordinati gerarchicamente
• Ogni operazione (ricerca, insert, delete) è O(log N) — molto veloce
• Perfetto per range queries (WHERE age BETWEEN 25 AND 35)

-- Crea indice B-Tree su colonna singola
CREATE INDEX idx_users_email ON users(email);

-- B-Tree composito: ordine conta! Colonna filtro-frequente per prima
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

-- Quando lo usi: WHERE, ORDER BY, JOIN ON
SELECT * FROM users WHERE email = 'leo@example.com';
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

2. Hash Index: Veloci ma Limitati

Hash usa una funzione hash per mappare valori a posizioni. Molto veloce per uguaglianza esatta, inutile per range.

-- Hash index: solo = non <, >, BETWEEN
CREATE INDEX idx_users_phone USING HASH ON users(phone);

-- Velocissimo per questo:
SELECT * FROM users WHERE phone = '555-1234';  -- ✓ Usa hash index

-- Ma non per questo:
SELECT * FROM users WHERE phone > '555-0000';  // ✗ Ignora hash index

Usa hash solo se: Filtri sempre con uguaglianza esatta (WHERE column = value) e mai range.

3. GIN Index: Per Array e JSON

GIN (Generalized Inverted Index) è il tuo amico per colonne con valori multipli: array, JSON, full-text search.

-- GIN per array
CREATE INDEX idx_tags_gin ON posts USING GIN(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];  // ✓ Usa GIN

-- GIN per JSON
CREATE INDEX idx_metadata_gin ON users USING GIN(metadata);
SELECT * FROM users WHERE metadata @> '{"verified": true}';

-- GIN per full-text search
CREATE INDEX idx_articles_fts ON articles USING GIN(to_tsvector('italian', body));
SELECT * FROM articles WHERE to_tsvector('italian', body) @@ to_tsquery('italian', 'database');

4. BRIN Index: Per Tabelle Giganti

BRIN (Block Range Index) è minuscolo (1% dello spazio di B-Tree) e perfetto per tabelle ordinate naturalmente per timestamp.

-- BRIN per colonna temporale ordinata naturalmente
CREATE INDEX idx_events_timestamp_brin ON events USING BRIN(created_at);

-- Perfetto per:
SELECT * FROM events WHERE created_at > '2026-06-01';

-- Tabella con 1 miliardo di righe:
-- B-Tree index: 50GB
-- BRIN index: 500MB

Quando usare BRIN: Colonne ordinata naturalmente (timestamp, ID auto-incremento) in tabelle huge.

5. Indici Compositi: L'Ordine Conta

Un indice su (A, B, C) non aiuta query su (C, A). L'ordine è critico.

-- Indice composito
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at);

-- Questo query LO USA (prefisso match):
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

// Questo IGNORA INDICE (non ha user_id):
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

// Questo PARZIALMENTE LO USA (user_id match, status range):
SELECT * FROM orders
WHERE user_id = 123 AND status BETWEEN 'a' AND 'z'
ORDER BY created_at;

6. Come Identificare Indici Inutili

Un indice inutile rallenta INSERT/UPDATE/DELETE senza velocizzare SELECT. Trovali con:

-- PostgreSQL: indici mai usati
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Mysql: unused indexes
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA != 'mysql'
AND COUNT_STAR = 0
ORDER BY SUM_NUMBER_OF_BYTES_READ DESC;

Se un indice ha 0 scansioni da settimane, eliminalo.

7. Checklist: Quando Creare Indici

8. Misurazione: Prima vs Dopo

-- Step 1: Baseline senza indice
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 2000ms

-- Step 2: Crea indice
CREATE INDEX idx_orders_status ON orders(status);

-- Step 3: Richiedi statistiche aggiornate
ANALYZE orders;

-- Step 4: Misura di nuovo
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 50ms

-- Miglioramento: 2000ms → 50ms (40x più veloce!)

Conclusione

Indici non sono magia. Sono trade-off: velocità di lettura vs costo di scrittura e memoria. Usa il tipo giusto, nell'ordine giusto, solo dove necessario.

Misura sempre prima e dopo. Un indice che non aiuta è un peso morto.