Les bases de données OLAP doivent-elles être dénormalisées pour les performances de lecture?

Mythologie

J’ai toujours pensé que les bases de données devaient être dénormalisées pour la lecture, comme c’est le cas pour la conception de la firebase database OLAP, et pas trop exagérées pour la conception OLTP.

Il y a un mythe à cet effet. Dans le contexte de la firebase database relationnelle, j’ai ré-implémenté six très grandes “bases de données” “normalisées”. et exécuté plus de quatre-vingts missions corrigeant des problèmes sur d’autres, simplement en les normalisant, en appliquant des normes et des principes d’ingénierie. Je n’ai jamais vu aucune preuve du mythe. Seules les personnes répétant le mantra comme s’il s’agissait d’une sorte de prière magique.

Normalisation vs non normalisé

(“Dé-normalisation” est un terme frauduleux que je refuse de l’utiliser.)

Il s’agit d’une indussortinge scientifique (du moins, celle qui fournit des logiciels qui ne cassent pas, qui mettent les gens sur la lune, qui utilisent des systèmes bancaires, etc.). Il est régi par les lois de la physique et non par la magie. Les ordinateurs et les logiciels sont tous des objects physiques finis, tangibles et soumis aux lois de la physique. Selon l’enseignement secondaire et supérieur, j’ai reçu:

• Il n’est pas possible qu’un object plus gros, plus gros et moins organisé fonctionne mieux qu’un object plus petit, plus mince et plus organisé.

• La normalisation donne plus de tables, oui, mais chaque table est beaucoup plus petite. Et même s’il y a plus de tables, il y a en fait (a) moins de jointures et (b) les jointures sont plus rapides car les ensembles sont plus petits. Moins d’indices sont nécessaires dans l’ensemble, car chaque petite table nécessite moins d’indices. Les tables normalisées génèrent également des tailles de lignes beaucoup plus courtes.

• pour un ensemble de ressources donné, les tables normalisées:

  • ajuster plus de lignes dans la même taille de page
  • donc adapter plus de lignes dans le même espace de cache, donc le débit global est augmenté)
  • donc adapter plus de lignes dans le même espace disque, donc le nombre d’E / S est réduit; et lorsque les E / S sont requirejses, chaque E / S est plus efficace.
    .
• Il n’est pas possible qu’un object fortement dupliqué fonctionne mieux qu’un object stocké sous la forme d’une version unique de la vérité. Par exemple. lorsque j’ai supprimé la duplication 5 x au niveau de la table et de la colonne, toutes les transactions ont été réduites; le locking réduit; la mise à jour des anomalies a disparu. Cela a considérablement réduit les conflits et donc l’augmentation de l’utilisation simultanée.

Le résultat global était donc beaucoup plus performant.

D’après mon expérience, qui fournit à la fois OLTP et OLAP à partir de la même firebase database, il n’a jamais été nécessaire de «dé-normaliser» mes structures normalisées pour obtenir une vitesse plus élevée pour les requêtes en lecture seule (OLAP). C’est un mythe aussi.

• Non, la “dé-normalisation” demandée par d’autres a réduit la vitesse, et elle a été éliminée. Pas de surprise pour moi, mais encore une fois, les demandeurs ont été surpris.

Beaucoup de livres ont été écrits par des gens, vendant le mythe. Il faut reconnaître que ce sont des personnes non techniques; comme ils vendent de la magie, la magie qu’ils vendent n’a aucune base scientifique et ils évitent commodément les lois de la physique dans leur argumentaire de vente.

(Pour quiconque souhaite contester la science physique ci-dessus, répéter simplement le mantra n’aura aucun effet, veuillez fournir des preuves spécifiques soutenant le mantra.)

Pourquoi le mythe est-il prévalent?

Eh bien, tout d’abord, il n’est pas répandu parmi les types scientifiques, qui ne cherchent pas les moyens de surmonter les lois de la physique.

De mon expérience, j’ai identifié trois raisons principales de la prévalence:

1. Pour les personnes qui ne peuvent pas normaliser leurs données, il convient de ne pas le faire. Ils peuvent se référer au livre de magie et sans aucune preuve de la magie, ils peuvent dire avec respect “voir un écrivain célèbre valider ce que j’ai fait”. Pas fait, le plus précisément.

2. De nombreux codeurs SQL ne peuvent écrire que du SQL simple et simple. Les structures normalisées nécessitent un peu de capacité SQL. S’ils n’en ont pas s’ils ne peuvent pas produire de SELECT sans utiliser de tables temporaires; S’ils ne peuvent pas écrire des sous-requêtes, ils seront collés psychologiquement à des fichiers plats (ce que sont les structures “dé-normalisées”), qu’ils peuvent traiter.

3. Les gens aiment lire des livres et discuter de théories. Sans expérience Surtout en ce qui concerne la magie. C’est un tonique, un substitut à l’expérience réelle. Quiconque a normalisé correctement une firebase database n’a jamais déclaré que “la normalisation est plus rapide que la normalisation”. Pour ceux qui disent le mantra, je dis simplement «montre-moi les preuves» et ils n’en ont jamais produit. Donc, la réalité est que les gens répètent la mythologie pour ces raisons, sans aucune expérience de normalisation . Nous sums des animaux de troupeau et l’inconnu est l’une de nos plus grandes peurs.

   C’est pourquoi j’inclus toujours le SQL “avancé” et le mentorat sur tout projet.

Ma réponse

Cette réponse sera ridiculement longue si je réponds à chaque partie de votre question ou si je réponds aux éléments incorrects de certaines des autres réponses. Par exemple. le ci-dessus a répondu à un seul élément. Je vais donc répondre à votre question au total sans aborder les composants spécifiques et adopter une approche différente. Je ne traiterai que de la science liée à votre question, avec laquelle je suis qualifié et très expérimenté.

Permettez-moi de vous présenter la science dans des segments gérables.
Le modèle type des six missions de mise en œuvre complètes à grande échelle.

• Celles-ci étaient les “bases de données” fermées que l’on trouvait couramment dans les petites entresockets et les organisations étaient de grandes banques.
• très agréable pour une première génération, un état d’esprit qui fonctionne, mais un échec complet en termes de performances, d’intégrité et de qualité
• ils ont été conçus pour chaque application, séparément
• la création de rapports n’était pas possible, ils ne pouvaient signaler que via chaque application
• puisque “dé-normalisé” est un mythe, la définition technique exacte est qu’ils n’étaient pas normalisés
  • Pour “dé-normaliser”, il faut d’abord normaliser; puis inverser un peu le processus dans tous les cas où les gens m’ont montré leurs modèles de données “dé-normalisés”, le simple fait était qu’ils ne s’étaient pas normalisés du tout; la “dé-normalisation” n’était donc pas possible; il était simplement non normalisé
• comme ils n’avaient pas beaucoup de technologie relationnelle, ou les structures et le contrôle des bases de données, mais ils ont été transmis en tant que “bases de données”, j’ai placé ces mots entre guillemets
• comme il est scientifiquement garanti pour les structures non normalisées, ils ont subi plusieurs versions de la vérité (duplication des données) et donc une contention élevée et une faible concurrence, dans chacun d’eux
• ils avaient un problème supplémentaire de duplication des données à travers les “bases de données”
• l’organisation essayait de garder tous ces doublons synchronisés, ils ont donc implémenté la réplication; ce qui signifiait bien sûr un serveur supplémentaire; ETL et scripts de synchronisation à développer; et entretenu; etc
• Inutile de dire que la synchronisation n’a jamais été suffisante et ils la changeaient toujours
• avec tout ce conflit et ce faible débit, il n’y avait aucun problème justifiant un serveur séparé pour chaque “firebase database”. Cela n’a pas beaucoup aidé.

Nous avons donc envisagé les lois de la physique et nous avons appliqué un peu de science.
Nous avons implémenté le concept Standard selon lequel les données appartiennent à l’entreprise (et non aux départements) et la société voulait une version de la vérité. La firebase database était pure relationnelle, normalisée à 5NF. Pure Open Architecture, de sorte que toute application ou outil de rapport puisse y accéder. Toutes les transactions dans les processus stockés (par opposition aux chaînes de code SQL non contrôlées sur tout le réseau). Les mêmes développeurs pour chaque application ont codé les nouvelles applications, après notre formation “avancée”.

Evidemment, la science a fonctionné. Eh bien, ce n’était pas ma science privée ou ma magie, c’était l’ingénierie ordinaire et les lois de la physique. Tout s’est déroulé sur une plate-forme de serveur de firebase database; deux paires (production et DR) de serveurs ont été mises hors service et confiées à un autre département. Les 5 “bases de données” totalisant 720 Go ont été normalisées en une seule firebase database totalisant 450 Go. Environ 700 tables (de nombreuses copies et colonnes dupliquées) ont été normalisées dans 500 tables non dupliquées. Il s’est montré beaucoup plus rapide, 10 fois plus rapide dans l’ensemble, et plus de 100 fois plus rapide dans certaines fonctions. Cela ne m’a pas surpris, car c’était mon intention, et la science l’avait prédit, mais cela a surpris les gens avec le mantra.

Plus de normalisation

Eh bien, ayant eu du succès avec la normalisation dans chaque projet et avec la confiance dans la science impliquée, il s’est avéré une progression naturelle de normaliser davantage , pas moins. Autrefois, le 3NF était assez bon et plus tard, les FN n’ont pas encore été identifiés. Au cours des 20 dernières années, je n’ai livré que des bases de données qui ne présentaient aucune anomalie de mise à jour. Il s’agit donc de définitions actuelles des FN, j’ai toujours livré des 5F.

De même, 5NF est génial mais il a ses limites. Par exemple. Le pivotement de grands tableaux (pas de petits ensembles de résultats selon l’extension MS PIVOT) était lent. Donc, j’ai (et d’autres) développé une façon de fournir des tableaux normalisés tels que Pivoter était (a) facile et (b) très rapide. Il s’avère, maintenant que 6NF a été défini, que ces tables sont 6NF.

Comme je fournis OLAP et OLTP à partir de la même firebase database, j’ai constaté que, conformément à la science, plus les structures sont normalisées:

• plus vite ils effectuent

• et ils peuvent être utilisés de plusieurs manières (par exemple, les pivots)

Donc oui, j’ai une expérience constante et constante, non seulement la normalisation est beaucoup, beaucoup plus rapide que la normalisation ou la «dé-normalisation»; plus normalisé est encore plus rapide que moins normalisé.

Un signe de réussite est la croissance des fonctionnalités (le signe de l’échec est la croissance en taille sans croissance des fonctionnalités). Ce qui signifiait qu’ils nous demandaient immédiatement plus de fonctionnalités de reporting, ce qui signifiait que nous nous normalisions encore plus et fournissions plus de ces tables spécialisées (qui se sont avérées être des années plus tard, soit 6NF).

Progresser sur ce thème. J’étais toujours un spécialiste de la firebase database, pas un spécialiste de l’entrepôt de données, donc mes premiers projets avec des entrepôts n’étaient pas des implémentations complètes, mais plutôt des affectations substantielles de réglage des performances. Ils étaient dans mon domaine, sur des produits dans lesquels je me suis spécialisé.
Ne vous inquiétez pas du niveau exact de normalisation, etc., car nous examinons le cas typique. Nous pouvons considérer comme étant donné que la firebase database OLTP était raisonnablement normalisée, mais pas capable d’utiliser OLAP, et que l’entreprise avait acheté une plate-forme OLAP complètement séparée, du matériel; investi dans le développement et le maintien de masses de code ETL; etc. Après la mise en œuvre, ils ont passé la moitié de leur vie à gérer les doublons créés. Ici, les auteurs et les vendeurs de livres doivent être blâmés pour le gaspillage massif de matériel et de licences de logiciels de plate-forme séparés qu’ils incitent à acheter.

• Si vous ne l’avez pas encore observé, je vous demanderais de noter les similitudes entre la “firebase database” de première génération typique et l’ entrepôt de données type

Pendant ce temps, à la ferme (les bases de données 5NF ci-dessus), nous continuons à append de plus en plus de fonctionnalités OLAP. Bien sûr, la fonctionnalité de l’application a augmenté, mais c’était peu, l’entreprise n’avait pas changé. Ils demanderaient plus de 6NF et il était facile à fournir (5NF à 6NF est un petit pas; 0NF à tout, sans parler de 5NF, est un grand pas en avant, une architecture organisée est facile à étendre).

Une différence majeure entre OLTP et OLAP, la justification de base des logiciels de plate-forme OLAP distincts , est que le protocole OLTP est orienté ligne, nécessite des lignes sécurisées sur le plan des transactions et rapide; et le OLAP ne se soucie pas des problèmes transactionnels, il a besoin de colonnes et rapide. C’est la raison pour laquelle toutes les plates – formes BI ou OLAP haut de gamme sont axées sur les colonnes, et c’est pourquoi les modèles OLAP (Star Schema, Dimension-Fact) sont axés sur les colonnes.

Mais avec les tables 6NF:

• il n’y a pas de lignes, seulement des colonnes; nous servons des lignes et des colonnes à la même vitesse aveuglante

• les tableaux (à savoir la vue 5NF des structures 6NF) sont déjà organisés en Dimension-Facts. En fait, ils sont organisés en plusieurs dimensions que tout modèle OLAP ne pourrait jamais identifier, car ils sont tous des dimensions.

• Faire pivoter des tables entières avec agrégation à la volée (par opposition au PIVOT d’un petit nombre de colonnes dérivées) est (a) un code simple et sans effort et (b) très rapide

Ce que nous fournissons depuis de nombreuses années, par définition, ce sont les bases de données relationnelles avec au moins 5F pour l’utilisation OLTP et 6NF pour les exigences OLAP.

• Notez que c’est la même science que nous avons utilisée dès le départ; passer des “bases de données” non normalisées à la firebase database d’ entreprise 5NF . Nous appliquons simplement plus de la science éprouvée et obtenons des commandes plus élevées de fonctionnalités et de performances.

• Notez la similarité entre la firebase database d’entreprise 5NF et la firebase database d’entreprise 6NF

• Le coût total du matériel OLAP séparé, du logiciel de plate-forme, de l’ETL, de l’administration et de la maintenance sont tous éliminés.

• Il existe une seule version des données, aucune anomalie de mise à jour ou maintenance de celles-ci; les mêmes données servies pour OLTP en tant que lignes et pour OLAP en tant que colonnes

La seule chose que nous n’avons pas faite, c’est de commencer un nouveau projet et de déclarer le 6NF pur dès le départ. C’est ce que j’ai aligné ensuite.

Quelle est la sixième forme normale?

En supposant que vous ayez une idée sur la normalisation (je ne vais pas le définir ici), les définitions non académiques pertinentes pour ce sujet sont les suivantes. Notez que cela s’applique au niveau de la table, vous pouvez donc avoir un mélange de tables 5NF et 6NF dans la même firebase database:

• Cinquième forme normale : toutes les dépendances fonctionnelles résolues dans la firebase database
  • en plus de 4NF / BCNF
  • chaque colonne non PK est 1 :: 1 avec son PK
  • et à aucun autre PK
  • Aucune anomalie de mise à jour
    .
• Sixième forme normale : est la NF irréductible, le point auquel les données ne peuvent plus être réduites ou normalisées (il n’y aura pas de 7NF)
  • en plus de 5NF
  • la ligne consiste en une clé primaire et au plus une colonne non-clé
  • élimine le problème nul

À quoi ressemble 6NF?

Les modèles de données appartiennent aux clients et notre propriété intellectuelle n’est pas disponible pour publication gratuite. Mais je participe à ce site Web et apporte des réponses spécifiques aux questions. Vous avez besoin d’un exemple concret, je publierai donc le modèle de données pour l’un de nos utilitaires internes.

Celui-ci concerne la collecte de données de surveillance de serveur (serveur de firebase database d’entreprise et système d’exploitation) pour un nombre illimité de clients, quelle que soit la période. Nous l’utilisons pour parsingr les problèmes de performances à distance et pour vérifier les performances que nous apportons. La structure n’a pas changé depuis plus de dix ans (en plus, sans modification des structures existantes), elle est typique de la 5NF spécialisée identifiée plusieurs années plus tard comme 6NF. Permet le pivotement complet; tout diagramme ou graphique à dessiner, sur n’importe quelle dimension (22 pivots sont fournis mais ce n’est pas une limite); émincer; mélanger et assortir. Notez qu’ils sont tous des dimensions.

Les données de surveillance ou les mésortingques ou les vecteurs peuvent changer (les changements de version du serveur, nous voulons prendre quelque chose de plus) sans affecter le modèle (vous vous souviendrez peut-être que EAV est le fils bâtard de 6NF; père non dilué, et fournit donc toutes les fonctionnalités d’EAV, sans sacrifier aucune norme, intégrité ou puissance relationnelle); vous ajoutez simplement des lignes.

▶ Surveiller le modèle de données statistiques ◀ . (trop grand pour l’inline; certains navigateurs ne peuvent pas charger en ligne; cliquez sur le lien)

Cela me permet de produire ces ▶ graphiques comme ceci ◀ , six frappes après avoir reçu un fichier de statistiques de surveillance brutes du client. Notez le mix-and-match; OS et serveur sur le même graphique; une variété de Pivots. (Utilisé avec permission.)

Les lecteurs qui ne sont pas familiarisés avec la norme de modélisation des bases de données relationnelles peuvent trouver la notation ▶ IDEF1X utile.

Entrepôt de données 6NF

Cela a été récemment validé par Anchor Modeling , dans la mesure où ils présentent désormais 6NF comme le modèle OLAP «nouvelle génération» pour les entrepôts de données. (Ils ne fournissent pas l’OLTP et l’OLAP à partir de la version unique des données, c’est le nôtre seul).

Expérience Data Warehouse (Uniquement)

Mon expérience avec les entrepôts de données uniquement (et non les bases de données OLTP-OLAP 6NF ci-dessus) a été marquée par plusieurs missions majeures, par opposition à des projets d’implémentation complets. Les résultats ont été, sans surprise:

• cohérent avec la science, les structures normalisées fonctionnent beaucoup plus rapidement; sont plus faciles à entretenir; et nécessitent moins de synchronisation des données. Inmon, pas Kimball.

• En accord avec la magie, après avoir normalisé un groupe de tables et fourni des performances sensiblement améliorées via l’application des lois de la physique, les seules personnes sursockets sont les magiciens avec leurs mantras.

Les personnes ayant l’esprit scientifique ne le font pas; ils ne croient pas ou ne comptent pas sur les balles d’argent et la magie; ils utilisent et travaillent dur pour résoudre leurs problèmes.

Justification valide de l’entrepôt de données

C’est pourquoi j’ai indiqué dans d’autres articles que la seule justification valable pour une plate-forme, un matériel, un ETL, une maintenance, etc., est le cas où de nombreuses bases de données ou bases de données sont fusionnées dans un entrepôt central. et OLAP.

Kimball

Un mot sur Kimball est nécessaire, car il est le principal partisan de la “normalisation de la performance” dans les entrepôts de données. Selon mes définitions ci-dessus, il fait partie de ces personnes qui ne se sont manifestement jamais normalisées dans leur vie; son sharepoint départ n’était pas normalisé (camouflé comme “dé-normalisé”) et il l’a simplement implémenté dans un modèle Dimension-Fact.

• Bien sûr, pour obtenir des performances, il lui fallait “dé-normaliser” encore plus, créer de nouveaux doublons et justifier tout cela.

  • Il est donc vrai que, de manière schizophrénique, la «dé-normalisation» des structures non normalisées, en faisant des copies plus spécialisées, «améliore les performances de lecture». Ce n’est pas vrai quand le tout prend en compte; c’est vrai seulement à l’intérieur de ce petit asile, pas à l’extérieur.

  • De même, il est vrai, de cette manière folle, que toutes les «tables» sont des monstres, que «les jointures sont chères» et qu’il faut les éviter. Ils n’ont jamais eu l’expérience de joindre des tables et des ensembles plus petits, ils ne peuvent donc pas croire le fait scientifique que des tables plus petites sont plus rapides.

  • Ils ont constaté que la création de «tables» en double est plus rapide. Ils ne peuvent donc pas croire que l’ élimination des doublons est encore plus rapide.

• ses dimensions sont ajoutées aux données non normalisées. Eh bien, les données ne sont pas normalisées, donc aucune dimension n’est exposée. Alors que dans un modèle normalisé, les dimensions sont déjà exposées, en tant que partie intégrante des données, aucune addition n’est requirejse.

• ce chemin bien pavé de Kimball mène à la falaise, où plus de lemmings tombent à mort plus rapidement. Les lemmings sont des animaux de troupeau, tant qu’ils marchent ensemble et meurent ensemble, ils meurent heureux. Les lemmings ne cherchent pas d’autres chemins.

Tout simplement des histoires, des parties de la mythologie qui se côtoient et se soutiennent mutuellement.

Votre mission

Si vous choisissez de l’accepter. Je vous demande de penser par vous-même et de cesser de divertir les pensées qui contredisent la science et les lois de la physique. Peu importe si elles sont communes ou mystiques ou mythologiques. Rechercher des preuves pour quelque chose avant de lui faire confiance. Soyez scientifique, vérifiez les nouvelles croyances pour vous-même. La répétition du mantra “dé-normalisé pour la performance” ne rendra pas votre firebase database plus rapide, elle vous permettra de vous sentir mieux. Comme le gros gosse assis en marge en se disant qu’il peut courir plus vite que tous les enfants de la course.

• sur cette base, même le concept “normaliser pour OLTP” mais faire le contraire, “dé-normaliser pour OLAP” est une contradiction. Comment les lois de la physique peuvent-elles fonctionner comme indiqué sur un ordinateur, mais inverser sur un autre ordinateur? Le mental s’embrouille. Ce n’est tout simplement pas possible, le travail est identique sur tous les ordinateurs.

Des questions ?

La dénormalisation et l’agrégation sont les deux principales stratégies utilisées pour obtenir des performances dans un entrepôt de données. Il est juste idiot de suggérer que cela n’améliore pas les performances de lecture! Je dois sûrement avoir mal compris quelque chose ici?

Agrégation: Considérons une table contenant 1 milliard d’achats. Contrastez-le avec une table contenant une ligne avec la sum des achats. Maintenant, qui est le plus rapide? Sélectionnez la sum (montant) de la table d’un milliard de lignes ou un montant sélectionné du tableau à une ligne? C’est un exemple stupide bien sûr, mais il illustre clairement le principe de l’agrégation. Pourquoi est-ce plus rapide? Car quel que soit le modèle / matériel / logiciel / religion magique que nous utilisons, la lecture de 100 octets est plus rapide que la lecture de 100 gigaoctets. Aussi simple que cela.

Dénormalisation: une dimension de produit typique dans un entrepôt de données de vente au détail contient des quantités de colonnes. Certaines colonnes sont des trucs faciles comme “Nom” ou “Couleur”, mais elles contiennent également des éléments compliqués, comme les hiérarchies. Hiérarchies multiples (gamme de produits (5 niveaux), acheteur prévu (3 niveaux), matières premières (8 niveaux), mode de production (8 niveaux) ainsi que plusieurs chiffres calculés tels que le délai moyen (depuis le début de l’année) , mesures de poids / d’emballage, etc. J’ai mis à jour une table de dimensions de produits avec plus de 200 colonnes, composée de ~ 70 tables provenant de cinq systèmes sources différents.

select product_id from table1 join table2 on(keys) join (select average(..) from one_billion_row_table where lastyear = ...) on(keys) join ...table70 where function_with_fuzzy_matching(table1.cola, table37.colb) > 0.7 and exists(select ... from ) and not exists(select ...) and table20.version_id = (select max(v_id from product_ver where ...) and average_price between 10 and 20 and product_range = 'High-Profile' 

… est plus rapide que la requête équivalente sur le modèle dénormalisé:

 select product_id from product_denormalized where average_price between 10 and 20 and product_range = 'High-Profile'; 

Pourquoi? En partie pour la même raison que le scénario agrégé. Mais aussi parce que les requêtes sont simplement “compliquées”. Ils sont tellement compliqués que l’optimiseur (et maintenant je vais parler des spécificités d’Oracle) devient confus et gâche les plans d’exécution. Des plans d’exécution sous-optimaux peuvent ne pas être si importants si la requête traite de petites quantités de données. Mais dès que nous commençons à participer aux Big Tables, il est essentiel que la firebase database obtienne le bon plan d’exécution. Après avoir dénormalisé les données dans une table avec une seule clé syntaxique (heck, pourquoi n’ajoute-je pas plus de carburant à ce feu en cours), les filtres deviennent de simples filtres d’échelle / égalité sur des colonnes précuites. Le fait de dupliquer les données dans de nouvelles colonnes nous permet de rassembler des statistiques sur les colonnes, ce qui aidera l’optimiseur à estimer les sélectivités et nous fournira ainsi un plan d’exécution approprié (enfin, …).

Evidemment, l’utilisation de la dénormalisation et de l’agrégation rend plus difficile les modifications de schéma, ce qui est une mauvaise chose. D’autre part, ils fournissent des performances en lecture, ce qui est une bonne chose.

Alors, devriez-vous dénormaliser votre firebase database afin d’obtenir des performances de lecture? Sûrement pas! Il ajoute tellement de complexités à votre système qu’il n’ya pas de limite au nombre de manières dont il vous gênera avant que vous ne livriez. Est-ce que ça vaut le coup? Oui, vous devez parfois le faire pour répondre à une exigence de performance spécifique.

Mise à jour 1

PerformanceDBA: 1 ligne serait mise à jour un milliard de fois par jour

Cela impliquerait une exigence (proche) en temps réel (ce qui générerait à son tour un ensemble complètement différent d’exigences techniques). Beaucoup d’entrepôts de données (sinon la plupart) n’ont pas cette exigence. J’ai choisi un exemple d’agrégation irréaliste simplement pour expliquer pourquoi l’agrégation fonctionne. Je ne voulais pas avoir à expliquer les stratégies de rollup aussi 🙂

De plus, il faut comparer les besoins de l’utilisateur type d’un entrepôt de données et de l’utilisateur type du système OLTP sous-jacent. Un utilisateur cherchant à comprendre quels sont les facteurs qui entraînent les coûts de transport, se fiche de savoir si 50% des données actuelles sont manquantes ou si 10 camions ont explosé et tué les conducteurs. L’exécution de l’parsing sur une période de deux ans de données aboutirait toujours à la même conclusion, même s’il disposait d’informations de dernière minute à sa disposition.

Comparez cela aux besoins des conducteurs de ce camion (ceux qui ont survécu). Ils ne peuvent pas attendre 5 heures à un sharepoint transit simplement parce qu’un processus d’agrégation stupide doit se terminer. Avoir deux copies séparées des données résout les deux besoins.

Un autre obstacle majeur au partage du même dataset pour les systèmes opérationnels et les systèmes de reporting est que les cycles de publication, les Q & R, le déploiement, les SLA et les autres éléments sont très différents. Encore une fois, il est plus facile de gérer deux copies distinctes.

Par “OLAP”, je vous comprends comme une firebase database relationnelle / SQL orientée sujet, utilisée pour l’aide à la décision – AKA, Data Warehouse.

La forme normale (généralement la forme normale 5/6) est généralement le meilleur modèle pour un entrepôt de données. Les raisons de normaliser un entrepôt de données sont exactement les mêmes que pour toute autre firebase database: elles réduisent la redondance et évitent les anomalies potentielles de mise à jour; il évite les biais intégrés et constitue donc le moyen le plus simple de prendre en charge les modifications de schéma et les nouvelles exigences. L’utilisation de la forme normale dans un entrepôt de données permet également de garder le processus de chargement des données simple et cohérent.

Il n’y a pas d’approche de “dénormalisation” traditionnelle. De bons entrepôts de données ont toujours été normalisés.

Une firebase database ne devrait-elle pas être dénormalisée pour les performances de lecture?

OK, voici un total “Votre kilométrage peut varier”, “Cela dépend”, “Utilisez l’outil approprié pour chaque travail”, “Une taille ne convient pas à tous” répond, avec un peu de “Ne le répare pas si Ain’t Broken “lancé dans:

La dénormalisation est un moyen d’améliorer les performances des requêtes dans certaines situations. Dans d’autres situations, il peut effectivement réduire les performances (en raison de l’utilisation accrue du disque). Cela rend certainement les mises à jour plus difficiles.

Cela ne devrait être envisagé que lorsque vous rencontrez un problème de performance (car vous donnez les avantages de la normalisation et introduisez de la complexité).

Les inconvénients de la dénormalisation sont moins problématiques avec les données qui ne sont jamais mises à jour ou uniquement mises à jour dans les travaux par lots, c’est-à-dire pas les données OLTP.

Si la dénormalisation résout un problème de performance que vous devez résoudre, et que les techniques moins invasives (comme les index, les caches ou l’achat d’un serveur plus grand) ne sont pas résolues, alors oui, vous devriez le faire.

D’abord mes opinions, puis quelques parsings

Des avis
La dénormalisation est perçue comme une aide à la lecture des données, car l’utilisation courante du mot dénormalisation inclut souvent non seulement la rupture des formes normales, mais également l’introduction de dépendances d’insertion, de mise à jour et de suppression dans le système.

Ceci, à proprement parler, est faux , voir cette question / réponse . Dénormalisation au sens ssortingct signifie briser une des formes normales de 1NF-6NF, les autres dépendances d’insertion, de mise à jour et de suppression sont traitées avec Principle of Orthogonal Design .

Donc, ce qui se passe, c’est que les gens adoptent le principe du compromis espace / temps et se souviennent du terme redondance (associé à la dénormalisation, qui n’est toujours pas le même) et concluent que vous devriez avoir des avantages. C’est une implication erronée, mais les fausses implications ne vous permettent pas de conclure l’inverse.

Briser des formes normales peut en effet accélérer la récupération de certaines données (détails en parsing ci-dessous), mais en règle générale, cela se fera aussi en même temps:

• privilégiez uniquement le type de requêtes spécifique et ralentissez tous les autres chemins d’access
• augmenter la complexité du système (qui influence non seulement la maintenance de la firebase database elle-même, mais augmente également la complexité des applications consommant les données)
• obscurcir et affaiblir la clarté sémantique de la firebase database
• Le point principal des systèmes de bases de données, car les données centrales représentant l’espace problématique doivent être impartiales dans l’enregistrement des faits, de sorte que lorsque les exigences changent, vous n’avez pas à repenser les parties du système (données et applications) indépendantes dans la réalité. pour pouvoir faire cela, il faut minimiser les dépendances artificielles – l’exigence «critique» d’accélérer une requête devient souvent marginale.

Une parsing

Donc, j’ai prétendu que parfois, briser des formes normales peut aider à la récupération. Il est temps de donner quelques arguments

1) Briser 1NF

Supposons que vous avez des dossiers financiers dans 6NF. À partir d’une telle firebase database, vous pouvez sûrement obtenir un rapport sur le solde de chaque compte pour chaque mois.

En supposant qu’une requête qui aurait à calculer un tel rapport doive passer par n enregistrements, vous pouvez créer une table

 account_balances(month, report) 

qui contiendrait des soldes XML structurés pour chaque compte. Cela casse 1NF (voir notes plus loin), mais permet à une requête spécifique de s’exécuter avec un minimum d’E / S.

Dans le même temps, en supposant qu’il soit possible de mettre à jour chaque mois avec des insertions, des mises à jour ou des suppressions d’enregistrements financiers, les performances des requêtes de mise à jour sur le système peuvent être ralenties par une durée proportionnelle à une fonction de n pour chaque mise à jour . (le cas ci-dessus illustre un principe, en réalité vous auriez de meilleures options et l’avantage d’obtenir un minimum d’E / S apporterait de telles pénalités que pour un système réaliste qui met à jour les données, type de charge de travail réelle, peut expliquer cela plus en détail si vous voulez)

Note: Ceci est en fait un exemple sortingvial et il y a un problème – la définition de 1NF. En supposant que le modèle ci-dessus casse 1NF, c’est en fonction de l’exigence que les valeurs d’un atsortingbut « contiennent exactement une valeur du domaine applicable ».

Cela vous permet de dire que le domaine du rapport d’atsortingbuts est un ensemble de tous les rapports possibles et qu’à partir de tous, il y a exactement une valeur et que 1NF n’est pas cassé (similaire à l’argument vous pourriez avoir des relations de letters quelque part dans votre modèle).

D’un autre côté, il existe des moyens beaucoup plus efficaces de modéliser ce tableau, ce qui serait plus utile pour un plus large éventail de requêtes (par exemple pour récupérer des soldes pour un compte unique pour tous les mois dans une année). Dans ce cas, vous justifieriez cette amélioration en disant que ce champ n’est pas dans 1NF.

Quoi qu’il en soit, cela explique pourquoi les gens affirment que la rupture des FN pourrait améliorer les performances.

2) Briser 3NF

En supposant que les tableaux dans 3NF

 CREATE TABLE `t` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `member_id` int(10) unsigned NOT NULL, `status` tinyint(3) unsigned NOT NULL, `amount` decimal(10,2) NOT NULL, `opening` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`), KEY `member_id` (`member_id`), CONSTRAINT `t_ibfk_1` FOREIGN KEY (`member_id`) REFERENCES `m` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB CREATE TABLE `m` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB 

avec des exemples de données (1M de lignes en t, 100k en m)

Supposons une requête commune que vous souhaitez améliorer

 mysql> select sql_no_cache m.name, count(*) from t join m on t.member_id = m.id where t.id between 100000 and 500000 group by m.name; +-------+----------+ | name | count(*) | +-------+----------+ | omega | 11 | | test | 8 | | test3 | 399982 | +-------+----------+ 3 rows in set (1.08 sec) 

vous pouvez trouver des suggestions pour déplacer le name atsortingbut dans la table m qui casse 3NF (il a une FD: member_id -> name et member_id n’est pas une clé de t)

après

 alter table t add column varchar(255); update t inner join m on t.member_id = t.id set t.name = m.name; 

fonctionnement

 mysql> select sql_no_cache name, count(*) from t where id between 100000 and 500000 group by name; +-------+----------+ | name | count(*) | +-------+----------+ | omega | 11 | | test | 8 | | test3 | 399982 | +-------+----------+ 3 rows in set (0.41 sec) 

notes: Le temps d’exécution de la requête ci-dessus est réduit de moitié , mais

• la table n’était pas dans 5NF / 6NF pour commencer
• le test a été effectué avec no_sql_cache, donc la plupart des mécanismes de cache ont été évités (et dans des situations réelles, ils jouent un rôle dans les performances du système)
• la consommation d’espace est augmentée d’environ 9 fois la taille du nom de la colonne x 100 000 lignes
• il devrait y avoir des déclencheurs pour conserver l’intégrité des données, ce qui ralentirait considérablement toutes les mises à jour et appendait des vérifications supplémentaires que les insertions dans t auraient besoin de passer
• On pourrait probablement obtenir de meilleurs résultats en lâchant des clés de substitution, en passant à des clés naturelles et / ou en indexant ou en redéfinissant les normes pour des normes nationales plus élevées.

La normalisation est la bonne façon à long terme. Mais vous n’avez pas toujours la possibilité de redéfinir l’ERP de votre entreprise (qui, par exemple, ne concerne déjà que la 3NF) – vous devez parfois accomplir certaines tâches avec des ressources données. Bien entendu, cette solution n’est que de courte durée.

Ligne de fond

Je pense que la réponse la plus pertinente à votre question est que l’indussortinge et l’éducation utilisent le terme «dénormalisation» dans

• sens ssortingct, pour briser les FN
• vaguement, pour introduire des dépendances d’insertion, de mise à jour et de suppression (la citation originale de Codd commente sur la normalisation en disant: ‘dépendances indésirables (!) d’insertion, de mise à jour et de suppression’, voir quelques détails ici )

Ainsi, sous une définition ssortingcte, l’agrégation (tableaux récapitulatifs) n’est pas considérée comme une dénormalisation et peut être très utile en termes de performances (tout comme le cache, qui n’est pas perçu comme une dénormalisation).

L’utilisation libre englobe à la fois les formes normales brisées et le principe de la conception orthogonale , comme indiqué précédemment.

Une autre chose qui pourrait nous éclairer, c’est qu’il existe une différence très importante entre le modèle logique et le modèle physique .

Par exemple, les index stockent des données redondantes, mais personne ne les considère comme dénormalisées, pas même les personnes qui utilisent le terme de manière souple et deux raisons (liées) à cela.

• ils ne font pas partie du modèle logique
• ils sont transparents et garantis pour ne pas casser l’intégrité de votre modèle

Si vous ne parvenez pas à modéliser correctement votre modèle logique, vous vous retrouvez avec une firebase database incohérente – types de relations erronés entre vos entités (incapacité à représenter un espace de problème), faits contradictoires (possibilité de perdre des informations). un modèle logique correct, c’est une base pour toutes les applications qui seront construites dessus.

La normalisation, la sémantique orthogonale et claire de vos prédicats, les atsortingbuts bien définis, les dépendances fonctionnelles correctement identifiées jouent tous un rôle pour éviter les pièges.

En ce qui concerne l’implémentation physique, les choses se relâchent en ce sens que la colonne calculée matérialisée dépend de la non-clé peut rompre 3NF, mais s’il existe des mécanismes garantissant la cohérence, elle est autorisée dans le modèle physique de la même manière que les index. sont autorisés, mais vous devez le justifier avec soin , car la normalisation produira généralement des améliorations identiques ou meilleures et aura un impact négatif nul ou négatif et maintiendra la conception claire (ce qui réduit les coûts de développement et de maintenance des applications), ce qui entraîne des économies que vous pouvez facilement consacrer à la mise à niveau du matériel pour améliorer la vitesse encore plus que ce que l’on obtient avec la rupture des FN.

Les deux méthodologies les plus populaires pour la construction d’un entrepôt de données (DW) semblent être celles de Bill Inmon et de Ralph Kimball.

La méthodologie d’Inmon utilise une approche normalisée, tandis que Kimball utilise la modélisation dimensionnelle – schéma en écanvas dé-normalisé.

Les deux sont bien documentés jusque dans les moindres détails et ont tous deux de nombreuses implémentations réussies. Les deux présentent une “route large et bien pavée” vers une destination DW.

Je ne peux pas commenter l’approche 6NF ni Anchor Modeling car je n’ai jamais vu ni participé à un projet DW utilisant cette méthodologie. En ce qui concerne les implémentations, j’aime voyager sur des chemins bien testés – mais ce n’est que moi.

Donc, pour résumer, DW devrait-il être normalisé ou dé-normalisé? Dépend de la méthodologie que vous choisissez – choisissez-en une et respectez-la, au moins jusqu’à la fin du projet.

EDIT – Un exemple

À l’endroit où je travaille actuellement, nous avions un rapport hérité qui fonctionnait depuis toujours sur le serveur de production. Pas un simple rapport, mais une collection de 30 sous-rapports envoyés par courrier électronique à tout le monde et à sa fourmi chaque jour.

Récemment, nous avons implémenté un DW. Avec deux serveurs de rapports et un tas de rapports en place, j’espérais que nous pouvions oublier le legs. Mais non, l’inheritance est un inheritance, nous l’avons toujours eu, alors nous le voulons, nous en avons besoin, nous ne pouvons pas vivre sans lui, etc.

Le problème, c’est que la mise à jour d’un script python et de SQL a nécessité huit heures (oui, huit heures) pour fonctionner chaque jour. Inutile de dire que la firebase database et l’application ont été construites au fil des années par quelques lots de développeurs – donc, pas exactement votre 5NF.

Il était temps de recréer l’inheritance de DW. Ok, pour être bref, cela est fait et il faut 3 minutes (trois minutes) pour le produire, six secondes par sous-rapport. Et j’étais pressé de livrer, et n’optimisais même pas toutes les requêtes. C’est un facteur de 8 * 60/3 = 160 fois plus rapide – sans parler des avantages de supprimer un travail de huit heures d’un serveur de production. Je pense que je peux encore me raser une minute ou deux, mais pour le moment personne ne s’en soucie.

Comme point d’intérêt, j’ai utilisé la méthode de Kimball (modélisation dimensionnelle) pour le DW et tout ce qui est utilisé dans cette histoire est open-source.

C’est ce que tout cela (entrepôt de données) est censé être, je pense. Est-ce qu’il importe même quelle méthodologie (normalisée ou dé-normalisée) a été utilisée?

EDIT 2

En tant que point d’intérêt, Bill Inmon a publié un article bien écrit sur son site Web: A Tale of Two Architectures .

Le problème avec le mot “dénormalisé”, c’est qu’il ne spécifie pas la direction dans laquelle il faut aller. Il s’agit d’essayer de se rendre à San Francisco depuis Chicago en s’éloignant de New York.

Un schéma en écanvas ou un schéma en flocon de neige n’est certainement pas normalisé. Et il fonctionne certainement mieux qu’un schéma normalisé dans certains modèles d’utilisation. Mais il y a des cas de dénormalisation où le concepteur ne suivait aucune discipline, mais qui composait simplement des tableaux par intuition. Parfois, ces efforts ne se déroulent pas.

En bref, ne pas dénormaliser. Suivez une discipline de conception différente si vous avez confiance en ses avantages, et même si elle ne correspond pas à une conception normalisée. Mais n’utilisez pas la dénormalisation comme une excuse pour la conception aléatoire.

La réponse courte est de ne pas résoudre un problème de performance que vous n’avez pas !

Comme pour les tables basées sur le temps, le pardigm généralement accepté est d’avoir des dates valid_from et valid_to dans chaque ligne. Ceci est toujours fondamentalement 3NF car il ne fait que changer la sémantique de “ceci est la seule et unique vérification de cette entité” en “ceci est la seule et unique version de cette entité en ce moment ”

Simplification:

Une firebase database OLTP devrait être normalisée (dans la mesure du possible).

Un entrepôt de données OLAP doit être dénormalisé dans les tables Fact et Dimension (afin de minimiser les jointures).