Il database è la memoria del nostro sito internet o applicativo e ci permette di offrire contenuti dinamici in tempo reale ai nostri utenti.
Ottimizzare il database del proprio sito internet è un’operazione estremamente complessa e condizionata da tante variabili.
Se gestiamo applicazioni o siti che devono supportare numerosi accessi simultanei oppure gestiamo una base di dati che nel tempo ha assunto una elevata complessità (nell’ordine di qualche centinaia di migliaia di righe) è possibile avere un degrado nelle prestazioni, che di solito può essere risolto generalmente ottimizzando il DB.
Di seguito una breve guida su come effettuare una prima ottimizzazione di MySQL:
La prima operazione che possiamo effettuare consiste nel verificato il server e la sua configurazione.
Usanto il comando “SQL SHOW VARIABLES” ci sarà permesso di visualizzare i valori di tutte le variabili in uso sul server (sebbene non tutte abbiano un’influenza diretta sulle prestazioni).
Le prime da prendere in considerazione per quanto riguarda l’ottimizzazione sono key_buffer_size e table_cache: la prima rappresenta la quantità di spazio di memoria che viene utilizzata da MySQL per tenere in memoria i valori degli indici delle tabelle MyISAM, in modo da limitare gli accessi al disco (può essere impostato intorno al 25% del totale della memoria per una macchina su cui MySQL è l’applicazione principale); la seconda invece indica il numero di tabelle che il server può mantenere aperte contemporaneamente. Una volta arrivato a questo numero, MySQL dovrà chiudere una tabella quando ha la necessità di aprirne un’altra.
Una pratica che può permettere di risparmiare tempo in lettura su tutte le istruzioni inviate al server è quello di utilizzare un sistema semplice di permessi: quindi, evitare completamente di attribuire permessi a livello di tabella o di colonna, e limitarsi a dare permessi sui database. Infatti, se le tabelle tables_priv e columns_priv del database mysql non contengono dati, MySQL non dovrà andare ogni volta a verificare i permessi su di esse.
Il secondo livello di ottimizzazione riguarda la struttura delle basi di dati, cioè il modo in cui vengono progettate le tabelle. Vediamo qualche best practices:
le tabelle MyISAM sulle quali vengono effettuati frequenti aggiornamenti sono più veloci se non hanno righe a lunghezza variabile (quindi se hanno lunghezza fissa, il sistema sa bene cosa aspettarsi); naturalmente va tenuto presente che usare righe a lunghezza fissa può avere la controindicazione di sprecare spazio, per cui bisogna fare una valutazione su quale dei due aspetti è prioritario;
le tabelle MyISAM possono rivelarsi piuttosto lente nel caso in cui abbiano frequenti aggiornamenti e siano lette da query lente; in questo caso è bene considerare la possibilità di cambiare storage engine;
cerca inoltre di limitare al minimo l’occupazione di spazio, in quanto ciò consente la lettura di quantità maggiori di dati con un solo accesso al disco: in altre parole cercate sempre di valutare qual è il campo più piccolo adattabile ai vostri dati e non utilizzatene uno più grande (ad esempio, per scrivere valori interi, il campo MEDIUMINT occupa 3 byte mentre INT ne occupa 4: usare un MEDIUMINT al posto di un INT comporta un risparmio del 25%); inoltre cerca di dichiarare le colonne “NOT NULL”, in modo da risparmiare lo spazio occupato dai valori NULL;
altra best practices è di far sì che la chiave primaria di una tabella sia più corta possibile, in maniera tale da rendere più immediata l’identificazione di una riga
nell’ottimizzazione la parte che riguarda gli indici è uno tra i fattori più importanti: infatti influenzano l’accesso ai dati, più sono ottimizzate più questo sarà veloce; in particolare le colonne che fanno riferimento ad altre tabelle (chiavi esterne) e quelle utilizzate per le ricerche dalle query devono essere sempre indicizzate; tuttavia bisogna considerare che la presenza di indici velocizza la lettura ma rallenta la scrittura (gli indici infatti vanno tenuti aggiornati), per cui è importante trovare il giusto equilibrio fra le due esigenze;
se dovete indicizzare dei campi di testo, sarebbe bene limitare il numero di caratteri inclusi nell’indice; ad esempio in un campo di 50 caratteri, se già i primi 10 sono sufficienti ad avere un range di valori ben distinti fra loro, indicizzare solo questi primi caratteri comporterà un rilevante risparmio sulle dimensioni dell’indice;
se dovete memorizzare dati binari (come ad esempio le immagini), è un ottima cosa salvarli su disco e non sul database, limitandosi ad inserire in tabella un riferimento al filesystem; questo consente una maggiore velocità di accesso al file.
Un ultimo livello di ottimizzazione, non meno importante degli altri, è quello che riguarda la lettura dei dati: infatti una query può essere più o meno veloce (a volte con differenze anche notevoli), in base alla strategia scelta da MySQL per eseguirla.
Altro argomento chiave e complesso per velocizzare le nostre query MYSQL è l’ottimizzazione di queste ultime.
Un primo strumento utilizzabile per valutare la velocità di una query è la funzione BENCHMARK, usata dal client mysql:
SELECT BENCHMARK(100000,’query‘);
Questa istruzione ci permette di ripetere una query un determinato numero di volte: tale numero è indicato dal primo parametro e la query che vogliamo ripetere come secondo parametro. L’istruzione non darà risultato ma il client MySQL ci mostrerà dopo ogni istruzione il tempo che ha impiegato ad eseguirla; in questo modo potremo valutare l’impatto di eventuali modifiche sulla struttura della query. Generalmente si consiglia di usare numeri piuttosto grandi (almeno 100.000, ma anche maggiori) per avere tempi nell’ordine dei centesimi di secondo; il tempo ovviamente dipende dalla complessità della query e dalla velocità del processore che utilizziamo.
Una volta eseguito il BENCHMARK, per valutare l’efficienza di una query è consigliato eseguire l’istruzione EXPLAIN, che ci permette di visualizzare i criteri utilizzati da MySQL per la sua esecuzione:
EXPLAIN [EXTENDED] SELECT …
Interpretare l’output di questa istruzione non è semplice ma potrete usare questa guida.
Per avere query più veloci, di solito, dovremo far sì che tutte le tabelle interessate vengano lette attraverso gli indici piuttosto che con uno scorrimento completo (table scan); in alcune situazioni può capitare che MySQL non utilizzi un indice anche se esiste: infatti se reputa più veloce scorrere la tabella, un tipico fattore di scelta è il fatto che la colonna indicizzata non abbia una quantità sufficiente di valori diversi.
Altrimenti può succedere perchè il server non ha statistiche aggiornate sul contenuto della tabella: possiamo allora aggiornarle eseguendo la query: ANALYZE TABLE nome_tabella.
Un modo di “suggerire” a MySQL di utilizzare un indice è quello di aggiungere la clausola FORCE INDEX nome_indice di seguito al nome della tabella nella SELECT.
Inoltre un’ottima pratica quando confrontiamo due campi indicizzati consiste nel far sì che i due indici siano dello stesso tipo e della stessa lunghezza: questo permette al server di massimizzare le prestazioni.
E’ possibile usare accorgimenti per velocizzare le operazioni di inserimento dei dati, in particolare quando dobbiamo inserire più righe alla volta.
Ad esempio:
e’ possibile eseguire una sola INSERT per più righe, utilizzando VALUES multipli;
se si devono caricare dati da file di testo, utilizzare l’istruzione LOAD DATA INFILE;
se un’istruzione deve effettuare più di cinque inserimenti, effettuare un LOCK sulla tabella interessata; se il numero di inserimenti è molto elevato (numerose migliaia), sbloccare le tabelle ogni migliaio di righe inserite per consentire ad altri client di accedervi senza costringerli ad attese troppo lunghe.
Inoltre dobbiamo tenere presente che, quando effettuiamo numerosi aggiornamenti o cancellazioni su una tabella, lo spazio su disco occupato da questa tabella può diventare male organizzato (in particolare per le tabelle con righe a lunghezza variabile); a questo punto è bene eseguire un’ottimizzazione della tabella attraverso l’istruzione OPTIMIZE TABLE nome_tabella.
Con questo abbiamo concluso una prima visione sull’ottimizzazione dei database MySQL; se non si è ancora soddisfatti dei risultati ottenuti è possibile approfondire sulla guida del manuale MySQL o rivolgersi a un sistemista professionista per una consulenza e/o preventivo.
