8.1 Защо е необходима денормализация?

Най-скъпата от изчислителна гледна точка операция между големи таблици е присъединяването. Съответно, ако в една заявка е необходимо да се "проветрят" няколко таблици, състоящи се от много мorони редове, тогава СУБД ще отдели много време за такава обработка.

Потребителят в този момент може да се отдалечи, за да пие кафе. Интерактивността на обработката на практика изчезва и се доближава до тази на груповата обработка. Още по-лошо, в пакетен режим потребителят получава всички данни, поискани предния ден сутринта и спокойно работи с тях, подготвяйки нови заявки за вечерта.

За да се избегне ситуацията на тежки съединения, таблиците се денормализират. Но не Howто и да е. Има някои правила, които ви позволяват да разглеждате транзакционно денормализираните таблици като "нормализирани" според правилата за конструиране на таблици за хранorща за данни.

Има две основни схеми, които се считат за „нормални“ при аналитичната обработка: „снежинка“ и „звезда“. Имената отразяват добре същността и следват директно от картината на свързаните таблици.

И в двата случая така наречените таблици с факти са централният елемент на схемата, съдържащ събитията, транзакциите, documentите и други интересни неща за анализатора. Но ако в транзакционна база данни един document е „размазан“ в няколко таблици (поне две: заглавки и редове със съдържание), тогава в tableта на фактите един document, по-точно всеки от неговите редове or набор от групирани редове, съответства на един запис.

Това може да стане чрез денормализиране на двете таблици по-горе.

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 (’Pirozhki LLC’,	’Vatrushki CJSC’) AND
   p.name = ’Flour’ 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 ('Pirozhki LLC', 'Vatrushki CJSC') AND
   p.name = 'Flour' AND
   s.date BETWEEN '2014-01-01' AND '2014-02-01'
GROUP BY c.name

Вместо тежко свързване между две таблици с documentи и тяхната композиция с мorони редове, СУБД получава директна работа с tableта на фактите и леки съединения с малки спомагателни таблици, които можете да направите и без, знаейки идентификаторите.

Java university 

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

Да се ​​върнем към схемите "звезда" и "снежинка". Зад кулисите на първата снимка имаше маси с клиенти, техните групи, магазини, продавачи и всъщност стоки. Когато се денормализират, тези таблици, наречени измерения, също се присъединяват към tableта на фактите. Ако tableта на фактите се отнася до таблици с измерения, които имат връзки към други измерения (измерения от второ ниво и по-горе), тогава такава схема се нарича "снежинка".

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


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)

В този случай денормализирането може да продължи и да изпусне измерението от второ ниво към първото, което улеснява запитването към tableта с факти.

Схема, в която table с факти се отнася само до измерения, които нямат второ ниво, се нарича звездна схема. Броят на измервателните таблици съответства на броя на "лъчите" в звездата.

Схемата Star напълно елиминира йерархията на измеренията и необходимостта от обединяване на съответните таблици в една заявка.


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

Недостатъкът на денормализирането винаги е излишъкът , който води до увеличаване на размера на базата данни Howто в транзакционните, така и в аналитичните applications. Нека изчислим приблизителна делта на горния пример за преобразуване на "снежинка" в "звезда".

В някои СУБД, като Oracle, няма специални цели числа на нивото на дефинициите на схемата на базата данни, трябва да използвате общия булев тип numeric(N), където N е броят на съхранените битове. Размерът на съхранение на такова число се изчислява с помощта на специална формула, дадена в documentацията за физическо съхранение на данни, и като правило надвишава с 1-3 byteа този на типовете от ниско ниво като "16 bit integer".

id_customer_groupДа предположим, че tableта sales не използва компресиране на данни и съдържа около 500 мorона реда, а броят на клиентските групи е около 1000. В този случай можем да използваме кратко цяло число (shortint, smallint), заемащо 2 byteа като тип идентификатор .

Ще приемем, че нашата СУБД поддържа двуbyteов целочислен тип (например PostgreSQL, SQL Server, Sybase и други). След това добавянето на съответната колона id_customer_groupкъм tableта с продажби ще увеличи размера й с поне 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Кога е необходима денормализация?

Нека да разгледаме някои често срещани ситуации, при които денормализирането може да бъде полезно.

Голям брой съединения на таблици

При заявки към напълно нормализирана база данни често се налага да обединявате до дузина or дори повече таблици. И всяка връзка е много ресурсоемка операция. В резултат на това такива заявки консумират сървърни ресурси и се изпълняват бавно.

В такава ситуация може да помогне:

  • денормализиране чрез намаляване на броя на таблиците. По-добре е да комбинирате в една няколко таблици с малък размер, съдържащи рядко променена (Howто често казват, условно постоянна or референтна) информация и информация, която е тясно свързана по смисъл.
  • Като цяло, ако трябва да обедините повече от пет or шест таблици в голям брой заявки, трябва да помислите за денормализиране на базата данни.
  • Денормализация чрез добавяне на допълнително поле към една от таблиците. В този случай се появява излишък на данни, необходими са допълнителни действия за поддържане на целостта на базата данни.

Прогнозни стойности

Често заявките са бавни и консумират много ресурси, при което се извършват някои сложни изчисления, особено когато се използват групирания и агрегатни функции (Sum, Max и т.н.). Понякога има смисъл да добавите 1-2 допълнителни колони към tableта, съдържаща често използвани (и трудни за изчисляване) изчислени данни.

Да предположим, че искате да определите общата цена на всяка поръчка. За да направите това, първо трябва да определите себестойността на всеки продукт (по формулата "брой продуктови единици" * "единична цена на продукта" - отстъпка). След това трябва да групирате разходите по поръчки.

Изпълнението на тази заявка е доста сложно и, ако базата данни съхранява информация за голям брой поръчки, може да отнеме много време. Вместо да изпълнявате такава заявка, можете да определите цената й на етапа на подаване на поръчка и да я съхраните в отделна колона на tableта с поръчки. В този случай, за да получите желания резултат, е достатъчно да извлечете предварително изчислените стойности от тази колона.

Създаването на колона, която съдържа предварително изчислени стойности, спестява много време при изпълнение на заявка, но изисква да актуализирате данните в тази колона своевременно.

дълга периферия

Ако имаме големи таблици в базата данни, които съдържат дълги полета (Blob, Long и т.н.), тогава можем сериозно да ускорим изпълнението на заявки към такава table, ако преместим дългите полета в отделна table. Искаме, да речем, да създадем каталог от снимки в базата данни, включително да съхраняваме самите снимки в полета с петна (професионално качество, висока резолюция и подходящ размер). От гледна точка на нормализацията, следната структура на tableта би била абсолютно правилна:

  • ID със снимка
  • ID на автора
  • ID на модела на камерата
  • самата снимка (поле за петна)

А сега нека си представим колко дълго ще работи заявката, като броим броя на снимките, напequalsи от всеки автор ...

Правилното решение (макар и в нарушение на принципите на нормализиране) в такава ситуация би било да се създаде друга table, състояща се само от две полета - идентификационният номер на снимката и полето blob със самата снимка. Тогава селекциите от главната table (в която вече няма огромно поле за петна) ще излязат незабавно, но когато искаме да видим самата снимка, добре, нека изчакаме ...

Как да определим кога денормализирането е оправдано?

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

Един от начините да се определи дали определени стъпки са оправдани е да се направи анализ по отношение на разходите и възможните ползи. Колко ще струва един денормализиран модел на данни?

Определяне на изискванията (Howво искаме да постигнем) → определяне на изискванията за данни (Howво трябва да следваме) → намиране на минималната стъпка, която удовлетворява тези изисквания → изчисляване на разходите за внедряване → внедряване.

Разходите включват физически аспекти като дисково пространство, ресурси, необходими за управление на тази структура, и загубени възможности поради забавянията във времето, свързани с поддържането на този процес. Трябва да платите за денормализиране. Денормализираната база данни увеличава излишъка на данни, което може да подобри производителността, но изисква повече усorя за контрол на свързаните данни. Процесът на създаване на applications ще стане по-труден, тъй като данните ще се повтарят и ще бъдат по-трудни за проследяване. Освен това внедряването на референтна цялост не е лесно – свързаните данни са разделени в различни таблици.

Предимствата включват по-бързо изпълнение на заявките и възможност за получаване на по-бърз отговор. Можете също така да се възползвате от други предимства, включително повишена производителност, удовлетвореност на клиентите и производителност, Howто и по-ефективно използване на външни инструменти за разработчици.

Скорост на заявка и последователност на производителността

Например, 72% от 1000 заявки, генерирани ежедневно от едно предприятие, са заявки на ниво обобщение, а не заявки за детайлизиране. Когато използвате обобщена table, заявките се изпълняват за приблизително 6 секунди instead of за 4 minutesи, което води до 3000 minutesи по-малко време за обработка. Дори след коригиране на 100-те minutesи, които трябва да бъдат изразходвани за поддържане на обобщените таблици всяка седмица, това спестява 2500 minutesи на седмица, което оправдава създаването на обобщената table. С течение на времето може да се случи, че повечето от заявките няма да бъдат addressирани към обобщени данни, а към подробни данни. Колкото по-малко заявки използват обобщената table, толкова по-лесно е да я премахнете, без да засягате други процеси.

И…

Изброените по-горе критерии не са единствените, които трябва да вземете предвид, когато решавате дали да предприемете следващата стъпка в оптимизацията. Трябва да се вземат предвид и други фактори, включително бизнес приоритети и нужди на крайния потребител. Потребителите трябва да разберат How, от техническа гледна точка, системната архитектура се влияе от изискванията на потребителите, които искат всички заявки да бъдат завършени за няколко секунди. Най-лесният начин да постигнете това разбиране е да очертаете разходите, свързани със създаването и управлението на такива таблици.

8.5 Как да приложим компетентно денормализиране.

Запазете подробни таблици

За да не се ограничават възможностите на базата данни, които са важни за бизнеса, е необходимо да се приеме стратегия на съвместно съществуване, а не на заместване, тоест да се поддържат подробни таблици за задълбочен анализ, като се добавят денормализирани структури към тях. Например брояча на посещенията. За бизнеса трябва да знаете броя на посещенията на дадена уеб page. Но за анализа (по периоди, по държави...) най-вероятно ще ни трябват подробни данни - table с информация за всяко посещение.

Използване на тригери

Възможно е да денормализирате структура на база данни и все пак да се възползвате от предимствата на нормализирането, като използвате тригери на база данни, за да запазите целостта integrityна дублиращите се данни.

Например, когато добавяте изчисляемо поле, всяка от колоните, от които зависи изчисляемото поле, се закача с тригер, който извиква една съхранена proceduresа (това е важно!), Която записва необходимите данни в изчисляемото поле. Необходимо е само да не се пропуска нито една от колоните, от които зависи изчисленото поле.

Софтуерна поддръжка

Ако не използвате вградени тригери и съхранени proceduresи, тогава разработчиците на applications трябва да се погрижат за осигуряването на съгласуваност на данните в денормализирана база данни.

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

заключения

При денормализиране е важно да се поддържа баланс между увеличаване на скоростта на базата данни и увеличаване на риска от непоследователни данни, между улесняване на живота на програмистите, които пишат Select-s, и усложняване на задачата на тези, които осигуряват популация на база данни и актуализации на данни. Следователно е необходимо да се денормализира базата данни много внимателно, много селективно, само когато е необходимо.

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

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