Наша база данных веб-аналитики MySQL содержит сводную таблицу, которая обновляется в течение дня по мере импорта новых действий. Мы используем ON DUPLICATE KEY UPDATE, чтобы суммирование перезаписывало более ранние вычисления, но возникают трудности, поскольку один из столбцов в UNIQUE KEY сводной таблицы является необязательным FK и содержит значения NULL.
Эти значения NULL предназначены для обозначения «отсутствует, и все такие случаи эквивалентны». Конечно, MySQL обычно рассматривает значения NULL как означающие «неизвестно, и все такие случаи не эквивалентны».
Базовая структура выглядит следующим образом:
Таблица «Активность», содержащая запись для каждого сеанса, каждая из которых принадлежит кампании, с необязательным фильтром и идентификаторами транзакций для некоторых записей.
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`)
);
«Сводная» таблица, содержащая ежедневные сводки общего количества сеансов в таблице действий и общее количество тех сеансов, которые содержат идентификатор транзакции. Эти сводки разделены по одному для каждой комбинации кампании и (необязательного) фильтра. Это нетранзакционная таблица, использующая 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;
Фактический запрос суммирования выглядит примерно так: подсчитывается количество сеансов и транзакций, затем группируется по кампаниям и (необязательно) фильтру.
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`)
;
Все отлично работает, за исключением сводки случаев, когда filter_id равен NULL. В этих случаях предложение ON DUPLICATE KEY UPDATE не соответствует существующей строке, и каждый раз записывается новая строка. Это связано с тем, что "NULL != NULL". Однако нам нужно «NULL = NULL» при сравнении уникальных ключей.
Я ищу идеи для обходных путей или отзывы о тех, которые мы придумали до сих пор. Обходные пути, о которых мы думали до сих пор, следуют.
Перед выполнением суммирования удалите все сводные записи, содержащие значение ключа NULL. (Это то, что мы делаем сейчас) Это имеет негативный побочный эффект, возвращая результаты с отсутствующими данными, если запрос выполняется во время процесса суммирования.
Измените столбец DEFAULT NULL на DEFAULT 0, что позволяет последовательно сопоставлять UNIQUE KEY. Это имеет негативный побочный эффект, заключающийся в чрезмерном усложнении разработки запросов к сводной таблице. Это вынуждает нас использовать много «CASE filter_id = 0 THEN NULL ELSE filter_id END» и делает неудобным объединение, поскольку все другие таблицы имеют фактические NULL для filter_id.
Создайте представление, которое возвращает «CASE filter_id = 0 THEN NULL ELSE filter_id END» и используйте это представление вместо таблицы напрямую. Сводная таблица содержит несколько сотен тысяч строк, и мне сказали, что производительность представления довольно низкая.
Разрешить создание повторяющихся записей и удалить старые записи после завершения суммирования. Имеет аналогичные проблемы с удалением их раньше времени.
Добавьте суррогатный столбец, который содержит 0 для NULL, и используйте этот суррогат в UNIQUE KEY (на самом деле мы могли бы использовать PRIMARY KEY, если все столбцы НЕ NULL).
Это решение кажется разумным, за исключением того, что приведенный выше пример является только примером. ; фактическая база данных содержит полдюжины сводных таблиц, одна из которых содержит четыре столбца, допускающих значение NULL, в UNIQUE KEY. Некоторые опасаются, что накладные расходы слишком велики.
У вас есть лучший обходной путь, структура таблицы, процесс обновления или лучшие практики MySQL, которые могут помочь?
РЕДАКТИРОВАТЬ: уточнить «значение нуля»
Данные в сводных строках, содержащих столбцы NULL, считаются связанными друг с другом только в том смысле, что они представляют собой единую «всеобъемлющую» строку в сводных отчетах, суммирующую те элементы, для которых эта точка данных не существует или неизвестна. Таким образом, в контексте самой сводной таблицы значением является «сумма тех записей, для которых неизвестно значение». С другой стороны, в реляционных таблицах это действительно NULL-результаты.
Единственная причина поместить их в уникальный ключ сводной таблицы — разрешить автоматическое обновление (путем ON DUPLICATE KEY UPDATE) при повторном расчете сводных отчетов.
Возможно, лучше всего описать это на конкретном примере, когда одна из сводных таблиц группирует результаты географически по префиксу почтового индекса рабочего адреса, указанному респондентом. Не все респонденты указывают служебный адрес, поэтому связь между транзакцией и таблицей адресов вполне корректно равна NULL. В сводной таблице для этих данных создается строка для каждого префикса почтового индекса, содержащая сводку данных в этой области. Создается дополнительная строка для отображения сводки данных, для которых не известен префикс почтового индекса.
Изменение остальных таблиц данных таким образом, чтобы они имели явное 0-значение «THERE_IS_NO_ZIP_CODE» и размещение специальной записи в таблице ZipCodePrefix, представляющей это значение, является неправильным — эта связь действительно равна NULL.
