8.1 Зачем нужна денормализация?
Наиболее дорогостоящая операция с точки зрения вычислительных ресурсов при работе с большими таблицами — соединение. Поэтому, если в одном запросе требуется объединить несколько таблиц, состоящих из многих миллионов строк, СУБД затратит достаточно времени на обработку. Пользователь в это время может выпить кофе. Интерактивность обработки практически исчезает и приближается к таковой для пакетной обработки. Даже хуже, в пакетном режиме пользователь получает все запрошенные данные утром и работает с ними до вечера, подготавливая новые запросы.
Чтобы избежать ситуации сложных соединений таблиц, их денормализуют. Но не произвольно. Существуют правила, позволяющие считать денормализованные таблицы «нормализованными» в соответствии с правилами построения таблиц для хранилищ данных.
В аналитической обработке две основные схемы, считающиеся «нормальными»: «снежинка» и «звезда». Названия хорошо отражают суть и происходят непосредственно из изображения связанных таблиц.
В обоих случаях центральным элементом схемы являются таблицы фактов, содержащие интересующие аналитика события, транзакции, документы и другое. Если в транзакционной БД один документ «размазан» по нескольким таблицам (как минимум по двум: заголовки и строки-содержание), то в таблице фактов каждой строке документа или набору сгруппированных строк соответствует одна запись.
Это можно сделать денормализацией двух упомянутых таблиц.
![](https://cdn.javarush.com/images/article/07bd1f49-716c-4226-8326-886e52968e41/1024.jpeg)
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
Вернёмся к схемам «звезда» и «снежинка». За кадром первого рисунка остались таблицы клиентов, их групп, магазинов, продавцов и товаров. При денормализации эти таблицы, называемые измерениями, соединяются с таблицей фактов. Если таблица фактов ссылается на таблицы-измерения, имеющие ссылки на другие измерения (второго уровня и выше), то такая схема называется «снежинка».
![](https://cdn.javarush.com/images/article/e81a6085-ffdc-4b0c-9645-cdd17b1c1691/1024.jpeg)
Как можно заметить, для запросов, включающих фильтрацию по группам клиентов, приходится делать дополнительное соединение.
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)
![](https://cdn.javarush.com/images/article/2abb3172-bf4e-41d9-b687-66c668783596/1024.jpeg)
Обратной стороной денормализации всегда является избыточность, которая увеличивает размер БД как в транзакционных, так и аналитических приложениях. Давайте посчитаем примерную дельту в примере преобразования «снежинки» в «звезду».
В некоторых СУБД, например 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'ы, и усложнением задачи тех, кто обеспечивает наполнение и обновление базы. Поэтому денормализацию нужно проводить осторожно и выборочно, только там, где обойтись без нее невозможно.
Если заранее нельзя оценить плюсы и минусы денормализации, то следует изначально реализовать модель с нормализованными таблицами, а потом, для оптимизации проблемных запросов, проводить денормализацию.
Денормализацию следует внедрять постепенно и только для случаев, когда присутствуют повторные выборки связанных данных из разных таблиц. Помните, при дублировании данных количество записей возрастет, но количество чтений уменьшится. Рассчитываемые данные лучше хранить в колонках, чтобы избежать ненужных агрегатных выборок.