Что такое денормализация?sql-21

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

Основные цели денормализации

  1. Ускорение выполнения запросов
    Уменьшение количества JOIN-операций в часто выполняемых запросах

  2. Упрощение сложных запросов
    Снижение сложности SQL-запросов для аналитических задач

  3. Оптимизация для конкретных сценариев
    Подстройка структуры данных под специфические требования приложений

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

1. Добавление вычисляемых полей

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(12,2), -- Денормализованное поле (можно вычислить из order_items)
    item_count INT             -- Денормализованное поле
);

2. Хранение дублирующих данных

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    product_name VARCHAR(100), -- Дублирование данных из таблицы products
    unit_price DECIMAL(10,2),  -- Дублирование цены на момент заказа
    quantity INT
);

3. Объединение таблиц

CREATE TABLE customer_orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100), -- Денормализация из таблицы customers
    customer_email VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL(12,2)
);

4. Создание предварительно агрегированных данных

CREATE TABLE product_sales_summary (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    total_sold INT,            -- Суммарное количество проданных единиц
    last_sale_date DATE,       -- Дата последней продажи
    avg_daily_sales DECIMAL(10,2) -- Средние дневные продажи
);

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

  1. Системы отчетности и аналитики (OLAP)
    Где важнее скорость чтения, чем целостность данных

  2. Частые сложные запросы
    Которые требуют множества JOIN-ов в нормализованной структуре

  3. Системы с преобладанием чтения
    Где операции записи происходят редко

  4. Оперативные аналитические обработки
    Где нужна мгновенная агрегация данных

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

Преимущества:

  • Значительное повышение скорости запросов
  • Упрощение SQL-запросов
  • Снижение нагрузки на сервер БД
  • Улучшение производительности для сложных отчетов

Недостатки:

  • Увеличение риска несогласованности данных
  • Повышенные требования к хранению
  • Усложнение операций обновления данных
  • Необходимость дополнительных механизмов поддержания целостности

Механизмы поддержания целостности при денормализации

  1. Триггеры для автоматического обновления денормализованных данных
CREATE TRIGGER update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = (SELECT SUM(unit_price * quantity)
                       FROM order_items
                       WHERE order_id = NEW.order_id)
    WHERE order_id = NEW.order_id;
END;
  1. Периодические задания для пересчета агрегированных данных
-- Ежедневное обновление сводных данных
CREATE PROCEDURE refresh_product_summary()
BEGIN
    TRUNCATE TABLE product_sales_summary;

    INSERT INTO product_sales_summary
    SELECT p.product_id, p.product_name,
           SUM(oi.quantity), MAX(o.order_date),
           AVG(oi.quantity)
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    GROUP BY p.product_id, p.product_name;
END;
  1. Материализованные представления (где поддерживаются)

Пример стратегической денормализации

Нормализованная схема:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
);

CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    user_id INT,
    content TEXT,
    created_at TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE post_comments (
    comment_id INT PRIMARY KEY,
    post_id INT,
    user_id INT,
    comment TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(post_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Денормализованная версия для ленты новостей:

CREATE TABLE news_feed (
    feed_id INT PRIMARY KEY,
    post_id INT,
    post_content TEXT,
    post_author VARCHAR(50), -- Денормализация
    post_author_avatar VARCHAR(255), -- Денормализация
    comment_count INT,      -- Агрегированные данные
    like_count INT,
    last_comment_text TEXT, -- Самый свежий комментарий
    last_comment_author VARCHAR(50)
);

Резюмируем: денормализация — это мощный инструмент оптимизации производительности, который следует применять осознанно и только после тщательного анализа рабочих нагрузок. Она особенно эффективна в read-heavy системах, где преимущества в скорости запросов перевешивают затраты на поддержание целостности данных.

Вопросы

Собеседования