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

Елена Дворядкина

Обзор

Денормализация — один из способов оптимизации аналитических запросов, который предполагает преобразование структуры данных путем объединения таблиц фактов и измерений в одну широкую ("плоскую") таблицу, чтобы сократить необходимость использования сложных соединений (операций JOIN) в запросах.

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

  • Условия, при которых денормализация может быть подходящей стратегией:

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

    • В запросах требуется применять как можно меньше операций объединения (например, при работе с большими наборами данных).

    • Данные относительно стабильны: не изменяются, изменяются редко или только добавляются в конец набора.

    • Затраты на хранение менее важны, чем высокая скорость выполнения запросов.

    • Допускается задержка доступности данных для аналитических запросов (то есть данные могут периодически полностью перезагружаться пакетами).

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

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

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

    • Наличие связей с высокой кардинальностью. Если строка в таблице имеет тысячи связанных записей в другой таблице, для денормализации их необходимо будет представить в виде массива значений простого типа или кортежей. Использование массивов из большого числа элементов (порядка 1000 и более) может негативно сказаться на производительности.

  • Потенциальные проблемы, к которым может привести денормализация:

    • Объем используемого хранилища может быстро увеличиваться из-за хранения повторяющихся данных (хотя благодаря мощным алгоритмам сжатия ClickHouse эффективность хранения может быть не столь проблематичной).

    • Вставка и обновление данных становятся сложными процессами. Как упоминалось выше, когда данные денормализованы, любое изменение отдельной сущности (например, названия продукта или информации о пользователе) требует перезаписи всего денормализованного набора данных.

    • Усложняется задача сохранения целостности данных — дублирование данных в строках денормализованной таблицы увеличивает риск возникновения несогласованности (особенно когда необходимы изменения).

Если денормализация в конкретном случае в целом оправдана, обычно нет необходимости денормализовывать все данные — достаточно включить в денормализацию только некоторые столбцы, к которым требуется наиболее частый доступ. Денормализация часто заключается в том, чтобы добавить в основную таблицу для аналитики один или два столбца (например, со статистическими значениями). При наличии сложных объектов или связей one-to-many для денормализации можно использовать сложные типы: Tuple, Array(Tuple) или Nested.

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

Пример

Приведенный ниже пример показывает, как можно минимизировать использование операций JOIN в запросах путем денормализации данных.

Нормализованная структура

Пример структуры данных

Исходная нормализованная модель данных включает четыре отдельные таблицы:

  • Таблица заказов orders (таблица фактов):

    CREATE TABLE orders (
    order_id UInt64,
    customer_id UInt64,
    order_date Date
) ENGINE = MergeTree()
ORDER BY (order_date, order_id);
    Данные таблицы
    INSERT INTO orders VALUES
(1001, 3, '2026-04-07'),
(1002, 2, '2026-04-07'),
(1003, 1, '2026-04-07'),
(1004, 4, '2026-04-08'),
(1005, 1, '2026-04-08');
       ┌─order_id─┬─customer_id─┬─order_date─┐
1. │     1001 │           3 │ 2026-04-07 │
2. │     1002 │           2 │ 2026-04-07 │
3. │     1003 │           1 │ 2026-04-07 │
4. │     1004 │           4 │ 2026-04-08 │
5. │     1005 │           1 │ 2026-04-08 │
   └──────────┴─────────────┴────────────┘

  • Таблица товаров products (таблица измерений):

    CREATE TABLE products (
    product_id UInt64,
    product_name String,
    category String,
    price_per_unit Decimal(10,2)
) ENGINE = MergeTree()
ORDER BY product_id;
    Данные таблицы
    INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 999.99),
(2, 'Smartphone', 'Electronics', 699.99),
(3, 'Headphones', 'Electronics', 49.99),
(4, 'Desk Chair', 'Furniture', 149.99),
(5, 'Coffee Table', 'Furniture', 249.99),
(6, 'Notebook', 'Stationery', 1.99),
(7, 'Pen', 'Stationery', 0.49),
(8, 'Backpack', 'Accessories', 59.99),
(9, 'Monitor', 'Electronics', 199.99),
(10, 'Office Chair', 'Furniture', 169.99),
(11, 'Keyboard', 'Electronics', 49.99),
(12, 'Printer', 'Electronics', 299.99),
(13, 'Cap', 'Accessories', 5.99),
(14, 'Binder', 'Stationery', 2.99),
(15, 'Water Bottle', 'Accessories', 9.99);
        ┌─product_id─┬─product_name─┬─category────┬─price_per_unit─┐
 1. │          1 │ Laptop       │ Electronics │         999.99 │
 2. │          2 │ Smartphone   │ Electronics │         699.99 │
 3. │          3 │ Headphones   │ Electronics │          49.99 │
 4. │          4 │ Desk Chair   │ Furniture   │         149.99 │
 5. │          5 │ Coffee Table │ Furniture   │         249.99 │
 6. │          6 │ Notebook     │ Stationery  │           1.99 │
 7. │          7 │ Pen          │ Stationery  │           0.49 │
 8. │          8 │ Backpack     │ Accessories │          59.99 │
 9. │          9 │ Monitor      │ Electronics │         199.99 │
10. │         10 │ Office Chair │ Furniture   │         169.99 │
11. │         11 │ Keyboard     │ Electronics │          49.99 │
12. │         12 │ Printer      │ Electronics │         299.99 │
13. │         13 │ Cap          │ Accessories │           5.99 │
14. │         14 │ Binder       │ Stationery  │           2.99 │
15. │         15 │ Water Bottle │ Accessories │           9.99 │
    └────────────┴──────────────┴─────────────┴────────────────┘

  • Таблица покупателей customers (таблица измерений):

    CREATE TABLE customers (
    customer_id UInt64,
    full_name String,
    city String,
    email String
) ENGINE = MergeTree()
ORDER BY customer_id;
    Данные таблицы
    INSERT INTO customers VALUES
(1, 'James Wilson', 'New York', 'jwilson@example.com'),
(2, 'Mary Burton', 'Chicago', 'mburton@example.com'),
(3, 'Patricia Robinson', 'Los Angeles', 'probinson@example.com'),
(4, 'Robert Gray', 'Seattle', 'rgray@example.com'),
(5, 'Elizabeth Tucker', 'Chicago', 'etucker@example.com'),
(6, 'Joseph Lewis', 'New York', 'jlewis@example.com'),
(7, 'Paul Brown', 'Chicago', 'pbrown@example.com'),
(8, 'Andrew Clarke', 'Los Angeles', 'aclarke@example.com'),
(9, 'William Ferguson', 'Chicago', 'wferguson@example.com'),
(10, 'Linda Black', 'New York', 'lblack@example.com'),
(11, 'David Green', 'Chicago', 'dgreen@example.com'),
(12, 'Daniel Gray', 'New York', 'dgray@example.com'),
(13, 'Mark Armstrong', 'New York', 'marmstrong@example.com'),
(14, 'Donald Carter', 'Seattle', 'dcarter@example.com'),
(15, 'Elizabeth Collins', 'Chicago', 'ecollins@example.com');
        ┌─customer_id─┬─full_name─────────┬─city────────┬─email──────────────────┐
 1. │           1 │ James Wilson      │ New York    │ jwilson@example.com    │
 2. │           2 │ Mary Burton       │ Chicago     │ mburton@example.com    │
 3. │           3 │ Patricia Robinson │ Los Angeles │ probinson@example.com  │
 4. │           4 │ Robert Gray       │ Seattle     │ rgray@example.com      │
 5. │           5 │ Elizabeth Tucker  │ Chicago     │ etucker@example.com    │
 6. │           6 │ Joseph Lewis      │ New York    │ jlewis@example.com     │
 7. │           7 │ Paul Brown        │ Chicago     │ pbrown@example.com     │
 8. │           8 │ Andrew Clarke     │ Los Angeles │ aclarke@example.com    │
 9. │           9 │ William Ferguson  │ Chicago     │ wferguson@example.com  │
10. │          10 │ Linda Black       │ New York    │ lblack@example.com     │
11. │          11 │ David Green       │ Chicago     │ dgreen@example.com     │
12. │          12 │ Daniel Gray       │ New York    │ dgray@example.com      │
13. │          13 │ Mark Armstrong    │ New York    │ marmstrong@example.com │
14. │          14 │ Donald Carter     │ Seattle     │ dcarter@example.com    │
15. │          15 │ Elizabeth Collins │ Chicago     │ ecollins@example.com   │
    └─────────────┴───────────────────┴─────────────┴────────────────────────┘

  • Таблица деталей заказов order_items (связь между заказами и товарами — many-to-many):

    CREATE TABLE order_items (
    order_id UInt64,
    product_id UInt64,
    quantity UInt32
) ENGINE = MergeTree()
ORDER BY (order_id, product_id);
    Данные таблицы
    INSERT INTO order_items VALUES
(1001, 1, 1),
(1001, 3, 2),
(1001, 7, 6),
(1002, 9, 2),
(1002, 10, 2),
(1003, 15, 3),
(1003, 8, 2),
(1003, 12, 1),
(1003, 9, 1),
(1004, 4, 1),
(1004, 13, 2),
(1005, 6, 10),
(1005, 9, 2),
(1005, 7, 5);
        ┌─order_id─┬─product_id─┬─quantity─┐
 1. │     1001 │          1 │        1 │
 2. │     1001 │          3 │        2 │
 3. │     1001 │          7 │        6 │
 4. │     1002 │          9 │        2 │
 5. │     1002 │         10 │        2 │
 6. │     1003 │          8 │        2 │
 7. │     1003 │          9 │        1 │
 8. │     1003 │         12 │        1 │
 9. │     1003 │         15 │        3 │
10. │     1004 │          4 │        1 │
11. │     1004 │         13 │        2 │
12. │     1005 │          6 │       10 │
13. │     1005 │          7 │        5 │
14. │     1005 │          9 │        2 │
    └──────────┴────────────┴──────────┘

Примеры запросов

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

  • Найти все продажи товаров не из категории Electronics позже определенной даты:

    SELECT
    o.order_id AS order_id,
    c.full_name AS customer,
    c.city,
    o.order_date,
    p.product_name AS product,
    p.category,
    oi.quantity AS units_sold,
    p.price_per_unit * oi.quantity AS sale_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.category != 'Electronics' AND o.order_date >= '2026-04-07';
       ┌─order_id─┬─customer──────────┬─city────────┬─order_date─┬─product──────┬─category────┬─units_sold─┬─sale_amount─┐
1. │     1001 │ Patricia Robinson │ Los Angeles │ 2026-04-07 │ Pen          │ Stationery  │          6 │        2.94 │
2. │     1002 │ Mary Burton       │ Chicago     │ 2026-04-07 │ Office Chair │ Furniture   │          2 │      339.98 │
3. │     1003 │ James Wilson      │ New York    │ 2026-04-07 │ Backpack     │ Accessories │          2 │      119.98 │
4. │     1003 │ James Wilson      │ New York    │ 2026-04-07 │ Water Bottle │ Accessories │          3 │       29.97 │
5. │     1004 │ Robert Gray       │ Seattle     │ 2026-04-08 │ Desk Chair   │ Furniture   │          1 │      149.99 │
6. │     1004 │ Robert Gray       │ Seattle     │ 2026-04-08 │ Cap          │ Accessories │          2 │       11.98 │
7. │     1005 │ James Wilson      │ New York    │ 2026-04-08 │ Notebook     │ Stationery  │         10 │        19.9 │
8. │     1005 │ James Wilson      │ New York    │ 2026-04-08 │ Pen          │ Stationery  │          5 │        2.45 │
   └──────────┴───────────────────┴─────────────┴────────────┴──────────────┴─────────────┴────────────┴─────────────┘

  • Вычислить сумму каждого заказа:

    SELECT
    o.order_id AS order_id,
    o.order_date,
    c.full_name AS customer,
    round(SUM(oi.quantity * p.price_per_unit), 2) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id, c.full_name, o.order_date
ORDER BY o.order_id;
       ┌─order_id─┬─order_date─┬─customer──────────┬─total_amount─┐
1. │     1001 │ 2026-04-07 │ Patricia Robinson │      1102.91 │
2. │     1002 │ 2026-04-07 │ Mary Burton       │       739.96 │
3. │     1003 │ 2026-04-07 │ James Wilson      │       649.93 │
4. │     1004 │ 2026-04-08 │ Robert Gray       │       161.97 │
5. │     1005 │ 2026-04-08 │ James Wilson      │       422.33 │
   └──────────┴────────────┴───────────────────┴──────────────┘

Денормализованная структура

Денормализация отдельных столбцов

Предположим, что наиболее часто требуется выводить данные по заказам с именем покупателя и общей суммой заказа. Чтобы исключить необходимость использования JOIN для таких запросов, можно создать таблицу orders_with_total на основе orders и добавить в нее столбец customer_name с именем покупателя и столбец total_amount с общей стоимостью заказа:

CREATE TABLE orders_with_total (
    order_id UInt64,
    customer_id UInt64,
    order_date Date,
    customer_name String,
    total_amount Decimal(10,2)
) ENGINE = MergeTree()
ORDER BY (order_date, order_id);

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

INSERT INTO orders_with_total
SELECT
    o.*,
    c.full_name,
    round(SUM(oi.quantity * p.price_per_unit), 2)
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id, o.customer_id, o.order_date, c.full_name;

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

SELECT order_id, order_date, customer_name, total_amount FROM orders_with_total;
   ┌─order_id─┬─order_date─┬─customer_name─────┬─total_amount─┐
1. │     1001 │ 2026-04-07 │ Patricia Robinson │      1102.91 │
2. │     1002 │ 2026-04-07 │ Mary Burton       │       739.96 │
3. │     1003 │ 2026-04-07 │ James Wilson      │       649.93 │
4. │     1004 │ 2026-04-08 │ Robert Gray       │       161.97 │
5. │     1005 │ 2026-04-08 │ James Wilson      │       422.33 │
   └──────────┴────────────┴───────────────────┴──────────────┘

Использование сложного типа при денормализации

Если один заказ включает несколько товаров (связь one-to-many), можно создать таблицу orders_with_items с использованием типа Nested для списка позиций в заказе (не разворачивая вложенные данные — flatten_nested=0) и с материализованным столбцом для вычисления суммы заказа:

SET flatten_nested=0;
CREATE TABLE orders_with_items (
    order_id UInt64,
    customer_id UInt64,
    order_date Date,
    items Nested (
        product_name String,
        category String,
        quantity UInt32,
        price_per_unit Decimal(10,2)
    ),
    total_amount Decimal(10,2) MATERIALIZED
        arraySum(x -> x.3 * x.4, items)
) ENGINE = MergeTree()
ORDER BY order_id;

Заполнение таблицы данными:

INSERT INTO orders_with_items
SELECT
    o.*,
    arrayMap(
        (product_name, category, quantity, price_per_unit) ->
            tuple(product_name, category, quantity, price_per_unit),
        groupArray(product_name),
        groupArray(category),
        groupArray(quantity),
        groupArray(price_per_unit)
    ) AS items
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id, o.customer_id, o.order_date;

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

  • Прочитать всю таблицу:

    SELECT order_id, order_date, items, total_amount FROM orders_with_items;
       ┌─order_id─┬─order_date─┬─items────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─total_amount─┐
1. │     1001 │ 2026-04-07 │ [('Laptop','Electronics',1,999.99),('Headphones','Electronics',2,49.99),('Pen','Stationery',6,0.49)]                                             │      1102.91 │
2. │     1002 │ 2026-04-07 │ [('Monitor','Electronics',2,199.99),('Office Chair','Furniture',2,169.99)]                                                                       │       739.96 │
3. │     1003 │ 2026-04-07 │ [('Backpack','Accessories',2,59.99),('Monitor','Electronics',1,199.99),('Printer','Electronics',1,299.99),('Water Bottle','Accessories',3,9.99)] │       649.93 │
4. │     1004 │ 2026-04-08 │ [('Desk Chair','Furniture',1,149.99),('Cap','Accessories',2,5.99)]                                                                               │       161.97 │
5. │     1005 │ 2026-04-08 │ [('Notebook','Stationery',10,1.99),('Pen','Stationery',5,0.49),('Monitor','Electronics',2,199.99)]                                               │       422.33 │
   └──────────┴────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────────┘

  • Найти все заказы с товаром Monitor и вывести количество проданных единиц в каждом заказе:

    SELECT order_id, order_date, tupleElement(arrayFilter(x -> x.1 = 'Monitor', items), 'quantity') AS monitors_sold
FROM orders_with_items
WHERE notEmpty(monitors_sold);
       ┌─order_id─┬─order_date─┬─monitors_sold─┐
1. │     1002 │ 2026-04-07 │ [2]           │
2. │     1003 │ 2026-04-07 │ [1]           │
3. │     1005 │ 2026-04-08 │ [2]           │
   └──────────┴────────────┴───────────────┘
Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней