Thiết kế database quyết định dữ liệu có thể giữ đúng khi ứng dụng và đội ngũ cùng phát triển hay không. Bài viết mô hình hóa hệ thống đặt hàng với khóa chính, khóa ngoại, constraint và index, sau đó dùng EXPLAIN ANALYZE để kiểm tra một truy vấn thực tế.

Mục lục

1. Bài toán thực tế
2. Sản phẩm hoàn thành
3. Kiến thức cần dùng
4. Chuẩn bị môi trường
5. Dữ liệu thực hành
6. Xây dựng từng bước
7. Chương trình hoàn chỉnh
8. Kiểm tra kết quả
9. Lỗi thường gặp
10. Đưa vào thực tế
11. Bài tập thực hành
12. Project mở rộng
13. Tổng kết
14. Tài liệu tham khảo

1. Bài toán thực tế

Một ứng dụng bán hàng ban đầu lưu customer_name và product_name trực tiếp trong orders. Khi khách đổi tên hoặc sản phẩm đổi giá, dữ liệu cũ bị sửa theo hoặc xuất hiện nhiều phiên bản viết khác nhau.

Nhóm cũng tạo index cho gần như mọi cột với kỳ vọng truy vấn sẽ nhanh. Kết quả là ghi chậm, dung lượng tăng nhưng nhiều index không bao giờ được dùng.

2. Sản phẩm hoàn thành

Sản phẩm là schema customers, products, orders và order_items ở dạng chuẩn hóa hợp lý, kèm constraint bảo vệ quy tắc và ba index phục vụ truy vấn chính.

Người học dùng EXPLAIN ANALYZE so sánh plan trước và sau index thay vì tối ưu theo cảm tính.

• Chọn primary key và business key.
• Biểu diễn quan hệ bằng foreign key.
• Dùng UNIQUE, NOT NULL và CHECK.
• Hiểu thứ tự cột trong composite index.
• Cân bằng hiệu năng đọc, ghi và dung lượng.

3. Kiến thức cần dùng

Primary key là định danh nội bộ duy nhất và không NULL. Business key như email hoặc sku có ý nghĩa nghiệp vụ và thường cần UNIQUE, nhưng có thể thay đổi nên không phải lúc nào phù hợp làm khóa tham chiếu.

Foreign key bảo đảm bản ghi con tham chiếu bản ghi cha tồn tại. ON DELETE cần lựa chọn có chủ đích: RESTRICT bảo vệ lịch sử, CASCADE phù hợp cho dữ liệu phụ thực sự không có ý nghĩa độc lập.

B-tree index phù hợp so sánh bằng, khoảng và ORDER BY. Composite index hữu ích theo tiền tố bên trái; index (customer_id, order_date) hỗ trợ lọc customer rồi khoảng ngày tốt hơn chỉ lọc order_date trong nhiều trường hợp.

4. Chuẩn bị môi trường

Ghi ba truy vấn quan trọng trước khi tạo index: lịch sử đơn của khách, tìm đơn theo trạng thái và ngày, tra sản phẩm theo sku. Index phải có workload mục tiêu.

Nạp đủ dữ liệu để planner có lựa chọn. Trên bảng vài chục dòng, sequential scan thường nhanh hơn và không chứng minh index vô ích.

ANALYZE customers; ANALYZE orders; ANALYZE order_items;

EXPLAIN (ANALYZE,BUFFERS)
SELECT * FROM orders
WHERE customer_id=1001
ORDER BY order_date DESC LIMIT 20;

5. Dữ liệu thực hành

customers và products là entity độc lập. orders giữ thông tin cấp đơn và order_items giữ sản phẩm trong đơn. Giá tại thời điểm bán nằm ở order_items, không đọc lại current_price của products cho lịch sử.

Một order có nhiều item; một product xuất hiện trong nhiều order. order_items giải quan hệ nhiều-nhiều và có khóa tổ hợp.

CREATE TABLE customers (
 customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 email text NOT NULL UNIQUE,customer_name text NOT NULL,created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE products (
 product_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 sku text NOT NULL UNIQUE,product_name text NOT NULL,current_price numeric(12,2) NOT NULL CHECK(current_price>=0)
);
CREATE TABLE orders (
 order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 customer_id bigint NOT NULL REFERENCES customers(customer_id),
 order_date timestamptz NOT NULL,status text NOT NULL CHECK(status IN ('pending','completed','cancelled'))
);
CREATE TABLE order_items (
 order_id bigint REFERENCES orders(order_id),product_id bigint REFERENCES products(product_id),
 quantity integer NOT NULL CHECK(quantity>0),unit_price numeric(12,2) NOT NULL CHECK(unit_price>=0),
 PRIMARY KEY(order_id,product_id)
);

6. Xây dựng từng bước

Bước 1: Chọn khóa không phụ thuộc tên

Identity key ngắn và ổn định. Email, sku vẫn được UNIQUE để bảo vệ business key.

INSERT INTO customers(email,customer_name) VALUES('an@example.com','Nguyễn An') RETURNING customer_id;

Kết quả mong đợi: Database sinh customer_id; email không được trùng.

Bước 2: Kiểm tra constraint

Thử dữ liệu xấu trong transaction rồi rollback để quan sát database bảo vệ quy tắc.

BEGIN;
INSERT INTO products(sku,product_name,current_price) VALUES('ERR','Sản phẩm lỗi',-1);
ROLLBACK;

Kết quả mong đợi: INSERT thất bại vì CHECK current_price.

Bước 3: Index lịch sử khách hàng

Truy vấn lọc customer và sắp ngày giảm dần; index cùng thứ tự có thể tránh sort.

CREATE INDEX idx_orders_customer_date
ON orders(customer_id,order_date DESC);

Kết quả mong đợi: Planner có lựa chọn index scan cho lịch sử một khách.

Bước 4: Partial index cho hàng đợi

Nếu phần lớn đơn completed nhưng vận hành thường đọc pending, partial index nhỏ và tập trung hơn.

CREATE INDEX idx_orders_pending_date
ON orders(order_date) WHERE status='pending';

Kết quả mong đợi: Truy vấn WHERE status='pending' có index chuyên biệt.

Bước 5: Đọc plan

Actual rows, loops, buffers và sort method quan trọng hơn chỉ nhìn cost. So sánh cùng truy vấn và dữ liệu.

EXPLAIN (ANALYZE,BUFFERS)
SELECT order_id,order_date,status
FROM orders
WHERE customer_id=1001
ORDER BY order_date DESC LIMIT 20;

Kết quả mong đợi: Plan thể hiện index scan khi có lợi.

Chuẩn hóa đến mức nào

Tách entity giảm lặp và anomaly, nhưng dashboard không nên JOIN hàng chục bảng giao dịch cho mọi biểu đồ. OLTP ưu tiên tính toàn vẹn và thao tác ghi; warehouse có thể denormalize theo nhu cầu đọc.

Thiết kế trong bài dành cho hệ thống giao dịch nhỏ. Khi xây analytics, tạo fact và dimension riêng thay vì dùng database ứng dụng như warehouse vĩnh viễn.

Index phục vụ truy vấn, không phục vụ sơ đồ

Hai bảng giống nhau có thể cần index khác vì workload khác. Một hệ thống tra cứu đơn theo customer khác hệ thống batch quét theo date. Thu thập query statistics trước khi thêm index hàng loạt.

Index covering với INCLUDE có thể giảm đọc heap cho truy vấn cụ thể, nhưng làm index lớn. Dùng khi số đo chứng minh lợi ích, không biến mọi index thành bản sao bảng.

Constraint và dữ liệu lịch sử

CHECK status giới hạn miền giá trị nhưng thay đổi enum nghiệp vụ cần migration. Bảng tham chiếu linh hoạt hơn khi trạng thái có metadata, trong khi CHECK đơn giản và nhanh cho miền ổn định.

Foreign key có chi phí ghi nhưng đổi lại ngăn orphan ngay tại nguồn. Nếu bỏ constraint vì tốc độ, hệ thống phải có cơ chế khác tương đương và giám sát liên tục.

Functional dependency và lựa chọn chuẩn hóa

Nếu sku xác định duy nhất product_name hiện tại, lưu tên lặp trong order_items tạo update anomaly. Nhưng unit_price tại thời điểm bán phải nằm ở item vì current_price thay đổi. Phân biệt thuộc tính hiện tại và snapshot lịch sử giúp tránh chuẩn hóa máy móc.

Một bảng chỉ nên chứa thuộc tính phụ thuộc vào khóa, toàn bộ khóa và không phụ thuộc bắc cầu trong mô hình chuẩn hóa cơ bản. Đây là hướng dẫn phân tích, không phải mục tiêu tuyệt đối cho mọi workload.

SELECT oi.order_id,oi.product_id,p.product_name,oi.unit_price,p.current_price
FROM order_items oi JOIN products p USING(product_id)
WHERE oi.unit_price<>p.current_price;

Unique constraint có điều kiện

Email có thể nullable nhưng khi có giá trị phải duy nhất. PostgreSQL UNIQUE cho phép nhiều NULL, phù hợp nhiều trường hợp. Nếu chỉ tài khoản active cần email unique, partial unique index biểu diễn rule rõ.

Cần xem vòng đời: tài khoản inactive kích hoạt lại có xung đột không? Constraint phải theo quy tắc thực, không chỉ theo màn hình hiện tại.

CREATE UNIQUE INDEX uq_active_customer_email
ON customers(LOWER(email))
WHERE status='active' AND email IS NOT NULL;

Covering index và index-only scan

INCLUDE thêm cột trả về nhưng không tham gia sắp xếp khóa. Truy vấn lịch sử đơn có thể đọc order_id, status, total_amount từ index nếu visibility map cho phép.

Index-only scan không được bảo đảm chỉ vì có INCLUDE; vacuum và pattern cập nhật ảnh hưởng. Đo buffers và kích thước index trước khi chấp nhận.

CREATE INDEX idx_orders_customer_date_cover
ON orders(customer_id,order_date DESC)
INCLUDE(order_id,status,total_amount);

EXPLAIN (ANALYZE,BUFFERS) SELECT order_id,order_date,status,total_amount
FROM orders WHERE customer_id=1001 ORDER BY order_date DESC LIMIT 20;

Xóa mềm và tính duy nhất

Soft delete thêm deleted_at nhưng bản ghi vẫn tồn tại, vì vậy UNIQUE(email) chặn tái sử dụng email. Partial unique index WHERE deleted_at IS NULL có thể phù hợp, nhưng lịch sử và phục hồi cần quy tắc xung đột.

Soft delete làm mọi truy vấn phải nhớ deleted_at IS NULL. View active_customers hoặc Row Level Security có thể giảm bỏ sót, nhưng tăng lớp trừu tượng cần tài liệu.

CREATE UNIQUE INDEX uq_customers_email_not_deleted
ON customers(LOWER(email)) WHERE deleted_at IS NULL;

Migration không downtime

Thêm cột NOT NULL vào bảng lớn cần kế hoạch. Thường thêm nullable, backfill theo batch, thêm CHECK NOT VALID, validate rồi đặt NOT NULL tùy phiên bản và quy trình. Thay đổi schema production khác nhiều CREATE TABLE trong lab.

Migration phải có hướng rollback hoặc roll-forward, quan sát lock và thời gian. Chạy thử trên dữ liệu gần kích thước thật trước cửa sổ triển khai.

ALTER TABLE orders ADD COLUMN sales_channel text;
UPDATE orders SET sales_channel='store' WHERE sales_channel IS NULL;
ALTER TABLE orders ADD CONSTRAINT orders_sales_channel_nn CHECK(sales_channel IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_sales_channel_nn;

7. Chương trình hoàn chỉnh

Schema hoàn chỉnh lưu snapshot unit_price ở item để giá lịch sử không đổi theo products. Tổng đơn có thể tính từ item hoặc lưu kèm cơ chế kiểm soát.

Migration tạo bảng trước, constraint và index sau theo thứ tự rõ. Mọi thay đổi schema được review như code.

SELECT o.order_id,o.order_date,o.status,
       SUM(oi.quantity*oi.unit_price) order_amount
FROM orders o JOIN order_items oi ON oi.order_id=o.order_id
WHERE o.customer_id=:customer_id
  AND o.order_date>=:from_time AND o.order_date<:to_time
GROUP BY o.order_id,o.order_date,o.status
ORDER BY o.order_date DESC,o.order_id DESC
LIMIT 20;

8. Kiểm tra kết quả

Dùng truy vấn catalog hoặc pg_stat_user_indexes để xem index được dùng sau một thời gian đại diện. Index chưa được dùng có thể vẫn phục vụ constraint hoặc job hiếm quan trọng, nên không xóa chỉ dựa trên một ngày.

Kiểm tra orphan bằng foreign key và thử thao tác xóa customer có order. Chính sách RESTRICT mặc định phải phù hợp yêu cầu giữ lịch sử.

SELECT indexrelname,idx_scan,idx_tup_read
FROM pg_stat_user_indexes
WHERE relname IN ('orders','order_items');

SELECT conname,contype FROM pg_constraint
WHERE conrelid='orders'::regclass;

9. Lỗi thường gặp

• Dùng tên làm khóa: Tên thay đổi và không duy nhất.
• Index mọi cột: Làm ghi chậm và tăng dung lượng.
• Sai thứ tự composite index: Không khớp bộ lọc phổ biến.
• Cascade delete lịch sử: Có thể xóa giao dịch ngoài ý muốn.
• Đọc plan trên dữ liệu quá nhỏ: Kết luận không đại diện production.

10. Đưa vào thực tế

Thay đổi index lớn có thể khóa hoặc tiêu tốn I/O; PostgreSQL hỗ trợ CREATE INDEX CONCURRENTLY với trade-off thời gian và quy trình transaction riêng.

Theo dõi bloat, vacuum và thống kê. Planner cần statistics đủ mới; một schema tốt vẫn có thể chạy chậm khi maintenance bị bỏ quên.

11. Bài tập thực hành

1. Thêm addresses mà không lặp cột trong customers.
2. Thiết kế product price history.
3. So sánh index (status,order_date) và (order_date,status).
4. Tạo partial index cho completed 30 ngày gần nhất và thảo luận hạn chế predicate động.
5. Tìm index trùng lặp.

12. Project mở rộng

Mở rộng schema cho multi-tenant bằng tenant_id trong khóa và index. Kiểm tra mọi unique constraint có cần phạm vi tenant hay toàn hệ thống.

13. Tổng kết

Database design tốt đưa quy tắc vào nơi dữ liệu được ghi, không chờ báo cáo phát hiện lỗi. Index tốt bắt đầu từ workload, không từ danh sách cột.

Bài cuối kết hợp schema, làm sạch, tổng hợp, kiểm thử và transaction thành project SQL hoàn chỉnh.

14. Tài liệu tham khảo

• Constraints
• Indexes
• EXPLAIN

TechData.AI - Leading the Future.
Tham khảo các khoá học theo link: https://techdata.ai/techdata-ai-course/
Hoàng Minh.