Thiết kế Database (Database Design) cho hệ thống Activity Log quy mô lớn: Tối ưu lưu trữ và hiệu năng truy vấn

Chào anh em!

Ở bài viết trước, chúng ta đã tìm hiểu về package spatie/laravel-activitylog — "hộp đen" giúp ghi nhận lịch sử hoạt động cực kỳ tiện lợi trong Laravel [cite: 17]. Tuy nhiên, câu hỏi đặt ra là: Bên dưới tầng cơ sở dữ liệu, cấu trúc bảng activity_log được thiết kế ra sao để có thể gánh hàng triệu, thậm chí hàng chục triệu dòng log mà không làm chậm hệ thống?

Hôm nay, hãy cùng mổ xẻ và tự tay thiết kế một cơ sở dữ liệu chuẩn chỉnh dành riêng cho phần Activity Log / Audit Trail từ góc độ kiến trúc Database nhé!

1. Thách thức khi thiết kế bảng Activity Log

Khác với các bảng dữ liệu thông thường (như users hay products), bảng lưu lịch sử hoạt động có những đặc thù riêng biệt:

• Tốc độ ghi cực lớn (High Write Volume): Mỗi một thao tác của người dùng đều sinh ra dòng log mới. Bảng sẽ phình to rất nhanh.
• Ít khi cập nhật hoặc xóa (Write-Once, Read-Rarely): Dữ liệu log sinh ra là để đọc lại (khi kiểm toán hoặc tra cứu) chứ rất ít khi bị sửa đổi.
• Cấu trúc dữ liệu biến đổi (Dynamic Payload): Mỗi model khác nhau sẽ có các trường thay đổi khác nhau, đòi hỏi thiết kế linh hoạt.

2. Thiết kế cấu trúc bảng activity_logs chuẩn mực

Dưới đây là cấu trúc bảng tối ưu được áp dụng trong các hệ thống lớn:

CREATE TABLE `activity_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    
    -- Phân loại log (Ví dụ: auth, order, product, system)
    `log_name` VARCHAR(191) NULL,
    
    -- Nội dung thông điệp mô tả ngắn gọn hành động
    `description` TEXT NOT NULL,
    
    -- Quan hệ Đa hình (Polymorphic) tới Model bị tác động (Ví dụ: bảng posts, products)
    `subject_type` VARCHAR(191) NULL,
    `subject_id` BIGINT UNSIGNED NULL,
    
    -- Quan hệ Đa hình tới Người thực hiện hành động (Thường là bảng users)
    `causer_type` VARCHAR(191) NULL,
    `causer_id` BIGINT UNSIGNED NULL,
    
    -- Lưu trữ chi tiết thay đổi (Old values & New values) dưới dạng JSON linh hoạt
    `properties` JSON NULL,
    
    -- Định danh nhóm sự kiện (Dùng để gom nhóm các log chạy chung một request)
    `batch_uuid` CHAR(36) NULL,
    
    `created_at` TIMESTAMP NULL,
    `updated_at` TIMESTAMP NULL,
    
    -- Đánh Index tối ưu hiệu năng tìm kiếm
    INDEX `idx_subject` (`subject_type`, `subject_id`),
    INDEX `idx_causer` (`causer_type`, `causer_id`),
    INDEX `idx_log_name` (`log_name`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. Phân tích chi tiết từng trường dữ liệu (Columns)

• log_name: Giúp phân tách các nhóm log khác nhau (ví dụ: auth cho đăng nhập, ecommerce cho đơn hàng). Điều này giúp việc lọc dữ liệu theo phân hệ trở nên cực kỳ nhanh chóng.
• Cặp subject_* và causer_* (Polymorphic Pattern):
  • subject: Đại diện cho đối tượng bị tác động (Ví dụ: Sản phẩm ID số 5 bị sửa).
  • causer: Đại diện cho tác nhân gây ra hành động (Ví dụ: User ID số 123 là người bấm nút sửa).
• Trường properties (Kiểu dữ liệu JSON): Đây là "vũ khí tối thượng" trong các hệ thống hiện đại. Thay vì đẻ ra hàng chục cột phụ, ta dùng kiểu JSON của MySQL để chứa mảng dữ liệu thay đổi (old và attributes). MySQL hỗ trợ đánh index trực tiếp trên các trường con của JSON, vừa linh hoạt vừa tiết kiệm không gian lưu trữ.
• Trường batch_uuid: Trong các thao tác lớn (ví dụ: Admin bấm nút duyệt đồng thời 50 sản phẩm), hệ thống sẽ sinh ra một mã UUID chung gắn vào tất cả các dòng log của batch đó, giúp gom nhóm lịch sử lại để hiển thị cho người quản trị dễ dàng theo dõi.

4. Chiến lược tối ưu hiệu năng (Performance Optimization) cho hệ thống lớn

Khi bảng activity_logs chạm ngưỡng hàng chục triệu bản ghi, việc truy vấn trực tiếp sẽ bắt đầu chậm lại. Bạn cần áp dụng các chiến lược sau:

A. Đánh Index thông minh (Strategic Indexing)

Như thiết kế ở trên, các cột tham gia vào mệnh đề WHERE hoặc JOIN như log_name, created_at, hay cặp subject/causer bắt buộc phải được đánh Index. Tuy nhiên, tránh lạm dụng đánh index quá nhiều cột vì sẽ làm chậm tốc độ ghi (INSERT).

B. Phân vùng bảng (Table Partitioning theo thời gian)

Đối với các hệ thống enterprise cực lớn, bạn nên áp dụng Partitioning theo khoảng thời gian (Range Partitioning) dựa trên trường created_at (Ví dụ: Mỗi tháng một bảng con phân vùng). Khi cần tra cứu log cũ, hệ thống chỉ quét trên phân vùng tháng đó thay vì quét toàn bộ bảng.

C. Lưu trữ trượt và Dọn dẹp định kỳ (Data Archiving / Pruning)

Không ai giữ log vĩnh viễn trên Database chính nếu không thực sự cần thiết (thường chỉ giữ từ 3 đến 6 tháng gần nhất). Hãy viết một câu lệnh chạy ngầm hàng tháng (Cronjob) để dọn dẹp hoặc chuyển các log cũ sang kho lưu trữ lạnh (Cold Storage / S3 / Elasticsearch):

-- Xóa các log cũ hơn 180 ngày
DELETE FROM activity_logs WHERE created_at < NOW() - INTERVAL 180 DAY;

Lời kết

Thiết kế Database cho hệ thống Activity Log không đơn thuần là tạo một bảng chứa chữ, mà là bài toán cân bằng giữa tính linh hoạt của dữ liệu (JSON properties), khả năng truy xuất nhanh (Indexes) và chiến lược lưu trữ lâu dài. Nắm vững mô hình này, bạn hoàn toàn có thể tự tin xây dựng một hệ thống kiểm toán (Audit Trail) vững chắc cho bất kỳ dự án Backend quy mô lớn nào!