记录一次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 以上两种方式差不多。