8.1 विसामान्यीकरण क्यों आवश्यक है?

बड़ी तालिकाओं के बीच सबसे कम्प्यूटेशनल रूप से महंगा ऑपरेशन ज्वाइन है। तदनुसार, यदि एक प्रश्न में कई लाखों पंक्तियों वाली कई तालिकाओं को "वेंटिलेट" करना आवश्यक है, तो DBMS इस तरह के प्रसंस्करण पर बहुत समय व्यतीत करेगा।

उपयोगकर्ता इस समय कॉफी पीने के लिए दूर जा सकता है। प्रसंस्करण की अन्तरक्रियाशीलता व्यावहारिक रूप से गायब हो जाती है और बैच प्रसंस्करण के करीब पहुंच जाती है। इससे भी बदतर, बैच मोड में, उपयोगकर्ता सुबह में अनुरोध किए गए सभी डेटा प्राप्त करता है और शाम के लिए नए अनुरोध तैयार करते हुए शांति से उनके साथ काम करता है।

भारी जोड़ की स्थिति से बचने के लिए, तालिकाओं को अपसामान्यीकृत किया जाता है। लेकिन किसी तरह नहीं। कुछ नियम हैं जो आपको डेटा वेयरहाउस के लिए तालिकाओं के निर्माण के नियमों के अनुसार व्यवहारिक रूप से विकृत तालिकाओं को "सामान्यीकृत" मानने की अनुमति देते हैं।

दो मुख्य योजनाएँ हैं जिन्हें विश्लेषणात्मक प्रसंस्करण में "सामान्य" माना जाता है: "स्नोफ्लेक" और "स्टार"। नाम सार को अच्छी तरह से दर्शाते हैं और संबंधित तालिकाओं की तस्वीर से सीधे अनुसरण करते हैं।

दोनों ही मामलों में, तथाकथित फैक्ट टेबल स्कीमा का केंद्रीय तत्व है, जिसमें घटनाओं, लेन-देन, दस्तावेजों और विश्लेषक के लिए ब्याज की अन्य दिलचस्प चीजें शामिल हैं। लेकिन अगर एक लेन-देन संबंधी डेटाबेस में एक दस्तावेज़ कई तालिकाओं (कम से कम दो: शीर्ष लेख और सामग्री की पंक्तियाँ) में "स्मियर" किया जाता है, तो तथ्य तालिका में एक दस्तावेज़, अधिक सटीक रूप से, इसकी प्रत्येक पंक्ति या समूहीकृत पंक्तियों का एक सेट मेल खाता है एक रिकॉर्ड के लिए।

यह उपरोक्त दो तालिकाओं को असामान्य करके किया जा सकता है।

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

अब आप मूल्यांकन कर सकते हैं कि DBMS के लिए किसी क्वेरी को निष्पादित करना कितना आसान होगा, उदाहरण के लिए, निम्न प्रकार: अवधि के लिए Pirozhki 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

दस्तावेजों की दो तालिकाओं और लाखों पंक्तियों के साथ उनकी संरचना के बीच भारी जुड़ाव के बजाय, DBMS को तथ्य तालिका के साथ सीधा काम मिलता है और प्रकाश छोटी सहायक तालिकाओं के साथ जुड़ता है, जिसे आप पहचानकर्ताओं को जाने बिना भी कर सकते हैं।

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

आइए "स्टार" और "स्नोफ्लेक" योजनाओं पर लौटते हैं। पहली तस्वीर के पर्दे के पीछे ग्राहकों, उनके समूहों, दुकानों, विक्रेताओं और वास्तव में सामानों की तालिकाएँ थीं। असामान्य होने पर, इन तालिकाओं, जिन्हें आयाम कहा जाता है, को तथ्य तालिका में भी जोड़ा जाता है। यदि तथ्य तालिका उन आयाम तालिकाओं को संदर्भित करती है जिनमें अन्य आयामों (दूसरे स्तर और ऊपर के आयाम) के लिंक हैं, तो ऐसी स्कीमा को "स्नोफ्लेक" कहा जाता है।

जैसा कि आप देख सकते हैं, क्‍वेरी के लिए जिसमें क्‍लाइंट समूहों द्वारा फिल्‍टरिंग शामिल है, आपको एक अतिरिक्‍त कनेक्‍शन बनाना होगा।


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 Server, 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और डेटाबेस आबादी और डेटा अपडेट प्रदान करने वालों के कार्य को जटिल बनाने के बीच। इसलिए, यह आवश्यक है कि डेटाबेस को बहुत सावधानी से, बहुत चुनिंदा रूप से, जहां यह अपरिहार्य है, को असामान्य करना चाहिए।

यदि अग्रिम में विचलन के पेशेवरों और विपक्षों की गणना करना असंभव है, तो शुरू में सामान्यीकृत तालिकाओं के साथ एक मॉडल को लागू करना आवश्यक है, और उसके बाद ही, समस्याग्रस्त प्रश्नों को अनुकूलित करने के लिए, असामान्यकरण करें।

डीनॉर्मलाइजेशन को धीरे-धीरे शुरू करना महत्वपूर्ण है और केवल उन मामलों के लिए जहां विभिन्न तालिकाओं से संबंधित डेटा को बार-बार प्राप्त किया जाता है। याद रखें, डेटा की प्रतिलिपि बनाते समय, रिकॉर्ड की संख्या बढ़ जाएगी, लेकिन पढ़ने की संख्या घट जाएगी। अनावश्यक समग्र चयन से बचने के लिए गणना किए गए डेटा को कॉलम में संग्रहीत करना भी सुविधाजनक है।