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 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; 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. 12); - 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. 10) 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.