Meilleures pratiques pour la longueur des colonnes SQL varchar

Question

Chaque fois que l'on configure une nouvelle table SQL ou que l'on ajoute une nouvelle varchar à une table existante, je me demande une chose : quelle est la meilleure valeur pour la colonne length .

Donc, disons que vous avez une colonne appelée name de type varchar . Donc, vous devez 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 2^n supérieur. Dans ce cas, je choisirais 32 comme longueur. Je fais cela, parce que du point de vue d'un informaticien, un nombre 2^n semble plus even Je suppose que l'architecture sous-jacente peut gérer ces nombres un peu mieux que d'autres.

D'autre part, le serveur MSSQL, par exemple, fixe la valeur de la longueur par défaut à 50, lorsque vous choisissez de créer une colonne varchar. Cela me fait réfléchir. Pourquoi 50 ? Est-ce juste un nombre aléatoire, ou basé sur la longueur moyenne des colonnes, ou quoi ?

Il se peut aussi - ou probablement - que les différentes implémentations des serveurs SQL (comme MySQL, MSSQL, Postgres, ...) aient des valeurs différentes pour la longueur optimale des colonnes.

Answer 1

Aucun SGBD que je connaisse n'a d'"optimisation" qui rendra une VARCHAR avec un 2^n sont plus performants que ceux qui ont une longueur 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 la longueur 255 différemment de celle avec une longueur maximale plus élevée. Je ne sais pas si c'est toujours le cas.

Pour la quasi-totalité des SGBD, l'espace de stockage réellement nécessaire est uniquement déterminé par le nombre de caractères que vous y mettez, et non par la taille de la base de données. max longueur que vous définissez. Ainsi, du point de vue du stockage (et très probablement aussi du point de vue des performances), il n'y a aucune différence si vous déclarez une colonne en tant que VARCHAR(100) ou VARCHAR(500) .

Vous devriez voir le max longueur prévue pour un VARCHAR comme une sorte de contrainte (ou de règle de gestion) plutôt que comme un élément 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, la modification de la contrainte de contrôle est beaucoup plus rapide que la modification de la table (car la table ne doit pas être réécrite).

La même chose peut s'appliquer à Oracle et à d'autres - dans le cas d'Oracle, ce 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 les performances lorsque l'on utilise varchar(max) par rapport à varchar(8000) .

Voir ce lien (posté par Erwin Brandstetter en tant que commentaire)

Edition 2013-09-22

En ce qui concerne le commentaire de Bigown :

Dans les versions de Postgres antérieures à 9.2 (qui n'était pas disponible lorsque j'ai rédigé la réponse initiale), une modification de la définition de la colonne a fait réécrire l'ensemble du tableau, voir par exemple ici . Depuis la version 9.2, ce n'est plus le cas et un test rapide a confirmé que l'augmentation de la taille des colonnes pour une table de 1,2 million de lignes ne prenait que 0,5 seconde.

Pour Oracle, cela semble également vrai, si l'on en juge par le temps qu'il faut pour modifier les données d'une grande table. varchar colonne. Mais je n'ai pu trouver aucune référence à ce sujet.

Pour MySQL le manuel dit " Dans la plupart des cas, ALTER TABLE fait une copie temporaire de la table originale ". Et mes propres tests le confirment : l'exécution d'un ALTER TABLE sur une table de 1,2 million de lignes (la même que dans mon test avec Postgres) pour augmenter la taille d'une colonne a pris 1,5 minutes. Dans MySQL cependant, vous pouvez pas utilisez la "solution de contournement" pour utiliser une contrainte de contrôle afin de limiter le nombre de caractères dans une colonne.

Pour SQL Server, je n'ai pas pu trouver de déclaration claire à ce sujet, mais le temps d'exécution pour augmenter la taille d'une base de données est plus long. varchar (à nouveau le tableau de 1,2 million de lignes ci-dessus) indique que pas de la réécriture a lieu.

Modifier 2017-01-24

Il semble que j'avais (au moins partiellement) tort à propos de SQL Server. Voir cette réponse d'Aaron Bertrand qui montre que la longueur déclarée d'un nvarchar ou varchar Les colonnes font une énorme différence pour les performances.

Answer 2

VARCHAR(255) et VARCHAR(2) prendre exactement la même quantité d'espace sur le disque ! Par conséquent, la seule raison de les limiter est si vous avez un besoin spécifique pour qu'ils soient plus petits. Sinon, faites-les tous à 255.

Plus précisément, lors du tri, les colonnes plus grandes prennent plus d'espace. Si cela nuit aux performances, il faut s'en préoccuper et les réduire. Mais si vous ne sélectionnez qu'une seule ligne de cette table, vous pouvez simplement les faire toutes de 255 et cela n'aura pas d'importance.

Voir : Quelles sont les tailles optimales des varchars pour MySQL ?

Answer 3

La meilleure valeur est celle qui convient aux données telles que définies dans le domaine sous-jacent.

Pour certains domaines, VARCHAR(10) convient au Name pour les autres domaines VARCHAR(255) pourrait être le meilleur choix.

Answer 4

En complément de la réponse de a_horse_with_no_name, les éléments suivants pourraient vous intéresser...

il n'y a aucune différence entre le fait de déclarer une colonne en tant que 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 le(s) octet(s) de longueur et l'octet nullable donc :

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 nul.

J'espère que cela vous aidera :)

Answer 5

Vérifiez toujours auprès de l'expert du domaine de votre entreprise. Si c'est vous, recherchez une norme industrielle. Si, par exemple, le domaine en question est le nom de famille d'une personne physique (nom de famille), pour une entreprise britannique, je me tournerais vers la norme de l'industrie. Catalogue de normes de données Govtalk du Royaume-Uni pour les informations sur les personnes et découvrez qu'un nom de famille comporte entre 1 et 35 caractères.