Ottimizzare le query con la funzione EXPLAIN di MySQL

mysqlSpesso capita che una pagina web ci metta molto a rispondere e quindi a caricarsi. Questo può dipendere anche dal fatto che l’interrogazione verso il database faccia da collo di bottiglia magari per una query che ci impiega molto tempo a restituire i record.
Per il database open source acquisito da Oracle, c’è mysql-slow-log, un file di testo nel quale MySQL scrive tutte le query che vengono eseguite in un tempo superiore al valore impostato nel parametro long_query_time (che di default vale 10 secondi). Solitamente si trova sotto /var/log/mysql/.
Ecco un esempio:

SELECT `Coupling`.`id`, `Coupling`.`sti_type`, `Coupling`.`model_id`, `Coupling`.`section_id`,
`Coupling`.`product_id`, `Coupling`.`ratio_table_code`, `Coupling`.`start_year`, `Coupling`.`end_year`,
 `Coupling`.`serie`, `Coupling`.`shifter_group`, `Coupling`.`legacy_id`, `Coupling`.`legacy_coupling_id`,
 `Coupling`.`last_user_id`, `Coupling`.`created_at`, `Coupling`.`updated_at`, `Model`.`id`, `Model`.`description`,
 `Model`.`brand_id`, `Model`.`displacement_id`, `Model`.`legacy_id`, `Model`.`last_user_id`, `Model`.`created_at`,
`Model`.`updated_at`, `Section`.`id`, `Section`.`spread_id`, `Section`.`section_description_id`,
`Section`.`description`, `Section`.`position`, `Section`.`description_centauro_it`,
`Section`.`description_centauro_en`, `Section`.`position_centauro`, `Section`.`legacy_id`, `Section`.`last_user_id`,
 `Section`.`created_at`, `Section`.`updated_at`, `Section`.`description_en`, `Section`.`description_de`,
`Section`.`description_fr`, `Section`.`description_es`, `Section`.`description_pt`, `Coupling_note`.`id`,
`Coupling_note`.`note_id`, `Coupling_note`.`coupling_id`, `Coupling_note`.`legacy_id`, `Coupling_note`.`last_user_id`,
`Coupling_note`.`created_at`, `Coupling_note`.`updated_at`, `Product`.`id`, `Product`.`code`, `Product`.`section_id`,
`Product`.`availability`, `Product`.`dimension_x`, `Product`.`dimension_y`, `Product`.`dimension_z`,
`Product`.`weight`, `Product`.`trade_family`, `Product`.`trade_group`, `Product`.`is_kit`, `Product`.`legacy_id`,
`Product`.`last_user_id`, `Product`.`created_at`, `Product`.`updated_at`, `Product`.`pieces`, `Description`.`id`,
`Description`.`product_id`, `Description`.`description`, `Description`.`legacy_id`, `Description`.`last_user_id`,
`Description`.`created_at`, `Description`.`updated_at`, `Description`.`description_en`,
`Description`.`description_de`, `Description`.`description_fr`, `Description`.`description_es`,
`Description`.`description_pt` FROM `couplings` AS `Coupling`
LEFT JOIN `models` AS `Model` ON (`Coupling`.`model_id` = `Model`.`id`)
LEFT JOIN `sections` AS `Section` ON (`Coupling`.`section_id` = `Section`.`id`)
LEFT JOIN `coupling_notes` AS `Coupling_note` ON (`Coupling_note`.`coupling_id` = `Coupling`.`id`)
LEFT JOIN `products` AS `Product` ON (`Product`.`id` = `Coupling`.`product_id`)
LEFT JOIN `descriptions` AS `Description` ON (`Coupling`.`product_id` = `Description`.`product_id`)
WHERE `Coupling`.`model_id` = 7009 LIMIT 20

Lanciamo: EXPLAIN SELECT `Coupling`.`id`, `Coupling`.`sti_type`, `Coupling`.`model_id`...

EXPLAIN viene utilizzata per consultare il “query optimizer”, la parte del server che effettua le valutazioni su come rendere più veloce l’interrogazione che viene richiesta al database: è questa parte che decide se un indice dovrà essere utilizzato, l’ordine di verifica delle condizioni ecc.
In pratica, interrogando il query optimizer si chiede a MySQL come intende sviluppare la query.
Le informazioni che fornisce EXPLAIN sono utili per molti aspetti:

  • forniscono indizi circa l’opportunità di aggiunta di alcuni indici alle tabelle;
  • se una tabella ha già degli indici utilizzati, l’output del comando aiuta a capire come vengono utilizzati dal motore;
  • se gli indici esistono ma non vengono utilizzati dal query optimizer, aiuta a scrivere meglio il codice SQL affinché la query venga eseguita beneficiando della presenza dell’indice.

Nella fattispecie della mia query è bastato aggiungere l’indice per il campo coupling_id nella tabella coupling_notes per ridurre il tempo di esecuzione della query da 250 secondi (quattro minuti) a 0.06 secondi.
Forte vero?

Approfondisci la lettura qui:
http://dev.mysql.com/doc/refman/5.6/en/optimization.html
http://dev.mysql.com/doc/refman/5.6/en/using-explain.html
http://dev.mysql.com/doc/refman/5.6/en/explain.html

Rispondi