Home News Gli indici SQL: cosa sono, come funzionano e perché sono utili
Gli indici SQL: cosa sono, come funzionano e perché sono utili PDF Stampa E-mail
Martedì 17 Febbraio 2015 14:00

Chiunque abbia lavorato con query e tabelle SQL si sarà imbattuto prima o poi in strutture interne chiamate indici e view. Tralasciando queste ultime, è importante capire bene cosa sia un indice, come funzioni e a cosa servano all’interno del linguaggio SQL.

Capire cosa sono gli indici non è complicato e, allo stesso tempo, è importante per riuscire a scrivere le query in modo performante.

Si cerchi, per un attimo, di dimenticare linguaggio SQL e la questione indici e ci si immagini all’interno di una biblioteca. In questa biblioteca, tutti i libri presenti (oltre un milione di volumi) sono indicizzati secondo uno specifico archivio che, in ordine alfabetico o per autore, recensisce ogni singolo volume, indicando l’esatta posizione dove andare a recuperarlo.

Una volta immaginata la situazione (non è poi così complicato e sarà capitato a tutti di trovarsi a dover cercare delle informazioni in un archivio), si faccia un ulteriore passo in avanti. Si immagini che, per un motivo o per un altro, questo archivio di indicizzazione venga disordinato o dismesso. Accedere a un volume diverrà molto più complicato e se prima era sufficiente valutare un indice alfabetico per pochi minuti prima di individuare il libro desiderato, ora la questione diventa molto più complicata. In una biblioteca di questo tipo, per trovare il libro giusto sarà necessario consultare, in media, almeno 500 mila volumi prima di arrivare a quello desiderato.

Questa operazione complica la ricerca e allunga notevolmente i tempi.

Capito l’esempio della biblioteca, si può tornare a parlare di tabelle SQL. Un indice è una sorta di schedario, quindi una tabella essa stessa, che tiene traccia su dove sono posizionati i dati all’interno del database.

Gli indici sono quindi delle tabelle speciali associate alle tabelle dati e diventano utili nel momento in cui vengono eseguite delle query.

Infatti, quando un database crea un record in una tabella, tale record seguirà un ordine che è quello di inserimento. Questo equivale a dire che, in assenza di un indice, ogni operazione che tenta il recupero di dati da qualsiasi tabella del database, costringe il database stesso a leggere l’intera tabella, eseguendo quello che in gergo viene definito scansione della tabella. Il famigerato Table Scan, infatti, di norma è sinonimo di crollo delle prestazioni.

Con un indice appropriato, invece, il database è capace di recuperare i dati necessari direttamente consultando l’indice, per identificare la posizione esatta occupata dalle informazioni sul database stesso.

In questo modo, si evita il Table Scan, il recupero dei dati su cui le query devono lavorare avviene in modo più veloce e la query stessa è più performante.

Consigli d’uso per ottenere benefici dall’uso degli indici

Messa in questi termini, i neofiti o comunque chi si è avvicinato da poco al linguaggio SQL potrebbe pensare di associare un indice a qualsiasi campo di qualsiasi tabella dati contenuta nel database, credendo che in questo modo le performance tendano a migliorare.

In realtà, questa conclusione è errata e viene a essere confutata dall’esperienza d’uso. Infatti, quando con il linguaggio SQL si creano degli indici, SQL memorizza tanto i dati della tabella, quanto i dati degli indici. Inoltre, a differenza di altri linguaggi progettati per la gestione dei file, il linguaggio SQL permette di creare più indici sulla stessa tabella.

Entrambe queste caratteristiche delle strutture degli indici fanno sì che, qualora si modifichino spesso i dati a cui gli indici sono associati, a queste variazioni ne conseguano altrettante relative ai puntatori che puntano alle tuple della tabella stessa.

Come dire: cambiano i dati, cambiano anche gli indici.

Questa situazione, insieme al fatto che gli indici occupano spazio su disco, porta a una brusca caduta delle performance dell’intero database, qualora gli indici siano troppi o siano associati a tipologie di query, di esecuzioni e dati in modo non opportuno.

Per questo, nell’uso degli indici, è opportuno tenere a mente tutta una serie di tips e reminders, utili a capire come gestire questa tipologia di struttura.

Prima di tutto, bisogna sapere che:

  • gli indici sono indicati nelle query di SELECT, in quelle con condizioni WHERE o negli ordinamenti di tipo ORDER BY; sono da evitare nei comandi di INSERT e UPDATE per quanto detto fino a ora;
  • gli indici velocizzano le query a livello computazionale, garantendo un accesso più veloce ai dati coinvolti nell’interrogazione, ma occupano molto spazio su disco;
  • le query possono essere ottimizzate tramite l’uso degli indici solo se lavorano su una quantità di dati che non superi il 30 percento dei dati totali. Nel caso in cui si lavori con una quantità di dati superiore, allora gli indici non migliorano la velocità di lettura delle query;
  • gli indici non dovrebbero comunque essere usati sulle tabelle piccole e con poche tuple, perché non migliorerebbero i tempi di accesso, ma produrrebbero l’effetto contrario;
  • i migliori risultati nell’uso degli indici si ottengono quando questi lavorano su un numero consigliato di quattro o cinque colonne con importanti quantità di dati e con pochi valori NULL;
  • gli indici non dovrebbero essere usati su dati che richiedono modifiche frequenti e di questa regola bisognerebbe tenerne conto, quando si effettuano molti aggiornamenti. Se si deve procedere con un aggiornamento totale dei dati e vi sono degli indici, le buone norme impongono che prima si proceda alla distruzione degli indici, poi si aggiornino i dati e infine si ricreino gli indici necessari;
  • se si usano delle funzioni sugli attributi, allora è opportuno non indicizzarli;
  • l’indice non si sfrutta se si usa l’operatore di diseguaglianza (!=);
  • l’uso degli indici è sconsigliato nei confronti testuali con l’operatore LIKE e, comunque, in questi casi le wildcard vanno messe in fondo alla direttiva;
  • nelle query su indici combinati, bisogna mantenere l’ordine per garantire migliori performance.

Quali tipologie di indici esistono

Nell’ultima raccomandazione, si è parlato di indici combinati. In realtà, con l’aggettivo combinato si indica una tipologia di indice, fra diversi disponibili.

In generale, si può dire che un indice può essere di tipo semplice o combinato, clustered o unclusterd, univoco o non univoco.

Senza scendere troppo nei dettagli, un indice viene detto semplice quando contiene una sola colonna, mentre viene definito composto quando è dichiarato su due o più colonne di dati.

Un indice cluster, invece, definisce l’ordinamento della tabella. In pratica, questo tipo di indice non esiste fisicamente, ma definisce le colonne (o attributi) rispetto ai quali ordinare, in modo sequenziale, i dati memorizzati nella tabella. Ovviamente, può essere definito un solo indice clustered per ogni tabella ed è il più performante dal punto di vista delle query di richiesta. Allo stesso modo, è anche il più esoso dal punto di vista delle risorse, qualora venga usato nelle query di aggiornamento e inserimento. Il più delle volte, l’indice clustered coincide con l’identificatore della tupla di dati, perché è immodificabile.

Un indice non clustered non memorizza i dati della tabella, ma solo i puntatori ai dati in una struttura a tabella a parte contenuta sempre nel database.

Infine, un indice può essere univoco qualora i dati a cui l’indice fa riferimento non possano essere duplicati all’interno della tabella, mentre, al contrario, quello non univoco permette di inserire nella tabella più tuple con gli stessi valori delle colonne definite come indici.

Come si implementano e si distruggono gli indici

Dal punto di vista applicativo, la creazione di un indice avviene in modo molto semplice, con un apposito comando del linguaggio SQL.

La sintassi di base prevede che si usi una query del tipo:

CREATE INDEX index_name ON table_name;

Per quanto riguarda il nome dell’indice, è prassi comune usare il prefisso idx_, prima del nome dell’indice, di solito formato dalle informazioni relative alla tabella e alla colonna su cui l’indice viene creato. In questo modo, si evita di confondere l’indice con altre strutture del database.

Per creare un indice su singola colonna, usare:

CREATE INDEX index_name ON table_name (column_name);

mentre per creare un indice composito, bisogna usare:

CREATE INDEX index_name ON table_name (column1, column2);

Allo stesso modo, per avere un indice unico, la sintassi SQL esatta è:

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Qualora sia necessario eliminare un indice creato in precedenza, bisogna usare:

DROP INDEX index_name;

Come si visualizzano e si usano in phpMyAdmin

Anche con le piattaforme di gestione database MySQL come phpMyAdmin, utilizzato, ad esempio, nei piani hosting di Aruba, è possibile interagire e visualizzare eventuali indici presenti. Ad esempio, collegandosi all’interfaccia di gestione MySQL di Aruba e autenticandosi al servizio è possibile accedere al phpMyAdmin.

Gli indici SQL: cosa sono, come funzionano e perché sono utili

Da qui, una volta selezionato il database, bisogna scegliere la tabella di interesse, effettuando sempre un clic con il pulsante sinistro del mouse.

Gli indici SQL: cosa sono, come funzionano e perché sono utili

Nella parte bassa, vengono indicati gli Indici definiti sulla tabella, da cui possono essere modificati ed eliminati con i link Modifica ed Elimina.

 

ArubaQuesto articolo è offerto da Aruba.it. Aruba gestisce una propria rete di Data Center a livello Europeo ed è leader nell’erogazione di servizi di hosting condiviso in Italia con oltre 1 milione di clienti attivi. L’offerta di hosting condiviso include diverse soluzioni per il singolo professionista o l’azienda, verifica la migliore per te.  Aruba S.p.A., fondata nel 1994, è la prima società in Italia per i servizi di web hosting, e-mail, PEC e registrazione domini, la società gestisce oltre 2 milioni di clienti. In aggiunta ai servizi di web hosting, Aruba fornisce anche tutti una serie di servizi come server dedicati, housing e colocation o servizi managed. Aruba propone anche tutte le soluzioni nell’ambito dell’e-Security, come la firma digitale, la PEC e la conservazione sostitutiva.

 

Tutorial

Vuoi approfondire degli argomenti specifici? Segui i miei tutorial, per dubbi o domande contattami.

Vai ai tutorial

Flickr

Guarda le mie foto su Flickr. Hai bisogno di foto particolari? Vuoi imparare a fotografare? Contattami
flickr_logo

Servizi Online

Vuoi predisporre il tuo sito web personalizzato? Hai bisogno di aiuto o consulenza?

Acquista i servizi online

Contattami

Per approfondimenti o informazioni su corsi di formazione o lavori contattami.

Richiedi uno scambio link con il mio sito.