SELECT id, cu_id,name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where biz_type ='0'AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20;
表的数据量大致有36w左右,该sql是一个非常典型的排序+分页查询:order by col limit N,OFFSET M , MySQL 执行此类sql时需要先扫描到N行,然后再去取 M行。对于此类大数据量的排序操作,取前面少数几行数据会很快,但是越靠后,sql的性能就会越差,因为N越大,MySQL 需要扫描不需要的数据然后在丢掉,这样耗费大量的时间。
SELECT a.* FROM relation a,(select id from relation where biz_type ='0'AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id
root@xxx 12:33:43>explain SELECT a.* FROM relation a,(select id from relation where biz_type ='0'AND end_time >='2014-05-29' ORDER BY id asc LIMIT 149420 ,20 ) b where a.id=b.id;