Utiliser une sous requête c’est mal ? (suite) part 1-3

mai 27, 2013

Tags: loosescan, matérialisation, optimiseur, optimizer_switch, semi-jointure

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

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 :

2 tables temporaires pour le distinct et la matérialisation : (Created_tmp_tables | 2)

La semi-jointure : (Handler_read_next | 243)
La sous-requête elle, est responsable de : (Handler_read_key | 237)
Remplissage + lecture des tables temporaire : (Handler_read_rnd_next | 245 + Handler_tmp_write | 243)

Bench

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…

Olivier DASINI

Architecte Solution Cloud chez Oracle
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
—–

dasini.net – Journal d'un expert MySQL