Un indice è una struttura dati separata e ordinata (un B-tree in InnoDB) che permette a MySQL di trovare le righe senza scandire l'intera tabella. Senza di esso, una ricerca legge ogni riga (un full table scan); con esso, percorre un albero in pochi passi.
In InnoDB la tabella stessa è l'indice della primary key — un clustered index. Le righe sono memorizzate fisicamente in ordine di primary key, quindi la ricerca per primary key arriva direttamente sulla riga completa.
Un indice secondario è un B-tree separato con chiave su altre colonne. I suoi nodi foglia memorizzano il valore indicizzato più la primary key, quindi usare un indice secondario spesso costa una seconda ricerca ("ritorno al clustered index") per recuperare il resto della riga.
CREATE TABLE users (
id INT PRIMARY KEY, -- clustered: righe memorizzate in ordine di id
email VARCHAR(255),
city VARCHAR(80)
);
CREATE INDEX idx_city ON users (city); -- B-tree secondario su city
-- Veloce: percorre idx_city, poi la primary key per il resto di ogni riga
SELECT * FROM users WHERE city = 'Hanoi';
-- Lento: nessun indice su email -> full table scan
SELECT * FROM users WHERE email = '[email protected]';
Gli indici velocizzano le letture, ma ogni INSERT/UPDATE/DELETE deve anche aggiornarli, e occupano spazio su disco. Indicizza le colonne su cui filtri, fai join o ordini davvero — non ogni colonna.
Gli indici sono la leva più grande sulla velocità delle query: la differenza tra leggere 3 nodi di un albero e scandire un milione di righe. Sapere che InnoDB fa clustering sulla primary key spiega anche perché una chiave corta e monotòna (come AUTO_INCREMENT) mantiene snelli gli insert e ogni indice secondario.
Una raccolta di domande di colloquio IT con risposte dettagliate — da Junior a Senior.
Dona