création de site internet

flus RSS du blogsur Facebooksur Twitter
créateur de mot de passe

Générateur de mot de passe

Créez un mot de passe sécurisé

05
06

Optimisation MySQL - 1 - les types de données

dans MySQL

L'optimisation des bases de données MySQL et de leur utilisation intervient à différents niveaux (paramètrages, clés, requêtes, ...). Mais la première étape se fait dès la structuration de la base de données et de ses tables, lorsqu'il faut choisir le type des données qui les composent.

Je ne vais pas m'attarder à décrire tous les types de données existants en MySQL, il existe de nombreuses ressources sur internet à ce sujet et notamment sur le site officiel MySQL. Concrètement gardons en mémoire qu'il y a 3 grandes familles (4 si l'on considère les types relatifs aux données spatiales) : les types numériques (INT, ...), les types date et heure (DATE, TIMESTAMP, ...), les types chaînes de caractères (VARCHAR, TEXT,...).

1) Le choix des types de données

Le premier réflexe est donc d'utiliser le type le plus pertinent pour chaque donnée. Par exemple pour stocker l'âge d'un membre, il faut utiliser une donnée de type numérique, pour stocker sa date d'inscription, il faut une donnée de type date et heure, enfin pour stocker son nom, une donnée de type chaîne de caractères, ... l'idée étant d'utiliser les types qui disposent des opérations et fonctionnalités que l'on aura besoin d'exploiter (les fonctions mathématiques pour les types numériques, les fonctions de dates et d'heures pour les types date, les fonctions de chaînes de caractères et de recherche Full-text pour les types chaînes de caractères.

2) Le choix des tailles de données

Il convient ensuite d'adapter la taille des données au plus juste en fonction des besoins. Il est par exemple inutile de faire appel à un VARCHAR(255) pour des données qui ne dépassent pas 50 caractères. Ce réflexe permet de diminuer l'espace recquis par la base de données, de limiter les accès disque et d'augmenter le nombre d'informations stockées en mémoire et donc au final d'améliorer les performances. Un autre avantage non négligeable, lors de la migration de la base de données, plus la base est légère plus vite se font les dumps et les imports.

Pour les données numériques, il convient donc de sélectionner le type adapté aux valeurs possibles (TINYINT pour des valeurs comprises entre -128 et 127 ou 0 et 255, SMALLINT entre -32768 et 32767 ou 0 et 65535, ...). Important également, l'attribut UNSIGNED permet d'exploiter uniquement des valeurs positives et ainsi de ne pas réserver inutilement de l'espace pour des valeurs négatives non exploitées. Par exemple, pour l'âge, il faut utiliser un TINYINT UNSIGNED (valeurs comprises entre 0 et 255)

Pour les données date et heure, le type sera fonction de la précision souhaitée (DATE si on ne souhaite stocker que le jour, DATETIME ou TIMESTAMP si on souhaite une précision à la seconde, YEAR si on en souhaite stocker que l'année, ...). Par exemple pour la date de naissance, il faut utiliser un DATE (car la précision à la seconde importe peu).

Enfin pour les données chaînes de caractère, le choix du type sera fonction du nombre de caractères à stocker (CHAR peut contenir jusqu'à 255 caractères, VARCHAR jusqu'à 65535, ...).

Il existe une commande MySQL intéressante pour ce genre de réflexion, la commande PROCEDURE ANALYSE. Elle permet, à partir d'une requête, d'avoir une analyse détaillée de chaque colonne qui est prise en compte :

SELECT * FROM matable PROCEDURE ANALYSE()

Un autre paramètre à envisager, lors de l'utilisation des clauses JOIN dans les requêtes, si les colonnes comparées sont de types différents, MySQL va réaliser une opération afin de convertir les données pour pouvoir les comparer, ce qui ralentira l'exécution de la requête. C'est donc également un critère à prendre en compte lors de la structuration des tables.

3) Les types numériques plutôt que les types chaînes de caractères

De part leur nature, les types numériques s'avèrent plus performants (plus légers, ...) que les types chaînes de caractères dans les requêtes. Dans certains cas de figure, il est donc plus pertinent de faire appel à ce type de données. Notamment sur des données dont le nombre de valeurs possibles est limité et fermé. Par exemple, quand on veut stocker la civilité, plutôt que de stocker sous forme de chaîne de caractère ('M.', 'Mme', 'Melle'), il est plus efficace de stocker sous forme numérique (1 pour monsieur, 2 pour madame, 3 pour mademoiselle). Ainsi, les requêtes en lecture sur cette donnée se feront sur un type numérique et s'exécuteront donc plus vite, surtout sur des gros volumes. Il existe plein d'autres exemples d'applications de ce principe (liste de pays, liste d'options, ...). Qui plus est, avec l'application des clés que j'évoquerai dans un prochain article, le choix des données numériques revêt tout son sens.

En fait l'idée est que la gestion du dictionnaire de valeurs ne se fait plus au niveau MySQL, mais au niveau applicatif (par exemple, au niveau du site, un script PHP fera les conversions des civilités : 1 = monsieur, 2 = madame, 3 = mademoiselle).

A noter que dans cette logique, le type ENUM pourrait également répondre à cette problématique, mais au-delà du fait qu'il ne soit pas standard SQL, les performances d'exploitation de ce type sont considérablement dégradées dans certains cas de figure (dans le cas de l'utilisation de JOIN avec ORDER BY notamment)

4) Eviter les valeurs NULL

La valeur NULL est une valeur différente des autres valeurs. Et de part sa spécificité, elle est plus lourde à stocker et nécessite des traitements particuliers. Elle a donc un impact négatif sur les performances. Il est donc préférable de systématiquement indiquer la clause NOT NULL sur chaque colonne.

Partager :