8.1 Miért van szükség denormalizálásra?

A számítási szempontból legdrágább művelet a nagy táblák között az összekapcsolás. Ennek megfelelően, ha egy lekérdezés során több millió sorból álló táblát kell "szellőztetni", akkor a DBMS sok időt fog fordítani az ilyen feldolgozásra.

A felhasználó ekkor elköltözhet kávézni. A feldolgozás interaktivitása gyakorlatilag megszűnik, és megközelíti a kötegelt feldolgozást. Még rosszabb, hogy kötegelt módban a felhasználó reggel megkapja az összes előző nap kért adatot, és nyugodtan dolgozik velük, estére új kéréseket készítve elő.

A nehéz illesztések elkerülése érdekében a táblázatokat denormalizáljuk. De nem akárhogy. Vannak olyan szabályok, amelyek lehetővé teszik, hogy a tranzakciósan denormalizált táblákat "normalizáltnak" tekintse az adattárházak tábláinak létrehozására vonatkozó szabályok szerint.

Az analitikai feldolgozás során két fő séma tekinthető „normálisnak”: a „hópehely” és a „csillag”. A nevek jól tükrözik a lényeget, és közvetlenül következnek a kapcsolódó táblázatok képéből.

Mindkét esetben az úgynevezett ténytáblák a séma központi elemei, amelyek az elemzőt érdeklő eseményeket, tranzakciókat, dokumentumokat és egyéb érdekességeket tartalmazzák. De ha egy tranzakciós adatbázisban egy dokumentum több táblán (legalább kettőn: fejléceken és tartalomsorokon) van „elkenve”, akkor a ténytáblában egy dokumentum, pontosabban annak minden sora vagy csoportosított sorok halmaza felel meg. egy rekordhoz.

Ezt a fenti két táblázat denormalizálásával tehetjük meg.

8.2 Denormalizációs példa

Most már felmérheti, hogy mennyivel könnyebb lesz a DBMS-nek végrehajtani egy például a következő típusú lekérdezést: a Pirozhki LLC és a Vatrushki CJSC ügyfelei számára adott lisztértékesítés volumenének meghatározása az adott időszakban.

Normalizált tranzakciós adatbázisban:


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

Az analitikai adatbázisban:


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

A két dokumentumtábla és azok milliós soros kompozíciója közötti erős összekapcsolás helyett a DBMS közvetlenül a ténytáblázattal és a kis segédtáblázatokkal való könnyű összeillesztésekkel dolgozik, amelyeket az azonosítók ismeretében nélkülözhet is.

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

Térjünk vissza a "csillag" és a "hópehely" sémához. Az első kép kulisszái mögött a vásárlók, csoportjaik, üzletei, eladói és tulajdonképpen áruk asztalai voltak. Denormalizáláskor ezek a dimenzióknak nevezett táblák szintén a ténytáblázathoz kapcsolódnak. Ha a ténytáblázat olyan dimenziótáblákra hivatkozik, amelyek más dimenziókra (a második szint és afölötti dimenziókra) hivatkoznak, akkor az ilyen sémát "hópehelynek" nevezik.

Mint látható, az ügyfélcsoportok szerinti szűrést tartalmazó lekérdezésekhez további kapcsolatot kell létrehoznia.


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)

Ebben az esetben a denormalizálás folytatódhat, és a második szintű dimenziót az elsőre ejtheti, ami megkönnyíti a ténytábla lekérdezését.

Az olyan sémát, amelyben a ténytábla csak olyan dimenziókra hivatkozik, amelyeknek nincs második szintje, csillagsémának nevezzük. A mérési táblázatok száma megfelel a csillagban lévő "sugarak" számának.

A Star séma teljesen kiküszöböli a dimenziók hierarchiáját és a megfelelő táblák egyetlen lekérdezésben történő összekapcsolásának szükségességét.


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

A denormalizálás hátulütője mindig a redundancia , ami az adatbázis méretének növekedését okozza mind a tranzakciós, mind az analitikai alkalmazásokban. Számítsunk ki egy hozzávetőleges deltát a fenti példában, amikor a „hópehely”-t „csillaggá” alakítjuk.

Egyes DBMS-ekben, például az Oracle-ben, nincsenek speciális egész típusok az adatbázisséma-definíciók szintjén, az általános logikai típust kell használni numeric(N), ahol N a tárolt bitek száma. Egy ilyen szám tárolási méretét a fizikai adattárolás dokumentációjában megadott speciális képlet alapján számítják ki, és általában 1-3 bájttal haladja meg az alacsony szintű típusokét, mint például a "16 bites integer".

Tegyük fel, hogy az értékesítési tábla nem használ adattömörítést és kb. 500 millió sort tartalmaz, a vevőcsoportok száma pedig kb. 1000. Ebben az esetben egy 2 bájtot elfoglaló rövid egész számot (shortint, smallint) használhatunk azonosító típusként id_customer_group.

Feltételezzük, hogy DBMS-ünk támogatja a kétbájtos egész típust (például PostgreSQL, SQL Server, Sybase és mások). Ekkor a megfelelő oszlop hozzáadásával id_customer_groupaz értékesítési táblázat mérete legalább 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Mikor van szükség denormalizálásra?

Nézzünk meg néhány gyakori helyzetet, amikor a denormalizálás hasznos lehet.

Nagy számú asztalcsatlakozás

Egy teljesen normalizált adatbázis lekérdezésekor gyakran akár egy tucat vagy akár több táblát is össze kell kapcsolnia. És minden egyes kapcsolat nagyon erőforrás-igényes művelet. Ennek eredményeként az ilyen kérések a szerver erőforrásait fogyasztják, és lassan hajtódnak végre.

Ilyen helyzetben segíthet:

  • denormalizálás a táblák számának csökkentésével. Jobb, ha több kis méretű táblázatot egyesítünk egybe, amelyek ritkán változott (ahogy gyakran mondják, feltételesen állandó vagy referencia) információkat és jelentésükben szorosan összefüggő információkat tartalmaznak.
  • Általában, ha több mint öt vagy hat táblát kell összekapcsolnia nagyszámú lekérdezés során, érdemes megfontolni az adatbázis denormalizálását.
  • Denormalizálás egy további mező hozzáadásával az egyik táblához. Ilyenkor adatredundancia jelenik meg, további intézkedésekre van szükség az adatbázis integritásának megőrzéséhez.

Becsült értékek

A lekérdezések gyakran lassúak és sok erőforrást emésztenek fel, amelyek során összetett számításokat hajtanak végre, különösen csoportosítások és összesítő függvények (Sum, Max stb.) használatakor. Néha érdemes 1-2 további oszlopot hozzáadni a gyakran használt (és nehezen kiszámítható) számított adatokat tartalmazó táblázathoz.

Tegyük fel, hogy meg szeretné határozni az egyes rendelések teljes költségét. Ehhez először meg kell határoznia az egyes termékek költségét (a "termékegységek száma" * "a termék egységára" képlet szerint - kedvezmény). Ezt követően a költségeket rendelésenként kell csoportosítani.

Ennek a lekérdezésnek a végrehajtása meglehetősen bonyolult, és ha az adatbázis nagyszámú megrendelésről tárol információt, hosszú ideig tarthat. Egy ilyen lekérdezés végrehajtása helyett meghatározhatja annak költségét a rendelés feladásánál, és a rendelési táblázat külön oszlopában tárolhatja. Ebben az esetben a kívánt eredmény eléréséhez elegendő ebből az oszlopból kivonni az előre kiszámított értékeket.

Az előre kiszámított értékeket tartalmazó oszlop létrehozása sok időt takarít meg a lekérdezés futtatása során, de az oszlopban lévő adatokat kellő időben frissítenie kell.

hosszú karimájú

Ha nagy tábláink vannak az adatbázisban, amelyek hosszú mezőket tartalmaznak (Blob, Long stb.), akkor komolyan felgyorsíthatjuk egy ilyen táblára irányuló lekérdezések végrehajtását, ha a hosszú mezőket egy külön táblába helyezzük át. Szeretnénk, mondjuk, egy katalógust létrehozni az adatbázisban lévő fényképekről, beleértve a fényképek tárolását a blob mezőkben (professzionális minőség, nagy felbontás és megfelelő méret). A normalizálás szempontjából a következő táblázatszerkezet lenne teljesen helyes:

  • fényképes igazolvány
  • Szerző azonosító
  • A fényképezőgép típusazonosítója
  • maga a fotó (blob mező)

És most képzeljük el, mennyi ideig fut a lekérdezés, megszámolva a szerzők által készített fényképek számát ...

A helyes megoldás (bár a normalizálás alapelveit sérti) ilyen helyzetben az lenne, ha egy másik táblázatot hoznánk létre, amely csak két mezőből áll - a fényképes azonosítóból és egy blob mezőből, amely magában foglalja a fényképet. Ezután a főtáblázatból (amiben már nincs hatalmas blob mező) azonnal mennek a válogatások, de ha magát a fotót akarjuk látni, akkor várjunk...

Hogyan állapítható meg, hogy a denormalizálás mikor indokolt?

8.4 A denormalizálás előnyei és hátrányai

Annak megállapítására, hogy bizonyos lépések indokoltak-e, az egyik módja a költségek és a lehetséges hasznok elemzése. Mennyibe kerül egy denormalizált adatmodell?

Határozzuk meg a követelményeket (mit szeretnénk elérni) → határozzuk meg az adatigényeket (mit kell követnünk) → keressük meg azt a minimális lépést, amely ezeket a követelményeket kielégíti → számolja ki a megvalósítás költségeit → valósítsa meg.

A költségek magukban foglalják a fizikai szempontokat, például a lemezterületet, a struktúra kezeléséhez szükséges erőforrásokat és a folyamat fenntartásával kapcsolatos késések miatt elveszített lehetőségeket. A denormalizálásért fizetni kell. A denormalizált adatbázis növeli az adatredundanciát, ami javíthatja a teljesítményt, de több erőfeszítést igényel a kapcsolódó adatok ellenőrzése. Az alkalmazások létrehozásának folyamata nehezebbé válik, mivel az adatok ismétlődnek és nehezebben követhetők. Ráadásul a hivatkozási integritás megvalósítása nem egyszerű – a kapcsolódó adatok különböző táblázatokba vannak osztva.

Az előnyök közé tartozik a gyorsabb lekérdezési teljesítmény és a gyorsabb válaszadás lehetősége. Egyéb előnyökhöz is juthat, beleértve a megnövekedett átvitelt, a vevői elégedettséget és a termelékenységet, valamint a külső fejlesztői eszközök hatékonyabb használatát.

A kérés aránya és a teljesítmény összhangja

Például a vállalat által naponta generált 1000 lekérdezés 72%-a összefoglaló szintű lekérdezés, nem részletező lekérdezés. Összefoglaló táblázat használatakor a lekérdezések 4 perc helyett körülbelül 6 másodperc alatt futnak le, ami 3000 perccel kevesebb feldolgozási időt eredményez. Még a heti 100 perc beállítás után is, amit a pivot táblák karbantartásával kell tölteni, ez heti 2500 percet takarít meg, ami indokolja a pivot tábla létrehozását. Idővel előfordulhat, hogy a legtöbb lekérdezés nem összefoglaló adatokra, hanem részletes adatokra irányul. Minél kevesebb lekérdezés használja az összefoglaló táblát, annál könnyebb eldobni anélkül, hogy ez más folyamatokat érintene.

És…

A fent felsorolt ​​kritériumok nem az egyedüli szempontok, amelyeket figyelembe kell venni, amikor eldöntjük, hogy megtesszük-e a következő lépést az optimalizálásban. Más tényezőket is figyelembe kell venni, beleértve az üzleti prioritásokat és a végfelhasználói igényeket. A felhasználóknak meg kell érteniük, hogy technikai szempontból hogyan befolyásolja a rendszer architektúráját azon felhasználók követelményei, akik azt szeretnék, hogy minden kérés néhány másodperc alatt teljesítsen. Ennek megértésének legegyszerűbb módja az ilyen táblák létrehozásával és kezelésével kapcsolatos költségek felvázolása.

8.5 A denormalizálás kompetens végrehajtása.

Mentse el a részletes táblázatokat

Annak érdekében, hogy az adatbázis vállalkozás számára fontos képességei ne korlátozódjanak, az együttélési stratégiát kell elfogadni, nem a helyettesítést, vagyis részletes táblázatokat kell vezetni a mélyelemzéshez, denormalizált struktúrákkal kiegészítve. Például a találatszámláló. Az üzleti élethez tudnia kell, hogy hány látogatást tettek egy weboldalon. De az elemzéshez (időszakonként, országonként...) nagy valószínűséggel részletes adatokra lesz szükségünk - egy táblázatra minden látogatásról.

Triggerek használata

Lehetőség van az adatbázis-struktúra denormalizálására, és továbbra is élvezni a normalizálás előnyeit adatbázis-triggerek használatával, amelyek megőrzik a integrityduplikált adatok integritását.

Például egy számított mező hozzáadásakor minden oszlop, amelytől a számított mező függ, le van függesztve egy triggerrel, amely egyetlen tárolt eljárást hív meg (ez fontos!), amely a szükséges adatokat írja a számított mezőbe. Csak egyetlen olyan oszlopot sem kell kihagyni, amelytől a számított mező függ.

Szoftver támogatás

Ha nem használ beépített triggereket és tárolt eljárásokat, akkor az alkalmazásfejlesztőknek gondoskodniuk kell az adatok konzisztenciájáról egy denormalizált adatbázisban.

A triggerekkel analóg módon egy olyan funkciónak kell lennie, amely frissíti az összes olyan mezőt, amely a módosítandó mezőtől függ.

következtetéseket

A denormalizálás során fontos egyensúlyt tartani az adatbázis sebességének növelése és az inkonzisztens adatok kockázatának növelése, a programozók életének megkönnyítése Select-sés az adatbázis-populációt és adatfrissítést végzők munkájának megnehezítése között. Ezért nagyon körültekintően, nagyon szelektíven kell denormalizálni az adatbázist, csak ott, ahol az elengedhetetlen.

Ha nem lehetséges előre kiszámítani a denormalizálás előnyeit és hátrányait, akkor kezdetben egy modellt kell megvalósítani normalizált táblákkal, és csak ezután, a problémás lekérdezések optimalizálása érdekében, hajtsa végre a denormalizálást.

Fontos a denormalizálás fokozatos bevezetése, és csak azokban az esetekben, amikor a kapcsolódó adatok ismétlődő lekérése különböző táblákból történik. Ne feledje, hogy az adatok duplikálásakor a rekordok száma nő, de az olvasások száma csökken. Az is kényelmes, hogy a számított adatokat oszlopokban tárolja, hogy elkerülje a szükségtelen összesített kijelölést.