Cosa sono gli indici SQL? Perché sono importanti e come funzionano

Gli indici sono tra gli strumenti più importanti per migliorare le prestazioni dei database.

In questo articolo scoprirai:

• Cosa sono gli indici SQL
• Perché accelerano notevolmente le query
• Come crearli
• Un esempio che mostra le differenze di prestazioni
• Come funzionano internamente

Rendiamo intuitivi gli indici.

Cos’è un indice?

Un indice in SQL è una struttura dati speciale (di solito un B-Tree) che permette al database di trovare le righe molto più rapidamente, senza scansionare l’intera tabella.

Puoi immaginare un indice come:

• Un elenco ordinato di valori
• Ogni valore punta a una riga della tabella principale
• Organizzato in modo che il database possa cercarvi rapidamente (tempo logaritmico)

Senza indice → il database deve controllare ogni riga.
Con un indice → il database passa direttamente ai valori corrispondenti.

Perché sono stati creati gli indici SQL

Gli indici esistono per risolvere un problema:

Velocizzare le query che cercano valori specifici.

Sono stati creati perché le scansioni complete delle tabelle diventano troppo lente quando i dataset crescono.
In una tabella di 10 righe, scansionarle tutte è veloce.
In una tabella di 10 milioni di righe, farlo ripetutamente diventa impraticabile.

Gli indici consentono:

• Ricerche rapide
• Ordinamenti rapidi (ORDER BY)
• Join rapidi
• Verifiche di unicità rapide (tramite indice UNIQUE)

Database come PostgreSQL, MySQL e SQLite si basano tutti su di essi.

Esempio con JOIN

Gli indici accelerano notevolmente anche i JOIN.

Usiamo le tabelle users e orders:

SELECT users.name, orders.product
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.email = 'bob@example.com';

Indici consigliati

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_id ON orders(user_id);

Senza indici: scansioni complete delle tabelle su entrambi i lati

Quando non esistono indici su users.email e orders.user_id, il database deve:

• Scansionare ogni riga di users per trovare le righe che soddisfano la condizione sull’email
• Scansionare ogni riga di orders per trovare quelle il cui user_id corrisponde a quegli ID utente

Questa operazione si chiama scansione sequenziale o scansione completa della tabella.

Dimensioni di esempio:

• Tabella users: 100.000 righe
• Tabella orders: 1.000.000 di righe

Il database deve esaminare:

• 100.000 righe di utenti
• 1.000.000 di righe di ordini

Anche se il risultato finale potrebbe essere una sola riga, il costo della ricerca è molto elevato.

Prestazioni indicative (variano a seconda della macchina):

• Controllo di 100.000 utenti + 1 milione di ordini
• Senza indici: da circa 300 a 600 ms
• Con indici: da circa 1 a 3 ms

È un miglioramento da 100× a 500×.

Con gli indici: ricerca rapida e diretta

Con i due indici:

• idx_users_email → permette di trovare subito l’utente con quell’email
• idx_orders_user_id → permette di trovare subito tutti gli ordini appartenenti a quell’utente

Invece di scansionare milioni di righe:

• Il database passa direttamente alle voci corrette dell’indice (una ricerca nel B-tree)
• Vengono consultate solo le righe pertinenti
• Il JOIN diventa estremamente veloce

Cosa influisce sulle prestazioni?

I tempi esatti variano in base all’hardware, ma questi fattori della macchina contano più degli altri:

Gli indici sono essenziali nelle applicazioni perché persino macchine potenti faticano a eseguire scansioni complete ripetute quando i dati crescono.

• Senza indici: il database deve leggere ogni riga di entrambe le tabelle
• Con gli indici: il database esegue ricerche rapide usando strutture dati efficienti
• Questo permette ai JOIN di scalare fino a milioni di righe con tempi di risposta dell’ordine dei millisecondi

Come funzionano internamente gli indici (spiegazione semplice)

La maggior parte dei database SQL usa B-Tree, che:

• Mantengono i valori ordinati
• Consentono una ricerca simile a quella binaria
• Hanno una profondità estremamente ridotta (di solito da 3 a 5 livelli anche per tabelle enormi)

Questo significa che:

• Trovare una riga richiede solo pochi confronti
• L’accesso è prevedibile e veloce
• Gli indici crescono in modo logaritmico, non lineare

Non serve comprendere a fondo l’algoritmo, ma ecco l’idea fondamentale:

Cercare in un albero ordinato è molto più veloce che cercare in un milione di righe non ordinate.

Quando usare un indice?

Usa un indice quando:

• Esegui spesso SELECT su una colonna
• Filtri con WHERE
• Ordini usando ORDER BY
• Esegui JOIN tra tabelle sulle chiavi esterne
• Imponi l’unicità (UNIQUE INDEX)
• Cerchi tramite query di uguaglianza o di intervallo

Gli indici sono più utili su:

• email
• nome utente
• created_at
• chiavi esterne (user_id, post_id)
• slug
• colonne usate per le ricerche

Quando non usare un indice

Gli indici hanno un costo:

• Occupano spazio su disco
• Rallentano le scritture (INSERT/UPDATE/DELETE devono aggiornare l’indice)

Evita di indicizzare:

• colonne booleane
• colonne con cardinalità molto bassa (ad esempio, genere, stato = “attivo”)
• colonne usate raramente nelle query
• colonne di testo molto grandi