Existe uma diferença real de desempenho entre as chaves primárias INT e VARCHAR?

Existe uma diferença de desempenho mensurável entre o uso de INT vs. VARCHAR como uma chave primária no MySQL? Eu gostaria de usar o VARCHAR como a chave primária para listas de referência (pense nos estados dos EUA, códigos de país) e um colega de trabalho não vai ceder no INT AUTO_INCREMENT como uma chave primária para todas as tabelas.

Meu argumento, conforme detalhado aqui , é que a diferença de desempenho entre INT e VARCHAR é insignificante, uma vez que toda referência de chave estrangeira INT exigirá que um JOIN faça sentido da referência, uma chave VARCHAR apresentará diretamente a informação.

Então, alguém tem experiência com esse caso de uso específico e as preocupações de desempenho associadas a ele?

Você faz um bom ponto que você pode evitar um número de consultas unidas usando o que é chamado de uma chave natural em vez de uma chave substituta . Só você pode avaliar se o benefício disso é significativo em sua aplicação.

Ou seja, você pode medir as consultas em seu aplicativo que são as mais importantes para serem rápidas, porque elas funcionam com grandes volumes de dados ou são executadas com muita frequência. Se essas consultas se beneficiarem da eliminação de uma junit e não sofrerem o uso de uma chave primária varchar, faça-o.

Não use qualquer estratégia para todas as tabelas no seu database. É provável que, em alguns casos, uma chave natural seja melhor, mas em outros casos, uma chave substituta é melhor.

Outras pessoas afirmam que é raro, na prática, que uma chave natural nunca seja alterada ou tenha duplicatas, de modo que as chaves substitutas geralmente valem a pena.

Não é sobre desempenho. É sobre o que faz uma boa chave primária. Único e imutável ao longo do tempo. Você pode pensar que uma entidade como um código de país nunca muda com o tempo e seria um bom candidato para uma chave primária. Mas a experiência amarga é que raramente é assim.

INT AUTO_INCREMENT atende à condição “único e imutável ao longo do tempo”. Daí a preferência.

Depende do comprimento .. Se o varchar for 20 caracteres, e o int for 4, então se você usar um int, seu índice terá CINCO vezes mais nós por página de espaço de índice no disco … Isso significa que atravessar o índice exigirá um quinto da quantidade de leituras físicas e / ou lógicas.

Então, se o desempenho é um problema, dada a oportunidade, sempre use uma chave não significativa integral (chamada de substituto) para suas tabelas, e para Chaves Estrangeiras que referenciam as linhas nessas tabelas …

Ao mesmo tempo , para garantir a consistência dos dados, todas as tabelas importantes também devem ter uma chave alternativa não numérica significativa (ou Índice exclusivo) para garantir que linhas duplicadas não possam ser inseridas (duplicadas com base em atributos de tabela significativos).

Para o uso específico do qual você está falando (como as pesquisas de estado), isso realmente não importa, porque o tamanho da tabela é muito pequeno. Em geral, não há impacto no desempenho de índices em tabelas com menos de alguns milhares de linhas. ..

Absolutamente não.

Eu fiz várias … várias … verificações de desempenho entre INT, VARCHAR e CHAR.

Uma tabela de 10 milhões de registros com uma PRIMARY KEY (exclusiva e em cluster) tinha exatamente a mesma velocidade e desempenho (e custo de subtree), não importando qual dos três eu usei.

Dito isto … use o que for melhor para sua aplicação. Não se preocupe com o desempenho.

Para códigos curtos, provavelmente não há diferença. Isso é especialmente verdadeiro, já que a tabela contendo esses códigos provavelmente será muito pequena (algumas milhares de linhas no máximo) e não será alterada com frequência (quando for a última vez que adicionamos um novo estado dos EUA).

Para tabelas maiores com uma variação maior entre a chave, isso pode ser perigoso. Pense em usar o endereço de e-mail / nome de usuário de uma tabela de usuário, por exemplo. O que acontece quando você tem alguns milhões de usuários e alguns desses usuários têm nomes longos ou endereços de e-mail. Agora, toda vez que você precisar entrar nessa tabela usando essa chave, ela se tornará muito mais cara.

Eu estava um pouco irritado com a falta de benchmarks para isso online, então eu mesmo fiz um teste.

No entanto, observe que não faço isso regularmente, portanto, verifique minha configuração e as etapas de quaisquer fatores que possam ter influenciado os resultados de forma não intencional e publique suas preocupações nos comentários.

A configuração foi a seguinte:

  • CPU Intel® Core ™ i7-7500U @ 2.70GHz × 4
  • 15,6 GiB RAM, dos quais eu assegurei que cerca de 8 GB estavam livres durante o teste.
  • Drive SSD de 148,6 GB, com bastante espaço livre.
  • Ubuntu 16.04 64 bits
  • MySQL Ver 14.14 Distrib 5.7.20, para Linux (x86_64)

As mesas:

create table jan_int (data1 varchar(255), data2 int(10), myindex tinyint(4)) ENGINE=InnoDB; create table jan_int_index (data1 varchar(255), data2 int(10), myindex tinyint(4), INDEX (myindex)) ENGINE=InnoDB; create table jan_char (data1 varchar(255), data2 int(10), myindex char(6)) ENGINE=InnoDB; create table jan_char_index (data1 varchar(255), data2 int(10), myindex char(6), INDEX (myindex)) ENGINE=InnoDB; create table jan_varchar (data1 varchar(255), data2 int(10), myindex varchar(63)) ENGINE=InnoDB; create table jan_varchar_index (data1 varchar(255), data2 int(10), myindex varchar(63), INDEX (myindex)) ENGINE=InnoDB; 

Então, eu preenchi 10 milhões de linhas em cada tabela com um script PHP cuja essência é assim:

 $pdo = get_pdo(); $keys = [ 'alabam', 'massac', 'newyor', 'newham', 'delawa', 'califo', 'nevada', 'texas_', 'florid', 'ohio__' ]; for ($k = 0; $k < 10; $k++) { for ($j = 0; $j < 1000; $j++) { $val = ''; for ($i = 0; $i < 1000; $i++) { $val .= '("' . generate_random_string() . '", ' . rand (0, 10000) . ', "' . ($keys[rand(0, 9)]) . '"),'; } $val = rtrim($val, ','); $pdo->query('INSERT INTO jan_char VALUES ' . $val); } echo "\n" . ($k + 1) . ' millon(s) rows inserted.'; } 

Para tabelas int , o bit ($keys[rand(0, 9)]) foi substituído por apenas rand(0, 9) , e para tabelas varchar , usei nomes completos de estados dos EUA, sem cortá-los ou estendê-los para 6 caracteres. generate_random_string() gera uma string aleatória de 10 caracteres.

Então eu corri no MySQL:

  • SET SESSION query_cache_type=0;
  • Para a tabela jan_int :
    • SELECT count(*) FROM jan_int WHERE myindex = 5;
    • SELECT BENCHMARK(1000000000, (SELECT count(*) FROM jan_int WHERE myindex = 5));
  • Para outras tabelas, o mesmo que acima, com myindex = 'califo' para tabelas char e myindex = 'california' para tabelas varchar .

Tempos da consulta BENCHMARK em cada tabela:

  • jan_int: 21.30 sec
  • jan_int_index: 18,79 seg
  • jan_char: 21,70 seg
  • jan_char_index: 18,85 seg
  • jan_varchar: 21.76 seg
  • jan_varchar_index: 18.86 seg

Em relação aos tamanhos de tabela e índice, aqui está a saída do show table status from janperformancetest; (com algumas colunas não mostradas):

 |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Collation | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | jan_int | InnoDB | 10 | Dynamic | 9739094 | 43 | 422510592 | 0 | 0 | 4194304 | NULL | utf8mb4_unicode_520_ci | | jan_int_index | InnoDB | 10 | Dynamic | 9740329 | 43 | 420413440 | 0 | 132857856 | 7340032 | NULL | utf8mb4_unicode_520_ci | | jan_char | InnoDB | 10 | Dynamic | 9726613 | 51 | 500170752 | 0 | 0 | 5242880 | NULL | utf8mb4_unicode_520_ci | | jan_char_index | InnoDB | 10 | Dynamic | 9719059 | 52 | 513802240 | 0 | 202342400 | 5242880 | NULL | utf8mb4_unicode_520_ci | | jan_varchar | InnoDB | 10 | Dynamic | 9722049 | 53 | 521142272 | 0 | 0 | 7340032 | NULL | utf8mb4_unicode_520_ci | | jan_varchar_index | InnoDB | 10 | Dynamic | 9738381 | 49 | 486539264 | 0 | 202375168 | 7340032 | NULL | utf8mb4_unicode_520_ci | |-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 

Minha conclusão é que não há diferença de desempenho para esse caso de uso específico.

Quanto à chave primária, o que quer que faça fisicamente uma linha única deve ser determinado como a chave primária.

Para uma referência como uma chave estrangeira, usar um número inteiro de incremento automático como substituto é uma boa ideia por duas razões principais.
– Primeiro, há menos sobrecarga incorrida na junit normalmente.
– Em segundo lugar, se você precisar atualizar a tabela que contém o varchar exclusivo, a atualização precisará ser transferida para todas as tabelas filhas e atualizar todas elas, bem como os índices, enquanto que, com o substituto int, ela só precisará atualizar o tabela mestre e seus índices.

O inconveniente de usar o substituto é que você poderia permitir a mudança do significado do substituto:

 ex. id value 1 A 2 B 3 C Update 3 to D id value 1 A 2 B 3 D Update 2 to C id value 1 A 2 C 3 D Update 3 to B id value 1 A 2 C 3 B 

Tudo depende do que você realmente precisa se preocupar em sua estrutura e o que significa mais.

Na HauteLook, mudamos muitas das nossas tabelas para usar chaves naturais. Nós experimentamos um aumento real no desempenho. Como você mencionou, muitas de nossas consultas agora usam menos junções, o que torna as consultas mais eficientes. Até usaremos uma chave primária composta se fizer sentido. Dito isto, algumas tabelas são mais fáceis de trabalhar se tiverem uma chave substituta.

Além disso, se você estiver permitindo que as pessoas gravem interfaces em seu database, uma chave substituta poderá ser útil. A terceira parte pode confiar no fato de que a chave substituta mudará apenas em circunstâncias muito raras.

Casos comuns em que um AUTO_INCREMENT substituto dói:

Um padrão de esquema comum é um mapeamento de muitos para muitos :

 CREATE TABLE map ( id ... AUTO_INCREMENT, foo_id ..., bar_id ..., PRIMARY KEY(id), UNIQUE(foo_id, bar_id), INDEX(bar_id) ); 

O desempenho desse padrão é muito melhor, especialmente ao usar o InnoDB:

 CREATE TABLE map ( # No surrogate foo_id ..., bar_id ..., PRIMARY KEY(foo_id, bar_id), INDEX (bar_id, foo_id) ); 

Por quê?

  • As chaves secundárias do InnoDB precisam de uma pesquisa extra; movendo o par para o PK, isso é evitado em uma direção.
  • O índice secundário é “cobrindo”, portanto, não precisa da consulta extra.
  • Esta tabela é menor porque se livrou do id e de um índice.

Outro caso ( país ):

 country_id INT ... -- versus country_code CHAR(2) CHARACTER SET ascii 

Geralmente, o novato normaliza o country_code em um INT 4 bytes, em vez de usar uma string de 2 bytes ‘natural’, quase imutável. Mais rápido, menor, menos JOINs, mais legível.

Eu enfrentei o mesmo dilema. Fiz um DW (Constellation schema) com 3 tabelas de fatos, acidentes rodoviários, veículos em acidentes e acidentes em acidentes. Os dados incluem todos os acidentes registrados no Reino Unido de 1979 a 2012 e 60 tabelas de dimensões. Todos juntos, cerca de 20 milhões de registros.

Relacionamentos de tabelas de fatos:

 +----------+ +---------+ | Accident |>--------<| Vehicle | +-----v----+ 1 * +----v----+ 1| |1 | +----------+ | +---<| Casualty |>---+ * +----------+ * 

RDMS: MySQL 5.6

Nativamente, o índice de acidentes é um varchar (números e letras), com 15 dígitos. Tentei não ter chaves substitutas, uma vez que os índices de acidentes nunca mudariam. Em um computador i7 (8 núcleos), o DW ficou lento demais para consultar após 12 milhões de registros de carga, dependendo das dimensões. Depois de vários retrabalhos e adicionando chaves substitutas, obtive um aumento médio de 20% no desempenho da velocidade. No entanto, para baixo ganho de desempenho, mas tentativa válida. Estou trabalhando em sintonia e clusterização do MySQL.

Não tenho certeza sobre as implicações de desempenho, mas parece que um possível comprometimento, pelo menos durante o desenvolvimento, seria include tanto a chave “substituta” inteira, auto-incrementada, quanto a chave “natural” desejada e única. Isso lhe daria a oportunidade de avaliar o desempenho, bem como outros possíveis problemas, incluindo a mudança de chaves naturais.

A questão é sobre o MySQL, então eu digo que há uma diferença significativa. Se era sobre o Oracle (que armazena números como string – sim, eu não podia acreditar no começo), então não fazia muita diferença.

Armazenamento na tabela não é o problema, mas atualizando e referindo-se ao índice é. Consultas envolvendo a procura de um registro com base em sua chave primária são freqüentes – você quer que elas ocorram o mais rápido possível, porque acontecem com tanta frequência.

A coisa é uma CPU lida com inteiros de 4 bytes e 8 bytes naturalmente, em silício . É REALMENTE rápido para comparar dois inteiros – isso acontece em um ou dois ciclos de clock.

Agora olhe para uma string – ela é composta de muitos caracteres (mais de um byte por caractere atualmente). Comparar duas strings por precedência não pode ser feito em um ou dois ciclos. Em vez disso, os caracteres das cadeias devem ser iterados até que uma diferença seja encontrada. Tenho certeza de que existem truques para torná-lo mais rápido em alguns bancos de dados, mas isso é irrelevante aqui, porque uma comparação int é feita naturalmente e rapidamente em silício pela CPU.

Minha regra geral – toda chave primária deve ser um INT autoincrementing especialmente em aplicativos OO usando um ORM (Hibernate, Datanucleus, o que for) onde há muitos relacionamentos entre objects – eles geralmente sempre serão implementados como um simples FK e a habilidade para o O database para resolver esses problemas é importante para a capacidade de resposta do seu aplicativo.

Como de costume, não há respostas genéricas. ‘Depende!’ e eu não estou sendo faceta. Meu entendimento da pergunta original era de chaves em tabelas pequenas – como Country (código inteiro ou código char / varchar), sendo uma chave estrangeira para uma tabela potencialmente enorme como endereço / tabela de contatos.

Há dois cenários aqui quando você deseja dados do database. A primeira é uma lista / tipo de pesquisa de consulta na qual você deseja listar todos os contatos com códigos ou nomes de estado e país (os ids não ajudarão e, portanto, precisarão de uma pesquisa). O outro é um cenário get na chave primária, que mostra um único registro de contato, no qual o nome do estado, país, precisa ser mostrado.

Para este último, provavelmente não importa em que se baseia o FK, pois estamos reunindo tabelas para um único registro ou alguns registros e para leituras de chaves. O cenário anterior (pesquisa ou lista) pode ser afetado por nossa escolha. Como é necessário mostrar o país (pelo menos um código reconhecível e talvez até a própria pesquisa inclua um código de país), não ter que ingressar em outra tabela por meio de uma chave substituta pode potencialmente (estou apenas sendo cauteloso aqui porque não testei realmente isso, mas parece altamente provável) melhora o desempenho; não obstante o fato de que certamente ajuda com a pesquisa.

Como os códigos são pequenos em tamanho – não mais que 3 caracteres geralmente para país e estado, pode ser correto usar as chaves naturais como foreign keys neste cenário.

O outro cenário em que as chaves dependem de valores de varchar mais longos e talvez em tabelas maiores; a chave substituta provavelmente tem a vantagem.

Permita-me dizer sim, definitivamente há uma diferença, levando em consideração o escopo do desempenho (definição Out of the box):

1- Usar substituto int é mais rápido no aplicativo porque você não precisa usar ToUpper (), ToLower (), ToUpperInvarient () ou ToLowerInvarient () em seu código ou em sua consulta e essas 4 funções têm diferentes comparações de desempenho. Veja as regras de desempenho da Microsoft sobre isso. (desempenho da aplicação)

2- Usar o substituto int garante não alterar a chave ao longo do tempo. Mesmo os códigos de país podem mudar, veja Wikipedia como os códigos ISO mudaram ao longo do tempo. Isso levaria muito tempo para alterar a chave primária das subtrees. (desempenho de manutenção de dados)

3- Parece haver problemas com soluções ORM, como o NHibernate, quando o PK / FK não é int. (desempenho do desenvolvedor)