1/ Le mystère de la variable Last_query_cost

MySQL utilise un optimiseur à base de coûts. Le plan d’exécution de la requête choisit est celui dont le coût est le plus faible. Ce dernier peut être visualisé grâce à la variable Last_query_cost.
Son unité est le coût (encore lui) des accès aléatoires en lecture de pages de 4ko.
Étrangement cette variable est assez peu/mal documentée. Voici ce qu’on retrouve dans la doc officielle de MySQL
Je cite:
Last_query_cost
The total cost of the last compiled query as computed by the query optimizer. This is useful for comparing the cost of different query plans for the same query. The default value of 0 means that no query has been compiled yet. The default value is 0.Last_query_cost has session scope.
TheLast_query_cost value can be computed accurately only for simple “flat” queries, not complex queries such as those with subqueries orUNION. For the latter, the value is set to 0.

La valeur de Last_query_cost est parfois déconcertante, même avec MySQL 5.6. Voyez par vous même…

Quelques informations avant d’entrer dans le vif du sujet.
Server version:        5.6.9-rc MySQL Community Server (GPL)

Configuration de l’instance:
Innodb buffer pool size = 32 Go
max_heap_table_size = tmp_table_size = 512 Mo

Table:
CREATE TABLE bills (
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
 d datetime NOT NULL,
 price smallint(5) unsigned NOT NULL,
 PRIMARY KEY (id),
 KEY d (d)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

SHOW TABLE STATUS:
*************************** 1. row ***************************
          Name: bills
        Engine: InnoDB
       Version: 10
    Row_format: Compact
          Rows: 176268
Avg_row_length: 56
    Data_length: 9961472
Max_data_length: 0
  Index_length: 4210688
     Data_free: 2097152
                   …
     Collation: utf8_swedish_ci
=> Données + Index:  environs 16 Mo

L’idée est d’exécuter la requête ci-dessous, avec différents “hint” est de voir les plans d’exécution (QEP) générés par MySQL ainsi que leurs coûts respectifs.
SELECT d,avg(price) FROM bills GROUP BY d

2 QEP possibles:
– Faire un full table scan, créer une table temporaire pour regrouper et ordonner (particularité du GROUP BY de MySQL) les données en fonction de la date (d) pour calculer la moyenne des prix (avg(price)).
– Faire un full index scan sur d, ce qui évite le tri et la création de la table temporaire. Cependant le “price” n’étant pas dans l’index, l’info doit être récupéré dans la couche “data”

Query 1/ EXPLAIN  SELECT d,avg(price) FROM bills GROUP BY d\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: bills
        type: index
possible_keys: NULL
         key: d
     key_len: 5
         ref: NULL
        rows: 176268
       Extra: NULL

Le choix de l’optimiseur est assez intuitif, il se sert du fait que l’index d est trié (type: index = full index scan) pour optimiser le GROUP BY (regroupement + tri), c’est à dire ne pas créer de table temporaire.

Le coût de cette opération est:
SHOW STATUS LIKE ‘Last_query_cost;
+—————–+—————+
| Variable_name   | Value         |
+—————–+—————+
| Last_query_cost | 212129.599000 |
+—————–+—————+

Question de psychopathe, que se passe t’il si je force l’optimiseur à utiliser l’index qu’il dit qu’il va utiliser ? (c’est vicieux je sais :D)

Query 2/ EXPLAIN  SELECT d,avg(price) FROM bills USE INDEX(d) GROUP BY d\G *************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: bills
        type: index
possible_keys: NULL
         key: d
     key_len: 5
         ref: NULL
        rows: 176268
       Extra: NULL

Alors pour être précis, là je ne le force pas vraiment, je l’incite (fortement) à l’utiliser. Mais sans surprise, il l’utilise (ouf !), en d’autre terme le QEP est le même que précédemment.

Et qu’en est il du coût ?

SHOW STATUS LIKE ‘Last_query_cost;
+—————–+—————+
| Variable_name   | Value         |
+—————–+—————+
| Last_query_cost | 212129.599000 |
+—————–+—————+

Pareil que précédemment, circuler il n’y a rien à voir !

Ok est si là je le force vraiment, avec FORCE INDEX (principale différence entre USE INDEX et FORCE INDEX est qu’avec le second l’optimiseur choisira (si c’est possible bien sûr) d’utiliser l’index en lieu est place d’un full table scan)

Query 3/  EXPLAIN  SELECT d,avg(price) FROM bills FORCE INDEX(d) GROUP BY d\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: bills
        type: index
possible_keys: NULL
         key: d
     key_len: 5
         ref: NULL
        rows: 176268
       Extra: NULL

Là encore, (sans surprise) le QEP est le même que précédemment. Et le coût ?

SHOW STATUS LIKE ‘Last_query_cost;
+—————–+—————+
| Variable_name   | Value         |
+—————–+—————+
| Last_query_cost | 387790.599000 |
+—————–+—————+

… WTF …
Bon là pour être franc, je n’ai pas d’explications rationnelles. Serait ce une feature ? (rationnelle on à dit !!!) ok ok bah un bug alors.
Inutile de préciser que je me suis empressé de tester avec d’autres versions de MySQL (psychopate !) et le résultat est sans appel:
Avec MySQL 5.5, MariaDB 5.5 & Percona server 5.5 le coût du FORCE INDEX est identique aux 3 autres coûts.

J’opterai donc pour une petite régression de l’optimiseur, bref un BUG

Soit ! si j’empêche l’optimiseur d’utiliser l’index:
Query 4/ EXPLAIN  SELECT d,avg(price) FROM bills IGNORE INDEX(d) GROUP BY d\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: bills
        type: ALL
possible_keys: NULL
         key: NULL
     key_len: NULL
         ref: NULL
        rows: 176268
       Extra: Using temporary; Using filesort

C’est plutôt clair, full table scan (type: ALL), plus création d’un table temporaire (Using temporary) pour regrouper les données et les trier (Using filesort). C’est le prix à payer pour ne pas utiliser l’index. Ça à l’air vachement coûteux tout ça, Voyons ce qu’en pense MySQL

SHOW STATUS LIKE ‘Last_query_cost’;
+—————–+—————+
| Variable_name   | Value         |
+—————–+—————+
| Last_query_cost | 212129.599000 |
+—————–+—————+

Humm pour l’optimiseur, le coût est le même, pas forcément intuitif tout ça Pour rappel (ou pas)  les paramètres pris en compte par l’optimiseur sont (entre autre):
– Pour les coûts I/O: information sur les enregistrements (nombre, taille,), informations sur les index(null, unique, cardinalité…)…
– Pour les coûts CPU: l’évaluation des conditions, comparaison du rapport index/enregistrements

Force est de constater que selon l’optimiseur, le coût du FTS + table temp + tri et équivalent, pour cette requête au FIS.

Bilan de cette première partie:
– Il y a manifestement un bug de l’optimiseur en MySQL 5.6.9 lors du calcul du coût d’une requête avec la clause FORCE INDEX
–  Dans notre exemple, le coût d’un full index scan est le même qu’un full table scan + Using temporary + Using filesort

Mais alors, quel QEP donne t’il le meilleur temps d’exécution ?

Cette question est traitée dans la 2ème partie de cet article

(Aller à la 2ème partie..)

 

Filed under:
optimisation by Olivier DASINI