J'ai une table MySQL qui représente une liste des commandes et une table enfant associée qui représente les envois associés à chaque commande (certaines commandes ont plus d'un envoi, mais la plupart n'en ont qu'une).
Chaque envoi a un certain nombre de coûts, par exemple:
Il existe de nombreux endroits dans l'application où j'ai besoin d'obtenir des informations consolidées pour la commande, telle que:
Tous ces champs dépendent des valeurs agrégées de la table d'expédition associée. Ces informations sont utilisées dans d'autres requêtes, rapports, écrans, etc., dont certains doivent renvoyer un résultat sur des dizaines de milliers d'enregistrements rapidement pour un utilisateur.
Comme je le vois, il y a quelques façons de base d'aller avec ceci:
Utilisez une sous-requête pour calculer ces éléments de la table d'expédition chaque fois qu'ils sont nécessaires. Cela complique un peu les choses pour tous les neutres qui nécessitent tout ou partie de ces informations. C'est aussi lent.
Créez une vue qui expose les sous-requêtes sous forme de champs simples. Cela conserve les rapports qui en ont besoin simple.
ajoutez ces champs dans la table de commande. Celles-ci me donneraient la performance que je cherche, au détriment de la double duplication de données et de le calculer lorsque je modifie les enregistrements d'expédition.
Une autre chose, j'utilise une couche d'entreprise qui expose les fonctions pour obtenir ces données (par exemple les getResters (filtre)) et je n'ai pas besoin des sous-totaux à chaque fois (ou seulement certaines d'entre elles une partie de l'heure), Donc, générer une sous-requête à chaque fois (même d'une vue) est probablement une mauvaise idée.
Y a-t-il des meilleures pratiques que tout le monde peut me signaler pour m'aider à décider de ce que le meilleur design pour cela est?
Incidemment, j'ai fini par faire # 3 principalement pour des raisons de simplicité de performance et de requête.
Vous avez beaucoup de bonnes commentaires assez rapidement, merci à tous. Pour donner un peu plus d'arrière-plan, l'un des endroits que les informations sont affichées figure sur la console d'administration où j'ai une liste de commandes potentiellement très longue et doit montrer au totalCost, TotalPaid et TotalProfit pour chacun.
5 Réponses :
J'irais probablement cela en cache les sous-totaux de la base de données pour la performance de la requête la plus rapide si la plupart du temps que vous faites des lectures au lieu d'écrit. Créez un déclencheur de mise à jour pour recalculer le sous-total lorsque la ligne change.
Je n'utiliserais une vue que pour les calculer sur Sélectionner si le nombre de lignes était généralement assez petit et accédait un peu peu fréquent. La performance sera beaucoup mieux si vous les mettrez en cache.
Yup, les déclencheurs semblent la voie à suivre jusqu'à présent. Je les ai déjà utilisés pour les bases de données Oracle et aurait dû comprendre que MySQL les soutient.
Theres absolument rien de mal à faire des déplacements de vos données statistiques et de la stocker pour améliorer les performances des applications. N'oubliez pas que vous aurez probablement besoin de créer un ensemble de déclencheurs ou d'emplois pour conserver les rouleaux de synchronisation avec vos données source.
J'aime cette idée. Les déclencheurs mettraient la logique au même endroit afin que je n'ai pas à vous rappeler de recalculer les choses à chaque fois (et lieu) que je tiens à mettre à jour les frais d'envoi. J'espère que je peux créer une fonction de déclenchement pour obtenir l'identifiant de commande associé de l'envoi et mettre à jour cet enregistrement de commande avec les totaux calculés pour ses envois associés.
Vous pouvez également aider à éviter les problèmes en choisissant judicieusement lorsque vous souhaitez utiliser vos valeurs dénormalisées et quand aller après tout ce qui est difficile. Une fois qu'une transaction est passée dans l'historique, le risque d'être modifié est petit. Par conséquent, un rapport de vente historique doit utiliser les colonnes dénormalisées. Inversement, pendant que vous traitez la vente, il est beaucoup plus susceptible d'être volatil, de sorte que le rapport d'impression de facture (unique) devrait faire les choses de manière difficile.
En plus de la gâchette, je pense que vous souhaitez également une contrainte de contrôle pour vous assurer que les chiffres s'additionnent. Si seulement MySQL a pris en charge les contraintes de contrôle. . .
Ce n'est pas une mauvaise idée, malheureusement MySQL n'a pas certaines fonctionnalités qui rendraient ces colonnes vraiment faciles - calculées et indexées (vues matérialisées). Vous pouvez probablement le simuler avec un déclencheur.
Je n'éviterais pas le plus possible possible que possible. Je préfère que pour des raisons différentes:
Il est trop difficile de discuter de la performance sans mesure. Imagerie L'utilisateur fait du shopping, ajoutant des articles de commande dans une commande; Chaque fois qu'un élément est ajouté, vous devez mettre à jour l'enregistrement de la commande, qui peut ne pas être nécessaire (certains sites ne font que montrer le total de la commande lorsque vous cliquez sur le panier d'achat et prêt à commander).
Avoir une colonne dupliquée demande des bugs - vous ne pouvez pas vous attendre à ce que chaque futur développeur / mainteneur soit au courant de cette colonne supplémentaire. Les déclencheurs peuvent aider, mais je pense que les déclencheurs ne devraient être utilisés que comme dernier recours pour aborder une mauvaise conception de la base de données.
Un schéma de base de données différent peut être utilisé à des fins de déclaration. La base de données de rapport peut être très normalisée pour le but de la performance sans compliquer l'application principale.
J'ai tendance à mettre la logique réelle pour calculer le sous-total au niveau de l'application, car le sous-total est en réalité une chose surchargée liée à différents contextes - parfois, vous voulez que le «sous-total brut», vous voulez parfois le sous-total après l'application de la remise. Vous ne pouvez tout simplement pas continuer à ajouter des colonnes à la table de commande pour différents scénarios.
Tous les très bons points. Toutefois, dans mon cas: 1. Les informations sont affichées sur la console d'administration où j'ai une liste de commandes potentiellement très longue et doit indiquer à TotalProfit pour chacun. 2. Semble que la méthode de déclenchement aurait au moins mettre la logique au même endroit, contraignant les bugs. 3. Complique les rapports 4. Atténuée par la somme de la même manière verticale des mêmes champs et de laisser la demande décider des parties qu'il se soucie.
Utilisez des déclencheurs pour effectuer la mise à jour forte> Si les déclencheurs prennent beaucoup de temps, assurez-vous de faire ces mises à jour En dehors des heures de pointe. p> Ne faites pas de facturation sur les commandes qui n'ont pas été traitées par la requête de validation forte> C'est aussi lent. P>
BlockQuote> Cela dépend beaucoup de la façon dont vous interrogez le dB.
Si et lorsque vous rencontrez des problèmes de performances et si vous ne pouvez pas résoudre ces autres moyens, l'option n ° 3 est la voie à suivre.
Vous devez utiliser des déclencheurs après l'insertion, la mise à jour et la suppression pour conserver les sous-totaux de votre tableau de commande en synchronisation avec les données sous-jacentes.
Prenez des soins particuliers lors de la modification rétrospective des prix et des trucs, car cela nécessitera un recalc complet de tous les sous-totaux.
Donc, vous aurez besoin de beaucoup de déclencheurs, qui ne font généralement pas beaucoup la plupart du temps.
Si un Taxrate change, il changera à l'avenir, pour les commandes que vous n'avez pas encore EM> P>
Vous voudrez peut-être aussi conserver un Golden code> sous-requête en place qui calcule toutes les valeurs et les vérifie contre les valeurs stockées dans la table de commande.
Exécutez cette requête toutes les nuits et faites-la signaler toutes les anomalies, afin que vous puissiez voir lorsque les valeurs dénormalisées sont obsolètes. P>
Ajoutez un champ de date supplémentaire au tableau Commander CODE> appelé TIMEOFLASTSECCESSELVALLVALIDATION CODE> et devez-le définir sur null code> si la validation était infructueuse.
Seuls les éléments de facturation avec un dateflastscucesfullvalidation code> il y a moins de 24 heures.
Bien sûr, vous n'avez pas besoin de vérifier les commandes entièrement traitées, uniquement des commandes en attente. P>
En ce qui concerne # 1 p>
Vous mentionnez des sous-éléments, dans la requête X / EM> surtout complète em> Je ne vois pas le besoin de nombreux sous-éléments, de sorte que vous m'avez perplexe un peu. P> SELECT field1,field2,field3
, oifield1,oifield2,oifield3
, NettItemCost * (1+taxrate) as TotalItemCost
, TotalShippingCost
, TotalHandlingCost
, NettItemCost * taxRate as TotalTaxCost
, (NettItemCost * (1+taxrate)) + TotalShippingCost + TotalHandlingCost as TotalCost
, TotalPaid
, somethingorother as TotalProfit
FROM (
SELECT o.field1,o.field2, o.field3
, oi.field1 as oifield1, i.field2 as oifield2 ,oi.field3 as oifield3
, SUM(c.productprice * oi.qty) as NettItemCost
, SUM(IFNULL(sc.shippingperkg,0) * oi.qty * p.WeightInKg) as TotalShippingCost
, SUM(IFNULL(hc.handlingperwhatever,0) * oi.qty) as TotalHandlingCost
, t.taxrate as TaxRate
, IFNULL(pay.amountpaid,0) as TotalPaid
FROM orders o
INNER JOIN orderitem oi ON (oi.order_id = o.id)
INNER JOIN products p ON (p.id = oi.product_id)
INNER JOIN prices c ON (c.product_id = p.id
AND o.orderdate BETWEEN c.validfrom AND c.validuntil)
INNER JOIN taxes t ON (p.tax_id = t.tax_id
AND o.orderdate BETWEEN t.validfrom AND t.validuntil)
LEFT JOIN shippingcosts sc ON (o.country = sc.country
AND o.orderdate BETWEEN sc.validfrom AND sc.validuntil)
LEFT JOIN handlingcost hc ON (hc.id = oi.handlingcost_id
AND o.orderdate BETWEEN hc.validfrom AND hc.validuntil)
LEFT JOIN (SELECT SUM(pay.payment) as amountpaid FROM payment pay
WHERE pay.order_id = o.id) paid ON (1=1)
WHERE o.id BETWEEN '1245' AND '1299'
GROUP BY o.id DESC, oi.id DESC ) AS sub
Pourquoi ne pas mettre en cache les sous-totaux de la couche d'application, chaque fois que vous effectuez une mise à jour sur la base de données, mettez à jour le cache de sous-total dans la couche d'application (en faisant de base aritméthic), puis écrivez à la base de données, de cette façon, vous calculez uniquement les sous-totaux lorsque l'application commence et de temps en temps pour valider le cache.
Si vous pouviez utiliser un RDBM plus avancé que MySQL, vous pourrez peut-être avoir votre gâteau et le manger aussi, avec une vue matérialisée.
@ todda.speot.is +1 pour manger du gâteau. Aussi pour des vues matérialisées, je suppose.