Handling Large Data (বিশাল ডেটা অপটিমাইজেশন)

আপনার টেবিলে ১০ হাজার ডেটা থাকলে ভুল কুয়েরি লিখলেও পারফরম্যান্স টের পাওয়া যায় না। কিন্তু যখন ১০ কোটি (100 Million+) ডেটা থাকে, তখন একটি ভুল JOIN বা ইনডেক্স মিসিং হলে সার্ভার ক্র্যাশ করতে পারে।

এই অধ্যায়ে আমরা শিখব কীভাবে "Big Data" হ্যান্ডেল করতে হয় এবং সার্ভারকে জীবিত রাখতে হয়।

১৮.১ কোর সমস্যাসমূহ (The 10M+ Row Problem)

যখন টেবিলের আকার বিশাল হয়, তখন সাধারণ অপারেশনগুলো দুঃস্বপ্ন হয়ে দাঁড়ায়:

🐌 Slow Reads

বাফার পুলে সব ডেটা ধরে না, তাই ডিস্ক I/O বেড়ে যায়।

✍️ Slow Writes

ইনডেক্স ট্রি (B+ Tree) অনেক বড় হওয়ায় প্রতি ইনসার্টে রি-ব্যালেন্স করতে সময় লাগে।

🔧 Maintenance Hell

একটি কলাম অ্যাড করতে (ALTER TABLE) কয়েক ঘণ্টা বা দিন লেগে যেতে পারে।

💾 Backup Delay

ব্যাকআপ নিতে এবং রিস্টোর করতে অনেক সময় লাগে।

১৮.২ Full Table Scan এড়ানো (Golden Rule)

বিশাল টেবিলে Full Table Scan মানে মৃত্যু।

❌ খারাপ কুয়েরি:

-- status কলামে ইনডেক্স না থাকলে ১০ কোটি রো চেক করবে
SELECT * FROM orders WHERE status = 'pending';

✅ সমাধান:

  • Index: WHERE ক্লজে ব্যবহৃত কলামে অবশ্যই ইনডেক্স থাকতে হবে।
  • Covering Index: যদি সম্ভব হয়, SELECT এর কলামগুলোও ইনডেক্সে রাখুন যাতে মেইন টেবিলে যেতে না হয়।

১৮.৩ ইনডেক্সিং স্ট্র্যাটেজি (Massive Scale)

ছোট টেবিলে সিঙ্গেল ইনডেক্স কাজ করে, কিন্তু বড় টেবিলে Composite Index এবং Column Order খুব গুরুত্বপূর্ণ।

Scenario: আমরা প্রায়ই স্ট্যাটাস এবং তারিখ দিয়ে সার্চ করি।

SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2024-01-01';

❌ ভুল ইনডেক্স:

  • INDEX(status): শুধু স্ট্যাটাস ফিল্টার করবে, তারিখের জন্য স্ক্যান করবে।
  • INDEX(created_at): শুধু তারিখ ফিল্টার করবে।

✅ সঠিক ইনডেক্স (Left-most Prefix):

INDEX(status, created_at): প্রথমে স্ট্যাটাস দিয়ে কমাবে, তারপর সেই রেঞ্জের তারিখ বের করবে। এটি সবচেয়ে ইফিসিয়েন্ট।

১৮.৪ Archival Strategy (Hot vs Cold Data)

সব ডেটা মেইন টেবিলে রাখার দরকার নেই। ১ বছরের পুরোনো অর্ডারের স্ট্যাটাস কেউ চেক করে না।

🔥 Hot Data

গত ৬ মাসের ডেটা (দ্রুত এক্সেস দরকার)

❄️ Cold Data

৬ মাসের পুরোনো ডেটা (রিপোর্টিং বা অডিটের জন্য দরকার)

💡 কৌশল: প্রতি মাসে একটি ক্রন জব (Cron Job) চালিয়ে পুরোনো ডেটা আর্কাইভ টেবিলে সরিয়ে ফেলুন।

-- ১. আর্কাইভ টেবিলে কপি করা
INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < '2023-01-01';

-- ২. মেইন টেবিল থেকে ডিলিট করা
DELETE FROM orders WHERE created_at < '2023-01-01';

⚠️ টিপস: ডিলিট করার সময় ছোট ছোট ব্যাচে (LIMIT 1000) করবেন যাতে টেবিল লক না হয়।

১৮.৫ Soft Delete এর বিপদ

আমরা প্রায়ই deleted_at বা is_deleted = 1 ব্যবহার করি। ছোট টেবিলে এটি ঠিক আছে। কিন্তু বিশাল টেবিলে এটি পারফরম্যান্স কিলার।

⚠️ সমস্যা:

  • ইনডেক্স সাইজ কমে না (বরং বাড়ে)
  • কুয়েরিতে সবসময় WHERE is_deleted = 0 লিখতে হয়, যা ইনডেক্স লুকআপকে জটিল করে

✅ সিনিয়র সলিউশন:

ডিলিট না করে ডেটাকে deleted_users বা trash টেবিলে মুভ করে ফেলুন। মেইন টেবিল ক্লিন থাকবে।

১৮.৬ Pagination Optimization (The Offset Problem)

❌ সমস্যা:

LIMIT 1000000, 20;

MySQL প্রথম ১০,০০,০০০ রো পড়ে ফেলে দেয়, তারপর ২০টি দেয়। এটি সার্ভার হ্যাং করতে পারে।

✅ সমাধান (Keyset / Cursor Based Pagination):

আমরা OFFSET বাদ দিয়ে লাস্ট আইডির রেফারেন্স ব্যবহার করব।

-- গত পেজের শেষ আইডি ছিল 1000000
SELECT * FROM orders 
WHERE id < 1000000 
ORDER BY id DESC 
LIMIT 20;

এটি সবসময় মিলি-সেকেন্ডে রেজাল্ট দেবে, ডেটা যত বড়ই হোক।

১৮.৭ Summary Tables (Poor Man's Materialized View)

রিয়েল-টাইম ড্যাশবোর্ডের জন্য বিশাল টেবিল এগ্রিগেট করা বোকামি।

Query: SELECT SUM(amount) FROM orders (১০ কোটি রো যোগ করা সময়সাপেক্ষ)

✅ সমাধান:

আলাদা একটি daily_sales টেবিল রাখুন।
Event বা Cron দিয়ে প্রতি ঘণ্টায় বা দিনে একবার ডেটা ক্যালকুলেট করে এখানে রাখুন।
ড্যাশবোর্ডে সরাসরি এই ছোট টেবিল থেকে কুয়েরি করুন।

১৮.৮ FULLTEXT Search (LIKE এর বিকল্প)

LIKE '%samsung%' ইনডেক্স ব্যবহার করতে পারে না। এর বদলে FULLTEXT Index ব্যবহার করুন। 

CREATE FULLTEXT INDEX idx_search ON products(name, description);

-- সার্চ কুয়েরি
SELECT * FROM products 
WHERE MATCH(name, description) AGAINST('samsung phone' IN BOOLEAN MODE);

এটি অনেক ফাস্ট এবং গুগল স্টাইলে সার্চ রেজাল্ট দেয়।

১৮.৯ Bulk Load Optimization

যখন অন্য ডেটাবেস থেকে মাইগ্রেট করবেন বা একবারে লাখ লাখ ডেটা ঢোকাবেন:

  • INSERT লুপ বাদ দিন: একটি একটি করে ইনসার্ট না করে LOAD DATA INFILE ব্যবহার করুন
  • ইনডেক্স ডিজেবল: ডেটা ঢোকানোর আগে DISABLE KEYS করুন, ঢোকানোর পর ENABLE KEYS করুন
LOAD DATA INFILE '/tmp/users.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '
';

১৮.১০ Zero Downtime Schema Change (ALTER TABLE)

বিশাল টেবিলে ALTER TABLE দিলে টেবিল লক হয়ে যায়। সাইট ডাউন থাকে।

🛠️ pt-online-schema-change (Percona)

এটি ব্যাকগ্রাউন্ডে নতুন স্ট্রাকচারের একটি কপি টেবিল বানায়, ডেটা সিঙ্ক করে এবং শেষে নাম অদলবদল (Swap) করে দেয়। কোনো লক হয় না।

🚀 gh-ost (GitHub)

গিটহাবের তৈরি টুল, যা বিনলগ (Binlog) ব্যবহার করে স্কিমা চেঞ্জ করে।

১৮.১১ Queue Table Design

কিউ বা জব টেবিলে প্রচুর ইনসার্ট এবং ডিলিট হয়। এতে টেবিলে প্রচুর "গর্ত" (Fragmentation) তৈরি হয় এবং ইনডেক্স স্লো হয়ে যায়।

✅ পার্টিশন ব্যবহার করুন।

  • আজকের জবের জন্য p_today
  • কাজ শেষ হলে DELETE না করে পুরো পার্টিশন DROP করে দিন (যদি হিস্ট্রি না লাগে) অথবা আর্কাইভ করুন

১৮.১২ Server Config Tuning (Hardware Level)

বিগ ডেটার জন্য ডিফল্ট কনফিগ কাজ করে না।

  • 📦Buffer Pool: র‍্যামের ৭০-৮০% দিন (innodb_buffer_pool_size)
  • 📝Log File: innodb_log_file_size বাড়ান (যেমন ১ জিবি), যাতে ঘন ঘন ডিস্কে ফ্লাশ করতে না হয়
  • 💾Storage: অবশ্যই SSD/NVMe ব্যবহার করুন। HDD দিয়ে বিগ ডেটা হ্যান্ডেল করা অসম্ভব

❓ Interview Q&A

Q: ১ বিলিয়ন রো থেকে নির্দিষ্ট ডেটা কীভাবে ফাস্ট আনবেন?

প্রথমে দেখব কুয়েরিটি ইনডেক্স ব্যবহার করছে কি না। তারপর পার্টিশন প্রুনিং (Pruning) হচ্ছে কি না দেখব। যদি তাও স্লো হয়, তবে দেখব আর্কিটেকচারাল চেঞ্জ (যেমন: শার্ডিং বা ইলাস্টিক সার্চে মুভ করা) দরকার কি না।

Q: COUNT(*) কেন স্লো এবং সমাধান কী?

InnoDB তে এটি স্লো কারণ MVCC এর জন্য প্রতিবার গুনতে হয়। সমাধান হলো অ্যাপ্লিকেশনে ক্যাশ (Redis) ব্যবহার করা অথবা সামারি টেবিল মেইনটেইন করা। রিয়েল-টাইম কাউন্ট দরকার না হলে SHOW TABLE STATUS এর Rows ভ্যালু (আনুমানিক) দেখানো যেতে পারে।

🎯 অধ্যায় ১৮ এর সারাংশ (Summary)

এই অধ্যায়ে আমরা শিখলাম:

  • ✓Avoid Full Table Scan: WHERE ক্লজের কলামে ইনডেক্স বাধ্যতামূলক
  • ✓Composite Index: (status, created_at) - সবচেয়ে নির্বাচনী কলাম আগে দিন
  • ✓Hot/Cold Data: পুরোনো ডেটা আর্কাইভ টেবিলে সরান
  • ✓Keyset Pagination: OFFSET বাদ দিন - WHERE id < last_id
  • ✓Zero Downtime Schema Change: pt-online-schema-change বা gh-ost ব্যবহার করুন
  • ✓Cold Data Archiving: ৬ মাসের পুরোনো ডেটা আর্কাইভে সরান

✨ এই অধ্যায়ের টেকনিকগুলো ফলো করলে আপনার ডেটাবেস ১০০ মিলিয়ন রো-এর পরেও বাটার স্মুথ কাজ করবে! 🚀

🔒 কপিরাইট সুরক্ষিত কন্টেন্ট 🔒

কপি, স্ক্রিনশট, প্রিন্ট করা সম্পূর্ণ নিষিদ্ধ।