Denormalisering af tabeller i databasen

8.1 Hvorfor er denormalisering nødvendig?

Den mest beregningsmæssigt dyre operation mellem store borde er sammenføjningen. Derfor, hvis det i en forespørgsel er nødvendigt at "ventilere" flere tabeller bestående af mange millioner rækker, vil DBMS bruge meget tid på en sådan behandling.

Brugeren kan på dette tidspunkt flytte væk for at drikke kaffe. Interaktiviteten i behandlingen forsvinder praktisk talt og nærmer sig batchbehandlingen. Endnu værre, i batch-tilstand modtager brugeren alle de anmodede data dagen før om morgenen og arbejder roligt med dem og forbereder nye anmodninger til aftenen.

For at undgå situationen med tunge sammenføjninger denormaliseres tabeller. Men ikke i hvert fald. Der er nogle regler, der giver dig mulighed for at betragte transaktionsmæssigt denormaliserede tabeller som "normaliserede" i henhold til reglerne for opbygning af tabeller til datavarehuse.

Der er to hovedskemaer, der betragtes som "normale" i analytisk behandling: "snefnug" og "stjerne". Navnene afspejler essensen godt og følger direkte af billedet af de relaterede tabeller.

I begge tilfælde er de såkaldte faktatabeller det centrale element i skemaet, der indeholder begivenheder, transaktioner, dokumenter og andre interessante ting af interesse for analytikeren. Men hvis ét dokument i en transaktionsdatabase er "smurt" ud over flere tabeller (mindst to: overskrifter og rækker af indhold), så svarer ét dokument i faktatabellen, mere præcist, hver af dets rækker eller et sæt af grupperede rækker. til én rekord.

Dette kan gøres ved at denormalisere de to tabeller ovenfor.

8.2 Eksempel på denormalisering

Nu kan du vurdere, hvor meget lettere det vil være for DBMS at udføre en forespørgsel, for eksempel af følgende type: at bestemme mængden af ​​melsalg til kunderne af Pirozhki LLC og Vatrushki CJSC for perioden.

I en normaliseret transaktionsdatabase:

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 analytiske database:

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

I stedet for en tung joinforbindelse mellem to tabeller med dokumenter og deres sammensætning med millioner af rækker, får DBMS direkte arbejde med faktatabellen og light joins med små hjælpetabeller, som du også kan undvære, ved at kende identifikatorerne.

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

Lad os vende tilbage til "stjerne" og "snefnug" -ordningerne. Bag kulisserne på det første billede var der borde med kunder, deres grupper, butikker, sælgere og faktisk varer. Når de er denormaliseret, er disse tabeller, kaldet dimensioner, også forbundet med faktatabellen. Hvis faktatabellen refererer til dimensionstabeller, der har links til andre dimensioner (dimensioner på andet niveau og derover), så kaldes et sådant skema et "snefnug".

Som du kan se, for forespørgsler, der inkluderer filtrering efter klientgrupper, skal du oprette en ekstra forbindelse.

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 dette tilfælde kan denormalisering fortsætte og droppe dimensionen på andet niveau til den første, hvilket gør det lettere at forespørge i faktatabellen.

Et skema, hvor en faktatabel kun refererer til dimensioner, der ikke har et andet niveau, kaldes et stjerneskema. Antallet af måletabeller svarer til antallet af "stråler" i stjernen.

Stjerneskemaet eliminerer fuldstændigt hierarkiet af dimensioner og behovet for at forbinde de tilsvarende tabeller i en enkelt forespørgsel.

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

Ulempen ved denormalisering er altid redundans , hvilket forårsager en stigning i størrelsen af ​​databasen i både transaktionelle og analytiske applikationer. Lad os beregne et omtrentligt delta på ovenstående eksempel på at konvertere "snefnug" til "stjerne".

I nogle DBMS, såsom Oracle, er der ingen specielle heltalstyper på niveau med databaseskemadefinitioner, du skal bruge den generiske booleske type numeric(N), hvor N er antallet af lagrede bits. Lagerstørrelsen af ​​et sådant tal beregnes ved hjælp af en speciel formel, der er angivet i dokumentationen for fysisk datalagring, og den overstiger som regel den for lavniveautyper som "16 bit heltal" med 1-3 bytes.

id_customer_groupAntag, at salgstabellen ikke bruger datakomprimering og indeholder omkring 500 millioner rækker, og antallet af kundegrupper er omkring 1000. I dette tilfælde kan vi bruge et kort heltal (shortint, smallint), der optager 2 bytes som en identifikatortype .

Vi vil antage, at vores DBMS understøtter en to-byte heltalstype (for eksempel PostgreSQL, SQL Server, Sybase og andre). Tilføjelse af den tilsvarende kolonne id_customer_grouptil salgstabellen vil øge dens størrelse med mindst 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Hvornår er denormalisering nødvendig?

Lad os se på nogle almindelige situationer, hvor denormalisering kan være nyttig.

Stort antal bordforbindelser

I forespørgsler til en fuldt normaliseret database skal du ofte tilslutte op til et dusin eller endda flere tabeller. Og hver forbindelse er en meget ressourcekrævende operation. Som følge heraf optager sådanne anmodninger serverressourcer og udføres langsomt.

I en sådan situation kan det hjælpe:

• denormalisering ved at reducere antallet af tabeller. Det er bedre at kombinere til én flere tabeller, der er små i størrelse, som indeholder sjældent ændrede (som de ofte siger, betinget konstant eller reference) information og information, der er tæt forbundet i betydning.
• Generelt, hvis du har brug for at deltage i mere end fem eller seks tabeller i et stort antal forespørgsler, bør du overveje at denormalisere databasen.
• Denormalisering ved at tilføje et ekstra felt til en af ​​tabellerne. I dette tilfælde vises dataredundans, yderligere handlinger er nødvendige for at opretholde databasens integritet.

Anslåede værdier

Ofte er forespørgsler langsomme og bruger mange ressourcer, hvor der udføres nogle komplekse beregninger, især ved brug af grupperinger og aggregerede funktioner (Sum, Max osv.). Nogle gange giver det mening at tilføje 1-2 yderligere kolonner til tabellen, der indeholder hyppigt anvendte (og vanskelige at beregne) beregnede data.

Antag, at du vil bestemme de samlede omkostninger for hver ordre. For at gøre dette skal du først bestemme prisen på hvert produkt (i henhold til formlen "antal produktenheder" * "enhedspris på produktet" - rabat). Derefter skal du gruppere omkostningerne efter ordrer.

Udførelsen af ​​denne forespørgsel er ret kompleks, og hvis databasen gemmer oplysninger om et stort antal ordrer, kan det tage lang tid. I stedet for at udføre en sådan forespørgsel, kan du bestemme dens pris på tidspunktet for afgivelse af en ordre og gemme den i en separat kolonne i ordretabellen. I dette tilfælde, for at opnå det ønskede resultat, er det nok at udtrække de forudberegnede værdier fra denne kolonne.

Oprettelse af en kolonne, der indeholder forudberegnede værdier, sparer meget tid, når du kører en forespørgsel, men kræver, at du opdaterer dataene i den kolonne rettidigt.

lang skygge

Hvis vi har store tabeller i databasen, der indeholder lange felter (Blob, Long osv.), så kan vi for alvor fremskynde udførelsen af ​​forespørgsler til sådan en tabel, hvis vi flytter de lange felter til en separat tabel. Vi ønsker f.eks. at oprette et katalog over fotos i databasen, herunder selve lagring af fotos i klatfelter (professionel kvalitet, høj opløsning og passende størrelse). Fra et normaliseringssynspunkt ville følgende tabelstruktur være helt korrekt:

• Foto ID
• Forfatter-id
• Kamera Model ID
• selve billedet (blob-felt)

Og lad os nu forestille os, hvor længe forespørgslen vil køre, når man tæller antallet af billeder taget af enhver forfatter ...

Den korrekte løsning (omend i strid med normaliseringsprincipperne) i en sådan situation ville være at oprette en anden tabel, der kun består af to felter - foto-id'et og et klatfelt med selve billedet. Så vil valg fra hovedtabellen (hvor der ikke længere er et stort klatfelt) gå med det samme, men når vi vil se selve billedet, ja, lad os vente ...

Hvordan bestemmer man, hvornår denormalisering er berettiget?

8.4 Fordele og ulemper ved denormalisering

En måde at afgøre, om visse trin er berettigede, er at foretage en analyse med hensyn til omkostninger og mulige fordele. Hvor meget vil en denormaliseret datamodel koste?

Bestem kravene (hvad vi ønsker at opnå) → bestemme datakravene (hvad vi skal følge) → find det minimumstrin, der opfylder disse krav → beregn implementeringsomkostningerne → implementer.

Omkostninger omfatter fysiske aspekter såsom diskplads, de ressourcer, der kræves for at administrere denne struktur, og tabte muligheder på grund af de tidsforsinkelser, der er forbundet med at vedligeholde denne proces. Du skal betale for denormalisering. En denormaliseret database øger dataredundansen, hvilket kan forbedre ydeevnen, men kræver mere indsats for at kontrollere relaterede data. Processen med at oprette applikationer vil blive vanskeligere, da dataene vil blive gentaget og sværere at spore. Derudover er implementeringen af ​​referentiel integritet ikke let - relaterede data er opdelt i forskellige tabeller.

Fordelene omfatter hurtigere forespørgselsydeevne og muligheden for at få et hurtigere svar. Du kan også høste andre fordele, herunder øget gennemløb, kundetilfredshed og produktivitet, samt mere effektiv brug af eksterne udviklerværktøjer.

Anmod om sats og præstationskonsistens

For eksempel er 72 % af de 1.000 forespørgsler, der genereres dagligt af en virksomhed, forespørgsler på oversigtsniveau, ikke drill-down-forespørgsler. Når du bruger en oversigtstabel, kører forespørgsler på cirka 6 sekunder i stedet for 4 minutter, hvilket resulterer i 3.000 minutter mindre behandlingstid. Selv efter justering for de 100 minutter, der skal bruges på at vedligeholde pivottabellerne hver uge, sparer det 2.500 minutter om ugen, hvilket retfærdiggør oprettelsen af ​​pivottabellen. Over tid kan det ske, at de fleste af forespørgslerne ikke vil være rettet til opsummerende data, men til detaljerede data. Jo færre forespørgsler, der bruger oversigtstabellen, jo lettere er det at droppe den uden at påvirke andre processer.

Og…

Kriterierne ovenfor er ikke de eneste, der skal tages i betragtning, når man skal beslutte sig for, om man skal tage det næste skridt i optimeringen. Andre faktorer skal overvejes, herunder forretningsprioriteter og slutbrugerbehov. Brugere skal forstå, hvordan systemarkitekturen fra et teknisk synspunkt påvirkes af kravet fra brugere, der ønsker, at alle anmodninger skal være gennemført på få sekunder. Den nemmeste måde at opnå denne forståelse på er at skitsere omkostningerne forbundet med at oprette og administrere sådanne tabeller.

8.5 Hvordan man kompetent implementerer denormalisering.

Gem detaljerede tabeller

For ikke at begrænse databasens muligheder, der er vigtige for virksomheden, er det nødvendigt at vedtage en strategi for sameksistens, ikke udskiftning, det vil sige at holde detaljerede tabeller til dyb analyse, tilføje denormaliserede strukturer til dem. For eksempel hittælleren. For erhvervslivet skal du kende antallet af besøg på en webside. Men til analysen (efter periode, efter land...) vil vi højst sandsynligt have brug for detaljerede data - en tabel med information om hvert besøg.

Brug af triggere

Det er muligt at denormalisere en databasestruktur og stadig nyde fordelene ved normalisering ved at bruge databasetriggere til at bevare integriteten integrityaf ​​duplikerede data.

For eksempel, når du tilføjer et beregnet felt, hænges hver af de kolonner, som det beregnede felt afhænger af, op med en trigger, der kalder en enkelt lagret procedure (dette er vigtigt!), som skriver de nødvendige data til det beregnede felt. Det er kun nødvendigt ikke at springe over nogen af ​​de kolonner, som det beregnede felt afhænger af.

Software support

Hvis du ikke bruger indbyggede triggere og lagrede procedurer, bør applikationsudviklere sørge for at sikre konsistensen af ​​data i en denormaliseret database.

Analogt med triggere skulle der være én funktion, der opdaterer alle felter, der er afhængige af, at feltet ændres.

konklusioner

Ved denormalisering er det vigtigt at opretholde en balance mellem at øge hastigheden på databasen og øge risikoen for inkonsistente data, mellem at gøre livet lettere for programmører, der skriver , Select-sog komplicere opgaven for dem, der leverer databasepopulation og dataopdateringer. Derfor er det nødvendigt at denormalisere databasen meget omhyggeligt, meget selektivt, kun hvor det er uundværligt.

Hvis det er umuligt at beregne fordele og ulemper ved denormalisering på forhånd, er det i første omgang nødvendigt at implementere en model med normaliserede tabeller, og først derefter, for at optimere problematiske forespørgsler, udføre denormalisering.

Det er vigtigt at indføre denormalisering gradvist og kun i de tilfælde, hvor der er gentagne hentning af relaterede data fra forskellige tabeller. Husk, når du dublerer data, vil antallet af poster stige, men antallet af læsninger vil falde. Det er også praktisk at gemme beregnede data i kolonner for at undgå unødvendige aggregerede valg.