{"id":1425,"date":"2013-05-27T14:05:36","date_gmt":"2013-05-27T13:05:36","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=1425"},"modified":"2013-05-30T16:02:37","modified_gmt":"2013-05-30T15:02:37","slug":"utiliser-une-sous-requete-cest-mal-suite-part-1-3","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2013\/05\/27\/utiliser-une-sous-requete-cest-mal-suite-part-1-3\/","title":{"rendered":"Utiliser une sous requ\u00eate c\u2019est mal ? (suite) part 1-3"},"content":{"rendered":"

Comme promit, voici la suite de l\u2019article Utiliser une sous-requ\u00eate c\u2019est mal ?<\/a><\/p>\n

L\u2019id\u00e9e ici est de r\u00e9pondre aux interrogations de svar<\/a> et d\u2019en profiter pour explorer les nouvelles possibilit\u00e9s de la variante stable de MySQL qui poss\u00e8de l\u2019optimiseur le plus avanc\u00e9<\/strong>, c\u2019est \u00e0 dire MariaDB 5.5<\/a><\/strong>.<\/p>\n

 <\/p>\n

Pr\u00e9ambule<\/h2>\n

En pr\u00e9-requis, je vous invite \u00e0 explorer la doc officielle de MySQL au sujet de la variable optimizer_switch<\/a>, qui permet de s\u00e9lectionner les algorithmes d\u2019optimisation, ainsi que les diff\u00e9rentes subtilit\u00e9s impl\u00e9ment\u00e9es dans l\u2019optimiseur de MariaDB<\/a>.<\/p>\n

 <\/p>\n

La valeur par d\u00e9faut sur MariaDB 5.5.30 de la variable optimizer_switch<\/em><\/strong> est:<\/p>\n

 <\/p>\n

mariadb5.5> SHOW VARIABLES LIKE ‘optimizer_switch’\\G<\/span><\/address>\n
*************************** 1. row ***************************<\/span><\/address>\n
Variable_name: optimizer_switch<\/span><\/address>\n
\u00a0\u00a0\u00a0\u00a0\u00a0 Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,<\/span><\/address>\n
index_merge_intersection=on,index_merge_sort_intersection=off,<\/span><\/address>\n
engine_condition_pushdown=off,index_condition_pushdown=on<\/strong>,<\/span><\/address>\n
derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,<\/strong><\/span><\/address>\n
materialization=on,in_to_exists=on,semijoin=on<\/strong>,partial_match_rowid_merge=on,<\/span><\/address>\n
partial_match_table_scan=on,subquery_cache=on,mrr=off<\/strong>,mrr_cost_based=off,<\/span><\/address>\n
mrr_sort_keys=off<\/strong>,outer_join_with_cache=on,semijoin_with_cache=on,<\/span><\/address>\n
join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,<\/span><\/address>\n
optimize_join_buffer_size=off,table_elimination=on,extended_keys=off<\/span><\/address>\n

Le truc super lisible en fait !<\/p>\n

Mais comme je suis un mec super sympa, j\u2019ai mis en gras les directives qui vont nous int\u00e9resser dans cet article.<\/p>\n

 <\/p>\n

Les patterns des tests<\/h2>\n

 <\/p>\n

Pour atteindre les objectifs fix\u00e9s, j\u2019ai effectu\u00e9 2 niveaux de tests. Le premier niveau va me fournir des donn\u00e9es \u201cobjectives\u201d. Il consiste \u00e0 ex\u00e9cuter la requ\u00eate avec diff\u00e9rentes directives d\u2019optimisation et d\u2019analyser les \u201cstatus\u201d ainsi que les plans d\u2019ex\u00e9cution associ\u00e9s.<\/p>\n

Niveau stats<\/h3>\n
    \n
  1. \n

    SET optimizer_switch=’default’<\/strong> : r\u00e9init des directives d\u2019optimisation<\/p>\n<\/li>\n

  2. \n

    SET optimizer_switch=’<\/strong>valeur ad\u00e9quate\u2019<\/strong> : choix des directives d\u2019optimisation<\/p>\n<\/li>\n

  3. \n

    EXPLAIN EXTENDED<\/a><\/strong> : QEP et infos de r\u00e9\u00e9critures de la requ\u00eate<\/p>\n<\/li>\n

  4. \n

    SHOW WARNINGS<\/strong> : pour savoir comment l\u2019optimiseur \u00e0 r\u00e9\u00e9crit la requ\u00eate<\/p>\n<\/li>\n

  5. \n

    FLUSH STATUS<\/strong> : r\u00e9init des stats \u00e0 0<\/p>\n<\/li>\n

  6. \n

    ex\u00e9cution de la requ\u00eate<\/em><\/p>\n<\/li>\n

  7. \n

    SHOW STATUS<\/strong> : pour r\u00e9cup\u00e9rer les variables d\u2019\u00e9tats, Handler*<\/em> & Created_tmp<\/em><\/p>\n<\/li>\n<\/ol>\n

    Avec le second niveau de tests, je vais r\u00e9cup\u00e9rer une info \u201csubjective\u201d, le temps. Cela consiste \u00e0 r\u00e9cup\u00e9rer la dur\u00e9e minimale d\u2019ex\u00e9cution de la requ\u00eate avec les diff\u00e9rentes directives d\u2019optimisations \u00e0 l\u2019aide de l\u2019outil mysqlslap<\/a>.<\/p>\n

    Niveau temps<\/h3>\n
      \n
    1. \n

      SET GLOBAL optimizer_switch=’default’<\/strong><\/p>\n<\/li>\n

    2. \n

      SET GLOBAL optimizer_switch=’<\/strong>valeur ad\u00e9quate<\/em>\u2019<\/strong><\/p>\n<\/li>\n

    3. \n

      mysqlslap \u00a0-c1 -i200 -q\u00a0\u00bb<\/strong>la requ\u00eate<\/em>\u00ab\u00a0<\/strong> : requ\u00eate unitaire jou\u00e9e plusieurs fois ie buffers chaud<\/p>\n<\/li>\n<\/ol>\n

       <\/p>\n

      A noter que le nombre d\u2019it\u00e9ration (200) \u00e0 \u00e9t\u00e9 r\u00e9duit \u00e0 5 lorsque la requ\u00eate dure de l\u2019ordre de la 10aine de minutes (bah oui, j\u2019ai une vie aussi).<\/p>\n

      Contexte<\/h2>\n

       <\/p>\n

      Pour rappel, le probl\u00e8me initiale est une sous requ\u00eate probl\u00e9matique<\/a> en MySQL (Percona server) 5.5 :<\/p>\n

       <\/p>\n

      SELECT DISTINCT OtherID<\/address>\n
      FROM MyTable<\/address>\n
      WHERE Status = 1<\/address>\n
      \u00a0AND Stage = 4<\/address>\n
      \u00a0AND Invisible = 0<\/address>\n
      \u00a0AND MainID IN<\/address>\n
      \u00a0\u00a0\u00a0 \u00a0(<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0SELECT MainID<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0FROM MyTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0WHERE OtherID = 2779262<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0AND Stage = 0<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0AND Invisible = 0<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0)<\/span><\/address>\n
      +———-+<\/address>\n
      | OtherID |<\/address>\n
      +———-+<\/address>\n
      …<\/address>\n
      7 rows in set (44 min 56.66 sec)<\/span> <= A froid<\/strong><\/address>\n

      L\u2019id\u00e9e ici n\u2019est pas de comparer le temps d\u2019ex\u00e9cution<\/span> de la requ\u00eate sur MariaDB par rapport \u00e0 \u00a0Percona server (les serveurs et les param\u00e8tres sont diff\u00e9rents), mais bien de voir et de comprendre les (nouveaux) choix de l\u2019optimiseur de MariaDB 5.5.<\/p>\n

      Traditional behavior (IN to EXISTS strategy)<\/h2>\n

       <\/p>\n

      TEST 1<\/strong><\/p>\n

      Traditionnellement l\u2019optimiseur converti la sous-requ\u00eate IN en EXIST. Pour simuler ce comportement il faut d\u00e9sactiver la mat\u00e9rialisation<\/a> et la semi-jointure<\/a>.<\/p>\n

       <\/p>\n

      SET optimizer_switch=’materialization=off,semijoin=off’;<\/strong><\/p>\n

       <\/p>\n

      QEP<\/h3>\n
      *************************** 1. row ***************************<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/span><\/address>\n
      select_type: PRIMARY<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/span><\/strong><\/address>\n
      possible_keys: StageMainIDOtherID,…<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: StageMainIDOtherID<\/span><\/address>\n
      \u00a0\u00a0\u00a0 key_len: 1<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: const<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 153455020<\/span><\/strong><\/address>\n
      \u00a0\u00a0\u00a0 filtered: 100.00<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Using temporary<\/strong><\/span><\/address>\n
      *************************** 2. row ***************************<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 2<\/span><\/address>\n
      select_type: DEPENDENT SUBQUERY<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: index_subquery<\/span><\/strong><\/address>\n
      possible_keys: MainIDOtherIDStatus,…<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: MainIDOtherIDStatus<\/span><\/address>\n
      \u00a0\u00a0\u00a0 key_len: 8<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: func,const<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 1<\/span><\/strong><\/address>\n
      \u00a0\u00a0\u00a0 filtered: 100.00<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where<\/span><\/address>\n

      Intuitivement on peut penser que la requ\u00eate interne est ex\u00e9cut\u00e9e en premier, puis qu\u2019elle g\u00e9n\u00e8re une liste d\u2019ID (MainID) qui est ensuite pass\u00e9e dans la clause IN, du genre :<\/p>\n

      \u2026 MainID IN (123, 456, 789).<\/address>\n

       <\/p>\n

      Eh ben non !<\/p>\n

      Comme convenu, le QEP est semblable \u00e0 celui utilis\u00e9 avec MySQL \/ percona server 5.5.<\/p>\n

      En fait, l\u2019optimiseur transforme le clause IN en EXISTS<\/a>. La table externe est lue la premi\u00e8re sur environ 50% de ses lignes (rows: 153455020<\/strong><\/span>), ce qui dans notre cas fait beaucoup et est jointe avec la table interne.<\/p>\n

      La sous-requ\u00eate d\u00e9pend alors des donn\u00e9es de la requ\u00eate externe (select_type: DEPENDENT SUBQUERY<\/span><\/strong>).<\/address>\n

      La requ\u00eate r\u00e9\u00e9crite par l\u2019optimiseur ressemble alors \u00e0 peu pr\u00e8s \u00e0 :<\/p>\n

       <\/p>\n

      SELECT DISTINCT OtherID<\/address>\n
      FROM MyTable AS MyExtTable<\/span><\/address>\n
      WHERE Status = 1<\/address>\n
      \u00a0AND Stage = 4<\/address>\n
      \u00a0AND Invisible = 0<\/address>\n
      \u00a0AND EXISTS<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0(<\/address>\n
      \u00a0\u00a0\u00a0 \u00a0SELECT 1<\/address>\n
      \u00a0\u00a0\u00a0 \u00a0FROM MyTable AS MyInnTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0WHERE OtherID = 2779262<\/address>\n
      \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0AND Stage = 0<\/address>\n
      \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0AND Invisible = 0<\/address>\n
      \u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0 \u00a0AND MyExtTable.MainID = MyInnTable.MainID<\/span><\/address>\n
      \u00a0\u00a0\u00a0 \u00a0)<\/address>\n
      \u00a0<\/address>\n
      \u00a0<\/address>\n

      Optimizer rewriting<\/h3>\n
      select distinct OtherID AS OtherID from MyTable<\/span><\/address>\n
      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<\/span><\/address>\n
      where (((OtherID = 2779262) and (Stage = 0) and (Invisible = 0)) and (<cache>(MainID) = MainID<\/strong>) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)))))))<\/span><\/address>\n

       <\/p>\n

      Confirmation que l\u2019on a bien une transformation IN to EXISTS<\/a>.<\/p>\n

      Status<\/h3>\n
      +—————————+———–+<\/span><\/address>\n
      | Variable_name \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value \u00a0\u00a0\u00a0 |<\/span><\/address>\n
      +—————————+———–+<\/span><\/address>\n
      | Created_tmp_disk_tables \u00a0\u00a0\u00a0\u00a0| 0 \u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Created_tmp_tables \u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| 2 \u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_mrr_key_refills \u00a0\u00a0\u00a0\u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_mrr_rowid_refills \u00a0\u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_prepare \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_first \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_key \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 44655686 \u00a0|<\/span><\/strong><\/address>\n
      | Handler_read_last \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 153049748 |<\/span><\/strong><\/address>\n
      | Handler_read_prev \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd_deleted \u00a0\u00a0\u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd_next \u00a0\u00a0\u00a0 | 8 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_tmp_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 206 \u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      +—————————+———–+<\/span><\/address>\n

       <\/p>\n

      Beaucoup de lecture dans l\u2019index (Handler_read_key | 44655686<\/span><\/strong>). Le probl\u00e8me ici est le nombre \u00e9lev\u00e9 de lectures transverse de l\u2019index (Handler_read_next | 153049748<\/span><\/strong>) qui est tr\u00e8s co\u00fbteux, surtout si les IO sont sur disque, et c\u2019est encore pire s\u2019ils sont al\u00e9atoires ie random IO disk.<\/p>\n

       <\/p>\n

      Contrairement \u00e0 MySQL 5.5 o\u00f9 il n\u2019y a qu\u2019une seule table temporaire cr\u00e9\u00e9e, ici il y en a 2 (Created_tmp_table | 2<\/span><\/strong>), l\u2019une pour le d\u00e9doublonnage li\u00e9 \u00e0 la clause DISTINCT et l\u2019autre… ben je ne sais pas :\/<\/p>\n

      En profilant la requ\u00eate avec la commande SHOW PROFILE<\/a>, il y est bien not\u00e9 que 2 tables temporaires sont supprim\u00e9es, mais dans le m\u00eame temps, une seule est cr\u00e9\u00e9e…<\/p>\n

      Si quelqu\u2019un \u00e0 une explication, je suis preneur.<\/p>\n

      Bench<\/h3>\n

      daz@sql:~\/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i5 -q\u00a0\u00bbSELECT DISTINCT OtherID \u00a0FROM MyTable \u00a0WHERE Status = 1 \u00a0AND Stage = 4 \u00a0AND Invisible = 0 \u00a0AND MainID IN \u00a0( SELECT MainID \u00a0FROM MyTable \u00a0WHERE OtherID = 2779262 \u00a0AND Stage = 0 \u00a0AND Invisible = 0 \u00a0);\u00a0\u00bb<\/span><\/p>\n

      Benchmark<\/span><\/p>\n

      \u00a0\u00a0Minimum number of seconds to run all queries: 550.444 seconds<\/strong><\/span><\/p>\n

       <\/p>\n

      Les meilleures performances de cette requ\u00eate, dans ces conditions, sont de l\u2019ordre de la 10aine de minutes, ce qui est rarement acceptable en OLTP<\/a>.<\/p>\n

      Pour simuler l\u2019\u201dintelligence\u201d de l\u2019optimiseur de MySQL \/ Percona Server 5.5, il a fallu d\u00e9sactiver 2 directives d\u2019optimisation (la mat\u00e9rialisation<\/a> et la semi-jointure<\/a>). Voyons ce qu\u2019il en est en ne d\u00e9sactivant qu\u2019une seule d\u2019entre elles, \u00e0 tour de r\u00f4le.<\/p>\n

      MATERIALIZATION strategy (disable semi-join)<\/h2>\n

       <\/p>\n

      TEST 2<\/strong><\/p>\n

      Pour d\u00e9sactiver la semi-jointure, il suffit de mettre la directive suivante \u00e0 OFF:<\/p>\n

      SET optimizer_switch=’semijoin=off’;<\/strong><\/p>\n

       <\/p>\n

      La strat\u00e9gie d\u2019optimisation utilis\u00e9e par l\u2019optimiseur est alors la mat\u00e9rialisation<\/a>. En 2 mots, le r\u00e9sultat de la sous-requ\u00eate est stock\u00e9 dans une table temporaire qui contient un index unique. Cet index sert \u00e0 d\u00e9doublonner les valeurs ins\u00e9r\u00e9e et \u00e0 r\u00e9cup\u00e9rer les infos de mani\u00e8re performante (comme un index classique), qui vont alimenter la requ\u00eate externe.<\/p>\n

      QEP<\/h3>\n
      *************************** 1. row ***************************<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/span><\/address>\n
      select_type: PRIMARY<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/span><\/strong><\/address>\n
      possible_keys: StageMainIDOtherID, …<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: StageMainIDOtherID<\/span><\/address>\n
      \u00a0\u00a0\u00a0 key_len: 1<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: const<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 153455020<\/span><\/strong><\/address>\n
      \u00a0\u00a0\u00a0 filtered: 100.00<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Using temporary<\/span><\/address>\n
      *************************** 2. row ***************************<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 2<\/span><\/address>\n
      select_type: MATERIALIZED<\/span><\/strong><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/span><\/strong><\/address>\n
      possible_keys: MainIDOtherIDStatus,OtherIDStageStatusInvisibleMainID,StageMainIDOtherID, …<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: OtherIDStageStatusInvisibleMainID<\/span><\/address>\n
      \u00a0\u00a0\u00a0 key_len: 5<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: const,const<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 235<\/span><\/strong><\/address>\n
      \u00a0\u00a0\u00a0 filtered: 100.00<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Using index<\/strong><\/span><\/address>\n

       <\/p>\n

      L\u00e0 encore, selon le QEP la table externe est lue la premi\u00e8re sur environ 50% de ses lignes \u00a0(rows: 153455020<\/span><\/strong>). Ce qui change l\u00e0, c\u2019est que la strat\u00e9gie d\u2019optimisation retenue est la mat\u00e9rialisation<\/a>.<\/address>\n

      Autres diff\u00e9rences avec le QEP originel, un covering index (Extra: Using where; Using index<\/span><\/strong>) sur les 235 (une estimation) enregistrements (rows: 235<\/span><\/strong>) de la table mat\u00e9rialis\u00e9e.<\/p>\n

      Optimizer rewriting<\/h3>\n
      select distinct OtherID AS OtherID from MyTable<\/span><\/address>\n
      where ((Status = 1) and (Stage = 4) and (Invisible = 0) and <expr_cache><MainID>(<in_optimizer>(MainID,MainID in<\/span><\/address>\n
      ( <materialize> (select MainID from MyTable<\/span><\/strong><\/address>\n
      where ((OtherID = 2779262) and (Stage = 0) and (Invisible = 0)) ), <primary_index_lookup>(MainID in <temporary table> on distinct_key where ((MainID = <subquery2>.MainID))))))<\/strong>)<\/span><\/address>\n

       <\/p>\n

      Confirmation que l\u2019on \u00e0 bien la mat\u00e9rialisation de la sous-requ\u00eate sous la forme d\u2019une table temporaire index\u00e9e(<primary_index_lookup>MainID in <temporary table> on distinct_key<\/strong><\/span>).<\/p>\n

      A noter que l\u2019index \u00e0 \u00e9galement servi au d\u00e9doublonnage.<\/p>\n

      Status<\/h3>\n
      +—————————+———–+<\/span><\/address>\n
      | Variable_name \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value \u00a0\u00a0\u00a0 |<\/span><\/address>\n
      +—————————+———–+<\/span><\/address>\n
      | Created_tmp_disk_tables \u00a0\u00a0\u00a0\u00a0| 0 \u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Created_tmp_tables \u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| 3 \u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_mrr_key_refills \u00a0\u00a0\u00a0\u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_mrr_rowid_refills \u00a0\u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_prepare \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_first \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_key \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 44655687 \u00a0|<\/span><\/strong><\/address>\n
      | Handler_read_last \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 153049981 |<\/span><\/strong><\/address>\n
      | Handler_read_prev \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd_deleted \u00a0\u00a0\u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd_next \u00a0\u00a0\u00a0 | 8 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_tmp_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 442 \u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      +—————————+———–+<\/span><\/address>\n

      Niveau table temporaires, on a 1 table temporaire pour le DISTINCT + 1 table pour la mat\u00e9rialisation + 1 table temporaire myst\u00e8re (Created_tmp_tables | 3<\/span>).<\/p>\n

      Avec une table temporaire suppl\u00e9mentaire, il y a alors plus d\u2019\u00e9criture (Handler_tmp_write | 442<\/span><\/strong>).<\/p>\n

      442 (Handler_tmp_write) – 235 (MATERIALIZED rows) = 207 (\u00e0 rapporter au 206 du Handler_tmp_write du QEP originel)<\/em><\/p>\n

       <\/p>\n

      A l\u2019aide de toutes ces informations, lequel des 2 modes est le plus performant ?<\/p>\n

      D\u2019un point de vu status, il y a 1 table temporaire de plus pour T2, mais les tables \u00e9tant cr\u00e9\u00e9es enti\u00e8rement en m\u00e9moire, le co\u00fbt reste donc faible. De plus le nombre de Handler_read_next est sensiblement \u00e9gale. M\u00eame s\u2019il n\u2019est pas r\u00e9parti de la m\u00eame mani\u00e8re.<\/p>\n

      Bref, niveau status, exaequo entre T1 & T2<\/p>\n

      Avec explain, le nombre de rows de T2 est 235 fois plus important que pour T1. Cependant, T2 fournit une mat\u00e9rialisation et index covering index alors qu\u2019avec T1 on est dans de la sous-requ\u00eate corr\u00e9l\u00e9e !<\/p>\n

      Bref niveau explain, c\u2019est pas clair non plus \ud83d\ude00 mais la mat\u00e9rialisation se faisant en m\u00e9moire, elle devrait \u00eatre moins co\u00fbteuse.<\/p>\n

      Bench<\/h3>\n
      daz@sql:~\/mariadb5.5.30$ mysqlslap –create-schema=test -c1 -i5 -q\u00a0\u00bbSELECT DISTINCT OtherID \u00a0FROM MyTable \u00a0WHERE Status = 1 \u00a0AND Stage = 4 \u00a0AND Invisible = 0 \u00a0AND MainID IN \u00a0( SELECT MainID \u00a0FROM MyTable \u00a0WHERE OtherID = 2779262 \u00a0AND Stage = 0 \u00a0AND Invisible = 0 \u00a0);\u00a0\u00bb<\/span><\/address>\n
      Benchmark<\/span><\/address>\n
      \u00a0\u00a0Minimum number of seconds to run all queries: 430.712 seconds<\/strong><\/span><\/address>\n

       <\/p>\n

      T2 s\u2019ex\u00e9cute avec 2 minutes de moins<\/strong> que T1.<\/p>\n

      SEMIJOIN strategy (disable materialization)<\/h2>\n

       <\/p>\n

      TEST 3<\/strong><\/p>\n

      Pour d\u00e9sactiver la mat\u00e9rialisation, il suffit de mettre la directive suivante \u00e0 OFF:<\/p>\n

      SET optimizer_switch=’materialization=off’;<\/strong><\/p>\n

       <\/p>\n

      La strat\u00e9gie d\u2019optimisation utilis\u00e9e par l\u2019optimiseur est alors la semi-jointure<\/a> (une sorte de jointure interne o\u00f9 seule une occurrence des valeurs jointes est r\u00e9cup\u00e9r\u00e9e).<\/p>\n

      QEP<\/h3>\n
      *************************** 1. row ***************************<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/span><\/address>\n
      select_type: PRIMARY<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: index<\/span><\/strong><\/address>\n
      possible_keys: MainIDOtherIDStatus,OtherIDStageStatusInvisibleMainID,StageMainIDOtherID<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: OtherIDStageStatusInvisibleMainID<\/span><\/address>\n
      \u00a0\u00a0\u00a0 key_len: 15<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: NULL<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 306910041<\/span><\/strong><\/address>\n
      \u00a0\u00a0\u00a0 filtered: 0.00<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using where; Using index; Using temporary; LooseScan<\/strong><\/span><\/address>\n
      *************************** 2. row ***************************<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 id: 1<\/span><\/address>\n
      select_type: PRIMARY<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 table: MyTable<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 type: ref<\/span><\/strong><\/address>\n
      possible_keys: MainIDOtherIDStatus,StageMainIDOtherID<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: MainIDOtherIDStatus<\/span><\/address>\n
      \u00a0\u00a0\u00a0 key_len: 4<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ref: MyTable.MainID<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 rows: 1<\/span><\/address>\n
      \u00a0\u00a0\u00a0 filtered: 100.00<\/span><\/address>\n
      \u00a0\u00a0\u00a0\u00a0\u00a0 Extra: Using index condition<\/strong>; Using where<\/span><\/address>\n

      Le QEP obtenu est un full index scan aka FIS (type: index<\/span><\/strong>) avec covering index (Extra: Using index;<\/span><\/strong>). En g\u00e9n\u00e9ral un FIS est plut\u00f4t une mauvaise nouvelle pour les perfs, sauf dans le cas d\u2019un index covering.<\/p>\n

      L\u2019algorithme utilis\u00e9 par l\u2019optimiseur pour la semi-jointure est le \u00a0LooseScan<\/a> (Extra: LooseScan<\/span><\/strong>).<\/p>\n

      A noter l\u2019utilisation de l\u2019optimisation index condition pushdown ou ICP<\/a> (Extra: Using index condition<\/span><\/strong>). On en y revient plus tard.<\/p>\n

      Optimizer rewriting<\/h3>\n
      select distinct OtherID from MyTable semi join (MyTable)<\/strong><\/span><\/address>\n
      where ((Status = 1) and (Stage = 4) and (Invisible = 0) and (OtherID = 2779262) and (Stage = 0) and (Invisible = 0)<\/span><\/address>\n
      and (MainID = MainID)<\/strong>)<\/span><\/address>\n

       <\/p>\n

      Confirmation que l\u2019on a bien une transformation en semi-jointure<\/a>.<\/p>\n

      Status<\/h3>\n
      +—————————+———–+<\/span><\/address>\n
      | Variable_name \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Value \u00a0\u00a0\u00a0 |<\/span><\/address>\n
      +—————————+———–+<\/span><\/address>\n
      | Created_tmp_disk_tables | 0 \u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Created_tmp_files \u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Created_tmp_tables \u00a0\u00a0\u00a0\u00a0 | 1 \u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_mrr_key_refills \u00a0\u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_mrr_rowid_refills | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_prepare \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_first \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 1 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_read_key \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 236 \u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_read_last \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_next \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 306909498 |<\/span><\/strong><\/address>\n
      | Handler_read_prev \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd_deleted \u00a0| 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      | Handler_read_rnd_next \u00a0\u00a0\u00a0 | 8 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_tmp_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 7 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/strong><\/address>\n
      | Handler_write \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/address>\n
      +—————————+———–+<\/span><\/address>\n

       <\/p>\n

      Les informations d\u2019\u00e9tat sont assez claires (si si vous allez voir):<\/p>\n