🛩️ 🎓 👨‍🏫 एक साधारण MySQL क्वेरी से अप्रत्याशित परिणाम 👨‍👩‍👧‍👦 🙅🏼 👄

मैंने हाल ही में एक टन का समय बिताया है कि यह जानने के लिए कि एक ही अनुरोध ऐसा अजीब परिणाम क्यों पैदा करता है। MariaDB पर, इसने स्पष्ट रूप से गलत परिणाम दिया, लेकिन अच्छे पुराने MySQL पर बिल्कुल भी निष्पादित नहीं किया गया था। यानी अनुरोध लॉन्च किया गया था, लेकिन इसके पूरा होने की प्रतीक्षा करना संभव नहीं था। यह पता लगाने के लिए कि मामला क्या था, मुझे थोड़ा शोध करना पड़ा। लेकिन चलो क्रम में सब कुछ के बारे में बात करते हैं।

अंतिम महीने के लिए दिन के दौरान मंच पर कम से कम एक बार लिखने वाले उपयोगकर्ताओं की संख्या की गणना करना आवश्यक था।
मान लीजिए कि फ़ोरम पोस्ट निम्न तालिका में हैं:

CREATE TABLE `forum_posts` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `created` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

जिसमें निम्नलिखित डेटा शामिल हैं

 INSERT INTO `forum_posts` (`user_id`, `created`) VALUES (1, '2013-01-01'), (1, '2013-01-01'), (2, '2013-01-01'), (2, '2013-01-01'), (2, '2013-01-01'), (3, '2013-01-01'), (3, '2013-01-01'), (4, '2013-01-01'), (5, '2013-01-01'), (5, '2013-01-01'), (1, '2013-01-02'), (1, '2013-01-02'), (2, '2013-01-02'), (2, '2013-01-02'), (3, '2013-01-02'), (3, '2013-01-02'), (4, '2013-01-02'), (4, '2013-01-02'), (1, '2013-02-02'), (1, '2013-02-02'), (2, '2013-02-02'), (2, '2013-02-02'), (3, '2013-02-02'), (3, '2013-02-02'), (4, '2013-02-02'), (4, '2013-02-02'), (5, '2013-02-02'), (5, '2013-02-02');

एक क्वेरी जो आवश्यक डेटा लौटाती है:

 SELECT DATE(fp.created) dt, count(*) qnt FROM forum_posts fp WHERE fp.id IN ( SELECT fp2.id FROM forum_posts fp2 WHERE fp2.created >= '2013-01-01' AND fp2.created < '2013-02-01' GROUP BY DATE(fp2.created), fp2.user_id ) GROUP BY dt;

इस अनुरोध का परिणाम अप्रत्याशित था, यह वापस आ गया:

 2013-01-01 10 2013-01-02 8

अपनी आंखों पर विश्वास नहीं करते हुए, मैं अलग से सबक्वेरी को अंजाम देने के लिए दौड़ पड़ा - उसने सही परिणाम (9 लाइन) लौटाया। अर्थात, WHERE id IN (...) शर्त के साथ एक पहचानकर्ता IN (...) को पारित किए गए पहचानकर्ताओं की तुलना में अधिक रिकॉर्ड लौटाए गए। यह स्पष्ट हो गया कि शाम लंबी थी।

हमारे पास क्या है?

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

चूंकि मैं रहस्यवाद में विश्वास नहीं करता हूं और शाम को वास्तव में खुद पर भरोसा नहीं करता, इसलिए मैंने समस्या को हल करने के लिए कॉमरेड xzander को आकर्षित किया। उन्होंने मेरे डेटाबेस के डंप पर एक क्वेरी लॉन्च की (वास्तव में, लगभग 4 मिलियन पंक्तियाँ थीं)। परिणाम अप्रत्याशित था - 10 मिनट में अनुरोध पूरा नहीं हुआ। पर्यावरण की तुलना करने पर, हमें पता चला कि मैं MariaDB (5.5.30) पर एक क्वेरी चला रहा हूं, और यह MySQL (5.58) पर है

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

MariaDB पेचीदा है - यह क्वेरी को जल्दी से क्रियान्वित करता है, लेकिन उपनगर में GROUP BY को अनदेखा करता है और जैसा कि यह निकला, यह एक बग बिल्कुल नहीं है, लेकिन एक विशेषता है । यहां आपके पास संक्रमण की पूर्ण संगतता और पारदर्शिता है।

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

क्या करें?

यह पता चला कि ऑप्टिमाइज़र को धोखा देना मुश्किल नहीं था: यह उसे समझाने के लिए पर्याप्त था कि उपनगर उसके लिए जितना मुश्किल लग रहा था, उससे अधिक कठिन था, HAVING 1 जोड़ना

तो यहाँ क्वेरी है जो मारियाडीबी पर सही तरीके से काम करती है:

 SELECT DATE(fp.created) dt, count(*) qnt FROM forum_posts fp WHERE fp.id IN ( SELECT fp2.id FROM forum_posts fp2 WHERE fp2.created >= '2013-01-01' AND fp2.created < '2013-02-01' GROUP BY DATE(fp2.created), fp2.user_id HAVING 1 ) GROUP BY dt;

MySQL पर, आपको शायद फिर भी क्वेरी को फिर से लिखना होगा, जो कि सबक्वेरी से छुटकारा दिलाएगा।

हम क्या निष्कर्ष निकाल सकते हैं?

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

युपीडी।
अलनो दाखिल करने से , यह पता चला कि fp2.id HAVING 1 साथ एक बैसाखी के बजाय, यह fp2.id में MIN(fp2.id) साथ fp2.id को बदलने के लिए पर्याप्त है।
लेकिन पेट्रोपावेल ने फैसला किया कि यह अभी भी एक बग था, एक सुविधा नहीं थी, और मारियाबेड बग ट्रैकर में एक टिकट बनाया। आइए देखें कि डेवलपर्स इसके बारे में क्या सोचते हैं।

एक साधारण MySQL क्वेरी से अप्रत्याशित परिणाम

हमारे पास क्या है?

क्या करें?

हम क्या निष्कर्ष निकाल सकते हैं?

More articles: