8.1 De ce este necesară denormalizarea?

Operația cea mai costisitoare din punct de vedere computațional între tabele mari este îmbinarea. În consecință, dacă într-o singură interogare este necesară „ventilarea” mai multor tabele formate din multe milioane de rânduri, atunci DBMS-ul va petrece mult timp unei astfel de procesări.

Utilizatorul în acest moment se poate îndepărta pentru a bea cafea. Interactivitatea prelucrării practic dispare și se apropie de cea a prelucrării în lot. Și mai rău, în modul batch, utilizatorul primește dimineața toate datele solicitate cu o zi înainte și lucrează cu calm cu ele, pregătind noi solicitări pentru seară.

Pentru a evita situația îmbinărilor grele, tabelele sunt denormalizate. Dar nu oricum. Există unele reguli care vă permit să considerați tabelele denormalizate tranzacțional ca fiind „normalizate” conform regulilor de construire a tabelelor pentru depozitele de date.

Există două scheme principale care sunt considerate „normale” în procesarea analitică: „fulg de zăpadă” și „stea”. Numele reflectă bine esența și urmează direct din imaginea tabelelor aferente.

În ambele cazuri, așa-numitele tabele de fapte sunt elementul central al schemei, conținând evenimentele, tranzacțiile, documentele și alte lucruri interesante de interes pentru analist. Dar dacă într-o bază de date tranzacțională un document este „untat” pe mai multe tabele (cel puțin două: antete și rânduri de conținut), atunci în tabelul de fapte un document, mai precis, fiecare dintre rândurile sale sau un set de rânduri grupate, corespunde. la o singură înregistrare.

Acest lucru se poate face prin denormalizarea celor două tabele de mai sus.

8.2 Exemplu de denormalizare

Acum puteți evalua cât de ușor va fi pentru SGBD să execute o interogare, de exemplu, de următorul tip: pentru a determina volumul vânzărilor de făină către clienții Pirozhki LLC și Vatrushki CJSC pentru perioada respectivă.

Într-o bază de date tranzacțională normalizată:


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

În baza de date analitică:


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

În loc de o îmbinare grea între două tabele de documente și alcătuirea lor cu milioane de rânduri, SGBD-ul primește lucru direct cu tabelul de fapte și îmbinări ușoare cu tabele auxiliare mici, de care puteți face și fără, cunoscând identificatorii.

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

Să revenim la schemele „stea” și „fulg de zăpadă”. În culisele primei imagini se aflau mese cu clienții, grupurile acestora, magazinele, vânzătorii și, de fapt, mărfurile. Când sunt denormalizate, aceste tabele, numite dimensiuni, sunt, de asemenea, asociate cu tabelul de fapte. Dacă tabelul de fapte se referă la tabelele de dimensiuni care au legături către alte dimensiuni (dimensiuni ale celui de-al doilea nivel și mai sus), atunci o astfel de schemă se numește „fulg de zăpadă”.

După cum puteți vedea, pentru interogările care includ filtrarea pe grupuri de clienți, trebuie să faceți o conexiune suplimentară.


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)

În acest caz, denormalizarea poate continua și reduce dimensiunea de al doilea nivel la primul, facilitând interogarea tabelului de fapte.

O schemă în care un tabel de fapte se referă doar la dimensiuni care nu au un al doilea nivel se numește schemă stea. Numărul de tabele de măsurare corespunde numărului de „raze” din stea.

Schema Star elimină complet ierarhia dimensiunilor și necesitatea de a uni tabelele corespunzătoare într-o singură interogare.


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

Dezavantajul denormalizării este întotdeauna redundanța , care determină o creștere a dimensiunii bazei de date atât în ​​aplicațiile tranzacționale, cât și în cele analitice. Să calculăm o deltă aproximativă pe exemplul de mai sus de conversie a „fulgului de zăpadă” în „stea”.

În unele SGBD, cum ar fi Oracle, nu există tipuri speciale de numere întregi la nivelul definițiilor schemei bazei de date, trebuie să utilizați tipul boolean generic numeric(N), unde N este numărul de biți stocați. Dimensiunea de stocare a unui astfel de număr este calculată folosind o formulă specială dată în documentația pentru stocarea fizică a datelor și, de regulă, o depășește pe cea a tipurilor de nivel scăzut precum „întreg pe 16 biți” cu 1-3 octeți.

id_customer_groupSă presupunem că tabelul de vânzări nu utilizează compresia datelor și conține aproximativ 500 de milioane de rânduri, iar numărul de grupuri de clienți este de aproximativ 1000. În acest caz, putem folosi un număr întreg scurt (shortint, smallint) care ocupă 2 octeți ca tip de identificator .

Vom presupune că SGBD-ul nostru acceptă un tip întreg de doi octeți (de exemplu, PostgreSQL, SQL Server, Sybase și altele). Apoi adăugarea coloanei corespunzătoare id_customer_groupla tabelul de vânzări va crește dimensiunea acesteia cu cel puțin 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Când este necesară denormalizarea?

Să ne uităm la câteva situații comune în care denormalizarea poate fi utilă.

Număr mare de îmbinări la masă

În interogările către o bază de date complet normalizată, adesea trebuie să vă alăturați până la o duzină sau chiar mai multe tabele. Și fiecare conexiune este o operațiune care necesită foarte multe resurse. Drept urmare, astfel de solicitări consumă resursele serverului și sunt efectuate lent.

Într-o astfel de situație, poate ajuta:

  • denormalizare prin reducerea numărului de tabele. Este mai bine să combinați într-unul singur mai multe tabele de dimensiuni mici, care conțin informații rar modificate (cum se spune adesea, constantă condiționat sau de referință) și informații care sunt strâns legate în sens.
  • În general, dacă trebuie să uniți mai mult de cinci sau șase tabele într-un număr mare de interogări, ar trebui să luați în considerare denormalizarea bazei de date.
  • Denormalizare prin adăugarea unui câmp suplimentar la unul dintre tabele. În acest caz, apare redundanța datelor, sunt necesare acțiuni suplimentare pentru a menține integritatea bazei de date.

Valori estimate

Adesea, interogările sunt lente și consumă o mulțime de resurse, în care se efectuează unele calcule complexe, mai ales atunci când se folosesc grupări și funcții de agregare (Suma, Max etc.). Uneori este logic să adăugați 1-2 coloane suplimentare la tabel care conțin date calculate utilizate frecvent (și greu de calculat).

Să presupunem că doriți să determinați costul total al fiecărei comenzi. Pentru a face acest lucru, trebuie mai întâi să determinați costul fiecărui produs (după formula „număr de unități de produs” * „prețul unitar al produsului” - reducere). După aceea, trebuie să grupați costurile pe comenzi.

Executarea acestei interogări este destul de complexă și, dacă baza de date stochează informații despre un număr mare de comenzi, poate dura mult timp. În loc să executați o astfel de interogare, puteți determina costul acesteia în etapa plasării unei comenzi și îl puteți stoca într-o coloană separată a tabelului de comenzi. În acest caz, pentru a obține rezultatul dorit, este suficient să extrageți valorile precalculate din această coloană.

Crearea unei coloane care conține valori precalculate economisește mult timp atunci când rulați o interogare, dar vă necesită să actualizați datele din acea coloană în timp util.

bor lung

Dacă avem tabele mari în baza de date care conțin câmpuri lungi (Blob, Long etc.), atunci putem accelera serios execuția interogărilor la un astfel de tabel dacă mutăm câmpurile lungi într-un tabel separat. Dorim, să zicem, să creăm un catalog de fotografii în baza de date, inclusiv stocarea propriilor fotografii în câmpuri blob (calitate profesională, rezoluție înaltă și dimensiune adecvată). Din punct de vedere al normalizării, următoarea structură a tabelului ar fi absolut corectă:

  • ID foto
  • ID autor
  • ID-ul modelului camerei
  • fotografia în sine (câmp blob)

Și acum să ne imaginăm cât timp va rula interogarea, numărând numărul de fotografii făcute de orice autor...

Soluția corectă (deși încălcând principiile normalizării) într-o astfel de situație ar fi crearea unui alt tabel format din doar două câmpuri - ID-ul cu fotografie și un câmp blob cu fotografia în sine. Apoi selecțiile din tabelul principal (în care nu mai există un câmp de blob imens) vor merge instantaneu, dar când vrem să vedem fotografia în sine, ei bine, să așteptăm...

Cum să determinați când denormalizarea este justificată?

8.4 Avantaje și dezavantaje ale denormalizării

O modalitate de a determina dacă anumiți pași sunt justificați este efectuarea unei analize în ceea ce privește costurile și posibilele beneficii. Cât va costa un model de date denormalizate?

Determinați cerințele (ceea ce dorim să realizăm) → determinați cerințele de date (ce trebuie să urmărim) → găsiți pasul minim care satisface aceste cerințe → calculați costurile de implementare → implementați.

Costurile includ aspecte fizice, cum ar fi spațiul pe disc, resursele necesare pentru a gestiona această structură și oportunități pierdute din cauza întârzierilor asociate cu menținerea acestui proces. Trebuie să plătești pentru denormalizare. O bază de date denormalizată crește redundanța datelor, ceea ce poate îmbunătăți performanța, dar necesită mai mult efort pentru a controla datele aferente. Procesul de creare a aplicațiilor va deveni mai dificil, deoarece datele vor fi repetate și mai greu de urmărit. În plus, implementarea integrității referențiale nu este ușoară - datele aferente sunt împărțite în diferite tabele.

Beneficiile includ performanță mai rapidă a interogărilor și capacitatea de a obține un răspuns mai rapid. De asemenea, puteți beneficia de alte beneficii, inclusiv debitul crescut, satisfacția clienților și productivitatea, precum și utilizarea mai eficientă a instrumentelor pentru dezvoltatori externi.

Rata de solicitare și consistența performanței

De exemplu, 72% din cele 1.000 de interogări generate zilnic de o întreprindere sunt interogări la nivel de rezumat, nu interogări detaliate. Când utilizați un tabel rezumat, interogările rulează în aproximativ 6 secunde în loc de 4 minute, ceea ce duce la un timp de procesare cu 3.000 de minute mai mic. Chiar și după ajustarea pentru cele 100 de minute care trebuie petrecute menținând tabelele pivot în fiecare săptămână, se economisesc 2.500 de minute pe săptămână, ceea ce justifică crearea tabelului pivot. În timp, se poate întâmpla ca majoritatea interogărilor să nu fie adresate unor date rezumative, ci unor date detaliate. Cu cât sunt mai puține interogări care folosesc tabelul rezumat, cu atât este mai ușor să îl renunțați fără a afecta alte procese.

Și…

Criteriile enumerate mai sus nu sunt singurele de luat în considerare atunci când decideți dacă să faceți următorul pas în optimizare. Alți factori trebuie luați în considerare, inclusiv prioritățile de afaceri și nevoile utilizatorilor finali. Utilizatorii trebuie să înțeleagă cum, din punct de vedere tehnic, arhitectura sistemului este afectată de cerința utilizatorilor care doresc ca toate solicitările să fie finalizate în câteva secunde. Cel mai simplu mod de a obține această înțelegere este de a sublinia costurile asociate cu crearea și gestionarea unor astfel de tabele.

8.5 Cum să implementați în mod competent denormalizarea.

Salvați tabele detaliate

Pentru a nu limita capabilitățile bazei de date care sunt importante pentru afacere, este necesar să se adopte o strategie de conviețuire, nu de înlocuire, adică să păstreze tabele detaliate pentru analiză profundă, adăugându-le structuri denormalizate. De exemplu, contorul de lovituri. Pentru afaceri, trebuie să știți numărul de vizite pe o pagină web. Dar pentru analiza (pe perioada, pe tara...) foarte probabil vom avea nevoie de date detaliate - un tabel cu informatii despre fiecare vizita.

Utilizarea declanșatoarelor

Este posibil să denormalizați o structură a bazei de date și să vă bucurați în continuare de beneficiile normalizării prin utilizarea declanșatorilor bazei de date pentru a păstra integritatea integritydatelor duplicate.

De exemplu, la adăugarea unui câmp calculat, fiecare dintre coloanele de care depinde câmpul calculat, este închisă cu un declanșator care apelează o singură procedură stocată (acest lucru este important!), care scrie datele necesare în câmpul calculat. Este necesar doar să nu săriți peste niciuna dintre coloanele de care depinde câmpul calculat.

Suport software

Dacă nu utilizați declanșatoare încorporate și proceduri stocate, atunci dezvoltatorii de aplicații ar trebui să aibă grijă să asigure consistența datelor într-o bază de date denormalizată.

Prin analogie cu declanșatoarele, ar trebui să existe o singură funcție care actualizează toate câmpurile care depind de câmpul modificat.

concluzii

La denormalizare, este important să se mențină un echilibru între creșterea vitezei bazei de date și creșterea riscului de date inconsecvente, între ușurarea vieții programatorilor care scrie Select-sși complicarea sarcinii celor care oferă populația bazei de date și actualizările datelor. Prin urmare, este necesară denormalizarea bazei de date foarte atent, foarte selectiv, doar acolo unde este indispensabilă.

Dacă este imposibil să se calculeze în avans avantajele și dezavantajele denormalizării, atunci inițial este necesar să se implementeze un model cu tabele normalizate și numai apoi, pentru a optimiza interogările problematice, se efectuează denormalizarea.

Este important să se introducă denormalizarea treptat și numai pentru acele cazuri în care există preluari repetate de date asociate din tabele diferite. Amintiți-vă, la duplicarea datelor, numărul de înregistrări va crește, dar numărul de citiri va scădea. De asemenea, este convenabil să stocați datele calculate în coloane pentru a evita selecțiile agregate inutile.