{"id":430,"date":"2009-01-29T22:14:08","date_gmt":"2009-01-29T21:14:08","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=430"},"modified":"2009-01-29T22:24:23","modified_gmt":"2009-01-29T21:24:23","slug":"influencer-loptimiseur-de-mysql","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/01\/29\/influencer-loptimiseur-de-mysql\/","title":{"rendered":"Influencer l’optimiseur de MySQL"},"content":{"rendered":"

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 <\/strong>sont les suivantes:<\/p>\n

USE INDEX<\/strong> : utilise l’index pass\u00e9 en argument (MySQL ne l’utilisera pas si l’index est plus couteux qu’un full table scan<\/em>)<\/p>\n

FORCE INDEX<\/strong> : utilise l’index pass\u00e9 en argument (MySQL ne l’utilisera pas …s’il ne peut pas l’utiliser \ud83d\ude42 <\/em>)<\/p>\n

IGNORE INDEX<\/strong> : n’utilise pas l’index pass\u00e9 en argument<\/p>\n

La plus part du temps, il se d\u00e9brouille tr\u00e9s bien sans indications, mais parfois…<\/p>\n

Dans cet exemple, j’utilise une table rental_daz<\/em> inspir\u00e9e de la table rental <\/em>de la base de donn\u00e9e sakila<\/a><\/em>, voici sa structure:<\/p>\n

12:14 daz$sakila> SHOW CREATE TABLE rental_daz\\G\r\n*************************** 1. row ***************************\r\n       Table: rental_daz\r\nCreate Table: CREATE TABLE `rental_daz` (\r\n  `rental_id` int(11) NOT NULL AUTO_INCREMENT,\r\n  `rental_date` datetime NOT NULL,<\/strong>\r\n  `inventory_id` mediumint(8) unsigned NOT NULL,\r\n  `customer_id` smallint(5) unsigned NOT NULL,\r\n  `return_date` datetime DEFAULT NULL,\r\n  `staff_id` tinyint(3) unsigned NOT NULL,\r\n  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP\r\n       ON UPDATE CURRENT_TIMESTAMP,\r\n  PRIMARY KEY (`rental_id`),\r\n  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),<\/strong>\r\n  KEY `idx_fk_inventory_id` (`inventory_id`),\r\n  KEY `idx_fk_customer_id` (`customer_id`),\r\n  KEY `idx_fk_staff_id` (`staff_id`)\r\n) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8<\/pre>\n
Voici ma requ\u00eate:<\/p>\n
SELECT * FROM rental_daz WHERE  rental_date > SUBDATE(now(), INTERVAL  3 YEAR);<\/strong><\/span><\/p>\n
La commande EXPLAIN <\/strong>me permet de connaitre son plan d’ex\u00e9cution:<\/p>\n
12:14 daz$sakila> EXPLAIN SELECT * FROM rental_daz\r\nWHERE  rental_date > SUBDATE(now(), INTERVAL  3 YEAR)\\G\r\n*************************** 1. row ***************************\r\n           id: 1\r\n  select_type: SIMPLE\r\n        table: rental_daz\r\n         type: range\r\npossible_keys: rental_date\r\n          key: rental_date<\/strong><\/span>\r\n      key_len: 8\r\n          ref: NULL\r\n         rows: 2744\r\n        Extra: Using where<\/pre>\n
L’optimiseur voit l’index composite rental_date<\/em> et l’utilise. \u00c7a \u00e0 l’air pas mal…<\/p>\n
Voyons le co\u00fbt de cette requ\u00eate,gr\u00e2ce \u00e0 au param\u00e8tre LAST_QUERY_COST<\/strong> :<\/p>\n
12:19 daz$sakila> SHOW STATUS LIKE 'Last_query_cost';\r\n+-----------------+-------------+\r\n| Variable_name   | Value       |\r\n+-----------------+-------------+\r\n| Last_query_cost | 3842.609000<\/strong> |\r\n+-----------------+-------------+<\/pre>\n
Emp\u00eachons <\/span>l’optimiseur d’utiliser l’index rental_date<\/em>:<\/p>\n
12:20 daz$sakila> EXPLAIN SELECT * FROM rental_daz IGNORE INDEX(rental_date)<\/strong>\r\nWHERE  rental_date > SUBDATE(now(), INTERVAL 3 YEAR)\\G\r\n*************************** 1. row ***************************\r\n           id: 1\r\n  select_type: SIMPLE\r\n        table: rental_daz\r\n         type: ALL\r\npossible_keys: NULL\r\n          key: NULL<\/strong><\/span>\r\n      key_len: NULL\r\n          ref: NULL\r\n         rows: 16298\r\n        Extra: Using where<\/pre>\n
L’optimiseur n’utilise donc pas l’index (il ne le voit m\u00eame pas). Un full table scan<\/em> est donc effectu\u00e9. C’est \u00e0 priori plus co\u00fbteux que d’utiliser l’index. Regardons le co\u00fbt de cette requ\u00eates.<\/p>\n
16:18 daz$sakila> SHOW STATUS LIKE 'Last_query_cost';\r\n+-----------------+-------------+\r\n| Variable_name   | Value       |\r\n+-----------------+-------------+\r\n| Last_query_cost | 3356.599000<\/strong> |\r\n+-----------------+-------------+<\/pre>\n
Malgr\u00e9 le full table scan<\/em>, cette requ\u00eate est moins co\u00fbteuse que celle qui utilise l’index !!!<\/p>\n
Essayons de v\u00e9rifier cela en dur\u00e9e. Le client mysqlslap <\/strong>est tout indiqu\u00e9 :<\/p>\n
mysqlslap -uroot -proot\u00a0 –create-schema=sakila -i50
\n-q\u00a0\u00bbSELECT * FROM rental_daz WHERE\u00a0 rental_date > SUBDATE(now(), INTERVAL\u00a0 3 YEAR);<\/strong>\u00a0\u00bb<\/p>\n
Benchmark<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Average number of seconds to run all queries: 0.287<\/strong><\/span> seconds<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Minimum number of seconds to run all queries: 0.140 seconds<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Maximum number of seconds to run all queries: 1.172 seconds<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Number of clients running queries: 1<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Average number of queries per client: 1<\/pre>\n
mysqlslap -uroot -proot\u00a0 –create-schema=sakila -i50
\n-q\u00a0\u00bbSELECT * FROM rental_daz ignore index(rental_date) WHERE\u00a0 rental_date > SUBDATE(now(), INTERVAL\u00a0 3 YEAR);<\/strong>\u00a0\u00bb<\/p>\n
Benchmark<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Average number of seconds to run all queries: 0.167<\/strong><\/span> seconds<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Minimum number of seconds to run all queries: 0.078 seconds<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Maximum number of seconds to run all queries: 1.094 seconds<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Number of clients running queries: 1<\/pre>\n
\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Average number of queries per client: 1<\/pre>\n
mysqlslap confirme bien que le full table scan est, dans ce cas pr\u00e9cis, plus performant que la recherche index\u00e9e par intervalle. On se trouve bien dans un cas o\u00f9 l’optimiseur se trompe.<\/p>\n","protected":false},"excerpt":{"rendered":"
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:<\/p>\n
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)<\/p>\n
FORCE INDEX : utilise l’index pass\u00e9 en argument (MySQL ne l’utilisera pas …s’il ne peut pas l’utiliser \ud83d\ude42 )<\/p>\n
IGNORE INDEX : n’utilise pas l’index pass\u00e9 en argument<\/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":[8,34],"tags":[96,97,80,94],"class_list":["post-430","post","type-post","status-publish","format-standard","hentry","category-mysql","category-optimisation","tag-index","tag-last_query_cost","tag-mysqlslap","tag-optimiser"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-6W","jetpack-related-posts":[{"id":1363,"url":"https:\/\/dasini.net\/blog\/2013\/01\/08\/full-table-scan-vs-full-index-scan-part1-2\/","url_meta":{"origin":430,"position":0},"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":453,"url":"https:\/\/dasini.net\/blog\/2009\/02\/18\/optimisation-de-requetes-comprendre-loptimiseur-de-mysql\/","url_meta":{"origin":430,"position":1},"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":[]},{"id":1425,"url":"https:\/\/dasini.net\/blog\/2013\/05\/27\/utiliser-une-sous-requete-cest-mal-suite-part-1-3\/","url_meta":{"origin":430,"position":2},"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":430,"position":3},"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":1370,"url":"https:\/\/dasini.net\/blog\/2013\/01\/30\/full-table-scan-vs-full-index-scan-part2-2\/","url_meta":{"origin":430,"position":4},"title":"Full table scan vs Full index scan part2-2","author":"Olivier DASINI","date":"30 janvier 2013","format":false,"excerpt":"2\/ FTS ou FIS Avant de r\u00e9pondre explicitement \u00e0 la question, un petit zoom sur l\u2019une des nombreuses nouveaut\u00e9s de MySQL 5.6. La commande EXPLAIN s\u2019est enrichie de la clause format=json. Elle permet d\u2019avoir une version un peu plus d\u00e9taill\u00e9e que l\u2019EXPLAIN classique. Query 1\/ EXPLAIN format=json SELECT d,avg(price) FROM\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":1533,"url":"https:\/\/dasini.net\/blog\/2015\/11\/17\/30-mins-avec-json-en-mysql\/","url_meta":{"origin":430,"position":5},"title":"30 mins avec JSON en MySQL","author":"Olivier DASINI","date":"17 novembre 2015","format":false,"excerpt":"Comme vous le savez MySQL 5.7 est GA. Cette nouvelle mouture de la base de donn\u00e9es open source la plus populaire au monde a plus de 150 nouvelles fonctionnalit\u00e9s. L'une d'entre elle est un type de donn\u00e9es JSON natif ainsi que les fonctions JSON associ\u00e9es. Prenons 30 minutes pour voir\u2026","rel":"","context":"Dans "json"","block_context":{"text":"json","link":"https:\/\/dasini.net\/blog\/category\/json-fr\/"},"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\/430","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=430"}],"version-history":[{"count":17,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/430\/revisions"}],"predecessor-version":[{"id":445,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/430\/revisions\/445"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=430"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=430"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=430"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}