Lời Nguyền Phân Trang: Khi OFFSET Bóp Nghẹt Database 10 Triệu Bản Ghi

Một buổi tối cuối tuần, hệ thống nội bộ của công ty bỗng dưng giật lag tung chảo. Check Grafana, mình thấy CPU của con MySQL nhảy vọt lên 100%. Lần theo slow query log, thủ phạm hiện nguyên hình là một câu SQL ngắn củn:

SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 500000;

Hỏi ra mới biết, một chị kế toán đang rảnh rỗi nên ngồi bấm "Next Page" liên tục trên trang quản lý đơn hàng để lùi về xem dữ liệu của... 2 năm trước. Chị ấy đang ở trang thứ 25.000.

Trong đầu anh em dev chúng ta từ xưa đến nay, công thức phân trang (Pagination) mặc định luôn là:

• Công thức: OFFSET = (Page_Number - 1) * Limit
• Ví dụ: Trang 3, mỗi trang 20 items -> LIMIT 20 OFFSET 40.

Nó chạy cực kỳ mượt mà ở 100 trang đầu tiên. Nhưng với tư duy của một Vibe Coder, bạn phải hiểu được bản chất vật lý bên dưới câu lệnh đó để thấy nó tàn phá Database khủng khiếp đến mức nào.

1. Sự thật tàn khốc về LIMIT ... OFFSET

Khi bạn ra lệnh: LIMIT 20 OFFSET 500000.

Bạn nghĩ Database sẽ thông minh nhảy một phát đến dòng thứ 500.000 và lấy 20 dòng tiếp theo?

KHÔNG! Database không hề biết bay! Bản chất của các hệ quản trị CSDL (như MySQL, Postgres) khi gặp lệnh OFFSET là nó phải:

1. Đọc từ dòng đầu tiên.
2. Quét qua đủ 500.020 dòng dữ liệu (hoặc index).
3. Sau đó, nó VỨT BỎ 500.000 dòng đầu tiên.
4. Và trả về cho bạn 20 dòng cuối cùng.

Càng bấm sang các trang về sau, độ sâu của OFFSET càng lớn, Database càng phải đọc và vứt đi một lượng rác khổng lồ. Việc này tiêu tốn CPU, chọc thủng Cache và làm kẹt ổ cứng (Disk I/O). Đây gọi là độ phức tạp O(N).

2. Sự Cứu Rỗi: Keyset Pagination (Phân Trang Bằng Con Trỏ)

Để gỡ bỏ lời nguyền này, các ông lớn như Facebook, Twitter hay Google từ lâu đã loại bỏ kiểu phân trang bằng số trang (Page 1, 2, 3...) ở các danh sách dài. Họ chuyển sang Infinite Scroll (Cuộn vô tận) hoặc nút "Tải thêm" (Load More).

Bên dưới lớp vỏ UI đó chính là kỹ thuật Keyset Pagination (hay Cursor-based Pagination).

Thay vì nói với Database: "Hãy bỏ qua 500.000 dòng".

Chúng ta nói: "Hãy lấy cho tao 20 dòng, bắt đầu TỪ CÁI ĐƠN HÀNG CÓ ID NÀY trở về trước".

Câu lệnh SQL chuẩn Vibe Coder:

SELECT * FROM orders 
WHERE id < 8921503 -- Đây chính là "Cursor" (ID của item cuối cùng ở trang trước)
ORDER BY id DESC 
LIMIT 20;

3. Phép Màu Của B-Tree Index

Tại sao câu query trên lại nhanh như điện xẹt, dù cho Database có 100 triệu dòng?

Vì lúc này, mệnh đề WHERE id < 8921503 đã tận dụng tối đa sức mạnh của B-Tree Index (như mình đã giải thích ở bài Exact Match).

1. Database không cần đọc từ đầu nữa.
2. Nó dùng Index để nhảy dù đánh bụp một phát ngay đúng cái Node có ID 8921503 (độ phức tạp O(log N) - mất chưa tới 1 mili-giây).
3. Từ vị trí đó, nó nhặt đúng 20 dòng tiếp theo và trả về ngay lập tức. KHÔNG CÓ MỘT DÒNG DỮ LIỆU NÀO BỊ ĐỌC THỪA VÀ VỨT ĐI!

4. Đánh Đổi (Trade-offs) Của Keyset Pagination

Là một Engineer, không có giải pháp nào là viên đạn bạc (Silver bullet). Keyset Pagination tuy mang lại tốc độ bàn thờ, nhưng bạn phải chấp nhận đánh đổi 2 thứ ở mặt UI/UX:

• Không thể nhảy cóc trang (Jump to Page): Bạn không thể cho người dùng gõ số "Trang 50" rồi Enter được nữa. Bắt buộc phải đi tuần tự từng trang bằng nút Next / Prev (vì phải có ID của trang trước làm điểm tựa).
• Cần một cột Sort tuần tự (Sequential Sort Column): Bạn cần một cột không bao giờ trùng lặp và có tính tuần tự để làm Cursor. Thường là cột id (Auto Increment / Snowflake ID) hoặc created_at (nếu đảm bảo không có 2 record trùng từng mili-giây).

Lời kết

Nếu dự án của bạn chỉ có vài ngàn bản ghi (như danh mục sản phẩm, danh sách nhân viên), cứ xài LIMIT OFFSET cho nhàn, để Frontend làm UI phân trang 1, 2, 3 cho đẹp.

Nhưng nếu bạn đang làm việc với Bảng Log, Bảng Giao Dịch, Bảng Tin nhắn (Chat), hay News Feed... nơi dữ liệu sinh ra hàng triệu dòng mỗi ngày, thì Keyset Pagination là con đường bắt buộc. Hãy bảo vệ Database của bạn trước khi nó bị những cú click "Next Page" vô tội vạ bóp nghẹt nhé!

Chủ đề tiếp theo: Race Condition - Ma Thuật Đen Khi 2 Người Cùng Mua 1 Món Đồ Cuối Cùng

Trong bài này, Database của bạn đã an toàn trước rủi ro bị quá tải do đọc (Read). Nhưng ở mặt ghi (Write) thì sao?

Giả sử kho của bạn chỉ còn đúng 1 chiếc iPhone 15 Pro Max bản giới hạn. Vào lúc 12:00:00, cả 2 khách hàng A và B cùng bấm "Thanh toán" vào đúng 1 mili-giây. Cả hai luồng code (Thread) cùng chạy lệnh: SELECT stock FROM products. Cả hai đều thấy kho còn 1 chiếc. Cả hai cùng vượt qua vòng if (stock > 0), và cùng trừ kho. Kết quả: Kho bị âm (-1), hệ thống nổ tung, bộ phận CSKH lại bị ăn chửi vì phải đi xin lỗi khách.

Đây là lỗ hổng kinh điển mang tên Race Condition (Tình trạng tương tranh). Ở bài viết tới, mình sẽ đập tan con quái vật này bằng nghệ thuật sử dụng Database Locks (Pessimistic Lock & Optimistic Lock). Cùng đón đọc cách làm sao để ép các luồng code phải xếp hàng ngay ngắn nhé!