Denormalizzazione delle tabelle nel database

8.1 Perché è necessaria la denormalizzazione?

L'operazione più costosa dal punto di vista computazionale tra tabelle di grandi dimensioni è il join. Di conseguenza, se in una query è necessario "ventilare" diverse tabelle composte da molti milioni di righe, il DBMS impiegherà molto tempo su tale elaborazione.

L'utente in questo momento può allontanarsi per bere il caffè. L'interattività dell'elaborazione praticamente scompare e si avvicina a quella dell'elaborazione batch. Peggio ancora, in modalità batch, l'utente riceve al mattino tutti i dati richiesti il ​​giorno prima e lavora con loro con calma, preparando nuove richieste per la sera.

Per evitare la situazione di join pesanti, le tabelle vengono denormalizzate. Ma non comunque. Esistono alcune regole che consentono di considerare le tabelle transazionalmente denormalizzate come "normalizzate" secondo le regole per la costruzione di tabelle per i data warehouse.

Esistono due schemi principali considerati "normali" nell'elaborazione analitica: "fiocco di neve" e "stella". I nomi ne rispecchiano bene l'essenza e seguono direttamente l'immagine delle relative tavole.

In entrambi i casi, le cosiddette tabelle dei fatti sono l'elemento centrale dello schema, contenente gli eventi, le transazioni, i documenti e altre cose interessanti di interesse per l'analista. Ma se in un database transazionale un documento è “spalmato” su più tabelle (almeno due: intestazioni e righe di contenuto), allora nella tabella dei fatti corrisponde un documento, più precisamente ciascuna delle sue righe o un insieme di righe raggruppate a un record.

Questo può essere fatto denormalizzando le due tabelle sopra.

8.2 Esempio di denormalizzazione

Ora puoi valutare quanto sarà più facile per il DBMS eseguire una query, ad esempio, del seguente tipo: determinare il volume delle vendite di farina ai clienti di Pirozhki LLC e Vatrushki CJSC per il periodo.

In un database transazionale normalizzato:

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

Nel database analitico:

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

Invece di un pesante join tra due tabelle di documenti e la loro composizione con milioni di righe, il DBMS ottiene un lavoro diretto con la tabella dei fatti e unioni leggere con piccole tabelle ausiliarie, di cui puoi anche fare a meno, conoscendo gli identificatori.

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

Torniamo agli schemi "stella" e "fiocco di neve". Dietro le quinte della prima immagine c'erano tavoli di clienti, i loro gruppi, negozi, venditori e, appunto, merci. Quando vengono denormalizzate, anche queste tabelle, denominate dimensioni, vengono unite alla tabella dei fatti. Se la tabella dei fatti fa riferimento a tabelle delle dimensioni che hanno collegamenti ad altre dimensioni (dimensioni di secondo livello e superiori), tale schema viene chiamato "fiocco di neve".

Come puoi vedere, per le query che includono il filtraggio per gruppi di client, devi effettuare una connessione aggiuntiva.

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 questo caso, la denormalizzazione può continuare e rilasciare la dimensione di secondo livello al primo, semplificando l'interrogazione della tabella dei fatti.

Uno schema in cui una tabella dei fatti fa riferimento solo a dimensioni che non hanno un secondo livello è chiamato schema a stella. Il numero di tabelle di misurazione corrisponde al numero di "raggi" nella stella.

Lo schema Star elimina completamente la gerarchia delle dimensioni e la necessità di unire le tabelle corrispondenti in un'unica query.

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

Lo svantaggio della denormalizzazione è sempre la ridondanza , che provoca un aumento delle dimensioni del database sia nelle applicazioni transazionali che analitiche. Calcoliamo un delta approssimativo sull'esempio precedente di conversione di "fiocco di neve" in "stella".

In alcuni DBMS, come Oracle, non ci sono tipi interi speciali a livello di definizioni dello schema del database, è necessario utilizzare il tipo booleano generico numeric(N), dove N è il numero di bit memorizzati. La dimensione di archiviazione di tale numero viene calcolata utilizzando una formula speciale fornita nella documentazione per l'archiviazione fisica dei dati e, di norma, supera quella dei tipi di basso livello come "16 bit integer" di 1-3 byte.

Supponiamo che la tabella delle vendite non utilizzi la compressione dei dati e contenga circa 500 milioni di righe e il numero di gruppi di clienti sia circa 1000. In questo caso, possiamo utilizzare un numero intero breve (shortint, smallint) che occupa 2 byte come tipo di identificatore id_customer_group.

Supponiamo che il nostro DBMS supporti un tipo intero a due byte (ad esempio, PostgreSQL, SQL Server, Sybase e altri). Quindi l'aggiunta della colonna corrispondente id_customer_groupalla tabella delle vendite ne aumenterà le dimensioni di almeno 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Quando è necessaria la denormalizzazione?

Diamo un'occhiata ad alcune situazioni comuni in cui la denormalizzazione può essere utile.

Numero elevato di join di tabella

Nelle query a un database completamente normalizzato, spesso devi unire fino a una dozzina o anche più tabelle. E ogni connessione è un'operazione che richiede molte risorse. Di conseguenza, tali richieste consumano le risorse del server e vengono eseguite lentamente.

In una situazione del genere, può aiutare:

• denormalizzazione riducendo il numero di tabelle. È meglio combinare in una più tabelle di piccole dimensioni, contenenti informazioni raramente modificate (come spesso si dice, condizionalmente costanti o di riferimento) e informazioni strettamente correlate nel significato.
• In generale, se è necessario unire più di cinque o sei tabelle in un numero elevato di query, è consigliabile denormalizzare il database.
• Denormalizzazione aggiungendo un campo aggiuntivo a una delle tabelle. In questo caso, appare la ridondanza dei dati, sono necessarie azioni aggiuntive per mantenere l'integrità del database.

Valori stimati

Spesso le query sono lente e consumano molte risorse, in cui vengono eseguiti alcuni calcoli complessi, soprattutto quando si utilizzano raggruppamenti e funzioni di aggregazione (Sum, Max, ecc.). A volte ha senso aggiungere 1-2 colonne aggiuntive alla tabella contenente dati calcolati utilizzati di frequente (e difficili da calcolare).

Supponiamo di voler determinare il costo totale di ciascun ordine. Per fare ciò, devi prima determinare il costo di ciascun prodotto (secondo la formula "numero di unità di prodotto" * "prezzo unitario del prodotto" - sconto). Successivamente, è necessario raggruppare i costi per ordini.

L'esecuzione di questa query è piuttosto complessa e, se il database memorizza informazioni su un numero elevato di ordini, può richiedere molto tempo. Invece di eseguire tale query, è possibile determinarne il costo nella fase di immissione di un ordine e memorizzarlo in una colonna separata della tabella degli ordini. In questo caso, per ottenere il risultato desiderato, è sufficiente estrarre i valori precalcolati da questa colonna.

La creazione di una colonna che contiene valori precalcolati consente di risparmiare molto tempo durante l'esecuzione di una query, ma richiede di aggiornare i dati in quella colonna in modo tempestivo.

tesa lunga

Se nel database sono presenti tabelle di grandi dimensioni che contengono campi lunghi (Blob, Long e così via), è possibile velocizzare notevolmente l'esecuzione delle query su tale tabella se spostiamo i campi lunghi in una tabella separata. Vogliamo, ad esempio, creare un catalogo di foto nel database, inclusa la memorizzazione delle foto stesse in campi blob (qualità professionale, alta risoluzione e dimensioni appropriate). Dal punto di vista della normalizzazione, la seguente struttura della tabella sarebbe assolutamente corretta:

• Documento d'identità con foto
• ID autore
• ID modello fotocamera
• la foto stessa (campo blob)

E ora immaginiamo per quanto tempo verrà eseguita la query, contando il numero di foto scattate da qualsiasi autore ...

La soluzione corretta (sebbene violando i principi di normalizzazione) in una situazione del genere sarebbe quella di creare un'altra tabella composta da due soli campi: l'ID della foto e un campo blob con la foto stessa. Quindi le selezioni dalla tabella principale (in cui non c'è più un enorme campo blob) andranno all'istante, ma quando vogliamo vedere la foto stessa, beh, aspettiamo ...

Come determinare quando la denormalizzazione è giustificata?

8.4 Pro e contro della denormalizzazione

Un modo per determinare se determinati passaggi sono giustificati è condurre un'analisi in termini di costi e possibili benefici. Quanto costerà un modello di dati denormalizzato?

Determina i requisiti (cosa vogliamo ottenere) → determina i requisiti dei dati (cosa dobbiamo seguire) → trova il passaggio minimo che soddisfa questi requisiti → calcola i costi di implementazione → implementa.

I costi includono aspetti fisici come lo spazio su disco, le risorse necessarie per gestire questa struttura e le opportunità perse a causa dei ritardi associati al mantenimento di questo processo. Devi pagare per la denormalizzazione. Un database denormalizzato aumenta la ridondanza dei dati, che può migliorare le prestazioni ma richiede uno sforzo maggiore per controllare i dati correlati. Il processo di creazione delle applicazioni diventerà più difficile, poiché i dati saranno ripetuti e più difficili da tracciare. Inoltre, l'implementazione dell'integrità referenziale non è facile: i dati correlati sono divisi in diverse tabelle.

I vantaggi includono prestazioni di query più rapide e la possibilità di ottenere una risposta più rapida. Puoi anche ottenere altri vantaggi, tra cui maggiore produttività, soddisfazione del cliente e produttività, nonché un uso più efficiente degli strumenti di sviluppo esterni.

Tasso di richiesta e coerenza delle prestazioni

Ad esempio, il 72% delle 1.000 query generate quotidianamente da un'azienda sono query a livello di riepilogo, non query drill-down. Quando si utilizza una tabella di riepilogo, le query vengono eseguite in circa 6 secondi invece di 4 minuti, con un tempo di elaborazione inferiore di 3.000 minuti. Anche dopo l'adeguamento per i 100 minuti che devono essere spesi a mantenere le tabelle pivot ogni settimana, ciò consente di risparmiare 2.500 minuti a settimana, il che giustifica la creazione della tabella pivot. Col passare del tempo, può capitare che la maggior parte delle interrogazioni non vengano indirizzate a dati di riepilogo, ma a dati di dettaglio. Minore è il numero di query che utilizzano la tabella di riepilogo, più facile è eliminarla senza influire su altri processi.

E…

I criteri sopra elencati non sono gli unici da considerare quando si decide se fare il passo successivo nell'ottimizzazione. Altri fattori devono essere considerati, comprese le priorità aziendali e le esigenze degli utenti finali. Gli utenti devono capire come, dal punto di vista tecnico, l'architettura del sistema risente dell'esigenza degli utenti che vogliono che tutte le richieste vengano completate in pochi secondi. Il modo più semplice per raggiungere questa comprensione è delineare i costi associati alla creazione e alla gestione di tali tabelle.

8.5 Come implementare con competenza la denormalizzazione.

Salva tabelle dettagliate

Per non limitare le capacità del database importanti per il business, è necessario adottare una strategia di coesistenza, non di sostituzione, ovvero mantenere tabelle dettagliate per analisi approfondite, aggiungendovi strutture denormalizzate. Ad esempio, il contatore di visite. Per le imprese, è necessario conoscere il numero di visite a una pagina web. Ma per l'analisi (per periodo, per paese...) molto probabilmente avremo bisogno di dati dettagliati - una tabella con le informazioni su ogni visita.

Utilizzo di trigger

È possibile denormalizzare una struttura di database e godere comunque dei vantaggi della normalizzazione utilizzando trigger di database per preservare l' integrityintegrità dei dati duplicati.

Ad esempio, quando si aggiunge un campo calcolato, ciascuna delle colonne da cui dipende il campo calcolato viene bloccata da un trigger che chiama una singola procedura memorizzata (questo è importante!), Che scrive i dati necessari nel campo calcolato. È solo necessario non saltare nessuna delle colonne da cui dipende il campo calcolato.

Supporto software

Se non si utilizzano trigger e stored procedure integrati, gli sviluppatori di applicazioni dovrebbero occuparsi di garantire la coerenza dei dati in un database denormalizzato.

Per analogia con i trigger, dovrebbe esserci una funzione che aggiorna tutti i campi che dipendono dal campo che viene modificato.

conclusioni

Durante la denormalizzazione, è importante mantenere un equilibrio tra l'aumento della velocità del database e l'aumento del rischio di dati incoerenti, tra il semplificare la vita ai programmatori che scrivono Select-se il complicare il compito di coloro che forniscono il popolamento del database e gli aggiornamenti dei dati. Pertanto, è necessario denormalizzare il database con molta attenzione, in modo molto selettivo, solo dove è indispensabile.

Se è impossibile calcolare in anticipo i pro ei contro della denormalizzazione, inizialmente è necessario implementare un modello con tabelle normalizzate e solo successivamente, per ottimizzare le query problematiche, eseguire la denormalizzazione.

È importante introdurre la denormalizzazione gradualmente e solo per quei casi in cui si verificano recuperi ripetuti di dati correlati da tabelle diverse. Ricorda, durante la duplicazione dei dati, il numero di record aumenterà, ma il numero di letture diminuirà. È inoltre conveniente archiviare i dati calcolati in colonne per evitare selezioni aggregate non necessarie.