InnoDB

InnoDB è un motore per il salvataggio di dati (storage engine) 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.
• 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 stati creati, mentre InnoDB li 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;
• buffer per gli indici, le chiavi, modifiche, cancellazioni;
• compressione delle tabelle.

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.

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;
• Gli indici FULLTEXT sono stati introdotti nella versione 5.6 di MySQL e nella versione 10.0 di MariaDB, ma in alcune circostanze sono meno efficienti rispetto a quelli di MyISAM.

Aspetti di base sul funzionamento

InnoDB memorizza i dati e gli indici all'interno dei TABLESPACE.

Nelle vecchie versioni di InnoDB vi era un solo tablespace. È ancora possibile memorizzare i dati in questo modo, ma per default ogni tabella ha un proprio tablespace. Questo è necessario per evitare alcuni vecchi problemi e utilizzare le funzionalità fornite dai formati di file più moderni rispetto ad Antelope. Le informazioni riguardanti la struttura e lo stato dei tablespace sono registrate nel database di sistema. Questo tablespace è registrato in uno o più file (di norma due) aventi come prefisso ibdata.

I tablespace si estendono automaticamente quando è necessario. Quando si cancellano grosse quantità di dati, però, le dimensioni restano invariate.

In certi casi i tablespace (ma anche alcuni log) vengono memorizzati su dispositivi fisici (dischi) diversi al fine di aumentare le prestazioni del sistema diminuendo le contese di I/O.

Come creare una 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

Dà 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: https://web.archive.org/web/20051003223833/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;

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/
• https://www.mysql.com/