NULL, JOIN nhân bản, N+1: những lỗi SQL không báo lỗi · Vì sao báo cáo SQL ra sai số
Báo cáo doanh thu tháng mình gửi đi lệch so với số bên kế toán. Query không báo lỗi gì, chạy nhanh, ra một con số nhìn rất hợp lý. Mình tin nó sái cổ, cho tới khi có người lôi ra đối chiếu.
Đây là loại lỗi mình sợ nhất. Code sai thì app crash, ít ra mình biết ngay. SQL sai thì vẫn trả về một con số, và con số đó đi thẳng vào slide họp. Dưới đây là năm lỗi mình từng dính, kèm cách mình phát hiện và sửa.
1. JOIN làm nhân dòng
Báo cáo doanh thu lệch ở trên là do lỗi này. Mình cần tổng giá trị các đơn có chứa sản phẩm thuộc một danh mục, nên join orders với order_items:
-- Sai: đơn có 3 sản phẩm thuộc danh mục 7 thì total_amount bị cộng 3 lần
SELECT SUM(o.total_amount)
FROM orders AS o
INNER JOIN order_items AS oi
ON oi.order_id = o.id
WHERE oi.category_id = 7;
Một đơn có nhiều dòng sản phẩm, join xong mỗi đơn xuất hiện nhiều lần, total_amount bị cộng lặp. Lúc viết mình chỉ nghĩ tới điều kiện lọc, quên béng chuyện số dòng.
Sửa thì đơn giản: chỉ cần lọc thì đừng join.
SELECT SUM(o.total_amount)
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM order_items AS oi
WHERE oi.order_id = o.id
AND oi.category_id = 7
);
Từ đó mình có thói quen: join xong là so COUNT(*) với COUNT(DISTINCT o.id). Hai số khác nhau là dòng đã bị nhân, mọi SUM hay AVG phía sau đều đáng ngờ. Còn khi bắt buộc phải lấy số từ bảng phía nhiều, mình gom bảng đó về một dòng mỗi đơn trước rồi mới join.
2. NULL không bằng cái gì, kể cả chính nó
Mình viết WHERE status <> 'cancelled' để lấy các đơn chưa huỷ, và mất luôn những đơn có status là NULL. So sánh gì với NULL cũng ra "không xác định", mà WHERE chỉ giữ những dòng ra đúng.
Cùng một gốc này còn mấy biến thể:
NOT INgặp NULL là ra rỗng.WHERE id NOT IN (SELECT customer_id FROM orders)không trả về dòng nào nếu có dù chỉ một đơn cócustomer_idNULL. Vì vậy mình luôn dùngNOT EXISTS.COUNT(cột)khácCOUNT(*).COUNT(phone)chỉ đếm dòng có số điện thoại.AVGvàSUMcũng bỏ qua NULL, nên trung bình là chia cho số dòng có giá trị chứ không phải tổng số dòng.NULL = NULLkhông đúng. Join hai bảng trên một cột có thể NULL thì các dòng NULL không khớp với nhau. Cần coi hai NULL là bằng nhau thì PostgreSQL cóIS NOT DISTINCT FROM, MySQL có<=>.
Ví dụ đầu thì sửa thành WHERE status <> 'cancelled' OR status IS NULL. Tốt hơn nữa là đặt NOT NULL cho cột ngay từ đầu, nếu nghiệp vụ vốn không cho phép đơn thiếu trạng thái.
3. LEFT JOIN biến thành INNER JOIN lúc nào không hay
Mình cần danh sách tất cả khách kèm số đơn đã thanh toán, khách chưa có đơn nào thì hiện 0:
-- Sai: khách không có đơn đã thanh toán biến mất khỏi kết quả
SELECT c.id, COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id;
Khách không có đơn thì LEFT JOIN điền NULL vào các cột của orders. Xong WHERE o.status = 'paid' loại đúng mấy dòng NULL đó. Kết quả y hệt INNER JOIN, dù trong query ghi rõ là LEFT JOIN.
Đưa điều kiện của bảng bên phải vào ON là xong:
SELECT c.id, COUNT(o.id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'paid'
GROUP BY c.id;
Giờ cứ thấy cột của bảng bên phải một LEFT JOIN nằm trong WHERE (trừ IS NULL) là mình dừng lại đọc kỹ.
4. N+1 query
Cái này không làm sai số mà làm chậm, nhưng mình vẫn cho vào vì nó cũng lặng lẽ y như vậy. Code lấy 100 đơn bằng một query, rồi trong vòng lặp mỗi đơn gọi thêm một query lấy thông tin khách. Một trang, 101 query.
Dùng ORM thì vòng lặp đó nhìn vô hại lắm, chỉ là order.customer.name. Cái làm mình tìm mãi không ra: query nào cũng nhanh, nên slow query log chẳng ghi gì. Trang thì chậm, mà không có query nào chậm.
Cách bắt: bật log toàn bộ query ở môi trường dev, load trang một lần. Thấy cùng một câu lặp lại vài chục lần, chỉ khác mỗi id, thì đúng là N+1.
Sửa bằng eager loading của ORM, hoặc tự gom danh sách id rồi lấy một lần:
SELECT id, name
FROM customers
WHERE id IN (12, 15, 42, 87); -- danh sách customer_id của 100 đơn vừa lấy
5. Khoảng thời gian và múi giờ
Hai lỗi này hay đi cùng nhau, và cả hai đều làm số lệch một chút, đủ nhỏ để không ai nghi.
Lỗi đầu là BETWEEN với cột có giờ phút giây:
-- Sai: '2024-05-31' được hiểu là 00:00:00 ngày 31, mất gần trọn ngày cuối tháng
WHERE created_at BETWEEN '2024-05-01' AND '2024-05-31'
-- Đúng: khoảng nửa mở, khỏi cần biết tháng có bao nhiêu ngày
WHERE created_at >= '2024-05-01'
AND created_at < '2024-06-01'
Lỗi thứ hai là múi giờ. Data lưu theo UTC, báo cáo tính theo giờ Việt Nam. Đơn đặt lúc 6 giờ sáng ngày 1/6 giờ Việt Nam được lưu là 23 giờ ngày 31/5 UTC, và rơi vào doanh thu tháng 5. Tháng nào cũng lệch bảy tiếng ở hai đầu.
Mình sửa bằng cách đổi mốc thời gian về UTC rồi mới lọc, để vẫn dùng được index trên created_at. Tháng 5 theo giờ Việt Nam là từ 17:00 ngày 30/4 tới trước 17:00 ngày 31/5 theo UTC:
WHERE created_at >= '2024-04-30 17:00:00'
AND created_at < '2024-05-31 17:00:00'
Cú pháp đổi múi giờ mỗi database một kiểu, lại còn phụ thuộc kiểu dữ liệu của cột, nên mình thường tính hai mốc này ở code rồi truyền vào làm tham số.
Trước khi gửi số cho ai
Giờ mình làm ba việc trước khi gửi một con số đi:
- Đếm số dòng sau mỗi bước join, so với số mình nghĩ nó phải ra.
- Đếm số dòng NULL ở mọi cột dùng trong
WHERE,JOINvà hàm tổng hợp. - Đối chiếu với một nguồn khác, dù chỉ cho một ngày hoặc một khách.
Ba việc này không bắt được hết. Hiểu sai nghiệp vụ, kiểu không biết đơn hoàn tiền được ghi thành dòng âm, thì chỉ có hỏi người nắm nghiệp vụ mới ra.
Query chạy không lỗi chưa có nghĩa là số đúng. Nếu muốn thử ngay: mở báo cáo quan trọng nhất của team, tìm một chỗ join sang bảng phía nhiều, so COUNT(*) với COUNT(DISTINCT id). Bài cuối đợt này mình viết về schema, transaction và mấy thứ chỉ lộ ra trên prod.
P.S. Lúc số đã lệch mà chưa biết vì sao, khó nhất là kìm mình lại đừng sửa ngay theo phỏng đoán đầu tiên. Mình dùng skill debug trong PrimeKit, bộ skill mình tự làm, để giữ kỷ luật đó: ghi rõ kết quả mong đợi với kết quả thực tế, rồi thử từng giả thuyết một, kiểu so COUNT(*) với COUNT(DISTINCT o.id) trước khi kết luận là join nhân dòng. Gõ /pk:debug trong Claude Code, kèm số lệch bao nhiêu, so với nguồn nào và query đang dùng.
All rights reserved