Phân trang: OFFSET đắt tới đâu

LIMIT 20 OFFSET 1000 là cách phân trang mà ai cũng viết đầu tiên. Nó đúng, nó ngắn, và nó chậm dần theo số trang — nhưng chậm bao nhiêu thì ít người đo. Phần này đo, và đo cả một lỗi nghiêm trọng hơn chuyện chậm.

Bảng đo

5.000.000 bài viết, 365 MB, có chỉ mục đúng thứ tự sắp xếp:

create index bv_luc on bv(luc desc, id desc);
select id, tieu_de from bv order by luc desc, id desc limit 20 offset :n;

OFFSET tăng tuyến tính

Cột cuối là điều đáng nhớ nhất: để trả về 20 dòng của trang cuối, PostgreSQL đọc đủ 5 triệu dòng rồi vứt đi 4.999.980 dòng.

OFFSET không phải là "nhảy tới vị trí N". Nó là "đọc N dòng rồi bỏ". Không có cấu trúc dữ liệu nào cho phép nhảy thẳng tới phần tử thứ N của một chỉ mục B-tree mà không đi qua N-1 phần tử trước đó.

Nhưng cũng đọc bảng này theo chiều ngược lại: vài trang đầu hoàn toàn miễn phí. Trang 6 (offset 100) mất 0,04 ms — không khác gì trang 1. Vấn đề chỉ thật sự bắt đầu từ khoảng trang 500 trở đi.

Với một blog hay một trang tin, phần lớn người đọc không bao giờ đi quá trang 5. Nếu đó là trường hợp của bạn, OFFSET là lựa chọn đúng và bài này chỉ cần đọc tới đây.

Phân trang theo khoá

Thay vì đếm số dòng phải bỏ, hãy nói cho cơ sở dữ liệu biết dòng cuối cùng bạn đã thấy:

select id, tieu_de from bv
where (luc, id) < (:luc_cuoi, :id_cuoi)
order by luc desc, id desc
limit 20;

Hằng số ở mọi độ sâu. Trang cuối cùng nhanh ngang trang đầu tiên — nhanh hơn OFFSET tương ứng 8.800 lần.

Ba điều bắt buộc để nó chạy đúng:

Chỉ mục phải khớp đúng thứ tự. create index on bv(luc desc, id desc) — cả hai cột, cả hai chiều. Sai một chi tiết là PostgreSQL phải sắp xếp lại và toàn bộ lợi thế biến mất (phần 12 đã đo cái giá của thứ tự cột sai: chậm 472 lần).

Khoá phải duy nhất. luc một mình không đủ vì hai bài có thể cùng thời điểm — khi đó bạn sẽ bỏ sót hoặc lặp dòng. Ghép thêm id làm khoá phụ để bảo đảm mỗi dòng có một vị trí duy nhất.

Dùng so sánh bộ giá trị, tức (luc, id) < (:a, :b), chứ không viết luc < :a or (luc = :a and id < :b). Hai cách cho cùng kết quả, nhưng chỉ dạng bộ giá trị mới được PostgreSQL chuyển thành một phép tìm kiếm duy nhất trên chỉ mục ghép.

Lỗi nghiêm trọng hơn chuyện chậm

OFFSET neo vào vị trí thứ N, mà vị trí thì đổi khi dữ liệu đổi. Hai kiểu hỏng, cả hai đều đo được.

Có dòng mới chèn lên đầu → dòng bị lặp:

trang 1 (offset 0):  Tin 20, Tin 19, Tin 18, Tin 17, Tin 16
   [một tin mới được đăng]
trang 2 (offset 5):  Tin 16, Tin 15, Tin 14, Tin 13, Tin 12

Tin 16 hiện ở cả hai trang. Người đọc thấy một bài trùng và tưởng có lỗi hiển thị.

Có dòng bị xoá ở đầu → dòng bị bỏ sót:

trang 1 (offset 0):  Tin 20, Tin 19, Tin 18, Tin 17, Tin 16
   [Tin 20 bị xoá]
trang 2 (offset 5):  Tin 14, Tin 13, Tin 12, Tin 11, Tin 10

Tin 15 biến mất hoàn toàn — nó không xuất hiện ở trang nào cả. Người đọc lật hết mọi trang vẫn không bao giờ thấy nó.

Kiểu hỏng thứ hai đáng sợ hơn vì nó im lặng. Không ai báo lỗi "tôi không thấy bài Tin 15", vì họ không biết Tin 15 tồn tại.

Phân trang theo khoá không gặp cả hai, vì nó neo vào một dòng cụ thể chứ không vào vị trí thứ N. Dữ liệu trước đó đổi bao nhiêu cũng không ảnh hưởng tới câu hỏi "cho tôi 20 dòng sau dòng này".

Trên bảng chỉ ghi thêm và ít xoá, rủi ro nhỏ. Trên bảng tin tức, hàng đợi công việc, hay bảng bị người dùng chỉnh sửa, nó xảy ra hàng ngày.

Đếm tổng số trang mới là phần đắt còn lại

Giao diện "trang 3 / 500" cần biết tổng số dòng, và đó là một truy vấn riêng:

Sau khi bạn tối ưu phân trang xuống 0,06 ms, câu count(*) 106 ms trở thành phần chậm nhất của cả trang. Đây là chỗ hay bị bỏ quên.

reltuples là số dòng ước lượng do ANALYZE cập nhật — chính xác tới vài phần trăm trên bảng được dọn đều, và lấy tức thì vì nó chỉ là một dòng trong catalog.

Với điều kiện lọc thì phải hỏi bộ lập lịch:

create or replace function uoc_dong(q text) returns bigint as $$
declare r jsonb;
begin
  execute 'explain (format json) ' || q into r;
  return (r->0->'Plan'->>'Plan Rows')::bigint;
end $$ language plpgsql;

Nhưng phải biết giới hạn của nó. Trên phép đo của tôi, nó cho 25.000 trong khi số thật là 10.000 — sai 2,5 lần. Đủ để hiện "khoảng 25 nghìn kết quả", không đủ để hiện "trang 3 / 500", vì số trang sai sẽ dẫn tới trang trống.

Ba cách xử lý thực dụng:

Cách đầu tiên là lý do các ứng dụng hiện đại bỏ hẳn số trang. Đó không phải quyết định thẩm mỹ.

Khi nào OFFSET không phải thủ phạm

Bỏ chỉ mục trên cột sắp xếp rồi đo lại:

Ngay offset 0 đã mất 221 ms. Ở đây chi phí nằm ở việc sắp xếp 5 triệu dòng, không nằm ở OFFSET.

Đây là chỗ chẩn đoán dễ sai: bạn thấy phân trang chậm, đọc được rằng OFFSET đắt, rồi bỏ công viết lại theo khoá — mà vấn đề thật là thiếu chỉ mục, và phân trang theo khoá cũng cần đúng chỉ mục đó.

Cách phân biệt: đo trang đầu tiên. Nếu trang 1 đã chậm thì vấn đề là sắp xếp, không phải OFFSET.

Bảng chọn nhanh

Điểm yếu duy nhất của phân trang theo khoá: không nhảy thẳng tới trang N được. Nó chỉ biết "trang sau" và "trang trước". Nếu giao diện của bạn có dãy số trang bấm được thì hoặc giữ OFFSET, hoặc đổi giao diện.

Thử ba mươi giây

Tìm xem trang sâu nhất mà người dùng thật sự bấm tới:

explain (analyze, buffers)
select * from <bảng> order by <cột> desc limit 20 offset 200;

Nếu con số ở offset 200 đã trên 50 ms, vấn đề là chỉ mục chứ không phải OFFSET. Nếu nó dưới 1 ms, hãy thử tăng offset lên gấp mười cho tới khi vượt 50 ms — con số đó là giới hạn thực tế của bạn, và nếu nó lớn hơn số trang người dùng bấm tới thì không cần đổi gì.

Phần sau đo JSONB: khi nào nên dùng, khi nào cột thường rẻ hơn, và cái giá của việc lưu dữ liệu có cấu trúc vào một cột duy nhất.