MySQL का उपयोग कर वेब प्रोजेक्ट ऑप्टिमाइज़ेशन रणनीति

परिचय

किसी भी बड़े वेब प्रोजेक्ट के जीवन में, विशेष रूप से PHP में, लेकिन, सामान्य तौर पर, यह वेब विकास के लिए उपयुक्त किसी भी सर्वर-साइड प्रोग्रामिंग भाषा पर लागू होता है, यह आमतौर पर समझ में आता है कि "आप इस तरह से नहीं रह सकते," और समय आ गया है जिसे आपको अनुकूलित करने की आवश्यकता है साइट का काम इतना है कि यह ब्रेक लगाना बंद कर देता है (कम से कम उत्पादन पर)।

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

यदि आप यह निर्धारित करने में सक्षम थे कि यह आपका मामला है और आपकी MySQL परियोजना है, तो यह लेख आपको ठोस उपाय करने और परिणाम को ठीक करने और DBMS के साथ स्पष्ट समस्याओं की घटना को रोकने के साथ स्थिति को सही करने में मदद कर सकता है।

अड़चनों की पहचान

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

क्या और कैसे मापना है

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

मान लेते हैं कि DBMS में बहुत सारे प्रश्न नहीं हैं, और यह अभी भी राक्षसी रूप से धीमा है, और यह बहुत स्पष्ट नहीं है कि वास्तव में इसका सबसे बड़ा प्रभाव क्या है।

मापने क्वेरी निष्पादन समय

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

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

चूंकि, अक्सर, देव आधार पर अनुरोधों का निष्पादन समय बहुत "उछल" होता है, धीमे अनुरोधों के लिए सीमा बहुत बड़े मानों पर सेट होती है (प्रोपेल के साथ प्रोपेल में, यह डिफ़ॉल्ट रूप से, 100 एमएस) या धीरे-धीरे निष्पादित होने वाले अनुरोधों पर ध्यान नहीं देते हैं। I / O को सब कुछ लिखना

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

प्रश्नों के लिए स्वचालित खोज

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

मैं एक सरल उदाहरण दूंगा (उदाहरण के लिए, एक तालिका में 100,000 रिकॉर्ड some_field के यादृच्छिक मूल्यों के साथ हैं):

प्लेट

CREATE TABLE some_table(id INT PRIMARY KEY AUTO_INCREMENT, some_field INT) 

हम एक सरल क्वेरी निष्पादित करते हैं जो 100,000 पंक्तियों को स्कैन करेगा और कुछ भी नहीं मिलेगा

 SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100 

हम MySQL 5.1.49 पर देखें

 EXPLAIN SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100 

EXPLAIN कहता है: नपलनिक के लिए, मुझे 100 लाइनों को देखने की जरूरत है, मैं प्राइमर इंडेक्स का उपयोग करूंगा, नपलनिक के लिए, सब कुछ मुझे अच्छी तरह से जागता है, मैं अपनी माँ की कसम खाता हूं!

हम एक सरल क्वेरी निष्पादित करते हैं जो 100 लाइनों (या थोड़ी अधिक) को स्कैन करेगा और उन्हें तुरंत वापस कर देगा

 SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100 

हम MySQL 5.0.51 पर देखें

 EXPLAIN SELECT * FROM some_table WHERE some_field <> 0 ORDER BY id LIMIT 100 

EXPLAIN कहता है: एक नपलनिक के लिए, मुझे १००,००० लाइनों को देखने की जरूरत है, मैं प्राथमिक सूचकांक का उपयोग करूँगा, एक लंबे समय तक जागेगा, नपलनिक के लिए! (हां, वही कहता है, मैं मंजिल देता हूं :))

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

हम पढ़ी गई पंक्तियों की संख्या गिनते हैं

यदि आपने सोचा था कि प्रदर्शन को मापने के लिए MySQL में सबसे उन्नत तरीका है, तो आप गलत हैं (इतनी देर पहले नहीं, मैंने भी ऐसा ही सोचा था :))।

वास्तव में, निम्नलिखित प्रश्नों का निष्पादन:

 SET query_cache_type = OFF; --  query cache ,   FLUSH STATUS; --   :) SELECT * FROM some_table WHERE some_field = 100500 ORDER BY id LIMIT 100; --   SHOW SESSION STATUS LIKE 'handler_%' --  ;)! 

यह निम्नलिखित छवि के समान कुछ का उत्पादन करेगा:



यह समझने के लिए कि MySQL में हैंडलर क्या है, आप पढ़ सकते हैं, उदाहरण के लिए, MySQL में HANDLER कमांड का वर्णन। संक्षेप में, हैंडलर विभिन्न प्रकार की तालिकाओं के लिए MySQL में पंक्तियों (और न केवल) को पढ़ने / लिखने के लिए एक सामान्य इंटरफ़ेस है। MySQL में संबंधित इंटरफ़ेस के लिए हैंडलर सॉकेट का नाम भी आपको संकेत देना चाहिए कि यह किस प्रकार का जानवर है।

इस प्रकार, पृष्ठ की शुरुआत में पहले 2 प्रश्नों को निष्पादित करके (क्वेरी कैश बंद करना और आँकड़े रीसेट करना), और पृष्ठ के अंतिम छोर पर - अंतिम क्वेरी, आप MySQL से पढ़ी / लिखी कुल पंक्तियों को प्राप्त कर सकते हैं। एक ऐसी परियोजना जिसके लिए मैं इसी तरह के अनुकूलन में लगा हुआ था, मैंने पृष्ठ पर अलर्ट () को देव संस्करण में जोड़ दिया था यदि पृष्ठ के परिणामस्वरूप 10,000 से अधिक लाइनें पढ़ी / लिखी गई थीं।

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

सर्वर द्वारा भेजी गई सूचना की मात्रा

सत्र स्थिति, वास्तव में, बहुत अधिक जानकारी शामिल है, उदाहरण के लिए, क्लाइंट और SQL सर्वर के बीच ट्रैफ़िक जानकारी (बाइट्स_%) - यदि आपके पास "विस्तृत" टेबल हैं, तो यह प्रासंगिक भी हो सकता है (विशेषकर ओआरएम के साथ काम करते समय,) जो तालिकाओं से सभी स्तंभों का चयन करना पसंद करते हैं, भले ही वे वहां आवश्यक न हों)

दरअसल, क्वेरी ऑप्टिमाइज़ेशन

अड़चनें पाए जाने के बाद, इन प्रश्नों का अनुकूलन शुरू करना आवश्यक है और / या साइट पर एक निश्चित ब्लॉक क्या करना चाहिए, इस पर अपने विचार पर पुनर्विचार करना चाहिए।

मुझे उम्मीद है कि इस विषय की जानकारी आपके लिए उपयोगी होगी :)। इस तकनीक का उपयोग करके, मैं बाधाओं की पहचान करने में सक्षम था और सिम्फनी साइट पर अधिकांश पृष्ठों के निष्पादन समय को ~ 1000 एमएस से 200-300 एमएस के बीच कहीं पर कम कर दिया और भविष्य में इसी तरह की समस्याओं को स्वचालित रूप से रोकने के लिए स्वचालित संस्करण में एक उपकरण जोड़ दिया। यह सब मुझे एक साइट के लिए ~ 20 एमबी सक्रिय "स्वयं" (कुल 100 एमबी के बारे में कोड) PHP कोड :) के बारे में 3 दिन लग गए। यह एक बहुत या थोड़ा है - अपने लिए सोचें :)