sabato 15 novembre 2025

Corso di SQL: 9 – Ottimizzazione Query e Indici

9 – Ottimizzazione Query e Indici

Obiettivi

Comprendere il concetto di indice e il suo impatto sulle performance.
Imparare a creare e gestire indici (CREATE INDEX, DROP INDEX).
Analizzare l’esecuzione delle query con EXPLAIN.
Applicare best practices per scrivere query veloci e scalabili.

1) Concetto di indice

Un indice è una struttura dati (tipicamente B-Tree o Hash) che accelera la ricerca di righe in una tabella.
Senza indice, il DBMS esegue una full table scan: legge ogni riga della tabella per trovare i risultati.
Con un indice, il DBMS può accedere direttamente alle righe corrispondenti ai criteri di ricerca.

Esempio analogico: cercare un nome in un libro senza indice = leggere tutte le pagine; con indice = usare l’indice alfabetico finale.

2) Tipi di indici comuni

Tipo di indice	Descrizione
B-Tree	Standard per colonne con uguaglianze e range; supporta ordinamento.
Hash	Ottimo per uguaglianze (`=`); non per range (`<, >`).
Unico (UNIQUE)	Garantisce valori unici nella colonna.
Composito	Indice su più colonne; utile per query che filtrano su più campi.
Full-text	Ricerca testo completo (MySQL: `FULLTEXT INDEX`).
Spatial	Dati geografici (PostGIS su PostgreSQL).

3) Creazione e gestione degli indici

MySQL

-- creare indice semplice
CREATE INDEX idx_students_name ON students(name);

-- indice unico
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- indice composito
CREATE INDEX idx_student_grade_subject ON grades(student_id, subject);

-- cancellare indice
DROP INDEX idx_students_name ON students;

PostgreSQL

-- indice semplice
CREATE INDEX idx_students_name ON students(name);

-- indice unico
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- indice composito
CREATE INDEX idx_student_grade_subject ON grades(student_id, subject);

-- cancellare indice
DROP INDEX idx_students_name;

4) Analisi delle query: EXPLAIN

Il comando EXPLAIN mostra il piano di esecuzione di una query.
Permette di capire se il DB usa un indice o effettua full table scan.

Esempi

EXPLAIN SELECT * FROM students WHERE name = 'Anna Rossi';
EXPLAIN SELECT * FROM grades WHERE student_id = 1 AND subject = 'Math';

Campi importanti (MySQL):

type: tipo di accesso (ALL = full scan, ref = utilizzo indice).
possible_keys: quali indici il DB può usare.
key: indice effettivamente usato.
rows: numero stimato di righe lette.

Campi importanti (PostgreSQL):

Seq Scan = full table scan
Index Scan = utilizzo di indice
Filter = condizioni di filtro applicate

5) Best practices per query veloci

Usare indici sulle colonne filtrate e joinate (WHERE, JOIN ON).

Evita funzioni sulle colonne filtrate, perché invalidano l’indice:

-- non ottimale
WHERE UPPER(name) = 'ANNA ROSSI';
-- ottimale
WHERE name = 'Anna Rossi';

Indice composito: creare solo se le colonne vengono usate insieme frequentemente nelle query.
Non creare troppi indici: rallentano INSERT/UPDATE/DELETE.
**Limitare SELECT ***: selezionare solo le colonne necessarie.
Normalizzazione e denormalizzazione: strutturare i dati per evitare join pesanti o query complesse.
Usare EXPLAIN regolarmente per verificare che le query sfruttino gli indici.

6) Esempi pratici

6.1 Query lenta senza indice

SELECT * FROM grades WHERE student_id = 12345;
-- Se la tabella ha 1 milione di righe, full table scan → lento

6.2 Query ottimizzata con indice

CREATE INDEX idx_grades_student_id ON grades(student_id);

SELECT * FROM grades WHERE student_id = 12345;
-- Uso di idx_grades_student_id → accesso diretto → più veloce

6.3 Indice composito per join veloce

CREATE INDEX idx_grades_student_subject ON grades(student_id, subject);

SELECT g.vote, s.name
FROM grades g
JOIN students s ON g.student_id = s.student_id
WHERE g.subject = 'Math';

7) Esercizio svolto: ottimizzare query

Scenario: Tabella orders con 1 milione di righe.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total DECIMAL(10,2)
);

Query lenta:

SELECT * FROM orders WHERE customer_id = 5678 AND order_date >= '2025-01-01';

Soluzione ottimizzata:

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

EXPLAIN SELECT * FROM orders WHERE customer_id = 5678 AND order_date >= '2025-01-01';
-- verifica che venga usato l'indice composito

Risultato: lettura molto più veloce e riduzione di righe scansionate.

8) Esercizi proposti

Creare indice su colonna email della tabella users.
Analizzare una query con EXPLAIN e interpretare il piano di esecuzione.
Creare un indice composito su orders(customer_id, order_date) e testare differenza di performance.
Identificare query che non utilizzano indici a causa di funzioni o tipi incompatibili e correggerle.
Simulare inserimenti massivi e osservare impatto degli indici sulle performance di INSERT.

9) Checklist rapida

Indici su colonne filtrate e joinate.
Limitare SELECT * e recuperare solo colonne necessarie.
Usare EXPLAIN per monitorare query.
Evitare funzioni sulle colonne degli indici.
Non creare troppi indici: bilanciare letture e scritture.
Creare indici compositi solo quando necessario per query frequenti.

SILICIO E NUVOLE