[DE Blog #02] Làm Chủ SQL Nâng Cao & Nghệ Thuật Tối Ưu Hóa Truy Vấn Cho Data Engineer

1. Bối cảnh thực tế (Context & Problem Statement)

Trong thế giới Data Engineering, viết SQL để chạy ra kết quả đúng chỉ là điều kiện cần, viết SQL chạy tối ưu trên hàng trăm triệu dòng mới là điều kiện đủ.

Một câu lệnh SQL viết ẩu có thể biến thời gian thực thi từ 2 giây thành 2 giờ, làm nghẽn tài nguyên cluster (CPU/RAM), gây timeout pipeline ETL/ELT hoặc làm đội chi phí hàng nghìn USD trên các nền tảng điện toán đám mây như BigQuery, Snowflake, Redshift.

2. Các Khái Niệm & Cơ Chế Cốt Lõi

2.1. Phân biệt các nhóm Window Functions

Cấu trúc chung:

<Function>() OVER (
    PARTITION BY <column_1> 
    ORDER BY <column_2> [ASC|DESC]
    [ROWS|RANGE BETWEEN <start> AND <end>]
)

Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(n).
Value / Navigation Functions: LAG(col, offset), LEAD(col, offset), FIRST_VALUE(col), LAST_VALUE(col).
Aggregate Window Functions: SUM(), AVG(), COUNT(), MAX(), MIN() tính lũy kế (Running Totals) hoặc trung bình động (Moving Averages).

2.2. Bản chất Index & Cấu trúc B-Tree

B-Tree Index (Balanced Tree): Cấu trúc cây tự cân bằng, giúp giảm độ phức tạp tìm kiếm từ $O(N)$ (Full Table Scan) xuống $O(\log N)$ (Index Seek).
Clustered Index: Xác định thứ tự sắp xếp vật lý thực tế của các dòng dữ liệu trên đĩa (Mỗi bảng chỉ có tối đa 1 Clustered Index, thường là Primary Key).
Non-Clustered Index: Cấu trúc tách biệt chứa khóa chỉ mục và con trỏ (Row Pointer / Row ID) trỏ về dòng dữ liệu gốc.
Composite Index & Quy tắc Leftmost Prefix: Khi tạo index trên (col_a, col_b, col_c), index chỉ có tác dụng khi điều kiện WHERE lọc theo col_a, hoặc (col_a, col_b), hoặc (col_a, col_b, col_c). Nếu chỉ lọc col_b hoặc col_c, Index sẽ bị vô hiệu hóa!

2.3. Đọc hiểu Kế hoạch thực thi (EXPLAIN / EXPLAIN ANALYZE)

Khi phân tích hiệu năng, cần chú ý các thao tác đọc và kết nối dữ liệu:

Các kiểu Scan:
- Sequential Scan (Full Table Scan): Đọc toàn bộ bảng từ đĩa (chậm khi bảng lớn).
- Index Scan: Tra cứu B-Tree để lấy con trỏ rồi đọc dữ liệu tương ứng.
- Index Only Scan (Covering Index): Tất cả các cột trong SELECT và WHERE đều nằm sẵn trong Index, không cần truy cập lại bảng gốc (tốc độ tối đa).
Các thuật toán JOIN cốt lõi: Nested Loop Join, Hash Join, Merge Join.

3. Các Bảng Markdown So Sánh Chi Tiết

📊 Bảng 1: So Sánh `ROW_NUMBER()` vs. `RANK()` vs. `DENSE_RANK()`

Giả sử có tập dữ liệu điểm số: [100, 90, 90, 80]

Hàm	Kết quả trả về	Cơ chế xử lý khi giá trị bằng nhau	Trường hợp sử dụng điển hình
`ROW_NUMBER()`	`1, 2, 3, 4`	Đánh số thứ tự tuần tự tuyệt đối, không có số trùng lặp	Lấy bản ghi mới nhất/cũ nhất (Deduplication), phân trang
`RANK()`	`1, 2, 2, 4`	Đồng hạng nhận cùng số thứ tự, bỏ qua số thứ tự kế tiếp (Gap)	Xếp hạng giải thưởng, thể thao (có đồng hạng và nhảy cóc hạng)
`DENSE_RANK()`	`1, 2, 2, 3`	Đồng hạng nhận cùng số thứ tự, không bỏ qua số thứ tự kế tiếp (No Gap)	Tìm giá trị cao thứ N (Top Nth Salary/Revenue) (Chuẩn phỏng vấn)

📊 Bảng 2: So Sánh CTE vs. Subquery vs. Temporary Table

Tiêu chí	Common Table Expression (CTE)	Subquery (Truy vấn con)	Temporary Table (Bảng tạm)
Tính dễ đọc (Readability)	Rất cao, luồng logic từ trên xuống dưới	Dễ rối và khó bảo trì khi lồng nhau nhiều cấp	Trung bình, cần viết nhiều lệnh DDL/DML
Tái sử dụng	Tái sử dụng được nhiều lần trong cùng 1 câu query	Không tái sử dụng được (phải viết lại)	Tái sử dụng xuyên suốt toàn bộ Session/Transaction
Đánh Index	Không thể đánh Index riêng	Không thể đánh Index riêng	Có thể tạo Index riêng để tối ưu join phức tạp
Không gian lưu trữ	Thường lưu trong bộ nhớ (In-memory/Inlined)	Lưu tạm trong bộ nhớ	Ghi ra đĩa/TempDB nếu dữ liệu lớn
Khi nào nên dùng	Logic phức tạp cần phân tách bước rõ ràng	Lọc điều kiện đơn giản (`IN`, `EXISTS`)	Dữ liệu trung gian rất lớn cần tái sử dụng qua nhiều bước ETL

📊 Bảng 3: So Sánh 3 Thuật Toán JOIN Cơ Bản

Thuật toán JOIN	Cơ chế hoạt động	Tối ưu khi nào?	Chi phí tài nguyên
Nested Loop Join	Duyệt từng dòng của bảng ngoài và quét bảng trong	1 bảng cực nhỏ + 1 bảng lớn có Index trên Join Key	Tốn CPU nếu cả 2 bảng đều lớn
Hash Join	Nạp bảng nhỏ vào bộ nhớ tạo Hash Table, sau đó quét bảng lớn để đối chiếu	2 bảng lớn, join theo phép so sánh bằng (`=`), không có index	Tốn nhiều RAM để chứa Hash Table
Merge (Sort-Merge) Join	Sắp xếp cả 2 bảng theo Join Key rồi duyệt đồng thời 2 con trỏ	Cả 2 bảng đều đã được sort sẵn hoặc có Clustered Index	Tốn I/O và CPU nếu phải Sort trước

4. Best Practices & Khái Niệm "Sargable Query"

🎯 Quy tắc vàng: Viết truy vấn SARGABLE (Search Argument Able)

Truy vấn Sargable là truy vấn cho phép Database Engine tận dụng được Index để tăng tốc tìm kiếm thay vì phải Full Table Scan.

❌ Non-Sargable (Tránh): Bọc hàm hoặc tính toán lên cột có Index:

-- Làm mất tác dụng của Index trên cột created_at:
SELECT user_id FROM orders WHERE YEAR(created_at) = 2026;

-- Làm mất tác dụng của Index trên cột phone_number:
SELECT user_id FROM users WHERE SUBSTRING(phone_number, 1, 3) = '098';

✅ Sargable (Khuyên dùng): Giữ nguyên cột gốc, chuyển hàm/tính toán sang phía giá trị so sánh:

-- Tận dụng Index Range Scan cực nhanh:
SELECT user_id FROM orders 
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- Tận dụng Index B-Tree với toán tử LIKE tiền tố:
SELECT user_id FROM users WHERE phone_number LIKE '098%';

⚠️ Tránh SELECT *: Chỉ SELECT đúng những cột cần thiết để kích hoạt Index Only Scan và giảm lưu lượng I/O truyền tải qua mạng.
⚠️ Cẩn trọng với UNION vs. UNION ALL: Sử dụng UNION ALL khi biết chắc dữ liệu không trùng lặp hoặc không cần khử trùng, vì UNION sẽ kích hoạt thêm một bước Sort & Unique tốn kém.

5. Góc Ôn Luyện Phỏng Vấn (Interview Corner)

❓ Câu hỏi 1: Viết câu lệnh SQL để tìm mức lương cao thứ 2 trong mỗi phòng ban? (Xử lý trường hợp có nhân viên đồng lương)

Lời giải & Giải thích:

WITH RankedSalaries AS (
    SELECT 
        department_id,
        employee_name,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT 
    department_id,
    employee_name,
    salary
FROM RankedSalaries
WHERE salary_rank = 2;

Điểm mấu chốt khi trả lời phỏng vấn: Cần chủ động giải thích lý do chọn DENSE_RANK() thay vì RANK() hoặc ROW_NUMBER(). Nếu 2 người cùng mức lương cao nhất (hạng 1), RANK() sẽ nhảy cóc mức lương tiếp theo thành hạng 3 (khiến kết quả hạng 2 bị rỗng), trong khi DENSE_RANK() sẽ xếp mức lương tiếp theo chính xác là hạng 2.

❓ Câu hỏi 2: Khi một câu truy vấn SQL chạy rất chậm trong môi trường Production, quy trình từng bước của bạn để điều tra và tối ưu là gì?

Gợi ý trả lời có cấu trúc:
1. Bước 1 - Thu thập Kế hoạch thực thi: Sử dụng EXPLAIN (ANALYZE, BUFFERS) để xem chi phí dự tính (Cost), thời gian thực tế (Actual Time), số dòng được quét và lượng I/O bộ nhớ/đĩa.
2. Bước 2 - Xác định điểm nghẽn (Bottlenecks):
  - Có xảy ra Seq Scan trên bảng lớn không? (Thiếu Index hoặc truy vấn Non-sargable).
  - Số dòng ước tính (Estimated Rows) có chênh lệch lớn với số dòng thực tế (Actual Rows) không? (Thống kê Database Statistics bị outdate -> Cần chạy ANALYZE).
  - Có phép Join nào bị tràn bộ nhớ đĩa (Spill to Disk) không? (Cần tăng work_mem hoặc tối ưu bộ lọc).
3. Bước 3 - Tiến hành tối ưu:
  - Viết lại câu lệnh chuẩn Sargable, loại bỏ tính toán thừa trong WHERE/JOIN.
  - Tạo hoặc điều chỉnh Composite Index phù hợp với quy tắc Leftmost Prefix.
  - Áp dụng Partitioning / Clustering nếu bảng vượt quá hàng trăm triệu dòng.
4. Bước 4 - Benchmark & Xác minh: Chạy thử nghiệm trên môi trường Staging/Dev với khối lượng dữ liệu tương đương để đo lường độ cải thiện trước khi release.

❓ Câu hỏi 3: Sự khác nhau giữa Filter trong `ON` clause và Filter trong `WHERE` clause khi thực hiện `LEFT JOIN` là gì?

Gợi ý trả lời:
- Điều kiện đặt trong ON: Lọc các dòng từ bảng bên phải (Right Table) trước khi thực hiện phép ghép. Bảng bên trái vẫn giữ lại toàn bộ các dòng (các dòng không khớp sẽ hiển thị NULL ở các cột bảng phải).
- Điều kiện đặt trong WHERE: Lọc trên tập kết quả sau khi đã hoàn thành phép LEFT JOIN. Nếu điều kiện WHERE lọc một cột thuộc bảng phải với điều kiện loại trừ NULL (ví dụ WHERE right_table.status = 'ACTIVE'), nó sẽ vô tình biến LEFT JOIN thành INNER JOIN!

6. Tóm Tắt Ghi Nhớ Nhanh (Key Takeaways)

DENSE_RANK() là "vũ khí tối thượng" cho các bài toán tìm Top Nth giá trị trong phỏng vấn SQL.
Sargable Query là nguyên tắc bắt buộc: Không bao giờ bọc hàm hoặc phép toán lên cột có Index ở mệnh đề WHERE.
Composite Index bắt buộc phải tuân thủ thứ tự Leftmost Prefix khi truy vấn.
EXPLAIN ANALYZE là công cụ phân tích bắt buộc phải biết đọc khi tối ưu hóa hiệu năng dữ liệu.