Come controllare e ottimizzare gli indici MySQL: Guida completa con SHOW INDEX, EXPLAIN e ottimizzazione delle prestazioni

1. Introduzione

MySQL è un database relazionale ampiamente utilizzato in molte applicazioni web e sistemi di gestione dei dati. Per migliorare la velocità di recupero dei dati, esiste un meccanismo chiamato “indice”. Tuttavia, se non gestiti correttamente, gli indici possono effettivamente ridurre le prestazioni.

Perché è importante controllare gli indici?

Un indice in un database è simile alla sezione indice di un libro. Indici progettati correttamente migliorano la velocità di esecuzione delle query di ricerca. Tuttavia, possono verificarsi i seguenti problemi:

• Gli indici non sono creati correttamente → Possono causare ricerche lente
• Esistono indici non necessari → Rallentano gli aggiornamenti e le inserzioni
• Incertezza su quali indici vengano utilizzati → Rende difficile decidere se rimuovere gli indici inutilizzati

Cosa imparerai in questo articolo

• Il meccanismo di base degli indici MySQL
• Come controllare gli indici attuali (usando comandi SQL)
• Come gestire e ottimizzare gli indici
• Tecniche per analizzare l’utilizzo degli indici

Ora, impariamo sistematicamente gli indici MySQL e utilizziamo queste conoscenze per migliorare le prestazioni del database.

2. Che cos’è un indice MySQL?

Un indice è una funzionalità essenziale per migliorare le prestazioni del database. In questa sezione spieghiamo il concetto di base, i tipi, i vantaggi e gli svantaggi degli indici.

Concetto di base degli indici

Un indice di database è un meccanismo che consente di cercare rapidamente i valori in colonne specifiche. Per esempio, quando si cerca un record specifico in una tabella contenente una grande quantità di dati, senza un indice il database deve scansionare tutti i record (full table scan). L’applicazione di un indice rende il recupero dei dati efficiente e migliora significativamente la velocità di elaborazione.

Tipi di indici MySQL

MySQL supporta diversi tipi di indici, ciascuno adatto a casi d’uso specifici.

1. PRIMARY KEY (Indice chiave primaria)

• Può essere impostato una sola volta per tabella
• Garantisce l’unicità della tabella
• Funziona come indice clusterizzato

1. UNIQUE Index

• Assicura che i valori nella colonna specificata non siano duplicati
• I valori NULL sono consentiti (sono permessi più NULL)

1. INDEX (Indice generico)

• Utilizzato per velocizzare le ricerche
• Sono consentiti dati duplicati

1. FULLTEXT Index (per ricerca testuale)

• Ottimizza la ricerca di testo
• Utilizzato in combinazione con la sintassi MATCH ... AGAINST

1. SPATIAL Index (per dati geografici)

• Progettato per dati spaziali (GIS)

Vantaggi e svantaggi degli indici

Vantaggi

• Migliora la velocità di ricerca delle query
• Potenzia le prestazioni delle operazioni JOIN e delle clausole WHERE
• Rende più efficiente il recupero di dati specifici

Svantaggi

• Un numero elevato di indici rallenta le operazioni INSERT, UPDATE e DELETE
• Consuma spazio su disco
• Indici progettati in modo inadeguato possono degradare le prestazioni

3. Come controllare gli indici MySQL

Per gestire correttamente gli indici MySQL, è importante verificare quali indici sono attualmente definiti su una tabella. In questa sezione spieghiamo come controllare gli indici usando il comando SHOW INDEX, INFORMATION_SCHEMA.STATISTICS e il comando mysqlshow.

Comando SHOW INDEX (metodo di base)

In MySQL, è possibile utilizzare il comando SHOW INDEX per ottenere un elenco degli indici definiti su una tabella specifica. Questo comando consente di verificare dettagli come il nome dell’indice, le colonne coperte dall’indice e l’esistenza di un vincolo di unicità.

Sintassi di base

SHOW INDEX FROM table_name;

Esempio

Ad esempio, per controllare gli indici definiti sulla tabella users, esegui il seguente SQL:

SHOW INDEX FROM users;

Output di esempio

Utilizzo di INFORMATION_SCHEMA.STATISTICS (ottenere informazioni dettagliate)

Utilizzando la tabella di sistema di MySQL INFORMATION_SCHEMA.STATISTICS, è possibile recuperare le stesse informazioni di SHOW INDEX con maggiore flessibilità.

Controllare gli indici per una tabella specifica

SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

Ottieni gli indici su tutto il database

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

Comando mysqlshow (Controlla da CLI)

Puoi anche recuperare le informazioni sugli indici usando gli strumenti da riga di comando di MySQL. Questo è particolarmente utile quando si lavora via SSH su un server MySQL.

Come eseguire il comando

mysqlshow -u username -p password database_name table_name

Esempio

mysqlshow -u root -p my_database users

Cosa fare se non esistono indici

Se non vengono visualizzati indici dopo aver eseguito SHOW INDEX o interrogato INFORMATION_SCHEMA.STATISTICS, la tabella potrebbe non avere indici appropriati. In tal caso, è possibile migliorare le prestazioni di ricerca creando indici secondo necessità.

Crea un nuovo indice

CREATE INDEX idx_column ON users (email);

Imposta una chiave primaria (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Elimina gli indici non necessari

ALTER TABLE users DROP INDEX idx_column;

4. Come verificare l’uso degli indici

Verificare se gli indici funzionano correttamente è un passaggio critico nell’ottimizzazione delle prestazioni di MySQL. In questa sezione, spieghiamo come determinare quali indici utilizza una query sfruttando il comando EXPLAIN e Performance Schema.

Analisi della query con EXPLAIN

Il comando EXPLAIN è usato per visualizzare come verrà eseguita una determinata query SQL. Ti aiuta ad analizzare gli indici utilizzati, i metodi di accesso e il piano di esecuzione, il che lo rende utile per verificare se gli indici funzionano come previsto.

Sintassi di base

EXPLAIN SELECT * FROM table_name WHERE condition;

Esempio

Ad esempio, per cercare nella tabella users usando la colonna email come condizione:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Output di esempio

Punti chiave

• Se type = ALL, la query esegue una scansione completa della tabella, quindi probabilmente è necessario un indice.
• Se un nome di indice appare in key, quell’indice viene utilizzato.
• Se il valore di rows è troppo grande, potrebbe essere necessaria l’ottimizzazione della query.

Utilizzo di Performance Schema

Con il performance_schema di MySQL, puoi analizzare in dettaglio quali indici vengono utilizzati e con quale frequenza durante l’esecuzione della query.

Ottieni le statistiche di esecuzione della query

SELECT * FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%SELECT%';

Verifica l’uso degli indici per una tabella specifica

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database_name' AND OBJECT_NAME = 'users';

Cosa fare se un indice non viene utilizzato

1. Revisiona la query

Se un indice non viene utilizzato, la struttura della query potrebbe essere il problema. Ad esempio, il seguente modello può impedire l’uso dell’indice.

❌ Esempio errato (La funzione disabilita l’uso dell’indice)

SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

→ A causa di LOWER(email), l’indice su email potrebbe essere ignorato.

✅ Esempio corretto (Evita di usare una funzione)

SELECT * FROM users WHERE email = 'test@example.com';

2. Ricrea l’indice

Se un indice esistente non funziona correttamente, eliminarlo e ricrearlo può talvolta migliorare le prestazioni.

ALTER TABLE users DROP INDEX idx_email;
CREATE INDEX idx_email ON users(email);

3. Aggiorna le statistiche

Se le statistiche della tabella diventano obsolete, MySQL potrebbe non utilizzare gli indici in modo ottimale. Puoi aggiornare le statistiche con il seguente comando:

ANALYZE TABLE users;

5. Gestione degli indici

MySQL indexes are essential for improving data retrieval performance. However, if not properly managed, they can degrade overall database performance. In this section, we explain in detail how to create, delete, and identify unnecessary indexes.

Creazione di Indici

Creando indici appropriati, è possibile velocizzare significativamente le ricerche di dati. In MySQL, è possibile aggiungere indici usando CREATE INDEX o ALTER TABLE.

Sintassi di Base

CREATE INDEX index_name ON table_name(column_name);

Esempio

Per aggiungere un indice alla colonna email nella tabella users:

CREATE INDEX idx_email ON users(email);

Indice Multi-Colonna (Indice Composito)

CREATE INDEX idx_name_email ON users(last_name, first_name, email);

Indice Unico

CREATE UNIQUE INDEX idx_unique_email ON users(email);

Impostazione di una Chiave Primaria (PRIMARY KEY)

ALTER TABLE users ADD PRIMARY KEY (id);

Rimozione di Indici

Rimuovere gli indici non necessari aiuta a ridurre il carico del database.

Sintassi di Base

ALTER TABLE table_name DROP INDEX index_name;

Esempio

Ad esempio, per eliminare un indice chiamato idx_email:

ALTER TABLE users DROP INDEX idx_email;

Identificazione e Rimozione di Indici Non Necessari

Verifica di Indici Inutilizzati

SELECT * FROM sys.schema_unused_indexes;

Verifica dello Stato della Tabella (Impatto degli Indici)

SHOW TABLE STATUS LIKE 'users';

Rimuovi Indici Non Necessari

ALTER TABLE users DROP INDEX idx_unused;

Dopo l’eliminazione, è consigliato aggiornare le statistiche usando ANALYZE TABLE.

ANALYZE TABLE users;

6. Ottimizzazione degli Indici (Miglioramento delle Prestazioni)

Una corretta gestione degli indici può migliorare significativamente le prestazioni delle query MySQL. Tuttavia, creare semplicemente gli indici non è sufficiente — progettazione, gestione e monitoraggio adeguati sono necessari per mantenere prestazioni ottimali.

Progettazione Corretta degli Indici

Gli indici ben progettati possono migliorare drasticamente la velocità di ricerca in MySQL.

Casi in cui gli Indici Dovrebbero essere Applicati

Casi in cui gli Indici Non Dovrebbero essere Applicati

Utilizzo del Log delle Query Lente

Il Log delle Query Lente consente di identificare le query a lunga esecuzione e analizzare quali indici non vengono applicati.

Abilita il Log delle Query Lente

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2; -- Log queries taking longer than 2 seconds

Controlla il Log delle Query Lente

SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

Analizza una Query Lenta

EXPLAIN SELECT * FROM users WHERE last_login > '2024-01-01';

Esempio di Applicazione di un Indice

CREATE INDEX idx_last_login ON users(last_login);

Aggiornamento delle Statistiche (ANALYZE & OPTIMIZE)

ANALYZE TABLE (Aggiorna Statistiche)

ANALYZE TABLE users;

OPTIMIZE TABLE (Deframmentazione)

OPTIMIZE TABLE users;

7. FAQ sugli Indici (Domande Frequenti)

Gli indici MySQL sono un meccanismo essenziale per migliorare le prestazioni del database. Tuttavia, se non gestiti correttamente, possono avere l’effetto opposto. In questa sezione, riassumiamo le domande frequenti (FAQ) e le risposte relative agli indici MySQL.

La velocità di ricerca migliorerà aggiungendo più indici?

A. Non necessariamente.

Gli indici sono progettati per migliorare le prestazioni delle query, ma l’aggiunta di troppi indici può effettivamente degradare le prestazioni del database.

• Aumenta il carico di scrittura (INSERT, UPDATE, DELETE)
• L’uso di un indice dipende dalla query
• Gli indici non necessari consumano memoria

Quali colonne dovrebbero avere indici?

A. È efficace applicare indici ai seguenti tipi di colonne:

Gli indici vengono creati automaticamente?

A. Alcuni indici vengono creati automaticamente, ma altri devono essere aggiunti manualmente.

Indici Creati Automaticamente

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY, -- PRIMARY KEY index
  email VARCHAR(255) UNIQUE -- Index automatically created by UNIQUE constraint
);

Indici Creati Manualmente

CREATE INDEX idx_email ON users(email);

Come scegliere tra indici B-Tree, Hash e FULLTEXT?

Come posso verificare la dimensione degli indici?

SHOW TABLE STATUS LIKE 'users';

Come posso verificare se un indice viene utilizzato?

EXPLAIN SELECT * FROM users WHERE email = 'example@example.com';

Quando dovrei rimuovere un indice?

Rimuovi un Indice Inutile

ALTER TABLE users DROP INDEX idx_unused;

8. Sommario

In questo articolo, abbiamo coperto in modo completo gli indici MySQL—dalle basi ai metodi di verifica, gestione, ottimizzazione e alle domande frequenti. Qui rivediamo i punti chiave di ogni sezione e riassumiamo le migliori pratiche per ottimizzare la gestione degli indici MySQL.

Punti Chiave

Che Cos’è un Indice MySQL?

• Un indice è un meccanismo che velocizza il recupero dei dati.
• Esistono diversi tipi, tra cui indici B-Tree, Hash e FULLTEXT.
• Più efficace quando applicato a colonne usate nelle clausole WHERE, JOIN e ORDER BY.

Come Verificare gli Indici MySQL

• Usa il comando SHOW INDEX per visualizzare l’elenco degli indici.
• Usa INFORMATION_SCHEMA.STATISTICS per ottenere informazioni dettagliate.

Come Verificare l’Uso degli Indici

• Usa EXPLAIN per vedere quali indici utilizza una query.
• Usa Performance Schema per analizzare la frequenza di utilizzo degli indici.

Gestione degli Indici

• Usa CREATE INDEX per applicare gli indici alle colonne appropriate.
• Usa ALTER TABLE DROP INDEX per rimuovere gli indici non necessari.
• Controlla la dimensione degli indici con SHOW TABLE STATUS e ottimizza secondo necessità.

Ottimizzazione degli Indici (Miglioramento delle Prestazioni)

• Applica gli indici alle colonne WHERE, JOIN e ORDER BY usate frequentemente.
• Usa il Slow Query Log per identificare e ottimizzare le query lente.
• Aggiorna le statistiche con ANALYZE TABLE e OPTIMIZE TABLE.

Best Practice per la Gestione degli Indici MySQL

1. Identifica i colli di bottiglia delle query prima di applicare gli indici.
2. Seleziona gli indici appropriati.

• Usa correttamente indici a colonna singola e indici compositi.
• Usa UNIQUE INDEX dove sono richiesti vincoli di unicità.

1. Rimuovi regolarmente gli indici non necessari.

• Identifica gli indici inutilizzati usando SHOW INDEX e schema_unused_indexes.

1. Aggiorna regolarmente le statistiche.

• Aggiorna le statistiche usando ANALYZE TABLE.
• Esegui OPTIMIZE TABLE per risolvere la frammentazione causata da delete e update.

Prossimi Passi

✅ Checklist Pratica
✅ Hai controllato gli indici attuali usando SHOW INDEX?
✅ Hai abilitato il Slow Query Log e identificato le query lente?
✅ Hai analizzato il piano di esecuzione della query usando EXPLAIN?
✅ Hai rimosso gli indici non necessari e creato quelli appropriati?
✅ Hai aggiornato le statistiche usando ANALYZE TABLE?

Riepilogo Finale

• Una corretta gestione degli indici MySQL migliora significativamente le prestazioni di ricerca.
• Usa il Slow Query Log e EXPLAIN per analizzare l’efficacia degli indici e ottimizzare di conseguenza.
• Mantieni le prestazioni del database aggiornando regolarmente le statistiche e ottimizzando le tabelle.

Questa è la guida completa alla gestione degli indici MySQL.

Usa queste conoscenze per costruire sistemi di database più veloci ed efficienti. 💡🚀