记录一次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

以上两种方式差不多。