8.1 Varför är denormalisering nödvändig?

Den beräkningsmässigt dyraste operationen mellan stora tabeller är sammanfogningen. Följaktligen, om det i en fråga är nödvändigt att "ventilera" flera tabeller som består av många miljoner rader, kommer DBMS att spendera mycket tid på sådan bearbetning.

Användaren kan vid denna tidpunkt flytta för att dricka kaffe. Interaktiviteten i bearbetningen försvinner praktiskt taget och närmar sig batchbearbetningen. Ännu värre, i batch-läge får användaren all information som efterfrågas dagen innan på morgonen och arbetar lugnt med dem och förbereder nya förfrågningar för kvällen.

För att undvika situationen med tunga sammanfogningar denormaliseras tabeller. Men inte i alla fall. Det finns några regler som gör att du kan betrakta transaktionsmässigt denormaliserade tabeller som "normaliserade" enligt reglerna för att konstruera tabeller för datalager.

Det finns två huvudscheman som anses vara "normala" i analytisk bearbetning: "snöflinga" och "stjärna". Namnen speglar essensen väl och följer direkt från bilden av de relaterade tabellerna.

I båda fallen är de så kallade faktatabellerna det centrala elementet i schemat, som innehåller händelser, transaktioner, dokument och andra intressanta saker av intresse för analytikern. Men om ett dokument i en transaktionsdatabas är "utsmetat" över flera tabeller (minst två: rubriker och innehållsrader), så motsvarar ett dokument i faktatabellen, mer exakt, var och en av dess rader eller en uppsättning grupperade rader. till ett rekord.

Detta kan göras genom att denormalisera de två tabellerna ovan.

8.2 Exempel på denormalisering

Nu kan du utvärdera hur mycket lättare det kommer att vara för DBMS att utföra en fråga, till exempel av följande typ: att bestämma volymen av mjölförsäljningen till kunderna till Pirozhki LLC och Vatrushki CJSC för perioden.

I en normaliserad transaktionsdatabas:


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

I den analytiska databasen:


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

Istället för en tung koppling mellan två tabeller med dokument och deras sammansättning med miljontals rader, får DBMS direkt arbete med faktatabellen och lätta kopplingar med små hjälptabeller, som du också kan klara dig utan, med att känna till identifierarna.

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

Låt oss återgå till "stjärna" och "snöflinga" -scheman. Bakom kulisserna på den första bilden fanns bord med kunder, deras grupper, butiker, säljare och faktiskt varor. När de denormaliseras är dessa tabeller, som kallas dimensioner, också sammanfogade med faktatabellen. Om faktatabellen hänvisar till dimensionstabeller som har länkar till andra dimensioner (dimensioner på den andra nivån och högre), så kallas ett sådant schema för en "snöflinga".

Som du kan se måste du göra en extra anslutning för frågor som inkluderar filtrering efter klientgrupper.


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)

I det här fallet kan denormaliseringen fortsätta och släppa den andra nivåns dimension till den första, vilket gör det lättare att fråga faktatabellen.

Ett schema där en faktatabell endast refererar till dimensioner som inte har en andra nivå kallas ett stjärnschema. Antalet mättabeller motsvarar antalet "strålar" i stjärnan.

Stjärnschemat eliminerar helt hierarkin av dimensioner och behovet av att sammanfoga motsvarande tabeller i en enda fråga.


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

Nackdelen med denormalisering är alltid redundans , vilket orsakar en ökning av storleken på databasen i både transaktions- och analytiska applikationer. Låt oss beräkna ett ungefärligt delta på ovanstående exempel på att konvertera "snöflinga" till "stjärna".

I vissa DBMS, som Oracle, finns det inga speciella heltalstyper på nivån för databasschemadefinitioner, du måste använda den generiska booleska typen , numeric(N)där N är antalet lagrade bitar. Lagringsstorleken för ett sådant nummer beräknas med hjälp av en speciell formel som ges i dokumentationen för fysisk datalagring, och som regel överstiger den lågnivåtyper som "16 bitars heltal" med 1-3 byte.

id_customer_groupAnta att försäljningstabellen inte använder datakomprimering och innehåller cirka 500 miljoner rader, och antalet kundgrupper är cirka 1000. I det här fallet kan vi använda ett kort heltal (shortint, smallint) som upptar 2 byte som identifierartyp .

Vi kommer att anta att vår DBMS stöder en två-byte heltalstyp (till exempel PostgreSQL, SQL Server, Sybase och andra). Om du sedan lägger till motsvarande kolumn id_customer_groupi försäljningstabellen ökar dess storlek med minst 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 När behövs denormalisering?

Låt oss titta på några vanliga situationer där denormalisering kan vara användbar.

Stort antal bordsanslutningar

I frågor till en helt normaliserad databas måste du ofta ansluta upp till ett dussin eller till och med fler tabeller. Och varje anslutning är en mycket resurskrävande operation. Som ett resultat förbrukar sådana förfrågningar serverresurser och utförs långsamt.

I en sådan situation kan det hjälpa:

  • denormalisering genom att minska antalet tabeller. Det är bättre att kombinera till en flera tabeller som är små i storlek, som innehåller sällan ändrad (som de ofta säger, villkorligt konstant eller referens) information och information som är nära relaterad till betydelse.
  • I allmänhet, om du behöver ansluta fler än fem eller sex tabeller i ett stort antal frågor, bör du överväga att avnormalisera databasen.
  • Denormalisering genom att lägga till ytterligare ett fält i en av tabellerna. I det här fallet visas dataredundans, ytterligare åtgärder krävs för att upprätthålla databasens integritet.

Uppskattade värden

Ofta är frågor långsamma och kräver mycket resurser, där vissa komplexa beräkningar utförs, särskilt när man använder grupperingar och aggregerade funktioner (Sum, Max, etc.). Ibland är det vettigt att lägga till ytterligare 1-2 kolumner i tabellen som innehåller ofta använda (och svårberäknade) beräknade data.

Anta att du vill bestämma den totala kostnaden för varje beställning. För att göra detta måste du först bestämma kostnaden för varje produkt (enligt formeln "antal produktenheter" * "enhetspris för produkten" - rabatt). Därefter måste du gruppera kostnaderna efter beställningar.

Att köra denna fråga är ganska komplicerat och, om databasen lagrar information om ett stort antal beställningar, kan det ta lång tid. Istället för att utföra en sådan fråga kan du bestämma dess kostnad vid beställningsstadiet och lagra den i en separat kolumn i beställningstabellen. I det här fallet, för att få det önskade resultatet, räcker det att extrahera de förberäknade värdena från denna kolumn.

Att skapa en kolumn som innehåller förberäknade värden sparar mycket tid när du kör en fråga, men kräver att du uppdaterar data i den kolumnen i tid.

lång brätte

Om vi ​​har stora tabeller i databasen som innehåller långa fält (Blob, Long, etc.) så kan vi på allvar snabba på exekveringen av frågor till en sådan tabell om vi flyttar de långa fälten till en separat tabell. Vi vill, säg, skapa en katalog med foton i databasen, inklusive att lagra fotona själva i blobfält (professionell kvalitet, hög upplösning och lämplig storlek). Ur normaliseringssynpunkt skulle följande tabellstruktur vara helt korrekt:

  • Foto-ID
  • Författar-ID
  • Kameramodell ID
  • själva fotot (blob-fält)

Och låt oss nu föreställa oss hur länge frågan kommer att köras, räknat antalet foton som tagits av någon författare ...

Den korrekta lösningen (om än att bryta mot normaliseringsprinciperna) i en sådan situation skulle vara att skapa ytterligare en tabell som bara består av två fält - foto-ID och ett blobfält med själva fotot. Sedan kommer val från huvudtabellen (där det inte längre finns ett stort blobfält) att gå direkt, men när vi vill se själva bilden, ja, låt oss vänta ...

Hur avgör man när denormalisering är motiverad?

8.4 För- och nackdelar med denormalisering

Ett sätt att avgöra om vissa steg är motiverade är att göra en analys avseende kostnader och möjliga fördelar. Hur mycket kommer en denormaliserad datamodell att kosta?

Bestäm kraven (vad vi vill uppnå) → bestäm datakraven (vad vi behöver följa) → hitta det minsta steget som uppfyller dessa krav → beräkna implementeringskostnaderna → implementera.

Kostnaderna inkluderar fysiska aspekter som diskutrymme, de resurser som krävs för att hantera denna struktur och förlorade möjligheter på grund av de tidsförseningar som är förknippade med att underhålla denna process. Du måste betala för denormalisering. En denormaliserad databas ökar dataredundansen, vilket kan förbättra prestandan men kräver mer ansträngning för att kontrollera relaterad data. Processen att skapa applikationer kommer att bli svårare, eftersom data kommer att upprepas och svårare att spåra. Dessutom är implementeringen av referensintegritet inte lätt - relaterade data är uppdelade i olika tabeller.

Fördelarna inkluderar snabbare frågeprestanda och möjligheten att få ett snabbare svar. Du kan också dra nytta av andra fördelar, inklusive ökad genomströmning, kundnöjdhet och produktivitet, samt effektivare användning av externa utvecklarverktyg.

Begär frekvens och prestandakonsistens

Till exempel är 72 % av de 1 000 frågor som genereras dagligen av ett företag sammanfattningsfrågor, inte detaljerade frågor. När du använder en sammanfattningstabell körs frågorna på cirka 6 sekunder istället för 4 minuter, vilket resulterar i 3 000 minuter kortare bearbetningstid. Även efter justering för de 100 minuter som måste läggas på att underhålla pivottabellerna varje vecka, sparar det 2 500 minuter per vecka, vilket motiverar skapandet av pivottabellen. Med tiden kan det hända att de flesta av frågorna inte kommer att riktas till sammanfattande data, utan till detaljerade data. Ju färre frågor som använder sammanfattningstabellen, desto lättare är det att släppa den utan att påverka andra processer.

Och…

Kriterierna ovan är inte de enda att ta hänsyn till när man bestämmer sig för om man ska ta nästa steg i optimeringen. Andra faktorer måste beaktas, inklusive affärsprioriteringar och slutanvändarnas behov. Användare måste förstå hur, ur teknisk synvinkel, systemarkitekturen påverkas av kravet från användare som vill att alla förfrågningar ska slutföras på några sekunder. Det enklaste sättet att uppnå denna förståelse är att beskriva kostnaderna för att skapa och hantera sådana tabeller.

8.5 Hur man på ett kompetent sätt implementerar denormalisering.

Spara detaljerade tabeller

För att inte begränsa funktionerna i databasen som är viktiga för verksamheten är det nödvändigt att anta en strategi för samexistens, inte ersättning, det vill säga hålla detaljerade tabeller för djup analys, lägga till denormaliserade strukturer till dem. Till exempel träffräknaren. För företag måste du veta antalet besök på en webbsida. Men för analysen (per period, per land...) kommer vi med stor sannolikhet att behöva detaljerad data - en tabell med information om varje besök.

Använder triggers

Det är möjligt att avnormalisera en databasstruktur och ändå njuta av fördelarna med normalisering genom att använda databasutlösare för att bevara integriteten integrityhos dubbletter av data.

Till exempel, när du lägger till ett beräknat fält, hängs var och en av kolumnerna som det beräknade fältet beror på med en trigger som anropar en enda lagrad procedur (detta är viktigt!), som skriver nödvändiga data till det beräknade fältet. Det är bara nödvändigt att inte hoppa över någon av kolumnerna som det beräknade fältet beror på.

Programvarustöd

Om du inte använder inbyggda triggers och lagrade procedurer, bör applikationsutvecklare se till att säkerställa konsistensen av data i en denormaliserad databas.

I analogi med triggers bör det finnas en funktion som uppdaterar alla fält som är beroende av att fältet ändras.

Slutsatser

Vid denormalisering är det viktigt att upprätthålla en balans mellan att öka hastigheten på databasen och öka risken för inkonsekventa data, mellan att göra livet lättare för programmerare som skriver , Select-soch att komplicera uppgiften för dem som tillhandahåller databaspopulation och datauppdateringar. Därför är det nödvändigt att avnormalisera databasen mycket noggrant, mycket selektivt, bara där det är oumbärligt.

Om det är omöjligt att beräkna för- och nackdelar med denormalisering i förväg, är det initialt nödvändigt att implementera en modell med normaliserade tabeller, och först då, för att optimera problematiska frågor, utföra denormalisering.

Det är viktigt att införa denormalisering gradvis och endast för de fall där det finns upprepade hämtningar av relaterade data från olika tabeller. Kom ihåg att när du duplicerar data kommer antalet poster att öka, men antalet läsningar kommer att minska. Det är också bekvämt att lagra beräknade data i kolumner för att undvika onödiga sammanlagda val.