MySQL的LIMIT與分頁最佳化

標籤：

在系統中需要進行分頁操作的時候，我們通常會使用LIMIT加上位移量的辦法實現，同時加上合適的ORDER BY子句。如果有對應的索引，通常效率會不錯，否則，MySQL需要做大量的檔案排序操作。

一個非常常見又令人頭疼的問題就是，在位移量非常大的時候，例如可能是LIMIT 10000,20這樣的查詢，這時MySQL需要查詢10020條記錄後只返回最後20條，前面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);

這裡的“延遲關聯”將大大提升查詢效率，它讓MySQL掃描儘可能少的頁面，擷取需要訪問的記錄後再根據關聯列回原表查詢需要的所有列。這個技術也可以用於最佳化關聯查詢中的LIMIT子句。

有時候也可以將LIMIT查詢轉換為已知的位置的查詢，讓MySQL通過範圍掃描獲得到對應的結果。例如，如果在一個位置列上有索引，並且預先計算出了邊界值，上面的查詢就可以改寫為：

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

對資料進行排名的問題也與此類似，但往往還會同時和GROUP BY混合使用。在這種情況下通常都需要預先計算並儲存排名資訊。

LIMIT和OFFSET的問題，其實是OFFSET的問題，它會導致MySQL掃描大量不需要的行然後再拋棄掉。如果可以使用書籤記錄上次取資料的位置，那麼下次就可以直接從該書籤記錄的位置開始掃描，這樣就可以避免使用OFFSET。例如，若需要按照租借記錄做翻頁，那麼可以根據最新一條租借記錄向後追溯，這種做法可行是因為租借記錄的主鍵是單調增長的。首先使用下面的查詢獲得一組結果：

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

假設上面的查詢返回的是主鍵為16049到16030的租借記錄，那麼下一頁查詢就可以從16030這個點開始：

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

該技術的好處是無論翻頁到那麼後面，其效能都會很好。

其他最佳化辦法還包括使用預先計算的匯總表，或者關聯到一個冗餘表，冗餘表只包含主鍵列和需要做排序的資料列。

分頁的時候，另一個常用的技巧是在LIMIT語句中加上SQL_CALC_FOUNT_ROWS提示(hint)，這樣就可以獲得去掉LIMIT以後滿足條件的行數，因此可以作為分頁的總數。看起來，MySQL做了一些非常“高深”的最佳化，像是通過某種方法預測了總行數。但實際上，MySQL只有在掃描了所有滿足條件的行以後，才會知道行數，所以加上這個提示以後，不管是否需要，MySQL都會掃描所有滿足條件的行，然後再拋棄掉不需要的行，而不是在滿足LIMIT的行數後就終止掃描。所以該提示的代價可能非常高。

一個更好的設計是將具體的頁數換成“下一頁”按鈕，假設每頁顯示20條記錄，那麼我們每次查詢的時候都是LIMIT返回21條記錄並只顯示20條，如果第21條存在，那麼我們就顯示“下一頁”按鈕，否則就說明沒有更多的資料，也就無須顯示“下一頁”按鈕了。

另一種做法是先擷取並緩衝較多的資料，例如，緩衝1000條，然後每次分頁都從這個緩衝中擷取。這樣做可以讓應用程式根據結果集的大小採取不同的策略，如果結果集少於1000，就可以在頁面上顯示所有的分頁連結，因為資料都在緩衝中，所以這樣做效能不會有問題。如果結果集大於1000，則可以在頁面上設計一個額外的“找到的結果多於1000條”之類的按鈕。這兩種策略都比每次產生全部結果集再拋棄掉不需要的資料的效率要高很多。

有時候也可以考慮使用EXPLAIN的結果中的rows列的值來作為結果集總數的近似值（實際上Google的搜尋結果總數也是個近似值）。當需要精確結果的時候，再單獨使用COUNT(*)來滿足需求，這時如果能夠使用索引覆蓋掃描則通常也會比SQL_CALC_FOUND_ROWS快得多。

MySQL的LIMIT與分頁最佳化