जटिल MySQL प्रश्नों का अनुकूलन

परिचय

MySQL एक बहुत विवादास्पद उत्पाद है। एक ओर, यह सरल ऑपरेशन / क्वेरी पर अन्य डेटाबेस पर एक अतुलनीय गति लाभ है। दूसरी ओर, इसमें ऐसा अविकसित (यदि अविकसित नहीं है) आशावादी है कि यह जटिल प्रश्नों पर एकमुश्त हार जाता है।

सबसे पहले, मैं अनुकूलन समस्याओं की सीमा को "विस्तृत" और बड़ी तालिकाओं तक सीमित करना चाहूंगा। मान लीजिए कि 10 मीटर तक के रिकॉर्ड और आकार में 20Gb तक, उनके साथ बड़ी संख्या में परिवर्तनशील अनुरोध हैं। यदि आपकी तालिका में कई लाखों रिकॉर्ड हैं, तो प्रत्येक 100 बाइट आकार में हैं, और इसके लिए पांच सरल संभव प्रश्न हैं, यह लेख आपके लिए नहीं है। NB: MySQL innodb / percona इंजन माना जाता है - इसके बाद बस MySQL।

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

हम बहुत छोटी तालिकाओं के लिए सबसे सरल मामलों को छोड़ देते हैं, जिसके लिए ऑप्टिमाइज़र अक्सर अनुक्रमणिका की उपलब्धता की परवाह किए बिना प्रकार = सभी (पूर्ण स्कैन) का उपयोग करता है - उदाहरण के लिए, 40 प्रविष्टियों वाला एक क्लासिफायरियर। MySQL में कई अनुक्रमित ( इंडेक्स मर्ज ) का उपयोग करने के लिए एक एल्गोरिथ्म है, लेकिन यह एल्गोरिथ्म बहुत बार काम नहीं करता है, और केवल क्रम के बिना। इंडेक्स मर्ज का उपयोग करने का प्रयास करने का एकमात्र समझदार तरीका OR के साथ विभिन्न स्तंभों को प्राप्त करना है।

एक और विषयांतर: यह समझा जाता है कि पाठक पहले से ही स्पष्टीकरण से परिचित है। अक्सर क्वेरी को ऑप्टिमाइज़र द्वारा थोड़ा संशोधित किया जाता है, इसलिए यह समझने के लिए कि यह या उस इंडेक्स का उपयोग क्यों किया गया था या नहीं, आपको कॉल करना चाहिए

explain extended select xxx; 

और फिर

 show warnings; 

जो आशावादी द्वारा संशोधित अनुरोध दिखाएगा।

कवरिंग इंडेक्स - मोटे टेबल्स से इंडेक्स तक

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

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

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

एक सूक्ष्म बिंदु जिसे आप कभी-कभी भूल जाते हैं वह यह है कि MySQL केवल क्लस्टर इंडेक्स बनाता है। क्लस्टर - वास्तव में, तालिका में रिकॉर्ड की पूर्ण स्थिति का संकेत नहीं है, लेकिन (सशर्त रूप से) प्राथमिक कुंजी का रिकॉर्ड, जो बदले में आपको वांछित रिकॉर्ड प्राप्त करने की अनुमति देता है। लेकिन MySQL, बिना किसी अन्य लुकअप के करने के लिए, बिना किसी अतिरिक्त जानकारी के, प्राथमिक कुंजी की चौड़ाई के लिए किसी भी कुंजी का विस्तार करता है। इस प्रकार, यदि आपके पास तालिका में प्राथमिक कुंजी (आईडी), कुंजी (ए, बी, सी) है, तो वास्तव में आपकी दूसरी कुंजी (ए, बी, सी) नहीं है , लेकिन (ए, बी, सी, आईडी) । इसलिए नैतिकता - वसा प्राथमिक कुंजी बुराई है।

यह अलग-अलग डेटाबेस में क्वेरी कैशिंग में अंतर पर ध्यान दिया जाना चाहिए। यदि PostgreSQL / Oracle caches क्वेरी प्लान्स (जैसे कि कुछ टाइमआउट के लिए तैयार है), तो MySQL सिर्फ़ क्वेरी STRING (पैरामीटर मान सहित) को कैश करता है और क्वेरी परिणाम को बचाता है। यही है, अगर आप लगातार चयन करते हैं

 select AAA from BBB where CCC=DDD 

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

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

1. सबसे पहले, खंड आदेश को देखें । उपयोग किए गए इंडेक्स को उसी कॉलम से शुरू करना चाहिए, जो उसी क्रम में या पूरी तरह से रिवर्स सॉर्टिंग में क्रम से उल्लिखित है। यदि सॉर्टिंग प्रत्यक्ष या रिवर्स नहीं है, तो इंडेक्स का उपयोग नहीं किया जा सकता है। एक बात है लेकिन ... MySQL अभी भी मिश्रित प्रकारों के साथ अनुक्रमणिकाओं का समर्थन नहीं करता है। सूचकांक हमेशा asc है । इसलिए यदि आपके पास A asc, B desc द्वारा ऑर्डर है - तो इंडेक्स के उपयोग को अलविदा कहें।
2. पुनर्प्राप्त किए गए कॉलम को कवरेज इंडेक्स में मौजूद होना चाहिए। सूचकांक की अंतहीन वृद्धि के कारण बहुत बार यह एक असंभव स्थिति है, जो कि आप जानते हैं, बुराई है। इसलिए, इस बिंदु के चारों ओर एक रास्ता है - सेल्फ ज्वाइन का उपयोग करना। यही है, पंक्ति चयन और डेटा निष्कर्षण में क्वेरी को विभाजित करना। सबसे पहले, दी गई स्थिति के अनुसार, हम केवल प्राथमिक कुंजी के कॉलम (जो हमेशा इंडेक्स क्लस्टर में मौजूद होते हैं) का चयन करते हैं, और दूसरी बात, प्राप्त परिणाम इसी प्राथमिक कुंजी का उपयोग करके सभी आवश्यक कॉलम के चयन में शामिल हो जाता है। इस प्रकार, हमारे पास पहले चयन में सूचकांक का उपयोग करके एक स्वच्छ और दूसरे चयन के लिए eq_ref (बहुवचन का सार) होगा। तो, हमें कुछ ऐसा ही मिलता है:
   

    select AAA,BBB,CCC,DDD from tableName as a join tableName as b using (PK) «where over table b» 

3. आगे जहां है यहां, सबसे खराब स्थिति में, हम पूरे सूचकांक ( टाइप = इंडेक्स ) पर पुनरावृति कर सकते हैं, लेकिन यदि संभव हो तो हमें उन कार्यों का उपयोग करने की कोशिश करनी चाहिए जो टाइप = रेंज ( >,> =, <, <=, जैसे "xxx%" और इससे आगे नहीं जाते हैं) । उपयोग किए गए सूचकांक में उन सभी क्षेत्रों को शामिल करना चाहिए जहां से सूचकांक का उपयोग करने के लिए संरक्षित किया जाता है । जैसा कि ऊपर उल्लेख किया गया है - आप index_merge का उपयोग करने की कोशिश कर सकते हैं - लेकिन अक्सर यह जटिल परिस्थितियों के साथ संभव नहीं है।
   

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

एक आवरण सूचकांक से मोटे क्षेत्रों का अलगाव - मोटी अनुक्रमित से पतले लोगों के लिए

लेकिन क्या होगा यदि हमारे पास कई प्रकार के प्रश्न हैं, या अलग-अलग छंटाई की आवश्यकता है और मोटे फ़ील्ड ( varchar ) का उपयोग करें? बस एक लाख रिकॉर्ड में varchar फ़ील्ड इंडेक्स साइज़ (100) की गिनती करें। और यदि इस क्षेत्र का उपयोग विभिन्न प्रकार के प्रश्नों में किया जाता है - जिसके लिए हमारे पास अलग-अलग कवरिंग इंडेक्स हैं? क्या अलग-अलग प्रश्नों में समान (या लगभग समान) प्रदर्शन को बनाए रखते हुए, इस मोटे क्षेत्र के लिए केवल एक इंडेक्स में मेमोरी होना संभव है? तो - अंतिम बिंदु।

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

    select A from tableName where A=1 or fatB='test' 

   कुंजी कुंजी (fatB, A) बनाने के बजाय हम एक पतली कुंजी (कुंजी) और एक मोटी कुंजी (fatB) बनाएंगे । और हम निम्नानुसार स्थिति को फिर से लिखते हैं।
   

    create temporary table tmp as select PK from tableName where fatB='test'; select A from tableName left join tmp using (PK) where A=1 or tmp.PK is not null; 

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

आत्म पार्सिंग असाइनमेंट

यह न्यूनतम संख्या की कुंजी बनाने के लिए आवश्यक है (स्मृति के संदर्भ में) और फ़ॉर्म के प्रश्नों को अनुकूलित करने के लिए:

 select A,B,C,D from tableName where A=1 and B=2 or C=3 and D like 'test%'; select A,C,D from tableName where B=3 or C=3 and D ='test' order by B; 

मान लीजिए कि प्रश्नों का प्रकार = श्रेणी के लिए पुनर्निर्धारण नहीं है।

प्रयुक्त साहित्य की सूची

1. उच्च प्रदर्शन MySQL, दूसरा संस्करण
   अनुकूलन, बैकअप, प्रतिकृति, और अधिक
   बैरन श्वार्ट्ज द्वारा, पीटर जेत्सेव, वादिम टकाचेंको, जेरेमी डी। ज़ॉवडनी, अरजेन लेंटेज़, डेरेक जे। बॉलिंग
   प्रकाशक: ओ'रिली मीडिया
   रिलीज़: जून 2008
   पेज: 712
2. www.mysqlperformanceblog.com