Quali sono i vantaggi e gli svantaggi di MySQL e quando è una buona scelta?
MySQL è un sistema di gestione di database relazionali utilizzabile per servizi web tipici e per l'elaborazione di transazioni online (OLTP), in particolare quando si usa il motore di archiviazione InnoDB e funzionalità quali transazioni, locking a livello di riga e letture consistenti. D'altra parte, la replica in lettura è asincrona per impostazione predefinita e le configurazioni ad alta disponibilità, le query complesse e il partizionamento comportano vincoli di progettazione e operativi. I suoi vantaggi diventano vantaggi reali soltanto quando sono adatti al carico di lavoro e alle capacità operative del team. dev.mysql.com dev.mysql.com
Nel valutare MySQL, è più accurato considerare come vengono letti e scritti i dati, cosa deve essere garantito in caso di guasto e quanto diventerà complesso lo schema, anziché chiedersi semplicemente se sia un "database veloce". La trattazione seguente segue l'ambito della documentazione ufficiale di MySQL 8.4. Il comportamento effettivo può variare in base alla versione, al motore di archiviazione, alla configurazione e alla topologia di replica. dev.mysql.com
Che tipo di database è MySQL?
Un database relazionale archivia i dati in tabelle costituite da righe e colonne e usa il linguaggio di query SQL per gestire le relazioni tra le tabelle. Per esempio, un negozio online può usare tabelle come customers, orders e order_items per gestire le relazioni tra clienti, ordini e prodotti ordinati. In molti casi è necessario raggruppare diverse modifiche in un'unica operazione, ad esempio creare un ordine, ridurre le scorte e registrare lo stato del pagamento.
I motori di archiviazione sono importanti in MySQL. Un motore di archiviazione è un componente responsabile del modo in cui le tabelle vengono archiviate, bloccate e ripristinate. In particolare, InnoDB è il motore di archiviazione predefinito di MySQL e fornisce transazioni ACID, commit e rollback, ripristino dopo un arresto anomalo, locking a livello di riga, controllo della concorrenza multiversione (MVCC) e chiavi esterne. Di conseguenza, l'affidabilità delle transazioni comunemente associata a MySQL si riferisce spesso a MySQL che usa tabelle InnoDB configurate correttamente. dev.mysql.com
ACID è il termine collettivo per le proprietà attese delle transazioni. L'atomicità indica che un'intera operazione riesce oppure viene annullata. La coerenza indica che le regole definite per i dati vengono mantenute. L'isolamento controlla gli effetti reciproci delle operazioni eseguite contemporaneamente, mentre la durabilità indica che i risultati confermati devono sopravvivere ai guasti. Le caratteristiche ACID di MySQL sono influenzate anche dal motore, dalla configurazione, dall'hardware e dalle procedure operative; pertanto, il solo nome non deve essere inteso come una soluzione automatica per ogni scenario di errore. dev.mysql.com
Perché le transazioni InnoDB e la concorrenza sono un vantaggio?
OLTP si riferisce a carichi di lavoro con richieste frequenti e relativamente brevi, come la ricezione di ordini, l'aggiornamento delle informazioni dei membri o la modifica dello stato dei pagamenti. In questo ambiente molti utenti possono modificare contemporaneamente gli stessi tipi di dati, quindi è importante raggruppare in sicurezza le modifiche ai dati e mantenere il più ridotto possibile l'ambito dei conflitti.
Poiché InnoDB fornisce commit, rollback e ripristino dopo un arresto anomalo, un'applicazione può essere configurata per annullare una transazione se un passaggio non riesce durante, ad esempio, la creazione di un ordine e la riduzione dell'inventario. Il locking a livello di riga blocca specifiche righe quando necessario e può favorire il lavoro concorrente più del blocco esteso di un'intera tabella. Tuttavia, attese e conflitti non scompaiono quando più operazioni competono spesso per le stesse righe o per intervalli di dati adiacenti. dev.mysql.com
MVCC fornisce letture consistenti usando più versioni dei dati. Non significa semplicemente che letture e scritture non interferiscano mai tra loro. I risultati osservati e il comportamento di locking possono differire in base al livello di isolamento di una transazione, alle istruzioni SQL in esecuzione e all'uso o meno di letture con blocco. Pertanto, quando si risolvono problemi di concorrenza, non controllare soltanto il nome del motore. Definisci prima, nelle regole aziendali, quali letture devono vedere il valore più recente e quali aggiornamenti devono essere mutuamente esclusivi.
Una chiave esterna è un vincolo che aiuta a garantire che un valore in una tabella faccia riferimento a una riga esistente in un'altra tabella. Per esempio, può imporre che l'ID cliente di un ordine indichi un cliente effettivo. Ciò può contribuire a ridurre i riferimenti non validi, ma implica anche che le regole di eliminazione e aggiornamento e la struttura delle tabelle debbano essere progettate con attenzione in anticipo. Se prevedi di introdurre il partizionamento in seguito, devi inoltre verificare le restrizioni di compatibilità che riguardano le chiavi esterne. dev.mysql.com dev.mysql.com
Quali sono i vantaggi per gli ambienti di sviluppo e il controllo degli accessi?
MySQL fornisce diversi protocolli client e API per C/C++, Java, PHP, Python, Ruby e altri linguaggi. Le applicazioni che usano già questi linguaggi e strumenti hanno quindi opzioni per creare un livello di connessione e può essere relativamente semplice stabilire un percorso di base tra un'applicazione web e il database. Tuttavia, la presenza di un'API per un determinato linguaggio non garantisce di per sé che il pooling delle connessioni, i tentativi dopo errori, i set di caratteri e la gestione dei fusi orari siano configurati correttamente. L'approccio di accesso ai dati dell'applicazione deve essere convalidato separatamente. dev.mysql.com
Anche il sistema di privilegi fa parte della progettazione operativa. MySQL fornisce privilegi a livello globale, di database e di oggetto, nonché privilegi dinamici. Questo può essere usato per separare i ruoli: per esempio, assegnare a un account dell'applicazione soltanto i permessi di lettura e scrittura necessari per tabelle specifiche, usando invece account separati per backup e attività amministrative. Il principio del privilegio minimo è un utile criterio progettuale per limitare l'impatto nel caso in cui un account venga compromesso o un programma contenga un errore. dev.mysql.com
Tuttavia, rendere i privilegi più granulari non completa di per sé la sicurezza. In pratica, devi gestire quali account dispongono di quali privilegi, se gli account di amministrazione e dell'applicazione sono separati e quale processo disciplina le modifiche ai privilegi. In altre parole, le funzionalità di privilegio di MySQL forniscono meccanismi di controllo, ma la responsabilità di assegnarli in base ai ruoli aziendali resta alle attività operative.
Quali problemi risolvono gli indici e il partizionamento?
Un indice è una struttura dati progettata per ridurre la necessità di analizzare un'intera tabella per trovare le righe desiderate. Per esempio, se sono frequenti le richieste di trovare un singolo ordine tramite il numero d'ordine, un indice su tale colonna può essere utile. Gli indici multicolonna possono essere utili per query che usano insieme diverse colonne come condizioni, ma contano l'ordine delle colonne e i predicati effettivi della query. InnoDB supporta fino a 64 indici secondari per tabella e fino a 16 colonne per indice multicolonna. dev.mysql.com
Tuttavia, gli indici non migliorano automaticamente la situazione quando ne vengono creati di più. Gli indici occupano spazio di archiviazione e devono essere mantenuti anche quando le righe vengono inserite, aggiornate o eliminate. Il limite del numero di indici supportati è un limite tecnico, non un obiettivo progettuale. La necessità effettiva di un indice che abbrevia un percorso di ricerca e il carico che aggiunge ai percorsi di scrittura devono essere valutati in base a query rappresentative e alla distribuzione dei dati.
Anche gli indici di stringhe presentano vincoli fisici. Il limite del prefisso della chiave di indice InnoDB è generalmente di 3.072 byte, sebbene possa ridursi a 767 byte a seconda del formato di riga. Se cerchi di indicizzare stringhe lunghe usando un set di caratteri con una grande dimensione di archiviazione per carattere, come utf8mb4, questo limite può influire sulla progettazione dello schema. È particolarmente importante distinguere che si tratta di un limite basato sui byte, non sul numero di caratteri. dev.mysql.com
Il partizionamento è una funzionalità che archivia una tabella in più partizioni secondo regole definite. Se una condizione corrisponde alle regole di partizionamento, il pruning delle partizioni può escludere le partizioni che MySQL non deve esaminare. Per esempio, per una grande tabella storica interrogata per intervallo di date, se la tabella è partizionata per data, puoi considerare una progettazione che riduca l'intervallo di destinazione per le ricerche su un periodo specifico. dev.mysql.com
Ciò non significa che ogni tabella di grandi dimensioni debba essere partizionata. Se le condizioni usate comunemente non corrispondono alla chiave di partizionamento, la prevista riduzione dei dati di destinazione potrebbe non verificarsi. Il partizionamento introduce inoltre regole aggiuntive per operazioni, progettazione delle chiavi e vincoli; pertanto, è preferibile confrontare prima se il problema possa essere risolto con indici e miglioramenti delle query più semplici.
Quali vincoli si applicano al partizionamento e alla ricerca full-text?
In MySQL 8.4, il partizionamento è supportato dai motori di archiviazione InnoDB e NDB. Una tabella InnoDB partizionata non può avere chiavi esterne né essere la destinazione di riferimenti tramite chiave esterna da un'altra tabella. Inoltre, ogni colonna usata nella chiave di partizionamento deve fare parte di ogni chiave univoca, inclusa la chiave primaria. Questa condizione può modificare significativamente il modello quando cerchi di partizionare una tabella principale con riferimenti fitti, come una tabella degli ordini. dev.mysql.com
La ricerca full-text è una funzionalità per cercare parole nel testo. MySQL supporta la ricerca full-text con InnoDB e MyISAM, ma non è supportata nelle tabelle partizionate. Pertanto, se prevedi sia funzionalità di ricerca per documenti lunghi sia il partizionamento per dati storici su larga scala nella stessa tabella, dovresti verificare tempestivamente se tale combinazione è possibile. L'aggiunta di una funzionalità in seguito potrebbe richiedere la suddivisione delle tabelle o la modifica dell'architettura di ricerca. dev.mysql.com
Queste restrizioni non mostrano semplicemente che MySQL non dispone di funzionalità, ma che le funzionalità potrebbero non essere combinabili in modo indipendente. Puoi verificare una per una la necessità di chiavi esterne, chiavi univoche, chiavi di partizionamento e ricerca full-text. È più sicuro evitare di decidere uno schema in base al vantaggio di una sola funzionalità.
Come può essere usata la replica per scalare le letture e i backup?
La replica è un'architettura che invia le modifiche da un server a un altro. In genere, un server sorgente registra le modifiche e i server replica le applicano. Distribuire alcune richieste di lettura tra più repliche può ridurre il carico di lettura sulla sorgente, e puoi anche considerare di spostare su repliche le attività di backup o analisi. dev.mysql.com
GTID è un metodo per gestire le posizioni di replica assegnando un identificatore a ogni transazione. La replica basata su GTID può contribuire a ridurre l'onere di allineare manualmente nomi e posizioni dei file di log binari. Tuttavia, stabilire una topologia di replica è distinto dal monitoraggio del ritardo di replica e dall'operatività delle procedure di ripristino. Devi determinare quale server gestisce le scritture, quali server possono servire letture e cosa fare quando si verifica un ritardo. dev.mysql.com
La replica predefinita è asincrona. Ciò significa che, nell'istante in cui viene completato un commit sulla sorgente, non vi è garanzia che ogni replica abbia applicato la stessa modifica. Per esempio, una query indirizzata a una replica subito dopo che un utente modifica un indirizzo può mostrare l'indirizzo precedente. Questo può essere considerato un problema di coerenza read-after-write. Le richieste che devono disporre dei dati più recenti richiedono una policy che le instradi alla sorgente o che tenga conto dello stato di applicazione della replica. dev.mysql.com
La replica semi-sincrona usa un approccio in cui la sorgente riceve la conferma che una replica ha ricevuto e registrato un evento di transazione. È un'alternativa alla replica asincrona predefinita, ma non significa che ogni requisito diventi completamente sincrono. Quando si discutono requisiti sincroni forti, definisci chiaramente il livello di coerenza richiesto, l'intervallo di latenza accettabile e il comportamento in caso di guasto, quindi considera anche opzioni separate come NDB Cluster. dev.mysql.com
Group Replication risolve automaticamente l'alta disponibilità?
L'alta disponibilità è l'obiettivo di configurare un sistema affinché un servizio possa continuare quando una parte del server o della rete si guasta. Group Replication gestisce l'appartenenza al gruppo, elegge automaticamente un primario in modalità a primario singolo oppure supporta configurazioni multi-primario. La sua capacità di formare una topologia ad alta disponibilità insieme a InnoDB Cluster e MySQL Router è un'importante opzione di MySQL. dev.mysql.com
Tuttavia, il consenso tra i server di database e il failover delle connessioni dell'applicazione non sono lo stesso problema. Group Replication non include funzionalità per spostare i client interessati da un guasto verso membri sani. Le applicazioni necessitano di MySQL Router, un bilanciatore di carico, un connettore o middleware personalizzato per determinare dove connettersi, e anche quel livello deve essere gestito tenendo conto di guasti, tentativi e aggiornamenti dello stato. dev.mysql.com
Pertanto, quando senti parlare di "failover automatico", poni almeno tre domande distinte. Primo, può essere eletto un primario? Secondo, le nuove connessioni dell'applicazione vengono indirizzate a un server sano? Terzo, quali risultati vedranno le richieste in corso e quelle ritentate dall'utente? L'esistenza di una funzionalità per la prima domanda non garantisce automaticamente le altre due.
Anche una configurazione multi-primario è difficile da comprendere semplicemente come un interruttore per aumentare le prestazioni in scrittura. Quando le scritture sono consentite da più posizioni, devi progettare anche come evitare o gestire, a livello aziendale, le modifiche concorrenti agli stessi dati e quali regole deve seguire il percorso di scrittura dell'applicazione. L'alta disponibilità è un tema operativo che include non solo la selezione delle funzionalità, ma anche esercitazioni sui guasti, osservabilità e procedure di ripristino.
Perché le query complesse possono aumentare l'onere di ottimizzazione?
L'ottimizzatore è un componente che seleziona il piano di esecuzione stimato con il costo più basso tra diversi modi di eseguire un'istruzione SQL. Per esempio, determina quale indice usare per primo e l'ordine con cui unire le tabelle. L'ottimizzatore basato sui costi di MySQL può basarsi su stime quando le statistiche sono insufficienti, quindi può scegliere un piano diverso da quello atteso da una persona. dev.mysql.com
Con l'aumentare del numero di tabelle unite, il numero di piani di esecuzione candidati può crescere esponenzialmente. In tal caso, non solo il recupero dei dati, ma anche il tempo di ottimizzazione necessario per esplorare piani adeguati può diventare un collo di bottiglia. Pertanto, nei sistemi che eseguono frequentemente query analitiche complesse o molte join, è difficile stabilire l'idoneità basandosi solo sul fatto che SQL possa essere eseguito sintatticamente. I test dovrebbero usare distribuzioni effettive dei dati e condizioni rappresentative. dev.mysql.com
EXPLAIN è uno strumento per verificare il piano di esecuzione selezionato per una query. Quando i risultati sono lenti, esamina prima i predicati, le condizioni di join, gli indici usati e il conteggio stimato delle righe. Se necessario, puoi aggiornare le statistiche oppure modificare gli indici e la struttura della query. Sono disponibili anche hint per gli indici e funzionalità di controllo dell'ottimizzatore, ma gli approcci che impongono un determinato piano devono essere verificati continuamente per assicurare che rimangano validi dopo le modifiche ai dati. dev.mysql.com dev.mysql.com
Questo non significa che l'analisi complessa non possa essere eseguita in MySQL. Tuttavia, se join multi-tabella su larga scala e query analitiche sono il carico di lavoro principale, è realistico confrontare in anticipo quanto tempo puoi dedicare all'ottimizzazione, se l'analisi debba essere spostata su repliche e se aggiungere un sistema analitico dedicato. Al contrario, questo onere può essere relativamente minore per un servizio costituito principalmente da transazioni brevi e prevedibili.
Quando occorre fare attenzione alle routine memorizzate?
Le routine memorizzate sono procedure o funzioni archiviate ed eseguite sul server di database. Possono mantenere alcune regole di elaborazione dei dati vicine al database, ma le funzioni memorizzate utilizzabili nelle istruzioni SQL presentano restrizioni. Per esempio, una funzione memorizzata non può usare un'istruzione che restituisce un set di risultati. Una funzione che calcola un singolo valore di ritorno e un'operazione di query che restituisce più righe hanno scopi e modalità d'uso differenti. dev.mysql.com dev.mysql.com
Anche il determinismo delle routine memorizzate è importante negli ambienti di replica. Deterministico significa produrre lo stesso risultato a parità di input. Routine non deterministiche o dipendenti dal tempo, che variano in base al tempo o allo stato dell'ambiente, possono creare problemi di riproducibilità a seconda del metodo di replica, richiedendo particolare attenzione con la replica basata su istruzioni. Quando inserisci logica aziendale nel database, dovresti inoltre verificare se tale logica possa produrre lo stesso risultato durante la replica e il ripristino dopo un arresto anomalo. dev.mysql.com
La decisione di usare routine memorizzate riguarda meno l'esistenza di una funzionalità che la collocazione della responsabilità per modifiche, test e distribuzione. Quando le regole sono divise tra il codice dell'applicazione e le routine del database, il tracciamento e i test possono diventare più complessi. Al contrario, possono essere utili per regole semplici vicine all'integrità dei dati. La domanda chiave è se il team possa comprendere e gestire dove tali regole vengono eseguite e il loro impatto sulla replica.
Quando MySQL è una buona scelta e quando occorre cautela?
La tabella seguente non è una classifica di prodotti. È una prospettiva per verificare l'aderenza tra requisiti e funzionalità.
| Situazione | Cosa puoi considerare con MySQL | Condizioni da verificare contestualmente |
|---|---|---|
| Servizi web generali ed elaborazione di ordini o membri | Puoi usare transazioni InnoDB, locking a livello di riga, MVCC e chiavi esterne. | Devono essere progettati i confini delle transazioni e le regole per gli aggiornamenti concorrenti. |
| Servizi a prevalenza di letture | La replica sorgente-replica può separare il carico di lettura, backup e analisi. | Sono necessarie policy per il ritardo delle repliche e le letture aggiornate. |
| Servizi che richiedono una configurazione tollerante ai guasti | Puoi considerare una topologia che combina Group Replication, Router e componenti correlati. | Failover delle connessioni, tentativi e procedure di guasto devono essere gestiti separatamente. |
| Query su grandi quantità di dati storici per intervallo di date | Il pruning delle partizioni può ridurre le partizioni interessate in base alla condizione. | Verifica prima i vincoli relativi a chiavi esterne, chiavi univoche e ricerca full-text. |
| Carichi di lavoro orientati all'analisi che uniscono molte tabelle | Puoi usare l'esecuzione SQL e funzionalità di controllo degli indici e dell'ottimizzatore. | Valuta la convalida dei piani di esecuzione e il costo dell'ottimizzazione continua. |
Le prime tre righe della tabella si basano sulle funzionalità ufficiali di InnoDB, replica e Group Replication. Le ultime due righe riflettono anche il comportamento e le restrizioni del partizionamento e dell'ottimizzatore. dev.mysql.com dev.mysql.com dev.mysql.com dev.mysql.com dev.mysql.com
In particolare, se una forte coerenza multi-regione o un failover ininterrotto sono requisiti principali, non dovresti prendere una decisione basandoti solo sulla replica asincrona predefinita. Specifica i requisiti di aggiornamento dei dati, la latenza accettabile, se le scritture restano possibili durante i guasti e il percorso di failover dell'applicazione, quindi confronta Group Replication, NDB Cluster o altre opzioni distribuite. Al contrario, se vuoi elaborare in modo affidabile tipiche transazioni di lettura-scrittura all'interno di una singola area di servizio e distribuire le letture alle repliche quando necessario, la combinazione di funzionalità di MySQL può essere un punto di partenza pratico. dev.mysql.com dev.mysql.com
Cosa dovresti verificare prima dell'adozione?
Innanzitutto, verifica che le tabelle principali usino InnoDB e che i confini delle transazioni corrispondano alle unità aziendali. Le modifiche che devono riuscire o fallire insieme, come la creazione di un ordine, dovrebbero essere definite come un'unica transazione, evitando però transazioni inutilmente lunghe che aumentano la durata dei blocchi. In secondo luogo, elenca le query di lettura e scrittura più frequenti e verifica che gli indici necessari corrispondano ai predicati effettivi e al metodo di ordinamento. dev.mysql.com dev.mysql.com
In terzo luogo, se usi la replica, decidi "quali letture sono consentite dalle repliche". Un approccio consiste nel distinguere le richieste che richiedono aggiornamento dei dati, come il controllo dello stato subito dopo il pagamento, dalle query di elenco e statistiche che possono tollerare un certo ritardo. In quarto luogo, se è richiesta una configurazione ad alta disponibilità, verifica gli scenari di guasto non solo per l'elezione dei membri del database, ma anche per il punto in cui si spostano effettivamente le connessioni dell'applicazione. dev.mysql.com dev.mysql.com
In quinto luogo, presumi che i dati cresceranno e verifica se il partizionamento sia veramente necessario e se puoi accettare i vincoli delle chiavi esterne e delle chiavi univoche. Se richiedi insieme ricerca di stringhe lunghe, ricerca full-text e partizionamento, esamina prima le limitazioni tra le funzionalità. Infine, se le join complesse sono centrali, esamina EXPLAIN in condizioni vicine ai dati di produzione e valuta se hai la capacità di gestire continuamente statistiche e modifiche agli indici. dev.mysql.com dev.mysql.com dev.mysql.com
Conclusione: come valutare i pro e i contro di MySQL?
I punti di forza di MySQL includono il controllo delle transazioni e della concorrenza basato su InnoDB, l'integrazione con una vasta gamma di ambienti di sviluppo, la distribuzione delle letture tramite replica e funzionalità ufficiali per creare configurazioni ad alta disponibilità. Questi elementi possono offrire una base significativa per servizi web generali e carichi di lavoro OLTP tipici. dev.mysql.com dev.mysql.com dev.mysql.com
Allo stesso tempo, il potenziale ritardo della replica predefinita, la progettazione aggiuntiva per il failover delle connessioni in alta disponibilità, la convalida dei piani di esecuzione per query complesse e i vincoli relativi a indici, partizionamento e ricerca full-text devono essere considerati costi reali. In definitiva, MySQL non è una scelta dotata di qualità universalmente positive. È un database la cui idoneità può essere valutata quando vengono resi specifici i requisiti di coerenza dei dati, il rapporto tra letture e scritture, i vincoli dello schema, il livello di risposta ai guasti e la capacità di ottimizzazione e operativa. dev.mysql.com dev.mysql.com dev.mysql.com