Indice dei contenuti dell'articolo:
Se stai cercando di migliorare le prestazioni dei database MySQL nel tuo software, potresti dover conoscere tutte le differenze tra InnoDB e MyISAM, due noti tipi di motori di archiviazione MySQL. E se stai per sceglierne uno, crediamo che ti piacerebbe scoprire cosa ognuno di essi ha da offrire e quale si adatta meglio al tuo software.
Ogni aspetto tecnico determina il successo di un progetto. Con la crescente necessità di archiviare tutti i dati, la scelta di un database in grado di controllare con successo tutti i requisiti di archiviazione anche a lungo termine è della massima importanza. Quando a un Solution Architect viene presentata la responsabilità di scegliere un database, confronta tali database l’uno con l’altro e sceglie quello che si adatta meglio.
In questo blog parleremo dei due motori di archiviazione più popolari dei database MySQL: InnoDB e MyISAM. Discuteremo cosa sono e capiremo MyISAM vs InnoDB confrontandoli con 7 fattori critici.
Capire cos’è MySQL
MySQL è un popolare RDBMS open source (Relational Database Management System)
È ampiamente utilizzato da accademici e professionisti di tutto il mondo. MySQL è disponibile gratuitamente con licenza pubblica GNU ed è anche noto come versione proprietaria premium. Michael Widenius ha originariamente sviluppato MySQL presso MySQL AB, una società con sede in Svezia. Nel 2012, Sun Microsystems ha acquisito MySQL AB e successivamente Oracle ha acquisito Sun Microsystems.
MySQL viene utilizzato per varie applicazioni ed è basato su SQL (Structured Query Language). Viene anche utilizzato da alcuni dei siti Web popolari, tra cui Twitter, Facebook, Mediawiki, YouTube e Flickr, ecc.
MySQL ha avuto molti fork di cui i più popolari sono MariaDB e Percona Server.
Comprendere le caratteristiche principali di MySQL
- Facilità d’uso: MySQL è facile da scaricare, installare e facile da usare. Puoi semplicemente andare ai download dal sito Web ufficiale e seguire le istruzioni dettagliate fornite nella loro documentazione.
- Scalabilità: MySQL ha un’architettura scalabile e fornisce utilità di caricamento rapido ad alte prestazioni con diverse cache di memoria.
- Compatibilità: MySQL è compatibile e può essere installato su tutte le piattaforme moderne come Windows, Linux, Unix.
- Sicurezza dei dati: MySQL ha tutte le funzionalità che garantiscono la sicurezza dei database e consente l’accesso solo agli utenti autorizzati.
- Basso costo: è gratuito. Chiunque da tutto il mondo può scaricare gratuitamente MySQL per uso personale.
Cos’è MyISAM?
MyISAM è l’abbreviazione di My Indexed Sequential Access Method; MyISAM è il sistema di archiviazione predefinito ed è spesso utilizzato in ambienti Web, data warehousing e altri analitici. Il motore di archiviazione MyISAM supporta tutte le configurazioni MySQL.
MyISAM era un motore di archiviazione predefinito fino a dicembre 2009; in seguito, InnoDB ha sostituito il motore di archiviazione predefinito. MyISAM si basa sull’algoritmo ISAM che visualizza più rapidamente il risultato di set di dati più grandi. MyISAM ha un footprint di dati minimo e quindi è più adatto per il data warehousing e le applicazioni web.
Cos’è InnoDB?
InnoDB è un motore di archiviazione sicuro per le transazioni (conforme ad ACID) per MySQL con funzionalità come Commit, Rollback e funzionalità di ripristino da crash per proteggere i dati degli utenti e fornire tolleranza agli errori. InnoDB utilizza il blocco a livello di riga e il non blocco coerente in stile Oracle sulle letture aumenta la concorrenza e le prestazioni multiutente.
InnoDB archivia i dati degli utenti in indici cluster basati su chiavi primarie per ridurre l’I/O per le query comuni. Per mantenere l’integrità dei dati, InnoDB supporta anche i vincoli di integrità referenziale FOREIGN KEY.
Con l’introduzione di MySQL 5.5, InnoDB è diventato il motore di archiviazione predefinito ed è più adatto per set di dati di grandi dimensioni contenenti dati strutturati e relazionali. InnoDB si concentra maggiormente sull’affidabilità e sulle prestazioni, rendendolo ottimo per i sistemi di gestione dei contenuti.
MyISAM vs InnoDB
Ora che abbiamo una conoscenza di base di MyISAM e InnoDB, confrontiamoli su vari fattori:
- MyISAM vs InnoDB: tipo di motore di archiviazione
- MyISAM vs InnoDB: blocco
- MyISAM vs InnoDB: chiavi esterne foreign key ed integrità referenziale
- MyISAM vs InnoDB: proprietà ACID
- MyISAM vs InnoDB: prestazioni
- MyISAM vs InnoDB: affidabilità
- MyISAM vs InnoDB: memorizzazione nella cache e indicizzazione
1. MyISAM vs InnoDBstorage: tipo di motore
MyISAM è un tipo di archiviazione non transazionale e qualsiasi opzione di scrittura deve essere ripristinata manualmente (se necessario).
InnoDB è un tipo di archiviazione delle transazioni che esegue automaticamente il rollback delle scritture se non vengono completate.
2. MyISAM vs InnoDB storage: locking
Il locking è il meccanismo in MySQL che impedisce a due utenti di modificare le righe duplicate contemporaneamente bloccando la riga. Gli utenti non possono modificare la tabella quando il locking è abilitato.
MyISAM utilizza il metodo predefinito di locking della tabella e consente a una singola sessione di modificare la tabella. Ciò significa che solo un utente alla volta può modificare la tabella. Se un altro utente prova a cambiare la tabella, riceverà un messaggio che dice che è bloccata. Il metodo di locking delle tabelle è utile per i database di sola lettura poiché non richiede molta memoria.
InnoDB utilizza il locking a livello di riga della tabella. Questo metodo supporta più sessioni sulla stessa riga bloccando solo le righe nel processo di modifica. Il locking delle righe è utile per i database con più utenti.
L’unico svantaggio del blocco a livello di riga è che consuma molta memoria e l’esecuzione di query e la modifica dei dati richiede tempo.
Quando una query viene eseguita su una tabella MyISAM, l’intera tabella in cui viene eseguita la query verrà bloccata. Ciò significa che le query successive verranno eseguite solo al termine di quella corrente. Se stai leggendo una tabella di grandi dimensioni e/o ci sono frequenti operazioni di lettura e scrittura, ciò può significare un enorme arretrato di query.
Quando una query viene eseguita su una tabella InnoDB, solo le righe coinvolte sono bloccate, il resto della tabella rimane disponibile per le altre operazioni. Ciò significa che le query possono essere eseguite contemporaneamente sulla stessa tabella, a condizione che non utilizzino la stessa riga.
3. MyISAM vs InnoDB storage: chiavi esterne o foreign key ed integrità referenziale.
Una chiave esterna è una colonna in una tabella che collega i dati a un’altra tabella. Impedisce agli utenti di aggiungere record che distruggono il collegamento tra due tabelle.
MyISAM non supporta l’opzione Chiave esterna.
InnoDB supporta l’opzione Chiave esterna.
L’integrità referenziale garantisce che le relazioni tra le tabelle rimangano coerenti. O in termini più generali una tabella ha una chiave esterna che punta a una tabella diversa. Quando viene apportata una modifica alla tabella puntata, le modifiche verranno limitate/collegate anche alla tabella di collegamento.
InnoDB è un DBMS relazionale (RDBMS) e quindi ha integrità referenziale, mentre MyISAM no. Quindi InnoDB supporta le chiavi esterne e l’integrità referenziale, comprese le eliminazioni e gli aggiornamenti in cascata, ma MyISAM non supporta i vincoli di chiave esterna.
Quindi, quando stai progettando un database MySQL e devi definire i vincoli di integrità tra le tabelle, InnoDB è la tua tazza di caffè. (Naturalmente, puoi avere il motore MyISAM per le tabelle di cui non hai bisogno per definire alcun vincolo di integrità referenziale).
4. MyISAM vs InnoDB storage: proprietà ACID
ACID sta per Atomicità, Consistenza, Isolamento e Durabilità. MyISAM non supporta le proprietà ACID mentre InnoDB supporta le proprietà ACID.
I dati in una tabella vengono gestiti utilizzando istruzioni DML (Data Manipulation Language), come SELECT, INSERT, UPDATE e DELETE. Una transazione raggruppa due o più istruzioni DML in un’unica unità di lavoro, quindi viene applicata l’intera unità o nessuna.
MyISAM non supporta le transazioni mentre InnoDB lo fa.
Pertanto, se una tabella utilizza il motore MyISAM e l’operazione viene interrotta, l’operazione viene interrotta immediatamente e le righe (o anche i dati all’interno di ciascuna riga) interessate rimangono interessate, anche se l’operazione non è stata completata. Se una tabella utilizza il motore InnoDB e l’operazione viene interrotta, poiché utilizza transazioni che hanno atomicità, qualsiasi transazione che non è stata completata non avrà effetto poiché non viene effettuato alcun commit.
Quando si esegue un’operazione in MyISAM, le modifiche vengono impostate e non è possibile ripristinare le modifiche mentre si è in InnoDB, è possibile eseguire il rollback di tali modifiche.
Quando lavori su un’applicazione che utilizza molte transazioni, InnoDB sarebbe un’opzione migliore di MyISAM.
InnoDB fornisce anche il ripristino automatico dopo un arresto anomalo del server MySQL o dell’host su cui viene eseguito il server.
5. MyISAM vs InnoDB storage: prestazioni
InnoDB supporta proprietà transazionali, cioè rollback e commit, e ha una maggiore velocità di scrittura. Le prestazioni di InnoDB per grandi volumi di dati sono migliori rispetto a MyISAM.
MyISAM non supporta le proprietà transazionali ed è più veloce da leggere. Rispetto a InnoDB, le prestazioni per un volume elevato di dati sono inferiori.
6. MyISAM vs InnoDB storage: affidabilità
InnoDB utilizza un log transazionale per registrare ogni operazione e quindi fornisce operazioni affidabili. Pertanto, in caso di errore, i dati possono essere recuperati rapidamente utilizzando quei registri.
MyISAM non offre integrità dei dati; guasti hardware e operazioni annullate possono causare il danneggiamento dei dati.
7. MyISAM vs InnoDB storage: memorizzazione nella cache e indicizzazione
InnoDB non supporta l’indicizzazione full-text fino alla versione 5.6.4 di MySQL. Sebbene sia una versione vecchia, alcune applicazioni utilizzano ancora le stesse o versioni precedenti di MySQL (in particolare i provider di hosting condiviso). Tuttavia, questo non è un motivo valido per utilizzare MyISAM. È meglio passare a un provider di hosting che supporti le versioni aggiornate di MySQL come il nostro.
Inoltre una tabella MyISAM che utilizza l’indicizzazione FULLTEXT non può essere convertita in una tabella InnoDB.
Come verificare se stai utilizzando MyISAM o InnoDB
Se non sai quale motore di archiviazione è attualmente utilizzato nel tuo database MySQL, controlliamolo e vediamo. Il primo modo è avviare MySQL Shell ed eseguire il comando, quindi individuare e selezionare il database richiesto con il comando.SHOW DATABASES;USE database_name;
Successivamente, verrà mostrato un messaggio di conferma che il database si trova. È ora possibile utilizzare il comando SHOW CREATE TABLE per visualizzare le informazioni sulla tabella e sul motore di archiviazione. Si presenta come segue:
SHOW CREATE TABLE database_name.table_name;
L’output mostrerà il motore di archiviazione predefinito per la tabella richiesta.
C’è un modo più semplice per farlo se stai usando PhpMyAdmin per MySQL, basta osservare le varie tabelle.
Quando è meglio usare MyISAM e quando dovresti usare InnoDB
Se una tabella MyISAM ha un problema, riguarderà solo la tabella. Se si verificano problemi con altre tabelle o database, non influiranno sulle operazioni di altre tabelle o database. Pertanto, MyISAM può essere consigliato per server con più siti.
Il motore di archiviazione InnoDB è consigliato per operazioni frequenti con le tabelle. Ha prestazioni migliori, funzionalità migliori e può anche ridurre sostanzialmente l’utilizzo della memoria sul server. Naturalmente, è generalmente preferito a MyISAM. Tuttavia, vale la pena notare che, poiché i problemi con le tabelle InnoDB possono causare la perdita di dati in altre tabelle InnoDB, è importante assicurarsi di aver abilitato i backup automatici.
Andando oltre la mera logica teorica o accademica per l’utilizzo in un contesto web come può essere l’utilizzo in un hosting WordPress, o un ecommerce come Prestashop o Magento è praticamente sempre consigliato usare InnoDB al posto di MyISAM.
Come convertire MyISAM in InnoDB
La conversione da MyISAM a InnoDB può essere piuttosto utile se, ad esempio, incontri una tabella MyISAM precedente che deve essere convertita per corrispondere al tuo attuale ambiente InnoDB.
Come convertire tutte le tabelle MyISAM in InnoDB
Se vuoi convertire tutte le tabelle in un database richiesto, puoi farlo in sicurezza tramite phpMyAdmin.
1. Una volta avviato e selezionato il database richiesto, è possibile eseguire la seguente query su di esso, dopo aver sostituito database_name con il nome effettivo del database:
SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND table_schema = 'database_name';
Ad esempio, questo è come apparirà con il database di sakila:
2. Dopo aver inviato questa query, vedrai l’elenco di tutte le tabelle da convertire in InnoDB.
3. Fare clic su +Opzioni sopra i risultati, selezionare Testi completi e fare clic su Vai nell’angolo inferiore destro dello schermo.
4. Selezionare la casella di controllo Mostra tutto e copiare tutte le query utilizzando Copia negli appunti nella casella Operazioni sui risultati della query .
5. Incolla i risultati nell’editor di testo e copia tutte le righe che iniziano con ALTER TABLE negli appunti.
6. Fare clic sulla scheda SQL sopra i risultati e incollare le istruzioni ALTER TABLE nel campo di testo, quindi fare nuovamente clic su Vai . Tutte le tabelle nel tuo database verranno convertite in InnoDB.
Come convertire una singola tabella MyISAM in InnoDB
Se vuoi convertire una singola tabella da MyISAM a InnoDB, esegui il comando ALTER TABLE, dopo aver sostituito database_name e table_name con il database e i nomi delle tabelle effettivi:
ALTER TABLE database_name.table_name ENGINE=InnoDB;
Conclusione
Questo articolo ha elencato le differenze critiche tra InnoDB e MyISAM confrontandole con fattori critici. È importante essere consapevoli delle proprietà di ciascun database perché diventa più facile fare una scelta. Dovresti essere consapevole delle somiglianze che possiedono e anche di ciò che li differenzia. Si spera che dopo aver letto questo articolo tu abbia una migliore comprensione di InnoDB e MyISAM.
Se come immaginiamo sei in questo blog in quanto stai lavorando con CMS o piattaforme ecommerce popolari come WordPress / Drupal / Joomla / Prestashop o Magento, il consiglio più spassionato che possiamo darti per migliorare le performance del tuo sito è quello di convertire tutte le tabelle MyISAM ad InnoDB.
Qualora incontrassi difficoltà o volessi delegare un’operazione a prima vista macchinosa o pericolosa, contattaci pure.
