MySQL क्लस्टर और साधारण सूचकांक (InnoDB)

हम सभी पाठ्यपुस्तक की व्याख्या "डेटाबेस में अनुक्रमित क्या हैं और वे सही पंक्तियों को खोजने के कार्य को कैसे सुविधाजनक बनाते हैं" याद करते हैं। मुझे यकीन है कि आप में से अधिकांश को आपकी आंखों के सामने कुछ ऐसा मिलता है:



और तुरंत यह स्पष्ट हो जाता है कि दो या तीन आवश्यक लाइनों को खोजने के लिए आपको कितना कम डेटा फावड़ा करने की आवश्यकता है। शानदार। बस। मैं देखता हूं।

और व्यक्तिगत रूप से, यह हमेशा मुझे लगता था कि इस योजना को बेहतर बनाने के लिए कहीं नहीं था ... जब तक कि मैं क्लस्टर इंडेक्स से परिचित नहीं हो गया। यह पता चला कि "साधारण" सूचकांक के साथ सब कुछ इतना रसीला नहीं है।

तो, एक क्लस्टर इंडेक्स क्या है, नॉन-क्लस्टर किए गए इंडेक्स से बेहतर क्या है, और MySQL इसके साथ कैसे कर रहा है।

गैर-सूचीबद्ध सूचकांक

भ्रमित न होने के लिए, एक समय पर हम एक क्षेत्र पर एक सरल सूचकांक पर विचार करेंगे। सरलीकृत गैर-क्लस्टर सूचकांक को एक अलग तालिका के रूप में दर्शाया जा सकता है, प्रत्येक पंक्ति जिसमें डेटा तालिका में एक या अधिक पंक्तियों को संदर्भित किया जाता है। सूचकांक तालिका में पंक्तियों को मुख्य क्षेत्र मानों द्वारा क्रमबद्ध और समूहीकृत किया जाता है। एक प्राथमिक प्रश्न की कल्पना करें:

SELECT * FROM `t1` WHERE `fld1` = 12; 

किसी भी अनुक्रमण के बिना, प्रत्येक पंक्ति को पढ़ा और जांचा जाएगा, और ऐसी लाइनें जो शर्त को पूरा नहीं करती हैं, बस परिणाम में नहीं आएंगी। लेकिन उन्हें पढ़ा जाएगा।

"सामान्य", गैर-संकुल सूचकांक का उपयोग करते समय, खोज कार्य बहुत तेज होता है। सबसे पहले , सूचकांक तालिका का वजन डेटा तालिका की तुलना में बहुत कम होता है, जिसका अर्थ है कि इसे आसानी से तेजी से पढ़ा जा सकता है। दूसरे , DBMSs अक्सर RAM में अनुक्रमित कैश करने की कोशिश करते हैं, जो अपने आप में एक हार्ड डिस्क * की तुलना में बहुत तेज है। तीसरा , सूचकांकों में कोई डुप्लिकेट पंक्तियाँ नहीं हैं। इसका मतलब यह है कि जैसे ही हम पहला मूल्य पाते हैं, खोज को रोका जा सकता है - यह अंतिम भी है। चौथा , सूचकांक में डेटा को क्रमबद्ध किया गया है। और तीसरी और चौथी बात, एक साथ वे द्विआधारी खोज एल्गोरिथ्म का उपयोग करने की अनुमति देते हैं (उर्फ आधे हिस्से में विभाजित करने की विधि), जिसकी प्रभावशीलता साधारण संपूर्ण खोज से कई गुना अधिक है।

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

अनुक्रमण एक महान शक्ति है। लेकिन अगर आप डेटा टेबल में पंक्तियों के सभी इंडेक्स टेबल पॉइंटर्स को SIMULTANEOUSLY को सबमिट करते हैं, तो आपको एक जटिल "वेब" मिलता है:



और यह वेब, कई इंटरसेक्टिंग तीरों के साथ, हमें समस्या में लाता है (यह केवल इसे स्पष्ट रूप से प्रदर्शित करता है) जो गैर-क्लस्टर इंडेक्स बनाता है।

विखंडन

MySQL ऑप्टिमाइज़र सभी छोटे तालिकाओं को खोजने के लिए अनुक्रमणिका का उपयोग न करने का निर्णय ले सकता है (कुछ दर्जन रिकॉर्ड तक - यह विशिष्ट डेटा संरचना और सूचकांक पर निर्भर करता है)। क्यों? क्योंकि खोज केवल क्रमिक रूप से डेटा के माध्यम से होती है। और इंडेक्स पॉइंटर से तात्पर्य डेटा के टुकड़ों को अलग करना है। और सूचकांक से लिंक में कूदना अंततः एक पूर्ण खोज से अधिक खर्च हो सकता है।

तो, अनुक्रमण के विकास के इस स्तर पर हमारे पास क्या है। एक बड़े अनुक्रमित खंडित तालिका की कल्पना करें। जैसे ही डेटा अव्यवस्थित और अनसुलझी आया, वे बच गए। अब सूचकांक तालिका की कल्पना करें। और हमारे अच्छे पुराने अनुरोध:

 SELECT * FROM `t1` WHERE `fld1` = 12; 

क्या चल रहा है? सूचकांक में मूल्य पाया जाता है - यह त्वरित और आसान है - और जिन पंक्तियों को यह सूचकांक संदर्भित करता है उन्हें डेटा तालिका से पढ़ा जाता है। स्वाभाविक रूप से, तालिका के एक बड़े विखंडन के साथ, इसके विभिन्न हिस्सों से पढ़ने का ओवरहेड ध्यान देने योग्य हो जाता है।

और यहाँ यह हमारे लिए उपयोगी है ...

क्लस्टर सूचकांक

क्लस्टर इंडेक्स गैर-संकुलित लोगों से उसी तरह भिन्न होते हैं जैसे किसी पुस्तक की सामग्री की तालिका एक वर्णमाला सूचकांक से भिन्न होती है। सटीक शब्द (मान) के लिए वर्णमाला सूचकांक (गैर-संकुलित सूचकांक) सटीक पृष्ठ संख्या (डेटाबेस में पंक्तियाँ) देता है। सामग्री की तालिका किसी विशेष अध्याय के अनुरूप पृष्ठों की श्रेणी को इंगित करती है, जिसमें खोज शब्द पहले से ही पाया गया है। इसके अलावा, प्रत्येक अध्याय, यदि यह पर्याप्त बड़ा है, तो इसमें सामग्री की अपनी तालिका हो सकती है।

एक गुच्छेदार सूचकांक एक पेड़ की तरह डेटा संरचना है जिसमें सूचकांक मूल्यों को उनके अनुरूप डेटा के साथ संग्रहीत किया जाता है। ऐसे संगठन में सूचकांक और डेटा दोनों का आयोजन किया जाता है। तालिका में एक नई पंक्ति जोड़ते समय, इसे फ़ाइल * के अंत में जोड़ा जाता है, न कि समतल सूची के अंत में, बल्कि छँटाई करके इसके अनुरूप पेड़ की संरचना की वांछित शाखा में।

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

MySQL के लिए सबसे शक्तिशाली और उत्पादक इंजनों में से एक InnoDB है। इसके कई कारण हैं, और उनमें से एक क्लस्टर इंडेक्स है। यह समझने का सबसे आसान तरीका है कि गुच्छेदार अनुक्रमित कैसे व्यवस्थित किए जाते हैं, उन्हें गतिशीलता में कल्पना करना है: डेटा बढ़ने पर वे कैसे बढ़ते हैं, और तालिका कैसे शाखा में शुरू होती है।

स्टेज एक: फ्लैट सूची

InnoDB डेटा 16 Kb पृष्ठों में संग्रहीत है। एक पृष्ठ का आकार हमारे पेड़ की संरचना के नोड का अधिकतम आकार है, जिस पर शाखा शुरू होती है। यदि पूरी तालिका एक पृष्ठ पर फिट होती है, तो इसे अलग-अलग सूचकांक तालिका के बिना, कुंजी फ़ील्ड द्वारा छांटी गई एक फ्लैट सूची के रूप में संग्रहीत किया जाता है।



भविष्य में, हमारे सभी डेटा को बिल्कुल उसी छोटी प्लेटों के साथ प्रस्तुत किया जाएगा, और सूचकांक पृष्ठों की श्रृंखला उन्हें एक पेड़ में जोड़ देगी।

दूसरा चरण: वृक्ष

जब डेटा अब एक पेज पर फिट नहीं होता है, तो सूची एक पेड़ में बदल जाती है। डेटा पेज को दो में विभाजित किया गया है, और उस नोड (उस पेज पर) में जहां डेटा हुआ करता था, अब दोनों पृष्ठों को कवर करने वाला एक सूचकांक है। इस तरह के पेड़ के एक विशेष नोड में सभी बाल नोड्स या अंतिम डेटा के सूचकांक शामिल होने चाहिए, यदि नोड अंतिम है। नोड्स केवल एक ही दिशा में एक-दूसरे का उल्लेख कर सकते हैं: माता-पिता से बच्चे तक।



जैसा कि आप अधिक से अधिक डेटा जोड़ते हैं, पेड़ अधिक जटिल और गहरा हो जाएगा। और जितना अधिक होगा और ब्रांचिंग होगी, उतना ही अधिक लाभ डेटा स्टोर करने के लिए इस तरह की योजना देगा।



ग्रे पृष्ठ पहले चरण के पृष्ठ के समान हैं - ये हमारे पेड़ के डेटा, पत्तियों (लीफ नोड्स) को क्रमबद्ध करते हैं। ब्लू पेज एक पेड़ का मध्यवर्ती नोड है जिसमें केवल एक सूचकांक होता है और इसमें डेटा नहीं होता है। तीर विशिष्ट प्रमुख मानों के लिए खोज पथों को चिह्नित करते हैं।

हमारी क्वेरी (हरे तीर) को याद करें:

 SELECT * FROM `t1` WHERE `fld1` = 12; 

तालिका की ओर मुड़ते हुए, क्वेरी पहले पृष्ठ पर जाती है और एक इंडेक्स प्राप्त करती है, जो इसे तुरंत डेटा के साथ अंतिम पृष्ठ पर भेजती है, जहां पंक्तियाँ होती हैं जो खोज मानदंडों को पूरा करती हैं। पृष्ठ को पहले से ही खोज चरण में पढ़ा गया है, सभी डेटा एकत्र किए गए हैं, डेटाबेस एक प्रतिक्रिया वापस कर सकता है।

हालाँकि, किसी अन्य पृष्ठ की ओर इशारा करने वाला एक इंडेक्स जरूरी नहीं कि वह सीधे डेटा पेज पर ले जाए। सूचकांक एक पृष्ठ के लिए एक मध्यवर्ती सूचकांक के साथ इंगित कर सकता है। शायद, तालिका के बड़े संस्करणों के साथ, डेटाबेस को खोज के अधिक पुनरावृत्तियों का संचालन करना होगा, लेकिन इस तरह के प्रत्येक पुनरावृत्ति में न्यूनतम मात्रा में डेटा शामिल होता है, और इसलिए, सामान्य रूप से, खोज तेज होती है।

यहां एक सरल नियम लागू होता है, जो किसी भी प्रकार के सूचकांक के लिए प्रासंगिक होता है: डेटा को जितना अधिक विविध किया जाता है, विशिष्ट मूल्यों के लिए खोज करने के लिए सूचकांक का उपयोग करना उतना ही अधिक कुशल होता है।

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

जटिल (समग्र) क्लस्टर कुंजियों के लिए, उनके लिए सटीक एक ही योजना लागू होती है, केवल डेटा दो क्षेत्रों द्वारा सॉर्ट किया जाता है। इंडेक्स अपने आप में नॉनक्लस्टर कंपोजिट कुंजी से अलग होता है।

InnoDB में क्लस्टर कुंजी

यहां सब कुछ सरल है। प्रत्येक InnoDB तालिका में एक क्लस्टर कुंजी होती है। हर एक। बिना किसी अपवाद के।

यह बहुत अधिक दिलचस्प है कि इसके लिए कौन से फ़ील्ड चुने गए हैं।

• यदि तालिका में प्राथमिक कुंजी निर्दिष्ट है, तो यह है
• अन्यथा, यदि तालिका में अद्वितीय (अद्वितीय) सूचकांक हैं - यह उनमें से पहला है
• अन्यथा, InnoDB स्वतंत्र रूप से एक 6 बाइट सरोगेट आईडी के साथ एक छिपा हुआ क्षेत्र बनाता है

बेहतर होगा कि आप अपने लंबे समय से पीड़ित सर्वर को तीसरे बिंदु पर न लाएं, और स्वयं आईडी जोड़ें।

और यह मत भूलो कि द्वितीयक कुंजियों में InnoDB तालिका में समाप्ति पंक्ति के संदर्भ के रूप में क्लस्टर कुंजी फ़ील्ड मानों का पूरा सेट संग्रहीत करता है। प्राथमिक कुंजी जितनी बड़ी होगी, माध्यमिक कुंजी उतनी बड़ी होगी।