Course All lectures for FR purposes - Lecture: Dénormalisation des tables dans la base de données

8.1 Pourquoi la dénormalisation est-elle nécessaire ?

L'opération la plus coûteuse en calcul entre de grandes tables est la jointure. En conséquence, si dans une requête, il est nécessaire de "ventiler" plusieurs tables composées de plusieurs millions de lignes, le SGBD consacrera beaucoup de temps à ce traitement.

L'utilisateur peut alors s'éloigner pour boire du café. L'interactivité du traitement disparaît pratiquement et se rapproche de celle du traitement par lots. Pire encore, en mode batch, l'utilisateur reçoit toutes les données demandées la veille au matin et travaille sereinement avec elles, préparant de nouvelles requêtes pour le soir.

Pour éviter la situation de jointures lourdes, les tables sont dénormalisées. Mais pas n'importe comment. Certaines règles vous permettent de considérer les tables dénormalisées de manière transactionnelle comme "normalisées" conformément aux règles de construction des tables pour les entrepôts de données.

Il existe deux schémas principaux considérés comme « normaux » dans le traitement analytique : « flocon de neige » et « étoile ». Les noms reflètent bien l'essence et découlent directement de l'image des tables associées.

Dans les deux cas, les soi-disant tables de faits sont l'élément central du schéma, contenant les événements, les transactions, les documents et d'autres choses intéressantes qui intéressent l'analyste. Mais si dans une base de données transactionnelle un document est « étalé » sur plusieurs tables (au moins deux : en-têtes et lignes de contenu), alors dans la table de faits un document, plus précisément chacune de ses lignes ou un ensemble de lignes groupées, correspond à un enregistrement.

Cela peut être fait en dénormalisant les deux tables ci-dessus.

8.2 Exemple de dénormalisation

Vous pouvez maintenant évaluer à quel point il sera plus facile pour le SGBD d'exécuter une requête, par exemple du type suivant : pour déterminer le volume des ventes de farine aux clients de Pirozhki LLC et Vatrushki CJSC pour la période.

Dans une base de données transactionnelle normalisée :


SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

Dans la base de données analytique :


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Au lieu d'une jointure lourde entre deux tables de documents et leur composition avec des millions de lignes, le SGBD travaille directement avec la table de faits et des jointures légères avec de petites tables auxiliaires, dont vous pouvez également vous passer, en connaissant les identifiants.


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Revenons aux schémas "étoile" et "flocon de neige". Dans les coulisses de la première photo se trouvaient des tables de clients, leurs groupes, des magasins, des vendeurs et, en fait, des marchandises. Lorsqu'elles sont dénormalisées, ces tables, appelées dimensions, sont également jointes à la table de faits. Si la table de faits fait référence à des tables de dimension qui ont des liens vers d'autres dimensions (dimensions du deuxième niveau et au-dessus), alors un tel schéma est appelé un "flocon de neige".

Comme vous pouvez le constater, pour les requêtes incluant un filtrage par groupes de clients, vous devez établir une connexion supplémentaire.


SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

Dans ce cas, la dénormalisation peut continuer et déposer la dimension de second niveau sur la première, ce qui facilite l'interrogation de la table de faits.

Un schéma dans lequel une table de faits ne fait référence qu'à des dimensions qui n'ont pas de second niveau est appelé schéma en étoile. Le nombre de tables de mesures correspond au nombre de "rayons" dans l'étoile.

Le schéma en étoile élimine complètement la hiérarchie des dimensions et la nécessité de joindre les tables correspondantes en une seule requête.


SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

L'inconvénient de la dénormalisation est toujours la redondance , qui entraîne une augmentation de la taille de la base de données dans les applications transactionnelles et analytiques. Calculons un delta approximatif sur l'exemple ci-dessus de conversion de "flocon de neige" en "étoile".

Dans certains SGBD, comme Oracle, il n'y a pas de types entiers spéciaux au niveau des définitions de schéma de base de données, vous devez utiliser le type booléen générique numeric(N), où N est le nombre de bits stockés. La taille de stockage d'un tel nombre est calculée à l'aide d'une formule spéciale donnée dans la documentation pour le stockage de données physiques et, en règle générale, elle dépasse celle des types de bas niveau comme "entier 16 bits" de 1 à 3 octets.

Supposons que la table des ventes n'utilise pas la compression des données et contienne environ 500 millions de lignes, et que le nombre de groupes de clients soit d'environ 1000. Dans ce cas, nous pouvons utiliser un entier court (shortint, smallint) occupant 2 octets comme type d'identifiant id_customer_group.

Nous supposerons que notre SGBD prend en charge un type entier à deux octets (par exemple, PostgreSQL, SQL Server, Sybase et autres). Ensuite, l'ajout de la colonne correspondante id_customer_groupà la table des ventes augmentera sa taille d'au moins 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Quand la dénormalisation est-elle nécessaire ?

Examinons quelques situations courantes où la dénormalisation peut être utile.

Grand nombre de jointures de table

Dans les requêtes adressées à une base de données entièrement normalisée, vous devez souvent joindre jusqu'à une douzaine de tables, voire plus. Et chaque connexion est une opération très gourmande en ressources. Par conséquent, ces requêtes consomment des ressources serveur et sont exécutées lentement.

Dans une telle situation, cela peut aider:

dénormalisation en réduisant le nombre de tables. Il est préférable de combiner en un seul plusieurs tableaux de petite taille, contenant des informations rarement modifiées (comme on dit souvent, conditionnellement constantes ou de référence) et des informations étroitement liées par leur sens.
En général, si vous devez joindre plus de cinq ou six tables dans un grand nombre de requêtes, vous devez envisager de dénormaliser la base de données.
Dénormalisation en ajoutant un champ supplémentaire à l'une des tables. Dans ce cas, la redondance des données apparaît, des actions supplémentaires sont nécessaires pour maintenir l'intégrité de la base de données.

Valeurs estimées

Souvent, les requêtes sont lentes et consomment beaucoup de ressources, dans lesquelles certains calculs complexes sont effectués, en particulier lors de l'utilisation de regroupements et de fonctions d'agrégation (Sum, Max, etc.). Parfois, il est logique d'ajouter 1 à 2 colonnes supplémentaires au tableau contenant des données calculées fréquemment utilisées (et difficiles à calculer).

Supposons que vous souhaitiez déterminer le coût total de chaque commande. Pour ce faire, vous devez d'abord déterminer le coût de chaque produit (selon la formule "nombre d'unités de produit" * "prix unitaire du produit" - remise). Après cela, vous devez regrouper les coûts par commandes.

L'exécution de cette requête est assez complexe et, si la base de données stocke des informations sur un grand nombre de commandes, peut prendre beaucoup de temps. Au lieu d'exécuter une telle requête, vous pouvez déterminer son coût au moment de passer une commande et le stocker dans une colonne séparée du tableau des commandes. Dans ce cas, pour obtenir le résultat souhaité, il suffit d'extraire les valeurs pré-calculées de cette colonne.

La création d'une colonne contenant des valeurs précalculées permet de gagner beaucoup de temps lors de l'exécution d'une requête, mais vous oblige à mettre à jour les données de cette colonne en temps opportun.

bord long

Si nous avons de grandes tables dans la base de données qui contiennent de longs champs (Blob, Long, etc.), nous pouvons sérieusement accélérer l'exécution des requêtes vers une telle table si nous déplaçons les longs champs vers une table séparée. Nous voulons, par exemple, créer un catalogue de photos dans la base de données, y compris stocker les photos elles-mêmes dans des champs blob (qualité professionnelle, haute résolution et taille appropriée). Du point de vue de la normalisation, la structure de table suivante serait tout à fait correcte :

photo d'identité
Identifiant de l'auteur
ID de modèle de caméra
la photo elle-même (champ blob)

Et maintenant, imaginons combien de temps durera la requête, en comptant le nombre de photos prises par n'importe quel auteur ...

La solution correcte (bien qu'en violation des principes de normalisation) dans une telle situation serait de créer une autre table composée de seulement deux champs - l'ID photo et un champ blob avec la photo elle-même. Ensuite, les sélections de la table principale (dans laquelle il n'y a plus un énorme champ blob) iront instantanément, mais quand on veut voir la photo elle-même, eh bien, attendons ...

Comment déterminer quand la dénormalisation est justifiée ?

8.4 Avantages et inconvénients de la dénormalisation

Une façon de déterminer si certaines mesures sont justifiées est de procéder à une analyse en termes de coûts et de bénéfices possibles. Combien coûtera un modèle de données dénormalisé ?

Déterminer les exigences (ce que nous voulons réaliser) → déterminer les exigences en matière de données (ce que nous devons suivre) → trouver l'étape minimale qui satisfait à ces exigences → calculer les coûts de mise en œuvre → mettre en œuvre.

Les coûts comprennent des aspects physiques tels que l'espace disque, les ressources nécessaires pour gérer cette structure et les opportunités perdues en raison des délais associés à la maintenance de ce processus. Vous devez payer pour la dénormalisation. Une base de données dénormalisée augmente la redondance des données, ce qui peut améliorer les performances mais nécessite plus d'efforts pour contrôler les données associées. Le processus de création d'applications deviendra plus difficile, car les données seront répétées et plus difficiles à suivre. De plus, la mise en œuvre de l'intégrité référentielle n'est pas facile - les données associées sont divisées en différentes tables.

Les avantages incluent des performances de requête plus rapides et la possibilité d'obtenir une réponse plus rapide. Vous pouvez également profiter d'autres avantages, notamment une augmentation du débit, de la satisfaction client et de la productivité, ainsi qu'une utilisation plus efficace des outils de développement externes.

Taux de demandes et cohérence des performances

Par exemple, 72 % des 1 000 requêtes générées quotidiennement par une entreprise sont des requêtes de niveau récapitulatif, et non des requêtes détaillées. Lors de l'utilisation d'un tableau récapitulatif, les requêtes s'exécutent en environ 6 secondes au lieu de 4 minutes, ce qui réduit de 3 000 minutes le temps de traitement. Même après ajustement pour les 100 minutes qui doivent être consacrées à la maintenance des tableaux croisés dynamiques chaque semaine, cela fait gagner 2 500 minutes par semaine, ce qui justifie la création du tableau croisé dynamique. Au fil du temps, il peut arriver que la plupart des requêtes ne portent pas sur des données sommaires, mais sur des données détaillées. Moins il y a de requêtes utilisant la table récapitulative, plus il est facile de la supprimer sans affecter les autres processus.

Et…

Les critères énumérés ci-dessus ne sont pas les seuls à prendre en compte pour décider de passer à l'étape suivante de l'optimisation. D'autres facteurs doivent être pris en compte, notamment les priorités commerciales et les besoins des utilisateurs finaux. Les utilisateurs doivent comprendre comment, d'un point de vue technique, l'architecture du système est affectée par l'exigence des utilisateurs qui souhaitent que toutes les demandes soient traitées en quelques secondes. Le moyen le plus simple de parvenir à cette compréhension est de décrire les coûts associés à la création et à la gestion de telles tables.

8.5 Comment mettre en œuvre avec compétence la dénormalisation.

Enregistrer des tableaux détaillés

Afin de ne pas limiter les capacités de la base de données qui sont importantes pour l'entreprise, il est nécessaire d'adopter une stratégie de coexistence et non de remplacement, c'est-à-dire de conserver des tableaux détaillés pour une analyse approfondie, en leur ajoutant des structures dénormalisées. Par exemple, le compteur de coups. Pour les entreprises, vous devez connaître le nombre de visites sur une page Web. Mais pour l'analyse (par période, par pays...) nous aurons très probablement besoin de données détaillées - un tableau avec des informations sur chaque visite.

Utiliser des déclencheurs

Il est possible de dénormaliser une structure de base de données tout en bénéficiant des avantages de la normalisation en utilisant des déclencheurs de base de données pour préserver l' integrityintégrité des données en double.

Par exemple, lors de l'ajout d'un champ calculé, chacune des colonnes dont dépend le champ calculé est accrochée à un déclencheur qui appelle une seule procédure stockée (c'est important !), qui écrit les données nécessaires dans le champ calculé. Il est seulement nécessaire de ne sauter aucune des colonnes dont dépend le champ calculé.

Assistance logicielle

Si vous n'utilisez pas de déclencheurs intégrés et de procédures stockées, les développeurs d'applications doivent veiller à assurer la cohérence des données dans une base de données dénormalisée.

Par analogie avec les déclencheurs, il devrait y avoir une fonction qui met à jour tous les champs qui dépendent du champ en cours de modification.

conclusion

Lors de la dénormalisation, il est important de maintenir un équilibre entre l'augmentation de la vitesse de la base de données et l'augmentation du risque de données incohérentes, entre faciliter la vie des programmeurs qui écrivent Select-set compliquer la tâche de ceux qui fournissent la population de la base de données et les mises à jour des données. Il faut donc dénormaliser la base de données très soigneusement, très sélectivement, uniquement là où c'est indispensable.

S'il est impossible de calculer à l'avance les avantages et les inconvénients de la dénormalisation, il faut d'abord implémenter un modèle avec des tables normalisées, et ensuite seulement, pour optimiser les requêtes problématiques, procéder à la dénormalisation.

Il est important d'introduire la dénormalisation progressivement et uniquement dans les cas où il y a des extractions répétées de données associées à partir de différentes tables. N'oubliez pas que lors de la duplication de données, le nombre d'enregistrements augmentera, mais le nombre de lectures diminuera. Il est également pratique de stocker les données calculées dans des colonnes pour éviter les sélections agrégées inutiles.