SQL & thiết kế cơ sở dữ liệu quan hệ
Chuẩn hoá và thiết kế schema, index (B-tree, composite, covering), đọc EXPLAIN, JOIN & window function, transaction & isolation, khoá & race condition, N+1, migration không downtime.
Database programming or design skills, including relational database skills (SQL, etc.)
JD ghi rõ cả "programming" lẫn "design". Người phỏng vấn sẽ cho một bài toán (đơn hàng, đặt chỗ, ví tiền) để xem bạn thiết kế bảng, chọn index, và xử lý đồng thời thế nào — đây là nơi bug tốn tiền nhất.
📋 Danh sách câu hỏi
- 47Thiết kế schema cho hệ thống đơn hàng (users, products, orders, order_items). Chuẩn hoá tới đâu là đủ?●●○○ Trung bình
- 48Index hoạt động thế nào? Composite index (a, b) dùng được cho truy vấn nào?●●●○ Khó
- 49Đọc EXPLAIN ANALYZE thế nào? Bạn tối ưu một query chậm ra sao?●●●○ Khó
- 50Transaction và isolation level. Hai người cùng mua sản phẩm cuối cùng — xử lý thế nào?●●●○ Khó
- 51Viết SQL: top 3 sản phẩm bán chạy nhất mỗi danh mục trong 30 ngày qua.●●○○ Trung bình
- 52N+1 query là gì? Phát hiện và sửa trong ORM (TypeORM/Prisma) thế nào?●●○○ Trung bình
- 53Thay đổi schema trên bảng lớn đang chạy production mà không downtime thế nào?●●●○ Khó
Xem câu trả lời & giải thích
Chuẩn hoá tới 3NF để tránh dữ liệu lặp và bất thường khi cập nhật: users, products, orders (user_id, status, total, created_at), order_items (order_id, product_id, qty, unit_price lưu lại tại thời điểm mua). Phi chuẩn hoá có chủ đích khi cần đọc nhanh (lưu total trên orders) — kèm cách giữ nhất quán.
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
user_id uuid NOT NULL REFERENCES users(id),
status text NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
total bigint NOT NULL CHECK (total >= 0), -- tiền: số nguyên (đồng)
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id uuid NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id uuid NOT NULL REFERENCES products(id),
qty int NOT NULL CHECK (qty > 0),
unit_price bigint NOT NULL,
PRIMARY KEY (order_id, product_id)
);
CREATE INDEX ON orders (user_id, created_at DESC);
Ràng buộc (NOT NULL, CHECK, FK, UNIQUE) là tuyến phòng thủ cuối — đừng chỉ dựa vào code ứng dụng.
Tiền không dùng float; thời gian dùng timestamptz (UTC); soft delete (deleted_at) cần index partial; audit log riêng cho thay đổi quan trọng.
Nối tiếp: "Giá sản phẩm đổi thì đơn cũ hiển thị giá nào?" → Giá lúc mua, vì vậy unit_price lưu trong order_items.
Lưu tiền bằng float; không có ràng buộc; tham chiếu giá hiện tại của sản phẩm cho đơn cũ.
- Tôi trả lời được câu này mà không cần xem đáp án
(a, b) dùng được cho truy vấn nào?Xem câu trả lời & giải thích
B-tree index là cây sắp xếp theo khoá, giúp tìm/khoảng/sắp xếp trong O(log n) thay vì quét cả bảng. Composite (a, b) dùng được khi lọc theo a, hoặc a và b, hoặc a = rồi sort theo b (quy tắc leftmost prefix); không dùng hiệu quả khi chỉ lọc theo b. Index tăng tốc đọc nhưng làm chậm ghi và tốn dung lượng.
- Thứ tự cột: cột lọc bằng (
=) trước, cột khoảng/sort sau; cột chọn lọc cao thường đặt trước. - Covering index (
INCLUDE) → index-only scan, không cần đọc bảng. - Partial index:
WHERE deleted_at IS NULL; expression index:lower(email). - Index không được dùng khi: bọc cột trong hàm (
WHERE date(created_at) = …),LIKE '%abc', ép kiểu ngầm, chọn lọc thấp (planner chọn seq scan). - Loại khác: GIN (JSONB, full-text, mảng), BRIN (bảng log lớn theo thời gian), hash.
Tìm query chậm qua pg_stat_statements, slow query log; thêm index rồi xác nhận bằng EXPLAIN ANALYZE. Tạo index trên bảng lớn bằng
CREATE INDEX CONCURRENTLY để không khoá ghi.
Tình huống: "Query WHERE status='paid' ORDER BY created_at DESC LIMIT 20 chậm." → Index (status, created_at DESC).
Đánh index mọi cột; không biết leftmost prefix.
- Tôi trả lời được câu này mà không cần xem đáp án
EXPLAIN ANALYZE thế nào? Bạn tối ưu một query chậm ra sao?Xem câu trả lời & giải thích
Xem kế hoạch thực thi: loại scan (Seq/Index/Index Only/Bitmap), kiểu join (Nested Loop/Hash/Merge), số dòng ước lượng so với thực tế (lệch nhiều → thống kê cũ), thời gian từng node, buffers. Quy trình: đo → tìm node tốn nhất → index/viết lại query/giảm dữ liệu → đo lại.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.country = 'VN' AND o.created_at > now() - interval '30 days';
-- Seq Scan on orders (rows=2,000,000) ... -> thiếu index (created_at) hoặc (user_id, created_at)
Kỹ thuật: chỉ SELECT cột cần, tránh SELECT *; lọc sớm; thay IN (subquery) bằng EXISTS/JOIN khi phù hợp; ANALYZE cập nhật thống kê;
phân trang keyset; materialized view cho báo cáo nặng.
Thường gặp nhất trong ứng dụng ORM: N+1 và thiếu index cho FK — không phải SQL phức tạp.
Nối tiếp: "Query nhanh ở local, chậm ở production." → Khác lượng dữ liệu, phân bố, thống kê, cache; luôn test trên dữ liệu cỡ thật.
Tối ưu bằng cảm giác, không đọc plan.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
ACID. Isolation: Read Committed (mặc định Postgres) tránh dirty read nhưng vẫn có non-repeatable read và lost update; Repeatable Read; Serializable (an toàn nhất, có thể phải retry). Bài 'sản phẩm cuối cùng': dùng cập nhật có điều kiện nguyên tử UPDATE ... SET stock = stock - 1 WHERE id = $1 AND stock >= 1 rồi kiểm tra số dòng bị ảnh hưởng; hoặc SELECT ... FOR UPDATE; hoặc optimistic locking bằng cột version.
-- Sai: đọc rồi ghi ở hai câu lệnh (race condition)
SELECT stock FROM products WHERE id = $1; -- cả hai đều thấy 1
UPDATE products SET stock = 0 WHERE id = $1; -- cả hai đều 'mua được'
-- Đúng: một câu lệnh nguyên tử
UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock >= 1 RETURNING stock;
-- Optimistic locking
UPDATE orders SET status = 'paid', version = version + 1 WHERE id = $1 AND version = $2; -- 0 dòng -> xung đột, báo 409
Giữ transaction ngắn; không gọi API bên ngoài (thanh toán, email) trong transaction; khoá theo thứ tự cố định để tránh deadlock; có retry cho lỗi serialization/deadlock.
Nối tiếp: "Flash sale 10.000 request/giây vào một sản phẩm?" → Hàng đợi/token trong Redis (DECR nguyên tử) để chặn trước, DB chỉ ghi những đơn hợp lệ.
Kiểm tra trong code rồi mới ghi; transaction bọc cả lời gọi HTTP.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Gom doanh số theo sản phẩm, dùng window function ROW_NUMBER()/DENSE_RANK() phân vùng theo danh mục, lọc thứ hạng ≤ 3.
WITH sales AS (
SELECT p.category_id, p.id AS product_id, p.name, SUM(oi.qty) AS units
FROM order_items oi
JOIN orders o ON o.id = oi.order_id AND o.status = 'paid' AND o.created_at >= now() - interval '30 days'
JOIN products p ON p.id = oi.product_id
GROUP BY p.category_id, p.id, p.name
), ranked AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY category_id ORDER BY units DESC) AS rnk FROM sales
)
SELECT category_id, product_id, name, units FROM ranked WHERE rnk <= 3 ORDER BY category_id, rnk;
ROW_NUMBER luôn ra đúng 3 dòng; DENSE_RANK giữ các sản phẩm đồng hạng. Nói rõ lựa chọn với người phỏng vấn.
Báo cáo chạy thường xuyên → materialized view làm mới định kỳ hoặc đẩy sang kho dữ liệu (BigQuery trên GCP).
Nối tiếp: "Thêm tỷ trọng doanh thu của sản phẩm trong danh mục?" → units * 1.0 / SUM(units) OVER (PARTITION BY category_id).
Không hỏi lại về đồng hạng; quên lọc trạng thái đơn.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Lấy N bản ghi bằng 1 query rồi lặp, mỗi bản ghi lại query thêm quan hệ → N+1 query. Phát hiện: log SQL, APM thấy hàng trăm query giống nhau trong một request. Sửa: eager load/JOIN (include, relations), batch bằng WHERE id IN (...), DataLoader (GraphQL).
// ❌ 1 + N
const orders = await repo.find();
for (const o of orders) o.user = await userRepo.findOneBy({ id: o.userId });
// ✅ 2 query
const orders = await prisma.order.findMany({ include: { user: { select: { id: true, name: true } } } });
Thêm cảnh báo trong test integration khi một request vượt X query. Cẩn thận eager load quá nhiều quan hệ làm JOIN nhân bản dữ liệu.
Tình huống: "API danh sách 50 đơn mất 2 giây, mỗi query chỉ 5ms." → 400 query × 5ms: N+1.
Đổ lỗi cho DB chậm.
- Tôi trả lời được câu này mà không cần xem đáp án
Xem câu trả lời & giải thích
Theo mẫu expand → migrate → contract: thêm cột/bảng mới (nullable, không khoá lâu) → deploy code ghi cả hai → backfill theo lô → chuyển đọc sang cột mới → bỏ ghi cột cũ → xoá cột cũ ở lần deploy sau. Mỗi bước tương thích với cả code cũ và mới vì trong lúc deploy hai phiên bản chạy song song.
- Tránh thao tác khoá lâu: thêm cột có default biến động, đổi kiểu cột, thêm NOT NULL trực tiếp trên bảng lớn → dùng
CHECK ... NOT VALIDrồiVALIDATE. - Index:
CREATE INDEX CONCURRENTLY. - Đặt
lock_timeoutđể migration không chặn hàng đợi truy vấn. - Đổi tên cột = thêm cột mới + đồng bộ + xoá cũ, không
RENAMEthẳng khi code cũ còn chạy.
Migration chạy trong CI/CD trước khi deploy code; có kế hoạch rollback (thường là roll-forward). Công cụ: Prisma Migrate, TypeORM migrations, Flyway, gh-ost/pgroll.
Nối tiếp: "Migration vô tình khoá bảng orders 2 phút lúc cao điểm." → Postmortem: thêm lock_timeout, review migration, chạy ngoài giờ cao điểm.
Đổi tên/xoá cột cùng lần deploy với code mới.
- Tôi trả lời được câu này mà không cần xem đáp án