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

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

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

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ù)核要求的行查到了，將他們與對(duì)應(yīng)的數(shù)據(jù)表的行進(jìn)行聯(lián)合查詢以獲取對(duì)應(yīng)行的其他列。

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

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

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

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

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

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

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

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

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

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

最新評(píng)論