Tối ưu phân trang

Mong muốn

Bạn có một trang web với danh sách rất nhiều các bài viết, hoặc blog, hoặc một số đối tượng khác và nó quá dài để hiển thị trên một trang duy nhất. Vì vậy, bạn quyết định chia nó thành nhiều phần, ví dụ 10 mục trên mỗi trang và hiển thị một nút nút Next để đi tới trang tiếp theo.

Dựa vào số trang hiện tại, bạn sẽ truyền các tham số phù hợp cho OFFSET và LIMIT để lấy ra được những dữ liệu phù hợp.

SELECT * FROM users WHERE <messy_filtering> ORDER BY created_at DESC OFFSET $M LIMIT $N

Vấn đề

Mọi thứ dường như nhẹ nhàng như lông hồng, cho đến khi bạn có 50.000 bản ghi trong table, và có người dùng nào đó "rảnh" cố gắng đi qua hết 5000 trang (VD như bác Google).

Vấn đề là gì? Là hiệu suất. Khi người dùng click đến trang thứ 5000, query của bạn sẽ có dạng SELECT ... OFFSET 49990 LIMIT 10. Bạn sẽ có chút buồn khi biết răng lúc đấy MySQL sẽ phải tìm tất cả 50.000 bản ghi phù hợp, bỏ 49.990 bản ghi đầu tiên và trả về 10 bản ghi cuối cùng cho bạn.

Tìm tất cả các bản ghi trong bảng chi để lấy các bản ghi cuối ở những page cuối, nó có thể tốt nhiều I/O server gây ra lỗi timeout, hoặc có thể ảnh hưởng tới các hoạt động khác, và quan trọng hơn là ảnh hưởng tới trải nghiệm của người dùng.

Một số vấn đề khác

Hiệu suất là vấn đề chính, ngoài ra nó còn một số vấn đề khác:

• Khi người dùng đang trải nghiệm web chuyển thì trang này sang trang khác, lúc đó có một bản ghi mới được thêm vào, người dùng có thể sẽ bỏ lỡ một bản ghi hoặc có một bản ghi bị lặp lại ở page sau.
• Nội dung thường tăng và thay đổi liên tục. Mệnh đề WHERE và ORDER BY thậm chí có thể làm cho MySQL cố gắng tìm tất cả các bản ghi phù hợp rồi chỉ lấy 10 bản ghi đầu tiên cho trang 1 trong kết quả.

Chúng ta phải làm gì?

Nâng cấp server? Không, đó chỉ là giải pháp tạm thời trước mắt. Dữ liệu sẽ tiếp tục tăng và sẽ đến lúc nâng cấp server không giải quyết được vấn đề đó.

Đánh INDEX liệu có tốt hơn không? Không, bạn đánh INDEX chỉ giải quyết được một số trường hợp query cụ thể nào đó, còn lại thì vẫn đâu vào đấy.

Xây dựng lại từ đầu để tìm cách tối ưu tận gốc? Chưa biết nó có khả thi hay không, nhưng chắc chắn đó là một cơn ác mộng dài ngày để duy trì.

Vậy phải làm sao? Đơn giản là bạn hãy bỏ OFFSET. Bạn hãy tìm lý do vì sao bạn cần dùng OFFSET và tìm cách giải quyết vấn đề đó mà không cần OFFSET nữa.

Phải làm gì?

Phần cứng? Không, đó chỉ là một bandaid. Dữ liệu sẽ tiếp tục phát triển và thậm chí phần cứng mới sẽ không xử lý nó.

Better INDEX? Không. Bạn phải tránh xa việc đọc toàn bộ bảng để lấy trang thứ 5000.

Xây dựng một bảng khác nói rằng các trang bắt đầu từ đâu? Thực tế! Đó sẽ là một cơn ác mộng duy trì, và đắt tiền.

Bottom line: Không sử dụng OFFSET; Thay vào đó hãy nhớ nơi bạn "bỏ đi".

#First page (latest 10 items):
    SELECT ... WHERE ... ORDER BY id DESC LIMIT 10
#Next page (second 10):
    SELECT ... WHERE ... AND id < $left_off ORDER BY id DESC LIMIT 10

Với INDEX (id), điều này trở nên rất hiệu quả.

Áp dụng - vứt bỏ OFFSET

Hiện tại, nút Next có thể có URL giống như thế này: ?category=1&page=4999&limit=10 Và câu query sẽ như thế này:

SELECT * FROM posts WHERE category=1 ORDER BY id DESC OFFSET 49990 LIMIT 10

Bây giờ, nút Next sẽ có URL giống như thế này: ?category=1&id=12345&limit=10 (Lưu ý rằng 12345 không thể tính từ 4999). Bằng cách đánh INDEX (category, id), bạn sẽ thấy nó hiệu quả thế này:

SELECT * FROM posts WHERE category=1 AND id < 12345 ORDER BY id DESC LIMIT 10

Như bạn thấy, MySQL sẽ chỉ lấy 10 bản ghi đầu tiên phù hợp.

Kết quả nhanh hơn thế nào?

Điều này phụ thuộc vào:

• Có bao nhiêu bản ghi trong bảng (tổng cộng)
• Mệnh đề WHERE ngăn cản việc sử dụng hiệu quả ORDER BY
• Dữ liệu lớn hơn bộ nhớ cache. Điều này cuối cùng đá vào khi xây dựng một trang đòi hỏi phải đọc thêm dữ liệu từ đĩa có thể được lưu trữ. Tại thời điểm đó, vấn đề đi từ CPU bị ràng buộc bởi tốc độ I/O. Điều này có thể sẽ làm chậm tốc độ tải trang xuống.

Hạn chế

• Không thể nhảy tới trang N bất kỳ. Nhưng tại sao bạn lại muốn làm điều đó?
• Đi ngược từ phía trang cuối sẽ không biết số trang.
• Code phức tạp hơn.

Tham khảo thêm

• A forum discussion
• Luke calls it "seek method" or "keyset pagination"
• More by Luke

Nguồn

https://mariadb.com/kb/en/mariadb/pagination-optimization/