Melhores práticas para o comprimento da coluna varchar SQL
Cada vez que se cria uma nova tabela SQL ou se adiciona uma nova coluna {[[0]} a uma tabela existente, pergunto-me uma coisa: qual é o melhor valor para o length.
name do tipo varchar. Então, você tem que escolher o comprimento. Não consigo pensar num nome > 20 caracteres, mas nunca saberás. Mas em vez de usar 20, Eu sempre arredondar para o próximo número 2^n. Neste caso, eu escolheria 32 como o comprimento. Eu faço isso, porque a partir de um ponto cientista da computação of view, a number 2^n looks more even to me than other numbers and i'm just supposing that the architecture underneath can handle those numbers slightly better than others.
por outro lado, o servidor MSSQL, por exemplo, define o valor de tamanho padrão para 50, quando você optar por criar uma coluna varchar. Isso faz-me pensar nisso. Porquê 50? é apenas um número aleatório, ou baseado no comprimento médio da coluna, ou o quê?
também pode ser - ou provavelmente é-que diferentes servidores SQL implementações (como MySQL, MSSQL, Postgres,...) têm diferentes melhores valores de comprimento de coluna.
7 answers
Nenhum DBMS que eu conheça tem qualquer "optimização" que faça um {[[0]} com um 2^n Comprimento executar melhor do que um com um max Comprimento que não é uma potência de 2.
Acho que as versões iniciais do servidor de SQL trataram realmente um {[[0]} com o comprimento 255 de forma diferente de um com um comprimento máximo mais elevado. Não sei se ainda é assim.
Para quase todos os DBMS, o armazenamento real que é necessário é determinado apenas pelo número de caracteres que você coloca nele, não o max comprimento que define. Assim, do ponto de vista do armazenamento (e provavelmente também do desempenho), não faz qualquer diferença se você declara uma coluna como VARCHAR(100) ou VARCHAR(500).
Você deve ver o max Comprimento previsto para uma coluna VARCHAR como uma espécie de restrição (ou regra de negócio) em vez de uma coisa técnica/física.
Para o PostgreSQL a melhor configuração é usar {[[9]} sem uma restrição de comprimento e um {[10] } que limita o número de caracteres para qualquer que seja o seu negócio requerer.
Se esse requisito mudar, alterar a restrição de verificação é muito mais rápido do que alterar a tabela (porque a tabela não precisa ser reescrita)
O mesmo pode ser aplicado para Oracle e outros-no Oracle seria VARCHAR(4000) Em vez de text embora.
Não sei se existe uma diferença física de armazenamento entre VARCHAR(max) e por exemplo VARCHAR(500) no servidor SQL. Mas aparentemente há um impacto no desempenho quando se usa varchar(max) em comparação com varchar(8000).
Ver Este link (publicado por Erwin Brandstetter como comentário)
Editar 2013-09-22
Quanto ao Comentário de bigown:Nas versões Postgres antes de 9.2 (que não estava disponível quando escrevi a resposta inicial) uma alteração na definição da coluna fez reescrever a tabela inteira, ver por exemplo aqui. Uma vez que 9.2 este já não é o caso e um teste rápido confirmou que o aumento do tamanho da coluna para uma tabela com 1,2 milhões as linhas de fato levaram apenas 0,5 segundos.
Para a Oracle, isto também parece ser verdade, a julgar pelo tempo necessário para alterar a coluna de uma grande tabela. Mas não encontrei nenhuma referência para isso. O manual diz: " na maioria dos casos,ALTER TABLE faz uma cópia temporária da tabela original". E meus próprios testes confirmam que: executar um ALTER TABLE em uma tabela com 1,2 milhões de linhas (o mesmo que em meu teste com Postgres) para aumentar o tamanho de uma coluna levou 1,5 minuta. No MySQL, no entanto, você pode Não usar o "workaround" para usar uma restrição de verificação para limitar o número de caracteres numa coluna.
Para o servidor de SQL não consegui encontrar uma declaração clara sobre isto, mas o tempo de execução para aumentar o tamanho de uma coluna varchar (mais uma vez a tabela de 1.2 milhões de linhas acima) indica que não a reescrita tem lugar.
Editar 2017-01-24
Parece que eu estava (pelo menos parcialmente) errado sobre o servidor SQL. Ver esta resposta de Aaron Bertrand {[42] } que mostra que o comprimento declarado de uma nvarchar ou varchar colunas faz uma enorme diferença para o desempenho.
VARCHAR(255) e VARCHAR(2) tome exactamente a mesma quantidade de espaço no disco! Então a única razão para limitá-lo é se você tem uma necessidade específica para que ele seja menor. Caso contrário, torna-os todos 255.
Especificamente, ao fazer a triagem, coluna maior ocupa mais espaço, por isso, se isso prejudicar o desempenho, então você precisa se preocupar com isso e torná-los menores. Mas se você só selecionar uma linha daquela mesa, então você pode apenas torná-los todos 255 e não vai importar.
Ver: Quais são os tamanhos de varchar ideais para o MySQL?
O melhor valor é o correcto para os dados, tal como definido no domínio subjacente.
Para alguns domínios, VARCHAR(10) é correcto para o atributo Name, para outros domínios VARCHAR(255) pode ser a melhor escolha.
Outra coisa a considerar de Bill Karwin:
Há um possível impacto no desempenho: em MySQL, mesas temporárias e as tabelas de memória armazenam uma coluna VARCHAR como uma coluna de comprimento fixo, acolchoado até ao seu comprimento máximo. Se desenhar muito as colunas VARCHAR maior do que o maior tamanho que você precisa, você vai consumir mais memória do que é preciso. Isso afeta a eficiência de cache, a velocidade de triagem, etc.Basicamente, inventa restrições de Negócio razoáveis e erros num tamanho ligeiramente maior. Como @onedaywhen apontou, os nomes de família no Reino Unido são geralmente entre 1-35 caracteres. Se você decidir fazer varchar (64), Você realmente não vai machucar nada... a não ser que estejas a guardar o nome de família deste tipo. até 666 caracteres de comprimento. Nesse caso, talvez varchar(1028) faça mais sentido.
E no caso de ser útil, aqui está o que varchar 2^5 a 2^10 pode parecer se for preenchido:
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
Adicionando à resposta do a_horse_with_ no_name, poderá encontrar o seguinte interessante...
Não faz diferença se declara uma coluna como VARCHAR (100) ou varchar(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ão se esqueça do byte (S) comprimento e do byte nullable so:
name varchar(100) not null será de 1 byte (comprimento) + até 100 caracteres (latin1)
name varchar(500) not null será de 2 bytes (comprimento) + até 500 caracteres (latin1)
name varchar(65533) not null serão 2 bytes (comprimento) + até 65533 caracteres (latin1)
name varchar(65532) serão 2 bytes (comprimento) + até 65532 caracteres (latin1) + 1 byte nulo
Verifique sempre com o seu especialista no domínio de negócios. Se és tu, procura um padrão industrial. Se, por exemplo, o domínio em questão for o nome de família de uma pessoa singular (apelido), então, para uma empresa do Reino Unido, eu iria ao catálogo de padrões de dados do GOVTALK do Reino Unido para informações sobre pessoas e descobriria que um nome de família será entre 1 e 35 caracteres.