Fullstack PHP & Laravel
Trang chủ / Module 6
🗄️ Module 6 / 19 · 6 ngày 📅 Tuần 4-5 ●●●○ Khó

Database với Eloquent: N+1, index, transaction, locking, migration không downtime

Database quyết định hiệu năng và tính đúng đắn: thiết kế schema và migration, Eloquent đúng cách (eager loading, chunk/lazy, select cột), đọc EXPLAIN và index, transaction, khoá dòng chống race condition, migration không downtime.

MySQLPostgreSQLEloquentN+1IndexTransaction
📌 Vì sao module này quan trọng

Ứng dụng Laravel chậm gần như luôn do database: N+1 query ẩn trong Blade/Resource, thiếu index, Model::all() trên bảng triệu dòng. Còn sự cố sai dữ liệu (trừ kho âm, trừ tiền hai lần) đến từ không hiểu transaction và khoá. Eloquent rất tiện nhưng che mất SQL — developer PHP $5000 phải đọc được SQL nó sinh ra và biết khi nào viết query builder/raw.

🎯 Mục tiêu học tập

  • Thiết kế schema, migration và ràng buộc đúng; chọn MySQL hay PostgreSQL có lý do
  • Phát hiện và loại bỏ N+1; dùng select, withCount, chunk/lazy hiệu quả
  • Tạo index composite đúng thứ tự, đọc EXPLAIN
  • Dùng transaction, lockForUpdate, cập nhật nguyên tử, optimistic locking
  • Viết migration an toàn cho bảng lớn đang chạy production

Schema, migration, cast

Schema::create('tasks', function (Blueprint $table) {
    $table->id();
    $table->ulid('public_id')->unique();                      // id công khai không đoán được
    $table->foreignId('organization_id')->constrained()->cascadeOnDelete();
    $table->foreignId('project_id')->constrained();
    $table->foreignId('assignee_id')->nullable()->constrained('users')->nullOnDelete();
    $table->string('title', 200);
    $table->string('status', 20)->default('todo');
    $table->unsignedBigInteger('estimate_minutes')->nullable();
    $table->json('metadata')->nullable();
    $table->timestamp('due_at')->nullable();
    $table->timestamps();
    $table->index(['project_id', 'status', 'created_at']);
});

// Model
protected function casts(): array
{
    return ['status' => TaskStatus::class, 'metadata' => AsArrayObject::class, 'due_at' => 'immutable_datetime'];
}
  • Tiền lưu số nguyên (đơn vị nhỏ nhất) hoặc decimal — không float. Thời gian lưu UTC.
  • MySQL (InnoDB) phổ biến nhất trong thế giới PHP; PostgreSQL mạnh hơn về kiểu dữ liệu, jsonb, partial index, transactional DDL. Biết cả hai.
  • MySQL dùng clustered index theo khoá chính → khoá chính tăng dần (id) tốt cho ghi; ULID/UUIDv7 nếu cần id phân tán.

N+1 và Eloquent hiệu quả

// ❌ N+1: 1 query projects + N query owner + N query tasks count
foreach (Project::all() as $p) { echo $p->owner->name, $p->tasks->count(); }

// ✅ 2-3 query, chỉ cột cần thiết
$projects = Project::query()
    ->select(['id', 'name', 'owner_id'])
    ->with('owner:id,name')
    ->withCount(['tasks', 'tasks as open_tasks_count' => fn ($q) => $q->where('status', '!=', 'done')])
    ->get();

// Xử lý bảng lớn: không load hết vào RAM
Task::where('status', 'done')->chunkById(1000, fn ($tasks) => ...);   // chunkById an toàn khi sửa dữ liệu trong vòng lặp
Task::query()->lazyById(1000)->each(...);                           // generator
  • Model::preventLazyLoading() (nằm trong shouldBeStrict) biến N+1 thành exception ở môi trường dev.
  • Laravel Debugbar/Telescope đếm số query mỗi request; đặt ngưỡng trong test.
  • Báo cáo/tổng hợp nặng: query builder hoặc SQL raw có tham số (DB::select('... where id = ?', [$id])) — không ép mọi thứ qua Eloquent.

Index và EXPLAIN

EXPLAIN SELECT * FROM tasks WHERE project_id = 42 AND status = 'todo' ORDER BY created_at DESC LIMIT 20;
-- MySQL: xem type (ALL = full scan, ref/range tốt), key (index được dùng), rows (số dòng ước lượng), Extra (Using filesort)
-- PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) — Seq Scan vs Index Scan, ước lượng vs thực tế
  • Composite index: cột so sánh bằng trước, cột khoảng/sort sau; (project_id, status, created_at) phục vụ truy vấn trên cả lọc lẫn sort.
  • Không bọc cột trong hàm (WHERE DATE(created_at) = ...) — index không dùng được; viết thành khoảng.
  • LIKE '%abc%' không dùng B-tree index → full-text index hoặc search engine (Module 9).
  • Bật slow query log; mỗi index làm chậm ghi — xoá index không dùng.

Transaction, khoá và race condition

// ❌ Đọc rồi ghi: hai request cùng thấy stock = 1 → bán âm
$p = Product::find($id); if ($p->stock > 0) { $p->decrement('stock'); }

// ✅ Cập nhật nguyên tử có điều kiện
$affected = Product::whereKey($id)->where('stock', '>', 0)->decrement('stock');
if ($affected === 0) throw new OutOfStock();

// ✅ Khoá dòng khi logic phức tạp
DB::transaction(function () use ($orderId) {
    $order = Order::whereKey($orderId)->lockForUpdate()->firstOrFail();   // SELECT ... FOR UPDATE
    if ($order->status !== OrderStatus::Pending) throw new InvalidState();
    $order->update(['status' => OrderStatus::Paid]);
}, attempts: 3);                                                           // retry khi deadlock

// ✅ Optimistic locking bằng cột version
$updated = Document::whereKey($id)->where('version', $version)
    ->update(['body' => $body, 'version' => DB::raw('version + 1')]);
if (! $updated) abort(409, 'Tài liệu đã bị người khác sửa');
⚠️ Không gọi API ngoài trong transaction

Gọi Stripe hay gửi mail trong DB::transaction giữ khoá hàng giây và không rollback được việc đã gửi. Ghi trạng thái trước, gọi ngoài transaction, hoặc dispatch job afterCommit / outbox (Module 7).

Migration không downtime

  • Expand → migrate → contract: thêm cột mới nullable → code ghi cả hai → backfill theo lô (job) → code đọc cột mới → xoá cột cũ ở lần deploy sau. Không đổi tên/xoá cột đang được code cũ dùng.
  • MySQL: phần lớn ALTER trên InnoDB 8.0 là online (ALGORITHM=INSTANT/INPLACE), nhưng vẫn cần metadata lock — một transaction dài có thể làm ALTER chờ và chặn cả bảng. Bảng rất lớn: gh-ost / pt-online-schema-change.
  • PostgreSQL: CREATE INDEX CONCURRENTLY (trong migration Laravel cần $withinTransaction = false), đặt lock_timeout.
  • Rollback code phải chạy được với schema mới — viết migration tương thích ngược.

🏋️ Bài tập thực hành

Dữ liệu thật, tải thật

(1) Hoàn thiện schema multi-tenant (organization, membership, project, task, comment, attachment) với FK, unique, index. (2) Seed 1 triệu task bằng factory theo lô; đo truy vấn danh sách trước/sau index, lưu EXPLAIN vào README. (3) Bật preventLazyLoading, sửa hết N+1 trong API. (4) Endpoint "nhận task" chỉ một người nhận được — script bắn 50 request song song chứng minh đúng. (5) Optimistic locking cho mô tả task, trả 409 khi xung đột. (6) Viết một migration đổi tên cột theo expand/contract qua 3 PR.

📚 Tài nguyên học tập

✅ Tự đánh giá hoàn thành

  • Không còn N+1; preventLazyLoading bật ở dev
  • Đọc được EXPLAIN và chứng minh index có hiệu quả
  • Giải được race condition bằng cập nhật nguyên tử và lockForUpdate
  • Biết quy trình migration expand/contract