Что такое денормализация и когда её применяют?

Что такое денормализация и когда её применяют?

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

Что такое нормализация:

Нормализация — это процесс организации данных в базе данных для минимизации дублирования и логической корректности. В идеально нормализованной БД:

• Каждый факт хранится один раз
• Нет дублирования данных
• Данные раскиданы по разным таблицам с помощью внешних ключей

Пример нормализованной структуры:

-- Таблица заказов
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

-- Таблица покупателей
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

-- Для получения имени покупателя нужен JOIN
SELECT o.order_id, c.customer_name 
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

Денормализация на практике:

Денормализованная структура — добавляем customer_name прямо в таблицу заказов:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),  -- ДЕНОРМАЛИЗАЦИЯ!
    order_date DATE
);

-- Теперь получить имя можно без JOIN
SELECT order_id, customer_name FROM orders;

Типы денормализации:

1. Копирование данных из связанной таблицы

Добавляем поля из связанной таблицы для избежания JOIN:

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),     -- Скопировано из products
    product_price DECIMAL(10,2),   -- Скопировано из products
    quantity INT
);

2. Вычисленные поля (Derived Data)

Хранимся результаты вычислений вместо пересчета при каждом запросе:

CREATE TABLE customer_stats (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    total_orders INT,             -- Вычисляется как COUNT
    total_spent DECIMAL(12,2),    -- Вычисляется как SUM
    last_order_date DATE          -- Вычисляется как MAX
);

3. Дублирование в разных форматах

Хранимые данные в нескольких форматах для разных случаев использования:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    user_json JSONB,              -- JSON-версия всех данных
    user_name VARCHAR(100),
    user_email VARCHAR(100)
);

Когда применять денормализацию:

1. Аналитические системы (OLAP)

Где доминируют операции чтения над письмом:

-- Для аналитики часто создают denormalized таблицы фактов
CREATE TABLE fact_sales (
    date_key INT,
    product_key INT,
    customer_key INT,
    product_name VARCHAR(100),    -- Денормализация для быстрого фильтра
    category VARCHAR(50),
    sales_amount DECIMAL(12,2),
    quantity INT
);

2. Улучшение производительности часто используемых запросов

Если JOIN замедляет критичные запросы:

-- Медленно: нужен JOIN
SELECT COUNT(*), p.category, SUM(o.amount)
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.category;

-- Быстро: денормализованная таблица
SELECT COUNT(*), category, SUM(amount)
FROM order_items_denorm
GROUP BY category;

3. Кэширование вычисляемых значений

Когда вычисления дорогие и нужны часто:

-- Денормализованная таблица статистики
CREATE TABLE product_analytics AS
SELECT 
    p.product_id,
    p.product_name,
    COUNT(o.order_id) as times_ordered,
    AVG(o.amount) as avg_order_value,
    MAX(o.order_date) as last_sold_date
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_id;

4. Распределённые системы

Когда JOIN невозможен или дорог из-за распределения данных:

-- Денормализация для микросервисов
-- Service A хранит всю необходимую информацию о заказе
CREATE TABLE orders_denorm (
    order_id INT,
    customer_id INT,
    customer_name VARCHAR(100),    -- Дублировано
    customer_email VARCHAR(100),   -- Дублировано
    product_name VARCHAR(100),     -- Дублировано
    amount DECIMAL(12,2)
);

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

1. Проблемы консистентности

Данные могут рассинхронизироваться:

-- Если обновить название в исходной таблице
UPDATE customers SET customer_name = 'New Name' WHERE customer_id = 1;

-- Но забыть обновить в orders_denorm — данные несогласованы!
SELECT * FROM orders WHERE customer_id = 1;  -- Старое имя

2. Сложность обновления

При изменении нужно обновить несколько таблиц:

-- Обновление требует транзакции
BEGIN;
    UPDATE customers SET name = 'New' WHERE id = 1;
    UPDATE orders SET customer_name = 'New' WHERE customer_id = 1;
    UPDATE invoices SET customer_name = 'New' WHERE customer_id = 1;
COMMIT;

3. Увеличение размера БД

Дублирование данных увеличивает объём хранилища:

-- Без денормализации: 1 млн строк в customers, 100 млн в orders
-- С денормализацией: customer_name в каждой строке orders = 100 млн доп. строк

Лучшие практики:

1. Используй денормализацию обдуманно:

• Только для критичных по производительности запросов
• Только когда нормализованный запрос действительно медленный

2. Документируй денормализацию:

-- Таблица order_denorm — денормализована для быстрых аналитических запросов
-- Источник истины для customer_name: таблица customers
-- Используй migration для синхронизации
CREATE TABLE order_denorm (...);

3. Автоматизируй обновление:

-- Триггер для синхронизации
CREATE TRIGGER sync_customer_name
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
    UPDATE orders SET customer_name = NEW.customer_name
    WHERE customer_id = NEW.customer_id;
END;

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

Правило 80/20:

Денормализуй для операций, которые:

• Выполняются часто (80% от всех запросов)
• Критичны по времени отклика
• Иначе требуют дорогих JOIN или агрегаций

Для остального — пусть останется нормализовано.

Денормализация — это инструмент оптимизации, а не закон. Используй её осознанно, после анализа и измерения производительности.