VietNetwork.Vn

Các truy vấn có LIMIT phổ biến trong ứng dụng yêu cầu phân trang và trong một số trường hợp có thể hoạt động tốt trong một thời gian.

Tuy nhiên, trong nhiều trường hợp, chúng trở nên chậm chạp và đau đớn khi LIMIT có giá trị cao.

1. Tại sao LIMIT quá chậm?

Chà, trong hầu hết các trường hợp, các truy vấn có độ lệch thấp không chậm. Sự cố bắt đầu với các giá trị LIMIT cao.

Nếu truy vấn của bạn đang sử dụng mệnh đề giới hạn sau: "LIMIT 50000, 20", thì thực tế nó đang yêu cầu cơ sở dữ liệu duyệt qua 50.020 hàng và loại bỏ 50.000 hàng đầu tiên. Hành động này có thể có chi phí cao và thời gian phản hồi tác động.


Bạn có thể tự hỏi mình "cái quái gì sẽ chuyển đến trang 50.000 trong ứng dụng của tôi vậy?".
Hãy liệt kê một vài trường hợp sử dụng có thể:

• Công cụ tìm kiếm yêu thích của bạn (Google / Bing / Yahoo / DuckDuckGo / bất cứ thứ gì) sắp lập chỉ mục trang web thương mại điện tử của bạn. Bạn có khoảng 100.000 trang trong trang web đó. Ứng dụng của bạn sẽ phản ứng như thế nào khi bot tìm kiếm sẽ cố gắng tìm nạp 50.000 trang cuối cùng đó để lập chỉ mục cho chúng? Điều đó sẽ xảy ra thường xuyên như thế nào?
• Trong hầu hết các ứng dụng web, chúng tôi cho phép người dùng chuyển đến trang cuối cùng chứ không chỉ trang tiếp theo. Điều gì sẽ xảy ra khi người dùng cố gắng chuyển đến trang 50.000 sau khi truy cập trang 2?
• Điều gì xảy ra nếu người dùng truy cập vào trang 20.000 từ kết quả tìm kiếm của Google, thích nội dung nào đó ở đó và đăng nó lên facebook để 1000 người bạn khác đọc?

Chúng tôi đã thử nghiệm các giá trị LIMIT sau bằng truy vấn sau đây để trình bày sự suy giảm hiệu suất khi LIMIT tăng lên.

Truy vấn được thực hiện trên một bảng chứa các sự kiện do người dùng thực hiện (bảng phân tích) với 150.000 bản ghi. Dữ liệu là thông tin người dùng thực và không được tạo tự động.
   
SELECT
    *
FROM
    events
WHERE
    date > '2010-01-01T00:00:00-00:00'
        AND event = 'editstart'
ORDER BY date
LIMIT 50;


2. Làm cách nào để tối ưu hóa các truy vấn LIMIT chậm?

Để tối ưu hóa các truy vấn LIMIT chậm, bạn có thể giới hạn số lượng trang được phép trong chế độ xem phân trang hoặc đơn giản là không sử dụng LIMIT.

Một giải pháp thay thế tốt cho việc sử dụng LIMIT sẽ là phương pháp tìm kiếm, cũng được cả Lukas Eder và Markus Winand khuyên dùng trong blog của họ.

Nói một cách đơn giản, phương pháp tìm kiếm là tìm kiếm một cột duy nhất hoặc một tập hợp các cột xác định từng hàng. Sau đó, thay vì sử dụng mệnh đề LIMIT, chúng ta chỉ có thể sử dụng giá trị duy nhất đó làm dấu trang trình bày vị trí của hàng cuối cùng mà chúng ta đã tìm nạp và truy vấn tập hợp các hàng tiếp theo bằng cách bắt đầu từ vị trí này trong mệnh đề WHERE.

Ví dụ: xem xét các truy vấn chúng tôi đã thực hiện trước đó, giả sử id sự kiện cuối cùng trong phần bù 999,999 là '111866', truy vấn sẽ là:

SELECT    *
FROM
    events
WHERE
    (date,id) > ('2010-07-12T10:29:47-07:00',111866)
        AND event = 'editstart'
ORDER BY date, id
LIMIT 10
Một cách khác để viết truy vấn là:

SELECT    *
FROM
    events
WHERE
    date>='2010-07-12T10:29:47-07:00' and not (date='2010-07-12T10:29:47-07:00' and id < 111866)
        AND event = 'editstart'
ORDER BY date, id
LIMIT 10
Xin lưu ý rằng bạn cần đảm bảo sắp xếp theo các cột duy nhất để thứ tự luôn được giữ giống nhau giữa các trang, nếu không bạn có thể gặp phải hành vi không mong muốn.

Đây là một so sánh về hiệu suất giữa cả hai phương pháp. Quan sát thú vị ở đây không chỉ là hiệu suất của phương pháp Seek tốt hơn mà còn ổn định hơn cho dù bạn đánh số trang vào bảng bao xa.


3. Cạm bẫy và thách thức có thể

Bạn sẽ cần phải thay đổi một số mã trong ứng dụng của mình để phương thức này hoạt động, bằng cách lưu hàng được tìm nạp cuối cùng (thay vì điều chỉnh giá trị LIMIT có liên quan).

Cần có một chỉ mục trên cột/bộ cột tìm kiếm duy nhất.

Mỗi cột trong nhóm cột duy nhất phải có ràng buộc NOT NULL, nếu không, bạn có thể gặp phải một số hành vi không mong muốn.

Khi người dùng bỏ qua các trang, trước tiên bạn cần tìm nạp vị trí có liên quan của trang đó. Giả sử chúng tôi muốn bỏ qua trang 40.000:

SELECT date, id
FROM events
ORDER BY date, id
LIMIT 1 OFFSET 39999;
Truy vấn này sẽ cực kỳ nhanh chóng vì nó đang sử dụng chỉ mục bao trùm.

Chúng tôi khuyên bạn không nên sử dụng khả năng LIMIT trong MySQL để triển khai khả năng phân trang. Khi dữ liệu tăng lên, có thể bạn sẽ bắt đầu nhận thấy các vấn đề về hiệu suất. Thay vào đó, hãy cân nhắc sử dụng phương pháp tìm kiếm được mô tả ở trên hoặc sử dụng index cho tất cả các bảng.

Ngoài ra bạn cũng có thể tham khảo thêm cách tối ưu câu truy vấn MySQL khi sử dụng mệnh Group, Order By với LIMIT kèm index các bảng tại đây:   Đăng nhập để xem liên kết