ऑटो-वृद्धिशील प्राथमिक कुंजी (सरोगेट कुंजी) = बुराई?

इस लेख में, मैं जोश बर्कस, पोस्टग्रेक्यूएल एक्सपर्ट्स इंक के सीईओ द्वारा एक नज़र (ज्यादातर नकारात्मक) ले जाऊंगा। डेटाबेस तालिकाओं के लिए सरोगेट कुंजी का उपयोग करने के लिए, वही INT नहीं है जो हम उपयोग कर रहे हैं। वास्तव में, यह इट्टूलबॉक्स पर उनके लेख का मुफ्त, बहुत कम अनुवाद होगा ।

एक पुरानी परियोजना में किए गए इस विषय पर मेरी अपनी गलतियों की समीक्षा के बाद लेख होगा। मैं युवा और बेवकूफ था, लेकिन वह मुझे माफ नहीं करता।

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

यदि आप एक अनुभवी डीबीए हैं, तो आपको शायद इस तरह से गुजरना चाहिए ताकि परेशान न हों।

लेकिन पहले बातें पहले। पहले बहुत संक्षिप्त अनुवाद:


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

अनुभवहीन डेवलपर्स, यह महसूस नहीं करते कि सरोगेट कुंजियों का उपयोग प्रदर्शन के विचारों के साथ एक व्यावहारिक समझौता है, हर जगह उनका उपयोग करें। यहां तक ​​कि डेटाबेस पर पुस्तकों के लेखक उन्हें किसी भी मामले में सभी तालिकाओं में बनाने की सलाह देते हैं।

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

मैं यह नहीं कह रहा हूं कि सरोगेट कुंजी का उपयोग बिल्कुल नहीं किया जा सकता है, मैं कह रहा हूं कि उनके उपयोग का दुरुपयोग नहीं किया जा सकता है।

किन कारणों से हमें सरोगेट कुंजी का उपयोग करने के लिए प्रेरित किया जा सकता है?

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

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

बाहरी आवश्यकताएं । सहसा आश्वस्त हुआ। आमतौर पर, विकास वातावरण और डेटाबेस उपकरण केवल सरोगेट कुंजी का समर्थन करते हैं। और अगर आपको लगता है कि यह उपकरण उस समस्या में अपरिहार्य है जिसे आप हल कर रहे हैं, तो ...

डेटा संगति । सहसा आश्वस्त हुआ। लेकिन केवल अगर आप वास्तव में योजना का पालन करते हैं और आपका पूरा डिजाइन सावधानी से योजनाबद्ध है।

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

आसान परिवर्तन की संभावना । अस्पष्ट। दरअसल, कुछ DBMS ON UPDATE CASCADE परफॉर्म नहीं कर पा रहे हैं या यह बहुत ही अकुशल तरीके से कर रहे हैं (वैसे, इसे DBMS बदलने की एक वजह के रूप में देखें)। और इस मामले में, यह कारण महत्वपूर्ण हो सकता है। हालांकि, कभी-कभी डेवलपर्स कहते हैं कि रिकॉर्डिंग के लिए कुंजी [प्राथमिक] को नहीं बदला जाना चाहिए और रिकॉर्डिंग के पूरे जीवन चक्र के दौरान समान रहना चाहिए। ध्यान रखें कि यह कथन लानत के लायक नहीं है और निश्चित रूप से, संबंधपरक सिद्धांत में पूरी तरह से अनुपस्थित है।

प्रदर्शन । आमतौर पर एक बुरा कारण। हां, वास्तव में, ऐसी परिस्थितियां उत्पन्न हो सकती हैं जिनमें प्राकृतिक कुंजियों का उपयोग सरोगेट की तुलना में सिस्टम को धीमा कर देता है। लेकिन 80% मामलों में, यह कथन वास्तविक परीक्षणों पर आधारित नहीं है, और ऐसा बयान निराधार है। प्री-ऑप्टिमाइज़ेशन डेटाबेस डिज़ाइन में कई बीमारियों की जड़ है।

मेगा-वॉल्यूम डेटाबेस के लिए, परिणामी तालिका आकार भी मायने रख सकता है। लेकिन इसके लिए आधार बहुत बड़ा होना चाहिए।

प्राथमिक कुंजी के प्रकार और इसके घटकों की संख्या के आधार पर, डेटा की एक बड़ी मात्रा में जुड़ने या छँटाई का प्रदर्शन भी मायने रखता है। हालांकि, मेरा अनुभव बताता है कि जब इस कारण को कहा जाता है, तो यह वास्तविक गणना या प्रदर्शन माप के पीछे बहुत कम होता है। उदाहरण के लिए, www.bricolage.cc कई वर्षों से अपनी तालिकाओं के लिए 14-बाइट संख्यात्मक प्राथमिक कुंजियों का उपयोग कर रहा है। हालांकि, इस मामले में, इतिहास में तीन मिलियन रिकॉर्ड के साथ एक उपयोगकर्ता की उपस्थिति के बाद, जब प्रदर्शन की खातिर प्राथमिक कुंजी बदलने के बारे में सवाल उठता है, तो प्रश्नों को फिर से लिखकर इस समस्या को हल किया गया था। उत्पादकता में लगभग 10 गुना वृद्धि हासिल की गई।

कृपया ध्यान दें कि समस्या सरोगेट कुंजी का उपयोग नहीं है, बल्कि उनका दुरुपयोग है। ”

मेरे बहुत संक्षिप्त अनुवाद का अंत। यहां मूल (जिसे प्राथमिक कीविल कहा जाता है): it.toolbox.com/home/search.aspx?r=%22Primary+kevill%22&community=1&contentType=5

अगर मुझे अनुवाद में कुछ महत्वपूर्ण याद आया, तो कृपया मुझे इसके बारे में बताएं। मैं जोड़ दूंगा।

अब थोड़ा मैं खुद क्या सोचता हूँ।

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

इस तथ्य के बावजूद कि लेख ऐसा लगता है जैसे कि सरोगेट कुंजी बुराई का प्रतीक है, लेखक कई बार जोर देता है कि समस्या उनका उपयोग नहीं है, बल्कि उनका दुरुपयोग है।

इस लेख ने इस अर्थ में मेरी आंखें खोल दीं कि मैंने हमेशा यह सोचा था कि प्राथमिक कुंजी के लिए विशेष उम्मीदवारों की तलाश न करें, लेकिन बस एक INT NOT NULL AUTO_INCREMENT PRIMARY KEY फ़ील्ड बनाएं और स्थिर रहें। बेशक, मुझे पता था कि आप प्राथमिक कुंजी के रूप में कोई भी अनोखी कुंजी चुन सकते हैं, लेकिन मैंने कभी उस पर ध्यान केंद्रित नहीं किया। मैंने इस बारे में कभी नहीं सोचा कि वास्तव में इस डेटाबेस पंक्ति को क्या विशिष्ट बनाता है और यह महत्वपूर्ण क्यों है। जैसा कि यह निकला, व्यर्थ में।

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

परियोजना एक बंद धार ट्रैकर है। मैं आपको सामान्यीकरण और अन्य सभी प्रकार की समस्याओं पर ध्यान नहीं देने के लिए कहता हूं। अगर मैं इसे अभी लिखता, तो शायद मैं कुछ अलग करता। चलो सरोगेट कुंजी पर ध्यान केंद्रित करते हैं।

डेटाबेस संरचना

pastebin.com/LstH8Xfx

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

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

इसका उत्तर बहुत सरल है। उपयोगकर्ता आईडी और घटना के समय का संयोजन। और यहाँ मैंने अचानक दूसरी तरफ से स्थिति देखी। मेरी लगभग सभी पुरानी परियोजनाओं में, DATETIME फ़ील्ड का उपयोग लॉग में समय संग्रह करने के लिए किया जाता है। सिर्फ इसलिए कि यह सुविधाजनक है। हां, मुझे पता था कि यह दूसरे के लिए सटीक रूप से संग्रहीत किया गया था और यह पूरी तरह से मेरे अनुकूल था। अब, जब मैंने प्राकृतिक कुंजियों को खोजना शुरू किया, तो यह अचानक ही मेरे साथ हुआ और इस परिणाम का क्या परिणाम हुआ। प्रश्न में धार ट्रैकर बहुत भारी है और बहुत कुछ एक सेकंड के भीतर हो सकता है। वास्तव में, अगर इस शापित सरोगेट कुंजी के साथ लॉग में एक ही समय के साथ कई घटनाएं होती हैं और वे एक के बाद एक बहुत तेज़ी से हुईं, तो मैं कह सकता हूं कि कौन सा पहला हुआ और आखिरी बार केवल सरोगेट कुंजी के ऑटो-इंक्रीमेंट द्वारा निर्देशित किया गया। तारीख सूचना क्षेत्र, जो कि ऐसी चीजों की रिपोर्ट करने के लिए सटीक रूप से बनाया गया था, मेरी मदद नहीं करेगा। और मैं सभी घटनाओं के बीच सटीक अंतराल निर्धारित करने में सक्षम नहीं था।

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

मैं यह कहना चाहता हूं कि प्राकृतिक कुंजी खोजने के दृष्टिकोण से समस्या पर विचार करना कुछ अलग दृष्टिकोण है। अपने प्रोजेक्ट के डिज़ाइन को इस तरह से देखने की कोशिश करें और देखें कि क्या सामने आता है।

ऐसा लगता है कि मेरी व्याख्या अराजक हो गई। मुझे उम्मीद है, फिर भी, मैं आपको मेरे विचार से अवगत कराने में कामयाब रहा।

अब मेज सहकर्मी है। उसके पास पहले से ही एक अनूठी कुंजी है जो बस प्राथमिक भूमिका के लिए पूछती है। प्रति सेकंड कई सैकड़ों आवेषण / विलोपन सहकर्मी तालिका में किए जाते हैं, और एक अतिरिक्त कुंजी को प्राथमिक कुंजी के रूप में रखना केवल लाभहीन होता है। इसलिए मैंने इसे खत्म कर दिया।

सत्र तालिका। किसी कारण से, मैं पूरी तरह से PHP सत्रों पर निर्भर नहीं था, लेकिन आंशिक रूप से कार्यान्वित मेरा। इस तालिका की प्राथमिक कुंजी एक यादृच्छिक मूल्य है। न केवल 40-चरित्र यादृच्छिक दृश्यों का उपयोग करना बेवकूफी है, बल्कि वास्तव में यहां इसकी आवश्यकता नहीं है। इस तालिका में प्रविष्टियों के लिए एक प्राकृतिक कुंजी के रूप में क्या कार्य करता है? इस परियोजना में, उपयोगकर्ता को एक ही समय में कई कंप्यूटरों से लॉग इन करने की अनुमति नहीं थी। उम। user_id? इस मूल्य के संबंध में बाकी सब कुछ गौण है। मैं अब इस सरल कथन से इस प्रकार का विश्लेषण नहीं करूँगा। सत्र तालिका को हटाने और दूसरे तंत्र को लागू करने के लिए बहुत सारी चीजें। कई विकल्प हैं।

चलिए धार तालिका पर चलते हैं।

एक छोटा विषयांतर ताकि आप चर्चा के विषय की कल्पना कर सकें। टोरेंट ट्रैकर जो मैंने विकसित किया था, उसी समय वितरित की गई फ़ाइलों के लिए पहला बीज था। टोरेंट टेबल में बैठी फाइलों के बारे में जानकारी रखी गई। ये फाइलें सर्वर फाइल सिस्टम में थीं, इसी के लिए .torrent फाइलें स्कीम वन फाइल = एक टोरेंट के अनुसार बनाई गई थीं, जिन्हें यूजर्स ने डाउनलोड किया था। प्रत्येक धार में एक तथाकथित info_hash होता है, जो विशिष्ट रूप से इसकी पहचान करता है ।

सहकर्मी तालिका में इस क्षेत्र को peer_info_hash कहा जाता है। और धार तालिका में, यह धार_info_hash फ़ील्ड है। torrent_id वहां बहुत ही कम है। निश्चित रूप से। कृपया ध्यान दें कि सहकर्मी torrent_id तालिका में भी है। यह स्पष्ट नहीं है कि क्यों।

खैर, उपयोगकर्ता तालिका। ऐसा लगता है कि यहाँ मैं सिर्फ गलतियाँ नहीं कर सकता। गलत था।

प्राधिकरण प्रणाली में, धार ट्रैकर्स, उपयोगकर्ता के लिए अद्वितीय मूल्य के साथ एक जीईटी पैरामीटर का उपयोग किया जाता है। तालिका में, यह user_torrent_uid का मान है। तो मुझसे पूछें, जिसने इस मूल्य को एक या दूसरे संस्करण में एक प्राकृतिक कुंजी के रूप में उपयोग करने से रोका था? हां, यह बदल सकता है। एक बहुत ही दुर्लभ मामले में। तो क्या? यदि 8 बाइट्स बहुत लंबी हैं, तो आप एक नियमित यादृच्छिक INT ले सकते हैं और इसे टेक्स्ट में बदल सकते हैं, जैसा कि स्मार्ट लोग फ्लिकर पर करते हैं। यह संभव था ... हां, बहुत सारी चीजें संभव थीं।

वहां तुम जाओ। सब कुछ स्पष्ट है, है ना? :)