Capire gli Indici in MySQL: B-Tree, Full-text e Spatial

Come abbiamo visto nella nostra guida all'ottimizzazione delle query, gli indici sono fondamentali per le performance di un database. MySQL supporta diversi tipi di indici, ognuno con una struttura dati e un caso d'uso specifico.

Indice B-Tree

È il tipo di indice di default e il più comune. Quasi tutti gli storage engine (incluso InnoDB) lo usano. La struttura dati B-Tree è perfetta per le query che usano gli operatori di confronto: `=`, `>`, `<`, `>=`, `<=`, `BETWEEN`, e per le ricerche su prefissi di stringhe (`LIKE 'prefisso%'`).

Può essere creato su una singola colonna o su più colonne (indice composto). È l'"operaio" versatile che userai nel 99% dei casi.

Indice Full-Text

Un indice B-Tree non è efficace per cercare parole all'interno di un grande blocco di testo (es. il contenuto di un articolo di blog). Per questo esiste l'indice `FULLTEXT`. Questo tipo di indice scompone il testo in singole parole (token) e crea un indice su di esse. Permette di eseguire query di ricerca complesse in linguaggio naturale.

SELECT * FROM articles WHERE MATCH(title, content) AGAINST ('database performance' IN NATURAL LANGUAGE MODE);

È supportato sia da MyISAM che, dalle versioni più recenti, anche da InnoDB.

Indice Spatial (Spaziale)

Progettato per indicizzare dati geospaziali, come coordinate geografiche (latitudine e longitudine). Invece di una struttura B-Tree, usa una struttura dati R-Tree, che è ottimizzata per interrogare dati in uno spazio bidimensionale.

Permette di eseguire in modo efficiente query come "trova tutti i punti di interesse entro 10 chilometri da un punto specifico". È la risposta di MySQL a estensioni come PostGIS per PostgreSQL, anche se meno potente.