Ottimizzazione query My-Sql, conosciamo la Funzione EXPLAIN

Cari lettori,  oggi vi parlerò di ottimizzazione query MY-SQL argomento che spero sia di vostro gradimento e di supporto per tutti voi.

Cominciamo oggi con un articolo in cui analizzeremo un grande strumento, la funzione EXPLAIN che My-Sql ci mette a disposizione per analizzare le nostre Query per poter apportare ulteriori miglioramenti, prestazionali alle stesse.

Per eseguire lo strumento basterà semplicemente digitare il comando EXPLAIN prima della query di selezione e lasciare che My-SQL lo esegua.
MySQL non eseguirà la query di selezione, ma analizzerà e visualizzerà  il risultato.

Un semplice esempio

Inizieremo con una semplice query di selezione a contare tutti i contatti di sesso maschile contenuti in una tabella Rubrica.

SELECT COUNT(*) FROM rubrica WHERE genere = 'M';
+----------+
| COUNT(*) |
+----------+
|   123456 |
+----------+
1 ROW IN SET (0.25 sec)

Ora useremo il comando EXPLAIN per ottenere le informazioni su come i dati per questa query sono ottenuti.
Esempio:

EXPLAIN SELECT COUNT(*) FROM dipendenti WHERE genere = 'M';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | TABLE     | TYPE | possible_keys | KEY  | key_len | REF  | ROWS   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | rubrica | ALL  | NULL          | NULL | NULL    | NULL | 123456 | USING WHERE |

Andiamo a dare un’occhiata per capire che cosa My-SQL ci sta dicendo.

Per ogni query, sottoquery o join EXPLAIN stamperà una riga con informazioni su come verranno recuperati i dati per questa parte della query, se si esegue la query.

In questo caso, ci dice che la prima query è un semplice selezionare sulla Tabella Rubrica.

possible_keys=NULL ci dice che non vi è alcuna chiave che My-SQL può scegliere e key=NULL significa che nessuna chiave è stata utilizzata per recuperare i dati.

Per questa SELECT c’è una chiave che può essere utilizzata, in modo che sarà fatta una scansione completa della tabella.

Miglioriamola aggiungendo a questa query un indice su genere, e facciamo girare la query e quindi di nuovo la funzione EXPLAIN.

Otteniamo così i dati reali delle prestazioni la query nella cache è stata disabilitata utilizzando SET SESSION query_cache_type OFF

ALTER TABLE rubrica ADD INDEX index_genere(genere);
Query OK, 123456 ROWS affected (6.37 sec)
Records: 123456  Duplicates: 0  Warnings: 0
 
SELECT COUNT(*) FROM rubrica WHERE genere = 'M';
+----------+
| COUNT(*) |
+----------+
|   123456 |
+----------+
1 ROW IN SET (0.18 sec)

Come possiamo vedere la query è stata eseguita più veloce, consente di vedere come il risultato di EXPLAIN è cambiato.

EXPLAIN SELECT COUNT(*) FROM rubrica WHERE genere = 'M';
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+
| id | select_type | TABLE     | TYPE | possible_keys | KEY          | key_len | REF   | ROWS   | Extra                    |
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | rubrica | REF  | index_gender  | index_gender | 1       | const | 123456| USING WHERE; USING INDEX |
+----+-------------+-----------+------+---------------+--------------+---------+-------+--------+--------------------------+

Questa volta MySQL ha trovato la chiave index_gender e l’ha utilizzata per recuperare i dati.

Anziché effettuare la scansione completa della tabella doveva soltanto esaminare metà delle righe per eseguire la query questa volta.

Particolare EXPLAIN di tutti i campi

id

L’identificatore della SELECT. Questo è il numero sequenziale del SELECT all’interno della query

select_type

Il tipo di SELECT, che può essere uno qualsiasi di quelli indicati nella seguente tabella:

SIMPLE	SELECT semplice (nessuna UNION / subqueries)
PRIMARY	SELECT estrema
UNION	ulteriore o secondaria stament di  SELECT  in ana UNION
DEPENDENT UNION	ulteriore o secondaria stament di  SELECT  in ana UNION, dipendente o altra query
UNION RESULT	Risulatati di una  UNION.
SUBQUERY	Prima SELECT in una sottoquery
DEPENDENT SUBQUERY	Prima SELECT in una sottoquery, dipendente o altra query
DERIVED	Tabella derivata da una query di SELECT (sottoquery in clausula di FROM )

table

La tabella a cui la riga di risultati si riferisce.

type

Il tipo di JOIN.

possible_keys

La colonna possible_keys indica quali indici MySQL può scegliere da utilizzare per trovare le righe in questa tabella.

key

La colonna key indica la chiave (indice) che MySQL in realtà ha deciso di utilizzare. La chiave è NULL se non  è stato scelto alcun indice. Per forzare MySQL di utilizzare o ignorare un indice elencati nella colonna possible_keys, utilizzare FORCE INDEX, USE INDEX, o ignorare INDEX nella query

key_len

La colonna key_len indica la lunghezza della chiave che MySQL ha deciso di utilizzare. La lunghezza è NULL se la colonna chiave dice NULL

ref

La colonna ref indica che le colonne o le costanti sono confrontate con l’indice denominato nella colonna chiave per selezionare le righe della tabella.

rows

La colonna rows indica il numero di righe che MySQL ritiene deve esaminare per eseguire la query.

Questa colonna contiene ulteriori informazioni su come MySQL risolve la query.

Conclusioni

Oggi abbiamo affrontato un argomento molto utile per migliorare le performance delle vostre query e quindi velocizzare i tempi di risposta delle vostre applicazioni.

Spero che l’articolo sia stato di vostro gradimento e colgo l’occasione per poter fare a tutti i miei auguri di Buon Natale. Alla Prossima !