{"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":{"footnotes":"","jetpack_post_was_ever_published":false},"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":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t