Quản lý dữ liệu phân cấp trong MySQL: Đừng để ‘Cây danh mục’ làm treo Server

Khi cây danh mục ‘nuốt chửng’ tài nguyên hệ thống

2 giờ sáng, điện thoại mình báo động liên tục. Dashboard giám sát cho thấy CPU của Database nhảy vọt lên 100% rồi đứng im tại đó. Sau khi truy vết, mình phát hiện một câu query lấy danh mục sản phẩm đang chạy ‘vô tận’.

Lúc đó, hệ thống dùng Adjacency List (mô hình cha-con đơn giản). Khi cây danh mục chạm ngưỡng 15 cấp với hơn 20.000 bản ghi, các câu lệnh đệ quy (Recursive CTE) bắt đầu vắt kiệt sức mạnh server. Đây là bài học đắt giá về việc chọn sai cấu trúc dữ liệu ngay từ đầu.

Dưới đây là 3 kỹ thuật phổ biến để quản lý dữ liệu dạng cây trong MySQL mà mình đã đúc kết sau nhiều lần xử lý sự cố thực tế.

1. Adjacency List: Đơn giản nhưng dễ hụt hơi

Đây là cách tiếp cận bản năng nhất của mọi lập trình viên. Bạn chỉ cần thêm một cột parent_id để trỏ về ID của bản ghi cha.

Cấu trúc bảng

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_id INT DEFAULT NULL,
    INDEX (parent_id),
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

Đánh giá thực tế

• Ưu điểm: Thêm mới hoặc di chuyển một nhánh cực nhanh. Bạn chỉ cần cập nhật duy nhất một giá trị parent_id.
• Nhược điểm: Truy vấn lấy toàn bộ cây rất tốn kém. Với MySQL dưới 8.0, bạn phải dùng code ứng dụng để đệ quy. Từ MySQL 8.0 trở đi, dù có CTE hỗ trợ, hiệu năng vẫn tụt dốc không phanh khi độ sâu của cây tăng lên.

Ví dụ truy vấn với CTE (MySQL 8.0+)

WITH RECURSIVE category_path (id, name, path) AS (
    SELECT id, name, name as path
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, CONCAT(cp.path, ' > ', c.name)
    FROM category_path cp JOIN categories c
    ON cp.id = c.parent_id
)
SELECT * FROM category_path;

2. Nested Set Model: Tối ưu cho việc đọc, ‘ác mộng’ khi ghi

Mô hình này loại bỏ parent_id. Thay vào đó, nó dùng hai giá trị lft (left) và rgt (right) để bao bọc các node con. Hãy tưởng tượng mỗi node là một chiếc hộp, node con là hộp nhỏ nằm gọn trong hộp lớn.

Cách hoạt động

Để lấy toàn bộ con cháu của một node, bạn không cần đệ quy. Chỉ cần một câu lệnh BETWEEN đơn giản:

SELECT * FROM nested_categories 
WHERE lft BETWEEN 10 AND 25 
ORDER BY lft ASC;

Ưu và nhược điểm

• Ưu điểm: Tốc độ đọc nhanh kinh ngạc. Nó cực kỳ phù hợp cho các trang tin tức hoặc danh mục sản phẩm ít thay đổi.
• Nhược điểm: Thao tác ghi là một cực hình. Khi bạn chèn thêm một node vào giữa, MySQL phải cập nhật lại giá trị lft và rgt của một nửa bảng. Với bảng có 100.000 rows, một lệnh chèn có thể gây lock toàn bộ bảng trong vài giây.

3. Closure Table: Giải pháp cân bằng và hiện đại

Đây là kỹ thuật mình ưu tiên dùng cho các dự án lớn. Thay vì lưu quan hệ trong bảng chính, chúng ta tách ra một bảng phụ để lưu mọi đường đi giữa các node (path).

Cấu trúc bảng

CREATE TABLE category_hierarchy (
    ancestor INT NOT NULL, -- ID tổ tiên
    descendant INT NOT NULL, -- ID con cháu
    path_length INT NOT NULL, -- Độ sâu
    PRIMARY KEY (ancestor, descendant)
);

Tại sao nên dùng Closure Table?

Nó giải quyết được cả hai vấn đề: đọc nhanh và ghi không quá chậm. Để tìm tất cả con cháu của node 1, bạn chỉ cần join bảng phụ. Việc di chuyển cả một nhánh cây cũng chỉ tốn vài câu lệnh DELETE và INSERT đơn giản trên bảng quan hệ.

• Ưu điểm: Linh hoạt nhất, hỗ trợ một node có nhiều cha (đa phân cấp).
• Nhược điểm: Tốn dung lượng ổ cứng. Với một cây sâu 10 cấp, mỗi bản ghi mới có thể tạo ra thêm 11 dòng trong bảng quan hệ.

Bảng so sánh hiệu năng

Kinh nghiệm thực chiến cho bạn

Đừng cố tìm mô hình hoàn hảo nhất, hãy tìm cái phù hợp nhất. Nếu bạn làm app Todo đơn giản, hãy dùng Adjacency List. Nếu làm hệ thống E-commerce với hàng triệu lượt view mỗi ngày, Closure Table là lựa chọn an toàn nhất để kê cao gối ngủ.

Trong sự cố lúc 2 giờ sáng năm đó, mình đã phải dùng Redis để cache tạm thời toàn bộ cây danh mục nhằm cứu server. Sau đó, team mất 3 ngày để chuyển đổi hoàn toàn sang Closure Table. Kết quả thật bất ngờ: CPU load giảm từ 90% xuống còn dưới 10%, và quan trọng nhất là mình không còn bị dựng dậy giữa đêm nữa.