Eu tenho várias tabelas onde os registros podem ser identificados exclusivamente com vários campos de negócios amplos. No passado, usei esses campos como um PK, com os seguintes benefícios em mente:
- Simplicidade; não há campos estranhos e apenas um índice
- O agrupamento permite junções de mesclagem rápidas e filtros baseados em intervalo
No entanto, ouvi um caso feito para criar um IDENTITY INTPK sintético e, em vez disso, impor a chave de negócios com uma UNIQUErestrição separada. A vantagem é que o PK estreito resulta em índices secundários muito menores.
Se uma tabela não tiver índices além do PK, não vejo nenhuma razão para favorecer a segunda abordagem, embora em uma tabela grande seja provavelmente melhor assumir que os índices podem ser necessários no futuro e, portanto, favorecer o PK sintético estreito . Estou perdendo alguma consideração?
Aliás, não estou argumentando contra o uso de chaves sintéticas em data warehouses, estou apenas interessado em quando usar um único PK amplo e quando usar um PK estreito mais um UK amplo.
Não há desvantagem significativa usando a chave natural como o índice clusterizado
A desvantagem seria o aumento das divisões de página, pois as inserções de dados seriam distribuídas pelos dados, em vez de no final.
Onde você tem índices FKs ou NC, o uso de um índice clusterizado crescente e numérico tem vantagens. Você repete apenas alguns bytes de dados por entrada NC ou FK, não a chave natural/business while.
Quanto ao porquê, leia também os 5 artigos do Google
Observe que evitei o uso de "chave primária".
Você pode ter o índice clusterizado na chave substituta, mas manter o PK nas regras de negócios, mas como não clusterizado. Apenas certifique-se de que o cluster é exclusivo porque o SQL adicionará um "uniquificador" para torná-lo único.
Finalmente, pode fazer sentido ter uma chave substituta, mas não cegamente em todas as tabelas : muitas tabelas não precisam de uma, ou onde uma chave composta das tabelas pai será suficiente
Embora eu arrisque afirmar o óbvio, um índice em uma chave substituta (um número de identificação) é útil se você precisar localizar coisas por seu número de identificação. Os usuários não vão lidar com o número de identificação; eles vão lidar com texto legível por humanos. Portanto, você precisa passar muito o texto e seu número de identificação, para que a interface do usuário possa exibir o texto e operar no número de identificação.
O dbms usará esse tipo de índice para suportar chaves estrangeiras, se você as definir dessa maneira.
Às vezes, você pode melhorar o desempenho usando números de identificação como chaves estrangeiras, mas não é uma melhoria absoluta. Em nosso sistema OLTP, as chaves estrangeiras que usam chaves naturais superaram as chaves estrangeiras que usam números de identificação em um conjunto de testes de cerca de 130 (eu acho) consultas representativas. (Como as informações importantes geralmente são carregadas nas chaves, o uso de chaves naturais evitou muitas junções.) A aceleração média foi um fator de 85 (junções usando números de identificação levaram 85 vezes mais para retornar linhas).
Testes mostraram que junções em números de id não funcionariam mais rápido do que leituras em chaves naturais em nosso banco de dados até que certas tabelas atingissem muitos milhões de linhas. A largura da linha tem muito a ver com isso - linhas mais largas significam que menos linhas cabem em uma página, então você tem que ler mais páginas para obter 'n' linhas. Quase todas as nossas tabelas estão em 5NF; a maioria das tabelas são bastante estreitas.
No momento em que as junções começam a executar leituras simples aqui , colocar tabelas e índices críticos em um disco de estado sólido pode nivelar o desempenho em centenas de milhões de linhas.
Eu tenho um banco de dados oltp inteiro projetado usando colunas de identidade para clustering + pk. Funciona muito rápido em inserts/buscas, mas eu vi alguns problemas:
1. a opção de preenchimento de índice é inútil porque as inserções acontecem apenas no final do índice
2. mais espaço de armazenamento. Tenho tabelas com dezenas de milhões de registros e 1 int ocupa espaço sozinho. Cada tabela com uma coluna de identidade para seu pk deve ter outro índice para buscas de negócios, portanto, ainda mais armazenamento necessário.
3. escalabilidade. Este é o pior problema. Como cada inserção vai até o final do índice, cada inserção enfatizará apenas o final do índice (alocação, io para gravações etc.). Usando uma chave comercial como uma chave de agrupamento, você pode distribuir as inserções uniformemente no índice. Isso significa que você acabou de eliminar um grande ponto de acesso. Você pode facilmente usar mais arquivos para um índice, cada arquivo em uma unidade separada, cada unidade trabalhando separadamente.
Comecei a mudar minhas tabelas de colunas de identidade para chaves naturais (talvez separadas para clustering e pk). Só me sinto melhor agora.
Eu sugeriria o seguinte (pelo menos para um banco de dados oltp):
1. use como chave de agrupamento as colunas certas na ordem certa para otimizar as consultas mais frequentes
2. use um pk as colunas certas que fazem sentido para sua tabela
Se a chave agrupada não for simples e contiver caracteres (char[], varchar, nvarchar), acho que a resposta é 'depende', você deve analisar individualmente cada caso.
Eu mantenho o seguinte princípio: otimizar para a consulta mais comum, minimizando o pior cenário.
Quase esqueci um exemplo. Eu tenho algumas tabelas que fazem referência a si mesmas. Se essa tabela tiver uma coluna de identidade para sua chave primária, inserir uma linha pode exigir uma atualização e inserir mais de uma linha por vez pode ser difícil, se não impossível (depende do design da tabela).
Do ponto de vista do desempenho, a escolha de qual tecla é a chave "primária" não faz nenhuma diferença. Não há diferença entre usar uma PRIMARY KEY e uma restrição UNIQUE para impor suas chaves.
O desempenho é determinado pela seleção e tipo de índices e outras opções de armazenamento e pela forma como as chaves são usadas em consultas e códigos.