Và giờ là lúc nói về mặt tối của việc dùng CTE — những lỗi thường gặp. Dù query của bạn có xịn cỡ nào thì cũng có thể toang nếu dùng mấy công cụ mạnh này sai cách. Nhưng đừng lo, tụi mình có hẳn một hướng dẫn để bạn bắt bệnh và phòng tránh luôn nhé!
1. Lỗi: materialization của CTE và hậu quả
Một trong những đặc điểm chính của PostgreSQL khi làm việc với CTE là materialization mặc định. Nghĩa là kết quả của CTE sẽ được xử lý và lưu tạm vào RAM (hoặc ổ cứng nếu dữ liệu quá nhiều). Nếu query nhiều hoặc dữ liệu lớn, nó có thể làm chậm hẳn việc thực thi.
Ví dụ:
WITH heavy_data AS (
SELECT * FROM large_table
)
SELECT * FROM heavy_data WHERE column_a > 100;
Nhìn qua thì tưởng CTE chỉ lọc dữ liệu thôi. Nhưng thực tế là heavy_data sẽ được load và materialize toàn bộ trước, rồi mới lọc. Cực kỳ tốn thời gian luôn.
Làm sao tránh?
Từ PostgreSQL version 12, bạn có thể dùng CTE như inline expression (kiểu như subquery), giải quyết luôn vụ materialization. Chỉ cần dùng CTE nào mà chỉ xài một lần, không cần lưu kết quả trung gian là được.
Ví dụ cách tối ưu:
WITH inline_data AS MATERIALIZED (
SELECT * FROM large_table
)
SELECT * FROM inline_data WHERE column_a > 100;
Mẹo: nếu bạn muốn materialization vẫn xảy ra, hãy ghi MATERIALIZED. Nếu không — dùng NOT MATERIALIZED nhé.
2. Lỗi: CTE đệ quy bị lặp vô tận
CTE đệ quy mạnh thật, nhưng nếu không giới hạn số vòng lặp thì dễ bị lặp vô tận. Không chỉ làm chậm mà còn ngốn hết tài nguyên luôn.
Ví dụ:
WITH RECURSIVE endless_loop AS (
SELECT 1 AS value
UNION ALL
SELECT value + 1
FROM endless_loop
)
SELECT * FROM endless_loop;Cái này sẽ tạo ra vô số dòng vì không có điều kiện dừng recursion.
Làm sao tránh?
Thêm điều kiện dừng rõ ràng với WHERE. Ví dụ:
WITH RECURSIVE limited_loop AS (
SELECT 1 AS value
UNION ALL
SELECT value + 1
FROM limited_loop
WHERE value < 10
)
SELECT * FROM limited_loop;Mẹo: nếu bạn dùng CTE đệ quy cho các hierarchy lớn, hãy giới hạn độ sâu recursion bằng option max_recursion_depth của PostgreSQL.
3. Lỗi: dùng sai UNION và UNION ALL
Khi bạn kết hợp query cơ bản và query đệ quy trong CTE, chọn sai giữa UNION và UNION ALL có thể gây ra kết quả bất ngờ. Ví dụ, UNION sẽ loại bỏ các dòng trùng, và việc này tốn thêm tài nguyên tính toán.
Ví dụ:
WITH RECURSIVE employee_hierarchy AS (
SELECT employee_id, manager_id
FROM employees
WHERE manager_id IS NULL
UNION -- Ở đây nên dùng UNION ALL
SELECT e.employee_id, e.manager_id
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;Trong ví dụ trên, UNION có thể xóa mất những dòng quan trọng trong hierarchy nếu bị trùng. Và còn làm query chậm nữa!
Sửa sao?
Dùng UNION ALL nếu bạn không thực sự cần loại bỏ dòng trùng:
UNION ALL
4. Lỗi: quá nhiều CTE trong một query
Cố gắng làm query thật rõ ràng, nhiều bạn lại thêm cả chục CTE. Không chỉ làm code rối mà còn làm PostgreSQL query planner bị quá tải.
Ví dụ:
WITH cte1 AS (...),
cte2 AS (...),
cte3 AS (...),
...
cte20 AS (...)
SELECT ...
FROM cte20;
Nhìn như ác mộng cho bất kỳ dev nào luôn.
Sửa sao?
— Chia query thành nhiều cái nhỏ hơn. Đừng nhồi hết vào một mega-query với cả đống CTE — hãy làm vài query độc lập.
— Hoặc: với kết quả trung gian cần dùng nhiều lần, hãy lưu vào bảng tạm.
5. Lỗi: CTE phức tạp mà không có index
Nếu CTE xử lý nhiều dữ liệu mà bạn quên tạo index cho bảng, query sẽ chạy siêu chậm. Index giống như doping cho database của bạn vậy.
Ví dụ:
WITH filtered_data AS (
SELECT * FROM large_table WHERE unindexed_column = 'value'
)
SELECT * FROM filtered_data;
Sửa sao?
Trước khi dùng CTE, hãy chắc là bảng của bạn đã được tối ưu:
CREATE INDEX idx_large_table ON large_table(unindexed_column);
6. Lỗi: cố dùng CTE để gọi lại dữ liệu nhiều lần
CTE được tạo, thực thi, rồi "đóng băng". Nếu bạn cần dùng kết quả ở nhiều chỗ, dữ liệu sẽ không được tính lại — đôi khi gây lỗi.
Ví dụ:
WITH data AS (
SELECT x, y FROM some_table
)
SELECT x FROM data
WHERE y > 10;
-- Nếu cần tính lại data lần nữa, sẽ không xảy ra đâu.
Sửa sao?
Nếu cần dynamic hoặc tính lại, có thể CTE không phải lựa chọn tốt nhất. Dùng subquery nhé.
7. Lỗi: không có comment
CTE có thể là công cụ tuyệt vời, nhưng ai mà cần một SQL query phức tạp mà sau hai tuần chính bạn cũng không đọc nổi?
Ví dụ:
WITH data_filtered AS (
SELECT *
FROM large_table
WHERE some_column > 100
)
SELECT * FROM data_filtered;
Một tháng sau chẳng ai nhớ tại sao lại lọc dữ liệu này đâu!
Nên hãy comment query, nhất là khi dùng CTE phức tạp hoặc đệ quy:
WITH data_filtered AS (
-- Lọc dữ liệu theo điều kiện giá trị cột some_column > 100
SELECT *
FROM large_table
WHERE some_column > 100
)
SELECT * FROM data_filtered;
8. Lỗi: lạm dụng CTE thay vì dùng bảng tạm
Đôi khi bảng tạm lại hợp lý hơn nhiều. Ví dụ, nếu kết quả cần dùng nhiều lần ở các query khác nhau hoặc bạn làm việc với bộ dữ liệu cực lớn.
Ví dụ:
WITH temp_data AS (
SELECT * FROM large_table
)
SELECT * FROM temp_data WHERE column_a > 100;
SELECT * FROM temp_data WHERE column_b < 50;
Query kiểu này với CTE sẽ chạy hai lần, dù dữ liệu không đổi!
Sửa sao?
Tạo bảng tạm nếu dữ liệu dùng nhiều lần:
CREATE TEMP TABLE temp_table AS
SELECT * FROM large_table;
SELECT * FROM temp_table WHERE column_a > 100;
SELECT * FROM temp_table WHERE column_b < 50;Mẹo cuối cùng
Như mọi tính năng mạnh, khi dùng CTE bạn cần hiểu là nó không phải lúc nào cũng là công cụ tốt nhất cho mọi bài toán. Nghĩ kỹ xem bạn dùng nó để làm gì và như thế nào. Kiểu "càng nhiều CTE càng tốt" có thể làm hiệu năng và code khó đọc đi xuống trầm trọng. Và nhớ test hiệu năng, tối ưu query thường xuyên nhé.
GO TO FULL VERSION