MySQL phân trang sâu: Khi nào đổi OFFSET sang keyset/cursor pagination?

Chào bạn Đức Lâm, OFFSET phù hợp cho danh sách nhỏ hoặc khi người dùng cần nhảy đến số trang cụ thể. Nhưng với offset rất lớn, MySQL thường vẫn phải đi qua/bỏ qua nhiều dòng trước khi trả về phần cần dùng. Với luồng đọc tuần tự như activity feed, keyset pagination thường ổn định và rẻ hơn. Thiết kế cursor cần: 1. Xác định thứ tự toàn phần, ví dụ created_at DESC, id DESC. Cột phụ id làm tie-breaker khi nhiều record cùng timestamp. 2. Cursor chứa giá trị của phần tử cuối đã thấy, không phải chỉ số trang. Truy vấn tiếp phải dùng predicate phù hợp với thứ tự, sau đó LIMIT page_size + 1 để biết còn trang hay không. 3. Ký hoặc mã hóa cursor ở phía server nếu nó chứa trạng thái không muốn client sửa; luôn validate cấu trúc, tenant scope và page size. 4. Chấp nhận/định nghĩa rõ behavior khi bản ghi mới xuất hiện hay bản ghi cũ bị xóa. Cursor giúp tránh việc item bị trượt do OFFSET, nhưng không tự tạo snapshot toàn cục qua nhiều request. 5. Có index khớp filter/sort và benchmark bằng dữ liệu lớn thật. Đừng thêm cursor pagination chỉ vì một trang có OFFSET. Hãy nhìn workload: trải nghiệm UX cần nhảy trang, yêu cầu export, thứ tự dữ liệu và chỉ số latency p95/p99 để quyết định.

Tôi hiểu vấn đề của bạn – offset sẽ rất nhanh ở những trang đầu tiên nhưng sẽ chậm dần khi bạn cần truy vấn vào các trang sâu hơn. Chuyển sang pagination dạng cursor (keyset) có thể khôi phục lại hiệu suất, miễn là bạn có một cột sắp xếp ổn định không thay đổi sau khi chèn dữ liệu. Nếu bạn có một khóa chính tự động tăng hoặc một dấu thời gian được chèn cùng với hàng (và không bao giờ được cập nhật), bạn có thể sử dụng cột đó làm cursor ẩn. Điều này giúp bạn tránh phải phụ thuộc vào thứ tự ngẫu nhiên khi dữ liệu mới xuất hiện. Hãy cân nhắc việc kết hợp hai cột – ví dụ: `id` làm bộ sắp xếp chính và `created_at` làm bộ sắp xếp phụ – để đảm bảo kết quả chính xác ngay cả khi các hàng có cùng giá trị `id`. Bạn có thể chia sẻ loại hoạt động hệ thống bạn đang ghi lại và liệu bạn có luôn chèn các hàng theo thời gian tăng dần hay không? Điều này sẽ giúp chúng tôi xác định một lược đồ cursor phù hợp với bạn.

Nếu bạn có một bảng nhật ký phát triển với hàng chục triệu hàng, `OFFSET` sẽ trở nên đắt đỏ vì cơ sở dữ liệu vẫn phải quét qua các hàng bị bỏ qua. Chuyển sang phân trang cursor (keyset) giúp bạn chỉ yêu cầu các hàng sau một khóa xác định, giúp truy vấn sử dụng hiệu quả chỉ mục và duy trì tốc độ ổn định khi phân trang sâu. Để sắp xếp ổn định, bạn cần một cột (hoặc sự kết hợp các cột) mà: 1. Luôn được chỉ định giá trị theo cách xác định (ví dụ: khóa chính tự động hoặc timestamp được tạo bởi trình tạo ID có thứ tự); 2. Được bao phủ bởi một chỉ mục giúp truy vấn có thể thực hiện hoàn toàn trong phạm vi chỉ mục; 3. Không bị thay đổi bởi các thao tác UPDATE/DELETE mà bạn muốn giữ lại trong kết quả. Nếu bạn cho phép chèn dữ liệu đồng thời, hãy đảm bảo cột sắp xếp của bạn không thể giảm (ví dụ: `id` tăng tự động) hoặc bạn sử dụng một thứ tự ưu tiên cao hơn (như `ROW_NUMBER()` được tính toán dựa trên một cột timestamp ổn định). Bạn đang sử dụng cột nào làm điểm bắt đầu cho cursor, và cột đó có đảm bảo thứ tự tăng dần không?

Khi phân trang qua hàng chục triệu bản ghi, `OFFSET` sẽ trở nên chậm vì cơ sở dữ liệu phải quét qua các hàng bị bỏ qua. Chuyển sang phân trang cursor (keyset) giúp bạn có thể yêu cầu các hàng xuất hiện sau một khóa xác định, điều này giúp truy vấn sử dụng hiệu quả chỉ mục và giữ cho hiệu suất ổn định ngay cả khi phân trang sâu. Để sắp xếp ổn định, bạn cần một cột (hoặc nhiều cột) mà: (1) luôn tăng hoặc giảm theo một cách xác định (ví dụ: khóa chính tự động hoặc timestamp có trình tạo có thứ tự), (2) được bao phủ bởi một chỉ mục giúp truy vấn có thể thực hiện hoàn toàn trong phạm vi chỉ mục, và (3) không bị thay đổi bởi các thao tác UPDATE/DELETE mà bạn muốn giữ lại. Nếu bạn cho phép chèn dữ liệu đồng thời, hãy đảm bảo cột sắp xếp không thể giảm (ví dụ: `id` tăng tự động) hoặc sử dụng một thứ tự ưu tiên cao hơn (như `ROW_NUMBER()` được tính toán dựa trên một cột timestamp ổn định). Bạn đang sử dụng cột nào làm điểm bắt đầu cho cursor, và liệu cột đó có đảm bảo thứ tự tăng dần không? Bạn có gặp phải trường hợp thứ tự bị đảo ngược do các thao tác cập nhật hoặc xóa không?

Chuyển sang keyset pagination đúng là giải pháp chuẩn cho phân trang sâu, OFFSET/LIMIT sẽ kill performance khi offset lớn do phải scan và discard hàng. Thực tế hay gặp nhất là sắp xếp theo `created_at` (không unique) kết hợp `id` (PK) để đảm bảo thứ tự xác định: `WHERE (created_at, id) > (?, ?) ORDER BY created_at, id LIMIT N`. Cần composite index `(created_at, id)` để query cover index, tránh back-table lookup. Vấn đề thực tế thường gặp khi kết hợp filter (search, status...): điều kiện WHERE phức tạp làm index không dùng được cho ordering, buộc phải filesort. Bạn có đang xử lý trường hợp filter động kèm keyset pagination không? Cách nào bạn dùng để giữ index hiệu quả khi filter thay đổi?

Thực tế thì hầu hết mọi người dùng khóa chính tự động tăng (`id` AUTO_INCREMENT) làm cursor vì nó thỏa mãn ba điều kiện: tăng đơn điệu, có chỉ mục chủ khóa và không bị thay đổi sau khi insert. Nếu bảng có cột `created_at` kèm `id` để đảm bảo thứ tự ổn định khi có insert đồng thời, hãy tạo chỉ mục tổ hợp `(created_at, id)` và dùng cặp giá trị đó làm cursor. Việc UPDATE/DELETE không làm đảo ngược thứ tự miễn là bạn không sửa `id` hay `created_at` của các bản ghi đã có. Nếu cần phân trang theo thứ tự giảm dần, chỉ cần đảo điều kiện `WHERE (created_at, id) < (?, ?)` và `ORDER BY created_at DESC, id DESC`. Bạn đang dùng chỉ mục đơn lẻ hay tổ hợp cho cột cursor?