Ottimizzare le query con MySQL Explain

Mattepuffo's logo
Ottimizzare le query con MySQL Explain

Ottimizzare le query con MySQL Explain

L'altro giorno mi sono imbattuto in una istruzione che non conoscevo: EXPLAIN!

In pratica ci permette di analizzare le query, per tracciare le eventuali migliorie da apportare.

In un sito di medie / grandi dimensioni, può essere molto importante ottimizzare le query su tabelle con migliaia di record.

Per poter usare il comando, vi basta anteporlo alla query:

EXPLAIN SELECT * FROM tabella

L'output non sarà quello classico a cui siete abitatuati.

Ma facciamo un esempio più pratico:

EXPLAIN SELECT * FROM articoli INNER JOIN listini ON listino_articolo_codice = articolo_codice WHERE articolo_collezione_fk = "K60" AND listino_codice = "EURO" AND articolo_abilitato = 1 GROUP BY articolo_codice;

Questa è una query che uso molto nel mio gestionale; tutte le WHERE e le GROUP BY sono fatte su indici (cosa fondamentale).

Non vi posto l'output perchè parecchio lungo; ma vi spiego qua sotto i campi che tira fuori (vi conviene eseguire una query e seguire passo passo i punti):

  • id: un numero sequanziale che identifica ogni query eseguita (nel mio caso non ci sono subqueries)
  • select_type: il tipo di SELECT che viene eseguito:
    • SIMPLE: senza nessuna subqueries o UNION (come nel mio caso)
    • PRIMARY: la SELECT è nella parte più esterna della JOIN
    • DERIVED: la SELECT è parte di una subquery
    • SUBQUERY: la SELECT è una subquery
    • DEPENDENT SUBQUERY: una subquery che dipende da una query esterna
    • UNCACHEABLE SUBQUERY: una subquery che non è cacheable
    • UNION: la SELECT è l'ultimo statement di una UNION
    • DEPENDENT UNION: la seconda o successiva SELECT di una UNION dipende da una query esterna
    • UNION RESULT: la SELECT è il risultato di una UNION
  • table: la tabella di riferimento della riga
  • type: come MySQL unisce le tabelle usate; è molto importante, perchè indica anche la eventuale assenza di indici. I possibili valori sono:
    • system: la tabella ha solo zero o una riga
    • const: la tabella ha una sola riga corrispondente che viene indicizzata. Questo è il tipo più veloce di join tra tabelle, perché la tabella deve essere letta solo una volta, e il valore della colonna può essere trattato come una costante quando si uniscono altre tabelle
    • eq_ref: tutte le parti di un indice sono utilizzati da join, e l'indice è PRIMARY KEY o UNIQUE NOT NULL. Questo è il tipo successivo migliore di collegamento tra tabelle
    • ref: tutte le righe corrispondenti di una colonna indicizzata vengono letti per ogni combinazione di righe della tabella precedente. Questo tipo di join appare per colonne indicizzate per confronto = o <=> operatori
    • fulltext: la join usa un indice FULLTEXT
    • ref_or_null: equivale ref, ma contiene anche colone con valori null
    • index_merge: il join utilizza un elenco di indici per produrre il set di risultati. La colonna chiave di EXPLAIN di output conterrà le chiavi usate
    • unique_subquery: restituisce un solo risultato dalla tavola e si avvale della chiave primaria
    • index_subquery: come unique_subquery, ma ritorna più righe
    • range: un indice è utilizzato per trovare corrispondenza righe in un intervallo specifico, ad esempio usando BETWEEN o IN
    • index: l'intero indice è scandagliato per identificare le righe corrette
    • all: l'intera tabella è scandagliata per trovare le righe. E' la peggior query da eseguire, e generalmente identifica l'assenza di indici nella tabella
  • possible_keys: mostra le chiavi che possono essere utilizzate da MySQL per trovare righe dalla tabella, anche se possono o non possono essere utilizzati in pratica. In realtà, questa colonna spesso può aiutare a ottimizzare le query in quanto se la colonna è NULL, indica nessun indice rilevanti è stato trovato
  • key: indica le chivi utilizzate. Ptrebero anche non comparire possible_keys
  • key_len: indica la lunghezza dell'indice usato
  • ref: indica le colonne o le costanti che sono comparate con gli indici indicati nella colonna key
  • rows: indica il numero di record che sono esaminati per ottenere i records
  • Extra: informazioni addizionali, che potete confrontare qui; comunque possiamo dire che se trovate Using where e il tipo è ALL o index, già c'è qualcosa che non va

Potete anche agguingere EXTENDED dopo EXPLAIN per ottenere delle informazioni aggiuntive.

Per capire meglio l'output, dovreste fare diverse prove.

Ad esempio eseguire le stesse query prima senza e poi con gli indici; oppure usando parecchie JOIN o subqueries.

Buone query!


Condividi

Commentami!