Gli indici nel linguaggio SQL

Le query su un database MySql possono essere ottimizzate applicando degli indici ai campi delle tabelle.

Quando un database Sql raggiunge una dimensione dei dati molto elevata, il tempo di risposta sulle query comincia a rallentare esponenzialmente.

Ottimizzando gli indici sul DBMS si può ridurre il tempo di risposta di trenta volte, migliorando sensibilmente l'efficienza delle applicazioni e dei programmi.

Cosa sono gli indici e a cosa servono

Un indice di una tabella svolge una funzione simile all'indice del libro, ordina i record del database in base al valore di un determinato campo.

Quando si cerca in un libro un argomento, è utile sfogliare prima l'indice e trovare le pagine di un libro dove è affrontato. Una sorta di segnalibro logico. In questo modo si evita di dover leggere ogni riga del libro per trovare le occorrenze.

La stessa cosa accade in un database. Se il campo indicizzato è strategico per la selezione, il DBMS consulta prima l'indice secondo i puntatori dei campi indicizzati e poi effettua la query.

La ricerca diventa molto più veloce e il tempo di risposta della query si riduce notevolmente rispetto a una ricerca full table scan.

Quali tipi di indici esistono in SQL

In una tabella SQL possono essere aggiunti diversi tipi di indice, i principali sono i seguenti:

  1. Primary key. E' l'indice associato alla chiave primaria della tabella. Non deve contenere dati duplicati, né valori Null. Spesso è usata su campi numerici con la direttiva auto_increment per identificare i record ( es. ID ).
  2. Unique key. E' un indice aggiuntivo. Si applica ai campi strategici della tabella. Non può contenere dati duplicati ma è consentita la presenza dei valori Null.
  3. Index. Questo tipo di indice consente sia i dati duplicati che i valori Null.
  4. Full Text. E' un indice specifico usato nelle ricerche delle parole all'interno dei testi.

Come si aggiunge un indice alla tabella

L'istruzione per aggiungere un indice alla tabella è il comando ALTER. Ha soltanto due parametri obbligatori, il nome della tabella e il nome del campo da indicizzare.

ALTER TABLE tabella ADD INDEX(campo);

Una volta aggiunto l'indice si può provare ad eseguire una selezione tramite il campo indicizzato.

SELECT * FROM tabella WHERE ( campo>5 )

La precedente query seleziona tutti i record della tabella che hanno un valore superiore a cinque registrato nel campo.

Non occorre dichiarare nulla nella SELECT. Il database riconosce che si tratta di un campo indicizzato e utilizza l'indice per eseguire la selezione.

Soltanto se la pista dell'indice è esterna deve essere richiamata esplicitamente con USE INDEX.

SELECT * FROM tabella USE INDEX ( nome indice ) WHERE ( campo>5 )

Le piste esterne sono utili se l'indicizzazione del campo è necessaria soltanto in determinate circostanze e non sempre.

La memoria e gli indici

Gli indici sono informazioni aggiuntive della tabella consentono di velocizzare l'esecuzione delle query ma occupano anche uno spazio in memoria sul server.

E' un altro aspetto da prendere in considerazione. Se il database è molto grande anche gli indici lo sono.

E' consigliabile indicizzare soltanto i campi strategici per le ricerche e non tutti.

Come ottimizzare le select ripetute

I nodi dei puntatori delle query più recenti sono memorizzati in uno spazio di memoria detto key_buffer nell'engine MyISAM.

Se il dato è già presente nel buffer, la select ripetuta non deve consultare il database, poiché lo trova in memoria, e il tempo di esecuzione si riduce.

Nel caso delle query ricorrenti, sugli stessi dati, è utile ampliare la dimensione di memoria a disposizione del buffer.

Si può modificare tramite la variabile key_buffer_size nel file di configurazione my.cnf che si trova nella cartella /etc.

22 / 08 / 2015

 


 

Segnalami un errore, un refuso o un suggerimento per migliorare gli appunti

FacebookTwitterLinkedinLinkedin
knowledge base

Menu principale

  1. Il linguaggio Sql
  2. Come installare MySQL sul PC
  3. Come installare PostgreSQL
  4. Come creare una tabella
  5. Come fare una selezione
  6. Come modificare i dati in una tabella