डेटाबेसमधील सारण्यांचे सामान्यीकरण

8.1 विकृतीकरण का आवश्यक आहे?

मोठ्या टेबलांमधील सर्वात संगणकीयदृष्ट्या महाग ऑपरेशन म्हणजे जोडणे. त्यानुसार, जर एका क्वेरीमध्ये लाखो पंक्ती असलेल्या अनेक टेबल्स "व्हेंटिलेट" करणे आवश्यक असेल, तर DBMS अशा प्रक्रियेवर बराच वेळ घालवेल.

यावेळी वापरकर्ता कॉफी पिण्यासाठी दूर जाऊ शकतो. प्रक्रियेची परस्पर क्रिया व्यावहारिकदृष्ट्या अदृश्य होते आणि बॅच प्रक्रियेच्या जवळ येते. आणखी वाईट म्हणजे, बॅच मोडमध्ये, वापरकर्त्यास आदल्या दिवशी सकाळच्या वेळी विनंती केलेला सर्व डेटा प्राप्त होतो आणि संध्याकाळसाठी नवीन विनंत्या तयार करून शांतपणे त्यांच्यासोबत कार्य करतो.

जड जोड्यांची परिस्थिती टाळण्यासाठी, टेबल्स डिनोर्मलाइझ केले जातात. पण तरीही नाही. असे काही नियम आहेत जे तुम्हाला डेटा वेअरहाऊससाठी टेबल्स बनवण्याच्या नियमांनुसार व्यवहारिकदृष्ट्या डिनोर्मलाइज्ड टेबल्सना "सामान्यीकृत" म्हणून विचार करण्याची परवानगी देतात.

दोन मुख्य योजना आहेत ज्या विश्लेषणात्मक प्रक्रियेत "सामान्य" मानल्या जातात: "स्नोफ्लेक" आणि "स्टार". नावे सार चांगल्या प्रकारे प्रतिबिंबित करतात आणि संबंधित सारण्यांच्या चित्रावरून थेट अनुसरण करतात.

दोन्ही प्रकरणांमध्ये, तथाकथित तथ्य सारणी हे स्कीमाचे मध्यवर्ती घटक आहेत, ज्यात घटना, व्यवहार, दस्तऐवज आणि विश्लेषकाला स्वारस्य असलेल्या इतर मनोरंजक गोष्टी असतात. परंतु जर एखाद्या व्यवहाराच्या डेटाबेसमध्ये एक दस्तऐवज अनेक सारण्यांमध्ये (किमान दोन: शीर्षलेख आणि सामग्रीच्या पंक्ती) वर "स्मीअर" केला असेल, तर वस्तुस्थिती सारणीमध्ये एक दस्तऐवज, अधिक तंतोतंत, त्याच्या प्रत्येक पंक्ती किंवा गटबद्ध पंक्तींचा संच, संबंधित आहे. एका रेकॉर्डवर.

हे वरील दोन सारण्यांचे विकृतीकरण करून केले जाऊ शकते.

8.2 सामान्यीकरण उदाहरण

आता तुम्ही मूल्यांकन करू शकता की DBMS साठी क्वेरी कार्यान्वित करणे किती सोपे आहे, उदाहरणार्थ, खालील प्रकारची: पिरोझकी LLC आणि Vatrushki CJSC च्या क्लायंटला या कालावधीसाठी पीठ विक्रीचे प्रमाण निश्चित करणे.

सामान्यीकृत व्यवहार डेटाबेसमध्ये:

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

विश्लेषणात्मक डेटाबेसमध्ये:

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

दस्तऐवजांच्या दोन टेबल्स आणि लाखो पंक्तींसह त्यांची रचना यांच्यामध्ये जड जोडण्याऐवजी, डीबीएमएसला फॅक्ट टेबलसह थेट काम मिळते आणि लहान सहाय्यक सारण्यांसह प्रकाश जोडला जातो, जे तुम्ही ओळखकर्त्यांना जाणून घेतल्याशिवाय देखील करू शकता.

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

चला "स्टार" आणि "स्नोफ्लेक" योजनांवर परत जाऊया. पहिल्या चित्राच्या पडद्यामागे ग्राहक, त्यांचे गट, दुकाने, विक्रेते आणि वस्तुतः वस्तूंची तक्ते होती. असामान्यीकरण केल्यावर, या सारण्या, ज्याला परिमाण म्हणतात, ते तथ्य सारणीमध्ये देखील जोडले जातात. जर वस्तुस्थिती सारणी इतर परिमाणे (दुसऱ्या स्तराची आणि त्यावरील परिमाणे) च्या दुवे असलेल्या आयाम सारण्यांचा संदर्भ देत असेल तर अशा स्कीमाला "स्नोफ्लेक" म्हणतात.

जसे तुम्ही पाहू शकता, क्लायंट गटांद्वारे फिल्टरिंग समाविष्ट असलेल्या क्वेरीसाठी, तुम्हाला अतिरिक्त कनेक्शन करावे लागेल.

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)

या प्रकरणात, डिनोर्मलायझेशन सुरू ठेवू शकते आणि द्वितीय-स्तरीय परिमाण पहिल्यावर सोडू शकते, ज्यामुळे तथ्य सारणीची क्वेरी करणे सोपे होते.

एक स्कीमा ज्यामध्ये तथ्य सारणी फक्त अशा परिमाणांचा संदर्भ देते ज्यात दुसरा स्तर नसतो त्याला स्टार स्कीमा म्हणतात. मापन सारण्यांची संख्या ताऱ्यातील "किरण" च्या संख्येशी संबंधित आहे.

स्टार स्कीमा परिमाणांचे पदानुक्रम आणि एकाच क्वेरीमध्ये संबंधित सारण्यांमध्ये सामील होण्याची आवश्यकता पूर्णपणे काढून टाकते.

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

डिनोर्मलायझेशनची नकारात्मक बाजू नेहमीच रिडंडंसी असते , ज्यामुळे व्यवहार आणि विश्लेषणात्मक दोन्ही अनुप्रयोगांमध्ये डेटाबेसच्या आकारात वाढ होते. "स्नोफ्लेक" ला "स्टार" मध्ये रूपांतरित करण्याच्या वरील उदाहरणावर अंदाजे डेल्टाची गणना करूया.

काही DBMS मध्ये, जसे की Oracle, डेटाबेस स्कीमा व्याख्येच्या स्तरावर कोणतेही विशेष पूर्णांक प्रकार नाहीत, तुम्ही जेनेरिक बुलियन प्रकार वापरला पाहिजे numeric(N), जेथे N ही संचयित बिट्सची संख्या आहे. भौतिक डेटा संचयनासाठी दस्तऐवजात दिलेल्या विशेष सूत्राचा वापर करून अशा संख्येचा संचयन आकार मोजला जातो आणि नियमानुसार, तो 1-3 बाइट्सने "16 बिट पूर्णांक" सारख्या निम्न-स्तरीय प्रकारांपेक्षा जास्त असतो.

समजा विक्री सारणी डेटा कॉम्प्रेशन वापरत नाही आणि त्यामध्ये सुमारे 500 दशलक्ष पंक्ती आहेत आणि ग्राहक गटांची संख्या सुमारे 1000 आहे. या प्रकरणात, आम्ही एक ओळखकर्ता प्रकार म्हणून 2 बाइट्स व्यापणारे एक लहान पूर्णांक (शॉर्टिंट, स्मॉलिंट) वापरू शकतो id_customer_group.

आम्ही असे गृहीत धरू की आमचा DBMS दोन-बाइट पूर्णांक प्रकारास समर्थन देतो (उदाहरणार्थ, PostgreSQL, SQL सर्व्हर, Sybase आणि इतर). नंतर विक्री सारणीमध्ये संबंधित स्तंभ जोडल्यास id_customer_groupत्याचा आकार कमीतकमी वाढेल 500 000 000 * 2 = 1 000 000 000 byte ~ 1 GByte.

8.3 विकृतीकरण कधी आवश्यक आहे?

चला काही सामान्य परिस्थिती पाहू ज्यामध्ये विकृतीकरण उपयुक्त ठरू शकते.

मोठ्या संख्येने टेबल सामील होतात

पूर्णपणे सामान्यीकृत डेटाबेसच्या प्रश्नांमध्ये, तुम्हाला अनेकदा डझनभर किंवा त्याहून अधिक टेबल्समध्ये सामील व्हावे लागते. आणि प्रत्येक कनेक्शन एक अतिशय संसाधन-केंद्रित ऑपरेशन आहे. परिणामी, अशा विनंत्या सर्व्हर संसाधनांचा वापर करतात आणि हळूहळू केल्या जातात.

अशा परिस्थितीत, हे मदत करू शकते:

• सारण्यांची संख्या कमी करून विकृतीकरण. क्वचितच बदललेली (जसे ते सहसा म्हणतात, सशर्त स्थिर किंवा संदर्भ) माहिती आणि अर्थाशी जवळून संबंधित असलेली माहिती, आकाराने लहान असलेल्या अनेक सारण्यांमध्ये एकत्र करणे चांगले आहे.
• सर्वसाधारणपणे, जर तुम्हाला मोठ्या संख्येने प्रश्नांमध्ये पाच किंवा सहा टेबलांपेक्षा जास्त सामील होण्याची आवश्यकता असेल, तर तुम्ही डेटाबेस डिनॉर्मलाइझ करण्याचा विचार केला पाहिजे.
• एका टेबलमध्ये अतिरिक्त फील्ड जोडून सामान्यीकरण. या प्रकरणात, डेटा रिडंडंसी दिसून येते, डेटाबेसची अखंडता राखण्यासाठी अतिरिक्त क्रिया आवश्यक आहेत.

अंदाजे मूल्ये

बर्‍याचदा, क्वेरी धीमे असतात आणि भरपूर संसाधने वापरतात, ज्यामध्ये काही जटिल गणना केल्या जातात, विशेषत: गट आणि एकत्रित कार्ये (बेरीज, कमाल इ.) वापरताना. काहीवेळा वारंवार वापरल्या जाणार्‍या (आणि गणना करणे कठीण) गणना केलेला डेटा असलेल्या टेबलमध्ये 1-2 अतिरिक्त स्तंभ जोडणे अर्थपूर्ण आहे.

समजा तुम्हाला प्रत्येक ऑर्डरची एकूण किंमत ठरवायची आहे. हे करण्यासाठी, आपण प्रथम प्रत्येक उत्पादनाची किंमत निर्धारित करणे आवश्यक आहे (सूत्रानुसार "उत्पादन युनिट्सची संख्या" * "उत्पादनाची युनिट किंमत" - सूट). त्यानंतर, तुम्हाला ऑर्डरनुसार खर्चाचे गट करणे आवश्यक आहे.

ही क्वेरी कार्यान्वित करणे खूप क्लिष्ट आहे आणि, जर डेटाबेसमध्ये मोठ्या संख्येने ऑर्डरची माहिती संग्रहित केली गेली, तर बराच वेळ लागू शकतो. अशी क्वेरी कार्यान्वित करण्याऐवजी, तुम्ही ऑर्डर देण्याच्या टप्प्यावर त्याची किंमत निर्धारित करू शकता आणि ऑर्डर टेबलच्या वेगळ्या कॉलममध्ये संग्रहित करू शकता. या प्रकरणात, इच्छित परिणाम प्राप्त करण्यासाठी, या स्तंभातून पूर्व-गणना केलेली मूल्ये काढणे पुरेसे आहे.

पूर्वगणना केलेली मूल्ये असलेला स्तंभ तयार केल्याने क्वेरी चालवताना बराच वेळ वाचतो, परंतु तुम्ही त्या स्तंभातील डेटा वेळेवर अपडेट करणे आवश्यक आहे.

लांब काठोकाठ

जर आमच्याकडे डेटाबेसमध्ये मोठ्या टेबल्स असतील ज्यात लांब फील्ड (ब्लॉब, लाँग, इ.) असतील, तर आम्ही लांब फील्ड वेगळ्या टेबलवर हलवल्यास आम्ही अशा टेबलवर क्वेरीच्या अंमलबजावणीची गती वाढवू शकतो. ब्लॉब फील्डमध्ये (व्यावसायिक गुणवत्ता, उच्च रिझोल्यूशन आणि योग्य आकार) फोटो संग्रहित करण्यासह, डेटाबेसमध्ये फोटोंचा कॅटलॉग तयार करायचा आहे. सामान्यीकरणाच्या दृष्टिकोनातून, खालील सारणीची रचना पूर्णपणे योग्य असेल:

• फोटो आयडी
• लेखक आयडी
• कॅमेरा मॉडेल आयडी
• फोटो स्वतः (ब्लॉब फील्ड)

आणि आता कल्पना करूया की ही क्वेरी किती काळ चालेल, कोणत्याही लेखकाने काढलेल्या फोटोंची संख्या मोजून...

अशा परिस्थितीत योग्य उपाय (सामान्यीकरणाच्या तत्त्वांचे उल्लंघन करत असले तरी) फक्त दोन फील्ड असलेले दुसरे टेबल तयार करणे असेल - फोटो आयडी आणि फोटोसह ब्लॉब फील्ड. मग मुख्य टेबलमधील निवडी (ज्यामध्ये यापुढे एक मोठा ब्लॉब फील्ड नाही) त्वरित जाईल, परंतु जेव्हा आम्हाला फोटो स्वतःच पहायचा असेल, तेव्हा वाट पाहूया ...

विकृतीकरण न्याय्य आहे हे कसे ठरवायचे?

8.4 विकृतीकरणाचे फायदे आणि तोटे

काही पावले न्याय्य आहेत की नाही हे ठरवण्याचा एक मार्ग म्हणजे खर्च आणि संभाव्य फायद्यांच्या दृष्टीने विश्लेषण करणे. डिनोर्मलाइज्ड डेटा मॉडेलची किंमत किती असेल?

आवश्यकता निश्चित करा (आम्हाला काय साध्य करायचे आहे) → डेटा आवश्यकता निर्धारित करा (आम्हाला काय अनुसरण करायचे आहे) → या आवश्यकता पूर्ण करणारी किमान पायरी शोधा → अंमलबजावणी खर्चाची गणना करा → अंमलबजावणी करा.

खर्चामध्ये डिस्क स्पेस, ही रचना व्यवस्थापित करण्यासाठी आवश्यक संसाधने आणि या प्रक्रियेच्या देखभालीशी संबंधित वेळेच्या विलंबामुळे गमावलेल्या संधी यासारख्या भौतिक पैलूंचा समावेश होतो. डिनोर्मलायझेशनसाठी तुम्हाला पैसे द्यावे लागतील. डिनॉर्मलाइज्ड डेटाबेस डेटा रिडंडंसी वाढवते, जे कार्यप्रदर्शन सुधारू शकते परंतु संबंधित डेटा नियंत्रित करण्यासाठी अधिक प्रयत्न करणे आवश्यक आहे. अनुप्रयोग तयार करण्याची प्रक्रिया अधिक कठीण होईल, कारण डेटाची पुनरावृत्ती होईल आणि ट्रॅक करणे कठीण होईल. याव्यतिरिक्त, संदर्भित अखंडतेची अंमलबजावणी करणे सोपे नाही - संबंधित डेटा वेगवेगळ्या सारण्यांमध्ये विभागलेला आहे.

फायद्यांमध्ये वेगवान क्वेरी कार्यप्रदर्शन आणि जलद प्रतिसाद मिळविण्याची क्षमता समाविष्ट आहे. तुम्ही वाढलेले थ्रुपुट, ग्राहकांचे समाधान आणि उत्पादकता तसेच बाह्य विकसक साधनांचा अधिक कार्यक्षम वापर यासह इतर फायदे देखील मिळवू शकता.

विनंती दर आणि कार्यप्रदर्शन सुसंगतता

उदाहरणार्थ, एंटरप्राइझद्वारे दररोज व्युत्पन्न केलेल्या 1,000 क्वेरींपैकी 72% सारांश-स्तरीय क्वेरी आहेत, ड्रिल-डाउन क्वेरी नाहीत. सारांश सारणी वापरताना, क्वेरी 4 मिनिटांऐवजी अंदाजे 6 सेकंदात चालतात, परिणामी 3,000 मिनिटे कमी प्रक्रिया वेळ लागतो. पिव्होट टेबल्सची देखभाल करण्यासाठी प्रत्येक आठवड्याला 100 मिनिटे समायोजित केल्यावरही, जे दर आठवड्याला 2,500 मिनिटे वाचवते, जे पिव्हट टेबलच्या निर्मितीचे समर्थन करते. कालांतराने, असे होऊ शकते की बहुतेक क्वेरी सारांश डेटाकडे संबोधित केल्या जाणार नाहीत, परंतु तपशीलवार डेटासाठी. सारांश सारणी वापरणाऱ्या कमी क्वेरी, इतर प्रक्रियांवर परिणाम न करता ते सोडणे तितके सोपे आहे.

आणि…

ऑप्टिमायझेशनमध्ये पुढील पाऊल उचलायचे की नाही हे ठरवताना केवळ वर सूचीबद्ध केलेले निकष विचारात घेतले जात नाहीत. व्यवसाय प्राधान्यक्रम आणि अंतिम वापरकर्त्याच्या गरजा यासह इतर घटकांचा विचार करणे आवश्यक आहे. तांत्रिक दृष्टिकोनातून, सर्व विनंत्या काही सेकंदात पूर्ण व्हाव्यात अशी इच्छा असलेल्या वापरकर्त्यांच्या आवश्यकतेमुळे सिस्टम आर्किटेक्चर कसा प्रभावित होतो हे वापरकर्त्यांनी समजून घेतले पाहिजे. ही समज प्राप्त करण्याचा सर्वात सोपा मार्ग म्हणजे अशा सारण्या तयार करणे आणि व्यवस्थापित करण्याशी संबंधित खर्चाची रूपरेषा काढणे.

8.5 डिनोर्मलायझेशन सक्षमपणे कसे अंमलात आणायचे.

तपशीलवार तक्ते जतन करा

व्यवसायासाठी महत्त्वाच्या असलेल्या डेटाबेसच्या क्षमता मर्यादित न ठेवण्यासाठी, सहअस्तित्वाची रणनीती स्वीकारणे आवश्यक आहे, बदली नाही, म्हणजे, सखोल विश्लेषणासाठी तपशीलवार तक्ते ठेवणे आवश्यक आहे, त्यामध्ये असामान्य संरचना जोडणे. उदाहरणार्थ, हिट काउंटर. व्यवसायासाठी, तुम्हाला वेब पृष्ठावरील भेटींची संख्या माहित असणे आवश्यक आहे. परंतु विश्लेषणासाठी (कालावधीनुसार, देशानुसार...) आम्हाला बहुधा तपशीलवार डेटाची आवश्यकता असेल - प्रत्येक भेटीची माहिती असलेली टेबल.

ट्रिगर वापरणे

integrityडुप्लिकेट डेटाची अखंडता टिकवून ठेवण्यासाठी डेटाबेस ट्रिगर वापरून डेटाबेस स्ट्रक्चर डिनोर्मलाइझ करणे आणि तरीही सामान्यीकरणाच्या फायद्यांचा आनंद घेणे शक्य आहे .

उदाहरणार्थ, गणना केलेले फील्ड जोडताना, प्रत्येक स्तंभ ज्यावर गणना केलेले फील्ड अवलंबून असते, एका ट्रिगरसह हँग अप केले जाते जे एकल संचयित प्रक्रिया कॉल करते (हे महत्वाचे आहे!), जे गणना केलेल्या फील्डमध्ये आवश्यक डेटा लिहिते. गणना केलेले फील्ड अवलंबून असलेले कोणतेही स्तंभ वगळणे आवश्यक नाही.

सॉफ्टवेअर समर्थन

तुम्ही बिल्ट-इन ट्रिगर आणि संग्रहित प्रक्रिया वापरत नसल्यास, अनुप्रयोग विकसकांनी डिनोर्मलाइज्ड डेटाबेसमध्ये डेटाची सुसंगतता सुनिश्चित करण्याची काळजी घेतली पाहिजे.

ट्रिगर्सच्या सादृश्यतेनुसार, एक फंक्शन असावे जे बदललेल्या फील्डवर अवलंबून असलेल्या सर्व फील्ड अद्यतनित करते.

निष्कर्ष

डिनॉर्मलायझेशन करताना, डेटाबेसचा वेग वाढवणे आणि विसंगत डेटाचा धोका वाढवणे, प्रोग्रामर लिहिण्यासाठी जीवन सोपे करणे Select-sआणि डेटाबेस लोकसंख्या आणि डेटा अद्यतने प्रदान करणार्‍यांचे कार्य गुंतागुंतीचे करणे यामधील संतुलन राखणे महत्त्वाचे आहे. म्हणून, डेटाबेस अत्यंत काळजीपूर्वक, अत्यंत निवडकपणे, केवळ अपरिहार्य असेल तेथेच विकृत करणे आवश्यक आहे.

डिनोर्मलायझेशनच्या साधक आणि बाधकांची आगाऊ गणना करणे अशक्य असल्यास, सुरुवातीला सामान्यीकृत सारण्यांसह मॉडेल लागू करणे आवश्यक आहे आणि त्यानंतरच, समस्याग्रस्त प्रश्नांना अनुकूल करण्यासाठी, विकृतीकरण करा.

हळूहळू आणि फक्त अशा प्रकरणांसाठी जेथे वेगवेगळ्या टेबल्समधून संबंधित डेटाची पुनरावृत्ती होते अशा प्रकरणांसाठी विकृतीकरण सुरू करणे महत्त्वाचे आहे. लक्षात ठेवा, डेटा डुप्लिकेट करताना, रेकॉर्डची संख्या वाढेल, परंतु वाचनाची संख्या कमी होईल. अनावश्यक एकत्रित निवडी टाळण्यासाठी गणना केलेला डेटा स्तंभांमध्ये संग्रहित करणे देखील सोयीचे आहे.