Choisir les bons types de colonne SQL

Formation

En Ligne

Prix sur demande

Appeler le centre

Avez-vous besoin d'un coach de formation?

Il vous aidera à comparer différents cours et à trouver la solution la plus abordable.

Description

  • Typologie

    Formation

  • Méthodologie

    En ligne

Grâce à cette formation vous pourrez acquérir les connaissances nécessaires qui vous permettrons d’ajouter des compétences à votre profil et obtenir de solides aptitude qui vous offriront de nombreuses opportunités professionnelles.

Questions / Réponses

Ajoutez votre question

Nos conseillers et autres utilisateurs pourront vous répondre

À qui souhaitez-vous addresser votre question?

Saisissez vos coordonnées pour recevoir une réponse

Nous ne publierons que votre nom et votre question

Les Avis

Le programme

Introduction du cours

Ce tutoriel vise à alléger au maximum vos tables SQL en choisissant les bons types et en utilisant à bon escient les attributs et autres options.

Vous n'utilisez que les types INT et TEXT sans aucun attribut ?

Alors ce tuto est pour vous, vous êtes sur le point de diviser par 2 la taille des données stockées dans votre BDD.

Dans ce tutoriel, je parlerai des bases de données MySQL (je me base sur la version que je connais le mieux, la 5.0). Si vous ne connaissez pas votre configuration SQL, alors vous avez certainement celle-là, elle est très répandue. Et dans le cas où vous auriez une base différente, vous devriez trouver dans votre documentation des types équivalents pour les rares colonnes dont je pourrais parler et qui ne seraient pas dans votre liste.

Ce tutoriel sera composé de deux types de parties. Lorsque vous voyez à droite le logo vert ci-contre, vous êtes dans une partie principale, aucune connaissance outre les bases en SQL n'est nécessaire. En suivant toutes les parties vertes, vous pourrez déjà grandement optimiser vos tables.

Les parties arborant un logo orange permettent d'approfondir. Si vous êtes curieux, si vous voulez savoir le pourquoi du comment, lisez-les. Et n'oubliez pas qu'en comprenant MySQL en profondeur, vous serez d'autant plus efficace dans la pratique et pourrez vraiment choisir vos structures en connaissance de cause. Notez que ces parties vous demanderont quelques notions de mathématique, de binaire ou d'informatique. Si vous débutez ou que vous souhaitez aller à l'essentiel, vous pouvez sauter ces parties et descendre jusqu'au logo vert suivant.

Comment choisir ?

Commençons par un exemple de table mal optimisée (créée avec phpMyAdmin) :

Je teste une requête sur cette table, voilà la requête :

INSERT INTO `super_lourd` VALUES (1, 'Jean-Yves', '1990-02-16', 'rouge', '0125262728', 19);

Et ensuite, je regarde le poids de cette ligne ("Espace utilisé" dans phpMyAdmin) : 52 octets.

Essayons maintenant avec cette configuration :

Les données restent exactement les mêmes sauf que le poids, lui, passe à 28 octets.

Bref sur une table aussi simple que celle-là contenant 1000 lignes, on peut économiser 23 Ko !

Optimiser ses structures de table SQL consiste à choisir les formats les plus petits possibles pour stocker ses données.

En gros, c'est comme si vous deviez envoyer à 1000 personnes :

  • un courrier

  • un outil

  • un mp3

Et que vous deviez commander vos enveloppes et cartons en lots.
Si vous n'envoyez que des petits courriers vous prenez un lot de 1000 petites enveloppes, pas des enveloppes A3, vous allez perdre des sous.
L'outil peut être gros, il vous faut un carton extensible ^^
Et les mp3, c'est fragile, si vous ne mettez pas de papier-bulle, ils vont se casser.

En SQL, il faut aussi adapter le conteneur au contenu. Voyons maintenant les formats disponibles.

Les formats numériquesPourquoi préférer les types numériques aux textuels ?

Tout ce qui peut être rapporté à un nombre entier doit être enregistré dans un format INT (TINYINT, SMALLINT, INT...).

Et pourquoi d'abord ?

Parce qu'au final, tout sera enregistré en binaire dans des petits octets.
Un octet peut prendre 256 formes. Pour une valeur numérique, ça revient à pouvoir stocker un nombre entre 0 et 255, et pour un texte, c'est une lettre ou un symbole ou un chiffre. Ce qui implique que si vous prenez un type texte limité à 1 octet pour stocker un nombre, vous irez de 0 à 9 et c'est tout ! On est loin des 255.

Comment choisir le bon INT ?

Et oui, des INT, il y en a pas mal. Ils ont chacun une taille définie et des limites que voici :

Type

Poids en octets

Minimum

Maximum

TINYINT

1

-128

127

SMALLINT

2

-32 768

32 767

MEDIUMINT

3

-8 388 608

8 388 607

INT

4

-2 147 483 648

2 147 483 647

BIGINT

8

-9 223 372 036 854 775 808

9 223 372 036 854 775 807

Il faut donc choisir le type dont les bornes encadrent vos nombres au plus près.

Le nombre à virgule

Pour les nombres à virgule, c'est moins compliqué, vous avez FLOAT (occupe 4 octets) et DOUBLE (8 octets).
La seule chose qui différencie les deux types, c'est la précision :
Supposons qu'on veuille stocker : 3.1415926535897931159979634685441851615905761718753236...
En DOUBLE, on a : 3.141592653589793115997963468544185161590576171875
En FLOAT, on a : 3.1415926535897931159979634685442
Vous pouvez voir que DOUBLE est plus précis que FLOAT mais qu'aucun des deux types ne permet de stocker une précision infinie. Le stockage binaire est dit discret. Ce qui signifie qu'au delà d'une précision donnée, les valeurs et calculs de nombres à virgule ne sont que des approximations.

Le stockage des nombres flottants se fait en deux parties, l'une est appelée mantisse et l'autre exposant. Le nombre est le résultat de mantisse x 2exposant. La mantisse et l'exposant sont des nombre entiers convertis en binaires. Par exemple, on peut stocker 1,25 de la façon suivante : 5x2-2. Avec FLOAT, la mantisse est codée sur 24 bits et l'exposant sur 8, avec DOUBLE, ils sont codés respectivement sur 53 bits et 11 bits.

Enfin, il y a DECIMAL, c'est un type pour des nombres entiers ou à virgule de précision exacte dont on peut définir les limites. Par exemple DECIMAL(5,3) autorise au maximum 5 chiffres dont 3 sont situés après la virgule (exemple 12,345). Ce type est un peu moins optimisé que les précédents puisqu'il occupe M+2 octets si D>0 et M+1 octets si D=0 avec M et D les paramètres de DECIMAL : DECIMAL(M,D). Donc avec notre exemple DECIMAL(5,3) D=3 et M=5 ; la colonne occupe alors 5+2=7 octets. Notez qu'un nombre entier ou à virgule prend autant de place dans un VARCHAR que dans un type DECIMAL. Il est plus que probable que DECIMAL convertisse les nombres en chaînes de caractères pour les stocker à la différence que l'utilisation de DECIMAL vous garantit qu'il n'y aura que des nombres dans la colonne.

Rappel : MEDIUMINT occupe seulement 3 octets et peut y stocker 5 chiffres sans problème. Pour stocker un nombre décimal du type 12,345 vous pouvez le multiplier par 1000 et l'enregistrer dans un MEDIUMINT puis le diviser par 1000 lorsque vous le récupérer, vous économisez ainsi 4 octets.

Les attributs des types numériques

C'est bête, moi je veux enregistrer la taille en centimètre de mes visiteurs, les TINYINT ne sont pas suffisants et en même temps, je n'ai pas besoin des négatifs.

Et bien il y a mieux que de soustraire 127 à la taille pour l'enregistrer en TINYINT, vous pouvez choisir l'attribut UNSIGNED qui signifie non-algébrique, cela limite les colonnes aux nombres positifs. Le minimum des tous les types devient alors 0 et les maximums sont les suivants :

Type

Poids en octets

Maximum

TINYINT

1

255

SMALLINT

2

65 535

MEDIUMINT

3

16 777 215

INT

4

4 294 967 295

BIGINT

8

18 446 744 073 709 551 615

Talus me propose de vous expliquer pourquoi le maximum augmente en UNSIGNED.

Les nombres sont stockés sous leur forme binaire (ils sont convertis en une suite de 0 et de 1 appelés bits). Un octet donne 8 bits, d'où les 256 solutions (28), or si un nombre peut être négatif, il va falloir utiliser 1 bit pour stocker le signe (0, pour + et 1 pour -). Voyez par vous-même le résultat d'un petit script maison :

46 en binaire

00101110

128-46 en binaire

01010010

256-46 en binaire

11010010

-46 en binaire

11010010

Vous constatez que les 7 derniers bits sont les mêmes dans les trois dernières lignes et que -46 ainsi que 210 (256-46) s'écrivent de la même façon dans un octet. Et le premier octet suit cette règle :
De -128 à -1 : 1
De 0 à 127 : 0
De 128 à 255 : 1
Donc si le premier bit est égal à 0, SQL convertit simplement ce nombre en décimal.
S'il est égal à 1 :

  • il renvoie un nombre négatif de valeur égale à 128 moins les 7 autres bits convertis en décimal.

  • Sauf si l'on active l'attribut UNSIGNED, là il convertit les 8 bits en décimal.

Donc en libérant le bit qui s'occupe du signe, on multiplie par deux la capacité de stockage pour la valeur absolue du nombre.

Peut importe le nombre de bits total utilisé, il n'y a jamais besoin que d'un seul bit pour coder le signe. On peut donc obtenir le nombre maximal stockable dans un type de colonne en fonction de la taille de stockage en octets (notée n) comme ça :

  • Sans attribut : 2(n*8)-1-1

  • Avec UNSIGNED : 2(n*8)-1

Et le deuxième attribut qui s'avère utile moins souvent mais quelques fois tout de même : ZEROFILL. Comme son nom l'indique, il remplit les valeurs entrées avec des zéros.

N'ayez crainte, aucun mal ne sera fait à aucun d'entre nous, il s'agit du chiffre zéro et non des utilisateurs de ce site. (Comment ça, c'est pas drôle )

Donc si j'enregistre 23 dans un TINYINT UNSIGNED ZEROFILL (ZEROFILL implique l'attribut UNSIGNED), SQL enregistrera 023. Ou encore dans un SMALLINT UNSIGNED ZEROFILL : 00023.

Ha c'est bien mais si je veux stocker un nombre à 4 chiffres complété par des zéros (comme par exemple un CHMOD 0755, 0664...), hein ?

Et bien, c'est là, qu'intervient la taille des colonnes.

SMALLINT(4) UNSIGNED ZEROFILL

Ou sur phpMyAdmin :

Précision de calcul

On a vu plus haut que les nombres à virgules étaient stockés sous la forme de deux nombres entiers. En comprenant que seul des nombres entiers sont enregistrés et qu'ils sont codés sur un nombre fini de bits, certains nombres ou résultats seront obligatoirement approximés.

Tout d'abord, sachez que si vous faites la somme de plusieurs nombres entiers, il n'y a pas de perte de précision. Ce n'est pas parce que votre colonne est un TINYINT que SQL ne peut pas calculer au-delà de 256, en additionnant deux TINYINT de 250, on obtient bien 500. Les résultats sont fiables tant qu'on ne dépasse pas 64 bits de précision. En BIGINT, certaines fonctions telles que SMU() permettent d'obtenir des valeurs exactes même avec de grands nombre. Par exemple la somme de deux entées : 18446744073709551615 et 18446744073709551614, le résultat retourné est 36893488147419103229. La valeur est exacte malgré le dépassement des 64 bits. Attention cependant au logiciel qui récupèrera ce résultat. Par exemple PHP a...

Appeler le centre

Avez-vous besoin d'un coach de formation?

Il vous aidera à comparer différents cours et à trouver la solution la plus abordable.

Choisir les bons types de colonne SQL

Prix sur demande