Padroneggiare l'Indicizzazione MySQL per Prestazioni di Query più Veloci

L'indicizzazione MySQL è solitamente il primo posto in cui guardo quando un database sembra lento, ma è anche uno dei posti più facili in cui commettere un errore sicuro di sé. Un indice può trasformare una scansione di tabella in una rapida ricerca. Può anche rallentare le scritture, sprecare memoria e darti un falso senso di progresso se il pianificatore di query non lo usa mai.

La domanda pratica non è "Questa colonna dovrebbe avere un indice?" La domanda migliore è: "Quale query sto cercando di rendere più economica e come dimostrerò che l'indice ha aiutato?" Tieni a mente questa domanda mentre leggi. Una buona indicizzazione inizia con query reali, non con un elenco di colonne che sembrano importanti.

Cos'è un Indice MySQL?

Un indice MySQL è una struttura dati che migliora la velocità delle operazioni di recupero dati su una tabella del database. Pensalo come un indice in un libro: invece di leggere l'intero libro per trovare un argomento specifico, puoi cercare l'argomento nell'indice, che ti dirà il numero di pagina esatto. Allo stesso modo, un indice del database consente a MySQL di individuare rapidamente le righe che corrispondono a una condizione di query specifica senza scansionare l'intera tabella.

Quando esegui una query su una tabella, MySQL può utilizzare un indice per trovare le righe pertinenti molto più velocemente di quanto potrebbe esaminando ogni riga. Ciò è particolarmente vantaggioso per le tabelle con un gran numero di righe o per query che coinvolgono filtri (clausole WHERE), join di tabelle (clausole JOIN) o ordinamento (clausole ORDER BY).

Come Funzionano gli Indici

MySQL utilizza comunemente indici B-tree per i normali indici InnoDB. Un B-tree mantiene le chiavi in ordine ordinato, motivo per cui funziona bene per ricerche di uguaglianza, intervalli, scansioni ordinate e molti join. Quando crei un indice su una o più colonne, MySQL costruisce una struttura in cui:

• I nodi foglia contengono i puntatori ai dati effettivi o, nel caso di indici cluster (come la chiave primaria di InnoDB), le righe di dati stesse.
• I nodi interni contengono chiavi che aiutano a navigare nell'albero per trovare il nodo foglia corretto.

Quando una query può utilizzare il lato sinistro di quell'indice, MySQL può navigare verso una parte ristretta dell'albero invece di leggere l'intera tabella. Questo è il vero vantaggio. L'indice non rende veloce ogni query; rende economici determinati modelli di accesso.

Tipi di Indici MySQL

MySQL supporta vari tipi di indici, ciascuno con i propri punti di forza e casi d'uso.

1. PRIMARY KEY

• Un vincolo PRIMARY KEY garantisce che ogni valore nella/e colonna/e sia univoco e non NULL. È implicitamente indicizzato.
• Una tabella può avere una sola PRIMARY KEY.
• Le tabelle InnoDB sono fisicamente ordinate dalla loro chiave primaria (indice cluster).

Esempio:

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);

2. Indice UNIQUE

• Un indice UNIQUE impone che tutti i valori nella/e colonna/e indicizzate siano distinti. Consente valori NULL, ma sono ammessi più NULL (a meno che la colonna non faccia anche parte di una PRIMARY KEY o di un altro vincolo UNIQUE che lo impedisca).
• Utile per garantire l'integrità dei dati dove una colonna deve essere univoca ma non è l'identificatore primario.

Esempio:

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    sku VARCHAR(50) UNIQUE
);

3. INDEX (o KEY)

• Un indice standard, chiamato anche indice non univoco.
• Utilizzato per accelerare il recupero dei dati. Non impone l'univocità.

Esempio:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    INDEX idx_customer_id (customer_id)
);

4. Indice FULLTEXT

• Utilizzato per ricerche full-text su colonne CHAR, VARCHAR e TEXT.
• Consente ricerche per parole chiave all'interno di campi di testo di grandi dimensioni.
• Supportato da InnoDB nelle versioni moderne di MySQL. Le installazioni MySQL più vecchie possono avere limiti diversi, quindi controlla la tua versione esatta prima di progettare in base ad essa.

Esempio:

CREATE TABLE articles (
    article_id INT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT (title, body)
);

5. Indice SPATIAL

• Utilizzato per indicizzare tipi di dati spaziali come punti, linee e poligoni.
• Il comportamento e i requisiti variano in base alla versione di MySQL e al motore di archiviazione. Testa l'esatta query spaziale che intendi eseguire; non dare per scontato che un indice spaziale aiuti ogni predicato di tipo GIS.

6. Indice HASH (Uso Limitato)

• Il motore di archiviazione MEMORY di MySQL supporta gli indici HASH. Sono progettati per ricerche di uguaglianza, non per scansioni di intervallo o ordinamento.
• Non è un tipo di indice generico per la maggior parte degli scenari comuni.

Creazione e Gestione degli Indici

Come Creare un Indice

Puoi creare indici sia durante la creazione di una tabella che modificando una tabella esistente.

1. Durante la Creazione della Tabella:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE,
    INDEX idx_department (department_id),
    INDEX idx_hire_date (hire_date)
);

2. Modifica di una Tabella Esistente:

-- Aggiungi un indice a colonna singola
ALTER TABLE customers
ADD INDEX idx_email (email);

-- Aggiungi un indice univoco
ALTER TABLE users
ADD UNIQUE INDEX uidx_username (username);

-- Aggiungi un indice multi-colonna (composito)
ALTER TABLE orders
ADD INDEX idx_customer_date (customer_id, order_date);

Come Eliminare un Indice

Se un indice non è più necessario o ha un impatto negativo sulle prestazioni (ad esempio, durante le scritture), puoi eliminarlo.

-- Elimina un indice standard
ALTER TABLE customers
DROP INDEX idx_email;

-- Elimina un indice univoco
ALTER TABLE users
DROP INDEX uidx_username;

Indici Multi-Colonna (Compositi)

Gli indici compositi vengono creati su due o più colonne. L'ordine delle colonne in un indice composito è cruciale.

• Un indice composito su (col1, col2) può essere utilizzato per query che filtrano solo su col1 o su entrambe col1 E col2.
• Generalmente non viene utilizzato per query che filtrano solo su col2.

Esempio:

Considera un indice su (customer_id, order_date). Questo indice è più efficace per query come:

SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-10-27';
SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_date;

Potrebbe non essere molto utile per SELECT * FROM orders WHERE order_date = '2023-10-27';.

Migliori Pratiche per l'Indicizzazione MySQL

1. Indicizza le Colonne Utilizzate nelle Clausole WHERE, JOIN e ORDER BY

Questi sono i luoghi più comuni in cui gli indici forniscono significativi vantaggi in termini di prestazioni.

• Clausole WHERE: Le condizioni di filtro sono il caso d'uso principale.
• Condizioni JOIN: L'indicizzazione delle colonne utilizzate nelle clausole ON delle istruzioni JOIN accelera notevolmente i join delle tabelle.
• Clausole ORDER BY e GROUP BY: Gli indici possono aiutare MySQL a evitare operazioni di ordinamento.

2. Usa Saggiamente gli Indici Compositi

• L'ordine è importante: Metti prima le colonne che corrispondono alla forma della query. I filtri di uguaglianza di solito vengono prima dei filtri di intervallo. Le colonne utilizzate per l'ordinamento possono aiutare dopo le colonne di filtro.
• Non mettere ciecamente la colonna più selettiva per prima se le tue query reali non filtrano per essa. Un indice su (status, created_at) può essere eccellente per WHERE status = 'paid' ORDER BY created_at DESC LIMIT 50, anche se status ha una bassa cardinalità, perché corrisponde al modello di accesso.

3. Usa EXPLAIN Prima e Dopo

Non giudicare mai un indice per speranza. Esegui EXPLAIN, aggiungi l'indice in un ambiente di staging o di manutenzione sicuro, quindi esegui di nuovo EXPLAIN.

EXPLAIN
SELECT order_id, total, created_at
FROM orders
WHERE customer_id = 123
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC
LIMIT 20;

Guarda type, key, rows e Extra. Se key è NULL, MySQL non ha scelto un indice. Se rows è ancora vicino alla dimensione della tabella, l'indice potrebbe non essere abbastanza selettivo per questa query. Se Extra dice Using filesort, non è automaticamente negativo, ma ti dice che MySQL non ha potuto restituire le righe nell'ordine richiesto dall'indice scelto.

Per MySQL 8.0.18 e versioni successive, EXPLAIN ANALYZE può essere ancora più utile perché esegue la query e riporta i tempi effettivi e i conteggi delle righe:

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

Usalo con attenzione sui sistemi di produzione perché esegue l'istruzione.

4. Costruisci Indici Intorno ai Flussi di Lavoro, Non alle Tabelle

Immagina una schermata di amministrazione che elenca i pagamenti falliti recenti:

SELECT id, customer_id, failure_code, created_at
FROM payments
WHERE status = 'failed'
ORDER BY created_at DESC
LIMIT 100;

Un indice solo su status potrebbe comunque lasciare MySQL a ordinare un gran numero di righe fallite. Un indice su (status, created_at) è solitamente una corrispondenza migliore perché MySQL può trovare le righe fallite e leggerle in ordine di tempo. Se la query restituisce solo colonne nell'indice, potresti prendere in considerazione un indice di copertura:

CREATE INDEX idx_payments_status_created_cover
ON payments (status, created_at, id, customer_id, failure_code);

Può essere veloce, ma non è gratuito. L'indice è più ampio, occupa più spazio di archiviazione e costa di più su ogni scrittura. Ricorro agli indici di copertura solo per query calde che vengono eseguite abbastanza spesso da giustificare il costo di manutenzione aggiuntivo.

5. Fai Attenzione agli Indici che Sembrano Giusti ma Non Fanno Nulla

Queste sono trappole comuni:

• Una funzione nasconde il valore indicizzato: WHERE DATE(created_at) = '2025-01-01'.
• Un carattere jolly iniziale impedisce l'uso normale del B-tree: WHERE email LIKE '%@example.com'.
• Una mancata corrispondenza di tipo forza la conversione: confrontare una colonna intera con una stringa quotata può ancora funzionare, ma può confondere i piani negli schemi reali.
• L'indice inizia con la colonna sbagliata per la query: (created_at, customer_id) non è la stessa cosa di (customer_id, created_at).

Riscrivi il predicato quando puoi:

WHERE created_at >= '2025-01-01'
  AND created_at <  '2025-01-02'

Quella forma consente a MySQL di utilizzare una scansione di intervallo su created_at.

6. Rimuovi gli Indici Ridondanti e Inutilizzati con Attenzione

L'eccesso di indicizzazione è un problema di prestazioni silenzioso. Ogni indice secondario aggiuntivo deve essere mantenuto durante INSERT, UPDATE e DELETE. Su una tabella con molte scritture, cinque indici inutilizzati possono essere più importanti di una singola SELECT lenta.

In MySQL 5.7 e 8.0, lo schema sys può aiutarti a trovare candidati:

SELECT *
FROM sys.schema_unused_indexes
WHERE object_schema = 'app';

Tratta quell'output come un suggerimento, non un comando. Un indice può sembrare inutilizzato perché il server è stato riavviato di recente, perché un report mensile non è ancora stato eseguito o perché il traffico in staging non corrisponde alla produzione. Prima di eliminare un indice, controlla la cronologia di distribuzione, i lavori pianificati e i requisiti di chiave esterna.

7. Aggiungi Indici in Sicurezza su Tabelle Grandi

Su tabelle piccole, ALTER TABLE ... ADD INDEX è solitamente un evento tranquillo. Su una grande tabella di produzione, può essere un'operazione importante. A seconda della versione di MySQL, del motore di archiviazione, della definizione della tabella e del DDL esatto, l'aggiunta di un indice può utilizzare DDL online o può ancora creare pressione attraverso blocchi di metadati, spazio temporaneo, generazione di redo e ritardo di replica.

Prima di aggiungere un grande indice, controlla:

• Quanto sono grandi la tabella e gli indici esistenti.
• Se le repliche possono tenere il passo.
• Se la tua versione di MySQL supporta l'algoritmo online che ti aspetti.
• Se l'applicazione può tollerare un blocco di metadati se una transazione lunga blocca il DDL.

Per sistemi sensibili, utilizza uno strumento di migrazione come pt-online-schema-change o gh-ost, o pianifica il DDL durante una finestra a basso traffico.

Una Routine Pratica di Revisione degli Indici

Quando rivedo una query MySQL lenta, utilizzo questo ordine:

1. Cattura l'SQL esatto con valori di bind reali.
2. Esegui EXPLAIN e, dove sicuro, EXPLAIN ANALYZE.
3. Controlla se gli indici esistenti corrispondono al modello WHERE, JOIN e ORDER BY.
4. Aggiungi il più piccolo indice composito utile in staging.
5. Confronta le righe esaminate, il tempo di query e l'impatto sulla scrittura.
6. Distribuisci con attenzione e monitora i log delle query lente e il ritardo di replica.

Quella routine mantiene l'indicizzazione onesta. Non stai cercando di collezionare indici. Stai cercando di ridurre la quantità di lavoro che MySQL deve fare per le query che la tua applicazione esegue effettivamente.