大数据单表查询mysql

千万级别的表中获取分页数据（尤其在获取第百万页的数据时，查询时间差别明显）

select * from score WHERE 1=1 AND score_status =1 limit 11014490,400000      

平均80-90秒

 

select * from score WHERE 1=1 AND score_status =1 AND id BETWEEN  11014490 AND 11014490

平均2-3秒

select * from score WHERE id in (SELECT a.id FROM (select id from score WHERE 1=1 AND score_status =1 ORDER BY ID limit 11014490,400000) a)

平均3-5秒

 

select * from score WHERE 1=1 AND obj_type=2  AND score_status =1 AND id <11014491  ORDER BY id DESC limit 400000 

平均2-3秒