{"id":1163,"date":"2012-01-09T19:24:08","date_gmt":"2012-01-09T18:24:08","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1163"},"modified":"2017-03-07T17:03:41","modified_gmt":"2017-03-07T16:03:41","slug":"ameliorations-de-loptimiseur-dans-mariadb","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2012\/01\/09\/ameliorations-de-loptimiseur-dans-mariadb\/","title":{"rendered":"Am\u00e9liorations de l’optimiseur dans MariaDB"},"content":{"rendered":"

Les \u00e9quipes de MariaDB<\/a> ont \u00e9norm\u00e9ment travaill\u00e9es sur l’optimiseur de la version 5.3<\/a>, notamment en permettant une r\u00e9elle utilisation des sous-requ\u00eates<\/a>.<\/p>\n

Voici un effet visuel de ces optimisations:<\/p>\n

Avec MySQL 5.5, l’utilisation de tables d\u00e9riv\u00e9es (type de sous-requ\u00eates dans la clause FROM d’un SELECT), donne le plan d’ex\u00e9cution suivant:<\/p>\n

MySQL-5.5 > EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM City ccc ) cc ) c \\G
\n*************************** 1. row ***************************
\nid: 1
\nselect_type: PRIMARY
\ntable: <derived2>
\ntype: ALL
\npossible_keys: NULL
\nkey: NULL
\nkey_len: NULL
\nref: NULL
\nrows: 4079
\nExtra:
\n*************************** 2. row ***************************
\nid: 2
\nselect_type: DERIVED
\ntable: <derived3>
\ntype: ALL
\npossible_keys: NULL
\nkey: NULL
\nkey_len: NULL
\nref: NULL
\nrows: 4079
\nExtra:
\n*************************** 3. row ***************************
\nid: 3
\nselect_type: DERIVED
\ntable: ccc
\ntype: ALL
\npossible_keys: NULL
\nkey: NULL
\nkey_len: NULL
\nref: NULL
\nrows: 4079
\nExtra:<\/p>\n

 <\/p>\n

Le m\u00eame EXPLAIN avec MariaDB 5.3 donne:
\nMariaDB-5.3 >\u00a0 EXPLAIN SELECT * FROM (SELECT * FROM (SELECT * FROM City ccc ) cc ) c \\G
\n*************************** 1. row ***************************
\nid: 1
\nselect_type: SIMPLE
\ntable: ccc
\ntype: ALL
\npossible_keys: NULL
\nkey: NULL
\nkey_len: NULL
\nref: NULL
\nrows: 4079
\nExtra:<\/p>\n

L’optimiseur comprend que les 2 niveaux de \u00ab\u00a0SELECT * FROM\u00a0\u00bb ne servent \u00e0 rien, et r\u00e9\u00e9crit donc la requ\u00eate.<\/p>\n

L\u2019int\u00e9r\u00eat n’est heureusement pas que visuel:<\/p>\n

Sur MySQL 5.5<\/strong>:<\/p>\n

mysqlslap –create-schema=world -i10 -q\u00a0\u00bbSELECT * FROM (SELECT * FROM (SELECT * FROM City ccc ) cc ) c ;\u00a0\u00bb -S\/tmp\/mysql_55.sock
\nBenchmark
\nAverage number of seconds to run all queries: 0.004 seconds<\/strong><\/span><\/p>\n

 <\/p>\n

Sur MariaDB 5.3<\/strong>:
\nmysqlslap –create-schema=world -i10 -q\u00a0\u00bbSELECT * FROM (SELECT * FROM (SELECT * FROM City ccc ) cc ) c ;\u00a0\u00bb -S\/tmp\/mariadb_53.sock
\nBenchmark
\nAverage number of seconds to run all queries: 0.002 seconds<\/strong><\/span><\/p>\n

Le test \u00e9tant r\u00e9alis\u00e9 sur un petit volume de donn\u00e9es, le temps \u00e9coul\u00e9 n’est pas tr\u00e8s important, cependant l’on peu noter que l’optimisation apport\u00e9 par MariaDB, divise le temps d’ex\u00e9cution par 2<\/span><\/strong> dans ce contexte (\u00e0 conf \u00e9quivalente, donn\u00e9es \u00e9quivalentes,…).<\/p>\n

 <\/p>\n

D’autres tests s’imposent, mais l’on peut d\u00e9j\u00e0 f\u00e9liciter les \u00e9quipes de MariaDB<\/p>\n

 <\/p>\n

http:\/\/dimitrik.free.fr\/blog\/archives\/2016\/02\/mysql-performance-scalability-on-oltp_rw-benchmark-with-mysql-57.html<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

Les \u00e9quipes de MariaDB ont \u00e9norm\u00e9ment travaill\u00e9es sur l’optimiseur de la version 5.3, notamment en permettant une r\u00e9elle utilisation des sous-requ\u00eates.<\/p>\n

Voici un effet visuel de ces optimisations:<\/p>\n

Avec MySQL 5.5, l’utilisation de tables d\u00e9riv\u00e9es (type de sous-requ\u00eates dans la clause FROM d’un SELECT), donne le plan d’ex\u00e9cution suivant:<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[81,167],"tags":[],"class_list":["post-1163","post","type-post","status-publish","format-standard","hentry","category-bench","category-mariadb"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-iL","jetpack-related-posts":[{"id":1425,"url":"https:\/\/dasini.net\/blog\/2013\/05\/27\/utiliser-une-sous-requete-cest-mal-suite-part-1-3\/","url_meta":{"origin":1163,"position":0},"title":"Utiliser une sous requ\u00eate c\u2019est mal ? (suite) part 1-3","author":"Olivier DASINI","date":"27 mai 2013","format":false,"excerpt":"Comme promit, voici la suite de l\u2019article Utiliser une sous-requ\u00eate c\u2019est mal ? L\u2019id\u00e9e ici est de r\u00e9pondre aux interrogations de svar et d\u2019en profiter pour explorer les nouvelles possibilit\u00e9s de la variante stable de MySQL qui poss\u00e8de l\u2019optimiseur le plus avanc\u00e9, c\u2019est \u00e0 dire MariaDB 5.5. Pr\u00e9ambule En pr\u00e9-requis,\u2026","rel":"","context":"Dans "MariaDB"","block_context":{"text":"MariaDB","link":"https:\/\/dasini.net\/blog\/category\/mariadb\/"},"img":{"alt_text":"","src":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA","width":350,"height":200,"srcset":"https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1x, https:\/\/lh3.googleusercontent.com\/8zkRFWHP6EnBzoQbslcH8lk9Cq1TJ1NFMT7tjTsN8fgdk-UBh0vaRJqzEd3SbRjCCFV-jdUOoH1U_WUdKcI1nT1G_gz9HHpefwKCxWIMh_S55Gl2iumBn_gNRA 1.5x"},"classes":[]},{"id":1413,"url":"https:\/\/dasini.net\/blog\/2013\/04\/23\/utiliser-une-sous-requete-cest-mal\/","url_meta":{"origin":1163,"position":1},"title":"Utiliser une sous-requ\u00eate c’est mal ?","author":"Olivier DASINI","date":"23 avril 2013","format":false,"excerpt":"Jusqu\u2019en MySQL 5.5 inclus, l\u2019utilisation de sous-requ\u00eates peut, dans certain cas, \u00eatre la cause de probl\u00e8mes de performances (l\u2019optimiseur est bien meilleur en MySQL 5.6, MariaDB 5.5 et MariaDB 10). R\u00e9cemment j\u2019ai eu un souci en prod, apr\u00e8s une MEP, avec une requ\u00eate qui durait en moyenne plus de 1000\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1256,"url":"https:\/\/dasini.net\/blog\/2012\/05\/15\/jointure-vs-sous-requete\/","url_meta":{"origin":1163,"position":2},"title":"Jointure vs sous-requ\u00eate","author":"Olivier DASINI","date":"15 mai 2012","format":false,"excerpt":"MySQL est connu pour ne pas \u00eatre tr\u00e8s performant avec les sous-requ\u00eates. Ce n'est pas faux, et d'ailleurs c'est encore le cas avec MySQL 5.5. Le contournement consiste en g\u00e9n\u00e9ral \u00e0 r\u00e9\u00e9crire la requ\u00eate, certaines sous-requ\u00eates pouvant \u00eatre ais\u00e9ment r\u00e9\u00e9crite en jointure. C'est le cas de SELECT a FROM T1\u2026","rel":"","context":"Dans "Astuce"","block_context":{"text":"Astuce","link":"https:\/\/dasini.net\/blog\/category\/astuce\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1363,"url":"https:\/\/dasini.net\/blog\/2013\/01\/08\/full-table-scan-vs-full-index-scan-part1-2\/","url_meta":{"origin":1163,"position":3},"title":"Full table scan vs Full index scan part1-2","author":"Olivier DASINI","date":"8 janvier 2013","format":false,"excerpt":"MySQL utilise un optimiseur \u00e0 base de co\u00fbts. Le plan d\u2019ex\u00e9cution de la requ\u00eate choisit est celui dont le co\u00fbt est le plus faible. Ce dernier peut \u00eatre visualis\u00e9 gr\u00e2ce \u00e0 la variable Last_query_cost. Son unit\u00e9 est le co\u00fbt (encore lui) des acc\u00e8s al\u00e9atoires en lecture de pages de 4ko.\u2026","rel":"","context":"Dans "optimisation"","block_context":{"text":"optimisation","link":"https:\/\/dasini.net\/blog\/category\/optimisation\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":430,"url":"https:\/\/dasini.net\/blog\/2009\/01\/29\/influencer-loptimiseur-de-mysql\/","url_meta":{"origin":1163,"position":4},"title":"Influencer l’optimiseur de MySQL","author":"Olivier DASINI","date":"29 janvier 2009","format":false,"excerpt":"Il est possible d'influencer l'optimiseur pour qu'il choisisse d'utiliser ou de ne pas utiliser un index particulier. Les clauses \u00e0 placer dans votre requ\u00eate SELECT sont les suivantes: USE INDEX : utilise l'index pass\u00e9 en argument (MySQL ne l'utilisera pas si l'index est plus couteux qu'un full table scan) FORCE\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":453,"url":"https:\/\/dasini.net\/blog\/2009\/02\/18\/optimisation-de-requetes-comprendre-loptimiseur-de-mysql\/","url_meta":{"origin":1163,"position":5},"title":"Optimisation de requ\u00eates: comprendre l’optimiseur de MySQL","author":"Olivier DASINI","date":"18 f\u00e9vrier 2009","format":false,"excerpt":"Le but de cet article est d'optimiser une simple requ\u00eate (SELECT avg(Population) FROM city GROUP BY CountryCode) et surtout de comprendre comment l'optimiseur proc\u00e8de, en \u00e9tudiant les r\u00e9sultats donn\u00e9s par les variables qui permettent de surveiller le serveur MySQL.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1163","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=1163"}],"version-history":[{"count":5,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1163\/revisions"}],"predecessor-version":[{"id":1817,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/1163\/revisions\/1817"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=1163"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=1163"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=1163"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}