Início / dba / Perguntas / 341720
Accepted
ERFANIUM
Asked: 2024-08-17 06:42:25 +0800 CST

Chave primária única vs. chave primária composta para armazenar dados isolados do usuário

  • 772

Digamos que eu queira armazenar os dados fornecidos pelo usuário em uma tabela chamada post.

  • os dados dos usuários são isolados por user_idcoluna
  • select sum(likes_count) where user_id = $deveria ser rápido
  • select * from post where id = $deveria ser rápido

Você poderia ajudar a comparar esses esquemas em termos de desempenho, indexação e casos de uso típicos?

nota: os valores char(24) são a string hexadecimal ObjectId do MongoDB. eles são exclusivos no banco de dados.

Esquema 1:

usando PRIMARY KEY (id)e índice secundário em(user_id)

CREATE TABLE posts_v1 (
  id CHAR(24) NOT NULL,
  user_id CHAR(24) NOT NULL,
  likes_count INT,
  PRIMARY KEY (id),
  KEY 'user_id_idx' (user_id)
);

usando PRIMARY KEY (user_id, id)e índice exclusivo secundário em(id)

Esquema 2:

CREATE TABLE posts_v2 (
  id CHAR(24) NOT NULL,
  user_id CHAR(24) NOT NULL,
  likes_count INT,
  PRIMARY KEY (user_id, id),
  UNIQUE KEY 'id_idx' (id)
);

Questões:

1- Em quais cenários um esquema teria desempenho melhor que o outro? Por exemplo, consultas que filtram por user_id, id ou ambos.

2- posts_v2O esquema usa menos leituras de página do InnoDB ao consultar por user_id? Presumo que, no caso de posts_v2, a tabela esteja fisicamente agrupada por user_id, para que possamos recuperar todas as postagens dos usuários com menos varreduras de página. Minha suposição é verdadeira?

3- Integridade e manutenção de dados: Há alguma implicação para integridade de dados, manutenção ou evolução futura de esquema com qualquer design?

4- Casos de uso: Quais são os casos de uso ou cargas de trabalho típicos que podem favorecer um design em detrimento de outro?

Agradeço quaisquer insights ou recomendações que você possa fornecer!

mysql

2 respostas

  1. Começando pela pergunta nº 3 – Integridade dos dados: Não, esses dois esquemas não são iguais. Na verdade, elas são tão diferentes que outras questões não são realmente relevantes.

    A tabela com PK ativado idteria valor único no idcampo:

    insert into posts_v1 (id, user_id) values(1, 'john')
insert into posts_v1 (id, user_id) values(2, 'john')
insert into posts_v1 (id, user_id) values(2, 'mary') -- error

    Mas a tabela com PK ativada user_id,idpode ter valores duplicados no idcampo:

    insert into posts_v2 (id, user_id) values(1, 'john')
insert into posts_v2 (id, user_id) values(2, 'john')
insert into posts_v2 (id, user_id) values(2, 'mary') -- not an error

    Mas se você quiser comparar o PK de campo único com um PK de vários campos: o PK de campo único geralmente tem melhor desempenho. Há exceções, é claro, mas são raros os casos em que o multicampo tem melhor desempenho.

    A resposta mais prática para o seu problema seria: primeiro, decida o que é um natural keyno seu caso, é um campo múltiplo ou pode ser um campo único? Busque o que é melhor do ponto de vista comercial, não do desempenho. Depois disso, você pode adicionar um secundário unique indexno outro campo (ou campos). Dessa forma, você obterá cobertura para ambos os tipos de consultas - por um ou vários campos, independentemente de fazerem parte do PK ou do índice.

    • 1
  2. Best Answer

    O esquema 2 é bom para essas consultas .

    • PRIMARY KEY (user_id, id)é a chave para tornar a consulta "SUM" mais rápida. No InnoDB, o PRIMARY KEYé agrupado com os dados. select sum(likes_count) FROM ... where user_id = $pode simplesmente percorrer um subconjunto da tabela para obter SUMaquele usuário.

    • select * from post where id = $é uma "consulta pontual". Portanto, será rápido o suficiente se ida primeira coluna do PK for um índice secundário. (PK seria mais rápido, mas estamos falando da ordem de um milissegundo.)

    • "os dados dos usuários são isolados pela coluna user_id" - Quanto "isolamento" você deseja? As afirmações acima explicam que você tem o suficiente para desempenho.

    Quanto às 4 perguntas...

    • Geralmente não vale a pena medir o tamanho e a contagem de páginas.
    • Os nós BTree se dividem e se recombinam de maneiras aparentemente aleatórias.
    • Minimizar as páginas necessárias para uma consulta é o verdadeiro objetivo. Refiro-me a isso quando sugiro que isso (user_id, ...)ajude a consulta SUM.
    • Discuto as duas questões acima; se houver outras consultas "caras", irei analisá-las.
    • 0

relate perguntas