Query Optimization: EXPLAIN, eager loading ও N+1
EXPLAIN, Query Optimization, and N+1
এই পাঠের সূচিপত্র
এই পাঠে যা শিখবেন
- Query count, rows ও latency একসঙ্গে মাপা
- EXPLAIN estimates ও actual execution
- Eloquent eager loading ও N+1
সমস্যা দিয়ে শুরু
Order list মাত্র বিশ row ফেরায়, কিন্তু প্রতিটি order-এর customer আলাদা query-তে পড়ছে। Total একুশ query, network latency বারবার যোগ হচ্ছে। অন্য endpoint এক query-তে লক্ষ row join করছে। Query count কমানো ভালো হতে পারে, কিন্তু একটি huge queryও expensive; measurement boundary দরকার।
সহজ করে বুঝি
EXPLAIN optimizer-এর chosen plan ও estimated cost/rows দেখায়। EXPLAIN ANALYZE query চালিয়ে actual timing ও rows দেয়; write statement-এ side effects হতে পারে। N+1-এ parent list-এর পরে প্রত্যেক relation আলাদা fetch হয়। Eager loading related IDs batch query-তে পড়ে। Selective columns, bounded pagination ও suitable indexes result shaping-এর অংশ। Optimization-এর আগে slow endpoint, query fingerprint এবং representative parameters চিহ্নিত করুন।
পরিচিত একটি উপমা
দোকানের clerk বিশ customer-এর address জানতে register room-এ বিশবার যাচ্ছেন। এক তালিকায় IDs দিয়ে একবারে খুঁজে আনা eager loading। তবে সব customer-এর সব ইতিহাস এক cart-এ আনা আবার অতিরিক্ত কাজ। Batch এবং necessary data দুটোই গুরুত্বপূর্ণ।
নকশাটি দেখুন
Diagram লোড হচ্ছে…
Diagram-এর text flow দেখুন
flowchart TD A[Fetch bounded orders] --> B[Collect customer IDs] B --> C[Fetch customers in batch] C --> D[Attach relations in memory] D --> E[Explicit response fields] A --> P[Inspect query plan and rows]
Parent query bounded। Related IDs অনুযায়ী eager-load query চলে, তারপর Eloquent relation attach করে। সব relation eager load করা দরকার নেই; API output যেগুলো ব্যবহার করে সেগুলোই নিন। Plan inspection parent filter ও sort efficiency দেখবে। Query count, rows fetched এবং serialization memory একসঙ্গে পরীক্ষা করুন।
PHP ও Laravel-এ প্রয়োগ
Controller/service fragment Order::customer belongsTo relation এবং user-scoped query ধরে নেওয়া হয়েছে। customer_id select-এ রাখা জরুরি relation match-এর জন্য। Customer name access authorization business অনুযায়ী যাচাই করুন। Development-এ lazy loading prevention accidental N+1 ধরতে সাহায্য করে।
use App\Models\Order;
use Illuminate\Database\Eloquent\Model;
Model::preventLazyLoading(!app()->isProduction());
$orders = Order::query()
->where('user_id', $userId)
->with('customer:id,name')
->orderByDesc('id')
->limit(20)
->get(['id', 'user_id', 'customer_id', 'status']);
return $orders->map(fn (Order $order) => [
'id' => $order->id,
'customer_name' => $order->customer?->name,
'status' => $order->status,
]);সুবিধা, সীমাবদ্ধতা ও trade-off
Eager loading repeated round trip কমায়, কিন্তু relation size বড় হলে memory ও transferred rows বাড়ায়। Aggregate count দরকার হলে withCount বা targeted aggregate সব children load-এর চেয়ে ভালো হতে পারে। Query rewrite readability কমালে measured benefit ও test evidence রাখুন। Cache slow query ঢাকতে পারে, কিন্তু miss path ও source correctness এখনও optimize করতে হয়।
সাধারণ ভুল
- EXPLAIN ANALYZE write query production-এ অনিচ্ছাকৃত চালানো।
- Eager load-এ matching foreign key select থেকে বাদ দেওয়া।
- Development-এর ছোট uniform data দিয়ে production skew অস্বীকার করা।
আরও একটু গভীরে
Estimated rows ও actual rows অনেক আলাদা হলে statistics, parameter selectivity ও correlated data দেখুন। Sequential scan সবসময় খারাপ নয়; বড় fraction পড়লে random index lookup বেশি cost হতে পারে। Slow query log-এ personal data/secret parameter redact করুন। Query timing-এ database execution ছাড়াও pool wait, network, hydration ও serialization user latency যোগ করে; শুধু SQL milliseconds থেকে endpoint speed ঘোষণা করবেন না।
মনে রাখুন
প্রথমে work খুঁজুন, তারপর query shape ও index বদলান। N+1 repeated access-এর সমস্যা, eager loading bounded হওয়া উচিত। Actual workload ও plan দিয়ে improvement যাচাই করুন।
নিজে চেষ্টা করুন
একটি order list-এ relation accessসহ query log capture করুন। Eager loading-এর আগে-পরে count, rows ও p95 endpoint latency test plan দিন। কোন data shape-এ memory বাড়তে পারে সেটিও লিখুন।
নিজের বোঝা যাচাই করুন
1. N+1 শুধু query count?
Repeated per-row lookup pattern। তার cost network, latency ও database work-এর ওপর নির্ভর করে; ছোট responseও অনেক query করতে পারে।
2. সব relation eager load?
না। Needed bounded relationships load করুন। Unused বা huge child collections memory ও database কাজ বাড়ায়।
3. EXPLAIN ANALYZE safe read?
এটি statement execute করে। Mutating query-এর side effects হতে পারে; controlled environment/transaction ও engine behavior বুঝে ব্যবহার করুন।
4. Foreign key select কেন?
Eloquent related records parent-এর সঙ্গে match করতে key দরকার। Omit করলে relation incorrect বা empty হতে পারে।
5. Sequential scan খারাপ?
Not always। ছোট table বা large matching fraction-এ efficient হতে পারে। Actual rows ও cost দেখে বিচার করুন।
আরও পড়ুন
এই পাঠের কুইজ
এই পাঠ থেকে ২টি প্রশ্ন। উত্তর দিয়ে ব্যাখ্যা দেখুন; অধ্যায়ের কুইজ আলাদাভাবে দিতে হবে।
এই পাঠের ২টি প্রশ্নের উত্তর দিন। পাস করতে দুটিই সঠিক হতে হবে।
জমা দেওয়ার পর সঠিক উত্তর ও ব্যাখ্যা দেখবেন। ফলাফল এই ব্রাউজারে সংরক্ষিত থাকবে। অনুশীলনের ফল অধ্যায় সম্পন্নের হিসাবে যোগ হয় না।
অগ্রগতি এই ব্রাউজারে সংরক্ষিত থাকে। শুধু পাঠ খুললে সম্পন্ন হয় না।