MySQL Storage Engines & InnoDB Internals

গাড়ির বডি দেখতে যেমনই হোক, আসল শক্তি থাকে তার ইঞ্জিনে। MySQL-এর ক্ষেত্রেও তাই। আপনি SQL কমান্ড দেন, কিন্তু সেই ডেটা ডিস্কে কীভাবে রাখা হবে, কীভাবে লক করা হবে বা ক্র্যাশ করলে কীভাবে রিকভার করা হবে—এসব ঠিক করে Storage Engine।

MySQL-এর সবচেয়ে বড় সুবিধা হলো এটি Pluggable Storage Engine সাপোর্ট করে। অর্থাৎ, আপনি চাইলে টেবিল অনুযায়ী ইঞ্জিন বদলাতে পারেন।

১২.১ Storage Engine কী?

MySQL আর্কিটেকচার প্রধানত ৩ স্তরের:

🔝 Top Layer: ক্লায়েন্ট কানেকশন হ্যান্ডলিং।

🧠 Middle Layer: কুয়েরি পার্সার এবং অপটিমাইজার (মস্তিষ্ক)।

🖐️ Storage Engine Layer: ডেটা রিড/রাইট করা (হাত-পা)।

জনপ্রিয় ইঞ্জিনসমূহ:

  • InnoDB: ডিফল্ট এবং প্রোডাকশন স্ট্যান্ডার্ড।
  • MyISAM: পুরনো, এখন আর ব্যবহার করা উচিত নয়।
  • Memory: র‍্যাম ভিত্তিক, সুপার ফাস্ট কিন্তু টেম্পোরারি।

১২.২ কেন InnoDB সেরা? (Why InnoDB is King?)

MySQL 5.5 এর পর থেকে InnoDB ডিফল্ট ইঞ্জিন। কারণ এটি সাপোর্ট করে:

✅ ACID Transactions

ব্যাংকিং ট্রানজেকশনের জন্য নিরাপদ।

✅ Row-Level Locking

একজন ইউজার কাজ করলে পুরো টেবিল লক হয় না।

✅ Crash Recovery

সার্ভার ক্র্যাশ করলেও ডেটা ঠিক থাকে।

✅ Foreign Keys

রিলেশনশিপ মেইনটেইন করে।

১২.৩ InnoDB vs MyISAM (Most Common Interview Question)

জুনিয়র ডেভেলপাররা অনেক সময় MyISAM ব্যবহার করে কারণ এটি সেটআপে সিম্পল মনে হয়, কিন্তু প্রোডাকশনে এটি বিপদজনক।

বৈশিষ্ট্যInnoDBMyISAM
Transaction✅ হ্যাঁ (ACID সাপোর্ট করে)❌ না
Locking✅ Row-Level (হাই কনকারেন্সি)❌ Table-Level (স্লো)
Foreign Key✅ সাপোর্ট করে❌ সাপোর্ট করে না
Crash Recovery✅ অটোমেটিক রিকভারি হয়❌ ডেটা করাপ্ট হওয়ার রিস্ক থাকে
Data Storageক্লাস্টার্ড ইনডেক্স (PK এর সাথে থাকে)হিপ স্টোরেজ (ছড়ানো ছিটানো)
Best Useপ্রোডাকশন অ্যাপ (User, Order, Payment)লগিং বা রিড-অনলি ডেটা

💡 Senior Advice: আপনার প্রজেক্টে চোখ বন্ধ করে InnoDB ব্যবহার করুন। MyISAM এখন ইতিহাস।

১২.৪ InnoDB Internal Architecture (Buffer Pool - সহজ ভাষায়)

এটি বুঝলে আপনি সত্যিকারের সিনিয়র ইঞ্জিনিয়ার। InnoDB এর আর্কিটেকচার বুঝতে প্রথমে Buffer Pool কে ভালোভাবে বোঝা দরকার।

📚 গল্প দিয়ে Buffer Pool বোঝা (লাইব্রেরি উদাহরণ)

মনে করুন, আপনি একটি বিশাল লাইব্রেরির লাইব্রেরিয়ান (ডেটাবেস) — লাইব্রেরিতে লক্ষ লক্ষ বই আছে (ডেটা), যা মূলত গুদামে (হার্ড ডিস্ক) রাখা। এগুলো খুব ধীরগতিতে পাওয়া যায়।

🛑 সমস্যা (Buffer Pool ছাড়া):

প্রতি বার দর্শনার্থী যখন কোনো বই চাইবে, আপনাকে দৌড়ে গুদামে (ডিস্কে) গিয়ে বই আনতে হবে। এতে দর্শনার্থীর (অ্যাপ/ইউজার) জন্য অপেক্ষা করতে হবে, যা খুবই স্লো।

✨ সমাধান (Buffer Pool - The Superfast Desk):

Buffer Pool হলো লাইব্রেরির রিডিং রুমের টেবিল (RAM-এ রাখা জায়গা)।

📖 কিভাবে কাজ করে?

  1. পড়ার সময় (Read): কেউ প্রথমবার "হিমু" বই চাইলে, আপনি গুদামে গিয়ে বই এনে রিডিং রুমের টেবিলে (Buffer Pool) রেখে দিলেন। দ্বিতীয়বার কেউ চাইলে, আপনাকে আর গুদামে যেতে হবে না। টেবিল (RAM) থেকে সাথে সাথে বইটা দিয়ে দিতে পারেন। এটাই Caching (দারুণ দ্রুত)।
  2. লেখার সময় (Dirty Page): কেউ এসে বইয়ের একটি পৃষ্ঠায় কলম দিয়ে লিখে দিল (UPDATE command)। এখন টেবিলের (RAM-এর) বইটা পরিবর্তিত হয়েছে, কিন্তু গুদামের আসল বইটা এখনও আগের মতোই আছে। এই অবস্থায়, RAM-এর পরিবর্তিত পৃষ্ঠাটিকে "Dirty Page" (নোংরা পাতা) বলা হয়। কারণ এটি আসল গুদামের সাথে মেলে না।
  3. গুদাম আপডেট করা (Flushing): লাইব্রেরিয়ান (MySQL) যখন একটু ফ্রি হয়, অথবা টেবিলে জায়গা কমে গেলে, তখন চুপিচুপি "Dirty Page"-গুলোর লেখাগুলো গুদামের (ডিস্কের) আসল বইয়েও লিখে দেয়।

🔄 সার্ভার ক্র্যাশ হলে কী হয়? (Crash Recovery)

ধরুন, আপনি লিখলেন (Dirty Page তৈরি হলো), কিন্তু গুদামে আপডেট করার আগেই বিদ্যুৎ চলে গেল।

  • Buffer Pool (RAM) এর ডেটা: মুছে গেল (কারণ RAM ভোলাটে)।
  • আসল ডেটা (ডিস্কে): এখনো পুরনো অবস্থায় আছে।

তাহলে কি আপনার লেখাটা হারিয়ে গেল? না! এখানেই Redo Log বাঁচায়। আপনি লিখার সময় লাইব্রেরিয়ান একটি খাতায় (Redo Log ফাইল) টুকে রেখেছিলেন, "পৃষ্ঠা নং ৫ এ 'ক' লিখো"। বিদ্যুৎ চলে গেলেও, সেই খাতাটি কিন্তু ডিস্কে (গুদামের পাশে) সেভ থাকে। চালু করার পর সেই খাতা দেখে বুঝে যায়, "ওহ! আমি তো ৫ নম্বর পৃষ্ঠাটা লিখতে যাচ্ছিলাম" - এবং লিখে ফেলে।

InnoDB আর্কিটেকচার (RAM ↔ Disk)

┌─────────────────────────────────────┐

│ Buffer Pool (RAM) │

│ 🔥 Cached Data + Dirty Pages │

├─────────────────────────────────────┤

│ Redo Log │ Undo Log │ Doublewrite │

│ (Disk - Safe Storage) │

└─────────────────────────────────────┘

📝 সংক্ষেপে (TL;DR)

  • Buffer Pool = RAM-এর ক্যাশ (দ্রুত পড়া/লেখার জন্য)
  • Dirty Page = RAM-এ আপডেট হয়েছে, কিন্তু ডিস্কে আপডেট হয়নি (অমিল অবস্থা)
  • Redo Log = ব্যাকআপ খাতা (বিদ্যুৎ গেলেও যাতে ডেটা না হারায়)

💡 Senior Level Tip (Performance Optimization)

আপনি যদি innodb_buffer_pool_size কে আপনার সার্ভারের মোট RAM-এর 70-80% বরাদ্দ দিয়ে দেন, তাহলে প্রায় সব কুয়েরি কখনো ডিস্কে যাবে না, RAM থেকেই রেসপন্স দেবে। তখন আপনার ডেটাবেস রকেটের গতি পাবে! 🚀

১২.৫ Buffer Pool (The RAM Workspace)

Buffer Pool হলো র‍্যামের একটি অংশ যেখানে InnoDB ডেটা এবং ইনডেক্স ক্যাশ (Cache) করে রাখে।

  • যখন ডেটা রিড হয়: ডিস্ক থেকে না এনে প্রথমে বাফার পুল চেক করে।
  • যখন ডেটা রাইট হয়: সরাসরি ডিস্কে রাইট না করে প্রথমে বাফার পুলে রাইট করে (একে Dirty Page বলে)। পরে ব্যাকগ্রাউন্ড প্রসেস এটি ডিস্কে সেভ করে।

⚙️ Config Tip: ডেডিকেটেড ডেটাবেস সার্ভারে innodb_buffer_pool_size মোট র‍্যামের ৭০-৮০% রাখা উচিত। এটি পারফরম্যান্সের মেইন চাবিকাঠি।

১২.৬ Clustered Index (ডেটা সাজানোর পদ্ধতি)

অন্যান্য ডেটাবেসে ডেটা এবং ইনডেক্স আলাদা থাকে। কিন্তু InnoDB-তে Primary Key এর ইনডেক্স এবং আসল ডেটা একসাথেই থাকে। একে Clustered Index বলে।

✅ সুবিধা: প্রাইমারি কি দিয়ে সার্চ করলে সবচেয়ে দ্রুত ডেটা পাওয়া যায়।

💡 টিপস: প্রাইমারি কি সবসময় ছোট রাখুন (BIGINT is best)। UUID প্রাইমারি কি হলে ইনডেক্স এলোমেলো হয়ে যায় এবং পারফরম্যান্স ড্রপ করে।

১২.৭ Redo Log (Crash Recovery - The Savior)

ধরুন, ইউজার টাকা পাঠাল, সিস্টেমে COMMIT হলো, কিন্তু ডিস্কে সেভ হওয়ার আগেই কারেন্ট চলে গেল। ডেটা কি হারাবে? না!

Redo Log এর কাজ হলো Durability নিশ্চিত করা।

  • কোনো চেঞ্জ হলে InnoDB প্রথমে সেটি Redo Log ফাইলে লিখে রাখে (Write Ahead Logging)।
  • তারপর বাফার পুলে চেঞ্জ করে।
  • সার্ভার রিস্টার্ট হলে Redo Log চেক করে মিসিং ডেটা আবার রিকভার করে নেয়।

১২.৮ Undo Log (Rollback & MVCC)

এটি অনেকটা "Ctrl+Z" বা হিস্ট্রি খাতার মতো।

✅ কাজ ১ (Rollback): আপনি ROLLBACK কমান্ড দিলে InnoDB এই লগ দেখে ডেটা আগের অবস্থায় ফিরিয়ে আনে।

✅ কাজ ২ (MVCC): একটি রো আপডেট হওয়ার সময় অন্য ইউজার যদি সেটি রিড করতে চায়, তবে Undo Log থেকে পুরনো ভার্সনটি দেখায়।

১২.৯ MVCC — Multi-Version Concurrency Control

এটি InnoDB-র একটি জাদুকরী ফিচার। এর মানে হলো "Locking ছাড়াই Consistent Read"।

👥 দৃশ্যপট:

  • User A: id=10 এর ব্যালেন্স ৫০০ থেকে কমিয়ে ৪০০ করছে (এখনো COMMIT করেনি)।
  • User B: id=10 এর ব্যালেন্স চেক করছে।
  • MVCC ছাড়া: User B কে অপেক্ষা করতে হতো (Lock)।
  • MVCC সহ: User B সাথে সাথে ৫০০ দেখবে (Undo Log থেকে পুরনো স্ন্যাপশট)। তাকে অপেক্ষা করতে হবে না।

১২.১০ Doublewrite Buffer & Change Buffer

🛡️ Doublewrite Buffer (ডেটা করাপশন রোধ)

অপারেটিং সিস্টেম সাধারণত ৪KB সাইজে ডেটা লেখে, কিন্তু InnoDB ১৬KB পেজ সাইজ ব্যবহার করে। রাইট করার সময় যদি মাঝপথে ক্র্যাশ করে, তবে পেজটি আংশিক রাইট হবে (Partial Page Write)। এটি ডেটা করাপ্ট করে দেয়।

সমাধান: InnoDB প্রথমে ডেটা Doublewrite Buffer এ লেখে (সেফটি কপি), তারপর আসল জায়গায় লেখে। ক্র্যাশ হলে সেফটি কপি থেকে রিকভার করে।

⚡ Change Buffer (Insert Optimization)

সেকেন্ডারি ইনডেক্স (যেমন: phone বা email) আপডেট করা ব্যয়বহুল কারণ এগুলো ডিস্কে ছড়িয়ে ছিটিয়ে থাকে।

InnoDB সাথে সাথে ডিস্কে আপডেট না করে Change Buffer এ জমিয়ে রাখে এবং পরে একসাথে আপডেট করে। এটি INSERT/UPDATE পারফরম্যান্স বাড়ায়।

১২.১১ Tablespaces (File-Per-Table)

আগে সব টেবিলের ডেটা ibdata1 নামে একটি বিশাল ফাইলে থাকত। এটি ম্যানেজ করা খুব কঠিন ছিল।

এখন innodb_file_per_table = 1 মোডে প্রতিটি টেবিলের জন্য আলাদা .ibd ফাইল তৈরি হয়।

  • users.ibd
  • orders.ibd

এতে স্পেস ম্যানেজ করা এবং ব্যাকআপ নেওয়া সহজ হয়।

১২.১২ অন্যান্য ইঞ্জিন (Memory & Archive)

💾 MEMORY Engine

  • সব ডেটা র‍্যামে থাকে।
  • ✅ সুবিধা: রকেটের গতি।
  • ❌ অসুবিধা: সার্ভার রিস্টার্ট দিলে সব ডেটা মুছে যায়।
  • ব্যবহার: টেম্পোরারি ক্যালকুলেশন বা ক্যাশিং।

📦 ARCHIVE Engine

  • ডেটা কমপ্রেস করে রাখে।
  • ✅ সুবিধা: ডিস্ক স্পেস খুব কম লাগে।
  • ❌ অসুবিধা: আপডেট বা ডিলিট করা যায় না।
  • ব্যবহার: অডিট লগ বা অনেক পুরনো হিস্ট্রি।

১২.১৩ Senior-Level Performance Tips

  • 1️⃣Primary Key সিলেকশন: সবসময় AUTO_INCREMENT BIGINT ব্যবহার করুন। UUID বা র‍্যান্ডম স্ট্রিং প্রাইমারি কি হিসেবে ব্যবহার করলে ইনসার্ট স্লো হয়ে যায় (Clustered Index রি-অর্গানাইজ করতে হয় বলে)।
  • 2️⃣Buffer Pool: আপনার সার্ভারের র‍্যামের সর্বোচ্চ ব্যবহার নিশ্চিত করুন বাফার পুল দিয়ে।
  • 3️⃣Isolation Level: ডিফল্ট REPEATABLE READ ভালো, তবে অতিরিক্ত কনকারেন্সি দরকার হলে READ COMMITTED ব্যবহার করা যেতে পারে।
  • 4️⃣Disk Type: InnoDB এর জন্য SSD (Solid State Drive) ব্যবহার করলে পারফরম্যান্স ১০ গুণ বেড়ে যায় (HDD এর তুলনায়)।

❓ Interview Q&A

Q: COUNT(*) কেন MyISAM এ ফাস্ট কিন্তু InnoDB তে স্লো?

Ans: MyISAM টেবিলের হেডার ফাইলে মোট রো সংখ্যা সেভ করে রাখে, তাই গুনতে হয় না। কিন্তু InnoDB-তে MVCC এর কারণে একেক ইউজার একেক রকম রো দেখতে পারে, তাই তাকে প্রতিবার ইনডেক্স স্ক্যান করে গুনতে হয়।

Q: InnoDB তে কি Deadlock হয়?

Ans: হ্যাঁ, হয়। কারণ এটি Row-Level locking ব্যবহার করে। দুটি ট্রানজেকশন ক্রস ডিপেন্ডেন্সি তৈরি করলে ডেডলক হয়।

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

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

  • ✓InnoDB vs MyISAM: Transaction, Row Lock, Foreign Key - InnoDB সব দিক থেকে সেরা।
  • ✓Buffer Pool: RAM-এ ডেটা ক্যাশ করে যা পারফরম্যান্সের মেরুদণ্ড।
  • ✓Clustered Index: Primary Key সাথে ডেটা সংরক্ষণ করে - দ্রুততম সার্চ।
  • ✓Redo/Undo Log: Crash Recovery এবং Rollback নিশ্চিত করে।
  • ✓MVCC: Lock ছাড়াই Consistent Read - হাই কনকারেন্সির চাবিকাঠি।

✨ এখন আপনি জানেন ডেটাবেসের ইঞ্জিনের ভেতরে গিয়ারগুলো কীভাবে ঘোরে। পরবর্তী অধ্যায়ে আমরা শিখব MySQL Security & Hardening—যেখানে আমরা শিখব কীভাবে হ্যাকারদের হাত থেকে ডেটাবেসকে রক্ষা করতে হয় (SQL Injection Prevention, User Roles & Privileges, SSL/TLS)।

প্রস্তুত তো? 🚀

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

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