LIMIT và OFFSET trong SQLite: phân trang kết quả truy vấn

LIMIT giới hạn số hàng

LIMIT là nút điều chỉnh đơn giản nhất trong SQL: nó bảo SQLite "cho tôi tối đa chừng này hàng". Đặt nó ở cuối câu SELECT và bạn sẽ nhận về tối đa số kết quả đó: không hơn, có thể ít hơn nếu bảng không đủ hàng.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
);

INSERT INTO books (title, year) VALUES
    ('The Pragmatic Programmer', 1999),
    ('Code', 1999),
    ('Refactoring', 1999),
    ('Clean Code', 2008),
    ('Designing Data-Intensive Applications', 2017);

SELECT title, year FROM books LIMIT 3;

Bạn nhận về ba hàng đầu tiên. Nhưng chính xác là ba hàng nào? Đó là điểm mấu chốt: không có ORDER BY, SQLite chọn thứ tự nào tiện cho nó. Hôm nay có thể là thứ tự chèn; ngày mai, sau một lần cập nhật hay thay đổi chỉ mục, có thể không còn như vậy. Chỉ dùng LIMIT thì ổn khi bạn cần "xem thử vài hàng", nhưng ngay khi thứ tự quan trọng, bạn phải chỉ định rõ.

OFFSET bỏ qua các hàng ở đầu

Kết hợp LIMIT với OFFSET để lấy một đoạn ở giữa tập kết quả. OFFSET k bỏ đi k hàng đầu tiên; sau đó LIMIT n trả về tối đa n hàng trong phần còn lại.

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    year INTEGER
);

INSERT INTO books (title, year) VALUES
    ('The Pragmatic Programmer', 1999),
    ('Code', 1999),
    ('Refactoring', 1999),
    ('Clean Code', 2008),
    ('Designing Data-Intensive Applications', 2017);

SELECT title, year
FROM books
ORDER BY year, title
LIMIT 2 OFFSET 2;

Câu này nghĩa là "bỏ qua hai hàng, trả về hai hàng tiếp theo", tức hàng thứ 3 và thứ 4 của kết quả đã sắp xếp. Cách hình dung: WHERE lọc, ORDER BY sắp xếp, OFFSET bỏ qua, LIMIT giới hạn. Chúng chạy theo đúng thứ tự đó, và mệnh đề nào cũng quan trọng.

Phân trang luôn cần ORDER BY

Cách dùng LIMIT và OFFSET phổ biến nhất là phân trang: chia một danh sách dài thành các trang, chẳng hạn mỗi trang 20 hàng. Trang 1 là LIMIT 20 OFFSET 0, trang 2 là LIMIT 20 OFFSET 20, và cứ thế tiếp tục.

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT,
    created_at TEXT
);

INSERT INTO posts (title, created_at) VALUES
    ('First post', '2024-01-01'),
    ('Second post', '2024-01-02'),
    ('Third post', '2024-01-03'),
    ('Fourth post', '2024-01-04'),
    ('Fifth post', '2024-01-05');

-- Page 2, with 2 posts per page
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 2 OFFSET 2;

Có hai điều cần chú ý. Thứ nhất, ORDER BY là bắt buộc: không có nó, "trang 2" không có ý nghĩa xác định và các hàng có thể bị xáo trộn giữa những lần tải trang. Thứ hai, khóa sắp xếp có thêm id để phân định khi bằng nhau. Nếu hai bài viết có cùng created_at, bạn cần một cột duy nhất để cho chúng một thứ tự xác định, nếu không vị trí của chúng có thể đổi chỗ và một hàng có thể lọt sang trang khác.

Quy tắc chung: sắp xếp theo một cột duy nhất, hoặc theo cột sắp xếp của bạn cộng thêm một cột duy nhất để phân định.

Cách viết tắt: LIMIT n, m

SQLite hỗ trợ cú pháp dấu phẩy cũ để tương thích với MySQL: LIMIT offset, count. Nó có nghĩa giống LIMIT count OFFSET offset, nhưng thứ tự bị đảo ngược và dễ đọc nhầm.

-- These two are equivalent:
SELECT * FROM books LIMIT 10 OFFSET 20;
SELECT * FROM books LIMIT 20, 10;     -- offset first, then count

Dạng thứ hai ngắn gọn, nhưng hay làm khó những ai nghĩ số đầu tiên là số lượng. Hãy dùng LIMIT n OFFSET k: rõ ràng và đọc từ trái sang phải.

OFFSET không có LIMIT: mẹo LIMIT -1

OFFSET không hợp lệ khi đứng một mình: ngữ pháp của SQLite yêu cầu nó phải đi sau LIMIT. Vậy làm sao để nói "bỏ qua 10 hàng đầu và trả về toàn bộ phần sau"? Quy ước là LIMIT -1, mà SQLite hiểu là "không giới hạn trên".

CREATE TABLE numbers (n INTEGER);
INSERT INTO numbers (n) VALUES (1), (2), (3), (4), (5), (6), (7), (8);

-- Skip the first 3, return the rest
SELECT n FROM numbers ORDER BY n LIMIT -1 OFFSET 3;

Mọi giá trị LIMIT âm đều có tác dụng như vậy, nhưng -1 là cách viết quen thuộc. Bạn sẽ thường gặp nó trong các script duyệt qua kết quả theo trang và cần một truy vấn "lấy hết phần còn lại" cho lô cuối cùng.

Cái bẫy hiệu năng của OFFSET

Đây là điều không ai nhắc tới cho đến khi bạn gặp phải: OFFSET không giúp SQLite bỏ qua công việc, nó chỉ bỏ qua đầu ra. Để trả về các hàng từ 10.001 đến 10.020, engine vẫn phải duyệt qua mười nghìn hàng đầu tiên bên trong trước khi bắt đầu trả kết quả. Offset nhỏ thì không tốn gì; offset lên tới hàng chục hay hàng trăm nghìn sẽ chậm thấy rõ.

Với phân trang sâu, cách khắc phục chuẩn là keyset pagination: thay vì "bỏ qua N hàng", hãy nhớ khóa sắp xếp của hàng cuối cùng và yêu cầu "các hàng sau hàng này".

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    created_at TEXT,
    message TEXT
);

INSERT INTO events (created_at, message) VALUES
    ('2024-01-01 10:00', 'login'),
    ('2024-01-01 10:05', 'view'),
    ('2024-01-01 10:10', 'click'),
    ('2024-01-01 10:15', 'logout'),
    ('2024-01-01 10:20', 'login');

-- After receiving id=2, the next page is:
SELECT id, created_at, message
FROM events
WHERE id > 2
ORDER BY id
LIMIT 2;

Mỗi trang là một lần tra cứu qua chỉ mục thay vì quét qua mọi thứ đứng trước. Đổi lại, bạn không thể nhảy thẳng tới "trang 47", chỉ có thể đi tiếp về phía trước. Với feed cuộn vô hạn và cursor của API, đó chính xác là điều bạn muốn.

Phân trang bằng OFFSET phù hợp với bảng quản trị và tập kết quả nhỏ. Với mọi thứ tăng trưởng không giới hạn, hãy dùng keyset pagination.

Một ví dụ hoàn chỉnh

Ghép tất cả lại: một truy vấn phân trang có lọc, sắp xếp và cột phân định để thứ tự luôn xác định:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    category TEXT,
    price REAL
);

INSERT INTO products (name, category, price) VALUES
    ('Pen', 'office', 1.50),
    ('Notebook', 'office', 4.00),
    ('Stapler', 'office', 8.00),
    ('Mug', 'kitchen', 6.00),
    ('Kettle', 'kitchen', 25.00),
    ('Toaster', 'kitchen', 30.00);

-- Page 1: cheapest 2 office items, alphabetical on price ties
SELECT name, price
FROM products
WHERE category = 'office'
ORDER BY price ASC, name ASC
LIMIT 2 OFFSET 0;

Lọc còn các sản phẩm văn phòng, sắp xếp theo giá tăng dần với tên làm cột phân định, lấy hai hàng đầu. Đổi OFFSET 0 thành OFFSET 2 để lấy trang 2. Truy vấn ngắn, nhưng mệnh đề nào cũng có vai trò riêng.

Tiếp theo: DISTINCT

LIMIT kiểm soát số hàng trả về; DISTINCT kiểm soát việc có trả về hàng trùng hay không. Đó là mệnh đề tiếp theo trong bộ công cụ của SELECT, và rất dễ dùng sai: đó là nội dung của trang tiếp theo.