👰🏻 🚝 🚴 MySQL JOIN प्रदर्शन की खोज ☪️ ✌🏿 👕

मुझे लगता है कि यह किसी के लिए कोई रहस्य नहीं है कि JOIN को एक महंगा ऑपरेशन माना जाता है, और कई नौसिखिए प्रोग्रामर (जो MySQL का उपयोग करते हैं) कि जोइन बुरा है, उसे डराना पसंद है और यदि संभव हो तो उनके बिना करना सबसे अच्छा है।

आइए इस प्रश्न की अधिक विस्तार से जांच करें और देखें कि क्या JOIN वास्तव में बुरा है, और इसके बारे में कब सोचना है।

मैंने क्या लिखा है

शुरू करने के लिए, मैं तुरंत कहना चाहूंगा कि मैं ऐसा नहीं करूंगा:

- MySQL ट्यूनिंग: सभी सेटिंग्स डिफ़ॉल्ट रूप से ली जाती हैं ( innodb_buffer_pool_size = 8 एमबी और अधिक सहित )
- प्रोग्रामिंग भाषाओं के साथ एकीकरण: सीक्वल प्रो के MySQL क्लाइंट के माध्यम से सभी अनुरोध किए जाएंगे, और समय को इसके मूल्यांकन के आधार पर मापा जाएगा
- स्पष्ट चीजें, जैसे 3 पंक्तियों को लाने पर जुड़ती हैं: सवाल यह है कि क्या मैं मैचों को बचा सकता हूं या नहीं, मैं विचार नहीं करना चाहता हूं - हम दर्जनों बार बचत करने पर विचार करेंगे, न कि दस प्रतिशत

प्रारंभिक शर्तें

हमारे पास दो प्लेटें होंगी जो अपमान करने के लिए सरल हैं ( टेबल केवल उदाहरण के लिए बनाए गए थे और यादृच्छिक डेटा से भरे हुए थे ):

कॉल - 10 मिलियन लाइनें:

आईडी	INT प्राथमिक कुंजी AUTO_INCREMENT
user_id	INT
लागत	INT
call_dt	DATETIME
tariff_id	INT

उपयोगकर्ता - 100 हजार लाइनें:

आईडी	INT प्राथमिक कुंजी AUTO_INCREMENT
जन्मतिथि	DATE को
नाम	VARCHAR (10)
लिंग	ENUM ('M', 'F')

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

तालिका प्रकार

अनुसंधान उद्देश्यों के लिए, कॉल तालिका को दो प्रकारों में लिया गया था - MyISAM और InnoDB, और उपयोगकर्ता तालिका, जिसमें हम तीन प्रकारों के JOIN बनाते हैं - MyISAM, InnoDB और MEMORY

पहला खून

सभी परीक्षण मेरे लैपटॉप पर किए गए, MySQL संस्करण 5.5.9 के साथ मैक ओएस एक्स पर MAMP से डिफ़ॉल्ट सेटिंग्स के साथ। सभी तालिकाएं पूरी तरह से मेमोरी में फिट हो जाती हैं, यह सुनिश्चित करने के लिए कि कैश में सब कुछ मिल गया है, कई बार प्रश्न चलाए गए थे।

शुरुआत करने के लिए, आइए ऐसी क्वेरी को निष्पादित करके MyISAM और InnoDB में पंक्तियों को देखने की गति को देखें (मैं आपको याद दिलाता हूं कि मूल्य या user_id द्वारा कोई अनुक्रमणिका नहीं हैं - हम MySQL में पूर्ण स्कैन गति को मापते हैं:

SELECT MAX(cost) FROM calls WHERE user_id = 5000; --  №1

और ऐसा निवेदन:

 SELECT MAX(cost) FROM calls; --  №2

परिणाम (5% से कम त्रुटि):

संख्या	InnoDB, एमएस	मायिसम एमएस
1	५ ३६०	862
2	5390	1 150

मैं इस बारे में अधिक विस्तार से अध्ययन नहीं करना चाहता कि MyISAM में समग्र चयन, WHERE के अंतर्गत आने वाली पंक्तियों की संख्या से बहुत अधिक प्रभावित होते हैं, लेकिन तथ्य यह है कि जब मैं तालिका में पूर्ण रूप से देखता हूं, तो MyISAM, InnoDB से 4.5 गुना तेज है। इसलिए राय है कि InnoDB एक "ब्रेक" है, और यह MySQL खुद (MyISAM के साथ) बहुत स्मार्ट है।

मिनी-निष्कर्ष: जब MyISAM को पूरी तरह से क्रमिक रूप से देखा जाता है, तो यह InnoDB से 5 गुना तेज है

शामिल हों

आइए अब उपयोगकर्ता तालिका को मामले से जोड़ते हैं - इस बात के लिए नहीं कि हमने इसे बनाया था।
हम ऐसी योजना के अनुरोधों की जांच करेंगे:

 SELECT MAX(calls.cost) FROM calls JOIN users ON calls.user_id = users.id WHERE calls.cost > %d AND users.sex = 'M'

लागत के लिए पैरामीटर चुना जाता है ताकि कॉल तालिका में रिकॉर्ड का एक निश्चित प्रतिशत चयन के तहत गिर जाए

यदि हम लागत पैरामीटर के साथ मांसपेशियों के ऊपर क्वेरी बनाते हैं, जो पंक्तियों के N% के अनुरूप होगी, तो MySQL केवल N% मामलों में शामिल हो जाएगा, और शेष (100-N)% पंक्तियों के लिए यह JOIN नहीं करेगा। यहाँ इस तरह के एक चतुर MySQL है।

जोइन के साथ परीक्षण करने के लिए नीचे उतरें:

पूर्ण स्कैन + जॉइन 0.1% लाइनें

उपयोगकर्ता \ Calls	InnoDB, एमएस (JOIN, एमएस केवल)	MyISAM, एमएस (JOIN, एमएस केवल)
InnoDB	5,450 (~ 0)	857 (~ 0)
MyISAM	5,450 (~ 0)	937 (~ 100)
स्मृति	5 350 (~ 0)	845 (~ 100)

अब तक, समय पूर्ण स्कैन से बहुत कम है। यह समझ में आता है - सब के बाद, जोइन को लाइनों की एक डरावनी संख्या के लिए बनाया गया है।

पूर्ण स्कैन + लाइनों का 1% शामिल हों

उपयोगकर्ता \ Calls	InnoDB, एमएस (JOIN, एमएस केवल)	MyISAM, एमएस (JOIN, एमएस केवल)
InnoDB	5,660 (300)	999 (140)
MyISAM	6 530 (1 200)	1 810 (950)
स्मृति	5,460 (100)	911 (65)

मजेदार हुह? केवल 1% पंक्तियाँ शामिल हैं, और MyISAM + MyISAM के लिए परिणाम MyISAM + Inn3DB से 2 गुना अधिक हैं। यह बहुत मज़ेदार है कि इस मामले में इनओबीडी के लिए जोइन, जोइन से मयसाम की तुलना में तेज़ है। और हमने अभी तक इसका परीक्षण शुरू नहीं किया है :)!

पूर्ण स्कैन + 10% लाइनों में शामिल हों

उपयोगकर्ता \ Calls	InnoDB, एमएस	मायिसम एमएस
InnoDB	7,230 (1,900)	2 190 (990)
MyISAM	16 100 (8 800)	10,200 (9,000)
स्मृति	6,080 (700)	1,440 (580)

यह पावर (MyISAM) के लिए शर्म की बात है, लेकिन मैं क्या कर सकता हूं ... यह पता चला कि MyISAM इतना स्मार्ट नहीं है ... या नहीं? आइए अंतिम परीक्षा परिणामों को देखें।

पूर्ण स्कैन + लाइनों की 100% शामिल हों

उपयोगकर्ता \ Calls	InnoDB, एमएस	मायिसम एमएस
InnoDB	18,000 (14,650)	12,500 (11,655)
MyISAM	100,000 (96,650)	91 600 (90 750)
स्मृति	10 500 (7 150)	5,280 (4,435)

MyISAM के साथ JOIN के साथ राक्षसी (!) पर ध्यान दें। लेकिन InnoDB सुखद आश्चर्यचकित था - अपनी वास्तुकला के कारण, JOIN InnoDB के लिए बहुत महंगा ऑपरेशन नहीं है। सच कहूं, तो मुझे बहुत आश्चर्य हुआ जब मुझे ऐसा परिणाम मिला कि दूसरा सबसे तेज JOIN विकल्प तब है जब InnoDB MyISAM से जुड़ा है।

खैर, मेमोरी के साथ, मुझे लगता है, सब कुछ स्पष्ट है - मेमरी 525% (4,435 एमएस) का ओवरहेड देती है, पीके में शामिल होता है, इनोबीडी 1,380% (11,655 एमएस) का ओवरहेड देता है, यह MyISAM के बारे में बात करने के लिए शर्म की बात है।

जॉय की जगह IN (...) के साथ

गहरी नजर यह देख सकती है कि हमारे परिदृश्य के लिए (जब हम उपयोगकर्ताओं को कॉल तालिका से सभी महिलाओं को बाहर निकालने के लिए JOIN करते हैं) JOIN के बिना एक रास्ता है, लेकिन IN () में सभी user_id की एक सरल सूची के साथ:

 SET group_concat_max_len = 10000000; SELECT GROUP_CONCAT(id) FROM users WHERE sex = 'M'; --    50 ,  -   SELECT MAX(cost) FROM calls WHERE user_id IN(%s); -- %s

MyISAM प्रकार की एक तालिका के लिए इस तरह की क्वेरी 3,730 एमएस में, और 8,290 एमएस में इनोबीडी में काम करेगी। गहरी आंख यह देख सकती है कि यह विधि JOIN to MEMORY से तेज है, हालाँकि ज्यादा नहीं। यह विधि उन मामलों में उपयुक्त है जब आपके पास MySQL (उदाहरण के लिए, UNIX सॉकेट) के लिए बहुत तेज़ कनेक्शन है। अन्य मामलों में, IMHO, इतने सारे रिकॉर्ड के साथ, यह स्पष्ट है कि MySQL सर्वर और एप्लिकेशन सर्वर के बीच भारी मात्रा में ट्रैफ़िक चलाना सबसे अच्छा विचार नहीं है।

आप स्वयं निष्कर्ष निकाल सकते हैं: यदि आपको बहुत अधिक जुड़ने की आवश्यकता है, और किसी कारण से आपको उच्च प्रदर्शन की आवश्यकता है, तो संयोजन MyISAM + InnoDB, या बस InnoDB + InnoDB का उपयोग करें, यदि एक से अधिक जोड़ हैं। ठीक है, अगर आप चरम पर हैं, तो आप किसी भी परिदृश्य में अधिकतम प्रदर्शन प्राप्त करने के लिए MyISAM + मेमोरी का उपयोग कर सकते हैं। MEMORY + MEMORY के साथ एक और विकल्प है, लेकिन बड़ी संख्या में रिकॉर्ड के लिए मैं ऐसा नहीं करूंगा;)।

UPD: मैं इस उपयोगी उदाहरण के लिए, बहुत ही उपयोगी टिप्पणियों के लिए हॉम हब्रायुज़र को धन्यवाद देना चाहूंगा। सामान्य तौर पर, मैं टिप्पणियों को पढ़ने की अत्यधिक सलाह देता हूं, वे बहुत सी चीजें समझाते हैं जो किसी कारण से पाठकों के लिए स्पष्ट नहीं थीं:
- अनुरोध कैश अक्षम है
- प्राथमिक कुंजी पर JOIN किया जाता है
- कॉल टेबल पर अनुक्रमणिका नहीं बनाई जाती हैं, क्योंकि हम किसी विशेष क्वेरी को अनुकूलित करने के लिए कार्य निर्धारित नहीं करते हैं

MySQL JOIN प्रदर्शन की खोज