Tutorial su Mysql->Differenze tra chiavi e indici

Differenze tra chiavi e indici

Dallo schema E-R alle tabelle

Dopo aver sviluppato una basi di dati mediante lo schema Entità Relazione, ci si trova a dover implementare le tabelle fisiche. Il grosso del lavoro successivo, corrisponde a impostare opportunamente gli indici. Prima di parlare degli indici vediamo alcune considerazioni sulle chiavi create nella progettazione.
La chiave primaria è quel campo che assicura l'univocità del record: valori univoci ed assenza di valori nulli.
Poiché ci si aspetta che la maggior parte degli accessi fanno riferimento ad essa, il gestore di database crea un albero di ricerca su di essa. In pratica, quando occorre cercare un record tramite una chiave primaria, invece di cercarlo leggendo i record successivamente, lo cerca in una struttura dati ad albero, partendo dal nodo radice e leggendo successivamente solo i nodi figli che portano alla chiave, riducendo notevolmente i tempi di ricerca. Quando trova il nodo foglia relativo alla chiave, in esso ci sarà un puntatore al record effettivo, dove andarlo a leggere. Un campo impostato come indice permette di creare un altro albero di ricerca. L'uso di indici, quindi serve per effettuare delle query più veloci ma aumentano il volume dei dati archiviati e il tempo impiegato per gestire gli alberi di ricerca nelle operazioni di inserimento e modifica. Spesso di indici vengono determinati empiricamente (per tentativi).


La figura precedente è un esempio di albero di ricerca. Il nodo radice è il nodo più in alto. Ogni nodo è costituito da un vettore ordinato di indici, e tra un indice e il suo successivo c'è il puntatore al nodo figlio. Il nodo foglia ha solo puntatori ai dati. Per cercare il record con indice 72, si parte dal nodo radice, quindi utilizzando il vettori di elementi ordinati, si scende al nodo figlio (indici da 32 al 78). In questo caso il nodo trovato corrisponde ad un nodo foglia, da cui si trova l'indice da trovare e il puntatore al record.

Contatore o chiavi primarie multiple?

Spesso ci si trova a dover creare una tabella con chiave multipla, ma conviene lasciare questa impostazione, o utilizzare un contatore e impostare opportunamente i campi come indici? Ovviamente nel secondo caso, se occorre, è possibile inserire anche un vincolo di univocità tra più campi.
Vediamo come esempio due tabelle correlate, e verifichiamo pregi e difetti dei due metodi. L'esempio in esame sarà relativo all'archiviazione di un documento fattura con intestazione e dettaglio.
Schema E-R Le tabelle corrispondenti sono: documento (anno, numero_documento, id_gruppo, ...) e, documento_dettaglio (anno, numero_documento, id_gruppo, numero_riga, ...).
Applicando un contatore, le tabelle diventano: documento (id, anno, numero_documento, id_gruppo, ...), documento_dettaglio (id, id_documento, numero_riga, ...).
Notiamo subito il grosso vantaggio del primo caso, ossia la garanzia dell'univocità del record tramite anno, numero_documento e id_gruppo. Tuttavia, nel secondo caso esistono altri vantaggi, di cui alcuni meno ovvi:
1) Assenza di dati ridondanti tra tabelle relazionate;
2) Semplificazioni del codice SQL nelle query join;
3) Riduzione del numero di parametri nelle ricerche;
4) Possibilità di gestire casi particolari (in questo esempio il caso della fattura bis);
5) Migliore manutenzione delle tabelle e del codice SQL.

Vediamo in dettaglio i punti espressi.
Primo punto. (Assenza di dati ridondanti tra tabelle relazionate) Nel primo metodo la ridondanza è ovvia, in quanto la tabella documento_dettaglio ripete gli stessi campi presenti nella chiave della tabella documento.

Secondo punto. (Semplificazioni del codice SQL nelle query join) Ipotizziamo di dover eseguire una query collegando le due tabelle tramite LEFT JOIN; nel primo caso, il codice SQL ha una forma simile alla seguente Se consideriamo che all'interno di un gestionale, ci dovranno essere tante query come queste, (specie se si applica lo stesso ragionamento anche alle altre tabelle) è molto facile imbattersi nel seguente errore Se il beta-testing non riesca a rilevare l'errore, esso si scoprirà solo dopo un anno di utilizzo del gestionale.
Nel secondo metodo questo errore non potrebbe verificarsi
Terzo punto. (Riduzione del numero di parametri nelle ricerche) Nel sorgente di un un applicativo, nel primo caso per cercare una fattura dobbiamo gestire tre variabili mentre nel secondo basta una sola variabile.

Quarto punto. (Possibilità di gestire casi particolari) Un caso particolare di fattura, è la fattura bis. Questo tipo di fattura si applica quando si ha la necessità di inserire una fattura in un determinato periodo nel passato. Poiché le fatture sono progressive, l'unico modo è quello di duplicare un numero di una fattura, inserendo nella seconda affianco al numero la dicitura bis. È ovvio che solo applicando il secondo metodo, si può gestire un caso così particolare.

Quinto punto. (Migliore manutenzione delle tabelle e del codice SQL) Ipotizziamo di voler convertire il programma di fatturazione mono-azienda in un programma di fatturazione multi-azienda. Quando ci si imbatte in modifiche di questo tipo, occorre rivedere i requisiti del sistema e modificare lo schema E-R. Vediamo di seguito lo schema modificato


Si noti che mentre nel primo metodo, l'identificativo dell'azienda diviene parte della chiave del documento, nel secondo caso diviene un semplice campo in più. Questo vuole dire, che nel primo metodo, il codice SQL per una query che collega le due tabelle diventa Quindi in questo caso occorre rivedere tutte le query dell'applicativo.
Nel secondo metodo non tutte le query necessitano di essere modificate; questo si traduce in un minore costo di implementazione.

Chiavi e codici alfanumeri

Vorrei aggiungere un altro tipo di problema che si manifesta con la scelta delle chiavi primarie. Consideriamo la tabella relativa all'anagrafica dei clienti; se impostiamo come chiave primaria la partita IVA, allora vuol dire escludere clienti da nazioni come la Svizzera. Proprio per evitare questi problemi, molti gestionali impostano delle chiavi alfanumeriche, per clienti, fornitori e articoli. A questo punto sembra di aver risolto tutti i problemi, ma non è così. Questi codici alfanumeri, sono molto utilizzati dagli utenti del sistema come valori di riferimento, e come ogni elemento del sistema, l'utente richiede espressamente di poterli modificare a piacere. Il problema è che per un utente, un codice alfanumerico è una chiave "intellettuale" utile per gli utenti del sistema, e non una chiave "fisica" necessaria per il sistema. Ad esempio ci sono utenti che eliminano vecchi articoli o vecchi clienti e riutilizzano il codice (in realtà un cliente o un articolo associati a delle operazioni, non dovrebbe essere eliminato, ma solo sospeso). Un altro caso è quando un vecchio codice alfanumerico ritenuto non conforme, viene sostituito da un nuovo codice. Si rende quindi necessario creare un contatore nascosto all'utente come chiave primaria, quindi per l'utente si crea un campo specifico alfanumerico (indicizzato) da utilizzare per richiamare i record. In questo modo, la modifica di questo campo, non crea nessun problema. Ovviamente in fase di modifica e creazione, occorre sempre verificare l'univocità del codice modificato dall'utente.

Riepilogo

• Introduzione
• Differenze tra chiavi e indici
• Operatore JOIN
• Sub-query
• Transazione protette
• Ottimizzazione delle interrogazioni
• View, Function e Stored procedure