La plupart des développeurs sont persuadés que mettre toutes les informations dans une même table rendra leur base de données plus rapide… Et l’on voit apparaître dans la base de nombreuses tables de plusieurs dizaines de colonnes. C’est une vue à court terme, car dès que la base de données commence à croitre ou que le nombre d’utilisateur augmente, les performances deviennent vite catastrophique… Cet article explique pourquoi…
À chaque audit que je fais, et cela depuis maintenant plus de 15 ans, on trouve régulièrement la même erreur : des tables avec un nombre effrayant de colonnes. Par exemple une table des personnes, dans laquelle on trouve des adresses, des téléphones, des mails, … bref tout un tas d’information relatif à une personne, mais qui ne constitue aucunement des attributs directs de la personne. Et chaque fois que l’on fait remarquer cela aux développeurs, la réponse est la même :
- « on m’a dit que les jointures c’était mal »
- « les jointures c’est pas performants, c’est connu »
- « Je mets tout dans la même table, ça ira plus vite »
…
Bien évidemment, c’est tout le contraire, mais pour le comprendre il faut revenir à ce qu’est une base de données relationnelle, comment fonctionne les SGBDR client/serveur et ce qu’est la normalisation d’un modèle de données, c’est à dire son découpage en de multiples tables, chacune étant bien spécialisé et concentré sur l’objet qu’elle modélise.
Types de données et forme normale
Le choix des types de données, la maîtrise de la qualité des données et le respect des formes normales représente, lorsqu’on ne les met pas en Å“uvre, plus de 70% des problèmes de performance. Mais là où c’est pervers, c’est que le développeur ne s’en rendra compte que trop tard, lorsque après plusieurs mois, voire plusieurs années de production, le volume de la base de données dépassant largement celui de la RAM du serveur, les performances deviennent catastrophiques…
J’ai vu plusieurs éditeurs en situation de quasi faillite après avoir vendu leur logiciel avec une base mal modélisée à leur premier gros client. Le scénario est généralement le suivant : après plusieurs semaines d’exploitation, les temps de réponses étant déjà mauvais, le client intime à l’éditeur de trouver une solution. Comme il n’y a pas de silver boulette (comme disent nos amis américains) et que tout redévelopper est généralement inenvisageable, on patch quelques petits éléments à droite à gauche pour gagner du temps… Au bout de quelques mois, le client s’impatiente et menace de ne pas payer… Et finalement c’est ce qu’il fait car rien ne vient !
Normaliser une base de données
La normalisation d’une base de données (c’est-à -dire le respect des règles de modélisation) n’est pas une figure de style. C’est, avant tout, une question de performance !
Par exemple le viol de la notion d’atomicité (première forme normale), c’est à dire mettre plusieurs informations dans une même colonne est une erreur aux conséquences lourdes. Elle conduira tôt ou tard à des tables obèses et des acrobaties pour écrire des requêtes qui ne pourront jamais être optimisées.
Je donne souvent les exemples suivants :
« modélisez moi une adresse IP, modélisez moi un n° de sécurité sociale » (bien entendu ces demandes sont « noyées » dans un exercice plus général de modélisation)….
La plupart du temps les développeurs modélisent une adresse IP sous la forme d’une chaine de caractères de longueur maximale 15.
Là je leur demande de saisir quelques adresses IP et je leur demande d’écrire la requête suivante :
« Trouvez-moi les ordinateurs afférent au masque de sous réseau de classe C »
Et là c’est la panique… Lorsque la requête est écrite (ce qui est rarement arrivée…) elle est catastrophiquement lente du fait de multiples fonctions. C’est à ce moment que le développeur me dit que cela aurait été mieux s’il avait modélisé cette adresse IP avec 4 entiers.
Parfait lui dis-je…
À ce moment-là , je lui demande de me dire comment traiter les adresses IP V6 !
Pour le numéro de sécurité sociale, c’est le même tabac. La plupart du temps les développeurs me mettent deux zones de saisie : une de 13 caractères et l’autre de 2 pour la clef.
Maintenant je leur demande de me dire comment ils vont contrôler la saisie sachant que le premier caractères doit être un 1 ou 2 , les 2 second de 00 à 99, les 2 d’après de 01 à 12, etc.
Puis je leur demande de m’écrire une requête pour trouver toutes les personnes du sexe masculin née dans les années 60 en région parisienne…
Bien entendu je leur montre la solution finale qui consiste à découper le n° de sécurité sociale en au moins 5 groupes : sexe, mois, année, commune et rang.
De la même façon je leur donne une table contenant des informations de personnes ou l’on trouve outre le nom et le prénom une adresse un mail et 3 zone de saisie de téléphones (fixe, fax et GSM), et là je leur demande de me retrouver le nom d’une personne dont le n° est 06 11 44 78 95…
Évidemment la table contient ce genre de choses :
TEL FAX GSM
---------------- ------------------ ------------------
01 47 58 42 23 +33 5 21 56 89 45 06 11 49 56 12
0645145874
01-22-44-25-36 04-87-56-23-21 09 48 41 25 14
0645184953
+33 6 52418552 06 12457825 06 78 41 56 23
06 11 22 55 87 01.22.44.12.52
Alors qu’il est si simple de créer une table des téléphones avec une seule colonne pour tous les numéros en les typant et en maitrisant la saisie.
Le non respect des formes normales, conduit systématiquement à décrochage des performances dès que le volume des données de la base dépasse la quantité de RAM.
L’application du processus de normalisation, conduit à un grand nombre de tables avec très peu de colonnes (en moyenne moins d’une dizaine) et de nombreuses jointures doivent être réalisées pour retrouver les données. Les jointures, tout particulièrement lorsqu’elles portent sur des « petites » clefs (comme un entier avec un auto incrément de type SEQUENCE ou IDENTITY) sont des processus d’une extrême rapidité (c’est l’opération la plus courante dans un SGBDR, donc la plus optimisée). Par exemple, avec deux tables de 100 millions de lignes, une jointure qui au final renvoie une ligne ne coutera que la lecture de 6 pages, ce qui est très peu. Et joindre 30 tables de 100 millions de lignes de ce type ne coute que 180 lectures, ce qui n’est rien pour un SGBDR qui généralement lit 8 pages d’un seul coup !
Différences entre des petites tables et une grosse table pour les opérations de mises à jour (écriture) :
- Dans une grosse table contenant un grand nombre de colonnes, chaque écriture (INSERT, UPDATE or DELETE) pose un verrou exclusif tant est si bien que personne d’autre ne peut l’utiliser.
- Dans un jeu de plusieurs petites tables représentant une seule et même grosse table, les opérations d’écriture se succéderons séquentiellement et tandis que l’un est mis a jour avec un verrou exclusive, les autres peuvent être utilisés en lecture comme en écriture.
Finalement plus d’utilisateurs peuvent travailler simultanément sans être victimes de temps d’attente importants dans une base constituée de nombreuses petites tables.
Différences entre des petites tables et une grosse table pour les opérations de lecture :
- Dans une table, qu’elle soit petite ou grande, une seule méthode doit être choisie pour accéder aux données :
- – balayage de toutes les lignes de la table;
- – balayage de toutes les lignes d’un index;
- – recherche dans un index;
- Une fois qu’une grande table a été morcelée en plusieurs petites tables, l’optimiseur peut choisir la meilleure méthode d’accès entre balayage et recherche pour chacune des tables de la requête, en préférant la recherche aussi souvent que possible.
- S’il existe plusieurs prédicats de recherches ou plusieurs conditions dans le prédicat, la seule manière d’opérer dans une grande table est de balayer toute la table.
Bien entendu les recherches sont incommensurablement plus rapides que les balayages parce que leur coût est logarithmique.
Finalement et malgré le coût des jointures, une requête avec plusieurs prédicats ou conditions s’exécutera beaucoup plus rapidement dès que le volume de données commence à peser d’un poids important. Et plus les requêtes « roulent » vite, plus un grand nombre d’utilisateurs peuvent utiliser simultanément le système…
Toutes les opérations relationnelles dans une base de données sont faites exclusivement en mémoire.
Aussi quelque soit la méthode d’accès aux données, toutes les données nécessaires à la requête doivent être placées en RAM avant d’être lues.
- Avec un balayage, toutes les lignes de la table doivent être placées en mémoire
- Avec une recherche, très peu de pages doivent être placées en mémoire, parce qu’un index est un arbre et qu’il suffit d’y placer la page racine, les pages de navigation et la page feuille contenant les données finales.
Bien entendu un balayage place un grand nombre de page en mémoire, tandis qu’une recherché en place très peu.
Indexation
Un moyen de réduire drastiquement les temps d’accès est d’indexer la table. Cependant, nous allons découvrir qu’indexer une table obèse génère trois problèmes supplémentaire :
– un accroissement important du volume globale de la base,
Рune forte diminution de la concurrence en lecture comme en ̩criture,
– une optimisation notablement moins efficace…
Étudions un cas théorique simplifié afin de comprendre pourquoi ces effets vont se faire sentir dans une table obèse.
Soit la table suivante :
CREATE TABLE Contact
(ID_personne INT PRIMARY KEY,
telephone CHAR(20),
email VARCHAR(256),
skype VARCHAR(128));
Nous supposons que les colonnes telephone, email et adresse ne sont pas toujours renseignée et dans ce cas une modélisation respectant à la lettre les principes, aurait conduit à séparer en 4 tables, ces données à savoir :
CREATE TABLE Contact
(ID_personne INT PRIMARY KEY)
CREATE TABLE Contact_TEL
(ID_personne INT PRIMARY KEY,
telephone CHAR(20) NOT NULL)
CREATE TABLE Contact_EML
(ID_personne INT PRIMARY KEY,
email VARCHAR(256));
CREATE TABLE Contact_SKP
(ID_personne INT PRIMARY KEY,
skype VARCHAR(128));
1) quelle est la version du modèle, présentant le plus fort volume ?
Si nous comptons les données des tables, la base 1 (monotabulaire) compte au plus 408 octets par ligne (le INT comptant pour 4 octets).
La base 2 fait :
TABLE Contact : 4
TABLE Contact_TEL : 24
TABLE Contact_MEL : 260
TABLE Contact_SKP : 132
Total base 2 : 420 octets.
La différence est minime et s’il y a des NULL, leur absence de stockage dans la base 2 penchera en faveur de cette solution… Mais raisonnons par l’absurde et considérons un taux de NULL de zéro…
Ajoutons maintenant les index… À nouveau, considérons l’absurde et tentons d’indexer les 2 bases pour optimiser toutes les requêtes possibles.
Dans la base monotable, les index à poser, pour optimiser tous les cas de requêtes, sont les suivants :
telephone email skype CLEF D'INDEX
------------- --------- ------- ----------------------------------------------
X1 1 (telephone)
X2 1 (email)
X3 1 (skype)
X4 1 2 (telephone, email)
X5 1 2 (telephone, skype)
X6 1 2 (email, skype)
X7 1 2 3 (telephone, email, skype)
Cependant ces index ne suffisent pas à couvrir tous les cas de figure. En effet, les informations d’un index étant vectorisées (tri de la première colonne de le clef d’index, puis tri relatif de la seconde, pour les données identiques dans la première) certaines requêtes ne peuvent bénéficier des index ci dessus.
Par exemple la clause WHERE suivante :
WHERE telephone = '0123456789' AND email LIKE 'dupont%'
pourra pleinement utiliser l’index X4, tandis que cette nouvelle clause WHERE ne le peut :
WHERE telephone = '012345%' AND email = 'dupont@ibm.com'
Il faut des index complémentaires avec des clefs inversées !
telephone email skype CLEF D'INDEX
------------- --------- ------- ----------------------------------------------
X8 2 1 (email, telephone)
X9 2 1 (skype, telephone)
X10 2 1 (skype, email)
X11 1 3 2 (telephone, skype, email)
X12 2 1 3 (email, telephone, skype)
X13 2 3 1 (skype, telephone, email)
X14 3 1 2 (email, skype, telephone)
X15 3 2 1 (skype, email, telephone)
Le volume théorique des index est alors le suivant :
X1 : 20
X2 : 256
X3 : 128
X4 : 276
X5 : 148
X6 : 384
X7 : 404
X8 : 276
X9 : 148
X10 : 384
X11 : 404
X12 : 404
X13 : 404
X14 : 404
X15 : 404
TOTAL indexation base monotable : 4 444 octets (+ 404 octets de ligne).
Indexation base multi tabulaire :
X1 sur table Contact_TEL (telephone) =: 20
X2 sur table Contact_MEL (email) =: 256
X3 sur table Contact_SKP (skype) =: 128
TOTA indexation base multitable : 404 (+ 420 octets)
Différence de volume des deux bases : 4848 / 824 = 5,88. Autrement dit la base monotabulaire serait près de 6 fois plus grosse que la base multitabulaire !
Comme il y a moins de chance que le plan soit optimal (voir ci après), cela conduit à des balayages de tables ou d’index plus fréquent, ce qui prends plus de place en cache… Au détriment d’autres données (le cache n’étant pas élastique ! – car c’est la RAM…)
En ce qui concerne les mises à jour et compte tenu des verrous à poser, dans la base monotabulaire, toute mise à jour d’une seul information, bloque le ligne de la table. Plus personne ne peut accéder aux autres rubriques. Dans la base multitabulaire, la mise à jour d’une information ne bloque pas les autres rubrique pour la même personne. La base multitabulaire est donc 3 fois plus fluide statistiquement que la base monotabulaire…
Confronté à un choix d’index, l’optimiseur doit décider lequel prendre. Plus il y en a, et plus le travail est complexe, ardu et prends du temps. Comme les optimiseurs ont un temps impartis pour calculer le meilleur plan il est possible que, devant la combinatoire, le temps impartit soit écoulé avant que le plan optimal soit trouvé… Conséquence, plus il y a d’index en général et sur une même table en particulier dans toute la base, et plus il y a de chance que le plan de requête soit finalement moins bon !
Enfin, pour les SGBDR qui savent travailler en parallèle, l’accès aux données sur plusieurs tables et souvent plus rapide, car fait en parallèle,que sur une seule table plus grosse plus longue, plus grande !!!
Autre argument : la compacité des données
Un autre argument peut être invoqué pour faire des petites tables….
Les SGBDR stockent leur données dans des pages qui font une certaine taille. Pour SQL Server comme pour PostGreSQL, cette taille est de 8 Ko.
Le principe est qu’une ligne d’une table doit impérativement tenir intégralement dans une page. Il n’est donc pas possible de dépasser une certains taille de ligne qui est de 8060 octets dans SQL Server et 8136 dans PostGreSQL. Si vous construisez des tables avec de nombreuses colonnes il est probable que vous vous rapprocherez vite de la limite. Mais en étant un peu au dessus de la moitié de cette taille des problèmes irréfragables commencent. En effet si vous construisez une table avec une taille de ligne moyenne de 4500 octets, alors vous n’aurez jamais plus d’une ligne par page et perdrez par conséquent plus de 3500 octets qui seront inutilisés. De plus votre table sera vu comme fragmentée de plus de 40% et toutes tentative de défragmentation sera vaine. Enfin, vous augmentez artificiellement le volume globale de votre table de plus de 40% ! Bref, là encore les performances vont chuter….
Conclusion :
Voici un graphique montrant le temps de réponse en fonction de l’augmentation du volume de la base.
Performance d’une base de données avec des tables obèses
Performance d’une base de données avec des tables obèses
On y voit clairement que tant que le volume des données est faible, l’avantage est à la supertable. Cependant dès que le volume des données devient important, alors la base normalisées, constitué de petites tables prend l’avantage, et plus la base grossira, plus l’écart sera important.
Dans une base de données relationnelle bien modélisée on trouve de très nombreuses petites tables dépassant rarement la dizaine de colonnes (hors clef étrangères). Les performances restent stables quelque soit le volume des données, car l’optimiseur trouvera toujours un index dont le but est faire passer le coût d’accès de linéaire à logarithmique.
Dans une base ayant des tables obèses, l’accès aux données se fait essentiellement par balayage. Les index sont rarement utilisés. Tant que les tables peuvent être mise en cache dans la RAM, les temps d’accès semblent corrects. Dès que la volumétrie des tables dépasse la quantité de RAM, les performances chutent drastiquement et les temps de réponse s’envolent.
Dans une base mal modélisée, le résultat est une consommation anormale de RAM du fait de la structure de la table et de nombreux balayage (peu d’utilisation d’index). Dans un tel cas, la solution consiste à avoir une RAM sur le serveur égal à la taille de la base ou bien de restructurer la base !
Le problème est que restructurer la base nécessite une refonte complète du développement, si les développeurs n’ont pas prévu au départ d’utiliser des vues…
Bref, pour ne pas avoir respecter l’art de la modélisation des données, les performances deviendront tôt ou tard un cauchemar !
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Bonjour,
J’ai lu avec grand intérêt cet article.
Je fais un site qui permet d’éditer des cours en ligne, et permet aux élèves de consulter ces cours.
Un cours est structuré en séquence, séance puis activité. Chacun de ces éléments peut avoir un diaporama, un qcm, etc. Et tous ces éléments, disposant d’une table différente selon leur type, sont inclus les uns dans les autres selon une arborescence limitée (on peut mettre une image dans un diaporama mais pas le contraire !) :
séquence 2 enfants
-> séance 1 2 enfants
-> activité 1.1 1 enfant
-> diaporama 3 enfants
-> photo 1
-> photo 2
-> photo 3
-> activité 1.2 1 enfant
-> exercice 3 enfants
-> qcm 1
-> qcm 2
-> qcm 3
-> fichier PDF
-> séance 2 1 enfant
-> activité 2.1
De plus, je ne recherche jamais (ou presque) un enregistrement par des critères divers mais uniquement par son type, qui renvoit à une table, et son identifiant qui sont tout deux connus.
Inversement, je ne connais jamais ni le nombre ni le type des enfants d’un élément. Le bon sens peut limiter à un maximum de 20 photos par diaporama, et à 8 enfants pour les autres éléments.
Pour chaque élément pouvant avoir des enfants, une colonne enfs code le type d’enfant et son identifiant dans une chaine du type B1324 B3443 C3423 I43554 etc,
1 lettre pour définir la table de l’enfant, 8 chiffres pour l’ identifiant et un séparateur entre chaque enfant.
Ceci correspond à la version 1 de la BDD.
Pour la version 2 de la BDD, j’ai fais avec une table de liaison mais alors, il me fallait préciser l’ordre des éléments enfant dans son parent (version 2) :
type_parent id_parent type_enfant id_enfant ordre
C(sequence) 23345 B(séance) 1324 1
C(sequence) 23345 B(séance) 3443 2
C(sequence) 23345 C(séance) 3423 3
C(sequence) 23345 I(séance) 43554 4
Et chacun des éléments en ligne ci-dessus pouvait à son tour avoir d’autres enfants… Et définitive, cette table peut vite compter des milliards de ligne et devrait être lue 12 fois pour l’arborescence présentée ci-dessus.
J’ai fait un test des deux structures de base de donné avec 200 000 lignes parent ou enfants et une table de liaison de 400 000 lignes.
La version 1 était presque deux fois plus rapide que la version 2 sur serveur en ligne.
Je précise aussi qu’un seul utilisateur (le prof) peut modifier un enregistrement et qu’au maximum, 400 élèves consulteront un même cours.
Cette base est donc particulière car certains éléments comme le contenu du cours exige une colonne de 4000 octets et qu’avec 10000 profs, il peut y avoir 10 millions de lignes avec ce type de contenu.
Mon inquiétude est dans le risque de lenteur pour un prof ou un élève.
Pour y pallier, le serveur n’envoie hormis le texte du contenu, que des informations codées avec un minimum de html, le javascript s’occupe de l’affichage.
Avez-vous un avis sur cette base et si cela est mauvais, des pistes d’amélioration ?
Cordialement
Bonjour à vous,
J’ai lu avec grande attention ce que vous avez écrit car c’est précisément ma problématique du moment.
En effet, j’envisage un portage de la base que je développe sous PostgreSQL en partant du SGBD 4D. Hors, j’ai développé la base avec d’autres personnes et il a fallu dans le passé, arbitrer une redondance de clés étrangères, selon le même principe que ce que j’ai vu plus bas :
Si on va de t1 à t5, autant avoir une clé étrangère vers t5 dans t1 pour aller plus vite (au lieu de t1->t2->t3->t4->t5).
Deux mauvais arguments pour ça :
– c’est plus simple en support
– ce sera plus rapide
Maintenant, avec PostgreSQL, je m’imagine affranchi de ce problème. Surtout quand je lis « jointure 30 tables, 10^8 données : 1ms ».
Mais dans l’exemple que vous donnez, c’est 10^8 données dans chacune des 30 tables ?
Autre point non évoqué ici mais très en rapport avec ce que vous écrivez :
– j’ai eu maille à partir avec un « cador » chez l’éditeur 4D, pour qui le futur ne se dessine qu’à l’aide de clés primaires GUID
– j’ai défendu mon point de vue qui est l’utilisation d’INT, puis vu votre intervention ici : http://www.developpez.net/forums/d717802/bases-donnees/langage-sql/id-auto-incremente-guid/
Ma question sur ce point : avez vous changez de point de vue où est-ce pour vous toujours une hérésie de n’utiliser que des clés primaires GUID. Je pense que la réponse est « oui, c’est une hérésie ». Mais comme j’ai été mis à rude épreuve et ramené au rang de personne ne savant pas vivre à son époque, je préfère faire preuve de doute et me remettre en cause. Ceci étant dit, nul doute que les 30 jointures en question iraient moins vites….
Merci d’avance pour vos lumières !
Intéressant article, on vous remercie pour votre effort.
Sinon j’ai un gros souci sur base de données disant qu’elle contient 4 tables, y’a une seule table qui contient actuellement 20 millions de lignes et elle contiendra beaucoup plus que ça, le temps de réponse actuel d’une requête de recherche est de 3min.
La requete est :
SELECT (CASE WHEN (SELECT COUNT(j.JobID) FROM job j WHERE j.ParentJobID=jb.JobID )=0 THEN 0 ELSE 1 END) as ‘ExitSousJob’,jb.JobID as ‘JobID’ ,
jb.JobID as ‘DownFileIn’ , jb.JobID as ‘DownFileOut’,jb.JobID as ‘DownFileAll’ , jb.ParentJobID as ‘JobPereID’ , jb.StartDate as ‘DateDebut’ ,
(CASE WHEN jb.Duration IS NULL THEN ‘-‘ ELSE (CASE WHEN jb.Duration 0 THEN jb.Duration/1000 ELSE jb.Duration END) END ) as ‘Duree’ ,
Lab.Fr as ‘Statut’ ,
(CASE WHEN jbt.LabelID IS NULL THEN jbt.Alias ELSE (SELECT Fr FROM label l WHERE l.LabelID= jbt.LabelID) END) as ‘Type’ ,
(CASE WHEN jb.Description IS NULL THEN ‘-‘ ELSE jb.Description END ) as ‘Description’,
st.StatusTypeID as ‘IdStat’
FROM Job jb
INNER JOIN jobtype jbt on jb.JobTypeID=jbt.JobTypeID
INNER JOIN statustype st on st.StatusTypeID=jb.StatusTypeID
LEFT JOIN label lab on lab.LabelID = st.LabelID
WHERE jb.EnvID IN ( 1,40,41,22,37,25,26,39,38,42,43 ) AND jb.JobTypeID = 1 AND jb.StatusTypeID = 0
ORDER BY StartDate DESC LIMIT 500
Structure de la table Job : CREATE TABLE IF NOT EXISTS `job` (
`JobID` int(10) unsigned NOT NULL,
`StartDate` datetime NOT NULL,
`EndDate` datetime DEFAULT NULL,
`Duration` int(10) unsigned DEFAULT NULL,
`ParentJobID` int(10) unsigned DEFAULT NULL,
`SessionJobID` int(10) unsigned DEFAULT NULL,
`JobTypeID` smallint(5) unsigned NOT NULL,
`StatusTypeID` mediumint(8) unsigned NOT NULL,
`Visible` tinyint(1) NOT NULL,
`EnvID` int(5) unsigned NOT NULL,
`Description` text,
PRIMARY KEY (`JobID`),
KEY `ParentJobID` (`ParentJobID`),
KEY `SessionJobID` (`SessionJobID`),
KEY `JobTypeID` (`JobTypeID`),
KEY `EnvID` (`EnvID`),
KEY `StatusTypeID` (`StatusTypeID`),
KEY `StartDate` (`StartDate`),
KEY `Duration` (`Duration`)
)
Merci d’avance.
Cet article est très intéressant et me donne envie de réfléchir sur l’architecture actuelle de notre base de donnée.
Nous enregistrons 1000 valeurs tous les X secondes et notre table ressemble à cela.
Date | Valeur1 | Valeur2 | Valeur3 … Valeur1000
X | 5.3666 | 4.1223 | 7.8888 … 5.757
X+1 | 5.3666 | 4.1223 | 7.8888 … 5.757
X+2 | 5.3666 | 4.1223 | 7.8888 … 5.757
Quelle pourrait être la forme normal de cette table dans une base de donnée relationnelle ?
Nous avons un problème avec le nombre max de colonne qui est limité à ce que peut faire SQL Server (1024) et nous aimerions améliorer les performances d’écriture.
Nos tables contiennent plusieurs millions de points (en moyenne 1 ligne par secondes donc au bout d’un an ça fait 31.536.000 de lignes de 1000 valeurs).
Auriez vous une piste d’amélioration ?
Bonjour,
Votre table devrait ressembler
(
ChampDate datetime NOT NULL, // conserve la valeur de datetime
Numvaleur int NOT NULL, // conserve le numéro de valeur (index de ta colonne)
Valeur float(53) NOT NULL // conserve la valeur
) ON [PRIMARY]
(j’ai éludé la problématique des indexs)
tu pourras facilement faire des opérations de sommes, de moyennes et autres stats à vitesse grand V sur n’importe quelle plage de temps.
ta table ne fera pas 31Millions de ligne mais 31 milliards (bon courage!) => peut être penser à optimiser les types de valeurs car le moindre octet de gagné entraînera 31Go de data sur le disque en moins).
tu ne seras plus limité par le nombre de valeur par ligne
Ajouter 1000 lignes par seconde ne devrait pas être un soucis (si c’est un soucis, contactez moi)
Pour info, nous avons une table de 2 milliards de points de mesure ainsi formée pour le concentrateur de données de surveillance des crues du grand delta du rhône. Cela ne pose aucun problème de performances (et même avec un serveur n’ayant que 4 Go de ram !!!), d’autant que c’est associé à des vues indexées.
A +
Ping : La normalisation | Module base de données à l'ICOF – BTS SIO
Bonjour,
J’aurais une question concernant l’atomicité (enfin je crois que c’est à cela que ça se rapporte).
Dans le cadre d’une base de donnée servant à gérer les privilèges d’accès des utilisateurs à certaines applications propre à l’entreprise, il existerait la table suivante :
CREATE TABLE DBO.tbl_UM_Demand_UMD(
ID INT IDENTITY(1,1) NOT NULL,
USER_ID INT NOT NULL,
APP_ID INT NOT NULL,
RIGHT_ID INT NOT NULL,
ASK_DATE DATETIME NOT NULL CONSTRAINT DF_tbl_UM_Demande_UMD_ASK_DATE DAFAULT (getdate()),
VALIDATED BIT NOT NULL CONSTRAINT DF_tbl_UM_Demande_UMD_VALIDATED DAFAULT (‘false’),
VALIDATION_DATE DATETIME NULL
)
Dans cette table, les colonnes USER_ID, APP_ID et RIGHT_ID sont bien sûr des clefs étrangères vers les tables adéquates.
Ma question porte sur les colonnes VALIDATED et VALIDATION_DATE.
À partir du moment où une date de validation est présente, on peut en déduire à 100% que la demande est bien validée. De ce fait, la colonne VALIDATED est-elle nécessaire ? Est-ce une « grave » faute de conception ou cela peut-il être envisagé dans certains cas ?
Griftou.
Ping : Optimiser les tables de sa base de données | liens-geeks.com
Eric190 : Merci pour la mention du travail fait par la DSI de la Préfecture de Police (mon nouveau service :-))…
SQLpro : Merci à la PP pour utiliser quelques uns des mes algo comme PHONEX… Mais pour eux la marchandise est humaine !
Super billet, comme à ton habitude… continue comme ça.
Merci pour la mention du travail fait par la DSI de la Préfecture de Police (mon nouveau service :-))…
@+
Eric POMMEREAU
ec : l’atomicité … […] doit être adaptée aux objectifs de l’application
SQLpro : c’est parfaitement exact, mais cela n’empêche pas de le faire un minimum. Votre exemple est celui que je prend pour mes cours dans les écoles d’ingénieurs. Et notamment c’est très important pour les sites web marchand, car il existe des entreprises qui valident les adresses à condition que l’on ait dissocié le type de voie, le nom de la voie et le n°.
Récemment dans un colloque sur les SIG dans PostGreSQL, la Préfecture de Police montrait une application de validation d’adresse basé sur cette même atomisation. le but de la validation des adresses est d’éviter des retours de marchandise très couteux. Les premiers sites Web marchand en ont fait les frais et certains ont dû déposer le bilan à cause d’un taux de retour trop élevé, mangeant la marge (affaire Père Noel .com par exemple).
ec : Enfin la dé-normalisation, si elle est parfois plus pratique, est facile à obtenir par des requêtes qui rassemblent les informations de plusieurs tables parfaitement normalisées
SQLpro : c’est le but des vues !
seb : Faire une jointure est toujours plus lent que de mettre les données dans la même table.
SQLpro : visiblement vous n’avez rien compris…. Une jointure n’est pas couteuse lorsqu’elle est faites sur de bonnes clefs (colonne unique indexées de type entier de part et d’autre du prédicats de jointure) car le SGBDR fait un tri fusion (MERGE). Bref, pour joindre deux tables de 100 millions de lignes et retrouver une ligne il faut lire 3 pages de part et d’autre… C’est presque rien.
Alors que dans votre grosse table comme il faudra sans arrêt la mettre à jour elle sera verrouillé de manière exclusive bien plus fréquemment que si vous aviez fait de multiples tables, ce qui empêchera la montée en charge concurrentielle et par conséquent ralentira globalement le service des données de manière rapide.
En gros, avec une bonne normalisation, le temps de réponse lors de la montée en charge est linéaire, tandis que que sans elle est exponentielle.
Bien entendu vous avez finalement raison, de dire que « faire une jointure est toujours plus lent que de mettre les données dans la même table » s’il n’y a jamais qu’un seul utilisateur dans votre base !
Et avec un tel raisonnement à courte vue, pourquoi ne pas faire une seule table dans la base ? Ceal ira encore plus vite… non ?
Voici un bel article théorique sans expérience …
La normalisation d’une base de données est liée aux besoins et non une doctrine.
Faire une jointure est toujours plus lent que de mettre les données dans la même table.
Les SGBD gèrent facilement des gigas de données alors rien ne sert d’éviter les duplications de données.
Les benchs globaux sont idiots : on utilise pas toutes les requêtes de la même manière.
Normaliser une BD est lié au besoin fonctionnel : quelles filtres devrons nous mettre en place ?
Excellent article !
Mais… si l’atomicité est un excellent principe de conception… elle doit être adaptée aux objectifs de l’application.
Exemple : la plupart du temps on met le N° de rue, le type de rue et le nom de rue dans une seul champ « xyzAdresseRue ». Et cela ne pose généralement aucun problème.
Mais si un organisme a recours à ses propres porteurs pour distribuer des courriers, il est nécessaire de pouvoir trier les nom de rues et donc d’isoler dans un champ ces noms de rues et de mettre dans d’autres champs les numéros et les type de rues. Il peut même être utile de regrouper les rues d’un même quartier et donc d’ajouter ce type de précision dans un champs de la table.
L’exigence d’atomicité est donc relative aux besoins de l’organisme.
Il est clair qu’il convient d’anticiper ces besoins au moment de la conception. Mais quelle utilité de dicotomiser les adresses pour une entreprise qui confierait tout à la Poste ?
Enfin la dé-normalisation, si elle est parfois plus pratique, est facile à obtenir par des requêtes qui rassemblent les informations de plusieurs tables parfaitement normalisées et parfois même en rassemblant plusieurs champs dans un seul champs … par exemple des requêtes pour des mailings : en fin de course la secrétaire appréciera de n’avoir qu’un champs « xyzAdresseRue » à manipuler… de même que civilité-prénom-nom rassemblés par exemple ou s’il faut remettre sur une même ligne tous les numéros de téléphones, mobiles et fax qui auraient été typés avec deux champs (type et numéro) comme évoqué dans l’article.
Max34 : Si j’ai un requête qui nécessite 30 jointures suis je toujours gagnant ?
SQLpro : Oui, en démo avec mes éleves je demande quel est le cout de 30 jointures sur des tables de 100 millions de lignes dont la clef est un INT… La réponse est 180 pages à lire soit moins de 1 ms sur un serveur bas de gamme !
SamSam69 : Il est plus judicieux de mettre une jointure entre ma table t1 et t5 afin de réduire les jointures de 4 à 1
SQLpro : NON ! Car vous oubliez toujours la même chose : vous allez gagner pour cette requête, mais comme vous allez augmenter la volume des données, vous allez perdre GLOBALEMENT pour toutes les autres requêtes !
Avez vous fait des bench globaux avant de préconiser cette solution ???
Bonjour
Il est plus facile de « dénormaliser » que l’inverse.
Un projet devrait commencer avec des tables normalisées et, ensuite, on dénormalise si le besoin se fait sentir.
actuellement, nos DBA voient des trucs assez ahurissants, par ex du numérique stocké dans du char parceque les reports sont plus faciles à produire. Sur une table interrogée plusieurs milliers de fois par heure, le cout des conversions CHAR==>NUM est si important que les chefs ont démarré un projet de remise en forme des données.
Le soucis vient surtout du manque d’importance accordée à la normalisation et aux performances. les dev ont appris des trucs à l’école, mais à quoi ca sert?
Et je ne parle pas de normalisation « extrème », mais des trucs tout bete à respecter.
Bon article de best pratice
Je rejoins l’avis de plusieurs personnes en commentaire, la normalisation n’est pas toujours adaptée. Dans un SID, il me semble plus interessant d’avoir une structure denormalisée.
Bonjour
Merci SQLpro pour cet article, ça me rappelle la formation que tu as faites à mes collègues et moi il y a 4 ans sur Nice. Et c’était joyeux une base de données de plus d’une centaine de tables sans aucune clef étrangère ni index ni vue et énormément de données dupliquées et redontantes pour éviter les jointures.
@SamSam69 : comme le disait mon formateur : l’efficacité d’une dénormalisation ne se suppose pas, elle se prouve. Donc oui on peut dénormaliser mais il faut bien le faire.
On peut découper chaque champs lettre par lettre ou apprendre à se servir correctement des index et utiliser un SGBD performant.
Vous poussez le principe d’atomicité un peu trop loin à mon goût. Est-ce que sous prétexte de faire des recherches alphabétiques sur des noms, il faudrait créer un champs spécifique pour leurs premières lettres ?
Je comprends que les consultants aient besoin de justifier leurs tarifs mais là c’est soit trop abusif, soit expliqué avec de mauvais exemples.
Très bon article!
Cependant la normalisation a ses limites car à force de normaliser on perd en performance. C’est la où l’on choisi une architecture OLAP.
Voici un exemple :
Imaginons que j’ai besoin de filtrer des informations de ma table t1 par rapport à un champ qui se trouve dans une table t5 (GROUP BY) en amont (à un t1 correspond un seul t2 et ainsi de suite) :
exemple : t1 -> t2 -> t3 -> t4 -> t5.monChampDAgregation
Il est plus judicieux de mettre une jointure entre ma table t1 et t5 afin de réduire les jointures de 4 à 1 tout en limitant le poids de ma table t1 car un id est en général un simple entier donc très peu gourmand en terme de ressources.
Pour conclure il faut normaliser au maximum puis dénormaliser un peu pour optimiser les performances.
PS: Dans mon exemple t1 est une très grosse table. Si toutes les tables contenaient peu de lignes les performances seraient largement correctes même en la laissant normalisée (avec les indexes qui vont bien évidemment).
Bonjour,
Et merci pour cette mise au point. Cependant je voulais savoir jusqu’à quel point on peut multiplier les tables, normaliser les données? Si j’ai un requête qui nécessite 30 jointures suis je toujours gagnant?
Max
Zinzineti : La formation, car l’ignorance tue !
Hélas tu prêche un convertis… J’enseigne dans différentes écoles d’ingé. 32 h de cours sur 5 ans pour faire :
1) la modélisation des données
2) le SQL et les transactions
3) le décisionnel
4) l’admin de SGBDR !
Inutile de te dire que lorsque l’on arrive à peu près à faire 1 et 2 je suis content !
A +
Voilà un condensé de best pratices pour tous ceux qui travaillent directement ou indirectement avec les bases de données.
132 ! 132 c’est le nombre de colonnes d’une table que j’ai identifié lors d’une opération « pompier » sur une application qui met à genou le serveur de base de données et renvoie les téléconseillers d’un centre d’appel en congés forcés…
Ce qui est navrant c’est quand ces erreurs de conception proviennent de « grand » éditeur de logiciel.
Comment en arrive-t-on là ?
Pourquoi ces erreurs ne sont pas identifiées lors des tests de montée de charge ?
Pourquoi ces erreurs de conception ne sont pas identifées avant la sortie officelle du logiciel ?
Le développeur est-il le seul responsable dans cette affaire ?
Pour ma petite espérience je pense que le mot développeur est un fourre-tout !
un fourre-tout parce que Monsieur le développeur doit avoir des compétences larges et varées.
Qu’attend les managers d’équipe ou chefs de projet d’un développeur ?
Pour eux le développeur doit :
¤ pisser des lignes de code : du bon code (.NET ou Java ou …)
¤ savoir déployer ces applications quelque soit l’environnement du client final …
¤ être bon en système et réseaux (pour ne pas tout le temps déranger les ingénieurs systèmes,…)
¤ avoir l’aptitude de travailler non seulement sur WINDOWS mais aussi sur Linux pour écrire de temps en temps des scripts shell
¤ avoir de bonne aptitude à produire des documents de tests, de recette, d’exploitation,…
Et les base de données ?
Le développeur doit :
¤ maîtriser la modélisation/conception des bases de données (SQL SERVER ET ORACLE ET SYBASE ET …)
¤ maîtriser les principaux tâches de maintenance des serveurs de base de données (SQL SERVER ET ORACLE ET SYBASE ET …)
¤ maîtriser l’écriture de requêtes SQL : pas des SELECT * Non ! mais des requêtes optimées bref il doit être expert en TUNING de requêtes SQL
¤ maîtriser la conception, la création et déploiement des rapports (reporting) sur SSAS ET sur BO ET sur COGNOS ET sur ….
Honnêtement, est ce possible qu’un développeur ait toutes ces compétences ?
Les bases de données sont complexes, et il faut du temps et du travail pour avoir un niveau de maîtrise acceptable sur UN SGBD !
Mais est ce que les managers d’épuipe, les chefs de projet sont-ils sensibilisés sur l’importance d’une bonne conception des bases de données ?
Pas sûr …
Et calà complique tout ! sur les analyses, sur les décisions et les actions à mener : Notamment la formation !
La formation, car l’ignorance tue !