[MySQL] Optimisation d'une recherche avec multiples jointures

[MySQL] Optimisation d'une recherche avec multiples jointures
Par Godefroy dans Développement 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 ?
<br />

Tags : mysql, jointures
Commentaires
1

versgui

Jeudi 10 Décembre 2009 à 23:46

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

2

Godefroy

Vendredi 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

Samedi 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

Samedi 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 :

Tu as toujours ton problèmes de perfs

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

Godefroy

Dimanche 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.

6

froggylink

Lundi 5 Juillet 2010 à 18:38

ARRFFFF...... Au secours :-)
Ton ami t'a juste fait re-developper un system foireux de cache pour une requete :-)))
Utilise un vrai system de cache et ca te servira pour tout ton site : http://eaccelerator.net/

Perf x100 garantie...
En bonus : pré-compil de code!

++

7

Godefroy

Lundi 5 Juillet 2010 à 21:56

Je connais le cache, merci... J'utilise APC pour le cache opcode, et Memcached pour le cache de variables / requêtes.
Mon pb c'est que j'ai des dizaines de milliers de requêtes différentes sur cette table. Ça constitue donc une optimisation pas si mal.
Mais je vais bientôt remplacer ça par une recherche Fulltext avec Solr.

8

Metal3d

Mardi 27 Juillet 2010 à 17:49

J'ai une idée... juste comme ça... t'as mis des indexes sur les champs de recherche ?

9

Godefroy

Jeudi 29 Juillet 2010 à 23:55

Evidemment...
Mais bon maintenant, les recherches par tag dans EklaBlog passent par Solr, c'est ultra rapide

Suivre le flux RSS des commentaires de cet article

Ajouter un commentaire

Nom / Pseudo :

E-mail (facultatif) :

Site Web (facultatif) :

Commentaire :

Me prévenir par mail en cas de réponse

[MySQL] Optimisation d'une recherche avec multiples jointures

Solution possible

Conclusion

Commentaires