Aide pour la conception et l'exploitation d'une table
Bonjour à tous.
Je viens vous solliciter pour avoir votre avis éclairé sur la structure à mettre en place et son exploitation, dont voici la problématique :
Il s'agit d'une table "historique" permettant de sauvegarder des changements de valeurs, pour différents appareils connectés au système.
Pour le contexte, nous utilisons MySQL (tables InnoDB pour profiter des transactions), et possédons en production un peu plus de 20 millions de valeurs. Nous souhaitons optimiser cette partie, qui commence à donner des signes de faiblesse au niveau des perfomances des requètes de lecture.
Il existe quantité de valeurs différentes (le cahier des charges à ce niveau est continuellement mis à jour en fonction des attentes des clients; à ce jour, nous avons près de 240 valeurs différentes), avec des types également différents (float, int, string...).
Enfin, je suis très loin d'être un expert dans le domaine des SGBD. C'est toutefois moi qui remplis le rôle de DBA dans la société. Je vous remercie de votre indulgence sur les erreurs de termes que je pourrais faire.
Deux questions me procupent à ce niveau:
1) Est-il préférable d'avoir un champ unique de type "varchar", puis de caster la valeur vers le bon type dans l'application ? Ou bien est-il plus appropié d'avoir plusieurs "colonnes" (une pour chaque type), et une colonne indiquant quel est le type de valeur ? (quittes à ce que toutes les colonnes non utilisées soient à NULL)
2) Est il plus intéréssant d'avoir une clef primaire avec un incrément ? Ou bien une clef composite regroupant le type d'information stocké, sa date, et la clef étrangère de l'appareil possédant la valeur ? (il ne peut exister deux fois la même variable au même moment pour le même appareil)
Voici la table telle que je la conçevrais:
Code:
1
2
3
4
5
6
7
8
9
| CREATE TABLE valeurs (
type_info varchar(32) NOT NULL,
date TIMESTAMP NOT NULL,
value varchar(32) NOT NULL,
write_mode bool NOT NUL,
id_appareil int NUT NULL,
PRIMARY KEY(type_info, date, id_appareil),
FOREIGN KEY(id_appareil) REFERENCES appareil(id)
); |
- type_info, est le nom de variable sauvegardée
- date, est le moment où cette valeur a été mesurée
- value, est la valeur
- write_mode, permet de savoir si la valeur est lue depuis l'appareil, ou écrite vers l'appareil. Dans la pratique, la valeur vaut vrai quand un utilisateur souhaite écrire la dite valeur.
- id_appareil, et la clef étrangère permettant de savoir à qui appartient la valeur.
Plusieurs requètes de lecture sont effectuées. La plus sollicitée est celle permettant de retourner les dernière valeurs sauvegardées:
Code:
1
2
3
4
5
6
7
8
9
| SELECT *
FROM valeurs AS v, (
SELECT id_appareil, MAX(date) AS date, type_info
FROM valeurs
WHERE id_appareil = ?
GROUP BY id_appareil, type_info ) AS v_temp
WHERE v.id_appareil = v_temp.id_appareil
AND v.date = v_temp.date
AND v.type_info = v_temp.type_info; |
Y a t'il un moyen plus performant de l'écrire ?
Quels sont les index à placer ? J'aurais tendance à en placer un dans cet ordre: (id_appareil, date, type_info)
Qu'en pensez vous ?
Il me semble qu'une clef primaire est automatiquement un index. Ce qui me ramène à ma question 2) du dessus. Le choix d'utiliser une clef primaire composite, puisque celle-ci correspond à l'index que je voudrais poser, est-il pertinent ?
Il existe également d'autres requêtes, pour obtenir la somme, moyenne, ou la liste de certaines valeurs sur une période de temps donnée, mais elles sont plus triviales.
Cette table est très sollicitée, autant en écriture qu'en lecture.
Des INSERT sont effectués très régulièrement, mais aussi des UPDATES ! (en moyenne 40 requètes insert/update par seconde) En effet, pour certaines valeurs on ne souhaite que conserver les changements de valeurs (détéction des fronts), on ne met à jour alors que la date, de façon à signifier jusqu'à quel moment cette valeur était valide.
Les SELECT sont effectués lorsqu'un utilisateur veut connaitre l'état d'un appareil, en plus du système lui même pour ses opérations (comme la connexion d'un nouvel appareil, ou récupérer les changement effectuée par un utilisateur en "write_mode").
L'utilisation d'une vue matérialisés n'est donc pas non plus à exclure de façon à limiter le champ de recherche pour les lectures des dernières valeurs.
Ne maitrisant absolument pas les triggers et autres déclencheurs, il me semble plus sécurisé (fautes des compétences adéquates) de développer le nécéssaire coté applicatif.
Mon patron m'a suggéré de créer une table supplémentaires, qui contiendrait toutes les colonnes nécéssaires. Celà ferais une table avec près de 250 colonnes, et des ALTER TABLE pour en rajouter des nouvelles toutes les 3 semaines. Une table de ce type ne devrait contenir que quelques millers de lignes.
Certes, en une ligne toutes les informations seraient retournées, mais les contraintes qui celà implique valent elle le compromis ? Ou bien faut-il rester sur le même type de structure que la table "valeurs" ci-dessus ?
Je vous remercie de toutes éclaircissement que vous pourriez me donner sur ces différents points.
