ডাটাবেস আর্কিটেকচার এবং অপ্টিমাইজেশন

একজন জুনিয়র ডেভেলপার চিন্তা করে—"কিভাবে ডাটা সেভ করব?"
একজন Senior Developer চিন্তা করে—"১০ মিলিয়ন ডাটা হয়ে গেলে এই কুয়েরি কত সেকেন্ডে রেসপন্স করবে?"

এই অধ্যায়ে আমরা ডাটাবেস ডিজাইন, ইনডেক্সিং এবং অপ্টিমাইজেশনের গভীরে প্রবেশ করব।

৯.১ PDO — প্রোডাকশন গ্রেড কানেকশন

mysqli ভুলে যান। আধুনিক পিএইচপি মানেই PDO (PHP Data Objects)। এটি ডাটাবেস-অ্যাগনস্টিক (MySQL, PostgreSQL, SQLite সব সাপোর্ট করে)।

⚙️ ইন্টারনাল কানেকশন অপ্টিমাইজেশন:

PDO কানেকশন তৈরি করার সময় কিছু ফ্ল্যাগ সেট করা বাধ্যতামূলক যা জুনিয়ররা মিস করে।

$dsn = "mysql:host=127.0.0.1;dbname=production_db;charset=utf8mb4";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // এরর হলে এক্সেপশন থ্রো করবে
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,      // মেমোরি সাশ্রয়ী অ্যারে ফরম্যাট
    PDO::ATTR_EMULATE_PREPARES   => false,                 // রিয়েল প্রিপেয়ার্ড স্টেটমেন্ট
];

$pdo = new PDO($dsn, "user", "pass", $options);

💡 ATTR_EMULATE_PREPARES => false কেন?
এটি true থাকলে পিএইচপি ইন্টারনালি কুয়েরি জেনারেট করে পাঠায়। false করে দিলে MySQL ইঞ্জিনের নেটিভ প্রিপেয়ার্ড স্টেটমেন্ট ফিচার ব্যবহার হয়, যা ফাস্ট এবং বেশি সিকিউর।

৯.২ SQL ইনজেকশন এবং প্রিপেয়ার্ড স্টেটমেন্ট

SQL ইনজেকশন প্রতিরোধ করার একমাত্র উপায় হলো ডাটা এবং কুয়েরি লজিক আলাদা রাখা।

🔄 প্রিপেয়ার্ড স্টেটমেন্ট কীভাবে কাজ করে?

  1. Prepare: পিএইচপি কুয়েরি টেমপ্লেট (SELECT * FROM users WHERE email = ?) ডাটাবেসে পাঠায়
  2. Compile: ডাটাবেস ইঞ্জিন কুয়েরিটি পার্স করে, কম্পাইল করে এবং অপ্টিমাইজ করে প্ল্যান সেভ করে রাখে
  3. Execute: এরপর শুধু ডাটা (param) পাঠানো হয়

যেহেতু কুয়েরি আগেই কম্পাইল হয়ে গেছে, তাই হ্যাকার যদি ইনপুটে ; DROP TABLE users; পাঠায়, ডাটাবেস সেটাকে কেবল একটি স্ট্রিং টেক্সট হিসেবে দেখবে, কুয়েরি হিসেবে নয়।

✅ Named Parameter (Best Practice)

$stmt = $pdo->prepare("SELECT * FROM users WHERE email = :email AND status = :status");
$stmt->execute([
    'email' => $userInput,
    'status' => 1
]);

৯.৩ ট্রানজেকশন (ACID প্রপার্টি)

টাকা ট্রান্সফারের সময় যদি কারেন্ট চলে যায়? টাকা যাতে সেন্ডারের অ্যাকাউন্ট থেকে না কাটে—সেটাই ট্রানজেকশনের কাজ।

A
Atomicity
C
Consistency
I
Isolation
D
Durability

💰 ট্রানজেকশন প্যাটার্ন:

try {
    $pdo->beginTransaction();

    // 1. টাকা কাটা
    $stm1 = $pdo->prepare("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $stm1->execute();

    // 2. টাকা জমা দেওয়া
    $stm2 = $pdo->prepare("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    $stm2->execute();

    $pdo->commit(); // সব ঠিক থাকলে পার্মানেন্ট সেভ
} catch (Exception $e) {
    $pdo->rollBack(); // ভুল হলে আগের অবস্থায় ফিরে যাও
    // Log Error
}

৯.৪ ইনডেক্সিং এবং B-Tree (The Performance King)

ইনডেক্স ছাড়া ডাটাবেস অনেকটা অগোছালো বইয়ের মতো—প্রতিটি পৃষ্ঠা উল্টে খুঁজতে হয় (Full Table Scan - O(n))। ইনডেক্স থাকলে সেটা ফোনবুকের মতো কাজ করে (B-Tree Search - O(log n))。

🌲 কম্পোজিট ইনডেক্স এবং Leftmost Prefix Rule

এটি সিনিয়দের ইন্টারভিউতে হট টপিক। ধরুন আপনার ইনডেক্স আছে: INDEX(a, b, c)।

  • ✅ ফাস্ট হবে: WHERE a=?, WHERE a=? AND b=?, WHERE a=? AND b=? AND c=?
  • ❌ ইনডেক্স কাজ করবে না: WHERE b=?, WHERE c=?, WHERE b=? AND c=?

কারণ: B-Tree বাম দিক থেকে সাজানো থাকে। তাই প্রথম কলাম (a) স্কিপ করলে ইনডেক্স অকেজো।

📊 কার্ডিনালিটি (Cardinality)

Gender কলামে (Male/Female) ইনডেক্স দেওয়া বোকামি। কারণ ডাটা ভ্যারাইটি কম। ইনডেক্স তখনই ভালো কাজ করে যখন ডাটা ইউনিক বা হাইলি ভেরিয়েবল হয় (যেমন: Email, UUID, Phone)।

৯.৫ JOIN অপ্টিমাইজেশন এবং EXPLAIN

JOIN স্লো হওয়ার প্রধান কারণ ইনডেক্স না থাকা।

✅ Foreign Key Indexing: যে কলাম দিয়ে জয়েন করবেন (ON u.id = o.user_id), সেই কলামে অবশ্যই ইনডেক্স থাকতে হবে।

🔍 EXPLAIN ব্যবহার: কুয়েরি রান করার আগে EXPLAIN SELECT * FROM users JOIN orders ... লিখুন।

আউটপুটে rows কলাম দেখুন। যদি দেখেন ১০০০+ রো স্ক্যান করছে, তার মানে অপ্টিমাইজেশন দরকার।

⚠️ Data Type Mismatch: user_id যদি এক টেবিলে INT এবং অন্য টেবিলে VARCHAR হয়, তবে ইনডেক্স কাজ করবে না। টাইপ অবশ্যই সেম হতে হবে।

৯.৬ পেজিনেশন অপ্টিমাইজেশন (The Offset Trap)

১০ লাখ ডাটা থাকলে সাধারণ পেজিনেশন সাইট ডাউন করে দিতে পারে।

❌ খারাপ পদ্ধতি (Offset):

SELECT * FROM logs LIMIT 20 OFFSET 100000;

ইঞ্জিন প্রথম ১,০০,০০০ ডাটা পড়ে ফেলে দেয়, তারপর ২০টা দেয়। এটি অত্যন্ত ব্যয়বহুল।

✅ অপ্টিমাইজড পদ্ধতি (Cursor / Seek Method):

SELECT * FROM logs WHERE id > :last_seen_id LIMIT 20;

এখানে id প্রাইমারি কি (ইনডেক্সড), তাই এটি সরাসরি ওই পয়েন্টে জাম্প করে। O(1) টাইমে রেজাল্ট দেয়।

৯.৭ স্টোরড প্রসিডিউর (Stored Procedures)

কখন ব্যবহার করবেন? যখন লজিক খুব জটিল এবং প্রচুর ডাটা প্রসেস করতে হয়।

পিএইচপি থেকে ১০টি কুয়েরি পাঠানোর চেয়ে ১টি প্রসিডিউর কল করা ফাস্ট (Network Latency কমে)。

⚠️ Senior Warning: "Business Logic should stay in Code, not Database." শুধুমাত্র রিপোর্টিং বা হেভি ক্যালকুলেশনের জন্যই এটি ব্যবহার করুন।

৯.৮ ডাটাবেস মাইগ্রেশন (Version Control for DB)

টিমে কাজ করার সময় মাইগ্রেশন ছাড়া উপায় নেই। লারাভেল বা phinx এর মতো টুল ব্যবহার করুন।

✅ Reproducibility

নতুন ডেভেলপার migrate কমান্ড দিলেই পুরো ডাটাবেস স্ট্রাকচার পেয়ে যায়।

⏪ Rollback

ভুল হলে migrate:rollback দিয়ে আগের অবস্থায় ফেরা যায়।

৯.৯ Redis — মেমোরি ক্যাশিং

Redis হলো সুপার ফাস্ট ইন-মেমোরি ডাটা স্টোর।

Cache-Aside Pattern (Best Practice):

$id = 123;
$cacheKey = "user:{$id}";

// ১. প্রথমে রেডিস চেক করো
$data = $redis->get($cacheKey);

if (!$data) {
    // ২. না থাকলে ডিবি থেকে আনো
    $data = $db->query("SELECT * FROM users WHERE id = $id")->fetch();
    
    // ৩. এবং রেডিসে সেভ করে রাখো (TTL সহ)
    $redis->setex($cacheKey, 3600, json_encode($data));
}

return json_decode($data);

৯.১০ কিউ সিস্টেম (RabbitMQ / Redis Queue)

ইউজার রেজিস্ট্রেশন করার পর সাথে সাথে ইমেইল পাঠানো যাবে না। এতে ইউজারকে অপেক্ষা করতে হয়।

✅ সমাধান (Asynchronous Processing):

  1. ইউজার সাবমিট করল → ডাটা সেভ হলো
  2. একটি জব (Job) কিউ-তে পুশ করলাম (RegisterEmailJob)
  3. ইউজারকে বললাম "Success"
  4. ব্যাকগ্রাউন্ডে একটি Worker স্ক্রিপ্ট কিউ থেকে জব নিয়ে ইমেইল পাঠাবে

📦 Redis Queue

ছোট ও মাঝারি প্রজেক্টের জন্য বেস্ট (Simple Push/Pop)

🐇 RabbitMQ

এন্টারপ্রাইজ লেভেল, যেখানে গ্যারান্টিড ডেলিভারি এবং কমপ্লেক্স রাউটিং দরকার

🎯 Senior Developer Interview Questions (Chapter 9)

Q: OFFSET ভিত্তিক পেজিনেশন কেন বড় ডাটাবেসে স্লো হয়? সমাধান কী?

OFFSET ডাটা স্কিপ করার জন্য আগের সব ডাটা স্ক্যান করে। সমাধান হলো Cursor-based বা ID-based পেজিনেশন (WHERE id > last_id) ব্যবহার করা।

Q: কম্পোজিট ইনডেক্সে Leftmost Prefix Rule বলতে কী বোঝেন?

INDEX(a,b,c) তৈরি করলে এটি তখনই কাজ করবে যখন কুয়েরিতে a অথবা a,b অথবা a,b,c ব্যবহার হবে। a বাদ দিয়ে শুধু b বা c দিয়ে সার্চ করলে ইনডেক্স কাজ করবে না।

Q: ট্রানজেকশনে ACID প্রপার্টি কেন গুরুত্বপূর্ণ?

এটি ডাটা ইন্টিগ্রিটি নিশ্চিত করে। যেমন ব্যাংকিং সিস্টেমে টাকা কাটার পর সার্ভার ক্র্যাশ করলেও ACID এনশিওর করে যে টাকা হারিয়ে যাবে না (Rollback হবে)।

Q: N+1 প্রবলেম কী এবং এটি কীভাবে সলভ করবেন?

লুপের ভেতর কুয়েরি চালানোকে N+1 প্রবলেম বলে। এটি সলভ করতে JOIN অথবা ORM-এর Eager Loading (with('relation')) ব্যবহার করতে হয়।

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

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

  • ✓PDO Configuration: ERRMODE_EXCEPTION, EMULATE_PREPARES=false
  • ✓Prepared Statements: SQL Injection প্রতিরোধের একমাত্র উপায়
  • ✓ACID Transactions: beginTransaction + commit + rollback pattern
  • ✓B-Tree Indexing: O(log n) vs Full Table Scan O(n)
  • ✓Leftmost Prefix: INDEX(a,b,c) → WHERE a=? or a,b=? or a,b,c=?
  • ✓Cursor Pagination: WHERE id > last_id LIMIT 20 - O(1)
  • ✓Cache-Aside Pattern: Redis → DB → Redis TTL

✨ এখন আপনার ডাটাবেস নলেজ একজন আর্কিটেক্ট লেভেলের।

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

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