5.1 Introdução

A Internet está cheia de preceitos dogmáticos sobre como as chaves devem ser escolhidas e usadas em bancos de dados relacionais. Às vezes, as disputas até se transformam em holivares: chaves naturais ou artificiais devem ser usadas? Números inteiros de incremento automático ou UUIDs?

Depois de ler sessenta e quatro artigos, folhear seções de cinco livros e fazer toneladas de perguntas no IRC e no StackOverflow, eu (Joe "begriffs" Nelson, o autor do artigo original) pareço ter juntado as peças do quebra-cabeça e agora pode reconciliar adversários. Muitas disputas importantes, na verdade, surgem de um mal-entendido do ponto de vista de outra pessoa.

Vamos desmontar o problema e remontá-lo no final. Primeiro, vamos fazer a pergunta - o que é uma "chave"?

Vamos esquecer as chaves primárias por um momento, estamos interessados ​​em uma ideia mais geral. Uma chave é uma coluna (coluna) ou colunas que não possuem valores duplicados nas linhas . Além disso, as colunas devem ser irredutivelmente únicas, ou seja, nenhum subconjunto das colunas tem essa exclusividade.

Mas antes, alguma teoria:

chave primária

Chave primáriausado diretamente para identificar linhas em uma tabela. Deve obedecer às seguintes restrições:

  • A chave primária deve ser única o tempo todo.
  • Deve estar sempre presente na tabela e ter um valor.
  • Não deve mudar seu valor com frequência. Idealmente, não deve alterar o valor .

Normalmente, uma chave primária representa uma única coluna de uma tabela, mas também pode ser uma chave composta que consiste em várias colunas.

chave composta

Chave personalizada- uma combinação de atributos (colunas) que identificam exclusivamente cada linha da tabela. Pode ser todas as colunas, várias e uma. Nesse caso, as linhas que contém os valores desses atributos não devem ser repetidas.

Chave Potencial

Chave candidata- representa a chave composta mínima da relação (tabela), ou seja, um conjunto de atributos que satisfaz uma série de condições:

  • Irredutibilidade : não pode ser reduzido, contém o mínimo conjunto possível de atributos.
  • Uniqueness : Deve ter valores únicos independente da alteração da linha.
  • Presença de um valor : Não deve ter um valor nulo, ou seja, deve ter um valor.

5.2 O curioso caso das chaves primárias

O que acabamos de chamar de “chaves” na seção anterior é comumente referido como “chaves candidatas”. O termo "candidato" implica que todas essas chaves competem pelo papel honorário de "chave primária" (chave primária) e o restante recebe "chaves alternativas" (chaves alternativas).

Demorou um pouco para as implementações de SQL superarem a incompatibilidade entre as chaves e o modelo relacional, e os primeiros bancos de dados foram voltados para o conceito de baixo nível de uma chave primária. As chaves primárias nesses bancos de dados eram necessárias para identificar a localização física de uma linha na mídia de armazenamento sequencial. Aqui está como Joe Celko explica:

O termo "chave" significava uma chave de classificação de arquivo, necessária para executar qualquer operação de processamento em um sistema de arquivos sequencial. Um conjunto de cartões perfurados era lido em uma e apenas uma ordem; era impossível voltar. As primeiras unidades de fita imitavam o mesmo comportamento e não permitiam acesso bidirecional. Ou seja, o Sybase SQL Server original precisava “retroceder” a tabela até o início para ler a linha anterior.

No SQL moderno, você não precisa se concentrar na representação física das informações, relacionamentos de modelo de tabelas e a ordem interna das linhas não é importante. No entanto, mesmo agora o servidor SQL cria por padrão um índice clusterizado para chaves primárias e, de acordo com a antiga tradição, organiza fisicamente a ordem das linhas.

Na maioria dos bancos de dados, as chaves primárias são coisa do passado e fornecem pouco mais do que um reflexo ou localização física. Por exemplo, em uma tabela PostgreSQL, declarar uma chave primária impõe automaticamente uma restrição NOT NULLe define uma chave estrangeira padrão. Além disso, as chaves primárias são as colunas preferenciais para o operador JOIN.

A chave primária não substitui a possibilidade de declarar outras chaves. Ao mesmo tempo, se nenhuma chave for atribuída como primária, a tabela ainda funcionará bem. O raio, em qualquer caso, não o atingirá.

Java webinar

5.3 Encontrando chaves naturais

As chaves discutidas acima são chamadas de "naturais" porque são propriedades do objeto modelado que são interessantes em si mesmas, mesmo que ninguém queira fazer uma chave com elas.

A primeira coisa a lembrar ao examinar uma tabela em busca de possíveis chaves naturais é tentar não ser muito inteligente. O usuário sqlvogel no StackExchange dá o seguinte conselho:

Algumas pessoas têm dificuldade em escolher uma chave "natural" porque surgem com situações hipotéticas em que uma determinada chave pode não ser única. Eles não entendem o próprio significado da tarefa. O significado da chave é definir a regra segundo a qual os atributos em um determinado momento devem ser e sempre serão únicos em uma determinada tabela. A tabela contém dados em um contexto específico e bem compreendido (na "área de assunto" ou "área de discurso"), e o único significado é a aplicação da restrição naquela área específica.

A prática mostra que é necessário introduzir uma restrição de chave quando a coluna é única com os valores disponíveis e permanecerá assim em cenários prováveis. E, se necessário, a restrição pode ser removida (se isso o incomoda, a seguir falaremos sobre a estabilidade das teclas).

Por exemplo, um banco de dados de membros de clubes de hobby pode ter exclusividade em duas colunas - first_name, last_name. Com uma pequena quantidade de dados, as duplicatas são improváveis ​​​​e, antes que surja um conflito real, é bastante razoável usar essa chave.

À medida que o banco de dados cresce e o volume de informações aumenta, a escolha de uma chave natural pode se tornar mais difícil. Os dados que armazenamos são uma simplificação da realidade externa e não contêm alguns aspectos que distinguem os objetos no mundo, como suas coordenadas que mudam com o tempo. Se um objeto carece de qualquer código, como você pode diferenciar duas latas de bebida ou duas caixas de mingau de aveia por sua disposição espacial ou pequenas diferenças de peso ou embalagem?

É por isso que os organismos de normalização criam e aplicam marcas distintivas aos produtos. Os veículos são carimbados com um número de identificação do veículo (VIN) , os livros são impressos com ISBNs e as embalagens de alimentos têm UPCs . Você pode objetar que esses números não parecem naturais. Então, por que eu as chamo de chaves naturais?

A naturalidade ou artificialidade de propriedades únicas em um banco de dados é relativa ao mundo exterior. Uma chave que era artificial ao ser criada em um órgão de normalização ou órgão governamental torna-se natural para nós, pois torna-se padrão no mundo inteiro e/ou é impressa em objetos.

Existem muitos padrões industriais, públicos e internacionais para uma variedade de assuntos, incluindo moedas, idiomas, instrumentos financeiros, produtos químicos e diagnósticos médicos. Aqui estão alguns dos valores que são frequentemente usados ​​como chaves naturais:

  • Códigos de país ISO 3166
  • Códigos de idioma ISO 639
  • Códigos de moeda de acordo com ISO 4217
  • Símbolos de ações ISIN
  • UPC/EAN, VIN, GTIN, ISBN
  • nomes de login
  • endereço de e-mail
  • números de quartos
  • endereço MAC de rede
  • latitude, longitude para pontos na superfície da Terra

Eu recomendo declarar as chaves sempre que possível e razoável, talvez até várias chaves por tabela. Mas lembre-se de que todos os itens acima podem ter exceções.

  • Nem todo mundo tem um endereço de e-mail, embora isso possa ser aceitável em algumas condições do banco de dados. Além disso, as pessoas mudam seus endereços de e-mail de tempos em tempos. (Mais informações sobre a estabilidade das teclas posteriormente.)
  • Os símbolos de ações ISIN mudam de tempos em tempos, por exemplo, os símbolos GOOG e GOOGL não descrevem com precisão a reorganização da empresa do Google para o Alphabet. Às vezes, pode surgir confusão, como com TWTR e TWTRQ, alguns investidores compraram o último por engano durante o IPO do Twitter.
  • Os números da Previdência Social são usados ​​apenas por cidadãos americanos, têm restrições de privacidade e são reutilizados após a morte. Além disso, após o roubo de documentos, as pessoas podem obter novos números. Finalmente, o mesmo número pode identificar uma pessoa e um identificador de imposto de renda.
  • Os códigos postais são uma má escolha para as cidades. Algumas cidades possuem um índice comum, ou vice-versa, são vários índices em uma cidade.

5.4 Chaves artificiais

Dado que a chave é uma coluna com valores únicos em cada linha, uma maneira de criá-la é trapacear - você pode escrever valores únicos fictícios em cada linha. Estas são chaves artificiais: código inventado usado para se referir a dados ou objetos.

É muito importante que o código seja gerado a partir do próprio banco de dados e seja desconhecido para qualquer pessoa, exceto para os usuários do banco de dados. Isso é o que distingue as chaves artificiais das chaves naturais padronizadas.

Enquanto as chaves naturais têm a vantagem de proteger contra linhas duplicadas ou inconsistentes em uma tabela, as chaves artificiais são úteis porque facilitam para humanos ou outros sistemas referirem-se à linha e aceleram pesquisas e junções porque não usam comparações de strings (ou multicolunas).

Substitutos

Chaves artificiais são usadas como âncoras - não importa como as regras e colunas mudem, uma linha sempre pode ser identificada da mesma maneira. A chave artificial utilizada para esse fim é chamada de "chave substituta" e requer atenção especial. Vamos considerar substitutos abaixo.

As chaves artificiais não substitutas são úteis para referenciar uma linha de fora do banco de dados. Uma chave artificial identifica brevemente um dado ou objeto: pode ser especificada como uma URL, anexada a uma fatura, ditada por telefone, obtida em um banco ou impressa em uma placa de carro. (A placa de um carro é uma chave natural para nós, mas projetada pelo governo como uma chave artificial.)

Chaves sintéticas devem ser escolhidas levando em consideração os possíveis meios de transmissão para minimizar erros de digitação e erros. Deve-se observar que a chave pode ser falada, lida impressa, enviada por SMS, lida manuscrita, digitada no teclado e incorporada a uma URL. Além disso, algumas chaves artificiais, como números de cartão de crédito, contêm uma soma de verificação para que, se ocorrerem determinados erros, possam pelo menos ser reconhecidas.

Exemplos:

  • Para placas dos EUA, há regras sobre o uso de caracteres ambíguos, como O e 0.
  • Hospitais e farmácias devem ter cuidado especial, dada a caligrafia dos médicos.
  • Você envia um código de confirmação por mensagem de texto? Não vá além do conjunto de caracteres GSM 03.38.
  • Ao contrário do Base64, que codifica dados de bytes arbitrários, o Base32 usa um conjunto de caracteres limitado que é conveniente para os humanos usarem e manusearem em sistemas de computador mais antigos.
  • Proquints são identificadores legíveis, graváveis ​​e pronunciáveis. Estes são QUINT-uplets PRO-nonciáveis ​​de consoantes e vogais inequivocamente compreendidas.

Lembre-se de que, assim que você apresentar sua chave artificial ao mundo, as pessoas estranhamente começarão a dar atenção especial a ela. Basta olhar para as placas dos "ladrões" ou para o sistema de criação de identificadores pronunciáveis, que se tornou o infame gerador automatizado de maldições.

Mesmo que nos limitemos às teclas numéricas, existem tabus como o décimo terceiro andar. Embora os proquints tenham uma densidade maior de informações por sílaba falada, os números também são bons de várias maneiras: em URLs, teclados de pinos e notas manuscritas, desde que o destinatário saiba que a chave são apenas números.

No entanto, observe que você não deve usar ordem sequencial em chaves numéricas públicas, pois isso permite que você vasculhe os recursos (/videos/1.mpeg, /videos/2.mpeg e assim por diante) e também vaza informações sobre o número dados. Sobreponha uma rede de Feistel em uma sequência de números e preserve a exclusividade enquanto oculta a ordem dos números.

O único argumento contra a declaração de chaves adicionais é que cada nova traz consigo outro índice exclusivo e aumenta o custo de gravação na tabela. Claro, depende de quão importante a exatidão dos dados é para você, mas, provavelmente, as chaves ainda devem ser declaradas.

Também vale a pena declarar várias chaves artificiais, se houver. Por exemplo, uma organização tem candidatos a emprego (candidatos) e funcionários (funcionários). Cada funcionário já foi um candidato e se refere aos candidatos por seu próprio identificador, que também deve ser a chave do funcionário. Outro exemplo, você pode definir o id do funcionário e o nome de login como duas chaves em Employees.

5.5 Chaves substitutas

Como já mencionado, um importante tipo de chave artificial é chamada de "chave substituta". Não precisa ser conciso e passável como outras chaves artificiais, mas é usado como um rótulo interno que sempre identifica a string. Ele é usado no SQL, mas o aplicativo não o acessa explicitamente.

Se você estiver familiarizado com as colunas de sistema do PostgreSQL, poderá pensar em substitutos quase como um parâmetro de implementação de banco de dados (como ctid), que, no entanto, nunca muda. O valor substituto é selecionado uma vez por linha e nunca mais alterado.

As chaves substitutas são ótimas como chaves estrangeiras e as restrições em cascata devem ser especificadas ON UPDATE RESTRICTpara corresponder à imutabilidade do substituto.

Por outro lado, chaves estrangeiras para chaves compartilhadas publicamente devem ser marcadas com ON UPDATE CASCADE, para fornecer o máximo de flexibilidade. Uma atualização em cascata é executada no mesmo nível de isolamento da transação circundante, portanto, não se preocupe com problemas de simultaneidade - o banco de dados ficará bem se você escolher um nível de isolamento estrito.

Não torne as chaves substitutas "naturais". Depois de mostrar o valor da chave substituta para os usuários finais, ou pior, deixá-los trabalhar com esse valor (principalmente por meio de uma pesquisa), você está efetivamente atribuindo um valor à chave. Então, a chave mostrada em seu banco de dados pode se tornar uma chave natural no banco de dados de outra pessoa.

Forçar sistemas externos a usar outras chaves artificiais projetadas especificamente para transmissão nos permite alterar essas chaves conforme necessário para atender às necessidades de mudança, mantendo a integridade referencial interna com substitutos.

Incremento automático INT/BIGINT

O uso mais comum para chaves substitutas é a coluna "bigserial" de incremento automático , também conhecida como IDENTITY . (Na verdade, o PostgreSQL 10 agora suporta a construção IDENTITY, assim como o Oracle, veja CREATE TABLE.)

No entanto, acredito que um inteiro de incremento automático é uma escolha ruim para chaves substitutas. Esta opinião é impopular, então deixe-me explicar.

Desvantagens das chaves seriais:

  • Se todas as sequências começarem em 1 e aumentarem gradualmente, as linhas de tabelas diferentes terão os mesmos valores de chave. Esta opção não é ideal, ainda é preferível usar conjuntos de chaves disjuntos em tabelas, para que, por exemplo, as consultas não possam confundir acidentalmente as constantes JOINe retornar resultados inesperados. (Como alternativa, para garantir que não haja interseções, pode-se construir cada sequência a partir de múltiplos de números primos diferentes, mas isso seria bastante trabalhoso.)
  • A chamada nextval() para gerar uma sequência no SQL distribuído de hoje resulta em todo o sistema não escalando bem.
  • Consumir dados de um banco de dados que também usa chaves sequenciais resultará em conflitos porque os valores sequenciais não serão exclusivos entre os sistemas.
  • Do ponto de vista filosófico, o aumento sequencial dos números está associado a sistemas antigos em que a ordem das linhas estava implícita. Se agora você deseja ordenar as linhas, faça isso explicitamente com uma coluna de carimbo de data/hora ou algo que faça sentido em seus dados. Caso contrário, a primeira forma normal é violada.
  • Razão fraca, mas esses identificadores curtos são tentadores de contar a alguém.

UUID

Vejamos outra opção: usar inteiros grandes (128 bits) gerados de acordo com um padrão aleatório. Algoritmos para gerar esses identificadores universalmente exclusivos (UUIDs) têm uma probabilidade extremamente baixa de escolher o mesmo valor duas vezes, mesmo quando executados em dois processadores diferentes ao mesmo tempo.

Nesse caso, os UUIDs parecem uma escolha natural para usar como chaves substitutas, não é? Se você deseja rotular as linhas de uma maneira única, nada supera um rótulo exclusivo!

Então, por que nem todos os estão usando no PostgreSQL? Existem várias razões inventadas para isso e uma lógica que pode ser contornada, e apresentarei benchmarks para ilustrar meu ponto.

Primeiro, falarei sobre os motivos rebuscados. Algumas pessoas pensam que os UUIDs são strings porque são escritos na notação hexadecimal tradicional com um traço: 5bd68e64-ff52-4f54-ace4-3cd9161c8b7f. De fato, alguns bancos de dados não possuem um tipo uuid compacto (128 bits), mas o PostgreSQL possui e possui um tamanho de dois bigint, ou seja, comparado com a quantidade de outras informações no banco de dados, o overhead é insignificante.

Os UUIDs também são injustamente acusados ​​de serem incômodos, mas quem irá pronunciá-los, digitá-los ou lê-los? Dissemos que faz sentido mostrar chaves artificiais, mas ninguém (por definição) deve ver o UUID substituto. É possível que o UUID seja tratado por um desenvolvedor executando comandos SQL no psql para depurar o sistema, mas isso é tudo. E o desenvolvedor também pode se referir a strings usando chaves mais convenientes, se forem fornecidas.

O problema real com os UUIDs é que valores altamente aleatórios levam à amplificação de gravação devido a gravações de página inteira no log write-ahead (WAL) . No entanto, a degradação do desempenho realmente depende do algoritmo de geração de UUID.

Vamos medir a amplificação de gravação . Na verdade, o problema está em sistemas de arquivos mais antigos. Quando o PostgreSQL grava no disco, ele altera a "página" no disco. Se você desligar o computador, a maioria dos sistemas de arquivos ainda relatará uma gravação bem-sucedida antes que os dados sejam armazenados com segurança no disco. Se o PostgreSQL ingenuamente perceber tal ação como concluída, o banco de dados será corrompido durante a próxima inicialização do sistema.

Como o PostgreSQL não pode confiar na maioria dos sistemas operacionais/sistemas de arquivos/configurações de disco para fornecer continuidade, o banco de dados salva o estado completo da página de disco alterada em um log write-ahead que pode ser usado para recuperar de uma possível falha. A indexação de valores altamente aleatórios, como UUIDs, normalmente envolve várias páginas de disco diferentes e resulta no tamanho total da página (geralmente 4 ou 8 KB) sendo gravado no WAL para cada nova entrada. Essa é a chamada gravação de página inteira (gravação de página inteira, FPW).

Alguns algoritmos de geração de UUID (como o "snowflake" do Twitter ou uuid_generate_v1() na extensão uuid-ossp do PostgreSQL) geram valores monotonicamente crescentes em cada máquina. Essa abordagem consolida as gravações em menos páginas de disco e reduz o FPW.

5.6 Conclusões e recomendações

Agora que vimos os diferentes tipos de chaves e seus usos, quero listar minhas recomendações para usá-las em seus bancos de dados.

Para cada mesa:

  • Defina e declare todas as chaves naturais.
  • Crie uma chave substituta <table_name>_iddo tipo UUID com um valor padrão de uuid_generate_v1(). Você pode até marcá-lo como uma chave primária. Se você adicionar o nome da tabela a esse identificador, isso simplificará JOIN, ou seja, receber JOIN foo USING (bar_id)em vez de JOIN foo ON (foo.bar_id = bar.id). Não passe essa chave para clientes e não a exponha fora do banco de dados.
  • Para tabelas intermediárias que passam por JOIN, declare todas as colunas de chave estrangeira como uma única chave primária composta.
  • Opcionalmente, adicione uma chave artificial que pode ser usada na URL ou outras indicações de referência de string. Use uma grade Feistel ou pg_hashids para mascarar inteiros de incremento automático.
  • Especifique uma restrição em cascata ON UPDATE RESTRICTusando UUIDs substitutos como chaves estrangeiras e para chaves estrangeiras artificiais ON UPDATE CASCADE. Escolha chaves naturais com base em sua própria lógica.

Essa abordagem garante a estabilidade das chaves internas, permitindo e até mesmo protegendo as chaves naturais. Além disso, chaves artificiais visíveis não ficam presas a nada. Tendo entendido tudo corretamente, você não pode ficar preso apenas às “chaves primárias” e usar todas as possibilidades de uso das chaves.