ultima modifica 10 luglio 2020
In questo capitolo, parleremo dei motori di archiviazione MySQL.
Un motore di archiviazione è un modulo software che un sistema di gestione di database utilizza per creare, leggere, aggiornare i dati di un database. Ci sono due tipi di motori di archiviazione in MySQL: transazionale e non transazionale.
Per MySQL 5.5 e successivi, il motore di archiviazione predefinito è InnoDB.Il motore di archiviazione predefinito per MySQL prima della versione 5.5 era MyISAM. Scegliere il giusto motore di archiviazione è una decisione strategica importante, che avrà un impatto sullo sviluppo futuro. In questo tutorial, useremo i motori di archiviazione MyISAM, InnoDB, Memory e CSV. Se sei nuovo di MySQL e stai studiando il sistema di gestione di database MySQL, allora questo non è un problema. Se state progettando un database di produzione, allora le cose diventano più complicate.
Lista dei motori di archiviazione
MySQL supporta i motori di archiviazione:
- InnoDB
- MyISAM
- Memory
- CSV
- Merge
- Archive
- Federated
- Blackhole
- Example
InnoDB è lo storage engine più usato con supporto alle transazioni. È un motore di archiviazione conforme a ACID. Supporta il locking a livello di riga, il crash recovery e il controllo della concorrenza multi-versione. È l’unico motore che fornisce un vincolo di integrità referenziale a chiave esterna. Oracle raccomanda di usare InnoDB per le tabelle, tranne che per casi d’uso specializzati.
MyISAM è il motore di archiviazione originale. È un motore di archiviazione veloce e non supporta le transazioni. MyISAM fornisce il livellamento delle tabelle. È usato principalmente nel Web e nel data warehousing.
Il motore di archiviazione memoriale crea tabelle in memoria. È il motore più veloce. Fornisce il blocco a livello di tabella. Non supporta le transazioni.Il motore di memorizzazione in memoria è ideale per creare tabelle temporanee o ricerche rapide.I dati vengono persi quando il database viene riavviato.
CSV memorizza i dati in file CSV. Fornisce grande flessibilità perché i dati in questo formato sono facilmente integrabili in altre applicazioni.
Merge opera su tabelle MyISAM sottostanti. Le tabelle Merge aiutano a gestire grandi volumi di dati più facilmente. Raggruppa logicamente una serie di tabelle MyISAM identiche e le referenzia come un unico oggetto.
Il motore di archiviazione dell’archivio è ottimizzato per l’inserimento ad alta velocità. Comprime i dati mentre vengono inseriti. Non supporta le transazioni. È ideale per memorizzare e recuperare grandi quantità di dati storici, archiviati, a cui si fa riferimento raramente.
Il motore di archiviazione Blackhole accetta ma non memorizza dati. I recuperi restituiscono sempre un insieme vuoto. La funzionalità può essere utilizzata nella progettazione di database distribuiti in cui i dati sono automaticamente replicati, ma non memorizzati localmente. Questo motore di archiviazione può essere usato per eseguire test di prestazioni o altri test.
Il motore di archiviazione federato offre la possibilità di separare i server MySQL per creare un database logico da molti server fisici.Le query sul server locale vengono eseguite automaticamente sulle tabelle remote (federate). Nessun dato viene memorizzato sulle tabelle locali.È buono per ambienti distribuiti.
mysql> SHOW ENGINES\G*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 2. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO...
Il comando SHOW ENGINES mostra tutti i motori disponibili che il server supporta.
Scegliere il motore giusto
Nessun motore di archiviazione è ideale per tutte le circostanze. Alcuni funzionano meglio in determinate condizioni e funzionano peggio in altre situazioni. Ci sono dei compromessi che devono essere considerati. Una soluzione più sicura richiede più risorse; potrebbe essere più lenta, richiedere più tempo di CPU e spazio su disco. MySQL è molto flessibile nel fatto che fornisce diversi motori di archiviazione. Alcuni di essi, come il motore di archiviazione, sono creati per essere utilizzati in situazioni specifiche.
In alcuni casi la risposta è chiara. Ogni volta che abbiamo a che fare con alcuni sistemi di pagamento, siamo obbligati a usare la soluzione più sicura. Non possiamo permetterci di perdere dati così sensibili. InnoDB è la strada da percorrere. Se vogliamo una ricerca a tutto testo, allora possiamo scegliere MyISAM o InnoDB. Solo InnoDB supporta il vincolo di integrità referenziale a chiave esterna e se abbiamo intenzione di usare questo vincolo, allora la scelta è chiara.
Specificare e modificare gli storage engine
Lo storage engine è specificato al momento della creazione della tabella.
mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), -> Cost INTEGER) ENGINE='MyISAM';
La parola chiave ENGINE specifica lo storage engine usato per questa particolare tabella.
Se non si specifica esplicitamente il motore di archiviazione, viene utilizzato il motore di archiviazione predefinito. Prima di MySQL 5.5 lo storage engine di default era MyISAM. Per MySQL 5.5 e successivi, lo storage engine predefinito è InnoDB.
È possibile migrare ad un diverso motore di archiviazione. Si noti che la migrazione di una grande tabella potrebbe richiedere molto tempo. Inoltre potremmo incorrere in alcuni problemi durante la migrazione delle tabelle. Alcune caratteristiche potrebbero non essere supportate in entrambe le tabelle.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| InnoDB |+--------+1 row in set (0,05 sec)
Questa istruzione SQL trova il motore di archiviazione usato per una tabella Cars nel database mydb. Potremmo anche usare lo statement SQL SELECT CREATE TABLE Cars. La information_schema è una tabella che memorizza informazioni tecniche sulle nostre tabelle.
mysql> ALTER TABLE Cars ENGINE='MyISAM';
Questa istruzione SQL cambia il motore di memorizzazione della tabella Cars in MyISAM.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| MyISAM |+--------+1 row in set (0,00 sec)
Ora il motore di memorizzazione della tabella è MyISAM.
In questa parte del tutorial MySQL, abbiamo coperto i motori di memorizzazione.