10.17 Quel sont les différents formats de lignes? Quand utiliser VARCHAR/CHAR?

MySQL n'a pas de type SQL VARCHAR à proprement parler.

A la place, , MySQL possède trois manières d'enregistrer les informations, et d'émuler le type VARCHAR.

Si une table n'a aucune colonne de type VARCHAR, BLOB ou TEXT, la table est considéré à taille de ligne constante. Sinon, c'est une ligne à taille variable. Les colonnes CHAR et VARCHAR sont alors traitées de manière identique du point de vue de l'application : dans les deux cas, les espaces de fin de chaînes sont supprimés à la lecture.

Vous pouvez vérifier le format utilisé dans une table avec isamchk -d (-d signifie ``description de la table``).

MySQL possède trois formats de tables différents : taille fixe, taille variable, compressé. En voici la comparaison :

Tables à taille fixe

  • C'est le format par défaut. Il est utilisé lorsqu'une table ne contient pas de colonne de type VARCHAR, BLOB ou TEXT.
  • Toutes les colonnes de type CHAR, NUMERIC et DECIMAL sont complétée par des espaces pour remplir la colonne
  • Tres rapide
  • Facile à mettre en cache
  • Facile à reconstruire après un crash, car tous les enregistrements ont une position fixe.
  • N'a pas besoin de réorganisation (avec isamchk) à moins q'un nombre important d'enregistrement ne soit effacé, et que vous vouliez libérer de l'espace disque pour le serveur.
  • Généralement, requiert plus de place que les tables à taille variable

Tables à taille variable

  • Ce format est utilisé pour gérer le tables qui contiennent des colonnes de type VARCHAR, BLOB ou TEXT.
  • Toutes les colonnes de type chaîne sont de taille variable (à l'exception de celle qui font moins de 4 caractères)
  • Car enregistrement est précédé d'un octet qui indique quelle colonnes sont vides (''), ou nulle (suivant le type de la colonne). (Ne pas confondre avec les valeurs NULL). Si une colonne de type chaîne a une longueur de 0 après suppression des espaces de remplissage, ou bien si une colonne de type numérique à une valeur de 0, cette information est reportée dans cet octet, et la valeur n'est pas sauvée. Les chaînes non vides ont une longueur d'un octet de plus que le nombre de caractères qu'elles contiennent.
  • En général, ces tables prennent moins de place sur le disque.
  • Chaque enregistrement utilise uniquement l'espace dont il a besoin. Si le record augmente en taille, il est scindé en plusieurs parties. Cela aboutit à la fragmentation de la table.
  • Si vous mettez à jour un enregistrement qui a besoin de plus de place, l'enregistrement sera fragmenté. Dans ce cas, il vous faudra exécuter isamchk -r de temps en temps pour améliorer les performances d'ensemble. Utilisez isamchk -ei nom_table pour avoir des informations concernant la fragmentation d'une table.
  • Ce type de table n'est pas simple à reconstruire après un crash, car les enregistrements peuvent être fragmenté.
  • On peut s'attendre à ce qu'un enregistrement prennent la place suivante : 

3

+ (nombre de colonnes + 7) / 8

+ (nombre de colonne de type char)

+ taille compactee des colonnes de type numerique

+ longueur des chaînes 

+ (nombre de valeur NULL + 7) / 8

Il y a aussi une pénalité de 6 octet pour chaque fragment de record. Un record de taille variable est fragmenté à chaque fois qu'il grandit de taille. Chaque nouveau fragment prendra au moins 20 octets, ce qui fait que plusieurs aggrandissements pourront partager le même fragment. Sinon, un autre fragment sera généré. Vous pouvez compter le nombre de fragment avec la commande isamchk -ed. Tous ces fragments peuvent être supprimés avec isamchk -r.

Table compressée

  • C'est une table accessible uniquement en lecture, et constituée grce à pack_isam. Tous les clients avec une garantie étendue par email ont droit à une copie de l'utilitaire pack_isam pour leur utilisation personnelle.
  • L'utilitaire de décompression est fourni avec toutes les distributions de MySQL ce qui fait que tout le monde peut accéder aux tables comrpessées, par pack_isam (ATTENTION : la comrpession est plate forme dépendante.)
  • Prend très peu de place sur le disque. Réduit l'espace disque au minimum.
  • Chaque enregistrement est compressé séparément. Les entêtes d'un enregistrement sont de taille fixe (1-3octets) suivant la taille du plus grand enregistrement de la table. Chaque colonne est compressée différemment. Voici les types de compression :
    • Compression de type Huffman, différente pour chaque colonne de la table.
    • Compression des suffixes
    • Compression des prefixes
    • Les valeurs nulles (0) sont stockés sur un bit.
    • Les valeurs entières sont stockées au format le plus petit possible. Par exemple, un BIGINT (8 octets) peut être stocké au format TINYINT (1 octet) si sa valeur est entre 0 et 255.
    • Si une colonne a un petit nombre de valeur différente, elle est converti en ENUM.
    • Une colonne peut utiliser une combinaison de toutes les techniques ci dessus.
  • Gère les tables de taille fixe ou variable, mais pas les types BLOB ou TEXT.
  • Peut être décompressé par isamchk.

MySQL peut supporter différents types d'index, mais le format standard est NISAM. C'est un index de type B-tree dont on peut grossièrement calculer la taille avec la formule (key_length+4)*0.67, appliquée à toutes les clés. (Ce qui correspond au pire cas, quand toutes les clés sont insérée en ordre).

Les index de chaînes subissent une compression sur les espaces. Si la première partie de l'index est une chaîne, elle sera aussi compressée. La compression des espaces réduit la taille si la colonne a beaucoup d'espace de remplissage, ou si une colonne de type VARCHAR n'est pas toujours remplie. La compression par préfixe est particulièrement utile si plusieurs chaînes commencent de même.