Posted inMySQL

Tối ưu phân trang MySQL hàng triệu bản ghi: Dùng Seek Method thay thế OFFSET

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

Hồi mình mới deploy cái users table lên production, mọi thứ chạy mượt. Nhưng khi table vượt 10 triệu row, slow query log bắt đầu ghi những câu SELECT có thời gian thực thi lên đến 3–5 giây — và chúng đều là các câu phân trang dạng LIMIT x OFFSET y. Đó là lúc mình phải ngồi xuống tìm hiểu nghiêm túc về vấn đề này.

Vấn đề thực tế: OFFSET càng lớn, query càng chậm

Query này ai cũng từng viết ít nhất một lần:

-- Trang 1
SELECT id, username, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Trang 500
SELECT id, username, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 9980;

Trang 1 chạy dưới 10ms. Trang 500 mất 2–3 giây. Trang 1000 mất 5–6 giây. Và nó không dừng lại ở đó.

Đây là benchmark mình đo thực tế trên bảng 10 triệu row, có index trên created_at:

OFFSET 0         →   8ms
OFFSET 100,000   →  420ms
OFFSET 500,000   →  2.1s
OFFSET 1,000,000 →  4.3s

Phân tích nguyên nhân: MySQL đang làm gì phía sau?

Chạy EXPLAIN cho câu query với OFFSET lớn để xem thực sự chuyện gì xảy ra:

EXPLAIN SELECT id, username, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 500000;

MySQL không nhảy thẳng đến row thứ 500,000. Thay vào đó, nó phải thực hiện tuần tự:

  1. Scan qua index created_at từ đầu
  2. Đọc 500,020 row từ disk hoặc buffer pool
  3. Bỏ đi 500,000 row đầu tiên
  4. Trả về 20 row còn lại

Với OFFSET 500,000, bạn đang buộc MySQL xử lý nửa triệu row chỉ để lấy 20 row. Không có shortcut nào ở đây — đây là đặc tính thiết kế của SQL chuẩn, không phải bug của MySQL.

Còn một vấn đề nữa. Nếu bảng dùng clustered index trên id nhưng sort theo created_at, MySQL phải thực hiện double-lookup: đọc secondary index → lấy primary key → đọc lại row từ clustered index. Nhân I/O đó lên 500,000 lần — bạn tự hiểu tại sao query chậm đến vậy.

Các cách giải quyết

Cách 1: Late Row Lookup — tối ưu nhanh, không đổi API

Ý tưởng đơn giản: tách query thành 2 bước. Đầu tiên dùng subquery chỉ lấy id (rất nhẹ vì chỉ scan index), rồi mới JOIN để lấy đủ cột:

SELECT u.id, u.username, u.email, u.created_at
FROM users u
INNER JOIN (
    SELECT id FROM users
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 500000
) AS tmp ON u.id = tmp.id;

Thay vì đọc toàn bộ dữ liệu (username, email…) cho 500,020 row, MySQL chỉ scan index để lấy 500,020 giá trị id nhỏ gọn, rồi mới join để lấy data thật. Giảm I/O đáng kể.

Kết quả thực tế: câu query trên giảm từ ~2.1s xuống còn ~600ms. Cải thiện ngay mà không cần thay đổi logic phía client — vẫn dùng page number như bình thường.

Cách 2: Seek Method (Keyset Pagination) — giải pháp triệt để

Seek Method không “nhảy” tới vị trí. Nó dùng giá trị của row cuối trang trước làm cursor — rồi query tiếp từ đúng điểm đó. Logic thay đổi hoàn toàn:

-- Trang đầu tiên (không cần cursor)
SELECT id, username, email, created_at
FROM users
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Trang tiếp theo: truyền vào created_at và id của row CUỐI trang trước
-- Giả sử row cuối có: created_at = '2024-03-15 10:30:00', id = 9876543
SELECT id, username, email, created_at
FROM users
WHERE (created_at < '2024-03-15 10:30:00')
   OR (created_at = '2024-03-15 10:30:00' AND id < 9876543)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Tạo composite index để MySQL có thể range scan hiệu quả:

CREATE INDEX idx_users_created_id ON users (created_at DESC, id DESC);

Benchmark so sánh trên cùng bảng 10 triệu row:

-- Trước (OFFSET)
OFFSET 0         →  8ms
OFFSET 500,000   →  2.1s

-- Sau (Seek Method)
Trang đầu        →  8ms
Trang 25,000     →  9ms  ← bất kể trang thứ bao nhiêu!

Lý do hiệu quả: thay vì scan từ đầu và bỏ đi, MySQL dùng điều kiện WHERE để index range scan và chỉ đọc đúng 20 row cần thiết. Thời gian query gần như cố định, không phụ thuộc vào độ sâu phân trang.

Implementation trong Python

def get_users_page(db, cursor_created_at=None, cursor_id=None, limit=20):
    """
    cursor_created_at: datetime string của row cuối trang trước
    cursor_id: id của row cuối trang trước
    Trả về: {"data": [...], "next_cursor": {...} hoặc None}
    """
    if cursor_created_at is None:
        # Trang đầu tiên
        query = """
            SELECT id, username, email, created_at
            FROM users
            ORDER BY created_at DESC, id DESC
            LIMIT %s
        """
        params = (limit,)
    else:
        query = """
            SELECT id, username, email, created_at
            FROM users
            WHERE (created_at < %s)
               OR (created_at = %s AND id < %s)
            ORDER BY created_at DESC, id DESC
            LIMIT %s
        """
        params = (cursor_created_at, cursor_created_at, cursor_id, limit)

    rows = db.execute(query, params)

    # Cursor của row cuối — client dùng để lấy trang tiếp
    next_cursor = None
    if rows:
        last = rows[-1]
        next_cursor = {"created_at": str(last["created_at"]), "id": last["id"]}

    return {"data": rows, "next_cursor": next_cursor}

Chốt lại: dùng gì, khi nào?

Mình đã migrate pagination ít nhất 3 lần trên production. Đây là những gì rút ra được:

  • OFFSET thông thường: Bảng nhỏ hơn 100,000 row — không cần tối ưu, đừng over-engineer.
  • Late Row Lookup: Cần giữ phân trang theo số trang, không muốn đổi API contract, muốn cải thiện nhanh ngay hôm nay.
  • Seek Method: Build infinite scroll, nút “Xem thêm”, hoặc REST API cursor-based. Đây là lựa chọn tốt nhất về hiệu năng dài hạn.

Bẫy hay gặp khi implement Seek Method

  • Không nhảy trang tùy ý được: Phải đi tuần tự từ trang 1 → 2 → 3… Không thể nhảy thẳng từ trang 1 lên trang 50. Phù hợp cho infinite scroll, không phải numbered pagination kiểu “Trang 1 2 3 … 50”.
  • Cursor phải unique: Nếu sort theo cột có giá trị trùng lặp, luôn thêm id vào composite cursor. Không làm vậy sẽ bỏ sót row.
  • Robust hơn OFFSET khi có insert/delete: OFFSET bị “trôi” khi có row mới được thêm vào giữa hai lần request. Seek Method không bị vấn đề này — một điểm cộng lớn cho real-time data.
  • COUNT(*) cần cache: Với bảng InnoDB lớn, SELECT COUNT(*) phải full scan (khác MyISAM). Cache giá trị này với TTL 5–10 phút thay vì chạy mỗi request.
-- Cache kết quả này (TTL 5-10 phút), KHÔNG chạy lại mỗi request
SELECT COUNT(*) FROM users WHERE status = 'active';

Tổng kết

LIMIT x OFFSET y tiện lợi khi mới code, nhưng là cái bẫy hiệu năng thực sự khi dữ liệu lớn. Mình đã học điều này theo cách khó nhất — sau khi user report trang danh sách load mãi không xong lúc nửa đêm.

Đang thiết kế tính năng mới? Dùng Seek Method ngay từ đầu, đừng để nợ kỹ thuật. Đang có sẵn OFFSET-based pagination? Migrate sang Late Row Lookup trước — ít breaking change nhất, cải thiện gần như tức thì — rồi từ từ chuyển sang cursor-based khi có điều kiện.

Share:
Tags: