Nasza baza danych analityki internetowej MySQL zawiera tabelę podsumowującą, która jest aktualizowana przez cały dzień w miarę importowania nowej aktywności. Używamy ON DUPLICATE KEY UPDATE, aby podsumowanie nadpisywało wcześniejsze obliczenia, ale ma trudności, ponieważ jedna z kolumn UNIQUE KEY tabeli podsumowań jest opcjonalna FK i zawiera wartości NULL.

Te NULL mają oznaczać „nieobecne, a wszystkie takie przypadki są równoważne”. Oczywiście MySQL zazwyczaj traktuje NULL jako „nieznane”, a wszystkie takie przypadki nie są równoważne ”.

Podstawowa struktura wygląda następująco:

Tabela „Aktywność” zawierająca wpis dla każdej sesji, każda należąca do kampanii, z opcjonalnym filtrem i identyfikatorami transakcji dla niektórych wpisów.

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`)
);

Tabela „Podsumowanie” zawierająca codzienne zestawienia łącznej liczby sesji w tabeli działań, a także łączną liczbę tych sesji, które zawierają identyfikator transakcji. Te podsumowania są podzielone, z jednym na każdą kombinację kampanii i (opcjonalnie) filtra. Jest to tabela nietransakcyjna wykorzystująca 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;

Rzeczywiste zapytanie podsumowujące jest podobne do następującego, zliczając liczbę sesji i transakcji, a następnie grupując według kampanii i (opcjonalnie) filtr.

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`)
;

Wszystko działa świetnie, z wyjątkiem podsumowania przypadków, w których filter_id ma wartość NULL. W takich przypadkach klauzula ON DUPLICATE KEY UPDATE nie pasuje do istniejącego wiersza, a nowy wiersz jest zapisywany za każdym razem. Wynika to z faktu, że „NULL! = NULL”. Podczas porównywania unikalnych kluczy potrzebujemy jednak „NULL = NULL”.

Szukam pomysłów na obejścia lub opinie na temat tych, które do tej pory wymyśliliśmy. Rozwiązania, o których myśleliśmy do tej pory, następują.

Usuń wszystkie wpisy podsumowania zawierające wartość klucza NULL przed uruchomieniem podsumowania. (To właśnie robimy teraz). Ma to negatywny efekt uboczny w postaci zwracania wyników z brakującymi danymi, jeśli zapytanie jest wykonywane podczas procesu podsumowania.

Zmień kolumnę DEFAULT NULL na DEFAULT 0, co pozwala na spójne dopasowanie UNIQUE KEY. Ma to negatywny efekt uboczny polegający na nadmiernym komplikowaniu rozwoju zapytań w stosunku do tabeli podsumowującej. Zmusza nas to do korzystania z wielu opcji „CASE filter_id = 0 THEN NULL ELSE filter_id END” i sprawia, że ​​dołączanie jest niewygodne, ponieważ wszystkie inne tabele mają rzeczywiste NULL dla id_filtrów.

Utwórz widok, który zwraca „CASE filter_id = 0 THEN NULL ELSE filter_id END” i używając tego widoku zamiast tabeli bezpośrednio. Tabela podsumowująca zawiera kilkaset tysięcy wierszy i powiedziano mi, że wydajność wyświetlania jest dość niska.

Zezwalaj na tworzenie zduplikowanych wpisów i usuń stare wpisy po zakończeniu podsumowania. Ma podobne problemy z usuwaniem ich wcześniej.

Dodaj kolumnę zastępczą, która zawiera 0 dla NULL, i użyj tego surogatu w UNIQUE KEY (właściwie możemy użyć PRIMARY KEY, jeśli wszystkie kolumny NIE są NULL).
To rozwiązanie wydaje się rozsądne, z wyjątkiem tego, że powyższy przykład jest tylko przykładem; aktualna baza danych zawiera pół tuzina tabel podsumowujących, z których jedna zawiera cztery puste kolumny w UNIQUE KEY. Niektórzy obawiają się, że koszty ogólne są zbyt duże.

Czy masz lepsze obejście, strukturę tabeli, proces aktualizacji lub najlepsze praktyki MySQL, które mogą ci pomóc?

EDYCJA: Aby wyjaśnić „znaczenie null”

Dane w wierszach podsumowania zawierających kolumny NULL są uważane za należące do siebie tylko w tym sensie, że są pojedynczym wierszem „catch-all” w raportach podsumowujących, podsumowującym te elementy, dla których ten punkt danych nie istnieje lub jest nieznany. Zatem w kontekście samej tabeli podsumowań znaczenie ma „suma wpisów, dla których nie jest znana żadna wartość”. Z drugiej strony w tabelach relacyjnych są to naprawdę NULL.

Jedynym powodem umieszczenia ich w unikalnym kluczu w tabeli podsumowań jest zezwolenie na automatyczną aktualizację (przez ON DUPLICATE KEY UPDATE) podczas ponownego obliczania raportów podsumowujących.

Być może lepszym sposobem opisania tego jest konkretny przykład, że jedna z tabel podsumowania grupuje wyniki geograficznie według prefiksu kodu pocztowego adresu biznesowego podanego przez respondenta. Nie wszyscy respondenci podają adres firmy, więc relacja między tabelą transakcji i adresów jest całkiem prawidłowa NULL. W tabeli podsumowującej te dane generowany jest wiersz dla każdego prefiksu kodu pocztowego, zawierający podsumowanie danych w tym obszarze. Generowany jest dodatkowy wiersz, aby wyświetlić podsumowanie danych, dla których nie jest znany prefiks kodu pocztowego.

Zmiana reszty tabel danych, aby uzyskać jawną wartość 0-THERE_IS_NO_ZIP_CODE i umieszczenie specjalnego rekordu w tabeli ZipCodePrefix reprezentującej tę wartość, jest niewłaściwa - ta relacja naprawdę ma wartość NULL.