মাইএসকিউএল লুজ স্ক্যান অপ্টিমাইজেশান: পোস্টগ্রেএসকিউএল এবং এমএসএসকিউএল-এর বিরুদ্ধে একটি তুলনামূলক কর্মক্ষমতা মূল্যায়ন

একটি COUNT DISTINCT বা SELECT DISTINCT ক্লজ সন্তুষ্ট করার সবচেয়ে সাধারণ উপায় হল পুরো টেবিল বা সূচকটি স্ক্যান করা এবং এটি থেকে শুধুমাত্র স্বতন্ত্র মানগুলি বের করা৷ এই অপারেশনে 2টি মৌলিক কৌশল থাকতে পারে। অবশ্যই, কিছু ডাটাবেস ইঞ্জিন আনুমানিক গণনার জন্য HyperLogLog অ্যালগরিদমকেও সমর্থন করে, কিন্তু আমরা এই নিবন্ধে আরও ঐতিহ্যগত পদ্ধতিতে মনোনিবেশ করব। এছাড়াও, এই নিবন্ধে আমি "গ্রুপ বাই" বা "গ্রুপিং" শব্দটি ব্যবহার করব COUNTটি আলাদা অপারেশনকেও উপস্থাপন করতে, কারণ নিম্নলিখিত প্রশ্নগুলি শব্দার্থগতভাবে সমান।

 select count(distinct A) from group_by_table; select count(*) from (select a from group_by_table group by a) as tmp;

হ্যাশ অ্যাগ্রিগেশন

এই কৌশলটি সাধারণত ব্যবহৃত হয় যখন একটি ক্যোয়ারীকে একটি নন-ইনডেক্সড কলামে মানগুলিকে গোষ্ঠীভুক্ত করার প্রয়োজন হয়। এই প্রক্রিয়া চলাকালীন, একটি হ্যাশ টেবিল তৈরি করা হয়, যেখানে কীগুলি কলামের মানগুলির অনন্য সমন্বয়কে উপস্থাপন করে। ডাটাবেস ইঞ্জিন সারিগুলির মাধ্যমে স্ক্যান করার সময়, এটি প্রতিটি সারির কলামের মানের জন্য হ্যাশ মান গণনা করে এবং হ্যাশ বালতিতে একই হ্যাশ সহ প্রকৃত মান সংরক্ষণ করে। যদিও এই পদ্ধতিটি বড় ডেটাসেটের জন্য মেমরি-নিবিড় হতে পারে, এটি প্রায়শই দ্রুততম পদ্ধতি হয় যখন সার্ভারে হ্যাশ টেবিল সংরক্ষণ করার জন্য যথেষ্ট মেমরি থাকে।

স্ট্রিম একত্রীকরণ

স্ট্রিম অ্যাগ্রিগেশন ব্যবহার করা হয় যখন গোষ্ঠীভুক্ত করা কলামটি ইতিমধ্যেই সাজানো থাকে বা প্রায় সাজানো থাকে। ডাটা স্ট্রীম আসার সাথে সাথে ডাটাবেস ইঞ্জিন বর্তমান সারি মানকে আগেরটির সাথে তুলনা করে। যদি বর্তমান সারিটি একই গ্রুপের অন্তর্গত হয়, তবে ডাটাবেস ইঞ্জিন একত্রিতকরণ চালিয়ে যায়। যখন মান ভিন্ন হয়, একটি নতুন গ্রুপ শুরু হয়, এবং পূর্ববর্তী গোষ্ঠীর মান কার্যকরী পরিকল্পনায় আরও পাস করা হয়। স্ট্রিম অ্যাগ্রিগেশন হ্যাশ অ্যাগ্রিগেশনের তুলনায় অনেক কম মেমরি ব্যবহার করে (শুধুমাত্র একটি মান সংরক্ষণ করা প্রয়োজন), তবে এটির জন্য ডেটা সাজানো প্রয়োজন, যা ডেটা ইতিমধ্যে সাজানো না থাকলে একটি অতিরিক্ত সাজানোর অপারেশন জড়িত হতে পারে।

কিন্তু এই উভয় কৌশলের জন্যই এখনও একটি সম্পূর্ণ কলাম স্ক্যান প্রয়োজন এবং COUNTটি আলাদা অপারেশনের জন্য একটি ভাল কৌশল রয়েছে, যখন নিম্ন কার্ডিনালিটি সহ কলামগুলির সাথে কাজ করা হয় যা PostgreSQL এবং MS SQL সার্ভারের নেই, কিন্তু MySQL এর আছে।

লুজ স্ক্যান কি?

লুজ স্ক্যান হল একটি উন্নত MySQL অপ্টিমাইজেশান কৌশল যা নির্দিষ্ট GROUP BY অপারেশনের প্রেক্ষাপটে প্রযোজ্য, বিশেষ করে এমন পরিস্থিতিতে যেখানে সারণির মোট সারির সংখ্যার তুলনায় তুলনামূলকভাবে অল্প সংখ্যক সারি প্রক্রিয়া করা হচ্ছে। এই কৌশলটি ডেটাবেস থেকে পড়ার জন্য প্রয়োজনীয় ডেটার পরিমাণ হ্রাস করে প্রশ্নের কার্যকারিতাকে উল্লেখযোগ্যভাবে উন্নত করে।

লুজ স্ক্যানের অন্তর্নিহিত নীতি

সারমর্মে, লুজ স্ক্যান কৌশলটি একটি সাধারণ নীতির উপর কাজ করে: যোগ্যতা সারিগুলির জন্য সমগ্র সূচকটি স্ক্যান করার পরিবর্তে (ওরফে 'টাইট' স্ক্যান), এটি প্রতিটি গ্রুপের জন্য প্রথম ম্যাচিং সারিটির জন্য 'আলগালি' স্ক্যান করে। একটি মিল খুঁজে পাওয়ার পরে, এটি অবিলম্বে পরবর্তী গ্রুপে চলে যায়। এই পদ্ধতিটি মূল্যায়ন করা প্রয়োজন এমন সারির সংখ্যা হ্রাস নিশ্চিত করে, এইভাবে একটি ক্যোয়ারী চালানোর জন্য মোট সময় কমিয়ে দেয়।

মাইএসকিউএল ছাড়াও, অন্যান্য ডাটাবেস ইঞ্জিনেও অনুরূপ কৌশল প্রয়োগ করা হয়। একে বলা হয় "স্কিপ স্ক্যান" ইন ওরাকল , SQLite , এবং তেলাপোকা ডিবি , "জাম্প স্ক্যান" ইন DB2 , এবং "হাইব্রিড স্ক্যান" ইন যুগাবাইটডিবি .

পরিবেশ স্থাপন করা হচ্ছে

আমি অনুমান করি, এটি যথেষ্ট তত্ত্ব, আসুন একটি ব্যবহারিক অংশে চলে যাই এবং MySQL বনাম PostgreSQL এবং Microsoft SQL সার্ভারে লুজ স্ক্যানের একটি তুলনামূলক বিশ্লেষণ করি। আমি আমার ল্যাপটপে MySQL 8.0.33, PostgreSQL 15.3 এবং MS SQL 2022-CU4 সহ সর্বশেষ ডকার কন্টেনার ব্যবহার করব। আমি 1 মিলিয়ন সারি এবং বিভিন্ন কার্ডিনালিটি সহ পূর্ণসংখ্যা ডেটা টাইপের তিনটি কলাম সহ একটি টেবিল তৈরি করব। প্রথম কলামে 100 হাজার অনন্য মান, দ্বিতীয়টিতে 1 হাজার এবং তৃতীয়টিতে শুধুমাত্র 10টি অনন্য মান রয়েছে। আমি তিনটি পৃথক নন-ক্লাস্টারড ইনডেক্স তৈরি করব এবং প্রতিটি কলামে COUNTটি আলাদা প্রশ্ন চালাব। প্রতিটি ক্যোয়ারী শুধুমাত্র ডাটাবেসগুলিকে গরম করার জন্য 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);

ডেটাবেস ইঞ্জিনের কর্মক্ষমতা তুলনা করতে ব্যবহৃত প্রশ্নগুলি:

 select count(*) from (select a from group_by_table group by a) as tmp; -- ~ 100 thousand unique values select count(*) from (select b from group_by_table group by b) as tmp; -- 1000 unique values select count(*) from (select c from group_by_table group by c) as tmp; -- 10 unique values

ফলাফল

সমস্ত ডেটাবেস কলাম A-তে প্রায় একই রকম কাজ করে, যেখানে ডেটা কার্ডিনালিটি বেশি (1 মিলিয়ন সারির মধ্যে 100 হাজার অনন্য মান)। PostgreSQL মোট এক্সিকিউশন সময় ছিল 4.72 সেকেন্ড, MS SQL সার্ভার - 1.42 সেকেন্ড, এবং MySQL - 3.11 সেকেন্ড। কিন্তু কলাম B-এ, যেখানে কার্ডিনালিটি মাত্র 1000টি অনন্য মান, MySQL মোট এক্সিকিউশন টাইম 58.6 মিলিসেকেন্ডে নেমে আসে, যখন PostgreSQL এটি 4.2 সেকেন্ডে এবং MS SQL সার্ভার 1.08 সেকেন্ডে করে। **MySQL দ্বিতীয় কোয়েরিটি PostgreSQL এর চেয়ে 70 গুণ দ্রুত এবং MS SQL সার্ভারের চেয়ে 18 গুণ দ্রুত সম্পন্ন করে। ** কলাম সি-তে পরিস্থিতি আরও ভালো, যেখানে শুধুমাত্র 10টি অনন্য মান রয়েছে: MySQL - 12.5ms, PostgreSQL - 3.33s এবং MS SQL সার্ভার - 1.02s। **শেষ উদাহরণে MySQL অত্যন্ত দ্রুত এবং PostgreSQL-কে 250 বারের বেশি এবং MS SQL সার্ভারকে 80 গুণ বেশি করে ছাড়িয়েছে।
**

নিচে লগারিদমিক স্কেল ব্যবহার করে একটি ভিজ্যুয়ালাইজেশন আছে। এটি 20টি মৃত্যুদণ্ডের পর মোট মৃত্যুদন্ডের সময় দেখায়।

কর্মক্ষমতা উন্নত করতে MS SQL এবং PostgreSQL-এ কী করা যেতে পারে?

PostgreSQL এবং MS SQL সার্ভারে বর্তমানে এই ধরনের অপ্টিমাইজেশনের অভাব থাকলেও, এই ইঞ্জিনগুলিতে আপনার SELECT/COUNTটি আলাদা প্রশ্নের কর্মক্ষমতা উন্নত করার জন্য আপনি একটি কৌশল করতে পারেন। ধারণাটি হল ডিফল্ট পূর্ণ সূচক স্ক্যানের উপর নির্ভর না করে সূচকে বেশ কয়েকটি লুকআপ করা।\

উদাহরণস্বরূপ, 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 হিসাবে উল্লেখ করব। এখানে সম্পূর্ণ ফলাফল সহ সারণী।

উপসংহার

ফলাফলগুলি বেশ সুস্পষ্ট, লুজ স্ক্যান হল একটি দুর্দান্ত অপ্টিমাইজেশান যা সূচী ব্যবহার করার সময় SELECT DISTINCT বা COUNT DISTINCT প্রশ্নের জন্য মূল্যায়ন করা সারিগুলির সংখ্যা উল্লেখযোগ্যভাবে হ্রাস করতে সহায়তা করে৷ যদিও PostgreSQL আপনাকে MySQL এর মতোই কার্যকারিতা সহ কম কার্ডিনালিটি কলামগুলি পরিচালনা করার জন্য জটিল পুনরাবৃত্তিমূলক প্রশ্নগুলি লিখতে দেয়, এটি কলাম A-তে উল্লেখযোগ্য কর্মক্ষমতা পেনাল্টি রয়েছে, যেখানে কার্ডিনালিটি বেশি। এমএস এসকিউএল সার্ভার স্পষ্টতই এই উদাহরণে একজন বহিরাগত, যদিও কিছু সমাধান এখনও মূল প্রশ্নের চেয়ে ভাল।

আমি আশা করি PostgreSQL এবং MS SQL সার্ভার পরবর্তী সংস্করণগুলিতে Skip Scan অপটিমাইজেশন বাস্তবায়ন করবে।