Tipi di tabelle

MySQL permette di utilizzare numerosi tipi diversi di tabelle, ovvero diversi “storage engine” (motori di archiviazione) per la memorizzazione dei dati. La distinzione più importante fra i diversi sistemi è quella fra transazionali e non transazionali.

I motori transazionali offrono alcuni importanti vantaggi: sono più sicuri (permettono di recuperare i dati anche in caso di crash di MySQL o di problemi hardware) e consentono di effettuare più modifiche e convalidarle tutte insieme o, al contrario, ripristinare la situazione preesistente se qualcosa va male.

Dal canto loro, i motori non transazionali hanno il vantaggio di una maggior velocità, minore utilizzo di spazio su disco e minor richiesta di memoria per gli update. È anche possibile combinare tabelle transazionali e non nelle stesse istruzioni, anche se, in questo caso, le modifiche fatte sulle tabelle non transazionali divengono comunque effettive nel momento in cui sono eseguite.

Quando si crea una tabella si specifica a MySQL di che tipo si tratta attraverso l’opzione ENGINE:

CREATE TABLE tabella (a INT) ENGINE = INNODB;

Nel caso in cui la dichiarazione venga omessa, MySQL utilizzerà il tipo di default, che normalmente è MyISAM.

Tuttavia, se avete installato MySQL su Windows attraverso il Configuration Wizard come vi abbiamo suggerito nella lezione 2, il tipo di default che troverete impostato sarà InnoDB. Potete modificare questo valore intervenendo sull’opzione –default-table-type nel file di configurazione (v. lezione 4).

Se tentate di creare un tipo di tabella non supportato dal vostro database (ad esempio perchè il motore relativo non è incluso nel vostro server oppure perchè lo avete avviato senza quel sistema), MySQL creerà al suo posto una tabella MyISAM.

Vediamo ora una panoramica dei diversi sistemi:

MyISAM

È lo storage engine di default di MySQL, basato sul vecchio ISAM ora non più supportato.

Ogni tabella MyISAM utilizza tre file: un file .frm che contiene la definizione della tabella, più un file .MYD per i dati e un file .MYI per gli indici.

È possibile indicizzare le colonne di tipo BLOB e TEXT, e si possono utilizzare valori NULL nelle colonne indicizzate. Inoltre MyISAM può gestire una colonna di tipo AUTO_INCREMENT per ogni tabella, incrementando automaticamente il suo valore per ogni riga scritta; i valori eliminati non vengono riutilizzati nemmeno se sono gli ultimi della sequenza. Ogni colonna può utilizzare un set di caratteri diverso.

Le tabelle MyISAM possono avere un formato statico, dinamico o compresso. Il formato statico viene utilizzato quando la tabella non contiene colonne a lunghezza variabile (VARCHAR, BLOB, TEXT) e offre maggior sicurezza e velocità, ma generalmente richiede più spazio su disco. Il formato dinamico invece può portare facilmente ad una frammentazione della tabella (in caso di numerose modifiche o cancellazioni dei dati), nel qual caso è bene periodicamente effettuare un’ottimizzazione attraverso il comandoOPTIMIZE TABLE.

Da notare che se una tabella è in formato dinamico, le colonne definite come CHAR vengono convertite in VARCHAR a meno che non abbiano una lunghezza non superiore ai 4 bytes. Il formato compresso infine è utile per generare tabelle a sola lettura che minimizzano l’occupazione di spazio; vengono create attraverso l’utility myisampack.

Nel caso si riscontrino problemi su una tabella, è necessario utilizzare il comando CHECK TABLE per verificare la situazione, e REPAIR TABLE per ripristinare la tabella. MySQL è in grado di risolvere la maggior parte dei problemi con questo sistema.

InnoDB

È uno storage engine transazionale dotato di capacità di commit, rollback e crash recovery. È ottimizzato per l’uso concorrente dei dati fra molti utenti e per essere molto performante anche su grandi quantità di dati. Inoltre supporta le FOREIGN KEY.

Se non usate le tabelle InnoDB, potete avviare il server con l’opzione –skip-innodb. Se invece le utilizzate, dovreste fornire al server le indicazioni sui file da utilizzare per i dati.

Vediamo un esempio di opzioni di configurazione relative a InnoDB sul file delle opzioni:

innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend:max:500M
innodb_buffer_pool_size=70M
innodb_additional_mem_pool_size=10M
innodb_log_group_home_dir = /iblogs
innodb_log_files_in_group = 2
innodb_log_file_size=20M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M

innodb_data_home_dir indica la directory per il file dei dati. Se non la indicate, InnoDB utilizza la directory dati di MySQL. Se la indicate vuota, dovete indicare il percorso completo su innodb_data_file_path, che indica i nomi dei file che saranno utilizzati come tablespace. Potete indicarne uno o più: nell’esempio avremo un file ibdata1 di 50 megabytes e un file ibdata2 di 50 megabytes che potrà estendersi fino a 500. L’opzione autoextend può essere indicata solo sull’ultimo file. Tenete presente la dimensione massima dei file sul vostro sistema per indicare il parametro max. Le directory indicate devono esistere, perchè il server non è in grado di crearle.

innodb_buffer_pool_size dovrebbe essere circa la metà della memoria del vostro computer.

innodb_log_group_home_dir è la directory per i file di log. Se non la indicate verrà usata la stessa dei dati.

innodb_log_file_size dovrebbe essere circa il 25% della dimensione del buffer pool. Le altre impostazioni dovrebbero andare bene così come sono.

Se non indicate le impostazioni di configurazione, InnoDB creerà un file dati di 10MB e due file di log da 5MB ciascuno nella directory dati di MySQL.

Abbiamo detto che InnoDB consente l’uso delle transazioni. Ogni connessione al server MySQL inizia in autocommit mode, il che significa che tutte le istruzioni di aggiornamento vengono rese effettive immediatamente. Se disattivate l’autocommit con SET AUTOCOMMIT = 0, le modifiche diverranno operative solo quando eseguite l’istruzione COMMIT. Se al suo posto eseguite ROLLBACK, verranno annullate tutte le modifiche fino alla COMMIT precedente. Potete utilizzare le transazioni senza disattivare l’autocommit, iniziandole con START TRANSACTION o BEGIN e terminandole con COMMIT o ROLLBACK.

InnoDB gestisce il valore AUTO_INCREMENT per una tabella in modo particolare: questo viene infatti calcolato la prima volta che si rende necessario dopo l’avvio del server (ad esempio per una INSERT), selezionando il valore massimo esistente sulla tabella e incrementandolo di 1. A quel punto il valore viene conservato in memoria ma non scritto su disco, per cui al riavvio successivo sarà ricalcolato. Questo significa che se cancellate gli ultimi valori della tabella senza fare nuovi inserimenti, al successivo riavvio il server riutilizzerà quei valori.

Con le tabelle InnoDB possiamo definire le foreign key, cioè collegare i valori delle colonne che contengono chiavi di altre tabelle alle tabelle stesse. In questo modo è possibile verificare automaticamente quando i valori della tabella madre vengono modificati o eliminati in modo da impedire queste modifiche o, al contrario, modificare di conseguenza anche i valori sulla tabella figlia. Inoltre non è possibile inserire nella tabella figlia valori che non hanno un corrispondente nella tabella madre. Vediamo un esempio di definizione:

CREATE TABLE madre(id INT NOT NULL,
                    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE figlia(id INT, madre_id INT,
                   INDEX par_ind (madre_id),
                   FOREIGN KEY (madre_id) REFERENCES madre(id)
                     ON DELETE CASCADE
) ENGINE=INNODB;

In questo codice vediamo definite due tabelle nelle quali la tabella figlia ha una foreign key sulla tabella madre. Quando viene cancellata una riga dalla tabella madre, se il valore di id è presente in un campo madre_id della tabella figlia la riga corrispondente viene anch’essa eliminata. Entrambe le tabelle devono essere di tipo InnoDB; entrambe le colonne devono figurare come primo campo di un indice.

È possibile dare un nome alla relazione specificando “CONSTRAINT nome” prima di FOREIGN KEY. Tale nome sarà poi utilizzabile qualora si decida di eliminare la FOREIGN KEY, con la sintassi “ALTER TABLE tabella DROP FOREIGN KEY nome“.

Qualora il nome non venga indicato, sarà creato in automatico dal server e visualizzabile attraverso “SHOW CREATE TABLE tabella“.

Nell’esempio abbiamo visto l’indicazione della clausola ON DELETE per gestire le cancellazioni sulla chiave esterna referenziata; allo stesso modo è possibile utilizzare ON UPDATE per gestire gli aggiornamenti. Entrambe le opzioni accettano come valori possibili uno dei seguenti: CASCADE, SET NULL, RESTRICT, NO ACTION.

Con il primo, come abbiamo visto, le modifiche sulla tabella madre vengono riportate sulla tabella figlia. Con SET NULL gli eventuali valori presenti nella tabella figlia vengono impostati a NULL (la definizione della colonna lo deve consentire). Le ultime due equivalgono a non indicare la clausola e comportano il rifiuto della modifica sulla tabella madre.

In una tabella InnoDB non è possibile utilizzare indici FULLTEXT, e le colonne di tipo geometrico sono disponibili solo a partire dalla versione 5.0.16.
Per le colonne AUTO_INCREMENT deve esistere un indice di cui siano l’unico componente.

MERGE

Una tabella MERGE è formata da un insieme di tabelle MyISAM identiche fra loro nella struttura. Questo significa che devono avere le stesse colonne e gli stessi indici; anche l’ordine in cui colonne e indici sono dichiarate deve essere il medesimo. Ecco un esempio di definizione:

CREATE TABLE totali (
  a INT NOT NULL AUTO_INCREMENT,
  messaggio CHAR(20), INDEX(a))
  ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

La clausola UNION specifica i nomi delle tabelle che costituiscono la tabella totali. Tali tabelle dovranno avere definiti gli stessi campi e gli stessi indici; l’unica eccezione sono le primary key che non vengono replicate nella tabella MERGE in quanto l’unicità delle chiavi può essere garantita solo all’interno della singola tabella.

 

Con INSERT_METHOD=LAST si specifica che eventuali inserimenti devono essere effettuati sull’ultima delle tabelle elencate; l’alternativa sarebbe l’opzione FIRST per effettuare gli inserimenti sulla prima. Nel caso non venga specificata la clausola, non sarà possibile inserire record sulla tabella. Da notare che se si esegue il DROP TABLE su una tabella MERGE sarà solo quest’ultima ad essere eliminata, non le tabelle sottostanti.

Le tabelle di questo tipo possono essere utili quando si ha a che fare con grosse moli di dati che si preferisce tenere separati fisicamente ma che all’occorrenza vanno consultati tutti insieme (ad esempio tabelle di log). L’uso delle tabelle MERGE può dare vantaggi prestazionali rispetto al caso in cui tutti i dati si trovano su un’unica tabella.

MEMORY

Le tabelle MEMORY (in precedenza chiamate HEAP) hanno una definizione che viene salvata su disco, ma i loro dati sono conservati solo in memoria. Questo significa che ad ogni riavvio del server tali tabelle saranno vuote. Ovviamente tabelle di questo tipo saranno molto veloci. Bisogna però stare attenti a non generare tabelle che assumano dimensioni troppo grandi, o si può mandare in crisi il server. La variabile di sistema max_heap_table_size pone un limite alla loro dimensione.

Sulle tabelle MEMORY non è possibile utilizzare campi di tipo BLOB o TEXT.

BDB (BerkeleyDB)

Le tabelle BDB non funzionano su sistemi Linux Alpha, AMD64, IA-64, s390, nonchè su Mac OS X. Funzionano invece su piattaforme Linux Intel, nonchè Sun Solaris, FreeBSD, AIX 4.3.x, SCO (OpenServer e UnixWare 7.1.x), Windows (da architetture NT in poi).

Se non si possono o non si vogliono utilizzare le tabelle BDB, si può avviare il server con l’opzione –skip-bdb. Da notare comunque che questo motore non è sempre disponibile: ad esempio le distribuzioni binarie Windows lo includono solo nella versione MySQL-Max.

Le tabelle BDB sono di tipo transazionale; come già visto per InnoDB, BDB lavora di default in autocommit, ed è possibile cambiare il suo comportamento con SET AUTOCOMMIT=0, oppure con una istruzione BEGIN WORK che fa iniziare una transazione sospendendo l’autocommit. COMMIT o ROLLBACK sono le possibili conclusioni della transazione.

È possibile indicare nei file di configurazione alcune opzioni relative a BDB. Ad esempio le opzioni –bdb-home e –bdb-logdir specificano rispettivamente la directory per i dati e per i file di log. Se esiste un indice unique su una colonna che ammette valori NULL, un solo valore NULL per quella colonna potrà essere presente sulla tabella.

Le tabelle BDB sono formate da un file .frm con la definizione della tabella e da un file .db che contiene dati e indici. Tali file non possono essere spostati perchè il file .db contiene anche il proprio percorso.

ARCHIVE

Le tabelle di tipo ARCHIVE, come suggerisce il nome, possono essere utilizzate per archiviare grosse quantità di dati senza indici, e senza la possibilità di modificarli ma solo di aggiungere righe alla tabella. Su queste tabelle è possibile infatti compiere esclusivamente SELECT e INSERT. I dati vengono compressi con zlib, e per questo la loro occupazione di spazio è molto limitata.

CSV

Questo storage engine consente di memorizzare i dati su file di testo i cui valori sono separati da virgole. Una tabella CSV è formata da un file .frm che, come sempre, descrive la tabella, e dal file .CSV che contiene i dati in formato testuale. è intuitivo che questo tipo di tabelle, che non supporta indici, può essere utile qualora si vogliano esportare dati verso applicazioni come i fogli di calcolo.

Per brevità non ci soffermiamo sugli altri storage engine: EXAMPLEFEDERATEDBLACKHOLENDB Cluster.

Both comments and pings are currently closed.

Comments are closed.