Query Optimization (স্লো কুয়েরি ফাস্ট করার কৌশল)
একজন জুনিয়র ডেভেলপার খুশি হয় যখন তার কুয়েরি রান করে এবং সঠিক ডেটা দেয়।
একজন সিনিয়র ডেভেলপার তখনই খুশি হন যখন সেই কুয়েরিটি 0.0X সেকেন্ডে এক্সিকিউট হয় এবং সার্ভারের রিসোর্স কম ব্যবহার করে।
এই অধ্যায়ে আমরা শিখব কীভাবে MySQL-এর ব্রেইন (Optimizer) কাজ করে এবং কীভাবে স্লো কুয়েরি ডিবাগ করে রকেটের গতিতে চালানো যায়।
৯.১ MySQL Query Execution Flow (কুয়েরি কীভাবে প্রসেস হয়?)
আমরা যখন একটি কুয়েরি হিট করি, তখন ভেতরে ৪টি ধাপে কাজ হয়:
১. Parser (সিনট্যাক্স চেকার)
প্রথমে দেখে আপনার SQL ঠিক আছে কি না (বানান ভুল, কমা মিসিং ইত্যাদি)।
২. Optimizer (দ্য মাস্টারমাইন্ড)
এটিই সবচেয়ে গুরুত্বপূর্ণ ধাপ। অপটিমাইজার সিদ্ধান্ত নেয়:
- কোন ইনডেক্স ব্যবহার করবে?
- কোন টেবিল আগে জয়েন করবে?
- ফুল টেবিল স্ক্যান করবে নাকি রেঞ্জ স্ক্যান?
৩. Executor
অপটিমাইজারের প্ল্যান অনুযায়ী কাজ শুরু করে।
৪. Storage Engine (InnoDB)
ডিস্ক বা মেমোরি থেকে আসল ডেটা তুলে আনে।
🎯 লক্ষ্য: আমাদের কাজ হলো Optimizer-কে সাহায্য করা যাতে সে সঠিক ইনডেক্সটি বেছে নিতে পারে।
৯.২ EXPLAIN — কুয়েরির পোস্টমর্টেম রিপোর্ট
আপনার কুয়েরি কেন স্লো? এটা বোঝার একমাত্র উপায় হলো কুয়েরির আগে EXPLAIN কিওয়ার্ড বসানো।
EXPLAIN SELECT * FROM users WHERE email = 'shagor@gmail.com';রেজাল্টের গুরুত্বপূর্ণ কলামগুলো:
- type: (সবচেয়ে গুরুত্বপূর্ণ) এটি বলে ডেটা কীভাবে খোঁজা হচ্ছে।
- key: কোন ইনডেক্স ব্যবহার করা হচ্ছে।
- rows: আনুমানিক কতগুলো রো স্ক্যান করে রেজাল্ট পাওয়া যাবে।
- Extra: অতিরিক্ত তথ্য (যেমন Using index, Using filesort)।
৯.৩ Access Types (Speed Ranking) — ফাস্ট থেকে স্লো
type কলামে নিচের ভ্যালুগুলো দেখা যায়। আমাদের লক্ষ্য সবসময় তালিকার ওপরের দিকে থাকা।
| Type | অর্থ | গতি | উদাহরণ |
|---|---|---|---|
| system / const | মাত্র ১টি রো পড়ছে (PK/Unique Key দিয়ে সার্চ) | 🚀 Super Fast | WHERE id = 5 |
| eq_ref | জয়েন করার সময় প্রাইমারি কি দিয়ে ম্যাচিং | 🔥 Fast | JOIN on Primary Key |
| ref | ইনডেক্স আছে কিন্তু ইউনিক নয় (অনেক রো হতে পারে) | ⚡ Fast | WHERE status = 'active' |
| range | নির্দিষ্ট সীমার মধ্যে খোঁজা | ⚠️ Medium | BETWEEN, >, < |
| index | পুরো ইনডেক্স ট্রি স্ক্যান করা | 🐌 Slow | COUNT(*) |
| ALL | পুরো টেবিল স্ক্যান (Full Table Scan) | 💀 Very Slow | ইনডেক্স ছাড়া সার্চ |
🎯 Senior Goal: সবসময় চেষ্টা করবেন ref, eq_ref বা const এর মধ্যে থাকতে। ALL দেখলে বুঝবেন বিপদ!
৯.৪ EXPLAIN এনালাইসিস (Example)
❌ খারাপ কুয়েরি (Bad Query):
EXPLAIN SELECT * FROM orders WHERE user_id = 10;
-- Result: type = ALL, rows = 500000 (৫ লক্ষ), key = NULLমানে: ইনডেক্স নেই, তাই ৫ লক্ষ রো চেক করছে।
✅ অপটিমাইজেশন:
CREATE INDEX idx_user_id ON orders(user_id);ভালো কুয়েরি (Good Query):
EXPLAIN SELECT * FROM orders WHERE user_id = 10;
-- Result: type = ref, rows = 5, key = idx_user_idমানে: এখন মাত্র ৫টি রো চেক করেই ডেটা পেয়ে গেছে। বিশাল ইম্প্রুভমেন্ট!
৯.৫ COVERING INDEX (সবচেয়ে দ্রুততম কুয়েরি)
সাধারণত ইনডেক্স থেকে আইডি নিয়ে মেইন টেবিলে গিয়ে বাকি ডেটা আনতে হয়। কিন্তু যদি আপনার প্রয়োজনীয় সব কলাম ইনডেক্সের ভেতরেই থাকে, তবে মেইন টেবিলে যাওয়ার দরকার নেই। একে Covering Index বলে।
Index: (email, name)
Query:
SELECT name FROM users WHERE email = 'x@gmail.com';Explain Output: Extra: Using index
মানে: এটি ডিস্কের মেইন ডেটা ফাইলে যায়নি, ইনডেক্স থেকেই উত্তর দিয়ে দিয়েছে। এটি সুপার ফাস্ট।
৯.৬ SELECT * পরিহার করুন (Strict Rule)
❌ কেন SELECT * খারাপ?
- Covering Index নষ্ট হয়: ইনডেক্সে সব কলাম থাকে না, তাই মেইন টেবিলে যেতেই হয়।
- I/O Load: অপ্রয়োজনীয় কলাম (যেমন বড় টেক্সট) মেমোরি দখল করে।
- Network Bandwidth: সার্ভার থেকে অ্যাপে ডেটা পাঠাতে সময় বেশি লাগে।
✅ সঠিক নিয়ম:
শুধু যা দরকার, তা সিলেক্ট করুন।
SELECT id, name, email FROM users WHERE id = 10;৯.৭ ORDER BY অপটিমাইজেশন
ORDER BY খুব এক্সপেন্সিভ অপারেশন যদি ইনডেক্স না থাকে। ইনডেক্স ছাড়া সর্ট করতে গেলে MySQL filesort ব্যবহার করে যা খুব স্লো।
✅ Fast:
-- created_at কলামে ইনডেক্স থাকতে হবে
SELECT * FROM users ORDER BY created_at DESC LIMIT 20;❌ Slow (Filesort):
-- city তে ইনডেক্স নেই
SELECT * FROM users ORDER BY city DESC;💡 টিপস: যে কলামে ORDER BY করবেন, সেটিতে ইনডেক্স রাখুন।
৯.৮ LIMIT & Pagination Optimization (Keyset Pagination)
আমরা সাধারণত পেজিনেশন করি এভাবে:
-- ১০ লক্ষ ১ নম্বর রো থেকে ২০টা ডেটা দাও
LIMIT 1000000, 20;MySQL-কে প্রথম ১০ লক্ষ রো পড়তে হয়, তারপর ফেলে দিয়ে শেষের ২০টা দিতে হয়। এটি মারাত্মক স্লো।
✅ Senior Solution (Keyset Pagination / Cursor Based):
আমরা OFFSET বাদ দিয়ে লাস্ট আইডির পরের ডেটা খুঁজব।
-- শেষ যে আইডি লোড হয়েছিল (ধরুন 1000000)
SELECT * FROM orders
WHERE id < 1000000
ORDER BY id DESC
LIMIT 20;এটি সবসময় সমান সময়ে (O(1)) কাজ করবে, তা পেজ ১ হোক বা ১০০০।
৯.৯ Subquery vs JOIN
MySQL-এর পুরোনো ভার্সনে Subquery খুব স্লো ছিল। যদিও MySQL 8.0 অনেক ইম্প্রুভ করেছে, তবুও JOIN সাধারণত বেশি স্টেবল পারফরম্যান্স দেয়।
❌ Avoid (Dependent Subquery):
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);✅ Use (JOIN):
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id;৯.১০ Full Table Scan এড়ানোর উপায় (Checklist)
কুয়েরি লেখার সময় এই ভুলগুলো করলে ইনডেক্স কাজ করবে না:
- ❌ইনডেক্সড কলামে ফাংশন ব্যবহার:
WHERE DATE(created_at) = '2025-01-01' - ✅
WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59' - ❌টাইপ মিসম্যাচ: স্ট্রিং কলামে নম্বর দিয়ে সার্চ (phone = 017... vs phone = '017...')
- ❌শুরুতে ওয়াইল্ডকার্ড:
LIKE '%ra'(ইনডেক্স কাজ করে না) - ✅
LIKE 'ra%'(ইনডেক্স কাজ করে) - ❌Negative Search: != বা <> বা NOT IN সাধারণত ইনডেক্স ব্যবহার করে না।
৯.১১ Query Rewrite Techniques
কখনও কখনও লজিক একটু ঘুরিয়ে লিখলে পারফরম্যান্স অনেক বাড়ে।
❌ WHERE price + tax > 100 (ক্যালকুলেশন রান টাইমে হচ্ছে)
✅ WHERE price > 100 - tax (যদি tax কনস্ট্যান্ট হয়) অথবা total_price নামে কলাম স্টোর করে রাখা।
❌ WHERE status != 'active' (ইফিসিয়েন্ট নয়)
✅ WHERE status IN ('inactive', 'banned') (যদি স্ট্যাটাস কম থাকে)
৯.১২ N+1 Query Problem (Developers Must Know)
এটি ডেটাবেসের সমস্যা নয়, কোডিং প্যাটার্ন সমস্যা।
ধরুন আপনি ৫০ জন ইউজার আনলেন, তারপর লুপ চালিয়ে প্রত্যেকের জন্য আলাদা কুয়েরি করে তাদের প্রোফাইল আনলেন।
- ১টা কুয়েরি (Users)
- ৫০টা কুয়েরি (Profile for each user)
- মোট ৫১টা কুয়েরি!
✅ সমাধান: JOIN ব্যবহার করে এক কুয়েরিতে সব ডেটা আনুন অথবা অ্যাপ্লিকেশনে Eager Loading (Laravel/Node.js) ব্যবহার করুন।
৯.১৩ Slow Query Log (ডিবাগিং টুল)
প্রোডাকশনে কোন কুয়েরি স্লো কাজ করছে তা বের করতে কনফিগারেশন অন করুন:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- ১ সেকেন্ডের বেশি লাগলে লগ হবেএরপর লগ ফাইলে (/var/lib/mysql/slow.log) গিয়ে দেখুন ভিলেন কুয়েরি কোনটি।
🎯 অধ্যায় ৯ এর সারাংশ (Summary)
এই অধ্যায়ে আপনি শিখলেন:
- ✓EXPLAIN: কুয়েরি প্ল্যান বোঝা এবং type (ALL vs REF) চেক করা।
- ✓Indexing: Covering Index এবং ফাংশনাল ইনডেক্স এড়ানোর গুরুত্ব।
- ✓Optimization: ORDER BY, LIMIT এবং JOIN অপটিমাইজ করার টেকনিক।
- ✓Pagination: কেন OFFSET বাদ দিয়ে Keyset Pagination ব্যবহার করবেন।
- ✓Debugging: স্লো কুয়েরি লগ এবং N+1 প্রবলেম সলভিং।
✨ এখন আপনি শুধু কুয়েরি লিখবেন না, কুয়েরি ডিজাইন করবেন। পরবর্তী অধ্যায়ে আমরা শিখব Stored Procedures & Functions, Triggers এবং Views—যেখানে আমরা শিখব ডেটাবেসের ভেতরেই লজিক লিখে অটোমেশন করার পদ্ধতি।
প্রস্তুত তো? 🚀