SQL & Database

SQL (relational) lưu dữ liệu dạng bảng có schema cố định, hỗ trợ JOIN và transaction ACID — hợp cho dữ liệu có quan hệ chặt chẽ, cần nhất quán (tài chính, đơn hàng). NoSQL (document, key-value, graph...) linh hoạt về schema, dễ scale ngang, hợp cho dữ liệu lớn, ít quan hệ hoặc cấu trúc thay đổi (log, cache, real-time). Chọn dựa trên mô hình dữ liệu và yêu cầu nhất quán, không phải theo trào lưu.

INNER JOIN chỉ trả về hàng khớp ở cả hai bảng. LEFT JOIN giữ tất cả hàng bảng trái, phần không khớp bên phải là NULL; RIGHT JOIN ngược lại. FULL OUTER JOIN giữ tất cả hàng của cả hai bảng, chỗ không khớp là NULL. Chọn loại JOIN theo việc bạn muốn giữ lại hàng không khớp ở bên nào.

SELECT u.name, o.id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

CROSS JOIN tạo tích Descartes — mỗi hàng bảng A ghép với mọi hàng bảng B (A×B hàng), thường dùng để sinh tổ hợp. SELF JOIN là join một bảng với chính nó (dùng alias khác nhau), hữu ích cho dữ liệu phân cấp như nhân viên và người quản lý cùng nằm trong một bảng.

SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Index (thường là B-tree) giúp truy vấn tìm hàng theo cột nhanh hơn nhiều thay vì quét toàn bảng. Đổi lại nó tốn dung lượng và làm chậm INSERT/UPDATE/DELETE vì phải cập nhật index. Nên đánh index cho cột hay dùng trong WHERE, JOIN, ORDER BY; tránh index thừa.

B-tree giữ dữ liệu được sắp xếp và cân bằng, nên tìm kiếm, chèn, xóa đều ở độ phức tạp O(log n). Nó hỗ trợ tốt cả truy vấn chính xác (=) lẫn truy vấn khoảng (>, <, BETWEEN) và ORDER BY vì dữ liệu đã có thứ tự. Đây là lý do B-tree là index mặc định trong PostgreSQL và MySQL (InnoDB).

Composite index là index trên nhiều cột theo thứ tự, ví dụ (a, b, c). Nó tuân theo nguyên tắc leftmost prefix: index chỉ dùng được cho WHERE a, WHERE a AND b, hoặc a AND b AND c, nhưng không cho WHERE b một mình. Vì vậy nên đặt cột lọc chọn lọc cao (hoặc dùng thường xuyên nhất) lên trước.

CREATE INDEX idx_user_status ON orders (user_id, status);

Covering index là index chứa tất cả các cột mà truy vấn cần, nên database đọc kết quả trực tiếp từ index mà không cần truy cập bảng (index-only scan). Điều này giảm I/O đáng kể. Trong PostgreSQL có thể dùng INCLUDE để thêm cột không-key vào index.

CREATE INDEX idx_cover ON orders (user_id) INCLUDE (status, total);

PRIMARY KEY định danh duy nhất mỗi hàng — không NULL, mỗi bảng chỉ có một, tự tạo index. UNIQUE cũng đảm bảo không trùng nhưng cho phép NULL và có thể có nhiều unique constraint. FOREIGN KEY tham chiếu khóa chính/unique của bảng khác, đảm bảo toàn vẹn tham chiếu (referential integrity).

Normalization là quá trình tổ chức bảng để giảm dư thừa dữ liệu và tránh anomaly. 1NF: mỗi ô chứa giá trị nguyên tử, không nhóm lặp. 2NF: đạt 1NF và mọi cột non-key phụ thuộc toàn bộ khóa chính (loại phụ thuộc bộ phận). 3NF: đạt 2NF và không có phụ thuộc bắc cầu (cột non-key không phụ thuộc cột non-key khác).

Denormalization là cố ý thêm dư thừa (nhân bản dữ liệu, gộp bảng, thêm cột tính sẵn) để tăng tốc đọc bằng cách giảm số JOIN. Nên dùng khi hệ thống đọc nhiều hơn ghi và JOIN trở thành nút cổ chai. Đánh đổi là dữ liệu có thể không nhất quán và việc ghi phức tạp hơn, nên cần cơ chế đồng bộ.

Transaction là một nhóm thao tác được xem như một đơn vị — thành công tất cả hoặc rollback tất cả. ACID: Atomicity (nguyên tử, tất cả hoặc không gì), Consistency (dữ liệu luôn hợp lệ theo ràng buộc), Isolation (các transaction chạy song song không nhiễu nhau), Durability (đã commit thì bền vững kể cả khi mất điện).

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Có 4 mức theo chuẩn SQL, tăng dần độ chặt: Read Uncommitted (cho phép dirty read), Read Committed (chặn dirty read, còn non-repeatable read), Repeatable Read (chặn non-repeatable read, có thể còn phantom), Serializable (chặn tất cả, như chạy tuần tự). Mức càng cao càng an toàn nhưng càng giảm concurrency.

Dirty read: đọc dữ liệu mà transaction khác chưa commit (có thể bị rollback). Non-repeatable read: đọc cùng một hàng hai lần trong cùng transaction cho ra giá trị khác nhau vì transaction khác đã UPDATE và commit. Phantom read: chạy lại cùng một truy vấn theo điều kiện cho ra số lượng hàng khác vì transaction khác đã INSERT/DELETE.

Deadlock xảy ra khi hai transaction cùng chờ khóa mà bên kia đang giữ, tạo vòng chờ vô hạn; database sẽ phát hiện và hủy một transaction (nạn nhân). Để giảm: luôn khóa tài nguyên theo cùng một thứ tự, giữ transaction ngắn, giảm isolation level nếu hợp lý, và retry transaction bị hủy ở tầng ứng dụng.

N+1 xảy ra khi bạn chạy 1 truy vấn lấy N hàng, rồi lặp qua từng hàng để chạy thêm 1 truy vấn con — tổng cộng N+1 lần round-trip tới DB, rất chậm. Khắc phục bằng eager loading (JOIN hoặc IN (...) để lấy dữ liệu liên quan trong 1-2 truy vấn), hoặc dùng tính năng include/preload của ORM.

EXPLAIN cho biết query plan — cách database sẽ thực thi truy vấn: dùng index hay quét toàn bảng (Seq Scan vs Index Scan), thứ tự JOIN, và chi phí ước lượng. EXPLAIN ANALYZE còn chạy thật để cho thời gian và số hàng thực tế. Đây là công cụ chính để tìm và tối ưu truy vấn chậm.

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

WHERE lọc từng hàng trước khi nhóm (GROUP BY), không dùng được với hàm tổng hợp. HAVING lọc các nhóm sau khi tổng hợp, dùng được với COUNT, SUM,... Thứ tự thực thi logic: WHERE → GROUP BY → HAVING.

SELECT user_id, COUNT(*) c
FROM orders WHERE status = 'paid'
GROUP BY user_id HAVING COUNT(*) > 5;

GROUP BY gom các hàng có cùng giá trị vào một nhóm, sau đó các hàm aggregate như COUNT, SUM, AVG, MIN, MAX tính trên mỗi nhóm. Mọi cột trong SELECT không nằm trong aggregate phải xuất hiện trong GROUP BY. Ví dụ tính tổng doanh thu theo từng khách hàng.

JOIN thường hiệu quả hơn khi cần kết hợp cột từ nhiều bảng và optimizer tối ưu tốt. Subquery dễ đọc hơn cho các phép kiểm tra tồn tại/lọc (IN, EXISTS) hoặc tính giá trị đơn. Trong nhiều DB hiện đại, optimizer có thể viết lại subquery thành JOIN, nhưng nên đo bằng EXPLAIN khi nghi ngờ hiệu năng.

Correlated subquery là subquery tham chiếu cột của truy vấn ngoài, nên nó được chạy lại cho từng hàng của truy vấn ngoài — có thể rất chậm. Khác với subquery độc lập chỉ chạy một lần. Thường có thể viết lại thành JOIN để tối ưu.

SELECT u.name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);