在有分頁查詢的應用中，包括 LIMIT 和 OFFSET 的查詢十分常見，而且幾乎每個都會有一個 ORDER BY 子句。如果使用索引排序的話將對性能優化十分有幫助，否則服務端需要做很多文件排序。

一個高頻的問題是 offset 的值過大。如果查詢類似 LIMIT 10000, 20，將會產生10020行，并將之前的10000行丟棄，這樣的代價很高。假設所有的頁使用相同的頻次訪問，這樣的查詢將平均掃描一半數據表。為了優化他們，你可以在分頁視圖中限制最多可訪問的頁數，或者讓大便宜的查詢更有效。

一個改善性能簡單的技巧是在覆蓋索引上進行查詢操作而不是整行數據。你可以將結果與完整的行做一次聯合然后再獲取額外需要的列。這樣的效率會更高，例如下面的查詢：

SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

如果數據表很大的話，則可以按下面的方式進行優化：

SELECT film.film_id, film.description
FROM sakila.film
	INNER JOIN (
    SELECT film_id FROM sakila.film
    ORDER BY title LIMIT 50, 5)
  ) as lim USING(film_id);

這種“推斷聯合查詢”能夠有效工作是因為它使用了索引減少了服務端盡可能少地訪問數據行去檢查數據。一旦復核要求的行查到了，將他們與對應的數據表的行進行聯合查詢以獲取對應行的其他列。

有些時候也可以將 limit 轉換為固定位置的查詢，這種方式可以對索引進行范圍掃描完成。例如，如果你預先計算一個固定位置的列 稱之為 position，可以重寫查詢如下：

SELECT film_id, description FROM sakila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;

排序的數據也可以使用類似的方式解決，但是通常會被 GROUP BY操作影響。大部分情況下需要提前計算和存儲排序值。

LIMIT 和 OFFSET 真正的問題是在OFFSET，這意味著服務端會把很多數據行丟棄。如果使用一個有序書簽來記錄下次獲取行的位置的話，則可以從上次的位置開始訪問接下來的數據。例如，如果你需要對出租記錄進行分頁，從最新的出租記錄開始往回查詢，則可以依賴于記錄的主鍵是一直增加的，因此可以對第一頁數據這樣查詢：

SELECT * FROM sakila.rental
ORDER BY rental_id DESC LIMIT 20;

這個查詢返回16049到16030之間的數據。接下來的查詢可以從之前結束位置開始：

SELECT * FROM sakila.rental
WHERE rental_id  16030 
ORDER BY rental_id DESC LIMIT 20;

這個技巧不管你從多遠的偏移值開始查詢都是很有效的。

其他的一些技巧包括使用預先計算的統計值，或者通過聯合冗余了主鍵和排序列的數據表進行查詢，這兩種方式都是通過空間換取時間的方式提高查詢效率。

以上就是MySQL 分頁查詢的優化技巧的詳細內容，更多關于MySQL 分頁查詢的優化的資料請關注腳本之家其它相關文章！