Influencer l’optimiseur de MySQL
Il est possible d’influencer l’optimiseur pour qu’il choisisse d’utiliser ou de ne pas utiliser un index particulier. Les clauses à placer dans votre requête SELECT sont les suivantes:
USE INDEX : utilise l’index passé en argument (MySQL ne l’utilisera pas si l’index est plus couteux qu’un full table scan)
FORCE INDEX : utilise l’index passé en argument (MySQL ne l’utilisera pas …s’il ne peut pas l’utiliser 🙂 )
IGNORE INDEX : n’utilise pas l’index passé en argument
La plus part du temps, il se débrouille trés bien sans indications, mais parfois…
Dans cet exemple, j’utilise une table rental_daz inspirée de la table rental de la base de donnée sakila, voici sa structure:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
12:14 daz$sakila> SHOW CREATE TABLE rental_daz\G *************************** 1. row *************************** Table: rental_daz Create Table: CREATE TABLE `rental_daz` ( `rental_id` int(11) NOT NULL AUTO_INCREMENT, <strong> `rental_date` datetime NOT NULL,</strong> `inventory_id` mediumint(8) unsigned NOT NULL, `customer_id` smallint(5) unsigned NOT NULL, `return_date` datetime DEFAULT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`rental_id`), <strong> UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),</strong> KEY `idx_fk_inventory_id` (`inventory_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `idx_fk_staff_id` (`staff_id`) ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 |
Voici ma requête:
SELECT * FROM rental_daz WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR);
La commande EXPLAIN me permet de connaitre son plan d’exécution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
12:14 daz$sakila> EXPLAIN SELECT * FROM rental_daz WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental_daz type: range possible_keys: rental_date <span style="color: #ff0000;"> <strong>key: rental_date</strong></span> key_len: 8 ref: NULL rows: 2744 Extra: Using where |
L’optimiseur voit l’index composite rental_date et l’utilise. Ça à l’air pas mal…
Voyons le coût de cette requête,grâce à au paramètre LAST_QUERY_COST :
|
1 2 3 4 5 6 |
12:19 daz$sakila> SHOW STATUS LIKE 'Last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | <strong>3842.609000</strong> | +-----------------+-------------+ |
Empêchons l’optimiseur d’utiliser l’index rental_date:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
12:20 daz$sakila> EXPLAIN SELECT * FROM rental_daz <strong>IGNORE INDEX(rental_date)</strong> WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR)\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental_daz type: ALL possible_keys: NULL <span style="color: #ff0000;"><strong>key: NULL</strong></span> key_len: NULL ref: NULL rows: 16298 Extra: Using where |
L’optimiseur n’utilise donc pas l’index (il ne le voit même pas). Un full table scan est donc effectué. C’est à priori plus coûteux que d’utiliser l’index. Regardons le coût de cette requêtes.
|
1 2 3 4 5 6 |
16:18 daz$sakila> SHOW STATUS LIKE 'Last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | <strong>3356.599000</strong> | +-----------------+-------------+ |
Malgré le full table scan, cette requête est moins coûteuse que celle qui utilise l’index !!!
Essayons de vérifier cela en durée. Le client mysqlslap est tout indiqué :
mysqlslap -uroot -proot –create-schema=sakila -i50
-q »SELECT * FROM rental_daz WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR); »
|
1 |
Benchmark |
|
1 |
Average number of seconds to run all queries: <span style="color: #ff0000;"><strong>0.287</strong></span> seconds |
|
1 |
Minimum number of seconds to run all queries: 0.140 seconds |
|
1 |
Maximum number of seconds to run all queries: 1.172 seconds |
|
1 |
Number of clients running queries: 1 |
|
1 |
Average number of queries per client: 1 |
mysqlslap -uroot -proot –create-schema=sakila -i50
-q »SELECT * FROM rental_daz ignore index(rental_date) WHERE rental_date > SUBDATE(now(), INTERVAL 3 YEAR); »
|
1 |
Benchmark |
|
1 |
Average number of seconds to run all queries: <span style="color: #ff0000;"><strong>0.167</strong></span> seconds |
|
1 |
Minimum number of seconds to run all queries: 0.078 seconds |
|
1 |
Maximum number of seconds to run all queries: 1.094 seconds |
|
1 |
Number of clients running queries: 1 |
|
1 |
Average number of queries per client: 1 |
mysqlslap confirme bien que le full table scan est, dans ce cas précis, plus performant que la recherche indexée par intervalle. On se trouve bien dans un cas où l’optimiseur se trompe.
MySQL Geek, Architecte, DBA, Consultant, Formateur, Auteur, Blogueur et Conférencier.
—–
Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–
Je n’ai pas bien compris la conclusion. En quoi l’optimiseur se trompe t’il ? On arrive bien à un résultat inférieur sans utiliser l’index en utilisant Last_query_cost AINSI qu’avec mysqlslap.
Bonjour Riplay,
effectivement, on arrive bien à un résultat inférieur sans utiliser l’index, mais ce n’est pas ce plan qui est choisit par l’optimiseur, car il préfère utiliser l’index.
Le plan d’exécution optimal est choisit, dans l’exemple, grâce à la clause ignore index(rental_date). a noter que ce plan est plus performant dans ce cas là. Avec une autre distribution des données ce ne serait peut être pas le cas !
J’ai laissé reposer cette nuit, et j’ai compris. Merci.