Навіщо потрібна денормалізація баз даних, і коли її використовувати



В нашому блозі на Хабре ми не тільки розповідаємо про розвиток свого продукту — білінгу для операторів зв'язку «Гідра», але і публікуємо матеріали про роботу з інфраструктурою і використанні технологій.

Нещодавно ми писали про використання Clojure і MongoDB, а сьогодні мова піде про плюси і мінуси денормалізації баз даних. Розробник баз даних і фінансовий аналітик Еміл Дркушич (Emil Drkušić) написав у блозі компанії Vertabelo матеріал про те, навіщо, як і коли використовувати цей підхід. Ми представляємо вашій увазі головні тези цієї замітки.

Що таке денормалізація?

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

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

Розглянемо нормалізовану модель для найпростішої CRM-системи:



Пробіжимося по наявним тут таблиць:

  • Таблиця
    user_account
    зберігає дані про користувачів, зареєстрованих у додатку (для спрощення моделі ролі та права користувачів з неї виключені).
  • Таблиця
    client
    містить якісь базові відомості про клієнтів.
  • Таблиця
    product
    — це список пропонованих товарів.
  • Таблиця
    task
    містить всі створені завдання. Кожну з них можна представити у вигляді набору узгоджених дій по відношенню до клієнта. Для кожної є список дзвінків, зустрічей, запропонованих та проданих товарів.
  • Таблиці
    call
    та
    meeting
    зберігають дані про замовлення та зустрічах з клієнтами і пов'язують їх з поточними завданнями.
  • Словники
    task_outcome
    ,
    meeting_outcome
    та
    call_outcome
    містить всі можливі варіанти результату дзвінків, зустрічей і завдання.
  • product_offered
    зберігає список продуктів, які були запропоновані клієнтам;
  • product_sold
    — продукти, які вдалося продати.
  • Таблиця
    supply_order
    зберігає інформацію про всіх розміщених замовлень.
  • Таблиця
    writeoff
    містить перелік списаних з якихось причин товарів.
У цьому прикладі база даних сильно спрощена для наочності. Але неважко побачити, що вона відмінно нормалізована — в ній немає ніякої надмірності, і все має працювати, як годинник. Ніяких проблем з продуктивністю не виникає до того моменту, поки база не зіткнеться з великим об'ємом даних.

Коли корисно використовувати денормализацию

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

  1. Збереження історичних даних. Дані змінюються з плином часу, але може бути потрібно зберігати значення, які були введені в момент створення запису. Наприклад, можуть змінитися ім'я та прізвище клієнта або інші дані про його місце проживання і рід занять. Завдання повинна містити значення полів, які були актуальні на момент створення завдання. Якщо цього не забезпечити, то відновити минулі дані коректно не вдасться. Вирішити проблему можна, додавши таблицю з історією змін. У такому разі SELECT-запит, який буде повертати завдання і актуальне ім'я клієнта буде більш складним. Можливо, додаткова таблиця — не кращий вихід з положення.
  2. Підвищення продуктивності запитів. Деякі запити можуть використовувати безліч таблиць для доступу до часто запитуваних даних. Приклад — ситуація, коли необхідно поєднати до 10 таблиць для отримання імені клієнта і найменування товарів, які були йому продані. Деякі з них, в свою чергу, можуть містити великі обсяги даних. При такому розкладі розумним буде додати безпосередньо полі
    client_id
    в таблицю
    products_sold
    .
  3. Прискорення створення звітів. Бізнесу часто потрібно вивантажувати певну статистику. Створення звітів за «живим» даними може вимагати великої кількості часу, так і продуктивність всієї системи може в такому випадку впасти. Наприклад, потрібно відстежувати клієнтські продажу за певний проміжок по заданій групі або всім користувачам разом. Вирішальний цю задачу запит в «бойовій» базі перелопатит її повністю, перш ніж подібний звіт буде сформований. Неважко уявити, наскільки повільніше все буде працювати, якщо такі звіти будуть потрібні щодня.
  4. Попередні обчислення часто запитуваних значень. Завжди є потреба тримати найбільш часто запитувані значення напоготові для регулярних розрахунків, а не створювати їх заново, генеруючи їх кожен раз в реальному часі.
Висновок напрошується сам собою: не варто звертатися до денормалізації, якщо не стоїть завдань, пов'язаних з продуктивністю програми. Але якщо відчувається, що система сповільнилася або скоро сповільниться, саме час задуматися про застосування даної техніки. Однак, перш ніж звертатися до неї, варто застосувати і інші можливості поліпшення продуктивності: оптимізацію запитів і правильну індексацію.

Не все так гладко

Очевидна мета денормалізації — підвищення продуктивності. Але всьому є своя ціна. В даному випадку вона складається з наступних пунктів:

  • Місце на диску. Очікувано, оскільки дані дублюються.
  • Аномалії даних. Необхідно розуміти, що з певного моменту дані можуть бути змінені в декількох місцях одночасно. Відповідно, потрібно коректно змінювати та їх копії. Це ж відноситься до звітів і попередньо вимірюваним значенням. Вирішити проблему можна за допомогою тригерів, транзакцій і збережених процедур для суміщення операцій.
  • Документація. Кожне застосування денормалізації варто докладно документувати. Якщо в майбутньому структура бази зміниться, то в ході цього процесу потрібно буде врахувати всі минулі зміни — можливо, від них взагалі можна буде до того моменту відмовитися через непотрібність. (Приклад: в клієнтську таблицю доданий новий атрибут, що призводить до необхідності збереження минулих значень. Щоб вирішити цю задачу, доведеться змінити настройки денормалізації).
  • Уповільнення інших операцій. Цілком можливо, що застосування денормалізації сповільнить процеси вставки, модифікації і видалення даних. Якщо подібні дії проводяться відносно рідко, то це може бути виправдано. У цьому випадку ми розбиваємо один повільний SELECT-запит на ряд більш дрібних запитів щодо введення, оновлення та видалення даних. Якщо складний запит може серйозно уповільнити всю систему, то уповільнення безлічі невеликих операцій не позначиться на якості роботи програми настільки драматичних чином.
  • код. Пункти 2 і 3 зажадають додавання коду. В той же час вони можуть істотно спростити деякі запити. Якщо денормалізації піддається існуюча база даних, то потрібно модифікувати ці запити, щоб оптимізувати роботу всієї системи. Також знадобиться оновити існуючі записи, заповнивши значення доданих атрибутів — це теж вимагає написання деякої кількості коду.

Денормалізація на прикладі

В представленій моделі були застосовані деякі з вищезазначених правил денормалізації. Синім позначені нові блоки, рожевим — ті, що були змінені.



Що змінилося і чому?



Єдине нововведення в таблиці
product
— рядок
units_in_stock
. В нормалізованої моделі ми можемо обчислити це значення наступним чином: замовлене найменування — продане — (запропоноване) — списане (units ordered — units sold — (units offered) — units written off). Обчислення повторюється кожний раз, коли клієнт запитує товар. Це досить витратний за часом процес. Замість цього можна обчислювати значення заздалегідь так, щоб до моменту надходження запиту від покупця, усе вже було напоготові. З іншого боку, атрибут units_in_stock повинен оновляться після кожної операції введення, оновлення та видалення таблиць
products_on_order
,
writeoff
,
product_offered
та
product_sold
.



У таблицю
task
додано два нових атрибути:
create_webdir_failed
та
user_first_last_name
. Обидва вони зберігають значення на момент створення завдання — це потрібно, тому що кожне з них може змінитися з плином часу. Також потрібно зберегти зовнішній ключ, який пов'язує їх з вихідним користувача і клієнтським ID. Є й інші значення, які потрібно зберігати, наприклад, адреси клієнта або інформація про включені у вартість податки начебто ПДВ.



Денормализованная таблиця
product_offered
отримала два нових атрибути:
price_per_unit
та
price
. Перший з них потрібен для зберігання актуальною ціни на момент пропозиції товару. Нормалізована модель буде показувати лише її поточний стан. Тому, як тільки ціна зміниться, зміниться і «цінова історія». Нововведення не просто прискорить роботу бази, воно покращує функціональність. Рядок price обчислює значення units_sold * price_per_unit. Таким чином, не потрібно робити розрахунок щоразу, як знадобиться поглянути на список запропонованих товарів. Це невелика ціна за збільшення продуктивності.

Зміни в таблиці
product_sold
зроблені з тих же міркувань. З тією лише різницею, що в даному випадку мова йде про проданих найменуваннях товару.



Таблиця
statistics_per_year
(статистика за рік) у тестовій моделі — абсолютно новий елемент. По суті, це денормализованная таблиця, оскільки всі її дані можуть бути розраховані з інших таблиць. Тут зберігається інформація про поточні завдання, успішно виконані завдання, зустрічі, дзвінки по кожному заданому клієнту. У цьому місці також зберігається загальна сума проведених нарахувань за кожен рік. Після введення, оновлення або видалення будь-яких даних в таблицях
task
,
meeting
,
call
та
product_sold
доводиться перераховувати ці дані для кожного клієнта і відповідного року. Так як зміни, швидше за все, стосуються лише поточного року, звіти за попередні роки тепер можуть залишатися без змін. Значення цієї таблиці обчислюються заздалегідь, тому ми зекономимо час і ресурси, коли нам знадобляться результати розрахунків.

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

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

Наш досвід

Ми в Латере багато займаємося оптимізацією продуктивності нашої білінгової системи «Гідра», що недивно, враховуючи обсяги наших клієнтів і специфіку телеком-галузі.

Один із прикладів у статті передбачає створення таблиці із проміжними підсумками для прискорення звітів. Звичайно, найскладніше в цьому підході — підтримувати актуальний стан такої таблиці. Іноді можна перекласти це завдання на СУБД — наприклад, використовувати матеріалізовані уявлення. Але, коли бізнес-логіка для отримання проміжних результатів виявляється трохи більш складним, актуальність денормализованных даних доводиться забезпечувати вручну.

«Гідра» має глибоко опрацьовану систему привілеїв для користувачів, операторів білінгу. Права видаються кількома способами — можна дозволити певні дії конкретному користувачеві, можна заздалегідь підготувати ролі і видати їм різні набори прав, можна наділити певний відділ спеціальними привілеями. Тільки уявіть, наскільки повільними стали б звернення до будь-яких сутностей системи, якщо б кожен раз потрібно було пройти всю цю ланцюжок, щоб переконатися: «так, цього співробітника дозволено укладати договори з юридичними особами» або «ні, у цього оператора недостатньо привілеїв для роботи з абонентами сусіднього філії». Замість цього ми окремо зберігаємо готовий агрегований список діючих прав для користувачів і оновлюємо його, коли в систему вносяться зміни, здатні на цей список вплинути. Співробітники переходять із одного відділу в інший набагато рідше, ніж відкривають чергового абонента в інтерфейсі білінгу, а значить обчислювати повний набір їх прав нам доводиться настільки ж рідше.

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

Інші технічні статті в нашому блозі:



Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.