Comme promit, voici la suite de l’article Utiliser une sous-requête c’est mal ?
L’idée ici est de répondre aux interrogations de svar et d’en profiter pour explorer les nouvelles possibilités de la variante stable de MySQL qui possède l’optimiseur le plus avancé, c’est à dire MariaDB 5.5.
Préambule
En pré-requis, je vous invite à explorer la doc officielle de MySQL au sujet de la variable optimizer_switch, qui permet de sélectionner les algorithmes d’optimisation, ainsi que les différentes subtilités implémentées dans l’optimiseur de MariaDB.
La valeur par défaut sur MariaDB 5.5.30 de la variable optimizer_switch est:
mariadb5.5> SHOW VARIABLES LIKE ‘optimizer_switch’\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,
index_merge_intersection=on,index_merge_sort_intersection=off,
engine_condition_pushdown=off,index_condition_pushdown=on,
derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,
materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,
partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,
mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,
join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,
optimize_join_buffer_size=off,table_elimination=on,extended_keys=off
Le truc super lisible en fait !
Mais comme je suis un mec super sympa, j’ai mis en gras les directives qui vont nous intéresser dans cet article.
Les patterns des tests
Pour atteindre les objectifs fixés, j’ai effectué 2 niveaux de tests. Le premier niveau va me fournir des données “objectives”. Il consiste à exécuter la requête avec différentes directives d’optimisation et d’analyser les “status” ainsi que les plans d’exécution associés.
Niveau stats
-
SET optimizer_switch=’default’ : réinit des directives d’optimisation
-
SET optimizer_switch=’valeur adéquate’ : choix des directives d’optimisation
-
EXPLAIN EXTENDED : QEP et infos de réécritures de la requête
-
SHOW WARNINGS : pour savoir comment l’optimiseur à réécrit la requête
-
FLUSH STATUS : réinit des stats à 0
-
exécution de la requête
-
SHOW STATUS : pour récupérer les variables d’états, Handler* & Created_tmp
Avec le second niveau de tests, je vais récupérer une info “subjective”, le temps. Cela consiste à récupérer la durée minimale d’exécution de la requête avec les différentes directives d’optimisations à l’aide de l’outil mysqlslap.
Niveau temps
-
SET GLOBAL optimizer_switch=’default’
-
SET GLOBAL optimizer_switch=’valeur adéquate’
-
mysqlslap -c1 -i200 -q »la requête« : requête unitaire jouée plusieurs fois ie buffers chaud
A noter que le nombre d’itération (200) à été réduit à 5 lorsque la requête dure de l’ordre de la 10aine de minutes (bah oui, j’ai une vie aussi).
Contexte
Pour rappel, le problème initiale est une sous requête problématique en MySQL (Percona server) 5.5 :
SELECT DISTINCT OtherID
FROM MyTable
WHERE Status = 1
AND Stage = 4
AND Invisible = 0
AND MainID IN
(
SELECT MainID
FROM MyTable
WHERE OtherID = 2779262
AND Stage = 0
AND Invisible = 0
)
+———-+
| OtherID |
+———-+
…
7 rows in set (44 min 56.66 sec) <= A froid
L’idée ici n’est pas de comparer le temps d’exécution de la requête sur MariaDB par rapport à Percona server (les serveurs et les paramètres sont différents), mais bien de voir et de comprendre les (nouveaux) choix de l’optimiseur de MariaDB 5.5.
Traditional behavior (IN to EXISTS strategy)
TEST 1
Traditionnellement l’optimiseur converti la sous-requête IN en EXIST. Pour simuler ce comportement il faut désactiver la matérialisation et la semi-jointure.
SET optimizer_switch=’materialization=off,semijoin=off’;
QEP
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: MyTable
type: ref
possible_keys: StageMainIDOtherID,…
key: StageMainIDOtherID
key_len: 1
ref: const
rows: 153455020
filtered: 100.00
Extra: Using where; Using temporary
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: MyTable
type: index_subquery
possible_keys: MainIDOtherIDStatus,…
key: MainIDOtherIDStatus
key_len: 8
ref: func,const
rows: 1
filtered: 100.00
Extra: Using where
Intuitivement on peut penser que la requête interne est exécutée en premier, puis qu’elle génère une liste d’ID (MainID) qui est ensuite passée dans la clause IN, du genre :
… MainID IN (123, 456, 789).
Eh ben non !
Comme convenu, le QEP est semblable à celui utilisé avec MySQL / percona server 5.5.
En fait, l’optimiseur transforme le clause IN en EXISTS. La table externe est lue la première sur environ 50% de ses lignes (rows: 153455020), ce qui dans notre cas fait beaucoup et est jointe avec la table interne.
La sous-requête dépend alors des données de la requête externe (select_type: DEPENDENT SUBQUERY).
La requête réécrite par l’optimiseur ressemble alors à peu près à :
SELECT DISTINCT OtherID
FROM MyTable AS MyExtTable
WHERE Status = 1
AND Stage = 4
AND Invisible = 0
AND EXISTS
(
SELECT 1
FROM MyTable AS MyInnTable
WHERE OtherID = 2779262
AND Stage = 0
AND Invisible = 0
AND MyExtTable.MainID = MyInnTable.MainID
)
Optimizer rewriting
select distinct OtherID AS OtherID from MyTable
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and <expr_cache><MainID>(<in_optimizer>(MainID,<exists>(<index_lookup>(<cache>(MainID) in MyTable on MainIDOtherIDStatus
where (((OtherID = 2779262) and (Stage = 0) and (Invisible = 0)) and (<cache>(MainID) = MainID) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)))))))
Confirmation que l’on a bien une transformation IN to EXISTS.
Status
+—————————+———–+
| Variable_name | Value |
+—————————+———–+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 2 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 44655686 |
| Handler_read_last | 0 |
| Handler_read_next | 153049748 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 8 |
| Handler_tmp_write | 206 |
| Handler_write | 0 |
+—————————+———–+
Beaucoup de lecture dans l’index (Handler_read_key | 44655686). Le problème ici est le nombre élevé de lectures transverse de l’index (Handler_read_next | 153049748) qui est très coûteux, surtout si les IO sont sur disque, et c’est encore pire s’ils sont aléatoires ie random IO disk.
Contrairement à MySQL 5.5 où il n’y a qu’une seule table temporaire créée, ici il y en a 2 (Created_tmp_table | 2), l’une pour le dédoublonnage lié à la clause DISTINCT et l’autre… ben je ne sais pas :/
En profilant la requête avec la commande SHOW PROFILE, il y est bien noté que 2 tables temporaires sont supprimées, mais dans le même temps, une seule est créée…
Si quelqu’un à une explication, je suis preneur.
Bench
daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i5 -q »SELECT DISTINCT OtherID FROM MyTable WHERE Status = 1 AND Stage = 4 AND Invisible = 0 AND MainID IN ( SELECT MainID FROM MyTable WHERE OtherID = 2779262 AND Stage = 0 AND Invisible = 0 ); »
Benchmark
Minimum number of seconds to run all queries: 550.444 seconds
Les meilleures performances de cette requête, dans ces conditions, sont de l’ordre de la 10aine de minutes, ce qui est rarement acceptable en OLTP.
Pour simuler l’”intelligence” de l’optimiseur de MySQL / Percona Server 5.5, il a fallu désactiver 2 directives d’optimisation (la matérialisation et la semi-jointure). Voyons ce qu’il en est en ne désactivant qu’une seule d’entre elles, à tour de rôle.
MATERIALIZATION strategy (disable semi-join)
TEST 2
Pour désactiver la semi-jointure, il suffit de mettre la directive suivante à OFF:
SET optimizer_switch=’semijoin=off’;
La stratégie d’optimisation utilisée par l’optimiseur est alors la matérialisation. En 2 mots, le résultat de la sous-requête est stocké dans une table temporaire qui contient un index unique. Cet index sert à dédoublonner les valeurs insérée et à récupérer les infos de manière performante (comme un index classique), qui vont alimenter la requête externe.
QEP
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: MyTable
type: ref
possible_keys: StageMainIDOtherID, …
key: StageMainIDOtherID
key_len: 1
ref: const
rows: 153455020
filtered: 100.00
Extra: Using where; Using temporary
*************************** 2. row ***************************
id: 2
select_type: MATERIALIZED
table: MyTable
type: ref
possible_keys: MainIDOtherIDStatus,OtherIDStageStatusInvisibleMainID,StageMainIDOtherID, …
key: OtherIDStageStatusInvisibleMainID
key_len: 5
ref: const,const
rows: 235
filtered: 100.00
Extra: Using where; Using index
Là encore, selon le QEP la table externe est lue la première sur environ 50% de ses lignes (rows: 153455020). Ce qui change là, c’est que la stratégie d’optimisation retenue est la matérialisation.
Autres différences avec le QEP originel, un covering index (Extra: Using where; Using index) sur les 235 (une estimation) enregistrements (rows: 235) de la table matérialisée.
Optimizer rewriting
select distinct OtherID AS OtherID from MyTable
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and <expr_cache><MainID>(<in_optimizer>(MainID,MainID in
( <materialize> (select MainID from MyTable
where ((OtherID = 2779262) and (Stage = 0) and (Invisible = 0)) ), <primary_index_lookup>(MainID in <temporary table> on distinct_key where ((MainID = <subquery2>.MainID)))))))
Confirmation que l’on à bien la matérialisation de la sous-requête sous la forme d’une table temporaire indexée(<primary_index_lookup>MainID in <temporary table> on distinct_key).
A noter que l’index à également servi au dédoublonnage.
Status
+—————————+———–+
| Variable_name | Value |
+—————————+———–+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 3 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 44655687 |
| Handler_read_last | 0 |
| Handler_read_next | 153049981 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 8 |
| Handler_tmp_write | 442 |
| Handler_write | 0 |
+—————————+———–+
Niveau table temporaires, on a 1 table temporaire pour le DISTINCT + 1 table pour la matérialisation + 1 table temporaire mystère (Created_tmp_tables | 3).
Avec une table temporaire supplémentaire, il y a alors plus d’écriture (Handler_tmp_write | 442).
442 (Handler_tmp_write) – 235 (MATERIALIZED rows) = 207 (à rapporter au 206 du Handler_tmp_write du QEP originel)
A l’aide de toutes ces informations, lequel des 2 modes est le plus performant ?
D’un point de vu status, il y a 1 table temporaire de plus pour T2, mais les tables étant créées entièrement en mémoire, le coût reste donc faible. De plus le nombre de Handler_read_next est sensiblement égale. Même s’il n’est pas réparti de la même manière.
Bref, niveau status, exaequo entre T1 & T2
Avec explain, le nombre de rows de T2 est 235 fois plus important que pour T1. Cependant, T2 fournit une matérialisation et index covering index alors qu’avec T1 on est dans de la sous-requête corrélée !
Bref niveau explain, c’est pas clair non plus 😀 mais la matérialisation se faisant en mémoire, elle devrait être moins coûteuse.
Bench
daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i5 -q »SELECT DISTINCT OtherID FROM MyTable WHERE Status = 1 AND Stage = 4 AND Invisible = 0 AND MainID IN ( SELECT MainID FROM MyTable WHERE OtherID = 2779262 AND Stage = 0 AND Invisible = 0 ); »
Benchmark
Minimum number of seconds to run all queries: 430.712 seconds
T2 s’exécute avec 2 minutes de moins que T1.
SEMIJOIN strategy (disable materialization)
TEST 3
Pour désactiver la matérialisation, il suffit de mettre la directive suivante à OFF:
SET optimizer_switch=’materialization=off’;
La stratégie d’optimisation utilisée par l’optimiseur est alors la semi-jointure (une sorte de jointure interne où seule une occurrence des valeurs jointes est récupérée).
QEP
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: MyTable
type: index
possible_keys: MainIDOtherIDStatus,OtherIDStageStatusInvisibleMainID,StageMainIDOtherID
key: OtherIDStageStatusInvisibleMainID
key_len: 15
ref: NULL
rows: 306910041
filtered: 0.00
Extra: Using where; Using index; Using temporary; LooseScan
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: MyTable
type: ref
possible_keys: MainIDOtherIDStatus,StageMainIDOtherID
key: MainIDOtherIDStatus
key_len: 4
ref: MyTable.MainID
rows: 1
filtered: 100.00
Extra: Using index condition; Using where
Le QEP obtenu est un full index scan aka FIS (type: index) avec covering index (Extra: Using index;). En général un FIS est plutôt une mauvaise nouvelle pour les perfs, sauf dans le cas d’un index covering.
L’algorithme utilisé par l’optimiseur pour la semi-jointure est le LooseScan (Extra: LooseScan).
A noter l’utilisation de l’optimisation index condition pushdown ou ICP (Extra: Using index condition). On en y revient plus tard.
Optimizer rewriting
select distinct OtherID from MyTable semi join (MyTable)
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)
and (MainID = MainID))
Confirmation que l’on a bien une transformation en semi-jointure.
Status
+—————————+———–+
| Variable_name | Value |
+—————————+———–+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 236 |
| Handler_read_last | 0 |
| Handler_read_next | 306909498 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 8 |
| Handler_tmp_write | 7 |
| Handler_write | 0 |
+—————————+———–+
Les informations d’état sont assez claires (si si vous allez voir):
-
Le FIS est un parcours transverse complet de l’arbre d’index: (Handler_read_next | 306909498 et Handler_read_first | 1)
-
La sous-requête elle, est responsable de: (Handler_read_key | 236)
-
Le distinct est responsable de la création de la table temporaire: (Created_tmp_tables | 1)
-
Remplissage de la table temporaire: (Handler_tmp_write | 7)
-
Lecture des données de la table temporaire: (Handler_read_rnd_next | 8)
T2 vs T3 ça donne quoi ?
Là encore ce n’est pas trivial de comparer les QEP et status des 2 requêtes. On peut cependant raisonnablement penser que le FIS avec covering index de T3 va générer moins de random IO que T2.
Bench
daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i200 -q »SELECT DISTINCT OtherID FROM MyTable WHERE Status = 1 AND Stage = 4 AND Invisible = 0 AND MainID IN ( SELECT MainID FROM MyTable WHERE OtherID = 2779262 AND Stage = 0 AND Invisible = 0 ); »
Benchmark
Minimum number of seconds to run all queries: 130.531 seconds
T3 s’exécute avec 3m20 de moins que T2.
T3 s’exécute avec 5m20 de moins que T1.
Optimizer choice
TEST 4
Comme je l’ai mentionné en introduction, l’optimiseur de MariaDB est le plus avancé (en matière de possibilités d’optimisation) dans l’univers MySQL. Voyons comment cet optimiseur, avec les réglages par défaut, gère cette requête.
La commande suivante permet de réinitialiser, les directives d’optimisation:
SET optimizer_switch=’default’;
QEP
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <subquery2>
type: ALL
possible_keys: distinct_key
key: NULL
key_len: NULL
ref: NULL
rows: 235
filtered: 100.00
Extra: Using temporary
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: MyTable
type: ref
possible_keys: MainIDOtherIDStatus,StageMainIDOtherID
key: MainIDOtherIDStatus
key_len: 4
ref: MyTable.MainID
rows: 1
filtered: 100.00
Extra: Using index condition; Using where
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: MyTable
type: ref
possible_keys: OtherIDStageStatusInvisibleMainID, StageMainIDOtherID, …
key: OtherIDStageStatusInvisibleMainID
key_len: 5
ref: const,const
rows: 235
filtered: 100.00
Extra: Using where; Using index; Distinct
La sous-requête est matérialisée et on a également une semi-jointure entre cette dernière est la table de la requête externe (MyTable).
Optimizer rewriting
select distinct OtherID AS OtherID from MyTable semi join (MyTable)
where ((Status = 1) and (Stage = 4) and (Invisible = 0) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)
and (MainID = MainID))
L’information sur la semi-join est explicite (MyTable semi join (MyTable)… (MainID = MainID)).
Par contre, pas de trace ici de la matérialisation…
Status
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 2 |
| Handler_mrr_key_refills | 0 |
| Handler_mrr_rowid_refills | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 237 |
| Handler_read_last | 0 |
| Handler_read_next | 243 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_deleted | 0 |
| Handler_read_rnd_next | 245 |
| Handler_tmp_write | 243 |
| Handler_write | 0 |
+—————————+——-+
Niveau handler, que des petites valeurs, ça sent bon la perf tout ça !!!
Grosso-modo, ces infos nous disent :
Bench
daz@sql:~/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i200 -q »SELECT DISTINCT OtherID FROM MyTable WHERE Status = 1 AND Stage = 4 AND Invisible = 0 AND MainID IN ( SELECT MainID FROM MyTable WHERE OtherID = 2779262 AND Stage = 0 AND Invisible = 0 ); »
Benchmark
Minimum number of seconds to run all queries: 0.026 seconds
T4 est plus rapide que T1 de plus de 9 minutes.
T4 est plus rapide que T2 de plus de 7 minutes.
T4 est plus rapide que T3 de plus de 2 minutes.
En image, en temps relatif par rapport à T1 (la requête la plus longue) ça donne :
Avec les versions de MySQL / Percona server inférieures ou égales à 5.5 et les versions de MariaDB inférieures ou égales à 5.2 (de mémoire), les sous-requêtes peuvent poser de gros problèmes de performances. Il faut donc bien tester ces requêtes avant de les passer en prod.
Une bonne raison supplémentaire pour tester (et migrer) vers MariaDB 5.5 ou MySQL 5.6 (qui possède également les optimisations semijoin et materialization).
A suivre, dans la deuxième partie de cet article : Index Condition Pushdown et Multi Range Read…
J’aime ça :
J’aime chargement…