Series Nâng cao Laravel Query Builder #3: "Đọc vị" Kế hoạch thực thi (Explain) và Tuyệt kỹ CTE (Trạm cuối)

1. Nghệ thuật "Đọc vị" qua EXPLAIN

Đừng bao giờ đoán mò tại sao câu query chậm. Hãy bắt Database phải khai báo "kế hoạch" của nó. Trong Laravel, bạn chỉ cần thêm ->explain() vào cuối chuỗi query.

$result = DB::table('transactions')
    ->where('station_id', 'S03')
    ->where('created_at', '>', '2026-07-01')
    ->explain(); // Thay vì ->get()

Khi bạn dd($result), Laravel sẽ trả về một bảng chi tiết lộ trình Database sẽ đi qua. Hãy chú ý 3 cột quan trọng nhất:

• type: Nếu thấy chữ ALL, đó là thảm họa (Full Table Scan - duyệt toàn bộ bảng). Bạn bắt buộc phải đánh Index cho cột đó.
• key: Database đang dùng Index nào? Nếu nó để NULL, nghĩa là nó đang "mò kim đáy bể".
• rows: Database ước tính phải duyệt qua bao nhiêu dòng? Nếu bảng chỉ có 10.000 dòng mà nó ước tính phải duyệt 9.000 dòng, Index của bạn đang cực kỳ kém hiệu quả.

Chuẩn Senior: Một câu query tối ưu phải có type là const hoặc ref, key phải khớp với các cột trong WHERE, và số lượng rows phải càng gần với kết quả trả về thực tế càng tốt.

2. Tuyệt kỹ CTE: Đệ quy và Xử lý sơ đồ cây

Trong hệ thống Metro, các ga tàu thường được tổ chức theo sơ đồ cây (Ga đầu -> Ga trung gian -> Ga cuối) hoặc sơ đồ phân cấp (Chi nhánh -> Trạm -> Cổng).

Nếu bạn muốn lấy ra tất cả các trạm con nằm dưới một Chi nhánh quản lý, bạn sẽ làm thế nào? Viết vòng lặp PHP đệ quy? Sẽ chết ngay lập tức khi sơ đồ hàng ngàn trạm!

Laravel hỗ trợ CTE (Common Table Expressions) – một bảng tạm được tạo ra ngay trong thời gian thực thi câu query. Đây là cách làm "đúng bài" nhất:

$stations = DB::table('stations as base')
    ->select('id', 'name', 'parent_id')
    ->where('id', 1) // Bắt đầu từ ID Chi nhánh gốc
    ->unionAll(
        DB::table('stations as recursive')
            ->select('id', 'name', 'parent_id')
            ->join('base', 'base.id', '=', 'recursive.parent_id') // Đệ quy: Join vào chính cái bảng tạm vừa tạo
    )
    ->withExpression('base', $initialQuery) // Khai báo biểu thức CTE
    ->get();

CTE giúp bạn thực hiện các truy vấn đệ quy ngay tại Database mà không cần gọi đi gọi lại vào code PHP. Code của bạn sẽ cực kỳ tinh gọn và tốc độ nhanh gấp bội.

3. "Clean Query" với Scope và Query Builder

Mặc dù chúng ta đang tập trung vào Query Builder, nhưng đừng quên nguyên tắc đóng gói của Senior. Đừng bao giờ viết code query thô nằm tràn lan trong Controller. Hãy bọc chúng lại vào các Scope (nếu dùng Model) hoặc các Service/Repository (nếu dùng Query Builder thuần).

Ví dụ, tạo một "thư viện query" cho Transactions:

// Trong một class Repository hoặc Service
public static function getMonthlyReport($query, $month)
{
    return $query->whereMonth('created_at', $month)
                 ->selectRaw('DATE(created_at) as date, SUM(amount) as total')
                 ->groupBy('date');
}

// Khi dùng:
$query = DB::table('transactions');
$report = TransactionRepository::getMonthlyReport($query, 7)->get();

Việc này giúp tái sử dụng query ở khắp mọi nơi (Dashboard, API, Xuất Excel) mà vẫn đảm bảo tính nhất quán của dữ liệu.

4. Checklist vàng trước khi Deploy Query

Trước khi đẩy bất kỳ câu query Builder nào lên Production, hãy tự hỏi 3 câu:

1. Index: Cột trong where, join, orderBy đã được đánh Index chưa?
2. Select: Mình có đang select * không? (Chỉ lấy đúng cột cần dùng, đừng lãng phí băng thông mạng).
3. Explain: Đã chạy ->explain() chưa? (Đã kiểm tra xem Database có thực sự dùng Index không?).

Tổng kết Series Query Builder

Trải qua 3 bài học, chúng ta đã chuyển đổi tư duy:

• Từ "Viết code chạy được" sang "Viết code tối ưu hiệu năng" (Chunk/Cursor/Upsert).
• Từ "Code mì gõ" sang "Tư duy Fluent & Subquery" để xử lý các yêu cầu nghiệp vụ phức tạp.
• Từ "Mò mẫm Debug" sang "Đọc vị cơ chế lõi" bằng EXPLAIN và CTE.

Bạn đã làm chủ được Query Builder - lớp lá chắn cuối cùng của hệ thống. Hiệu năng của API giờ đây đã nằm hoàn toàn trong tầm kiểm soát của bạn.

Series này đã kết thúc chuỗi kiến thức cốt lõi về Backend cho hệ thống AFC Metro. Bạn muốn chúng ta tiếp tục với chủ đề nào?