Aide pour la conception et l'exploitation d'une table

**Sebajuste** · 14/04/2013, 15h34

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

**alassanediakite** · 15/04/2013, 11h39

Salut
Pour les tables je ferai trois : appareil(id,...), variable(id, nom, letype), valeur(idap, idvar, lavaleur, ladate...). Pour la table valeur, idap, idvar et ladate formerons la clé primaire.
Pour les triggers je ne voie pas l'utilité à moins qu'une table "historiquevaleur" soit utilisée ce dont je ne voie pas non plus l'utilité vu que la taille des données n'en diminue pas. Une table historiquevaleur peut quand même servir à optimiser les SELECT.
@+

**fsmrel** · 15/04/2013, 17h09

Bonjour Sebajuste,

Envoyé par Sebajuste

Nous avons près de 240 valeurs différentes.

Il devrait donc exister une table dont chaque ligne correspond à une de ces valeurs :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
VALEUR 
(
        ValeurId        INT               NOT NULL,
        ValeurNom       VARCHAR(64)       NOT NULL
)
        PRIMARY KEY (ValeurId) ;

Question : Qu’est-ce que Type_Info ? Plus précisément qu’entendez-vous exactement par « variable sauvegardée » ? Une des 240 valeurs différentes ?

Votre table VALEURS actuelle ressemble fortement à une association entre la APPAREIL et la table VALEUR ci-dessus. Pour éviter les confusions, je renomme votre table VALEURS en APPAREIL_VALEUR. En attendant d’en savoir plus sur la nature et le rôle précis de Type_Info, sa structure commencerait à ressembler à ceci :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
APPAREIL_VALEUR
(
        AppareilId        INT             NOT NULL,
        ValeurId          INT             NOT NULL,
        ...               ...             NOT NULL
)
        PRIMARY KEY (AppareilId, ValeurId, ...) 
        FOREIGN KEY (AppareilId) REFERENCES APPAREIL ;

Du point de vue des performances, le choix du 1er attribut entrant dans la composition de la clé primaire est déterminant : à supposer qu’à l’instar de la requête que vous proposez, et par référence à ce que vous écrivez : « Les SELECT sont effectués lorsqu'un utilisateur veut connaitre l'état d'un appareil », c’est l’attribut AppareilId qui figurerait en tête.

Il est important de faire maigrir les tables et les index. Vous avez défini un index où figure Type_Info dont le type est un Varchar, c’est funeste, à proscrire ! (D’où ma question (entre autres) sur ce qu’est et quel est le rôle joué par cet attribut).

Envoyé par Sebajuste

Quels sont les index à placer ?

Il faut déjà soumettre à EXPLAIN le top 10 des requêtes les plus utilisées. EXPLAIN est la requête du DBA.

**fsmrel** · 15/04/2013, 17h19

A propos de MySQL et EXPLAIN, voyez la discussion créée par GueloSuperStar.

**alassanediakite** · 15/04/2013, 17h24

Envoyé par fsmrel

Bonjour Sebajuste,

**fsmrel** · 15/04/2013, 17h30

Envoyé par alassanediakite

Et Bugs ! Suis-je distrait !

**Sebajuste** · 15/04/2013, 18h34

Désolé pour mon manque de clarté, mais vous avez bien compris en ce qui concerne le type_info. Il s'agit bien du "nom" de l'une des 240 valeurs possible.

Par ailleurs, l'idée d'une table supplémentaire pour n'utiliser qu'un entier dans la clef primaire de APPAREIL_VALEUR me parfait effectivement plus intelligent.

Egalement, en faisant des test (merci EXPLAIN), je me suis rendu compte que la clef primaire suffisait. On se retrouve avec cette table:

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
APPAREIL_VALEUR
(
        AppareilId        INT               NOT NULL,
        ValeurId          INT               NOT NULL,
        ValeurDate      TIMESTAMP    NOT NULL;
        ValeurString     VARCHAR(32)  NOT NULL,
        ValeurWrite      TINYINT(1)    NOT NULL,
)
        PRIMARY KEY (AppareilId, ValeurId, ValeurDate) 
        FOREIGN KEY (AppareilId) REFERENCES APPAREIL ;

+----+------------+-----------+--------+----------------+---------+------------------------+-----+---------------------------------------+
| id | select_type| table     | type   | possible_keys  | key     | ref                    | rows| Extra                                 |
+----+------------+-----------+--------+--------------- +---------+------------------------+-----+---------------------------------------+
|  1 | PRIMARY    | <derived2>| ALL    | NULL           | NULL    | NULL                   | 240 | Using temporary                       |
|  1 | PRIMARY    | av        | eq_ref | PRIMARY        | PRIMARY | av_temp.AppareilId, ...|   1 |                                       |
|  2 | DERIVED    | av_bis    | range  | PRIMARY        | PRIMARY | NULL                   |   1 | Using where; Using index for group-by |
+----+------------+-----------+--------+----------------+---------+------------------------+-----+---------------------------------------+

L'autre requête très sollicité est similaire, mais test que la boolean ValeurWrite, afin de ne récupérer que les valeurs "lues", et dont le système est sûr de la validité.

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT av.AppareilId, av.ValeurDate, av.ValeurId, av.ValeurString, av.ValeurWrite
FROM APPAREIL_VALEUR AS av, (
     SELECT MAX( ValeurDate) AS ValeurDate, AppareilId, ValeurId
     FROM APPAREIL_VALEUR
     WHERE AppareilId = ?
     AND ValeurWrite IS FALSE 
     GROUP BY AppareilId, ValeurWrite, ValeurId
) AS av_temp
WHERE av.AppareilId = av_temp.AppareilId
AND av.ValeurId = av_temp.ValeurId
AND av.ValeurDate = av_temp.ValeurDate

J'ai donc placer un index sur (AppareilId, ValeurWrite, ValeurId, ValeurDate)

Le résulat d'EXPLAIN dans ces conditions montre que MySQL utilise bien le nouvel index, mais le nombre de "rows" dépasse les 7000. J'imagine que l'on peut faire mieux... Mais je me vois mal essayer au hasard toutes les combinaisons d'index possible :p

+----+------------+-----------+--------+----------------------+-------------+------------------------+------+---------------------------------------+
| id | select_type| table     | type   | possible_keys        | key         | ref                    | rows | Extra                                 |
+----+------------+-----------+--------+----------------------+-------------+------------------------+------+---------------------------------------+
|  1 | PRIMARY    | <derived2>| ALL    | NULL                 | NULL        | NULL                   |  240 | Using temporary                       |
|  1 | PRIMARY    | av        | eq_ref | PRIMARY, idx_valeurs | PRIMARY     | av_temp.AppareilId, ...|    1 |                                       |
|  2 | DERIVED    | av_bis    | range  | PRIMARY, idx_valeurs | idx_valeurs | NULL                   | 7297 | Using where; Using index for group-by |
+----+------------+-----------+--------+----------------------+-------------+------------------------+-----+---------------------------------------+

En tout cas, j'ai fais un grand pas en avant, merci à vous.

**fsmrel** · 15/04/2013, 18h56

Il faudrait ajouter la clé étrangère qui manque : {ValeurId} référençant la table VALEUR :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
APPAREIL_VALEUR
(
        AppareilId        INT               NOT NULL,
        ValeurId          INT               NOT NULL,
        ValeurDate        TIMESTAMP         NOT NULL,
        ValeurString      VARCHAR(32)       NOT NULL,
        ValeurWrite       TINYINT(1)        NOT NULL,
 
        PRIMARY KEY (AppareilId, ValeurId, ValeurDate), 
        FOREIGN KEY (AppareilId) REFERENCES APPAREIL, 
        FOREIGN KEY (ValeurId) REFERENCES VALEUR
) ;

Y a-t-il une relation entre ValeurId et ValeurString ?

**Sebajuste** · 15/04/2013, 19h33

Petit oublie pour al clef étrangère

Pour ce qui est de la relation entre ValeurId et ValeurString, il n'y en a pas directement.
Si ValeurId est le "nom" de la variable, ValeurString en est la valeur.

Toutes ces valeurs peuvent correspondre à différents éléments:
- des relevés de sondes
- des paramètres des appareils
- des préférences utilisateurs

Voici un exemple purement visuel de données type à sauvegarder pour être un peu plus explicatif.

+----------------------+--------+------------------+----------+
| Variable             | Valeur | Date             | Ecriture |
+----------------------+--------+------------------+----------+
| Consigne à atteindre |     20 | 15/04/2013 18:30 |      oui |
| Sonde 1              |     15 | 15/04/2013 18:15 |      non |
| Consigne à atteindre |     15 | 15/04/2013 18:00 |      non |
| Sonde 2              |    300 | 15/04/2013 17:00 |      non |
| Erreur sonde 2       |    oui | 15/04/2013 17:00 |      non |
| Nom de la sonde 1    |   Toto | 15/04/2013 17:00 |      oui |
| ...                  |        |                  |          |
+----------------------+--------+------------------+----------+

- On remarque que la valeur peut être de type totalement différent (ici, on a des entier, des boolean et une string).
- Les informations "remontée" de l'appareil sont à "false" en écriture. Celles modifiées par l'utilisateur sont à "true".

Si il me parait en effet judicieux de mettre tous les "noms" de variable (Consigne à atteindre, Sonde 1, Sonde 2, Erreur sonde 2, Nom de la sonde 1, etc...) dans une table à part, ça l'est moins pour la valeur.
Excepté le fait qu'elle soit de type indéterminé (bool ou int ou float ou string).

Je suis donc parti sur un type VARCHAR, qui sera casté vers le bon type coté applicatif.

J'imagine qu'il est possible d'avoir le type nativement en utilisant 4 tables: une pour chaque type de valeur, puis d'utiliser une clef étrangère (vers la bonne table ?) dans la table APPAREIL_VALEUR.

Mais j'ai peur de créer trop de complexité à maintenir.

**fsmrel** · 15/04/2013, 20h57

Envoyé par Sebajuste

Si ValeurId est le "nom" de la variable, ValeurString en est la valeur.

D’accord.

Envoyé par Sebajuste

Voici un exemple purement visuel

...Parfaitement clair et qui remplace un long discours !

Envoyé par Sebajuste

Si il me parait en effet judicieux de mettre tous les "noms" de variable (Consigne à atteindre, Sonde 1, Sonde 2, Erreur sonde 2, Nom de la sonde 1, etc...) dans une table à part, ça l'est moins pour la valeur.
[...]
Je suis donc parti sur un type VARCHAR, qui sera casté vers le bon type coté applicatif.

Ça marche.

Envoyé par Sebajuste

j'ai peur de créer trop de complexité à maintenir.

Effectivement. Vous pouvez réfléchir à l’encapsulation des jointures dans une vue par type de données, mais le jeu n’en vaut pas forcément la chandelle, on peut en rester au CAST, à Dieu vat !

N.B. Pourrez-vous nous tenir au courant des performances en relation avec l'évolution de la structure de la table APPAREIL_VALEUR ?

**Sebajuste** · 16/04/2013, 12h10

Voici la listes des temps de traitement de la requête SQL présentée plus haut.

Les données sont issues de ma table de valeurs actuellement en production (avec près de 22 millions de valeurs), après être passées à la moulinette pour être correctement insérées dans les nouvelles tables, cela va sans dire

Le premier test utilise la table avec des index mieux placés, mais conserve le VARCHAR "type_info" (le nom de la variable) dans la clef primaire (tests effectués hier).

Le second test utilise la table APPAREIL_VALEUR associée à la table VALEUR comme suggéré hier soir (les index sont posés de la même façon).

Les temps sont exprimés en secondes.

+--------+--------+
| Test 1 | Test 2 |
+--------+--------+
| 0.7932 | 0.1391 |
| 0.3174 | 0.0759 |
| 0.8018 | 0.1333 |
| 0.4803 | 0.0863 |
| 0.4579 | 0.0939 |
| 0.1342 | 0.0429 |
+--------+--------+

Et au niveau de l'empreinte mémoire :

+---------+---------+---------+
|         | Test 1  | Test 2  |
+---------+---------+---------+
| Données | 1.3 Gio | 925 Mio |
| Index   | 824 Mio | 872 Mio |
| Total   | 2.1 Gio | 1.8 Gio |
+---------+---------+---------+

A titre de comparaison, la table actuellement en production pèse un total de 5Gio, avec des temps d'exécution pour la même requètes SQL allant de 2.9s à 5s et parfois plus...

Je crois qu'il n'y a pas grand chose à dire d'autre.

Ah si ! Merci beaucoup

**fsmrel** · 16/04/2013, 14h38

Bonjour,

Au vu des résultats, on peut penser que les utilisateurs ne seront pas trop mécontents...

En tout cas, on peut penser qu’en bon DBA vous prononcerez à l’avenir le mot magique « EXPLAIN » aussi souvent que nécessaire...

N’hésitez pas à voter pour les messages que vous avez jugés utiles et à venir faire un tour pour discuter d’autres problèmes d’architecture ou de performance...

Bonne route,

François

Aide pour la conception et l'exploitation d'une table

Langage SQL

Discussions similaires

Partager

Partager