Cả bài trong một trang: ý chính, sơ đồ, điểm phỏng vấn.
Quy trình data modeling: access pattern, loại bảng, khoá ngoại và table template
1Bắt đầu từ access pattern
Access pattern là cách dữ liệu được đọc, ghi: lọc gì, sắp gì, bao nhiêu lần mỗi giây. Viết danh sách này trước khi vẽ bảng; nó là tiêu chí nghiệm thu schema.
flowchart TD
A["Phân tích: danh sách truy vấn, tần suất, p95"] --> B["Mô hình hoá: gom trường thành bảng"]
B --> C["Toàn vẹn: chuẩn hoá đến đâu"]
C --> D{"Truy vấn nóng có index phục vụ?"}
D -->|"Có"| E["Giữ chuẩn hoá"]
D -->|"Không"| F["Phi chuẩn hoá có chủ đích"]
2Bốn loại bảng
Entity có danh tính và vòng đời (cột status); mọi bảng còn lại là relation, support hoặc config. Biết loại thì biết bảng cần cột gì.
Loại
Ví dụ
Đặc điểm
Entity
post, booking
status, version, cột audit
Relation
post_tag
Khoá chính ghép, N–M
Support
post_revision
1–N, khoá ngoại về cha
Config
country, currency
Khoá tự nhiên, cache được
3Khi nào phi chuẩn hoá
Chuẩn hoá làm nền. Thêm cột dư thừa khi truy vấn nóng lọc ở bảng A nhưng sắp ở bảng B: chỉ cùng bảng mới có composite index phục vụ trọn.
Feed tác giả SG, 2 triệu bài
Buffer
Thời gian
JOIN post–author
125.000
63 ms
post.author_country + index
23
0,074 ms
4Khoá ngoại: giữ hay bỏ
Mặc định giữ khi cùng database và service. Chi phí thật là khoá dòng cha: InnoDB insert dòng con khoá S dòng cha, chặn mọi UPDATE vào nó.
PostgreSQL chỉ khoá FOR KEY SHARE: UPDATE thường vẫn chạy.
Bỏ FK khi khác shard, khác service, hoặc dùng gh-ost.
Bỏ thì bù: SELECT ... FOR SHARE hoặc job tìm dòng mồ côi.
Cột tham chiếu 1–1 đặt ở entity sinh sau.
5Table template cho entity
Mỗi cột kỹ thuật làm một việc; bảng không cần việc đó thì bỏ cột đó. Sổ cái chỉ thêm thì bỏ updated_* và version.
version int NOT NULL DEFAULT 0, -- khoá lạc quan
created_at timestamptz NOT NULL, -- keyset, partition
created_by text NOT NULL, -- ai tạo
updated_at timestamptz NOT NULL, -- đồng bộ CDC
updated_by text NOT NULL
💡 Ghi nhớ
Danh sách truy vấn kèm tần suất, p95 trước ERD.
status là máy trạng thái, không phải chuỗi tự do.
Cột đếm cập nhật nguyên tử: SET n = n + 1.
NoSQL càng phải bắt đầu từ access pattern.
⭐ Điểm phỏng vấn
Tự trả lời trước, rồi bấm từng câu để xem đáp án.
Bắt đầu thiết kế schema thế nào?
Liệt kê truy vấn, tần suất, mục tiêu độ trễ.
Cột tham chiếu 1–1 đặt đâu?
Bảng sinh sau: payment_txn.order_id + UNIQUE.
Có bỏ khoá ngoại không?
Mặc định giữ; bỏ khi khác shard, service.
Xong phần tự kiểm tra, bài này đã được đánh dấu đã học ✓
⚠️ Lỗi thường gặp
Mở công cụ vẽ ERD kéo ra mười cái hộp trước khi có truy vấn.
Đọc số đếm lên Java, cộng một, ghi lại: mất cập nhật.
Kiểm tra cha bằng SELECT rồi INSERT không khoá: lọt dòng mồ côi.
Schema là thứ khó sửa nhất của backend: code sai thì deploy lại, schema sai thì phải migrate hàng trăm triệu dòng khi hệ thống vẫn chạy. Bài này là nửa đầu quy trình: bắt đầu từ truy vấn, phân loại bảng, chuẩn hoá hay không, giữ hay bỏ khoá ngoại, đặt cột tham chiếu và table template. Phần 2 nói về cột JSON, đổi schema không downtime, kiểu dữ liệu và view; phần 3 áp dụng tất cả vào sáu bài toán cụ thể.
Nên đọc trước: chuẩn hoá 1NF–5NF cho lý thuyết dạng chuẩn, index và EXPLAIN cho composite index. Số đo chạy thật trên PostgreSQL 16 và MySQL 8.4 (Docker, laptop Apple Silicon), chỉ để so tỉ lệ, không phải benchmark.
1. Quy trình: bắt đầu từ access pattern, không phải từ bảng
Ý chính: liệt kê truy vấn và lệnh ghi chính (kèm tần suất, độ trễ mong muốn) trước, rồi mới gom trường thành bảng. Bảng vẽ trước truy vấn trông hợp lý nhưng chưa chắc trả lời nhanh câu hỏi quan trọng nhất.
Access pattern là cách dữ liệu được đọc và ghi: truy vấn nào, lọc gì, sắp gì, bao nhiêu lần mỗi giây. Ba giai đoạn, theo đúng thứ tự:
Phân tích: yêu cầu, danh sách access pattern, schema hiện có (nếu sửa hệ thống cũ), danh sách trường sơ bộ.
Mô hình hoá: gom trường thành bảng, tinh chỉnh, chọn khoá và quan hệ.
Toàn vẹn dữ liệu: review, chuẩn hoá đến đâu, phi chuẩn hoá chỗ nào.
Sai lầm phổ biến: nhảy thẳng vào bước 2, mở công cụ vẽ ERD kéo ra mười cái hộp.
Ví dụ: nền tảng blog nhiều tác giả
#
Truy vấn
Tần suất
Mục tiêu
Q1
Feed: 20 bài mới nhất của tác giả thuộc một quốc gia
300 req/s
p95 < 50 ms
Q2
Chi tiết bài viết kèm số bình luận
1.000 req/s
p95 < 20 ms
Q3
Dashboard tác giả: các bài của tôi, mới nhất trước
20 req/s
p95 < 200 ms
W1
Đăng bài
5 req/s
p95 < 300 ms
W2
Thêm bình luận
50 req/s
p95 < 100 ms
Trường sơ bộ tự gom thành nhóm (tác giả, bài viết, bình luận), thành bảng. Bảng truy vấn còn lộ hai điều ERD không thấy:
Q1 lọc theo tác giả nhưng sắp theo bài viết: dấu hiệu cần phi chuẩn hoá (số đo ở mục 3).
Q2 đọc gấp 20 lần W2 ghi: đáng có một cột đếm bình luận.
2. Entity, ERD và bốn loại bảng
Ý chính: entity là thứ có danh tính và vòng đời; mọi bảng còn lại là relation, support hoặc config. Biết bảng thuộc loại nào thì biết nó cần cột gì.
Entity có danh tính (id không đổi suốt đời) và vòng đời (thường là cột status). Bài viết là entity: được tạo, đăng, ẩn, xoá. Quốc gia trong danh mục không có vòng đời đáng kể; nó là dữ liệu cấu hình.
Coi status là máy trạng thái (tập trạng thái cố định, bước chuyển được phép), không phải chuỗi tự do:
stateDiagram-v2
[*] --> DRAFT
DRAFT --> PUBLISHED: publish
PUBLISHED --> HIDDEN: moderator hides
HIDDEN --> PUBLISHED: restore
DRAFT --> DELETED: delete
PUBLISHED --> DELETED: delete
DELETED --> [*]
Bước chuyển hợp lệ thường kiểm ở tầng domain, kèm điều kiện UPDATE ... WHERE status = 'DRAFT' để hai request đồng thời không cùng chuyển.
ERD (sơ đồ entity và quan hệ) trên slide dùng ký pháp Chen (hộp, hình thoi, oval; sinh viên N–M lớp học, lớp N–1 giáo viên). Ký pháp crow’s foot (Mermaid erDiagram) gọn và gần bảng hơn.
Loại bảng
Vai trò
Ví dụ
Đặc điểm
Entity
Đối tượng nghiệp vụ có vòng đời
author, post, booking
Có status, version, cột audit
Relation
Nối quan hệ N–M
post_tag (post_id, tag_id)
Khoá chính ghép, thường không có id riêng
Support
Dữ liệu phụ của một entity, tách ra vì 1–N hoặc vì lớn
post_revision, author_address
Có khoá ngoại về entity cha
Config
Danh mục ít đổi
country, currency, system_config
Đọc nhiều, cache được, thường khoá tự nhiên
Quy ước: entity dùng template đầy đủ (mục 5), relation chỉ cần created_at, config dùng khoá tự nhiên (country_code char(2)) và cache ở app.
3. Chuẩn hoá hay phi chuẩn hoá: một khung quyết định
Ý chính: chuẩn hoá làm nền, rồi phi chuẩn hoá có chủ đích ở chỗ đo được, điển hình là khi truy vấn nóng lọc ở một bảng nhưng sắp ở bảng khác. Cái giá là phải tự giữ dữ liệu dư thừa nhất quán.
Lý thuyết dạng chuẩn ở bài chuẩn hoá. Hai mặt cần nhớ:
Chuẩn hoá (tách bảng lớn thành bảng nhỏ có quan hệ): ít trùng lặp, toàn vẹn tốt; nhưng phải JOIN, và có truy vấn không index nào phục vụ trọn.
Phi chuẩn hoá (gộp bảng, chép dữ liệu dư thừa): đọc nhanh, index được tổ hợp cột của hai bảng; nhưng tốn chỗ, tự giữ nhất quán.
Với Q1 (100.000 tác giả, 2 triệu bài, SG chỉ 0,1% tác giả), cùng một câu SQL:
Cách
Quốc gia
Buffer đọc
Thời gian
JOIN post–author, lọc ở author
VN (60% tác giả)
đọc 32 bài
0,2 ms
JOIN post–author, lọc ở author
SG (0,1%)
125.000 (≈41.000 bài)
63 ms
Cột post.author_country + index (author_country, created_at)
SG
23
0,074 ms
Cái giá: tác giả đổi quốc gia thì phải cập nhật mọi bài của họ.
Câu SQL và kế hoạch thực thi của hai cách
-- Chuẩn hoá: lọc ở author, sắp xếp ở postSELECT p.post_id, p.titleFROM post p JOIN author a USING (author_id)WHERE a.country = 'SG'ORDER BY p.created_at DESC LIMIT 20;
PostgreSQL đi ngược index post (created_at) và kiểm từng tác giả. Với VN chỉ cần đọc 32 bài là đủ 20 kết quả. Với SG phải đọc khoảng 41.000 bài và 125.000 buffer mới gom đủ 20 dòng. Độ trễ phụ thuộc vào giá trị tham số.
SELECT post_id, title FROM postWHERE author_country = 'SG'ORDER BY created_at DESC LIMIT 20;-- Index Scan Backward using post_country_created_idx-- Buffers: 23, Execution Time: 0.074 ms
Giữ cột đếm nhất quán
Ý chính: cột đếm (comment_count) cập nhật nguyên tử trong SQL, qua cùng transaction, trigger hoặc event; không đọc lên app cộng một rồi ghi lại.
Cách
Nhất quán
Chi phí
Cùng transaction: SET comment_count = comment_count + 1
Mạnh
Mọi chỗ ghi phải nhớ; dòng hot bị tranh khoá
Trigger trong database
Mạnh
Logic ẩn, khó debug (bài tập 2)
Event bất đồng bộ (outbox, Kafka)
Cuối cùng
Có độ trễ; cần idempotency và đối soát
4. Khoá ngoại: giữ hay bỏ
Ý chính: mặc định giữ khoá ngoại khi các bảng cùng database và service. Chi phí thật không phải lần kiểm tra mà là khoá trên dòng cha, nặng hơn hẳn ở InnoDB.
Slide nêu quan điểm cá nhân: bỏ ràng buộc khoá ngoại, kiểm tra ở tầng ứng dụng. Quan điểm này phổ biến ở công ty dùng MySQL sharding, nhưng không nên áp mặc định: ba trong bốn lý do của slide không còn đúng.
Bốn lý do bỏ FK của slide và phản biện
Lý do của slide
Thực tế
Database phải kiểm khoá cha mỗi lần ghi bảng con
Chỉ là một lần tra index khoá chính bảng cha, thường nằm sẵn trong RAM
Ứng dụng phải phân tích exception để biết lý do lỗi
Spring đã dịch thành DataIntegrityViolationException có tên constraint
Backup/restore phải theo thứ tự
pg_dump, mysqldump đã lo
Schema phức tạp hơn
Đúng, nhưng đổi lại không bao giờ có dòng mồ côi
Slide cũng nhắc một ý đúng: khoá ngoại (constraint) khác cột tham chiếu; bỏ constraint thì cột customer_id vẫn còn và vẫn cần index.
Khoá trên dòng cha: PostgreSQL và InnoDB khác nhau
Ý chính: khi có dòng con mới chưa commit, PostgreSQL chỉ chặn xoá hoặc đổi khoá của dòng cha; InnoDB chặn mọiUPDATE vào dòng cha.
Phiên A insert đơn hàng của khách 1, chưa commit; phiên B thao tác trên dòng khách 1 (lock_timeout = 1s):
Thao tác của phiên B
PostgreSQL 16
MySQL 8.4 (InnoDB)
UPDATE customer SET name = ... (không đổi khoá)
Chạy ngay (0,7 ms)
Bị chặn, hết 1 giây thì lỗi 1205
DELETE FROM customer WHERE id = 1
Bị chặn
Bị chặn
UPDATE customer SET id = 10
Bị chặn
Bị chặn
Kiểm tra ở ứng dụng có race condition
Ý chính:SELECT xem cha có không rồi INSERT để lọt dòng mồ côi khi cha bị xoá ở giữa. Muốn đúng phải khoá dòng cha (SELECT ... FOR SHARE), tức tự viết lại thứ FK làm sẵn.
Dòng mồ côi là dòng con trỏ tới một dòng cha không còn tồn tại.
Thí nghiệm race condition: có FK và không có FK
Trên PostgreSQL, bảng đơn hàng không có FK:
Phiên A BEGIN, thấy khách 2 tồn tại, ngủ 2 giây.
Trong lúc đó phiên B xoá khách 2 và commit.
Phiên A insert đơn hàng cho khách 2 và commit thành công: một đơn hàng mồ côi.
Làm lại với bảng có FK: phiên B xoá được (A chưa insert), nhưng lệnh insert của A bị từ chối với violates foreign key constraint.
Cột tham chiếu đặt ở bảng nào
Ý chính: với quan hệ 1–1, entity sinh ra sau giữ tham chiếu tới entity sinh ra trước, kèm UNIQUE để giữ 1–1.
Bài toán trên slide: đặt hàng tạo một order rồi một giao dịch thanh toán; quyên góp chỉ tạo giao dịch, không có order. Order và giao dịch quan hệ 1–1. Câu trả lời: đặt order_id trong bảng giao dịch.
CREATE TABLE orders ( order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id bigint NOT NULL, total bigint NOT NULL, status text NOT NULL, created_at timestamptz NOT NULL DEFAULT now());CREATE TABLE payment_txn ( txn_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, purpose text NOT NULL CHECK (purpose IN ('ORDER', 'DONATION')), order_id bigint REFERENCES orders, -- NULL với quyên góp campaign_id bigint, -- chỉ có với quyên góp amount bigint NOT NULL CHECK (amount > 0), created_at timestamptz NOT NULL DEFAULT now(), CHECK ((purpose = 'ORDER') = (order_id IS NOT NULL)), UNIQUE (order_id) -- giữ quan hệ 1–1);
Khi tạo order, giao dịch chưa có; đặt txn_id trong orders buộc insert cột rỗng rồi update lại.
Thêm loại giao dịch mới (nạp ví, hoàn tiền) không đụng tới orders: đó là ý “tốt cho khả năng mở rộng” của slide.
Bỏ UNIQUE (order_id) là thành 1–N (thanh toán nhiều lần) mà không đổi cột.
5. Table template: cột nghiệp vụ và cột kỹ thuật
Ý chính: entity table có cột nghiệp vụ (id, cột riêng, status, data JSON) và cột kỹ thuật (version, created_at/by, updated_at/by). Mỗi cột kỹ thuật làm một việc; bảng không cần việc đó thì bỏ cột đó.
CREATE TABLE app_user ( user_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- nghiệp vụ name varchar(100) NOT NULL, birth_date date, status text NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'LOCKED', 'CLOSED')), data jsonb NOT NULL DEFAULT '{}', -- kỹ thuật version int NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), created_by text NOT NULL, updated_at timestamptz NOT NULL DEFAULT now(), updated_by text NOT NULL);
Cột
Để làm gì
version
Khoá lạc quan: WHERE id = ? AND version = ?, 0 dòng nghĩa là có người sửa trước
created_at
Sắp xếp, phân trang keyset, partition theo thời gian
created_by, updated_by
Ai (người dùng, admin, service, job) đã tạo, sửa
updated_at
Đồng bộ tăng dần sang hệ khác (CDC, search index)
Slide dùng age SMALLINT; lưu birth_date đúng hơn vì tuổi đổi mỗi năm.
MySQL có ON UPDATE CURRENT_TIMESTAMP(3) cho updated_at; PostgreSQL cần trigger BEFORE UPDATE (bài tập 1) hoặc ORM.
Bảng chỉ thêm (sổ cái, log sự kiện) không cần updated_at, version; bảng relation chỉ cần created_at.
Cột data JSON có giới hạn riêng (phần 2). Soft delete (deleted_at) là quyết định riêng, có giá với unique index và mọi truy vấn (soft delete).
Code Spring Boot 3: AuditableEntity và AuditorAware
JPA và Spring Data lo phần lớn cột kỹ thuật:
@MappedSuperclass@EntityListeners(AuditingEntityListener.class)public abstract class AuditableEntity { @Version private int version; @CreatedDate @Column(nullable = false, updatable = false) private Instant createdAt; @CreatedBy @Column(nullable = false, updatable = false) private String createdBy; @LastModifiedDate @Column(nullable = false) private Instant updatedAt; @LastModifiedBy @Column(nullable = false) private String updatedBy;}@Configuration@EnableJpaAuditingclass AuditConfig { @Bean AuditorAware<String> auditorAware() { // người dùng đăng nhập, hoặc tên service/job khi chạy nền return () -> Optional.ofNullable(SecurityContextHolder.getContext().getAuthentication()) .map(Authentication::getName) .or(() -> Optional.of("system")); }}
6. Ngoài quan hệ: mô hình NoSQL cũng bắt đầu từ access pattern
Ý chính: với key-value hay document store, truy vấn không thiết kế trước thì gần như không trả lời được; bảng access pattern ở mục 1 chính là schema. (Phần này không có trong slide, mình thêm để đối chiếu.)
Database quan hệ cho chuẩn hoá trước, thêm index sau, vì SQL trả lời được truy vấn bất kỳ; DynamoDB, Cassandra, MongoDB thì không.
Cassandra, DynamoDB và MongoDB mô hình hoá theo truy vấn thế nào
Cassandra, DynamoDB: mỗi truy vấn chính thường có một bảng (hoặc index) với partition key và sort key chọn đúng cho nó; dữ liệu được nhân bản giữa các bảng. “Single-table design” của DynamoDB đặt nhiều loại entity vào một bảng, phân biệt bằng tiền tố khoá (AUTHOR#42, POST#2026-11-18#981) để một lần đọc lấy được cả cụm.
MongoDB: nhúng (embed) dữ liệu luôn đọc cùng nhau và có giới hạn số lượng (địa chỉ của user); tham chiếu (reference) dữ liệu tăng không giới hạn (bình luận của bài viral).
Bài tập
Bài 1: Áp table template vào schema đặt vé máy bay
Đề (diễn đạt lại): bài tập về nhà của slide là thiết kế schema đặt vé máy bay theo table template. Schema đầy đủ đã có ở bài thiết kế database đặt vé và bài booking, sổ cái, nên ở đây làm phần còn thiếu: áp template cho ba bảng flight, booking, entry và giải thích cột kỹ thuật nào hợp, cột nào không.
Lời giải bài 1: template cho flight, booking, entry
Chạy trên PostgreSQL 16:
CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN NEW.updated_at := now(); RETURN NEW;END $$;CREATE FUNCTION forbid_change() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN RAISE EXCEPTION '% is append-only, % is not allowed', TG_TABLE_NAME, TG_OP;END $$;-- booking: aggregate root, có data jsonb cho thuộc tính phụ theo kênh bánCREATE TABLE booking ( booking_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, booking_code char(6) NOT NULL UNIQUE, user_id bigint NOT NULL, status text NOT NULL DEFAULT 'PENDING_PAYMENT', total_amount bigint NOT NULL CHECK (total_amount >= 0), currency char(3) NOT NULL DEFAULT 'VND', data jsonb NOT NULL DEFAULT '{}', version int NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now(), created_by text NOT NULL, updated_at timestamptz NOT NULL DEFAULT now(), updated_by text NOT NULL);CREATE TRIGGER booking_touch BEFORE UPDATE ON booking FOR EACH ROW EXECUTE FUNCTION touch_updated_at();-- entry: bút toán sổ cái, chỉ thêmCREATE TABLE entry ( entry_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, txn_id bigint NOT NULL, account_id bigint NOT NULL, amount bigint NOT NULL CHECK (amount <> 0), created_at timestamptz NOT NULL DEFAULT now(), created_by text NOT NULL);CREATE TRIGGER entry_append_only BEFORE UPDATE OR DELETE ON entry FOR EACH ROW EXECUTE FUNCTION forbid_change();
Kiểm tra:
Tạo booking với version = 0. Service thanh toán UPDATE ... SET status = 'PAID', version = version + 1 ... WHERE booking_id = 1 AND version = 0 trả 1 dòng.
Một request cũ cũng điều kiện version = 0 trả 0 dòng (bị phát hiện sửa chồng); updated_at đã lớn hơn created_at nhờ trigger.
UPDATE entry bị từ chối với entry is append-only, UPDATE is not allowed.
Từng quyết định:
flight (DDL giống bảng gốc, thêm version, created_*, updated_* và trigger touch_updated_at, không in lại): đủ bộ cột kỹ thuật vì giờ bay bị nhân viên vận hành và job đồng bộ lịch bay sửa; updated_by trả lời câu “ai đổi giờ chuyến VN213”. Không có cột data: thuộc tính chuyến bay ổn định và đều cần lọc.
booking: đủ bộ, data jsonb hợp cho thông tin theo kênh bán (mã đại lý, UTM, thiết bị) vốn thưa và không lọc. version bắt buộc vì booking bị nhiều bên sửa (khách, service thanh toán, job hết hạn). created_by có vẻ trùng user_id nhưng không: booking có thể do nhân viên tổng đài tạo hộ.
entry: chỉ created_at, created_by. Sổ cái không bao giờ sửa: sai thì ghi bút toán đảo. updated_* hay version ngầm cho phép sửa nên bỏ; trigger chặn UPDATE, DELETE biến quy ước thành ràng buộc. Ở production nên thêm REVOKE UPDATE, DELETE cho role của ứng dụng.
Bài 2: Cột đếm bình luận bằng trigger
Đề: giữ article.comment_count luôn bằng số bình luận thật, kể cả khi nhiều request ghi đồng thời và có người insert bằng SQL tay.
Lời giải bài 2: trigger đếm bình luận và lỗi search_path
Chạy trên PostgreSQL 16:
CREATE TABLE article ( article_id bigint PRIMARY KEY, title text NOT NULL, comment_count int NOT NULL DEFAULT 0 CHECK (comment_count >= 0));CREATE TABLE comment ( comment_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, article_id bigint NOT NULL REFERENCES article, body text NOT NULL);CREATE INDEX ON comment (article_id);CREATE FUNCTION sync_comment_count() RETURNS triggerLANGUAGE plpgsql SET search_path = public AS $$BEGIN IF TG_OP = 'INSERT' THEN UPDATE article SET comment_count = comment_count + 1 WHERE article_id = NEW.article_id; ELSIF TG_OP = 'DELETE' THEN UPDATE article SET comment_count = comment_count - 1 WHERE article_id = OLD.article_id; END IF; RETURN NULL;END $$;CREATE TRIGGER comment_count_sync AFTER INSERT OR DELETE ON comment FOR EACH ROW EXECUTE FUNCTION sync_comment_count();
Kết quả: 5 insert, 1 delete thì comment_count = 4, khớp count(*). 8 phiên song song, mỗi phiên 25 insert: 204 khớp 204, vì UPDATE ... + 1 khoá dòng bài viết và tuần tự hoá các lượt cộng.
Lần chạy đầu, hàm không có SET search_path trong khi bảng nằm ở schema riêng, và mọi insert từ phiên khác lỗi relation "article" does not exist: tên không kèm schema được tìm theo search_path của phiên gọi. Luôn ghim search_path trong hàm trigger.
Mọi bình luận của một bài khoá cùng một dòng article. Bài viral cần đếm theo slot hoặc event cộng theo lô, kèm job đối soát count(*) với cột đếm.
Ghi nhớ
Giải thích được vì sao phải viết danh sách access pattern (tần suất, p95) trước khi vẽ ERD.
Phân loại được một bảng vào entity, relation, support hay config, và biết mỗi loại cần cột kỹ thuật nào.
Biết khi nào thêm cột dư thừa (lọc bảng A, sắp bảng B) và ba điều kiện để làm an toàn.
So sánh được khoá dòng cha khi insert dòng con ở InnoDB và PostgreSQL, và khi nào bỏ FK.
Biết đặt cột tham chiếu 1–1 ở bảng nào và vì sao sổ cái không cần updated_*, version.
Câu hỏi phỏng vấn
Junior
Entity khác bảng config thế nào?
Entity có danh tính và vòng đời; bảng config là danh mục ít đổi, không có vòng đời đáng kể.
Entity thường có status đổi theo thời gian (đơn hàng, bài viết). Config (quốc gia, tiền tệ) hợp với khoá tự nhiên và cache ở tầng ứng dụng.
Khoá ngoại khác cột tham chiếu thế nào?
Cột tham chiếu là dữ liệu (lưu id dòng cha); khoá ngoại là ràng buộc database đặt lên cột đó để giá trị luôn trỏ tới dòng cha tồn tại.
Bỏ ràng buộc thì cột vẫn còn và vẫn cần index nếu có truy vấn đi theo nó.
Bảng relation cho quan hệ N–M thường có khoá chính thế nào?
Khoá chính ghép từ hai khoá ngoại, ví dụ post_tag (post_id, tag_id).
Nó vừa chống trùng cặp vừa phục vụ truy vấn theo cột đầu. Thêm index (tag_id, post_id) nếu cần chiều ngược lại. Thường chỉ cần thêm created_at, không cần đủ table template.
Mid
Bạn bắt đầu thiết kế schema cho một tính năng mới thế nào?
Viết danh sách truy vấn và lệnh ghi chính trước, mỗi cái có tần suất và mục tiêu độ trễ.
Sau đó gom trường thành entity, phân loại bảng (entity, relation, support, config), chuẩn hoá làm nền, rồi kiểm từng truy vấn nóng xem có index phục vụ được không; chỗ nào không thì cân nhắc phi chuẩn hoá có chủ đích.
Cột tham chiếu của quan hệ 1–1 giữa order và giao dịch thanh toán nên đặt ở bảng nào?
Ở bảng sinh ra sau, tức payment_txn.order_id, kèm UNIQUE để giữ 1–1.
Khi tạo order chưa có giao dịch nên không phải insert rồi update. Thêm loại giao dịch mới (quyên góp không có order) không phải đổi bảng orders.
Vì sao read-modify-write một cột đếm trong Java là sai?
Hai request đọc cùng giá trị, cùng cộng một, cùng ghi, nên mất một lượt (lost update).
Sửa bằng cập nhật nguyên tử SET count = count + 1 trong SQL, hoặc khoá lạc quan @Version để một bên thất bại và thử lại.
Khi nào bạn thêm cột dư thừa vào một bảng đã chuẩn hoá?
Khi một truy vấn nóng lọc ở bảng này nhưng sắp ở bảng kia, nên không composite index nào phục vụ trọn.
Ví dụ feed theo quốc gia của tác giả: thêm author_country vào post và index (author_country, created_at) giảm từ 125.000 buffer xuống 23. Điều kiện: dữ liệu nguồn ít đổi, một cơ chế đồng bộ duy nhất, và job đối soát.
Senior
Bạn có bỏ ràng buộc khoá ngoại không?
Mặc định giữ khi các bảng cùng một database và một service sở hữu: kiểm tra chỉ là một lần tra index, đổi lại không bao giờ có dòng mồ côi.
Bỏ khi cha con ở shard hay service khác, khi cần công cụ migrate như gh-ost, hoặc khi shared lock của InnoDB trên dòng cha gây tranh chấp đo được. Bỏ thì phải có kiểm tra có khoá hoặc job tìm dòng mồ côi; SELECT rồi INSERT không khoá có race condition.
Insert dòng con có khoá ngoại ảnh hưởng gì tới dòng cha?
InnoDB đặt shared lock trên bản ghi cha tới hết transaction, chặn mọi UPDATE vào dòng cha; PostgreSQL chỉ khoá FOR KEY SHARE.
FOR KEY SHARE chặn xoá và đổi khoá nhưng không chặn cập nhật cột thường. Với dòng cha nóng trên MySQL, đây là nguồn chờ khoá và deadlock.
Bảng sổ cái có nên dùng đủ table template không?
Không. Sổ cái chỉ thêm: sai thì ghi bút toán đảo, không sửa.
Chỉ cần created_at, created_by; bỏ updated_* và version vì chúng ngầm cho phép sửa. Biến quy ước thành ràng buộc bằng trigger chặn UPDATE, DELETE và REVOKE quyền đó khỏi role ứng dụ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
access pattern
/ˈækses ˈpætərn/
cách dữ liệu được đọc, ghi
“I list the access patterns with their frequency before I draw a single table.”
entity
/ˈentəti/
đối tượng có danh tính và vòng đời
“An entity has an identity and a lifecycle, usually modeled as a status column.”
denormalize
/diːˈnɔːrməlaɪz/
phi chuẩn hoá
“We denormalized the author’s country onto posts so one index serves the feed.”
referential integrity
/ˌrefəˈrenʃl ɪnˈteɡrəti/
toàn vẹn tham chiếu
“Foreign keys give us referential integrity even when someone runs a manual script.”
orphan row
/ˈɔːrfn roʊ/
dòng mồ côi
“Without the constraint, the race left an orphan row.”