Курс All lectures for UA purposes - Лекція: Денормалізація таблиць у БД

8.1 Навіщо потрібна денормалізація?

Найбільш дорога з погляду обчислювальних ресурсів операція між великими таблицями - з'єднання. Відповідно, якщо в одному запиті необхідно «провентилювати» кілька таблиць, що складаються з багатьох мільйонів рядків, то СУБД витратить досить багато часу на таку обробку.

Користувач тим часом може відійти випити кави. Інтерактивність обробки практично зникає і наближається до такої обробки пакетної. Навіть гірше, у пакетному режимі користувач зранку отримує всі запитані дані і спокійно працює з ними, готуючи нові запити до вечора.

Щоб уникнути ситуації важких з'єднань таблиці, денормалізують. Але не аби як. Існують деякі правила, що дозволяють вважати денормалізовані з погляду транзакційної обробки таблиці «нормалізованими» згідно з правилами побудови таблиць для сховищ даних.

Основних схем, які вважаються «нормальними» в аналітичній обробці, дві: «сніжинка» та «зірка». Назви добре відображають суть і випливають безпосередньо з картинки зв'язаних таблиць.

В обох випадках центральним елементом схеми є так звані таблиці фактів, що містять події, що цікавлять аналітика, транзакції, документи та інші цікаві речі. Але якщо в транзакційній БД один документ «розмазаний» по кількох таблицях (як мінімум по двох: заголовки та рядки-зміст), то в таблиці фактів одному документу, точніше, кожному його рядку або набору згрупованих рядків, відповідає один запис.

Зробити це можна денормалізацією двох вищезгаданих таблиць.

8.2 Приклад денормалізації

Тепер можна оцінити, наскільки полегшиться для виконання СУБД запит, наприклад, наступного виду: визначити обсяги продажу борошна клієнтам ТОВ Пиріжки та ЗАТ Ватрушки за період.

У нормалізованій транзакційній БД:


SELECT
   SUM(dl.qty) AS total qty, SUM(dl.price) AS total amount, c.name 
FROM 
   docs d
   INNER JOIN doc lines dl ON d.id doc = dl.id doc 
   INNER JOIN customers c ON d.id customer = c.id customer 
   INNER JOIN products p ON dl.id product = p.id product 
WHERE
   c.name IN (’ООО Пирожки’,	’ЗАО Ватрушки’) AND
   р.name = ’Мука’ AND
   d.date BETWEEN ’2014-01-01’ AND ’2014-02-01’
GROUP BY c.name

В аналітичній БД:


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, c.name
FROM
   sales s
   INNER JOIN customers c ON d.id_customer = c.id_customer
   INNER JOIN products p ON dl.id_product = p.id_product
WHERE
   c.name IN ('ТОВ Тістечка', 'ЗАТ Ватрушки') AND
   p.name = 'Мука' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Замість важкого з'єднання між двома таблицями документів та їх складу з мільйонами рядків, СУБД дістається пряма робота з таблицею фактів та легкі з'єднання з невеликими допоміжними таблицями, без яких можна обійтися, знаючи ідентифікатори.


SELECT
   SUM(s.qty) AS total_qty, SUM(s.amount) AS total_amount, s.id_customer
FROM
   sales s
WHERE
   s.id_customer IN (1025, 20897) AND
   s.id_product = 67294 AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY s.id_customer

Повернемося до схем «зірка» та «сніжинка». За кадром першого малюнка залишабося таблиці клієнтів, їх груп, магазинів, продавців та, власне, товарів. При денормалізації ці таблиці, які називаються вимірами, також з'єднуються з таблицею фактів. Якщо таблиця фактів посилається на таблиці-вимірювання, що мають посилання на інші виміри (виміри другого рівня та вище), то така схема називається «сніжинка».

Як можна помітити, для запитів, що включають фільтрацію груп клієнтів, доводиться робити додаткове з'єднання.


SELECT sum(amount)
FROM sales s
   INNER JOIN customers c ON s.id_customer = c.id_customer
WHERE c.id_customer_group IN (1, 2, 10, 55)

У такому разі денормалізацію можна продовжити та опустити вимірювання другого рівня на перший, полегшивши запити до таблиці фактів.

Схема, у якій таблиця фактів посилається лише з виміри, які мають другого рівня, називається «зірка». Число таблиць вимірювань відповідає числу «променів» у зірці.

Схема «Зірка» повністю виключає ієрархію вимірів та необхідність з'єднання відповідних таблиць в одному запиті.


SELECT sum(amount)
FROM sales s
WHERE s.id_customer_group IN (1, 2, 10, 55)

Зворотною стороною денормалізації завжди є надмірність , що є причиною збільшення розміру БД як у разі транзакційних, і аналітичних додатків. Давайте порахуємо зразкову дельту на наведеному вище прикладі перетворення «сніжинки» на «зірку».

У деяких СУБД, наприклад Oracle, спеціальні цілі численні типи на рівні визначень схеми БД відсутні, необхідно використовувати універсальний логічний тип numeric(N), де N - число розрядів, що зберігаються. Розмір зберігання такого числа розраховується за спеціальною формулою, що наводиться у документації з фізичного зберігання даних, і, як правило, він перевищує такий для низькорівневих типів на кшталт «16 бітне ціле» на 1-3 байти.

Припустимо, таблиця продажів не використовує компресію даних і містить близько 500 мільйонів рядків, а кількість груп покупців близько 1000. У цьому випадку ми можемо використовувати як тип ідентифікатора id_customer_groupкоротке ціле (shortint, smallint), що займає 2 байти.

Вважатимемо, що наша СУБД підтримує двобайтовий цілочисельний тип (наприклад, PostgreSQL, SQL Server, Sybase та інші). Тоді додавання відповідної колонки id_customer_groupтаблицю продажів викличе збільшення її розміру як мінімум на 500 000 000 * 2 = 1 000 000 000 байт ~ 1 гігабайт.

8.3 Коли потрібна денормалізація?

Розглянемо деякі поширені ситуації, у яких денормалізація може бути корисна.

Велика кількість з'єднань таблиць

У запитах до повністю нормалізованої базі нерідко доводиться з'єднувати до десятка, або навіть більше, таблиць. А кожне з'єднання — операція дуже ресурсомістка. Як наслідок, такі запити є ресурси сервера і виконуються повільно.

У такій ситуації може допомогти:

денормалізація шляхом скорочення кількості таблиць. Краще об'єднувати в одну кілька таблиць, що мають невеликий розмір, що містять рідко змінювану (як часто кажуть, умовно-постійну, або нормативно-довідкову) інформацію, причому інформацію, за змістом тісно пов'язану між собою.
У випадку, якщо у великій кількості запитів потрібно об'єднувати більше п'яти чи шести таблиць, слід розглянути варіант денормалізації бази даних.
Денормалізація шляхом введення додаткового поля до однієї з таблиць. При цьому з'являється надмірність даних, потрібні додаткові дії для збереження цілісності БД.

Розрахункові значення

Найчастіше повільно виконуються і споживають багато ресурсів запити, у яких виробляються якісь складні обчислення, особливо у використанні угруповань і агрегатних функцій (Sum, Max тощо.). Іноді має сенс додати в таблицю 1-2 додаткових стовпця, що містять розрахункові дані, що часто використовуються (і складно обчислюються).

Припустимо, необхідно визначити загальну вартість кожного замовлення. Для цього спочатку слід визначити вартість кожного продукту (за формулою «кількість одиниць продукту» * «Ціна одиниці продукту» – знижка). Після цього необхідно згрупувати ціни на замовлення.

Виконання цього запиту є досить складним і якщо в базі даних зберігаються відомості про велику кількість замовлень, може зайняти багато часу. Замість виконання такого запиту можна на етапі розміщення замовлення визначити його вартість та зберегти її в окремому стовпці таблиці замовлень. У цьому випадку для отримання необхідного результату достатньо витягти з цього стовпця заздалегідь розраховані значення.

Створення стовпця, що містить заздалегідь розраховані значення, дозволяє значно заощадити час при виконанні запиту, проте вимагає своєчасної зміни даних у цьому стовпці.

Довгі поля

Якщо в базі даних є великі таблиці, що містять довгі поля (Blob, Long і т.п.), то серйозно прискорити виконання запитів до такої таблиці ми зможемо, якщо винесемо довгі поля в окрему таблицю. Хочемо ми, скажімо, створити в базі каталог фотографій, у тому числі зберігати в blob-полях і самі фотографії (професійної якості, з високою роздільною здатністю та відповідного розміру). З погляду нормалізації абсолютно правильною буде така структура таблиці:

ID фотографії
ID автора
ID моделі фотоапарата
сама фотографія (blob-поле)

А зараз уявимо, скільки часу буде працювати запит, який підраховує кількість фотографій, зроблених яким-небудь автором.

Правильним рішенням (хоча й порушуючим принципи нормалізації) у такій ситуації буде створити ще одну таблицю, що складається лише з двох полів — ID фотографії та blob-поле із самою фотографією. Тоді вибірки з основної таблиці (у якій величезного blob-поля зараз вже немає) будуть йти моментально, а коли захочемо подивитися саму фотографію — що ж, почекаємо…

Як визначити, коли денормалізація виправдана?

8.4 Плюси та мінуси денормалізації

Один із способів визначити, наскільки виправдані ті чи інші кроки — провести аналіз у термінах витрат та можливих вигод. Скільки коштуватиме денормалізована модель даних?

Визначити вимоги (чого хочемо досягти) → визначити вимоги до даних (що потрібно дотримуватись) → знайти мінімальний крок, який задовольняє ці вимоги → підрахувати витрати на реалізацію → реалізувати.

Витрати включають фізичні аспекти, такі як дисковий простір, ресурси, необхідні для управління цією структурою, і втрачені можливості через тимчасові затримки, пов'язані з обслуговуванням цього процесу. За денормалізацію треба платити. У денормалізованій базі даних підвищується надмірність даних, що може підвищити продуктивність, але вимагатиме більше зусиль для контролю за пов'язаними даними. Ускладниться процес створення додатків, оскільки дані повторюватимуться і їх важче відстежуватиме. Крім того, здійснення цілісності посилань виявляється не простою справою - пов'язані дані виявляються розділеними по різних таблицях.

До переваг відноситься більш висока продуктивність при виконанні запиту та можливість отримати при цьому більш швидку відповідь. Крім того, можна отримати й інші переваги, у тому числі збільшення пропускної спроможності, рівня задоволеності клієнтів та продуктивності, а також ефективніше використання інструментарію зовнішніх розробників.

Частота запитів та стійкість продуктивності

Наприклад, 72% із тисячі запитів, щодня генеруються підприємством, є запитами рівня зведених, а не детальних даних. При використанні таблиці зведених даних запити виконуються приблизно 6 секунд замість 4 хвабон, т. е. час обробки менше 3000 хвабон. Навіть із поправкою на ті 100 хвабон, які потрібно щотижня витрачати на підтримку таблиць зведених даних, в результаті економиться 2500 хвабон на тиждень, що повністю виправдовує створення таблиці зведених даних. Згодом може статися так, що більшість запитів буде звернено не до зведених даних, а до детальних даних. Чим менша кількість запитів, що використовують таблицю зведених даних, тим простіше від неї відмовитися, не торкаючись інших процесів.

А також…

Наведені вище критерії не єдині, які слід враховувати, приймаючи рішення про те, чи слід робити наступний крок оптимізації. Необхідно враховувати й інші чинники, зокрема пріоритети бізнесу та потреби кінцевих користувачів. Користувачі повинні розуміти, як з технічного погляду на архітектуру системи впливає вимога користувачів, які бажають, щоб усі запити виконувалися за кілька секунд. Найпростіше досягти цього розуміння — окреслити витрати, пов'язані зі створенням таких таблиць та їх управлінням.

8.5 Як грамотно продати денормалізацію.

Зберегти детальні таблиці

Щоб не обмежувати можливості бази даних, важливих для бізнесу, необхідно дотримуватись стратегії співіснування, а не заміни, тобто зберегти детальні таблиці для глибинного аналізу, додавши до них денормалізовані структури. Наприклад, лічильник відвідин. Для бізнесу потрібно знати кількість відвідувань веб-сторінки. Але для аналізу (за періодами, країнами…) нам дуже ймовірно знадобляться детальні дані – таблиця з інформацією про кожне відвідування.

Використання тригерів

Можна денормалізувати структуру бази даних і при цьому продовжувати користуватися перевагами нормалізації, якщо користуватися тригерами баз даних для збереження цілісності(integrity) інформації, ідентичності даних, що дублюються.

Наприклад, при додаванні обчислюваного поля на кожен із стовпців, від яких обчислюване поле залежить, вішається тригер, що викликає єдину процедуру, що зберігається (це важливо!), яка і записує потрібні дані в обчислюване поле. Треба тільки не пропустити жоден зі стовпців, від яких залежить поле, що обчислюється.

Програмна підтримка

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

За аналогією з тригерами повинна бути одна функція, що оновлює всі поля, що залежать від поля, що змінюється.

Висновки

При денормалізації важливо зберегти баланс між підвищенням швидкості роботи бази та збільшенням ризику появи суперечливих даних, між полегшенням життя програмістам, що пишуть, Select'ыта ускладненням завдання тих, хто забезпечує наповнення бази та оновлення даних. Тому проводити денормалізацію бази треба дуже акуратно, дуже вибірково лише там, де без цього ніяк не обійтися.

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

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