È ormai stabile e affermata a livello l'associazione di MySQL e PHP: praticamente ogni hoster che offre supporto per questo linguaggio lato server permette l'utilizzo di uno o più database su MySQL. Anche per quanto riguarda gli scripts questo è vero: molti di essi infatti si appoggia ad un rdbms, quasi sempre MySQL.
Sulla maggior parte dei server di produzione in questo momento è presente la versione 3.23.x di MySQL, versione a cui la Mysql AB non lavora più. La serie consigliata è infatti la 4.0.x (l'ultima versione rilasciata è la 4.0.17). Questa nuova serie dell'rdbms mantiene la caratteristica velocità che ha sempre contraddistinto questo software tra i suoi concorrenti aggiungendo però funzionalità molto interessanti come il caching delle query (capace secondo stime della stessa MySQL AB di incrementare la velocità di esecuzione fino al 50%).
Anche per quanto riguarda le versioni in sviluppo (4.1.x in stato alpha e 5.0.0 ancora in un primo sviluppo) sono già emerse (se non addirittura implementate) caratteristiche da aggiungere al fine di migliorare il supporto per gli standard e realizzare un prodotto di fascia medio/alta: tra queste spiccano il supporto per le subquery (4.1) e per le stored procedures (5.0).
Visto che quasi sicuramente ci troveremo a dover utilizzare questa accoppiata, perché non utilizzarla al meglio ottimizzandone i vari aspetti? Cercherò di trattare questo argomento sotto differenti punti di vista: dalla struttura di una query al fine di velocizzarne l'esecuzione, alle funzioni PHP migliori per il recupero dei record, al tuning della parte lato server per ottimizzare l'utilizzo di risorse.
Usare MySQL da PHP
L'interazione tra PHP e MySQL è molto semplice (così come in genere l'interazione con molti rdbms) e si articola in linea di massima in 6 fasi:
- Connessione all'host su cui è in esecuzione il server
- Selezione del database su cui agire
- Esecuzione della query
- Recupero dei record restituiti
- Liberazione delle risorse impegnate dalla query
- Chiusura della connessione
Le ultime due fasi possono anche essere omesse dato che il PHP si occupa comunque di liberare risorse e chiudere le connessioni attive al termine dell'esecuzione dello script. Ora ci interesseremo prevalentemente della creazione delle query e del recupero dei dati; se siete interessati alle altre fasi potete trovare informazioni nella guida dedicata
Ottimizzazione delle query
Il linguaggio PHP è molto veloce, quando riscontriamo tempi di esecuzione estremamente lunghi oppure un uso eccessivo di memoria potremmo scoprire che il collo di bottiglia si trova proprio al livello dell'interazione con il database e spesso per una progettazione sbagliata delle query.
Diminuire il numero di query
La regola fondamentale consiste nel lasciare eseguire tutto il lavoro possibile al server MySQL liberando il carico del PHP facendo il minor numero possibile di query.
Esempio: Abbiamo due tabelle, categoria (id, nome) e prodotto (id, catid, nome) e vogliamo ricavare il nome di un prodotto e della categoria di appartenenza. L'errore sarebbe fare qualcosa di simile:
$catid = @mysql_result($res, 0, 'catid');
$nome = @mysql_result($res, 0, 'nome');
@mysql_free_result($res);
$res = @mysql_query("SELECT nome FROM categoria WHERE id = " . $catid);
Come si può capire però in questo caso abbiamo eseguito due query e sprecato una gran quantità di tempo tenendo conto che possiamo ottenere lo stesso risutato con una sola query:
SELECT prodotto.nome AS prodotto, categoria.nome AS categoria FROM categoria, prodotto WHERE prodotto.catid = categoria.id
Una delle poche situazioni in cui è consigliabile non rispettare questa regola è quella in cui dobbiamo contare il numero di record che restituirebbe una query se non fosse presente la clausola LIMIT, nel qual caso è meglio eseguire un'altra query in cui usare la funzione MySQL
COUNT()
SELECT COUNT(1) FROM tabella
Se invece non abbiamo utilizzato LIMIT possiamo risalire al numero di record restituiti per mezzo della funzione PHP mysql_num_rows()
Specificare i campi richiesti
Una delle regole basilari per velocizzare l'esecuzione di una query (e diminuirne l'utilizzo di risorse) consiste nello specificare esattamente i campi interessati dalla nostra richiesta omettendo tutti i campi privi di interesse.
Esempio: Dalla tabella tab (campo1, campo2, campo3, campo4, campo5, campo6) dobbiamo ricavare i campi campo1, campo2, campo5. L'errore più comune consiste nell'eseguire una query del genere:
Così facendo però occupiamo risorse non solo per i dati che ci interessano, bensì per tutti i campi della tabella. La query corretta allora sarebbe:
Immaginate quale possa essere il risparmio di risorse se ad esempio i campi ignorati fossero stati campi TEXT o peggio ancora con contenuto binario.
Uso della clausola LIMIT
Altro trucchetto per risparmiare risorse consiste nell'usare la clausola LIMIT all'interno delle query. Se ad esempio in una pagina dobbiamo visualizzare massimo 5 record, è assolutamente inutile fare una query in cui vengono chiesti tutti i record. Basta aggiungere LIMIT:
Esempio
Nota bene
Uso della clausola WHERE
Ci sono molti altri espedienti per velocizzare le query, o meglio l'elaborazione da parte di MySQL delle stesse, ad esempio analizzare nel dettaglio la clausola WHERE. La prima cosa da fare è eliminare tutte le parentesi superflue, ricordandosi che l'ordine di priorità degli operatori AND e OR prevede come prioritario il primo:
WHERE a = 2 OR (b = 3 AND c = 5)
WHERE (a = 2 OR b = 3) AND c = 5
In questo caso la prima e la seconda si equivalgono (quindi possiamo eliminare le parentesi dalla seconda) metre la terza ha tutt'altro significato.
Il manuale inoltre consiglia di utilizzare il maggior numero possibile di "costanti". Ad esempio una clausola WHERE del genere:
WHERE tab1.a = 5 AND tab2.b = tab1.a AND tab2.c = 6
può essere riscritta in maniera più efficiente in questo modo:
WHERE tab1.a = 5 AND tab2.b = 5 AND tab2.c = 6
È molto importante dotare le tabelle di un campo id di tipo numerico ed usare quello per identificare i record univocamente. Questo perché per MySQL è più semplice confrontare costanti numeriche piuttosto che stringhe. Se poi questo campo è di definito PRIMARY la query viene velocizzata anche dal fatto che questo viene considerato dal motore di MySQL alla stregua di una costante.
Il manuale di MySQL riporta una serie di consigli
Nota Bene EXPLAIN
Recuperare i record trovati
Analizziamo velocemente anche questo aspetto dell'interazione tra PHP e MySQL. Ci troviamo a disposizione due diverse tipologie di funzioni per il recupero dei record:
- mysql_result()
- mysql_fetch_*()
La funzione mysql_result() è utile nei casi in cui ci troviamo ad avere un unico campo selezionato e un unico record restituito (come ad esempio il caso della conta del numero di record interessati da un'istruzione) oppure quando abbiamo bisogno di scorrere l'insieme dei risultati non in maniera lineare (cioè non vogliamo accedere prima al primo record, poi al secondo ecc. fino all'ultimo in questo preciso ordine).
La seconda serie di funzioni comprende:
Il mio consiglio è quello di utilizzare mysql_fetch_assoc(), per ottenere maggiore chiarezza all'interno del codice e rendere il tutto indipendente dall'ordine dato ai campi nella query, dove possibile. Usare mysql_fetch_row() in caso di utilizzo combinato con list(). Non usare affatto mysql_fetch_array().
Tuning del server MySQL
Se amministriamo un server MySQL (online o in locale è indifferente) abbiamo a disposizione due maniere diverse per impostare i parametri con cui questo funzionerà e quindi per renderlo più funzionale alle nostre necessità: passare i parametri direttamente allo script di avvio di mysqld (mysqld_safe per mysql4 e safe_mysqld per mysql3) oppure creare un file my.cnf in una directory di sistema (c:windows per windows e /etc/ per linux). Con i sorgenti di MySQL vengono rilasciati anche alcuni esempi di file my.cnf per vari carichi di lavoro, in genere essi sono pienamente sufficienti per le nostre esigenze, ma è sempre meglio sapere che cosa stiamo facendo, quindi ecco alcune variabili con il loro significato.
- connect_timeout
- key_buffer_size
- long_query_time
- max_sort_length
- max_user_connections
- query_cache_limit
- query_cache_size
- query_cache_type
- sort_buffer_size
- thread_cache_size
Per un elenco esaustivo è possibile fare riferimento direttamente alle pagine del manuale MySQL.
