Posted inDatabase

Tái Cấu Trúc SQL: Cách Chuyển Đổi Subquery Lồng Nhau sang CTE để Tăng Hiệu Suất và Dễ Đọc

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Vấn Đề với Subquery Lồng Nhau Sâu

Sáu tháng làm việc trong một dự án, một đồng nghiệp đưa cho tôi một câu SQL query đã phình to đến mức tôi chỉ có thể gọi là mê cung. Ba tầng subquery lồng nhau, mỗi tầng tham chiếu đến tầng bên trong, và một mệnh đề WHERE kéo dài mười hai dòng. Query này chạy mất 8 giây trên bảng có 2 triệu hàng.

Query đó không phải được viết cẩu thả một cách có chủ ý — nó lớn dần một cách tự nhiên. Một developer cần tập dữ liệu đã lọc, viết một subquery. Sau đó, ai đó cần tổng hợp thêm, thêm một tầng nữa. Cuối cùng query đúng về mặt kỹ thuật nhưng thực tế không thể bảo trì được.

Đã làm việc với MySQL, PostgreSQL và SQL Server trong nhiều dự án khác nhau, mỗi hệ thống có quirk riêng của optimizer — nhưng subquery lồng nhau đều dễ trở nên rối rắm trên tất cả chúng. Optimizer có thể xử lý tốt hoặc không, và quan trọng hơn, con người chắc chắn không xử lý tốt sau vài tháng không đụng đến code.

Điều Gì Khiến Subquery Lồng Nhau Khó Làm Việc

  • Khả năng đọc: Bạn phải đọc từ trong ra ngoài. Query trong cùng thực thi trước, nhưng nó lại bị chôn vùi ở giữa đoạn code.
  • Debug: Để kiểm tra một tầng cụ thể, bạn phải trích xuất thủ công mỗi lần.
  • Lặp lại: Nếu hai phần của query cần cùng một tập dữ liệu đã lọc, bạn phải viết subquery hai lần.
  • Hành vi optimizer: Một số cơ sở dữ liệu (đặc biệt MySQL trước 8.0) materialized correlated subquery theo những cách không rõ ràng, gây ra nhiều lần quét toàn bảng.

CTE Là Gì và Cách Chúng Hoạt Động

Common Table Expression (CTE) — được giới thiệu bằng từ khóa WITH — cho phép bạn định nghĩa một tập kết quả tạm thời có tên trước query chính. Hãy nghĩ nó như việc đặt tên cho một đoạn logic để bạn có thể tham chiếu một cách rõ ràng, thay vì lồng nó vào giữa SQL.

Cú pháp cơ bản:

WITH cte_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM cte_name
WHERE ...;

Bạn có thể định nghĩa nhiều CTE trong một mệnh đề WITH bằng cách ngăn cách chúng bằng dấu phẩy:

WITH
  active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
  ),
  recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= NOW() - INTERVAL '30 days'
    GROUP BY user_id
  )
SELECT u.name, u.email, COALESCE(o.order_count, 0) AS orders
FROM active_users u
LEFT JOIN recent_orders o ON u.id = o.user_id;

PostgreSQL, MySQL 8.0+ và SQL Server đều hỗ trợ cú pháp này. Với các phiên bản MySQL cũ hơn, giải pháp thay thế thường là bảng tạm hoặc view — một lý do nữa để cập nhật phiên bản cơ sở dữ liệu.

CTE Materialized và Non-Materialized

Trước khi bắt đầu chuyển đổi tất cả, hãy biết rằng CTE không phải lúc nào cũng tương đương với subquery về mặt execution plan.

  • PostgreSQL (trước 12): CTE luôn được materialized — được đánh giá một lần, kết quả lưu tạm thời. Điều này có thể giúp hoặc làm giảm hiệu suất tùy thuộc vào số lần CTE được tham chiếu và kích thước tập kết quả.
  • PostgreSQL 12+: Optimizer giờ có thể inline CTE khi chúng chỉ được tham chiếu một lần. Bạn cũng có thể buộc materialization với WITH cte AS MATERIALIZED (...).
  • MySQL 8.0+: CTE thường không được materialized (inline) trừ khi optimizer quyết định khác.

Hãy ghi nhớ điều này khi tái cấu trúc vì hiệu suất. Một CTE được tham chiếu ba lần trong query có thể thực thi ba lần trên một số cơ sở dữ liệu nếu không được materialized.

Tái Cấu Trúc Subquery Lồng Nhau: Từng Bước

Cách tiếp cận đơn giản về nguyên tắc: bắt đầu với subquery trong cùng, đặt tên cho nó và làm từng bước ra ngoài. Mỗi tầng trở thành một khối có thể đọc và kiểm tra được thay vì bị chôn vùi trong code.

Ví Dụ 1 — Subquery Lồng Hai Tầng

Đây là một query tìm tên bộ phận có mức lương nhân viên trung bình cao hơn mức trung bình toàn công ty:

-- Trước: lồng nhau, lặp lại
SELECT department_name
FROM departments
WHERE id IN (
    SELECT department_id
    FROM employees
    WHERE salary > (
        SELECT AVG(salary)
        FROM employees
    )
    GROUP BY department_id
    HAVING AVG(salary) > (
        SELECT AVG(salary)
        FROM employees
    )
);

Lưu ý rằng SELECT AVG(salary) FROM employees xuất hiện hai lần. Cơ sở dữ liệu có thể thực thi nó hai lần tùy thuộc vào engine và phiên bản.

-- Sau: phiên bản CTE
WITH
  company_avg AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
  ),
  high_earning_depts AS (
    SELECT department_id
    FROM employees
    CROSS JOIN company_avg
    WHERE salary > company_avg.avg_salary
    GROUP BY department_id
    HAVING AVG(salary) > (SELECT avg_salary FROM company_avg)
  )
SELECT d.department_name
FROM departments d
INNER JOIN high_earning_depts hed ON d.id = hed.department_id;

CTE company_avg được định nghĩa một lần. Query đọc từ trên xuống dưới: định nghĩa mức trung bình toàn công ty, tìm bộ phận đáp ứng ngưỡng, tra cứu tên bộ phận.

Ví Dụ 2 — Ba Tầng Lồng Nhau

Ba tầng sâu? CTE có tên xử lý điều đó dễ dàng. Giả sử bạn cần các khách hàng đã đặt đơn hàng chứa sản phẩm từ nhà cung cấp #42, và những khách hàng đó phải có tài khoản hơn một năm:

-- Trước: ba tầng sâu
SELECT customer_name
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE id IN (
        SELECT order_id
        FROM order_items
        WHERE product_id IN (
            SELECT id
            FROM products
            WHERE supplier_id = 42
        )
    )
)
AND account_created < NOW() - INTERVAL '1 year';

-- Sau: các tầng có tên
WITH
  supplier_products AS (
    SELECT id AS product_id
    FROM products
    WHERE supplier_id = 42
  ),
  qualifying_orders AS (
    SELECT DISTINCT oi.order_id
    FROM order_items oi
    INNER JOIN supplier_products sp ON oi.product_id = sp.product_id
  ),
  qualifying_customers AS (
    SELECT DISTINCT o.customer_id
    FROM orders o
    INNER JOIN qualifying_orders qo ON o.id = qo.order_id
  )
SELECT c.customer_name
FROM customers c
INNER JOIN qualifying_customers qc ON c.id = qc.customer_id
WHERE c.account_created < NOW() - INTERVAL '1 year';

Bạn giờ có thể chạy từng CTE độc lập để xác minh kết quả. Debug không còn là công việc khảo cổ — bạn cô lập chính xác tầng nào tạo ra kết quả sai, sửa nó và tiếp tục.

Xác Minh và Theo Dõi Hiệu Suất

Luôn Xác Nhận Kết Quả Khớp Trước

Trước khi giả định query đã tái cấu trúc của bạn tương đương, hãy xác minh bằng một so sánh nhanh. Chạy cả hai query trên cùng tập dữ liệu và kiểm tra số hàng khớp:

-- So sánh số lượng hàng
SELECT COUNT(*) FROM ( /* phiên bản lồng nhau gốc */ ) AS old_q;
SELECT COUNT(*) FROM ( /* phiên bản CTE */ ) AS new_q;

-- PostgreSQL: so sánh checksum trên tập kết quả
SELECT md5(string_agg(customer_name::text, '' ORDER BY customer_name))
FROM ( /* query */ ) q;

Dùng EXPLAIN ANALYZE để So Sánh Execution Plan

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH supplier_products AS (
  SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;

-- MySQL 8.0+
EXPLAIN FORMAT=JSON
WITH supplier_products AS (
  SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;

Chú ý cụ thể vào:

  • Seq Scan vs Index Scan: CTE không phải lúc nào cũng đẩy predicates qua — đôi khi CTE buộc quét toàn bộ tập kết quả trung gian.
  • Actual rows vs estimated rows: Sự chênh lệch lớn cho thấy thống kê đã cũ. Chạy ANALYZE table_name trong PostgreSQL hoặc ANALYZE TABLE table_name trong MySQL để làm mới chúng.
  • CTE Scan nodes (PostgreSQL): Nếu bạn thấy “CTE Scan” trên tập dữ liệu lớn chỉ được tham chiếu một lần, hãy thêm NOT MATERIALIZED (PostgreSQL 12+) để optimizer inline nó.
-- Buộc inline trong PostgreSQL 12+
WITH supplier_products AS NOT MATERIALIZED (
  SELECT id FROM products WHERE supplier_id = 42
)
SELECT ...;

Khi CTE Không Giúp Ích cho Hiệu Suất

CTE hầu như luôn cải thiện khả năng đọc. Hiệu suất là câu hỏi khác. Hai tình huống mà subquery có thể vẫn hoạt động tốt hơn CTE:

  1. Correlated subquery đơn giá trị trong SELECT: Nếu bạn đang lấy một giá trị tổng hợp mỗi hàng và có covering index, optimizer đôi khi có thể rút gọn sớm. Một CTE join có thể làm nhiều việc hơn cần thiết.
  2. Tập kết quả subquery rất nhỏ: Nếu một subquery trả về một vài ID từ bảng cấu hình, optimizer thường có thể đẩy bộ lọc sâu vào execution plan mà không cần materialized bất kỳ thứ gì. Bọc nó trong CTE thêm một tầng có thể ngăn chặn tối ưu hóa đó.

Quy tắc kinh nghiệm của tôi: tái cấu trúc vì khả năng đọc trước, sau đó đo lường bằng EXPLAIN. Nếu phiên bản CTE chậm hơn, hãy thêm index trước khi hoàn nguyên — chín trên mười lần, nút cổ chai là thiếu index, không phải bản thân cấu trúc CTE.

Lưu Ý về CTE Đệ Quy

CTE cũng hỗ trợ đệ quy bằng WITH RECURSIVE, mở khóa các query phân cấp — sơ đồ tổ chức, cấu trúc cây, duyệt đồ thị — mà nếu không sẽ cần vòng lặp ở tầng ứng dụng hoặc nhiều lần round trip đến cơ sở dữ liệu. Đó là một chủ đề riêng. Hãy nắm vững CTE không đệ quy trước; đệ quy xây dựng trên cùng nền tảng đó.

Share:
Tags: