अनुकूलन आदेश द्वारा - कितने भूल जाते हैं

MySQL प्रश्नों के अनुकूलन के विषय पर बहुत कुछ लिखा गया है, हर कोई जानता है कि SELECT, INSERT को कैसे ऑप्टिमाइज़ करना है, आपको कुंजी द्वारा क्या शामिल होना है, आदि। आदि

लेकिन एक बिंदु है, जिसे सभी मैनुअल में बार-बार वर्णित किया जाता है, लेकिन किसी कारण से हर कोई इसके बारे में भूल जाता है।

जुड़ने वाले प्रश्नों में ORDER BY का अनुकूलन।

बहाना: खोज का उपयोग किया, नहीं मिला!

अधिकांश का मानना ​​है कि यदि ORDER BY इंडेक्स द्वारा होता है, तो कोई समस्या नहीं है, हालांकि, यह हमेशा ऐसा नहीं होता है। हाल ही में, मैंने एक क्वेरी के साथ निपटाया जो बेतहाशा डेटाबेस को धीमा कर देती है, हालांकि ऐसा लगता है कि सही स्थानों में सभी अनुक्रमित हैं। ORDER BY अंतिम स्थान था जिसे मैंने पोक किया था, और समस्या थी।

अनुकूलन मैनुअल से लघु अंश:

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

ORDER BY के लिए, यह महत्वपूर्ण है कि जिस तालिका को क्रमबद्ध किया जाए, वह पहले स्थान पर हो। हालाँकि, डिफ़ॉल्ट रूप से, आप तालिकाओं में शामिल होने के क्रम में कोई फर्क नहीं पड़ता, mysql में निर्मित ऑप्टिमाइज़र उन्हें इस क्रम में पुनर्व्यवस्थित करेगा कि वह इसे आवश्यक मानता है। यही है, यदि आप क्वेरी में वांछित तालिका पहले डालते हैं, तो इसका मतलब यह नहीं है कि यह वास्तव में पहला होगा।

सौभाग्य से, mysql अनुकूलक को हमारे द्वारा निर्दिष्ट क्रम में तालिकाओं में शामिल होने के लिए कहा जा सकता है, इसके लिए आपको STRAIGHT_JOIN कमांड को चयन में जोड़ना होगा:

STRAIGHT_JOIN का चयन करें ... तालिका से प्राप्त करें ....... ORDER BY table.row

फोरम PHPBB3 के mysql डेटाबेस पर लगभग 300,000 पदों की जाँच करें:

SELECT t.*, p.*, u.username FROM phpbb3_topics as t, phpbb3_posts as p, phpbb3_users as u WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='16' AND p.post_id<'244103' ORDER by post_id desc LIMIT 40 

क्वेरी ने 12.2571 सेकेंड का समय लिया

समझाने में हम भयानक देखते हैं: जहां का उपयोग करना; अस्थायी का उपयोग करना; फाइलसर्ट का उपयोग करना

हम तालिकाओं के क्रम को बदल देते हैं (रिबूटिंग द्वारा मांसपेशी कैश को रीसेट किया गया था):

 SELECT STRAIGHT_JOIN t.*, p.*, u.username FROM phpbb3_posts as p, phpbb3_topics as t, phpbb3_users as u WHERE t.topic_replies>0 AND p.poster_id=u.user_id AND topic_first_post_id<>p.post_id AND topic_approved=1 AND p.topic_id=t.topic_id AND t.forum_id='13' AND p.post_id<'234103' ORDER by post_id desc LIMIT 40 

क्वेरी ने 0.0447 सेकेंड लिया

समझाने में: जहाँ का उपयोग करना;

तालिकाओं के इस तरह के एक मजबूर क्रमांकन के साथ, हमने क्वेरी को 300 गुना तेज कर दिया!

इसका मतलब यह नहीं है कि आपको हमेशा STRAIGHT_JOIN का उपयोग करना चाहिए और तालिकाओं के क्रम का ट्रैक स्वयं रखना चाहिए। लेकिन कुछ मामलों में यह आवश्यक है।

PS यह क्वेरी Yandex द्वारा phpbb मंचों को अनुक्रमित करने के लिए उपयोग की जाती है। अनुकूलन से पहले, यैंडेक्स -बॉट ने php.ru सर्वर को हर रात कई घंटों के लिए रखा (सर्वर बहुत शक्तिशाली नहीं है)। यैंडेक्स ब्लॉग पर इस विषय पर चर्चा हुई थी, लेकिन कुछ साल पहले इसे बंद कर दिया गया था और निर्णय की घोषणा नहीं की गई थी।