Bonjour à tous,
Situation
Je suis chef de projet pour un logiciel de comptabilité immobilière en ligne.
Nous avons ~200 tables, la plupart étant d'une manière ou d'une autre reliée entre-elles.
Lorsque je vais chercher une donnée (exemple : une facture), je dois rapidement aller dans une trentaine de tables (exemple : coordonnées du fournisseur, les articles, les prix, on a une table pour gérer toutes les références, une pour gérer les taux de TVA, etc.), avec des sous-requêtes sur trois niveaux, etc.
Je me suis donc retrouvé avec un dilemme :
1/ Soit faire une seule grande requête qui va tout chercher ;
2/ Soit faire une multitude de petites requêtes.
La solution 1 m'a semblé la plus évidente : les jointures. Nous travaillons avec Symfony et Doctrine.
Le problème
Le problème est que j'arrive très très très rapidement à des requêtes SELECT MySQL de plus de 1000 lignes (en copier/coller), parfois sur trois niveau (Subquery) ; difficile cependant ici d'expliquer pourquoi sans vous perdre. C'est vraiment notre logique métier qui veut ça : tout est découpé, avec des répartitions sur des centaines de clients, etc.
Je ne peux pas mettre en cache le résultat de la totalité de cette requête puisque plein d'informations peuvent-être modifiées d'ailleurs (exemple : coordonnées du fournisseur).
Exemple concret
J'ai dévéloppé (enfin demandé à un collègue) rapidement un mini-site (totalement inutile, c'est pour illuster) : http://burger.gaylord-poillon.com/commande/
C'est un site internet qui permet d'ajouter une commande de frites et nuggets. Chaque commande a un nombre de [ frites + poids ] et de [ nuggets + poids ]. Le but étant d'afficher le poids total de la commande.
Pour avoir ce résultat en une fois, nous avons du faire une requête, elle est très simple à comprendre (Et rebalancer tout ça avec SetResultMapping pour que les entités soient bien hydratés) :
SELECT
*,
(poids_frites + poids_nuggets) AS poids_total
FROM
(
SELECT
m.id,
m.id AS m_id,
c.id AS c_id,
c.nom,
c.prenom,
(
SELECT
COALESCE(
SUM(f.poids),
0
) AS poids_frites
FROM
frite f
WHERE
f.menu_id = m_id
) AS poids_frites,
(
SELECT
COALESCE(
SUM(n.poids),
0
) AS poids_nuggets
FROM
nugget n
WHERE
n.menu_id = m_id
) AS poids_nuggets
FROM
menu m
INNER JOIN client c ON m.client_id = c.id
) tmpBon la, ça va, c'est pour illuster ... mais dans mon cas, les requêtes font 20 fois ça.
Comment feriez-vous, vous, pour optimiser cela ?
J'ai envisagé de ne jamais faire de jointure, et d'aller chercher les données une à une, toujours une table à la fois, et mettre en cache donnée par donnée mais bon, c'est super sale et je me dis que j'aurais bien une surprise qui va me tomber dessus (ça arrive toutes les semaines ...)
Merci d'avance :)
PS : Notre projet fonctionne très bien, mais je commence à porter à la réflexion l'optimisation et la relecture.
4 réponses
Les jointures te permettrons d'optimiser par exemple la récupération de données entre plusieurs tables à la fois, tu devrais commencer à prendre cette habitude si tu le peux, on a pas inventé ça pour faire chier les gens, bien au contaire.
Sinon, il faut aussi s'assurer que t'a base de données soit bien structuré au niveau des tables, éviter de séparer trop de données si le nombre de liaison reste du 1-1 de chaque côté par exemple.
Après je ne suis pas un expert non plus en base de données, je ne connais que les bases mais j'ai déjà travailler sur une grosse base de données sous SQL Server (la galère) ou il y avais plus de 100.000 données dans certaines tables, et la seul solution que j'ai toruvé c'étais les jointures, mais après ton application et la mienne reste bien différente.
Pour t'a problématique, je te sugère une multitude de petite requête, bien optimisé avec des jointures si besoin et possible.
C'est franchement pas simple à répondre sans voir l'architecture complète.
Si je prends l'exemple que tu as cité, j'évite en ce qui me concerne les requetes imbriquées, elles n'apportent aucun avantage et ne permet pas d'optimiser individuellement les requetes. De même qu'une énorme requete plein d'imbrication qui remonte dans les slow query ne t'aidera pas forcement à la débugguer. A contrario, une requete seul qui remonte sera plus simple à optimiser, tu seras tout de suite que c'est cette requete qui a un soucis, et non l'une des requetes imbriqués.
Avant, j'avais l'habitude de faire des requetes assez lourdes, très chronophage à optimiser/debugguer (et difficile à mettre en cache pour MySql/MariaDb), maintenant, je préfère des requetes simples (et du coup nombreuses), mais individuellement très basique. Evidemment, l'ORM (maison dans mon cas) gère tout ça en arrière plan. J'ai du coup, en debug, beaucoup plus de requetes par page, mais bien moins groumande qu'avant, individuellement, les requetes prennnet rarement plus de 0.20ms, donc même en ayant 40 requetes, ça ne fait que 8ms.
Enfin difficile de faire des généralités, ça dépend tellement des cas, l'infrastructure etc etc.
Dans ton exemple, tu utilises que des clés primaires en imbrication, mais suppose que ce n'est pas le cas, et que tu as 50 requetes de ce genre imbriqués, cette requete se trouve mettre 400 ms, comment savoir, parmis les 50 requetes imbriqués, celle(s) qui lague(nt) ? Bon courage .. A l'inverse, elle serait séparé, tu vois tout de suite le temps requete par requete, tu optimises tout de suite celle qui est concerné par le lag.
Hello,
Même si j'admet ne pas être avancé merci quand même, ça me rassure un peu qu'il n'y ait pas de réponse franche en fait, pas de " règles officielles " ou de réelles " recommandations ", plutôt plusieurs possibilités.
Je vais envisager la location d'un autre serveur pour réaliser des tests grandeur nature sur de grosses données.
En tous cas merci quand même ^^
PS : @Fukotaku, dans ta réponse j'ai l'impression que tu sous-entends que je ne connais pas les jointures ; juste je les connais et dans tous les sens ^^
Il est vraiment difficile de faire des réponses franches avec MySql, d'autant qu'une requete pourra être idéalement optimisé selon un certains nombres de données, et laggué au fur et à mesure que le nombre de données augmentent. Non pas que la première requete soit pas optimisé (comme je le disais, elle était idéalement optimisé), mais le moteur de MySql étant ce qu'il est, il est parfois nécessaire d'optimiser de façon différente une même requete selon la quantité de donnée. Même si ça s'améliore de version en version, il existe encore de nombreux cas où l'analyseur MySql ne fait pas le bon choix parmis les index par exemple.
D'où le meilleur conseil qu'on peut te donner "génériquement", garde des requetes simples et accessibles, aussi bien toi que mysql sera mieux les analyser/optimiser/mettre en cache. (c'est d'ailleurs valable en dev en général, un code trop complexe (ou mal structuré, on va dire) n'aide pas à l'optimisation, la relecture, le refactoring).