Примеры денормализации данных и её последствия

Нормализация решает одни проблемы, но в некоторых случаях создает другие, особенно когда дело касается производительности. Сегодня мы откроем для вас врата в тёмное (а иногда светлое) искусство — денормализацию. Да, вы можете нарушать правила нормализации... но только с умом!

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

Можно сказать, что денормализация — это компромисс между чистотой данных и скоростью выполнения запросов.

Когда использовать денормализацию?

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

1. Часто используемые запросы становятся медленными. Когда система с большой нагрузкой часто выполняет одинаковые запросы (например, сводные отчеты и агрегаты), соединения множества таблиц могут занимать значительное время. Денормализация позволяет уменьшить количество таких соединений.

2. Аналитические задачи и статистика. В аналитических системах (например, BI — Business Intelligence) часто требуется объёмный анализ данных. В таких случаях денормализация ускоряет обработку за счёт "предподготовленных" данных.

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

4. Количество соединений превышает здравый смысл. Если у вас запросы с 25 таблицами в JOIN, возможно, пришло время пересмотреть подход.

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

Пример 1: Интернет-магазин. В нормализованной базе данных интернет-магазина у нас могут быть такие таблицы:

1. customers — данные о клиентах.
2. orders — информация о заказах.
3. products — данные о продуктах.
4. order_items — товары, входящие в заказ.

Запрос для получения информации может быть примерно таким:

SELECT
    c.customer_name,
    o.order_date,
    p.product_name,
    oi.quantity
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id
WHERE 
    c.customer_id = 42;

Но что если наш интернет-магазин обрабатывает сотни тысяч заказов в день? Этот запрос станет слишком медленным из-за большого количества соединений.

Решение: денормализация.

Давайте создадим таблицу для часто используемой информации:

CREATE TABLE order_summary AS
SELECT 
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date,
    p.product_id,
    p.product_name,
    oi.quantity
FROM 
    customers c
JOIN 
    orders o ON c.customer_id = o.customer_id
JOIN 
    order_items oi ON o.order_id = oi.order_id
JOIN 
    products p ON oi.product_id = p.product_id;

Теперь, когда нам нужно получить данные, мы просто делаем запрос к order_summary:

SELECT * FROM order_summary WHERE customer_id = 42;

Пример 2: Система аналитики. Представим, что вы работаете с базой данных для компании, занимающейся продажей билетов на мероприятия. Есть таблицы:

1. events — информация о событиях.
2. sales — данные о продажах билетов.

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

SELECT
    e.event_name,
    AVG(s.price) AS avg_ticket_price
FROM 
    events e
JOIN 
    sales s ON e.event_id = s.event_id
GROUP BY 
    e.event_name;

Этот запрос может быть довольно медленным, особенно если запись о каждой продаже занимает миллионы строк.

Решение: денормализация. Создадим отдельную таблицу с агрегированными данными:

CREATE TABLE event_summary AS
SELECT 
    e.event_id,
    e.event_name,
    COUNT(s.sale_id) AS ticket_count,
    SUM(s.price) AS total_revenue,
    AVG(s.price) AS avg_ticket_price
FROM 
    events e
JOIN 
    sales s ON e.event_id = s.event_id
GROUP BY 
    e.event_id, e.event_name;

Теперь отчёты будут работать быстрее на агрегатированном уровне:

SELECT
    event_name, 
    avg_ticket_price 
FROM 
    event_summary;

Последствия денормализации

Денормализация, конечно, может ускорить запросы, но она — не волшебная палочка, которая решит все проблемы. Вот с чем можно столкнуться, если решитесь на этот шаг.

Первое — дублирование данных. Когда одна и та же информация хранится в нескольких местах, размер базы быстро растёт, и с этим становится сложнее работать.

Второе — обновлять данные теперь сложнее. Представьте, у вас есть данные о клиенте в таблице customers, а ещё копия этих данных в таблице order_summary. Если клиент сменит имя или адрес, нужно не забыть обновить сразу в двух местах. Промахнулся — и получилась ошибка, потому что данные больше не совпадают.

Третье — из-за такой избыточности легко запутаться и допустить ошибки. Это как с разными версиями одного и того же документа — иногда трудно понять, какая версия правильная.

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

В общем, денормализация — это инструмент, который стоит использовать с умом, зная все плюсы и минусы.

2

Задача

SQL SELF, 25 уровень, 4 лекция

Недоступна

Создание денормализованной таблицы на основе существующих данных

Создание денормализованной таблицы на основе существующих данных

Открыть

1

Опрос

Нормализация данных, 25 уровень, 4 лекция

Недоступен

Нормализация данных

Нормализация данных

Открыть