Наша база данных веб-аналитики 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, если все столбцы NOT NULL).
Это решение кажется разумным, за исключением того, что приведенный выше пример является лишь примером; фактическая база данных содержит полдюжины сводных таблиц, одна из которых содержит четыре столбца с нулевым значением в UNIQUE KEY. Некоторые обеспокоены тем, что накладные расходы слишком много.
У вас есть лучшее обходное решение, структура таблицы, процесс обновления или наилучшая практика MySQL, которая может помочь?
EDIT: Чтобы прояснить "значение null"
Данные в сводных строках, содержащих столбцы NULL, считаются принадлежащими друг другу только в том смысле, что они представляют собой единую строку "catch-all" в сводных отчетах, суммируя те элементы, для которых эта точка данных не существует или неизвестна, Таким образом, в контексте самой сводной таблицы значение означает "сумма тех записей, для которых неизвестно значение". С другой стороны, в реляционных таблицах это действительно NULL-результаты.
Единственная причина поместить их в уникальный ключ в сводной таблице - это позволить автоматическое обновление (путем включения DUPLICATE KEY UPDATE) при повторном подсчете итоговых отчетов.
Возможно, лучший способ описать его - это конкретный пример того, что одна из групп сводных таблиц географически отображает префикс почтового индекса делового адреса, заданного респондентом. Не все респонденты предоставляют бизнес-адрес, поэтому связь между таблицей транзакций и адресов вполне корректна NULL. В сводной таблице для этих данных создается строка для каждого префикса почтового индекса, содержащего сводку данных в этой области. Создается дополнительная строка, показывающая сводку данных, для которых не известен префикс почтового индекса.
Изменение остальных таблиц данных с явным значением "THERE_IS_NO_ZIP_CODE" 0 и значением специальной записи в таблице ZipCodePrefix, представляющей это значение, является неправильным - это отношение действительно равно NULL.
