Denormalisatie van tabellen in de database

8.1 Waarom is denormalisatie nodig?

De meest computationeel dure bewerking tussen grote tabellen is de join. Dienovereenkomstig, als het in één query nodig is om meerdere tabellen bestaande uit vele miljoenen rijen te "ventileren", dan zal het DBMS veel tijd besteden aan dergelijke verwerking.

De gebruiker kan op dit moment weggaan om koffie te drinken. De interactiviteit van verwerking verdwijnt praktisch en benadert die van batchverwerking. Erger nog, in batchmodus ontvangt de gebruiker alle gegevens die de dag ervoor 's ochtends zijn aangevraagd en werkt hij er rustig mee om nieuwe verzoeken voor de avond voor te bereiden.

Om de situatie van zware joins te voorkomen, worden tabellen gedenormaliseerd. Maar hoe dan ook niet. Er zijn enkele regels waarmee u transactioneel gedenormaliseerde tabellen als "genormaliseerd" kunt beschouwen volgens de regels voor het maken van tabellen voor datawarehouses.

Er zijn twee hoofdschema's die als "normaal" worden beschouwd bij analytische verwerking: "sneeuwvlok" en "ster". De namen geven de essentie goed weer en volgen direct uit de afbeelding van de gerelateerde tafels.

In beide gevallen vormen de zogenaamde feitentabellen het centrale element van het schema, met de gebeurtenissen, transacties, documenten en andere interessante zaken die de analist interesseren. Maar als in een transactionele database één document over meerdere tabellen is "uitgesmeerd" (minstens twee: kopteksten en rijen met inhoud), dan komt in de feitentabel één document, meer precies, elk van zijn rijen of een reeks gegroepeerde rijen overeen tot één record.

Dit kan worden gedaan door de twee bovenstaande tabellen te denormaliseren.

8.2 Voorbeeld van denormalisatie

Nu kunt u evalueren hoeveel gemakkelijker het voor het DBMS zal zijn om een ​​query uit te voeren, bijvoorbeeld van het volgende type: om het volume van de meelverkoop aan de klanten van Pirozhki LLC en Vatrushki CJSC voor de periode te bepalen.

In een genormaliseerde transactionele database:

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

In de analytische 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

In plaats van een zware join tussen twee tabellen met documenten en hun samenstelling met miljoenen rijen, krijgt het DBMS direct werk met de feitentabel en lichte joins met kleine hulptabellen, wat je ook zonder de identifiers kunt doen.

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

Laten we terugkeren naar de schema's "ster" en "sneeuwvlok". Achter de schermen van de eerste foto waren tafels met klanten, hun groepen, winkels, verkopers en in feite goederen. Wanneer ze gedenormaliseerd zijn, worden deze tabellen, dimensies genoemd, ook samengevoegd met de feitentabel. Als de feitentabel verwijst naar dimensietabellen die links hebben naar andere dimensies (dimensies van het tweede niveau en hoger), dan wordt zo'n schema een "sneeuwvlok" genoemd.

Zoals u kunt zien, moet u voor query's die filteren op klantgroepen bevatten, een extra verbinding maken.

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)

In dit geval kan denormalisatie doorgaan en de dimensie van het tweede niveau naar de eerste laten vallen, waardoor het gemakkelijker wordt om de feitentabel te doorzoeken.

Een schema waarin een feitentabel alleen verwijst naar dimensies die geen tweede niveau hebben, wordt een sterschema genoemd. Het aantal meettafels komt overeen met het aantal "stralen" in de ster.

Het Star-schema elimineert volledig de hiërarchie van dimensies en de noodzaak om de overeenkomstige tabellen in één enkele query samen te voegen.

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

De keerzijde van denormalisatie is altijd redundantie , waardoor de database groter wordt in zowel transactionele als analytische toepassingen. Laten we een geschatte delta berekenen op het bovenstaande voorbeeld van het omzetten van "sneeuwvlok" naar "ster".

In sommige DBMS, zoals Oracle, zijn er geen speciale typen gehele getallen op het niveau van databaseschemadefinities. U moet het generieke booleaanse type gebruiken numeric(N), waarbij N het aantal opgeslagen bits is. De opslagcapaciteit van een dergelijk nummer wordt berekend met behulp van een speciale formule die wordt gegeven in de documentatie voor fysieke gegevensopslag, en overschrijdt in de regel die van low-level typen zoals "16 bit integer" met 1-3 bytes.

id_customer_groupStel dat de verkooptabel geen datacompressie gebruikt en ongeveer 500 miljoen rijen bevat, en het aantal klantgroepen ongeveer 1000 is. In dit geval kunnen we een kort geheel getal (shortint, smallint) van 2 bytes gebruiken als identificatietype .

We gaan ervan uit dat ons DBMS een geheel getal van twee bytes ondersteunt (bijvoorbeeld PostgreSQL, SQL Server, Sybase en andere). Als u vervolgens de overeenkomstige kolom id_customer_groupaan de verkooptabel toevoegt, wordt de kolom met minstens 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Wanneer is denormalisatie nodig?

Laten we eens kijken naar enkele veelvoorkomende situaties waarin denormalisatie nuttig kan zijn.

Groot aantal tafel joins

Bij query's naar een volledig genormaliseerde database moet u vaak een dozijn of zelfs meer tabellen samenvoegen. En elke verbinding is een zeer resource-intensieve operatie. Als gevolg hiervan verbruiken dergelijke verzoeken serverbronnen en worden ze langzaam uitgevoerd.

In een dergelijke situatie kan het helpen:

• denormalisatie door vermindering van het aantal tabellen. Het is beter om meerdere tabellen te combineren die klein van formaat zijn, die zelden gewijzigde (zoals ze vaak zeggen, voorwaardelijk constant of referentie) informatie bevatten, en informatie die qua betekenis nauw verwant is.
• Als u meer dan vijf of zes tabellen moet samenvoegen in een groot aantal query's, kunt u in het algemeen overwegen de database te denormaliseren.
• Denormalisatie door een extra veld toe te voegen aan een van de tabellen. In dit geval verschijnt gegevensredundantie, aanvullende acties zijn vereist om de integriteit van de database te behouden.

Geschatte waarden

Query's zijn vaak traag en verbruiken veel bronnen, waarbij enkele complexe berekeningen worden uitgevoerd, vooral bij het gebruik van groeperingen en aggregatiefuncties (Sum, Max, etc.). Soms is het zinvol om 1-2 extra kolommen toe te voegen aan de tabel met veelgebruikte (en moeilijk te berekenen) berekende gegevens.

Stel dat u de totale kosten van elke bestelling wilt bepalen. Om dit te doen, moet u eerst de kosten van elk product bepalen (volgens de formule "aantal producteenheden" * "eenheidsprijs van het product" - korting). Daarna moet u de kosten per bestelling groeperen.

Het uitvoeren van deze query is behoorlijk complex en kan lang duren als de database informatie over een groot aantal bestellingen opslaat. In plaats van een dergelijke zoekopdracht uit te voeren, kunt u de kosten ervan bepalen tijdens het plaatsen van een bestelling en deze opslaan in een aparte kolom van de besteltabel. In dit geval volstaat het om de vooraf berekende waarden uit deze kolom te extraheren om het gewenste resultaat te verkrijgen.

Het maken van een kolom die vooraf berekende waarden bevat, bespaart veel tijd bij het uitvoeren van een query, maar vereist wel dat u de gegevens in die kolom tijdig bijwerkt.

lange rand

Als we grote tabellen in de database hebben die lange velden bevatten (Blob, Long, etc.), dan kunnen we de uitvoering van query's naar zo'n tabel serieus versnellen als we de lange velden naar een aparte tabel verplaatsen. We willen bijvoorbeeld een catalogus van foto's in de database maken, inclusief het opslaan van foto's zelf in blob-velden (professionele kwaliteit, hoge resolutie en geschikte grootte). Vanuit het oogpunt van normalisatie zou de volgende tabelstructuur absoluut correct zijn:

• Foto-ID
• Auteur-ID
• Cameramodel-ID
• de foto zelf (blobveld)

En laten we ons nu eens voorstellen hoe lang de zoekopdracht zal lopen, het aantal foto's tellend dat door een auteur is gemaakt ...

De juiste oplossing (zij het in strijd met de principes van normalisatie) in een dergelijke situatie zou zijn om een ​​andere tabel te maken die uit slechts twee velden bestaat: het foto-ID en een blobveld met de foto zelf. Dan gaan selecties uit de hoofdtabel (waarin geen enorm blobveld meer is) meteen weg, maar als we de foto zelf willen zien, nou, laten we wachten ...

Hoe te bepalen wanneer denormalisatie gerechtvaardigd is?

8.4 Voors en tegens van denormalisatie

Een manier om te bepalen of bepaalde stappen gerechtvaardigd zijn, is door een analyse uit te voeren op kosten en mogelijke baten. Hoeveel kost een gedenormaliseerd datamodel?

Bepaal de vereisten (wat we willen bereiken) → bepaal de datavereisten (wat we moeten volgen) → vind de minimale stap die aan deze vereisten voldoet → bereken de implementatiekosten → implementeer.

De kosten omvatten fysieke aspecten zoals schijfruimte, de middelen die nodig zijn om deze structuur te beheren en gemiste kansen als gevolg van de tijdvertragingen die gepaard gaan met het onderhouden van dit proces. Je moet betalen voor denormalisatie. Een gedenormaliseerde database verhoogt de gegevensredundantie, wat de prestaties kan verbeteren, maar meer inspanning vereist om gerelateerde gegevens te beheren. Het proces van het maken van applicaties wordt moeilijker, omdat de gegevens worden herhaald en moeilijker te volgen zijn. Bovendien is de implementatie van referentiële integriteit niet eenvoudig - gerelateerde gegevens zijn verdeeld in verschillende tabellen.

Voordelen zijn onder meer snellere queryprestaties en de mogelijkheid om sneller antwoord te krijgen. U kunt ook profiteren van andere voordelen, waaronder een hogere doorvoer, klanttevredenheid en productiviteit, evenals een efficiënter gebruik van externe ontwikkelaarstools.

Vraagsnelheid en prestatieconsistentie

Zo zijn bijvoorbeeld 72% van de 1000 query's die dagelijks door een onderneming worden gegenereerd, query's op samenvattingsniveau, geen drill-down-query's. Bij gebruik van een overzichtstabel worden query's in ongeveer 6 seconden uitgevoerd in plaats van 4 minuten, wat resulteert in 3000 minuten minder verwerkingstijd. Zelfs na correctie voor de 100 minuten die wekelijks besteed moeten worden aan het onderhouden van de draaitabellen, scheelt dat 2.500 minuten per week, wat het maken van de draaitabel rechtvaardigt. Na verloop van tijd kan het gebeuren dat de meeste vragen niet gericht zijn op samenvattingsgegevens, maar op gedetailleerde gegevens. Hoe minder query's de overzichtstabel gebruiken, hoe gemakkelijker het is om deze te laten vallen zonder andere processen te beïnvloeden.

En…

Bovenstaande criteria zijn niet de enige waarmee u rekening moet houden bij de beslissing of u de volgende stap in optimalisatie wilt zetten. Er moet rekening worden gehouden met andere factoren, waaronder zakelijke prioriteiten en behoeften van eindgebruikers. Gebruikers moeten begrijpen hoe, vanuit technisch oogpunt, de systeemarchitectuur wordt beïnvloed door de eisen van gebruikers die willen dat alle aanvragen binnen enkele seconden worden afgehandeld. De gemakkelijkste manier om dit inzicht te krijgen, is door de kosten te schetsen die gepaard gaan met het maken en beheren van dergelijke tabellen.

8.5 Hoe denormalisatie vakkundig te implementeren.

Sla gedetailleerde tabellen op

Om de mogelijkheden van de database die belangrijk zijn voor het bedrijf niet te beperken, is het noodzakelijk om een ​​strategie van coëxistentie te hanteren, niet van vervanging, dat wil zeggen gedetailleerde tabellen bij te houden voor diepgaande analyse, door er gedenormaliseerde structuren aan toe te voegen. Bijvoorbeeld de hitteller. Voor zaken moet u het aantal bezoeken aan een webpagina weten. Maar voor de analyse (per periode, per land...) hebben we zeer waarschijnlijk gedetailleerde gegevens nodig - een tabel met informatie over elk bezoek.

Triggers gebruiken

Het is mogelijk om een ​​databasestructuur te denormaliseren en toch te genieten van de voordelen van normalisatie door databasetriggers te gebruiken om de integrityintegriteit van dubbele gegevens te behouden.

Wanneer u bijvoorbeeld een berekend veld toevoegt, wordt elk van de kolommen waarvan het berekende veld afhangt, opgehangen met een trigger die een enkele opgeslagen procedure aanroept (dit is belangrijk!), Die de benodigde gegevens naar het berekende veld schrijft. Het is alleen nodig om geen van de kolommen over te slaan waarvan het berekende veld afhangt.

Software-ondersteuning

Als u geen ingebouwde triggers en opgeslagen procedures gebruikt, moeten applicatieontwikkelaars zorgen voor de consistentie van gegevens in een gedenormaliseerde database.

Naar analogie met triggers zou er één functie moeten zijn die alle velden bijwerkt die afhankelijk zijn van het veld dat wordt gewijzigd.

conclusies

Bij denormalisatie is het belangrijk om een ​​evenwicht te bewaren tussen het verhogen van de snelheid van de database en het vergroten van het risico op inconsistente gegevens, tussen het gemakkelijker maken voor programmeurs die schrijven Select-s, en het ingewikkelder maken van de taak van degenen die zorgen voor databasepopulatie en gegevensupdates. Daarom is het noodzakelijk om de database zeer zorgvuldig, zeer selectief te denormaliseren, alleen waar dit onmisbaar is.

Als het onmogelijk is om de voor- en nadelen van denormalisatie vooraf te berekenen, dan is het in eerste instantie nodig om een ​​model met genormaliseerde tabellen te implementeren en pas daarna, om problematische queries te optimaliseren, denormalisatie uit te voeren.

Het is belangrijk om denormalisatie geleidelijk in te voeren en alleen voor die gevallen waarin er herhaaldelijk gerelateerde gegevens uit verschillende tabellen worden opgehaald. Onthoud dat bij het dupliceren van gegevens het aantal records zal toenemen, maar het aantal leesbewerkingen zal afnemen. Het is ook handig om berekende gegevens in kolommen op te slaan om onnodige geaggregeerde selecties te voorkomen.