在有分頁查詢的應(yīng)用中，包括 LIMIT 和 OFFSET 的查詢十分常見，而且?guī)缀趺總€都會有一個 ORDER BY 子句。如果使用索引排序的話將對性能優(yōu)化十分有幫助，否則服務(wù)端需要做很多文件排序。

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

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

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

如果數(shù)據(jù)表很大的話，則可以按下面的方式進(jìn)行優(yōu)化：

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);

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

有些時候也可以將 limit 轉(zhuǎn)換為固定位置的查詢，這種方式可以對索引進(jìn)行范圍掃描完成。例如，如果你預(yù)先計算一個固定位置的列 稱之為 position，可以重寫查詢?nèi)缦拢?/p>

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

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

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

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

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

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

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

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

以上就是MySQL 分頁查詢的優(yōu)化技巧的詳細(xì)內(nèi)容，更多關(guān)于MySQL 分頁查詢的優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！