Indici

Gli indici sulle tabelle costituiscono un aspetto fondamentale nella progettazione di un database: un indice infatti consente di velocizzare, spesso in maniera estremamente consistente, l’accesso ai dati in fase di lettura.

Gli indici possono essere di diverso tipo: le Primary Key (chiavi primarie) consentono di identificare univocamente un record all’interno di una tabella; gli indici unique, come le chiavi primarie, non possono avere valori duplicati, ma, al contrario di queste, possono contenere valori NULL.
Gli altri indici prevedono invece la possibilità che i loro valori siano ripetuti.

È possibile avere indici formati da più colonne, così come è possibile, nel caso di campi non numerici, indicizzare solo la prima parte di una colonna. Sui campi di tipo BLOB e TEXT è possibile creare indici solo nelle tabelle MyISAM e InnoDB, indicando obbligatoriamente una lunghezza in byte.

Esistono poi gli indici fulltext, che consentono di effettuare ricerche sui testi basate sul “linguaggio naturale”; tali ricerche forniscono anche l’indicazione del livello di pertinenza rispetto alla stringa cercata.
Gli indici fulltext possono essere creati solo su tabelle MyISAM per colonne di tipo CHAR, VARCHAR e TEXT.

Nella progettazione degli indici bisogna tenere conto che, come costo per la velocità che offrono in fase di lettura, comportano un rallentamento in fase di inserimento e aggiornamento dei dati. Di conseguenza non bisogna esagerare nelle definizioni. Nel caso in cui si vogliano definire indici multi-colonna, è bene usare per prime le colonne che vengono utilizzate più spesso nelle query.

Infatti l’indice su più colonne può essere utilizzato anche dalle query che referenziano una parte di tali colonne, purchè si tratti delle prime. In certi casi (colonne contenenti caratteri) può essere utile definire l’indice non sull’intera colonna ma solo sui primi n caratteri, se questi costituiscono un prefisso significativo. Con questo sistema infatti si otterrà un indice di dimensioni più ridotte e quindi più performante.

Nella lezione precedente abbiamo già visto che, in una CREATE TABLE, possiamo definire direttamente le colonne come PRIMARY KEY o come UNIQUE; gli altri indici vanno nelle definizioni relative alla tabella, così come anche i primi due tipi se devono comprendere più di una colonna.

Abbiamo visto anche che gli indici definiti in quest’ultimo modo prevedono la dichiarazione della (o delle) colonna_indice. Inoltre in alcuni casi è possibile usare il parametrotipo_indice; tale parametro può essere indicato per le tabelle di tipo MyISAM, InnoDB e MEMORY.

Tuttavia le prime due supportano solo indici di tipo BTREE, mentre solo per le tabelle MEMORY è possibile una reale scelta tra indici HASH e BTREE. Gli indici di tipo HASH vengono usati solo per confronti che includono l’operatore di uguaglianza, e hanno bisogno di utilizzare l’intera chiave (al contrario dei BTREE che possono utilizzare anche solo la prima parte). In questi casi sono molto veloci.

Il tipo di indice si indica con la clausola USING:

CREATE TABLE nome_tabella (id INT, INDEX USING HASH (id)) ENGINE = MEMORY;

Gli indici di tipo HASH sono il default per le tabelle MEMORY.

La dichiarazione della colonna indice avviene indicando il nome della colonna seguito facoltativamente da una lunghezza fra parentesi tonde. Questo parametro si utilizza quando non si vuole indicizzare l’intera colonna, ma solo una sua parte.

Vediamo due esempi:

CREATE TABLE nome_tabella (campo1 CHAR(10), campo2 CHAR(50),
INDEX (campo1), INDEX (campo2(10)));

In questo caso abbiamo indicizzato per intero campo1 e solo i primi 10 caratteri di campo2. Ovviamente per creare indici su più colonne si uniranno le dichiarazioni delle colonne che lo compongono, separate da virgole.

Gli indici possono essere creati anche dopo la creazione della tabella, con l’istruzione CREATE INDEX:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX nome_indice
[USING tipo_indice]
ON nome_tabella (colonna_indice,…)

Infine, per eliminare un indice, si usa DROP INDEX:

DROP INDEX nome_indice ON nome_tabella

Both comments and pings are currently closed.

Comments are closed.