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

Question

J'ai toujours pensé que les bases de données devraient être dénormalisées pour les performances de lecture, comme cela est fait pour la conception de bases de données OLAP, et ne pas exagérer beaucoup plus loin 3NF pour la conception OLTP.

PerformanceDBA dans différents postes, par exemple, dans Performances des différentes approches des données temporelles défend le paradigme selon lequel les bases de données doivent toujours être bien conçues par normalisation en 5NF et 6NF (forme normale).

L'ai-je bien compris (et qu'ai-je bien compris) ?

Qu'est-ce qui ne va pas avec l'approche traditionnelle de dénormalisation/paradigme de conception des bases de données OLAP (en dessous de 3NF) et le conseil que 3NF est suffisant pour la plupart des cas pratiques de bases de données OLTP ?

Par exemple :

• "La simple vérité ... est que 6NF, exécuté correctement, est l'entrepôt de données " (PerformanceDBA)

Je dois avouer que je n'ai jamais pu saisir les théories selon lesquelles la dénormalisation facilite les performances de lecture. Quelqu'un peut-il me donner des références avec de bonnes explications logiques de ceci et des croyances contraires ?

Quelles sont les sources auxquelles je peux me référer lorsque j'essaie de convaincre mes interlocuteurs que les bases de données OLAP/entreposage de données doivent être normalisées ?

Pour améliorer la visibilité, j'ai copié ici des commentaires :

"Ce serait bien si les participants ajouteraient (divulgueraient) le nombre d'études de la vie réelle (pas d'études de projets scientifiques inclus) implémentations d'entrepôts de données en 6NF qu'ils ont vues ou auxquelles ils ont participé. Une sorte de pool rapide. Moi = 0." - Damir Sudarevic

Article sur les entrepôts de données de Wikipedia raconte :

"L'approche normalisée [par opposition à l'approche dimensionnelle de Ralph Kimball], également appelée la Modèle 3NF (Troisième Forme Normale) dont les partisans sont appelés " Inmonites ", croient en l'approche de Bill Inmon dans laquelle l'entrepôt de données doit être modélisé à l'aide d'un modèle E-R ou d'un modèle normalisé. modèle/normalisé".

Il semble que l'approche normalisée de l'entreposage des données (par Bill Inmon) soit perçue comme ne dépassant pas 3NF ( ?).

Je veux juste comprendre quelle est l'origine du mythe (ou de la croyance axiomatique omniprésente) selon lequel l'entreposage de données/OLAP est synonyme de dénormalisation ?

Damir Sudarevic répond qu'il s'agit d'une approche bien pavée. Permettez-moi de revenir à la question : Pourquoi croit-on que la dénormalisation facilite la lecture ?

Answer 1

Mythologie

J'ai toujours pensé que les bases de données devraient être dénormalisées pour la lecture, comme cela est fait pour la conception de bases de données OLAP, et ne pas exagérer beaucoup plus loin 3NF pour la conception OLTP.

Il y a un mythe à cet effet. Dans le contexte des bases de données relationnelles, j'ai réimplémenté six très grandes "bases de données" dites "dé-normalisées" ; et j'ai exécuté plus de quatre-vingts missions de correction de problèmes sur d'autres, simplement en les normalisant, en appliquant les normes et les principes d'ingénierie. Je n'ai jamais vu la moindre preuve de ce mythe. Seulement des gens qui répètent le mantra comme s'il s'agissait d'une sorte de prière magique.

Normalisation vs non normalisée

("Dé-normalisation" est un terme frauduleux que je refuse d'utiliser).

Il s'agit d'une industrie scientifique (du moins celle qui fournit des logiciels qui ne se cassent pas, qui envoie des gens sur la Lune, qui fait fonctionner des systèmes bancaires, etc.) Elle est régie par les lois de la physique, pas par la magie. Les ordinateurs et les logiciels sont tous des objets finis, tangibles et physiques qui sont soumis aux lois de la physique. D'après l'enseignement secondaire et tertiaire que j'ai reçu :

• il n'est pas possible qu'un objet plus grand, plus gros et moins organisé soit plus performant qu'un objet plus petit, plus fin et mieux organisé.

• La normalisation donne plus de tableaux, certes, mais chaque tableau est beaucoup plus petit. 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'index sont nécessaires dans l'ensemble, car chaque table plus petite nécessite moins d'index. Les tables normalisées produisent également des tailles de ligne beaucoup plus courtes.

• pour tout ensemble donné de ressources, tableaux normalisés :

  • faire tenir plus de rangées dans la même taille de page
  • donc faire tenir plus de lignes dans le même espace de cache, d'où une augmentation du débit global)
  • Par conséquent, un plus grand nombre de lignes peuvent être placées dans le même espace disque, ce qui réduit le nombre d'entrées/sorties et, lorsqu'une entrée/sortie est nécessaire, chaque entrée/sortie est plus efficace.
    .

• il n'est pas possible qu'un objet qui est fortement dupliqué soit plus performant qu'un objet qui est stocké comme une seule version de la vérité. Par exemple, lorsque j'ai supprimé la duplication 5 fois plus importante au niveau des tables et des colonnes, la taille de toutes les transactions a été réduite, le verrouillage a diminué et les anomalies de mise à jour ont disparu. Cela a considérablement réduit la contention et donc augmenté l'utilisation simultanée.

Le résultat global a donc été une performance beaucoup, beaucoup plus élevée.

Dans mon expérience, qui fournit à la fois OLTP et OLAP à partir de la même base de données, 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 également un mythe.

• Non, la "dé-normalisation" demandée par d'autres réduisait la vitesse, et elle a été éliminée. Ce n'est pas une surprise pour moi, mais là encore, les demandeurs ont été surpris.

De nombreux livres ont été écrits par des gens qui vendent le mythe. Il faut reconnaître qu'il s'agit de personnes non techniques ; puisqu'elles vendent de la magie, celle-ci n'a aucune base scientifique et elles évitent commodément les lois de la physique dans leur discours de vente.

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

Pourquoi ce mythe est-il si répandu ?

Tout d'abord, elle n'est pas répandue parmi les scientifiques, qui ne cherchent pas à surmonter les lois de la physique.

D'après mon expérience, j'ai identifié trois raisons principales à cette prévalence :

1. Pour les personnes qui ne peuvent pas normaliser leurs données, il s'agit d'une justification pratique pour ne pas le faire. Ils peuvent se référer au livre magique et, sans aucune preuve de cette magie, ils peuvent dire avec révérence "vous voyez, un écrivain célèbre valide ce que j'ai fait". Pas fait, très exactement.

2. De nombreux codeurs SQL ne peuvent écrire que du SQL simple, à un seul niveau. Les structures normalisées nécessitent un peu de connaissances en SQL. S'ils n'en ont pas, s'ils ne peuvent pas produire des SELECT sans utiliser de tables temporaires, s'ils ne peuvent pas écrire de sous-requêtes, ils seront psychologiquement collés à la hanche aux fichiers plats (ce que sont les structures "dé-normalisées"), qu'ils ne pourront pas utiliser. puede processus.

3. Personnes amour de lire des livres, et de discuter de théories. Sans expérience. Surtout en matière de magie. C'est un tonique, un substitut à l'expérience réelle. Quiconque a réellement normalisé une base de données correctement n'a jamais déclaré que "la dé-normalisation est plus rapide que la normalisation". À quiconque énonce ce mantra, je dis simplement "montrez-moi les preuves", et ils n'en ont jamais produit. La réalité est donc que les gens répètent la mythologie pour ces raisons, sans aucune expérience de la normalisation . Nous sommes 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 dans tout projet.

Ma réponse

Cette réponse va être ridiculement longue si je réponds à chaque partie de votre question ou si je réponds aux éléments incorrects dans certaines des autres réponses. Par exemple, la réponse ci-dessus n'a répondu qu'à un seul élément. Par conséquent, je vais répondre à votre question dans son ensemble, sans aborder les éléments spécifiques, et adopter une approche différente. Je ne m'occuperai que de la science liée à votre question, pour laquelle je suis qualifié et très expérimenté.

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

• Il s'agissait des "bases de données" fermées que l'on trouve généralement dans les petites entreprises, et les organisations étaient de grandes banques.
• très bien pour un esprit de première génération, mais un échec total en termes de performances, d'intégrité et de qualité.
• ils ont été conçus pour chaque application, séparément
• Il n'était pas possible de faire des rapports, ils ne pouvaient le faire que via chaque application.
• puisque la "dé-normalisation" est un mythe, la définition technique exacte est qu'ils ont été non normalisé
  • 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 fait est qu'ils n'avaient pas du tout normalisé ; la "dé-normalisation" n'était donc pas possible ; les données étaient simplement non normalisées.
• étant donné qu'ils ne disposaient pas de la technologie relationnelle, ni des structures et du contrôle des bases de données, mais qu'ils étaient présentés comme des "bases de données", j'ai placé ces mots entre guillemets.
• comme cela est scientifiquement garanti pour les structures non normalisées, elles ont souffert de multiples versions de la vérité (duplication des données) et donc d'une forte contention et d'une faible concurrence, au sein de chacune d'entre elles
• ils avaient un problème supplémentaire de duplication des données à travers les "bases de données"
• l'organisation essayait de synchroniser tous ces doublons, elle a donc mis en place la réplication, ce qui a bien sûr nécessité un serveur supplémentaire, des scripts ETL et de synchronisation à développer et à maintenir, etc.
• Inutile de dire que la synchronisation n'était jamais suffisante et qu'ils la modifiaient constamment.
• Avec toute cette contention et ce faible débit, ce n'était pas du tout un problème de justifier un serveur séparé pour chaque "base de données". Cela n'a pas aidé beaucoup.

Nous avons donc contemplé les lois de la physique, et nous avons appliqué un peu de science.
Nous avons mis en œuvre le concept standard selon lequel les données appartiennent à la société (et non aux départements) et la société voulait une seule version de la vérité. La base de données était purement relationnelle, normalisée en 5NF. Architecture ouverte pure, afin que n'importe quelle application ou outil de rapport puisse y accéder. Toutes les transactions étaient stockées dans des procs (par opposition à des chaînes de SQL incontrô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".

De toute évidence, la science a fonctionné. Ce n'était pas ma science privée ou de la magie, c'était de l'ingénierie ordinaire et les lois de la physique. Tout cela fonctionnait sur une plate-forme de serveur de base de données ; deux paires de serveurs (production et DR) ont été mises hors service et données à un autre département. Les 5 "bases de données" totalisant 720 Go ont été normalisées en une seule base de données de 450 Go. Environ 700 tables (beaucoup de doublons et de colonnes dupliquées) ont été normalisées en 500 tables non dupliquées. Les performances sont beaucoup plus rapides, 10 fois plus rapides dans l'ensemble, et plus de 100 fois plus rapides dans certaines fonctions. Cela ne m'a pas surpris, parce que c'était mon intention et que la science l'avait prédit, mais cela a surpris les personnes qui avaient le mantra.

Plus de normalisation

Et bien, ayant eu du succès avec la normalisation dans chaque projet, et la confiance dans la science impliquée, il a été une progression naturelle de normaliser más et non moins. Autrefois, 3NF était suffisant, et les NF ultérieures n'étaient pas encore identifiées. Au cours des 20 dernières années, je n'ai livré que des bases de données qui n'avaient aucune anomalie de mise à jour, donc il s'avère que selon les définitions actuelles des NFs, j'ai toujours livré 5NF.

De même, 5NF est une excellente solution, mais elle a ses limites. Par exemple, le pivotement de grands tableaux (et non de petits ensembles de résultats comme dans l'extension PIVOT de MS) était lent. J'ai donc (et d'autres) développé un moyen de fournir des tableaux normalisés de sorte que le pivotement soit (a) facile et (b) très rapide. Il s'avère, maintenant que la 6NF a été définie, que ces tableaux sont 6NF.

Comme je fournis des services OLAP et OLTP à partir de la même base de données, j'ai constaté que, conformément à la science, plus les structures sont normalisées :

• plus ils sont rapides

• et ils peuvent être utilisés de plusieurs façons (par exemple, les pivots).

Donc oui, j'ai une expérience constante et invariable, que non seulement la normalisation est beaucoup, beaucoup plus rapide que la non-normalisation ou la "dé-normalisation" ; más Normalised est encore plus rapide que moins normalisé.

Un signe de réussite est la croissance de la fonctionnalité (le signe d'échec est la croissance de la taille sans croissance de la fonctionnalité). Ce qui signifie qu'ils nous ont immédiatement demandé plus de fonctionnalités de reporting, ce qui signifie que nous avons Encore plus normalisé et a fourni davantage de ces tables spécialisées (qui se sont avérées, des années plus tard, être 6NF).

Je progresse sur ce thème. J'ai toujours été un spécialiste des bases de données, et non un spécialiste des entrepôts de données. Mes premiers projets avec des entrepôts n'étaient donc pas des mises en œuvre complètes, mais plutôt des missions substantielles d'optimisation des performances. Ils étaient dans mon champ d'action, sur des produits dont j'étais spécialiste.
Ne nous préoccupons pas du niveau exact de normalisation, etc., car nous examinons le cas typique. Nous pouvons considérer comme acquis que la base de données OLTP était raisonnablement normalisée, mais qu'elle n'était pas capable d'OLAP, et que l'organisation avait acheté une plateforme OLAP complètement séparée, du matériel, investi dans le développement et la maintenance de masses de code ETL, etc. Et après la mise en œuvre, ils ont passé la moitié de leur vie à gérer les doublons qu'ils avaient créés. Ici, les auteurs de livres et les vendeurs doivent être blâmés pour le gaspillage massif de matériel et de code ETL. séparé les licences de logiciels de plate-forme qu'ils font acheter aux organisations.

• Si vous ne l'avez pas encore observé, je vous demanderais de remarquer les similitudes entre la Base de données typique de la première génération y el Entrepôt de données typique

Pendant ce temps, à la ferme (la Bases de données 5NF ci-dessus), nous n'avons cessé d'ajouter de plus en plus de fonctionnalités OLAP. Bien sûr, la fonctionnalité de l'application a augmenté, mais c'était peu, l'activité n'avait pas changé. Ils demandaient plus de 6NF et c'était facile à fournir (de 5NF à 6NF, c'est un petit pas ; de 0NF à quoi que ce soit, sans parler de 5NF, c'est un grand pas ; une architecture organisée est facile à étendre).

L'une des principales différences entre OLTP et OLAP, la justification de base de l'OLTP. séparé OLAP, c'est que l'OLTP est orienté lignes, il a besoin de lignes transactionnellement sécurisées, et rapides ; et l'OLAP ne se soucie pas des questions transactionnelles, il a besoin de colonnes, et rapides. C'est la raison pour laquelle tous les logiciels haut de gamme de BI ou d'OLAP plates-formes sont orientés colonnes, et c'est pourquoi l'OLAP modèles (Star Schema, Dimension-Fact) sont orientés colonnes.

Mais avec les tables 6NF :

• il n'y a pas de rangées, seulement des colonnes ; nous servons des rangées et des colonnes à la même vitesse aveuglante

• les tables (c'est-à-dire la vue 5NF des structures 6NF) sont déjà organisé en Dimension-Facts. En fait, ils sont organisés en plus de Dimensions qu'aucun modèle OLAP ne pourra jamais identifier, car ils sont tous Dimensions.

• Le pivotement de tableaux entiers avec agrégation à la volée (par opposition au PIVOT d'un petit nombre de colonnes dérivées) est (a) sans effort, un code simple et (b) très rapide.

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

• Remarquez qu'il s'agit de la même science que celle que nous avons utilisée depuis le début ; pour passer de la Des "bases de données" non normalisées typiques a Base de données de l'entreprise 5NF . Nous appliquons simplement más de la science éprouvée, et obtenir des ordres supérieurs de fonctionnalité et de performance.

• Remarquez la similitude entre Base de données de l'entreprise 5NF y Base de données d'entreprise 6NF

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

• Il n'y a qu'une seule version des données, pas d'anomalies de mise à jour ou de maintenance ; les mêmes données sont 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 la 6NF pure dès le départ. C'est ce que j'ai prévu pour la suite.

Qu'est-ce que la sixième forme normale ?

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

• Cinquième forme normale : toutes les dépendances fonctionnelles résolues dans la base de données
  • 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 FN 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 est constituée d'une clé primaire et, au maximum, d'une colonne sans clé.
  • élimine le problème de la nullité

À quoi ressemble le 6NF ?

Les modèles de données appartiennent aux clients, et notre propriété intellectuelle n'est pas disponible pour une publication gratuite. Mais je suis présent sur ce site web et je fournis des réponses spécifiques aux questions. Vous avez besoin d'un exemple concret, je vais donc publier le modèle de données d'un de nos services publics internes.

Celui-ci est destiné à la collecte de données de surveillance de serveurs (serveur de base de données et système d'exploitation de classe entreprise) pour n'importe quel nombre de clients, pour n'importe quelle période. Nous utilisons ces données pour analyser les problèmes de performance à distance et pour vérifier les réglages de performance que nous effectuons. La structure n'a pas changé depuis plus de dix ans (ajouts, sans modification des structures existantes), elle est typique de la 5NF spécialisée qui, plusieurs années plus tard, a été identifiée comme 6NF. Permet de pivoter complètement, de dessiner n'importe quel graphique ou diagramme, sur n'importe quelle dimension (22 pivots sont fournis, mais ce n'est pas une limite), de découper, de mélanger et d'assortir. Remarquez qu'ils sont tous Dimensions.

Les données de surveillance, les métriques ou les vecteurs peuvent changer (la version du serveur change ; nous voulons récupérer quelque chose de plus) sans affecter le modèle (vous vous souvenez peut-être que dans un autre article, j'ai déclaré que l'EAV est le fils bâtard de la 6NF ; eh bien, il s'agit de la 6NF complète, le père non dilué, et elle fournit donc toutes les caractéristiques de l'EAV, sans sacrifier les normes, l'intégrité ou la puissance relationnelle) ; vous ajoutez simplement des lignes.

Modèle de données des statistiques du moniteur . (trop grand pour être mis en ligne ; certains navigateurs ne peuvent pas le charger en ligne ; cliquez sur le lien)

Cela me permet de produire ces Des graphiques comme celui-ci six frappes après avoir reçu un fichier de statistiques de surveillance brut du client. Remarquez le mélange des genres ; le système d'exploitation et le serveur sur le même graphique ; une variété de points pivots. (Utilisé avec permission.)

Les lecteurs qui ne sont pas familiers avec la Norme de modélisation des bases de données relationnelles peuvent trouver que la Notation IDEF1X utile.

Entrepôt de données 6NF

Cela a été récemment validé par Modélisation de l'ancrage La société présente maintenant 6NF comme le modèle OLAP de "nouvelle génération" pour les entrepôts de données. (Ils ne fournissent pas l'OLTP et l'OLAP à partir de la seule version des données, qui est la nôtre).

Expérience en entrepôt de données (uniquement)

Mon expérience des entrepôts de données uniquement (et non des bases de données OLTP-OLAP 6NF susmentionnées) a consisté en plusieurs missions importantes, par opposition à des projets de mise en œuvre complète. Les résultats ont été, sans surprise :

• Conformément à la science, les structures normalisées sont beaucoup plus rapides, plus faciles à maintenir et nécessitent moins de synchronisation des données. Inmon, pas Kimball.

• En accord avec la magie, après que j'ai normalisé un tas de tableaux et amélioré substantiellement les performances en appliquant les lois de la physique, les seules personnes surprises sont les magiciens avec leurs mantras.

Les personnes à l'esprit scientifique ne font pas cela ; elles ne croient pas aux balles d'argent et à la magie, et ne s'y fient pas ; elles utilisent la science et travaillent dur pour résoudre leurs problèmes.

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

C'est pourquoi j'ai déclaré dans d'autres posts, que la seule valide La justification d'une plateforme d'entrepôt de données séparée, du matériel, de l'ETL, de la maintenance, etc., se trouve dans le cas où il existe de nombreuses bases de données ou "bases de données", toutes fusionnées dans un entrepôt central, pour le reporting et l'OLAP.

Kimball

Un mot sur Kimball est nécessaire, car il est le principal partisan de la "dé-normalisation pour la performance" dans les entrepôts de données. Selon mes définitions ci-dessus, il fait partie de ces personnes qui ont de toute évidence n'ont jamais été normalisés dans leur vie ; son point de départ était non normalisé (camouflé en "dé-normalisé") et il l'a simplement mis en œuvre dans un modèle Dimension-Fact.

• Bien sûr, pour obtenir une quelconque performance, il a dû "dé-normaliser" encore plus, et créer d'autres doublons, et justifier tout cela.

  • Il est donc vrai, d'une manière schizophrénique, que 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 lorsque l'ensemble est pris en compte ; ce n'est vrai qu'à l'intérieur de ce petit asile, pas à l'extérieur.

  • De même, il est vrai, à leur manière, que lorsque toutes les "tables" sont des monstres, les "jointures sont coûteuses" et doivent être évitées. Ils n'ont jamais eu l'expérience de la jointure de tables et d'ensembles plus petits, et ne peuvent donc pas croire le fait scientifique que des tables plus nombreuses et plus petites sont plus rapides.

  • ils ont une expérience qui créer dupliquer les "tables" est plus rapide, donc ils ne peuvent pas croire que en éliminant duplicata est encore plus rapide que cela.

• ses Dimensions sont ajouté aux données non normalisées. 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 dimension n'est exposée. ajout est nécessaire.

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

Ce ne sont que des histoires, des parties d'une même 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 d'entretenir toute pensée qui contredit la science et les lois de la physique. Peu importe qu'elles soient communes, mystiques ou mythologiques. Cherchez des preuves de tout ce que vous croyez avant de vous y fier. Soyez scientifique, vérifiez les nouvelles croyances par vous-même. Répéter le mantra "dé-normalisé pour la performance" ne rendra pas votre base de données plus rapide, cela vous fera juste vous sentir mieux. Comme le gros gamin assis sur la touche qui se dit qu'il peut courir plus vite que tous les autres 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 fonctionner en sens inverse sur un autre ordinateur ? C'est à n'y rien comprendre. Il n'est tout simplement pas possible qu'elles fonctionnent de la même manière sur tous les ordinateurs.

Des questions ?

Answer 2

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 tout simplement stupide de suggérer que cela n'améliore pas les performances de lecture ! J'ai certainement dû mal comprendre quelque chose ici ?

Agrégation : Considérons un tableau contenant un milliard d'achats. Comparez-la avec un tableau contenant une ligne avec la somme des achats. Laquelle est la plus rapide ? Sélectionner sum(amount) dans le tableau contenant un milliard de lignes ou sélectionner amount dans le tableau contenant une seule ligne ? C'est un exemple stupide bien sûr, mais il illustre très clairement le principe de l'agrégation. Pourquoi est-ce plus rapide ? Parce que, quel que soit le modèle magique, le matériel, le logiciel ou la religion que nous utilisons, lire 100 octets est plus rapide que lire 100 gigaoctets. C'est aussi simple que cela.

Dénormalisation : Une dimension produit typique dans un entrepôt de données de vente au détail comporte un nombre impressionnant de colonnes. Certaines colonnes sont simples, comme "Nom" ou "Couleur", mais il y a aussi des éléments compliqués, comme les hiérarchies. Des hiérarchies multiples (la gamme de produits (5 niveaux), l'acheteur prévu (3 niveaux), les matières premières (8 niveaux), le mode de production (8 niveaux) ainsi que plusieurs nombres calculés tels que le délai de livraison moyen (depuis le début de l'année), les mesures de poids/emballage, etc. etc. J'ai tenu à jour un tableau de dimensions de produit avec 200+ colonnes qui a été construit à partir de ~70 tableaux provenant de 5 systèmes sources différents. Il est tout simplement stupide de débattre de la question de savoir si une requête sur le modèle normalisé (ci-dessous)

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 tout simplement "compliquées". Elles sont tellement compliquées que l'optimiseur (et là, je passe aux spécificités d'Oracle) s'y perd et fait foirer les plans d'exécution. Les plans d'exécution sous-optimaux ne sont peut-être pas si importants si la requête porte sur de petites quantités de données. Mais dès que l'on commence à joindre les grandes tables, c'est crucial que la base de données a le bon plan d'exécution. Après avoir dénormalisé les données dans une table avec une seule clé syntétique (pourquoi ne pas ajouter de l'huile sur le feu), les filtres deviennent de simples filtres de plage/égalité sur des colonnes pré-cuites. Le fait d'avoir dupliqué les données dans de nouvelles colonnes nous permet de rassembler des statistiques sur les colonnes qui aideront l'optimiseur à estimer les sélectivités et donc à nous fournir un plan d'exécution approprié (enfin, ...).

Il est évident que l'utilisation de la dénormalisation et de l'agrégation rend plus difficile l'adaptation aux changements de schémas, ce qui est une mauvaise chose. D'un autre côté, elles fournissent des performances de lecture, ce qui est une bonne chose.

Alors, devez-vous dénormaliser votre base de données afin d'obtenir des performances de lecture ? Pas du tout ! Cela ajoute tellement de complexités à votre système qu'il n'y a pas de limite au nombre de façons dont il va vous bousiller avant que vous ne l'ayez livré. Cela en vaut-il la peine ? 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 de temps (quasi) réel (qui, à son tour, générerait un ensemble complètement différent d'exigences techniques). De nombreux entrepôts de données (si ce n'est la plupart) n'ont pas cette exigence. J'ai choisi un exemple d'agrégation irréaliste afin de montrer clairement pourquoi l'agrégation fonctionne. Je ne voulais pas avoir à expliquer les stratégies de rollup aussi :)

Il faut également comparer les besoins de l'utilisateur type d'un entrepôt de données et ceux de l'utilisateur type du système OLTP sous-jacent. Un utilisateur qui cherche à comprendre les facteurs qui déterminent les coûts de transport ne se soucie guère de savoir si 50 % des données actuelles sont manquantes ou si 10 camions ont explosé et tué les conducteurs. En effectuant l'analyse sur des données de deux ans, il arriverait toujours à la même conclusion, même s'il disposait d'informations actualisées à la seconde près.

Comparez cela aux besoins des conducteurs de ce camion (ceux qui ont survécu). Ils ne peuvent pas attendre 5 heures à un point de transit juste parce qu'un processus d'agrégation stupide doit se terminer. Le fait de disposer de deux copies distinctes des données répond à ces deux besoins.

Un autre obstacle majeur au partage d'un même ensemble de données pour les systèmes opérationnels et les systèmes de reporting est que les cycles de publication, les questions et réponses, le déploiement, les accords de niveau de service, etc. sont très différents. Là encore, le fait de disposer de deux copies distinctes facilite la gestion de ce problème.

Answer 3

Par "OLAP", je comprends que vous voulez dire une base de données relationnelle / SQL orientée sujet utilisée pour l'aide à la décision - AKA un entrepôt de données.

La forme normale (typiquement la 5e / 6e forme normale) 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 n'importe quelle autre base de données : cela réduit la redondance et évite les anomalies potentielles de mise à jour ; cela évite les biais intégrés et c'est donc le moyen le plus facile de supporter les changements 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'existe pas d'approche de dénormalisation "traditionnelle". Les bons entrepôts de données ont toujours été normalisés.

Answer 4

Une base de données ne devrait-elle pas être dénormalisée pour des raisons de performance de lecture ?

D'accord, voici une réponse totalement "Votre kilométrage peut varier", "Ça dépend", "Utilisez le bon outil pour chaque travail", "Une taille ne convient pas à tous", avec un peu de "Ne le réparez pas s'il n'est pas cassé" :

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

Elle ne devrait être envisagée que lorsque vous rencontrez un problème de performance (car vous renoncez aux avantages de la normalisation et introduisez de la complexité).

Les inconvénients de la dénormalisation sont moins importants avec des données qui ne sont jamais mises à jour, ou qui ne sont mises à jour que dans le cadre de travaux par lots, c'est-à-dire des données qui ne sont pas OLTP.

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

Answer 5

D'abord mes opinions, puis quelques analyses

Opinions
La dénormalisation est perçue comme facilitant la lecture des données, car l'utilisation courante du mot dénormalisation inclut souvent non seulement la rupture des formes normales, mais aussi l'introduction de toute dépendance d'insertion, de mise à jour et de suppression dans le système.

Ceci, à proprement parler, est faux voir ceci question/réponse La dénormalisation au sens strict signifie la rupture de l'une des formes normales de 1NF-6NF, les autres dépendances d'insertion, de mise à jour et de suppression sont abordées avec Principe de la conception orthogonale .

Donc ce qui se passe, c'est que les gens prennent Principe du compromis entre l'espace et le temps et rappelez-vous le terme de licenciement (associé à la dénormalisation, mais pas encore égal à celle-ci) et concluez que vous devriez avoir des avantages. Il s'agit d'une implication erronée, mais les implications erronées ne vous permettent pas de conclure l'inverse.

Rompre les formes normales puede en effet, accélérer algunos l'extraction des données (détails dans l'analyse ci-dessous), mais en règle générale, elle se fera également en même temps :

• favoriser uniquement un type spécifique d'interrogations et ral ralentir toutes les autres voies d'accès
• accroître la complexité du système (ce qui influe non seulement sur la maintenance de la base de données elle-même, mais aussi sur la complexité des applications qui consomment les données)
• obscurcir et affaiblir la clarté sémantique de la base de données
• le principal intérêt des systèmes de bases de données, en tant que données centrales représentant l'espace du problème, est d'être impartial dans l'enregistrement des faits, de sorte que lorsque les exigences changent, il n'est pas nécessaire de reconcevoir les parties du système (données et applications) qui sont indépendantes en réalité. pour pouvoir le faire, les dépendances artificielles doivent être réduites au minimum - l'exigence "critique" d'aujourd'hui, qui consiste à accélérer une requête, n'a souvent qu'une importance marginale.

Analyse

Donc, j'ai fait une déclaration selon laquelle parfois rupture formes normales peut aider à la récupération. Il est temps de donner quelques arguments

1) Rupture de 1NF

Supposons que vous ayez des enregistrements financiers en 6NF. À partir de cette base de données, vous pouvez certainement obtenir un rapport sur le solde de chaque compte pour chaque mois.

En supposant qu'une requête qui aurait à calculer un tel rapport devrait passer par n vous pourriez créer un tableau

account_balances(month, report)

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

En même temps, en supposant qu'il soit possible de mettre à jour n'importe quel mois avec des insertions, des mises à jour ou des suppressions d'enregistrements financiers, la performance des requêtes de mise à jour sur le système pourrait être ralentie par un temps proportionnel à une certaine fonction de n para chaque actualisation . (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 entraîne des pénalités telles que pour un système réaliste qui met souvent à jour les données, vous obtiendrez de mauvaises performances même pour votre requête ciblée en fonction du type de charge de travail réelle ; je peux expliquer cela plus en détail si vous le souhaitez)

Note : Il s'agit en fait d'un exemple trivial qui pose un problème - la définition de 1NF. L'hypothèse selon laquelle le modèle ci-dessus rompt la 1NF est basée sur l'exigence que les valeurs d'un attribut ' contenir exactement une valeur du domaine applicable '.

Cela vous permet de dire que le domaine de l'attribut rapport est un ensemble de tous les rapports possibles et que parmi tous ces rapports, il y a exactement une valeur et de prétendre que la 1FN n'est pas rompue (similaire à l'argument selon lequel le stockage des mots ne rompt pas la 1FN même si vous pouvez avoir letters quelque part dans votre modèle).

D'un autre côté, il existe de bien meilleures façons de modéliser cette table, qui seraient plus utiles pour un plus grand nombre de requêtes (par exemple pour récupérer les soldes d'un seul compte pour tous les mois d'une année). Dans ce cas, vous justifieriez cette amélioration en disant que ce champ n'est pas en 1NF.

Quoi qu'il en soit, cela explique pourquoi les gens prétendent que le fait de briser les FN peut améliorer les performances.

2) Rupture de 3NF

En supposant que les tables soient en 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 données échantillons (1M de lignes dans t, 100k dans m)

Supposons une requête courante que vous voulez 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 pourriez trouver des suggestions pour déplacer l'attribut name dans la table m qui casse la 3NF (elle 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;

en cours d'exécution

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 coupé en deux mais

• le tableau n'était pas en 5NF/6NF pour commencer
• le test a été fait 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 la performance du système)
• la consommation d'espace est augmentée d'environ 9x la taille du nom de la colonne x 100k lignes
• il devrait y avoir des triggers sur t pour préserver l'intégrité des données, ce qui ralentirait considérablement toutes les mises à jour de nom et ajouterait des vérifications supplémentaires que les insertions dans t devraient subir
• de meilleurs résultats pourraient probablement être obtenus en abandonnant les clés de substitution et en passant à des clés naturelles, et/ou en indexant, ou en redéfinissant la conception en fonction de NF plus élevées.

La normalisation est la bonne solution à long terme. Mais vous n'avez pas toujours la possibilité de reconcevoir l'ERP de l'entreprise (qui, par exemple, n'est déjà plus qu'en grande partie 3NF) - parfois, vous devez accomplir une certaine tâche avec des ressources données. Bien entendu, il ne s'agit là que d'une "solution" à court terme.

Ligne de fond

Je pense que la réponse la plus pertinente à votre question est que vous trouverez l'industrie et l'enseignement utilisant le terme "dénormalisation" dans les domaines suivants

• au sens strict, pour briser les FN
• en vrac, pour l'introduction de toute dépendance d'insertion, de mise à jour et de suppression (citation originale de Codd commentaires sur la normalisation en disant : indésirable ( !) dépendances d'insertion, de mise à jour et de suppression", voir quelques détails aquí )

Ainsi, selon la définition stricte, l'agrégation (tableaux récapitulatifs) n'est pas considérée comme une dénormalisation et elle peut aider beaucoup en termes de performance (comme tout cache, qui n'est pas perçu comme une dénormalisation).

L'usage libre englobe les deux formes normales de rupture. y le site principe de la conception orthogonale comme dit précédemment.

Une autre chose qui pourrait nous éclairer est qu'il y a une différence très importante entre le modèle logique y le modèle physique .

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

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

Si vous ne parvenez pas à modéliser correctement votre modèle logique, vous vous retrouverez avec une base de données incohérente - mauvais types de relations entre vos entités (incapacité à représenter l'espace des problèmes), faits contradictoires (possibilité de perdre des informations). Vous devez employer toutes les méthodes possibles pour obtenir un modèle logique correct, il s'agit d'une base pour toutes les applications qui seront construites par-dessus.

La normalisation, la sémantique orthogonale et claire de vos prédicats, les attributs bien définis, les dépendances fonctionnelles correctement identifiées sont autant de facteurs qui permettent d'éviter les pièges.

Lorsqu'il s'agit de l'implémentation physique, les choses deviennent plus souples dans le sens où une colonne calculée matérialisée qui dépend d'une non-clé peut briser la 3NF, mais s'il existe des mécanismes qui garantissent la cohérence, elle est autorisée dans le modèle physique de la même manière que les index, mais vous devez très soigneusement le justifier parce que, généralement, la normalisation permet d'obtenir des améliorations identiques ou supérieures dans tous les domaines, n'a pas ou peu d'impact négatif et permet de conserver une conception claire (ce qui réduit les coûts de développement et de maintenance de l'application), ce qui permet de réaliser des économies que vous pouvez facilement consacrer à la mise à niveau du matériel pour améliorer la vitesse encore plus que ce qui est obtenu en cassant les NF.