Sử dụng CTE (Common Table Expression) để tối ưu hóa truy vấn lấy thông báo (Notification) từ nhiều bảng

Trong bài viết này, mình sẽ chia sẻ cách sử dụng CTE (Common Table Expression) để lấy danh sách thông báo từ hai bảng notification.en_laand_user_notification và notification.en_laand_notification. Đoạn truy vấn SQL này là ví dụ điển hình về cách xử lý dữ liệu phức tạp từ nhiều nguồn và trả về kết quả đã được tổng hợp.

1. Đoạn truy vấn SQL Đây là đoạn truy vấn SQL mà chúng ta sẽ phân tích:

sql Sao chép Chỉnh sửa WITH user_notification AS ( SELECT un.notification_id, CASE WHEN un.is_read = 't' THEN 2 ELSE 1 END AS status, un.created_date AS date, un.group_id FROM notification.en_laand_user_notification un WHERE un.user_id = 'caobh' ), notification AS ( SELECT n.id AS notification_id, CASE WHEN n.is_global = 't' THEN 1 ELSE 0 END AS status, n.created_date AS date FROM notification.en_laand_notification n WHERE (n.is_deleted = 'f') AND (n.notification_type = 3) AND (n.expired_date IS NULL OR (NOW() BETWEEN n.effective_date AND n.expired_date)) AND (n.is_global = 't' OR n.user_id = 'caobh') OR EXISTS ( SELECT 1 FROM user_notification un WHERE n.group_id = un.group_id ) AND (n.content IS NULL OR n.content LIKE '%page1%')

UNION ALL

SELECT 
    un.notification_id,
    un.status,
    un.date AS date
FROM user_notification un

) SELECT notification_id, SUM(status) AS total_status, MIN(date) AS earliest_date FROM notification GROUP BY notification_id ORDER BY CASE WHEN SUM(status) > 1 THEN 1 ELSE 0 END, MIN(date); 2. Phân tích truy vấn a. CTE đầu tiên: user_notification Đoạn CTE này thu thập thông tin thông báo từ bảng en_laand_user_notification dựa trên user_id. Mỗi thông báo được đánh dấu trạng thái (status) dựa trên việc người dùng đã đọc (is_read = 't') hay chưa.

Kết quả của CTE user_notification:

notification_id status date group_id 101 2 2025-02-01 1 102 1 2025-02-02 2 b. CTE thứ hai: notification Đoạn này lấy dữ liệu từ bảng en_laand_notification với các điều kiện sau:

is_deleted = 'f': Thông báo chưa bị xóa. notification_type = 3: Loại thông báo cụ thể. Ngày hiệu lực: Thông báo có thể là vĩnh viễn (expired_date IS NULL) hoặc trong khoảng thời gian từ effective_date đến expired_date. Đồng thời, nó kiểm tra các điều kiện:

Nếu thông báo là toàn cục (is_global = 't') hoặc được gửi riêng cho người dùng (user_id = 'caobh'). Hoặc tồn tại (EXISTS) thông báo thuộc cùng group_id với một thông báo của người dùng. Kết quả từ bảng này được UNION ALL với dữ liệu từ user_notification.

c. Phần GROUP BY và ORDER BY Cuối cùng, dữ liệu được tổng hợp:

SUM(status): Tổng số trạng thái để xác định mức độ ưu tiên. Nếu tổng lớn hơn 1, đó là thông báo quan trọng (ví dụ: đã đọc và là thông báo toàn cục). MIN(date): Lấy ngày sớm nhất của thông báo. Sắp xếp (ORDER BY):

Thông báo quan trọng (SUM(status) > 1) được ưu tiên hiển thị trước. Các thông báo còn lại được sắp xếp theo ngày sớm nhất (MIN(date)). 3. Tối ưu hóa truy vấn Đoạn truy vấn trên hoạt động tốt nhưng có thể tối ưu hóa để tránh lỗi logic và tăng hiệu suất:

Sử dụng dấu ngoặc trong điều kiện WHERE: Điều kiện AND ... OR cần dấu ngoặc để tránh ưu tiên sai. Ví dụ:

sql Sao chép Chỉnh sửa AND ((n.is_global = 't' OR n.user_id = 'caobh') OR EXISTS ( SELECT 1 FROM user_notification un WHERE n.group_id = un.group_id )) Giảm số lượng dữ liệu trả về từ WITH CTE: Nếu dữ liệu lớn, hãy thêm LIMIT trong CTE để hạn chế kích thước dữ liệu trước khi kết hợp (UNION ALL).

Chuyển đổi sang truy vấn JPQL nếu có thể: Nếu bạn đang làm việc với Spring Data JPA, hãy cân nhắc viết lại bằng JPQL để dễ bảo trì hơn.

4. Kết luận CTE (Common Table Expression) giúp truy vấn phức tạp dễ đọc và dễ quản lý hơn. Sử dụng GROUP BY và ORDER BY để tổng hợp và sắp xếp dữ liệu phù hợp với yêu cầu. Hãy luôn kiểm tra logic điều kiện WHERE và tối ưu hóa khi cần thiết.