8.1 Hvorfor er denormalisering nødvendig?

Den mest beregningsmessig dyre operasjonen mellom store bord er sammenføyningen. Følgelig, hvis det i en spørring er nødvendig å "ventilere" flere tabeller som består av mange millioner rader, vil DBMS bruke mye tid på slik behandling.

Brukeren kan på dette tidspunktet flytte for å drikke kaffe. Interaktiviteten til prosessering forsvinner praktisk talt og nærmer seg batchbehandlingen. Enda verre, i batch-modus mottar brukeren alle dataene som ble bedt om dagen før om morgenen og jobber rolig med dem og forbereder nye forespørsler for kvelden.

For å unngå situasjonen med tunge sammenføyninger, denormaliseres tabeller. Men ikke uansett. Det er noen regler som lar deg vurdere transaksjonelt denormaliserte tabeller som "normaliserte" i henhold til reglene for å konstruere tabeller for datavarehus.

Det er to hovedordninger som anses som "normale" i analytisk behandling: "snøfnugg" og "stjerne". Navnene gjenspeiler essensen godt og følger direkte av bildet av de relaterte tabellene.

I begge tilfeller er de såkalte faktatabellene det sentrale elementet i skjemaet, og inneholder hendelser, transaksjoner, dokumenter og andre interessante ting av interesse for analytikeren. Men hvis ett dokument i en transaksjonsdatabase er "smurt" over flere tabeller (minst to: overskrifter og rader med innhold), så tilsvarer ett dokument i faktatabellen, mer presist, hver av dets rader eller et sett med grupperte rader. til én rekord.

Dette kan gjøres ved å denormalisere de to tabellene ovenfor.

8.2 Eksempel på denormalisering

Nå kan du vurdere hvor mye lettere det vil være for DBMS å utføre en spørring, for eksempel av følgende type: å bestemme volumet av melsalg til kundene til Pirozhki LLC og Vatrushki CJSC for perioden.

I en normalisert transaksjonsdatabase:


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 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

I stedet for en tung sammenføyning mellom to tabeller med dokumenter og deres sammensetning med millioner av rader, får DBMS direkte arbeid med faktatabellen og lette sammenføyninger med små hjelpetabeller, som du også kan klare deg uten, med kjennskap til identifikatorene.

Java webinar 

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

La oss gå tilbake til "stjerne" og "snøfnugg" -ordningene. Bak kulissene på det første bildet var tabeller over kunder, deres grupper, butikker, selgere og faktisk varer. Når de er denormalisert, blir disse tabellene, kalt dimensjoner, også koblet til faktatabellen. Hvis faktatabellen refererer til dimensjonstabeller som har koblinger til andre dimensjoner (dimensjoner på andre nivå og høyere), så kalles et slikt skjema et "snøfnugg".

Som du kan se, for spørringer som inkluderer filtrering etter klientgrupper, må du opprette en ekstra tilkobling.


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 tilfellet kan denormalisering fortsette og slippe andrenivådimensjonen til den første, noe som gjør det lettere å spørre etter faktatabellen.

Et skjema der en faktatabell kun refererer til dimensjoner som ikke har et andre nivå kalles et stjerneskjema. Antall måletabeller tilsvarer antall "stråler" i stjernen.

Stjerneskjemaet eliminerer fullstendig hierarkiet av dimensjoner og behovet for å slå sammen de tilsvarende tabellene i en enkelt spørring.


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

Ulempen med denormalisering er alltid redundans , som forårsaker en økning i størrelsen på databasen i både transaksjonelle og analytiske applikasjoner. La oss beregne et omtrentlig delta på eksemplet ovenfor på å konvertere "snøfnugg" til "stjerne".

I noen DBMS, som Oracle, er det ingen spesielle heltallstyper på nivået av databaseskjemadefinisjoner, du må bruke den generiske boolske typen , numeric(N)der N er antall lagrede biter. Lagringsstørrelsen til et slikt tall beregnes ved hjelp av en spesiell formel gitt i dokumentasjonen for fysisk datalagring, og som regel overstiger den lavnivåtyper som "16 bit heltall" med 1-3 byte.

id_customer_groupAnta at salgstabellen ikke bruker datakomprimering og inneholder ca. 500 millioner rader, og antall kundegrupper er ca. 1000. I dette tilfellet kan vi bruke et kort heltall (shortint, smallint) som opptar 2 byte som identifikatortype .

Vi vil anta at vår DBMS støtter en to-byte heltallstype (for eksempel PostgreSQL, SQL Server, Sybase og andre). Å legge til den tilsvarende kolonnen id_customer_groupi salgstabellen vil øke størrelsen med minst 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Når er denormalisering nødvendig?

La oss se på noen vanlige situasjoner der denormalisering kan være nyttig.

Stort antall tabeller

I spørringer til en fullstendig normalisert database, må du ofte slå sammen opptil et dusin eller enda flere tabeller. Og hver forbindelse er en svært ressurskrevende operasjon. Som et resultat forbruker slike forespørsler serverressurser og utføres sakte.

I en slik situasjon kan det hjelpe:

  • denormalisering ved å redusere antall tabeller. Det er bedre å kombinere til én flere tabeller som er små i størrelse, som inneholder sjelden endret (som de ofte sier, betinget konstant eller referanse) informasjon, og informasjon som er nært beslektet i betydning.
  • Generelt, hvis du trenger å slå sammen mer enn fem eller seks tabeller i et stort antall spørringer, bør du vurdere å denormalisere databasen.
  • Denormalisering ved å legge til et ekstra felt i en av tabellene. I dette tilfellet vises dataredundans, ytterligere handlinger kreves for å opprettholde integriteten til databasen.

Anslåtte verdier

Ofte er spørringer trege og bruker mye ressurser, der noen komplekse beregninger utføres, spesielt når du bruker grupperinger og aggregerte funksjoner (Sum, Max, etc.). Noen ganger er det fornuftig å legge til 1-2 ekstra kolonner i tabellen som inneholder ofte brukte (og vanskelige å beregne) beregnede data.

Anta at du vil bestemme den totale kostnaden for hver ordre. For å gjøre dette må du først bestemme kostnadene for hvert produkt (i henhold til formelen "antall produktenheter" * "enhetspris på produktet" - rabatt). Etter det må du gruppere kostnadene etter bestillinger.

Å utføre denne spørringen er ganske komplisert, og hvis databasen lagrer informasjon om et stort antall bestillinger, kan det ta lang tid. I stedet for å utføre en slik spørring, kan du bestemme kostnadene ved bestillingsstadiet og lagre den i en egen kolonne i ordretabellen. I dette tilfellet, for å oppnå ønsket resultat, er det nok å trekke ut de forhåndsberegnede verdiene fra denne kolonnen.

Å lage en kolonne som inneholder forhåndsberegnet verdier sparer mye tid når du kjører en spørring, men krever at du oppdaterer dataene i den kolonnen i tide.

lang brem

Hvis vi har store tabeller i databasen som inneholder lange felt (Blob, Long osv.), så kan vi for alvor få fart på utføringen av spørringer til en slik tabell hvis vi flytter de lange feltene til en egen tabell. Vi ønsker for eksempel å lage en katalog med bilder i databasen, inkludert å lagre bildene i seg selv i blob-felt (profesjonell kvalitet, høy oppløsning og passende størrelse). Fra normaliseringssynspunktet vil følgende tabellstruktur være helt korrekt:

  • Bilde-ID
  • Forfatter-ID
  • Kameramodell ID
  • selve bildet (blob-felt)

Og la oss nå forestille oss hvor lenge spørringen vil kjøre, teller antall bilder tatt av en forfatter ...

Den riktige løsningen (riktignok i strid med normaliseringsprinsippene) i en slik situasjon ville være å lage en annen tabell bestående av bare to felt - bilde-ID og et blob-felt med selve bildet. Da vil valgene fra hovedtabellen (hvor det ikke lenger er et stort klumpfelt) gå umiddelbart, men når vi vil se selve bildet, vel, la oss vente ...

Hvordan bestemme når denormalisering er berettiget?

8.4 Fordeler og ulemper med denormalisering

En måte å avgjøre om visse trinn er berettiget, er å gjennomføre en analyse med tanke på kostnader og mulige fordeler. Hvor mye vil en denormalisert datamodell koste?

Bestem kravene (hva vi ønsker å oppnå) → bestemme datakravene (hva vi må følge) → finn minimumstrinnet som tilfredsstiller disse kravene → beregne implementeringskostnadene → implementere.

Kostnadene inkluderer fysiske aspekter som diskplass, ressursene som kreves for å administrere denne strukturen, og tapte muligheter på grunn av tidsforsinkelser knyttet til vedlikehold av denne prosessen. Du må betale for denormalisering. En denormalisert database øker dataredundansen, noe som kan forbedre ytelsen, men krever mer innsats for å kontrollere relaterte data. Prosessen med å lage applikasjoner vil bli vanskeligere, ettersom dataene vil bli gjentatt og vanskeligere å spore. I tillegg er implementeringen av referanseintegritet ikke lett - relaterte data er delt inn i forskjellige tabeller.

Fordelene inkluderer raskere søkeytelse og muligheten til å få raskere respons. Du kan også høste andre fordeler, inkludert økt gjennomstrømning, kundetilfredshet og produktivitet, samt mer effektiv bruk av eksterne utviklerverktøy.

Be om pris og ytelseskonsistens

For eksempel er 72 % av de 1000 spørringene som genereres daglig av en bedrift, oppsummeringsnivåspørringer, ikke drill-down-spørringer. Når du bruker en sammendragstabell, kjører søk på omtrent 6 sekunder i stedet for 4 minutter, noe som resulterer i 3000 minutter mindre behandlingstid. Selv etter å ha justert for de 100 minuttene som må brukes på å vedlikeholde pivottabellene hver uke, sparer det 2500 minutter per uke, noe som rettferdiggjør opprettelsen av pivottabellen. Over tid kan det hende at de fleste forespørslene ikke blir adressert til oppsummeringsdata, men til detaljerte data. Jo færre søk som bruker sammendragstabellen, desto lettere er det å droppe den uten å påvirke andre prosesser.

Og…

Kriteriene som er oppført ovenfor er ikke de eneste du bør vurdere når du skal bestemme om du skal ta neste skritt i optimalisering. Andre faktorer må vurderes, inkludert forretningsprioriteringer og sluttbrukerbehov. Brukere må forstå hvordan, fra et teknisk ståsted, systemarkitekturen påvirkes av kravet til brukere som ønsker at alle forespørsler skal fullføres på noen få sekunder. Den enkleste måten å oppnå denne forståelsen på er å skissere kostnadene forbundet med å lage og administrere slike tabeller.

8.5 Hvordan implementere denormalisering kompetent.

Lagre detaljerte tabeller

For ikke å begrense mulighetene til databasen som er viktige for virksomheten, er det nødvendig å vedta en strategi for sameksistens, ikke erstatning, det vil si å holde detaljerte tabeller for dyp analyse, og legge til denormaliserte strukturer til dem. For eksempel trefftelleren. For bedrifter må du vite antall besøk på en nettside. Men for analysen (etter periode, etter land...) vil vi høyst sannsynlig trenge detaljerte data – en tabell med informasjon om hvert besøk.

Bruker triggere

Det er mulig å denormalisere en databasestruktur og fortsatt nyte fordelene med normalisering ved å bruke databaseutløsere for å bevare integriteten integritytil dupliserte data.

For eksempel, når du legger til et beregnet felt, blir hver av kolonnene som det beregnede feltet avhenger av, hengt opp med en trigger som kaller en enkelt lagret prosedyre (dette er viktig!), som skriver de nødvendige dataene til det beregnede feltet. Det er bare nødvendig å ikke hoppe over noen av kolonnene som det beregnede feltet avhenger av.

Programvarestøtte

Hvis du ikke bruker innebygde triggere og lagrede prosedyrer, bør applikasjonsutviklere sørge for å sikre konsistensen av data i en denormalisert database.

Analogt med triggere bør det være én funksjon som oppdaterer alle felt som er avhengige av at feltet endres.

konklusjoner

Ved denormalisering er det viktig å opprettholde en balanse mellom å øke hastigheten på databasen og øke risikoen for inkonsistente data, mellom å gjøre livet enklere for programmerere som skriver , Select-sog komplisere oppgaven til de som gir databasepopulasjon og dataoppdateringer. Derfor er det nødvendig å denormalisere databasen veldig nøye, veldig selektivt, bare der det er uunnværlig.

Hvis det er umulig å beregne fordeler og ulemper ved denormalisering på forhånd, er det i utgangspunktet nødvendig å implementere en modell med normaliserte tabeller, og først da, for å optimalisere problematiske spørsmål, utføre denormalisering.

Det er viktig å introdusere denormalisering gradvis og kun for de tilfellene hvor det er gjentatte henting av relaterte data fra forskjellige tabeller. Husk at når du dupliserer data, vil antallet poster øke, men antallet avlesninger vil reduseres. Det er også praktisk å lagre beregnede data i kolonner for å unngå unødvendige aggregerte valg.