Phân trang SQL bằng con trỏ: vì sao OFFSET chậm dần và cách chuyển sang cursor pagination
API danh sách đơn hàng chạy ổn suốt mấy tháng. Trang 1 nhanh, trang 2 nhanh, chẳng ai phàn nàn gì. Cho tới khi có một job export gọi API đó lần lượt từng trang để kéo hết data về. Mấy trang đầu vèo vèo, càng về sau càng ì, tới mấy nghìn trang thì mỗi request mất cả chục giây, và DB của prod bắt đầu thở dốc.
Query không đổi gì, chỉ có con số OFFSET to dần lên. Bài này kể vì sao OFFSET lại chậm theo kiểu đó, và cách mình chuyển sang phân trang bằng con trỏ, hay cursor pagination (có chỗ gọi là keyset pagination, cùng một ý).
OFFSET thật ra làm gì?
Query kiểu quen thuộc:
SELECT id, total_amount, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;
Đọc thì tưởng database "nhảy" thẳng tới dòng thứ 100.001. Thực tế nó không nhảy được. Nó phải đi lần lượt qua 100.000 dòng đầu theo đúng thứ tự ORDER BY, vứt hết đi, rồi mới lấy 20 dòng. Có index trên created_at thì đỡ được khâu sort, nhưng vẫn phải đi qua chừng ấy entry trong index.
Nên chi phí tăng theo số trang. Trang 1 đọc 20 dòng, trang 5000 đọc 100.020 dòng để trả về đúng 20. Job export kéo hết từng trang thì tổng số dòng phải đọc tăng theo bình phương, chậm dần là phải.
Muốn tự thấy thì chạy EXPLAIN (ANALYZE, BUFFERS) cho trang 1 và một trang sâu, rồi so số dòng đọc với số buffer. Cách đọc plan mình viết ở bài về execution plan trong series.
Chậm chưa phải cái tệ nhất
Cái làm mình khó chịu hơn là OFFSET có thể trả về trùng hoặc sót dòng.
Người dùng đang xem trang 1, trong lúc đó có 3 đơn mới được tạo. Họ bấm sang trang 2. Mọi dòng cũ đã bị đẩy xuống 3 vị trí, nên 3 dòng cuối của trang 1 hiện lại ở đầu trang 2. Ngược lại, có đơn bị xoá thì sẽ có dòng bị nhảy qua mà không ai thấy.
Còn một lỗi nữa nằm ngay trong ORDER BY. Sort mỗi theo created_at mà có nhiều đơn trùng thời điểm thì thứ tự giữa chúng không xác định, hai lần chạy có thể ra hai thứ tự khác nhau. Cắt trang ngay giữa một nhóm trùng như vậy là có dòng xuất hiện ở cả hai trang, có dòng không ở trang nào. Chuyện này giống hệt vụ ROW_NUMBER() ra hai dòng cho một khách ở bài về JOIN, CTE và window function, và cách chữa cũng vậy: luôn thêm một cột unique, thường là id, vào cuối ORDER BY.
Con trỏ: nhớ chỗ mình dừng thay vì đếm
Ý tưởng đơn giản: thay vì nói "bỏ qua 100.000 dòng", mình nói "lấy 20 dòng đứng sau dòng cuối cùng mình vừa thấy". Giá trị của dòng cuối đó chính là con trỏ (cursor): server trả nó kèm mỗi trang, client gửi lại khi xin trang tiếp.
-- Trang đầu: không có điều kiện con trỏ
SELECT id, total_amount, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Các trang sau: $1, $2 là created_at và id của dòng cuối trang trước
SELECT id, total_amount, created_at
FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Kèm một index đúng thứ tự:
CREATE INDEX idx_orders_created_id
ON orders (created_at DESC, id DESC);
Có index này thì database nhảy thẳng tới vị trí của con trỏ trong index rồi đọc tiếp 20 entry. Trang 5000 tốn công gần như trang 2, vì nó không còn phải đi qua mấy trang trước nữa.
Mấy chỗ mình phải để ý khi làm thật:
- Con trỏ phải gồm cả
id. Chỉ dùngcreated_atthì quay lại đúng vấn đề trùng thời điểm ở trên:created_at < $1sẽ bỏ sót những đơn còn lại có cùng thời điểm với dòng cuối trang. - Lọc thêm điều kiện thì cột lọc đứng đầu index. Ví dụ danh sách đơn của một khách thì index là
(customer_id, created_at DESC, id DESC), và query có thêmWHERE customer_id = $3. - Cú pháp so sánh tuple không đâu cũng có. PostgreSQL dùng
(created_at, id) < ($1, $2)ngon lành. MySQL cũng hiểu cú pháp này, nhưng nhớEXPLAINxem có range scan trên index không; không thì viết dạng tách ra. SQL Server thì không có cú pháp này, phải viết tách:
WHERE created_at < @created_at
OR (created_at = @created_at AND id < @id)
- Đừng đưa thẳng giá trị con trỏ ra URL. Mình gói
created_atvàidthành một chuỗi (base64 của JSON chẳng hạn) để trả cho client, nhận về thì decode và validate lại, kiểu dữ liệu sai hay thiếu field là trả lỗi luôn. Trang rỗng thì không trả con trỏ tiếp.
Đổi lại thì mất gì?
Keyset không miễn phí, và đây là chỗ mình thấy nhiều bài viết lướt qua.
Mất khả năng nhảy tới trang bất kỳ. Không có "đi tới trang 37" nữa, chỉ có trang tiếp và trang trước. Trang trước thì đảo chiều so sánh và chiều sort (> và ASC), lấy xong đảo ngược lại thứ tự ở code.
Mất luôn con số "tổng 4.832 trang". Muốn có thì vẫn phải COUNT(*), mà COUNT(*) trên bảng lớn có điều kiện lọc thì cũng chẳng rẻ. Phần lớn giao diện mình làm sau này chuyển sang nút "Xem thêm" hoặc cuộn vô hạn, và chẳng ai đòi lại số trang.
Keyset cũng không phải snapshot. Nó hết lỗi trùng dòng khi có đơn mới chèn lên đầu, nhưng một dòng bị sửa đúng cột dùng để sort thì vẫn có thể nhảy sang trang khác. Cần một ảnh chụp cố định của data, kiểu export đối soát, thì phải tính cách khác.
Vậy còn dùng OFFSET không?
Có, và mình không thấy ngại. Trang admin vài trăm dòng, người dùng cần nhảy trang và cần biết tổng số trang, thì LIMIT ... OFFSET vẫn là thứ đơn giản và đủ dùng. Chuyển sang keyset ở chỗ đó chỉ thêm code mà không được gì.
Mình chỉ đổi sang keyset khi gặp một trong mấy thứ này: bảng lớn và người dùng hay đi sâu, job hay API phải đi qua toàn bộ data, hoặc danh sách thay đổi liên tục như feed hay log.
Muốn thử thì tìm trong project một endpoint list đang dùng OFFSET, chạy EXPLAIN (ANALYZE, BUFFERS) cho trang 1 và một trang thật sâu, so hai plan. Nếu chênh nhiều mà endpoint đó lại hay bị gọi sâu, nhất là bởi job nội bộ, thì đó là chỗ đáng chuyển đầu tiên.
Bạn đang phân trang kiểu gì cho mấy bảng lớn? Có ai đã thử deferred join (lấy id bằng OFFSET trước rồi mới join lấy cột) thay vì chuyển hẳn sang keyset không, comment cho mình biết kết quả với.
P.S. Phần keyset này mình có đối chiếu lại với skill postgres-best-practices trong PrimeKit, bộ skill mình tự làm. Skill có một mục riêng về pagination, và nó cũng nhắc đúng mấy điểm mình hay quên: con trỏ phải là cặp timestamp với id, trang đầu bỏ điều kiện con trỏ, trang rỗng thì không trả con trỏ tiếp, và keyset không phải snapshot. Gõ /pk:postgres-best-practices trong Claude Code rồi dán query list đang dùng OFFSET vào, nhờ nó đề xuất bản keyset kèm index; nó sẽ ghi cả chi phí ghi và chuyện lock của index đó. Cài bằng npx @lovehlmmm/primekit init. Đề xuất index của nó thì bạn vẫn nên EXPLAIN lại trên data thật trước khi tạo.
All Rights Reserved