InnoDB

InnoDB è un motore per il salvataggio di dati per MySQL, fornito in tutte le sue distribuzioni. La sua caratteristica principale è quella di supportare le transazioni di tipo ACID.

La licenza è la GNU GPL versione 2.

È stato acquistato dalla Oracle Corporation prima ancora che acquistasse MySQL.

Ecco in che cosa si differenzia da MyISAM.

• Per riparare una tabella dopo un crash del sistema, InnoDB riesegue le ultime istruzioni registrate nei log. MyISAM deve invece eseguire una scansione completa della tabella per poi ripararla, ed eventualmente ricostruire gli indici. Di conseguenza, il tempo impiegato da InnoDB per la riparazione non aumenta con il crescere dei dati contenuti nella tabella, mentre il tempo impiegato da MyISAM è proporzionale alle dimensioni della tabella.
• Mentre MyISAM si affida al sistema operativo per il caching delle letture e delle scritture sulle tabelle, InnoDB ha una sua propria gestione della cache. Le pagine di dati modificate non vengono inviate immediatamente al sistema e questo, in alcuni casi, può rendere la modifica dei dati molto più rapida con InnoDB.
• MyISAM generalmente immagazzina i record di una tabella nell'ordine in cui sono state create, mentre InnoDB le immagazzina nell'ordine seguito dalla chiave primaria. Quando viene utilizzata la chiave per la lettura di una riga, l'operazione avviene più rapidamente.
• InnoDB comprime i record molto meno rispetto a MyISAM. Questo significa che la memoria e lo spazio su disco richiesti da InnoDB sono maggiori, nonostante nella versione 5 di MySQL lo spazio su disco richiesto sia diminuito del 20%.
• Allo stato attuale, InnoDB non supporta le ricerche fulltext.

Anche lo Storage Engine BDB, come InnoDB, era stato aggiunto a MySQL con lo scopo di offrire supporto per le transazioni. È stato abbandonato a partire dalla versione 5, per non meglio precisati "motivi tecnici". Esso era più veloce e richiede meno spazio su disco e meno memoria rispetto a InnoDB, ma non supportava i savepoint né le transazioni di tipo XA.

Un fork di InnoDB, chiamato XtraDB, è sviluppato e mantenuto da Percona. Esso è completamente compatibile con InnoDB, il cui codice viene costantemente importato ad ogni nuova versione. In più comprende una serie di bug fix e di funzionalità aggiuntive. XtraDB è presente per default in MariaDB e in Percona Server, in luogo del progetto originale.

Funzionalità del motore InnoDB di MySQL

InnoDB mette a disposizione le seguenti funzionalità:

• transazioni SQL con savepoint e transazioni XA;
• lock a livello di record;
• chiavi esterne;
• integrità referenziale;
• colonne AUTOINCREMENT;
• tablespace.

Il lock in InnoDB per quanto riguarda i comandi SELECT è di tipo non-locking. InnoDB offre delle ottime performance in termini di prestazioni e utilizzo della CPU specialmente quando si ha a che fare con una grande quantità di dati. InnoDB può interagire tranquillamente con tutti gli altri tipi di tabelle in MySQL.

Limiti delle tabelle InnoDB

Le tabelle InnoDB sono soggette alle seguenti limitazioni:

• Non è possibile creare più di 1000 colonne per tabella
• Su alcuni sistemi operativi le dimensioni del tablespace non possono superare i 2 Gb
• La grandezza di tutti i file di log di InnoDB deve essere inferiore ai 4 Gb
• La grandezza minima di un tablespace è di 10 MB
• Non possono essere creati indici di tipo FULLTEXT (dalla versione fornita con MySQL 5.6 sono supportati al pari di MyISAM)
• Le SELECT COUNT(*) su tabelle di grandi dimensioni possono essere molto lente

Cenni di base sul funzionamento

Al contrario di quello che fa MYISAM, InnoDB memorizza di default i propri dati all'interno di un unico file definito TABLESPACE.
Questo file in base a parametri di configurazione che è possibile specificare e impostare nel file di configurazione di MYSQL può essere AUTO ESTESO (se la dimensione iniziale era per esempio di 50M e li ho riempiti tutti posso dire a MYSQL di aumentare automaticamente di n tot di MB o GB questo file), impostato ad una grandezza voluta, ecc.
È possibile anche usare più TABLESPACE per memorizzare i dati con InnoDB (In questo caso l'unico file che può essere autoesteso sarà solo l'ultimo). È molto comodo utilizzare più file per memorizzare i dati quando si ha a che fare con sistemi operativi che non supportano file troppo grandi (alcuni sistemi operativi max 2 Gb), in questo modo si aggira il problema. In certi casi più TABLESPACE vengono memorizzati su dispositivi fisici (dischi) diversi al fine di aumentare le prestazioni del sistema diminuendo le contese di I/O.

Come creare un tabella di tipo InnoDB

Per sapere se InnoDB è presente nella propria installazione di MySQL, eseguire:

SHOW VARIABLES LIKE 'have_innodb'

Questo comando mostra il valore della variabile have_innodb: se è YES InnoDB è attivo. Oppure, per avere una panoramica di tutti i tipi di tabella attivi e non attivi, eseguire:

SHOW ENGINES

La sintassi per creare una tabella InnoDB è la seguente:

CREATE TABLE prova (pk1 INT, .....) ENGINE = InnoDB;
CREATE TABLE prova (pk1 INT, .....) TYPE = InnoDB;

Per convertire una tabella già esistente in InnoDB:

ALTER TABLE prova ENGINE = InnoDB;
ALTER TABLE prova TYPE = InnoDB;

È consigliato utilizzare la sintassi ENGINE poiché TYPE (utilizzato nelle vecchie versioni di MySQL) è in disuso. INNOBASE è sinonimo di InnoDB.

Principali parametri di configurazione del Tablespace

I seguenti parametri sono da impostare nel file di configurazione di MySQL.

• innodb_data_home_dir = nome_dir
  

Imposta la directory dove verrà memorizzato il tablespace di InnoDB

• innodb_data_file_path = ibdata1:50M
  

Questa riga crea il tablespace ibdata1 settandolo ad una grandezza di 50M

• innodb_data_file_path = ibdata1:50M:autoextend
  

Da la possibilità al tablespace di autoestendersi

• innodb_data_file_path = ibdata1:50M;ibdata2:50M:autoextend
  

Crea 2 table space (ibdata1 e ibdata2), in questo caso solo l'ultimo tablespace può autoestendersi

• innodb_file_per_table
  

Verrà creato un tablespace per ogni tabella (Attenzione al baco delle versioni precedenti alla 4.1.9)

Vengono creati per ogni tabella anche dei file con estensione .FRM all'interno della cartella relativa al DB, che contengono i descrittori della struttura della tabella.

Per aggiungere dei TABLESPACE e/o modificare quelli esistenti è possibile procedere in questo modo: Per aggiungere un TABLESPACE bisogna impostare i parametri di configurazione di MYSQL come mostrato sopra.
Per editarne uno esistente l'unica soluzione possibile è quella di fermare il server, fare delle copie dei dati (es con MYSQLDUMP) distruggere il TABLESPACE e ricrearne uno nuovo.
Utilizzare l'utiliti di importazione per ripristinare i dati.

Di default innoDB utilizza 2 file detti LOG FILES per memorizzare le modifiche fatte sul DB prima di memorizzarle nel TABLESPACE.

I file di LOG sono molto utili poiché tengono traccia delle modifiche effettuate sul DB lavorando come dei SAVEPOINT e permettendo in genere il ripristino del DB se quest'ultimo vienne corrotto.
È possibile modificare la modalità di lavoro di questi file di log tramite le seguenti proprietà:

• innoDB_flush_log_at_trx_commit
  

Di default questa variabile è impostata a 1 e cioè ad ogni COMMIT le modifiche riportate vengono salvate dal buffer in memoria prima nel file di LOG, poi partendo dal file di LOG nel TABLESPACE.
Quando è impostato a 0 l'operazione di flush viene effettuata una volta ogni secondo e non sul COMMIT.
Quando è impostato a 2 una volta al secondo i dati vengono copiati dal buffer nel file di LOG, ma l'operazione di inserire i dati nel TABLESPACE non viene effettuata su di esso.

Molti altri parametri possono essere configurati per i file di LOG: http://dev.mysql.com/doc/mysql/en/innodb.html

Backup e ripristino

InnoDB permette tramite i file di log di tentare il ripristino del database al riavvio da una situazione che ha bloccato il server.
È utile e consigliato effettuare (dopo aver fermato il server) copie di sicurezza dei tablespace, dei log dei file frm e dei file di configurazione di MYSQL. Una utility che permette il backup dei db è mysqldump.

Colonne autoincrementali

In InnoDB è possibile specificare dei campi con valori che si autoincrementano ad ogni INSERT. Per gestire questi valori MySQL utilizza una tabella in memoria centrale dove memorizza ad ogni richiesta il valore del campo calcolandolo così:

SELECT max(nome_col) FROM tabella FOR UPDATE;

La gestione del lock su questa tabella è affidata a MySQL. Bisogna tener conto che in caso di rollback di una transazione alcuni valori potrebbero essere saltati se prima della chiusura della transazione sono pervenute altre richieste.

Chiavi esterne

InnoDB supporta le chiavi esterne. Per funzionare le chiavi esterne in InnoDB devono rispondere alle seguenti proprietà:

• tutte le tabelle coinvolte devono essere di tipo InnoDB
• nella tabella di riferimento il campo in relazione deve avere un indice
• i campi BLOB e TEXT non supportano le foreign key in quanto richiedono l'uso di un prefisso per l'indice e questo nelle foreign key non è supportato.

InnoDB rifiuta l'inserimento nella tabella figlia se il valore che si vuole inserire non è presente nella tabella padre. L'integrità referenziale è supportata.

È possibile gestire le operazioni da effettuare quando si modificano dei dati in relazione con un'altra tabella in questo modo:

FOREIGN KEY nome1(nome_colonna) REFERENCES nome_tabella(nome_colonna)
ON DELETE
ON UPDATE
RESTRICT
  impossibile eliminare i valori dalla tabella madre se i esiste una relazione con la tabella
  figlia per quel valore
CASCADE
  Aggiorna i valori nella tabella figlia partendo dal valore della tabella madre
SET NULL
  imposta i valori in relazione nella tabella figlia a NULL
NO ACTION
  Nessuna azione definita

È possibile disabilitare i controlli sulle chiavi esterne utilizzando la variabile: FOREIGN_KEY_CHECKS = 0;

È possibile eliminare una chiave esterna nel seguente modo:

ALTER TABLE nome_tabella DROP FOREIGN KEY nome_foreign_key;

Esempio di utilizzo delle chiavi esterne:

CREATE TABLE prova1 (pr_code_pk INT not null, pr_denom VARCHAR(30), primary key(pr_code_pk))
ENGINE=InnoDB;

CREATE TABLE figlio1 (ch_code_pk INT not null, ch_pr_code_fk INT, primary key(ch_code_pk), INDEX
ch1(ch_pr_code_fk), foreign key (ch_pr_code_fk) references prova1(pr_code_pk) on delete cascade)
ENGINE=InnoDB;

Usare le transazioni con InnoDB

Di default InnoDB ha la proprietà AUTOCOMMIT impostata a 1, quindi ad ogni comando SQL effettuato verrà effettuato un commit.
Per usare le transazioni è possibile usare la seguente sintassi:

BEGIN;
 Comando SQL1
COMMIT;

SET AUTOCOMMIT = 0;
 Comando SQL1
 Comando SQL2
ROLLBACK;

Cenni sul funzionamento delle transazioni

InnoDB implementa un lock a livello di record.

Il lock può essere condiviso (prop. lock, altre transazioni possono leggere il record) o esclusivo (accesso esclusivo al record).

Valori possibili per la variabile Transaction isolation level di InnoDB:

• READ-UNCOMMITTED
• READ-COMMITTED
• REPEATABLE-READ
• SERIALIZABLE

È sempre possibile impostare la modalità di lavoro della transazione tramite:

SET TRANSACTION ISOLATION LEVEL tipo

Voci correlate

• MySQL
• XtraDB
• MyISAM

Collegamenti esterni

• http://www.innodb.com/
• http://www.mysql.com/

Portale Software libero: accedi alle voci di Wikipedia che trattano di Software libero