Come controllare e gestire gli indici in MySQL: guida a SHOW INDEX, INFORMATION_SCHEMA e EXPLAIN

1. Cos’è un indice MySQL? La chiave per migliorare le prestazioni del database

Utilizzando efficacemente gli indici in un database MySQL, è possibile migliorare significativamente le prestazioni delle query. Un indice è una struttura dati creata su colonne specifiche di una tabella del database per aumentare la velocità di ricerca e filtraggio. Ad esempio, quando si estrae informazioni specifiche da un grande set di dati, l’uso di un indice consente a MySQL di saltare la scansione di tutte le righe e di cercare solo nella colonna indicizzata.

Ruoli e tipi di indici

MySQL fornisce i seguenti tipi di indici:

• PRIMARY (Chiave primaria) : Una chiave unica consentita una sola volta per tabella, usata come identificatore principale della tabella.
• Indice UNIQUE : Un indice che impone l’unicità e impedisce l’inserimento di valori duplicati nella colonna specificata.
• Indice regolare : Un indice senza vincolo di unicità, usato per migliorare le prestazioni di ricerca su colonne specifiche.

Come mostrato sopra, gli indici migliorano l’efficienza delle ricerche e delle operazioni sui dati nelle tabelle e sono essenziali per grandi set di dati. Tuttavia, avere troppi indici può rallentare le operazioni INSERT e UPDATE, quindi una corretta gestione degli indici è importante.

2. Metodi di base per verificare gli indici in MySQL

In MySQL, è possibile utilizzare il comando SHOW INDEX per verificare gli indici esistenti. Questo semplice comando SQL visualizza le informazioni sugli indici per una tabella specificata. La procedura specifica è spiegata di seguito.

Sintassi di base e output di SHOW INDEX

SHOW INDEX FROM table_name;

Spiegazione delle colonne di output

Quando esegui questo comando, vengono visualizzate le seguenti informazioni:

• Table : Il nome della tabella in cui l’indice esiste
• Non_unique : Indica se l’indice consente duplicati (1) o è unico (0)
• Key_name : Il nome dell’indice
• Column_name : Il nome della colonna a cui è applicato l’indice
• Cardinality : Una stima del numero di valori unici nell’indice. Viene usata come indicatore dell’efficienza di ricerca.

Utilizzando queste informazioni, è possibile comprendere visivamente lo stato degli indici all’interno di una tabella e come gli indici sono applicati a ciascuna colonna. È inoltre possibile restringere i risultati usando una clausola WHERE, se necessario.

3. Verifica degli indici usando la tabella INFORMATION_SCHEMA.STATISTICS

Oltre all’istruzione SHOW INDEX, MySQL consente di verificare gli indici interrogando la tabella INFORMATION_SCHEMA.STATISTICS. Questo metodo è utile per elencare gli indici su un intero database e fornisce informazioni più dettagliate.

Query di base per INFORMATION_SCHEMA.STATISTICS

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, INDEX_NAME 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'database_name';

Dettagli del risultato della query

• TABLE_SCHEMA : Il nome del database a cui appartiene l’indice
• TABLE_NAME : Il nome della tabella in cui l’indice esiste
• COLUMN_NAME : Il nome della colonna a cui è applicato l’indice
• INDEX_NAME : Il nome dell’indice

Questo metodo consente di visualizzare le informazioni sugli indici su più tabelle o su un database specifico in un unico elenco. È particolarmente utile quando si gestiscono gli indici su un intero database.

4. Come aggiungere e rimuovere gli indici e il loro impatto

Come aggiungere un indice

È possibile aggiungere indici in seguito, secondo necessità. Usa il comando seguente per creare un indice su una colonna specificata:

CREATE INDEX index_name ON table_name(column_name);

Ad esempio, se vuoi aggiungere un indice alla colonna email della tabella users, esegui il seguente comando:

CREATE INDEX idx_email ON users(email);

Come rimuovere un indice

Puoi rimuovere gli indici non necessari per ottimizzare le prestazioni di INSERT e UPDATE. Usa il comando DROP INDEX per eliminare un indice:

DROP INDEX index_name ON table_name;

Un esempio di indice non necessario è quello creato su una colonna che non viene utilizzata nelle condizioni di ricerca (clausole WHERE). Rimuovere tali indici può migliorare la velocità di inserimento e aggiornamento dei dati.

5. Verifica delle prestazioni degli indici usando l’istruzione EXPLAIN

L’istruzione EXPLAIN di MySQL è utile per verificare il piano di esecuzione della query e identificare quali indici vengono utilizzati. Questo aiuta a valutare l’efficacia degli indici e a ottimizzare le prestazioni quando necessario.

Uso base dell’istruzione EXPLAIN

EXPLAIN SELECT * FROM table_name WHERE column_name = 'condition';

Utilizzando questo comando, è possibile determinare se un indice viene utilizzato o se viene eseguita una scansione completa della tabella. Il risultato include le seguenti colonne:

• type : Il tipo di query (ALL indica una scansione completa della tabella; INDEX indica che viene utilizzato un indice)
• possible_keys : Un elenco di indici che potrebbero essere utilizzati per la query
• key : Il nome dell’indice effettivamente utilizzato
• rows : Il numero stimato di righe scansionate

Sulla base di queste informazioni, è possibile analizzare l’efficacia degli indici e determinare se sono necessari miglioramenti delle prestazioni di ricerca.

6. Conclusione

Una corretta gestione degli indici è essenziale per ottimizzare le prestazioni del database MySQL. In particolare per le tabelle che gestiscono grandi volumi di dati, impostare indici sulle colonne utilizzate nelle clausole WHERE e nelle operazioni JOIN può migliorare notevolmente l’efficienza della ricerca. Tuttavia, avere troppi indici può rallentare le operazioni di inserimento e aggiornamento, quindi è importante mantenere un equilibrio adeguato.

Comprendendo come aggiungere, verificare, rimuovere e valutare gli indici, è possibile ottimizzare il proprio database più facilmente e migliorare l’efficienza complessiva del sistema.