Una query scritta male può mettere a dura prova il server più potente o il database più performante, oltre ovviamente a rendere il sito o l'applicazione inusabile o quasi per gli utenti. A volte una piccola latenza nella risposta del database, magari appena percettibile in fase di testing, diventa un serio problema in produzione, quando vi saranno migliaia di richieste contemporanee.
Alcuni piccoli accorgimenti posso quindi riverlarsi determinanti.
L'articolo è riferito nello specifico a MySQL in ambiente LAMP, ma nei concetti generali può essere valido per qualsiasi database.
1. Progetta con cura
Il primo passo è ovviamente quello della progettazione del database. È necessario concepire il database come un'entità complessa e interdipendente, dove ogni tabella è legata alle altre da precise regole, anche solo formali, come la scelta dei nomi, e dove i dati nel complesso sono sottoposti ad una severa normalizzazione e tipizzazione.
I peggiori database sono quelli che nascono per apposizione di elementi, senza un progetto ben preciso alle spalle o con tentativi di seguire strade differenti; quelli insomma dove NodeID in una tabella corrisponde al nid di un'altra, o dove tutti campi sono int(10) o varchar(255).
Per riassumere: progettate sempre con un obiettivo ben preciso e attenetevi alle decisioni prese in questa fase!
2. Testa le tue query
Durante lo sviluppo dell'applicazione è necessario testare la qualità delle query, singolarmente e per gruppi quando, come nel caso di una pagina web, agiscono insieme. Provate le query con EXPLAIN e date un occhio alle risorse del server; anche strumenti di benckmarking per Apache come ab possono essere utili per testare indirettamente come si comporta il database. Non dimenticate invece di loggare le slow query: molto spesso basta leggere questo log per risolvere il problema.
3. Usa la cache
La query più veloce è quella che non c'è. Un sistema di caching tipo Memcached può dare un po' di respiro al server in situazioni critiche, magari a fianco al sistema di cache nativo di MySQL che dovrebbe essere sempre attivo.
4. Una SELECT selettiva!
L'errore più comune (e chi non lo fa, alzi la mano!):
SELECT * FROM Rubrica
Bisogna sforzarsi sempre di specificare i campi che servono:
SELECT Nome,Cognome,eMail,CAP FROM Rubrica
e risparmiare un sacco di lavoro al database.
5. Usa LIMIT
Un altro errore molto comune è quello di non usare LIMIT, costringendo il database ad estrarre o verficare un numero molto elevato di record, mentre potrebbe fermarsi molto prima.
SELECT Nome,Cognome,eMail FROM Rubrica WHERE eMail = "info@domain.tld" LIMIT 1
SELECT Titolo,Abstract,Data FROM BlogPost WHERE Public = 1 ORDER BY DATA DESC LIMIT 10
6. Fuori dal loop
Alcuni paradigmi di programmazione vorrebbero una query interna ad un ciclo. Per esempio:
foreach($ids as $id){
mysql_query("SELECT * FROM Nodes WHERE NodeID = '".$i."'");
}
che invece, con un notevole risparmio di risorse, può agevolmente diventare:
$ids = implode(",",$ids);
mysql_query("SELECT * FROM Nodes WHERE NodeID IN (".$ids.")");
7. JOIN e non subquery
Le sub-query sono comode, ma costano un salasso! Molto meglio una query più lunga, forse meno immediata da leggere, ma con JOIN come costrutto.
SELECT Groups.GroupID, Groups.NodeID, (SELECT Nodename FROM Nodes WHERE Nodes.NodeID = Groups.NodeID) AS Name FROM Groups;
può diventare:
SELECT Groups.GroupID, Groups.NodeID, Nodes.Nodename AS Name FROM Groups INNER JOIN Nodes WHERE Groups.NodeID = Nodes.NodeID;
8. Piano con le wildcard
Anche le wildcard sono strumenti potenti, ma i database in genere e nello specifico MySQL non se la cavano benissimo con il matching di stringhe, quindi sarebbe meglio non abusare di espressioni tipo:
SELECT NodeID,Nodename FROM Nodes WHERE Lastname LIKE "%ossi%"
9. UNION invece di OR
Una tipica query con OR
SELECT * FROM PostBlog WHERE PostID < 11 OR PostID > 20
può essere più efficace se scritta come:
SELECT * FROM PostBlog WHERE PostID < 11 UNION SELECT * FROM PostBlog WHERE PostID > 20
10. Usa INDEX!
Gli indici permettono di recuperare un'informazione con molta più efficacia di un confronto uno ad uno (immaginate di orientarvi in una biblioteca senza catalogo o di cercare un nome nell'elenco telefonico se fossero organizzati in ordine sparso).
Aiutate il vostro database con un indice ogni volta che questo è possibile.
