👊🏾 🤫 🌴 कस्टम चर का उपयोग करके MySQL क्वेरी का अनुकूलन 🐴 🥥 🚠

परिचय। आधुनिक दुनिया में बड़ी संख्या में ऐसे कार्य हैं जिनके भीतर एक ही प्रकार के डेटा को बड़ी मात्रा में संसाधित करना आवश्यक है। ज्वलंत उदाहरण स्टॉक कोट्स, मौसम की स्थिति, नेटवर्क ट्रैफिक आंकड़ों के विश्लेषण के लिए सिस्टम हैं। इनमें से कई प्रणालियाँ विभिन्न संबंधपरक डेटाबेसों का उपयोग करती हैं, जिनमें से तालिकाओं में डेटा के ऐसे खंड होते हैं कि इन तालिकाओं के लिए प्रश्नों का सही संकलन और अनुकूलन बस सिस्टम के सामान्य कामकाज के लिए आवश्यक हो जाता है। यह लेख सबसे बड़े रूसी नेटवर्क प्रदाताओं में से एक के मार्ग से गुजरने वाले नेटवर्क ट्रैफ़िक पर आँकड़ों से युक्त MySQL DBMS टेबलों से डेटा प्राप्त करने के लिए कई कार्यों के समाधान (और उपयोग की जाने वाली विधियों की तुलनात्मक समय विशेषताओं) के तरीकों का वर्णन करता है। मुख्य राउटर से आने वाले डेटा स्ट्रीम की तीव्रता ऐसी है कि उपयोग किए गए नेटवर्क ट्रैफिक मॉनिटरिंग सिस्टम के डेटाबेस टेबलों (राउटर नेटफ्लो प्रोटोकॉल के माध्यम से डेटा निर्यात में राउटर) में टीसीपी / आईपी लेनदेन के बारे में जानकारी प्राप्त करने के लिए औसतन 400 मिलियन से एक अरब रिकॉर्ड तक प्राप्त होता है। MySQL का उपयोग निगरानी प्रणाली के लिए DBMS के रूप में किया जाता है।

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

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

एक उदाहरण के रूप में, विचाराधीन इंटरनेट सेवा प्रदाता के अधीनस्थ संगठनों वाली तालिका पर विचार करें। इस संदर्भ में, तालिका में दो फ़ील्ड हैं - संगठन आईडी ( org_id ) और संगठन का नाम ( org_name )।

मान लीजिए कि हमने org_id फ़ील्ड में एक इंडेक्स जोड़ा (चित्र 1 देखें)। अनुक्रमणिका में तालिका से प्रत्येक पंक्ति के लिए एक प्रविष्टि होती है, और सूचकांक प्रविष्टियों को org_id मान द्वारा क्रमबद्ध किया जाता है । अब, तालिका के सभी रिकॉर्ड को स्कैन करने के बजाय, हम सूचकांक का उपयोग कर सकते हैं। मान लीजिए कि आप एक संगठन रिकॉर्ड ( इंस्टीट्यूट ऑफ ऑटोमेशन एंड इलेक्ट्रोमेट्री एसबी आरएएस ) युक्त स्ट्रिंग ढूंढना चाहते हैं, जिसके लिए अद्वितीय पहचानकर्ता ( org_id ) है 2. इंडेक्स द्वारा स्कैन करना एक पंक्ति देता है। अनुक्रमणिका के मानों को आरोही क्रम में क्रमबद्ध किया जाता है, इसलिए अगले मूल्य ( org_id के बराबर 3 ) पर पहुंचकर , आप स्कैन को पूरा कर सकते हैं: 3 के बाद , हम अब वांछित मान नहीं पाएंगे। यदि वांछित मान विशेष एल्गोरिदम (उदाहरण के लिए, द्विआधारी खोज विधि) का उपयोग करके तालिका के बीच में कहीं स्थित हैं, तो आप तालिका के लंबे रेखीय स्कैन के बिना वांछित पंक्ति में जा सकते हैं।

अंजीर। 1. एक सूचकांक का उपयोग करते हुए तालिका के साथ सहभागिता

सामान्य तौर पर, अधिकांश प्रश्नों को तालिका में आवश्यक अनुक्रमित सही ढंग से बनाकर और क्वेरी का निर्माण करके अनुकूलित किया जा सकता है ताकि वे प्रभावी रूप से उपयोग किए जा सकें। हालांकि, ऐसे प्रश्न हैं जिनकी निष्पादन की गति सूचकांक को बिल्कुल भी मदद नहीं करती है - उदाहरण के लिए, जब सूचकांक तालिका में सभी अभिलेखों के लगभग 1/10 से अधिक का चयन करता है, तो ऑप्टिमाइज़र डिस्क से अनुक्रमिक पढ़ने के बाद, सूचकांक का उपयोग करने के बजाय एफटीएस (पूर्ण तालिका स्कैन) पसंद करेगा। डिस्क के विभिन्न हिस्सों से पढ़ने से अधिक तेजी से होता है (डिस्क पर सिर को घुमाते हुए - एक "महंगा" ऑपरेशन है)। आशावादी को FORCE INDEX विकल्प का उपयोग करते हुए सूचकांक का उपयोग करने के लिए "मजबूर" किया जा सकता है, लेकिन यह आमतौर पर गति में लाभ नहीं देता है। टेबल्स इतने बड़े भी हो सकते हैं कि इंडेक्स बनाना अंतरिक्ष की दृष्टि से या ऑपरेशन की अवधि के लिए अव्यावहारिक होगा। इसके अलावा, इंडेक्स का उपयोग करने की सुविधा के लिए, आपको एक निश्चित तरीके से "भुगतान" करना होगा, इंडेक्स में अपनी कमियां हैं। उनमें से ज्यादातर नगण्य हैं, लेकिन उनके बारे में जानने लायक है।

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

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

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

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

SELECT @min_price:=MIN(price) FROM shop; SELECT * FROM shop WHERE price=@min_price;

उपयोगकर्ता चर @var_name के रूप में लिखे गए हैं, और पूर्णांक ( int ), आंशिक ( वास्तविक ) और स्ट्रिंग ( स्ट्रिंग ) प्रकार के मान ले सकते हैं। वैरिएबल का मान असाइन करना SET स्टेटमेंट ( SET @var1='RUS' ), SELECT स्टेटमेंट ( SELECT 'RUS' INTO @var1; ) के माध्यम से या ": = ऑपरेटर" ("=" समानता के रूप में माना जाता है) के माध्यम से किया जाता है। @ var1: = 72 )।

एक असिंचित चर NULL है ।

क्वेरी निष्पादित करने से पहले वेरिएबल्स को इनिशियलाइज़ करना आवश्यक है, क्योंकि अन्यथा क्वेरी के निष्पादन के दौरान वेरिएबल्स के प्रकार का निर्धारण किया जाएगा, और यह गलत तरीके से निर्धारित किया जाएगा, परिणामस्वरूप, एक गलत परिणाम प्राप्त होगा। उदाहरण , चर्चा ।

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

पीपी का उपयोग करने के नुकसान।

अन्य DBMSs के लिए जटिल पोर्टेबिलिटी (पीपी तंत्र MySQL DBMS के ढांचे के भीतर रिलेशनल डेटाबेस मॉडल पर एक निश्चित ऐड-ऑन के रूप में कार्य करता है)। हालाँकि, यह कमी बहुत महत्वपूर्ण नहीं है, क्योंकि इस समय सभी प्रमुख DBMS के ANSI SQL मानक से उनके विचलन हैं।
उपयोगकर्ता चर का व्यवहार जटिल क्वेरी के निष्पादन क्रम पर निर्भर करेगा, जिसे MySQL अनुकूलक (यदि STRAIGHT_JOIN ऑपरेटर का उपयोग नहीं किया गया है) द्वारा बदला जा सकता है।

उदाहरण संख्या १। नेटवर्क ट्रैफ़िक की निगरानी में एक महत्वपूर्ण कार्य इंटरनेट प्रदाताओं से प्रदान किए गए संचार चैनलों की लोडिंग चोटियों को ठीक करना है (देखें। 2)।

अंजीर। 2. ग्राफ दिखा चैनल लोडिंग गतिशीलता

मॉनिटरिंग सिस्टम आने वाले और बाहर जाने वाले ट्रैफ़िक के बारे में पूरी तरह से और विशेष रूप से प्रत्येक चैनल के लिए अलग-अलग तालिकाओं में जानकारी संग्रहीत करता है। तालिका प्रकार MyISAM है ।
इस प्रकार की तालिका का एक सरलीकृत रूप:

टी (टाइमस्टैम्प)

src (भेजे गए बाइट की संख्या)

dst (प्राप्त बाइट्स की संख्या)

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

 SELECT dst FROM t t2 WHERE t2.t<t1.t ORDER BY t2.t DESC LIMIT 1;

जहाँ t1.t टाइम स्टैम्प का वर्तमान मूल्य है), लेकिन यह डिज़ाइन अनुरोध को बहुत जटिल करता है, सामान्य तौर पर, O (n) से अनुरोध की जटिलता O (n ² ) तक बढ़ जाती है (इस तथ्य के कारण कि प्रत्येक बार स्टैम्प के लिए पिछले एक की गणना करना आवश्यक है) संपूर्ण तालिका को स्कैन करना), जो क्वेरी की गति को बहुत प्रभावित करता है। यदि तालिका में फ़ील्ड टी पर एक अद्वितीय सूचकांक है, तो गणना बहुत तेज़ होगी, लेकिन यह तब होता है जब यह सुनिश्चित हो जाता है कि तालिका में कोई लापता टाइमस्टैम्प नहीं हैं (और ऐसी आदर्श स्थिति लगभग कभी सामने नहीं आती है), और पिछले टाइमस्टैम्प की गणना केवल वांछित को घटाकर की जाती है वर्तमान से अंतराल (एक अद्वितीय सूचकांक का उपयोग करके, नमूना बहुत तेज है)। सामान्य स्थिति में, पिछले चरण में टाइमस्टैम्प के मूल्य की गणना एक उपश्रेणी द्वारा की जाती है, जो सख्त समानता पर आधारित नहीं है, लेकिन एक गैर-सख्त पर, छँटाई का उपयोग करते हुए, और इस तरह की क्वेरी, ज़ाहिर है, बहुत लंबे समय तक काम करती है। संबंधपरक संस्करण में, तेज डेटा पुनर्प्राप्ति के लिए एक उपश्रेणी को टी फ़ील्ड पर एक सूचकांक की आवश्यकता होती है, और एक बाहरी क्वेरी dst फ़ील्ड के साथ काम करती है, इसलिए, इस मामले में, तालिका ( t , dst ) फ़ील्ड पर एक समग्र सूचकांक t_dst है ।

संबंधपरक डेटाबेस मॉडल के भीतर क्वेरी विकल्प:

 SELECT MAX(t1.dst-(SELECT dst FROM t t2 WHERE t2.t<t1.t ORDER BY t2.t DESC LIMIT 1)) FROM t t1;

जैसा कि आप क्वेरी से देख सकते हैं, आपको तालिका n + 1 के माध्यम से आना होगा। उपयोगकर्ता चर आपको तालिका के माध्यम से एक पास में ऐसी क्वेरी को लागू करने की अनुमति देते हैं। एक क्वेरी के लिए जो तालिका के माध्यम से एक पास को सही ढंग से काम करने के लिए करता है, यह आवश्यक है कि डेटा समय के क्रम के आरोही क्रम में चुना गया हो।
यह निम्नलिखित तरीकों से प्राप्त किया जा सकता है।

तालिका निष्पादित करने से पहले तालिका में डेटा (यदि आवश्यक हो) को ALTER TABLE `t` ORDER BY `t` ( MyISAM प्रकार के टेबल के लिए, एक काम कर समाधान)। यदि डेटा क्रमिक रूप से डाला गया था, लेकिन कई रिकॉर्ड तालिका से हटा दिए गए थे, तो आपको OPTIMIZE TABLE `t` साथ तालिका को डीफ़्रैग्मेन्ट करना OPTIMIZE TABLE `t` । चूंकि डेटा पहले से सॉर्ट किया जाता है, इसलिए IGNORE INDEX(t_dst) निर्देश का उपयोग क्वेरी में किया जाता है ताकि डेटा इंडेक्स द्वारा चयनित न हो, लेकिन क्रमिक रूप से।
चयन योग्य पर एक समग्र सूचकांक की उपस्थिति ( WHERE / ORDER BY / GROUP BY में प्रयुक्त) फ़ील्ड आपको इस पर डेटा (इंडेक्स द्वारा) का चयन करने की अनुमति देता है - अर्थात, चयनित फ़ील्ड के आरोही क्रम में। इस स्थिति में, फ़ील्ड ( t , dst ) पर एक समग्र सूचकांक t_dst की उपस्थिति, t चिह्न के आरोही क्रम में डेटा नमूना प्रदान करती है। ऑप्टिमाइज़र के लिए, जब सूचकांक का उपयोग किया जाता है, तो FORCE INDEX(t_dst) स्टेटमेंट लागू किया जाना चाहिए।
तालिका t से डेटा का चयन करने के लिए नहीं, बल्कि उससे बनाए गए एक गतिशील चयन से, आरोही फ़ील्ड t (क्रमबद्ध SELECT * FROM `t` ORDER BY `t` ) द्वारा सॉर्ट किया गया।

एक पास में डेटा का चयन करने की क्षमता इस तथ्य पर आधारित है कि तालिका के पिछली पंक्ति पर उपयोगकर्ता चर में dst स्तंभ का मूल्य याद रखना संभव है और तालिका की वर्तमान पंक्ति पर dst मान के साथ इसकी तुलना करें।

एक क्वेरी जो तालिका के साथ काम करती है जहां डेटा सही क्रम में है।

 SET @data=NULL, @next_data=NULL, @max_value=0; SELECT @next_data:=`dst` `nextdata`,IF (@next_data- @data>@max_value, @max_value:=@next_data-@data, @max_value), @data:=dst FROM `t` IGNORE INDEX(t_dst); SELECT @max_value;

एक क्वेरी जो सूचकांक द्वारा वांछित क्रम में डेटा का चयन करती है:

 SET @data=NULL, @next_data=NULL, @max_value=0; SELECT @next_data:=`dst` `nextdata`,IF (@next_data- @data>@max_value, @max_value:=@next_data-@data, @max_value), @data:=dst FROM `t` FORCE INDEX(t_dst); SELECT @max_value;

एक क्वेरी जो भ्रूण प्रक्रिया के दौरान डेटा को सॉर्ट करती है:

 SET @data=NULL, @next_data=NULL, @max_value=0; SELECT @next_data:=`dst` `nextdata`, IF (@next_data - @data > @max_value, @max_value:=@next_data-@data, @max_value), @data:=dst FROM (SELECT * FROM `t` ORDER BY t) t1; SELECT @max_value;

तालिका 1 क्वेरी निष्पादन की अवधि (तालिका में पंक्तियों की एक अलग संख्या के साथ) दिखाती है। तालिका में पंक्तियों की संख्या अंतराल की लंबाई से निर्धारित होती है। इस मामले में, एक मिनट के अंतराल पर विचार किया जाता है।

अवधि	पंक्ति की गिनती	संबंधपरक क्वेरी विकल्प	सॉफ्टवेयर के साथ अनुरोध (अनुक्रमिक नमूनाकरण)	सॉफ्टवेयर के साथ अनुरोध (प्रगति में छंटनी)	सॉफ्टवेयर के साथ अनुरोध (सूचकांक द्वारा नमूनाकरण के द्वारा छंटनी की जाती है)
1 दिन	1,440	2.67 सेकंड	0.00 सेकंड	0.00 सेकंड	0.00 सेकंड
1 सप्ताह	10,080	2 मिनट 11 सेकंड	0.00 सेकंड	0.01 सेकंड	0.02 सेकंड
1 महीना	43,200	40 मिनट 1 सेकंड	0.02 सेकंड	0.04 सेकंड	0.07 सेकंड
1 साल	525.600	तीन दिन से ज्यादा	0.54 सेकंड	0.68 सेकंड	0.94 सेकंड

तालिका 1 उदाहरण संख्या 1 से प्रश्नों की अवधि का मापन।

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

उदाहरण संख्या 2। चैनल लोड का विश्लेषण करने के लिए, आपको नियमित रूप से निश्चित समय अवधि (कई सेकंड, कई मिनट) के लिए लोड का मूल्यांकन करना होगा। इस मामले में, 5 सेकंड की अवधि के साथ एक अंतराल चुना जाता है। जिस तालिका से डेटा चुना गया है, उसकी संरचना उदाहरण संख्या 1 में समान है, टाइमस्टैम्प टी प्रकार TARERAMP का होना चाहिए।

एक संबंधपरक क्वेरी समूह के माध्यम से इस तरह के चयन को लागू करती है, जिसके बाद परिणाम छाँटते हैं:

 SELECT `t`-INTERVAL(UNIX_TIMESTAMP(`t`)%5) SECOND AS s5,SUM(`dst`) AS `d` FROM `t` GROUP BY s5 ORDER BY `t

तालिका के माध्यम से एक पूर्ण पास के अलावा, यह क्वेरी परिणाम के समूहीकरण और छंटाई को भी लागू करती है।
स्पष्ट समूह की कमी के कारण कम भार वाले उपयोगकर्ता चर के माध्यम से एक ही क्वेरी को लागू किया जा सकता है, और इसलिए, छंटाई (बशर्ते कि तालिका में लापता टाइमस्टैम्प न हों)।

एक क्वेरी जो तालिका के साथ काम करती है जहां डेटा सही क्रम में है:

 SET @c:=0; SELECT `t`, `dst` FROM ( SELECT `t`, IF(UNIX_TIMESTAMP(`t`) % 5, @c := @c+`dst`, @c := `dst`) AS `dst`, IF ((UNIX_TIMESTAMP(`t`) + 1) % 5, 1,0) AS `mark` FROM `t` IGNORE INDEX(`t_dst`)) t1 WHERE mark = 0;

एक क्वेरी जो सूचकांक द्वारा वांछित क्रम में डेटा का चयन करती है:

 SET @c:=0; SELECT `t`, `dst` FROM ( SELECT `t`, IF(UNIX_TIMESTAMP(`t`) % 5, @c := @c+`dst`, @c := `dst`) AS `dst`, IF ((UNIX_TIMESTAMP(`t`) + 1) % 5, 1,0) AS `mark` FROM `t` FORCE INDEX(`t_dst`)) t1 WHERE mark = 0;

एक क्वेरी जो भ्रूण प्रक्रिया के दौरान डेटा को सॉर्ट करती है:

 SET @c:=0; SELECT `t`, `dst` FROM ( SELECT `t`, IF(UNIX_TIMESTAMP(`t`) % 5, @c := @c+`dst`, @c := `dst`) AS `dst`, IF ((UNIX_TIMESTAMP(`t`) + 1) % 5, 1,0) AS `mark` FROM ( SELECT * FROM `t` ORDER BY `t`) t1) t2 WHERE mark = 0;

उप- भाग के हिस्से के रूप में, पहले @ @ चर में योग किया जाता है, और अगर टाइमस्टैम्प पांच-सेकंड के अंतराल से मेल खाती है, तो काउंटर शून्य पर रीसेट हो जाता है और चिह्न को मान असाइन किया जाता है। अगला, बाहरी क्वेरी पंक्तियों को चिह्नित करती है जिसके लिए चिह्न 1, अर्थात वांछित है।
तालिका 2 क्वेरी निष्पादन की अवधि (तालिका में पंक्तियों की एक अलग संख्या के साथ) दिखाती है।

अवधि	पंक्ति की गिनती	संबंधपरक क्वेरी विकल्प	सॉफ्टवेयर के साथ अनुरोध (अनुक्रमिक नमूनाकरण)	सॉफ्टवेयर के साथ अनुरोध (प्रगति में छंटनी)	सॉफ्टवेयर के साथ अनुरोध (सूचकांक द्वारा नमूनाकरण के द्वारा छंटनी की जाती है)
1 दिन	86,400	0.11 सेकंड	0.05 सेकंड	0.1 सेकंड	0.08 सेकंड
1 सप्ताह	604.800	0.86 सेकंड	0.33 सेकंड	0.73 सेकंड	0.6 सेकंड
1 महीना	2,592,000	33.72 सेकंड	१.६५ सेकेंड	3.62 सेकंड	२. .३ सेक
सीजन 1 (3 महीने)	7,776,000	2 मिनट 45 सेकंड	4.87 सेकंड	10.46 सेकंड	8.36 सेकंड

तालिका 2 उदाहरण संख्या 2 से प्रश्नों की अवधि का मापन

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

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

org_id
(संगठन आईडी)

ORG_NAME
(संगठन का नाम)

जाल
(नेटवर्क पता, 4 बाइट्स)

जाल
(नेटमास्क, 4 बाइट्स)

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

संबंधपरक मॉडल में क्वेरी विकल्प:

 SELECT t1.org_id, t1.org_name, (SELECT count(*) FROM `nets` t2 WHERE t2.org_id = t1.org_id AND ((t2.mask<<32)+t2.net) < ((t1.mask<<32)+t1.net) ) c, inet_ntoa(t1.net), inet_ntoa(t1.mask) FROM nets t1 HAVING c<2 ORDER BY org_id, mask;

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

कस्टम MySQL चर के साथ क्वेरी विकल्प:

 set @i=0, @p=0; SELECT t.org_id, t.org_name, inet_ntoa(t.net), inet_ntoa(t.mask) FROM ( SELECT * FROM `nets` ORDER BY `org_id`, `mask` ) AS t WHERE ( (if (@p=org_id, @i:=@i+1,(@i:=0) or (@p:=org_id))) and @i<2);

पीपी के साथ क्वेरी की अधिक दक्षता इस तथ्य से परिणाम देती है कि क्वेरी के संबंधपरक संस्करण में एक उपश्रेणी है जो तालिका में प्रत्येक रिकॉर्ड को संसाधित करता है।

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

तालिका की संरचना को बदलने के विकल्प पर विचार करें - प्रचलित कॉलम को जोड़ना। प्रारंभिक डेटा तालिका निम्नानुसार है:

टी (टाइमस्टैम्प)

डी (माप डेटा)

तालिका में अनुक्रमणिका और एक प्राथमिक कुंजी नहीं होती है - यदि फ़ील्ड t ( T, d ) पर फ़ील्ड t या एक संयुक्त अनुक्रमणिका पर अनुक्रमणिका है, तो रिलेशनल क्वेरी संस्करण बहुत धीमा काम करता है ।

डेटाबेस रिलेशनल मॉडल के ढांचे के भीतर संशोधन संस्करण:

 CREATE TABLE `t_modifyed` SELECT t, (SELECT `d` FROM `t` `in` where `in`.t < `out`.`t` order by t desc limit 1 ) prev, d FROM `t` `out` ORDER BY `t`;

कस्टम चर का उपयोग करके संशोधन विकल्प:

 set @v = NULL; CREATE TABLE `t_moifyed` SELECT t, @v prev, @v:=dd FROM `t` ORDER BY `t`;

तालिका 3 क्वेरी निष्पादन की अवधि (तालिका में पंक्तियों की एक अलग संख्या के साथ) दिखाती है।

अवधि	पंक्ति की गिनती	संबंधपरक क्वेरी विकल्प	सॉफ्टवेयर के साथ अनुरोध (अनुक्रमिक नमूनाकरण)	सॉफ्टवेयर के साथ अनुरोध (प्रगति में छंटनी)
1 दिन	1,440	0.35 सेकंड	0.00 सेकंड	0.00 सेकंड
1 सप्ताह	10,800	12.33 सेकेंड	0.00 सेकंड	0.01 सेकंड
1 महीना	43,200	3 मिनट 45 सेकंड	0.10 सेकंड	0.15 सेकंड
1 साल	525.600	5 घंटे से अधिक	0.54 सेकंड	0.96 सेकेंड

तालिका 3. उदाहरण संख्या 4 से क्वेरी निष्पादन की अवधि का मापन।
अन्य उपयोग। कई रिलेशनल डीबीएमएस आपको क्वेरी के निष्पादन के दौरान वर्तमान आउटपुट लाइन की संख्या का उपयोग करने की अनुमति देते हैं।

Oracle (चर: पंक्तिबद्ध)
Microsoft SQL (फ़ंक्शन ROW_NUMBER ())
PostgreSQL (फ़ंक्शन ROW_NUMBER ())

MySQL में, कस्टम चर का उपयोग करके केवल पंक्तिबद्ध रूप से सही ढंग से अनुकरण किया जा सकता है:

 set @n:=0; select @n:=@n+1 as rownum, t.* from t;

कड़ाई से बोलते हुए, MySQL में पंक्तिबद्ध का उपयोग करने की कोई आवश्यकता नहीं है: क्वेरी द्वारा लौटाए गए पंक्तियों की संख्या को सीमित करने के लिए, एक लिमिट स्टेटमेंट है, लेकिन कभी-कभी इसकी आवश्यकता हो सकती है।

निष्कर्ष। किसी विशेष प्रकार की क्वेरी में कस्टम चर का उपयोग करने से आप अपने रिलेशनल समकक्षों की तुलना में प्रश्नों को निष्पादित करते समय अधिक से अधिक गति प्राप्त कर सकते हैं। ऐसा हो सकता है:

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

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

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

 DROP FUNCTION IF EXISTS pg_temp.max_drop(); CREATE FUNCTION pg_temp.max_drop() returns int as $$ DECLARE prev INTEGER; curr INTEGER; max_ INTEGER; begin max_ := 0; prev := 0; FOR curr IN SELECT d FROM pogoda LOOP IF (curr - prev > max_) THEN max_ := curr - prev; END IF; prev := curr; END LOOP; RETURN max_; end $$ lANGUAGE plpgsql;

Microsoft SQL : MySQL सॉफ्टवेयर का उपयोग करके क्वेरी और क्वेरी के बीच मुख्य अंतर यह है कि चर को पहले से सेट किया जाना चाहिए।

 DECLARE @diff int; DECLARE @prev int; set @diff = 0; set @prev = 0; select @diff = case when @diff> (d-@prev) then @diff else (d-@prev) end, @prev = d from npogoda; select @diff;

Oracle : इस लेखन के समय, Oracle SQL में कस्टम चर का उपयोग करने का कोई तरीका नहीं है - केवल PL / SQL और SQLPLUS, Oracle XE और जैसे एक्सटेंशन। टी। डी।

धन्यवाद। लेखक निम्नलिखित लोगों को लेखन प्रक्रिया में अमूल्य मदद के लिए आभारी है:

कस्टम चर का उपयोग करके MySQL क्वेरी का अनुकूलन

More articles: