Cụm bài Database: 1. Bảng và SQL cơ bản · 2. ERD, JOIN, GROUP BY · 3. Chuẩn hoá · 4. Bài tập thư viện · 5. Index và hiệu năng truy vấn · 6. Transaction và locking
Một bài tập lớn luyện lại ba bài đầu của cụm: bảng và SQL cơ bản, ERD, JOIN, GROUP BY và chuẩn hoá. Ta đi đúng trình tự làm thật: đọc đề, sửa chỗ đề thiếu, vẽ ERD, tạo bảng có ràng buộc, nạp dữ liệu mẫu, viết 30 câu truy vấn, rồi xem senior làm thêm gì trước khi lên production. Dialect: MySQL 8; mọi câu SQL đã chạy thử trên MySQL 8.4.
1. Đề bài
Ý chính: thư viện quản lý thành viên, nhân viên, nhà xuất bản (NXB), sách và phiếu mượn. Mỗi lần mượn là một phiếu, do một nhân viên lập.
Quy tắc:
- Một thành viên mượn được nhiều sách. Tại một thời điểm, một cuốn sách chỉ nằm trong tay một thành viên.
- Mỗi phiếu do đúng một nhân viên lập; một nhân viên lập nhiều phiếu.
- Một NXB in nhiều sách; mỗi sách thuộc đúng một NXB.
Thuộc tính và ràng buộc chi tiết của đề
- Thành viên: mã thẻ (chuỗi ≤ 5 ký tự, khoá), họ, tên (≤ 15), địa chỉ (≤ 50), ngày sinh, giới tính (chỉ
M/F), số điện thoại (≤ 15); tất cả bắt buộc. - Nhân viên: mã tự tăng, họ, tên, ngày sinh (bắt buộc), ngày vào làm.
- Sách: mã (≤ 5 ký tự), thể loại (một trong: novel, historical, for kids, poems, crime story, science fiction, science), giá (decimal 3 chữ số thập phân), tiêu đề (bắt buộc).
- Nhà xuất bản: mã tự tăng, tên, thành phố (≤ 50), điện thoại (≤ 15); bắt buộc.
- Phiếu mượn: số phiếu tự tăng, ngày mượn, hạn trả (ngày mượn phải trước hạn trả), tiền phạt (decimal 3 chữ số thập phân, không âm, mặc định 0).
2. Phân tích và sửa đề trước khi code
Ý chính: đọc kỹ xem mô hình có trả lời được các câu hỏi nghiệp vụ không. Ở đây đề thiếu ngày trả, và lời giải gốc hiểu sai quy tắc “một cuốn một người”.
- Thiếu ngày trả thực tế. Đề hỏi “sách chưa trả”, “phiếu không trễ hạn”, nhưng không có cột nào cho biết sách đã trả chưa. Mình thêm
returned_at DATETIME NULL(NULL = chưa trả). - “Một cuốn sách chỉ được mượn bởi một thành viên”. Lời giải gốc dùng
UNIQUE(book_id), tức mỗi cuốn cả đời chỉ mượn được một lần. Ý đúng: tại một thời điểm chỉ có một phiếu đang mở.
- Quan hệ “Book 1-1 Book_Invoice” trong lời giải gốc vì thế phải sửa thành Book 1-n Loan (theo thời gian), với tối đa một phiếu mở.
- Ngày mượn, hạn trả nên
NOT NULL. Lời giải gốc để nullable, và thiếuCHECK (penalty >= 0)dù đề yêu cầu. - Thể loại sách:
CHECK ... IN (...)chạy được (MySQL ≥ 8.0.16). Nếu thể loại hay thay đổi, bảng tra cứubook_type+ FK tốt hơn vì thêm thể loại không cầnALTER TABLE.
3. ERD
Ý chính: bốn quan hệ 1-n. Thành viên, nhân viên và sách đều nối tới phiếu mượn; NXB nối tới sách.
erDiagram
MEMBER ||--o{ LOAN : borrows
EMPLOYEE ||--o{ LOAN : issues
BOOK ||--o{ LOAN : "is lent in"
PUBLISHER ||--o{ BOOK : publishes
MEMBER {
string card_no PK
string surname
string given_name
string address
date birth_date
char gender
string phone
}
EMPLOYEE {
int emp_id PK
string surname
string given_name
date birth_date
datetime hired_at
}
PUBLISHER {
int pub_id PK
string name
string city
string phone
}
BOOK {
string book_id PK
string type
decimal price
string title
int pub_id FK
}
LOAN {
int invoice_no PK
string card_no FK
int emp_id FK
string book_id FK
datetime date_out
datetime due_date
datetime returned_at
decimal penalty
}
4. Tạo bảng
Ý chính: đưa mọi quy tắc của đề vào ràng buộc (NOT NULL, CHECK, FK, UNIQUE) để database tự giữ dữ liệu đúng, dù dữ liệu được ghi từ đâu.
CREATE TABLE member (
card_no VARCHAR(5) PRIMARY KEY,
surname VARCHAR(15) NOT NULL,
given_name VARCHAR(15) NOT NULL,
address VARCHAR(50) NOT NULL,
birth_date DATE NOT NULL,
gender CHAR(1) NOT NULL CHECK (gender IN ('M', 'F')),
phone VARCHAR(15) NOT NULL
);
CREATE TABLE employee (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
surname VARCHAR(15) NOT NULL,
given_name VARCHAR(15) NOT NULL,
birth_date DATE NOT NULL,
hired_at DATETIME
);
CREATE TABLE publisher (
pub_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
city VARCHAR(50) NOT NULL,
phone VARCHAR(15) NOT NULL
);
CREATE TABLE book (
book_id VARCHAR(5) PRIMARY KEY,
type VARCHAR(20) NOT NULL CHECK (type IN ('novel', 'historical', 'for kids', 'poems',
'crime story', 'science fiction', 'science')),
price DECIMAL(10,3) NOT NULL CHECK (price >= 0),
title VARCHAR(255) NOT NULL,
pub_id INT NOT NULL,
CONSTRAINT fk_book_publisher FOREIGN KEY (pub_id) REFERENCES publisher(pub_id)
);
CREATE TABLE loan (
invoice_no INT AUTO_INCREMENT PRIMARY KEY,
card_no VARCHAR(5) NOT NULL,
emp_id INT NOT NULL,
book_id VARCHAR(5) NOT NULL,
date_out DATETIME NOT NULL,
due_date DATETIME NOT NULL,
returned_at DATETIME NULL,
penalty DECIMAL(10,3) NOT NULL DEFAULT 0 CHECK (penalty >= 0),
-- chỉ có giá trị khi phiếu còn mở -> unique chỉ áp lên phiếu đang mở
open_book_id VARCHAR(5) AS (CASE WHEN returned_at IS NULL THEN book_id END) STORED,
CONSTRAINT chk_loan_dates CHECK (date_out < due_date),
CONSTRAINT uq_loan_one_open_per_book UNIQUE (open_book_id),
CONSTRAINT fk_loan_member FOREIGN KEY (card_no) REFERENCES member(card_no),
CONSTRAINT fk_loan_employee FOREIGN KEY (emp_id) REFERENCES employee(emp_id),
CONSTRAINT fk_loan_book FOREIGN KEY (book_id) REFERENCES book(book_id),
KEY idx_loan_card (card_no),
KEY idx_loan_book (book_id),
KEY idx_loan_emp_date (emp_id, date_out)
);
5. Dữ liệu mẫu
Ý chính: ngày mượn tính tương đối theo NOW(), nên kết quả “quá hạn”, “hôm nay” luôn có nghĩa bất kể bạn chạy lúc nào.
Bộ dữ liệu có đủ các trường hợp: trả đúng hạn, trả trễ, đang quá hạn, chưa tới hạn, mượn hôm nay. M0004 chưa mượn lần nào; “Little Lantern” giá 8.5 đang được mượn.
INSERT INTO member VALUES
('M0001', 'Vu', 'Minh Anh', '12 Hang Bai, Hanoi', '1996-02-14', 'F', '0901000001'),
('M0002', 'Dang', 'Quoc Bao', '45 Le Loi, Hue', '1991-08-30', 'M', '0901000002'),
('M0003', 'Ngo', 'Thanh Tam', '7 Tran Phu, Da Nang', '2000-12-01', 'M', '0901000003'),
('M0004', 'Ly', 'Thu Ha', '88 Nguyen Hue, HCMC', '1989-05-19', 'F', '0901000004');
INSERT INTO employee (surname, given_name, birth_date, hired_at) VALUES
('Bui', 'Duc Long', '1990-03-03', '2023-01-09 08:00:00'),
('Mai', 'Ngoc Lan', '1995-10-21', '2024-06-01 08:00:00'),
('Cao', 'Van Khoa', '1987-07-07', '2022-02-14 08:00:00');
INSERT INTO publisher (name, city, phone) VALUES
('Red River Books', 'Hanoi', '0241000001'),
('Southern Press', 'Ho Chi Minh City', '0281000002'),
('Central Coast Pub', 'Da Nang', '0236100003'),
('Tiny Shelf', 'Hanoi', '0241000004');
INSERT INTO book VALUES
('B0001', 'novel', 21.500, 'The Quiet Harbor', 1),
('B0002', 'historical', 18.000, 'Dynasties of the Delta', 2),
('B0003', 'crime story', 24.900, 'The Last Alibi', 1),
('B0004', 'crime story', 19.750, 'Cold Ledger', 3),
('B0005', 'science fiction', 27.000, 'Orbit of Glass', 2),
('B0006', 'for kids', 8.500, 'Little Lantern', 4),
('B0007', 'science', 32.000, 'How Engines Think', 1),
('B0008', 'poems', 12.250, 'Monsoon Lines', 3),
('B0009', 'novel', 16.000, 'Paper Kites', 1);
INSERT INTO loan (card_no, emp_id, book_id, date_out, due_date, returned_at) VALUES
('M0001', 1, 'B0003', NOW() - INTERVAL 60 DAY, NOW() - INTERVAL 46 DAY, NOW() - INTERVAL 50 DAY), -- trả đúng hạn
('M0001', 2, 'B0004', NOW() - INTERVAL 40 DAY, NOW() - INTERVAL 26 DAY, NOW() - INTERVAL 20 DAY), -- trả trễ
('M0001', 1, 'B0001', NOW() - INTERVAL 20 DAY, NOW() - INTERVAL 6 DAY, NULL), -- quá hạn 6 ngày
('M0002', 3, 'B0005', NOW() - INTERVAL 30 DAY, NOW() - INTERVAL 16 DAY, NULL), -- quá hạn 16 ngày
('M0002', 1, 'B0002', NOW() - INTERVAL 10 DAY, NOW() + INTERVAL 4 DAY, NULL), -- đang mượn, chưa tới hạn
('M0003', 2, 'B0003', NOW() - INTERVAL 1 HOUR, NOW() + INTERVAL 14 DAY, NULL), -- mượn hôm nay
('M0003', 2, 'B0006', NOW() - INTERVAL 2 HOUR, NOW() + INTERVAL 14 DAY, NULL); -- mượn hôm nay
6. 30 câu truy vấn
Ý chính: tự viết từng câu trước, rồi mở lời giải để so. Phần lớn chỉ luyện SELECT, JOIN, GROUP BY; cái đáng nhớ là vài bẫy về NULL, đếm số 0, index và ngày giờ.
Đề gốc có 30 yêu cầu, đáp án chỉ có 15 câu đầu, và câu 15 của đáp án lọc nhầm mã thành viên (đề hỏi M0002, đáp án dùng M0001). Lời giải dưới đây là của mình cho cả 30 câu.
- Toàn bộ thông tin sách.
- Tên và địa chỉ của các thành viên nam.
- Tổng giá của tất cả sách.
- Tên và ngày sinh nhân viên, sắp theo ngày sinh tăng dần.
- Tên và giá sách loại
novel, giá từ cao xuống thấp. - Số nhà xuất bản trong hệ thống.
- Tên sách và hạn trả của những phiếu đang quá hạn.
- Số sách theo từng thể loại.
- Mọi thông tin sách mà thành viên M0001 đã mượn.
- Đặt giá mọi sách
historicalthành 25.99. - Xoá các sách có giá dưới 10.
- Tên sách và ngày mượn của sách thể loại
science fiction. - Giá trung bình của tất cả sách.
- Tên sách và tên NXB của sách có tiêu đề bắt đầu bằng “The”.
- Tên sách và ngày mượn mà thành viên M0002 đã mượn, với sách giá trên 20.
- Sách và tên NXB, chỉ những sách có ít nhất một phiếu mượn.
- Số sách mỗi NXB đã xuất bản.
- Tên sách và NXB của sách có giá cao hơn giá trung bình.
- Mọi thành viên và số lần mượn sách của họ.
- Tên sách và NXB của sách đắt nhất và rẻ nhất.
- Tổng số sách các NXB đã xuất bản.
- Sách và NXB của sách có ít nhất một phiếu mượn được trả đúng hạn.
- Thành viên đã từng mượn sách và số lần mượn, sắp giảm dần.
- Số sách mỗi NXB, chỉ giữ NXB có từ 2 sách trở lên.
- Thành viên đã mượn sách
crime storyít nhất hai lần. - Sách đang mượn chưa trả, ngày mượn và số ngày quá hạn.
- Tên sách và NXB của sách chưa trả, quá hạn hơn 7 ngày.
- Hôm nay mỗi nhân viên lập bao nhiêu phiếu.
- Sách có ít nhất một phiếu mượn và thuộc NXB ở Hà Nội, kèm số lần được mượn.
- Sách có giá thấp hơn giá trung bình toàn bộ và thuộc NXB ở Hà Nội.
Những điểm đáng nhớ nhất trong lời giải:
- Câu 7, 26, 27: luôn có
returned_at IS NULL, nếu không sách đã trả từ lâu vẫn bị tính là quá hạn (lỗi của đáp án gốc, vì mô hình gốc không có cột ngày trả). - Câu 17, 19, 28:
LEFT JOIN+COUNT(cột của bảng phải)để dòng không có phiếu vẫn hiện số 0. - Câu 21, 22, 30: đề mơ hồ, phải chọn một cách hiểu và nói rõ (hoặc hỏi lại).
Lời giải câu 1–10
1. Toàn bộ thông tin sách.
SELECT * FROM book;
Trong code thật nên liệt kê cột thay vì *: tránh kéo cột thừa qua mạng, và thêm cột mới vào bảng không làm vỡ mapping.
2. Tên và địa chỉ của các thành viên nam.
SELECT CONCAT(surname, ' ', given_name) AS full_name, address
FROM member
WHERE gender = 'M';
3. Tổng giá của tất cả sách.
SELECT SUM(price) AS total_price FROM book;
4. Tên và ngày sinh nhân viên, sắp theo ngày sinh tăng dần.
SELECT surname, given_name, birth_date
FROM employee
ORDER BY birth_date ASC;
5. Tên và giá sách loại novel, giá từ cao xuống thấp.
SELECT title, price
FROM book
WHERE type = 'novel'
ORDER BY price DESC;
6. Số nhà xuất bản trong hệ thống.
SELECT COUNT(*) AS publisher_count FROM publisher;
7. Tên sách và hạn trả của những phiếu đang quá hạn.
SELECT b.title, l.due_date
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.returned_at IS NULL
AND l.due_date < NOW();
Không có returned_at IS NULL thì sách đã trả từ lâu vẫn bị tính là quá hạn.
8. Số sách theo từng thể loại.
SELECT type, COUNT(*) AS book_count
FROM book
GROUP BY type
ORDER BY book_count DESC;
9. Mọi thông tin sách mà thành viên M0001 đã mượn.
SELECT DISTINCT b.*
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.card_no = 'M0001';
DISTINCT vì một người có thể mượn cùng một cuốn nhiều lần. Dạng WHERE b.book_id IN (SELECT book_id FROM loan WHERE card_no = 'M0001') cũng đúng và tự loại trùng.
10. Đặt giá mọi sách historical thành 25.99.
UPDATE book SET price = 25.990 WHERE type = 'historical';
Lời giải câu 11–20
11. Xoá các sách có giá dưới 10.
DELETE FROM book
WHERE price < 10
AND NOT EXISTS (SELECT 1 FROM loan l WHERE l.book_id = book.book_id);
Vì sao phải có NOT EXISTS: xem khối “Xoá dòng đã có lịch sử” ở trên.
12. Tên sách và ngày mượn của sách thể loại science fiction.
SELECT b.title, l.date_out
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE b.type = 'science fiction';
13. Giá trung bình của tất cả sách.
SELECT ROUND(AVG(price), 3) AS avg_price FROM book;
14. Tên sách và tên NXB của sách có tiêu đề bắt đầu bằng “The”.
SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.title LIKE 'The%';
15. Tên sách và ngày mượn mà thành viên M0002 đã mượn, với sách giá trên 20.
SELECT b.title, l.date_out
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.card_no = 'M0002'
AND b.price > 20;
16. Sách và tên NXB, chỉ những sách có ít nhất một phiếu mượn.
SELECT b.book_id, b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (SELECT 1 FROM loan l WHERE l.book_id = b.book_id);
17. Số sách mỗi NXB đã xuất bản.
SELECT p.pub_id, p.name, COUNT(b.book_id) AS book_count
FROM publisher p
LEFT JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name;
LEFT JOIN để NXB chưa có sách nào vẫn hiện với số 0.
18. Tên sách và NXB của sách có giá cao hơn giá trung bình.
SELECT b.title, p.name AS publisher, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.price > (SELECT AVG(price) FROM book);
Subquery không tương quan (non-correlated, không phụ thuộc dòng bên ngoài) chỉ chạy một lần.
19. Mọi thành viên và số lần mượn sách của họ.
SELECT m.card_no, m.surname, m.given_name, COUNT(l.invoice_no) AS loan_count
FROM member m
LEFT JOIN loan l ON l.card_no = m.card_no
GROUP BY m.card_no, m.surname, m.given_name;
M0004 chưa mượn lần nào, hiện với số 0.
20. Tên sách và NXB của sách đắt nhất và rẻ nhất.
SELECT b.title, p.name AS publisher, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE b.price = (SELECT MAX(price) FROM book)
OR b.price = (SELECT MIN(price) FROM book);
Lời giải câu 21–30
21. Tổng số sách các NXB đã xuất bản.
Đề hơi mơ hồ. Nếu chỉ cần một con số thì SELECT COUNT(*) FROM book;. Mình hiểu là “số sách mỗi NXB kèm dòng tổng”:
SELECT COALESCE(p.name, 'TOTAL') AS publisher, COUNT(*) AS book_count
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
GROUP BY p.name WITH ROLLUP;
22. Sách và NXB của sách có ít nhất một phiếu mượn được trả đúng hạn.
SELECT b.title, p.name AS publisher
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE EXISTS (
SELECT 1 FROM loan l
WHERE l.book_id = b.book_id
AND l.returned_at IS NOT NULL
AND l.returned_at <= l.due_date
);
“Không trễ hạn” cần định nghĩa rõ: mình hiểu là đã trả và trả trước hạn. Phiếu đang mở mà chưa tới hạn thì chưa thể kết luận.
23. Thành viên đã từng mượn sách và số lần mượn, sắp giảm dần.
SELECT m.card_no, m.surname, m.given_name, COUNT(*) AS loan_count
FROM member m
JOIN loan l ON l.card_no = m.card_no
GROUP BY m.card_no, m.surname, m.given_name
ORDER BY loan_count DESC;
Khác câu 19: dùng INNER JOIN vì chỉ lấy người đã mượn.
24. Số sách mỗi NXB, chỉ giữ NXB có từ 2 sách trở lên.
SELECT p.pub_id, p.name, COUNT(*) AS book_count
FROM publisher p
JOIN book b ON b.pub_id = p.pub_id
GROUP BY p.pub_id, p.name
HAVING COUNT(*) >= 2;
25. Thành viên đã mượn sách crime story ít nhất hai lần.
SELECT m.card_no, m.surname, m.given_name, COUNT(*) AS crime_loans
FROM member m
JOIN loan l ON l.card_no = m.card_no
JOIN book b ON b.book_id = l.book_id
WHERE b.type = 'crime story'
GROUP BY m.card_no, m.surname, m.given_name
HAVING COUNT(*) >= 2;
Lọc thể loại ở WHERE (trước khi gom), ngưỡng số lượng ở HAVING (sau khi gom). Nếu cần “hai cuốn khác nhau” thì dùng COUNT(DISTINCT l.book_id).
26. Sách đang mượn chưa trả, ngày mượn và số ngày quá hạn.
SELECT b.title, l.date_out,
GREATEST(DATEDIFF(CURRENT_DATE, l.due_date), 0) AS days_overdue
FROM loan l
JOIN book b ON b.book_id = l.book_id
WHERE l.returned_at IS NULL;
DATEDIFF(a, b) trong MySQL trả về a - b tính theo ngày. GREATEST(..., 0) để sách chưa tới hạn hiện 0 thay vì số âm. PostgreSQL: GREATEST(CURRENT_DATE - l.due_date::date, 0).
27. Tên sách và NXB của sách chưa trả, quá hạn hơn 7 ngày.
SELECT b.title, p.name AS publisher,
DATEDIFF(CURRENT_DATE, l.due_date) AS days_overdue
FROM loan l
JOIN book b ON b.book_id = l.book_id
JOIN publisher p ON p.pub_id = b.pub_id
WHERE l.returned_at IS NULL
AND l.due_date < CURRENT_DATE - INTERVAL 7 DAY;
Điều kiện so due_date trơn với hằng số để dùng được index (xem khối “Bọc cột trong hàm” ở trên).
28. Hôm nay mỗi nhân viên lập bao nhiêu phiếu.
SELECT e.emp_id, e.surname, e.given_name, COUNT(l.invoice_no) AS invoices_today
FROM employee e
LEFT JOIN loan l
ON l.emp_id = e.emp_id
AND l.date_out >= CURRENT_DATE
AND l.date_out < CURRENT_DATE + INTERVAL 1 DAY
GROUP BY e.emp_id, e.surname, e.given_name;
29. Sách có ít nhất một phiếu mượn và thuộc NXB ở Hà Nội, kèm số lần được mượn.
SELECT b.title, COUNT(*) AS loan_count
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
JOIN loan l ON l.book_id = b.book_id
WHERE p.city = 'Hanoi'
GROUP BY b.book_id, b.title;
30. Sách có giá thấp hơn giá trung bình toàn bộ và thuộc NXB ở Hà Nội.
SELECT b.title, b.price
FROM book b
JOIN publisher p ON p.pub_id = b.pub_id
WHERE p.city = 'Hanoi'
AND b.price < (SELECT AVG(price) FROM book);
“Giá trung bình của tất cả sách” là trung bình toàn bảng, nên subquery không lọc theo thành phố. Nếu đề muốn trung bình của riêng sách Hà Nội thì subquery cũng phải join và lọc theo city. Lại là một chỗ cần hỏi lại yêu cầu.
7. Senior sẽ làm thêm gì trước khi lên production
Ý chính: lời giải trên đúng với đề, nhưng hệ thống thật phải chịu được người dùng đồng thời và vài năm dữ liệu. Bốn điểm mình sẽ nêu khi review: trả sách nguyên tử, index cho truy vấn nóng, chọn khoá chính, và dữ liệu cũ.
7.1. Trả sách và tính tiền phạt trong một câu lệnh
Cách ngây thơ: đọc phiếu lên Java, tính số ngày trễ, set returnedAt, penalty, rồi lưu. Hai thủ thư cùng bấm “trả sách” (hoặc một người bấm hai lần) sẽ chạy hai lần read-modify-write (đọc, sửa trong app, ghi lại). Gộp thành một UPDATE có điều kiện:
UPDATE loan
SET returned_at = NOW(),
penalty = GREATEST(DATEDIFF(NOW(), due_date), 0) * 0.500 -- 0.5 mỗi ngày trễ
WHERE invoice_no = 1003
AND returned_at IS NULL;
7.2. Index cho các truy vấn nóng
Truy vấn chạy nhiều nhất trong thư viện thật: “phiếu đang quá hạn” (câu 7, 27), “sách thành viên X đang giữ” và “phiếu hôm nay” (câu 28). Index idx_loan_emp_date (emp_id, date_out) đã phục vụ câu 28. Cho truy vấn quá hạn, mình thêm:
CREATE INDEX idx_loan_open_due ON loan (returned_at, due_date);
- MySQL xử lý
returned_at IS NULLnhư một phép so bằng trên cột đầu, nêndue_date < ?vẫn range scan được trên cột thứ hai. - PostgreSQL gọn hơn: partial index
ON loan (due_date) WHERE returned_at IS NULL, chỉ chứa phiếu đang mở nên nhỏ hơn rất nhiều.
Cách đọc EXPLAIN để kiểm tra nằm ở bài index và hiệu năng truy vấn.
7.3. Khoá tự nhiên hay khoá thay thế
7.4. Dữ liệu tăng theo thời gian
Bảng loan chỉ thêm không bớt. Sau vài năm, phiếu đã đóng chiếm gần hết bảng nhưng hiếm khi được đọc. Hai hướng:
- Job chạy theo lô chuyển phiếu đã đóng quá N năm sang
loan_archive. - Partition theo
date_outđể xoá cả partition cũ trong một lệnh. Nhưng MySQL không cho foreign key trên bảng partition, nên đây là quyết định có giá, cần cân nhắc từ lúc thiết kế.
Ghi nhớ
- Giải thích được vì sao
UNIQUE(book_id)sai và cách giả lập partial unique index trên MySQL. - Biết vì sao ràng buộc trong DB vẫn cần dù đã validate ở Java (race condition, nhiều đường ghi).
- Viết được truy vấn “mỗi X kèm số lượng” mà vẫn hiện số 0, và giải thích
WHEREkhácHAVING. - Biết viết điều kiện ngày sargable, khoảng nửa mở và để ý timezone.
- Biết làm thao tác “trả sách” idempotent bằng
UPDATE ... WHERE returned_at IS NULL.
Câu hỏi phỏng vấn
Junior
Vì sao câu 19 dùng LEFT JOIN và COUNT(l.invoice_no) thay vì COUNT(*)?
Để thành viên chưa mượn lần nào vẫn hiện, và hiện đúng số 0 chứ không phải 1.
LEFT JOIN giữ lại thành viên đó, với các cột của loan là NULL. COUNT(*) đếm dòng nên vẫn tính là 1. COUNT(l.invoice_no) chỉ đếm giá trị khác NULL, nên ra đúng 0.
Ở câu 25, điều kiện thể loại đặt ở WHERE còn ngưỡng số lần đặt ở HAVING. Vì sao?
WHERE lọc từng dòng trước khi gom nhóm, HAVING lọc sau khi gom.
Lọc thể loại ở WHERE vừa đúng nghĩa vừa giảm số dòng phải gom. HAVING là chỗ duy nhất dùng được giá trị tổng hợp như COUNT(*) >= 2. Đặt điều kiện thể loại vào HAVING thì sai cú pháp (cột không nằm trong nhóm) hoặc chậm hơn.
Mid
Làm sao đảm bảo “một cuốn sách chỉ có một phiếu mượn đang mở” ở tầng database?
Unique index chỉ áp lên phiếu đang mở: partial unique index trên PostgreSQL, generated column + unique trên MySQL.
- PostgreSQL:
UNIQUE (book_id) WHERE returned_at IS NULL. - MySQL: generated column bằng
book_idkhi chưa trả, NULL khi đã trả, rồi đặt unique trên cột đó (unique cho phép nhiều NULL).
Kiểm tra trong code thôi thì không đủ vì race condition giữa hai request đồng thời.
Đã validate dữ liệu ở tầng Java rồi, có cần NOT NULL, CHECK, FK trong database nữa không?
Có. Database là cửa cuối cùng mà mọi đường ghi đều phải đi qua.
Script migration, job batch, một service khác, người chạy SQL tay đều không đi qua code Java của bạn. Check-then-act cũng không an toàn khi đồng thời. Ràng buộc trong DB rẻ, luôn đúng, và là tài liệu sống của nghiệp vụ. Validation ở ứng dụng vẫn cần để trả lỗi thân thiện; hai lớp bổ sung cho nhau.
Senior
Bảng loan lên 50 triệu dòng, màn hình “phiếu quá hạn” chậm dần. Bạn xử lý thế nào?
Đo bằng EXPLAIN, thêm index chỉ nhắm vào phiếu đang mở, rồi về dài hạn archive phiếu cũ để bảng nóng nhỏ lại.
- Xem có full scan không. Truy vấn lọc
returned_at IS NULL AND due_date < ?, nên thêm index(returned_at, due_date)trên MySQL hoặc partial index(due_date) WHERE returned_at IS NULLtrên PostgreSQL; phiếu đang mở chỉ là phần nhỏ nên index rất gọn. - Danh sách dài: phân trang keyset thay vì
OFFSET. - Dài hạn: archive phiếu đã đóng lâu năm sang bảng riêng.
- Báo cáo tổng hợp vẫn nặng: đẩy sang replica đọc hoặc kho dữ liệu, không chạy trên primary.
Đề có chỗ mơ hồ (ví dụ “xoá sách”, “không trễ hạn”). Bạn làm gì?
Không tự đoán rồi code: liệt kê các cách hiểu, đề xuất một mặc định có lý do, rồi xác nhận với người ra yêu cầu.
Ghi rõ hệ quả từng cách: xoá cứng làm mất lịch sử mượn và vướng FK; “không trễ” có tính phiếu đang mở chưa tới hạn không. Câu trả lời đã chốt nên thành test case hoặc ghi vào spec, để lần sau không phải hỏi lại. Với dữ liệu, mặc định an toàn là không mất thông tin: soft delete thay vì xoá cứng.
Từ vựng tiếng Anh
| Term | Phát âm | Nghĩa | Ví dụ câu tiếng Anh dùng trong phỏng vấn |
|---|---|---|---|
| generated column | /ˈdʒenəreɪtɪd ˈkɒləm/ | cột sinh tự động | “I used a generated column to emulate a partial unique index in MySQL.” |
| sargable | /ˈsɑːɡəbl/ | điều kiện dùng được index | “Wrapping the column in DATE() makes the predicate non-sargable.” |
| semi-join | /ˈsemi dʒɔɪn/ | phép nửa kết nối (EXISTS) | “EXISTS acts as a semi-join, so it doesn’t duplicate rows.” |
| ambiguous requirement | /æmˈbɪɡjuəs rɪˈkwaɪəmənt/ | yêu cầu mơ hồ | “The requirement was ambiguous, so I confirmed whether ‘delete’ meant hard or soft delete.” |
| partial index | /ˈpɑːʃl ˈɪndeks/ | index chỉ chứa các dòng thoả một điều kiện | “A partial unique index on open loans enforces one active loan per book.” |
| surrogate key | /ˈsʌrəɡət kiː/ | khoá thay thế, không mang nghĩa nghiệp vụ | “I’d use a surrogate key and keep the card number as a unique business key.” |
| natural key | /ˈnætʃrəl kiː/ | khoá tự nhiên lấy từ dữ liệu nghiệp vụ | “Natural keys are risky when the business can change them.” |
| half-open interval | /hɑːf ˈəʊpən ˈɪntəvl/ | khoảng nửa mở [a, b) | “I filter by a half-open interval so the index on date_out can be used.” |
| overdue | /ˌəʊvəˈdjuː/ | quá hạn | “An overdue loan is one that’s still open after its due date.” |
| idempotent | /ˌaɪdemˈpəʊtənt/ | gọi nhiều lần cho cùng kết quả | “Guarding the update with returned_at IS NULL makes the return idempotent.” |
| race condition | /reɪs kənˈdɪʃn/ | lỗi do hai thao tác đồng thời chen nhau | “A check-then-insert in the service layer has a race condition.” |
| soft delete | /sɒft dɪˈliːt/ | đánh dấu đã xoá thay vì xoá thật | “For a library I’d soft delete books to keep the loan history.” |
Ghi chép dựa trên khoá Fundamentals of Backend (Ronin Engineer), viết lại theo cách hiểu của mình.