Anti-pattern, gỡ lỗi và checklist của chuyên gia
Danh mục các lỗi SQL phổ biến nhất (và cách đúng), quy trình gỡ lỗi 'kết quả sai' / 'truy vấn chậm', checklist review SQL và bài trắc nghiệm đoán kết quả có đáp án chạy thật.
Đến đây bạn đã biết công cụ. Điều phân biệt người có 5 năm kinh nghiệm với người mới là một thư viện lỗi trong đầu: nhìn câu truy vấn là "ngửi" ra chỗ có vấn đề trước cả khi chạy. Module này đóng gói thư viện đó thành danh mục, quy trình và checklist bạn có thể dán vào quy trình code review của đội.
🎯 Mục tiêu học tập
- Nhận diện nhanh 30+ anti-pattern SQL và nêu cách sửa
- Có quy trình có hệ thống để gỡ lỗi kết quả sai và truy vấn chậm
- Dùng checklist review SQL trước khi merge/deploy
- Dự đoán chính xác kết quả của các câu truy vấn 'bẫy' (NULL, kiểu dữ liệu, thứ tự, làm tròn)
Danh mục anti-pattern — nhóm 'kết quả sai'
| # | Anti-pattern | Hậu quả | Cách đúng |
|---|---|---|---|
| 1 | col = NULL, col <> 'x' mà quên NULL | Dòng NULL âm thầm bị loại | IS NULL, IS DISTINCT FROM |
| 2 | NOT IN (subquery có thể NULL) | Kết quả rỗng | NOT EXISTS |
| 3 | JOIN 1-n rồi SUM/COUNT cột của bảng cha | Số liệu bị nhân (fan-out) | Tổng hợp về cùng grain trước khi nối |
| 4 | DISTINCT để "chữa" nhân dòng | Che lỗi grain; còn xoá dòng hợp lệ trùng | Sửa điều kiện JOIN/grain |
| 5 | Điều kiện bảng phải của LEFT JOIN đặt ở WHERE | LEFT thành INNER, mất dòng | Đặt trong ON |
| 6 | count(*) sau LEFT JOIN | Đếm cả dòng "không có gì" (1 thay vì 0) | count(bảng_phải.id) |
| 7 | BETWEEN trên timestamp | Mất ngày cuối kỳ | >= đầu AND < đầu_kỳ_sau |
| 8 | Nhóm theo ngày không chuyển múi giờ | Doanh thu lệch ngày | AT TIME ZONE 'Asia/Ho_Chi_Minh' |
| 9 | Chia hai số nguyên | Cắt phần thập phân | Ép numeric / nhân 1.0 |
| 10 | Trung bình của trung bình, cộng các tỉ lệ | Sai số thống kê | Cộng tử, cộng mẫu, chia cuối |
| 11 | UNION thay vì UNION ALL | Mất dòng trùng hợp lệ | Mặc định UNION ALL |
| 12 | LIMIT không có ORDER BY (hoặc ORDER BY không duy nhất) | Kết quả/trang không ổn định | Thêm tie-breaker id |
| 13 | Dùng float cho tiền | Sai số tích luỹ, đối soát lệch | numeric(p,s) |
| 14 | LAST_VALUE / luỹ kế với frame mặc định | Giá trị sai khi có giá trị trùng | Frame ROWS tường minh |
| 15 | Tin row_number với ORDER BY không duy nhất | "Bản mới nhất" khác nhau mỗi lần chạy | Thêm tie-breaker |
Danh mục anti-pattern — nhóm 'chậm / không scale'
| # | Anti-pattern | Hậu quả | Cách đúng |
|---|---|---|---|
| 16 | Hàm/ép kiểu quanh cột trong WHERE | Index vô dụng, quét toàn bảng | Viết lại sargable / expression index |
| 17 | LIKE '%x%' trên bảng lớn | Quét toàn bảng | pg_trgm + GIN / full-text |
| 18 | Thiếu index trên khoá ngoại | JOIN/DELETE cha chậm, khoá lâu | Index cột FK |
| 19 | Index "cho chắc" quá nhiều | Ghi chậm, tốn RAM | Chỉ index truy vấn thật; xoá index không dùng |
| 20 | SELECT * | Kéo thừa dữ liệu, mất index-only scan, lộ cột nhạy cảm | Liệt kê cột |
| 21 | OFFSET lớn để phân trang | Chậm dần theo trang | Keyset pagination |
| 22 | count(*) bảng tỷ dòng ở mỗi request | Quét toàn bảng | Ước lượng/bảng đếm/cache |
| 23 | N+1 query | Hàng trăm round-trip | JOIN / = ANY / eager loading |
| 24 | Subquery tương quan trong SELECT trên bảng lớn | Chạy lại cho từng dòng | JOIN bảng đã gom / window |
| 25 | ORDER BY random() trên bảng lớn | Sắp xếp toàn bảng | TABLESAMPLE hoặc random theo id |
| 26 | Giao dịch dài, idle in transaction | Khoá, bloat, chặn migration | Transaction ngắn + timeout |
| 27 | DELETE/UPDATE hàng triệu dòng một câu | Khoá dài, WAL lớn, lag replica | Batch hoặc partition + drop |
| 28 | Đọc – tính ở app – ghi (read-modify-write) | Lost update | UPDATE ... SET x = x - n WHERE x >= n |
| 29 | Mở nghìn kết nối trực tiếp | Server cạn RAM/CPU | Pool + PgBouncer |
| 30 | Không có timeout | Một truy vấn xấu kéo sập cả hệ thống | statement_timeout, lock_timeout |
Danh mục anti-pattern — nhóm 'thiết kế & an toàn'
| # | Anti-pattern | Hậu quả | Cách đúng |
|---|---|---|---|
| 31 | Nối chuỗi SQL với input người dùng | SQL injection | Tham số hoá; danh sách trắng cho định danh |
| 32 | Không FK / không NOT NULL / không CHECK | Dữ liệu bẩn tích tụ | Ràng buộc ở database |
| 33 | Lưu danh sách trong một cột chuỗi ('a,b,c') | Không truy vấn/ràng buộc được | Bảng con hoặc array/jsonb có chủ đích |
| 34 | EAV cho mọi thuộc tính | Mất kiểu, truy vấn khổng lồ | Cột thật + jsonb cho phần thưa |
| 35 | Giá trị "ma" thay NULL (0, '', '1900-01-01') | Hỏng AVG/MIN, khoảng thời gian | NULL đúng nghĩa + NOT NULL khi bắt buộc |
| 36 | Lưu tiền/giờ bằng float, giờ không múi giờ | Sai số, lệch 7 tiếng | numeric, timestamptz |
| 37 | Id int cho bảng sẽ lớn | Tràn 2,1 tỷ, di trú đau đớn | bigint / UUID v7 từ đầu |
| 38 | Soft delete mà quên lọc ở khắp nơi | Lộ dữ liệu đã xoá; unique sai | View active, partial unique index |
| 39 | Tài khoản ứng dụng là owner/superuser | Một lỗi nhỏ thành thảm hoạ | Quyền tối thiểu theo vai trò |
| 40 | DDL nặng giờ cao điểm không lock_timeout | Cả hệ thống đứng hình | Migration nhỏ, tương thích ngược, CONCURRENTLY |
| 41 | Logic nghiệp vụ phức tạp nằm trong trigger | Khó thấy, khó test | Trigger cho toàn vẹn/audit; logic ở service |
| 42 | Test trên SQLite/mock thay cho PostgreSQL thật | Lỗi chỉ xuất hiện ở production | Test với PostgreSQL thật (container) |
Quy trình gỡ lỗi của chuyên gia
Khi kết quả sai hoặc "có gì đó lạ"
- Xác định grain: một dòng kết quả phải đại diện cho cái gì? Có đúng vậy không? (Đếm
count(*)vscount(DISTINCT khoá).) - Thu nhỏ: chạy trên một khách/một đơn cụ thể đã biết đáp án (
WHERE id = 2) và so từng bước. - Chạy từng CTE/JOIN riêng: sau mỗi bước kiểm tra số dòng và tổng so với kỳ vọng. Dòng nào bỗng tăng? Dòng nào mất?
- Soi NULL: cột nào nullable? điều kiện nào bị NULL loại âm thầm? kiểu so sánh có ngầm ép kiểu?
- Soi thời gian: múi giờ, nửa mở, ngày cuối kỳ, dữ liệu chưa đủ ở kỳ gần nhất.
- Đối soát chéo với một nguồn độc lập (tổng của bảng gốc, số liệu kế toán).
Khi truy vấn chậm
- Chạy
EXPLAIN (ANALYZE, BUFFERS)trên dữ liệu cỡ thật; tìm nút tốn thời gian nhất (nhớ nhânloops). - So
rowsước lượng và thực tế; lệch lớn →ANALYZE, kiểm tra thống kê/tương quan cột. - Seq Scan trên bảng lớn với điều kiện chọn lọc? → index / sargable. Sort tràn đĩa? → index/
work_mem. Nested Loop với phía ngoài rất lớn? → thiếu index phía trong. - Truy vấn có cần trả nhiều dòng thế không (LIMIT, lọc sớm, chỉ cột cần)? Có thể tính trước (materialized view)?
- Nếu chỉ chậm khi đông người dùng: tìm khoá chờ (
pg_stat_activity,pg_locks), N+1, thiếu pool.
-- Ba câu truy vấn "sức khoẻ" nên có sẵn trong ví dụ công cụ của bạn
-- 1) Bảng lớn nhất
SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS kich_thuoc
FROM pg_class WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY pg_total_relation_size(oid) DESC LIMIT 5; relname | kich_thuoc
-------------+------------
events | 288 kB
logins | 176 kB
order_items | 120 kB
orders | 96 kB
payments | 72 kB
(5 rows)-- 2) Index chưa từng được dùng (ứng viên để xoá) — cần chạy trên DB đã hoạt động một thời gian
SELECT relname AS bang, indexrelname AS index, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS kich_thuoc
FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC LIMIT 5;-- 3) Truy vấn tốn tổng thời gian nhất (cần extension pg_stat_statements đã bật trên server):
SELECT calls, round(total_exec_time::numeric) AS tong_ms, round(mean_exec_time::numeric, 2) AS tb_ms, left(query, 70) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
Checklist review một câu SQL / migration
- Grain của kết quả rõ ràng? Mọi JOIN 1-n đã được kiểm soát (không fan-out)?
- NULL: các cột nullable trong điều kiện/JOIN/aggregate đã được xử lý? Không có
NOT INsubquery nullable? - Thời gian: múi giờ, khoảng nửa mở, ngày cuối kỳ.
- Kiểu: tiền
numeric, chia số nguyên, ép kiểu ngầm ở điều kiện JOIN/WHERE. - Thứ tự: mọi
LIMIT/phân trang cóORDER BYduy nhất (tie-breaker)? - Sargable: không bọc hàm quanh cột có index?
- Index hỗ trợ truy vấn mới (đã
EXPLAINtrên dữ liệu cỡ thật)? Khoá ngoại có index? - Cột liệt kê tường minh (không
SELECT *)? - Tham số hoá — không nối chuỗi; định danh động qua danh sách trắng.
- Số truy vấn mỗi request hợp lý (không N+1)?
- Ghi dữ liệu: có
WHERE, đã thử bằngSELECTtrước, nằm trong transaction đúng phạm vi; idempotent khi retry? - Đồng thời: có read-modify-write? Cần khoá / cập nhật nguyên tử / retry?
- Ràng buộc (NOT NULL, FK, CHECK, UNIQUE) bảo vệ quy tắc nghiệp vụ ở database?
- Migration: tương thích ngược,
lock_timeout,CONCURRENTLY, có đường lui? - Quyền & dữ liệu nhạy cảm: quyền tối thiểu, không lộ cột nhạy cảm, log không chứa dữ liệu cá nhân.
Bài trắc nghiệm: đoán kết quả (đáp án chạy thật)
Với mỗi câu, tự đoán kết quả trước rồi mở đáp án. Mỗi câu kiểm tra một cái bẫy đã học.
SELECT count(*), count(x), sum(x), avg(x) FROM (VALUES (1), (NULL), (5)) t(x); Bốn số là gì?
Xem lời giải & giải thích
SELECT count(*), count(x), sum(x), avg(x) FROM (VALUES (1), (NULL), (5)) t(x); count | count | sum | avg
-------+-------+-----+--------------------
3 | 2 | 6 | 3.0000000000000000
(1 row)count(*) đếm dòng; các hàm còn lại bỏ NULL: avg = 6/2 = 3 (không phải 6/3).
SELECT * FROM (VALUES (1), (NULL)) t(x) WHERE x NOT IN (2); Trả về mấy dòng?
Xem lời giải & giải thích
SELECT * FROM (VALUES (1), (NULL)) t(x) WHERE x NOT IN (2); x
---
1
(1 row)Chỉ 1 dòng: NULL NOT IN (2) là UNKNOWN nên dòng NULL bị loại.
SELECT 5 / 2, 5 / 2.0, -5 / 2, 5 % 3, -5 % 3;
Xem lời giải & giải thích
SELECT 5 / 2, 5 / 2.0, -5 / 2, 5 % 3, -5 % 3; ?column? | ?column? | ?column? | ?column? | ?column?
----------+--------------------+----------+----------+----------
2 | 2.5000000000000000 | -2 | 2 | -2
(1 row)Chia nguyên cắt về 0 (-5/2 = -2) và phần dư mang dấu của số bị chia (-5 % 3 = -2).
SELECT 'a' || NULL, concat('a', NULL), length(''), length(NULL::text), '' IS NULL;
Xem lời giải & giải thích
SELECT 'a' || NULL AS noi, concat('a', NULL) AS concat_, length('') AS len_rong, length(NULL::text) AS len_null, '' IS NULL AS rong_la_null; noi | concat_ | len_rong | len_null | rong_la_null
------+---------+----------+----------+--------------
NULL | a | 0 | NULL | f
(1 row)Toán tử || với NULL cho NULL, concat() bỏ qua NULL. Chuỗi rỗng khác NULL (khác Oracle!).
SELECT 0.1 + 0.2 = 0.3, 0.1::float8 + 0.2::float8 = 0.3, round(2.5), round(2.5::float8), round(-2.5);
Xem lời giải & giải thích
SELECT 0.1 + 0.2 = 0.3 AS numeric_eq, 0.1::float8 + 0.2::float8 = 0.3 AS float_eq, round(2.5) AS r_numeric, round(2.5::float8) AS r_float, round(-2.5) AS r_am; numeric_eq | float_eq | r_numeric | r_float | r_am
------------+----------+-----------+---------+------
t | f | 3 | 2 | -3
(1 row)Literal 0.1 là numeric nên cộng chính xác. round(numeric) làm tròn ra xa 0; round(float8) làm tròn về chẵn.
Với phiên đặt Asia/Ho_Chi_Minh: SELECT timestamptz '2024-03-01 00:30+07' AT TIME ZONE 'UTC', date '2024-01-31' + interval '1 month';
Xem lời giải & giải thích
SELECT timestamptz '2024-03-01 00:30+07' AT TIME ZONE 'UTC' AS utc, date '2024-01-31' + interval '1 month' AS cong_thang; utc | cong_thang
---------------------+---------------------
2024-02-29 17:30:00 | 2024-02-29 00:00:00
(1 row)00:30 giờ VN là 17:30 ngày 29/2 theo UTC (lệch ngày!). Cộng 1 tháng vào 31/1 kẹp về ngày cuối tháng 2.
SELECT count(*) FROM orders o LEFT JOIN order_items oi ON oi.order_id = o.id; trả về 400 hay 1000?
Xem lời giải & giải thích
SELECT count(*) FROM orders o LEFT JOIN order_items oi ON oi.order_id = o.id; count
-------
1000
(1 row)1000, không phải 400: mỗi đơn xuất hiện lần theo số dòng hàng (fan-out). Đếm đơn thật phải dùng count(DISTINCT o.id).
SELECT x, sum(x) OVER (ORDER BY x), sum(x) OVER (ORDER BY x ROWS UNBOUNDED PRECEDING) FROM (VALUES (1),(1),(3)) t(x) ORDER BY x;
Xem lời giải & giải thích
SELECT x, sum(x) OVER (ORDER BY x) AS mac_dinh, sum(x) OVER (ORDER BY x ROWS UNBOUNDED PRECEDING) AS rows_ FROM (VALUES (1),(1),(3)) t(x) ORDER BY x; x | mac_dinh | rows_
---+----------+-------
1 | 2 | 1
1 | 2 | 2
3 | 5 | 5
(3 rows)Frame mặc định là RANGE nên hai dòng x=1 (peer) cùng nhận 2; ROWS cho 1 rồi 2.
SELECT 1 UNION SELECT 1 UNION ALL SELECT 1; trả về mấy dòng?
Xem lời giải & giải thích
SELECT 1 UNION SELECT 1 UNION ALL SELECT 1; ?column?
----------
1
1
(2 rows)2 dòng: phép toán đánh giá từ trái sang phải: (1 UNION 1) → 1 dòng, rồi UNION ALL 1 → 2 dòng.
SELECT count(*) FROM (SELECT customer_id FROM orders GROUP BY customer_id HAVING count(*) > 7) t; và vì sao WHERE count(*) > 7 lỗi?
Xem lời giải & giải thích
SELECT count(*) AS khach_tren_7_don FROM (SELECT customer_id FROM orders GROUP BY customer_id HAVING count(*) > 7) t; khach_tren_7_don
------------------
22
(1 row)WHERE chạy trước GROUP BY nên chưa có kết quả tổng hợp (module 1) — điều kiện trên hàm tổng hợp phải ở HAVING.
🏋️ Bài tập thực hành
Tìm ít nhất 4 vấn đề trong truy vấn: SELECT DISTINCT * FROM orders o, customers c WHERE o.customer_id = c.id AND o.ordered_at BETWEEN '2024-01-01' AND '2024-01-31' AND c.city NOT IN (SELECT city FROM customers WHERE id < 10) ORDER BY o.ordered_at LIMIT 20 OFFSET 100000;
Xem lời giải & giải thích
SELECT *+DISTINCT(che lỗi grain, tốn sort, lộ cột).- JOIN kiểu dấu phẩy (dễ tích Descartes), nên
JOIN ... ON. BETWEENtrên timestamp mất ngày 31.NOT INsubquery cócityNULL ⇒ kết quả rỗng.ORDER BY ordered_atkhông duy nhất +OFFSETlớn ⇒ phân trang không ổn định và chậm (dùng keyset với tie-breakerid).- Không rõ nhập
c.cityNULL; không có index rõ ràng choordered_at.
Chọn 8 điểm trong checklist 15 điểm phù hợp nhất với hệ thống của bạn (ví dụ hệ thống tài chính ưu tiên kiểu số, múi giờ, đồng thời; hệ thống phân tích ưu tiên grain, NULL, nửa mở) và biến thành mục trong template Pull Request.
Xem lời giải & giải thích
Đây là bài tập tư duy: không có đáp án duy nhất. Tiêu chí tốt: mỗi mục kiểm tra được bằng một hành động cụ thể ("đã chạy EXPLAIN ANALYZE và dán kế hoạch", "đã kiểm tra count trước/sau JOIN"), không phải khẩu hiệu chung chung.
📚 Tài nguyên học tập
-
SQL Antipatterns (Bill Karwin)Sách kinh điển về các anti-pattern thiết kế và truy vấnSách
-
PostgreSQL Wiki — Don't Do ThisDanh sách ngắn những điều nên tránh trong PostgreSQLBài viết
-
pgTAPFramework kiểm thử đơn vị cho PostgreSQLCông cụ
✅ Tự đánh giá hoàn thành
- Nhận diện được ít nhất 20 anti-pattern trong danh mục và nêu cách sửa
- Có quy trình gỡ lỗi 'kết quả sai' bắt đầu từ grain và đối soát chéo
- Có quy trình 'truy vấn chậm' bắt đầu từ EXPLAIN (ANALYZE, BUFFERS)
- Đã biến checklist review thành template Pull Request của đội