InnoDB और क्वेरी ऑप्टिमाइज़ेशन में क्लस्टर इंडेक्स

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

गुच्छेदार सूचकांक - एक फ़ाइल में एक टेबल को व्यवस्थित करने का एक रूप। InnoDB में, डेटा को सामान्य B-TREE कुंजियों के समान ट्री में संग्रहीत किया जाता है। InnoDB तालिका पहले से ही एक बड़ी B-TREE है। प्रमुख मूल्य क्लस्टर इंडेक्स हैं। प्रलेखन के अनुसार, PRIMARY KEY को क्लस्टर इंडेक्स के रूप में चुना गया है। यदि कोई प्राथमिक कुंजी नहीं है, तो पहला UNIQUE कुंजी चुना गया है। यदि यह मामला नहीं है, तो आंतरिक 6-बाइट कोड का उपयोग किया जाता है।

डिस्क पर डेटा के ऐसे संगठन से क्या होता है?

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

आप MySQL मैनुअल में इसके बारे में अधिक पढ़ सकते हैं।

हम दो प्रकार के अनुकूलन के बारे में बात करेंगे, जिसने हमारी लिपियों के काम को तेज करने में मदद की।

पर्यावरण का परीक्षण करें

अध्ययन के परिणामों पर कैशिंग के प्रभाव को कम करने के लिए, नमूनों में SQL_NO_CACHE जोड़ें, और हम प्रत्येक अनुरोध से पहले फ़ाइल सिस्टम कैश को भी फ्लश करेंगे। और, क्योंकि हम सबसे खराब स्थिति में रुचि रखते हैं, जब डेटा को वास्तव में डिस्क से खींचा जाना चाहिए, हम प्रत्येक अनुरोध से पहले MySQL को पुनरारंभ करेंगे।

उपकरण:

• Intel® पेंटियम® ड्यूल सीपीयू E2180 @ 2.00GHz
• रैम DIMM 800 MHz 4Gb
• उबुन्टु 11.04
• MySQL 5.5
• HDD हिताची HDS72161

हमने जिन लिपियों का इस्तेमाल किया, उन्हें GitHub पर लिया जा सकता है ।

गहरी नाप का अनुकूलन

उदाहरण के लिए, सार तालिका संदेश लें, जिसमें उपयोगकर्ता पत्राचार है।

  रचना संदेश ( 
             message_id int नहीं null auto_increment, 
             user1 int नहीं अशक्त, 
             user2 int नहीं अशक्त, 
             ts टाइमस्टैम्प डिफ़ॉल्ट रूप से current_timestamp को शून्य नहीं करता है, 
             बॉडी लोंगटेक्स्ट नहीं नल, 
             प्राथमिक कुंजी (message_id), 
             कुंजी (user1, user2, ts) 
         ) इंजन = InnoDB 

InnoDB की सूचीबद्ध विशेषताओं के प्रकाश में इस तालिका पर विचार करें।
यहाँ क्लस्टर इंडेक्स PRIMARY KEY से मेल खाता है और एक ऑटो-इन्क्रीमेंट फील्ड है। प्रत्येक पंक्ति में एक 4-बाइट पहचानकर्ता है। तालिका में नई पंक्तियों को सम्मिलित करना इष्टतम है। द्वितीयक कुंजी वास्तव में KEY (user1, user2, ts, message_id) है, और हम इसका उपयोग करेंगे।

हमारी तालिका में 100 मिलियन संदेश जोड़ें। यह InnoDB की आवश्यक विशेषताओं की पहचान करने के लिए पर्याप्त है। हमारे सिस्टम में केवल 10 उपयोगकर्ता हैं, इसलिए प्रत्येक जोड़े के बीच एक औसतन एक लाख संदेश होंगे।

मान लीजिए कि इन 10 परीक्षण उपयोगकर्ताओं ने बहुत सारे संदेशों का आदान-प्रदान किया और अक्सर पुराने पत्राचार को फिर से पढ़ना - इंटरफ़ेस आपको बहुत पुराने संदेशों के साथ एक पृष्ठ पर स्विच करने की अनुमति देता है। और इस इंटरफ़ेस के पीछे एक सरल अनुरोध है:

SELECT * FROM messages WHERE user1=1 and user2=2 order by ts limit 20 offset PageNumber*20

सबसे आम, वास्तव में, अनुरोध। आइए इसकी गहराई के आधार पर इसके निष्पादन के समय को देखें:

ओफ़्सेट	निष्पादन समय (एमएस)
100	311
1000	907
5000	3372
10000	6176
20000	11901
30000	17057
40000	21,997
50000	28,268
60000	32805

रैखिक विकास को देखने के लिए निश्चित रूप से बहुत से लोग उम्मीद करते हैं। लेकिन 60 हजार रिकॉर्ड पर 33 सेकंड पाने के लिए बहुत अधिक है! यह समझने के लिए कि इतना समय कितना आसान है - आपको बस MySQL कार्यान्वयन की सुविधाओं में से एक का उल्लेख करना होगा। तथ्य यह है कि इस क्वेरी को पढ़ने के लिए MySQL डिस्क से पंक्तियों की ऑफसेट + सीमा को घटाता है और उनसे सीमा वापस करता है। अब स्थिति स्पष्ट है: यह सब समय MySQL डिस्क से 60 हजार अनावश्यक लाइनों को पढ़ने में लगा हुआ था। ऐसी ही स्थिति में क्या करें? यह कई अलग-अलग समाधानों की भीख माँगता है। यहां, इन विकल्पों के बारे में एक दिलचस्प लेख है ।

हमें एक बहुत ही सरल समाधान मिला: पहली क्वेरी केवल संकुल सूचकांक मानों का चयन करती है, और फिर उनमें से विशेष रूप से चुनी जाती है। हम जानते हैं कि संकुल कुंजी का मान द्वितीयक कुंजी के अंत में मौजूद है, इसलिए, यदि हम message_id के साथ अनुरोध में * प्रतिस्थापित करते हैं, तो हमें एक अनुरोध मिलता है, जो क्रमशः केवल कुंजी द्वारा काम करता है, ऐसे अनुरोध की गति अधिक है।

यह था:

  mysql> उन संदेशों में से चयन करें * बताएं जहां उपयोगकर्ता 1 = 1 और उपयोगकर्ता 2 = 2 आदेश ts द्वारा सीमा 20 ऑफसेट 20000;
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- -------- +
 |  आईडी |  select_type |  तालिका |  प्रकार |  possible_keys |  कुंजी |  key_len |  रेफरी |  पंक्तियाँ |  अतिरिक्त |
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- -------- +
 |  1 |  SIMPLE |  संदेश |  रेफरी |  user1 |  user1 |  8 |  const, कास्ट |  210122 |  जहाँ का उपयोग करना |
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- -------- +
 1 पंक्ति में सेट (0.00 सेकंड) 

यह बन गया:

  mysql> उन चुनिंदा message_id को संदेशों से समझाएं जहाँ user1 = 1 और user2 = 2 क्रम से ts की सीमा 20 ऑफसेट 20000 है;
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- --------------------- +
 |  आईडी |  select_type |  तालिका |  प्रकार |  possible_keys |  कुंजी |  key_len |  रेफरी |  पंक्तियाँ |  अतिरिक्त |
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- --------------------- +
 |  1 |  SIMPLE |  संदेश |  रेफरी |  user1 |  user1 |  8 |  const, कास्ट |  210122 |  जहां का उपयोग करना;  इंडेक्स का उपयोग करना |
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------------- + -------- + ----- --------------------- +
 1 पंक्ति में सेट (0.00 सेकंड) 

इस मामले में सूचकांक का उपयोग करने का मतलब है कि MySQL माध्यमिक कुंजी से सभी डेटा प्राप्त करने में सक्षम होगा, और क्लस्टर इंडेक्स तक नहीं पहुंच पाएगा। इसके बारे में यहाँ और जानें।

और अब यह केवल स्ट्रिंग मानों को क्वेरी से सीधे निकालने के लिए बना हुआ है
SELECT * FROM messages WHERE message_id IN (....)

आइए देखें कि यह समाधान कितना अधिक उत्पादक है:

ओफ़्सेट	निष्पादन समय (एमएस)
100	243
1000	164
5000	213
10000	337
20000	618
30000	756
40000	971
50000	1225
60000	1477

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

एक बड़ी तालिका को अद्यतन करने के लिए प्रक्रिया का अनुकूलन

अनुकूलन की दूसरी आवश्यकता तब उत्पन्न हुई जब हमें एक बड़ी तालिका में अपने उपयोगकर्ताओं के बारे में प्रासंगिक डेटा एकत्र करने के लिए दिन में एक बार आवश्यकता हुई। उस समय, हमारे पास 130 मिलियन उपयोगकर्ता थे। स्क्रिप्ट, हमारे सभी डेटाबेस को दरकिनार करके और नया डेटा एकत्र करके, आधे घंटे में चलती है और 30 मिलियन परिवर्तित लाइनों का चयन करती है। स्क्रिप्ट का नतीजा हार्ड ड्राइव पर क्रमबद्ध नए मूल्यों के साथ दसियों हज़ार पाठ फ़ाइलों का है। प्रत्येक फ़ाइल में सैकड़ों उपयोगकर्ताओं के बारे में जानकारी होती है।

इन पाठ फ़ाइलों से जानकारी को डेटाबेस में स्थानांतरित करें। हम फाइलों को क्रमिक रूप से पढ़ते हैं, कई हजार के पैक्स में लाइनों को समूहित करते हैं और अपडेट करते हैं। स्क्रिप्ट का निष्पादन समय 3 से 20 घंटे तक होता है। स्वाभाविक रूप से, यह स्क्रिप्ट व्यवहार अस्वीकार्य है। इसके अलावा, यह स्पष्ट है कि प्रक्रिया को अनुकूलित करने की आवश्यकता है।

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

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

फिर हमने देखा कि वास्तव में तालिका में कितनी पंक्तियाँ बदलती हैं। 30 मिलियन पंक्तियों में से, केवल 3 मिलियन बदल गए हैं (जो तार्किक है, क्योंकि तालिका में उपयोगकर्ताओं के बारे में बहुत कम जानकारी है)। और इसका मतलब है कि 90% समय MySQL प्रूफरीडिंग पर खर्च करता है, और अपडेट करने पर नहीं। समाधान खुद ही आया: आपको जांचना चाहिए कि क्लस्टर इंडेक्स की यादृच्छिक पहुंच क्रमिक रूप से कैसे खो जाती है। परिणाम तालिका को अद्यतन करने के मामले में सामान्यीकृत किया जा सकता है (इसे अद्यतन करने से पहले, घटाव और तुलना अभी भी होती है)।

तकनीक बेहद सरल है - क्वेरी निष्पादन गति में अंतर को मापें
SELECT * FROM messages where message_id in ($values)
जहां मान 10K तत्वों की एक सरणी पास करते हैं। रैंडम एक्सेस की जांच के लिए एलिमेंट वैल्यू को पूरी तरह से रैंडम बनाएं। अनुक्रमिक पहुंच का परीक्षण करने के लिए, 10K तत्वों को क्रमिक रूप से बनाया जाना चाहिए, कुछ यादृच्छिक पूर्वाग्रह के साथ।

  फ़ंक्शन getValuesForRandomAccess () { 
     $ गिरफ्तार = सरणी (); 
     foreach (रेंज (1, 10000) $ i के रूप में) { 
         $ गिरफ्तारी [] = रैंड (1,100000000); 
     } 
     $ गिरफ्तारी वापस; 
 } 

 फ़ंक्शन getValuesForSequencialAccess () { 
     $ r = रैंड (1, 100000000-10000); 
     वापसी सीमा ($ r, $ r + 10000); 
 } 

यादृच्छिक और अनुक्रमिक अनुरोध निष्पादन समय:

एन	बिना सोचे समझे	क्रमबद्ध
1	38,494	171
2	40,409	141
3	40,868	147
4	37161	138
5	38,189	137
6	36,930	134
7	37398	176
8	38035	144
9	39,722	140
10	40,720	146

जैसा कि आप देख सकते हैं, निष्पादन समय में अंतर 200 गुना है। इसलिए, हमें इसके लिए लड़ना चाहिए। निष्पादन को अनुकूलित करने के लिए, आपको प्राथमिक कुंजी द्वारा स्रोत डेटा को सॉर्ट करना होगा। क्या हम फाइलों में 30 मिलियन मूल्यों को जल्दी से हल कर सकते हैं? उत्तर असमान है - हम कर सकते हैं!

इस अनुकूलन के बाद, स्क्रिप्ट चलाने का समय 2.5 घंटे तक कम हो गया था। 30 मिलियन लाइनों को पूर्व-छांटने में 30 मिनट लगते हैं (और गज़िप को अधिकतर समय लगता है)।

समान अनुकूलन, लेकिन SSD पर

लेख लिखने के बाद, हमने एक अतिरिक्त एसएसडी पाया, जिस पर हमने परीक्षण भी किया।

गहरी ऑफसेट नमूना:

ओफ़्सेट	निष्पादन समय (एमएस)
100	117
1000	406
5000	1681
10000	3322
20000	6561
30000	9754
40000	13039
50000	16,293
60000	19,472

अनुकूलित गहरी ऑफसेट नमूनाकरण:

ओफ़्सेट	निष्पादन समय (एमएस)
100	101
1000	21
5000	24
10000	32
20000	47
30000	94
40000	84
50000	95
60000	120

यादृच्छिक और अनुक्रमिक पहुंच की तुलना:

एन	बिना सोचे समझे	क्रमबद्ध
1	5321	118
2	5583	118
3	5881	117
4	6167	117
5	6349	120
6	6402	126
7	6516	125
8	6342	124
9	6092	118
10	5986	120

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

और हमारे लेख के निष्कर्ष में, हम कह सकते हैं कि हम डेटा नमूनाकरण दर को 20 गुना बढ़ाने में सक्षम थे। हमने तालिका के वास्तविक अद्यतन को 10 गुना (सरोगेट परीक्षण 200 बार त्वरित) तक त्वरित किया। याद रखें कि कैशिंग अक्षम के साथ एक सिस्टम पर प्रयोग किए गए थे। वास्तविक प्रणाली पर लाभ कम प्रभावशाली निकला (कैश अभी भी स्थिति को ठीक करता है)।

पूर्वगामी से निष्कर्ष सतह पर निहित है: जिस सॉफ्टवेयर के साथ आप काम करते हैं, उसकी ताकत और कमजोरियों को जानने के लिए यह पर्याप्त नहीं है, इस ज्ञान को व्यवहार में लाने में सक्षम होना महत्वपूर्ण है। MySQL की आंतरिक संरचना का ज्ञान कभी-कभी आपको दसियों बार प्रश्नों को गति देने की अनुमति देता है।

एलेक्सी अलेक्सा एर्मिकहिन, Badoo डेवलपर