वेबसाइट डेटाबेस को गति दें

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

इस प्रकार, यदि आपकी साइट के पृष्ठ 1.5 सेकंड से अधिक समय में वापस हो जाते हैं (Google पर जिस सीमा पर साइट तेज़ मानी जाती है), तो आप अपने आगंतुकों को निराश कर सकते हैं, इसी तरह अगर खोज परिणामों के साथ आपके पेज को लोड होने में बहुत समय लगता है या, उदाहरण के लिए। उत्पाद विवरण पृष्ठ शीघ्रता से लोड होते हैं, लेकिन उपयोगकर्ता समीक्षा कुछ सेकंड के लिए लोड होती है।

Google द्वारा "तेज़" वेबसाइटों की पहचान करने के लिए निर्धारित सीमा ~ 1.5 सेकंड है। आप वेबमास्टर टूल्स ([डोमेन] → डायग्नोस्टिक्स → परफॉर्मेंस पर जाएं) के लिए समान शेड्यूल प्राप्त कर सकते हैं।

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

डेटाबेस क्या है? एसक्यूएल क्या है?

एक डेटाबेस सामान्य रूप से सूचनाओं के साथ तालिकाओं का एक सेट होता है, जैसे कि, उदाहरण के लिए, ग्राहकों की सूची और उनके आदेश। यह एक फ़ाइल कैबिनेट, स्प्रेडशीट का एक गुच्छा, एक माइक्रोसॉफ्ट एक्सेस फ़ाइल या अमेज़ॅन बुक और क्लाइंट डेटा के 40 टेराबाइट्स हो सकता है ।
एक सामान्य ब्लॉग डेटाबेस में उपयोगकर्ता, श्रेणी, पोस्ट और टिप्पणियों के बारे में जानकारी वाली तालिकाएँ होती हैं। वर्डप्रेस में शुरू में ये और कई अन्य टेबल हैं। ई-कॉमर्स सिस्टम के लिए डेटाबेस में "टोकरी" में निहित ग्राहकों, उत्पादों, श्रेणियों, आदेशों, उत्पादों के बारे में जानकारी के साथ टेबल हैं। खुले Magento के इंजन में ये और कई अन्य टेबल होते हैं। डेटाबेस में कई एप्लिकेशन हैं - सामग्री के प्रबंधन के लिए, ग्राहक संबंधों, खातों और चालानों, घटनाओं को संग्रहीत करने के लिए - ये दोनों प्रकार (ब्लॉग और ई-कॉमर्स साइटों के लिए प्रवेश) इस लेख में दिखाई देंगे।
डेटाबेस में कुछ तालिकाओं को अन्य तालिकाओं के साथ जोड़ा जाता है। उदाहरण के लिए, एक ब्लॉग पोस्ट में कई टिप्पणियां हो सकती हैं, एक ग्राहक कई आदेश दे सकता है (यह एक से कई संबंध हैं)। डेटाबेस में रिश्ते का सबसे जटिल रूप कई-से-कई रिश्ते हैं। इस तरह का संबंध ई-कॉमर्स सिस्टम के डेटाबेस का मूल है: एक ऑर्डर में अलग-अलग उत्पाद हो सकते हैं, जैसे किसी भी उत्पाद को अलग-अलग ऑर्डर में शामिल किया जा सकता है। यह वह जगह है जहां तालिका "ऑर्डर सामग्री" दिखाई देती है, जो ऑर्डर और सामान के बीच "स्थित" है, और यह हर बार दर्ज किया जाता है जब उपयोगकर्ता ऑर्डर में सामान जोड़ता है। यह कितना महत्वपूर्ण है, हम बाद में समझेंगे, जब हम विचार करते हैं कि कुछ प्रश्नों को पूरा होने में लंबा समय क्यों लगता है।
एक डेटाबेस सॉफ्टवेयर को भी संदर्भित करता है जिसमें यह सारा डेटा होता है। सॉफ्टवेयर का अर्थ है जब वे कहते हैं, "जबकि मैंने आज नाश्ता किया, मेरा डेटाबेस" गिर गया "" या "मुझे डेटाबेस को अपडेट करने की आवश्यकता है।" ( "यह सबसे अधिक संभावना है कि DBMS का उल्लेख है, डेटाबेस का नहीं", अनुवादक ने कहा (:) । सामान्य प्रणालियां Microsoft Access 2010, Microsoft SQL Server, MySQL, PostgreSQL और Oracle Database 11g हैं।
SQL संक्षिप्त नाम अक्सर उल्लेख किया जाता है जब यह डेटाबेस की बात आती है। यह "संरचित क्वेरी भाषा" (संरचित क्वेरी भाषा) वाक्यांश से आता है और इसे "Es-Kyu-El" ( "YaZZ, अगर इसे USSR में विकसित किया गया था " उच्चारण किया गया है, "अनुवादक ने कहा (:) । यह भाषा हमें" संवाद "करने के लिए है। डेटाबेस के साथ एक अद्भुत तरीके से:

SELECT lastname FROM customers WHERE city='Brighton'; 

इस कोड को एक अनुरोध कहा जाता है। डेटाबेस के साथ काम करने के लिए अन्य अभिव्यक्तियाँ भी हैं, जैसे कि INSERT (डेटा जोड़ने के लिए), अद्यतन ( अद्यतन करने के लिए), DELETE (हटाने के लिए), सृजन तालिका (तालिकाएँ बनाने के लिए), अन्य तालिका और कई अन्य।

डेटाबेस किसी साइट को धीमा कैसे कर सकता है?

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

1. उस उत्पाद को पहचानें जिसके साथ हम "काम" करेंगे।
2. निर्धारित करें कि पिछली बार यह उत्पाद ग्राहकों द्वारा "टोकरी" (तालिका "ऑर्डर सामग्री" ऊपर) में कितनी बार जोड़ा गया था।
3. उन उत्पादों की एक सूची प्राप्त करें जिन्हें "टोकरी" में एक ही क्रम (केवल पुष्टि किए गए आदेश) के साथ जोड़ा गया था।
4. उन खरीदारों को चुनें जिन्होंने ये ऑर्डर किए थे।
5. ऊपर दिए गए आइटम से उपयोगकर्ताओं द्वारा किए गए आदेशों का चयन करें।
6. उनके आदेशों की सामग्री को देखें (एक ही तालिका "आदेश सामग्री")।
7. इन उत्पादों के बारे में जानकारी प्राप्त करें।
8. उन उत्पादों को पहचानें जो इन आदेशों में अधिक सामान्य हैं, और इस सूची को प्रदर्शित करते हैं।

आप एक जटिल क्वेरी में सब कुछ फिट कर सकते हैं या कई सरल लोगों में विभाजित हो सकते हैं। किसी भी स्थिति में, यह बहुत जल्दी पूरा हो सकता है यदि आपके डेटाबेस में 20 उत्पाद, 12 ग्राहक, 18 ऑर्डर और 67 खरीदे गए सामान ("बास्केट" में उत्पादों की कुल संख्या) हो। लेकिन अगर सब कुछ अक्षम रूप से किया जाता है, तो बड़ी मात्रा में डेटा पर इस तरह के ऑपरेशन का निष्पादन बहुत धीमा हो जाएगा। उदाहरण के लिए, 500 उत्पादों, 10,000 ग्राहकों, 14,000 ऑर्डर और 100,000 खरीदे गए सामानों को संसाधित करना पृष्ठ लोडिंग को धीमा कर देगा।
यह एक बहुत ही जटिल उदाहरण है, लेकिन यह कल्पना करना संभव बनाता है कि "पर्दे के पीछे" क्या हो रहा है, और कार्यक्षमता का एक हानिरहित तत्व साइट पर "डाला" क्यों जा सकता है।
साइट की मंदी अन्य कारणों से हो सकती है: सर्वर अपर्याप्त मेमोरी या डिस्क स्थान के साथ चल रहा है; इस सर्वर पर एक और साइट बहुत सारे संसाधनों की खपत करती है; सर्वर ईमेल कर रहा है या किसी अन्य "भारी" कार्य के साथ व्यस्त है; सॉफ्टवेयर या हार्डवेयर त्रुटियाँ; गलत कॉन्फ़िगरेशन। शायद लोकप्रियता और, परिणामस्वरूप, साइट पर यातायात अचानक बढ़ गया? अगले दो खंडों में, हम काम की गति को और अधिक विस्तार से मानते हैं।

डेटाबेस में मामला?

वर्तमान में, आपकी साइट की गति का विश्लेषण करने के कई तरीके हैं, जिनमें फ़ायरफ़ॉक्स के लिए फ़ायरबग प्लगइन , Google Chrome में डेवलपर्स के लिए टूल (Shift + Ctrl + I, इसके बाद संसाधन → संसाधन ट्रैकिंग सक्षम करना), याहू YSlow शामिल हैं । WebPagetest जैसी विशेष साइटें भी हैं, जहां आप URL दर्ज करते हैं और यह निर्दिष्ट स्थान से गति को मापेगा ।
ये उपकरण पृष्ठ पर उपलब्ध सभी संसाधनों (HTML, छवियाँ, CSS और जावास्क्रिप्ट फ़ाइलों) का आरेख प्रदर्शित करते हैं, जिनमें से प्रत्येक के लोडिंग समय का संकेत मिलता है। ये उपकरण निर्धारित समय को भी निर्धारित करेंगे:

• DNS क्वेरी (आईपी पते के अनुवाद के लिए डोमेन नाम),
• सर्वर से कनेक्शन
• सर्वर से प्रतिक्रिया की प्रतीक्षा की जा रही है ("टाइम टू फर्स्ट बाइट" उर्फ ​​TTFB - टाइम टू फर्स्ट बाइट (टिप्पणी अनुवादक)),
• सर्वर से डेटा प्राप्त करना (डाउनलोड करना)।

कई वेब पेज पूरी तरह से सर्वर पर इकट्ठे होते हैं, जिसमें PHP भी शामिल है, जो डेटाबेस तक पहुंचता है, फिर सब कुछ तुरंत ब्राउज़र को भेजता है, इसलिए किसी भी डेटाबेस में देरी होने से लंबे समय तक इंतजार करना होगा, और डेटा रिसेप्शन / डाउनलोडिंग समय उनकी मात्रा के लिए आनुपातिक होगा। । इस प्रकार, एक तेज़ कनेक्शन के साथ, एक 20 Kb वेब पेज जो 5 सेकंड में बनता है (हालांकि 0.05 सेकंड में लोड हो रहा है) सर्वर पर एक बड़ी देरी पैदा करेगा।
लेकिन फिर भी, सभी पृष्ठ नहीं हैं। PHP फ़ंक्शन फ्लश () ब्राउज़र में पहले से ही उत्पन्न HTML डेटा भेजता है। कोई और देरी पहले से ही इस डेटा के लोडिंग से जुड़ी होगी, न कि उनकी अपेक्षा के साथ।
किसी भी स्थिति में, आप एक ही समय पर समान सर्वर पर समान आकार के HTML पृष्ठ (या छवि, या अन्य स्थिर तत्व) के लिए प्रतीक्षा समय के साथ कथित तौर पर धीमी और जटिल वेब पेजों के लिए प्रतीक्षा / लोड समय की तुलना कर सकते हैं। यह संभवतः एक धीमे इंटरनेट कनेक्शन, या सर्वर लोड (इन दोनों विकल्पों में देरी का कारण होगा) के प्रभाव को समाप्त करता है और आपको पृष्ठों के निर्माण के समय की इन अवधियों की तुलना करने की अनुमति देता है। यह, निश्चित रूप से, एक सटीक विज्ञान नहीं है, फिर भी यह कुछ विचार देगा कि कहां धीमा हो जाता है।
नीचे दिए गए स्क्रीनशॉट Google Chrome वेब पृष्ठों और उसी आकार की छवियों में डेवलपर टूल द्वारा विश्लेषण के परिणाम दिखाते हैं - 20 Kb। वेब पेज के लिए प्रतीक्षा समय 130 एमएस था, डाउनलोड समय 22 एमएस था। छवि का समय क्रमशः 51 एमएस और 11 एमएस है। उनका डाउनलोड समय लगभग समान है, लेकिन सर्वर ने वेब पेज को संसाधित करने और उत्पन्न करने के लिए अतिरिक्त 80 एमएस लिया, जो पीएचपी कोड के निष्पादन और डेटाबेस के साथ बातचीत का एक परिणाम है।
स्थैतिक सामग्री का विश्लेषण करते समय इन परीक्षणों को निष्पादित करते हुए, पृष्ठ को ताज़ा करें ताकि इसके कैश्ड संस्करण को प्राप्त न करें। इसके अलावा, यह सुनिश्चित करने के लिए कि आपको सांख्यिकीय विचलन का सामना नहीं करना है, कई बार परीक्षण चलाएं। नीचे दिए गए तीसरे स्क्रीनशॉट से पता चलता है कि वेबपैगैस्ट एक ही समय में, एक ही पृष्ठ पर, Google से एक टूल के रूप में लगभग दो बार दिखाता है। इससे पता चलता है कि परीक्षण करते समय आपको किसी एक उपकरण का उपयोग करने की आवश्यकता है।

Google Chrome टूलकिट का उपयोग करते हुए, वेब पेज लोड करते समय हमें 130 ms मिलते हैं

वही उपकरण। समान आकार की छवियों को लोड करते समय हमारे पास 51 एमएस हैं

WebPagetest के साथ एक ही पृष्ठ का विश्लेषण करते हुए, हमें प्रतीक्षा करने के लिए 296 एमएस और कुल डाउनलोड समय 417 एमएस मिलता है

MySQL / PHP में मापने के प्रश्न?

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

 function StartTimer ($what='') { global $MYTIMER; $MYTIMER=0; //global variable to store time //if ($_SERVER['REMOTE_ADDR'] != '127.0.0.1') return; //only show for my IP address echo '<p style="border:1px solid black; color: black; background: yellow;">'; echo "About to run <i>$what</i>. "; flush(); //output this to the browser //$MYTIMER = microtime (true); //in PHP5 you need only this line to get the time list ($usec, $sec) = explode (' ', microtime()); $MYTIMER = ((float) $usec + (float) $sec); //set the timer } function StopTimer() { global $MYTIMER; if (!$MYTIMER) return; //no timer has been started list ($usec, $sec) = explode (' ', microtime()); //get the current time $MYTIMER = ((float) $usec + (float) $sec) - $MYTIMER; //the time taken in milliseconds echo 'Took ' . number_format ($MYTIMER, 4) . ' seconds.</p>'; flush(); } 

StartTimer एक टाइमर शुरू करता है, और आपके द्वारा मापी गई हर चीज को प्रदर्शित भी करता है। दूसरी पंक्ति आपके आईपी पते की जांच कर रही है। यह उपयोगी हो सकता है यदि आप (अस्थायी रूप से) किसी कार्य स्थल पर माप लेते हैं और नहीं चाहते हैं कि हर कोई ऐसे आंकड़े देख सके। प्रारंभिक // को हटाकर लाइन को अपकेंद्रित करें और अपने आईपी पते के साथ 127.0.0.1 को बदलें। StopTimer टाइमर को रोकता है और बीता हुआ समय प्रदर्शित करता है।
अधिकांश आधुनिक साइटों (विशेष रूप से अच्छी तरह से निर्मित ओपन सोर्स प्रोजेक्ट) में कई PHP फाइलें होती हैं, लेकिन डेटाबेस क्वेरीज़ उनमें से कुछ में ही निष्पादित होती हैं। इन फ़ाइलों में mysql_db_query या mysql_query रेखाएँ देखें। कई सॉफ्टवेयर विकास, जैसे कि BBEdit, में ऐसी विशेषताएं हैं जो आपको इस तरह की खोज करने की अनुमति देती हैं। यदि आप लिनक्स कंसोल से परिचित हैं, तो निम्न कमांड का प्रयास करें:

 grep mysql_query `find . -name \*php` 

परिणाम कुछ इस प्रकार है:

 mysql_query ($sql); 

वर्डप्रेस 3.0.4 के लिए, यह wp- शामिल / wp-db.php फ़ाइल की लाइन 1112 होगी। आप फ़ाइल के प्रारंभ में (या प्रत्येक पृष्ठ से कनेक्ट होने वाली फ़ाइल के लिए) ऊपर वर्णित कार्यों को कॉपी कर सकते हैं, और फिर इसे प्राप्त करने के लिए mysql_query लाइन से पहले और बाद में StartTimer और StopTimer फ़ंक्शन जोड़ सकते हैं:

 StartTimer ($query); $this->result = @mysql_query( $query, $dbh ); StopTimer(); 

नीचे दिया गया स्क्रीनशॉट वर्डप्रेस इंस्टॉल करने के तुरंत बाद हमारे कोड को जोड़ने का परिणाम दिखाता है। कुल में, 15 अनुरोध संसाधित किए जाते हैं, प्रत्येक में 0.0003 सेकंड लगते हैं। (0.3 एमएस), जो एक खाली डेटाबेस से अपेक्षित है।

सभी वर्डप्रेस अनुरोधों को यहां प्रदर्शित और मापा गया है।

यदि आपको अन्य व्यापक रूप से उपयोग किए जाने वाले सिस्टम में यह रेखा मिली है, तो कृपया इस लेख में एक टिप्पणी जोड़कर इस जानकारी को साझा करें।
आप अन्य रोचक चीजें कर सकते हैं: आप देख सकते हैं कि आपका कंप्यूटर मेरी तुलना में कितना तेज है। "काउंटडाउन" से 1.000.000 तक मेरे कंप्यूटर पर 2.9420 सेकंड लगते हैं, मेरा सर्वर थोड़ा तेज है - 2.0726 सेकंड।

 StartTimer ('counting to 10000000'); for ($i=0; $i<10000000; $i++); //count to a high number StopTimer(); 

परिणामों के बारे में कुछ।

यह विधि केवल तुलनात्मक परिणाम देती है। यदि आपका सर्वर उस समय व्यस्त था, तो सभी अनुरोध सामान्य से कम थे। लेकिन उन्हें कम से कम यह निर्धारित करने में सक्षम होना चाहिए कि "फास्ट" अनुरोध (संभवतः 1-5 एमएस) को कितनी देर तक निष्पादित किया जा रहा है, धीमी (200 एमएस से अधिक) और सबसे "भारी" (1 सेकंड से अधिक)। आप यह परीक्षण घंटे या दिन के दौरान कई बार चला सकते हैं (लेकिन पिछले परीक्षण के तुरंत बाद नहीं - डेटाबेस कैश पर अनुभाग देखें) यह सुनिश्चित करने के लिए कि यह एक अस्थायी नहीं है।
यह वेब पेज के डिज़ाइन को गंभीरता से खराब करने की भी संभावना है। PHP से विभिन्न चेतावनियाँ जैसे "हेडर सूचना को संशोधित नहीं कर सकता है। पहले से ही भेजे गए हेडर ... "। इसका कारण यह है कि माप संदेश कुकी और सत्र शीर्षकों से आगे हैं। यदि पृष्ठ की मुख्य सामग्री प्रदर्शित होती है, तो आप इन संदेशों को अनदेखा कर सकते हैं। यदि पृष्ठ रिक्त है, तो आपको कोड के विशिष्ट ब्लॉकों के आसपास StartTimer और StopTimer फ़ंक्शन घोषित करने की आवश्यकता हो सकती है, न कि mysql_query के आसपास।
यह विधि किसी न किसी परिणाम प्राप्त करने के लिए एक बहुत तेज़ तरीका है; आपको इसे एक काम करने वाली वेबसाइट पर नहीं छोड़ना चाहिए।

और क्या कारण हो सकता है?

यदि डेटाबेस क्वेरीज़ इतनी धीमी नहीं है, लेकिन पेज अभी भी लंबे समय के लिए बना है, तो इसका कारण सबसे खराब रूप से लिखित कोड है। आप यह देखने के लिए कि क्या देरी हो सकती है, कोड के बड़े वर्गों के आसपास टाइमर फ़ंक्शन जोड़ सकते हैं? शायद इसका कारण यह है कि आप सूचनाओं की 10,000 लाइनों से गुजरते हैं, भले ही आप केवल 20 वस्तुओं को प्रदर्शित करते हों?

रूपरेखा

यदि आप अभी भी भ्रमित हैं और / या अपने कोड में क्या चल रहा है, इसके बारे में अधिक विस्तृत जानकारी प्राप्त करना चाहते हैं, तो विभिन्न डिबगिंग और प्रोफाइलिंग टूल, जैसे कि Xdebug का प्रयास करें , जो साइट की एक स्थानीय प्रतिलिपि का विश्लेषण करता है। यह नेत्रहीन भी सभी बाधाओं को प्रदर्शित कर सकता है।

अनुक्रमण सारणी

उपरोक्त प्रयोग ने आपको यह दिखा कर आश्चर्यचकित कर दिया होगा कि आपकी साइट पर पृष्ठ डेटाबेस में कितने प्रश्न हैं, और मुझे आशा है कि यह आपको धीमी क्वेरी की पहचान करने में मदद करेगा।
इस प्रक्रिया को गति देने के लिए, आइए अब कुछ सरल संवर्द्धन देखें। ऐसा करने के लिए, आपको किसी तरह सीधे डेटाबेस को अनुरोध भेजने की आवश्यकता है। कई सर्वर प्रशासन पैकेज (जैसे CPanel या Plesk) समान कार्य करने के लिए PhpMyAdmin के साथ आते हैं। इसके अलावा, आप phpMiniAdmin जैसे कुछ को साइट पर अपलोड कर सकते हैं, बस एक PHP फ़ाइल जो आपको डेटाबेस को देखने और प्रश्नों को निष्पादित करने की अनुमति देती है। आपको डेटाबेस का नाम, उपयोगकर्ता नाम और पासवर्ड दर्ज करना होगा। यदि आप उन्हें नहीं जानते हैं, तो आप उन्हें आसानी से अपनी साइट की कॉन्फ़िगरेशन फ़ाइल में पा सकते हैं, यदि कोई हो (उदाहरण के लिए वर्डप्रेस, यह WP-config.php है)।
साइट के पृष्ठों वाले डेटाबेस क्वेरीज़ में, आपने शायद WHERE के साथ स्थितियां देखीं। यह SQL का उपयोग करके परिणामों को फ़िल्टर करने का एक तरीका है। उदाहरण के लिए, यदि आप किसी साइट पर "खरीद इतिहास" पृष्ठ देख रहे हैं, तो संभवतः आदेशों को पहचानने वाले प्रश्न हैं। कुछ इस तरह:

 SELECT * FROM orders WHERE customerid = 2; 

यह अनुरोध क्लाइंट द्वारा आईडी 2 के साथ रखे गए सभी आदेशों को पुनर्प्राप्त करता है। मेरे कंप्यूटर पर 100,000 ऑर्डर रिकॉर्ड के साथ, यह 0.2158 सेकंड लेता है।
ग्राहक के रूप में कॉलम, जिनमें कई संभावित मान हैं, जिनका उपयोग WHERE के साथ स्थितियों में किया जाता है, = या < , या > के साथ संयोजन में अनुक्रमित किया जाना चाहिए। यह पुस्तक के अंत में सामग्री की तरह है: यह डेटाबेस को अनुक्रमित डेटा को जल्दी से पुनर्प्राप्त करने में मदद करता है। यह डेटाबेस क्वेरी को तेज करने के सबसे तेज़ तरीकों में से एक है।

क्या सूचकांक?

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

 SELECT * FROM categories WHERE name = 'Books'; SELECT * FROM events WHERE startdate >= '2011-02-07'; 

डेटाबेस टेबल में से प्रत्येक में एक पहचानकर्ता कॉलम होना चाहिए (आमतौर पर आईडी , लेकिन कभी-कभी एक आईडी या ArticleID , आदि), प्राथमिक कुंजी के रूप में इंगित किया गया है, जैसा कि नीचे wp_posts तालिका के स्क्रीनशॉट में है। इन प्राथमिक कुंजियों को स्वचालित रूप से अनुक्रमित किया जाता है। लेकिन आपको उन स्तंभों को भी अनुक्रमणित करना होगा जो अन्य तालिकाओं में पहचानकर्ताओं को संदर्भित करते हैं, जैसे ऊपर उदाहरण में CustomerID । इस मामले में, वे विदेशी कुंजी होंगे ।

 SELECT * FROM orders WHERE customerid = 2; SELECT * FROM orderitems WHERE orderid = 231; 

यदि आपको बड़ी मात्रा में पाठ डेटा की खोज करने की आवश्यकता है, उदाहरण के लिए, माल का विवरण या लेखों की सामग्री, तो आप एक अन्य प्रकार का सूचकांक जोड़ सकते हैं - पूर्ण पाठ । FULL TEXT प्रकार के अनुक्रमित का उपयोग करने वाली क्वेरी में कई कॉलम हो सकते हैं और शुरू में 4 वर्णों से अधिक लंबे शब्दों को समायोजित करने के लिए कॉन्फ़िगर किए गए हैं। 50% से अधिक अनुक्रमित प्रविष्टियों में पाए जाने वाले शब्दों और शब्दों को भी बाहर रखा गया है। हालाँकि, इस प्रकार के सूचकांक का उपयोग करने के लिए, आपको SQL क्वेरी को संशोधित करना होगा। निम्नलिखित सवाल हैं, बिना और पूर्ण पाठ सूचकांक का उपयोग किए:

 SELECT * FROM products WHERE name LIKE '%shoe%' OR description LIKE '%shoe%'; SELECT * FROM products WHERE MATCH(name,description) AGAINST ('shoe'); 

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

 SELECT * FROM products WHERE stock_status = 1; 

कैसे करें इंडेक्स?

PhpMyAdmin या phpMiniAdmin का उपयोग करके, आप प्रत्येक तालिका की संरचना को देख सकते हैं और देख सकते हैं कि क्या कॉलम अनुक्रमणित हैं। PhpMyAdmin में, तालिका नाम का चयन करें, संरचना के अंत में अनुक्रमणिका की गणना होगी; phpMiniAdmin में, "तालिकाएँ दिखाएँ" पर शीर्ष पर क्लिक करें, फिर तालिका के विपरीत "sct" (तालिका बनाएँ) ; इन क्रियाओं के परिणामस्वरूप, तालिका बनाने के लिए आवश्यक क्वेरी प्रदर्शित की जाएगी, जिसके अंत में अनुक्रमितों की एक सूची भी दी जाएगी, जैसे कुछ:

 orderidindex" KEY ("orderid"); 

वर्डप्रेस में wp_posts तालिका में अनुक्रमित की सूची देखने के लिए PhpMiniAdmin का उपयोग करें

यदि कोई अनुक्रमणिका नहीं हैं, तो आप स्वयं बना सकते हैं। PhpMyAdmin में, "Indexes" सेक्शन में, उन कॉलम की संख्या निर्दिष्ट करें जिनके द्वारा आप इंडेक्स बनाना चाहते हैं और "Go" पर क्लिक करें। सूचकांक का नाम दर्ज करें, आवश्यक कॉलम चुनें और "सहेजें" पर क्लिक करें, जैसा कि नीचे स्क्रीनशॉट में दिखाया गया है:

PhpMyAdmin का उपयोग करके एक इंडेक्स बनाना

PhpMiniAdmin में, आपको पृष्ठ के शीर्ष पर अपना संबंधित फ़ील्ड सम्मिलित करके निम्नलिखित अनुरोध को निष्पादित करना होगा:

 ALTER TABLE orders ADD INDEX customeridindex (customerid); 

इंडेक्स बनाने के बाद खोज क्वेरी करना मेरे कंप्यूटर पर 0.0019 सेकंड का समय लगा, जो 113 गुना तेज है।
FULL TEXT इंडेक्स जोड़ना इसी तरह किया जाता है। सूचकांक को उन कॉलमों के अनुसार संकलित किया जाना चाहिए जिन्हें आप वास्तव में देख रहे हैं:

 ALTER TABLE articles ADD FULLTEXT(title,author,articletext); SELECT * FROM articles WHERE MATCH(title,author,articletext) AGAINST ('mysql'); 

बैकअप और सुरक्षा

तालिकाओं में कोई भी परिवर्तन करने से पहले, पूरे डेटाबेस की एक बैकअप प्रतिलिपि बनाएँ। आप निर्यात बटन पर क्लिक करके PhpMyAdmin और PhpMiniAdmin के साथ ऐसा कर सकते हैं। यदि आपके डेटाबेस में महत्वपूर्ण जानकारी, जैसे कि ग्राहक हैं, तो बैकअप को सुरक्षित स्थान पर रखें। आप SSH के माध्यम से डेटाबेस का बैकअप लेने के लिए mysqldump कमांड का उपयोग कर सकते हैं:

 mysqldump --user=myuser --password=mypassword --single-transaction --add-drop-table mydatabase > backup`date +%Y%e%d`.sql 

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

हम तालिकाओं का अनुकूलन करते हैं

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

 OPTIMIZE TABLE orders; 

100,000 रिकॉर्ड के साथ मेरे अडॉप्टिमाइज्ड ऑर्डर टेबल पर 31.2 MB का कब्जा है, और फॉर्म का एक प्रश्न SELECT * FROM के ऑर्डर 0.2676 सेकंड में पूरे हुए। पहले अनुकूलन के बाद, आकार 30.8 एमबी तक कम हो गया था, और अनुरोध 0.0595 सेकंड में पूरा हो गया है।
निम्नलिखित PHP फ़ंक्शन डेटाबेस में सभी तालिकाओं को अनुकूलित करना शुरू कर देगा:

 function OptimizeAllTables() { $tables = mysql_query ('SHOW TABLES'); //get all the tables while ($table = mysql_fetch_array ($tables)) mysql_query ('OPTIMIZE TABLE ' . $table[0]); //optimize them } 

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

 mysql_connect (DB_HOST, DB_USER, DB_PASSWORD); mysql_select_db (DB_NAME); OptimizeAllTables(); 

हम एक कैश का उपयोग करते हैं

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

 SELECT * FROM orders WHERE customerid=2; 

उसी अनुरोध को बार-बार निष्पादित करने के लिए केवल 0.0004 सेकंड की आवश्यकता होती है। MySQL ने निष्पादन परिणामों को याद किया और क्वेरी को फिर से पूरी तरह से निष्पादित किए बिना उन्हें आउटपुट कर सकता है।
हालाँकि, कई समाचार साइटें और ब्लॉग समान प्रश्नों का उपयोग यह सुनिश्चित करने के लिए कर सकते हैं कि लेख "प्रकाशन" की तारीख के बाद ही प्रदर्शित हों:

 SELECT * FROM posts WHERE publisheddate <= CURDATE(); SELECT * FROM articles WHERE publisheddate <= NOW(); 

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

 SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00'; 

आप यह सत्यापित करने के लिए PHP का उपयोग कर सकते हैं कि समय अंतराल 5 मिनट या तो है:

 $time = time(); $currenttime = date ('Ymd H:i', $time - ($time % 300)); mysql_query (“SELECT * FROM articles WHERE publisheddate <= '$currenttime'”); 

अभिव्यक्ति % 300 निकटतम 300 सेकंड (5 मिनट) के लिए समय को गोल करता है।
MySQL में अन्य गैर-परिवर्तनीय कार्य भी होते हैं , जैसे RAND।

और कैश बढ़ रहा है ...

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

 SELECT * FROM articles WHERE publisheddate <= '2011-01-17 17:00' AND categoryid=12; 

शायद अगर आपकी साइट में 500 श्रेणियां थीं, तो ऐसे अनुरोधों को कैश कर दिया जाएगा और परिणाम मिलीसेकंड पर वापस आ जाएगा। लेकिन क्या होगा अगर 1000 लगातार देखी जाने वाली श्रेणियां हैं? वे कैश से बाहर एक दूसरे को धक्का देंगे और बहुत धीमी गति से चलेंगे। इस स्थिति में, कैश का आकार बढ़ाने में मदद मिल सकती है। लेकिन कैश के लिए अधिक मेमोरी आवंटित करने से अन्य कार्यों पर प्रतिकूल प्रभाव पड़ सकता है, इसलिए सावधान रहें। आप सर्वर चर को बदलकर कैश दक्षता को बढ़ाने और सुधारने के लिए बहुत सारे सुझाव पा सकते हैं ।

जब कैशिंग शक्तिहीन है

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

खुद का कैश

यदि विशेष रूप से भारी डेटाबेस क्वेरी को पूरा होने में लंबा समय लगता है, और डेटा अक्सर नहीं बदलता है, तो आप परिणामों को स्वयं कैश कर सकते हैं।
मान लीजिए कि आप अंतिम सप्ताह में किसी साइट पर 20 लोकप्रिय पोस्ट दिखाना चाहते हैं, जिसमें एक सूत्र का उपयोग करके "पसंदीदा", "एक दोस्त को भेजें" को जोड़ते हुए खोज प्रश्नों को देखा जा सकता है। और आप इस सूची को मुख्य पृष्ठ पर बुलेटेड सूची के रूप में प्रदर्शित करना चाहते हैं।
सबसे आसान तरीका होगा, उदाहरण के लिए, एक घंटे या दिन में एक बार डेटाबेस में एक क्वेरी बनाने के लिए PHP का उपयोग करना और परिणामों को एक अलग फ़ाइल में सहेजना, जो तब साइट पेज से जुड़ा होता है।
सूची फ़ाइल बनाने वाले PHP कोड को लिखने के बाद, आप इसे शेड्यूल पर चलाने के लिए कई तरीकों का उपयोग कर सकते हैं। आप इस स्क्रिप्ट को प्रति घंटा इस तरह से चलाने के लिए सर्वर शेड्यूलर (Plesk 8: सर्वर → शेड्यूल्ड टास्क में) का उपयोग कर सकते हैं:

 wget -O /dev/null -q http://www.mywebsite.co.uk/runhourly.php 

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

 $filestat = stat ('includes/complicatedfile.html'); //look up information about the file if ($filestat['mtime'] < time()-3600) RecreateComplicatedIncludeFile(); //over 1 hour readfile ('includes/complicatedfile.html'); //include the file into the page 

ऊपर दिए गए उदाहरण पर लौटते हुए, "ग्राहक इस उत्पाद के साथ और क्या खरीदते हैं ...", आप डेटा को एक नए कॉलम या सामान्य रूप से एक तालिका में भी कैश कर सकते हैं। सप्ताह में एक बार, आप यह निर्धारित करने के लिए प्रत्येक उत्पाद के लिए जटिल प्रश्नों का एक बड़ा समूह चला सकते हैं कि इसके साथ कौन से उत्पाद खरीदे जाते हैं।
तब आप परिणामी उत्पाद पहचानकर्ताओं को अल्पविराम द्वारा अलग किए गए तत्वों के एक सेट के रूप में एक नए कॉलम में संग्रहीत कर सकते हैं। भविष्य में, सामानों की एक सूची प्राप्त करने के लिए, जिसे id = 12 के साथ एक उत्पाद के साथ खरीदा जाता है, आपको निम्नलिखित अतिवृद्धि करने की आवश्यकता होगी:

 SELECT * FROM products WHERE FIND_IN_SET(12,otherproductids); 

JOIN का उपयोग करके प्रश्नों की संख्या कम करें

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

 SELECT * FROM orders WHERE status>1; 

और प्रत्येक आदेश के लिए आपको अभी भी उस क्लाइंट को ढूंढना होगा जिसने इसे जारी किया था:

 SELECT * FROM customers WHERE id=1; SELECT * FROM customers WHERE id=2; SELECT * FROM customers WHERE id=3; etc 

यदि 100 आदेशों की जानकारी तुरंत पृष्ठ पर प्रदर्शित की जाती है, तो 101 अनुरोध करने होंगे। और अगर आपको अभी भी किसी अन्य तालिका से वितरण पते के बारे में जानकारी प्रदर्शित करने की आवश्यकता है, तो सभी ऑर्डर की कुल लागत, पेज निर्माण की गति गिर जाएगी, गिर जाएगी ... सब कुछ JOIN के माध्यम से अनुरोधों को जोड़कर बहुत तेजी से किया जा सकता है। निम्नलिखित उपरोक्त प्रश्नों के संयोजन का एक उदाहरण है:

 SELECT * FROM orders INNER JOIN customers ON orders.customerid = customers.id WHERE orders.status>=1; 

इन अनुरोधों को लिखने का एक और तरीका भी है, लेकिन बिना JOIN के:

 SELECT * FROM orders, customers WHERE orders.customerid = customers.id AND orders.status>=1; 

JOIN का उपयोग करने के लिए अनुरोधों का अनुवाद करना मुश्किल हो सकता है क्योंकि PHP कोड को बदलना होगा। लेकिन यदि हजारों अनुरोध "धीमी" पृष्ठ पर निष्पादित किए जाते हैं, तो शायद आपको वर्णित विधि पर ध्यान देना चाहिए? अधिक जानकारी के लिए, आप विकिपीडिया का उल्लेख कर सकते हैं, जिसमें JOIN का विवरण है । JOIN (उदाहरण के लिए, customerid ) में उपयोग किए जाने वाले स्तंभों को अनुक्रमित किया जाना चाहिए।
आप MySQL से " व्याख्या " करने के लिए भी कह सकते हैं कि यह आपकी क्वेरी कैसे करता है। नतीजतन, आप देखेंगे कि क्वेरी को निष्पादित करते समय कैसे और किन तालिकाओं का उपयोग किया जाता है, और आप कुछ का अनुकूलन कर सकते हैं। नीचे दिया गया स्क्रीनशॉट EXPLAIN का उपयोग करते हुए वर्डप्रेस में जटिल प्रश्नों में से एक को निष्पादित करने का परिणाम दिखाता है:

EXPLAIN का उपयोग करते हुए, हम सीखते हैं कि MySQL "जटिल प्रश्नों" को कैसे मानता है

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

उपयोग चालाक

अंत में, "इस उत्पाद के साथ ग्राहक क्या खरीदते हैं ..." के उदाहरण पर फिर से लौटते हुए, आप इसे सरल बना सकते हैं। उदाहरण के लिए, "फीचर्ड प्रोडक्ट्स" का नाम बदलें, और उसी श्रेणी के कई अन्य उत्पादों को प्रदर्शित करें या मैन्युअल रूप से निर्दिष्ट करें।

निष्कर्ष

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

PS , , .
PPS Q&A , Google Page Speed Online .