InnoDB, MyISAM e gli Storage Engine

In una delle lezioni precedenti, quando si è parlato della creazione di tabelle, abbiamo incontrato la parola chiave ENGINE:

CREATE TABLE Persone
(...)
ENGINE=InnoDb

In questo caso il codice definirebbe la tabella denominata Persone, con engine InnoDB. Semplicisticamente, si potrebbe pensare agli Storage Engine come i "tipi" delle tabelle. In realtà, essi sono delle librerie (prodotte congiuntamente al DBMS o da enti terzi) che determinano il modo in cui i dati di quella tabella saranno salvati su disco, e ciò sarà determinante per valutare le prestazioni, l'affidabilità, le funzionalità offerte dalla tabella stessa, rendendola più o meno adatta a particolari utilizzi.

A partire dalla versione 5.5 di MySQL, InnoDB è lo Storage Engine di default, ossia quello assegnato automaticamente qualora, in fase di creazione della tabella, non si specifichi il parametro ENGINE.

Per sapere quali storage engine sono a disposizione della propria installazione del DBMS, possiamo eseguire, tramite il client testuale mysql, il seguente comando:

SHOW ENGINES;

L'output mostrerà una tabella stilizzata che elenca tutti gli engine a disposizione e le principali funzionalità che li caratterizzano. In particolare, il campo Support sarà valorizzato con YES, NO o DEFAULT. I primi due valori specificano se lo storage engine può essere usato o no, l'ultimo indica se l'engine è quello di default.

In base alle proprie necessità, è possibile modificare lo Storage Engine di default:

SET storage_engine=MyISAM;

Il comando precedente fa sì che lo Storage Engine MyISAM sia impostato come opzione di default. Tramite SHOW ENGINES è possibile verificare se la modifica è stata attuata.

InnoDB

InnoDB è lo Storage Engine di default di MySQL. Lo scopo di InnoDB è quello di associare maggiore sicurezza (intesa soprattutto come consistenza ed integrità dei dati) a performance elevate.

Consistenza dei dati e velocità di elaborazione spesso possono contrastare, in quanto la prima proprietà necessita di applicare opportuni lock sui dati durante le modifiche, per evitare che altre richieste vi accedano; d'altro canto, per ottenere una maggiore velocità di elaborazione è richiesto che vengano servite contemporaneamente più richieste, agevolando la concorrenza.

Nell'ottica di perseguire tali finalità, InnoDB è stato arricchito di alcune funzionalità peculiari:

• supporto alle transazioni: per transazione (concetto che sarà approfondito più avanti nella guida) si intende la possibilità di un DBMS di svolgere più operazioni di modifica dei dati, facendo sì che i risultati diventino persistenti nel database solo in caso di successo di ogni singola operazione. In caso contrario, verranno annullate tutte le modifiche apportate;
• FOREIGN KEYS: conferiscono la possibilità di creare una relazione logica tra i dati di due tabelle, in modo da impedire modifiche all'una che renderebbero inconsistenti i dati dell'altra;
• lock a livello di riga piuttosto che di tabella: questa caratteristica permette di limitare le porzioni di dati sottoposte al lock durante le modifiche. Questo genere di lock può essere di due tipi: shared (permette alla transazione che mantiene il lock di leggere la tabella) o esclusivo (la transazione che attua il lock può anche modificare o cancellare i dati);
• MVCC (Multiversion Concurrency Control) si tratta di una tecnica molto potente che permette di incrementare la concorrenza nelle interrogazioni, senza subire rallentamenti a causa dei lock applicati da altre transazioni. In pratica, permette di eseguire query su righe che, contemporaneamente, sono in fase di aggiornamento, leggendo i dati così come apparivano prima delle modifiche in corso;
• clustered indexes: sono un tipo di indici che molto spesso coincidono con la chiave primaria. In assenza di questa possono essere definiti autonomamente da MySQL. Il motivo dell'efficienza di tali indici consiste nella loro definizione all'interno dello stesso file che contiene i dati della riga.

La dimensione di una tabella InnoDB può raggiungire i 64 TB, un valore inferiore a quello ammesso da altri Storage Engine.

MyIsam

Per molto tempo MyISAM è stato lo Storage Engine di default, finché non ha dovuto lasciare il passo alle ricche caratteristiche di InnoDB. Le tabelle che usano questo Storage Engine possono raggiungere dimensioni di 256 TB e vengono salvate su disco divise in tre file differenti: uno di estensione .frm che contiene il formato della tabella, uno .MYD che contiene i dati, ed un ultimo, .MYI, che contiene gli indici.

Questo engine attualmente presenta performance ridotte a causa del lock eseguito a livello di tabella. Ciò è particolarmente evidente quando si tratta di tabelle soggette indifferentemente ad operazioni di lettura e scrittura. Al contrario, tale effetto negativo è ridotto se la tabella è read-only o comunque modificata molto raramente. Per queste ultime casistiche, MyISAM appare ancora un'opzione accettabile.

L'ampio uso fatto negli anni delle tabelle MyISAM ne ha dimostrato abbondantemente la loro affidabilità. Tuttavia, può capitare che esse risultino corrotte e, di conseguenza, non più leggibili. Ciò può accadere a seguito di circostanze particolari seppur piuttosto rare: guasti hardware, interruzioni improvvise del demone mysqld, spegnimento inatteso della macchina ospite. Tabelle MyISAM danneggiate possono essere controllate e riparate con i seguenti strumenti:

• i comandi CHECK TABLE e REPAIR TABLE, rispettivamente, per controllare e riparare la tabella. Il loro uso è possibile se il DBMS è in esecuzione;
• lo strumento myisamchk, a corredo di MySQL, da usare se il demone non è attivo.

Memory

Questo storage engine ha la caratteristica di non salvare i dati in maniera persistente su disco, ma di mantenerli in memoria. La conseguenza sarà di avere a disposizione tabelle molto veloci, ottime come supporto allo svolgimento di operazioni piuttosto lunghe. Al contrario, non è utilizzabile per il salvataggio duraturo dei dati. Il loro utilizzo si è ridotto nel tempo grazie ad un'altra caratteristica molto importante di InnoDB, che consiste nell'utilizzare come cache un buffer pool in memoria per agevolare operazioni di lettura che coinvolgono grandi quantità di dati. L'avvertenza doverosa nei confronti di Memory, che rimane comunque una tecnologia molto utile, è di non far assumere a questo tipo di tabelle dimensioni eccessive, che potrebbero penalizzare le prestazioni dell'intero DBMS.

Archive

Lo storage engine Archive risponde all'esigenza di conservare nel tempo grandi moli di dati, non più aggiornate ma archiviate solo a carattere “storico” o per eventuali analisi future (per esempio nel caso dei log di sistema). Tabelle di questo tipo permettono solo operazioni di SELECT ed INSERT e non possono avere campi indicizzati, ma hanno il vantaggio di occupare meno spazio, in quanto compresse con zlib.

TokuDB

TokuDB è un nuovo Storage Engine per MySQL/MariaDB sviluppato da Percona, creato per diventare il sostituto di InnoDB con un occhio al mondo del big data. Esso ha alcune funzionalità comuni a InnoDB, come la possibilità di modificare le tabelle dopo la creazione con nuovi indici, nonchè quella di aggiungere ed eliminare campi, il supporto pieno alle transazioni e quello a MVCC. Manca invece il supporto alle foreign keys esplicite.

La caratteristica che lo differenzia dagli altri Storage Engine è l'utilizzo di un indice ad albero frattale che permette:

• prestazioni aumentate in scrittura e lettura (fino a 20 volte superiori a InnoDB senza operazioni di tuning particolari);
• teoricamente l'eliminazione dei lag nella replicazione verso server slave;
• marcato risparmio di spazio su disco (fino ad un livello di compressione 25 volte maggiore rispetto a InnoDB).

Negli ultimi anni TokuDB sta riscuotendo molto successo, ed alcuni sistemi hanno iniziato ad includerlo di default nelle proprie distribuzioni. Ad esempio, MariaDB viene rilasciato con questo Storage Engine disabilitato ma pronto per essere abilitato dal file di configurazione.

Ulteriori storage engine

Esistono molti altri storage engine, non sempre frequenti nell'uso ma particolarmente utili se impiegati negli ambiti per cui sono stati progettati:

• CSV: memorizza le informazioni come testo, in cui ogni riga contiene i campi separati da virgole. Non sono indicizzati e vengono utilizzati per l'esportazione o l'importazione di dati che, per le operazioni comuni, sono custoditi in tabelle gestite con altri Storage Engine;
• Blackhole: è paragonabile al dispositivo /dev/null dei sistemi Unix-like. Molto utile nei test, permette di eseguire comandi per verificarne la correttezza. Non verranno inseriti dati e le query restituiranno sempre insiemi nulli;
• Merge e Federated: sono due storage engine separati rivolti entrambi alla tematica dell'aggregazione. Il primo permette di utilizzare tabelle diverse ma accomunate dalla medesima struttura, come se fossero una sola. Il secondo offre accesso unitario a più server MySQL: utile per gestire più istanze del DBMS come una sola entità logica;
• Example: è uno storage engine di esempio. Ha il solo scopo di mostrare la struttura che deve avere uno storage engine. Utile come modello per sviluppatori;
• NDB Cluster: è uno storage engine dedicato alla gestione di cluster di database, e costituisce il motore di gestione delle tabelle di dati utilizzate in MySQL Cluster.