Tối ưu hóa phân trang mysql

Mayfest2023

Truy vấn phân trang của MySQL thường được thực hiện bằng LIMIT. Bạn có một trang web với các bài báo tin tức, hoặc một blog, hoặc một số điều khác có danh sách những thứ có thể quá dài cho một trang. Vì vậy, bạn quyết định chia nó thành các phần, giả sử, 10 mục và cung cấp nút [tiếp theo] để đi "trang" tiếp theo.

    SELECT  *
        FROM  items
        WHERE  messy_filtering
        ORDER BY  date DESC
        OFFSET  $M  LIMIT $N

Vấn đề Với việc lấy trang 5000 với số limit là 10. Trang web của bạn đang thực hiện "SELECT ... OFFSET 49990 LIMIT 10". DB phải tìm tất cả 50.000 records, offset 49.990 đầu tiên, sau đó lấy 10 record phía sau . Đối với các hiệu số nhỏ, không có vấn đề gì khi sử dụng trực tiếp giới hạn để truy vấn. Tuy nhiên, với sự gia tăng của lượng dữ liệu, trang được phân trang càng muộn thì độ lệch của câu lệnh giới hạn càng lớn và tốc độ càng chậm.

Ý tưởng tối ưu hóa: tránh quét quá nhiều bản ghi khi lượng dữ liệu lớn

Giải pháp: phân trang truy vấn phụ, phân trang kết hợp (cùng một mức và thời gian tiêu thụ về cơ bản là như nhau).

Đây là một ví dụ, khóa chính của bảng tăng dần. Trong trường hợp này, bạn có thể tối ưu hóa nó theo cách sau.

Lấy một bảng với 60000 mẩu dữ liệu trong môi trường sản xuất thực làm ví dụ để so sánh thời gian truy vấn trước và sau khi tối ưu hóa Bạn có thể thấy rằng hiệu suất đã được cải thiện nhiều lần sau khi tối ưu hóa.

Nguyên tắc tối ưu hóa:

Truy vấn phụ được hoàn thành trên chỉ mục, trong khi truy vấn thông thường được hoàn thành trên tệp dữ liệu. Nói chung, tệp chỉ mục nhỏ hơn nhiều so với tệp dữ liệu, vì vậy hoạt động sẽ hiệu quả hơn. Để trích xuất tất cả nội dung trường, phương pháp đầu tiên cần mở rộng một số lượng lớn các khối dữ liệu và trích xuất chúng, trong khi phương pháp thứ hai về cơ bản trích xuất nội dung tương ứng bằng cách định vị trực tiếp theo trường chỉ mục, điều này tự nhiên cải thiện đáng kể hiệu quả. Do đó, việc tối ưu hóa giới hạn không phải là sử dụng trực tiếp giới hạn, mà là lấy ID của bù đắp trước, sau đó sử dụng kích thước giới hạn để lấy dữ liệu trực tiếp.

Trong việc sử dụng dự án thực tế, bạn có thể sử dụng để xử lý phân trang. Giả sử khi gọi api phân trang như sau: http://localhost:50001/comet/api/posts?limit=100&page=1 . Ta có thể dễ dàng tính được các thông số và biến đổi vào query như trên

<?php
   $page = $query['page'] ?? 1;
   $limit = $query['limit] ?? 10
   $offset = ($page - 1) * $limit;

Trên đây là toàn bộ nội dung bài viết này hi vọng sẽ giúp ích được cho các bạn tìm hiểu. Link các bài viết tham khảo thêm:

MySql

Mục lục