Gli indici di una tabella creata in un database gestito con MySQL sono elementi, definibili per uno o più campi, attraverso i quali rendere più rapide le operazioni di ricerca ed estrazione dei records memorizzati, migliorando di conseguenza le prestazioni del DBMS in fase di restituzione dei risultati.
All'interno di una base di dati possono essere disponibili anche migliaia di record e, un DBMS, può trovarsi a dover gestire grandi quantità di query simultanee; scorrere interamente il contenuto di una o più tabelle, per fornire delle risposte alle interrogazioni, può ripercuotersi negativamente sul livello generale delle prestazioni. Per questo motivo esistono gli indici.
Vi sono diverse tipologie di indici che possono essere utilizzati in modo differente per ottenere risultati in modo più veloce e sfruttando la minore quantità possibile di risorse messe a disposizione da un sistema; nelle prossime pagine verranno proposti alcuni esempi per la valutazione delle prestazioni ottenibili tramite gli indici.
Tipologie e definizione degli indici
Gli indici definiti all'interno delle tabelle vengono salvati in un file in cui il Database manager memorizza i cosiddetti "puntatori", cioè gli indirizzi relativi ai dati archiviati sulla tabella stessa; per MySQL sono previste quattro tipologie di indici:
- indici che non permettono la presenza di valori duplicati ("ridondanza") e di valori nulli, chiamati chiavi primarie
- indici che permettono la presenza di valori duplicati, chiamati per questo motivo anche indici non unici
- indici che non accettano la presenza di valori ridondanti, noti anche come indici unici
- indici fulltext
La definizione di un indice non unico in sede di creazione di una tabella si basa sull'utilizzo dell'attributo INDEX seguito opzionalmente da un nome per l'indice e, obbligatoriamente, dal nome del campo da indicizzare:
CREATE TABLE nominativi (
id_nominativi INT(4);
nome VARCHAR(10),
cognome VARCHAR(20),
cap VARCHAR (5),
anni INT(3),
PRIMARY KEY(id_nominativi),
INDEX ind_tbl (cognome)
);L'indice associato al campo cognome, permetterà di effettuare interrogazioni a carico della tabella nominativi utilizzando come termine di confronto non soltanto la chiave primaria id_nominativi, che non consente la registrazione di valori duplicati, ma anche esso che invece permette la ridondanza dei dati.
La definizione di un indice non unico è inoltre possibile utilizzando la parola chiave KEY in luogo di INDEX, per cui nell'istruzione precedente si sarebbe potuto scrivere in alternativa: KEY ind_tbl (cognome).
Per poter definire invece degli indici unici è necessario utilizzare l'attributo UNIQUE; si analizzi il seguente esempio:
CREATE TABLE magazzino (
codice INT(6) NOT NULL UNIQUE,
prodotto VARCHAR(20),
marca VARCHAR(20)
);Si noti come per questa tabella non sia stata definita alcuna chiave primaria, in questo caso sarà possibile ottimizzare la struttura della tabella e fare un po' di economia sulla quantità di dati memorizzati, infatti l'indice "codice" presenta un comportamento del tutto simile a quello di una PRIMARY KEY, esso infatti è associato ad un campo NOT NULL che non accetta valori nulli e, essendo UNIQUE, non permette la ridondanza dei dati.
Migliorare l'utilizzo degli indici
Si immagini di eseguire un'interrogazione sulla tabella nominativi proposta nel paragrafo precedente, ma di aver definito questa volta come indici i campi denominati anni (l'età della persona registrata) e cap (il suo codice di avviamento postale):
La query esposta esegue l'estrazione dei cognomi
IN.
A questo punto la discussione verte sull'analisi del comportamento di MySQL rispetto agli indici: EXPLAIN
Da essi si nota subito come nella query siano stati coinvolti in lettura migliaia di record
rows
extra
USING WHERE
WHERE
extra
USING INDEX
Ora si immagini di utilizzare invece dell'argomento BETWEEN
WHERE
mysql> EXPLAIN SELECT cognome FROM nominativi WHERE anni = 25 AND cap IN ('09042', '09043', '09044');
+--------------+-------+-----------------+-------+---------+--------------------- +
| TABLE | type | possible_keys | KEY | rows | Extra |
+--------------+-------+-----------------+-------+---------+----------------------+
| nominativi | range | anni | anni | 5 | USING WHERE |
+--------------+-------+-----------------+-------+---------+----------------------+
Anche in questo caso il fattore che influenza la velocità di esecuzione è la clausola WHERE
coinvolti dalla query
Un confronto sulle prestazione potrebbe essere molto utile per chiarire la differenza tra le due interrogazioni esposte in quanto a rapidità di esecuzione:
mysql> SELECT SQL_NO_CACHE cognome FROM nominativi WHERE anni BETWEEN 25 AND 28 AND cap IN ('09042', '09043', '09044');
mysql> SELECT SQL_NO_CACHE cognome FROM nominativi WHERE anni = 25 AND cap IN ('09042', '09043', '09044');SQL_NO_CACHE
Le esecuzioni delle due query basate su SELECT SQL_NO_CACHE
Ora si analizzi un fattore particolarmente importante: eseguire per quattro volte la query anni range
Ma come ottenere lo stesso risultato della query basata su WHERE .. BETWEEN
UNION
mysql> SELECT cognome FROM nominativi WHERE anni = 25 AND cap IN ('09042', '09043', '09044')
-> UNION ALL
-> SELECT cognome FROM nominativi WHERE anni = 26 AND cap IN ('09042', '09043', '09044')
-> UNION ALL
-> SELECT cognome FROM nominativi WHERE anni = 27 AND cap IN ('09042', '09043', '09044')
-> UNION ALL
-> SELECT cognome FROM nominativi WHERE anni = 28 AND cap IN ('09042', '09043', '09044');
La query proposta è sintatticamente inelegante e richiede una lunga digitazione rispetto all'istruzione basata su WHERE .. BETWEEN
non necessariamente porta ad esecuzioni più rapide
Per approfondimenti è possibile consultare l'articolo Using UNION to implement loose index scan in MySQL
Conclusioni
Gli indici sono degli strumenti per la velocizzazione delle interrogazioni in MySQL, essi permettono di non scorrere tutti i record di una tabella per la produzione di determinati risultati; il DBMS però si comporta in modo diverso in relazione agli indici a seconda del loro utilizzo e delle istruzioni che deve eseguire, in questa breve trattazione sono stati affrontati alcuni aspetti relativi alle prestazioni fornite dagli indici nei casi in cui essi vengano utilizzati per query che possono coinvolgere anche un gran numero di dati.
