Sử dụng OFFSET để bỏ qua dòng và xây dựng phân trang

Cho mình hỏi nhé: khi bạn vào một trang web bán hàng mà thấy quá nhiều sản phẩm trên một trang thì bạn làm gì? Đúng rồi, bạn chuyển sang trang tiếp theo. Thế chuyện gì xảy ra ở phía sau? Đó là "ma thuật" của SQL — dùng lệnh OFFSET để bỏ qua dòng. Hôm nay bạn sẽ biết OFFSET là gì, dùng để làm gì, cách dùng ra sao và tại sao nó là nền tảng của phân trang dữ liệu.

OFFSET — là từ khóa trong SQL cho phép bạn bỏ qua một số dòng nhất định trong kết quả truy vấn. Nó giống như lật trang sách: bạn có thể "bỏ qua" 10 dòng đầu và bắt đầu xem từ dòng thứ 11.

Cú pháp

SELECT cột1, cột2
FROM bảng
OFFSET số_lượng_dòng;

• OFFSET — từ khóa để bỏ qua dòng.
• số_lượng_dòng — số dòng bạn muốn bỏ qua.

Ví dụ đơn giản về OFFSET

Giả sử mình có bảng students với dữ liệu như sau:

Mình muốn hiển thị tất cả sinh viên, bắt đầu từ người thứ ba. Để làm vậy, mình dùng truy vấn:

SELECT *
FROM students
OFFSET 2;

Kết quả:

SQL đã "bỏ qua" hai dòng đầu (Alysa và Bob) và trả về kết quả bắt đầu từ dòng thứ ba. Giống như dùng bookmark: "trang này đọc rồi, mở trang tiếp theo thôi".

Kết hợp OFFSET với LIMIT

OFFSET thường dùng chung với LIMIT. Như vậy bạn vừa bỏ qua dòng vừa giới hạn số bản ghi trả về. Cực kỳ hữu ích cho phân trang (page — trang) — hiển thị dữ liệu từng phần, mỗi lần vài dòng.

Giả sử mình muốn hiển thị 2 bản ghi mỗi lần và bắt đầu từ dòng thứ ba. Truy vấn sẽ như sau:

SELECT *
FROM students
LIMIT 2 
OFFSET 2;

Kết quả:

Logic hoạt động:

1. OFFSET 2 bỏ qua hai dòng đầu: Alysa và Bob.
2. LIMIT 2 lấy đúng hai dòng tiếp theo.

Xây dựng phân trang

Giờ đến phần thú vị nhất — xây dựng phân trang. Hãy tưởng tượng bạn làm web app có danh sách sinh viên. Mỗi trang bạn muốn hiển thị 2 bản ghi. Làm sao nhỉ?

Nguyên tắc cơ bản:

• Trang đầu bỏ qua 0 dòng: OFFSET 0.
• Trang thứ hai bỏ qua 2 dòng: OFFSET 2.
• Trang thứ ba bỏ qua 4 dòng: OFFSET 4.
• Cứ thế tiếp tục...

Ví dụ: hiển thị trang thứ hai

Trang đầu hiển thị bản ghi 1 và 2 (Alysa và Bob). Trang thứ hai — bản ghi 3 và 4 (Klara và Dan).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 2;

Kết quả:

Ví dụ: hiển thị trang thứ ba

Trang thứ ba — bản ghi 5 và 6 (nếu có).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 4;

Kết quả:

Công thức tính OFFSET

Khi xây dựng hệ thống phân trang, bạn có thể dùng công thức sau để tự động hóa:

OFFSET = (so_trang - 1) * so_ban_ghi_tren_trang

Ví dụ:

• Trang đầu: (1 - 1) * 2 = 0.
• Trang thứ hai: (2 - 1) * 2 = 2.
• Trang thứ ba: (3 - 1) * 2 = 4.

Lưu ý quan trọng về hiệu năng

Khi làm việc với bảng lớn, dùng OFFSET có thể không hiệu quả, nhất là ở các trang sau. Lý do là PostgreSQL vẫn phải duyệt qua các dòng bị bỏ qua. Ví dụ, truy vấn với OFFSET 10000 sẽ khiến DBMS phải duyệt qua 10 000 dòng đầu trước khi trả kết quả. Trong trường hợp này, bạn nên cân nhắc giải pháp khác, như dùng id duy nhất làm marker cho phân trang.

Giải pháp khác: phân trang bằng cursor

Để tối ưu, bạn có thể dùng cách "cursor". Thay vì bỏ qua dòng bằng OFFSET, bạn nhớ id của dòng cuối cùng ở trang trước và dùng nó cho truy vấn tiếp theo:

SELECT *
FROM students
WHERE id > id_cuoi_cung_da_hien_thi
ORDER BY id
LIMIT 2;

Cách này có thể tăng tốc đáng kể khi làm với bảng lớn.

Phân trang dùng trong thực tế

Phân trang được dùng trong hầu hết web app: shop online, blog, trang quản trị. Ví dụ:

• Hiển thị danh sách sản phẩm trong shop online;
• Danh sách user trong hệ thống CRM;
• Phân trang news feed.

Phân trang cũng hữu ích khi phân tích dữ liệu lớn, giúp bạn làm việc với từng phần nhỏ.

Lỗi thường gặp khi dùng OFFSET

Làm việc với OFFSET đôi khi gây khó khăn, nhất là lúc mới học. Đây là vài lỗi phổ biến:

Thiếu sắp xếp. Nếu bạn không thêm ORDER BY, thứ tự dòng trong kết quả OFFSET sẽ không đoán trước được.

Truy vấn sai:

SELECT * FROM students
OFFSET 5;

Truy vấn đúng:

SELECT * FROM students
ORDER BY id
OFFSET 5;

Giá trị OFFSET sai. Nếu bạn để giá trị quá lớn, kết quả sẽ là tập rỗng.

Vấn đề hiệu năng. Như đã nói ở trên, dùng OFFSET lớn ở trang sau sẽ không hiệu quả.

Thiếu filter. Nếu bạn dùng OFFSET và LIMIT mà không có filter (WHERE), bạn có thể lấy dữ liệu không cần thiết, làm giảm hiệu năng.

Thứ tự toán tử. Thứ tự toán tử trong SQL là cố định: LIMIT trước, OFFSET sau. Có thể bỏ qua (không ghi) toán tử, nhưng không được đổi thứ tự.