sql एक - डेटाबेस अनुक्रमण कैसे काम करता है?




प्रणाली प्रबंधन (8)

क्लासिक उदाहरण "पुस्तकें में सूचकांक"

1000 पृष्ठों की एक "पुस्तक" पर विचार करें, 100 वर्गों द्वारा विभाजित, एक्स पृष्ठों के साथ प्रत्येक अनुभाग।

सरल, हुह?

अब, इंडेक्स पेज के बिना, एक विशेष अनुभाग ढूंढने के लिए जो अक्षर "एस" से शुरू होता है, आपके पास पूरी पुस्तक के माध्यम से स्कैन करने से कोई अन्य विकल्प नहीं है। यानी: 1000 पेज

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

लेकिन फिर, 1000 पृष्ठों के अलावा, आपको इंडेक्स पेज प्रदर्शित करने के लिए एक और ~ 10 पृष्ठों की आवश्यकता होगी, इसलिए पूरी तरह से 1010 पेज।

इस प्रकार, इंडेक्स एक अलग सेक्शन है जो अनुक्रमित कॉलम + सूचक के सूचकांक पंक्तियों को कुशल लुक-अप के क्रमबद्ध क्रम में संग्रहीत करता है।

स्कूलों में चीजें सरल हैं, है ना? : पी

यह देखते हुए कि इंडेक्सिंग इतना महत्वपूर्ण है क्योंकि आपका डेटा सेट आकार में बढ़ता है, क्या कोई यह बता सकता है कि डेटाबेस-अज्ञेय स्तर पर अनुक्रमण कैसे काम करता है?

किसी फ़ील्ड को इंडेक्स करने के लिए पूछताछ के बारे में जानकारी के लिए, मैं डेटाबेस कॉलम को कैसे इंडेक्स कर सकता हूं


एक सूचकांक केवल एक डेटा संरचना है जो डेटाबेस में किसी विशिष्ट कॉलम के लिए खोज को तेज बनाता है। यह संरचना आमतौर पर एक बी-पेड़ या हैश टेबल होती है लेकिन यह कोई अन्य तर्क संरचना हो सकती है।

अधिक जानकारी के लिए, मैं अनुशंसा करता हूं: डेटाबेस इंडेक्स कैसे काम करते हैं? और, इंडेक्स कैसे मदद करते हैं?


सरल विवरण !!!!!!!!!!

सूचकांक डेटा संरचना के अलावा कुछ भी नहीं है जो किसी तालिका में किसी विशिष्ट कॉलम के मानों को संग्रहीत करता है। एक तालिका के कॉलम पर एक इंडेक्स बनाया गया है।

उदाहरण, हमारे पास एक डेटाबेस तालिका है जिसे उपयोगकर्ता नामक तीन कॉलम - नाम, आयु और पता है। मान लें कि उपयोगकर्ता तालिका में हजारों पंक्तियां हैं।

अब, मान लीजिए कि हम 'जॉन' नामक किसी भी उपयोगकर्ता के सभी विवरण ढूंढने के लिए एक क्वेरी चलाने के लिए चाहते हैं। अगर हम निम्नलिखित क्वेरी चलाते हैं।

SELECT * FROM User 
WHERE Name = 'John'

डेटाबेस सॉफ़्टवेयर को सचमुच उपयोगकर्ता तालिका में प्रत्येक पंक्ति को देखना होगा ताकि यह देखने के लिए कि उस पंक्ति का नाम 'जॉन' है या नहीं। इसमें काफी समय लगेगा।
यह वह जगह है जहां इंडेक्स हमारी मदद करता है "इंडेक्स का प्रयोग उस तालिका में रिकॉर्ड्स / पंक्तियों की संख्या को कम करके खोज क्वेरी को तेज करने के लिए किया जाता है, जिसकी जांच की आवश्यकता होती है"।
एक सूचकांक कैसे बनाएँ

CREATE INDEX name_index
ON User (Name)

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


इसकी आवश्यकता क्यों है?

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

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

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

अनुक्रमण क्या है?

इंडेक्सिंग कई क्षेत्रों पर कई रिकॉर्ड सॉर्ट करने का एक तरीका है। किसी तालिका में किसी फ़ील्ड पर एक इंडेक्स बनाना एक और डेटा स्ट्रक्चर बनाता है जो फ़ील्ड मान रखता है, और रिकॉर्ड के लिए एक पॉइंटर जो इससे संबंधित है। इस सूचकांक संरचना को तब क्रमबद्ध किया जाता है, जिससे बाइनरी खोजों को निष्पादित किया जा सकता है।

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

यह कैसे काम करता है?

सबसे पहले, चलिए एक नमूना डेटाबेस तालिका स्कीमा रूपरेखा है;

Field name       Data type      Size on disk
id (Primary key) Unsigned INT   4 bytes
firstName        Char(50)       50 bytes
lastName         Char(50)       50 bytes
emailAddress     Char(100)      100 bytes

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

उदाहरण 1 - सॉर्ट किए गए बनाम छोड़े गए फ़ील्ड

R = 204 बाइट्स की रिकॉर्ड लंबाई देकर एक निश्चित आकार के r = 5,000,000 रिकॉर्ड्स के हमारे नमूना डेटाबेस को देखते हुए और वे MyISAM इंजन का उपयोग कर तालिका में संग्रहीत हैं जो डिफ़ॉल्ट ब्लॉक आकार B = 1,024 बाइट्स का उपयोग कर रहा है। तालिका का अवरुद्ध कारक bfr = (B/R) = 1024/204 = 5 डिस्क प्रति डिस्क ब्लॉक होगा। तालिका को पकड़ने के लिए आवश्यक ब्लॉक की कुल संख्या N = (r/bfr) = 5000000/5 = 1,000,000 ब्लॉक है।

आईडी फ़ील्ड पर एक रैखिक खोज को मूल्य खोजने के लिए औसत N/2 = 500,000 ब्लॉक एक्सेस की आवश्यकता होगी, बशर्ते आईडी फ़ील्ड एक प्रमुख फ़ील्ड हो। लेकिन चूंकि आईडी फ़ील्ड को भी सॉर्ट किया गया है, इसलिए एक बाइनरी खोज आयोजित की जा सकती है जिसमें log2 1000000 = 19.93 = 20 ब्लॉक एक्सेस की आवश्यकता होती है। तुरंत हम देख सकते हैं कि यह एक कठोर सुधार है।

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

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

Field name       Data type      Size on disk
firstName        Char(50)       50 bytes
(record pointer) Special        4 bytes

नोट : तालिका के आकार के आधार पर MySQL में पॉइंटर्स 2, 3, 4 या 5 बाइट लंबाई में हैं।

उदाहरण 2 - अनुक्रमण

r = 5,000,000 बाइट्स की इंडेक्स रिकॉर्ड लंबाई के साथ r = 5,000,000 रिकॉर्ड के हमारे नमूना डेटाबेस को देखते हुए और डिफ़ॉल्ट ब्लॉक आकार B = 1,024 बाइट्स का उपयोग करते हुए। इंडेक्स का अवरुद्ध कारक bfr = (B/R) = 1024/54 = 18 डिस्क प्रति डिस्क ब्लॉक होगा। इंडेक्स को पकड़ने के लिए आवश्यक ब्लॉक की कुल संख्या N = (r/bfr) = 5000000/18 = 277,778 ब्लॉक है।

अब प्रथम नाम फ़ील्ड का उपयोग करके खोज प्रदर्शन को बढ़ाने के लिए इंडेक्स का उपयोग कर सकती है। यह log2 277778 = 18.08 = 19 ब्लॉक के औसत के साथ इंडेक्स की बाइनरी खोज की अनुमति देता है। वास्तविक रिकॉर्ड का पता ढूंढने के लिए, जिसके लिए पढ़ने के लिए और अधिक ब्लॉक पहुंच की आवश्यकता है, कुल मिलाकर 19 + 1 = 20 ब्लॉक एक्सेस तक पहुंचने के लिए, 1,000,000 ब्लॉक एक्सेस से बहुत रोना गैर-अनुक्रमित तालिका में पहला नाम मिलान ढूंढने के लिए आवश्यक है ।

इसका इस्तेमाल कब किया जाना चाहिए?

यह देखते हुए कि एक इंडेक्स बनाने के लिए अतिरिक्त डिस्क स्पेस (उपरोक्त उदाहरण से अतिरिक्त 277,778 ब्लॉक, ~ 28% की वृद्धि) की आवश्यकता होती है, और यह भी बहुत से इंडेक्स फ़ाइल सिस्टम आकार सीमा से उत्पन्न होने वाले मुद्दों का कारण बन सकता है, सही चयन करने के लिए सावधानीपूर्वक विचार का उपयोग किया जाना चाहिए सूचकांक के लिए फ़ील्ड।

चूंकि इंडेक्स का उपयोग केवल रिकॉर्ड्स के भीतर एक मिलान क्षेत्र की खोज को तेज़ी से करने के लिए किया जाता है, इसलिए इसका कारण यह है कि केवल आउटपुट के लिए उपयोग किए जाने वाले इंडेक्सिंग फ़ील्ड डिस्क स्थान की बर्बादी और सम्मिलित करने या ऑपरेशन हटाने के दौरान प्रोसेसिंग समय की बर्बादी होगी, और इस प्रकार से बचा जाना चाहिए। बाइनरी खोज की प्रकृति को भी देखते हुए, डेटा की कार्डिनालिटी या विशिष्टता महत्वपूर्ण है। 2 की कार्डिनालिटी वाले फ़ील्ड पर इंडेक्सिंग डेटा को आधे हिस्से में विभाजित करेगी, जबकि 1,000 की कार्डिनालिटी लगभग 1000 रिकॉर्ड लौटाएगी। इस तरह की कम कार्डिनालिटी के साथ प्रभावशीलता एक रैखिक प्रकार में कम हो जाती है, और क्वेरी ऑप्टिमाइज़र सूचकांक का उपयोग करने से बच जाएगा यदि कार्डिनालिटी रिकॉर्ड संख्या के 30% से कम है, जिससे सूचकांक को अंतरिक्ष की बर्बादी प्रभावी ढंग से बना दिया जाता है।


बस एक त्वरित सुझाव .. इंडेक्सिंग लागत के रूप में आपको अतिरिक्त लिखने और संग्रहण स्थान की आवश्यकता होती है, इसलिए यदि आपके एप्लिकेशन को अधिक डालने / अपडेट ऑपरेशन की आवश्यकता है, तो हो सकता है कि आप इंडेक्स के बिना टेबल का उपयोग करना चाहें, लेकिन यदि इसे अधिक डेटा पुनर्प्राप्ति संचालन की आवश्यकता है, तो आपको अनुक्रमित होना चाहिए तालिका।


पहली बार मैंने इसे पढ़ा यह मेरे लिए बहुत उपयोगी था। धन्यवाद।

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

हालांकि, इंडेक्स डेटा और टेबल डेटा के लिए दो अलग-अलग हार्ड डिस्क पर दो अलग-अलग स्थानों को परिभाषित करने से समय की बढ़ी हुई लागत की समस्या कम हो सकती है। इसके लिए वांछित हार्ड डिस्क और वांछित के रूप में तालिका / अनुक्रमणिका स्थान की परिभाषा के अनुसार अतिरिक्त फ़ाइल समूहों की परिभाषा की आवश्यकता होती है।

इंडेक्स के साथ एक और समस्या समय के साथ उनके विखंडन है क्योंकि डेटा डाला जाता है। REORGANIZE मदद करता है, आपको इसे पूरा करने के लिए दिनचर्या लिखनी होगी।

कुछ परिदृश्यों में एक ढेर इंडेक्स के साथ एक तालिका से अधिक सहायक है,

उदाहरण: - यदि आपके पास बहुत सारे प्रतिद्वंद्वियों लिखते हैं लेकिन रिपोर्टिंग के लिए व्यवसाय के घंटों के बाहर केवल एक रात पढ़ा जाता है।

इसके अलावा, क्लस्टर्ड और गैर-क्लस्टर इंडेक्स के बीच एक भिन्नता बल्कि महत्वपूर्ण है।

मेरी मदद की: - क्लस्टर और गैर क्लस्टर सूचकांक वास्तव में क्या मतलब है?


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

संक्षेप में, एक अनुक्रमणिका एक डेटा संरचना है जो तालिका में एक विशिष्ट कॉलम के लिए मानों को संग्रहीत करती है ताकि क्वेरी खोज तेज हो सके।


UPDATE TQ
SET TQ.IsProcessed = 1, TQ.TextName = 'bla bla bla'
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0

यह सुनिश्चित करने के लिए कि आप जो चाहते हैं उसे अपडेट कर रहे हैं, पहले चुनें

SELECT TQ.IsProcessed, 1 AS NewValue1, TQ.TextName, 'bla bla bla' AS NewValue2
FROM TableQueue TQ
INNER JOIN TableComment TC ON TC.ID = TQ.TCID
WHERE TQ.IsProcessed = 0






sql database performance indexing database-indexes