Laboratorio di Sistemi Informativi

Funzionalità avanzate

In questa lezione vedremo varie caratteristiche avanzate dei sistemi di gestione delle basi di dati in generale e di MySQL in particolare.

Indici

Abbiamo visto nelle scorse lezioni come manipolare ed estrarre dati. Non siamo stati molto attenti alle prestazioni, in quanto i database esemplificativi sono sempre stati molto piccoli e le nostre query non richiedevano al più qualche frazione di secondo per essere eseguite. Tuttavia, quando i database diventano molto grossi, e quando il server DBMS deve servire centinai di utenti connessi contemporaneamente, è importante conoscere gli accorgimenti che si possono adottare per migliorare la velocità di esecuzione.

Gli indici sono lo strumento principe per migliorare le prestazioni di un database. Se una tabella ha un indice su una determinata colonna (diciamo la colonna id), vuol dire che accedere alla tabella conoscendo il valore di tale campo, con un comando del tipo

SELECT * FROM tabella WHERE id=245;

è molto più efficiente che in assenza dell'indice. Questo, a sua volta, velocizza le operazioni di join sul campo id.

Ad esempio, consideriamo il file paroleconnumeri.txt costituito da due colonne, una con un identificatore numerico, l'altra con una parola della lingua inglese. Creiamo una tabella a partire da questi dati con:

CREATE TABLE paroleconnumeri (id int, parola varchar(40));

LOAD DATA LOCAL INFILE 'paroleconnumeri.txt' INTO TABLE paroleconnumeri;

Supponiamo di voler creare una query per visualizzare una lista di coppie (parola1, parola2) tali che parola2 segue immediatamente parola1 nell'elenco. Una query di questo tipo è:

SELECT p1.parola, p2.parola FROM paroleconnumeri p1, paroleconnumeri p2 WHERE p1.id+1=p2.id

Visto che il risultato è troppo voluminoso per poter essere visualizzato sullo schermo, mettiamo il risultato in una tabella:

CREATE TABLE coppie (p1 varchar(40), p2 varchar(40));

INSERT INTO coppie SELECT p1.parola, p2.parola FROM paroleconnumeri p1, paroleconnumeri p2 WHERE p1.id+1=p2.id

.....e aspettiamo... e aspettiamo...

Possiamo interrompere la interrogazione in corso aprendo un'altra connessione a MySQL e utilizzando i comandi SHOW PROCESSLIST e KILL (vedi sezione successiva in questa stessa lezione), rispettivamente per avere un elenco delle interrogazioni in corso e per interrompere quella relativa al comando di cui sopra.

Si può notare che alcuni dati sono già stati inseriti nella tabella, ma molti meno di quanti dovrebbero essere in totale. Ora, con il comando ALTER TABLE modifichiamo il campo id di paroleconnumeri in modo da trasformarlo in chiave primaria. Cancelliamo i dati già presenti nella tabella coppie e diamo di nuovo il comando:

INSERT INTO coppie SELECT p1.parola, p2.parola FROM paroleconnumeri p1, paroleconnumeri p2 WHERE p1.id+1=p2.id

...et voilà... ecco il risultato in 1.52 secondi (con il mio computer).

Comandi per manipolare gli indici

L'esistenza di una chiave primaria su alcuni campi determina automaticamente la creazione di un indice, ovvero una particolare struttura dati che consente di rintracciare velocemente le righe di una tabella a partire dai valori della chiave. Su MySQL, in realtà, i termini chiave e indice sono praticamente sinonimi. Si ricordi che però, ad essere precisi, una chiave è un concetto molto più astratto, legato al modello relazionale e alla capacità di un insieme di attributi di determinare univocamente una riga di una tabella.

Ogni indice è costruito su uno o più campi di una unica tabella. Esistono tre tipi di indici:

chiavi primarie: esiste al più una chiave primaria per ogni tabella. I campi che costituiscono la chiave primaria non possono assumere valore NULL, e non possono esistere due righe che hanno esattamente gli stessi valore per tutti i campi della chiave. L'idea è che la chiave primaria determina univocamente una unica riga di una tabella;
indici unici: come le chiavi primarie, ma i campi possono assumere il valore NULL. Non possono esistere due righe che hanno esattamente gli stessi valori per tutti i campi dell'indice, a meno che alcuni di questi valori non siano NULL;
indici non-unici: come gli indici unici, ma non danno luogo a nessuna condizione di unicità da rispettare.

È possibile sapere quale sono gli indici attivi su un data tabella con il comando DESCRIBE:

mysql> DESCRIBE paroleconnumeri;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | 0       |       |
| parola | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

Quella che ci interessa è la colonna Key, che può assumere i seguenti valori:

PRI: indica che il campo fa parte di una chiave primaria;
UNI: indica che il campo fa parte di un indice unico;
MUL: indica che il campo fa parte di un indice non-unico.

Ma, a parte la chiave primaria, come si fa a specificare degli indici aggiuntivi? È possibile specificare gli indici al momento della creazione di un tabella, e inoltre ci sono tutta una serie di comandi per la loro manipolazione:

ALTER TABLE <nometabella> ADD PRIMARY KEY (<listacampi>): se non ne esiste già una, crea una chiave primaria sui campi specificati;
ALTER TABLE <nometabella> DROP PRIMARY KEY: elimina l'indice di chiave primaria;
ALTER TABLE <nometabella> ADD UNIQUE [INDEX] [<nomeindice>] (<listacampi>): crea un indice di tipo unico sui campi specificati. Si può indicare il nome dell'indice, che altrimenti per default viene impostato pari al nome del primo campo di <listacampi>;
ALTER TABLE <nometabella> ADD INDEX [<nomeindice>] (<listacampi>): crea un indice di tipo non-unico sui campi specificati. Vedi sopra per il nome dell'indice;
ALTER TABLE <nometabella> DROP <nomeindice>: cancella l'indice specificato;
SHOW CREATE TABLE <nome_tabella>: comando che già conosciamo... visualizza, oltre alle informazioni sui campi delle tabelle, anche informazioni dettagliati sugli indici.

Per ragioni di uniformità con altri oggetti manipolati da MySQL, come tabelle e schemi, esiste anche una seconda serie di comandi per la creazione e cancellazione degli indici, del tutto equivalenti al comando ALTER TABLE:

CREATE [UNIQUE] INDEX <nomeindice> ON <nometabella> (<listacampi>): crea un indice, unico o no a seconda della presenza della parola chiave UNIQUE;
DROP INDEX <nomeindice> ON <nometabella>: cancella un indice.

Per tutti questi comandi, si può usare la parola KEY al posto di INDEX. Ad esempio, si può usare CREATE UNIQUE KEY per creare un indice unico.

Connessioni

Ogni server MySQL può eseguire più di una query contemporaneamente. Alcune di queste query possono essere particolarmente lunghe, per cui, dopo averle mandate in esecuzione, può essere necessario interromperle manualmente (vedi sezione precedente!!!). Analogamente, può essere necessario interrompere manualmente una connessione ad un client che per qualche motivo è rimasta aperta anche se il client non è accessibile e che, quindi, sta consumando inutilmente delle risorse.

MySQL mette a disposizione una serie di comandi per la manipolazione di interrogazioni e connessioni. Questi sono:

SHOW [FULL] PROCESSLIST: visualizza l'elenco delle connessioni in corso. Per ogni connessione visualizza l'utente collegato, il database attivo, lo stato, il numero di secondi da cui la connessione si trova in quello stato ed, eventualmente, l'interrogazione corrente. Se viene specificata la parola chiave FULL viene visualizzata la query completa, altrimenti solo i primi 100 caratteri. Normalmente solo le connessioni dell'utente corrente vengono visualizzati, a meno che esso non goda del privilegio PROCESS, come avviene ad esempio per l'utente root.
KILL [QUERY] <id connessione>: chiude manualmente la connessione specificata, ed interrompe l'interrogazione in corso. Se si specifica la parola chiave QUERY, l'interrogazione viene interrotta, ma la connessione rimane aperta. Normalmente si possono interrompere solo le connessioni effettuate dall'utente corrente, a meno che non si disponga del privilegio SUPER.

Abbiamo visto un esempio di utilizzo di questi comandi nella sezione dedicata agli indici.

Tabelle temporanee

In MySQL è possibile creare delle tabelle temporanee. Si tratta di tabelle che vengono create e vivono solo per una singola sessione, poi vengono eliminate. Inoltre la tabella temporanea è visibile solo all'interno della sessione in cui è stata creata. Le altre sessioni attive non vedono la tabella, anzi, possono addirittura crearne un'altra con lo stesso nome, e le due rimangono oggetti separati.

Tutti gli utenti che hanno il privilegio CREATE TEMPORARY TABLES possono creare una tabella temporanea con il comando CREATE TEMPORARY TABLE, che ha la stessa sintassi di CREATE TABLE. Ad esempio:

CREATE TEMPORARY TABLE temp (
valore int
);

A questo punto la si usa esattamente come una tabella normale... o quasi. I comandi manipolazione dei dati funzionano normalmente, ma, ad esempio, temp non appare nell'elenco delle tabelle che si ottiene con SHOW TABLES !!! Se inoltre apriamo un'altra finestra con il client MySQL, la tabella temp non è visibile, e un comando SELECT * FROM temp restituisce l'errore:

ERROR 1146: Table 'test.temp' doesn't exist

È possibile, in quest'altra finestra, creare una nuova tabella temporanea di nome temp e riempirla con valori differenti a quelli che essa possiede nell'altra sessione.

Le tabelle temporanee sono di solito usate per memorizzare temporaneamente i risultati di una query complessa, in modo da poter riaccedere ai dati successivamente, nell'ordine che si preferisce. Nelle vecchie versioni di MySQL erano utilizzate anche per sopperire alla mancanza delle SELECT annidate.

Notare che una tabella temporanea è comunque tenuta su disco. Invece di memorizzarla nella directory dati di MySQL (/var/lib/mysql nel caso della Ubuntu) viene memorizzata in una directory dedicata ai file temporanei (/tmp) e da lì automaticamente cancellata quando non serve più. Per tabelle temporanee piccole, può essere molto più conveniente tenere la tabella in memoria centrale, in modo da ridurre le operazioni di accesso al disco del server database. È quello che vedremo tra poco.

Motori per la memorizzazione dei dati

Una delle caratteristiche più interessanti di MySQL è che dispone di più di un metodo di memorizzazione dei dati. Ogni tabella può utilizzare un metodo diverso di memorizzazione, quello che meglio si adatta alle esigenze degli sviluppatori. Nella terminologia di MySQL questi metodi di memorizzazione prendono il nome di ENGINE (motori). Finora abbiamo sempre usato il motore standard, denominato MyISAM. Per avere un elenco di tutti i motori disponibili si può usare il comando:

SHOW [STORAGE] ENGINES: mostra quali sono i motori disponibili nella versione di MySQL che si sta utilizzando, indicando anche se sono abilitati o meno.

Per specificare un motore diverso da MyISAM, quando si crea una nuova tabella, basta aggiungere in fondo al comando CREATE TABLE o CREATE TEMPORARY TABLE la parola chiave ENGINE, in questo modo:

CREATE TABLE <nometabella> (
    ....
) ENGINE=<motore>

Il motore di default, usato quando non si specifica nulla, è appunto MyISAM. È possibile anche modificare il motore di una tabella già esistente con:

ALTER TABLE <nometabella> ENGINE=<motore>: modifica il motore della tabella indicata.

Tabelle di tipo MEMORY

Tavolta è necessario memorizzare dei dati che non è importante preservare in caso di spegnimento o riavvio del server. In questi casi, se i dati non sono eccessivamente voluminosi, potrebbe essere più utile tenerli in memoria centrale piuttosto che su disco. A tal scopo nasce il motore di memorizzazione MEMORY.

La tabella di tipo MEMORY ha alcune limitazioni dovute alla sua implementazione peculiare: ad esempio non si possono usare al suo interno campi di tipo TEXT. Per il resto, una tabella di tipo MEMORY si usa normalmente, ma ovviamente occorre ricordarsi che, non essendo memorizzata su disco, quando il server di database termina anche il contenuto della tabella viene perso. Attenzione però, la tabella continua comunque ad esistere anche se vuota. Inoltre, il contenuto di una tabella MEMORY, come per una tabella MyISAM, è condiviso tra tutte le sessioni MySQL attive. Non si tratta quindi di una tabella temporanea, come si ottiene invece col comando CREATE TEMPORARY TABLE.

È tuttavia possibile creare una tabella temporanea di tipo MEMORY con

CREATE TEMPORARY TABLE <nometabella> (
....
) ENGINE=MEMORY

In questo caso si ottiene una tabella temporanea il cui contenuto è memorizzato in memoria centrale. Essa è quindi particolarmente adatta per memorizzare temporaneamente dei dati non troppo voluminosi.

Tabelle di tipo InnoDB e vincoli di integrità referenziale

Un altro motore molto importante in MySQL è InnoDB. Le tabelle InnoDB supportano infatti una serie di caratteristiche non previste dalle tabelle MyISAM. In particolare:

vincoli di integrità referenziale: è possibile specificare che un campo della tabella T1 è chiave esterna per la tabella T2, e questo impedisce di creare righe in T1 che non corrispondono a nessuna riga in T2.
gestione delle transazioni: è il metodo standard per garantire che operazioni concorrenti sulla base di dati non provochino situazioni inconsistenti, e per preservare i dati anche in caso di guasti hardware o crash del server. Il motore MyISAM dispone di un metodo alternativo per la gestione della concorrenza, i blocchi di tabella, che pur essendo abbastanza efficiente, non è altrattando standard e sicuro nel caso di crash.

A fronte di queste maggiori funzionalità, le tabelle InnoDB sono generalmente più lente di quelle MyISAM nelle operazioni di lettura. Visto che MySQL è orientato ad applicazioni web, per le quali la velocità di risposta è fondamentale e le operazioni sono principalmente di lettura, gli sviluppatori hanno deciso di adottare come motore di default il MyISAM.

Nota. A dire il vero, numerosi siti Internet sembrano smentire l'affermazione che MyISAM sia più veloce di InnoDB per le operazioni di lettura, circostanziando le proprie affermazioni con numerosi test. Allo stato attuale delle conoscenze, sembra che l'unico metodo per appurare veramente la velocità dei due sistemi di memorizzazione sia quello di testarne le prestazioni caso per caso sulla singola applicazione.

Vediamo ora un esempio di utilizzo di vincoli referenziali. Supponiamo di creare la tabelle dipendenti e stipendi come segue:

CREATE TABLE dipendenti (
  id INT PRIMARY KEY,
  nome VARCHAR(20),
  citta VARCHAR(10)
) ENGINE=InnoDB;

CREATE TABLE stipendi (
  id INT,
  stipendio NUMERIC(10,2),
  FOREIGN KEY (id) REFERENCES dipendenti(id)
) ENGINE=InnoDB;

Se adesso diamo il comando INSERT INTO stipendi VALUES (1,20000) l'esecuzione fallisce perché nella tabella dipendenti non esiste nessuna istanza che ha id pari a 1:

mysql> INSERT INTO stipendi VALUES (1,20000); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test/stipendi`, CONSTRAINT `stipendi_ibfk_1` FOREIGN KEY (`id`) REFERENCES `dipendenti` (`id`))

Occorre prima inserire un elemento in dipendenti con id pari a 1 e poi si può ripetere l'inserimento:

mysql> INSERT dipendenti VALUES (1,'gianluca','chieti');
Query OK, 1 row affected (0.02 sec)

mysql>  INSERT INTO stipendi VALUES (1,20000);
Query OK, 1 row affected (0.01 sec)

Non è possibile cancellare la riga appena inserita, nè modificarne il valore id, perché questo porterebbe a violare il vincolo di integrità referenziale. A meno che, ovviamente, non si usino le clausole ON DELETE e ON UPDATE, per le quali vi rimando al vostro libro di basi di dati.

Alcune caratteristiche peculiari rispetto all'implementazione dei vincoli di integrità referenziale nello standard SQL sono:

sia la tabella esterna che quella interna devono essere di tipo InnoDB;
nella tabella esterna, deve esistere un indice sulle colonne a cui si riferisce il vincolo;
nella tabella interna, deve esistere un indice sulle colonne che costituiscono la chiave esterna; se questo indice non esiste, viene creato automaticamente;
i vincoli di integrità referenziale vanno specificati con una clausola FOREIGN KEY .... REFERENCES; l'uso delle sola clausola REFERENCES nella definizione di una colonna non funziona;
non sono supportate le clausole ON UPDATE SET DEFAULT e ON DELETE SET DEFAULT.

Infine, si noti che è possibile modificare i vincoli di integrità referenziale con i comandi:

ALTER TABLE <nometabella> [CONSTRAINT <nome vincolo>] ADD FOREIGN KEY (<lista campi>) REFERENCES <tabella esterna> (<lista campi>): aggiunge un vincolo di chiave esterna; si può specificare un nome vincolo da utilizzare successivamente per la cancellazione con la clausola CONSTRAINT, ma se non si specifica ne viene creato uno di default.
ALTER TABLE <nometabella> DROP FOREIGN KEY <nome vincolo>: cancella un vincolo di chiave esterna; se il vincolo è stato creato senza specificare un nome, è possibile dare il comando SHOW CREATE TABLE per sapere il nome generato automaticamente da MySQL.

Tutto ciò vale esclusivamente per le tabelle di tipo InnoDB. Per tutti gli altri metodi di memorizzazione i vincoli di integrità referenziale non sono supportati, e la maggior parte dei comandi relativi vengono silenziosamente ignorati.

Lezione Precedente

Elenco Lezioni

Lezione Successiva