Início / coding / Perguntas / 79543385
Accepted
mgregorelli
Asked: 2025-03-29 22:32:36 +0800 CST

Use uma chave primária auto_increment composta por motivos de desempenho

  • 772

Tenho uma tabela 'comentários' com uma chave primária int auto_increment e referências ao autor e à postagem (author_id, post_id).

Como costumo fazer a junção com a tabela 'posts' por motivos óbvios e raramente com autores (apenas no histórico do usuário, raramente usado), estou pensando em remover o status da chave primária em 'comment_id' e criar uma chave primária composta como (post_id, comment_id) com a ideia de conseguir buscar os comentários muito rápido, já que agora eles são armazenados ordenados graças à chave primária líder.

notas:

  • Estou usando o mariadb 11.7
  • No momento, tenho um índice secundário em 'post_id', mas isso implica que tenho que acessar muito mais páginas, pois os comentários referentes a uma postagem específica estão espalhados na tabela.
  • 'comment_id' é necessário agora e depois porque tenho que permitir vários comentários de um usuário em uma postagem e ele se tornaria indexado secundariamente, pois ainda é necessário para algumas operações.
  • meu exemplo é uma simplificação das minhas tabelas reais, mas deve explicar bem o conceito.
  • esta tabela não é muito escrita (inserções, atualizações), mas é muito lida (sim, eu sei sobre memcached)

Espero receber algumas ideias interessantes.

sql

1 respostas

  1. Best Answer

    Se sua prioridade é otimizar junções de poststabela para commentstabela, então pode ser uma pequena vantagem fazer com que a chave primária commentstenha um post_id.

    CREATE TABLE comments (
  post_id INT,
  author_id INT,
  -- presumably some other columns for comment content, date, etc.
  PRIMARY KEY (post_id, author_id),
  FOREIGN KEY (post_id) REFERENCES posts(post_id),
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

    Quando uma junção pesquisa a linha em commentspor post_id, ela pode usar o índice de chave primária, ou seja, o índice clusterizado, somente se post_idfor a coluna inicial da chave primária.

    Se você tivesse as colunas da chave primária composta invertidas, você ainda poderia fazer uma pesquisa eficiente post_idse aquela coluna fosse indexada. Qualquer coluna em uma restrição de chave estrangeira no InnoDB é indexada automaticamente.

    CREATE TABLE comments (
  author_id INT,
  post_id INT,
  -- presumably some other columns for comment content, date, etc.
  PRIMARY KEY (author_id, post_id),
  FOREIGN KEY (post_id) REFERENCES posts(post_id), -- creates an index on post_id
  FOREIGN KEY (author_id) REFERENCES authors(author_id) -- PK is the index; no extra index is created 
);

    Uma pesquisa por post_idneste último caso é quase tão boa. Ela encontra entradas de índice para um dado post_idjunto no índice secundário. Então, se sua consulta exigir outras colunas que não estejam naquele índice, ela terá que seguir a referência PK e carregar essas páginas. Mas apenas para o subconjunto de entradas correspondidas pelo post_idíndice, então essa sobrecarga é pequena.

    Honestamente, você está no território das micro-otimizações. É bom estar atento às oportunidades de otimização, mas tenha em mente que algumas otimizações, embora ofereçam um benefício maior que zero no sentido estrito, esse benefício pode ser tão pequeno para seu aplicativo que não vale a pena fazer a mudança.

    Como você pode saber se vale a pena? Experimente ambos os designs em um ambiente de teste. Meça o desempenho, usando uma consulta e quantidade de dados que são esperados para seu aplicativo. Em seguida, compare os resultados das medições de desempenho.

    A diferença no desempenho pode ser pequena o suficiente para ser insignificante para seu aplicativo.

    Por exemplo, suponha que a consulta por comentários leve 18 milissegundos em uma implementação e 22 milissegundos com a outra implementação. O requisito de carregamento da sua página é que seja abaixo de 100 milissegundos. Nesse caso, ambas as implementações satisfazem o requisito, e mudar uma implementação pela outra não faz diferença material.

    • 0

relate perguntas