To BLOB or not to BLOB, telle est la question:
Vaut-il mieux enregistrer une chaîne de caractères dans un
BLOB, dans un CHAR, ou dans un VARCHAR?
Différences entre le CHAR et
le VARCHAR
Beaucoup de gens croient que VARCHAR est meilleur parce qu'il enregistre
seulement des données réelles, alors que le CHAR est enregistré
dans la toute sa longueur. Ce n'est pas vrai. En fait, les CHAR et les
VARCHAR sont enregistrés dans la mémoire tampon dans l'intégralité
de
leur longueur déclarée ; quand la ligne est enregistrée
sur le disque, l'algorithme de compactage de RLE est employé
pour comprimer la ligne entière, c.-à-d. CHARs, VARCHARs,
INTEGERs, DATEs, etc. tous ensemble. Donc si vous voulez ménager
de l'espace, CHARs sont légèrement meilleurs que VARCHARs
(la difference est que VARCHAR enregistre la longueur de chaîne de
caractères dans deux octets). Il y a également un bug qui
fait que VARCHAR ne nettoie pas correctement la fin de la chaîne
de caractères si vous assignez une chaîne de caractères
plus courte, ce qui cause un plus mauvais compactage.
Beaucoup de gens croient également que VARCHAR envoie uniquement
les données réelles sur le réseau, alors que le CHAR
est transmis dans toute sa longueur déclarée. Ce n'est pas
vrai non plus. La transmission entre le client et le serveur est faite
par l'intermédiaire des messages de longueur fixe. Pour cette raison
le CHAR et les VARCHAR sont transmis dans leur pleine longueur déclarée.
Ainsi la décision d'utiliser le CHAR ou le VARCHAR devrait être
basée seulement sur les besoins de l'application. Par exemple enregistrez
les codes de longueur fixe dans le CHAR, enregistrez des noms dans VARCHAR
(pour permettre une concatenation correcte).
Comparaison entre les BLOBs et les
VARCHARs
Tous les commentaires dans ce paragraphe se référant au type
de VARCHAR sont valides pour le type CHAR également
Chaque commentaire termine avec BLOB+
ou
VARCHAR +
pour indiquer quel type de données est meilleur.
-
Connaissez-vous la longueur maximum de vos données?
Avec VARCHARs vous devez déclarer la longueur maximum de votre
chaîne de caractères.
Avec des BLOBs vous n'avez pas besoin de vous en inquiéter.
BLOB +
-
Devez-vous enregistrer des chaînes de caractères très
longues?
Une colonne de VARCHAR est limitée à 32Ko (c.-à-d.
à peu près 10000 caractères Unicode).
La taille maximum du BLOB est (selon le "Operation Guide"):
taille de page de 1Kb = > 64 Mo
taille de page de 2Kb = > 512 Mo
taille de page de 4Kb = > 4 gigaoctets
taille de page de 8Kb = > 32 gigaoctets
BLOB +
Devez-vous enregistrer beaucoup de longues colonnes de texte dans une
table?
La longueur totale d'une ligne (non comprimée) est limitée
à 64K. Les VARCHARs sont enregistrés directement dans la
ligne, ainsi vous ne pouvez pas enregistrer beaucoup de longues chaînes
de caractères dans une ligne
Les BLOBs sont représentés par leur blob-id, ainsi ils
utilisent seulement 8 octets sur les 64K.
BLOB +
Voulez-vous réduire au minimum la conversation entre le client
et le serveur?
Les données VARCHAR sont "fetchées" avec les autres données
de la ligne en une seule opération fetch, et habituellement plusieurs
lignes sont envoyées sur le réseau en une seule fois.
Chaque BLOB a besoin d'exécutions supplémentaires d'open/fetch.
VARCHAR +
Voulez-vous réduire au minimum la quantité de données
transférées entre le client et le serveur?
Avec les BLOBs vous pouvez décider après avoir
récupéré la ligne, si vous voulez récupérer
les données du blob également.
Avec VARCHAR vous avez l'inconvénient du fait que la ligne complète
est transférée à travers le réseau dans toute
sa longueur déclarée (les VARCHARs trop longs dégradent
sensiblement l'exécution même sur réseau local, c'est
dire d'une connection modem.)
BLOB +
Voulez-vous réduire au minimum l'espace disque utilisé?
VARCHARs sont comprimés par RLE (en fait la ligne entière
est comprimée, sauf les blobs). 128 octets au maximum peuvent être
comprimés sur 2 octets. Ceci signifie qu'un VARCHAR(32000) vide
occupera quand même 500+2 octets.
Les BLOBs ne sont pas comprimés, mais un blob vide (c.-à-d.
null) occupera seulement 8 octets de blob-id (qui seront compressés
ultérieurement par RLE). Un blob non vide peut être enregistré
soit sur la même page que les autres données de la ligne (s'il
tient), soit sur une page séparée. Un petit blob qui tient
sur une page de données a une entête d'environ 40 octets (ou
légèrement plus). Un grand blob a les mêmes 40 octets
au début de la page de données, plus 28 octets au début
de chaque page de blob (30 octets sur la première). Une page de
blob ne peut pas contenir plus d'une blob (c.-à-d. les pages de
blob ne sont pas partagées comme les pages de données). Par
exemple pour une taille de page de 4K, si vous enregistrez un blob de 5K,
deux pages de type blob seront assignées, ce qui signifie que vous
perdez 3K de l'espace! En d'autres termes plus la taille de page est grande,
plus la probabilité que les blobs s'adapteront est élevée,
mais plus il y a d'espace perdu si des pages supplémentaires sont
nécessaires pour des grands blobs.
VARCHAR + (excepté
pour les très longs VARCHARs, ou pour les tables avec un grand nombre
de blobs NULLs)
Avez-vous besoin d'une table avec un très grand nombre de lignes?
Chaque ligne est identifiée par un DB_KEY qui est une
valeur de 64 bits, dont 32 bits représentent l'identifiant de relation
et 32 bits sont employés pour localiser la ligne. Ainsi le nombre
maximum théorique des lignes dans une table est 2^32 (mais pour
plusieurs raisons le vrai maximum est encore inférieur). Les blob-id
sont assignées dans le même espace d'adresses que les DB_KEYs,
ce qui signifie que plus il y a de blobs dans une table, moins il reste
de DB_KEYs pour adresser des lignes. D'autre part lorsque les lignes d'enregistrement
sont grandes (par exemple si elles contiennent de longs VARCHARs), alors
moins de lignes tienent sur la page de données et beaucoup de valeurs
de DB_key resteront disponibles de toute manière.
varchar +?
Recherchez-vous la performance?
Puisque les grands blobs sont enregistrés hors des pages de
données, ils augmentent la "densité" des lignes sur les pages
de données et par là l'efficacité du cache (réduction
du nombre de lecture/écriture pendant la recherche).
BLOB +
Devez-vous exécuter des recherche sur le contenu des colonnes
de texte?
Sur les colonnes de VARCHAR que vous pouvez utiliser des opérateurs
comme ' = ', ' > ', BETWEEN, IN (), LIKE et STARTING (sensibles à
la casse) , CONTAINING (insensible à la casse). Dans la plupart
des cas l'index peut être utilisé pour accélérer
la recherche.
Les BLOBs ne peuvent pas être indexés, et vous êtes
limités aux opérateurs LIKE, STARTING et CONTAINING. Vous
ne pouvez pas directement comparer des blobs grâce aux opérateurs
' = ', ' > ' etc. (à moins que vous utilisez les UDF), donc vous
ne pouvez pas par exemple joindre des tables sur des champs blobs.
VARCHAR +
Voulez-vous rechercher le contenu de ces textes avec CONTAINING?
Puisqu'il n'est pas possible de définir la d'ordre de
"collation" pour les colonnes de type BLOB, vous ne pouvez pas utiliser
pleinement la recherche non sensible à la casse avec des caractères
nationaux (seulement de moitié inférieure du jeu de caractères
est insensible à la casse). (à moins que vous utilisez des
UDFs au lieu de l'opérateur CONTAINING.)
VARCHAR +
Avez-vous besoin de changer la casse de vos champs textes?
Vous pouvez utiliser la fonction intégrée UPPER() sur
les VARCHAR, mais pas sur les blobs. (en outre les fonctions CAST, MIN
et MAX ne peuvent pas être utilisées sur les blobs)
VARCHAR +
Il n'est pas possible de trier une table sur un champ blob (ainsi que
d'utiliser GROUP BY, DISTINCT, UNION, JOIN ON)
Il n'est pas possible d'e concaténer des blobs.
VARCHAR +
Il n'y a aucune fonction intégrée de conversion (CAST)
pour convertir de BLOB à VARCHAR et réciproquement.
Mais il est possible d'écrire une UDF à cette fin.
match nul
Il n'est pas possible d'assigner une valeur à un blob directement
par une commande SQL.
par exemple. INSERT INTO tab(MyBlob)VALUES('abc'); (mais il
est possible d'utiliser une UDF pour convertir une chaîne de caractères
en un blob).
VARCHAR +
Volume du fichier de tri.
Parfois quand InterBase doit trier une table résultat (par exemple
pour GROUP BY, DISTINCT, UNION sans ALL, etc...) il crée un fichier
de tri provisoire. Ce fichier contient les valeurs de toutes les colonnes
que vous choisissez, c.-à-d. soit des chaines des caractères
complètes (VARCHAR), soit seulement des courts identifiants de blob.
Les blobs permettent donc de réduire la taille du fichier de tri
à gérer.
BLOB +
Les blobs peuvent avoir des tailles de segments définis, des
sous-types de blobs et utiliser les filtres de blob.
Les VARCHARs ne supportent pas ces fonctionnalités.
BLOB +
Avez-vous besoin d'une bonne sécurité sur ces colonnes
de texte?
Pour effectuer une recherche dans une table, vous devez avoir accordé
le privilège SELECT.
Pour obtenir un blob, il vous suffit de connaître uniquement
son identifiant (blob-id enregistré dans la table), mais InterBase
ne vérifiera pas quels droits vous avez sur le blob. Ce qui signifie
que n'importe qui sachant ou devinant le bon blob-id peut lire le blob
sans aucun droit sur la table. (vous pouvez tester avec ISQL et la commande
BLOBDUMP.)
VARCHAR +
Quels outils vous utilisez?
La décision finale (si vous préférez le blob ou
le varchar) peut être déterminée par des outils utilisés
pour accéder à la base de données (composants Delphi,
logiciel...). Quelques outils peuvent avoir des problèmes avec la
gestion des blobs, quelques autres outils peuvent avoir des restrictions
sur les VARCHARs (par exemple limite à 255 caractères ou
incapacité à faire la différence entre chaine vide
et chaine nulle).
match nul
Copyright © 2001 Ivan Prenosil
traduit de l'anglais par Google, rectifications par gzOrg.