Aller sur la version française Go to English versionAccueil > Communautés > Le blog > Billets techniques > Des problèmes de performances avec les requètes de type IN ?

RSSFeed Rubriques

Archives

Des problèmes de performances avec les requètes de type IN ?

Par joce le 25 Juillet à 00:42:18 3 commentaire(s)

Martin Friebe, un contributeur très actif de la communauté MySQL s'est rendu compte d'une chose intéressante, notamment pour ceux utilisant de façon intensive des requètes du style IN () contenant beaucoup de valeur :
 
le code de l'optimizer de MySQL responsable du plan d'execution de type RANGE (utilisé notamment lors des requètes IN) n'est pas optimal, ce qui amène à des performances moins élevées que ce que l'on pourrait escompter.
Le problème devient particulièrement embétant dans MySQL 5.0 lorsque des jointures de ce style sont utilisées :
 
SELECT a FROM t1,t2 WHERE t1.a=t2.b AND t2.b IN (...)
 
En effet, MySQL 5.0 et > dispose d'une optimisation appelée "propagation d'égalité", et la requète devient donc après optimisation :
 
SELECT a FROM t1,t2 WHERE t1.a=t2.b AND t2.b IN (...) AND t1.a IN (...)
 
Et là, le problème de l'optimizer se fait sentir : en effet, lorsqu'il se trouve en presence de deux IN, il calcule de façon incorrecte que le nombre approximatif de ligne qui va être retourné par la requète est égal au nombre de valeur dans le premier IN * le nombre de valeur dans le deuxième IN, et va faire beaucoup plus de tests que nécessaire... ce qui peut faire particulièrement mal :D
Et il semble que l'optimizer ait d'autres problèmes s'ajoutant à cela, necessitant une investigation approfondie (voir le bug http://bugs.mysql.com/bug.php?id=20932 )
 
 
En bref, la phase de calcul des statistiques peut être jusqu'à 20x plus lente sur de grosse liste !
 
Il pourrait être donc judicieux, si vous vous trouvez dans ce cas, de créer une temporary table (en memoire) contenant votre liste et de faire une jointure sur cette liste, plutôt que d'utiliser un IN (...) avec énormement de valeurs.
 
Pour les personnes recherchant les performances à tout prix tout en gardant un IN (...) avec beaucoup de valeurs, ils peuvent jeter un oeil et essayer les patchs suivant :
 
http://bugs.mysql.com/bug.php?id=26453
http://bugs.mysql.com/bug.php?id=26232
 
 
en attendant que MySQL ne les intègre officiellement.
 
Un hack a été également posté, désactivant la propagation d'égalité dans 5.0 pour les listes contenant plus de 5 éléments :
 

Code :
  1. ===== item_cmpfunc.h 1.153 vs edited =====
  2. --- 1.153/sql/item_cmpfunc.h    2007-07-19 03:26:18 +04:00
  3. +++ edited/item_cmpfunc.h       2007-07-19 02:31:15 +04:00
  4. @@ -1144,6 +1144,8 @@
  5.   class Item_func_in :public Item_func_opt_neg
  6.   bool nulls_in_row();
  7.   bool is_bool_func() { return 1; }
  8.   CHARSET_INFO *compare_collation() { return cmp_collation.collation; }
  9. +  /* sergeyp: don't do eq substs inside IN if the IN-list has > 5 elements */
  10. +  bool subst_argument_checker(byte **arg) { return test(arg_count < 5); }
  11. };
  13. class cmp_item_row :public cmp_item
  14. ===== sql_select.cc 1.538 vs edited =====
  15. --- 1.538/sql/sql_select.cc     2007-07-19 03:26:18 +04:00
  16. +++ edited/sql_select.cc        2007-07-19 02:29:39 +04:00
  17. @@ -7375,11 +7375,15 @@ 
  18.   static COND *build_equal_items_for_cond(
  19.       an argument of a comparison predicate.
  20.     */
  21.     byte *is_subst_valid= (byte *) 1;
  22. -    cond= cond->compile(&Item::subst_argument_checker,
  23. +    Item *new_cond;
  24. +    if ((new_cond= cond->compile(&Item::subst_argument_checker,
  25.                         &is_subst_valid,
  26.                         &Item::equal_fields_propagator,
  27. -                        (byte *) inherited);
  28. -    cond->update_used_tables();
  29. +                        (byte *) inherited)) && (new_cond != cond))
  30. +    {
  31. +      cond= new_cond;
  32. +      cond->update_used_tables();
  33. +    }
  34.   }
  35.   return cond;
  36. }




Commentaires (3)

Fixazo

le 26 Juillet à 00:28:28


Est-ce qu'on retrouve le même problème avec la clause BETWEEN?

Joce

le 26 Juillet à 01:46:11


Meme si un BETWEEN utilise aussi le type RANGE au niveau de l'optimizer, je ne pense pas qu'il soit affecté par le probleme :
 
Il n'y a que deux valeurs qui sont recherchés au niveau de l'index (la borne min et la borne max), alors que pour le IN avec plusieurs valeurs il y a une recherche pour chaque valeur. Donc au niveau de l'optimizer le plan d'execution est très vite décidé, et peu de temps est passé dans la phase "stastistic" dans le cas du BETWEEN.

Fixazo

le 27 Juillet à 14:05:34


Ok, tout ce que je voulais savoir est là :)
 
Merci !

Inconnu

 

Vous devez être idendifié pour déposer un commentaire.

Cliquez ici pour vous enregistrer