Início / dba / Perguntas / 321609
Accepted
Nuno
Asked: 2023-01-01 17:32:25 +0800 CST

Como posso classificar milhares de respostas em um post, seguindo um algoritmo que não é baseado apenas nas colunas da tabela "Respostas"?

  • 772

No meu site, as respostas à postagem são classificadas com base em um algoritmo que depende de vários fatores: Reações, Qualidade da resposta, Reputação do usuário, Relatórios, Respostas à resposta, etc... (esses são apenas alguns exemplos)

Na maioria das postagens, a classificação das respostas é rápida, mas há algumas postagens que têm, digamos, 200 mil respostas e continuam crescendo, e a classificação de todas essas respostas leva mais de 1 segundo.

Estou ciente das VIRTUAL GENERATEDcolunas. No entanto:

  1. Somente se o algoritmo dependesse de colunas da mesma tabela, eu poderia criar uma VIRTUAL GENERATEDcoluna indexada.

  2. Porém, o algoritmo depende de fatores de outras tabelas, como a Reputação do Usuário.

  3. E, infelizmente, se uma tabela tiver uma VIRTUAL GENERATEDcoluna, não será mais possível fazer ALTER ONLINE TABLE. Como essa tabela cresce a cada dia, isso é um grande inconveniente.

Quais são as minhas alternativas para otimizar um algoritmo tão complexo para que ordenar Respostas em Posts que cresceram muito seja rápido/instância?

Como o Reddit classifica milhares e milhares de respostas, por "Top/Best", muito rapidamente?

São principalmente postagens com anos, mas ainda recebendo respostas ativamente.

Eu estava pensando em manter algum tipo de "visão materializada", que seria atualizada a cada nova resposta, ou quando algo acontecesse em uma resposta (como uma Reação, etc). E, em seguida, classifique as respostas com base na chave dessa exibição (juntando-se ao restante). No entanto, isso aumentaria muito o armazenamento necessário e UPDATES/INSERTS no banco de dados. Esta é uma solução razoável?

(Eu uso InnoDB, arquivo por tabela, sem partições. MariaDB 10.8.)

mysql

1 respostas

  1. Best Answer

    Ao inserir uma linha (Resposta, etc.), reúna todas as informações necessárias para a classificação em uma única tabela separada. Como você sugeriu uma visão "materializada"; mas você tem que fazer todo o trabalho.

    Sugerir que o armazenamento de uma resposta deve envolver a chamada de um procedimento armazenado que inserirá as informações nas tabelas atuais, além de adicionar/substituir a linha na tabela semelhante a uma exibição.

    Essa tabela teria apenas as colunas necessárias para a classificação; se possível, teria uma métrica indexada, tornando a classificação trivial.

    Se você precisar RANK()de ou DENSE_RANK()ou PERCENTILE(), isso será razoavelmente fácil de calcular - supondo que você tenha MySQL 8.0 ou MariaDB 10.2.

    Veja pt-online-schema-changeuma maneira de ALTER TABLEpraticamente nenhum tempo de inatividade.

    Uma maneira de melhorar o desempenho é alterar o PRIMARY KEYpara obter clustering. Parece que algo como (user_id, thread_id, post_id)ajudaria. (Presumo que "post_id" inclua postagens e respostas).

    Normalmente as pessoas têm PRIMARY KEY(id)e isso leva a coisas como "respostas" espalhadas pela mesa. Isso leva a muitas E/S. O agrupamento agrupa-os, reduzindo assim drasticamente a E/S.

    • 1

relate perguntas