Nosso banco de dados MySQL da web analytics contém uma tabela de resumo que é atualizada ao longo do dia conforme a nova atividade é importada. Usamos ON DUPLICATE KEY UPDATE para que o resumo substitua os cálculos anteriores, mas estão tendo dificuldades porque uma das colunas na UNIQUE KEY da tabela de resumo é um FK opcional e contém valores NULL.

Estes NULLs significam "não presentes, e todos esses casos são equivalentes". É claro que o MySQL geralmente trata NULLs como significando "desconhecido, e todos esses casos não são equivalentes".

Estrutura básica é a seguinte:

Uma tabela "Atividade" contendo uma entrada para cada sessão, cada uma pertencente a uma campanha, com IDs de filtro e transação opcionais para algumas entradas.

CREATE TABLE `Activity` (
    `session_id` INTEGER AUTO_INCREMENT
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `transaction_id` INTEGER DEFAULT NULL
    , PRIMARY KEY (`session_id`)
);

Uma tabela "Resumo" contendo distribuições diárias do número total de sessões na tabela de atividades e o número total de sessões que contêm um ID de transação. Esses resumos são divididos, com um para cada combinação de campanha e filtro (opcional). Esta é uma tabela não transacional usando MyISAM.

CREATE TABLE `Summary` (
    `day` DATE NOT NULL
    , `campaign_id` INTEGER NOT NULL
    , `filter_id` INTEGER DEFAULT NULL
    , `sessions` INTEGER UNSIGNED DEFAULT NULL
    , `transactions` INTEGER UNSIGNED DEFAULT NULL
    , UNIQUE KEY (`day`, `campaign_id`, `filter_id`)
) ENGINE=MyISAM;

A consulta de resumo real é algo como o seguinte, contando o número de sessões e transações, depois agrupando por campanha e filtro (opcional).

INSERT INTO `Summary` 
    (`day`, `campaign_id`, `filter_id`, `sessions`, `transactions`)
    SELECT `day`, `campaign_id`, `filter_id
        , COUNT(`session_id`) AS `sessions`
        , COUNT(`transaction_id` IS NOT NULL) AS `transactions`
    FROM Activity
    GROUP BY `day`, `campaign_id`, `filter_id`
ON DUPLICATE KEY UPDATE
    `sessions` = VALUES(`sessions`)
    , `transactions` = VALUES(`transactions`)
;

Tudo funciona muito bem, exceto pelo resumo dos casos em que o filter_id é NULL. Nesses casos, a cláusula ON DUPLICATE KEY UPDATE não corresponde à linha existente e uma nova linha é gravada sempre. Isso se deve ao fato de que "NULL! = NULL". O que precisamos, no entanto, é "NULL = NULL" ao comparar as chaves exclusivas.

Estou à procura de ideias para soluções alternativas ou feedback sobre as que surgiram até agora. Soluções alternativas que temos pensado até agora seguem.

Exclua todas as entradas de resumo que contenham um valor de chave NULL antes de executar a sumarização. (Isso é o que estamos fazendo agora) Isso tem o efeito colateral negativo de retornar resultados com dados ausentes se uma consulta for executada durante o processo de resumo.

Altere a coluna DEFAULT NULL para DEFAULT 0, o que permite que a UNIQUE KEY seja correspondida de forma consistente. Isso tem o efeito colateral negativo de complicar demais o desenvolvimento de consultas na tabela de resumo. Isso nos obriga a usar um monte de "CASE filter_id = 0 ENTÃO NULL ELSE filter_id END", e faz a junção desajeitada já que todas as outras tabelas possuem NULLs reais para o filter_id.

Crie uma exibição que retorne "CASE filter_id = 0 THEN NULL ELSE filter_id END" e usando essa exibição em vez da tabela diretamente. A tabela de resumo contém algumas centenas de milhares de linhas, e foi-me dito que o desempenho da visualização é bastante baixo.

Permita que as entradas duplicadas sejam criadas e exclua as entradas antigas após a conclusão do resumo. Tem problemas semelhantes para eliminá-los antes do tempo.

Adicione uma coluna substituta que contenha 0 para NULL e use esse substituto na UNIQUE KEY (na verdade, poderíamos usar a PRIMARY KEY se todas as colunas forem NOT NULL).
Essa solução parece razoável, exceto que o exemplo acima é apenas um exemplo; o banco de dados real contém meia dúzia de tabelas de resumo, uma das quais contém quatro colunas anuláveis ​​na UNIQUE KEY. Existe a preocupação de alguns de que a sobrecarga é demais.

Você tem uma solução melhor, estrutura de tabela, processo de atualização ou melhor prática do MySQL que pode ajudar?

EDIT: Para esclarecer o "significado de nulo"

Considera-se que os dados nas linhas de resumo que contêm colunas NULL pertencem unicamente no sentido de ser uma única linha "coligada" em relatórios de resumo, resumindo os itens para os quais esse ponto de dados não existe ou é desconhecido. Portanto, dentro do contexto da própria tabela de resumo, o significado é "a soma das entradas para as quais nenhum valor é conhecido". Dentro das tabelas relacionais, por outro lado, estes são verdadeiramente resultados NULL.

A única razão para colocá-los em uma chave exclusiva na tabela de resumo é permitir a atualização automática (por ON DUPLICATE KEY UPDATE) ao recalcular os relatórios de resumo.

Talvez a melhor maneira de descrevê-lo seja pelo exemplo específico de que uma das tabelas de resumo agrupa os resultados geograficamente pelo prefixo do código postal do endereço comercial fornecido pelo respondente. Nem todos os questionados fornecem um endereço comercial, portanto, o relacionamento entre a transação e a tabela de endereços é muito NULL. Na tabela de resumo para esses dados, uma linha é gerada para cada prefixo de código postal, contendo o resumo dos dados nessa área. Uma linha adicional é gerada para mostrar o resumo dos dados para os quais nenhum prefixo de código postal é conhecido.

Alterar o restante das tabelas de dados para ter um valor 0 "THERE_IS_NO_ZIP_CODE" explícito e colocar um registro especial na tabela ZipCodePrefix que representa esse valor é inadequado - esse relacionamento é realmente NULL.