ALICE AU CORE DE MySQL-5.1 :PART2: L'Optimiseur | The Optimizer

Dans la documentation officielle de MySQL on trouve dans le chapitre "7.2.4. Comment MySQL optimise les clauses WHERE" que "Cette section est incomplète. MySQL fait de très nombreuses optimisations, et nous n'avons pas eu le temps de toutes les documenter.". Alors on va la reprendre les posts qui viennent, dans l'objectif de mettre un point sur les inconvénients d'une requête mal écrite ou très complexes pour MySQL. Par ce que, même si le temps de l'évaluation de la condition "WHERE" est relativement négligeable, il semble qu'il est très utile de le supprimer!

L'optimisation des requêtes SELECT se fait dans le fichier sql_select.cc et certaines des optimisations effectuées par MYSQL sont :

• Si une dans une requête, un champ "field = constante", alors elle change toute les références de "field" à "constante" : "a=b AND b=c AND c=1" devient "b=1 AND a=1 AND c=1"
• Suppression des conditions constantes ou inutiles : ( 1 = 1, b = b, ..).
• Suppression des conditions impossibles : "item IS NULL" alors que "item" ne s'annule jamais.
• Retourne "Impossible WHERE" ou MySQL détecte rapidement les commandes SELECT impossibles (WHERE 1 = 2) et ne retourne aucune ligne.
• Suppression des parentheses inutiles: ((a AND b) AND c OR (((a AND b) AND (c AND d)))).
• Retourne le COUNT(*) sans "WHERE" d'une table simple directement depuis les informations de la table.
• etc..
  

Mais le plus important à savoir, reste comment MySQL optimise ce genre de requêtes afin de pouvoir en déduire les meilleures façons d'écrire nos requêtes et plus généralement la meilleure façon de concevoir ses bases de données.

Une balade dans le fichier sql_select.cc fera l'affaire :

Une définition étroite de l'OPTIMISEUR : est l'ensemble de routines qui décident quel methode d'exécution le SGBD devrait prendre pour les requetes. MySQL change ces routines fréquemment, ainsi vous devriez comparer ce qui est dit ici avec ce qu'est dans le code source. Pour faciliter les choses, cette description inclut des notes se rapportant à la routine appropriée, par exemple "/sql/select_cc, optimize_cond()". Quand une reqeute est changée en une autre reqeute qui fournit le même résultat, c'est la TRANSFORMATION. La plupart des transformations sont moins évidentes, mais quelques unes peuvent rendre l'exécution plus rapide.

Par exemple, MySQL peut changer :

 SELECT ... WHERE 5 = a

en

 SELECT ...WHERE a = 5

Voici le diagramme montrant la structure de code du handle_select() dans /sql/sql_select.cc, le code du serveur qui manipule les requetes :

   handle_select()
  mysql_select()
    JOIN::prepare()
      setup_fields()
 
    //{{{ L'optimisation commence ici ... 
    JOIN::optimize()       
      optimize_cond()
      opt_sum_query()
      make_join_statistics()
        get_quick_record_count()
        choose_plan()
          /* Trouve la meilleur fcon d'acces aux tables spécifié par l'utilisateur  */
          optimize_straight_join()
            best_access_path()
          /* Trouvez les plans les plus optimaux parmi le sous-ensemble de tous les 
               combinaisons possibles des requetes                                    */
          greedy_search()
            best_extension_by_limited_search()
              best_access_path()
          /* Exécutez une recherche approfondie du plan optimal */
          find_best()
      make_join_select()
    // }}} L'optimisation s'arrete ici

    JOIN::exec()

• Bien que le mot "JOIN" apparaisse, ses routines d'optimiseur sont les memes pour pour toutes les requetes.
• L'identation dans l'expositions du diagramme la methodes d'appel. Ainsi vous pouvez voir que handle_select() appelle mysql_select() qui appelle JOIN::prepare() qui appelle le setup_fields(), et ainsi de suite.
• La première partie de mysql_select() est JOIN::prepare() pour l'analyse du contexte, l'identification des metadata, et la transformations de quelques sous requetes.
• L'optimiseur est JOIN::optimize() et toutes les routines subalternes.
• Quand l'optimiseur finit son travail, JOIN::exec() execute ce que JOIN::optimize() vient de décidé .
• Les routines optimize_cond() et opt_sum_query() font les transformations.
  
• La routine make_join_statistics() remonte toute les informations qu'elle trouve au sujet des index qui pourraient être utiles pour accéder aux tables de la requete.