Risposta

Indici — strutture dati speciali (di solito basate su B-tree) utilizzate per la ricerca rapida di dati in una colonna specifica o in un insieme di colonne di una tabella. Gli indici accelerano il recupero, l'ordinamento e il filtraggio, riducendo il numero di righe esaminate.

Tipi di indici:

• Normali (B-tree, hash)
• Compositi (multi-colonna)
• Unici (garantiscono l'unicità dei valori)
• Coprenti (covering)
• Testo completo (full-text)

Gli indici accelerano:

• WHERE ... = ...
• JOIN su una colonna indicizzata
• ORDER BY e GROUP BY su una colonna indicizzata

Possono rallentare:

• Inserimenti, aggiornamenti, eliminazioni — gli indici richiedono operazioni aggiuntive per mantenere la loro struttura.
• Se le query utilizzano frequentemente colonne per cui non ci sono indici, gli indici saranno inutili, e durante inserimenti massivi — anche dannosi.

Esempio di creazione di un indice:

CREATE INDEX idx_user_email ON users (email);

Esempio in cui l'indice non aiuta:

SELECT * FROM users WHERE lower(email) = 'test@example.com';
-- se l'indice è costruito su email, ma la query utilizza la funzione lower(email), l'indice non viene usato!

Domanda insidiosa

Se si aggiunge un indice a tutte le colonne della tabella, l'esecuzione di tutte le query SELECT migliorerà sempre?

Risposta:
No. Gli indici velocizzano solo quelle query in cui il filtraggio o l'ordinamento avviene esclusivamente su colonne indicizzate senza l'uso di funzioni o operazioni che ostacolano l'utilizzo dell'indice. Un numero eccessivo di indici non solo rallenta INSERT/UPDATE/DELETE, ma occupa anche molto spazio, e alcune query complesse possono anche ignorare gli indici (ad esempio, durante la scansione per intervallo con una espressione).

Esempio:

SELECT * FROM orders WHERE year(order_date) = 2023;
-- se l'indice è solo su order_date, l'indice non funziona a causa della funzione year()

---

Storia

In un sistema di posta elettronica è stato creato un trigger che creava un indice per ogni campo frequentemente utilizzato. Dopo sei mesi, le prestazioni del sistema sono crollate — ogni inserimento o modifica di una riga richiedeva 4-5 volte più tempo. Dopo un audit, il numero di indici è stato ridotto e il sistema ha ripreso velocità.

---

Storia

In una piattaforma pubblicitaria si verificavano frequentemente query SELECT con filtro su substring(url, 1, 10). Nonostante l'indice su url, SQL non ha utilizzato l'indice a causa della funzione substring. La soluzione è stata l'introduzione di un campo separato per tale selezione e un indice su di esso.

---

Storia

In un programma di fidelizzazione è stato creato un indice composto su campi (customer_id, shop_id). Durante le query solo su shop_id, l'indice non veniva applicato e si verificava una scansione completa della tabella (full scan). Questo ha portato alla perdita dei vantaggi dall'indice nei calcoli dei bonus. L'ottimizzazione ha aiutato: indice separato su shop_id.