Làm chủ Common Table Expressions (CTE) và Recursive CTE trong MySQL 8: Tối ưu subquery và xử lý dữ liệu phân cấp

Quick start: Viết CTE đầu tiên trong 3 phút

Ai từng bảo trì những file SQL dài 300 dòng với 4-5 tầng SUBQUERY lồng nhau đều hiểu cảm giác hoa mắt khi tìm bug. CTE (Common Table Expression) giải quyết triệt để vấn đề này.

Về bản chất, CTE tạo ra một bảng tạm có tên ngay trong phiên chạy của truy vấn thông qua từ khóa WITH. Thay vì nhét cả cụm subquery vào mệnh đề FROM, bạn đưa toàn bộ khối tính toán lên đầu. Luồng đọc code chuyển về dạng từ trên xuống dưới, tương tự cách gán biến trong code backend.

Dưới đây là bảng dữ liệu 5 đơn hàng mẫu để chạy thử ngay trên MySQL 8.0+:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    order_date DATE
);

INSERT INTO orders VALUES 
(1, 101, 500, '2026-01-10'),
(2, 102, 1200, '2026-01-12'),
(3, 101, 700, '2026-02-01'),
(4, 103, 300, '2026-02-05'),
(5, 102, 400, '2026-02-15');

-- Lọc khách hàng có tổng chi tiêu trên 1.000 USD
WITH customer_spending AS (
    SELECT 
        customer_id,
        SUM(amount) AS total_spent,
        COUNT(order_id) AS total_orders
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total_spent, total_orders
FROM customer_spending
WHERE total_spent > 1000;

Logic truy vấn được gom gọn trong khối customer_spending. Câu lệnh SELECT cuối cùng chỉ việc gọi lại bảng này như một table vật lý thông thường.

Bản chất kỹ thuật của Standard CTE

1. Cú pháp cơ bản và kỹ thuật xâu chuỗi (Chaining)

Cú pháp chuẩn bắt đầu bằng WITH, theo sau là tên định danh và khối lệnh con đặt trong cặp ngoặc đơn ():

WITH cte_name (col1, col2) AS (
    SELECT col_a, col_b FROM table_name
)
SELECT * FROM cte_name;

Sức mạnh thực sự của CTE nằm ở khả năng xâu chuỗi. Bạn có thể khai báo nhiều CTE liên tiếp bằng dấu phẩy. CTE phía sau được phép gọi trực tiếp kết quả của CTE phía trước:

WITH 
january_orders AS (
    SELECT customer_id, amount 
    FROM orders 
    WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
),
loyal_customers AS (
    SELECT customer_id, SUM(amount) AS jan_total
    FROM january_orders
    GROUP BY customer_id
    HAVING jan_total >= 500
)
SELECT * FROM loyal_customers;

2. So sánh CTE, Subquery và Temporary Table

Tiêu chí	CTE (WITH)	Derived Table (Subquery)	Temporary Table
Vòng đời	Chỉ tồn tại trong 1 query	Chỉ tồn tại trong 1 query	Tồn tại suốt session kết nối
Tái sử dụng	Gọi nhiều lần trong cùng query	Phải viết lặp lại subquery	Gọi nhiều lần ở các query khác nhau
Bộ nhớ & I/O	RAM (Merge hoặc In-memory tmp)	Tương tự CTE	Tốn chi phí cấp phát và I/O đĩa
Khả năng debug	Rất cao (từng khối độc lập)	Kém khi lồng quá 3 tầng	Trung bình

Xử lý dữ liệu phân cấp với Recursive CTE

Trước bản 8.0, việc duyệt cây thư mục hoặc chuỗi bình luận cha – con trong MySQL buộc dev phải dùng Stored Procedure hoặc mô hình Nested Sets phức tạp. Recursive CTE giải quyết gọn bài toán này bằng đệ quy toán học.

Cấu tạo của một truy vấn đệ quy

Một cú pháp WITH RECURSIVE hợp lệ luôn gồm 3 thành phần:

1. Anchor Member (Neo gốc): Truy vấn lấy tầng đầu tiên của cây (thường là nút Root), không tham chiếu đến CTE.
2. Toán tử UNION ALL: Gắn kết dữ liệu giữa tầng cha và tầng con.
3. Recursive Member: Khối truy vấn JOIN ngược lại với chính CTE đó để lấy tầng tiếp theo. Quá trình dừng khi không còn bản ghi nào thỏa mãn.

Bài toán thực tế: Duyệt cây nhân sự từ bảng 6 cấp

Dựng bảng nhân viên mẫu với liên kết cha – con qua trường manager_id:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO Nguyễn Văn A', NULL),
(2, 'CTO Trần Thị B', 1),
(3, 'CFO Lê Văn C', 1),
(4, 'Dev Lead Phạm Văn D', 2),
(5, 'Senior Dev Hoàng Thị E', 4),
(6, 'Junior Dev Đặng Văn F', 5);

Yêu cầu: In ra cây quản lý từ CEO xuống Junior Dev, hiển thị rõ độ sâu (level) và đường dẫn phân cấp (path).

WITH RECURSIVE org_tree AS (
    -- 1. Anchor: Lấy nhân sự cấp cao nhất (không có quản lý)
    SELECT 
        employee_id, 
        name, 
        manager_id, 
        1 AS level,
        CAST(name AS CHAR(255)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 2. Recursive: Nối tầng tiếp theo
    SELECT 
        e.employee_id, 
        e.name, 
        e.manager_id, 
        ot.level + 1 AS level,
        CONCAT(ot.path, ' -> ', e.name) AS path
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT employee_id, name, level, path 
FROM org_tree
ORDER BY level, employee_id;

Lưu ý kỹ thuật: Bắt buộc dùng CAST(name AS CHAR(255)) ở phần Anchor. Nếu bỏ qua, MySQL sẽ cố định độ dài cột path bằng đúng số ký tự của dòng đầu tiên (15 ký tự của CEO), dẫn tới lỗi Data truncation: Data too long for column ở các tầng con.

Tạo chuỗi dữ liệu (Data Generator) lấp khoảng trống báo cáo

Khi vẽ biểu đồ doanh thu 30 ngày gần nhất, những ngày không có đơn hàng thường bị hụt trên biểu đồ. Dùng CTE đệ quy để sinh dải ngày chuẩn mà không cần tạo bảng phụ:

WITH RECURSIVE date_range AS (
    SELECT CAST('2026-03-01' AS DATE) AS report_date
    UNION ALL
    SELECT report_date + INTERVAL 1 DAY
    FROM date_range
    WHERE report_date < '2026-03-07'
)
SELECT report_date FROM date_range;

Kinh nghiệm tối ưu hiệu năng trên Production

1. Cơ chế phòng thủ vòng lặp vô tận (Infinite Loop)

Lỗi dữ liệu vòng tròn là ác mộng của đệ quy: A quản lý B, B quản lý C, nhưng C lại trỏ ngược về A. Query sẽ chạy không hồi kết, đẩy CPU lên 100%.

MySQL cung cấp biến cte_max_recursion_depth (mặc định là 1.000 vòng). Bạn nên chủ động siết lại con số này ngay trong câu truy vấn thông qua optimizer hint SET_VAR:

-- Chặn cứng tối đa 20 cấp đệ quy
WITH RECURSIVE org_tree AS (
    SELECT /*+ SET_VAR(cte_max_recursion_depth = 20) */
        employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT * FROM org_tree;

2. CTE không tự động giúp query chạy nhanh hơn

CTE chỉ là đường tắt về mặt cú pháp (syntactic sugar). Nó không thay thế được việc thiết kế chỉ mục (index).

Bộ tối ưu (Optimizer) của MySQL xử lý CTE qua 2 chiến lược:

• Merge: Gộp logic CTE trực tiếp vào query mẹ. Cách này giữ nguyên được index scan từ bảng gốc.
• Materialize: Tính toán toàn bộ kết quả CTE trước rồi lưu tạm vào RAM (hoặc đĩa nếu vượt quá ngưỡng tmp_table_size, mặc định 16MB). Bảng tạm này không có index. Query mẹ sẽ phải Full Scan bảng tạm đó.

Nếu trong CTE chứa GROUP BY, DISTINCT hoặc UNION, MySQL bắt buộc phải Materialize. Khi bảng nguồn đạt mốc từ 1-2 triệu dòng trở lên, query sẽ nghẽn nghiêm trọng.

Quy tắc kiểm tra trước khi deploy:

1. Luôn chạy EXPLAIN ANALYZE để kiểm tra xem MySQL dùng Materialize hay Merge.
2. Đánh index composite cho các cặp khóa ngoại dùng để đệ quy (ví dụ CREATE INDEX idx_manager ON employees(manager_id, employee_id);).
3. Chủ động ép optimizer bằng hint nếu cần: /*+ MERGE(cte_name) */ hoặc /*+ NO_MERGE(cte_name) */ trên MySQL 8.0.21+.