My SQL

LEZIONI - L'OTTIMIZZAZIONE

L'ottimizzazione nell'uso di un database è un argomento estremamente complesso, in quanto è condizionato da una notevole quantità di variabili. Esistono concetti che sono applicabili in generale alle basi di dati relazionali, e altri che sono specificamente relativi ad un certo RDBMS, in dipendenza delle sue caratteristiche. Naturalmente finchè le nostre applicazioni sono di dimensioni limitate, sia come quantità di dati, sia come numero di utenti che accedono al database, difficilmente noteremo problematiche di questo genere.

Se invece ci troviamo a gestire applicazioni che devono supportare numerosi accessi simultanei (ad esempio siti web che riscuotono un notevole successo) oppure basi di dati che assumono una notevole consistenza (nell'ordine almeno delle centinaia di migliaia di righe, il che può succedere anche per applicazioni con pochi utenti) potrà capitarci di avere un degrado più o meno forte nelle prestazioni, che di solito può essere risolto (o perlomeno limitato) ottimizzando alcuni aspetti dell'applicazione o della base dati, o in alcuni casi della configurazione del server.

In questa lezione faremo alcuni accenni all'ottimizzazione di MySQL, alla quale è dedicato un intero capitolo del manuale.

Il primo livello di ottimizzazione al quale possiamo guardare è quello relativo al server e alla sua configurazione. Come abbiamo già visto nella lezione 4, sono molto numerose le variabili che influiscono sul funzionamento di MySQL. L'amministratore del server ha la possibilità di impostarne i valori attraverso i file di configurazione, oppure con opzioni al momento dell'avvio, o ancora modificandole a server attivo.

L'istruzione SQL SHOW VARIABLES ci consente di visualizzare i valori di tutte le variabili in uso sul server (sebbene non tutte abbiano un'influenza diretta sulle prestazioni). Ovviamente sarebbe molto lungo spiegare il significato di tutte le variabili; inoltre è necessario tempo anche per abituarsi a valutare l'impatto di ciascuna di esse sul funzionamento del server. Ci limiteremo quindi a dire che 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. Raggiunto questo numero, MySQL dovrà chiudere una tabella ogni volta che ha la necessità di aprirne un'altra.

Un accorgimento che può consentire di risparmiare tempo su tutte le istruzioni inviate al server è quello di utilizzare un sistema semplice di permessi: in sostanza, 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 (vedere lez.6) 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 suggerimento:

• le tabelle MyISAM sulle quali vengono effettuati frequenti aggiornamenti sono più veloci se non hanno righe a lunghezza variabile (vedere lez. 9); naturalmente dovete tenere 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 (vedere lez. 8);
• cercate di limitare al minimo l'occupazione di spazio, perchè questo consente al server di leggere maggiori quantità di dati con un accesso al disco: di conseguenza valutate sempre qual è il campo più piccolo adattabile ai vostri dati e non utilizzatene uno più grande (ad esempio, per valori interi, un campo MEDIUMINT occupa 3 byte mentre un INT ne occupa 4: quindi se non vi servono più di 16 milioni di valori usare un MEDIUMINT invece che un INT comporta un risparmio del 25%); inoltre cercate di dichiarare sempre le colonne NOT NULL, in modo da risparmiare lo spazio necessario all'indicatore dei valori NULL: quanto meno, dichiarate che una colonna può essere NULL solo se ne avete realmente bisogno;
• la chiave primaria di una tabella dovrebbe essere più corta possibile, per rendere più immediata l'identificazione di una riga
• gli indici (vedere lez. 12) sono il fattore forse più importante nell'ottimizzazione di una tabella: sono infatti indispensabili per avere letture veloci; in particolare, le colonne che fanno riferimento ad altre tabelle (chiavi esterne) e quelle utilizzate per le ricerche dalle query dovrebbero 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 campi di testo, sarebbe bene limitare il numero di caratteri inclusi nell'indice; se ad esempio avete un campo di 50 caratteri, ma già i primi 10 sono sufficienti ad avere un range di valori ben distinti fra loro, indicizzare solo questi 10 comporterà un rilevante risparmio sulle dimensioni dell'indice;
• quando dovete memorizzare dati binari (ad esempio immagini), è consigliabile salvarli su disco e non sul database, limitandosi ad inserire in tabella un riferimento al filesystem: questo dovrebbe consentire una maggiore velocità
• i dati che fanno parte di una tabella dovrebbero essere in terza forma normale, ci sono casi in cui può essere conveniente accettare ridondanze, quando questo comporta significativi miglioramenti nelle prestazioni

Un terzo livello di ottimizzazione, non meno importante degli altri, è quello che riguarda l'accesso ai dati: una query infatti può essere più o meno veloce (a volte con differenze anche notevoli), in base alla strategia scelta da MySQL per eseguirla.

Anche l'ottimizzazione delle query è un argomento piuttosto complesso. Se notate che alcune query sono piuttosto lente, un primo strumento utilizzabile per valutarle è la funzione BENCHMARK, usata dal client mysql:

SELECT BENCHMARK(100000,'query');

Questa istruzione ci permette di eseguire una query un numero arbitrario di volte: indicheremo tale numero come primo parametro e la query che vogliamo vedere come secondo parametro. Come risultato dell'istruzione non otterremo niente, ma il client MySQL ci mostra dopo ogni istruzione il tempo che ha impiegato ad eseguirla: in questo modo potremo valutare l'impatto di eventuali modifiche sulla struttura della query. In genere è necessario usare numeri piuttosto grandi (almeno 100.000, ma spesso anche maggiori) per avere tempi valutabili nell'ordine dei centesimi di secondo; ovviamente questo dipende dalla complessità della query e dalla velocità del processore che utilizziamo.

Il secondo passo da fare per valutare l'efficienza di una query è l'utilizzo della EXPLAIN, che ci permette di visualizzare i criteri utilizzati da MySQL per la sua esecuzione:

EXPLAIN [EXTENDED] SELECT ...

A questa pagina del manuale potete trovare una dettagliata spiegazione di come interpretare l'output di questa istruzione.
In generale, per avere query più veloci dovremo far sì che tutte le tabelle interessate vengano lette attraverso gli indici, e non attraverso uno scorrimento completo (table scan); in alcune situazioni può capitare che MySQL non utilizzi un indice che pure esiste: a volte infatti considera più veloce scorrere la tabella, ad esempio perchè ritiene che la colonna indicizzata non abbia una quantità sufficiente di valori diversi. Può capitare però che questo avvenga perchè il server non ha statistiche aggiornate sul contenuto della tabella: possiamo allora aggiornarle eseguendo una 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.
Ricordiamo anche che quando confrontiamo in una query due campi indicizzati è bene che i due indici siano dello stesso tipo e della stessa lunghezza: questo permette al server di massimizzare le prestazioni.

Accorgimenti si possono utilizzare anche per velocizzare le operazioni di inserimento dei dati, in particolare quando dobbiamo inserire più righe alla volta.

Ad esempio:

• eseguire una sola INSERT per più righe, utilizzando VALUES multipli (vedere lez. 13);
• se si devono caricare dati da file di testo, utilizzare l'istruzione LOAD DATA INFILE (il guadagno di velocità è notevole);
• se un'elaborazione deve effettuare più di cinque inserimenti, precederli con un LOCK sulla tabella interessata (oppure includerli in una transazione se si usa uno storage engine transazionale); se il numero di inserimenti è molto elevato, sbloccare le tabelle ogni migliaio di righe inserite per consentire ad altri client di accedervi senza costringerli ad attese troppo lunghe.

Oltre a questi accorgimenti, 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): è bene quindi eseguire periodicamente un'ottimizzazione della tabella attraverso l'istruzione OPTIMIZE TABLE nome_tabella.

Con questo abbiamo concluso questa rapida carrellata sull'ottimizzazione: come abbiamo detto all'inizio, però, l'argomento è ben più complesso, per cui vi invitiamo ad approfondirlo sul manuale di MySQL.