Veritabanındaki tabloların denormalizasyonu

8.1 Denormalizasyon neden gereklidir?

Büyük tablolar arasında hesaplama açısından en pahalı işlem birleştirmedir. Buna göre, bir sorguda milyonlarca satırdan oluşan birkaç tabloyu "havalandırmak" gerekiyorsa, DBMS bu tür işlemler için çok zaman harcayacaktır.

Bu sırada kullanıcı kahve içmek için uzaklaşabilir. İşleme etkileşimi fiilen ortadan kalkar ve toplu işlemeye yaklaşır. Daha da kötüsü, toplu modda, kullanıcı önceki gün istenen tüm verileri sabah alır ve onlarla sakince çalışarak akşam için yeni istekler hazırlar.

Ağır birleştirme durumundan kaçınmak için tablolar denormalize edilir. Ama nasılsa değil. Veri ambarları için tablo oluşturma kurallarına göre, işlemsel olarak normalleştirilmemiş tabloları "normalleştirilmiş" olarak değerlendirmenize izin veren bazı kurallar vardır.

Analitik işlemede "normal" kabul edilen iki ana şema vardır: "kar tanesi" ve "yıldız". İsimler özü iyi yansıtıyor ve doğrudan ilgili tabloların resmini takip ediyor.

Her iki durumda da olgu tabloları, analistin ilgisini çekecek olayları, işlemleri, belgeleri ve diğer ilginç şeyleri içeren şemanın merkezi öğesidir. Ancak, işlem veritabanında bir belge birkaç tabloya (en az iki: başlıklar ve içerik satırları) "bulaşmışsa", o zaman olgu tablosunda bir belge, daha kesin olarak, satırlarının her biri veya bir dizi gruplandırılmış satır karşılık gelir. bir kayda.

Bu, yukarıdaki iki tabloyu denormalize ederek yapılabilir.

8.2 Denormalizasyon örneği

Artık DBMS'nin örneğin şu türde bir sorgu yürütmesinin ne kadar kolay olacağını değerlendirebilirsiniz: dönem için Pirozhki LLC ve Vatrushki CJSC müşterilerine yapılan un satışlarının hacmini belirlemek.

Normalleştirilmiş bir işlem veritabanında:

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

Analitik veritabanında:

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

DBMS, iki belge tablosu arasında ağır bir birleşim ve bunların milyonlarca satırlık bileşimi yerine, olgu tablosuyla doğrudan çalışır ve tanımlayıcıları bilmeden de yapabileceğiniz küçük yardımcı tablolarla hafif birleştirmelerle çalışır.

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

"Yıldız" ve "kar tanesi" şemalarına dönelim. İlk resmin perde arkasında müşterilerin, gruplarının, mağazaların, satıcıların ve aslında malların tabloları vardı. Denormalize edildiğinde, boyutlar adı verilen bu tablolar da olgu tablosuna eklenir. Olgu tablosu, diğer boyutlarla (ikinci seviye ve üzeri boyutlar) bağlantıları olan boyut tablolarına atıfta bulunuyorsa, böyle bir şemaya "kar tanesi" denir.

Görüldüğü gibi müşteri gruplarına göre filtreleme içeren sorgular için ek bir bağlantı yapmanız gerekmektedir.

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)

Bu durumda denormalizasyon devam edebilir ve ikinci seviye boyutu birinci seviyeye düşürerek olgu tablosunu sorgulamayı kolaylaştırır.

Olgu tablosunun yalnızca ikinci bir düzeyi olmayan boyutlara atıfta bulunduğu bir şemaya yıldız şeması denir. Ölçüm tablolarının sayısı, yıldızdaki "ışınların" sayısına karşılık gelir.

Yıldız şeması, boyutlar hiyerarşisini ve karşılık gelen tabloları tek bir sorguda birleştirme ihtiyacını tamamen ortadan kaldırır.

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

Denormalizasyonun dezavantajı , her zaman hem işlem hem de analitik uygulamalarda veritabanının boyutunda bir artışa neden olan fazlalıktır . Yukarıdaki "kar tanesi"nin "yıldız"a dönüştürülmesi örneğinde yaklaşık bir delta hesaplayalım.

Oracle gibi bazı DBMS'lerde, veritabanı şeması tanımları düzeyinde özel tamsayı türleri yoktur, genel boolean türünü kullanmanız gerekir numeric(N); burada N, depolanan bitlerin sayısıdır. Böyle bir sayının depolama boyutu, fiziksel veri depolama için belgelerde verilen özel bir formül kullanılarak hesaplanır ve kural olarak "16 bit tamsayı" gibi düşük seviyeli türlerinkinden 1-3 bayt aşar.

Satış tablosunun veri sıkıştırma kullanmadığını ve yaklaşık 500 milyon satır içerdiğini ve müşteri gruplarının sayısının yaklaşık 1000 olduğunu varsayalım. Bu durumda tanımlayıcı türü olarak id_customer_group2 bayt yer kaplayan bir kısa tamsayı (shortint, smallint) kullanabiliriz.

DBMS'mizin iki baytlık bir tamsayı türünü desteklediğini varsayacağız (örneğin, PostgreSQL, SQL Server, Sybase ve diğerleri). Ardından ilgili sütunu id_customer_groupsatış tablosuna eklemek, boyutunu en az artıracaktır 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 Denormalizasyon ne zaman gereklidir?

Denormalizasyonun yararlı olabileceği bazı yaygın durumlara bakalım.

Çok sayıda tablo birleştirme

Tamamen normalleştirilmiş bir veritabanına yapılan sorgularda, genellikle bir düzine veya daha fazla tabloyu birleştirmeniz gerekir. Ve her bağlantı çok kaynak yoğun bir işlemdir. Sonuç olarak, bu tür istekler sunucu kaynaklarını tüketir ve yavaş gerçekleştirilir.

Böyle bir durumda yardımcı olabilir:

• tablo sayısını azaltarak denormalizasyon. Küçük boyutlu, nadiren değişen (sıklıkla dedikleri gibi, koşullu olarak sabit veya referans) bilgileri ve anlamla yakından ilişkili bilgileri içeren birkaç tabloyu birleştirmek daha iyidir.
• Genel olarak, çok sayıda sorguda beş veya altıdan fazla tabloyu birleştirmeniz gerekiyorsa, veritabanını denormalize etmeyi düşünmelisiniz.
• Tablolardan birine ek bir alan ekleyerek denormalizasyon. Bu durumda, veri fazlalığı ortaya çıkar, veritabanının bütünlüğünü korumak için ek eylemler gerekir.

Tahmini değerler

Çoğu zaman, sorgular yavaştır ve özellikle gruplamalar ve toplama işlevleri (Toplam, Maks, vb.) kullanılırken bazı karmaşık hesaplamaların gerçekleştirildiği çok fazla kaynak tüketir. Bazen, sık kullanılan (ve hesaplaması zor) hesaplanmış verileri içeren tabloya 1-2 ek sütun eklemek mantıklıdır.

Her bir siparişin toplam maliyetini belirlemek istediğinizi varsayalım. Bunu yapmak için önce her bir ürünün maliyetini belirlemelisiniz ("ürün birim sayısı" * "ürünün birim fiyatı" - indirim formülüne göre). Bundan sonra, maliyetleri siparişlere göre gruplandırmanız gerekir.

Bu sorguyu yürütmek oldukça karmaşıktır ve veritabanı çok sayıda sipariş hakkında bilgi depoluyorsa uzun zaman alabilir. Böyle bir sorgulama yapmak yerine, maliyetini sipariş verme aşamasında belirleyerek siparişler tablosunun ayrı bir sütununda saklayabilirsiniz. Bu durumda istenilen sonucu elde etmek için bu kolondan önceden hesaplanmış değerleri çıkartmak yeterlidir.

Önceden hesaplanmış değerler içeren bir sütun oluşturmak, bir sorgu çalıştırırken çok zaman kazandırır, ancak o sütundaki verileri zamanında güncellemenizi gerektirir.

uzun kenar

Veritabanında uzun alanlar (Blob, Long vb.) içeren büyük tablolarımız varsa, uzun alanları ayrı bir tabloya taşırsak böyle bir tabloya sorguların yürütülmesini ciddi şekilde hızlandırabiliriz. Örneğin, fotoğrafların kendilerini blob alanlarında (profesyonel kalite, yüksek çözünürlük ve uygun boyut) depolamak da dahil olmak üzere, veritabanında bir fotoğraf kataloğu oluşturmak istiyoruz. Normalleştirme açısından, aşağıdaki tablo yapısı kesinlikle doğru olacaktır:

• fotoğraflı kimlik
• Yazar kimliği
• Kamera Modeli Kimliği
• fotoğrafın kendisi (damla alanı)

Ve şimdi, herhangi bir yazar tarafından çekilen fotoğraf sayısını sayarak sorgunun ne kadar süreceğini hayal edelim ...

Böyle bir durumda doğru çözüm (normalleştirme ilkelerini ihlal etse de) yalnızca iki alandan oluşan başka bir tablo oluşturmak olacaktır - fotoğraflı kimlik ve fotoğrafın kendisiyle bir damla alanı. Ardından (artık büyük bir damla alanının olmadığı) ana tablodan seçimler anında gidecek, ancak fotoğrafın kendisini görmek istediğimizde, bekleyelim ...

Denormalizasyonun ne zaman haklı olduğu nasıl belirlenir?

8.4 Denormalizasyonun artıları ve eksileri

Belirli adımların haklı olup olmadığını belirlemenin bir yolu, maliyetler ve olası faydalar açısından bir analiz yapmaktır. Denormalize edilmiş bir veri modelinin maliyeti ne kadar olacak?

Gereksinimleri belirleyin (neyi elde etmek istiyoruz) → veri gereksinimlerini belirleyin (neyi takip etmemiz gerekiyor) → bu gereksinimleri karşılayan minimum adımı bulun → uygulama maliyetlerini hesaplayın → uygulayın.

Maliyetler, disk alanı, bu yapıyı yönetmek için gereken kaynaklar ve bu sürecin sürdürülmesiyle ilişkili zaman gecikmelerinden kaynaklanan kaybedilen fırsatlar gibi fiziksel yönleri içerir. Denormalizasyon için ödeme yapmalısınız. Denormalize edilmiş bir veritabanı, performansı iyileştirebilen ancak ilgili verileri kontrol etmek için daha fazla çaba gerektiren veri fazlalığını artırır. Veriler tekrarlanacağından ve takibi zorlaşacağından, uygulama oluşturma süreci daha da zorlaşacaktır. Ek olarak, referans bütünlüğünün uygulanması kolay değildir - ilgili veriler farklı tablolara bölünür.

Avantajlar arasında daha hızlı sorgu performansı ve daha hızlı yanıt alma yeteneği yer alır. Artan iş hacmi, müşteri memnuniyeti ve üretkenliğin yanı sıra harici geliştirici araçlarının daha verimli kullanımı gibi diğer avantajlardan da yararlanabilirsiniz.

İstek Oranı ve Performans Tutarlılığı

Örneğin, bir kuruluş tarafından günlük olarak üretilen 1.000 sorgunun %72'si, detaya inme sorguları değil, özet düzeyindeki sorgulardır. Bir özet tablosu kullanırken, sorgular 4 dakika yerine yaklaşık 6 saniyede çalışır ve bu da 3.000 dakika daha kısa işlem süresi sağlar. Pivot tabloların bakımı için her hafta harcanması gereken 100 dakikayı ayarladıktan sonra bile, bu haftada 2.500 dakika tasarruf sağlıyor ve bu da pivot tablonun oluşturulmasını haklı çıkarıyor. Zamanla, sorguların çoğu özet verilere değil, ayrıntılı verilere yönelik olabilir. Özet tablosunu kullanan sorgu sayısı ne kadar az olursa, diğer işlemleri etkilemeden tabloyu bırakmak o kadar kolay olur.

Ve…

Optimizasyonda bir sonraki adımı atıp atmayacağınıza karar verirken dikkate alınması gerekenler yalnızca yukarıda listelenen kriterler değildir. İş öncelikleri ve son kullanıcı ihtiyaçları dahil olmak üzere diğer faktörlerin de dikkate alınması gerekir. Kullanıcılar, teknik açıdan sistem mimarisinin, tüm isteklerinin birkaç saniye içinde tamamlanmasını isteyen kullanıcıların gereksinimlerinden nasıl etkilendiğini anlamalıdır. Bu anlayışı elde etmenin en kolay yolu, bu tür tabloların oluşturulması ve yönetilmesiyle ilgili maliyetlerin ana hatlarını çizmektir.

8.5 Denormalizasyonun yetkin bir şekilde nasıl uygulanacağı.

Ayrıntılı tabloları kaydet

İş için önemli olan veritabanının yeteneklerini sınırlamamak için, değiştirme değil, bir arada olma stratejisini benimsemek, yani derin analiz için ayrıntılı tablolar tutmak ve bunlara denormalize edilmiş yapılar eklemek gerekir. Örneğin, isabet sayacı. İş için, bir web sayfasının ziyaret sayısını bilmeniz gerekir. Ancak analiz için (döneme göre, ülkeye göre...) büyük olasılıkla ayrıntılı verilere ihtiyacımız olacak - her ziyaret hakkında bilgi içeren bir tablo.

Tetikleyicileri kullanma

integrityYinelenen verilerin bütünlüğünü korumak için veritabanı tetikleyicilerini kullanarak bir veritabanı yapısını denormalize etmek ve normalleştirmenin avantajlarından yararlanmaya devam etmek mümkündür .

Örneğin, hesaplanan bir alan eklerken, hesaplanan alanın bağlı olduğu sütunların her biri, gerekli verileri hesaplanan alana yazan tek bir saklı yordamı çağıran (bu önemlidir!) bir tetikleyici ile kapatılır. Yalnızca hesaplanan alanın bağlı olduğu sütunlardan hiçbirinin atlanmaması gerekir.

Yazılım desteği

Yerleşik tetikleyiciler ve saklı yordamlar kullanmıyorsanız, uygulama geliştiricileri, denormalize edilmiş bir veritabanındaki verilerin tutarlılığını sağlamaya özen göstermelidir.

Tetikleyicilere benzeterek, değiştirilmekte olan alana bağlı olarak tüm alanları güncelleyen bir işlev olmalıdır.

sonuçlar

Select-sDenormalize ederken, veritabanının hızını artırmak ile tutarsız veri riskini artırmak arasında, programcıların yazma işini kolaylaştırmak ile veritabanı popülasyonu ve veri güncellemeleri sağlayanların görevini karmaşıklaştırmak arasında bir denge sağlamak önemlidir. Bu nedenle, veritabanını çok dikkatli bir şekilde, çok seçici bir şekilde, yalnızca vazgeçilmez olduğu yerlerde normalleştirmek gerekir.

Denormalizasyonun artılarını ve eksilerini önceden hesaplamak imkansızsa, başlangıçta normalleştirilmiş tablolarla bir model uygulamak ve ancak o zaman sorunlu sorguları optimize etmek için denormalizasyonu gerçekleştirmek gerekir.

Denormalizasyonu aşamalı olarak ve yalnızca farklı tablolardan ilgili verilerin tekrar tekrar getirildiği durumlarda uygulamak önemlidir. Unutmayın, verileri çoğaltırken kayıt sayısı artacak ancak okuma sayısı azalacaktır. Gereksiz toplu seçimlerden kaçınmak için hesaplanan verileri sütunlarda saklamak da uygundur.