Phỏng vấn theo JD Fullstack
Trang chủ / Chủ đề 7
🐘 Chủ đề 7 / 13 ❓ Câu 47-53

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.

📄 Dòng JD tương ứng

Database programming or design skills, including relational database skills (SQL, etc.)

📌 Người phỏng vấn muốn kiểm tra điều gì

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

  1. 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
  2. 48Index hoạt động thế nào? Composite index (a, b) dùng được cho truy vấn nào?●●●○ Khó
  3. 49Đọc EXPLAIN ANALYZE thế nào? Bạn tối ưu một query chậm ra sao?●●●○ Khó
  4. 50Transaction và isolation level. Hai người cùng mua sản phẩm cuối cùng — xử lý thế nào?●●●○ Khó
  5. 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
  6. 52N+1 query là gì? Phát hiện và sửa trong ORM (TypeORM/Prisma) thế nào?●●○○ Trung bình
  7. 53Thay đổi schema trên bảng lớn đang chạy production mà không downtime thế nào?●●●○ Khó
Mẹo: tự trả lời thành tiếng trước, rồi mới mở đáp án.
Câu 47●●○○ Trung bình
Thiết kế schema cho hệ thống đơn hàng (users, products, orders, order_items). Chuẩn hoá tới đâu là đủ?
Xem câu trả lời & giải thích
💬 Trả lời ngắn (nói trong 30-60 giây)

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.

📖 Giải thích lý thuyết
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.

🛠️ Áp dụng trong dự án thực tế

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.

🎭 Tình huống & câu hỏi nối tiếp

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.

🚩 Sai lầm khiến ứng viên mất điểm

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
Câu 48●●●○ Khó
Index hoạt động thế nào? Composite index (a, b) dùng được cho truy vấn nào?
Xem câu trả lời & giải thích
💬 Trả lời ngắn (nói trong 30-60 giây)

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.

📖 Giải thích lý thuyết
  • 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.
🛠️ Áp dụng trong dự án thực tế

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 & câu hỏi nối tiếp

Tình huống: "Query WHERE status='paid' ORDER BY created_at DESC LIMIT 20 chậm." → Index (status, created_at DESC).

🚩 Sai lầm khiến ứng viên mất điểm

Đá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
Câu 49●●●○ Khó
Đọc 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
💬 Trả lời ngắn (nói trong 30-60 giây)

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.

📖 Giải thích lý thuyết
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.

🛠️ Áp dụng trong dự án thực tế

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.

🎭 Tình huống & câu hỏi nối tiế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.

🚩 Sai lầm khiến ứng viên mất điểm

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
Câu 50●●●○ Khó
Transaction và isolation level. Hai người cùng mua sản phẩm cuối cùng — xử lý thế nào?
Xem câu trả lời & giải thích
💬 Trả lời ngắn (nói trong 30-60 giây)

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.

📖 Giải thích lý thuyết
-- 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
🛠️ Áp dụng trong dự án thực tế

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.

🎭 Tình huống & câu hỏi nối tiếp

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ệ.

🚩 Sai lầm khiến ứng viên mất điểm

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
Câu 51●●○○ Trung bình
Viết SQL: top 3 sản phẩm bán chạy nhất mỗi danh mục trong 30 ngày qua.
Xem câu trả lời & giải thích
💬 Trả lời ngắn (nói trong 30-60 giây)

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.

📖 Giải thích lý thuyết
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.

🛠️ Áp dụng trong dự án thực tế

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).

🎭 Tình huống & câu hỏi nối tiếp

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).

🚩 Sai lầm khiến ứng viên mất điểm

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
Câu 52●●○○ Trung bình
N+1 query là gì? Phát hiện và sửa trong ORM (TypeORM/Prisma) thế nào?
Xem câu trả lời & giải thích
💬 Trả lời ngắn (nói trong 30-60 giây)

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).

📖 Giải thích lý thuyết
// ❌ 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 } } } });
🛠️ Áp dụng trong dự án thực tế

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 & câu hỏi nối tiếp

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.

🚩 Sai lầm khiến ứng viên mất điểm

Đổ lỗi cho DB chậm.

  • Tôi trả lời được câu này mà không cần xem đáp án
Câu 53●●●○ Khó
Thay đổi schema trên bảng lớn đang chạy production mà không downtime thế nào?
Xem câu trả lời & giải thích
💬 Trả lời ngắn (nói trong 30-60 giây)

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.

📖 Giải thích lý thuyết
  • 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 VALID rồi VALIDATE.
  • 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 RENAME thẳng khi code cũ còn chạy.
🛠️ Áp dụng trong dự án thực tế

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.

🎭 Tình huống & câu hỏi nối tiếp

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.

🚩 Sai lầm khiến ứng viên mất đ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