MySQL लूज़ स्कैन ऑप्टिमाइज़ेशन: PostgreSQL और Microsoft के विरुद्ध एक तुलनात्मक प्रदर्शन मूल्यांकन

ग्रुप बाय क्लॉज को संतुष्ट करने का सबसे सामान्य तरीका पूरी तालिका या इंडेक्स को स्कैन करना और उसमें से केवल अलग-अलग मान निकालना है। इस ऑपरेशन में 2 रणनीतियाँ हो सकती हैं।

हैश एकत्रीकरण

यह तकनीक आमतौर पर तब नियोजित की जाती है जब किसी क्वेरी को कुछ गैर-अनुक्रमित कॉलमों के आधार पर डेटा को समूहित करने की आवश्यकता होती है। हैश एग्रीगेशन में, एक हैश तालिका का निर्माण किया जाता है, जहां कुंजियाँ समूह-दर-कॉलम मानों के अद्वितीय संयोजनों का प्रतिनिधित्व करती हैं।

जैसे ही डेटाबेस इंजन पंक्तियों के माध्यम से स्कैन करता है, यह प्रत्येक पंक्ति के समूह-दर-कॉलम मानों के लिए हैश मान की गणना करता है और हैश तालिका में प्रत्येक हैश मान के अनुरूप एकत्रित डेटा संग्रहीत करता है।

हालांकि यह विधि बड़े डेटासेट के लिए मेमोरी-गहन हो सकती है, यह अक्सर सबसे तेज़ तरीका होता है जब सर्वर में हैश तालिका को संग्रहीत करने के लिए पर्याप्त मेमोरी होती है।

स्ट्रीम एकत्रीकरण

स्ट्रीम एग्रीगेशन का उपयोग तब किया जाता है जब समूहीकृत किया जाने वाला डेटा समूह-दर-कॉलम पर पहले से ही सॉर्ट किया गया हो, या लगभग सॉर्ट किया गया हो। जैसे ही डेटा स्ट्रीम आती है, डेटाबेस इंजन वर्तमान पंक्ति की तुलना पिछली पंक्ति से करता है।

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

स्ट्रीम एग्रीगेशन हैश एग्रीगेशन की तुलना में कम मेमोरी का उपयोग करता है, लेकिन इसके लिए डेटा को सॉर्ट करने की आवश्यकता होती है, जिसमें डेटा पहले से सॉर्ट नहीं होने पर अतिरिक्त सॉर्ट ऑपरेशन शामिल हो सकता है।

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

ढीला स्कैन क्या है?

लूज़ स्कैन एक उन्नत MySQL अनुकूलन तकनीक है जो कुछ ग्रुप बाय ऑपरेशंस के संदर्भ में लागू होती है, खासकर उन परिदृश्यों में जहां तालिका में पंक्तियों की कुल संख्या की तुलना में अपेक्षाकृत कम संख्या में पंक्तियों को संसाधित किया जा रहा है।

यह तकनीक डेटाबेस से पढ़ने के लिए आवश्यक डेटा की मात्रा को कम करके प्रश्नों के प्रदर्शन में उल्लेखनीय सुधार करती है।

लूज़ स्कैन का अंतर्निहित सिद्धांत

संक्षेप में, लूज़ स्कैन तकनीक एक सरल सिद्धांत पर काम करती है: क्वालीफाइंग पंक्तियों (उर्फ 'टाइट' स्कैन) के लिए संपूर्ण सूचकांक को स्कैन करने के बजाय, यह प्रत्येक समूह के लिए पहली मिलान पंक्ति के लिए 'लूज़ली' स्कैन करती है। एक मैच ढूंढने के बाद, यह तुरंत अगले समूह में चला जाता है।

यह विधि उन पंक्तियों की संख्या में कमी सुनिश्चित करती है जिनका मूल्यांकन करने की आवश्यकता होती है, इस प्रकार एक क्वेरी को निष्पादित करने में लगने वाला कुल समय कम हो जाता है।

MySQL के अलावा, इसी तरह की तकनीक अन्य डेटाबेस इंजनों में भी लागू की जाती है। इसे "स्किप स्कैन" कहा जाता है आकाशवाणी , SQLite , और कॉकरोचडीबी , "जंप स्कैन" में डीबी2 , और "हाइब्रिड स्कैन" में युगाबाइटडीबी .

पर्यावरण की स्थापना

मुझे लगता है कि यह पर्याप्त सिद्धांत है; आइए व्यावहारिक भाग की ओर बढ़ते हैं और MySQL बनाम PostgreSQL और Microsoft SQL सर्वर में लूज़ स्कैन का तुलनात्मक विश्लेषण करते हैं। मैं अपने लैपटॉप पर MySQL 8.0.33, PostgreSQL 15.3 और MS SQL 2022-CU4 के साथ नवीनतम डॉकर कंटेनर का उपयोग करूंगा।

मैं अलग-अलग कार्डिनैलिटी के साथ 1 मिलियन पंक्तियों और पूर्णांक डेटा प्रकार के तीन कॉलम वाली एक तालिका बनाऊंगा। पहले कॉलम में 100 हजार अद्वितीय मान हैं, दूसरे में 1 हजार और तीसरे में केवल 10 अद्वितीय मान हैं।

मैं तीन अलग-अलग नॉनक्लस्टर्ड इंडेक्स बनाऊंगा और प्रत्येक कॉलम पर ग्रुप बाय क्वेरी चलाऊंगा। डेटाबेस को गर्म करने के लिए प्रत्येक क्वेरी को बिना बीते समय की गणना के 5 बार निष्पादित किया जाएगा और फिर 20 बार और, और हम बाद में औसत निष्पादन समय की तुलना करेंगे।

इसलिए, मैंने सभी डेटाबेस इंजनों को बिल्कुल समान स्थिति में रखने की कोशिश की।

एक स्क्रिप्ट है जिसका उपयोग मैंने सभी डेटाबेस में नमूना तालिकाओं को आरंभ करने के लिए किया है:

 -- MySQL create table numbers ( id int not null ); insert into numbers(id) with tmp as ( select a.id + b.id * 10 + c.id * 100 + d.id * 1000 as id from (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c cross join (select 0 as id union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as d ) select id from tmp; create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) with tmp as ( select a.id + b.id * 10000 as id from numbers as a cross join numbers as b ) select id, floor(rand() * 100000) as a, floor(rand() * 1000) as b, floor(rand() * 10) as c from tmp where id < 1000000; create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- PostgreSQL create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key (id) ); insert into group_by_table(id, a, b, c) select id, floor(random() * 100000) as a, floor(random() * 1000) as b, floor(random() * 10) as c from generate_series(1, 1000000, 1) as numbers(id); create index idx_group_by_table_a on group_by_table(a); create index idx_group_by_table_b on group_by_table(b); create index idx_group_by_table_c on group_by_table(c); -- MS SQL Server create table group_by_table ( id int not null, a int not null, b int not null, c int not null, primary key clustered (id) ); with tmp as ( select row_number() over (order by (select 1)) - 1 as id from sys.all_columns as a cross join sys.all_columns as b ) insert into group_by_table(id, a, b, c) select id, floor(rand(checksum(newid())) * 100000) as a, floor(rand(checksum(newid())) * 1000) as b, floor(rand(checksum(newid())) * 10) as c from tmp where id < 1000000; create nonclustered index idx_group_by_table_a on group_by_table(a); create nonclustered index idx_group_by_table_b on group_by_table(b); create nonclustered index idx_group_by_table_c on group_by_table(c);

परिणाम

सभी डेटाबेस कॉलम ए पर लगभग समान प्रदर्शन करते हैं, जहां डेटा कार्डिनैलिटी उच्च है (1 मिलियन पंक्तियों में से 100 हजार अद्वितीय मान)। PostgreSQL का कुल निष्पादन समय 3.57 सेकंड, MS SQL सर्वर - 2.72 सेकंड और MySQL - 3.44 सेकंड था।

लेकिन कॉलम बी में, जहां कार्डिनैलिटी केवल 1000 अद्वितीय मान है, MySQL का कुल निष्पादन समय घटकर 70.76 मिलीसेकंड हो जाता है, जबकि PostgreSQL इसे 1.56 सेकंड में और MS SQL सर्वर 2.52 सेकंड में करता है।

तो, MySQL दूसरी क्वेरी को PostgreSQL से 22 गुना तेजी से और MS SQL सर्वर से 35 गुना तेजी से पूरा करता है।

कॉलम C में स्थिति और भी बेहतर है, जहां केवल 10 अद्वितीय मान हैं: MySQL - 16.66ms, PostgreSQL - 1.58s, और MS SQL सर्वर - 2.55s।

पिछले उदाहरण में, MySQL बहुत तेज़ है और PostgreSQL से लगभग 95 गुना और MS SQL सर्वर से 150 गुना से अधिक बेहतर प्रदर्शन करता है।

नीचे, लघुगणकीय पैमाने का उपयोग करके एक विज़ुअलाइज़ेशन है। यह 20 निष्पादन के बाद कुल निष्पादन समय दिखाता है।

प्रदर्शन में सुधार के लिए MS SQL और PostgreSQL में क्या किया जा सकता है?

जबकि PostgreSQL और MS SQL सर्वर में वर्तमान में इस तरह के अनुकूलन का अभाव है, एक तरकीब है जिसे आप इन इंजनों पर अपने GROUP BY प्रश्नों के प्रदर्शन को बेहतर बनाने के लिए कर सकते हैं। डिफ़ॉल्ट पूर्ण इंडेक्स स्कैन पर निर्भर रहने के बजाय, इंडेक्स में कई लुकअप करने का विचार है।

उदाहरण के लिए, PostgreSQL में, आप सभी अद्वितीय मानों को खोजने के लिए एक पुनरावर्ती क्वेरी कर सकते हैं। पहला पुनरावृत्ति कॉलम से न्यूनतम मान का चयन करता है, जबकि प्रत्येक दूसरा पुनरावृत्ति पिछले से अधिक अगले मान का चयन करता है।

 with recursive t as ( select min(a) as x from group_by_table union all select (select min(a) from group_by_table where a > tx) from t where tx is not null ) select count(*) from ( select x from t where x is not null union all select null where exists (select 1 from group_by_table where a is null) ) as tmp;

यही ट्रिक हम MS SQL सर्वर में भी कर सकते हैं। लेकिन, दुर्भाग्य से, MS SQL सर्वर पुनरावर्ती क्वेरीज़ TOP या एग्रीगेट ऑपरेटरों का समर्थन नहीं करती हैं, इसलिए मैं परिणाम को संग्रहीत करने और LOOP का उपयोग करके पुनरावृत्त करने के लिए एक अस्थायी तालिका का उपयोग करूंगा।

बेशक, इसमें अधिक ओवरहेड है, लेकिन ऐसा लगता है, SQL सर्वर में इस तरह के अनुकूलन को पूरा करने का कोई अन्य सामान्य तरीका नहीं है।

 create table #result (x int); declare @current int; select top (1) @current = a from group_by_table order by a; while @@rowcount > 0 begin insert into #result values (@current); select top (1) @current = a from group_by_table where a > @current order by a; end; select count(*) from #result;

आइए अब तुलना करें कि ये संशोधित क्वेरी मूल और MySQL की तुलना में कैसे चलती हैं। मैं संशोधित क्वेरीज़ को A1, B1, और C1 के रूप में संदर्भित करूँगा। यहां पूर्ण परिणामों वाली तालिका दी गई है.

निष्कर्ष

परिणाम बहुत स्पष्ट हैं, लूज़ स्कैन एक बेहतरीन अनुकूलन है जो इंडेक्स का उपयोग करते समय GROUP BY या DISTINCT प्रश्नों के लिए मूल्यांकन की गई पंक्तियों की संख्या को काफी कम करने में मदद करता है।

हालाँकि PostgreSQL आपको MySQL के समान प्रभावशीलता के साथ कम कार्डिनैलिटी कॉलम को संभालने के लिए जटिल पुनरावर्ती क्वेरी लिखने की अनुमति देता है, लेकिन इसमें कॉलम A पर एक महत्वपूर्ण प्रदर्शन जुर्माना है, जहां कार्डिनैलिटी अधिक है।

एमएस एसक्यूएल सर्वर कॉलम ए पर दूसरों की तुलना में बेहतर प्रदर्शन करता है, लेकिन स्पष्ट रूप से किसी भी अन्य मामले में खराब प्रदर्शन करता है, लेकिन, निश्चित रूप से, कुछ समाधान अभी भी मूल क्वेरी से बेहतर हैं।

मुझे उम्मीद है कि PostgreSQL और MS SQL सर्वर अगले संस्करणों में कभी-कभी स्किप स्कैन ऑप्टिमाइज़ेशन लागू करेंगे।