Nelle precedenti lezioni abbiamo visto come creare una tabella all’interno di un database MySQL e come definire correttamente i campi in base al tipo di dato atteso. Abbiamo anche detto quanto sia importante scegliere il "giusto tipo" per le colonne delle nostre tabelle, in quanto una scelta scrupolosa e attenta può incidere (positivamente) sulle performances del nostro database.
In questa lezione ci soffermeremo ulteriormente su quest’ultimo concetto sottolineando come un’accurata fase di progettazione delle tabelle possa portare a vantaggi davvero consistenti in termini di velocità di accesso alle informazioni. Più precisamente parleremo degli indici MySQL ritornando sui modificatori PRIMARY KEY e INDEX appena accennati in una delle precedenti lezioni.
Cosa sono gli indici? a cosa servono?
Gli indici sono delle strutture accessorie alle tabelle che consentono di accedere molto più rapidamente alle informazioni in esse archiviate.
L’importanza degli indici ed il loro impatto sulle performances è maggiore all’aumentare delle dimensioni della tabella: se in tabelle di pochi record l’aggiunta di indici può considerarsi "facoltativa", in tabelle con milioni di record la previsione di uno o più indici diventa assolutamente indispensabile in quanto, in mancanza, l’engine sarebbe chiamato ad un enorme sforzo ad ogni operazione di selezione dati.
Questo perché, in mancanza di indici, MySQL sarebbe costretto a perlustrare ogni record della tabella prima di poter rispondere, ad esempio, ad una richiesta di selezione (SELECT). Viceversa, potendo disporre di uno o più indici l’operazione sarebbe molto più veloce e meno onerosa (in termini di risorse di sistema) in quanto MySQL saprebbe già "dove andare a pescare" i record interessati dalla selezione.
Facciamo un esempio pratico: supponiamo di voler estrarre dalla tabella "amici" tutti i record corrispondenti a persone con cognome "Rossi". Immaginiamo, altresì, che questa tabella sia popolata da un milione di record.
Il comando SQL per effettuare questa operazione di selezione è il seguente:
SELECT * FROM amici WHERE cognome = 'Rossi';Ricevuta questa interrogazione, in assenza di un indice ad hoc, il nostro DBMS dovrà procedere alla scansione di tutti i record della tabella (dal numero 1 fino al numero 1.000.000) in cerca di contatti aventi "Rossi" quale valore della colonna "cognome". Il risultato di una simile operazione si tradurrebbe in diversi secondi di attesa (la velocità di esecuzione dipende dalle caratteristiche hardware del sistema) e/o blocchi di esecuzione qualora le risorse fossero insufficienti.
In presenza di un indice sulla colonna "cognome", viceversa, il DBMS non sarebbe costretto a rastrellare l’intera tabella potendo accedere all’indice ed ottenendo da quest’ultimo l’accesso diretto ai soli campi corrispondenti alla ricerca effettuata. Avere un indice, in parole povere, è come accedere al sommario di un libro: non dovremo leggerlo tutto per cercare un capitolo di nostro interesse ma potremmo spostarci subito alla giusta pagina!
Diversi tipi di indici
Gli indici all’interno di una tabella MySQL possono essere di diversi tipi:
- primary key (o chiavi primarie): questo tipo di indice consente di identificare univocamente e direttamente ogni singolo record all’interno di una tabella; solitamente si utilizza un campo numerico intero con AUTO_INCREMENT per creare la chiave primaria: così facendo ogni record ha un suo "numero" attraverso il quale è possibile accederlo univocamente.
- unique: è simile al precedente nel senso che non vi possono essere valori duplicati all’interno della colonna, tuttavia ammette anche valori NULL.
- index o key: sono degli indici non univoci, questo vuol dire che ammettono anche valori duplicati; il loro scopo esclusivo è di aumentare la velocità di accesso ai record mediante selezioni che non riguardano la chiave primaria.
- fulltext: questo tipo di indice consente di effettuare, rapidamente, ricerche FULL TEXT all’interno di grandi volumi di dati testuali mediante le clausole MATCH() / AGAINST(). In sostanza, questo tipo di indice può essere utilizzato per crearea una sorta di "Google" interno grazie al quale effettuare ricerche naturali tra i contenuti archiviati nel DB. Questo tipo di indice può riguardare esclusivamente campi di tipo CHAR, VARCHAR e TEXT.
Pro e contro nell’utilizzo degli indici
L’utilizzo degli indici all’interno di una tabella MySQL, come detto, è una prassi consigliata in quanto consente di ottimizzare le prestazioni del sistema riducendo (anche notevolmente) i tempi di accesso e le risorse necessarie per la selezione di specifici record.
Tuttavia è giusto segnalare al lettore che questo (grande) vantaggio produce anche degli effetti collaterali:
- Aumenta lo spazio fisico occupato dal database;
- La presenza di indici comporta un rallentamento in fase di scrittura (inserimento e aggiornamento dati).
Per questo motivo, seppur l’utilizzo di indici sia caldamente consigliato, è necessario prestare attenzione nella loro definizione e non esagerare: creare troppi indici sulla medesima tabella, infatti, potrebbe poi portare ad una riduzione delle performances complessive invece che ad un miglioramento.
Indici a più colonne
Abbiamo detto che un indice altro non è che una specie di "sommario" dei contenuti di una tabella. Tale "sommario" può riguardare singole colonne oppure gruppi di colonne: si parla in questo caso di indici composti o su più colonne.
Solitamente si crea un indice su più colonne quando queste sono spesso utilizzate simultaneamente per la selezione di specifici record della tabella. Ovviamente non avrebbe alcun senso creare indici su più colonne che coinvolgano campi di tipo chiave primaria o unique in quanto, in questo caso, l’accesso è diretto e non necessita combinazioni di dati.
Facciamo un esempio: tornando alla nostra tabella "amici" potrebbe avere un senso creare un indice composto sulle due colonne "nome" e "cognome" qualora si utilizzasse spesso una query di questo tipo:
SELECT * FROM amici WHERE nome = '...' AND cognome = '...';In questo caso quindi, il nostro DBMS potrebbe accedere ad una sorta di "elenco telefonico" dove reperire velocemente tutti gli amici corrispondenti ai due fattori di selezione.
Nel creare indici composti è importante prestare attenzione all’ordine dei campi presenti nell’indice: un indice composto, infatti, può essere utilizzato anche per selezioni che riguardino solo alcuni dei campi presenti al suo interno a condizione però che si rispetti l’ordine coi quali tali campi sono stati elencati in fase di creazione dell’indice.
Facciamo un esempio: supponiamo di aver creato l’indice composto visto in precedenza includendo i campi nel seguente ordine: nome, cognome.
Ora, questo indice composto potrebbe essere utilizzato correttamente anche per effettuare una selezione di questo tipo:
SELECT * FROM amici WHERE nome = '...';ma NON di questo:
SELECT * FROM amici WHERE cognome = '...';Questo perché, quest’ultima query effettua una ricerca all’interno di un campo che, pur essendo presente in un indice non ne occupa la prima posizione e pertanto è stato utilizzato, in un certo senso, come filtro secondario. In tal caso, quindi, si rende necessario creare un secondo indice solo sul campo "cognome" per effettuare ricerche veloci solo su questo campo.
Creare un indice all’interno di una tabella MySQL
Un indice può essere creato sia in fase di creazione della tabella che successivamente mediante una modifica a quest’ultima. Tornando alla fase di creazione, vediamo come implementare gli indici all’interno di un’istruzione basata sul comando CREATE TABLE.
Definire una chiave primaria
Per farlo torniamo alla nostra famosa tabella "amici" alla quale andremo ad aggiungere una chiave primaria:
CREATE TABLE amici (
<strong>id INT(20) AUTO_INCREMENT</strong>,
nome VARCHAR(20),
cognome VARCHAR(20),
telefono INT(10),
<strong>PRIMARY KEY(id)</strong>
);Così facendo abbiamo aggiunto alla nostra tabella un nuovo campo ("id") di tipo numerico intero, autoincrementale e con ruolo di chiave primaria.
Definire un indice di tipo UNIQUE
La sintassi per aggiungere un indice di tipo UNIQUE è la medesima vista per PRIMARY KEY.
Vediamo come aggiungere, sempre alla nostra tabella "amici", anche un indice univoco su un campo che chiameremo "soprannome":
CREATE TABLE amici (
id INT(20) AUTO_INCREMENT,
nome VARCHAR(20),
cognome VARCHAR(20),
<strong>soprannome VARCHAR(20)</strong>
telefono INT(10),
PRIMARY KEY(id),
<strong>UNIQUE KEY(soprannome)</strong>
);Definire un INDEX
Ancora una volta la sintassi è la medesima. Supponiamo di voler indicizzare il campo "cognome" già in fase di creazione della tabella:
CREATE TABLE amici (
id INT(20) AUTO_INCREMENT,
nome VARCHAR(20),
cognome VARCHAR(20),
telefono INT(10),
PRIMARY KEY(id),
<strong>INDEX(cognome)</strong>
);E’ anche possibile assegnare un nome all’indice che stiamo creando, così:
CREATE TABLE amici (
id INT(20) AUTO_INCREMENT,
nome VARCHAR(20),
cognome VARCHAR(20),
telefono INT(10),
PRIMARY KEY(id),
<strong>INDEX indice_cognomi (cognome)</strong>
);Prevedere un nome per gli indici può rivelarsi utile in fase di eliminazione degli stessi (come vedremo in seguito).
Definire un indice composto
Tornando all’esempio di prima supponiamo di voler creare un indice sui due campi "nome" e "cognome":
CREATE TABLE amici (
id INT(20) AUTO_INCREMENT,
nome VARCHAR(20),
cognome VARCHAR(20),
telefono INT(10),
PRIMARY KEY(id),
<strong>INDEX(nome,cognome)</strong>
);Come potete vedere la sintassi prevede che all’interno delle parentesi tonde che seguono INDEX venga inserito l’elenco dei campi separati da una virgola.
Anche in questo caso avremmo potuto prevedere un nome per l’indice in questo modo:
INDEX nome_e_cognome (nome,cognome)Definire indici parziali
Un indice si dice parziale quando non riguarda l’intero campo ma solo i primi X caratteri in esso contenuti. Può essere utile quando, ad esempio, i primi caratteri di un dato campo sono un prefisso significativo. Per fare un esempio si supponga di voler indicizzare i primi tre caratteri del campo "cellulare" al fine di effettuare selezioni in base al prefisso:
CREATE TABLE amici (
id INT(20) AUTO_INCREMENT,
nome VARCHAR(20),
cognome VARCHAR(20),
telefono INT(10),
<strong>cellulare INT(10)</strong>
PRIMARY KEY(id),
<strong>INDEX(cellulare(3))</strong>
);Come potete vedere nelle parentesi tonde che seguono INDEX è stato inserito il nome del campo da indicizzare accompagnato dal numero di caratteri da prendere in considerazione.
Definire un indice Fulltext
Come detto questa tipologia di indice può essere utilizzato solo su tabelle di tipo MyISAM e può riguardare solo campi di tipo CHAR, VARCHAR e TEXT.
Vediamo come creare un indice fulltext su un ipotetico campo "biografia" nel quale andremo ad inserire un testo biografico per ogni amico presente in tabella:
CREATE TABLE amici (
id INT(20) AUTO_INCREMENT,
nome VARCHAR(20),
cognome VARCHAR(20),
telefono INT(10),
<strong>biografia TEXT</strong>
PRIMARY KEY(id),
<strong>FULLTEXT KEY(biografia)</strong>
);Per maggiori informazioni sulle ricerche FULLTEXT in MySQL vi invito alla lettura di questo articolo.
Aggiungere un indice ad una tabella esistente
Ovviamente è possibile aggiungere un’indice anche ad una tabella esistente. Per farlo si ricorre al comando ALTER TABLE (che verrà trattato in una prossima lezione della nostra guida) oppure al comando CREATE INDEX. Vediamo come funziona quest’ultimo.
Supponiamo di aver creato la tabella "amici" utilizzando questa query:
CREATE TABLE amici (
nome VARCHAR(20),
cognome VARCHAR(20),
telefono INT(10),
);e di voler aggiungere un indice sul campo "cognome" in quanto non previsto in fase di creazione:
CREATE INDEX indice_cognomi ON amici(cognome);Con questa sintassi abbiamo creato un indice "indice_cognomi" sulla tabella "amici" per il campo "cognome".
Eliminare un indice
Una volta creato, ovviamente, un indice può anche essere rimosso. Per farlo si ricorre al comando DROP INDEX mediante questa sintassi:
DROP INDEX nome_indice ON nome_tabellaPer eliminare una chiave primaria si utilizza sempre questa sintassi:
DROP INDEX 'PRIMARY' ON nome_tabella