Tối ưu hóa hiệu suất phân trang sâu trong MySQL

Tổng quan về vấn đề phân trang sâu trong MySQL

Khi làm việc với các hệ thống cơ sở dữ liệu lớn, việc phân trang (pagination) là một tính năng không thể thiếu. MySQL cung cấp cú pháp LIMIT offset, rows để dễ dàng truy xuất các tập con dữ liệu. Tuy nhiên, hiệu suất của phương pháp này suy giảm đáng kể khi chỉ số offset trở nên quá lớn, một tình trạng thường được gọi là "phân trang sâu" (deep pagination).

Ví dụ, một truy vấn như SELECT * FROM du_lieu LIMIT 600000, 10 yêu cầu MySQL quét qua khoảng 600.010 bản ghi, sau đó loại bỏ 600.000 bản ghi đầu tiên và chỉ trả về 10 bản ghi cuối cùng. Trong trường hợp không có index bao phủ, mỗi bản ghi được quét có thể đòi hỏi truy cập ngẫu nhiên vào đĩa để lấy toàn bộ dữ liệu (qua hành động "back-to-table" hoặc "lookup"), dẫn đến lãng phí tài nguyên và thời gian xử lý.

Tìm hiểu cú pháp LIMIT của MySQL

Cú pháp LIMIT trong MySQL dùng để giới hạn số lượng bản ghi trả về từ một truy vấn. Nó có thể được sử dụng để phân trang hoặc lấy một tập hợp con dữ liệu.

SELECT truong_du_lieu FROM ten_bang LIMIT [vi_tri_bat_dau,] so_luong_ban_ghi;

Các biến thể của câu lệnh LIMIT

  1. Dạng phổ biến: LIMIT vi_tri_bat_dau, so_luong_ban_ghi 
    -- Lấy 5 bản ghi đầu tiên, bắt đầu từ bản ghi thứ 0
SELECT * FROM san_pham LIMIT 0, 5;
-- Lưu ý: Hai tham số được phân cách bằng dấu phẩy.
  2. Dạng LIMIT so_luong_ban_ghi OFFSET vi_tri_bat_dau 
    -- Lấy 5 bản ghi đầu tiên, bắt đầu từ bản ghi thứ 0
SELECT * FROM don_hang LIMIT 5 OFFSET 0;
-- Lưu ý: Sử dụng hai từ khóa LIMIT và OFFSET, mỗi từ khóa đi kèm một tham số.
  3. Dạng rút gọn: LIMIT so_luong_ban_ghi 
    -- Lấy 5 bản ghi đầu tiên, vị trí bắt đầu mặc định là 0
SELECT * FROM nguoi_dung LIMIT 5;

Các chiến lược tối ưu hóa phân trang sâu

1. Giới hạn phạm vi phân trang ở giao diện người dùng

Tương tự cách các công cụ tìm kiếm lớn như Google hay Baidu hoạt động, chúng ta có thể giới hạn số trang người dùng có thể điều hướng trực tiếp (ví dụ, chỉ cho phép đi tới 100 trang). Nếu người dùng muốn truy cập các trang sâu hơn, họ sẽ phải thực hiện một hành động tải lại hoặc điều hướng đặc biệt, giúp giảm thiểu tần suất các truy vấn phân trang sâu không cần thiết.

2. Sử dụng ID lớn nhất hoặc mốc thời gian cuối cùng

Phương pháp này hiệu quả khi dữ liệu có thể được sắp xếp theo một trường tăng dần duy nhất (ví dụ: khóa chính ID, hoặc timestamp). Thay vì dùng OFFSET lớn, chúng ta chỉ cần tìm các bản ghi có ID lớn hơn ID của bản ghi cuối cùng trên trang trước đó.

-- Giả sử last_item_id là ID của bản ghi cuối cùng trên trang hiện tại
SELECT *
FROM danh_muc_san_pham
WHERE id_san_pham > :last_item_id
ORDER BY id_san_pham ASC
LIMIT :so_luong_ban_ghi;

Kỹ thuật này hoạt động tốt với các trường có tính rời rạc như ID tự tăng hoặc các trường liên tục như DATETIME.

3. Kết hợp Subquery để lọc ID trước

Chúng ta có thể sử dụng một subquery để trước tiên tìm ra các ID của các bản ghi mong muốn với điều kiện và LIMIT/OFFSET, sau đó dùng các ID này để truy vấn toàn bộ dữ liệu từ bảng chính.

SELECT *
FROM bang_chinh
WHERE ma_sp IN (
    SELECT ma_sp
    FROM bang_chinh
    WHERE (trang_thai = 'ACTIVE')
    ORDER BY thoi_gian_tao DESC
    LIMIT 100000, 10
);

Tuy nhiên, hiệu suất của subquery này vẫn có thể gặp vấn đề với LIMIT/OFFSET nếu không có index phù hợp. Đây là lý do dẫn đến phương pháp tiếp theo.

4. Kỹ thuật JOIN với Index bao phủ (khuyến nghị)

Đây là một trong những phương pháp hiệu quả nhất. Ý tưởng là sử dụng một subquery chỉ để lấy các khóa chính (hoặc các trường được index) từ các bản ghi mong muốn. Subquery này có thể được tối ưu hóa bằng một index bao phủ (covering index) để tránh truy cập bảng chính. Sau đó, kết quả của subquery được nối với bảng chính để lấy toàn bộ dữ liệu.

SELECT p.*
FROM san_pham p
INNER JOIN (
    SELECT sp.id_san_pham
    FROM san_pham sp
    WHERE (sp.loai_sp = 'Electronics')
    ORDER BY sp.ngay_cap_nhat DESC
    LIMIT 50000, 20
) AS id_trang
ON p.id_san_pham = id_trang.id_san_pham;

Để tối ưu hơn nữa, nếu có điều kiện WHERE, hãy đảm bảo rằng index bao gồm trường trong điều kiện WHERE (ví dụ: loai_sp) ở vị trí đầu tiên, và trường sắp xếp hoặc khóa chính ở vị trí tiếp theo.

-- Ví dụ cần tối ưu
SELECT id FROM bai_viet WHERE danh_muc_id = 1 LIMIT 100000,10;

-- Tạo index bao phủ phù hợp
ALTER TABLE bai_viet ADD INDEX idx_danh_muc_id_id(danh_muc_id, id);

Khi chỉ chọn khóa chính trong subquery và có một index bao phủ cho các trường trong WHERE và ORDER BY, MySQL có thể thực hiện toàn bộ subquery chỉ bằng cách đọc từ index mà không cần truy cập dữ liệu trên bảng chính, giúp tăng tốc độ đáng kể.

Ví dụ thực tế

1. Cơ chế phân trang trong các thư viện truy vấn

Các thư viện hoặc framework thường cung cấp các tiện ích để tạo câu lệnh SQL phân trang. Một phương thức điển hình có thể có dạng như sau:

// Phương thức giả định trong một lớp tạo truy vấn phân trang
public String taoTruyVanPhanTrang(String truong_chon, String bang_goc,
                                   String dieu_kien_where, String truong_sap_xep,
                                   String menh_de_limit) {
    StringBuilder sql = new StringBuilder();
    sql.append("SELECT ").append(truong_chon);
    sql.append(" FROM ").append(bang_goc);
    if (dieu_kien_where != null && !dieu_kien_where.isEmpty()) {
        sql.append(" WHERE ").append(dieu_kien_where);
    }
    sql.append(" ORDER BY ").append(truong_sap_xep);
    sql.append(" ").append(menh_de_limit); // Ví dụ: "LIMIT 10 OFFSET 0" hoặc "LIMIT 10"

    return sql.toString();
}

Trong trường hợp này, menh_de_limit có thể là "LIMIT 10", tương đương với "LIMIT 0, 10", truy xuất 10 bản ghi đầu tiên.

2. Chia dữ liệu thành các phân vùng dựa trên index

Trong các tình huống xử lý dữ liệu lớn (ví dụ: xử lý hàng loạt, ETL), việc chia dữ liệu thành các "khối" nhỏ hơn dựa trên một trường được index là rất hiệu quả. Quá trình này giúp xử lý từng phần dữ liệu một cách độc lập và tối ưu.

Các bước thực hiện:

  1. Đếm tổng số bản ghi: 
    SELECT COUNT(1) AS tong_so_ban_ghi FROM du_lieu_lon; -- Ví dụ: tong_so_ban_ghi = 1000
  2. Xác định kích thước mỗi khối: Nếu muốn chia thành 4 khối, mỗi khối sẽ có 1000 / 4 = 250 bản ghi.
  3. Tìm điểm biên của từng khối bằng cách truy vấn khóa chính:

    Giả sử khóa chính là ma_ghi_nhan.

    • Khối 1: Bắt đầu từ 0. Tìm bản ghi thứ 251. 
      SELECT ma_ghi_nhan FROM du_lieu_lon WHERE ma_ghi_nhan >= 0 ORDER BY ma_ghi_nhan ASC LIMIT 250, 1;
-- Kết quả ví dụ: 251 (tức là bản ghi thứ 251 có ma_ghi_nhan là 251)
-- Phạm vi Khối 1: [0, 251)
    • Khối 2: Bắt đầu từ 251. Tìm bản ghi thứ 251 từ điểm bắt đầu mới (tức là bản ghi thứ 501 tổng thể). 
      SELECT ma_ghi_nhan FROM du_lieu_lon WHERE ma_ghi_nhan >= 251 ORDER BY ma_ghi_nhan ASC LIMIT 250, 1;
-- Kết quả ví dụ: 502
-- Phạm vi Khối 2: [251, 502)
    • Khối 3: Tương tự, bắt đầu từ 502, tìm bản ghi thứ 751 tổng thể. 
      SELECT ma_ghi_nhan FROM du_lieu_lon WHERE ma_ghi_nhan >= 502 ORDER BY ma_ghi_nhan ASC LIMIT 250, 1;
-- Kết quả ví dụ: 753
-- Phạm vi Khối 3: [502, 753)
    • Khối 4: Bắt đầu từ 753 đến hết. 
      -- Phạm vi Khối 4: [753, +∞)
  4. Truy vấn từng khối dữ liệu:

    Với các điểm biên đã xác định, chúng ta có thể tạo các truy vấn để xử lý từng khối dữ liệu một cách hiệu quả.

    -- Truy vấn cho Khối 1
SELECT ma_ghi_nhan, du_lieu_khac FROM du_lieu_lon WHERE ma_ghi_nhan >= 0 AND ma_ghi_nhan < 251 ORDER BY ma_ghi_nhan ASC;

-- Truy vấn cho Khối 2
SELECT ma_ghi_nhan, du_lieu_khac FROM du_lieu_lon WHERE ma_ghi_nhan >= 251 AND ma_ghi_nhan < 502 ORDER BY ma_ghi_nhan ASC;

Thẻ: mysql pagination performance optimization sql Database Index

Đăng vào ngày 20 tháng 7 lúc 16:12

Thẻ Phổ Biến

©2026 Thành phố Cuồng loạn