13 Oct
duccio

duccio il 13 October 2009 parla di Rails Snippet, Risorse, Tutorial

Ottimizzare le query con EXPLAIN di MySQL

L’ottimizzazione delle query è sempre un punto cruciale per ogni progetto ed è sicuramente una parte fondamentale del processo di ottimizzazione di un’applicazione Web. Non credo di dirvi niente di nuovo ma, considerato che non ne ho mai parlato direttamente, riporto un piccolo riassunto.

Ip plugin query_analyzer vi consente di avere l’EXPLAIN delle query direttamente nel log:

    1 ./script/plugin install http://svn.nfectio.us/plugins/query_analyzer

La sintassi per vedere l’explain di una query da shell mysql:

    1 EXPLAIN [EXTENDED] SELECT select_options

L’explain restituisce informazioni per ogni tabella coinvolta nella query, vediamo un esempio in cui mancano indici:

picture-1

In questo caso il select_type è simple, non ci sono join, il type è ALL cioè cerca su tutte le righe, e nessuna possible_keys. Aggiungendo un indice al campo action della tabella contents (La query effettuata è Content.find_by_action(”nome azione”)) si ottiene questo risultato:

picture-2

Migliorando di fatto il tempo necessario per eseguire la query, tempo ulteriormente migliorabile usando il caching di Mysql.

In generale col query_analyzer (con l’EXPLAIN di mysql) potrete trovare i seguenti valori per ogni campo visualizzato:

Select_type

E’ il tipo di SELECT:

  • SIMPLE select semplice senza join o sottoquery
  • PRIMARY outermost SELECT
  • DEPENDENT UNION seconda o ultima SELECT in una unione
  • UNION RESULT risultato di una UNION
  • SUBQUERY prima SELECT in una subquery
  • DEPENDENT SUBQUERY prima SELECT in un una subquery dipendente da un altra query
  • DERIVED subquery nel FROM
  • UNCACHEABLE SUBQUERY subquery non cachabile

Table

E’ la tabella su cui si esegue la query.

Type

E’ il tipo di Join e sono in ordine dal migliore al peggiore:

  • system: la tabella ha una sola riga è un caso particolare di const
  • const: la tabella ha al massimo una riga corrispondente, che viene letta all’inizio della query. Poichè c’è una sola riga, i valori delle colonne in questa riga sono considerati come costanti da parte dell’optimizer. Le tabelle const sono molto veloci perché vengono letti solo una volta.
  • eq_ref: viene letta una riga per ogni combinazione di righe delle tabelle precedenti. È usato quando tutte le parti di un indice sono utilizzate dalla join e l’indice è una PRIMARY KEY o UNIQUE.
  • ref: tutte le righe con i corrispondenti valori di indice vengono letti da questa tabella per ogni combinazione di righe delle tabelle precedenti. ref viene utilizzato se la join utilizza solo la parte più a sinistra dell’indice o se la chiave non è un indice PRIMARY KEY o UNIQUE (in altre parole, se l’unione non può selezionare una sola riga sulla base del valore della chiave). Va bene quando il set di righe di unione è piccolo.
  • fulltext: ricerca di tipo fulltext
  • range: solo le righe che si trovano in un determinato intervallo vengono recuperate, utilizzando un indice per selezionare le righe. La colonna chiave della riga di output indica che l’indice viene utilizzato. Il key_len contiene la parte più lunga chiave che è stata utilizzata. La colonna ref è NULL per questo tipo. Viene usata per =, <>, >, >=, < , <=, IS NULL, <=>, BETWEEN, o IN()
  • index: è come ALL ma si cerca sull’indice
  • ALL: viene fatta una scansione completa della tabella, per ogni combinazione di righe delle tabelle precedenti. Questo normalmente non va bene, si risolve mettendo indici.

Possible_keys

La colonna possible_keys indica gli indici che MySQL può scegliere per trovare le righe in questa tabella.

Key

La colonna key è l’indice che MySQL ha effettivamente deciso di utilizzare. MySQL, può decidere di utilizzare sia uno degli indici possible_keys per cercare le righe, sia la primary key.

E ‘possibile che key sia il nome di un indice che non è presente in possible_keys. Questo può accadere se nessuno degli indici possible_keys è adatto per la ricerca, ma tutte le colonne selezionate dalla query sono colonne di qualche altro indice. Cioè, l’indice copre le colonne selezionate, quindi anche se non viene utilizzato per determinare le righe da recuperare, lì la ricerca è comunque più veloce.

Key_len

La colonna key_len indica la lunghezza della chiave che MySQL ha deciso di utilizzare. La lunghezza è NULL se la colonna key dice NULL. Si noti che il valore di key_len consente di determinare quante parti di più chiavi MySQL utilizza effettivamente.

Ref

La colonna ref mostra quali colonne o costanti sono confrontati con l’indice per selezionare le righe della tabella.

Rows

La colonna righe indica il numero di righe MySQL ritiene che esso deve verificare l’esecuzione della query. Per i db innoDB non sempre il numero di righe stimato è quello reale.

Extra

Questa colonna contiene ulteriori informazioni su come MySQL risolve la query. Dicimo che se trovat scritto Using where e type è ALL o index c’è qualche problema sulla vostra query.

1 Commento a “Ottimizzare le query con EXPLAIN di MySQL”

  1. Andrea Salicetti il 15 March 2011 alle 13:25 dice:

    Il link al progetto in questione è diventato obsoleto.
    Segnalo i seguenti due nuovi link a un progetto che, non so se sia lo stesso, si propone di dare le stesse informazioni direttamente nell’interfaccia web della propria applicazione web Rails:

    Grazie per l’ottima segnalazione!

Scrivi un commento