Chào mọi người! Với hầu hết những lập trình viên từng học PHP + Mysql bài toàn nâng cao đầu tiên lúc bấy giờ có thể kể đến chính là phân trang cho bảng dữ liệu MySQL. Chỉ cần vài câu PHP cùng một câu query bài toán đã được giải quyết.
// In ra danh sách các trang
$perPage = 10;
$page = (isset($_GET['page'])) ? (int)$_GET['page'] : 1;
$startAt = $perPage * ($page - 1);
$query = "SELECT COUNT(1) as total FROM `thisTable` WHERE user_id = '".$_SESSION['user_id']."'";
$data = mysql_fetch_assoc(mysql_query($query));
$totalPages = ceil($data ['total'] / $perPage);
$links = "";
for ($i = 1; $i <= $totalPages; $i++) {
$links .= ($i != $page)
? "Page $i "
: "$page ";
}
echo $links;
// Xử lý dữ liệu được lấy ra từng trang
$query = "SELECT * FROM `thisTable` LIMIT $startAt, $perPage";
$rowsWantToShow = mysql_query($query);
Vấn đề
Với đoạn code trên có thể thấy biến $startAt có vai trò làm OFFSET trong đoạn query, với một bảng dữ liệu vài nghìn dòng, tốc độ truy vấn không cho thấy sự khác nhau khi thực hiện. Tuy nhiên với bảng dữ liệu siêu to liên đến hàng triệu hay hàng tỉ bản ghi việc phân trang không còn đơn giản như vậy nữa!
Với tham số OFFSET ngày càng lớn và $perPage không đổi MySQL cần phải đưa toàn bộ OFFSET + $perPage bản ghi vào bộ nhớ RAM. Sau đó chỉ trả ra $perPage dòng dữ liệu có ý nghĩa. Vấn đề sẽ xảy ra khi dung lượng RAM còn trống không đủ để chứa toàn bộ OFFSET + $perPage dữ liệu, cho dù MySQL đủ tốt để có thể tiếp tục xử lý phần dữ liệu còn lại với mức RAM hạn hẹp nhưng đồng thời hiệu suất làm việc bị kéo xuống nghiêm trọng khi CPU cũng tham gia hết công suất nhằm điều phối I/O xuống ổ cứng. Kéo theo toàn bộ hệ thống bị đình trệ chỉ vì một câu truy vấn không tốt cho tới khi timeout hoặc tệ hơn khi timeout là vô cực.
Giải pháp
Như vậy cần đưa ra giải pháp để tối ưu trường này một cách đơn giản:
- Không hiển thị tổng số bản ghi mà bảng hiện có nếu người dùng không thực sự quan tâm!
- Không cho phép người dùng đi tới những trang quá lớn! Cố gắng chuyển hướng người dùng đến công cụ tìm kiếm. (Nếu người dùng đó đang tìm kiếm bộ phim hợp khẩu vị và mần tới trang thứ vài trăm, thực sự họ đang quá tuyệt vọng)
Một vài chỉnh sửa đơn giản sẽ đáp ứng được hai đầu dòng kia. Sử dụng load more thay cho phân trang dạng số trang. Tuy nhiên bản chất của load more vẫn dùng offset để lọc dữ liệu từ cơ sở dữ liệu! Người dùng kiên nhân nào đó vẫn có khả năng đạt tới giới hạn bộ nhớ và mọi thứ bắt đầu chậm chạp.
Giải pháp 2
Nếu dùng OFFSET không đem lại hiệu quả! Đừng dùng nó nữa. Giải pháp 2 theo mình có thể áp dụng vào những hệ thống backend CMS lớn, nhiều khi cần đào sâu dữ liệu tới những dòng cuối cùng của bảng dữ liệu! Sử dụng WHERE để tìm kiếm tập dữ liệu cần thiết trên trường định danh của bảng và LIMIT số lượng dòng cần lấy không sử dụng OFFSET.
Nói một cách đơn giản, việc tìm một cột duy nhất hoặc tập hợp các cột xác định mỗi hàng. Sau đó, thay vì sử dụng OFFSET, chúng ta chỉ có thể sử dụng giá trị duy nhất đó làm dấu trang thể hiện vị trí của hàng cuối cùng mà chúng ta đã tìm nạp và truy vấn nhóm hàng tiếp theo bằng cách bắt đầu từ vị trí này trong WHERE.
Ví dụ: nhìn vào các truy vấn mình đã thực hiện trước đó, giả sử id cuối cùng trong phần dữ liệu được lấy ra là 100000, truy vấn sẽ là:
SELECT *
FROM
`thisTable`
WHERE
id > 1000000
ORDER BY id
LIMIT $perPage
Vâng bây giờ việc lấy phần tử từ dòng +100000 đã nhanh “tụt quần”. Nhưng hạn chế của giải pháp yêu cầu lưu lại id cuối cùng của phần tử trang trước và gửi đi cùng khi nhảy trang. Nếu tệp dữ liệu sắp xếp theo nhiều cột cần tìm được cột nào luôn tăng hoặc luôn giảm để làm cột tìm kiếm nếu không dữ liệu các trang sẽ lộn xộn và trùng lặp. Giải pháp này mình mới tìm hiểu chưa áp dụng vào bài toán thực tế nào, nếu có thể sẽ Implement vào bài viết lần sau.
Tài liệu tham khảo: https://www.slideshare.net/suratbhati/efficient-pagination-using-mysql-6187107
(4 lượt thả tim)
Loading...
