paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL e l'EXPLAIN PLAN

MySQL offre diversi strumenti per mostrare come una query verra' eseguita e come puo' essere ottimizzata. Utilizzando queste informazioni possiamo modificare la query, definire un nuovo indice, o modificare lo schema per migliorare le prestazioni.
L'istruzione EXPLAIN PLAN, che ricordo e' uno strumento che fornisce dati approssivamativi, restituisce le informazioni su tutti gli step che verranno eseguiti, ma senza eseguire la select stessa, con alcune eccezioni. Nel caso infatti di subquery, MySQL ha bisogno di appoggiare i dati su una o piu' tabelle temporanee prima di analizzare la query completa e questo, come potete immaginare, nel caso di query complesse e che agiscono su grosse mole di dati, comporta un considerevole uso di risorse al momento di eseguire un Explain Plan.
Vediamo un esempio, prima di entrare nel dettaglio delle informazioni.
Sulla tablella film_detail dove non abbiamo creato alcun indice, nemmeno quello di PRIMARY KEY, eseguiamo questa istruzione di Explain plan

EXPLAIN PLAN
SELECT title, description, rating, category_name 
  FROM film_detail 
 WHERE film_id = 101;

Il risultato sara' questo

mysql> EXPLAIN SELECT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: No tables used

Poiche' non esiste un indice sulla tabella film_detail, MySQL deve cercare attraverso ogni riga della tabella per trovare la corrispondenza film_id. Se la tabella contiene un gran numero di righe, questo processo può risultare lento e molto inefficiente.

Perche' si crea un indice?
Se creiamo invece un indice, MySQL cerchera' le informazioni nell'indice, anziche' cercare, riga per riga, nell'intera tabella, e, di conseguenza, puo' trovare un certo valore più velocemente. Le ricerche sono piu' veloci se effettuate direttamente sugli indici, perche' quest'ultimi sono ordinati e soprattutto i valori identici sono raggruppati e organizzati su la cosidetta easy-to-locate order.
Inoltre, l'ordinamento consente a MySQL di interrompere la ricerca non appena si raggiunge il risultato corrispondente, senza quindi cercare su tutto l'indice.

Che indicano le colonne dell'Explain Plan?
L'istruzione EXPLAIN ci restituisce come risultati, una riga per ogni tabella presente nella select, e le informazioni su come l'istruzione SELECT viene eseguita, ovvero i valori delle colonne. Con questi dettagli, e' possibile determinare se gli indici vengono utilizzati in modo efficace e/o se e' necessario aggiungerne di nuovi.
Ogni colonna restituita dalla dichiarazione EXPLAIN fornisce informazioni specifiche su come l'ottimizzatore prevede di eseguire l'istruzione SELECT.
Qui di seguito, descrivero' ogni colonna restituita dalle informazioni presenti nell' EXPLAIN.
Pero' prima di iniziare, vi faccio presente che esistono due varianti dell'istruzione EXPLAIN.
EXPLAIN EXTENDED (seguito da SHOW WARNING): Questa istruzione non agisce sulla select stessa, ma su cio' che restituisce l'EXPLAIN PLAN, cosi' da fornire in output i dati che Query Optimizer ha analizzato. EXPLAIN PARTITION: Mostra le partizioni, se presenti, che utilizzera' la select.
Altra considerazione importante, prima di passare al dettaglio delle colonne dell'Explain Plan
L'Explain plan puo' essere eseguito solo su istruzioni di SELECT. Se di per se' questa sembra essere una limitazione, in realta' possiamo sempre riconvertire una istruzione di DELETE o UPDATE in una select ;)
Entriamo quindi nei dettagli del risultato di un explain plan, dove non mi soffermero' nello spiegare tutte le colonne che resistuisce (Il cui elenco lo trovate nell'esempio indicato in precedenza), ma solo quelle che ritengo piu' significative, mentre per un elenco piu' esaustivo, vi consiglio di fare riferimento alla documentazione ufficiale
Analizzeremo in dettaglio, i dati della colonna Type (quella che fornisce le informazioni piu' interessanti), Key, Key_Len, Rows, la Filtered, mostrata solo nel caso di uso dell'opzione EXPLAIN EXTENDED e la extra column.

Type Column
Fornisce le informazioni fondamentali su come l'engine accede ai dati della tabella e puo' assumere questi valori.
ALL: Verra' effettuato uno full scan su tutta la tabella per estrarre i valori cercati
index: Anche in questo caso si esegue un full scan, pero' ordinando per indice piuttosto che per riga/row. Una ricerca di questo tipo, risulta pero' parecchio dispendiosa.
range: esegue una scansione parziale dell'indice, ovvero da un punto di inizio ad uno di fine. Verra' usato nel caso che sia presente la clausola BETWEEN nella condizione di WHERE.
ref: viene usato in presenza di no-unique index (o no-unique prefix di un unique index).
eq_ref: L'engine considera che ritornera' almeno un singolo valore. Vedremo questo metodo di accesso quando MySQL decide di utilizzare una chiave primaria o un indice univoco, per soddisfare la query e confrontarlo con un valore di riferimento. E' molto veloce e performante, perche' l'engine sa che non dovra' effettuare alcun scan di valori, visto che ne trovera' solo uno.
const,system: L'engine utilizza questo tipo di accessi quando e' in grado di ottimizzare una parte della query e trasformarla cosi' in una costante. Ad esempio, se si seleziona la chiave primaria di una riga inserendo la sua chiave primaria nella clausola WHERE, MySQL sara' in grado di convertire la query in una costante e poi rimuovere efficacemente la tabella dall'esecuzione join.
NULL: Questo metodo di accesso indica che MySQL e' in grado di risolvere la query durante la fase di ottimizzazione ed inoltre bisogno di accedere alla tabella o all'indice durante la fase di esecuzione.

Key Column
Questa colonna indica quale indice MySQL ha deciso di utilizzare per ottimizzare l'accesso alla tabella.

Key Len Column
Questa colonna indica il numero di byte che MySQL utilizzera' nell'indice. Se MySQL sta utilizzando solo alcune colonne l'indice, si puo' usare questo valore per determinare quale delle colonne utilizza. Quindi, in definitiva indica la lunghezza massima dei campi indicizzati, partendo dalla definizione della tabella e non dai dati nella tabella (un valore in tabelle potrebbe essere di dimensione minore rispetto al tipo per cui e' stato dichiarato)

Rows Column
Indica il numero di righe che l'engine pensa che dovra' leggere. Questo valore e' per definizione per loop in the nested-loop join plan. Ovvero, non e' solo il numero di righe che MySQL pensa che avra' bisogno di leggere dalla tabella, ma il numero di righe, in media, che MySQL pensa che dovra' leggere per trovare le righe che soddisfano i criteri della query. Questa stima puo' risultare essere molto imprecisa, perche' dipende dalle statistiche sulla tabella e su quanto sono selettivi gli indici usati.

Filtered Column
Questo valore, usato solo nel caso di un EXPLAIN EXTENDED, indica la percentuale delle righe che soddisfano la condizione richiesta e quindi se lo moltiplichiamo con il valore del "rows" visto prima, avremo una stima del numero di righe che l'indice dovra' valutare.

Extra Column
Indica i valori che non sono presenti nelle altre colonne, tra cui i piu' interessanti sono:
Using index: Indica che MySQL usera' un covering index per evitare l'accesso alla tabella.
Using where: Suggerisce che le query possono beneficiare di diverse tecniche di indicizzazione.
Using temporary: Indica che MySQL usera' una temporary table mentre effettuera' un sort dei risultati della query
Range checked for each record (index map: N): indica che non e' ben indicizzata la tabellae e che gli indici dovranno essere ri-valutati per ogni riga nella join.

Questa la teoria. In un prossimo articolo, illustrero' un esempio completo di ottimizzazione di una tabella e delle sue query