Tối ưu Eloquent Query cho hệ thống lớn: Guide thực chiến

Giới thiệu

Eloquent là ORM tuyệt vời cho development, nhưng có thể là performance killer trong production nếu không cẩn thận. Bài viết này chia sẻ các kỹ thuật tối ưu cho hệ thống hàng triệu records.

Vấn đề phổ biến: N+1 Query

Phát hiện N+1

// ❌ N+1 Problem
$posts = Post::all();

foreach ($posts as $post) {
    echo $post->author->name; // Query thêm CHO MỖI post
}
// Result: 1 + N queries (N = số posts)

Giải pháp: Eager Loading

// ✅ Fixed với with()
$posts = Post::with('author')->get();

foreach ($posts as $post) {
    echo $post->author->name; // Không query thêm
}
// Result: 2 queries (posts + authors)

Nested Eager Loading

// Load multiple levels
$posts = Post::with([
    'author',
    'comments.user',
    'tags',
])->get();

// Constrained eager loading
$posts = Post::with(['comments' => function ($query) {
    $query->where('approved', true)
          ->orderBy('created_at', 'desc')
          ->limit(5);
}])->get();

Prevent N+1 trong Development

// app/Providers/AppServiceProvider.php
public function boot(): void
{
    Model::preventLazyLoading(!app()->isProduction());
}

Sẽ throw exception nếu có lazy loading trong dev environment.

Chunking cho Large Datasets

Vấn đề: Memory Exhaustion

// ❌ Load tất cả vào memory
$users = User::all(); // 1 triệu users = crash

foreach ($users as $user) {
    // Process
}

Giải pháp: chunk()

// ✅ Process từng batch
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        // Process user
    }
});

chunkById() cho Update Operations

// ✅ An toàn khi update/delete trong loop
User::where('active', false)
    ->chunkById(1000, function ($users) {
        foreach ($users as $user) {
            $user->delete();
        }
    });

Lazy Collections

// ✅ Memory efficient iterator
User::lazy()->each(function ($user) {
    // Chỉ load 1 record tại một thời điểm
});

// Với chunk size
User::lazy(1000)->each(function ($user) {
    // Process
});

Select Only What You Need

Vấn đề: SELECT *

// ❌ Load tất cả columns
$users = User::all();

// ✅ Chỉ load columns cần thiết
$users = User::select(['id', 'name', 'email'])->get();

Với Relationships

// ✅ Select specific columns trong eager loading
$posts = Post::with(['author:id,name,avatar'])->get();

// ✅ Combine với main select
$posts = Post::select(['id', 'title', 'author_id'])
    ->with(['author:id,name'])
    ->get();

Database Indexing

Kiểm tra Query Execution

// Xem query plan
$query = User::where('email', 'test@example.com');
DB::enableQueryLog();
$query->get();
dd(DB::getQueryLog());

// Hoặc explain()
User::where('email', 'test@example.com')->explain();

Tạo Indexes đúng cách

// Migration
Schema::table('users', function (Blueprint $table) {
    // Single column index
    $table->index('email');
    
    // Composite index - thứ tự quan trọng!
    $table->index(['status', 'created_at']);
    
    // Unique index
    $table->unique('username');
});

Index Strategy

// Composite index: order matters!
// Index on ['status', 'created_at'] works for:
User::where('status', 'active')->get(); // ✅
User::where('status', 'active')->where('created_at', '>', now()->subDays(7))->get(); // ✅

// But NOT for:
User::where('created_at', '>', now()->subDays(7))->get(); // ❌ Won't use index

Query Caching

Basic Caching

// Cache query results
$products = Cache::remember('active_products', 3600, function () {
    return Product::where('is_active', true)
        ->with('category')
        ->get();
});

Cache Tags

// Cache với tags để invalidate dễ dàng
$product = Cache::tags(['products', 'product_' . $id])
    ->remember('product_' . $id, 3600, function () use ($id) {
        return Product::with(['category', 'reviews'])->find($id);
    });

// Invalidate khi update
Cache::tags(['products'])->flush();

Query Cache Package

composer require genealabs/laravel-model-caching

// Model sử dụng caching tự động
use GeneaLabs\LaravelModelCaching\Traits\Cachable;

class Product extends Model
{
    use Cachable;
}

// Queries tự động được cache
Product::where('is_active', true)->get(); // Cached

Raw Queries khi cần thiết

Khi nên dùng Raw Queries

// Complex aggregations
$stats = DB::select("
    SELECT 
        DATE(created_at) as date,
        COUNT(*) as orders,
        SUM(total) as revenue,
        AVG(total) as avg_order
    FROM orders
    WHERE created_at >= ?
    GROUP BY DATE(created_at)
    ORDER BY date DESC
", [now()->subDays(30)]);

Raw trong Eloquent

// selectRaw()
$products = Product::selectRaw('category_id, COUNT(*) as count, AVG(price) as avg_price')
    ->groupBy('category_id')
    ->get();

// whereRaw()
$users = User::whereRaw('YEAR(created_at) = ?', [2024])->get();

// orderByRaw()
$products = Product::orderByRaw('FIELD(status, "active", "pending", "inactive")')->get();

Batch Operations

Insert Many

// ❌ Slow: Individual inserts
foreach ($data as $item) {
    Product::create($item);
}

// ✅ Fast: Batch insert
Product::insert($data); // Không trigger events

// ✅ Với timestamps
$data = array_map(function ($item) {
    $item['created_at'] = now();
    $item['updated_at'] = now();
    return $item;
}, $data);

Product::insert($data);

Upsert (Insert or Update)

// Insert hoặc update nếu tồn tại
Product::upsert(
    $products, // Data array
    ['sku'],   // Unique keys để check
    ['name', 'price', 'stock'] // Columns to update if exists
);

Batch Update

// Update nhiều records với điều kiện
Product::where('category_id', 5)
    ->update(['is_featured' => true]);

// Update với expression
Product::where('stock', '<', 10)
    ->increment('stock', 100);

Query Optimization Tools

Laravel Debugbar

composer require barryvdh/laravel-debugbar --dev

Shows:

Số queries
Query time
Duplicate queries
Memory usage

Laravel Telescope

composer require laravel/telescope --dev

Provides:

Query monitoring
Slow query detection
Request profiling

N+1 Query Detector

composer require beyondcode/laravel-query-detector --dev

Alerts khi có N+1 queries.

Real-world Example: Dashboard Stats

Before Optimization

// ❌ Multiple queries, N+1
public function getDashboardStats()
{
    $orders = Order::all();
    
    $totalRevenue = 0;
    $productsSold = 0;
    
    foreach ($orders as $order) {
        $totalRevenue += $order->total;
        foreach ($order->items as $item) { // N+1!
            $productsSold += $item->quantity;
        }
    }
    
    return [
        'orders' => $orders->count(),
        'revenue' => $totalRevenue,
        'products_sold' => $productsSold,
    ];
}
// Result: 1 + N queries, high memory

After Optimization

// ✅ Single aggregated query
public function getDashboardStats()
{
    $stats = Order::selectRaw('
        COUNT(*) as order_count,
        SUM(total) as total_revenue
    ')->first();
    
    $productsSold = OrderItem::sum('quantity');
    
    return [
        'orders' => $stats->order_count,
        'revenue' => $stats->total_revenue,
        'products_sold' => $productsSold,
    ];
}
// Result: 2 queries, minimal memory

Checklist Tối ưu

Development Phase

Enable preventLazyLoading()
Install Debugbar
Review N+1 queries

Before Production

Add proper indexes
Use eager loading
Implement caching
Review slow queries

Monitoring

Setup Telescope
Monitor query counts
Alert slow queries

Kết luận

Eloquent performance không phải magic - cần understanding và discipline:

Luôn dùng eager loading cho relationships
Select only needed columns
Index columns used in WHERE, ORDER BY
Cache expensive queries
Use chunking cho large datasets
Monitor queries trong production

Rule of thumb: 1 page request nên có < 20 queries. Nếu nhiều hơn, có gì đó cần review.