SQL từ cơ bản đến nâng cao
Trang chủ / Module 15
🧰 Module 15 / 16 · 5-6 giờ

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.

Anti-patternCode reviewDebugPhỏng vấn
📌 Vì sao module này quan trọng

Đế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-patternHậu quảCách đúng
1col = NULL, col <> 'x' mà quên NULLDòng NULL âm thầm bị loạiIS NULL, IS DISTINCT FROM
2NOT IN (subquery có thể NULL)Kết quả rỗngNOT EXISTS
3JOIN 1-n rồi SUM/COUNT cột của bảng chaSố liệu bị nhân (fan-out)Tổng hợp về cùng grain trước khi nối
4DISTINCT để "chữa" nhân dòngChe lỗi grain; còn xoá dòng hợp lệ trùngSửa điều kiện JOIN/grain
5Điều kiện bảng phải của LEFT JOIN đặt ở WHERELEFT thành INNER, mất dòngĐặt trong ON
6count(*) sau LEFT JOINĐếm cả dòng "không có gì" (1 thay vì 0)count(bảng_phải.id)
7BETWEEN trên timestampMất ngày cuối kỳ>= đầu AND < đầu_kỳ_sau
8Nhóm theo ngày không chuyển múi giờDoanh thu lệch ngàyAT TIME ZONE 'Asia/Ho_Chi_Minh'
9Chia hai số nguyênCắt phần thập phânÉp numeric / nhân 1.0
10Trung 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
11UNION thay vì UNION ALLMất dòng trùng hợp lệMặc định UNION ALL
12LIMIT không có ORDER BY (hoặc ORDER BY không duy nhất)Kết quả/trang không ổn địnhThêm tie-breaker id
13Dùng float cho tiềnSai số tích luỹ, đối soát lệchnumeric(p,s)
14LAST_VALUE / luỹ kế với frame mặc địnhGiá trị sai khi có giá trị trùngFrame ROWS tường minh
15Tin 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ạyThêm tie-breaker

Danh mục anti-pattern — nhóm 'chậm / không scale'

#Anti-patternHậu quảCách đúng
16Hàm/ép kiểu quanh cột trong WHEREIndex vô dụng, quét toàn bảngViết lại sargable / expression index
17LIKE '%x%' trên bảng lớnQuét toàn bảngpg_trgm + GIN / full-text
18Thiếu index trên khoá ngoạiJOIN/DELETE cha chậm, khoá lâuIndex cột FK
19Index "cho chắc" quá nhiềuGhi chậm, tốn RAMChỉ index truy vấn thật; xoá index không dùng
20SELECT *Kéo thừa dữ liệu, mất index-only scan, lộ cột nhạy cảmLiệt kê cột
21OFFSET lớn để phân trangChậm dần theo trangKeyset pagination
22count(*) bảng tỷ dòng ở mỗi requestQuét toàn bảngƯớc lượng/bảng đếm/cache
23N+1 queryHàng trăm round-tripJOIN / = ANY / eager loading
24Subquery tương quan trong SELECT trên bảng lớnChạy lại cho từng dòngJOIN bảng đã gom / window
25ORDER BY random() trên bảng lớnSắp xếp toàn bảngTABLESAMPLE hoặc random theo id
26Giao dịch dài, idle in transactionKhoá, bloat, chặn migrationTransaction ngắn + timeout
27DELETE/UPDATE hàng triệu dòng một câuKhoá dài, WAL lớn, lag replicaBatch hoặc partition + drop
28Đọc – tính ở app – ghi (read-modify-write)Lost updateUPDATE ... SET x = x - n WHERE x >= n
29Mở nghìn kết nối trực tiếpServer cạn RAM/CPUPool + PgBouncer
30Không có timeoutMột truy vấn xấu kéo sập cả hệ thốngstatement_timeout, lock_timeout

Danh mục anti-pattern — nhóm 'thiết kế & an toàn'

#Anti-patternHậu quảCách đúng
31Nối chuỗi SQL với input người dùngSQL injectionTham số hoá; danh sách trắng cho định danh
32Không FK / không NOT NULL / không CHECKDữ liệu bẩn tích tụRàng buộc ở database
33Lưu danh sách trong một cột chuỗi ('a,b,c')Không truy vấn/ràng buộc đượcBảng con hoặc array/jsonb có chủ đích
34EAV cho mọi thuộc tínhMất kiểu, truy vấn khổng lồCột thật + jsonb cho phần thưa
35Giá trị "ma" thay NULL (0, '', '1900-01-01')Hỏng AVG/MIN, khoảng thời gianNULL đúng nghĩa + NOT NULL khi bắt buộc
36Lưu tiền/giờ bằng float, giờ không múi giờSai số, lệch 7 tiếngnumeric, timestamptz
37Id int cho bảng sẽ lớnTràn 2,1 tỷ, di trú đau đớnbigint / UUID v7 từ đầu
38Soft delete mà quên lọc ở khắp nơiLộ dữ liệu đã xoá; unique saiView active, partial unique index
39Tài khoản ứng dụng là owner/superuserMột lỗi nhỏ thành thảm hoạQuyền tối thiểu theo vai trò
40DDL nặng giờ cao điểm không lock_timeoutCả hệ thống đứng hìnhMigration nhỏ, tương thích ngược, CONCURRENTLY
41Logic nghiệp vụ phức tạp nằm trong triggerKhó thấy, khó testTrigger cho toàn vẹn/audit; logic ở service
42Test trên SQLite/mock thay cho PostgreSQL thậtLỗi chỉ xuất hiện ở productionTest 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ạ"

  1. 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(*) vs count(DISTINCT khoá).)
  2. 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.
  3. 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?
  4. 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?
  5. 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.
  6. Đố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

  1. 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ân loops).
  2. 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.
  3. 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.
  4. 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)?
  5. 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;
Kết quả
   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

🧠 Checklist 15 điểm trước khi merge
  1. Grain của kết quả rõ ràng? Mọi JOIN 1-n đã được kiểm soát (không fan-out)?
  2. NULL: các cột nullable trong điều kiện/JOIN/aggregate đã được xử lý? Không có NOT IN subquery nullable?
  3. Thời gian: múi giờ, khoảng nửa mở, ngày cuối kỳ.
  4. Kiểu: tiền numeric, chia số nguyên, ép kiểu ngầm ở điều kiện JOIN/WHERE.
  5. Thứ tự: mọi LIMIT/phân trang có ORDER BY duy nhất (tie-breaker)?
  6. Sargable: không bọc hàm quanh cột có index?
  7. Index hỗ trợ truy vấn mới (đã EXPLAIN trên dữ liệu cỡ thật)? Khoá ngoại có index?
  8. Cột liệt kê tường minh (không SELECT *)?
  9. Tham số hoá — không nối chuỗi; định danh động qua danh sách trắng.
  10. Số truy vấn mỗi request hợp lý (không N+1)?
  11. Ghi dữ liệu: có WHERE, đã thử bằng SELECT trước, nằm trong transaction đúng phạm vi; idempotent khi retry?
  12. Đồng thời: có read-modify-write? Cần khoá / cập nhật nguyên tử / retry?
  13. Ràng buộc (NOT NULL, FK, CHECK, UNIQUE) bảo vệ quy tắc nghiệp vụ ở database?
  14. Migration: tương thích ngược, lock_timeout, CONCURRENTLY, có đường lui?
  15. 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.

Câu 1NULL

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);
Kết quả
 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).

Câu 2NULL

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);
Kết quả
 x
---
 1
(1 row)

Chỉ 1 dòng: NULL NOT IN (2) là UNKNOWN nên dòng NULL bị loại.

Câu 3Kiểu

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;
Kết quả
 ?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).

Câu 4Chuỗi

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;
Kết quả
 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!).

Câu 5Số

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;
Kết quả
 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.

Câu 6Thời gian

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;
Kết quả
         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.

Câu 7Aggregate

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;
Kết quả
 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).

Câu 8Window

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;
Kết quả
 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.

Câu 9Tập hợp

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;
Kết quả
 ?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.

Câu 10Gom nhóm

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;
Kết quả
 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

Code reviewTrung bì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
  1. SELECT * + DISTINCT (che lỗi grain, tốn sort, lộ cột).
  2. JOIN kiểu dấu phẩy (dễ tích Descartes), nên JOIN ... ON.
  3. BETWEEN trên timestamp mất ngày 31.
  4. NOT IN subquery có city NULL ⇒ kết quả rỗng.
  5. ORDER BY ordered_at không duy nhất + OFFSET lớn ⇒ phân trang không ổn định và chậm (dùng keyset với tie-breaker id).
  6. Không rõ nhập c.city NULL; không có index rõ ràng cho ordered_at.
Viết checklist riêng cho đội bạnDễ

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

✅ 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