Come ottimizzare MySQL per migliorare le prestazioni del database

Introduzione

MySQL è un applicativo per database molto famoso perchè è in grado di memorizzare e mostrare dati strutturati in una maniera molto efficace per la comprensione delle relazioni che intercorrono tra essi.

I database tendono a immagazzinare anche enormi quantità di dati, al punto da iniziare ad accusare alcune problematiche nelle loro performance.

Fortunatamente esistono diversi approcci per velocizzare le prestazioni del tuo database, permettendogli di resistere a questi cambiamenti con l'eventuale aumento della mole di dati salvata al suo interno. Alcuni di questi metodi ti verranno spiegati nel seguente tutorial.

Pre-Requisiti

Per seguire questo tutorial sarà necessario:

• Un sistema operativo Linux-based, come AlmaLinux o Ubuntu, con MySQL installato su di esso
• Un database esistente, da ottimizzare
• L'accesso come amministratore sia al sistema operativo che al database in questione.

Migliorare le prestazioni di MySQL a livello di sistema

In questa sezione ti verrà spiegato come intervenire, lato hardware e software, sul tuo sistema col fine di migliorare le prestazioni generali.

Hardware

La parte hardware è un primo punto da cui si può partire per migliorare le prestazioni del database. Componenti più di qualità aiutano ad evitare il cosiddetto "collo di bottiglia", fenomeno definito in informatica per spiegare come il flusso dei dati, seppur veloce, a volte sia costretto a un rallentamento proprio legato alla scarsa efficienza di un singolo componente.

Meno "colli di bottiglia" saranno presenti tra le componenti preposte allo scambio di informazioni, più sarà prestante generalmente il tuo sistema e, di conseguenza, anche le elaborazioni dei database.

Memoria

La maggior parte delle macchine esistenti sfruttava gli hard disk (HDD), il cui funzionamento dipende da una testina che ruota su un disco magnetico.

Gli SSD, o solid state drives non si basano invece su questo movimento fisico e, di conseguenza, offrono una velocità di scambio dei dati maggiore e migliori performance generali, oltre che un guadagno sulla silenziosità della macchina.

Sostituire il proprio hard disk con un SSD è quindi un ottimo consiglio per potenziare le prestazioni del tuo sistema e del tuo database.

Processori

Usando il comando top di Linux, puoi individuare i processi in esecuzione sulla tua macchina e la quantità di risorse che essi consumano, anche in termini di utilizzo della CPU.

Migliorare il proprio processore, seppur costoso, aiuta sensibilmente le prestazioni garantite per la gestione dei processi e per l'elaborazione dei dati sul tuo database.

Memoria

Un database, così come la tua macchina, necessità di una quantità di memoria volatile (RAM) da dedicargli durante l'elaborazione dei dati. In MySQL, è possibile decidere quanta RAM dedicargli per permettergli una esecuzione più performante.

È bene, ovviamente, non dedicare quasi tutta la memoria complessiva a disposizione poiché ciò potrebbe compromettere il funzionamento del tuo sistema.

Rete

La mole di dati viaggia sulla rete, la quale deve garantire una bassa latenza e pochi pacchetti persi per essere reputata affidabile e performante.

Motore del DB

Il motore del database è una componente software che è alla base delle operazioni di creazione, lettura, aggiornamento e cancellazione dei dati sul database. Molti sistemi di gestione di database offrono anche una GUI (Graphic User Interface) per consentire una migliore interazione coi dati all'interno del database.

Inizialmente, MySQL si poggiava sul motore MyISAM, che si mostrava abbastanza veloce e performante. Recentemente, però, il motore è stato sostituito da InnoDB, un suo simile che si è rivelato essere più performante, possedendo una buona gestione delle transazioni e il supporto alle chiavi esterne, caratteristica molto usata nei databases per mettere in relazione dati appartenenti a tabelle differenti.

Il motore del database è solitamente selezionabile in fase di creazione di un nuovo DB.

Versione di MySQL

Tenere MySQL sempre aggiornato aiuta a ricevere sempre gli ultimi miglioramenti generali. Ciò, ovviamente, avrà influenza su diverse caratteristiche come, ad esempio, affidabilità e prestazioni.

Migliorare le prestazioni di MySQL a livello software

Un altro approccio per migliorare l'efficienza del tuo database è quello di migliorare la velocità delle query su di esso.

Ci sono diversi tools e tecniche che ti spiegheremo per intervenire sulle prestazioni del tuo DB.

Usare dei tool automatici

Ci sono diversi tools che ti aiuteranno a migliorare in automatico l'efficienza e le prestazioni del tuo database.

Il primo tool che ti consigliamo è tuning-primer: esso è un vecchio tool, nato per MySQL 5.5-5.7, che ti permetterà di analizzare il tuo database e ti fornirà dei suggerimenti per migliorarne le prestazioni, modificando la configurazione del DB.

tuning-primer può essere scaricato come file .sh su GitHub(dove troverai anche maggiori indicazioni su come utilizzarlo) o può essere disponibile tramite il comando:

$ curl -L https://raw.githubusercontent.com/BMDan/tuning-primer.sh/master/tuning-primer.sh | bash

Il secondo tool che ti suggeriamo è MySQLTuner, uno script utile per analizzare i "colli di bottiglia" nelle configurazioni del tuo database. Interagendo con i file di log del tuo database, potrai vedere un resoconto dell'analisi sul tuo terminale, oltre a poter visionare consigli generali di miglioramento delle performance del DB.

Anche il file di questo script è presente nelle repository di GitHub, assieme alle indicazioni su come utilizzarlo e come interpretare le informazioni ricevute dalle analisi.

L'ultimo tool che ti consigliamo è phpMyAdmin Advisor. Anche esso può analizzare il tuo database e fornire raccomandazioni per sistemare la configurazione e renderla più efficace. Il tool è utilizzabile anche direttamente dal pannello grafico del gestionale phpMyAdmin.

Ottimizzare le query

Le query sono richieste inviate al DBMS sotto forma di istruzioni specifiche. Le richieste mirano a ricevere indietro un set di risultati che corrispondano ai filtri e all'ordinamento richiesto.

Ci sono, tuttavia, query più efficaci e query meno efficaci poiché , a seconda delle funzioni o dei filtri che verranno specificati, la complessità computazionale potrebbe aumentare o diminuire.

È possibile concatenare, infatti, diverse condizioni in una query per creare query via via più complesse. Ti mostreremo, però, come snellire la ricerca dei risultati concatenando bene le condizioni o trovando istruzioni alternative.

Usare gli indici

Gli indici permettono di snellire l'estrapolazione di risultati della tabella grazie all'associazione di essi alle righe.

Un esempio di indice più usato in MySQL è la chiave primaria, concetto fondamentale nei database per individuare univocamente e rapidamente una certa riga.

Ricorrere agli indici nelle istruzioni di SELECT aiuta a snellire le tempistiche di estrapolazione dei risultati.

Evitare le funzioni nei predicati

Ogni istruzione MySQL, per aggiungere delle condizioni, presenta dei predicati.

SELECT * FROM Students WHERE ....;

Dopo il WHERE, potrai specificare diverse condizioni più o meno complesse, aiutandoti anche con operatori logici e funzioni più sofisticate.

Usare delle funzioni in un predicato, tuttavia, rallenta l'effettiva estrapolazione dei dati poichè prima di produrre il risultato, dovrà attendere la corretta esecuzione delle operazioni previste nella funzione. Cerca di usarne il meno possibile, salvo che sia strettamente necessario.

Evitare i carattery jolly nei predicati

I caratteri jolly o Wildcards permettono di specificare delle condizioni un po' meno strette nella estrapolazione dei dati.

Per comprendere come un carattere jolly viene utilizzato, guarda l'esempio:

SELECT * FROM Students WHERE name LIKE "M%";

In questo caso, il database restituirà tutti i dati sugli studenti che abbiano il nome che inizi con la lettera M.

Oppure:

SELECT * FROM Students WHERE name LIKE "%a";

In questo caso, il database restituirà tutti i dati sugli studenti che abbiano il nome che termini con la lettera a.

• Nel primo caso, la ricerca non sarà completamente indicizzata e il maggior spettro di valori ammessi aumenterà le tempistiche di elaborazione dei risultati.
• Nel secondo, gli indici verranno completamente ignorati, ricorrendo a una ricerca sull'intera tabella. Anche ciò, ovviamente, aumenterà i tempi richiesti per l'elaborazione.

Per i motivi spiegati, quindi, è bene che tu usi il meno possibile caratteri jolly per la ricerca di un set di risultati.

Specificare le colonne nella SELECT

L'istruzione SELECT * è quella più "ampia" nella estrapolazione dei dati. Essa, infatti, stampa tutte le colonne di un set di righe (o risultati) appartenenti a una tabella.

Non sempre, però, potresti davvero aver bisogno di ricevere proprio tutti i campi della tabella. In tal caso, è bene specificare solo quelli di cui hai bisogno per ridurre i tempi di elaborazione.

Nell'esempio in cui si devono ricercare tutti i nomi e cognomi degli studenti registrati nel DB, al posto del comando:

SELECT * FROM Students;

Ricorri invece a:

SELECT name, surname FROM Students;

Usare correttamente l'ORDER BY

L'espressione ORDER BY permette di ordinare i risultati della query in base ai valori di una colonna o di un insieme di colonne. L'ordinamento può essere ASC (Ascendente) o DESC (Discendente).

Nell'esempio, la query richiederà i nomi e cognomi degli studenti registrati, ordinandoli prima in base al cognome e poi in base al nome, partendo sempre dalla A alla Z.

SELECT name, surname FROM Students ORDER BY surname, name ASC;

Una particolarità degli ordinamenti è che puoi applicare lo stesso a tutte le colonne o, per ogni colonna, specificare un ordinamento diverso.

Nell'esempio, la query richiederà i nomi e cognomi degli studenti registrati, ordinandoli prima in base al cognome dalla A alla Z e poi in base ai nomi, dalla Z alla A.

SELECT name, surname FROM Students ORDER BY surname ASC, name DESC;

Salvo casi di necessità, è bene adottare un unico ordinamento per tutte le colonne considerate, così da snellire i costi computazionali di elaborazione dei risultati.

Usare la GROUP BY invece del SELECT DISTINCT

L'istruzione SELECT DISTINCT serve a evitare righe duplicate nel risultato della query.

I tempi di elaborazione della SELECT DISTINCT, tuttavia, sono superiori rispetto alla direttiva GROUP BY, la quale è anche capace di aggregare in maniera meno ambigua i dati.

Prendiamo una tabella contenente, per ogni studente, il suo nome e cognome.

Su questo esempio, ipotizzando una query svolta su tale tabella studenti, possiamo osservare come, nel primo caso, alcuni nomi vengano ripetuti e compaiano in maniera più disordinata nei risultati della query.

SELECT DISTINCT nome,cognome FROM studenti;

Nel secondo caso, gli elementi vengono aggregati e, seppur con l' apparente perdita di righe, i tempi di esecuzione osservabili sono più veloci e il record di risultati è effettivamente privo di duplicati.

SELECT nome,cognome FROM studenti GROUP BY nome;

Nel caso della GROUP BY, è possibile inoltre combinarla con delle funzioni di aggregazione che permettano di contare, per esempio, il numero di occorrenze di un certo nome presente nella tabella. In questo modo ridurremo le ridondanze e lo spreco di risorse come nel SELECT DISTINCT ma non perderemo le informazioni necessarie ad avere una visione chiara dei dati immagazzinati nel db (es:quante volte si ripete quel nome in tabella?).

Risulta, quindi, più conveniente il combinare una funzione GROUP BY con altre piuttosto che ricorrere alla SELECT DISTINCT, che potrebbe produrre risultati ridondanti.

Nell'esempio, si è fatto ricorso alla funzione COUNT() per mostrare, di ogni nome diverso, il numero di volte in cui esso compare nella tabella.

SELECT nome,cognome,COUNT(nome) FROM studenti GROUP BY nome;

Come potrai osservare, non ci saranno ridondanze ma nemmeno perdita dei dati, in quanto il risultato della query mostrerà i nomi senza duplicati ma specificherà quante occorrenze di quel nome siano presenti nella tabella da te interrogata.

In conclusione, ottimizza le tue query preferendo sempre la GROUP BY alla SELECT DISTINCT.

Padroneggiare le JOIN

La JOIN è una delle operazioni più utilizzate nel database per combinare delle tabelle in un risultato unico che includa tutte le informazioni che ti possano servire.

La logica della JOIN è trovare, appunto, delle CONGIUNZIONI tra i valori di tabelle differenti. Queste congiunzioni vengono appunto usate per mostrare questi valori associati alla stessa riga.

Per entrare più a fondo dell'argomento, immaginiamo di avere una tabella contenente i dati degli studenti (nome, cognome e matricola) e averne un'altra con i dati degli esami sostenuti all'interno dell'università (data di svolgimento, voto, matricola dello studente che lo ha svolto).

Ecco le tabelle da considerare:

• Studenti:
  
• Esami:
  

Una JOIN ti aiuterà, per esempio, a visualizzare le informazioni sugli studenti che abbiano svolto almeno un esame.

SELECT studenti.matricola, nome,cognome,materia,data FROM studenti JOIN esami ON studenti.matricola = esami.matricola

Questa tabella altro non sarà che la fusione della tabella studenti e di quella esami, seguendo il criterio del dover solo visualizzare le corrispondenze tra i numeri di matricola registrati tra gli studenti e nel registro degli esami.

Un modo equivalente di descrivere una JOIN, in questo caso una INNER JOIN, è quello di usare la clausola WHERE, come in esempio:

SELECT studenti.matricola, nome,cognome,materia,data FROM studenti,esami WHERE studenti.matricola = esami.matricola

Sebbene equivalenti, a seconda del motore del DB, i tempi di esecuzione potrebbero essere leggermente diversi.

Ci sono anche delle operazioni insiemistiche che, ai fini del non intaccare l'efficienza, occorrerebbe evitare in MySQL, salvo casi strettamente necessari. È il caso, per esempio, di UNION e DISTINCT.

Riconfigurare le impostazioni di MySQL Server

Per ottimizzare l'efficienza di MySQL Server, occorrerà che tu cambi alcuni valori all'interno del file my.cnf, situato nel percorso /etc/mysql/.

Presta attenzione a eseguire un cambiamento alla volta, piuttosto che stravolgere la configurazione e rischiare dei malfunzionamenti.

Ecco allora, su cosa potresti intervenire:

• query_cache_size : specifica la quantità di memoria cache da dedicare alle query in esecuzione. Incrementa gradualmente i valori da 10MB a 100/200MB ma evita di andare oltre poiché potresti creare problemi di gestione della memoria e sperimentare dei blocchi.
• max_connection : numero di massime connessioni consentite al database. Incrementare il valore consente di non incappare in erorri dovuti a un limite troppo basso di connessioni a fronte di numerosi client che vogliono accedere al database.
• innodb_buffer_pool_size : questa impostazione permette di sfruttare la RAM di sistema per fornire più memoria cache al tuo database. Gestisci con parsimonia questa impostazione, tenendo conto della RAM necessaria all'esecuzione degli altri applicativi sul tuo sistema.
• innodb_io_capacity : questa variabile definisce la velocità della tua memoria secondaria. Cambia questo valore in base al tuo HDD o SSD per ottimizzare la corrispondenza con il tuo hardware.

Conclusioni

Ora sai come migliorare da diversi aspetti le prestazioni del tuo database. Potrai procedere, quindi, effettuando delle migliorie a livello hardware o software.

Potrai anche scrivere query più efficaci per evitare inefficienze durante l'elaborazione dei dati.