Introduzione agli indici

L’articolo descrive un introduzione agli indici proponendo esempi su come creare e come rimuovere indici su tabelle MySQL

Gli indici di una tabella sono degli oggetti definiti su uno o più campi, che consentono di velocizzare la ricerca e l’ordinamento dei dati della data tabella, secondo il campo o i campi per cui è definito. In un database reale, infatti, il numero di record di una tabellà può diventare veramente molto grande, anche milioni.

Ciò rende sempre più lento il reperimento di dati ed informazioni, in quanto il DBMS deve gestire un quantitativo di “BIT” veramente enorme. In altre parole deve scorrere tutta la tabella, finché non trova il dato di interesse. Per risolvere questa problematica, e quindi velocizzare la ricerca dei dati su una tabella, è possibile ricorrere agli indici. Gli indici, come le tabelle, sono memorizzati in un file contenenti puntatori, ovvero gli indirizzi, dei dati sulla tabella. p<>. Quando sono utilizzati gli indici, la ricerca di un dato risulta essere molto più veloce, in quanto il DBMS si occupa, prima di reperire l’indice del dato che si sta cercando, e successivamente, accede al file contenente i dati in tabella, già sapendo in quale posizione andare a reperire le informazioni. E’ evidente che, in presenza di tabelle molto grandi, gli indici riducono drasticamente i tempi di ricerca, migliorando le prestazioni della nostra base dati.

MySQL supporta quattro tipi di indici:

1. Indici non unici, che consentono la presenza di dati duplicati
2. Indici unici, che non consentono la presenza di dati duplicati
3. Chiavi primarie, che non consentono valori duplicati e valori nulli
4. Fulltext, utilizzati per velocizzare la ricerca di testi

Gli indici di una tabella possono essere creati sia in fase di creazione della tabella stessa, che in fase di modifica. Ad esempio, supponiamo di voler creare una tabella Studente con i campi Matricola, Nome, Cognome, Esame, Voto, e di voler definire il campo Matricola come chiave primaria della tabella, e il campo Cognome come indice della tabella stessa. In questo caso la query da scrivere sarà la seguente.

 CREATE TABLE Studente (
               Matricola INT(10),
               Nome VARCHAR(40),
               Cognome VARCHAR(40),
               Esame VARCHAR(40),
               Voto INT(2),
               PRIMARY KEY(Matricola),
               INDEX(Cognome)
               );

Il comando che è stato utilizzato per definire il campo cognome come indice è ovviamente INDEX. In questo modo si rende molto più veloce non solo l’accesso in tabella attraverso la chiave primaria, ma anche attraverso il campo Cognome. Oltre al comando INDEX, per definire un indice si può ricorrere indifferentemente anche alla parola chiave KEY. Di conseguenza, la query di seguito è del tutto identica alla precedente.

 CREATE TABLE Studente (
               Matricola INT(10),
               Nome VARCHAR(40),
               Cognome VARCHAR(40),
               Esame VARCHAR(40),
               Voto INT(2),
               PRIMARY KEY(Matricola),
               KEY(Cognome)
               );

E’ possibile ovviamente inserire anche un indice riferito a più campi. Ad esempio supponiamo che alla tabella studente, oltre all’indice Cognome, vogliamo aggiungere un altro indice legato ai campi Nome e Cognome. In questo caso, bisognerà ricorrere al comando ALTER TABLE sulla tabella di interesse e poi ricorrere alla clausola ADD seguita dal tipo di indice che si vuole creare. Nel nostro caso la query da utilizzare è la seguente

ALTER TABLE Studente
ADD INDEX(Nome, Cognome);

Gli indici che abbiamo creato, seguendo la sequenza che abbiamo indicato sopra, sono degli indici del primo tipo, in quanto consentono l’inserimento per i campi Nome e Cognome di dati duplicati. Qualora volessimo creare degli indici che non devono essere duplicati bisogna ricorrere alla clausola UNIQUE. Quindi, supponiamo di voler creare la tabella studente avente come unico indice il campo matricola, con tale indice che non deve presentare duplicati. In questo caso la query da creare sarà la seguente

CREATE TABLE Studente (
              Matricola INT(10) NOT NULL,
              Nome VARCHAR(40),
              Cognome VARCHAR(40),
              Esame VARCHAR(40),
              Voto INT(2),
              UNIQUE(Matricola)
              );

Osserviamo che in questo caso, anche se non abbiamo dichiarato il campo matricola esplicitamente come chiave primaria per la tabella studente, esso si comporta proprio una chiave primaria. Infatti, col comando NOT NULL, applicato in fase di creazione del campo matricola, abbiamo impedito che possano essere inseriti dei record in tabella che abbiano quel campo nullo. Inoltre, avendolo definito come indice unico, abbiamo fatto in modo che per quel campo non possano esserci duplicati, comportandosi di fatto come chiave primaria. C’è da dire inoltre, che se l’indice unico è composto da un solo campo, esso può essere definito direttamente nella fase di creazione del campo stesso. Quindi la query di prima risulta analoga alla seguente.

CREATE TABLE Studente (
              Matricola INT(10) NOT NULL UNIQUE,
              Nome VARCHAR(40),
              Cognome VARCHAR(40),
              Esame VARCHAR(40),
              Voto INT(2)
              );

Osserviamo che agli indici creati in precedenza non è mai stato dato un nome. Quando non viene associato un nome ad un indice, MySQL vi assegna come nome di default il primo nome dei campi che afferiscono a quell’indice. Quindi, nei casi precedenti, all’indice contenente il campo Cognome, MySQL vi associa di default il nome ‘Cognome’, mentre all’indice costituito dai campi Nome e Cognome, MySQL vi associa il nome ‘Nome’. Qualora volessimo associare un nome ad un indice in fase di creazione, dobbiamo inserire tale nome dopo la clausola KEY o INDEX, e prima della dichiarazione dei campi che compongono tale l’indice. Ad esempio, se in fase di creazione della tabella studente l’indice associato al campo Cognome vogliamo chiamarlo Last_Name, la query da eseguire sarà la seguente:

 CREATE TABLE Studente (
               Matricola INT(10),
               Nome VARCHAR(40),
               Cognome VARCHAR(40),
               Esame VARCHAR(40),
               Voto INT(2),
               PRIMARY KEY(Matricola),
               KEY Last_Name (Cognome)
               );

Qualora invece di inserire un indice volessimo eliminarlo da una tabella, l’istruzione da utilizzare è DROP seguito dal nome dell’indice da eliminare. Quando non si conosce il nome dell’indice da eliminare, possiamo procedere alla seguente maniera. Supponiamo di avere a database una tabella studente e di non conoscere gli indici di questa tabella. Per visualizzare gli indici ricorriamo al comando SHOW CREATE TABLE. Nel nostro caso sarà:

SHOW CREATE TABLE Studente

La query fornisce il seguente risultato, che altro non è che tutto il codice SQL associato alla tabella studente creata.

 'Studente', CREATE TABLE 'studente' (
   'Matricola' int(10) NOT NULL DEFAULT '0',
   'Nome' varchar(40) DEFAULT NULL,
   'Cognome' varchar(40) DEFAULT NULL,
   'Esame' varchar(40) DEFAULT NULL,
   'Voto' int(2) DEFAULT NULL,
   PRIMARY KEY ('Matricola'),
   KEY 'Last_Name' ('Cognome'),
   KEY 'First_Last_Name' ('Nome','Cognome')
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Come osserviamo, il nome dell’indice contenente i campi Nome e Cognome ha come nome First_Last_Name. A questo punto volendo cancellare l’indice costituito dai campi Nome e Cognome la query da scrivere è la seguente:

ALTER TABLE studente
DROP INDEX First_Last_Name;

Oltre ai metodi sopra descritti, esiste un altro modo per creare e rimuovere indici su tabelle MySQL, attraverso i comandi CREATE INDEX, CREATE UNIQUE INDEX e DROP INDEX. Le sintassi da utilizzare sono la seguenti:

CREATE INDEX Nome_Indice ON Nome_Tabella(Campi_Tabella);
CREATE UNIQUE INDEX Nome_Indice ON Nome_Tabella(Campi_Tabella);
DROP INDEX Nome_Indice ON Nome_Tabella;

Quindi, se ad esempio volessimo sulla tabella studente creare un indice di nome Prova, per il campo Esame, le query per inserirlo ed eliminarlo sono le seguenti:

CREATE INDEX Prova ON studente(Esame);

DROP INDEX Prova ON studente;

Infine bisogna sottolineare che, a differenza del comando ALTER TABLE per la creazione e rimozione di indici, le funzioni di creazione e rimozione indici di cui sopra consentono la creazione e rimozione di un indice alla volta a differenza di quanto si può fare con ALTER TABLE.

Argomenti chiave:

• index
• indici
• create index
• drop index
• aggiungere indici
• indici mysql