Corso di SQL: 9 – Ottimizzazione Query e Indici

9 – Ottimizzazione Query e Indici

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

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

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

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

3. Indice composito: creare solo se le colonne vengono usate insieme frequentemente nelle query.

4. Non creare troppi indici: rallentano INSERT/UPDATE/DELETE.

5. **Limitare SELECT ***: selezionare solo le colonne necessarie.

6. Normalizzazione e denormalizzazione: strutturare i dati per evitare join pesanti o query complesse.

7. 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

1. Creare indice su colonna email della tabella users.

2. Analizzare una query con EXPLAIN e interpretare il piano di esecuzione.

3. Creare un indice composito su orders(customer_id, order_date) e testare differenza di performance.

4. Identificare query che non utilizzano indici a causa di funzioni o tipi incompatibili e correggerle.

5. 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.