Lezione 26: Ottimizzare le Query con gli Indici in MySQL - Una Guida Completa per Principianti

Scopri cosa sono gli indici in MySQL, perché sono fondamentali per la performance del tuo database e come utilizzarli per velocizzare le tue query, anche se sei un principiante.

Benvenuti alla Lezione 26 del nostro corso "Impara MySQL in 45 lezioni"! Oggi affronteremo un argomento cruciale per chiunque voglia scrivere applicazioni web performanti e gestire database efficienti: gli indici in MySQL. Se finora ti sei concentrato sulla creazione di tabelle, inserimento di dati e interrogazioni di base, è arrivato il momento di capire come le tue query possano diventare incredibilmente più veloci.

Immagina di avere un'enciclopedia di migliaia di pagine e di dover trovare tutte le occorrenze della parola "programmazione web". Senza un indice, dovresti sfogliare ogni singola pagina, riga per riga. Un lavoro estenuante e lentissimo! Ora, immagina la stessa enciclopedia con un indice analitico dettagliato che ti indica esattamente a quali pagine si trova la parola. La ricerca diventa istantanea.

Ecco, un indice in un database MySQL funziona esattamente allo stesso modo. È una struttura dati speciale che MySQL può utilizzare per trovare rapidamente le righe in una tabella, proprio come l'indice di un libro ti aiuta a trovare informazioni specifiche senza dover leggere ogni singola parola. Capire come e quando usare gli indici è una delle competenze più importanti per ottimizzare le prestazioni delle tue applicazioni web che interagiscono con un database.

In questa lezione, esploreremo in dettaglio cosa sono gli indici, come funzionano, i diversi tipi disponibili in MySQL, quando e perché usarli (e quando evitarli), e ti guideremo passo dopo passo nella loro creazione e gestione. Preparati a dare una svolta alla velocità delle tue query!

1. Introduzione: Il Segreto della Velocità – Cosa Sono gli Indici in MySQL?

Un indice in MySQL è una struttura dati speciale, associata a una o più colonne di una tabella, che consente al database di trovare rapidamente le righe contenenti valori specifici in quelle colonne. Senza un indice, MySQL dovrebbe eseguire una scansione completa della tabella (detta "full table scan"), esaminando ogni singola riga per trovare quelle che corrispondono ai criteri della tua query. Questo può essere estremamente lento su tabelle con milioni di righe.

Quando crei un indice su una colonna (o un insieme di colonne), MySQL costruisce e mantiene una struttura dati ordinata (spesso un albero B-Tree) che mappa i valori delle colonne ai puntatori alle righe della tabella. Quando esegui una query che filtra o ordina i dati in base a quelle colonne indicizzate, MySQL può utilizzare l'indice per "saltare" direttamente alle righe pertinenti, evitando la scansione completa e migliorando drasticamente la velocità di esecuzione della query.

Non è solo la velocità di ricerca a beneficiare degli indici. Essi possono anche accelerare le operazioni di JOIN tra tabelle, l'ordinamento dei risultati (ORDER BY) e l'aggregazione di dati (GROUP BY). In pratica, ogni volta che MySQL deve cercare, ordinare o confrontare grandi quantità di dati, un indice ben posizionato può fare la differenza tra una query che impiega millisecondi e una che ne impiega secondi (o minuti!).

È fondamentale capire che gli indici non sono una soluzione magica per ogni problema di performance. Hanno un costo: occupano spazio su disco e rallentano le operazioni di scrittura (INSERT, UPDATE, DELETE) perché il database deve aggiornare anche la struttura dell'indice ogni volta che i dati indicizzati cambiano. Pertanto, la chiave è trovare il giusto equilibrio, indicizzando ciò che serve e non di più.

2. Come Funzionano gli Indici: Uno Sguardo Sotto il Cofano

Per capire veramente l'utilità degli indici, è utile avere una panoramica (anche semplificata) del loro funzionamento interno. La maggior parte degli indici in MySQL (in particolare quelli usati dai motori di storage come InnoDB) sono implementati utilizzando una struttura dati chiamata "B-Tree" (Balance Tree).

Immagina un B-Tree come un albero genealogico molto organizzato. Ogni "nodo" dell'albero contiene un certo numero di chiavi (i valori delle colonne che hai indicizzato) e dei puntatori ad altri nodi (o alle righe di dati effettive). I valori all'interno di ogni nodo sono ordinati, e i nodi sono bilanciati in modo che il percorso da qualsiasi radice a qualsiasi foglia sia della stessa lunghezza. Questo garantisce che la ricerca di un valore sia sempre efficiente, indipendentemente da dove si trovi nell'albero.

Quando MySQL deve trovare un valore in una colonna indicizzata:

  1. Inizia dalla radice: Il motore di storage parte dal nodo radice del B-Tree.
  2. Attraversa l'albero: Confronta il valore che sta cercando con le chiavi presenti nel nodo corrente. In base a questo confronto, decide quale "ramo" (puntatore a un nodo figlio) prendere per avvicinarsi al valore desiderato. Poiché i valori sono ordinati, sa sempre dove andare.
  3. Raggiunge la foglia: Continua questo processo fino a raggiungere un nodo "foglia". Questi nodi foglia contengono i valori indicizzati e i puntatori diretti alle righe di dati nella tabella principale (o, nel caso di un indice di chiave primaria in InnoDB, i dati stessi).
  4. Recupera la riga: Una volta trovato il puntatore, MySQL può recuperare rapidamente l'intera riga di dati dalla tabella.

Questo processo è estremamente più veloce di una scansione completa della tabella, specialmente su grandi volumi di dati, perché MySQL deve leggere solo una piccola frazione dei dati per trovare ciò che cerca. La profondità dell'albero B-Tree cresce in modo logaritmico rispetto al numero di righe, il che significa che anche con milioni di righe, l'albero rimane relativamente "basso" e la ricerca richiede solo pochi accessi al disco.

3. Tipi di Indici in MySQL: Conoscere le Opzioni

MySQL offre diversi tipi di indici, ognuno con le sue caratteristiche e scopi specifici. Conoscerli ti aiuterà a scegliere l'indice più adatto per le tue esigenze.

Indice di Chiave Primaria (PRIMARY KEY)

Ogni tabella dovrebbe avere una chiave primaria. Un indice di chiave primaria è un indice UNIQUE (ogni valore deve essere unico) e NOT NULL (non può contenere valori nulli). MySQL crea automaticamente un indice di tipo PRIMARY KEY sulla colonna o sulle colonne designate come chiave primaria. Questo indice è il più importante per l'integrità dei dati e per la velocità delle ricerche dirette.

In InnoDB, il motore di storage più comune in MySQL, l'indice di chiave primaria è un "indice clusterizzato". Questo significa che i dati della tabella sono fisicamente ordinati sul disco in base ai valori della chiave primaria. Quando recuperi una riga tramite la sua chiave primaria, i dati sono già lì, ordinati e veloci da accedere.

Indice Unico (UNIQUE)

Un indice UNIQUE garantisce che tutti i valori nella colonna (o nelle colonne) indicizzata siano unici. Se tenti di inserire o aggiornare una riga con un valore duplicato in una colonna con un indice unico, MySQL restituirà un errore. Oltre a imporre l'unicità, un indice UNIQUE offre anche gli stessi vantaggi di performance di un indice standard per le operazioni di ricerca.

La differenza principale con la PRIMARY KEY è che una tabella può avere una sola PRIMARY KEY ma può avere più indici UNIQUE. Inoltre, le colonne di un indice UNIQUE possono contenere valori NULL (a meno che non siano esplicitamente definite NOT NULL), ma solo una volta per valore NULL (se la colonna è NULL ammissibile).

Indice Standard (Non-Unico)

Questo è il tipo di indice più comune e generico. Viene utilizzato per accelerare le query SELECT su colonne che non richiedono l'unicità. Non impone alcuna restrizione sui valori delle colonne; possono essere duplicati e possono essere NULL (se la colonna lo permette). È ideale per colonne utilizzate frequentemente nelle clausole WHERE, JOIN o ORDER BY.

Indici Full-Text e Spaziali (Breve Menzione)

  • Indici Full-Text: Questi indici sono progettati specificamente per la ricerca di testo all'interno di colonne di tipo stringa (es. TEXT, VARCHAR). Permettono di eseguire ricerche basate su parole chiave complesse, come quelle che troveresti in un motore di ricerca, utilizzando la sintassi MATCH...AGAINST. Sono molto più efficienti della clausola LIKE '%parola%' per la ricerca di testo libero.
  • Indici Spaziali: Utilizzati per dati geografici o geometrici (es. punti, linee, poligoni). Permettono di eseguire query efficienti su questi tipi di dati, come trovare tutti i punti all'interno di un certo raggio. Sono meno comuni nelle applicazioni web generiche ma essenziali per quelle basate sulla posizione.

Per un principiante, concentrarsi su PRIMARY KEY, UNIQUE e indici standard è più che sufficiente. Gli indici Full-Text e Spaziali sono argomenti più avanzati che potrai esplorare in futuro.

4. Il Bilanciamento Performance: Vantaggi e Svantaggi degli Indici

Come accennato, gli indici sono un potente strumento di ottimizzazione, ma non sono privi di costi. Comprendere il bilanciamento tra i loro vantaggi e svantaggi è fondamentale per usarli in modo efficace.

I Vantaggi Innegabili

  1. Velocità delle Query SELECT: Questo è il beneficio principale. Gli indici accelerano notevolmente le query che usano WHERE, JOIN, ORDER BY e GROUP BY sulle colonne indicizzate.
  2. Velocità delle JOIN: Quando due tabelle sono unite (JOIN) su colonne indicizzate, MySQL può trovare rapidamente le righe corrispondenti in entrambe le tabelle.
  3. Ordinamento più Rapido: Se una query include ORDER BY su una colonna indicizzata, MySQL può spesso recuperare i dati già ordinati dall'indice, evitando di dover eseguire un'operazione di ordinamento costosa in memoria o su disco.
  4. Unicità dei Dati: Gli indici PRIMARY KEY e UNIQUE garantiscono l'integrità dei dati, impedendo l'inserimento di valori duplicati.
  5. Copertura delle Query (Covering Indexes): In alcuni casi, una query può essere completamente soddisfatta leggendo solo l'indice, senza dover accedere alla tabella principale. Questo è estremamente veloce.

I Costi Nascosti

  1. Spazio su Disco: Ogni indice occupa spazio su disco. Su tabelle molto grandi con molti indici, questo spazio può diventare significativo.
  2. Rallentamento delle Operazioni di Scrittura: Ogni volta che inserisci (INSERT), aggiorni (UPDATE) o elimini (DELETE) una riga in una tabella, MySQL deve non solo modificare i dati della tabella, ma anche aggiornare tutti gli indici associati a quella tabella. Questo overhead può rallentare notevolmente le operazioni di scrittura, specialmente se ci sono molti indici o se le colonne indicizzate vengono modificate frequentemente.
  3. Complessità di Gestione: Un numero eccessivo di indici o indici mal progettati possono rendere più complessa la manutenzione del database e persino confondere l'ottimizzatore di query di MySQL, portandolo a scegliere un indice meno efficiente.

Quando Indici e Quando No: Regole Generali

Per decidere se creare un indice, considera questi punti:

  • Indicizza le colonne usate frequentemente in WHERE: Se una colonna è spesso utilizzata per filtrare i risultati, indicizzala.
  • Indicizza le colonne usate nelle JOIN: Le colonne utilizzate nelle clausole ON delle JOIN sono ottime candidate per gli indici.
  • Indicizza le colonne usate in ORDER BY e GROUP BY: Se ordini o raggruppi frequentemente i risultati su una colonna, un indice può aiutare.
  • Preferisci colonne con alta cardinalità: La cardinalità si riferisce al numero di valori distinti in una colonna. Indici su colonne con molti valori unici (alta cardinalità, es. email, id_utente) sono molto più efficaci che su colonne con pochi valori distinti (bassa cardinalità, es. sesso, stato_attivo). Indicizzare una colonna con bassa cardinalità (es. una colonna booleana che contiene solo TRUE o FALSE) è raramente utile, perché MySQL potrebbe trovare più efficiente scansionare l'intera tabella piuttosto che usare un indice che punta a metà delle righe.
  • Evita di indicizzare colonne che cambiano molto spesso: Se una colonna è soggetta a frequenti UPDATE, l'overhead di aggiornamento dell'indice potrebbe superare i benefici di performance.
  • Evita di indicizzare colonne con tipi di dati molto grandi: Indici su colonne TEXT o BLOB interi sono raramente una buona idea a causa dello spazio e dell'overhead. Per le colonne TEXT, considera gli indici FULLTEXT o l'indicizzazione di un prefisso (es. INDEX(colonna(100))).
  • Non indicizzare ciecamente: Ogni indice ha un costo. Valuta attentamente l'utilità di ogni indice e rimuovi quelli che non vengono utilizzati o che non portano benefici significativi.

5. Creare, Modificare ed Eliminare Indici: La Sintassi SQL

Ora che abbiamo capito l'importanza e i tipi di indici, vediamo come gestirli in pratica utilizzando SQL.

Creare un Indice su una Tabella Esistente

Puoi creare un indice su una tabella già esistente utilizzando la clausola CREATE INDEX o ALTER TABLE ADD INDEX. La seconda è spesso preferita perché più flessibile e standardizzato.

Supponiamo di avere una tabella prodotti con una colonna nome_prodotto e vogliamo velocizzare le ricerche basate su questo nome.

-- Usando CREATE INDEX
CREATE INDEX idx_nome_prodotto ON prodotti (nome_prodotto);

-- Oppure, usando ALTER TABLE (più comune e raccomandato)
ALTER TABLE prodotti ADD INDEX idx_nome_prodotto (nome_prodotto);

Spiegazione:

  • CREATE INDEX idx_nome_prodotto: Crea un nuovo indice e gli assegna il nome idx_nome_prodotto. È una buona pratica dare nomi significativi agli indici, spesso con il prefisso idx_ seguito dal nome della colonna o delle colonne.
  • ON prodotti (nome_prodotto): Specifica che l'indice deve essere creato sulla tabella prodotti e sulla colonna nome_prodotto.
  • ALTER TABLE prodotti ADD INDEX idx_nome_prodotto (nome_prodotto): Fa la stessa cosa, ma è parte della sintassi ALTER TABLE che usi per modificare la struttura delle tabelle.

Per creare un indice UNIQUE:

ALTER TABLE utenti ADD UNIQUE INDEX idx_email_unico (email);

Questo creerà un indice sulla colonna email della tabella utenti e garantirà che ogni indirizzo email inserito sia unico.

Aggiungere un Indice durante la Creazione della Tabella

Puoi anche definire gli indici direttamente quando crei la tabella. Questo è particolarmente utile per le PRIMARY KEY e per gli indici UNIQUE.

CREATE TABLE ordini (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_utente INT NOT NULL,
    data_ordine DATETIME NOT NULL,
    stato_ordine VARCHAR(50) NOT NULL,
    INDEX idx_id_utente (id_utente), -- Indice standard
    INDEX idx_data_stato (data_ordine, stato_ordine) -- Indice composito
);

Spiegazione:

  • PRIMARY KEY: Definisce id come chiave primaria, e MySQL creerà automaticamente un indice clusterizzato su di essa.
  • INDEX idx_id_utente (id_utente): Crea un indice standard sulla colonna id_utente.
  • INDEX idx_data_stato (data_ordine, stato_ordine): Questo è un indice composito, che copre più colonne. Vedremo la sua utilità più avanti. L'ordine delle colonne in un indice composito è importante!

Visualizzare gli Indici Esistenti

Per vedere quali indici sono presenti su una tabella, puoi usare il comando SHOW INDEXES (o SHOW KEYS).

SHOW INDEXES FROM prodotti;
-- Oppure
SHOW KEYS FROM prodotti;

Questo comando ti mostrerà una lista di tutti gli indici sulla tabella prodotti, inclusi i nomi degli indici, le colonne che coprono, se sono unici, e altre informazioni utili.

Eliminare un Indice

Se un indice non è più necessario o sta causando problemi di performance nelle scritture, puoi eliminarlo.

DROP INDEX idx_nome_prodotto ON prodotti;
-- Oppure (preferito)
ALTER TABLE prodotti DROP INDEX idx_nome_prodotto;

Ricorda che non puoi eliminare l'indice di PRIMARY KEY senza prima eliminare la PRIMARY KEY stessa (che è un'operazione più complessa e raramente necessaria).

6. Esempi Pratici: Vedere gli Indici in Azione

Vediamo alcuni scenari comuni in cui gli indici possono fare una grande differenza.

Creiamo una tabella di esempio con un po' di dati per simulare una situazione reale. Useremo circa 100.000 righe per rendere l'effetto degli indici più evidente.

CREATE TABLE utenti_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    cognome VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    data_registrazione DATETIME NOT NULL,
    stato_attivo BOOLEAN NOT NULL DEFAULT TRUE
);

-- Inseriamo 100.000 righe di dati fittizi
DELIMITER //
CREATE PROCEDURE insert_dummy_users()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 100000 DO
        INSERT INTO utenti_test (nome, cognome, email, data_registrazione, stato_attivo)
        VALUES (
            CONCAT('Nome', i),
            CONCAT('Cognome', i),
            CONCAT('email', i, '@esempio.com'),
            NOW() - INTERVAL FLOOR(RAND() * 365) DAY,
            IF(RAND() > 0.1, TRUE, FALSE) -- 90% attivi, 10% non attivi
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_dummy_users();
DROP PROCEDURE insert_dummy_users;

Ora che abbiamo i dati, proviamo alcune query.

Scenario 1: Ottimizzare una Ricerca con WHERE

Supponiamo di voler cercare utenti per email.

-- Query senza indice (potrebbe essere lenta)
SELECT * FROM utenti_test WHERE email = 'email50000@esempio.com';

Per vedere come MySQL esegue questa query, possiamo usare EXPLAIN:

EXPLAIN SELECT * FROM utenti_test WHERE email = 'email50000@esempio.com';

Output di EXPLAIN (prima dell'indice): id: 1, select_type: SIMPLE, table: utenti_test, partitions: NULL, type: ALL, possible_keys: NULL, key: NULL, key_len: NULL, ref: NULL, rows: 100000, filtered: 10.00, Extra: Using where

Notate type: ALL e rows: 100000. Questo significa che MySQL sta eseguendo una scansione completa della tabella, esaminando tutte le 100.000 righe.

Ora, aggiungiamo un indice sulla colonna email:

ALTER TABLE utenti_test ADD UNIQUE INDEX idx_email (email);

Rieseguiamo la stessa query e EXPLAIN:

EXPLAIN SELECT * FROM utenti_test WHERE email = 'email50000@esempio.com';

Output di EXPLAIN (dopo l'indice): id: 1, select_type: SIMPLE, table: utenti_test, partitions: NULL, type: const, possible_keys: idx_email, key: idx_email, key_len: 403, ref: const, rows: 1, filtered: 100.00, Extra: NULL

Ora, type: const (o ref o eq_ref per ricerche uniche) e rows: 1 indicano che MySQL sta usando l'indice idx_email per trovare la riga direttamente. La query sarà quasi istantanea.

Scenario 2: Accelerare le JOIN tra Tabelle

Gli indici sono cruciali per le performance delle JOIN. Supponiamo di avere una tabella ordini_test e di voler unire con utenti_test.

CREATE TABLE ordini_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_utente INT NOT NULL,
    importo DECIMAL(10, 2) NOT NULL,
    data_ordine DATETIME NOT NULL
);

-- Inseriamo alcuni ordini per utenti casuali
DELIMITER //
CREATE PROCEDURE insert_dummy_orders()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 50000 DO
        INSERT INTO ordini_test (id_utente, importo, data_ordine)
        VALUES (
            FLOOR(1 + RAND() * 100000), -- id_utente casuale tra 1 e 100000
            ROUND(10 + (RAND() * 990), 2), -- importo casuale tra 10 e 1000
            NOW() - INTERVAL FLOOR(RAND() * 365) DAY
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL insert_dummy_orders();
DROP PROCEDURE insert_dummy_orders;

-- Query di JOIN senza indice su id_utente in ordini_test
SELECT u.nome, u.cognome, o.importo, o.data_ordine
FROM utenti_test u
JOIN ordini_test o ON u.id = o.id_utente
WHERE u.id = 12345;

EXPLAIN SELECT u.nome, u.cognome, o.importo, o.data_ordine
FROM utenti_test u
JOIN ordini_test o ON u.id = o.id_utente
WHERE u.id = 12345;

L'output di EXPLAIN per ordini_test mostrerà probabilmente una scansione completa (type: ALL) o una scansione dell'indice (type: index) ma ancora non ottimale se id_utente non è indicizzato.

Ora aggiungiamo un indice su id_utente nella tabella ordini_test:

ALTER TABLE ordini_test ADD INDEX idx_id_utente (id_utente);

Rieseguiamo la query e EXPLAIN:

EXPLAIN SELECT u.nome, u.cognome, o.importo, o.data_ordine
FROM utenti_test u
JOIN ordini_test o ON u.id = o.id_utente
WHERE u.id = 12345;

Vedrete che ordini_test ora userà l'indice idx_id_utente (type: ref), rendendo la JOIN molto più veloce. Questo è fondamentale per le relazioni tra tabelle.

Scenario 3: Migliorare l'Ordinamento (ORDER BY)

Se ordini frequentemente i risultati per una certa colonna, un indice può eliminare la necessità di un'operazione di ordinamento separata.

-- Query di ordinamento senza indice su data_registrazione
SELECT id, nome, cognome, data_registrazione
FROM utenti_test
ORDER BY data_registrazione DESC
LIMIT 10;

EXPLAIN SELECT id, nome, cognome, data_registrazione
FROM utenti_test
ORDER BY data_registrazione DESC
LIMIT 10;

L'output di EXPLAIN mostrerà Extra: Using filesort. filesort indica che MySQL deve eseguire un'operazione di ordinamento separata, che può essere lenta per grandi set di dati.

Ora aggiungiamo un indice su data_registrazione:

ALTER TABLE utenti_test ADD INDEX idx_data_registrazione (data_registrazione);

Rieseguiamo la query e EXPLAIN:

EXPLAIN SELECT id, nome, cognome, data_registrazione
FROM utenti_test
ORDER BY data_registrazione DESC
LIMIT 10;

L'output di EXPLAIN ora non dovrebbe più mostrare Using filesort. MySQL può usare l'indice per recuperare i dati già ordinati, rendendo la query molto più efficiente.

Usare EXPLAIN per Analizzare le Query (Introduzione)

Come hai visto negli esempi, il comando EXPLAIN è il tuo migliore amico quando si tratta di ottimizzazione delle query. Ti mostra il "piano di esecuzione" che MySQL intende seguire per eseguire la tua query. Analizzando l'output di EXPLAIN, puoi capire se MySQL sta usando gli indici che ti aspetti o se sta eseguendo costose scansioni complete della tabella.

Gli elementi chiave da osservare in EXPLAIN sono:

  • type: Indica come MySQL accede ai dati. const, eq_ref, ref, range sono buoni. ALL (full table scan) è quasi sempre un problema su tabelle grandi.
  • possible_keys: Gli indici che MySQL potrebbe usare.
  • key: L'indice che MySQL ha effettivamente usato.
  • rows: Il numero stimato di righe che MySQL deve esaminare. Un numero basso è meglio.
  • Extra: Informazioni aggiuntive. Using filesort e Using temporary indicano operazioni costose che gli indici a volte possono evitare.

Imparare a interpretare EXPLAIN è una competenza fondamentale per ogni sviluppatore web che lavora con database, e ti incoraggio a usarlo regolarmente per testare l'efficacia dei tuoi indici e delle tue query.

7. Errori Comuni e Migliori Pratiche con gli Indici

Anche se potenti, gli indici possono essere usati in modo errato, portando a problemi anziché soluzioni. Ecco alcuni errori comuni e come evitarli.

Indicizzare Troppo o Troppo Poco

  • Troppo Poco: L'errore più comune per i principianti. Non indicizzare le colonne chiave porta a query lente e un'esperienza utente frustrante. Se una query è lenta e non usa indici dove potrebbe, è il primo posto dove guardare.
  • Troppo: Indicizzare ogni colonna "per sicurezza" è un errore. Troppi indici aumentano lo spazio su disco e, cosa più importante, rallentano significativamente le operazioni INSERT, UPDATE e DELETE. Ogni modifica ai dati deve aggiornare tutti gli indici pertinenti, e questo costo può rapidamente superare i benefici delle SELECT accelerate.

Migliore Pratica: Indicizza solo le colonne che sono frequentemente utilizzate nelle clausole WHERE, JOIN, ORDER BY o GROUP BY. Rivedi periodicamente i tuoi indici e rimuovi quelli che non sono più necessari o che non vengono utilizzati dall'ottimizzatore di query.

Indicizzare le Colonne Sbagliate

  • Bassa Cardinalità: Indicizzare colonne con pochi valori distinti (es. stato_attivo con solo TRUE/FALSE) è raramente efficace. MySQL potrebbe preferire una scansione completa piuttosto che usare un indice che punta a metà delle righe della tabella.
  • Colonne con LIKE '%stringa': Gli indici B-Tree non possono essere usati per ricerche LIKE che iniziano con un wildcard (es. LIKE '%parola'). Possono essere usati solo se il wildcard è alla fine (es. LIKE 'parola%'). Per ricerche di testo libero, considera gli indici FULLTEXT.

Migliore Pratica: Concentrati su colonne con alta cardinalità. Per le ricerche di testo complesse, esplora soluzioni specifiche come gli indici FULLTEXT o motori di ricerca esterni (es. Elasticsearch).

Non Considerare l'Ordine nelle Colonne degli Indici Compositi

Un indice composito (su più colonne, es. (colonna1, colonna2, colonna3)) è utile per query che filtrano su tutte o un prefisso delle colonne indicizzate. L'ordine delle colonne è cruciale.

Se hai un indice (cognome, nome):

  • Sarà usato per query WHERE cognome = 'Rossi' AND nome = 'Mario'.
  • Sarà usato per query WHERE cognome = 'Rossi'.
  • NON sarà usato per query WHERE nome = 'Mario' (a meno che MySQL non trovi un altro indice o decida che una scansione completa è più efficiente).

Migliore Pratica: Metti la colonna più selettiva (quella che riduce maggiormente il set di risultati) all'inizio dell'indice composito. Se una colonna è usata più frequentemente da sola, potrebbe aver bisogno di un suo indice separato.

Ignorare l'Analisi delle Query con EXPLAIN

Molti sviluppatori creano indici e sperano che funzionino. Senza usare EXPLAIN, non sai mai con certezza se MySQL sta effettivamente utilizzando i tuoi indici e se li sta usando nel modo più efficiente.

Migliore Pratica: Fai di EXPLAIN il tuo strumento di debugging principale per le performance delle query. Ogni volta che una query è lenta, usa EXPLAIN per capire perché e come gli indici potrebbero aiutare.

8. Prossimi Passi: Continua il Tuo Viaggio nell'Ottimizzazione MySQL

Congratulazioni! Hai fatto un passo enorme nella comprensione dell'ottimizzazione delle performance in MySQL. Gli indici sono una delle tecniche più efficaci per velocizzare le tue applicazioni web.

Ecco alcuni argomenti e concetti correlati che potresti voler esplorare nei tuoi prossimi passi per approfondire la tua conoscenza:

  • Indici Compositi Avanzati: Approfondisci come l'ordine delle colonne e la "copertura" delle query influenzano l'efficacia degli indici compositi.
  • Indici su Prefissi: Scopri come indicizzare solo una parte di una colonna di tipo stringa per risparmiare spazio e migliorare le performance, specialmente per colonne molto lunghe.
  • Indici Funzionali/Espressioni (MySQL 8+): Impara a creare indici su espressioni o funzioni, permettendo di indicizzare i risultati di calcoli o manipolazioni di colonne.
  • Query Optimizer di MySQL: Approfondisci come l'ottimizzatore di query di MySQL decide quale indice usare (o non usare) e come influenzarlo con hint.
  • Statistiche del Database: Comprendi l'importanza delle statistiche del database per l'ottimizzatore e come mantenerle aggiornate (ANALYZE TABLE).
  • Partizionamento delle Tabelle: Per tabelle estremamente grandi, il partizionamento può migliorare ulteriormente le performance, soprattutto in combinazione con gli indici.
  • Hardware e Configurazione di MySQL: Ricorda che gli indici sono solo una parte dell'equazione. La configurazione del server MySQL (buffer pool, cache) e l'hardware sottostante sono altrettanto cruciali per le performance complessive.

Gli indici sono un vasto e affascinante argomento. La pratica e l'esperienza ti guideranno nel prendere le decisioni migliori per i tuoi specifici carichi di lavoro. Continua a sperimentare, a misurare e a ottimizzare! Nella prossima lezione, potremmo esplorare un altro aspetto fondamentale della programmazione web con MySQL, magari concentrandoci sulle viste o sulle stored procedure.

Continua a studiare e a costruire, e vedrai le tue applicazioni web diventare sempre più veloci e robuste!