Bài 4: Phân trang hiệu quả – Bí quyết xử lý dữ liệu lớn không dùng OFFSET

Dưới đây là nội dung chi tiết cho Bài 4: Phân trang hiệu quả – Phân tích sâu cơ chế và triển khai Keyset Pagination:

Hiểu cơ chế đọc dữ liệu của database để tăng tốc phân trang gấp 100 lần

1. Vấn đề: Tại sao OFFSET chậm?

Giả sử query:

SELECT * FROM orders  
ORDER BY id  
LIMIT 10 OFFSET 100000;

➜ Database phải làm gì?

→ Chi phí:

EXPLAIN ANALYZE  
SELECT * FROM orders  
ORDER BY id  
LIMIT 10 OFFSET 100000;

Kết quả:

  Planning Time: 0.2 ms  
  Execution Time: 1200.5 ms  -- Rất chậm với OFFSET lớn!

Không đếm số hàng đã bỏ qua, thay vào đó:
- Dựa vào giá trị cột cuối cùng của trang trước (ví dụ: id).
- Dùng WHERE để lấy hàng sau giá trị đó.

Trang 1:

  SELECT * FROM orders  
  ORDER BY id  
  LIMIT 10;

→ Giá trị id cuối cùng: 100.

Trang 2:

  SELECT * FROM orders  
  WHERE id > 100  -- "Nhảy" thẳng đến vị trí này  
  ORDER BY id  
  LIMIT 10;

Database sử dụng index (nếu cột sắp xếp được index) để:
- Tìm vị trí bắt đầu (WHERE id > 100) trực tiếp thông qua B-tree.
- Quét tuần tự 10 hàng tiếp theo từ vị trí đó.
  → Độ phức tạp: O(log N + LIMIT), không phụ thuộc vào OFFSET.

EXPLAIN ANALYZE  
SELECT * FROM orders  
WHERE id > 100000  
ORDER BY id  
LIMIT 10;

Kết quả:

  Planning Time: 0.1 ms  
  Execution Time: 0.5 ms  -- Nhanh hơn 2400 lần!

Index trên cột id lưu trữ giá trị theo thứ tự, phân chia thành các node.
Khi query WHERE id > 100000:
1. Tìm kiếm binary search trên B-tree để xác định node chứa id = 100000.
2. Quét tuần tự các leaf node tiếp theo để lấy 10 giá trị.

B-tree Index Scan

Cột sắp xếp phải có index (ví dụ: PRIMARY KEY, UNIQUE INDEX).
Thứ tự sắp xếp phải cố định (ASC/DESC) giữa các trang.
Dữ liệu không thay đổi giữa các lần pagination (hoặc xử lý được việc thay đổi).

Tham số API:

  GET /posts?limit=10&cursor=2023-10-05T12:00:00Z

Query SQL:

  SELECT * FROM posts  
  WHERE created_at < '2023-10-05T12:00:00Z'  -- Lấy bài viết cũ hơn cursor  
  ORDER BY created_at DESC  
  LIMIT 10;

Vấn đề: Bài viết mới được thêm giữa các lần pagination → Thứ tự thay đổi.
Giải pháp:
- Dùng cột immutable làm cursor (e.g., id thay vì created_at).
- Hoặc thêm id vào điều kiện để đảm bảo thứ tự nhất quán:
```
  WHERE (created_at, id) < ('2023-10-05T12:00:00Z', 100)  
  ORDER BY created_at DESC, id DESC;
```

Tiêu chí	OFFSET	Keyset
Hiệu năng	Chậm khi OFFSET lớn	Nhanh, không phụ thuộc vào vị trí
Tài nguyên	Tốn CPU, I/O, Memory	Tiết kiệm
Nhất quán dữ liệu	Dễ bị missing/duplicate khi data thay đổi	Nhất quán nếu dùng cursor cố định
Ứng dụng	Phân trang đơn giản, dữ liệu nhỏ	Dữ liệu lớn, real-time

Triển khai Keyset Pagination cho bảng products:

Yêu cầu:
- Sắp xếp theo price DESC, id ASC.
- Lấy trang tiếp theo dựa trên price và id của sản phẩm cuối cùng.
Giả sử sản phẩm cuối cùng của trang hiện tại có price = 50, id = 200.

Gợi ý đáp án:

SELECT * FROM products  
WHERE (price, id) < (50, 200)  -- So sánh composite  
ORDER BY price DESC, id ASC  
LIMIT 10;

→ Giải thích:

Điều kiện (price, id) < (50, 200) đảm bảo lấy các sản phẩm có giá nhỏ hơn 50 hoặc bằng 50 nhưng id lớn hơn 200.
Index cần thiết: Composite index trên (price DESC, id ASC).

OFFSET phù hợp cho dữ liệu nhỏ, không yêu cầu tốc độ cao.
Keyset Pagination là lựa chọn tối ưu cho:
- Ứng dụng real-time.
- Dataset lớn (hàng triệu đến tỷ bản ghi).
- API cần trải nghiệm người dùng mượt mà.
Luôn đảm bảo:
- Index phù hợp cho cột cursor.
- Xử lý nhất quán thứ tự và thay đổi dữ liệu.

Hiểu sâu cơ chế index và cách database quét dữ liệu giúp bạn thiết kế phân trang "đẳng cấp"!

Preview bài tiếp theo:
Bài 5: Phân tích Execution Plan – Bí kíp đọc hiểu và tối ưu hóa SQL từ gốc.