SQL分頁過多時, 如何優化

問題：

我們經常會使用到分頁操作，這裡有個問題，在偏移量非常大的時候，它會導緻MySQL掃描大量不需要的行然後再抛棄掉。如：

SELECT id, name FROM A ORDER BY id DESC LIMIT 10000, 20;      

上述這條SQL語句需要查詢10020條記錄然後隻傳回最後20條。前面的10000條記錄都将被抛棄，這樣代價非常高。

方法一、延遲關聯

優化此類分類查詢的一個最簡單的辦法就是盡可能地使用索引覆寫掃描（如果一個索引包含（或者說覆寫）所有需要查詢的字段的值，我們就稱之為“覆寫索引”。可以使用explain檢視extra列資訊，如果看到“Using index”的資訊則說明使用到了覆寫索引。），而不是查詢所有的列。然後根據需要做一次關聯操作再傳回所需的列。對于偏移量很大的時候，這樣做的效率會提升非常大。

如上述SQL語句可以修改為：

SELECT id, name 
     FROM A 
         INNER JOIN (
            SELECT id FROM A 
            ORDER BY id DESC LIMIT 10000, 20
         ) AS tmp USING(id);      

這裡的“延遲關聯”将大大提升查詢效率，它讓MySQL掃描盡可能少的頁面，擷取需要通路的記錄後再根據關聯列回原表查詢需要的所有列。

方法二、使用書簽記錄上次取資料的位置

可以使用書簽記錄上次取資料的位置，那麼下次就可以直接從該書簽記錄的位置開始掃描，這樣就可以避免使用OFFSET。

如上一次記錄到10000為止，則可以修改為：

SELECT id, name FROM A WHERE id < 10000 ORDER BY id DESC LIMIT 20;      

這種方法的好處是無論翻頁到多麼後面，其性能都會很好。

方法三：使用between and

有時候也可以将LIMIT查詢轉換為已知位置的查詢，讓MySQL通過範圍掃描獲得到對應的結果。

如知道邊界值為10000，10020後上述語句可以修改為：

SELECT id , name FROM A WHERE id BETWEEN 10000 AND 10020 ORDER BY id DESC;      

除上述方法外，還有一些其它方法，如：

• 使用預先計算的彙總表
• 關聯到一個備援表，備援表隻包含主鍵列和需要做排序的資料列
• 使用Sphinx優化一些搜尋操作