使用子查詢提高MySQL分頁效率

今天在老王的部落格中看到了一篇《驗證使用子查詢提高MySQL分頁效率》的文章，很有收穫，總結分享之~

對於有大資料量的mysql表來說，使用LIMIT分頁存在很嚴重的效能問題。例如老王做的測試，拿一個接近一千萬行記錄的表，進行查詢從第1000000之後的30條記錄：

 

SQL代碼1：平均用時6.6秒
SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

SQL代碼2：平均用時0.6秒
SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30

因為要取出所有欄位內容，第一種需要跨越大量資料區塊並取出，而第二種基本通過直接根據索引欄位定位後，才取出相應內容，效率自然大大提升。

可以看出，越往後分頁，LIMIT語句的位移量就會越大，兩者速度差距也會越明顯。實際應用中，可以利用類似策略模式的方式去處理分頁，比如判斷如果是一百頁以內，就使用最基本的分頁方式，大於一百頁，則使用子查詢的分頁方式。