記錄一次mysql分頁查詢優化方案

mysql表資料  3467376

select * from mmp_coupon_info_detail limit 10,20   #0.021 select * from mmp_coupon_info_detail limit 100,20   #0.013 select * from mmp_coupon_info_detail limit 1000,20   #0.014 select * from mmp_coupon_info_detail limit 10000,20   #0.022 select * from mmp_coupon_info_detail limit 400000,20   #0.436 以上分頁查詢效率還可以。再往下看

   select * from mmp_coupon_info_detail limit 3478908,20   #3.733

   當查詢最後一頁時，效率明顯下降

   select id from mmp_coupon_info_detail limit 3478908,20  #1.163

   這個是利用了主鍵索引，是以查詢效率比較高

   #利用表的覆寫索引來加速分頁查詢

select * from mmp_coupon_info_detail where id >(select id from mmp_coupon_info_detail limit 3478908,1) limit 20  #1.472 select * from mmp_coupon_info_detail a join (select id from mmp_coupon_info_detail limit 3478908,20) b on a.id=b.id  #1.555 以上兩種方式差不多。