Meilleures pratiques pour la longueur des colonnes de varchar SQL
chaque fois que l'on installe une nouvelle table SQL ou qu'on ajoute une nouvelle colonne varchar à une table existante, je me demande une chose: quelle est la meilleure valeur pour la length .
donc, disons, vous avez une colonne appelée name de type varchar . Donc, vous avez à choisir la longueur. Je ne peux pas penser à un nom > 20 caractères, mais vous ne le saurez jamais. Mais au lieu d'utiliser 20, j'arrondis toujours au nombre suivant de 2^N. Dans ce cas, je choisirais 32 Comme longueur. Je faites cela, parce que du point de vue d'un informaticien, un nombre 2^n me semble plus even que d'autres nombres et je suppose juste que l'architecture en dessous peut gérer ces nombres légèrement mieux que d'autres.
d'un autre côté, MSSQL server par exemple, définit la valeur par défaut de la longueur à 50, lorsque vous choisissez de créer une colonne varchar. Cela me fait penser à elle. Pourquoi 50? c'est juste un nombre aléatoire, ou basé sur la longueur moyenne des colonnes, ou quoi?
il se pourrait aussi - ou c'est probablement - que différentes implémentations de serveurs SQL (comme MySQL, MSSQL, Postgres, etc.) soient utilisées ...) ont des valeurs différentes de la longueur de la meilleure colonne.
7 réponses
aucun SGBD que je connaisse n'a aucune "optimisation" qui fera un VARCHAR avec une longueur 2^n effectuer mieux qu'un max longueur qui n'est pas une puissance de 2.
je pense que les premières versions de SQL Server traitaient en fait un VARCHAR avec une longueur 255 différemment d'un avec une longueur maximale plus élevée. Je ne sais pas si c'est toujours le cas.
pour presque tous les SGBD, le stockage réel requis est seulement déterminé par le nombre de caractères que vous y mettez, pas la longueur max que vous définissez. Donc, à partir d'un stockage de point de vue (et plus probablement de performance), il ne fait aucune différence si vous déclarez une colonne VARCHAR(100) ou VARCHAR(500) .
vous devriez voir la longueur max prévue pour une colonne VARCHAR comme une sorte de contrainte (ou règle d'affaires) plutôt qu'une chose technique/physique.
pour PostgreSQL la meilleure configuration est d'utiliser text sans restriction de longueur et un CHECK CONSTRAINT qui limite le nombre de caractères à ce que votre entreprise exige.
si cette exigence change, modifier la contrainte de contrôle est beaucoup plus rapide que modifier la table (parce que la table n'a pas besoin d'être réécrite)
la même chose peut être appliquée pour Oracle et d'autres - dans Oracle il serait VARCHAR(4000) au lieu de text cependant.
Je ne sais pas s'il y a une différence de stockage physique entre VARCHAR(max) et par exemple VARCHAR(500) dans SQL Server. Mais apparemment il y a un impact sur la performance quand on utilise varchar(max) par rapport à varchar(8000) .
voir ce lien (posté par Erwin Brandstetter comme un commentaire)
Modifier 2013-09-22
concernant le commentaire de bigown:
dans les versions Postgres avant 9.2 (qui n'était pas disponible lorsque j'ai écrit la réponse initiale) un changement à la définition de la colonne did réécrire la table entière, voir par exemple ici . Depuis 9.2 ce n'est plus le cas et un test rapide a confirmé que l'augmentation de la taille de la colonne pour une table avec 1,2 millions de lignes n'a en effet pris que 0,5 secondes.
Pour Oracle, et cela semble être vrai, à en juger par le temps qu'il faut pour modifier la colonne varchar d'une grande table. Mais je ne pouvais pas trouver aucune référence.
Pour MySQL le manuel dit " Dans la plupart des cas, ALTER TABLE fait une copie temporaire de la table d'origine ". Et mes propres tests confirment que: lancer un ALTER TABLE sur une table avec 1,2 millions de lignes (le même que dans mon test avec Postgres) pour augmenter la taille d'une colonne a pris 1,5 minutes. In MySQL however you peut pas utiliser le "contournement" de l'utilisation d'une contrainte de vérification afin de limiter le nombre de caractères dans une colonne.
pour SQL Server Je n'ai pas pu trouver une déclaration claire à ce sujet, mais le temps d'exécution pour augmenter la taille d'une colonne varchar (encore une fois le tableau de 1,2 millions de lignes ci-dessus) indique que Non réécriture a lieu.
Modifier 2017-01-24
semble que j'avais (au moins partiellement) tort sur SQL Server. Voir cette réponse D'Aaron Bertrand qui montre que la longueur déclarée d'une colonne nvarchar ou varchar fait une énorme différence pour la performance.
VARCHAR(255) et VARCHAR(2) prendre exactement la même quantité d'espace sur le disque! Donc, la seule raison à la limite c'est si vous avez un besoin spécifique pour qu'il soit plus petit. Sinon, ils sont tous au nombre de 255.
en particulier, lors du tri, plus grande colonne ne prennent plus d'espace, donc si cela nuit à la performance, alors vous devez vous inquiéter à ce sujet et les rendre plus petits. Mais si vous sélectionnez seulement une rangée de cette table, alors vous pouvez les faire tous 255 et il ne sera pas question.
voir: quelles sont les tailles optimales de varchar pour MySQL?
la meilleure valeur est celle qui convient aux données telles que définies dans le domaine sous-jacent.
pour certains domaines, VARCHAR(10) est juste pour l'attribut Name , pour d'autres domaines VARCHAR(255) pourrait être le meilleur choix.
chaque fois que j'installe une nouvelle table SQL, je ressens la même chose à propos de 2^n étant plus "Pair"... mais pour résumer les réponses ici, il n'y a pas d'impact significatif sur l'espace de stockage simplement en définissant varchar(2^N) ou même varchar(MAX).
cela dit, vous devez toujours anticiper les implications potentielles sur le stockage et la performance lors de la fixation d'une limite de varchar() élevée. Par exemple, disons que vous créez une colonne varchar(MAX) pour contenir les descriptions de produit avec le texte complet indexer. Si 99% des descriptions ne font que 500 caractères, et puis soudainement vous obtenez quelqu'un qui remplace lesdites descriptions par des articles de Wikipédia, vous pouvez remarquer un stockage significatif imprévu et des résultats de performance.
une autre chose à considérer de Bill Karwin :
il y a un impact de performance possible: dans MySQL, les tables temporaires et les tables de mémoire stockent une colonne VARCHAR comme une longueur fixe colonne, collier à sa longueur maximale. Si vous concevez des colonnes VARCHAR beaucoup plus grande que la plus grande taille dont vous avez besoin, vous consommez plus de mémoire que vous devez. Cela affecte l'efficacité du cache, la vitesse de tri, etc.
essentiellement, il suffit de trouver des contraintes et des erreurs commerciales raisonnables sur une taille légèrement plus grande. Comme @onedaywhen l'a souligné, les noms de famille au Royaume-Uni sont généralement entre 1-35 caractères. Si vous décidez de le faire varchar(64), vous n'êtes pas ça va vraiment faire mal à n'importe quoi... sauf si vous stockez le nom de famille de ce type qui est dit pour être jusqu'à 666 caractères long. Dans ce cas, peut-être que varchar(1028) a plus de sens.
et dans le cas où il est utile, voici ce que varchar 2^5 à 2^10 pourrait ressembler si rempli:
varchar(32) Lorem ipsum dolor sit amet amet.
varchar(64) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
varchar(128) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
varchar(256) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
varchar(512) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
varchar(1024) Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
velit metus, sit amet tristique purus condimentum eleifend. Quis
que mollis magna vel massa malesuada bibendum. Proinde tincidunt
dolor tellus, sit amet porta neque varius vitae. Seduse molestie
lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
mollis diam pretium gravida. In facilisis vitae tortor id vulput
ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
dapibus leo lobortis eleifend. Vivamus vitae diam turpis. Vivamu
nec tristique magna, vel tincidunt diam. Maecenas elementum semi
quam. In ut est porttitor, sagittis nulla id, fermentum turpist.
Curabitur pretium nibh a imperdiet cursus. Sed at vulputate este
proin fermentum pretium justo, ac malesuada eros et Pellentesque
vulputate hendrerit molestie. Aenean imperdiet a enim at finibus
fusce ut ullamcorper risus, a cursus massa. Nunc non dapibus vel
Lorem ipsum dolor sit amet, consectetur Praesent ut ultrices sit
ajouter à la réponse de a_horse_with_no_name ce qui suit pourrait vous intéresser...
, il ne fait aucune différence si vous déclarez une colonne VARCHAR(100) ou VACHAR (500).
-- try to create a table with max varchar length
drop table if exists foo;
create table foo(name varchar(65535) not null)engine=innodb;
MySQL Database Error: Row size too large.
-- try to create a table with max varchar length - 2 bytes for the length
drop table if exists foo;
create table foo(name varchar(65533) not null)engine=innodb;
Executed Successfully
-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65533))engine=innodb;
MySQL Database Error: Row size too large.
-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65532))engine=innodb;
Executed Successfully
N'oubliez pas la longueur en octets(s) et les valeurs null byte:
name varchar(100) not null sera de 1 octet (longueur) + jusqu'à 100 caractères (latin1)
name varchar(500) not null sera de 2 octets (longueur) + jusqu'à 500 caractères (latin1)
name varchar(65533) not null sera de 2 octets (longueur) + jusqu'à 65533 caractères (latin1)
name varchar(65532) sera de 2 octets (longueur) + jusqu'à 65532 caractères (latin1) + 1 octet null
Espérons que cela aide :)
Vérifiez toujours auprès de votre expert de domaine d'affaires. Si c'est vous, cherchez une norme industrielle. Si, par exemple, le domaine en question Est le nom de famille d'une personne physique (nom de famille), alors pour une entreprise du Royaume-Uni, je me rendrais au catalogue Govtalk data standards du Royaume-Uni pour des informations sur la personne et découvrirais qu'un nom de famille sera entre 1 et 35 caractères.
Je n'ai pas vérifié ces derniers temps, mais je sais dans le passé avec Oracle que le pilote JDBC réserverait un morceau de mémoire pendant l'exécution de requête pour retenir le jeu de résultats revenant. La taille du morceau de mémoire dépend des définitions de la colonne et de la taille du fetch. Ainsi la longueur des colonnes de varchar2 affecte combien de mémoire est réservée. Cela a causé de sérieux problèmes de performance pour moi Il ya des années comme nous avons toujours utilisé varchar2(4000) (Le max à l'époque) et la collecte des ordures a été beaucoup moins efficace qu'elle ne l'est aujourd'hui.