Денормализация таблиц в БД

8.1 Зачем нужна денормализация?

Наиболее дорогостоящая операция с точки зрения вычислительных ресурсов при работе с большими таблицами — соединение. Поэтому, если в одном запросе требуется объединить несколько таблиц, состоящих из многих миллионов строк, СУБД затратит достаточно времени на обработку. Пользователь в это время может выпить кофе. Интерактивность обработки практически исчезает и приближается к таковой для пакетной обработки. Даже хуже, в пакетном режиме пользователь получает все запрошенные данные утром и работает с ними до вечера, подготавливая новые запросы.

Чтобы избежать ситуации сложных соединений таблиц, их денормализуют. Но не произвольно. Существуют правила, позволяющие считать денормализованные таблицы «нормализованными» в соответствии с правилами построения таблиц для хранилищ данных.

В аналитической обработке две основные схемы, считающиеся «нормальными»: «снежинка» и «звезда». Названия хорошо отражают суть и происходят непосредственно из изображения связанных таблиц.

В обоих случаях центральным элементом схемы являются таблицы фактов, содержащие интересующие аналитика события, транзакции, документы и другое. Если в транзакционной БД один документ «размазан» по нескольким таблицам (как минимум по двум: заголовки и строки-содержание), то в таблице фактов каждой строке документа или набору сгруппированных строк соответствует одна запись.

Это можно сделать денормализацией двух упомянутых таблиц.

8.2 Пример денормализации

Теперь можно оценить, насколько упростится запрос к СУБД для определения объёмов продаж муки клиентам «ООО Пирожки» и «ЗАО Ватрушки» за период в нормализованной транзакционной БД.

SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name
FROM
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc
   INNER JOIN customers c ON d.id customer = c.id customer
   INNER JOIN products p ON dl.id product = p.id product
WHERE
   c.name IN (’ООО Пирожки’,	’ЗАО Ватрушки’) AND
   р.name = ’Мука’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

В аналитической БД:

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('ООО Пирожки', 'ЗАО Ватрушки') AND
   p.name = 'Мука' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Вместо сложного соединения между двумя документами и их составом, содержащим миллионы строк, СУБД предоставляет прямую работу с таблицей фактов и легкие соединения с небольшими вспомогательными таблицами, которые можно обойти, зная идентификаторы.

SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Вернёмся к схемам «звезда» и «снежинка». За кадром первого рисунка остались таблицы клиентов, их групп, магазинов, продавцов и товаров. При денормализации эти таблицы, называемые измерениями, соединяются с таблицей фактов. Если таблица фактов ссылается на таблицы-измерения, имеющие ссылки на другие измерения (второго уровня и выше), то такая схема называется «снежинка».

Как можно заметить, для запросов, включающих фильтрацию по группам клиентов, приходится делать дополнительное соединение.

SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

В таком случае денормализацию можно продолжить, опустив измерение второго уровня на первый. Это облегчит запросы к таблице фактов. Такая схема называется «звезда». Она полностью исключает иерархию измерений и необходимость соединения соответствующих таблиц в одном запросе. Число таблиц измерений соответствует числу «лучей» в звезде.

SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

Обратной стороной денормализации всегда является избыточность, которая увеличивает размер БД как в транзакционных, так и аналитических приложениях. Давайте посчитаем примерную дельту в примере преобразования «снежинки» в «звезду».

В некоторых СУБД, например Oracle, нет специальных целочисленных типов на уровне определений схемы БД. В этом случае необходимо использовать универсальный логический тип numeric(N), где N — число хранимых разрядов. Размер хранения такого числа обычно больше, чем у низкоуровневых типов, например «16 битное целое», на 1-3 байта.

Предположим, таблица продаж не использует компрессию данных и содержит около 500 миллионов строк, а количество групп покупателей порядка 1000. В этом случае мы можем использовать в качестве типа идентификатора id_customer_group короткое целое (shortint, smallint), занимающее 2 байта.

Если СУБД поддерживает двухбайтовый целочисленный тип (например, PostgreSQL, SQL Server, Sybase и другие), добавление колонки id_customer_group в таблицу продаж увеличит её размер на 1 гигабайт.

8.3 Когда нужна денормализация?

Давайте рассмотрим несколько распространенных ситуаций, в которых денормализация может быть полезной.

Большое количество соединений таблиц

В случае полностью нормализованной базы данных запросы, требующие соединения до 10 и более таблиц, являются ресурсоемкими и медленными. Для решения этой проблемы может помочь:

• Денормализация может быть достигнута сокращением количества таблиц. Лучше объединить несколько таблиц с небольшим размером, содержащих редко изменяемую (условно-постоянную или нормативно-справочную) информацию, которая тесно связана по смыслу.
• Если для выполнения большого количества запросов требуется объединять более пяти или шести таблиц, рассмотрите вариант денормализации базы данных.
• Денормализация может быть достигнута путем добавления дополнительного поля в одну из таблиц. Это приведет к избыточности данных, поэтому для сохранения целостности БД потребуются дополнительные действия.

Расчетные значения

Зачастую запросы со сложными вычислениями выполняются медленно и потребляют много ресурсов, особенно при использовании группировок и агрегатных функций (Sum, Max и т. д.). В этом случае можно добавить в таблицу 1-2 дополнительных столбца с часто используемыми (и сложно вычисляемыми) расчетными данными.

Например, для определения общей стоимости заказа нужно рассчитать стоимость каждого продукта (по формуле «количество единиц продукта» * «цена единицы продукта» – скидка) и сгруппировать стоимости по заказам. Выполнение такого запроса может занять много времени, если в базе данных хранятся много заказов.

Вместо этого можно на этапе размещения заказа рассчитать его стоимость и сохранить в отдельном столбце таблицы заказов. Таким образом, для получения результата достаточно будет извлечь из этого столбца предварительно рассчитанные значения. Это позволит сэкономить время при выполнении запроса, но требует своевременного обновления данных в этом столбце.

Длинные поля

Если в базе данных есть большие таблицы с длинными полями (Blob, Long и т. д.), то мы можем ускорить выполнение запросов, вынося длинные поля в отдельную таблицу. Например, если мы хотим создать каталог фотографий, включающий в себя профессиональное качество, высокое разрешение и соответствующий размер, то из точки зрения нормализации правильно будет использовать такую структуру таблицы.

• ID фотографии
• ID автора
• ID модели фотоаппарата
• сама фотография (blob-поле)

Представим, сколько времени займет запрос, подсчитывающий количество фотографий, сделанных каким-либо автором. В такой ситуации правильным решением (нарушающим принципы нормализации) будет создание дополнительной таблицы с двумя полями: ID фотографии и blob-поле с самой фотографией. Таким образом, выборки из основной таблицы (без blob-поля) будут происходить моментально. При необходимости просмотра фотографии придется подождать.

Когда денормализация оправдана?

8.4 Плюсы и минусы денормализации

Один из способов оценить целесообразность действий — проанализировать затраты и возможные выгоды. Сколько обойдется денормализованная модель данных?

Для начала нужно определить требования и найти минимальный шаг, удовлетворяющий эти требования. Затем подсчитать затраты и реализовать.

Затраты включают в себя физические аспекты, такие как дисковое пространство, ресурсы для управления структурой и временные задержки при обслуживании. Денормализация приводит к увеличению избыточности данных, что может улучшить производительность, но потребует больше усилий для контроля за связанными данными. Создание приложений станет сложнее, поскольку данные будут повторяться и их труднее будет отслеживать. Осуществление ссылочной целостности также станет непростым заданием.

Достоинства денормализации: высокая производительность при запросах, быстрый ответ, увеличение пропускной способности, удовлетворенность клиентов и производительности, более эффективное использование инструментария внешних разработчиков.

Частота запросов и устойчивость производительности

72% из тысячи ежедневно генерируемых предприятием запросов представляют собой запросы сводных данных, а не детальных. Использование таблицы сводных данных уменьшает время обработки запросов до 6 секунд вместо 4 минут. Это дает экономию в 2500 минут в неделю, даже с учетом 100 минут на поддержку таблицы. Со временем может случиться так, что большая часть запросов будет обращена к детальным данным. В этом случае можно отказаться от таблицы сводных данных, не затрагивая другие процессы.

А так же…

Вышеуказанные критерии не являются единственными факторами, которые необходимо учитывать при принятии решения о дальнейшей оптимизации. Необходимо учитывать и другие параметры, включая приоритеты бизнеса и потребности конечных пользователей. Пользователи должны понимать, как требования пользователей, желающих быстрого выполнения запросов, влияют на техническую сторону архитектуры системы. Для достижения этого понимания лучше всего представить затраты, связанные с созданием таких таблиц и их управлением.

8.5 Как грамотно реализовать денормализацию

Сохранить детальные таблицы

Чтобы не ограничивать возможности базы данных для бизнеса, необходимо придерживаться стратегии сохранения, а не замены. То есть, сохранить детальные таблицы для глубинного анализа и добавить к ним денормализованные структуры. Например, счетчик посещений. Для бизнеса важно знать количество посещений веб-страницы. Для анализа по периодам, странам и т. д. могут понадобиться детальные данные – таблица с информацией о каждом посещении.

Использование триггеров

Можно денормализовать структуру базы данных и при этом пользоваться преимуществами нормализации, если использовать триггеры баз данных для сохранения целостности информации и идентичности дублирующихся данных. Например, при добавлении вычисляемого поля, зависящего от других столбцов, можно повесить триггер, который будет вызывать одну хранимую процедуру. Эта процедура запишет нужные данные в вычисляемое поле. Не забывайте о всех столбцах, от которых зависит вычисляемое поле.

Программная поддержка

Если не использовать встроенные триггеры и хранимые процедуры, то разработчики приложений должны заботиться об обеспечении непротиворечивости данных в денормализованной базе.

Аналогично триггерам, должна быть одна функция, которая будет обновлять все поля, зависящие от изменяемого.

Выводы

При денормализации важно сохранить баланс между повышением скорости работы базы и увеличением риска появления противоречивых данных, между облегчением жизни программистов, пишущих Select'ы, и усложнением задачи тех, кто обеспечивает наполнение и обновление базы. Поэтому денормализацию нужно проводить осторожно и выборочно, только там, где обойтись без нее невозможно.

Если заранее нельзя оценить плюсы и минусы денормализации, то следует изначально реализовать модель с нормализованными таблицами, а потом, для оптимизации проблемных запросов, проводить денормализацию.

Денормализацию следует внедрять постепенно и только для случаев, когда присутствуют повторные выборки связанных данных из разных таблиц. Помните, при дублировании данных количество записей возрастет, но количество чтений уменьшится. Рассчитываемые данные лучше хранить в колонках, чтобы избежать ненужных агрегатных выборок.

Проектирование баз данных