Godefroy de Compreignac

Blog de Skreo :: Webdev && High-Tech 

• Blog
• Twitter
• Facebook
• Contact

• Web development

  [MySQL] Optimisation d'une recherche avec multiples jointures

  par Skreo, le 10 Décembre 2009 à 16:42

  J'ai été confronté à un problème de ralentissements sur EklaBlog dûs à une requête SELECT utilisant de multiples jointures. Nous allons voir dans cet article comment optimiser cela.

  La requête en elle-même n'est pas très complexe et utilise uniquement des clés (primary et indexes), mais elle est très lente pour plusieurs raisons :

  • Jointure sur 5 tables
  • Recherche sur 4 champs chacun dans une table différente
  • Tri par ORDER BY

  Elle ressemble en gros à ceci :

  SELECT a.id, a.blog, a.category_id, a.title, a.content, a.author, a.creadt, c.url
      FROM mod_article a 
      INNER JOIN categories b ON b.id=a.category_id 
      INNER JOIN blogs c ON c.id=b.blog_id 
      INNER JOIN articles_tags at ON at.article_id=a.id
      INNER JOIN tags t ON t.id=at.tag_id
      WHERE t.name="skreo" AND a.online="1" AND b.protect="" AND c.protect="" 
      ORDER BY a.creadt DESC
      LIMIT 10

  [code=mysql]SELECT a.id, a.blog, a.category_id, a.title, a.content, a.author, a.creadt, c.url
      FROM mod_article a 
      INNER JOIN categories b ON b.id=a.category_id 
      INNER JOIN blogs c ON c.id=b.blog_id 
      INNER JOIN articles_tags at ON at.article_id=a.id
      INNER JOIN tags t ON t.id=at.tag_id
      WHERE t.name="skreo" AND a.online="1" AND b.protect="" AND c.protect="" 
      ORDER BY a.creadt DESC
      LIMIT 10[/code]

  Les tables concernées sont relativement grosses :

  • 130 000 entrées dans tags
  • 1 400 000 entrées dans articles_tags
  • 400 000 entrées dans articles
  • 70 000 entrées dans categories
  • 130 000 entrées dans tags
  • 19 000 entrées dans blogs

  D'où un temps d'exécution de la requête pouvant atteindre 30 secondes...

  Lors d'un test en local, que je répèterai dans la suite de l'article, j'obtiens une exécution en 0.24s en moyenne quand le pc ne fait rien d'autre.

   

  Solution possible

  Une solution possible que m'a indiqué mon ami Guilhem est de stocker un résultat intermédiaire de la requête dans une table de type MEMORY (en RAM) :

  CREATE TABLE tags_lookup (
      article_id int(10) unsigned NOT NULL,
      tag_id int(10) unsigned NOT NULL,
      creadt int(10) unsigned NOT NULL,
      blog_id int(10) unsigned NOT NULL,
      KEY tag_id (tag_id),
      KEY creadt (creadt),
      KEY blog_id (blog_id)
  ) ENGINE=MEMORY

  [code=mysql]CREATE TABLE tags_lookup (
      article_id int(10) unsigned NOT NULL,
      tag_id int(10) unsigned NOT NULL,
      creadt int(10) unsigned NOT NULL,
      blog_id int(10) unsigned NOT NULL,
      KEY tag_id (tag_id),
      KEY creadt (creadt),
      KEY blog_id (blog_id)
  ) ENGINE=MEMORY[/code]

  Il faut ensuite remplir la remplir :

  # On vide la table
  TRUNCATE TABLE tags_lookup;
  # Puis on la remplit entièrement
  INSERT INTO tags_lookup
      SELECT a.id AS article_id, at.tag_id, a.creadt, c.id AS blog_id
      FROM articles a, categories b, blogs c, articles_tags at
      WHERE a.online="1" AND b.protect="" AND c.protect=""
      AND b.id=a.category_id AND c.id=a.blog_id AND at.article_id=a.id;

  [code=mysql]# On vide la table
  TRUNCATE TABLE tags_lookup;
  # Puis on la remplit entièrement
  INSERT INTO tags_lookup
      SELECT a.id AS article_id, at.tag_id, a.creadt, c.id AS blog_id
      FROM articles a, categories b, blogs c, articles_tags at
      WHERE a.online="1" AND b.protect="" AND c.protect=""
      AND b.id=a.category_id AND c.id=a.blog_id AND at.article_id=a.id;[/code]

  Il faudra exécuter les requêtes ci-dessus régulièrement pour mettre à jour le contenu de la table, à l'aide d'un bash par exemple.

  Pour obtenir les informations qu'on voulait au début, on peut alors exécuter cette requête :

  SELECT a.id, a.blog, a.category_id, a.title, a.content, a.author, a.creadt, c.url
      FROM tags t
      INNER JOIN tags_lookup s ON s.tag_id=t.id
      INNER JOIN articles a ON a.id=s.article_id
      INNER JOIN categories b ON b.id=a.category_id 
      INNER JOIN blogs c ON c.id=a.blog _id
      WHERE t.name="skreo"
      ORDER BY s.creadt DESC
      LIMIT 10

  [code=mysql]SELECT a.id, a.blog, a.category_id, a.title, a.content, a.author, a.creadt, c.url
      FROM tags t
      INNER JOIN tags_lookup s ON s.tag_id=t.id
      INNER JOIN articles a ON a.id=s.article_id
      INNER JOIN categories b ON b.id=a.category_id 
      INNER JOIN blogs c ON c.id=a.blog _id
      WHERE t.name="skreo"
      ORDER BY s.creadt DESC
      LIMIT 10[/code]

  Lors de mon test, j'obtiens cette fois-ci 0.15s d'exécution en moyenne, ce qui est déjà mieux.

  Mais le gros avantage d'une telle structure, c'est qu'on va pouvoir faire une recherche beaucoup plus facile à comprendre pour le serveur MySQL, grâce à des requêtes imbriquées :

  SELECT a.id, a.blog, a.category_id, a.title, a.content, a.author, a.creadt, c.url
      FROM (
          SELECT article_id
          FROM tags_lookup
          WHERE tag_id=(
              SELECT id
              FROM tags
              WHERE name="skreo"
              LIMIT 1
          )
          ORDER BY creadt DESC
          LIMIT 10
      ) s
      INNER JOIN articles a ON a.id=s.article_id
      INNER JOIN categories b ON b.id=a.category_id
      INNER JOIN blogs c ON c.id=a.blog_id
      ORDER BY a.creadt DESC
      LIMIT 10;

  [code=mysql]SELECT a.id, a.blog, a.category_id, a.title, a.content, a.author, a.creadt, c.url
      FROM (
          SELECT article_id
          FROM tags_lookup
          WHERE tag_id=(
              SELECT id
              FROM tags
              WHERE name="skreo"
              LIMIT 1
          )
          ORDER BY creadt DESC
          LIMIT 10
      ) s
      INNER JOIN articles a ON a.id=s.article_id
      INNER JOIN categories b ON b.id=a.category_id
      INNER JOIN blogs c ON c.id=a.blog_id
      ORDER BY a.creadt DESC
      LIMIT 10;[/code]

  Toujours avec la même base de données, j'obtiens cette fois-ci une exécution presque instantanée : entre 0 et 0.02 secondes !

   

  Conclusion

  En mettant en place une table en mémoire vive rassemblant des données correspondant à une partie du traitement d'une grosse requête, on peut donc éviter des jointures très lourdes.

  Cette méthode a bien évidemment comme inconvénient d'avoir des données pas toujours ultra fraiches en mémoire, mais ça peut être un bon compromis quand on voit la rapidité des requêtes qui en résultent.

  Vous avez d'autres solutions intéressantes ? une meilleure idée ?

  
  Partager cet article :
  Tags : mysql, jointures
  Suivre le flux RSS des commentaires de cet article
  Revenir à la liste des articles

  Haut de page

• Commentaires

  1

  versgui

  10 Décembre 2009 à 23:46

  Interessante, cette méthode... Tes tests ont été fait en InnoDB ou MyISAM ?

  2

  Skreo

  11 Décembre 2009 à 15:03

  

  MyISAM, j'ai pas encore essayé ça en InnoDB, mais je pense que ça doit aussi être intéressant au niveau des perfs.

  3

  versgui

  12 Décembre 2009 à 20:31

  Je pense que passer tes tables en InnoDB avec un système de clés primaires digne de ce nom pourrait avoir des effets notables, notamment en lecture (reste à voir en écriture).

  4

  Jon

  12 Décembre 2009 à 20:39

  

  Effectivement, une jointure sur 5 tables avec MyISAM, cela pose quelques problèmes !!
  
  Enfin bon, ta solution est relativement élégante, il faudrait voir si en passant à InnoDB : 
  

  1. Tu as toujours ton problèmes de perfs
  2. Si cela va encore plus vite sur ta nouvelle requête (tu as encore 3 jointures à la fin).

  Sinon tu as choisi quoi pour automatiser (maintenant que c'est en prod ;) ) ça ? Cron ou autre chose ?

  5

  Skreo

  13 Décembre 2009 à 01:52

  

  Yep, maintenant j'ai passé la table des articles en InnoDB (essentiellement pour avoir du row lock et non du table lock lors des écritures), je préfère pas changer tout trop rapidement sur un truc en prod.
  
  Et pour automatiser, j'ai juste fait un petit bash appelé toutes les nuit par cron, qui contient les deux requêtes (TRUNCATE et INSERT) appelées par la commande mysql.
  L'étape suivante serait de faire ça dans une table temporaire pour éviter d'avoir une coupure le temps de la mise à jour.

  
  
  Ajouter un commentaire
  
  

  Nom / Pseudo :
  
  E-mail (facultatif) :
  
  Site Web (facultatif) :
  
  Commentaire :
  
  

  
  Recopiez le code inscrit sur l'image :
  
  
  Ce code est un code de sécurité pour empêcher le spam en vérifiant que la personne qui poste est bien un humain et non un robot.
  
  

  Haut de page