SQL : formatage des données, optimisation & astuces

Ayant dans les derniers mois reprit l’utilisation de MySQL pour mon projet de gestion de licences, j’ai décidé d’écrire noir sur blanc les avancées et les choses que j’ai revues ou apprises, afin de ne pas perdre du temps une prochaine fois. J’en profite pour faire un petit article, en même temps donc que la rédaction des mes notes.

Le but de cet article est de faire un tour de présentation de l’optimisation d’une base MySQL et également de voir quelques requêtes spécifiques, toujours dans un but de stabilité et de performance.

Il faut savoir lorsque l’on construit sa base de données, exactement le type de données qu’on veut y stocker. Contrairement à un conception NoSQL, le modèle relationnel est plus statique, et demande une grosse optimisation aux premiers abords. Sur quoi peut on jouer pour mettre en place sa BDD ?

1- Le SGBD

MySQL commence à se faire dépasser par sa petite sœur MariaDB (blague dissimulée que seul les puristes comprendront, je vous invite d’ailleurs à commenter si vous avez compris :p ). En effet, ce fork prometteur fait beaucoup parler de lui, et si vous voulez commencer votre optimisation, on peut regarder en premier lieu un SGBD différent. Mes contraintes dans mon projet incluaient malheureusement l’utilisation de MySQL, donc ce premier point sera peut-être utile à d’autres, mais il reste suggestif. Depuis l’achat de MySQL par Oracle, les équipes sont surement moins motivée sur le projet open-source, alors que certains anciens développeurs de MySQL continuent à porter des améliorations significatives sur leur nouveau bébé MariaDB. Tout cela pour dire que la plupart des benchmarks placent MariaDB devant MySQL, et qu’il est de plus en plus adopté.

En voici un qui compare MariaDB 10.1.0 (en rouge) avec MySQL 5.7.4 (en vert) sur la performance au nombre de transactions par secondes.LinkBench_measureOn constate qu’au début, MySQL a de bien meilleures performances, mais au fur et à mesure que le buffer pool (le cache) se remplit, MariaDB se maintient alors que les performances de MySQL chutent. Je ne compare ici qu’un seul terrain, il y en a bien sûr d’autres sur lesquels MySQL reste en tête, notamment sur les comparaisons de performances avec le même moteur de stockage et les même technologies activées (comme le thread pool par exemple), ou l’on constate qu’il faut une configuration différente et que tous les scénarios ne conviennent pas pour faire passer MariaDB devant MySQL.

Toutefois, on constate de nets progrès chez MariaDB dans la performance, et, même si on doit se méfier de la désinformation sur le Web de nos jours, le chiffre de 5-6 % revient souvent pour quantifier le gain réel de performance dans des backend d’applications en scénario de test. MariaDB gère maintenant les sous-requêtes et ont un moyen d’optimiser les vues. Les vues (qui ne sont ni plus ni moins que des requêtes préparée utilisables pour réunir les informations nécessaires à volonté) sont couteuses en performances, car elles font souvent appel à de nombreuses tables. Et bien MariaDB aide à optimiser ces vues, par le principe de « table elimination » (élimination de tables), qui va détecter les tables inutilisées dans les requêtes et les éliminer.

2- Les types de données

La taille

Quand on parle d’insérer 50 tuples, n’importe qu’elle base peut tenir la charge aisément. Mais quand il s’agit de 10 000 000 d’enregistrements, la il faut commencer à économiser la ressource. On commence par les nombres : adaptez leur taille maximale. Pour une colonne ID il est prudent de laisser une grosse marge, mais pour un colonne Age, ou on stocke l’âge d’une personne, il est inutile de mettre un type INT. Le type numérique de base entier, INT, est codé sur 4 octets, il accepte donc des valeurs de -2^16 à 2^16. Un type TINYINT, codé sur 1 octet suffit (valeurs de -128 à +127).

Les bornes

Si on veut pousser le détail un peu plus, une dame de 128 ans veut s’enregistrer sur votre site de réservation de carrés VIP en boite de nuit. Problème, votre champ n’accepte pas les nombre plus grands que 127. Il est possible de spécifier que la valeur ne peut pas contenir de valeurs négatives. Cela s’appelle une valeur UNSIGNED, et fait que l’on déplace les bornes de notre valeur de [-128, 127] à [0, 255], permettant donc a tout le monde de s’inscrire.

`age` tinyint unsigned,

Le format :

Inutile d’utiliser un format DECIMAL ou FLOAT (nombre flottant avec une précision définissable) alors que l’on a besoin de de nombre entier, ou d’utiliser un format texte pour définir un Prénom, alors que l’on peut utiliser un VARCHAR (la particularité du format VARCHAR est que la taille occupée va s’adapter en fonction du contenu de chaque champ).

3- Les relations

Parfois, même dans un modèle relationnel, il sera bénéfique de stocker pas mal d’options dans une chaine de caractère, et avec une fonction côté langage serveur de récupérer se champ et le convertir en tableau, afin d’avoir un tableau d’options, plutôt que d’avoir pleins de champs ou d’avoir une relation supplémentaire avec une table. Bien entendu, ce procédé est a utiliser avec précaution car il ne faut partir dans trop de linéarité, mais il est utile dans les cas ou :

  • on récupère un bon nombre de d’options (en général des petites valeurs pour chaque option) en même temps à chaque requête
  • on a besoin de ces données dans un traitement déjà lourd avec une requête imbriquée à plusieurs niveaux (ça évite d’en rajouter)

Les puristes du SQL me taperont peut-être sur les doigts en lisant ça, mais même du haut d’une petite expérience, ce procédé m’a déjà été utilise plusieurs fois car résultant en un gain de performance.

4- Les attributs & triggers

On peut par exemple définir des valeurs par défaut de certains champs (cela ne marche pas avec le type TEXT), ça nous évitera à chaque insertion de préciser toutes les valeurs que devraient prendre des champs. Cela se fait en rajoutant le mot clé DEFAULT <valeur>.

`private` bit default '0',

Les triggers, ou « déclencheurs », sont très pratique dans le cas ou on veut de manière automatique, insérer un/des enregistrement dans une table lorsqu’une condition est respectée. Ils sont très pratiques dans le cas ou on veut mettre à jour des vues matérialisées que l’on a créées. Un trigger va venir s’attacher à une table, et écouter des évènements sur cette table, qui se présentent sous la forme des opérations INSERT,DELETE et UPDATE. Il peut s’exécuter avant ou après l’évènement en question (BEFORE ou AFTER). Il y a donc en tout 6 types possibles de déclencheurs. Prenons un exemple concret :

Admettons que nous ayons une table nommée « participants » qui stockera les participants d’un concours. Ce concours possèdera différentes épreuves : une course de pédalo yeux bandés,  des lancers de rouleaux de papier hygiénique, du curling sur gazon … on commence par créer la table :

CREATE TABLE participants (
  `id`    INTEGER PRIMARY KEY AUTO_INCREMENT,
  `nom`  VARCHAR(255) NOT NULL
) ENGINE=InnoDB;

Et également une table « resultats » dans laquelle on stockera les résultats de chaque participants aux différentes épreuves :

CREATE TABLE resultats (
  `id_participant`       INT NOT NULL REFERENCES participants( id ) ON DELETE CASCADE,
  `id_epreuve`     SMALLINT UNSIGNED REFERENCES epreuves( id ),
  `score` MEDIUMINT,
  `temps` NUMERIC(5,2),
  PRIMARY KEY( id_participant, id_epreuve ),
  KEY( id_participant, id_epreuve )
) ENGINE=InnoDB;

Note : lorsque l’on créé la table resultats, la ligne ou l’on créé « id_participant » signifie que ce champ sera un entier (4 octets) non nul, qu’il fait référence au champ « id » de a table participants, et ON DELETE CASCADE indique que si on supprime un objet parent (un participant), toutes les références de ce participant dans la table des résultats seront automatiquement supprimées. Ce qui peut être utilisé si on ne veut pas laisser de trace de ce qui n’est plus actif.

Note 2 : Si on avait voulu le comportement inverse, c’est à dire qu’il soit impossible de supprimer un enregistrement dans la table resultats si il fait référence à un objet qui existe dans la table participants, il aurait simplement fallu utiliser ON DELETE RESTRICT au lieu de CASCADE.

Bien, maintenant nous allons ajouter un trigger qui va par exemple automatiquement remplir une table resultats_history, dès qu’un résultat est ajouté dans la table resultats :

La tableau resultats_history sera un clone de la table resultats, avec toutefois un champ en plus de type DATETIME, pour stocker la date et l’heure à laquelle le résultat a été ajouté. Le mécanisme que j’ai décrit va nous permettre même quand les résultats sont supprimés, de converser une trace datée des évènements. Si on voulait voir plus loin, on pourrait même noter les mise à jours et les suppressions dans cette table, pour faire un log complet. Mais nous allons nous contenter de stocker l’historique des insertions :

CREATE TRIGGER `trace_results` BEFORE INSERT
ON `resultats`
FOR EACH
ROW BEGIN
   INSERT INTO resultats_history (
      id_participant,
      id_epreuve,
      date_insertion,
      score,
      temps)
   VALUES (
      NEW.id_participant,
      NEW.id_epreuve,
      NOW(),
      NEW.score,
      NEW.temps
   );
END;

Dans ce code, je vais créer un TRIGGER nommé trace_results, qui  va se déclencher juste avant une insertion sur la table resultats. J’indique que pour toutes les lignes (pas de filtre), lorsque j’ajoute une ligne dans resultats, il doit également insérer dans la table resultats_history les valeurs qui vont être insérées. Et NOW() renvoie la date à l’instant T, qui va être stockée dans le champ date_insertion. J’ai choisi cet exemple car il est simple à comprendre.

5- Des requêtes uniques

Il est souvent plus avantageux de faire une seule requête, quitte à la charger un peu, plutôt qu’en faire plusieurs. Pourquoi ? Car à chaque requête, il va y falloir repasser

6- Les moteurs de stockage

Et enfin, si vous voulez pousser le détail, vous pouvez comparer et tester les nombreux moteurs de stockage à disposition. Les deux étant les plus connus sont InnoDB et MyISAM. Le premier est celui par défaut dans les version de MySQL récentes et gère les transactions, il est par contre un peu moins performant que MyISAM. Pourquoi ? Car il est plus strict sur la gestion des données. Il va permettre de gérer atomiquement les transaction, et donc pouvoir contrôler l’intégrité des données. On notera donc les avantages/désavantages des deux moteurs dans le tableau ci-dessous :

 

Moteur Avantages Inconvénients
InnoDB Gère l’atomicité des requêtes Plus lent en général pour les opérations CRUD
Contrôle de l’intégrité des données Consomme de la ressource matérielle plus fortement
Gère les transactions (COMMIT, ROLLBACK, …) Plus complexe à utiliser
Gère les clés étrangères
Plus facile à récupérer après un crash
MyISAM Plus performant dans la recherche de texte Pas de transactions
Rapide pour les INSERT et les SELECT Ne gère pas les clés étrangères
Gère la compression de données Plus difficile à récupérer après un crash
Possède un table lock, pour verrouiller des tables en écriture

Il existe bien sûr d’autres moteurs, mais je ne les connais que de nom, et je ne saurais pas expliquer leurs particularités. Dans la majorité des cas je vous conseillerais d’utiliser le plus récent, InnoDB, afin d’avoir une meilleure stabilité générale. Je peux tout de même citer certains moteurs notables comme MEMORY, qui stocke en mémoire vive les donnée, redoutable et efficace, mais si on éteint la machine sans sauvegarde, on perd tout :p On peut également importer des moteurs de stockage externes, non-natifs à MySQL, comme Aria, qui est le moteur par défaut de MariaDB.

Bon c’est a peu près tout, je pense avoir survolé la plupart des facettes de l’optimisation. Toutefois, je ne suis pas passé sur certains points volontairement, comme les vues matérialisées, que je ne maitrise pas forcément et qui, mal utilisées, peuvent causer des complications et soucis de performances. Ça sera peut-être pour une prochaine fois :)

Laisser un commentaire