SQL Academy · Lezione

Indici compositi e ordine delle colonne

Scelga l’ordine delle colonne per gli indici compositi, comprenda la regola del prefisso sinistro e progetti indici che coprano più query.

Indici compositi e ordine delle colonne è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è un indice composto?

È un indice su più di una colonna. I B-tree di PostgreSQL possono indicizzare fino a 32 colonne; nella pratica, 2–4 è il numero più comune.

CREATE INDEX orders_user_created_idx ON orders(user_id, created_at DESC);

Perché usare un indice composto?

Due scenari:

La query filtra contemporaneamente su più colonne
La query filtra su una colonna e ordina in base a un'altra

Regola del prefisso più a sinistra

Un indice su (a, b, c) può servire:

WHERE a = ?
WHERE a = ? AND b = ?
WHERE a = ? AND b = ? AND c = ?

NON può servire WHERE b = ? da solo, né WHERE c = ?.

L'ordine conta

Metta per prima la colonna più selettiva e filtrata più frequentemente. Le colonne usate per l'uguaglianza devono precedere quelle usate per gli intervalli:

CREATE INDEX orders_user_total_idx ON orders(user_id, total);

-- Best for:  WHERE user_id = 42 AND total > 100
-- Also OK:  WHERE user_id = 42
-- Useless:  WHERE total > 100   (without user_id)

Uguaglianza e intervalli

I predicati di uguaglianza utilizzano l'indice in modo efficiente; gli intervalli lo utilizzano, ma ne limitano l'utilità successiva. Le colonne usate per l'uguaglianza devono venire per prime:

-- Index: (status, created_at)
WHERE status = 'paid' AND created_at >= ...   -- both columns used
WHERE created_at >= ... AND status = 'paid'  -- index used for status, range scan on created_at

Ordine dell'indice = ordine di ordinamento

Un indice su (user_id, created_at DESC) gestisce la query "ordini dell'utente, dal più recente" senza un passaggio di ordinamento:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC LIMIT 10;
-- Index Scan, no Sort node needed

Includere colonne non chiave (INCLUDE)

PG 11+ supporta INCLUDE per gli indici "covering": colonne aggiuntive memorizzate nelle foglie, ma non utilizzate per l'ordinamento:

CREATE INDEX orders_user_idx ON orders(user_id)
  INCLUDE (total, status);

-- Enables Index-Only Scan for queries that need just these columns.

Quando NON creare indici composti

Se le colonne vengono interrogate indipendentemente, due indici su una sola colonna possono servire più query. Gli indici composti sacrificano la flessibilità per offrire "tutto il necessario in un'unica query".

Combinazione di indici bitmap

PostgreSQL può combinare più indici su una singola colonna tramite Bitmap Index Scan:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- BitmapAnd of two index scans — works without a composite, but slower than the right composite.

Monitorare il bloat degli indici

Gli indici compositi sono più ampi. Indici più ampi = file più grandi = una porzione minore dell'indice entra nella cache. Usarli con criterio.

Scelta dell'ordine delle colonne — un esempio pratico

Query: «ordini di un utente in un intervallo di date, ordinati per data».

-- Index design:
CREATE INDEX orders_user_created_idx
  ON orders(user_id, created_at DESC);

-- Query (efficiently uses the index):
SELECT * FROM orders
WHERE user_id = 42
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC LIMIT 10;

Riepilogo

Gli indici compositi gestiscono i filtri su più colonne ed evitano i passaggi di ordinamento.

Regola del prefisso più a sinistra
Prima le colonne usate per l'uguaglianza
INCLUDE per le colonne usate solo per coprire la query
Due indici singoli o uno composito: dipende dalla combinazione delle query

Verifica rapida

Si dispone di un indice su (user_id, status, created_at). Quale query NON può usarlo in modo efficiente?

Domande Frequenti

La lezione «Indici compositi e ordine delle colonne» è gratuita?

Sì — il testo completo di «Indici compositi e ordine delle colonne» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Indici compositi e ordine delle colonne»?

Scelga l’ordine delle colonne per gli indici compositi, comprenda la regola del prefisso sinistro e progetti indici che coprano più query. Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.

Quanto tempo richiede la lezione «Indici compositi e ordine delle colonne»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

← Torna a SQL Academy