2/ FTS ou FIS

(Aller à la 1ère partie)
Avant de répondre explicitement à la question, un petit zoom sur l’une des nombreuses nouveautés de MySQL 5.6. La commande EXPLAIN s’est enrichie de la clause format=json. Elle permet d’avoir une version un peu plus détaillée que l’EXPLAIN classique.

Query 1/ EXPLAIN format=json  SELECT d,avg(price) FROM bills GROUP BY d\G
*************************** 1. row ***************************
EXPLAIN: {
« query_block »: {
« select_id »: 1,
« grouping_operation »: {
« using_filesort »: false,
« table »: {
« table_name »: « bills »,
« access_type »: « index »,
« key »: « d »,
« used_key_parts »: [
« d »
],
« key_length »: « 5 »,
« rows »: 176268,
« filtered »: 100
}
}
}
}

Query 4/ EXPLAIN format=json  SELECT d,avg(price) FROM bills IGNORE INDEX(d) GROUP BY d\G
*************************** 1. row ***************************
EXPLAIN: {
« query_block »: {
« select_id »: 1,
« grouping_operation »: {
« using_temporary_table »: true,
« using_filesort »: true,
« table »: {
« table_name »: « bills »,
« access_type »: « ALL »,
« rows »: 176268,
« filtered »: 100
}
}
}
}

Sans surprise, on retrouve les informations de la version classique de l’EXPLAIN, a savoir:
FIS d’un coté (choix de l’optimiseur) contre FTS + création d’une table temporaire + tri de l’autre. Néanmoins, pour certains types de requêtes, les informations générées par la clause format=json peuvent être plus pertinentes.

Comme nous l’avons également vu dans la première partie de cet article, le GROUP BY de MySQL génère systématiquement un tri (« using_filesort »: true,). Ce tri n’étant pas toujours nécessaire, il y a une astuce pour ne pas le générer. Ajouter la clause ORDER BY NULL

Query 5/ EXPLAIN format=json  SELECT d,avg(price) FROM bills IGNORE INDEX(d) GROUP BY d ORDER BY NULL\G
*************************** 1. row ***************************
EXPLAIN: {
« query_block »: {
« select_id »: 1,
« ordering_operation »: {
     « using_filesort »: false,
« grouping_operation »: {
       « using_temporary_table »: true,
       « using_filesort »: false,
« table »: {
« table_name »: « bills »,
« access_type »: « ALL »,
« rows »: 176268,
« filtered »: 100
}
}
}
}
}

Query 5/ EXPLAIN  SELECT d,avg(price) FROM bills IGNORE INDEX(d) GROUP BY d ORDER BY NULL\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

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

Il y a une incidence notable sur le coût de la requête, 35861.599 au lieu de 212129.599.
Pas vraiment surprenant !

Niveau performance ça donne quoi ?

Quick bench (au meilleur des 4 tests ie 4 fois 100 runs):

Server version:        5.6.9-rc MySQL Community Server (GPL)

Taille: environs 16 Mo ((Données + Index))

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

Query 1:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills  GROUP BY d ; »
Benchmark
Average number of seconds to run all queries: 0.203 seconds
   Minimum number of seconds to run all queries: 0.199 seconds
Maximum number of seconds to run all queries: 0.224 seconds

Query 2:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills USE INDEX(d) GROUP BY d ; »
Benchmark
Average number of seconds to run all queries: 0.206 seconds
   Minimum number of seconds to run all queries: 0.198 seconds
Maximum number of seconds to run all queries: 0.217 seconds

Query 3:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills FORCE INDEX(d) GROUP BY d ; »
Benchmark
Average number of seconds to run all queries: 0.203 seconds
   Minimum number of seconds to run all queries: 0.199 seconds
Maximum number of seconds to run all queries: 0.219 seconds

Le temps d’exécution des requêtes 1, 2 et 3 est le même ce qui est logique car le plan d’exécution est le même, en d’autres termes, forcer l’utilisation de l’index ou non n’a pas d’incidence sur la durée des requêtes.
De plus ce résultat confirme que le coût bizarre du FORCE INDEX est bien un bug (cf la première partie de cet article).

Query 4:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills IGNORE INDEX(d) GROUP BY d ; »
Benchmark
Average number of seconds to run all queries: 0.168 seconds
   Minimum number of seconds to run all queries: 0.166 seconds
Maximum number of seconds to run all queries: 0.172 seconds

Plusieurs indications:

• Le full table scan donne un meilleur temps d’exécution que le full index scan:
  • 0.166 vs 0.199
  • D’une manière générale, le FTS et plus rapide que le FIS sauf dans le cas d’un index covering (using index)

 

• Le coût de la création de la table temporaire et du tri n’a pas un impact suffisant pour que le temps d’exécution soit supérieur au FIS:
  • Created_tmp_tables: 1, Created_tmp_disk_tables: 0,  Sort_merge_passes: 0
  • Seules 231 lignes sont à trier (Handler_read_rnd = 231)
  • Ces opérations se déroulent en mémoire (ie pas d’accès disque). C’est essentiellement un coût processeur.

Et avec l’astuce ORDER BY NULL ?

Query 6:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills USE INDEX(d) GROUP BY d ORDER BY NULL; »
Benchmark
Average number of seconds to run all queries: 0.203 seconds
   Minimum number of seconds to run all queries: 0.199 seconds
Maximum number of seconds to run all queries: 0.218 seconds

Pas de tri, pas d’incidence sur les perfs, trivial

Query 5:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills IGNORE INDEX(d) GROUP BY d ORDER BY NULL; »
Benchmark
Average number of seconds to run all queries: 0.168 seconds
   Minimum number of seconds to run all queries: 0.166 seconds
Maximum number of seconds to run all queries: 0.172 seconds

Le coût du tri est faible, négligeable => pas d’incidences sur les perfs

En diminuant la taille des paramètres max_heap_table_size & tmp_table_size :

Taille: environs 16 Mo ((Données + Index))

Configuration de l’instance:
Innodb buffer pool size = 32 Go
max_heap_table_size = tmp_table_size = 16 Ko

Infos sur les variables tmp_table_size & max_heap_table_size

En diminuant max_heap_table_size & tmp_table_size, pour forcer la création de la table temporaire sur le disque:

Query 2:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills USE INDEX(d) GROUP BY d; »
Benchmark
Average number of seconds to run all queries: 0.203 seconds
   Minimum number of seconds to run all queries: 0.199 seconds
Maximum number of seconds to run all queries: 0.221 seconds

Pas d’incidence sur les perfs, car pas de tri donc pas de créations de table temporaire. Trivial !

Query 4:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills IGNORE INDEX(d) GROUP BY d; »
Benchmark
Average number of seconds to run all queries: 0.767 seconds
   Minimum number of seconds to run all queries: 0.757 seconds
Maximum number of seconds to run all queries: 0.778 seconds

Sans surprise, c’est plus long. La table temporaire étant créée sur disque.

Query 5:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills IGNORE INDEX(d) GROUP BY d ORDER BY NULL; »
Benchmark
Average number of seconds to run all queries: 0.765 seconds
   Minimum number of seconds to run all queries: 0.756 seconds
Maximum number of seconds to run all queries: 0.773 seconds

Le tri étant fait en mémoire, les variables max_heap_table_size & tmp_table_size n’ont pas d’incidences sur le tri.

En diminuant la taille du paramètre innodb_buffer_pool_size :

Taille: environs 16 Mo ((Données + Index))

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

Query 2:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills USE INDEX(d) GROUP BY d ; »
Benchmark
Average number of seconds to run all queries: 1.214 seconds
   Minimum number of seconds to run all queries: 1.174 seconds
Maximum number of seconds to run all queries: 1.243 seconds

Query 4:
daz@mysql:~/mysql_5_6_5$ ./mysqlslap –create-schema=test -c1 -i100 -q »SELECT avg(price) FROM test.bills IGNORE INDEX(d) GROUP BY d ; »
Benchmark
Average number of seconds to run all queries: 0.179 seconds
   Minimum number of seconds to run all queries: 0.174 seconds
Maximum number of seconds to run all queries: 0.185 seconds

L’écart de performance est encore plus important avec des données qui ne tiennent (apparemment) pas entièrement en mémoire. Le sur-coût I/O imposé par la double recherche (dans l’index secondaire, puis random I/O dans la clé primaire) n’est pas anodin.

Conclusion
– Le choix de l’optimiseur n’est parfois pas le bon
– Le choix le plus intuitif n’est parfois pas le plus performant
– Toujours valider ses hypothèses par la pratique
– Se méfier des Full Index Scan, qui peuvent être vraiment plus coûteux qu’un Full Table Scan en cas de non covering index

Filed under:
optimisation by Olivier DASINI