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 che vengono utilizzati per rendere più rapide le operazioni di ricerca delle stringhe nei testi.
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):
mysql> EXPLAIN SELECT cognome FROM nominativi WHERE anni BETWEEN 25 AND 28 AND cap IN ('09042', '09043', '09044');
+------------+-------+-----------------+-------+---------+--------------------- +
| TABLE | type | possible_keys | KEY | rows | Extra |
+------------+-------+-----------------+-------+---------+----------------------+
| nominativi | range | anni | anni | 4223 | USING WHERE |
+------------+-------+-----------------+-------+---------+----------------------+
La query esposta esegue l'estrazione dei cognomi presenti nella tabella selezionandoli tra quelli corrispondenti a persone di età compresa tra i 25 e i 28 anni, residenti in località aventi come CAP i valori passati come parametri alla clausola IN.
A questo punto la discussione verte sull'analisi del comportamento di MySQL rispetto agli indici: EXPLAIN permette di mostrare le informazioni relative ai criteri utilizzati dal Database manager per l'esecuzione dell'interrogazione, nell'esempio appena proposto non sono stati esposti tutti i risultati prodotti dall'istruzione, ma soltanto quelli interessanti per l'analisi delle prestazioni.
Da essi si nota subito come nella query siano stati coinvolti in lettura migliaia di record (si veda a questo proposito il valore corrispondente alla colonna rows), la colonna extra, che permette di visualizzare i fattori che hanno influito sui tempi di esecuzione della query, mostra invece il risultato USING WHERE; ciò vuol dire che in realtà il DBMS non ha ottimizzato l'utilizzo degli indici limitandosi a cercare le corrispondenze dei CAP in tutti i record scansionati, il fattore che ha influito più pesantemente sulle prestazioni della query è stato la clausola WHERE e non gli indici, lo dimostra il fatto che la colonna extra non mostra il valore USING INDEX.
Ora si immagini di utilizzare invece dell'argomento BETWEEN per la clausola WHERE il simbolo di uguaglianza vincolando l'interrogazione ad un singolo valore del campo "anni", il risultato potrebbe essere simile al seguente:
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, ma si nota subito come il numero di record coinvolti dalla query sia stato questa volta nettamente inferiore.
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 permette di eseguire una query senza avvalersi del meccanismo di caching messo a disposizione dal DBMS per la velocizzazione delle istruzioni già eseguiti, in questo modo MySQL restituirà in output il tempo realmente necessario per la produzione di una risposta in seguito alle richieste provenienti dal client.
Le esecuzioni delle due query basate su SELECT SQL_NO_CACHE restituiranno tempi di molto differenti: naturalmente la prima, coinvolgendo un numero di record nettamente superiore, necessiterà di molto più tempo per essere eseguita.
Ora si analizzi un fattore particolarmente importante: eseguire per quattro volte la query basata sul confronto con un solo valore del campo anni, richiederebbe in ogni caso un tempo complessivo estremamente inferiore rispetto a quello necessario per effettuare un confronto basato su di un intervallo (range) di valori; ciò è dovuto ad un semplice motivo: i record coinvolti sarebbero in numero nettamente inferiore.
Ma come ottenere lo stesso risultato della query basata su WHERE .. BETWEEN evitando di coinvolgere il medesimo numero di record? E soprattutto, come ottenere un risultato equivalente registrando un miglior livello di prestazioni? Una soluzione potrebbe essere quella di utilizzare un'istruzione basata sull'utilizzo di UNION, non per creare relazioni tra più tabelle ma per simulare un confronto basato su un intervallo di valori:
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, il suo scopo è però quello di dimostrare che la digitazione di istruzioni più brevi non necessariamente porta ad esecuzioni più rapide delle interrogazioni. In ogni caso, un'istruzione del genere, ha ragione di essere utilizzata soltanto in mancanza di altre alternative per ottenere prestazioni migliori.
Per approfondimenti è possibile consultare l'articolo Using UNION to implement loose index scan in MySQL, fonte di ispirazione per questa trattazione.
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.
