Database Indexes
- ●Index হলো ডেটাবেজের একটা আলাদা ডেটা স্ট্রাকচার যা নির্দিষ্ট কলামে খোঁজা দ্রুত করে।
- ●Index ছাড়া ডেটাবেজকে full table scan করতে হয়; index থাকলে B-tree দিয়ে সরাসরি খুঁজে পায়।
- ●Index পড়া দ্রুত করে কিন্তু লেখা ধীর করে ও storage খায় — তাই বুঝে বসাতে হয়।
সমস্যাটা কী?
ভাবো তোমার একটা users টেবিল আছে যেখানে ১ কোটি (১০ মিলিয়ন) ব্যবহারকারী আছে। এখন তুমি query করলে:
SELECT * FROM users WHERE email = 'rahim@example.com';
কোনো index না থাকলে ডেটাবেজের কাছে কোনো শর্টকাট নেই। তাকে প্রথম row থেকে শুরু করে এক এক করে ১ কোটি row-র email মিলিয়ে দেখতে হবে যতক্ষণ না মিল পায় (বা শেষ পর্যন্ত যায়)। এটাকে বলে Full Table Scan। ১ কোটি row স্ক্যান করতে কয়েক সেকেন্ড লেগে যেতে পারে — অথচ তোমার দরকার ছিল মাত্র একটা row!
এটা ঠিক যেন ৫০০ পৃষ্ঠার একটা বইয়ে "Photosynthesis" শব্দটা খুঁজতে প্রথম পৃষ্ঠা থেকে এক এক করে পড়া। বইয়ের পেছনে যদি একটা index (বর্ণানুক্রমিক সূচি) থাকত, তুমি সরাসরি পৃষ্ঠা নম্বর পেয়ে যেতে। ডেটাবেজ index ঠিক এই কাজটাই করে।
মূল ধারণা
Index হলো ডেটাবেজের একটা আলাদা, সাজানো ডেটা স্ট্রাকচার যা এক বা একাধিক কলামের মান অনুযায়ী row-এর অবস্থান (pointer) ধরে রাখে, যাতে খোঁজা দ্রুত হয়।
মূল ব্যাপারটা হলো — index মূল টেবিলের একটা ছোট, সাজানো "মানচিত্র"। কলামের মান sorted অবস্থায় থাকে, প্রতিটার সাথে থাকে মূল row-এর ঠিকানা। ফলে ডেটাবেজ পুরো টেবিল না ঘেঁটে index-এ দ্রুত খুঁজে নির্দিষ্ট row-তে লাফ দিতে পারে।
ফলাফল — full table scan-এর জায়গায় হয় index lookup। ১ কোটি row-এ যেখানে scan-এ ১ কোটি ধাপ লাগত, সেখানে index lookup-এ লাগে মাত্র ২০-২৫টা ধাপের মতো (logarithmic)।
কীভাবে কাজ করে
B-tree: index-এর মূল কাঠামো
বেশিরভাগ ডেটাবেজ index-এর জন্য B-tree (আসলে B+ tree) ব্যবহার করে। এটা একটা ভারসাম্যপূর্ণ গাছের মতো কাঠামো:
- উপরে root, নিচে শাখা, একদম নিচে পাতা (leaf) — পাতায় থাকে আসল মান ও row-এর pointer।
- প্রতিটা node-এ মান sorted থাকে, তাই কোন শাখায় যাবে তা ডেটাবেজ সহজে ঠিক করে।
- গাছের উচ্চতা কম থাকে — ১ কোটি মানের জন্যও মাত্র ৩-৪ স্তর। তাই খোঁজা O(log n), যা প্রায় ধ্রুবক সময়ের মতো দ্রুত।
Scan বনাম Lookup
| বিষয় | Full Table Scan | Index Lookup |
|---|---|---|
| কীভাবে খোঁজে | প্রতিটা row একে একে | B-tree-তে সরাসরি লাফ |
| জটিলতা | O(n) | O(log n) |
| ১ কোটি row-এ ধাপ | ~১ কোটি | ~২৪ |
| লেখায় প্রভাব | নেই | প্রতিবার index আপডেট |
range query-তেও দ্রুত
যেহেতু B-tree sorted, তাই WHERE age BETWEEN 20 AND 30 বা ORDER BY জাতীয় query-ও index দ্রুত করে — ডেটাবেজ শুরুর মান খুঁজে পেয়ে পরপর পাতাগুলো ধরে এগিয়ে যায়।
ভাবো ঢাকার একটা বড় হাসপাতালের পুরোনো রোগীদের ফাইল রাখার ঘর। ফাইলগুলো এলোমেলোভাবে রাখা থাকলে, "করিম" নামের রোগীর ফাইল খুঁজতে প্রতিটা তাক ঘাঁটতে হবে (full scan)। কিন্তু রিসেপশনে যদি নামের বর্ণানুক্রমে সাজানো একটা রেজিস্টার (index) থাকে যেখানে লেখা "করিম → তাক ৭, বাক্স ৩", তাহলে সরাসরি সেখানে গিয়ে নিমেষেই ফাইল পেয়ে যাবে। রেজিস্টারটা মূল ফাইলের নকল নয়, শুধু একটা সাজানো পথনির্দেশ — index ঠিক এমনই।
প্রকারভেদ
১. Primary Index
Primary key-র উপর স্বয়ংক্রিয়ভাবে তৈরি হওয়া index। এটা সাধারণত unique এবং অনেক ডেটাবেজে এটাই ঠিক করে দেয় টেবিলে ডেটা ফিজিক্যালি কীভাবে সাজানো থাকবে (clustered index)।
২. Secondary Index
Primary key ছাড়া অন্য কলামে (যেমন email, created_at) বানানো index। একটা টেবিলে একাধিক secondary index থাকতে পারে। এগুলো মূল row-এর দিকে pointer রাখে।
৩. Composite Index
একাধিক কলাম মিলিয়ে একটা index — যেমন (last_name, first_name)। এটা leftmost prefix নিয়মে কাজ করে:
(last_name)দিয়ে খুঁজলে — কাজ করে।(last_name, first_name)দিয়ে — কাজ করে।- শুধু
(first_name)দিয়ে — কাজ করে না।
তাই composite index-এ কলামের ক্রম খুব গুরুত্বপূর্ণ — সবচেয়ে বেশি filter হওয়া কলাম আগে রাখো।
৪. Unique Index
মান যেন duplicate না হয় তা নিশ্চিত করে (যেমন প্রতিটা email আলাদা), পাশাপাশি দ্রুত খোঁজাও দেয়।
কখন ব্যবহার করবে / করবে না
করবে:
- যে কলাম প্রায়ই
WHERE,JOIN,ORDER BY-তে আসে। - বড় টেবিলে, যেখানে scan ব্যয়বহুল।
- উচ্চ cardinality কলামে (যেখানে অনেক ভিন্ন মান — যেমন email)।
করবে না:
- ছোট টেবিলে (scan এমনিতেই দ্রুত)।
- যে কলামে খুব কম ভিন্ন মান (যেমন
is_activeযার মান শুধু true/false) — সেখানে index তেমন লাভ দেয় না। - প্রতিটা কলামে অন্ধভাবে index — এটা সবচেয়ে সাধারণ ভুল।
Index বিনামূল্যে নয়। প্রতিটা index-এর জন্য প্রতিবার insert/update/delete-এ ডেটাবেজকে সেই index-ও আপডেট করতে হয়, ফলে write ধীর হয় এবং disk space খরচ হয়। যদি একটা টেবিলে ১৫টা index থাকে, একটা insert মানে ১৫ জায়গায় লেখা। তাই "যত index তত ভালো" — এই ধারণা ভুল। শুধু যেটা সত্যিই query-তে কাজে লাগে, সেটাই বসাও।
বাস্তব উদাহরণ
প্রায় সব বড় সিস্টেমেই index অপরিহার্য। যেমন Instagram PostgreSQL-এ কোটি কোটি user ও post রাখে; user_id ও created_at-এর উপর composite index ছাড়া কোনো ব্যবহারকারীর timeline বা ফলোয়ারদের পোস্ট বের করা অসম্ভব হয়ে যেত।
আবার ডেভেলপাররা EXPLAIN (বা EXPLAIN ANALYZE) কমান্ড দিয়ে দেখে নেয় কোনো query আসলেই index ব্যবহার করছে নাকি লুকিয়ে full table scan করছে — ধীর query ডিবাগ করার এটাই প্রথম ধাপ।
ইন্টারভিউতে "এই query ধীর কেন?" জিজ্ঞেস করলে প্রথমেই বলো — EXPLAIN দিয়ে দেখব index ব্যবহার হচ্ছে কি না। তারপর ব্যাখ্যা করো কোন কলামে index দরকার, এবং কেন composite index-এ কলামের ক্রম জরুরি (leftmost prefix)। সাথে write-এর উপর খরচের কথা বললে দেখাবে তুমি ট্রেড-অফটাও বোঝো।
মূল শব্দ (Key Terms)
মিনি কুইজ
1. Index ছাড়া কোনো কলামে খোঁজা হলে ডেটাবেজ কী করে?
2. Index-এর প্রধান অসুবিধা কোনটি?
3. Composite index (a, b)-তে কোন query সবচেয়ে ভালো কাজ করে?