Ottimizzare le query di MySQL

Utilizzare la corretta sintassi nella creazione di istruzioni SQL permette di ottenere in output risultati più precisi e veloci risparmiando nello stesso tempo le preziose risorse messe a disposizione dal server.

In questo e in un secondo articolo tratteremo alcuni casi esemplificativi su come scegliere la giusta sintassi a seconda delle query che intendiamo sottoporre al DBMS MySQL.

Un utilizzo efficiente delle condizioni

Nella digitazione delle istruzioni SQL vi sono alcune regole che attengono al buon senso e che non richiedono vaste capacità tecniche per essere comprese. Per esempio, salta immediatamente all'occhio la differenza che intercorre tra una query formulata in questo modo:

mysql> SELECT * FROM tbl WHERE id = 22;

e un'altra di questo genere:

mysql> SELECT nome FROM tbl WHERE id = 22;

Se abbiamo necessità di conoscere tutti i record della colonna "nome" relativi ad un determinato Id, non avremo certo bisogno di coinvolgere all'interno della query tutti i campi che compongono la struttura di una tabella.

Eppure l'osservanza di questa semplice norma viene spesso dimenticata, si pensi per esempio alla creazione di script in PHP dove per l'estrazione di pochi dati vengono creati array inutilmente sovrappopolati.

Ma torniamo alla nostra query e analizziamo un aspetto meno palese del precedente. Prediamo in considerazione la causa WHERE utilizzata per delimitare l'output dell'interrogazione; se invece di scrivere:

 WHERE id = 22;

scrivessimo.

 WHERE id = '22';

cambierebbe qualcosa ai fini dell'ottimizzazione? Sì.

È buona norma infatti utilizzare all'interno delle condizioni termini di confronto omogenei, ciò significa che il valore introdotto come condizione deve appartenere allo stesso tipo di dato della colonna sulla quale avviene il confronto.

Nel primo caso del nostro esempio abbiamo infatti un confronto tra tipi numerici interi; nel secondo caso vi sarà invece un confronto tra un tipo numerico e una stringa, MySQL sarà quindi costretto ad operare una conversione da stringa a numero inutile e dispendiosa se ripetuta frequentemente.

Uno sguardo agli operatori

Il discorso appena affrontato riguardo alla clausola di confronto WHERE può essere esteso anche ad operatori di frequente utilizzo, soprattutto nelle fasi di ricerca ed estrazione dei dati. Prendiamo per esempio LIKE, molto utilizzato per il pattern-matching di stringhe, e inseriamolo in un'interrogazione come la seguente:

 mysql> SELECT nome FROM tbl WHERE nome LIKE = 'ma%';

La wildcard % posta alla destra della sottostringa da ricercare ci permette di riassumere in pochi caratteri una query del tutto equivalente alla seguente:

 mysql> SELECT nome FROM tbl WHERE nome >= 'ma' AND  < 'mb';

Grazie ad essa riusciamo a delimitare in modo efficace l'ambito di ricerca limitandolo esclusivamente ai record in grado di soddisfare la richiesta.

Un fattore fondamentale per ottimizzare le query è infatti l'eliminazione di tutti gli output non necessari, condizione che abbiamo già analizzato consigliando di sostituire il carattere jolly * con il nome dei campi effettivamente necessari.

Stesso discorso può essere affrontato prendendo il considerazione la clausola LIMIT; se infatti non desideriamo visualizzare più di un certo numero di record, o siamo a conoscenza del fatto che il record cercato si trova all'interno di un determinato range, sarà opportuno introdurre un LIMIT in modo da evitare di scorrere inutilmente l'intero contenuto della tabella.

La clausola LIMIT è importante anche per un altro motivo, in determinati casi consente al sistema di terminare l'esecuzione di una query in anticipo rispetto ad un'interrogazione che ne è priva.

Anche in questo caso il buon senso suggerisce giusti comportamenti; prendiamo per esempio il caso del motore di ricerca interno di un sito Web, poniamo di ordinare i risultati delle ricerca in senso decrescente rispetto all'attinenza con la key di input: quale utente prolungherebbe la consultazione oltre le prime pagine di risultati? Plausibilmente abbastanza pochi da giustificare l'eccessivo carico dovuto a query non limitate.

Non solo SELECT: ottimizzare le query INSERT

Quanto detto finora sulle selezioni può essere esteso senza particolari modifiche alle operazioni di DELETE o UPDATE (con un po' di cautela in più naturalmente). Anche nelle query per l'inserimento di nuovi dati l'unione tra buon senso e sintassi corretta possono portare ad ottimi risultati in termini di ottimizzazione e di uso efficiente delle risorse.

Di norma il primo consiglio utile è quello che suggerisce di non utilizzare, quando possibile, più INSERT per singoli record, ma più record con un solo INSERT. In pratica, ad una soluzione come la seguente:

 
mysql> INSERT INTO tbl (nome,cognome)
             VALUES('francesco','totti');

mysql> INSERT INTO tbl (nome,cognome)
             VALUES('luca','toni');

mysql> INSERT INTO tbl (nome,cognome)
            VALUES('ringhio','Gattuso'); 

è sempre preferibile questa seconda soluzione

 mysql> INSERT INTO tbl (nome,cognome)
        VALUES  ('francesco','totti'),
                ('luca','toni'),
                ('ringhio','Gattuso'); 

Una sola interrogazione per più inserimenti, oltre ad essere sicuramente più semplice e veloce da digitare, permetterà a MySQL (ma questo vale per qualsiasi DBMS) di svolgere tutte le operazioni all'interno di un unico flusso di dati, inoltre ciò comporterà un unico aggiornamento degli indici.

Se si lavora su una tabella InnoDB potrà essere presa in considerazione anche la soluzione relativa all'utilizzo di un INSERT per ogni record, ma questi dovranno essere previsti tutti all'interno di un'unica transazione:

mysql> BEGIN
mysql> INSERT INTO tbl (nome,cognome)
             VALUES('francesco','totti');
mysql> ...
mysql> COMMIT

Sempre a proposito degli inserimenti, è bene sapere che ai fini dell'ottimizzazione, quando possibile è sempre meglio caricare i record per l'aggiornamento da un file esterno attraverso l'istruzione LOAD DATA INFILE, ciò permetterà di eseguire le query in modo ancora più veloce rispetto all'INSERT unico per più record.

Infine, nelle query di sostituzione bisognerebbe sempre ricordarsi di utilizzare il comando REPLACE in luogo dell'uso congiunto di comandi INSERT e DELETE. Anche in questo caso avremo sia un risparmio in termini di digitazione (sintassi più breve), che la possibilità di risolvere la nostra interrogazione in un unico flusso di dati.

Ottimizzazione delle query di UPDATE

Gli aggiornamenti dei record tramite comando UPDATE possono pesare sulle risorse generali a disposizione tanto quanto le interrogazioni precedentemente analizzate. Il discorso diventa ancora più importante quando disponiamo di tabelle molto popolate ma vogliamo aggiornare solo determinati record e non altri.

In questo caso è necessario sfruttare al meglio le potenzialità della sintassi SQL, disponendo nel giusto modo clausole ed operatori.

Immaginiamo per esempio di dover gestire un database di prodotti alimentari e di voler modificare con una sola istruzione il prezzo di alcune tipologia di prodotti, ma soltanto per determinati record corrispondenti ad Id conosciuti. La nostra istruzione potrebbe essere simile alla seguente:

mysql> UPDATE tbl
SET prezzo = 
    CASE 
        WHEN tipologia = 'salumi' THEN 10
        WHEN tipologia = 'formaggi' THEN 15
        WHEN tipologia = 'vini' THEN 20
        ELSE prezzo
    END
WHERE id IN (2,9,22,56);

L'istruzione è sicuramente più complessa di una semplice query di aggiornamento espressa su un singolo record Id, ma il sistema ne guadagna sicuramente in termini di efficienza generale, senza contare l'evidente risparmio sui tempi di digitazione.

Efficienza delle query ed indici

In presenza di più indici su singole colonne, MySQL cerca di riconoscere autonomamente quello più efficace per risolvere l'interrogazione che gli viene inviata; supponiamo per esempio di avere come database un piccolo elenco telefonico in cui possono essere utilizzati come indici un Id relativo all'account e uno relativo alla provincia dell'utente.

Potremo effettuare query di questo genere basate sui due diversi indici:

mysql> SELECT telefono FROM tbl WHERE id_nome = 32;

mysql> SELECT telefono FROM tbl WHERE id_provincia = 12;

In entrambi i casi MySQL eseguirà efficacemente quanto richiesto basandosi sui singoli indici passati come parametri. Ma cosa succederebbe nel caso di un'interrogazione come questa.

mysql> SELECT telefono FROM tbl WHERE id_nome = 32 or id_provincia = 12;

Semplicemente il DBMS non saprebbe quale indice scegliere e ne risentirebbe l'efficienza del sistema. Ciò non è in contraddizione con quanto detto in precedenza, infatti tra i due indici utilizzati non ve n'è uno prioritario, quindi MySQL non potendo distinguerli finirà per ignorarli entrambi.

Fortunatamente esiste una soluzione praticabile a questo problema basata sulla semplice introduzione del comando UNION:

mysql> SELECT telefono FROM tbl WHERE id_nome = 32; 
             UNION 
             SELECT telefono FROM tbl WHERE id_provincia = 12;

In questo modo UNION permette di associare 2 query e per ognuna di esse potrà essere utilizzato un diverso indice; così facendo l'istruzione non verrà risolta sulla base di una lettura completa della tabella ma grazie alla lettura di indici con un consistente guadagno di tempo.