Database Index: B-tree ও composite index
Indexes: B-Trees and Composite Indexes
এই পাঠের সূচিপত্র
এই পাঠে যা শিখবেন
- B-tree access ও composite index order
- Filter, sort ও selectivity
- Laravel migration-এ targeted index
সমস্যা দিয়ে শুরু
Customer নিজের শেষ বিশটি order দেখতে চান, কিন্তু database লক্ষ row scan করছে। Query ছোট, response-ও ছোট; কাজ বড় কারণ matching ও sorting path efficient নয়। Index query-এর জন্য আলাদা organized access structure। প্রতিটি column-এ index যোগ করলে read সবসময় দ্রুত হয় না, বরং writes ও storage cost বাড়ে।
সহজ করে বুঝি
B-tree sorted keys রাখে, equality ও range lookup এবং কিছু ordered traversal দ্রুত করতে পারে। Composite index-এর columns-এর order access pattern-এর সঙ্গে মেলাতে হয়। user_id ও id index user filter করে id-order traversal সাহায্য করতে পারে। Index selectivity মানে predicate কত অল্প row বেছে আনে; boolean status একা কম selective হতে পারে। Optimizer table size, statistics ও cost দেখে index নাও বেছে নিতে পারে। Index থাকা মানেই সব query তা ব্যবহার করবে না।
পরিচিত একটি উপমা
লাইব্রেরির subject অনুযায়ী, তারপর author অনুযায়ী card catalog ভাবুন। Subject জানা থাকলে ordered subsection খোঁজা সহজ। শুধু author জানা থাকলে সেই ordering সব query-তে সমান সাহায্য নাও করতে পারে। Catalog বজায় রাখতে নতুন বই এলেই entry update করতে হয়—index-এর write costও আছে।
নকশাটি দেখুন
Diagram লোড হচ্ছে…
Diagram-এর text flow দেখুন
flowchart LR Q[Filter user and order by id] --> I[Composite B tree user_id id] I --> R[Locate matching ordered keys] R --> T[Read required table rows] T --> L[Return first 20]
Query user_id filter দিয়ে index range খোঁজে। id ordering এবং limit একসঙ্গে কম row পড়ার সুযোগ দেয়। Table row lookup এখনও লাগতে পারে; covering/index-only behavior engine ও selected fields-এর ওপর নির্ভর করে। Diagram ideal candidate plan দেখায়, actual plan EXPLAIN দিয়ে যাচাই করতে হবে।
PHP ও Laravel-এ প্রয়োগ
Migration up fragment orders table আগে থেকেই আছে। Default index creation large table-এ lock/IO impact দিতে পারে; PostgreSQL concurrent index creation আলাদা migration/transaction constraint নিয়ে plan করতে হবে। Controller query authenticated user ID-তে scoped হবে, arbitrary client user_id-তে নয়।
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
Schema::table('orders', function (Blueprint $table) {
$table->index(['user_id', 'id'], 'orders_user_id_id_index');
});
$orders = \App\Models\Order::query()
->where('user_id', $userId)
->orderBy('id')
->limit(20)
->get(['id', 'status']);সুবিধা, সীমাবদ্ধতা ও trade-off
Index read work কমাতে পারে, তবে insert/update/delete-এ index maintenance হয়। বেশি index disk, cache footprint ও migration time বাড়ায়। Covering index table fetch কমায় কিন্তু extra included/ordered columns cost বাড়ায়। Prefix matching, collation, NULL handling ও descending scan engine-specific; copied rule না দিয়ে target database plan পড়ুন। Partial index sparse business state-এ useful হতে পারে, product support যাচাই করুন।
সাধারণ ভুল
- Query না দেখে সব column individually index করা।
- Function/cast দিয়ে indexed column wrap করে plan না পরীক্ষা করা।
- Production বড় table-এ unplanned blocking index build চালানো।
আরও একটু গভীরে
Composite index leftmost-prefix guideline useful হলেও optimizer behavior আরও nuanced; newer engine skip-scan বা bitmap combinations ব্যবহার করতে পারে। তাই absolute “কখনো ব্যবহার করবে না” rule এড়ান। Statistics stale হলে estimate ভুল হয়; ANALYZE ও representative data distribution দেখুন। Index-only scan-এ visibility checks লাগতে পারে। Small test table sequential scan বেছে নিলে সেটি automatically bad plan নয়।
মনে রাখুন
Index access pattern-এর জন্য, field list-এর জন্য নয়। Filter, sort, limit ও data distribution একসঙ্গে দেখুন। Actual plan ও write cost ছাড়া optimization ঘোষণা করবেন না।
নিজে চেষ্টা করুন
এক user-এর recent orders query-এর জন্য (user_id,id) ও (id,user_id) candidate লিখুন। Representative data-তে EXPLAIN plan তুলনা করুন। New order insert cost ও index size-ও measure plan-এ রাখুন।
নিজের বোঝা যাচাই করুন
1. Index থাকলেই use করবে?
না। Optimizer statistics ও estimated cost দেখে অন্য plan নিতে পারে। Small table sequential scan যুক্তিসংগত হতে পারে।
2. Composite order জরুরি?
হ্যাঁ। Filter ও ordered traversal-এর access path বদলায়। Engine-specific optimizer capability মাথায় রেখে actual plan দেখুন।
3. সব field index করব?
না। Extra index write amplification ও storage বাড়ায়। Frequent costly queries-এর জন্য targeted design করুন।
4. Low-cardinality field useless?
সর্বদা নয়। Composite/partial index বা distribution অনুযায়ী useful হতে পারে। Predicate ও workload দিয়ে বিচার করুন।
5. Migration ঝুঁকি কী?
Index build lock, IO ও time নিতে পারে। Large table-এ online/concurrent capability ও rollback plan পরীক্ষা জরুরি।
আরও পড়ুন
এই পাঠের কুইজ
এই পাঠ থেকে ২টি প্রশ্ন। উত্তর দিয়ে ব্যাখ্যা দেখুন; অধ্যায়ের কুইজ আলাদাভাবে দিতে হবে।
এই পাঠের ২টি প্রশ্নের উত্তর দিন। পাস করতে দুটিই সঠিক হতে হবে।
জমা দেওয়ার পর সঠিক উত্তর ও ব্যাখ্যা দেখবেন। ফলাফল এই ব্রাউজারে সংরক্ষিত থাকবে। অনুশীলনের ফল অধ্যায় সম্পন্নের হিসাবে যোগ হয় না।
অগ্রগতি এই ব্রাউজারে সংরক্ষিত থাকে। শুধু পাঠ খুললে সম্পন্ন হয় না।