È 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:
$res = @mysql_query("SELECT catid, nome FROM prodotto WHERE id = 2");
$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:
SELECT * FROM tab
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:
SELECT campo1, campo2, campo5 FROM tab
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:
SELECT campi FROM tabella LIMIT [inizio, ] fine
Esempio: LIMIT 5 (i primi cinque record), LIMIT 2, 3 (i primi tre record a partire dal secondo)
Nota bene: questa clausola è alla base del funzionamento dei sistemi di paginazione, presenti in molti siti web, che suddividono un gran numero di contenuti in varie pagine con un menu per passare alle pagine successive.
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)
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 per quanto riguarda l'ottimizzazione delle query.
Nota Bene: è consigliabile dotare le proprie tabelle di indici numerici che rispecchino le clausole WHERE più usate al fine di velocizzarne l'esecuzione. Per analizzare una query e scoprire quali campi potrebbero essere utilizzati come indici è possibile far precedere la query dalla keyword 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() recupera un solo campo di un solo record alla volta
- mysql_fetch_*() funzioni che recuperano un intero record e lo memorizzano in maniere diverse a seconda della funzione specifica
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:
- mysql_fetch_row() memorizza il record in un array avente indici numerici da 0 al numero di campi meno uno
- mysql_fetch_assoc() memorizza il record in un array avente come indici i nomi dei campi
- mysql_fetch_array() memorizza il record in un array avente entrambi i tipi di indici
- mysql_fetch_object() memorizza il record come un oggetto avente per variabili interne i campi resituiti
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: imposta il timeout dato alle connessioni. Dopo questo valore espresso in secondi il server chiude la connessione.
- key_buffer_size: imposta la memoria da riservare al buffer degli indici delle tabelle. Per rendere il sistema più veloce dare a questo parametro un valore abbastanza alto senza eccedere. Un valore eccessivo infatti fa sì che il sistema tenda ad usare la swap e quindi si ottenga il risultato opposto a quello desiderato.
- long_query_time: imposta il tempo massimo oltre al quale una query viene considerata lenta e registrata in caso di presenza del parametro "--log-slow-queries" passato allo script di avvio. Conviene attivare questa funzionalità per avere un'idea delle query che converrebbe ottimizzare ulteriormente.
- max_sort_length: imposta il numero di bytes da considerare quando impostiamo un ordinamento per un campo di tipo testuale. Conviene impostare questo parametro con un valore abbastanza basso al fine di non rendere troppo dispendiosa l'esecuzione delle query. (Dove possibile poi sarebbe molto meglio progettare le query per non avere questo tipo di ordinamento).
- max_user_connections: imposta il numero massimo di connessioni contemporanee consentite ad un utente. È molto utile nei sistemi in cui allo stesso server MySQL hanno accesso più utenti.
- query_cache_limit: imposta la dimensione massima dei risultati da inserire in cache (Da mysql4).
- query_cache_size: imposta la quantità di memoria destinata al caching dei risultati delle query. Se impostato a 0 la funzione è disabilitata (default). Conviene sempre abilitare questa funzionalità dato che l'aumento di prestazioni è sensibile. (Da mysql4).
- query_cache_type: imposta il tipo di caching, disattivato (0), sempre attivo tranne quando richiesto diversamente con le query di tipo "SELECT SQL_NO_CACHE" (1), attivo solo quando richiesto con le query di tipo "SELECT SQL_CACHE" (2).
- sort_buffer_size: imposta la dimensione del buffer destinato all'implementazione delle clausole ORDER BY e GROUP BY. Per rendere le query più performanti conviene impostare questo parametro ad un valore non troppo basso (Di default impostato a 2Mb).
- thread_cache_size: imposta il numero di thread da mantenere in cache. Questa funzionalità rende l'uso di thread molto performante.
Per un elenco esaustivo è possibile fare riferimento direttamente alle pagine del manuale MySQL.
