在有分頁查詢的應(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)合查詢”能夠有效工作是因?yàn)樗褂昧怂饕郎p少了服務(wù)端盡可能少地訪問數(shù)據(jù)行去檢查數(shù)據(jù)誊辉。一旦復(fù)核要求的行查到了，將他們與對應(yīng)的數(shù)據(jù)表的行進(jìn)行聯(lián)合查詢以獲取對應(yīng)行的其他列亡脑。
有些時候也可以將 limit 轉(zhuǎn)換為固定位置的查詢堕澄，這種方式可以對索引進(jìn)行范圍掃描完成。例如霉咨，如果你預(yù)先計(jì)算一個固定位置的列 稱之為 position蛙紫，可以重寫查詢?nèi)缦拢?/p>
SELECT film_id, description FROM sakila.film
WHERE position BETWEEN 50 AND 54 ORDER BY position;
排序的數(shù)據(jù)也可以使用類似的方式解決，但是通常會被 GROUP BY操作影響途戒。大部分情況下需要提前計(jì)算和存儲排序值坑傅。
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ù)先計(jì)算的統(tǒng)計(jì)值崔泵，或者通過聯(lián)合冗余了主鍵和排序列的數(shù)據(jù)表進(jìn)行查詢秒赤，這兩種方式都是通過空間換取時間的方式提高查詢效率。
