Início / dba / Perguntas / 115271
Accepted
bobocopy
Asked: 2015-09-17 09:00:35 +0800 CST

Qual é o tipo de dados ideal para um campo MD5?

  • 772

Estamos projetando um sistema que é conhecido por ser de leitura pesada (da ordem de dezenas de milhares de leituras por minuto).

  • Há uma tabela namesque serve como uma espécie de registro central. Cada linha tem um textcampo representatione um exclusivo keyque é um hash MD5 daquele representation. 1 Esta tabela tem atualmente dezenas de milhões de registros e espera-se que cresça em bilhões ao longo da vida útil do aplicativo.
  • Existem dezenas de outras tabelas (de esquemas e contagens de registros altamente variados) que fazem referência à namestabela. Qualquer registro em uma dessas tabelas tem a garantia de ter um name_key, que é funcionalmente uma chave estrangeira para a namestabela.

1: Aliás, como você pode esperar, os registros nesta tabela são imutáveis ​​uma vez gravados.

Para qualquer tabela que não seja a namestabela, a consulta mais comum seguirá este padrão:

SELECT list, of, fields 
FROM table 
WHERE name_key IN (md5a, md5b, md5c...);

Eu gostaria de otimizar o desempenho de leitura. Suspeito que minha primeira parada deve ser minimizar o tamanho dos índices (embora eu não me importe de ser provado errado lá).

A pergunta:
Qual é/são os tipos de dados ideais para as colunas keye ? Existe uma razão para usar mais ? ou ?name_key
hex(32)bit(128)BTREEGIN

database-design postgresql

3 respostas

  1. Best Answer

    O tipo de dados uuidé perfeitamente adequado para a tarefa. Ele ocupa apenas 16 bytes em oposição a 37 bytes na RAM para a representação varcharou text. (Ou 33 bytes no disco, mas o número ímpar exigiria preenchimento em muitos casos para torná-lo 40 bytes efetivamente.) E o uuidtipo tem mais algumas vantagens.

    Exemplo:

    SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;

    Ver:

    Você pode considerar outras funções de hashing (um pouco mais baratas) se não precisar do componente criptográfico do md5, mas eu usaria o md5 para o seu caso de uso. md5 está bem estabelecido, muito rápido e seus valores são principalmente somente leitura de qualquer maneira.

    Uma palavra de advertência : Para o seu caso ( immutable once written) uma PK funcionalmente dependente (pseudo-natural) está bem. Mas o mesmo seria uma dor onde as atualizações textsão possíveis. Pense em corrigir um erro de digitação: o PK e todos os índices dependentes, colunas FK em "dezenas de outras tabelas" e outras referências também teriam que mudar. Tabelas e índices inchados, problemas de bloqueio, atualizações lentas, referências perdidas, ...

    Se textpuder mudar em operação normal, um PK substituto seria uma escolha melhor. Sugiro uma bigserialcoluna com um intervalo de -9223372036854775808 a +9223372036854775807. Isso é nove quintilhões duzentos e vinte e três quatrilhões trezentos e setenta e dois trilhões trinta e seis algo bilhões ) valores distintos para "bilhões de linhas". Pode ser uma boa ideia em qualquer caso: 8 em vez de 16 bytes para dezenas de colunas e índices FK!). Ou um UUID aleatório para cardinalidades muito maiores ou sistemas distribuídos. Você sempre pode armazenar o referido md5 (as uuid) adicionalmente para encontrar linhas na tabela principal do texto original rapidamente.
    Relacionado:

    Para sua consulta , consulte:

    E os hífens?

    Se preferir uma representação sem hífens, remova os hífens para exibição:

    SELECT replace('90b7525e-84f6-4850-c2ef-b407fae3f271', '-', '')

    Mas eu não me incomodaria. A representação padrão é muito boa. E o problema realmente não é a representação aqui.

    Se outras partes devem ter uma abordagem diferente e lançar strings sem hífens na mistura, isso também não é problema. O Postgres aceita várias representações de texto razoáveis ​​como entrada para um arquivo uuid. O manual :

    O PostgreSQL também aceita as seguintes formas alternativas para entrada: uso de dígitos maiúsculos, o formato padrão entre chaves, omitindo alguns ou todos os hífens, adicionando um hífen após qualquer grupo de quatro dígitos. Exemplos são:

    A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

    Por que não bytea?

    A md5()função retorna text. Você usaria decode()para converter byteae a representação padrão disso é:

    SELECT decode(md5('Store hash for long string, maybe for index?'), 'hex')

\220\267R^\204\366HP\302\357\264\007\372\343\362q

    Você teria que encode()novamente para obter a representação de texto original:

    SELECT encode(my_md5_as_bytea, 'hex');

    Para completar, os valores armazenados como byteaocupariam 20 bytes na RAM (e 17 bytes no disco, 24 com preenchimento ) devido à sobrecarga internavarlena , que é particularmente desfavorável para tamanho e desempenho de índices simples.

    E os UUIDs "inválidos"?

    Não há UUIDs "inválidos".

    Octeto 13 e 17 codificam uma "versão" e uma "variante" para certos tipos de UUID. Mas o tipo de dados do Postgres uuidaceita todas as quantidades de 128 bits sem considerar "versão" ou "variante". Isso está de acordo com a RFC 4122 :

    Mecanismo de validação:
    Além de determinar se a parte do carimbo de data/hora do UUID está no futuro e, portanto, ainda não pode ser atribuída, não há mecanismo para determinar se um UUID é 'válido'.

    "Versão" e "variante" não fazem sentido/não se aplicam a este caso de uso. Para verificar, fiz um teste rápido:

    db<>fique aqui

    Tudo funciona a favor de umuuidaqui.

    • 67

  2. Eu armazenaria o MD5 em uma coluna textou . varcharNão há diferença de desempenho entre os vários tipos de dados de caracteres. Você pode querer restringir o comprimento dos valores md5 usando varchar(xxx)para garantir que o valor md5 nunca exceda um determinado comprimento.

    Listas IN grandes geralmente não são muito rápidas, é melhor fazer algo assim:

    with md5vals (md5) as (
  values ('one'), ('two'), ('three')
)
select t.*
from the_table t
  join md5vals m on t.name_key  = m.md5;

    Outra opção que às vezes é considerada mais rápida é usar um array:

    select t.*
from the_table t
where name_key = ANY (array['one', 'two', 'three']);

    Como você está apenas comparando por igualdade, um índice BTree regular deve ser bom. Ambas as consultas devem poder usar esse índice (especialmente se estiverem selecionando apenas uma pequena fração das linhas.

    • 2

  3. Outra opção é usar 4 colunas INTEGER ou 2 colunas BIGINT.

    • 0

relate perguntas