Обратная нормализация (денормализация)

Привет, Хабр!

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

Но, как и все в этом мире, нормализация не идеальна. Иногда она может привести к чрезмерной комплексности и снижению производительности запросов. И здесь бывает полеза обратная нормализация. Если нормализация - это упорядочивание, то обратная нормализация - это некоторое "ослабление" правил для достижения определенных целей. В БД это означает объединение таблиц, добавление избыточных данных и так далее, чтобы ускорить чтение данных и упростить запросы, даже если это идет в ущерб некоторой "чистоте" схемы.

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

Обратная нормализация делает запросы более прямолинейными и понятными.

Планирование процесса обратной нормализации

Перед тем как начать, нужно понять, какая цель обратной нормализации.

К примеру цели могут быть такими:

Сокращение времени отклика для определенных типов запросов,

Облегчение понимания и работы с данными,

Ускорение отчетности или аналитических запросов ( в целом специфических функций),

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

На основе анализа определите, какие таблицы и связи являются кандидатами для денормализации. Рассмотрите:

Таблицы с частыми и медленными JOIN-операциями,

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

Далее естественно идет документация и планирование рабочего процесса.

Как сейчас обстоят дела со схемой БД?

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

Что характерно узким местам?

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

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

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

Избыточная нормализация

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

Необходимость написания сложных запросов с множеством JOIN операций для извлечения относительно простой информации может быть признаком избыточной нормализации.

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

Примеры узких мест в SQL БД

Множественное соединение (JOIN) таблиц

SELECT *
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
JOIN Products ON OrderDetails.ProductID = Products.ProductID
WHERE Customers.Country = 'Disneyland';

Если таблицы сильно нормализованы, множественные JOIN операции могут существенно замедлить выполнение запроса, особенно если таблицы большие и не оптимизированы с помощью индексов.

Избыточная информация:

Таблица Orders:

OrderID | CustomerID | OrderDate

Таблица OrderDetails:

OrderDetailID | OrderID | ProductID | Quantity

Таблица Products:

ProductID | ProductName | UnitPrice

Чтобы получить полную информацию о заказе, необходимо соединить все три таблицы. Это очень трудоемкий процесс.

Неоптимизрованные запросы:

SELECT *
FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = 'Germany');

Вложенные запросы могут быть оч.затратными, если они возвращают большое количество данных.

Отсутствие или неправильное использование индексов:

SELECT *
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

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

Реализация изменений

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

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

Реализация в sql бд в примерах

Простое объединение таблиц

У вас есть две таблицы Orders и OrderDetails, которые часто используются вместе. Мы можем уменьшить количество JOIN операций, объединив данные в одной таблице.

-- новую таблаяу, объединяющую Orders и OrderDetails
CREATE TABLE CombinedOrders AS
SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate, OrderDetails.ProductID, OrderDetails.Quantity
FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID;

Добавление расчетных столбцов

К примеру нам нужно часто вычислять общую сумму заказа. Ускорить этот процесс можно с помощью расчетного столбца:

-- Добавляем столбец TotalAmount в таблицу Orders
ALTER TABLE Orders ADD TotalAmount DECIMAL(10,2);

-- Обновляем TotalAmount на основе данных из OrderDetails
UPDATE Orders
SET TotalAmount = (SELECT SUM(UnitPrice * Quantity) FROM OrderDetails WHERE Orders.OrderID = OrderDetails.OrderID);

Денормализация связей многие-ко-многим

Есть сложная связь многие-ко-многим между Products и Categories. Мы можем упростить запросы, создав денормализованную таблицу:

Изначально:

CREATE TABLE Courses (
    CourseID INT,
    CourseName VARCHAR(100)
);

CREATE TABLE Students (
    StudentID INT,
    StudentName VARCHAR(100)
);

-- таблица для связи многие-ко-многим
CREATE TABLE CourseStudents (
    CourseID INT,
    StudentID INT
);

После:

-- добавление столбца с информацией о курсах непосредственно в таблицу студентов
ALTER TABLE Students ADD Courses VARCHAR(255);

-- обновление данных с информацией о курсах для каждого студента
UPDATE Students
SET Courses = (SELECT STRING_AGG(CourseName, ', ') FROM Courses JOIN CourseStudents ON Courses.CourseID = CourseStudents.CourseID WHERE CourseStudents.StudentID = Students.StudentID);

Денормализация связи один-ко-многим

-- добавляем столбец 'TotalOrderAmount' в таблицу 'Orders'
ALTER TABLE Orders
ADD TotalOrderAmount DECIMAL(10,2);

-- обновляем 'TotalOrderAmount' суммой всех позиций заказа
UPDATE Orders
SET TotalOrderAmount = (SELECT SUM(UnitPrice * Quantity) FROM OrderDetails WHERE OrderDetails.OrderID = Orders.OrderID);

Использование материализованных представлений

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

-- материализованное представление для сложного запроса
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT ProductID, SUM(Quantity) AS TotalSold, AVG(UnitPrice) AS AveragePrice
FROM OrderDetails
GROUP BY ProductID;

Кэширование сложных запросов

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

Можно ускорить доступ к результатам этих запросов, кэшируя их в отдельной таблице:

-- таблица для кэширования результатов сложного запроса
CREATE TABLE CachedComplexQuery AS
SELECT ComplexQueryColumn1, ComplexQueryColumn2, ...
FROM ...
WHERE ...;

-- Обновляем кэш при необходимости
TRUNCATE TABLE CachedComplexQuery;
INSERT INTO CachedComplexQuery (ComplexQueryColumn1, ComplexQueryColumn2, ...)
SELECT ComplexQueryColumn1, ComplexQueryColumn2, ...
FROM ...
WHERE ...;

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

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

-- функцию триггера для обновления TotalAmount
CREATE OR REPLACE FUNCTION update_total_amount() RETURNS TRIGGER AS $$
BEGIN
  UPDATE Orders
  SET TotalAmount = (SELECT SUM(UnitPrice * Quantity) FROM OrderDetails WHERE OrderDetails.OrderID = NEW.OrderID)
  WHERE Orders.OrderID = NEW.OrderID;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

--  триггер, который вызывает функцию при изменении OrderDetails
CREATE TRIGGER UpdateTotalAmount AFTER INSERT OR UPDATE OR DELETE ON OrderDetails
FOR EACH ROW EXECUTE FUNCTION update_total_amount();

Индикатор статуса

-- добавление столбца статуса в таблицу заказов
ALTER TABLE Orders ADD Status VARCHAR(50);

-- установка статуса на основе определенных условий
UPDATE Orders
SET Status = CASE
    WHEN OrderDate < '2023-01-01' THEN 'Archived'
    ELSE 'Active'
END;

Денормализация иерархических данных

Прежде:

-- таблица сотрудников с ссылкой на менеджера
CREATE TABLE Employees (
    EmployeeID INT,
    EmployeeName VARCHAR(100),
    ManagerID INT
);

после:

-- добавление имени менеджера непосредственно в таблицу сотрудников
ALTER TABLE Employees ADD ManagerName VARCHAR(100);

-- обновление данных с именами менеджеров
UPDATE Employees e
SET ManagerName = (SELECT EmployeeName FROM Employees WHERE EmployeeID = e.ManagerID);

Когда денормализация становится вашим врагом

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

Денормализация ускоряет чтение, но что насчет записи? Каждое обновление может стать мучительно медленным. Больше данных = больше затрат на хранение и обработку.

С каждым новым денормализованным элементом ваша система становится все более запутанной. Краткосрочные выгоды могут привести к долгосрочным проблемам.

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

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