mysql: use SET ou muitas colunas?

Estou usando PHP e MySQL. Tenho registros para:

  • events com vários “tipos de events” que são hierárquicos (os events podem ter várias categorias e subcategorias, mas há uma quantidade fixa de tais categorias e subcategorias) (timestamped)

Qual é a melhor maneira de configurar a mesa? Devo ter um monte de colunas (30 ou mais) com enums para sim ou não indicando adesão nessa categoria? ou devo usar o tipo de dados do MySQL SET? http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

Basicamente eu tenho uma performance em mente e eu quero ser capaz de recuperar todas as ids dos events para uma determinada categoria. Apenas procurando uma visão sobre a maneira mais eficiente de fazer isso.

Parece que você está principalmente preocupado com o desempenho.

Algumas pessoas sugeriram dividir em 3 tabelas (tabela de categorias mais uma tabela de referência cruzada simples ou uma maneira mais sofisticada de modelar a hierarquia da tree, como o conjunto nested ou o caminho materializado), que é a primeira coisa que pensei quando li sua pergunta .

Com índices, uma abordagem totalmente normalizada como essa (que adiciona dois JOINs) ainda terá desempenho de leitura “muito bom”. Uma questão é que um INSERT ou UPDATE para um evento agora também pode include um ou mais INSERT / UPDATE / DELETEs na tabela de referência cruzada, que em MyISAM significa que a tabela de referência cruzada está bloqueada e no InnoDB significa que as linhas estão bloqueadas, então, se o seu database estiver ocupado com um número significativo de gravações, você terá problemas de contenção maiores do que se apenas as linhas de events estivessem bloqueadas.

Pessoalmente, eu tentaria essa abordagem totalmente normalizada antes de otimizar. Mas, assumirei que você sabe o que está fazendo, que seus pressupostos são corretos (as categorias nunca mudam) e você possui um padrão de uso (muitas escritas) que exige uma estrutura plana menos normalizada. Isso é totalmente bom e é parte do que o NoSQL é sobre.

SET vs “muitas colunas”

Então, no que diz respeito à sua pergunta real “SET vs muitas colunas”, posso dizer que trabalhei com duas empresas com engenheiros inteligentes (cujos produtos eram aplicativos web CRM … um era realmente gerenciamento de events) e ambos usou a abordagem “muitas colunas” para este tipo de dados de configuração estática.

Meu conselho seria pensar sobre todas as consultas que você estará fazendo nesta tabela (ponderada pela sua freqüência) e como os índices funcionariam.

Primeiro, com a abordagem “muitas colunas”, você precisará de índices em cada uma dessas colunas para que você possa fazer SELECT FROM events WHERE CategoryX = TRUE . Com os índices, essa é uma consulta super-rápida.

Versus com SET, você deve usar bit a bit AND (&), LIKE ou FIND_IN_SET () para fazer essa consulta. Isso significa que a consulta não pode usar um índice e deve fazer uma pesquisa linear de todas as linhas (você pode usar EXPLAIN para verificar isso). Consulta lenta!

Essa é a razão principal SET é uma má idéia – seu índice só é útil se você estiver selecionando por grupos exatos de categorias. SET funciona muito bem se você selecionasse categorias por evento, mas não o contrário.

O problema primário com a abordagem de “muitas colunas” menos normalizadas (versus totalmente normalizado) é que ele não escala. Se você tem 5 categorias e elas nunca mudam, bem, mas se você tiver 500 e as está mudando, é um grande problema. No seu cenário, com cerca de 30 que nunca mudam, a principal questão é que existe um índice em cada coluna, portanto, se você estiver fazendo gravações freqüentes, essas consultas ficam mais lentas por causa do número de índices que precisam ser atualizados. Se você escolher essa abordagem, você pode querer verificar o log da consulta lenta do MySQL para se certificar de que não há consultas atrasadas em atraso por causa da contenção em horários de trabalho intensos.

No seu caso, se o seu é um aplicativo web típico liso e pesado, acho que ir com a abordagem de “muitas colunas” (como os dois produtos CRM, pelo mesmo motivo) provavelmente é sã. É definitivamente mais rápido do que SET para essa consulta SELECT.

TL; DR Não use SET porque a consulta “selecionar events por categoria” será lenta.

A relação entre events e tipos / categorias de events é um relacionamento de muitos a muitos , como o eco diz , mas uma tabela xref simples irá deixar você com um problema: se você deseja consultar todos os descendentes de qualquer nó dado, então você deve fazer vários consultas recursivas. Em uma tree profunda, isso será muito ineficiente.

Então, quando você diz “recuperar todos os IDs para uma determinada categoria”, se você quer dizer todos os descendentes, então você deseja usar um modelo ajustado nested :

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

O modelo Nested Set torna as atualizações escritas um pouco mais lentas, mas facilita a recuperação de subtrees:

insira a descrição da imagem aqui

  • Para obter a sub-tree Televisions , você consulta para todas as categorias left >= 2 e right < = 9 .
  • Os nós da folha sempre foram left = right - 1
  • Você pode encontrar a contagem de descendentes sem puxar essas linhas: (right - left - 1)/2
  • Encontrar caminhos de inheritance e profundidade também é muito fácil (material de consulta único). Veja o artigo para detalhes completos.

Você pode tentar usar uma tabela de referência cruzada (Xref), para criar uma relação de muitos para muitos entre seus events e seus tipos.

 create table event_category_event_xref ( event_id int, event_category_id int, foreign key(event_id) references event(id), foreign key (event_category_id) references event_category(id) ); 

A associação de evento / categoria é definida por registros nesta tabela. Então, se você tiver um registro com {event_id = 3, event_category_id = 52} , significa que o evento # 3 está na categoria # 52. Da mesma forma, você pode ter registros para {event_id = 3, event_category_id = 27} , e assim por diante.

É bom que o número de categorias seja resolvido. Se não fosse, você não poderia usar qualquer abordagem.

Verifique o porquê você não deve usar SET na página que você ligou. Eu acho que isso deve dar-lhe um guia abrangente.

Eu acho que o mais importante é sobre índices. Além disso, modificar um SET é um pouco mais complexo.